聊聊数据库~SQL基础篇 (5)

课后拓展：

SQLServer突破内存限制： https://www.cnblogs.com/zkweb/p/6137423.html 官方demo： https://www.microsoft.com/en-us/sql-server/developer-get-started/python/ubuntu 官方文档： https://docs.microsoft.com/zh-cn/sql/linux/sql-server-linux-overview?view=sql-server-2017 PS：SQL Server默认端口为TCP 1433 3.3.区别

简单列举下上面的区别（欢迎补充）:

MySQL自增长是auto_increment，MSSQL是identity

MySQL可以设置无符号unsigned，MSSQL不可以直接设置无符号整型，需要通过约束之类的来限制

alter table的时候，MSSQL没有[first | after 列名]，而且语法差别也挺大

4.增删改查（CURD） 4.1.MySQL

select语句执行流程：

from 表

[inner|left|right] join 表 on 条件

where 条件

对select的结果进行过滤

group by 字段

根据指定条件把查询结果进行分组，以用做聚合运算

having 条件

对分组聚合运算(group by)后的结果进行过滤

order by 字段 [asc|desc]

根据指定字段对查询结果进行排序（默认升序asc）

select 字段

limit [偏移量,]显示数量

显示多少条数据 | 分页显示

增删改 -- 4.1.插入 help insert -- 自增长主键和默认值的字段可以不写 insert into dotnetcrazy.users(username, password, email, tel, usercode, createtime, updatetime, datastatus) values ('dnt', '7c4a8d09ca3762af61e59520943dc26494f8941b', 'dnt@qq.com', '18738002038', uuid(), now(), now(), 1); -- 批量插入 insert into dotnetcrazy.users(username, password, email, tel, usercode, createtime, updatetime, datastatus) values('xxx', '7c4a8d09ca3762af61e59520943dc26494f8942b', 'xxx@qq.com', '13738002038', uuid(), now(), now(), 0),('mmd', '7c4a8d09ca3762af61e59520943dc26494f8941b', 'mmd@qq.com', '13718002038', uuid(), now(), now(), 1),('小张', '7c4a8d09ca3762af61e59520943dc26494f8941b', 'zhang@qq.com', '13728002038', uuid(), now(), now(), 1); -- 4.2.修改 help update update dotnetcrazy.users set datastatus=99, updatetime = now() where username = 'mmd'; -- 一定要有where条件！开发中一般都是先写where条件再写update -- 4.3.删除 -- 删除数据（自增长不重置）help delete; delete from dotnetcrazy.users where datastatus = 0; -- 删除全部数据（自增长重置）help truncate; truncate table dotnetcrazy.users; 查询 -- 数据构造见附录 -- 4.4.查询 help select -- 查询来源url（去重后） select distinct url from file_records; -- 查询来源url（分组方式） select url from file_records group by url; -- 分别统计一下url出现的次数（分组+聚合） -- 分组一般都和聚合函数一起使用 select url, count(*) as count from file_records group by url; -- 分别统计一下url出现的次数，已经删除的文件不算进去 select url, count(*) as count from file_records group by url having count > 3; -- 在group by的结果上筛选 -- 分别统计一下url出现的次数并查出对应的id select group_concat(id) as ids, url from file_records group by url; -- 内连接查询 innet join tb_name on 关联条件 select file_records.id, users.id as uid, users.username, users.email, file_records.file_name, file_records.md5, inet_ntoa(file_records.ip) as ip, file_records.url from users inner join file_records on file_records.user_id = users.id -- 连接条件 where users.datastatus = 1 and file_records.datastatus = 1 order by file_records.file_name desc; -- 文件名降序排序 -- MySQL没有`select top n`语法，可以使用 limit来实现，eg：top 5 select * from file_records limit 5; -- limit 0,5 -- 分页查询 -- page:1,count=5 ==> 0,5 ==> (1-1)*5,5 -- page:2,count=5 ==> 5,5 ==> (2-1)*5,5 -- page:3,count=5 ==> 10,5 ==> (3-1)*5,5 -- 推理：limit (page-1)*count,count select file_records.id, users.id as uid, users.username, users.email, file_records.file_name, file_records.md5, inet_ntoa(file_records.ip) as ip, file_records.url from file_records inner join users on file_records.user_id = users.id limit 0,5; -- limit后面跟表达式就会报错 select file_records.id, users.id as uid, users.username, users.email, file_records.file_name, file_records.md5, inet_ntoa(file_records.ip) as ip, file_records.url from file_records inner join users on file_records.user_id = users.id limit 5,5; -- limit (2-1)*5,5; -- limit错误写法 -- limit要放在最后 select file_records.id, users.id as uid, users.username, users.email, file_records.file_name, file_records.md5, inet_ntoa(file_records.ip) as ip, file_records.url from file_records inner join users on file_records.user_id = users.id order by username desc, file_name desc limit 10,5; -- 先order by排完序，然后再取第三页的5个数据 -- 查找一下从来没上传过文件的用户 -- right join：以右边表（users）为基准连接 select file_records.id as fid, users.id as uid, users.username, users.email, file_records.file_name, file_records.md5, inet_ntoa(file_records.ip) as ip, file_records.url from file_records right join users on file_records.user_id = users.id where users.datastatus = 1 and file_records.id is null order by username desc, file_name desc; -- 自连接案例： -- 二级联动 p：province，c：city，a：area -- 前端一般都会显示省级信息，用户选择后可以获得对应的二三级信息 select c.name, a.name from city_infos as c inner join city_infos as a on a.pcode = c.code where c.pcode = '320000'; -- pcode设置为索引 -- 通过省名称查询 select p.name, c.name, a.name from city_infos as c inner join city_infos as p on c.pcode = p.code inner join city_infos as a on a.pcode = c.code where p.name = '江苏省'; 视图 -- 简单提一下视图： -- 创建视图 create view view_userinfo as select id, username, password, email, tel, datastatus from dotnetcrazy.users; -- 查询视图 select id, username, password, email, tel, datastatus from dotnetcrazy.view_userinfo; -- 删除视图 drop view if exists view_userinfo;

附录：