MySQL select查询 高阶

--- 关于库定义规范 *****
--- 1.库名使用小写字符
--- 2.库名不能以数字开头
--- 3.不能是数据库内部的关键字
--- 4.必须设置字符集.

--- 建表规范 *****
--- 1. 表名小写字母,不能数字开头,
--- 2. 不能是保留字符,使用和业务有关的表名
--- 3. 选择合适的数据类型及长度
--- 4. 每个列设置 NOT NULL + DEFAULT .对于数据0填充,对于字符使用有效字符串填充
--- 5. 每个列设置注释
--- 6. 表必须设置存储引擎和字符集
--- 7. 主键列尽量是无关列数字列,最好是自增长
--- 8. enum类型不要保存数字,只能是字符串类型

--- 常用数据类型

整型: tinyint(m) 1个字节 范围(-128~127)
int(m) 4个字节 范围(-2147483648~2147483647)

浮点型: float(m,d) 单精度浮点型 8位精度(4字节) m总个数，d小数位
double(m,d) 双精度浮点型 16位精度(8字节) m总个数，d小数位

字符串: CHAR 0-255 bytes 定长字符串
VARCHAR 0-65535 bytes 变长字符串
TEXT 0-65535 bytes 长文本数据(不需要指定长度)

1、 char长度固定， 即每条数据占用等长字节空间,不足的用空格补充；适合用在身份证号码、手机号码等定。
2、 varchar可变长度，可以设置最大长度；适合用在长度可变的属性。
3、 text不设置长度， 当不知道属性的最大长度时，适合用text。
查询速度快慢：CHAR < VARCHAR < TEXT

时间: datetime 日期时间 '2008-12-2 22:06:44'
timestamp 自动存储记录修改时间

enum 枚举数据类型 address enum('sz','sh','bj'.....)
1 2 3
以上数据类型可能会影响到索引的性能

--- 建表
表名,列名,列属性,表属性
--- 列属性
PRIMARY KEY : 主键约束,表中只能有一个,非空且唯一.
NOT NULL : 非空约束,不允许空值
UNIQUE KEY : 唯一键约束,不允许重复值
DEFAULT : 默认值，一般配合 NOT NULL 一起使用.
UNSIGNED : 无符号,一般是配合数字列,非负数
COMMENT : 注释
AUTO_INCREMENT : 自增长的列

?

1 2 3 4 5 6 7

CREATE TABLE stu ( id INT PRIMARY KEY NOT NULL AUTO_INCREMENT COMMENT '学号', sname VARCHAR(255) NOT NULL COMMENT '姓名', age TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '年龄', sex ENUM('boy','girl','n') NOT NULL DEFAULT 'n' COMMENT '性别', intime DATETIME NOT NULL DEFAULT NOW() COMMENT '入学时间' )ENGINE INNODB CHARSET utf8mb4;

　　

--- 创建一个表结构一样的表
CREATE TABLE test LIKE stu;

--- 在stu表中添加qq列 *****

?

1 2	DESC stu; ALTER TABLE stu ADD qq VARCHAR(20) NOT NULL COMMENT 'qq号';

--- 在sname后加微信列 ***

?

1	ALTER TABLE stu ADD wechat VARCHAR(64) NOT NULL UNIQUE COMMENT '微信号' AFTER sname;

--- 把刚才添加的列都删掉(危险,不代表生产操作) ***

?

1 2 3 4

ALTER TABLE stu DROP num; DESC stu; ALTER TABLE stu DROP qq; ALTER TABLE stu DROP wechat;

　　

插入数据
mysql> insert into stu(sname,age,sex) values('zs',18,1),('ls',17,1),('xh',16,2);
查看数据
mysql> select * from stu;
修改数据
mysql> update stu set age=17 where id=1;
删除数据（生产线上都是逻辑删除）
mysql> delete from stu where id=1;

--- select 通用语法(单表) ***** 逻辑顺序
select 列
from 表
where 条件 条件
group by 条件 分组 1 count:行数 2 sum:总和(求总和) 3 avg:平均值 4 max 最大值 5 min : 最小值
having 条件 条件
order by 条件 排序
limit ； LIMIT M,N :跳过M行,显示一共N行

-- where等值条件查询 *****
查询学生年龄等于18岁的
mysql> select sname,age from stu where age=18;

-- where 配合比较判断查询(> < >= <=) *****
查询学生年龄大于等于18的
mysql> select sname,age from stu where age>=18;

-- where 配合 逻辑连接符(and or)
查询学生年龄小于18的女生
mysql> select sname,age from stu where age<18 and sex=2;
查询学生年龄18或者16的学生
mysql> select sname,age from stu where age=18 or age=16;

-- where 配合 like 子句 模糊查询 *****
查询学生姓名开头是l的
mysql> select sname,age from stu where sname like 'l%';

-- where 配合 in 语句
查询学生年龄在17,18的
mysql> select sname,age from stu where age in (17,18);

--- GROUP BY
统计每个国家,城市的个数

?

1 2	SELECT countrycode ,COUNT(id) FROM city GROUP BY countrycode;

　　

--- select 配合 having 应用
统计所有国家的总人口数量、将总人口数大于一亿的过滤出来 having执行结果后条件，where 执行前条件

?

1 2 3

SELECT countrycode,SUM(population) FROM city GROUP BY countrycode HAVING SUM(population)>100000000

　　

--- SELECT 配合 ORDER BY 子句
1. 统计所有国家的总人口数量,
将总人口数大于5000w的过滤出来,
并且按照从大到小顺序排列

?

1 2 3 4

SELECT countrycode,SUM(population) FROM city GROUP BY countrycode HAVING SUM(population)>50000000 ORDER BY SUM(population) DESC ;

　　

---SELECT 配合 LIMIT 子句
统计所有国家的总人口数量,将总人口数大于5000w的过滤出来,并且按照从大到小顺序排列,只显示前三名

?

1 2 3 4 5

SELECT countrycode,SUM(population) FROM city GROUP BY countrycode HAVING SUM(population)>50000000 ORDER BY SUM(population) DESC LIMIT 3 OFFSET 0;

　　

例子： 统计中国,每个省的总人口,找出总人口大于500w的,并按总人口从大到小排序,只显示前三名

?

1 2 3 4 5 6

SELECT district ,SUM(population) FROM city WHERE countrycode='CHN' GROUP BY district HAVING SUM(population)>5000000 ORDER BY SUM(population) DESC LIMIT 3;

　　

小扩展

批量生成sql语句

SELECT
CONCAT('ALTER TABLE ', table_schema, '.', table_name, ' MODIFY id BIGINT NOT NULL AUTO_INCREMENT;')
FROM information_schema.TABLES t
where table_schema=databases_name and table_name=table_name

concat 做列值拼接
mysql> select concat(user,"@",host) from mysql.user;
显示如：  root@localhost 

group_concat 列转行聚合函数
distinct 可以排除重复值

--- union 和 union all 作用: 多个结果集合并查询的功能
union all 不做去重复
union 会做去重操作

需求: 查询中国或者美国的城市信息

?

1 2 3 4 5 6

SELECT * FROM city WHERE countrycode='CHN' OR countrycode='USA';   #改写为: SELECT * FROM city WHERE countrycode='CHN' UNION ALL SELECT * FROM city WHERE countrycode='USA';

　　

--- 多表连接例子 join on

在Join时用小表驱动大表，提高查询效率
例: user表10000条数据，class表20条数据
select * from user u left join class c u.userid=c.userid
这样则需要用user表循环10000次才能查询出来，而如果用class表驱动user表则只需要循环20次就能查询出来。

别名： 列别名可以被 having 和 order by 调用， ----表别名是全局调用的.

?

1 2 3 4 5 6 7 8 9 10

SELECT t.tname ,GROUP_CONCAT(CONCAT(st.sname,":",sc.score)) FROM teacher as t JOIN course as c ON t.tno=c.tno JOIN sc ON c.cno=sc.cno JOIN student as st ON sc.sno=st.sno WHERE sc.score<60 GROUP BY t.tno