MYSQL专题

MYSQL之旅

MYSQL数据库是每个公司、大小型企业、个人必备的数据存储管理库。MYSQL数据库有利于帮助个人、企业科学地管理处理数据。
数据库地合理科学运用，有助于软件的科学性和高效性。一个合格的程序员必须精通的一门技术。

数据库的常见概念

1、DB：数据库，存储数据的容器
2、DBMS：数据库管理系统，又称为数据库软件或数据库产品，用于创建或管理DB
3、SQL：结构化查询语言，用于和数据库通信的语言，不是某个数据库软件特有的，而是几乎所有的主流数据库软件通用的语言

MySQL服务的启动和停止

方式一：通过命令行 net start 服务名
net stop 服务名
方式二：计算机——右击——管理——服务

MySQL服务的登录和退出

登录：mysql 【-h 主机名 -P 端口号】 -u 用户名 -p密码
mysql -u root -p
退出：exit或ctrl+C

SQL的分类

• DDL(Date Definition Language) 数据定义语言，用来定义数据库对象：库、表、列等；CREATE 、ALTER、DROP
• DML(Data Manipulation Language)数据操作语言，用来定义数据库记录(数据):INSERT、UPDATE、DELETE
• DCL(Data Control Language)数据控制语言，用来定义访问权限和安全级别
• DQL(Data Query Language)数据查询语言，用来查询记录（数据）：SELECT

数据库管理

• 创建数据库，并设置字符集为utf8
  CREATE DATABASE 库名 CHARSET utf8;
• 查看所有数据库
  show databases;
• 查看单个数据库
  show create database 库名;
• 删除数据库
  drop database 库名;
• 为了防止删除不存在的数据库报错
  drop database if exists 库名;
• 选择数据库
  use 库名;
• 导入数据库
  use 库名;
source 路径/文件名.sql;
• 修改mysql root 用户密码

mysqladmin -u root -poldpassword password newpassword

⚠️注意：-poldpassword之间没有空格

数据表管理

• 创建数据表
  字段 id 为主键自增
  字段 name 为字符串类型varchar 并不允许为 null
  字段 description 为可为null 字符串
  字符集为 utf8 ，如果不设置将继承数据库字符集

  create table 表名(
    id int primary key AUTO_INCREMENT,
    name varchar(30) NOT NULL,
    description varchar(100) default NULL) 
    charset utf8;

• 数据表中添加数据
  INSERT INTO class (name,description) VALUES('xxx','xxxx');
• 根据存在的表结构创建新表
  create table 新表名 like 旧表名;
• 复制其他表的数据
  insert into 新表名 select * from 旧表名;
• 只复制选定字段数据
  insert into 新表名(name) select name from 旧表名;
• 复制表时，同时复制数据
  create table 新表名 select * from 旧表名;
• 只复制指定字段，并为不同名字段起别名
  create table 新表名(id int primary key AUTO_INCREMENT,name varchar(30)) select id,cname as name from 旧表名;
• 删除数据表
  DROP TEMPORARY TABLE IF EXISTS 表名;

临时表

临时表是用于储存临时数据表，会在数据库连接中断时自动删除

• 临时表可以与普通表同名，优先级高于普通表
• 连接终端时自动删除
  create TEMPORARY TABLE 临时表名 SELECT * from 旧表名;
select * from 临时表名;
• 删除临时表
  DROP TEMPORARY TABLE IF EXISTS 临时表名;

注意：在实际开发中，我们更喜欢把临时数据放在缓存或会话中，以上只是介绍mysql的特性

实体完整性(主键，自动递增，唯一约束）

• primary key主键约束，特点：数据唯一，且不能为null
• RIMARY KEY(id,name)联合主键，表示id+name不能一样，单个id或name可以相同。
• AUTO_INCREMENT自动递增，给主键添加自动增长的数值，列只能是整数类型，但是如果删除之前增长的序号，后面再添加的时候序号不会重新开始，而是会接着被删除的那一列序号。
• unique唯一约束，代表唯一不能重复。name varchar(20) UNIQUE表示name不能重复。

域完整性

域完整性的作用：限制此单元格的数据正确与否
域完整性约束：数据类型 非空约束（not null） 、默认值约束（default）、Check约束（mysql不支持） 、外键约束（FOREIGN KEY）

• 添加外键约束 FOREIGN KEY
• CONSTRAINT 外键名称 FOREIGN KEY(外键字段) REFERENCES 表名（主键）
  实例：给学生表添加外键约束，使成绩表score与学生对应。

  CREATE TABLE score(
  id int PRTMARY KEY AUTO_INCREMENT,
  score int,
  name varchar(10),
  sid int,
  CONSTRAINT fk_student_score FOREIGN KEY(sid) REFERENCES student(id);
  );

查询语句

• 查询表中数据
  select 查询列表 from 表名;
  1、查询列表可以是字段、常量、表达式、函数，也可以是多个
  2、查询结果是一个虚拟表
  例如：
  1、查询单个字段
  select 字段名 from 表名;
  2、查询多个字段
  select 字段名，字段名 from 表名;
  3、查询所有字段
  select * from 表名
  4、查询常量
  select 常量值;
  注意：字符型和日期型的常量值必须用单引号引起来，数值型不需要
  5、查询函数
  select 函数名(实参列表);
  6、查询表达式
  select 100/1234;
  7、起别名
  ①as
  ②空格
  8、去重
  select distinct 字段名 from 表名;

⚠️注意：distinct不要和多个字段使用，如果使用就无效了
9、+
作用：做加法运算
select 数值+数值; 直接运算 select 字符+数值;先试图将字符转换成数值，如果转换成功，则继续运算；否则转换成0，再做运算 select null+值;结果都为null

10、【补充】concat函数
功能：拼接字符
select concat(字符1，字符2，字符3,...);

11、【补充】ifnull函数
功能：判断某字段或表达式是否为null，如果为null 返回指定的值，否则返回原本的值
select ifnull(commission_pct,0) from employees;

12、【补充】isnull函数
功能：判断某字段或表达式是否为null，如果是，则返回1，否则返回0

• 条件筛选
  1、根据条件查询
  select * from 表名 where name = 'zhangsan';
  2、查询包含关键词的数据
  select * from 表名 where description like '%P%';
  3、合并列返回查询结果
  select CONCAT(id,name) as 'class_info' FROM 表名;
  4、指定多条件查询
  SELECT * FROM 表名 where id>1 and name = 'mysql';
  5、查询一班或姓张的同学
  select * from 表名 where class_id =1 or name like '%张%';
  6、介绍中不包含mysql的班级
  SELECT * from 表名 WHERE description NOT LIKE '%mysql%';
  7、查询学生所在班级编号，并去除重复值
  SELECT DISTINCT class_id from stu;
  8、查询年龄在20～35岁的同学
  select * from stu where age BETWEEN 20 and 35;
  9、查询不在30～35岁间的同学
  select * from stu where age NOT BETWEEN 30 and 35;
  10、查找2、3班的所有同学
  SELECT * FROM stu where class_id IN(2,3);
  11、查找除了1、3班的同学
  select *from stu where class_id NOT IN (1,3);
  12、查询没有分配班级的学生姓名
  select name from stu where class_id is null;
  13、查询已经分配班级的学生信息
  SELECT * from stu where class_id is not null;
  14、查询结果中对没分配班级的学生显示未分配

  select name,if(class_id is null,'未分配',class_id) from stu;
  # 也可以使用IFNULl 函数简化操作
  select name,ifnull (class_id ,'未分配') from stu;

查询SELECT

SELECT select_list /*要查询的列名称*/
FROM table_list /*要查询的表名称*/
WHERE condition /*行条件*/
GROUP BY grouping_columns /*对结果分组*/
HAVING condition /*分组后的行条件*/
ORDER BY sorting_columns /*对结果排序*/
LIMIT offset_start,row_count /*结果限定*/

⚠️注意：顺序是固定的

排序结果

• 按学生年龄从大到小排序
  SELECT * FROM stu order by age desc;
• 班级从大到小排序，相同班级的同学年龄从小到大排序
  select * from stu order by class_id DESC,age ASC;
• 随机获取一名同学
  SELECT * from stu order by RAND() limit 1;
• 最后报名的同名
  select * from stu order by id desc limit 1;
• 第二和第三报名的同学
  SELECT * FROM stu order by id ASC limit 1,2;
• 查找2班年龄最小的同学
  SELECT * from stu where class_id = 2 and age is not null order by age asc limit 1;
  以上代码结果不准确，可能因为有同年龄的同学，所以可以使用子查询操作
  select * from stu where age = (SELECT age from stu where class_id = 2 and age is not null order by age asc limit 1);

修改数据UPDATE

• 将班级为2的学生改为班级为3
  UPDATE stu SET class_id = 3 WHERE class_id = 2;
• 2班年龄小于20岁的同学年龄设置为NULL
  UPDATE stu SET age = null WHERE class_id = 2 AND age < 20;
• 将年龄小于20岁的同学年龄加10岁
  UPDATE stu SET age = age + 10 WHERE age <20;

删除记录 DELETE

• 删除所有年龄小于20的同学
  DELETE FROM stu WHERE age < 20;
• 删除所有年龄小于30并没有班级的同学
  DELETE FROM stu WHERE age < 30 AND class_id IS NULL;
• 删除所有记录

DELETE FROM stu;
或者
truncate table stu;
⚠️注意：
DELETE 删除表中的数据，表结构还在，删除后的数据可以找回。
TRUNCATE 删除是把表直接 DROP 掉，然后再创建一个相同的新表。删除的数据不能找回，执行速度比 DELETE 快。

添加数据 INSERT

• 添加一条记录
  INSERT INTO stu SET name = 'zhangsan',age = 22,class_id = 1;
• 添加多条记录
  INSERT INTO stu (name,class_id,age) VALUES ('zhangsan',1,22),('xiaohong',2,22);

修改表

• 修改表名
  ALTER TABLE stu RENAME stus;
RENAME TABLE stus to stu;
• 修改表字符集
  ALTER TABLE class charset gbk;
• 删除表所有数据
  TRUNCATE stu;
• 删除数据表
  DROP TABLE IF EXISTS stu;

字段管理

• 修改字段类型
  ALTER TABLE stu MODIFY name char(30) not null;
• 修改字段时间同时更改字段名
  ALTER TABLE stu CHANGE name sname varchar(30) not null;
• 添加字段
  ALTER TABLE stu ADD sex SMALLINT default null;
• 在学生名称后添加邮箱字段
  ALTER TABLE stu ADD email varchar(50) AFTER name;
• 将字段添加到最前面
  ALTER TABLE stu ADD qq varchar(30) first;
• 删除学生邮箱字段
  ALTER TABLE stu DROP email;

主键操作

注意：一般主键为自增字段，需要删除自增属性后才可以删除主键
ALTER TABLE stu MODIFY id int not null;

• 删除主键
  ALTER TABLE stu DROP PRIMARY key;
• 添加表主键
  ALTER table stu2 add PRIMARY KEY(id);
• 添加自增列
  ALTER TABLE stu2 MODIFY id int not null AUTO_INCREMENT;
• 主键与自增列一起添加
  ALTER table stu3 modify id int not null AUTO_INCREMENT ,add PRIMARY key(id);

字符串数据类型

• 下面是mysql支持的字符串类型
  
  char类型是定长类型，比如定义了20长度的char类型，只存一个字符也占20个长度，好处是处理速度快，缺点是空间占用大，把手机号、邮箱、密码等设置为char类型是不错的选择。
  varchar类型与char类型相反，占用空间受内容影响，可以把文章标题、介绍等设置为varchar类型更合适。

  字符集

• 字符串分二进制与非二进制类型，二进制用于储存图片、声音等文件，非二进制用于储存文本数据。
• 非二进制文本受字符集和校对规则影响。
• 字符集(Character set)是多个字符的集合，字符集种类较多，每个字符集包含的字符个数不同。常用的字符集有GBK、BIG5、UTF8.
• UTF8字符包含文字内容更广，如韩文、日文、德文兼容度更高，也是推荐使用的字符集。
• 查看服务器支持的字符集
  SHOW CHARACTER SET;

表不设置字符集继承数据库，字段不设置字符集继承表的字符集

校对规则

是在字符集内用于字符比较和排序的一套规则，以_ci结束的为大小写不敏感、_bin结束的为不区分大小写。

• 查看系统支持的校对规则
  show COLLATION;
• 当使用不区分大小写的校对规则时A与a是相同的，否则则不相同，这会影响到排序与比对。
• 当使用utf8_bin校对规则时，下面的查询将匹配不到大写的PHP
select * from class WHERE name = 'php';

注意：修改表校对规则，对表的原字段将不影响，只对新增字段影响。

常见函数

• 大小写转换
  SELECT UPPER(name) as name,LOWER(description) as 'desc' from class;
• Left&right
  left与right函数用于取左或右指定数量的字符，下面是取班级介绍前8个字符并用...连接。
  SELECT CONCAT(LEFT(description,8),'...') FROM class;
• mid
  从中间取字符串，参数二为起始，参数三为取的字符数量。下面是获取从第二个字符取两个字符值为hp的。
  select * from class where mid(name,2,2) = 'hp';
• substring
  从指定位置开始向右所有字符串，下面是获取从第二个位置开始的字符值为hp的记录。
  select * from class where SUBSTRING(name,2) = 'hp';
• char_length
  获取字符串长度
• concat
  连接字符串使用
  SELECT concat('编号：',id) as id,concat('班级：',cname) as name FROM class;
• 将所有班级前加上尖子班
UPDATE class SET name = CONCAT('尖子班:',name);
• 截取班级介绍，超过8个字符的后面连接...
SELECT if(CHAR_LENGTH(description)>8,CONCAT(LEFT(description,8),'...'),LEFT(description,8)) as name FROM class;

正则表达式

mysql支持正则表达式操作，可用于处理复杂的匹配操作。

• 查找第二个字符为h的字符串
  SELECT * FROM class WHERE name REGEXP '^.h';
• 查找班级名称中含有php或mysql的记录
  SELECT * FROM class WHERE name REGEXP 'php|mysql';
• 所有介绍中包含PHP与mysql的课程名前加上精品
update class set cname = REPLACE(cname,cname,concat('精品',cname)) where description REGEXP 'php|mysql';

LIKE

在LIKE表达式中%用于匹配任意多个字符，_用于匹配一个字符。
查找第二个字符为h的班级
SELECT * FROM class WHERE name LIKE '_h%';

整型数据类型

• 下面是mysql支持的整型类型
  
• 取值范围如果加了unsigned，则最大值翻倍，如tinyint unsigned的取值范围为(0~256)。
• m的含义不是允许字段的长度，而是显示长度，在为字段设置zerofill时有效。
• 添加有前导零的字段
  ALTER TABLE class ADD stu_count smallint(6) ZEROFILL default null;

浮点数数据类型

• 下面是mysql支持的浮点数类型
  
  下面是检测浮点数精度的示例
  alter table class add e FLOAT(10,2);
update class set e = 12345678.66 where id=11;
  查看结果时会发布浮点数结果不准确。
• float：2^23 = 8388608,一共七位，这意味着最多能有7位有效数字，但绝对能保证的为6位，即float的精度为6～7位有效数字
• double：2^52 = 4503599627370496,一共16位，double的精度为15～16位
• 浮点型在数据库中存放的是近似值，而定点类型在数据库中存放的是精确值
• decimal(m,d)m<65是总个数，d<30且d < m是小数位
• 对货币等对精确度敏感的数据，应该用定点数decimal存储

ENUM/SET

ENUM

• ENUM 类型因为只允许在集合中取得一个值，有点类似于单选项。在处理相互排斥的数据时容易让人理解，例如人类的性别。换个枚举最大可以有65535个成员值
  ALTER TABLE stu ADD sex ENUM('男','女') DEFAULT NULL;
• 可以使用索引或值添加enum数据
  INSERT INTO stu (name,class_id,sex) VALUES('zhangsan',1,'男');
INSERT INTO stu (name,class_id,sex) VALUES('lisi',1,2);
• 可以使用值与索引检索ENUM
  SELECT * from stu WHERE sex = '女';
SELECT * from stu WHERE sex = 2;

SET

• SET类型与ENUM类型相似但不相同。SET类型可以从预定义的集合中取得任意数量的值。一个SET类型最多可以包含64项元素。
• 使用SET类型添加文章属性字段
  ALTER TABLE article ADD flag SET('推荐'.'置顶'.'图文'.'热门');
• 添加数据
  INSERT INTO article (title,status,flag) VALUES ('技术博客',1,'图文，推荐，置顶');
• 使用find_in_set查找数据
  SELECT * FROM article WHERE find_in_set('图文',flag);
• 使用like查找数据
  SELECT * FROM article WHERE flag like '%置顶%';
• 二进制比较
  可以使用二进制方式对SET类型进行模糊筛选。
  
• 获取包含图文与推荐的文章
  SELECT * FROM article WHERE & 5;

日期时间数据类型

• 下面是mysql支持的日期时间数据类型
  
• mysql保存日期格式使用YYYY-MM-DD HH:MM:SS的ISO 8601标准
• 向数据表储存日期与时间必须使用ISO格式
• 创建字段
  ALTER TABLE stu ADD birthday datetime default null;
• 格式化
  参数介绍
  

实例操作

• 使用Date_format格式化日期与时间显示
  select name,DATE_FORMAT(birthday,'%Y年%m月%d %H时%i分%s秒') as birthday from stu;
• 使用time_format格式化输出时间
  select name,TIME_FORMAT(birthday,'%r') as birthday from stu;

时间戳

以时间戳格式来记录日期与时间

• 设置TIMESTAMP字段

  alter table stu add updated_at
  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;
  insert into stu set name = 'zhangsan',updated_at = '2020-4-19 10:33:33';

• 添加数据时自动更新时间

  alter table stu add updated_at
  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

• 当执行添加与更新字段时将自动为当前时间

  insert into stu set name = 'zhangsan';
  update stu set name = 'ih' where id = 7;

执行更新或添加都会改变timestamp字段

常见函数

• 获取当前日期、时间的示例
  select CURRENT_DATE,CURRENT_TIME,NOW();
• 获取时间的部分值
  select YEAR(updated_at),MONTH(updated_at),DAY(updated_at) from stu;
• 其他可以使用的函数如下
  
• 拆分日期时间

  select name,YEAR(birthday),MONTH(birthday),DAY(birthday),HOUR(birthday),MINUTE(birthday),SECOND(birthday) from stu;

• 当前日期时间
  SELECT now(),CURDATE(),CURRENT_DATE(),CURRENT_TIME(),NOW();
• 时间计算
  SELECT DAYOFYEAR(now()),DAYOFMONTH(now()),DAYOFWEEK(now()),WEEKDAY(now());
• 秒转换
  不包含日期的秒转换

  SET @time = time(now());
  SELECT now(),TIME_TO_SEC(@time),SEC_TO_TIME(TIME_TO_SEC(@time));

• 日期时间与秒转换
  SELECT now(),UNIX_TIMESTAMP(birthday),FROM_UNIXTIME(UNIX_TIMESTAMP(birthday)) FROM stu;
• 天转换
  SELECT now(),TO_DAYS(birthday),FROM_DAYS(TO_DAYS(birthday)) FROM stu;
• 差值计算
  计算天数差值，忽略时间部分
  SELECT now(),DATEDIFF(now(),birthday) from stu;
  计算时间差值，忽略天数
  SELECT now(),TIMEDIFF(time(birthday),time(now())) from stu;
• 指定单位差值
  支持的单位有YEAR/MONTH/WEEK/DAY/HOUR/MINUTE/SECOND等。下面是获取学生来到人生经历的天数。
  select name,TIMESTAMPDIFF(day,birthday,NOW()) from stu;

基本查询

• 查找在1990～1999年出生的同学
  SELECT * FROM stu WHERE birthday BETWEEN '1990-01-01' AND '1999-12-31';
  或者
  SELECT * FROM stu WHERE birthday >= '1990-01-01' AND birthday <= '1999-12-31';
• 获取年龄最大的同学

  SELECT * FROM stu ORDER BY birthday ASC LIMIT 1;
  # 更准确的查询
  SELECT * FROM stu
  WHERE birthday = (SELECT birthday FROM stu ORDER BY birthday ASC LIMIT 1);

• 查询在1班或2班1990年出生的同学
  SELECT * FROM stu WHERE class_id IN(1,2) AND year(birthday) = 1999;
• 90后最喜欢上的班级

  SELECT count(id) as total ,class_id from stu
  WHERE LEFT(birthday,4) >= 1990 AND YEAR(birthday) <= 2000
  GROUP BY class_id
  ORDER BY total DESC
  LIMIT 1;

• 大于20岁的女生最多的班级

  SELECT count(id),class_id FROM stu
  WHERE TIMESTAMPDIFF(YEAR,birthday,now())>20 AND sex=2
  GROUP BY class_id
  ORDER BY count(id) DESC
  LIMIT 1;

• 查询id为1、2、3的记录 IN

SELECT * from stu WHERE id IN(1,2,3);

• 查询id不为1、2、3的记录 IN

SELECT * from stu WHERE id NOT IN(1,2,3);

• 查询sname为null的记录

SELECT * from stu WHERE sname IS NULL;

时间计算

• 七小时前的时间
  select ADDTIME(now(),'-7:00:00');
• 七天后的日期
  SELECT DATE_ADD(now(),INTERVAL 7 DAY);
• 20小时10分钟后的日期
  SELECT DATE_ADD(NOW(),INTERVAL '20:10' HOUR_MINUTE);
• 2天8小时后的日期
  SELECT DATE_ADD(NOW(),INTERVAL '2 8' DAY_HOUR);
• 获取本月最后一天日期
  SELECT LAST_DAY(now());
• 获取本月的第一天日期
  SELECT DATE_SUB(now(),INTERVAL DAYOFMONTH(now())-1 DAY);
• 获取本月发表的文章

  SELECT * FROM article
  WHERE created_at >= DATE_SUB(now(),INTERVAL DAYOFMONTH(now())-1 DAY)
  AND created_at <= LAST_DAY(now());

注意：因为使用大量函数会造成性能下降，所以推荐在PHP中算出时间后比对

• 获取三个月内发表的文章

  SELECT * FROM article
  WHERE publish_time >= DATE_SUB(now(),INTERVAL -3 MONTH);
  # 如果从前上个月的一号开始获取
  SELECT * from article
  WHERE publish_time >= DATE_FORMAT(DATE_SUB(now(),INTERVAL 3 MONTH),'%Y-%m-01');

• 获取上个月的最后一天
  SELECT LAST_DAY(DATE_SUB(NOW(),INTERVAL 1 MONTH));
  或直接通过明确日期
  SELECT * FROM article WHERE publish_time >= '2019-01-01' AND publish_time < CURDATE();
• 下个月的第一天
  SELECT DATE_ADD(LAST_DAY(NOW()),INTERVAL 1 DAY);
• 查看出生超过20年的同学
  SELECT * FROM stu WHERE birthday < DATE_SUB(NOW(),INTERVAL 20 YEAR);
• 本周二的日期
  SELECT now(),DATE_ADD(NOW(),INTERVAL 3-DAYOFWEEK(NOW()) DAY);
• 查看三周前的周二
  SELECT now(),DATE_SUB(DATE_ADD(NOW(),INTERVAL 3-DAYOFWEEK(NOW()) DAY),INTERVAL 21 DAY);
• 上周一的日期

  SET @week = DATE_SUB(NOW(),interval 1 week);
  SELECT DATE_ADD(@week,INTERVAL 0-WEEKDAY(@week) day);

• 获取本月迟到的同学
  select * from attendance WHERE created_at >= date_sub(NOW(),INTERVAL DAYOFMONTH(now())-1 DAY) AND time(created_at)>'08:30:00';
• 本月迟到超过2次的同学

  select stu_id from attendance
  WHERE created_at >= date_sub(NOW(),INTERVAL DAYOFMONTH(now())-1 DAY)
  AND time(created_at) > '08:30:00'
  GROUP BY stu_id
  HAVING count(id)>=2;

• 本周周一的日期
  SELECT DATE_ADD(now(),INTERVAL 0-WEEKDAY(now()) day);
• 获取本周迟到的学生编号

  set @begin = DATE_FORMAT(date_add(now(),INTERVAL 0-WEEKDAY(now()) day),'%Y-%m-%d');
  select stu_id from attendance
  WHERE created_at >= @begin
  AND time (create_at) > '08:00:00'
  GROUP BY stu_id;

• 获取上周打卡记录

  set @week = DATE_SUB(now(),INTERVAL 1 WEEK);
  SELECT stu_id FROM attendance
  WHERE created_at >= DATE_ADD(@week,interval 0-WEEKDAY(@week) day)
  AND created_at <= DATE_ADD(@week,interval 4-WEEKDAY(@week) day);

• 获取本周发表的文章
  SELECT * FROM article WHERE created_at >= DATE_ADD(now(),INTERVAL 0-WEEKDAY(now()) day);
• 周日来校实习的同学
  SELECT * from attendance WHERE date(created_at) = date(date_add(now(),INTERVAL 6 - WEEKDAY(now()) day));

摘要与排序

排序介绍 ORDER BY

mysql对查询结果使用order by进行排序

• 对任何字段进行排序
• desc降序 asc升序
• 对别名字段可排序
• 对函数结果可排序
• 支持多列表排序
• 排序受校对规则影响

HAVING 字句

⚠️注意：

• having是在分组后对数据进行过滤，where是在分组前对数据进行过滤。
• having后面可以使用聚合函数（统计函数），where后面不可以使用聚合函数
• where是对分组前记录的条件，如果某行记录没有满足where子句的条件，那么这行记录不会参加分组；而having是对分组后数据的约束。

排序实例

• 从男到女排序
  SELECT * FROM stu ORDER BY sex ASC;
• 从男到女排序年龄从小到大排序
  SELECT * FROM stu ORDER BY sex ASC,birthday DESC;
• 随机获取学生
  SELECT * FROM stu ORDER BY RAND() LIMIT 1;
• 按出生月份从小到大排序

  SELECT birthday,MONTH(birthday) as m FROM stu ORDER BY m ASC;
  或使用字符串函数操作
  SELECT birthday,mid(birthday,6,2) as m FROM stu ORDER BY m ASC;

• 多个数据排序(age升序排列相同时，id降序排列)

SELECT id,name,age FROM stu OREDR BY age ASC,id DESC;

自定义排序

• field函数用于比较值在集合中的索引，利用这一特性可以自定义排序
  SELECT * FROM stu ORDER BY FIELD(left(name,1),'何'，‘赵’);

COUNT

• 统计所有学生人数
  SELECT COUNT(*) FROM stu;
• 所有女生人数
  SELECT COUNT(*) FROM stu WHERE sex=2;
• 统计所有分配班级的学生（count(字段)不会统计null值，使用count(*)时会计算null），所以下面使用具体的字段
  SELECT COUNT(class_id) FROM stu;

MIN/MAX

• 获取最小的学生出生年份
  SELECT year(min(birthday)) from stu;
• 最大的班级编号
  SELECT max(class_id) FROM stu;
• 获取点击数最少的文章
  SELECT * FROM article WHERE click = (SELECT MIN(click) FROM article);

SUM/AVG

• 获取所有文章总点击数
  SELECT SUM(click) FROM article;
• 获取平均点击数
  SELECT AVG(click) FROM article;
• 获取低于平均点击数据的文章
  SELECT * FROM article WHERE click < (SELECT AVG(click) FROM article);
• 获取学生的平均年龄
  SELECT ROUND(AVG(TIMESTAMPDIFF(YEAR,birthday,now()))) FROM stu;

DISTINCT去重

distinct用于去除结果集中的重复记录

• 获取所有班级编号
  SELECT DISTINCT class_id AS class FROM stu WHERE class_id IS NOT NULl;
• 获取学生数，同班同名的算一个
  SELECT COUNT(DISTINCT class_id,name) FROM stu WHERE class_id IS NOT NULL;

GROUP

• 统计每个班级的人数
  SELECT COUNT(*),class_id FROM stu WHERE class_id IS NOT NULL GROUP BY class_id;
• 每个班年龄最大的同学
  执行下面查询会报错，因为mysql要求select中的列要在group中使用。
  可以通过更改查询模式，允许select的列不在group中出现
  SELECT sql_mode = (SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
  然后查询就可以得到结果
  SELECT min(birthday),name FROM stu GROUP BY class_id;
  也可以使用子查询
  SELECT * FROM stu where birthday IN(SELECT min(birthday) FROM stu GROUP BY class_id);
• 统计每班的男、女人数
  SELECT concat(class_id,'班')，if(sex=1,'男','女') as xes count(*) FROM stu WHERE class_id IS NOT NULL GROUP BY class_id,sex ORDER BY class_id;
• 查找超过两个同学的班级
  SELECT class_id FROM stu GROUP BY class_id HAVING count(*)>2;
• 查找本周迟到超过两次的同学
  SELECT stu_id FROM attendance WHERE date(created_at)>date(DATE_ADD(NOW(),INTERVAL 0-WEEKDAY(NOW()) day)) AND time(created_at)>'08:30:00' GROUP BY stu_id HAVING COUNT(*)>2;
• 本周哪个同学准时到校次数最多
  SELECT count(*) as c,stu_id FROM attendance WHERE date(created_at)>=date(date_add(now(),interval 0-WEEKDAY(now()) day)) AND time(created_at)<='08:30:00' GROUP BY stu_id ORDER by c desc limit 1;
• 本周哪一天迟到的人数最少
  SELECT date(created_at) FROM attendance WHERE date(created_at) > date(DATE_ADD(NOW(),INTERVAL 0-WEEKDAY(NOW()) day)) AND time(created_at)<='08:30:00' GROUP BY created_at ORDER BY COUNT(*) DESC LIMIT 1;
• 查找哪个姓的同学最多
  SELECT left(name,1) as s,count(*) AS c FROM stu GROUP BY s ORDER BY c DESC LIMIT 1;
• 查找超过两个同学的姓氏
  SELECT left(name,1) as s,count(*) AS c FROM stu GROUP BY s HAVING c>=2;

LIMIT方言

LIMIT用来限定查询结果的起始行，以及总行数。是MYSQL独有的，oracle和SQL Server没有LIMIT这种用法。
⚠️实际应用中：如果页数p=1～10，开始的位置就=(p-1)*每页的条数

• 查询5行记录，起始行从0开始

SELECT * FROM stu LIMIT 0,5; 0表示从第0个记录开始，5表示显示5条数据。

• 查询10行记录，起始行从3开始

SELECT * FROM stu LIMIT 3,10;

多表攻略

学习了sql语句的操作，但是我们大部分都是操作的单张表，大部分问题只有一张表是不能解决的。我们需要从多张表中获取数据，或者通过其他表的数据删除每个表的记录等操作，这个时候就需要多表操作。

表关联

• 一对一
  比如说会员表与个人资料（QQ,邮箱）表即为一对一关系。
• 一对多
  比如学生与班级表间即为一对多关系，一个班级有多个学生，一个学生属于一个班级。
• 多对多
  粉丝表与用户表关系，一个粉丝可以关注多个用户，一个用户也可以有多个粉丝，像这样关系我们会使用一张中间表来记录关系。

多表查询

• 合并结果集： UNION、UNION ALL
• 连接查询
• • 内连接： [INNER] JOIN ON
• • 外连接：OUTER JOIN ON
• • 左外连接：LEFT [OUTER] JOIN
• • 右外连接：RIGHT [OUTER] JOIN
• • 全外连接：(MYSQL不支持) FULLJOIN
• • 自然连接：NATURALJOIN

笛卡尔积

多个表的连接将会得到所有可能出现的行，即没有明确做两个表间的关联条件时，所有记录都将符合
SELECT * FROM stu,class;

• 下面是添加条件后的结果(笛卡尔积过滤)
  SELECT * FROM stu,class WHERE stu.class_id = class.id;

INNER

所有多表操作都可以简单理解为，把多个表联系成一个表，最终思想成面上当成一个表对待

INNER JOIN

• 获取用户的资料信息
  SELECT * FROM stu AS s INNER JOIN user_info as i ON s.id = i.stu_id;
• 使用INNER JOIN使用多表关联的语义更清晰
  SELECT * FROM stu INNER JOIN class ON stu.class_id = class.id;
• 查询一班的所有同学
  SELECT * FROM stu INNER JOIN class ON stu.class_id = class.id WHERE class.id = 1;
• 为了性能和多表字段重名覆盖的问题，有必要在查询时明确获取的列
  SELECT name,class_id,stu.id as stu_id,sex,name FROM stu INNER JOIN class ON stu.class_id = class.id WHERE class.id = 1;
• 每个班级发表的文章数量，来操作多张表的关联操作
  SELECT c.id,count(*) FROM stu as s INNER JOIN class as c INNER JOIN article as a ON s.class_id = c.id AND s.id = a.stu_id GROUP BY c.id;
• 大学所有女生发表的文章
  SELECT c.id,a.title FROM stu as s INNER JOIN class as c INNER JOIN article as a ON s.class_id = c.id AND s.id = a.stu_id WHERE c.id = 1 AND s.sex = '女';
• 每个班级发表的文章数
  SELECT count(a.id) as article_sum,c.id FROM stu as s INNER JOIN class as c INNER JOIN article as a ON s.class_id = c.id AND s.id = a.stu_id GROUP BY c.id;
• 哪个班级发表的文章超过两篇
  SELECT c.id,count(*) as total FROM stu as s INNER JOIN class as c INNER JOIN article as a ON s.class_id = c.id AND s.id = a.stu_id GROUP BY c.id HAVING total >=2;
• 每个班级文章的总点击数与平均点击数
  SELECT sum(a.click) as class_sum,avg(a.click) ,c.id FROM stu as s INNER JOIN class as c INNER JOIN article as a ON s.class_id = c.id AND s.id = a.stu_id GROUP BY c.id ORDER BY class_sum DESC;
• 每个班级有多少同学
  SELECT count(*),c.name FROM stu as s INNER JOIN class as c GROUP BY c.name;
• 学生数大于两个的班级名称
  SELECT count(*) as total,name FROM stu as s INNER JOIN class as c ON s.class_id = c.id GROUP BY c.name HAVING total>=2;

OUTER JOIN

外链接包括LEFT JOIN与RIGHT JOIN,可以简单理解为LEFT JOIN会包含左侧所有表记录，RIGHT JOIN会包含右侧表全部记录。

• 获取没有设置QQ的用户
  SELECT s.sname FROM stu AS s LEFT JOIN user_info as i ON s.id = i.stu_id WHERE i.qq is null;
• 查找所有没有发表文章的同学
  SELECT s.id,s.name FROM stu as s LEFT JOIN article as a NO s.id = a.stu_id WHERE a.id IS NULL;
• 哪个班级没有学生
  SELECT sname,c.id,c.name FROM stu AS s RIGHT JOIN class as c ON s.class_id = c.id WHERE s.id IS NULL;
• 每个班级的平均年龄
  SELECT c.cname,avg(timestampdiff(year,s.birthday,now())) as t FROM stu as s INNER JOIN class as c ON s.class_id = c.id GROUP BY c.cname;
• 查找学生所在班级，没有班级的学生显示无
  SELECT sname,ifnull(s.class_id,'无') FROM stu AS s LEFT JOIN class AS c ON s.class_id = c.id;

SELF JOIN

SELF JOIN为自连接即表与自身进行关联。虽然自连接的两张表都是同一张表，但也把它按两张表对待，这样理解就会容易些。

• 查找zhangsan的同班同学
  使用子查询操作
  SELECT * FROM stu WHERE class_id = (SELECT class_id FROM stu WHERE sname = 'zhangsan') AND stu.sname != 'zhangsan';
  使用自连接查询
  SELECT s1.sname,s2.sname FROM stu as s1 INNER JOIN stu as s2 ON s1.class_id = s2.class_id WHERE s1.sname = 'zhangsan' AND s2.sname != 'zhangsan';
• 查找与zhangsan同年出生的同学
  SELECT s2.* FROM stu as s1 INNER JOIN stu AS s2 ON year(s1.birthday) = year(s2.birthday) WHERE s1.sname = 'zhangsan' AND s2.sname != 'zhangsan';
• 查找比zhangsan大的同学
  SELECT s2.sname,s2.birthday FROM stu AS s1 INNER JOIN stu AS s2 ON year(s1.birthday)>year(s2.birthday) WHERE s1.sname = 'zhangsan';

多对多

比如学生可以学习多个课程，一个课程也可以被多个学生学习，这种情况就是多对多的关系。需要创建一张中间表来把这种关系联系起来。

• 查找zhangsan学习的课程
  SELECT sname,l.name FROM stu AS s INNER JOIN user_lesson AS ul ON s.id = ul.stu_id INNER JOIN lesson AS l ON ul.lesson_id = l.id WHERE s.sname = 'zhangsan';
• 哪个班级的同学最爱学习PHP
  SELECT c.cname,count(*) AS total FROM stu AS s INNER JOIN user_lesson AS ul INNER JOIN lesson AS l ON s.id = ul.stu_id AND ul.lesson_id = l.id INNER JOIN class AS c ON c.id = s.class_id WHERE l.name = 'PHP' GROUP BY c.cname ORDER by total LIMIT 1;

UNION

UNION用于连接多个查询结果，要保证每个查询返回的列数与顺序要一样

• UNION会过滤重复的结果

• UNION ALL 不过滤重复结果

• 列表字段是第一个查询的字段

• 查询年龄最大与最小的同学

  (SELECT sname,birthday FROM stu ORDER BY birthday DESC LIMIT 1)
  UNION
  (SELECT sname,birthday from stu ORDER BY birthday ASC LIMIT 1)
  ORDER BY birthday DESC;

• 最新发表的文章和学习的课程组成动态数据

  (SELECT CONCAT(s.sname,'发表了文章：',a.title) from article as a
  INNER JOIN stu as s
  ON s.id =  a.stu_id
  LIMIT 2)
  UNION
  (SELECT CONCAT(s.sname,'正在学习：',l.name) FROM stu AS s
  INNER JOIN user_lesson as ul
  INNER JOIN lesson as l
  ON s.id = ul.stu_id AND ul.lesson_id = l.id
  LIMIT 2);

多表删除 IN

• 删除所有没有学习任何课程的同学
  DELETE s FROM stu as s LEFT JOIN user_lesson as ul ON s.id = ul.stu_id WHERE ul.lesson_id IS NULL;

• 使用子查询操作

    DELETE FROM stu WHERE id IN(
    SELECT id FROM 
    (SELECT s.id FROM stu as s
        LEFT JOIN user_lesson as ul
        ON s.id = ul.stu_id
        WHERE ul.lesson_id IS NULL)
        AS s
    );