Mysql

SQL

通用语法

1. 可以单行或多行书写，以分号结尾

2. 不区分大小写，关键字建议大写

3. 注释：
• 单行：– 注释内容 or # 注释内容
• 多行：/*注释内容*/

SQL分类

DDL

数据库操作

查询

SHOW DATABASES;

SHOW DATABASE();

创建

CREATE DATABASE [IF NOT EXISTS] 数据库名 [DEFAULT CHARSET 字符集] [COLLATE 排序规则];

删除

DROP DATABASE [IF EXISTS] 数据库名;

使用

USE 数据库名;

表操作

查询

SHOW TABLES;

DESC 表名;

SHOW CREATE TABLE 表名;

创建

CREATE TABLE 表名(
    字段1 字段1类型[COMMENT 字段1注释],
    字段2 字段2类型[COMMENT 字段2注释],
    字段3 字段3类型[COMMENT 字段3注释],
    ......    字段n 字段n类型[COMMENT 字段n注释]
)[COMMENT 表注释];

create table tb_user(
    id int comment '编号',
    name varchar(50) comment '姓名',
    age int comment '年龄',
    gender varchar(1) comment '性别') comment '用户表';

修改

ALTER TABLE 表名 ADD 字段名 类型 (长度) [ COMMENT 注释 ] [ 约束 ];

ALTER TABLE 表名 MODIFY 字段名 新数据类型 (长度);

ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型 (长度) [ COMMENT 注释 ] [ 约束 ];

ALTER TABLE emp CHANGE nickname username varchar(30) COMMENT '用户名';

ALTER TABLE 表名 DROP 字段名;

ALTER TABLE 表名 RENAME TO 新表名;

删除

DROP TABLE [ IF EXISTS ] 表名;

TRUNCATE TABLE 表名;

数据类型

数值类型

/*如:1). 年龄字段 -- 不会出现负数, 而且人的年龄不会太大 */age tinyint unsigned
/*2). 分数 -- 总分100分, 最多出现一位小数*/score double(4,1)

字符串类型

/*如：1). 用户名 username ------> 长度不定, 最长不会超过50*/username varchar(50)
/*2). 性别 gender ---------> 存储值, 不是男,就是女*/gender char(1)
/*3). 手机号 phone --------> 固定长度为11*/phone char(11)

日期时间类型

/*如:1). 生日字段 birthday*/birthday date/*2). 创建时间 createtime*/createtime datetime

DML

• 添加数据（INSERT）

• 修改数据（UPDATE）

• 删除数据（DELETE）

添加数据

• 字符串和日期型数据应该包含在引号中

• 不能爆数据类型

INSERT INTO 表名 (字段名1, 字段名2, ...) VALUES (值1, 值2, ...);

insert into user(id,name,age) values(1,'Kamisato Ayaka',18)

INSERT INTO 表名 VALUES (值1, 值2, ...);

insert into user values(2,'Ganyu',17);

INSERT INTO 表名 (字段名1, 字段名2, ...) VALUES (值1, 值2, ...), (值1, 值2, ...), (值1, 值2, ...) ;

INSERT INTO 表名 VALUES (值1, 值2, ...), (值1, 值2, ...), (值1, 值2, ...) ;

insert into user values(3,'Yae Miko',500),(4,'Raiden Shogun',300);

修改数据

• 不加where对整张表修改

UPDATE 表名 SET 字段名1 = 值1 , 字段名2 = 值2 , .... [ WHERE 条件 ] ;

删除数据

• 不加where删除整张表的数据

• 不能删除某一个字段的值（用update）

DELETE FROM 表名 [ WHERE 条件 ] ;

delete from user where id is null;

DQL

基本语法

SELECT 字段列表
FROM 表名列表
WHERE 条件列表
GROUP BY 分组字段列表
HAVING 分组后条件列表
ORDER BY 排序字段列表
LIMIT 分页参数

基础查询

SELECT 字段1, 字段2, 字段3 ... FROM 表名;
SELECT * FROM 表名;

SELECT 字段1 [AS 别名1], 字段2 [AS 别名2] ... FROM 表名
# AS 可省略

SELECT DISTINCT 字段列表 FROM 表名;

条件查询

SELECT 字段列表 FROM 表名 WHERE 条件列表;

select * from emp where name like '__';
# 查询名字是两个字的员工信息，每个_代表一个字符
select * from emp where idcard like '%X';
# 查询身份证结尾是X的员工信息

聚合函数

SELECT 聚合函数(字段列表) FROM 表名;

select sum(age) as 'xxx' from emp where workaddress='西安';

分组查询

SELECT 字段列表 FROM 表名 [WHERE 条件] GROUP BY 分组字段名 [HAVING 分组后过滤条件]

select workaddress from emp where age<45 group by workaddress having count(*)>=3;

排序查询

SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1 , 字段2 排序方式2 ;

select * from emp order by emp.age ,entrydate desc

分页查询

• 起始索引从0开始，起始索引 = （查询页码 - 1）* 每页显示记录数

• 分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中是LIMIT

• 如果查询的是第一页数据，起始索引可以省略，直接简写为 limit 10

SELECT 字段列表 FROM 表名 LIMIT 起始索引, 查询记录数;

select * from emp limit 10,10

DCL

用户管理

USE mysql;
SELECT * FROM user;

CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
CREATE USER '用户名'@'%' IDENTIFIED BY '密码';
#% 表示任意主机都可以访问

ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码';

DROP USER '用户名'@'主机名';

权限控制

SHOW GRANTS FOR '用户名'@'主机名';

GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';

REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';

函数

字符串函数

UPDATE emp SET workno=lpad(workno,5,'0')

数值函数

select lpad(floor(1000000*rand()),6,'0')

日期函数

select name,DATEDIFF(CURDATE(),entrydate) days from emp order by days desc

流程函数

select name,case workaddress when '北京' then '一线城市' when '上海' then '一线城市' else '二线城市' end from emp

约束

CREATE Table test(
    id int PRIMARY KEY AUTO_INCREMENT comment 'ID唯一标识',
    name varchar(10) Not Null Unique  comment '姓名',
    age int check ( age>0&&age<=120 )  comment '年龄',
    status char(1) DEFAULT '1' comment '状态',
    gender char(1) comment '性别')comment '用户表';

外键约束

CREATE TABLE 表名(
        字段名 数据类型,
        ...
        [CONSTRAINT] [外键名称] FOREIGN KEY (外键字段名) REFERENCES 主表 (主表列名)
);

ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名) REFERENCS 主表(主表列名);

alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id);

ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

ALTER TABLE 表名 主表名 (主表字段名) ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段) REFERENCES ON UPDATE 行为 ON DELETE 行为;

多表查询

多表关系

• 一对多 在多的一方建立外键，指向一的一方的主键

• 多对多 建立第三张中间表，中间表至少包含两个外键，分别关联两方主键

• 一对一 多用于单表拆分，将一张表的基础字段放在一张表中，把详情字段放在另一张表中
在任意一方加入外键，关联另一方主键

多表查询

• 连接查询
• 内连接：相当于查询A、B交集部分数据
• 外连接：
• 左外连接：查询左表所有数据，以及两张表交集部分数据
• 右外连接：查询右表所有数据，以及两张表交集部分数据
• 自连接：当前表与自身的连接查询，自连接必须使用表别名

• 子查询

内连接

SELECT 字段列表 FROM 表1 , 表2 WHERE 条件 ...;

SELECT 字段列表 FROM 表1 [ INNER ] JOIN 表2 ON 连接条件 ...;

外连接

SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件 ...;

SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件 ...;

自连接

SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 连接条件 ...;

select a.name , b.name from emp a , emp b where a.managerid = b.id;

联合查询

SELECT 字段列表 FROM 表A ...
UNION [ ALL ]
SELECT 字段列表 FROM 表B ...;

子查询

SELECT * FROM t1 WHERE column1= (SELECT column1 FROM t2);

标量子查询

select * from emp where dept_id = (select id from dept where name = '销售部');

select * from emp where entrydate > (select entrydate from emp where name = '方东白');

列子查询

select * from emp where salary > any ( select salary from emp where dept_id = (select id from dept where name = '研发部') );

行子查询

select * from emp where (salary,managerid) = (select salary, managerid from emp where name = '张无忌');

表子查询

select e.*, d.* from (select * from emp where entrydate > '2006-01-01') e left join dept d on e.dept_id = d.id;

事务

控制事务

SELECT @@autocommit;
SET @@autocommit = 0;

COMMIT;

ROLLBACK;

START TRANSACTION 或 BEGIN;

并发事务问题

事务隔离级别

SELECT @@TRANSACTION_ISOLATION;

SET [ SESSION | GLOBAL ] TRANSACTION ISOLATION LEVEL { READ UNCOMMITTED |READ COMMITTED | REPEATABLE READ | SERIALIZABLE }