DML 表查询
针对 表 中的 数据行 进行的增、删、改、查
增 insert into ...
-- 标准 insert into st(id,name,age,gender,address,intime,cardnum,num) values(1,'张三',18,'m','北京','2020-04-27','666666',10); select * from st; -- 部分列录入 insert into st(name,intime,num) values('李四',NOW(),1111); select * from st;
update 更新数据行的值
update st set name='张六' where id=4; select * from st; update st set name='张qi' , age=21 where id=4; select * from st;
delete 删除数据行
delete from st where id=5; select * from st;
伪删除：update 替代 delete ，添加状态列，1带表存在，0代表删除
添加状态列 alter table st add column state tinyint not null default 1 comment '状态列,0是删除,1是存在'; 使用update 替换 delete 原： delete from st where id=4 修改后： update st set state=0 where id=4;
面试题 drop table t1 ,truncate table t1 , delete from t1 区别 ？
# drop table t1; 作用：1. 删除所有表数据，删除整个表段（rm ibd ）,属于物理性质，会释放磁盘空间。 2. 删除表定义 (rm frm , 元数据也会被删除) # truncate table t1 ; 作用：保留表结构，清空表段中的数据页。属于物理删除，会释放磁盘空间。 # delete from t1; 作用: 删除数据行。逐行删除。保留表结构，属于逻辑性质删除。只是标记删除，不会立即释放磁盘空间。 所以delete 操作会产生碎片。
DQL 数据查询语句
select 独立使用（MySQL 独有）
-- 查询数据库服务器配置参数 select @@port; select @@server_id; select @@basedir; select @@datadir; select @@socket; select @@innodb_flush_log_at_trx_commit; -- 替代方法： show variables; show variables like '%trx%';
查询内置函数
select DATABASE(); select NOW(); select USER(); select CONCAT("hello world"); select user,host from mysql.user; select CONCAT("数据库用户:",user,"@",host,plugin,";") from mysql.user;
其他数据库
select NOW() from dual;
select 通用使用方法
select 多子句执行顺序（单表） select 列 from 表 where 条件 group by 列 having 条件 order by 列 limit 条件
生产中熟悉业务：
- comment
- desc ，简单查询表中数据，猜
- E-R关系图
select 配合 from 子句使用
查询表中所有数据（小表） use world; select id,name,countrycode ,district,population from city; 或者 select id,name,countrycode ,district,population from world.city;
select + from + where 子句使用
查询city表中，所有中国的城市信息。 select * from city where countrycode = 'CHN'; 查询人口数小于100人城市信息 select * from city where population<100;
where 配合逻辑连接符（and, or , between and），实现多条件过滤
查询人口数为100w-200w（包括两头）城市信息 select * from city where population >= 1000000 and population <= 2000000 查询中国或美国的城市信息。 select * from city where countrycode='CHN' or countrycode='USA' ; 查询中国或美国，人口数大于500w的城市 select * from world.city where countrycode in ('CHN','USA') and population >= 5000000;
select + from + group by + 聚合函数
--- 聚合函数：count() 统计数量sum() 求和avg() 平均数max() 最大值min() 最小值group_concat() 列转行
- group by 分组功能原理 1. 按照分组条件进行排序 2. 进行分组列的去重复 3. 聚合函数将其他列的结果进行聚合。 --- SQL_MODE=only_full_group 保证group by 语句 准确性 统计中国城市的个数 select COUNT(*) from city where countrycode='CHN'; 统计中国的总人口数。 select SUM(population) from city where countrycode='CHN'; 统计中国每个省的城市个数及城市名列表 mysql> select district, COUNT(name),name -> from city -> where countrycode='CHN' group by district -> ; ERROR 1055 (42000): Expression #3 of SELECT list is not in GROUP BY clause an
报错说明：5.7 之后的SQL对于group by语句的限制。
- 没有在group by 后
- 同时没有在函数中聚合操作违反了SQL_MODE=only_full_group_by
统计中国每个省的城市个数及城市名列表。 select district, COUNT(name),GROUP_CONCAT(name) from city where countrycode='CHN' group by district
having 子句应用
后判断，主要应用在group by之后需要判断。 --- 统计每个国家的总人口数，只显示总人口超过1亿人的信息 select countrycode,SUM(population) from city group by countrycode having SUM(population)>100000000;
order by 语句应用
查询中国所有的城市信息，并按照人口数从大到小排序输出 select * from city where countrycode = 'CHN' order by population desc ; 每个国家的总人口数，总人口超过5000w的信息,并按总人口数从大到小排序输出 select countrycode,SUM(population) from city group by countrycode having SUM(population)>50000000 order by SUM(population) desc ;
limit 分页查询
一般配合order by 使用
查询中国所有的城市信息，并按照人口数从大到小排序输出，只显示前十名。 select * from city where countrycode = 'CHN' order by population desc limit 10 ; 6-10 名 select * from city where countrycode = 'CHN' order by population desc limit 5,5
送个福利
