SQL基础
第1章 SQL介绍
1.1 SQL 标准
1 | (1)结构化查询语言 |
1.2 常用SQL分类
1 | DDL(Data definition language):数据定义语言 |
1.3 怎么查看帮助
1 | mysql> help contents; |
1.4 工具
1 | sqlyog |
1.5 mysql内置的功能
1 | ego (\G) Send command to mysql server, display result vertically. |
第2章 SQL-DDL应用
2.1 DDL - 针对库的操作
2.1.2 建库
1 | CREATE DATABASE guifan CHARSET utf8mb4 ; |
2.1.2 删库(不代表生产操作)
1 | DROP DATABASE oldguo; |
2.1.3 修改库
1 | ALTER DATABASE linux CHARSET utf8mb4; |
2.1.4 查看库(不属于DDL)
1 | SHOW CREATE DATABASE linux; |
2.1.5 总结
1 | CREATE DATABASE |
2.1.6 建库规范
1 | (1) 库名是小写 |
2.2 DDL - 针对表的管理
2.2.1 数据类型
数值类型
1 | tinyint : 1字节,-128~127 ,0-255 |
字符类型
1 | char(M) : |
时间类型
列值不能为空,也是表设计的规范,尽可能将所有的列设置为非空。可以设置默认值为0
unique key :唯一键 列值不能重复
unsigned :无符号 针对数字列,非负数
其他属性:
key : 索引 可以在某列上建立索引,来优化查询
1 | DATETIME |
枚举类型
1 | enum('m','f'):枚举类型 |
二进制类型
2.2.2 约束
1 | primary key 主键 : 非空且唯一,一个表只能有一个主键 |
2.2.3 其他属性
1 | comment : 注释 |
2.2.4 表属性
1 | 存储引擎: |
2.2.5 列属性
1 | 约束(一般建表时添加): |
2.3 DDL - 表定义
建表
1 | CREATE TABLE stu( |
2.4 建表规范总结
1 | (1) 表名要和业务有关 |
— 查看表结构
1 | desc stu; |
— 删除表 (危险!!!)
1 | mysql> drop table user; |
— 修改表结构
1 | (1) 在stu表中添加qq列 |
第3章 DCL
1 | grant |
第4章 DML (表中的数据行)
4.1 INSERT
1 | # 语法:INSERT INTO 表 VALUES(v1,v2,v3,....) |
4.2 UPDATE
1 | UPDATE 表 SET 列=cc WHERE 条件 |
4.3 DELETE
1 | DELETE FROM 表 WHERE 条件 |
4.4 伪删除
1 | 用状态列标记一个行是否存在. |
第5章 DQL应用(select )
select : 查询表中的数据行(记录)
5.1 单独使用(MySQL)
5.1.1 SELECT @@参数名
1 | -- select @@参数名 |
5.1.2 – select 函数();
1 | USE world; |
5.1.3 SELECT 计算功能
1 | mysql> select 3+5; |
5.2 SELECT 配合各种”子句”使用
5.2.1 各子句的执行顺序
1 | SELECT 列1,列2,列3 ,.. |
5.2.2 单表子句-from
1 | world : 世界 |
5.2.3 单表子句-where
SELECT 列…. FROM 表 WHERE 条件;
例子:
1 | --- WHERE 的等值条件查询 |
5.2.4 SELECT 配合 GROUP BY + 聚合函数应用
— 聚合函数(数据统计类的功能)
平均值 AVG()
最大值 MAX()
最小值 MIN()
总和 SUM()
计数 COUNT()
列转行 GROUP_CONCAT()
— GROUP BY
1 | 例子: |
5.2.5 select 配合 having 应用
1 | 例子: |
5.2.6 SELECT 配合 ORDER BY 排序应用
1 | 例子: |
5.2.7 SELECT 配合 LIMIT 使用
- 查看中国所有的城市,并按人口数进行排序(从大到小)
1 | SELECT * FROM city WHERE countrycode='CHN' ORDER BY population DESC; |
- 统计中国各个省的总人口数量,按照总人口从大到小排序
1 | SELECT district AS 省 ,SUM(Population) AS 总人口 |
- 统计中国,每个省的总人口,找出总人口大于500w的,并按总人口从大到小排序,只显示前三名
1 | SELECT district, SUM(population) FROM city |
5.2.8 distinct:去重复
1 | SELECT countrycode FROM city ; |
5.2.9 联合查询- union all
1 | -- 中国或美国城市信息 |
第6章 多表连接查询
6.1 案例准备
按需求创建一下表结构:
1 | use school |
6.2 多表连接应用
1 | 1.查询一下世界上人口数量小于100人的城市名和国家名 |
6.3 别名
1 | --- 表别名 |
第7章 练习
统计zhang3,学习了几门课
1 | mysql> select student.sname,count(sc.cno) |
查询zhang3,学习的课程名称有哪些
1 | mysql> select student.sname,group_concat(course.cname) |
查询oldguo所教课程的平均分数
1 | mysql> select teacher.tname,course.cname,avg(sc.score) |
查询所有老师所教学生不及格的信息
1 | mysql> select teacher.tname , student.sname , course.cname , sc.score |
查询oldguo所教的不及格的学生姓名
1 | mysql> select teacher.tname,student.sname,course.cname,sc.score |
每位老师所教课程的平均分,并按平均分排序
1 | select teacher.tname , course.cname , avg(sc.score) |
查询平均成绩大于60分的同学的学号和平均成绩
1 | select student.sno,avg(sc.score) |
查询所有同学的学号、姓名、选课数、总成绩
1 | SELECT student.sno,student.sname,COUNT(sc.cno),SUM(sc.score) |
查询各科成绩最高和最低的分:以如下形式显示:课程ID,最高分,最低分
1 | SELECT sc.cno,MAX(sc.score),MIN(sc.score) |
统计各位老师,所教课程的及格率
1 | SELECT teacher.tname,course.cname,CONCAT(COUNT(sc.score>60 OR NULL)/COUNT(sc.score)*100,"%") AS 及格率 |
查询每门课程被选修的学生数
1 | SELECT course.cname,COUNT(sc.sno) |
查询出只选修了一门课程的全部学生的学号和姓名
1 | SELECT student.sno,student.sname |
查询选修课程门数超过1门的学生信息
1 | SELECT student.sno,student.sname |
统计每门课程:优秀(85分以上),良好(70-85),一般(60-70),不及格(小于60)的学生列表
1 | SELECT course.cname,GROUP_CONCAT(CASE WHEN sc.score>85 THEN student.sname END) AS 优秀, |
查询平均成绩大于85的所有学生的学号、姓名和平均成绩
1 | SELECT student.sno,student.sname,AVG(sc.score) |
第8章 show
1 | show databases |
第9章 元数据
9.1 元数据介绍
1 | 数据字典信息(表结构信息), |
9.2 元数据控制
1 | "基表" 存储 |
9.3 information_schema 常用视图
1 | mysql> DESC TABLES; |
1.查询world库下所有的表名信息
1 | SELECT table_name FROM information_schema.tables |
2.统计所有库下的表个数
1 | SELECT table_schema,COUNT(table_name) |
3.统计每个数据库下的表的个数,名称
1 | SELECT table_schema,COUNT(table_name),GROUP_CONCAT(table_name) |
4.统计每个数据库(生产库)下的表的个数,名称,要求表名后跟表注释
1 | #提示: NOT IN (sys,information_schema,performance_schema,mysql) |
5.统计全库数据量
1 | TABLE_ROWS #数据行 |
6.统计每个业务库的总大小
1 | SELECT table_schema,SUM(TABLE_ROWS*AVG_ROW_LENGTH+INDEX_LENGTH)/1024/1024 AS total_size_mb |
7.统计下city表的大小
1 | SELECT table_name , (TABLE_ROWS*AVG_ROW_LENGTH+INDEX_LENGTH)/1024/1024 AS mb |
8.拼接:批量生成命令
1 | mysqldump -uroot -p123 world city >/backup/world_city.sql |
columns (数据字典统计)
1 | #统计school数据库下的表的数据字典信息 |