/*
基础查询练习：
1.字段列表查询
当查询全部字段时，一种简便方式，使用*代替全部字段（企业中不推荐使用） 2.去除重复行
DISTINCT，注意修饰的是行，也就是整个字段列表，而不是单个字段。
DISTINCT的位置在字段列表前。 3.计算字段
字段运算，函数处理。 bug点： null值参与计算，得到的值是null
方案:IFNULL(含null值表达式, null值的代替值)函数处理。 4.别名
AS关键字
*/
DROP TABLE IF EXISTS student;
CREATE TABLE student(
id INT PRIMARY KEY, -- 编号
name VARCHAR(20), -- 姓名
age INT, -- 年龄
sex VARCHAR(5), -- 性别
address VARCHAR(100), -- 地址
math INT, -- 数学
english INT -- 英语
);
INSERT INTO student(id, name, age, sex, address, math, english)
VALUES
(1,'马云',55,'男','杭州',66,78),
(2,'马化腾',45,'女','深圳',98,87),
(3,'马景涛',55,'男','香港',56,77),
(4,'柳岩',20,'女','湖南',76,65),
(5,'柳青',20,'男','湖南',76,NULL),
(6,'刘德华',57,'男','香港',99,99),
(7,'马德',22,'女','香港',99,99),
(8,'德玛西亚',18,'男','南京',56,65); -- 1.字段列表查询
SELECT name, math, english FROM student; -- 简便方式，*表示全部字段，进行查询
SELECT * FROM student; -- 规范写法，并且企业中规定禁止用SELECT *，效率低。
SELECT
name, -- 姓名
math, -- 数学
english -- 英语
FROM
student; -- 学生表 -- 2.去除重复行，DISTINCT修饰的是字段列表，不是单个字段。
SELECT DISTINCT address from student; SELECT name, DISTINCT address from student;-- 错误语句 -- 3.计算字段
SELECT name, math, english, math + english FROM student;-- bug: NULL值，参与计算，得到的结果是NULL。不符合要求。 SELECT name, math, english, IFNULL(math, 0) + IFNULL(english, 0) FROM student;-- 调用函数，对NULL进行处理 -- 4.起别名
SELECT name, math, english, IFNULL(math, 0) + IFNULL(english, 0) AS total_score FROM student;-- 对计算字段起别名 -- 5.bug
DROP TABLE IF EXISTS student;
CREATE TABLE student(
id INT PRIMARY KEY, -- 编号
name VARCHAR(20), -- 姓名
age INT, -- 年龄
sex VARCHAR(5), -- 性别
address VARCHAR(100), -- 地址
math INT, -- 数学
english INT -- 英语
);
INSERT INTO student(id,name,age,sex,address,math,english) VALUES (1,'马云',55,'男','
杭州',66,78),(2,'马化腾',45,'女','深圳',98,87),(3,'马景涛',55,'男','香港',56,77),(4,'柳岩
',20,'女','湖南',76,65),(5,'柳青',20,'男','湖南',86,NULL),(6,'刘德华',57,'男','香港
',99,99),(7,'马德',22,'女','香港',99,99),(8,'德玛西亚',18,'男','南京',56,65); -- bug1: NULL值，参与计算，得到的结果是NULL。不符合要求。
SELECT name, math, english, math + english FROM student; -- bug2: 在字符串中间换行，导致字符串中会额外插入一个换行符。
SELECT DISTINCT address from student;-- bug演示：发现有2个香港，没有去重，其实其中1个有个额外的换行符。
基础查询练习
/*
基础条件查询:
全部的比较运算符参看MySQL手册第12章运算符和函数
1.关系运算符
> < >= <= = !=
<>
2.逻辑运算符
AND OR NOT
&& || ! 不推荐
3.便捷运算符
BETWEEN AND, IN
NOT BETWEEN AND, NOT IN
4.null值判断
IS NULL
IS NOT NULL
5.模糊查询
LIKE
NOT LIKE
%(匹配n个任意字符)
_(匹配1个任意字符)
*/
DROP TABLE IF EXISTS student;
CREATE TABLE student(
id INT PRIMARY KEY, -- 编号
NAME VARCHAR(20), -- 姓名
age INT, -- 年龄
sex VARCHAR(5), -- 性别
address VARCHAR(100), -- 地址
math INT, -- 数学
english INT -- 英语
);
INSERT INTO student(id, NAME, age, sex, address, math, english)
VALUES
(1,'马云',55,'男','杭州',66,78),
(2,'马化腾',45,'女','深圳',98,87),
(3,'马景涛',55,'男','香港',56,77),
(4,'柳岩',20,'女','湖南',76,65),
(5,'柳青',20,'男','湖南',76,NULL),
(6,'刘德华',57,'男','香港',99,99),
(7,'马德',22,'女','香港',99,99),
(8,'德玛西亚',18,'男','南京',56,65); SELECT * FROM student; -- 查询年龄大于20岁
SELECT * FROM student WHERE age > 20;
-- 查询年龄大于等于20岁
SELECT * FROM student WHERE age >= 20; -- 查询年龄等于20岁
SELECT * FROM student WHERE age = 20;
-- 查询年龄不等于20岁
SELECT * FROM student WHERE age != 20;
SELECT * FROM student WHERE age <> 20; -- 查询年龄在20岁到30岁的人
SELECT * FROM student WHERE age >= 20 AND age <= 30;
SELECT * FROM student WHERE age BETWEEN 20 AND 30;
-- 查询年龄不在20岁到30岁的人
SELECT * FROM student WHERE age NOT BETWEEN 20 AND 30; -- 查询年龄为18岁，20岁，22岁的人
SELECT * FROM student WHERE age = 18 OR age = 20 OR age = 22;
SELECT * FROM student WHERE age IN(18, 20, 22);
-- 查询年龄不为18岁，20岁，22岁的人
SELECT * FROM student WHERE age NOT IN(18, 20, 22); -- 查询英语成绩为null的人，即没有参加英语考试的人
SELECT * FROM student WHERE english = NULL; -- 错误 不能用 = !=判断null值
SELECT * FROM student WHERE english IS NULL; -- 查询英语成绩不为null的人，即参加了英语考试的人
SELECT * FROM student WHERE english IS NOT NULL; -- 查询姓马的人
SELECT * FROM student WHERE NAME LIKE '马%';
-- 查询不姓马的人
SELECT * FROM student WHERE NAME NOT LIKE '马%'; -- 查询名字中含德的人
SELECT * FROM student WHERE NAME LIKE '%德%'; -- 查询名字中第2个字是化的人
SELECT * FROM student WHERE NAME LIKE '_化%';
-- 查询名字为3个字的人
SELECT * FROM student WHERE NAME LIKE '___';
基础条件查询练习
/*
分组聚合排序分页查询：
WHERE和HAVING的区别？
1.where在分组前过滤数据，不满足条件的，不参与分组。having在分组后过滤数据。
2.where中不能使用聚合函数，having中可以使用聚合函数。 1.分组
1.语法：
[GROUP BY {col_name | expr | position}[ASC | DESC], ... [WITH ROLLUP]]
2.过滤分组：
[HAVING where_condition]
分组后过滤数据。可以使用聚合函数。
3.注意点：
1.分组后查询目标：分组字段，聚合函数，
2.提供了ASC和DESC，这是提供的ORDER BY的一种简便方式。 2.聚合
5个聚合函数，排除null值。不对null值进行统计。
1.count()
2.max()
3.min()
4.avg()
5.sum()
特殊的：count(*)，统计结果集中记录数，包括null行。 3.排序
语法：
[ORDER BY {col_name | expr | position} [ASC | DESC], ...]
排序方式：
ASC 升序 默认的
DESC 降序
注意点：
1.如果排序中有null值，则MySQL是nullmin的，即null值最小。
2.多条件排序，多个排序条件有先后次序。
3.如果进行了分组，则是对分组后的结果集进行排序 4.分页
语法：
[LIMIT {[offset,] row_count | row_count OFFSET offset}]
offset: 起始行
row_count: 行数
公式：
起始行 = （当前页 - 1）* 页大小
行数 = 页大小
*/
DROP TABLE IF EXISTS student;
CREATE TABLE student(
id INT PRIMARY KEY, -- 编号
NAME VARCHAR(20), -- 姓名
age INT, -- 年龄
sex VARCHAR(5), -- 性别
address VARCHAR(100), -- 地址
math INT, -- 数学
english INT -- 英语
);
INSERT INTO student(id, NAME, age, sex, address, math, english)
VALUES
(1,'马云',55,'男','杭州',66,78),
(2,'马化腾',45,'男','深圳',98,87),
(3,'马景涛',55,'男','香港',56,77),
(4,'柳岩',20,'女','湖南',76,65),
(5,'柳青',20,'男','湖南',76,NULL),
(6,'刘德华',57,'男','香港',99,99),
(7,'马德',22,'女','香港',99,99),
(8,'德玛西亚',18,'男','南京',56,65); SELECT * FROM student; -- 按男女分组，查询平均分
SELECT sex, AVG(math) FROM student GROUP BY sex;
-- 按男女分组，查询平均分和男生女生人数
SELECT sex, AVG(math), COUNT(id) FROM student GROUP BY sex;
-- 按男女分组，查询平均分和男生女生人数。要求：分数低于70的不参与分组
SELECT sex, AVG(math), COUNT(id) FROM student WHERE math > 70 GROUP BY sex;
-- 按男女分组，查询平均分和男生女生人数。要求：分数低于70的不参与分组，并且分组人数大于2
SELECT sex, AVG(math), COUNT(id) FROM student WHERE math > 70 GROUP BY sex HAVING COUNT(id) > 2; -- 统计学生人数，用count(*)
SELECT count(*) FROM student; SELECT
MAX(english) max_score,
MIN(english) min_score,
AVG(english) avg_score,
sum(english) sumAll,
count(english) num
FROM student; -- 按照（总分 desc，英语 desc）排序
SELECT *, (IFNULL(math, 0) + IFNULL(english, 0)) AS total_score from student
ORDER BY total_score DESC, english DESC; -- 页大小为3，数据库中总共有8条数据
SELECT * FROM student LIMIT 0, 3; -- 第1页 返回3条
SELECT * FROM student LIMIT 3, 3; -- 第2页 返回3条
SELECT * FROM student LIMIT 6, 3; -- 第3页 返回2条
SELECT * FROM student LIMIT 9, 3; -- 第4页 返回0条
分组聚合排序分页查询
/*
约束分类: 4种约束的CRUD
注意点：
唯一 主键 外键 都可以约束多列。
4种约束，建表后，添加约束，如果表中有数据，则可能出bug，导致约束添加失败。 查询约束：
查询约束信息：SHOW CREATE TABLE 表名;
查询约束语法：help create talbe; help alter table 1.非空
NOT NULL 非空
建表时加非空，删除非空，建表后加非空 建表后添加非空约束：
bug: 如果表中有数据，并且指定列有null值，则非空约束添加失败。 2.唯一
UNIQUE 唯一，可以有多个null值
建表时加UNIQUE，删除UNIQUE，建表后加UNIQUE 建表后添加唯一约束：
bug: 如果表中有数据，并且指定的列有重复数据，则UNIQUE约束添加失败 注意点：
UNIQUE约束允许多个null值。 3.主键
PRIMARY KEY 唯一非空，一张表只能有一个主键约束
建表时加PRIMARY KEY，删除PRIMARY KEY，建表后加PRIMARY KEY 建表后添加主键约束：
bug: 如果表中有数据，并且指定列含有null值或者重复数据，则添加主键失败。 注意点：
1.PRIMARY KEY != UNIQUE + NOT NULL（因为主键只能有一个，而UNIQUE + NOT NULL可以多个）
2.删除PRIMARY KEY后还会有一个NOT NULL约束 4.外键
FOREIGN KEY 外键约束可以设置级联更新和级联删除
建表时加FOREIGN KEY，删除FOREIGN KEY，建表后加FOREIGN KEY 建表后添加外键约束：
bug: 如果从表有数据，并且外键列引用了被引用列没有的值，则添加外键约束失败 注意点：
1.添加外键约束，主表必须存在
2.表数据约束：
1.从表不能引用主表中不存在的值
2.主表不能删除被从表引用的记录
3.删表：从表引用主表，不能直接删除主表。 5.综合：
唯一 主键 外键 都可以约束多列。
4种约束，建表后，添加约束，如果表中有数据，则可能出bug，导致约束添加失败。 6.自动增长
AUTO_INCREMENT
1.一张表只能有一个自动增长列，该列必须是数值类型
2.自增的新值= 表数据最大值 + 1
7.默认值
DEFAULT */
DROP TABLE IF EXISTS student; -- 建表时添加NOT NULL约束
CREATE TABLE student(
name VARCHAR(50) NOT NULL
); -- 删除NOT NULL约束
ALTER TABLE student MODIFY name VARCHAR(50); -- 建表后添加NOT NULL约束
-- bug: 如果表中有数据，并且指定列有null值，则非空约束添加失败。
ALTER TABLE student MODIFY name VARCHAR(50) NOT NULL;
DELETE FROM student WHERE name IS NULL; DROP TABLE IF EXISTS student; -- 建表时,添加UNIQUE约束
CREATE TABLE student(
name VARCHAR(50) UNIQUE
); CREATE TABLE student(
name VARCHAR(50),
CONSTRAINT uni_name UNIQUE(name)
); -- 删除UNIQUE约束
ALTER TABLE student DROP INDEX name; -- 建表后,添加UNIQUE约束
-- bug: 如果表中有数据，并且指定的列有重复数据，则UNIQUE约束添加失败
ALTER TABLE student MODIFY name VARCHAR(50) UNIQUE;
ALTER TABLE student ADD CONSTRAINT uni_name UNIQUE(name); -- 查看表信息
SHOW CREATE TABLE student; /*
PRIMARY KEY 主键约束
唯一非空，一张表只能有一个主键约束
注意点：
PRIMARY KEY != UNIQUE + NOT NULL（因为主键只能有一个，而UNIQUE + NOT NULL可以多个）
*/
DROP TABLE IF EXISTS student; -- 建表时,添加PRIMARY KEY约束
CREATE TABLE student(
name VARCHAR(50) PRIMARY KEY
); CREATE TABLE student(
name VARCHAR(50),
PRIMARY KEY(name)
); -- 删除主键
ALTER TABLE student DROP PRIMARY KEY; -- 删除后还有一个NOT NULL约束 -- 创建完表后，添加主键
-- bug: 如果表中有数据，并且指定列含有null值或者重复数据，则添加主键失败。
ALTER TABLE student MODIFY name VARCHAR(50) PRIMARY KEY;
ALTER TABLE student ADD PRIMARY KEY (name); -- 查看表：约束
SHOW CREATE TABLE student; /*
外键约束：
注意点：
1.添加外键约束，主表必须存在
2.表数据约束：
1.从表不能引用主表中不存在的值
2.主表不能删除被从表引用的记录
3.删表：从表引用主表，不能直接删除主表。
*/
DROP TABLE IF EXISTS employee;
DROP TABLE IF EXISTS department; CREATE TABLE department(
id INT PRIMARY KEY,
name varchar(50) UNIQUE NOT NULL
);
-- 建表时，添加外键
CREATE TABLE employee(
id INT PRIMARY KEY,
name varchar(50) NOT NULL,
dep_id INT,
CONSTRAINT emp_dep_fk FOREIGN KEY(dep_id) REFERENCES department(id)
);
-- 删除外键
ALTER TABLE employee DROP FOREIGN KEY emp_dep_fk; -- 建表后，添加外键
-- bug: 如果从表有数据，并且外键列引用了被引用列没有的值，则添加外键约束失败
ALTER TABLE employee ADD CONSTRAINT emp_dep_fk FOREIGN KEY(dep_id) REFERENCES department(id); SHOW CREATE TABLE employee;
MySQL约束