MySQL数据库基础合集

MySQL数据库基础合集

SQL关键字

DDL关键字

DDL关键字	含义
CREATE TABLE	创建一个新的数据库表
ALTER TABLE	修改现有数据库表的结构或属性
DROP TABLE	删除数据库中的表
MODIFY COLUMN	修改列
ADD COLUMN	新增列
CREATE INDEX	创建索引
DROP INDEX	删除索引
CREATE VIEW	创建视图
DROP VIEW	删除视图
CREATE FUNCTION	创建函数
ALTER FUNCTION	修改已存在的函数
DROP FUNCTION	删除函数
ENUM()	枚举(多选多)
SET()	集合(多选一)

DML关键字

DML关键字	含义
SELECT	从数据库中检索数据
INSERT INTO	向数据库表中插入新的行或数据
UPDATE	更新数据库表中的现有行或数据
DELETE FROM	从数据库表中删除行或数据

DQL关键字

DQL关键字	含义
SELECT	SELECT 字段列表，查询检索的列或表达式，它指定了最终结果包含的列
FROM	FROM 表名，指定了要查询的列表或视图，例FROM table_name
WHERE	WHERE 条件列表，筛选查询结果，通常是列(字段)的条件表达式，例：WHERE id = 10
GROUP BY	GROUP BY 分组字段列表，根据一个或多个列对结果进行分组，但是通常需要满足聚合条件
HAVING	HAVING 分组后的条件列表，用于在GROUP BY子句的结果上进行过滤筛选
ORDER BY	ORDER BY 排序字段列表，用于按照一个或多个列对结果进行排序，它可以按升序（ASC）或降序（DESC）对查询结果进行排序
LIMIT	LIMIT 分页参数，一般在语句的最后，用于限制查询结果的数量
DQL执行顺序	FROM -> WHERE -> GROUP BY -> HAVING ->SELECT -> ORDER BY -> LIMIT

DCL关键字

DCL关键字	含义
GRANT	授予用户或角色特定的权限
REVOKE	撤销用户或角色的权限
COMMIT	提交当前事务
ROLLBACK	回滚当前事务
SAVEPOINT	在事务中设置保存点
SET	设置会话级别的参数

约束关键字

约束关键字	含义
DEFAULT	默认约束：为列设置默认值
AUTO_INCREMENT	自增约束：自动递增生成唯一标识符
UNIQUE	唯一约束：唯一约束确保列中的值是唯一的，但允许为空值
NOT NULL	非空约束：非空约束要求列中的值不能为空
CHECK	检查约束：检查约束定义了对列值的条件限制
PRIMARY KEY	主键约束：主键必须是唯一且非空的，每个表只能有一个主键
FOREIGN KEY	外键约束：用于建立表与表之间的关联关系，确保引用的数据存在于其他表中的主键列中
ZEROFILL	填充约束：以0填充，int(4) ZEROFILL 赋值1结果= 0001
UNSIGNED	非负约束：只能存储非负整数类型

SQL基础数据类型

整数类型

类型	含义
TINYINT	占用1个字节，范围-128-127，无符号范围0-255
SMALLINT	占用2个字节，范围-32768-32767，无符号范围0-65535
MEDIUMINT	占用3个字节，范围-8388608-8388607，无符号范围0-16777215
INT	占用4个字节，范围-2147483648-2147483647，无符号范围0-4294967295
BIGINT	占用8个字节，范围-9223372036854775808-9223372036854775807，无符号范围0-18446744073709551615

字符类型

类型	含义
CHAR	默认长度为1，可指定长度，例如CHAR(10)为长度10
VARCHAR	默认长度为1，可指定长度，例如VARCHAR(32)为长度32
TINYTEXT	最多可容纳255个字符
TEXT	无固定长度限制，可以容纳更大的字符数量，最多可容纳65535个字符（约64KB）
MEDIUMTEXT	最多容纳16777215个字符（约16MB）
LONGTEXT	最多可容纳4294967295个字符（约4GB）
ENUM	用于存储预定义的枚举值之一。需要定义可选的枚举值，例如ENUM('A', 'B', 'C')
SET	用于存储从一个或多个预定义选项集合中选择的值。需要定义可选的集合，例如SET('option1', 'option2', 'option3')

浮点类型

类型	含义
FLOAT	单精度浮点数，精度约为6-7位有效数字
DOUBLE	双精度浮点数，精度约为15-16位有效数字
DECIMAL	用于存储高精度的十进制数，精度由用户指定

时间类型

类型	含义
DATE	格式为’YYYY-MM-DD’
TIME	格式为’HH:MM:SS’
DATETIME	格式为’YYYY-MM-DD HH:MM:SS’
TIMESTAMP	类似于DATETIME，格式为’YYYY-MM-DD HH:MM:SS’，用DEFAULT将默认值设置为CURRENT_TIMESTAMP可以默认当前时间
YEAR	用于存储年份值，表示四位数的年份

数据定义语言DDL

主要分为

• CREAT：创建
• DROP：删除
• ALTER：修改

登录MySQL

mysql -uroot -p
Enter password: ****

1.查看数据库

show databases;

2.创建库

create database emp;

3.删除库

drop database users;

4.切换库

use emp;

5.创建表

create table emp(id int,name varchar(32),age int);

• enum插入数据时只能是enum中包含的数据
• set插入数据时必须是set数据中的一个

create table emp(id int,name varchar(32),hobby enum('read','run','listen'), gender set('male','female'));

6.删除表

注意要先use选择数据库

drop table emp;

7.查看表

show tables;

8.查看表属性

desc emp;

9.插入列

alter table emp add(sex varchar(2));

10.修改列

必须是当前表中存在的属性

alter table emp modify column name varchar(24);

11.设置主键

修改

alter table emp modify column id int primary key;

新建

create table emp1(id int primary key);

12.设置外键并绑定主键

表1

create table dep (id int primary key auto_increment,name varchar(32) comment'部门名');

表2

create table emp (id int primary key auto_increment,name varchar(32) comment'员工名',dep_id int, foreign key(dep_id) references dep(id));

• 当外键表2试图添加表1主键中不存在的数据时便会报错

• 当表1企图删除表2所在的行时也会报错(修改同理)

13.设置自增

alter table emp modify column id int auto_increment;

14.删除列

alter table emp drop column sex;

15.重命名

RENAME

rename table emp to stu;

16.设定默认值

DEFAULT

create table emp3(id int,name varchar(32),age varchar(3) default "男");

17.添加备注

COMMENT

为表添加备注：

create table emp4(id int,name varchar(32)) COMMENT '员工表';

为字段添加备注：

create table emp4(id int COMMENT '员工编号',name varchar(32) COMMENT '员工姓名');

ALTER table emp4 modify column name varchar(32) COMMENT '员工名';

18.设置是否可为空

默认情况都是可以为空

NULL：可以为空

create table emp4(id int COMMENT '员工编号',name varchar(32) NULL COMMENT '员工姓名');

NOT NULL：不可为空

create table emp4(id int COMMENT '员工编号',name varchar(32) NOT NULL COMMENT '员工姓名');

数据操纵语言DML

• SELECT：查表，SELECT在官方文档中隶属于DML，但是我们一般将其当做DQL操作
• INSERT：插入数据
• UPDATE：更新数据
• DELETE：删除数据

1.查表

查表之前首先要选择库use database_name

查看emp表全部列

select * from emp5;

查看name字段

select name from emp5

2.插入数据

insert into 表 (列名1,列名2,列名3...) values (值1,值2,值3...)；

insert into emp5(name) value("李四");

3.更新数据

update emp5 set name="王五" where id=1;

4.删除数据

delete from emp5 where id=4;

删除表中所有数据

delete from emp5 ;

或者

truncate emp5;

数据查询语言DQL

1.子查询

• 将一个查询语句的结果当做另外一个查询语句的条件使用

+----+--------+---------------------+------+-------+
| id | name   | time                | age  | wages |
+----+--------+---------------------+------+-------+
|  1 | 张三   | 2024-01-25 15:24:19 |   18 |  2000 |
|  2 | 倪哥   | 2024-01-25 15:34:11 |   20 |  1000 |
|  3 | 李四   | 2024-01-25 16:14:19 |   18 |  2000 |
|  4 | 陈五   | 2024-01-25 16:14:19 |   19 |  1000 |
|  5 | 王六   | 2024-01-25 16:14:19 |   19 |  2000 |
|  6 | 横七   | 2024-01-25 16:14:19 |   20 |  1000 |
|  7 | 竖八   | 2024-01-25 16:14:19 |   20 |  2200 |
+----+--------+---------------------+------+-------+
-- 查询最大工资
select max(wages) from emp5;
+------------+
| max(wages) |
+------------+
|       2200 |
+------------+
-- 将最大工资作为条件插入查询name字段的语句中
select name from emp5 where wages = (select max(wages) from emp5);
+--------+
| name   |
+--------+
| 竖八   |
+--------+
-- 多个字段同理
select name,age,wages from emp5 where wages = (select max(wages) from emp5);
+--------+------+-------+
| name   | age  | wages |
+--------+------+-------+
| 竖八   |   20 |  2200 |
+--------+------+-------+

2.联表查询

-- 查询部门表
select * from dep
+-----+--------------+
| id  | name         |
+-----+--------------+
| 200 | 技术部       |
| 201 | 人力资源     |
| 202 | 销售部       |
| 203 | 运营部       |
| 204 | 售后部       |
+-----+--------------+-- 查询员工表
select * from emp5;
+----+--------+---------------------+------+-------+--------+
| id | name   | time                | age  | wages | dep_id |
+----+--------+---------------------+------+-------+--------+
|  1 | 张三   | 2024-01-25 15:24:19 |   18 |  2000 |    203 |
|  2 | 倪哥   | 2024-01-25 15:34:11 |   20 |  1000 |    201 |
|  3 | 李四   | 2024-01-25 16:14:19 |   18 |  2000 |    202 |
|  4 | 陈五   | 2024-01-25 16:14:19 |   19 |  1000 |    203 |
|  5 | 王六   | 2024-01-25 16:14:19 |   19 |  2000 |    201 |
|  6 | 横七   | 2024-01-25 16:14:19 |   20 |  1000 |    200 |
|  7 | 竖八   | 2024-01-25 16:14:19 |   20 |  2200 |    205 |
+----+--------+---------------------+------+-------+--------+-- 联表查询dep和emp5
select * from dep,emp5;
+-----+--------------+----+--------+---------------------+------+-------+--------+
| id  | name         | id | name   | time                | age  | wages | dep_id |
+-----+--------------+----+--------+---------------------+------+-------+--------+
| 204 | 售后部       |  1 | 张三   | 2024-01-25 15:24:19 |   18 |  2000 |    203 |
| 203 | 运营部       |  1 | 张三   | 2024-01-25 15:24:19 |   18 |  2000 |    203 |
| 202 | 销售部       |  1 | 张三   | 2024-01-25 15:24:19 |   18 |  2000 |    203 |
| 201 | 人力资源     |  1 | 张三   | 2024-01-25 15:24:19 |   18 |  2000 |    203 |
| 200 | 技术部       |  1 | 张三   | 2024-01-25 15:24:19 |   18 |  2000 |    203 |
| 204 | 售后部       |  2 | 倪哥   | 2024-01-25 15:34:11 |   20 |  1000 |    201 |
| 203 | 运营部       |  2 | 倪哥   | 2024-01-25 15:34:11 |   20 |  1000 |    201 |
| 202 | 销售部       |  2 | 倪哥   | 2024-01-25 15:34:11 |   20 |  1000 |    201 |
| 201 | 人力资源     |  2 | 倪哥   | 2024-01-25 15:34:11 |   20 |  1000 |    201 |
| 200 | 技术部       |  2 | 倪哥   | 2024-01-25 15:34:11 |   20 |  1000 |    201 |
| 204 | 售后部       |  3 | 李四   | 2024-01-25 16:14:19 |   18 |  2000 |    202 |
| 203 | 运营部       |  3 | 李四   | 2024-01-25 16:14:19 |   18 |  2000 |    202 |
| 202 | 销售部       |  3 | 李四   | 2024-01-25 16:14:19 |   18 |  2000 |    202 |
| 201 | 人力资源     |  3 | 李四   | 2024-01-25 16:14:19 |   18 |  2000 |    202 |
| 200 | 技术部       |  3 | 李四   | 2024-01-25 16:14:19 |   18 |  2000 |    202 |
| 204 | 售后部       |  4 | 陈五   | 2024-01-25 16:14:19 |   19 |  1000 |    203 |
| 203 | 运营部       |  4 | 陈五   | 2024-01-25 16:14:19 |   19 |  1000 |    203 |
| 202 | 销售部       |  4 | 陈五   | 2024-01-25 16:14:19 |   19 |  1000 |    203 |
| 201 | 人力资源     |  4 | 陈五   | 2024-01-25 16:14:19 |   19 |  1000 |    203 |
| 200 | 技术部       |  4 | 陈五   | 2024-01-25 16:14:19 |   19 |  1000 |    203 |
| 204 | 售后部       |  5 | 王六   | 2024-01-25 16:14:19 |   19 |  2000 |    201 |
| 203 | 运营部       |  5 | 王六   | 2024-01-25 16:14:19 |   19 |  2000 |    201 |
| 202 | 销售部       |  5 | 王六   | 2024-01-25 16:14:19 |   19 |  2000 |    201 |
| 201 | 人力资源     |  5 | 王六   | 2024-01-25 16:14:19 |   19 |  2000 |    201 |
| 200 | 技术部       |  5 | 王六   | 2024-01-25 16:14:19 |   19 |  2000 |    201 |
| 204 | 售后部       |  6 | 横七   | 2024-01-25 16:14:19 |   20 |  1000 |    200 |
| 203 | 运营部       |  6 | 横七   | 2024-01-25 16:14:19 |   20 |  1000 |    200 |
| 202 | 销售部       |  6 | 横七   | 2024-01-25 16:14:19 |   20 |  1000 |    200 |
| 201 | 人力资源     |  6 | 横七   | 2024-01-25 16:14:19 |   20 |  1000 |    200 |
| 200 | 技术部       |  6 | 横七   | 2024-01-25 16:14:19 |   20 |  1000 |    200 |
| 204 | 售后部       |  7 | 竖八   | 2024-01-25 16:14:19 |   20 |  2200 |    205 |
| 203 | 运营部       |  7 | 竖八   | 2024-01-25 16:14:19 |   20 |  2200 |    205 |
| 202 | 销售部       |  7 | 竖八   | 2024-01-25 16:14:19 |   20 |  2200 |    205 |
| 201 | 人力资源     |  7 | 竖八   | 2024-01-25 16:14:19 |   20 |  2200 |    205 |
| 200 | 技术部       |  7 | 竖八   | 2024-01-25 16:14:19 |   20 |  2200 |    205 |
+-----+--------------+----+--------+---------------------+------+-------+--------+-- 添加条件限制,当员工表中的部门id = 部门表中id时，如果不存在对应的部门则不打印(比如id7的205部门)
select * from dep,emp5 where emp5.dep_id=dep.id;
+-----+--------------+----+--------+---------------------+------+-------+--------+
| id  | name         | id | name   | time                | age  | wages | dep_id |
+-----+--------------+----+--------+---------------------+------+-------+--------+
| 203 | 运营部       |  1 | 张三   | 2024-01-25 15:24:19 |   18 |  2000 |    203 |
| 201 | 人力资源     |  2 | 倪哥   | 2024-01-25 15:34:11 |   20 |  1000 |    201 |
| 202 | 销售部       |  3 | 李四   | 2024-01-25 16:14:19 |   18 |  2000 |    202 |
| 203 | 运营部       |  4 | 陈五   | 2024-01-25 16:14:19 |   19 |  1000 |    203 |
| 201 | 人力资源     |  5 | 王六   | 2024-01-25 16:14:19 |   19 |  2000 |    201 |
| 200 | 技术部       |  6 | 横七   | 2024-01-25 16:14:19 |   20 |  1000 |    200 |
+-----+--------------+----+--------+---------------------+------+-------+--------+

• 例：打印各个部门中最高工资的员工信息

select * from emp5 where wages in (select max(wages) from emp5 group by dep_id);

3.GROUP BY

• group by 作用于where之后，order by 之前
• 不适用group by的场景：查询后显示所有数据，且没有分组需要，例如当表中基本没有重复数据时就没必要分组

将表格emp5以wages(工资)分组(2000,1000,2200)

select wages from emp5 group by wages;

• 例：我想查询各个部门的最高工资：

select dep_id,max(wages) from emp5 group by dep_id;

• 配合函数作用列，如sum、max、avg、count等

select dep_id,sum(wages) from emp5 group by dep_id;
select dep_id,max(wages) from emp5 group by dep_id;
select dep_id,avg(wages) from emp5 group by dep_id;
select dep_id,count(*) from emp5 group by dep_id;

• group_concat可以查看到组内所有人的属性
  • 根据部门(dep_id)分组，并且显示各个部门的人员

select group_concat(name), dep_id from emp5 group by dep_id;

• group_concat也可以查看进行字符的拼接
  • 根据部门(dep_id)分组，并且显示各个部门的人员以及其工资

select group_concat(name,":",wages), dep_id from emp5 group by dep_id;

4.HAVING

• HAVING一般紧随GROUP BY之后，一般用于限定SELECT筛选的字段
• 查询各个部门的最高工资，并且排除薪资低于2000的

select dep_id,max(wages) from emp5 group by dep_id having max(wages)>2000;

• 查询各个部门的最高工资，并且只要202和203部门的

select dep_id,max(wages) from emp5 group by dep_id having dep_id in (202,203);

5.分页查询

• limit 0,2从第0页开始，往后每页2条数据
• limit 2,2每页两条数据从第二页开始查询

select * from emp5 limit 0,2

6.排序查询

• 升序：ASC
• 降序：DESC
• 按照员工的工资升序排序

select * from emp5 order by wages ASC;

• 按照员工的部门排序，当部门相同时按照工资升序排序

select * from emp5 order by dep_id ASC, wages ASC;

数据控制语言DCL

1.事务

事务的四大特性：原子性，一致性，隔离性，持久性

• START TRANSACTION：开始事务

start transaction;

• ROLLBACK：回滚

rollback

• COMMIT：提交事务

提交事务后意味着改部分sql语句已运行结束，不能再执行回滚操作

start transaction;
insert into emp(name) values("王五");
commit;	# 提交事务后无法再进行回滚

2.用户权限

• CREATE USER：创建新的用户并指定权限

username为用户名，password为密码，IDENTIFIED为指定用户密码的关键字

create user username IDENTIFIED by 'password';

• DROP USER：删除用户

DROP USER username;

• ALTER USER：更改用户权限或属性

ALTER USER username SET PASSWORD = 'new_password';

• GRANT：赋予用户权限

赋予user用户 SELECT，INSERT等权限，table_name为权限生效的表名(修改权限之前要use库)

GRANT SELECT, INSERT ON table_name TO user;

• REVOKE：取消用户权限

用法与grant相同

REVOKE SELECT, INSERT ON table_name FROM user;

6518652337)]

• COMMIT：提交事务

提交事务后意味着改部分sql语句已运行结束，不能再执行回滚操作

start transaction;
insert into emp(name) values("王五");
commit;	# 提交事务后无法再进行回滚

2.用户权限

• CREATE USER：创建新的用户并指定权限

username为用户名，password为密码，IDENTIFIED为指定用户密码的关键字

create user username IDENTIFIED by 'password';

• DROP USER：删除用户

DROP USER username;

• ALTER USER：更改用户权限或属性

ALTER USER username SET PASSWORD = 'new_password';

• GRANT：赋予用户权限

赋予user用户 SELECT，INSERT等权限，table_name为权限生效的表名(修改权限之前要use库)

GRANT SELECT, INSERT ON table_name TO user;

• REVOKE：取消用户权限

用法与grant相同

REVOKE SELECT, INSERT ON table_name FROM user;