MySQL语法，增改删查、多表查询

 

sql语句的5种类型

SQL 全称 Structured Query Language 结构化查询语言，是操作关系型数据库的通用语言、标准语句。各数据库厂商在标准sql的基础上进行扩展，增加了一些特有的sql语法，比如mysql的 limit、oracle的 top。
 

sql语句的5种类型

• 数据查询语言（Data Query Language，DQL）：即用于查询数据的sql语句，主要为select，包含from、where、group by、having、order by等字句。
• 数据定义语言（Data Definition Language，DDL）：即用于定义数据库、数据表、索引、视图等结构的sql语句，主要包括create、alter、drop。
• 数据 操纵 | 操作 | 处理 语言（Data Manipulation Language，DML）：用于操纵、操作、处理数据（记录）的sql语句，主要包括 insert、update、delete。
• 数据控制语言（Data Control Language，DCL）：用来设置数据操作权限、监控数据库运行状态等，主要包括 grant、revoke。
• 事务控制语言（Transaction Control Language，TCL）：用于控制事务，主要包括 commit、rollback、savepoint。

上线时提供的sql脚本，一般只包含DDL、DML。

 

mysql说明

• mysql关键字不区分大小写，字符串不区分单双引
• 字段名不加引号，as可以使用中文字段别名，如果与sql关键字冲突，加反引号。
• 字符串、日期时间类型的值要引起来

--注释

/*注释*/


--查看mysql版本
select version();  

 

数据库常用操作

--查看所有的数据库
show databases;  

--操作某个数据库之前要先用use指定要使用数据库
use db_student;

--删除指定的数据库
drop database db_student;  

 

数据表常用操作

--查看当前数据库中的表
SHOW tables;

-- 查看表结构（字段定义）
DESC t_user;

 

create table 创建数据表

CREATE TABLE [IF NOT EXISTS] `t_user` (
  --自增，从1开始，第一个值是1
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '用户id，自增主键',
  --非空约束
  `username` varchar(255) NOT NULL COMMENT '用户名',
  --未指定值时自动使用默认值。未指定是指insert、update中未设置此字段，如果显式设置了此字段的值为null，eg. udpate t_user set tel=null 并不会应用默认值，会直接报错
  `tel` varchar(11) NOT NULL DEFAULT '' COMMENT '手机号',
  `address` varchar(255) DEFAULT NULL COMMENT '地址',
  `birthday` datetime DEFAULT NULL COMMENT '生日',
  --CURRENT_TIMESTAMP 预定义值，当前时间 
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  --ON UPDATE 更新时自动set此字段的值
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  --指定主键
  PRIMARY KEY (`id`),
  --加唯一索引，如果insert、update时该列出现重复的值，会直接报错
  UNIQUE KEY `uk_username` (`username`),
  --加普通索引
  KEY `idx_tel` (`tel`)
) ENGINE=InnoDB AUTO_INCREMENT=1431 DEFAULT CHARSET=utf8mb3;

 

alter table 修改表定义

add新增，drop删除，modify修改字段属性，change修改字段名，alter修改默认值

-- 添加字段
ALTER TABLE `t_user`
	-- first表示位置在第一列
    ADD COLUMN `id` BIGINT NOT NULL COMMENT '主键,雪花算法生成' FIRST,
    -- after在指定字段之后
    ADD COLUMN `gender` TINYINT NOT NULL DEFAULT '0' COMMENT '性别：0男性,1女性,2其它' AFTER `username`,
    ADD COLUMN `expire_time` DATETIME NOT NULL DEFAULT '2099-01-01 23:59:59' COMMENT '过期时间' AFTER `gender`,

-- 删除字段
ALTER TABLE t_user
	DROP COLUMN `age`;


-- 修改字段定义，字段类型、默认值、注释、前后顺序 均可以用此方式修改
ALTER TABLE t_user
    MODIFY COLUMN `username` varchar(50) NOT NULL COMMENT '用户名';

-- 重命名字段，依次指定旧字段名、新字段名，新旧字段名相同时，作用和MODIFY相同
ALTER TABLE `t_user`
    CHANGE `status` `user_status` TINYINT NOT NULL DEFAULT '0' COMMENT '0已启用,1已禁用,2已注销';


-- 修改字段默认值。如果已有默认值，需要先删除默认值再重新设置，如果不存在默认值则可以直接设置
-- 删除默认值
ALTER TABLE `t_user`
	ALTER COLUMN gender DROP DEFAULT; 
-- 设置默认值
ALTER TABLE `t_user`
	ALTER COLUMN gender SET DEFAULT '0';


-- 修改字符集
ALTER TABLE t_user CHARACTER SET utf8mb4;

 

alter可以同时操作多个字段

ALTER TABLE `t_user`
    ADD COLUMN `gender` TINYINT NOT NULL DEFAULT '0' COMMENT '性别：0男性,1女性,2其它' AFTER `username`,
    ADD COLUMN `expire_time` DATETIME NOT NULL DEFAULT '2099-01-01 23:59:59' COMMENT '过期时间' AFTER `gender`,
    DROP COLUMN `age`,
    MODIFY COLUMN `username` varchar(50) NOT NULL COMMENT '用户名',
    CHANGE `status` `user_status` TINYINT NOT NULL DEFAULT '0' COMMENT '0已启用,1已禁用,2已注销';

 

3种删除方式的区别

--删除表
DROP TABLE t_user;
DROP TABLE [IF EXISTS] `t_user`;

--截断表
TRUNCATE TABLE t_user;

--删除表中的记录，如果不用where指定条件，会删除表中所有的记录
DELETE FROM t_user; 

• drop会删除记录、表定义（表结构），truncate、delete都只删除记录，仍保留表定义。
• truncate、delete只能对table使用，drop可以对table、view使用，delete可以只删除部分行（where筛选）。
• truncate、drop是一次性删除表的所有内容、对应的索引，不是作为事务执行，不能回滚，虽然可以恢复数据，但很麻烦；delete会作为事务执行，可以回滚。

记录数很多时，执行速度一般是 drop > truncate > delete

 

重置自增主键从1开始

--方式一 清空记录后修改表定义
delete table t_xxx; 
alter table t_xxx AUTO_INCREMENT=1; 

--方式二 截断表 （推荐）
truncate table t_xxx;

truncate 截断，也可以在navicat中对表单击右键 -> 截断表，相当于清空记录 + 重置自增主键位置；清空表只是清空记录，不会重置自增主键位置。

 

记录的增改删查

-- 列之间可以进行运算
select id,username,tel from t_user;
select * from t_user;
select price * number from t_order_item where id=1; 


-- value只能插入一条记录，values可以插入一条或多条记录，多条记录之间逗号分隔，插入所有字段时可缺省字段名
insert into t_user (username, tel) value ('chy', '188xxxxx'); 
insert into t_user (username, tel) values ('chy1, '188xxxx'), ('chy2', '187xxxxx');
insert into t_user values ('chy', '188xxxxx');

-- insert ignore into、replace into 用法与 insert into 相同，只是前两者会根据 unique key 检查记录是否已存在、重复，不存在才插入，
-- 已存在：insert ignore into 会跳过，replace into 会先删除原记录后再插入，二者均会记录重复的记录数，常用于数据同步
insert ignore into t_user (username, tel) value ('chy', '188xxxxx');
replace into t_user (username, tel) value ('chy', '188xxxxx');


-- 可以直接在原值的基础上修改
update t_user set username='chy', age=20 where id=1;
update t_user set age=age+2 where id=1;


delete from t_user where id=1

 

跨库、跨表复制数据

插入可以根据需要选择 insert into、insert ignore into、replace into，搭配 select 子句使用

insert ignore into d_user.t_user (username, tel, email)
    select username, tel, email from d_user_old.t_user;

-- 复制全部字段时可以缺省列名
insert ignore into d_user.t_user
    select * from d_user_old.t_user;

-- 同一个库可以缺省库名
insert ignore into t_user
    select * from t_user_old;

 

where子句

-- 标准sql中不等于用<>，mysql也可以用!=
where id = 1
where id <> 1
where id != 1


-- 范围查询
where id between 1 and 10  -- [1,10]，闭区间
where id in (1, 3, 9)
where id not in (1, 3, 9)


where id < all(1, 3, 9)  -- id要小于集合中的任何一个元素
where id < all(select id from t_user where age < 20)  -- 一般和子查询搭配使用

where id < any(1, 3, 9)  #id小于集合中的任意一个元素即可，id可以是[1,8]。一般和子查询搭配使用

-- 与、或、非（not），优先级 not > and > or，可以通过加()明确优先级
where score > 60 and score < 90
where score < 60 or score > 90



-- 判断是否为null，不能用=、!=、<>来判断
where score is null
where score is not null


-- 模糊查询，常用正则表达式如下
-- % 匹配0个或多个字符，_ 匹配任意一个字符
-- [] 匹配其中的任意一个字符，类似于sql的in，示例：[0123456789]匹配0~9中的任意一个数字
-- [!] 匹配除此之外的任意一个字符，类似于sql的not in，示例：[!0123456789] 只要不是0~9中的任意一个就ok
[^abcd]  -- !也可以写成^
where name like '计算机%'
where name like '%计算机'
where name like '%计算机%'
where name not like '计算机%'

 

查询常用

别名

-- as 结果集字段别名，返回时用别名替换字段名
select user_id as '用户id', username as '用户姓名', sum(recharge_amount) as '充值总额' from tb_recharge_order group by user_id;

-- 表别名，常用于多表关联查询
select tu.username from tb_user tu;

 

distinct 去重

distinct 修饰的是select选择的所有字段，如果结果集中有相同的记录，则相同的记录只保留一条

-- 单个字段
select distinct tel from t_user;
-- 多个字段
select distinct tel,username from t_user;

-- 一个字段时可以写成函数形式
select distinct(tel)  from t_user;  

 

order by 结果集排序

-- 默认asc升序
select * from t_user order by age;
-- desc降序
select * from t_user order by age desc;

-- 可以使用多个排序字段，前面的关键字字段值相同时，使用后面的关键字排序
select * from t_user order by name,age desc;

 

group by 结果集分组

-- 把dep相同的记录分为一组，返回的是分好的组，不是记录
select * from t_student group by dep;


-- group by常和聚合函数搭配使用，用于统计各个组的情况，select选取的其它字段通常也是group by分组的字段
select user_id, username, sum(recharge_amount) from t_recharge_order group by user_id;

-- 聚合函数最终值返回一条记录，如果不使用group by，则聚合函数统计的是整个结果集，sum()是确定的，但不确定最终会返回哪条记录的user_id、username
select user_id, username, sum(recharge_amount) from t_recharge_order;


-- 统计每个系的学生人数。单独使用group by意义不大，一般搭配聚合函数使用，统计每组的数据
select count(*） from t_student group by dep;

-- 统计每个班的平均分
select avg(score） from t_student group by class;


-- group by会自动根据分组字段排序，无需再用order by排序。默认asc升序，也可以显式指定排序方式
select avg(score） from t_student group by class desc;

 

having 分组过滤

having子句，一般和group by搭配使用，用来对分组进行过滤

select * from t_student [where子句] group by class having class between 1 and 10; #查询[1,10]班的学生信息
-- group by后面不能使用where，且where子句中不能直接使用聚合函数，having用于弥补where的不足
-- group by以哪个字段进行分组，having就只能以哪个字段进行过滤，把不符合要求的组去掉

 

limit 结果集分页

-- 从第n条记录开始取，取m条返回，[n,n+m)
select * from t_user [where子句] [order by子句] limit n,m;

-- 只有一个参数时，默认从0开始取，相当于limit 0,m
select * from t_user [where子句] [order by子句] limit m;

注意：这里的n是结果集中的记录下标，从0开始，并不是自增主键id的值。

 

多个子句的书写、执行顺序

select ... from ... [where子句]  [group by子句]  [having子句]  [order by子句]  [limit子句]

执行顺序

• 先执行select … from … [where子句] ，将匹配的记录放到结果集中
• 再group by对结果集中的记录进行分组
• having对分组进行过滤，去掉不满足条件的分组
• orderby排序
• limit只取出结果集的某个区间，返回给客户端
   

多个子句常见的组合方式

select ...  from ...  [where子句]  [group by子句]  [having子句]
select ...  from ...  [where子句]  [order by子句]  [limit子句]

 

多表查询

union 结果集合并

如果多个select分别查表，select取的字段数、字段名、字段类型相同，可以使用union把多个结果集合并为一个结果集。
一个select返回m条记录，另一个select返回n条记录，合并之后就是m+n条记录

-- 查询全校师生的id、name

-- union默认会去重，如果合并得到的结果集中有重复的记录，只保留一条，distinct可以缺省
select id，name from t_student union [distinct] select id,name from tb_teacher;  

-- all，保留全部记录，不去重
select id，name from t_student union all select id,name from tb_teacher;   

-- 如果要合并多个结果集，两两之间union连接
select语句1 union [distinct|all] select语句2 union [distinct|all] select语句3 ...

 

子查询

-- 在where中使用子查询
-- eg.有2张表，tb_dep 一条记录即一个系的信息，tb_student 一条记录即一个学生的信息，查询计算系所有学生的学号、姓名
select id,name from t_student where dep_id=(select id from tb_dep where dep_name='计算机系'); 



-- 在having中使用子查询
-- eg. 查询大于平均分的学生成绩
select * from t_score group by id having score>(select avg(score) from tb_score)；  
-- group by 主键，按主键分组，一个分组就是一条记录，对分组的过滤就是对记录的过滤


-- where、having的过滤中有2个常用关键字：any、all
where xxx > any(子查询)  #大于任一个即可
where xxx > all(子查询)  #大于所有才行



-- 在from中使用子查询
select 列名 from (select子查询);  -- 把子查询的结果集作为一张临时表
select 列名 from （select子查询）[别名] [子句]； -- 临时表的使用方式和表名一样。在5.7版本中，临时表必须要有表别名

 

join 连接查询

笛卡尔积

2个集合相乘，得到的是笛卡尔积

{A,B,C} * {a,b,c} = { (A,a)，(A,b)，(A,c)，(B,a)，(B,b)，(B,c)，(C,a)，(C,b)，(C,c) } 

即用一个集合的每个元素去乘另一个集合的每个元素

 

sql99 连接查询语法

select  表1中的列, 表2中的列  from

表1 [别名]  [连接类型] join  表2 [别名] on 连接条件

[where子句]

[group by子句]

[having子句]

[order by子句]

[limit子句]

sql 99的连接查询带有关键字join，称之为显式写法。在sql 99之前还有一个sql语法版本：sql 92，顾名思义，92年制定的标准。sql 92的连接查询不带join，也具有相同的功能，现在还能用，称之为隐式写法。

 

根据连接类型可以把连接查询分为4类

• 交叉连接
• 内连接：又分为等值连接、非等值连接、自连接
• 外连接：又分为左外连接（左连接）、右外连接（右连接）、全外连接（全连接，mysql不支持，oracle支持）
• 自然连接
   

假设tb_student有30条记录，tb_score有20条记录（10人因缺考、缓考等原因暂时没有成绩）

-- 交叉连接，得到的是笛卡尔积，基本不用
select st.*,sc.* from t_student st cross join tb_score sc;
select * from t_student cross join tb_score;  -- 可以用一个*表示多表的所有字段
-- 30*20=600，返回600条记录




-- 内连接，以少的为准（2张表中都要有）
select * from t_student st [inner] join t_score sc on st.id=sc.student_id;  -- join默认就是内连接，inner可以缺省
-- 只返回20条记录，成绩为空的10人不要了

-- on指定2张表的连接条件（关联关系），使用on对笛卡尔积中的记录进行筛选。如果是单张表的条件，比如 st.id>10，写在where中

-- 等值连接：on中使用等号进行筛选、判断
-- 非等值连接：on中使用>、>=、<、<=、between and...连接2张表（不使用等号）



-- 内连接的隐式写法
select * from t_student st,t_score sc where st.id=sc.student_id;  -- 在where中指定连接条件



-- 内连接之自连接
-- 顾名思义，自己连接自己进行查询。有时候表是关联到自身的，需要连接自身来查询
-- eg. 员工表，员工和直属上司都是员工，都在一张表里。查询员工信息及其直属上司的姓名，superior_id是直属上司的id
select te1.*，te2.name as superior_name from t_employees te1 [inner] join t_employees te2 where te1.superior_id=te2.id;
-- 都是同一张表，取不同的别名来区分




-- 外连接
-- 内连接以少的为准（作为主表），外连接可以指定以哪张表为准（作为主表），更灵活
select * from t_student st left [outer] join t_score sc on st.id=sc.student_id;
-- outer可以省略不写，left是左外连接，right是右外连接，full是全外连接（mysql不支持，oracle支持）

-- 左外连接以join左边的表为准（主表），右外连接以join右边的表为准，全连接是2张表的记录都保留（在另一张表中找不到对应|关联的记录都保留）。




-- 自然连接
-- 自然连接可以看做是一种特殊的等值连接，会自动把2张表中相同的列（列名、数据类型都要相同）作为连接条件
select * from t_student natural join t_score;
-- 比如两张表都有id字段，会自动添加连接条件 on 表1.id=表2.id




-- 多张表，写多个join就行
select 表1的字段,表2的字段,表3的字段 from 表1 [别名]
left join 表2 [别名] on 表1.id=表2.id
left join 表3 [别名] on 表2.id=表3.id

左连接、右连接的左右是以join为标椎定义的，左连接以【left】 join左边的表为主表，右连接以【right】join右边的表为主表。

 

常用函数

聚合函数

聚合函数用于对结果集进行统计、分析，在聚合函数中，值是null的记录不参与统计
 

count() 统计记录数

-- 统计满足条件的记录数。mysql从5.7.18开始对count(*)做了算法优化，性能很高；count(1)会遍历所有记录，不取值，按行累计
select count(*) from t_user where...;
select count(1) from t_user where...;


-- count(字段) 统计某一列值不为null记录数。会遍历所有记录，取指定字段值，判断是否为null，不为null才累计
select count(id) from t_user where...; 
select count(username) from t_user;


-- 去重统计，重复的记录只算做一条。会把值加载到内存进行去重统计，适合中小数据量
select count(distinct username) from t_user;
-- 也可以使用 子查询 + group by 实现，适合大数据量
select count(*) from (select 1 from t_user group by username) t_user;

不同存储引擎对 count(*) 的优化不同

• MyISAM：把一个表的总记录数保存在硬盘上，执行count(*)时直接返回总记录数，效率极高；
• InnoDB：支持事务，因为多版本并发控制（MVCC）的原因，InnoDB同一时刻在不同事务中count()返回的记录数可能是不同的，所以没有像MyISAM一样把总记录数保存起来，而是在需要 count() 的各个事务中各自遍历所有记录累计行数。

数据量比较大（几十万、几百万、几千万、上亿）时，执行速度通常是：count(*) ≈ count(1) > count(主键) > count(普通字段)

 

select max(age) from t_user;
select min(age) from t_user;


-- 查询指定用户的充值总金额
select user_id, sum(recharge_amount) from t_recharge_order where user_id=xxx;
-- 查询平均分
select avg(score） from t_student;


-- 该列的第一个|最后一个值
select first(username) from t_user;  
select last(username) from t_user;  

 

if()、ifnull()

-- 相当于三元运算符，as不能省略
select if(score >= 60, '合格', '不合格') as result from t_student;

--为null时指定默认值
select ifnull(amount, 0) as money from t_user;

注意：不同类型的数据库，判空函数可能有区别

 

日期时间

-- curdate()当前日期，curtime()时间，now()日期时间
select curdate(), curtime(), now();

-- date() 获取日期时间的日期部分
select date(create_time) from t_user

-- extract() 获取日期时间指定部分的值，可参考：https://www.w3school.com.cn/sql/func_extract.asp
select 
	extract(year from create_time) as create_year,
	extract(month from create_time) as create_month,
	extract(day from create_time) as create_day
from t_user


-- date_add()、date_sub() 在指定日期时间上加减指定时间间隔
-- 可参考：https://www.w3school.com.cn/sql/func_date_add.asp
select date_add(create_time, interval 2 month) as add_result from t_user
select date_sub(create_time, interval 2 day) as sub_result from t_user

--计算2个日期时间相差的天数，会直接舍弃时间部分，以第一个的日期减第二个的日期
select datediff('2020-10-10', '2020-10-09') as diff_day


-- date_format() 日期时间格式化，注意：%m月，%i分（第二个字母），%s、%S都是秒，
-- 可参考：https://www.w3school.com.cn/sql/func_date_format.asp
select date_format(now(),'%Y-%m-%d %H:%i:%s') as format_result
-- 按月统计交易金额
select date_format(finish_time,'%Y-%m') as month, sum(trade_amount) as amount from t_trade_order group by month

注意：不同类型的数据库，日期时间函数可能有区别

 

转换、格式

-- 转换为全大写、全小写
select ucase(username) from t_user;
select lcase(username) from t_user;


-- 四舍五入，第二个参数指定保留的小数位数
select round(amount, 2) from t_user
-- 格式化数值，效果同round
select format(amount, 2) from t_user

 

说明

别名

w3c的标准：表名、列名使用别名时都要加as
实际：表名、列名起别名时都可以不加as；有些数据库，eg. oracle，给表起别名时不能加as。

建议：给表起别名不加as，给列起别名可加可不加
 

多个条件

where中可以有多个and、or，同时存在and、or时，and的优先级高于or
 

空值判断、等值判断

等于、不等于用 =、<>，mysql等部分数据库也可以!=表示不等于；

判断是否为null要用 is null、is not null，不能用=、!=、<>
 

使用会话变量

可以用set定义会话变量，变量名以@开头，定义的会话变量只对当前sql客户端有效，且只在本次会话中有效。

SET @user_id = 1;
SELECT * FROM t_user_recharge WHERE user_id = @user_id;
SELECT * FROM t_user_recharge_invalid WHERE user_id = @user_id;