MySQL-DAY03

1.order by

升序排列 ASC

降序排列 DESC

2.limit

限制显示条数

3.内连接和外连接

内连接：CROSS JOIN 与 INNER JOIN 与 JOIN 是一样的

外连接：左外连接，LEFT [OUTER] JOIN；右外连接，RIGHT [OUTER] JOIN

4.外键

 5.联合查询和子查询

 6.正则表达式查询

############################DAY03########################################

-- 按照id降序排列DESC 默认是ASC
SELECT * FROM user13 ORDER BY id;
SELECT * FROM user13 ORDER BY id ASC;  #按照升序进行排列
SELECT * FROM user13 ORDER BY id DESC; #按照降序进行排列

-- 按照年龄升序排列
SELECT * FROM user13 ORDER BY age ASC;  #按照升序进行排列
SELECT * FROM user13 ORDER BY 1 ASC;  #按照字段位置 1 即为 age 升序进行排列

UPDATE user13 SET age=12 WHERE id=8;

-- 按照年龄升序，id降序排列
SELECT * FROM user13 ORDER BY age ASC,id DESC;  #先考虑年龄升序，再考虑id降序

SELECT id,age,email,GROUP_CONCAT(username),COUNT(*) AS totalusers,SUM(age) AS sum_age
FROM user13
WHERE id>=2
GROUP BY age
HAVING COUNT(*)>=2
ORDER BY age DESC,id ASC;

SELECT * FROM user13 ORDER BY RAND(); #随机提取记录

-- 查询表中前3条记录
SELECT * FROM user13 LIMIT 3; #限制显示条数和序号没有关系
SELECT * FROM user13 ORDER BY id DESC LIMIT 5;

-- 查询表中前一条记录
SELECT * FROM user13 LIMIT 1;
SELECT * FROM user13 LIMIT 0,3; # 从第一条开始显示3条记录
SELECT * FROM user13 LIMIT 1,3; # 从第2条开始显示3条记录

SELECT id,age,email,GROUP_CONCAT(username),
COUNT(*) AS totalUsers,
MAX(age) AS max_age,
MIN(age) AS min_age,
AVG(age) AS avg_age,
SUM(age) AS sum_age
FROM user13
WHERE id>=1
GROUP BY age
HAVING COUNT(*)>=2
ORDER BY age DESC
LIMIT 0,2;


-- 更新用户名为4为的用户，让其已有的年龄 -3
UPDATE user13 set age = age-3 WHERE username LIKE '____';

-- 更新前3条记录，让已有年龄 +10
UPDATE user13 SET age = age+10 LIMIT 3;

-- 按照id降序排列，更新前3条记录 +10
UPDATE user13 SET age = age+10 ORDER BY id DESC LIMIT 3;

-- 删除年龄为22的用户，按照年龄降序排列，删除前1条记录
DELETE FROM user13 WHERE age=22 ORDER BY age DESC LIMIT 1;


-- 查询用户表user13 中id,username
-- 查询用户表user5  中provinces,proName
SELECT user13.id,username FROM user13,user5;

-- cms_user的proId对应省份表中的id
SELECT cms_user.id,username,proName FROM cms_user,procinces
WHERE cms_user.proId=provinces.id;

-- 查询cms_user表中id,username,email,sex
-- 查询provinces表中proName
SELECT u.id,u.username,u.email,u.sex,p.proName
FROM cms_user AS u
INNER JOIN provinces AS p
ON u.proId=p.id;
-- 另一种方法
SELECT u.id,u.username,u.email,u.sex,p.proName
FROM cms_user AS u
CROSS JOIN provinces AS p #CROSS JOIN 与 INNER JOIN 与 JOIN 是一样的
ON u.proId=p.id;

-- 查询cms_user 中 id,username,sex
-- 查询provinces中 proName
-- 条件时cms_user的性别为男的用户
SELECT u.id,u.username,u.sex,p.proName
FROM cms_user AS u
JOIN provinces AS p
ON u.proId=p.id
WHERE u.sex='男';

-- 根据proName分组
SELECT u.id,u.username,u.sex,p.proName,COUNT(*) AS totalusers,GROUP_CONCAT(username)
FROM cms_user AS u
JOIN provinces AS p
ON u.proId=p.id
WHERE u.sex='男'
GROUP BY p.proName;

-- 对分组结果进行筛选，选出组中人数大于等于1
SELECT u.id,u.username,u.sex,p.proName,COUNT(*) AS totalusers,GROUP_CONCAT(username)
FROM cms_user AS u
JOIN provinces AS p
ON u.proId=p.id
WHERE u.sex='男'
GROUP BY p.proName
HAVING COUNT(*)>=1 #选出组中人数大于等于1
ORDER BY u.id ASC  #按照id升序排列
LIMIT 2;           #限制显示条数前2条


-- 查询cms_news中的id,title,
-- 查询cms_cate中的cateName
SELECT n.id,n.title,c.cateName
FROM cms_news AS n
JOIN cms_cate AS c
ON n.cId=c.id;

-- 查询cms_news id,title
-- 查询cms_admin username,role
SELECT n.id,n.title,a.username,a.role
FROM cms_news AS n
JOIN cms_admin AS a
ON n.aId=a.id;

-- 查询cms_news 中 id,title
-- 查询cms_cate 中 cateName
-- 查询cms_admin 中 username，role
SELECT n.id,n.title,c.cateName,a.username,a.role
FROM cms_cate AS c
JOIN cms_news AS n
ON n.cId=c.id
JOIN cms_admin AS a
on n.aId=a.id;


-- 插入错误的数据
INSERT cms_user(username,password,regTime,proId)
VALUES('Test2','Test2','133546','20');


-- 外连接查询（左外连接，LEFT [OUTER] JOIN；右外连接，RIGHT [OUTER] JOIN）
SELECT n.id,n.title,a.username,a.role
FROM cms_news AS n
LEFT JOIN cms_admin AS a # 左外连接以左表为准，即 FROM 的表
ON n.aId=a.id;

SELECT n.id,n.title,a.username,a.role
FROM cms_news AS n
RIGHT JOIN cms_admin AS a # 右外连接以右表为准，即 JOIN 的表
ON n.aId=a.id;


-- 外键
-- 创建部门表departmen（主表）
-- id,depName.

CREATE TABLE IF NOT EXISTS department(
id TINYINT UNSIGNED auto_increment KEY,
depName VARCHAR(20) NOT NULL UNIQUE
)ENGINE=INNODB
DEFAULT CHARSET=utf8;

INSERT department(depName) VALUES('教学部'),
('市场部'),
('运营部'),
('督导部');

-- 创建员工表employee(子表)
CREATE TABLE IF NOT EXISTS employee(
id SMALLINT UNSIGNED auto_increment KEY,
username VARCHAR(20) NOT NULL UNIQUE,
depId TINYINT UNSIGNED
)ENGINE=INNODB
DEFAULT CHARSET=utf8;

INSERT employee(username,depId) VALUES('king',1),
('张华',2),
('qw',3),
('tds',4),
('wddd',1);
-- 内连接查询
SELECT e.id,e.username,d.depName
FROM employee AS e
JOIN department AS d
ON e.depId=d.id;
-- 删除督导部
DELETE FROM department WHERE depName = '督导部';
-- 删除表employee,department
DROP TABLE employee,department;

-- 创建department（主表）
CREATE TABLE IF NOT EXISTS department(
id TINYINT UNSIGNED auto_increment KEY,
depName VARCHAR(20) NOT NULL UNIQUE
)ENGINE=INNODB
DEFAULT CHARSET=utf8;
INSERT department(depName) VALUES('教学部'),
('市场部'),
('运营部'),
('督导部');
-- 创建员工表employee(子表)
CREATE TABLE IF NOT EXISTS employee(
id SMALLINT UNSIGNED auto_increment KEY,
username VARCHAR(20) NOT NULL UNIQUE,
depId TINYINT UNSIGNED,
FOREIGN KEY(depId) REFERENCES department(id)
)ENGINE=INNODB
DEFAULT CHARSET=utf8;
INSERT employee(username,depId) VALUES('king',1),
('张华',2),
('qw',3),
('tds',4),
('wddd',1);
-- 删除主表记录 
DELETE FROM department WHERE id = 1; #此时1部门下有员工，执行会提示错误

-- 删除员工表中属于1部门的人
DELETE FROM employee WHERE depId=1;
DELETE FROM department WHERE id = 1; #此时1部门下没有员工，该部门可以删除

-- 插入垃圾数据
INSERT employee(username,depId) VALUES('test',11); #受外键影响，因为没有11部门，所以该插入命令不会被执行

-- 删除员工表
DROP TABLE employee;
-- 创建员工表employee(子表),设置外键名称
CREATE TABLE IF NOT EXISTS employee(
id SMALLINT UNSIGNED auto_increment KEY,
username VARCHAR(20) NOT NULL UNIQUE,
depId TINYINT UNSIGNED,
CONSTRAINT emp_fk_dep         #设置外键名称
FOREIGN KEY(depId) REFERENCES department(id) #设置外键
ON DELETE CASCADE             #当删除主表中数据，子表中的数据也会被删除，（级联操作）
on UPDATE CASCADE             #当更新主表中数据，子表中的数据也会被更新
)ENGINE=INNODB
DEFAULT CHARSET=utf8;

-- 删除外键
ALTER TABLE employee DROP FOREIGN KEY emp_fk_dep;  #删除外键但不会删除索引
-- 添加外键
ALTER TABLE employee ADD CONSTRAINT emp_fk_dep FOREIGN KEY(depId) REFERENCES department(id) #此时子表中不应该有垃圾数据

-- CASCADE
DELETE FROM department WHERE id=1; #当设置了 ON DELETE CASCADE ，删除主表中的数据，子表中的相应数据也会被删除

UPDATE department SET id=id+10; #当子表中存在记录时，主表中的数据不能进行更新

-- SET NULL
CREATE TABLE IF NOT EXISTS employee(
id SMALLINT UNSIGNED auto_increment KEY,
username VARCHAR(20) NOT NULL UNIQUE,
depId TINYINT UNSIGNED,
CONSTRAINT emp_fk_dep         #设置外键名称
FOREIGN KEY(depId) REFERENCES department(id) #设置外键
ON DELETE SET NULL             #当删除主表中数据，子表中的数据会被设置为NULL
on UPDATE SET NULL             #当更新主表中数据，子表中的数据会被设置为NULL
)ENGINE=INNODB
DEFAULT CHARSET=utf8;


-- 联合查询 UNION 和 UNION ALL
SELECT username FROM employee UNION SELECT username FROM cms_user;     #返回数据，将重复的记录删除
SELECT username FROM employee UNION ALL SELECT username FROM cms_user; #返回所有的记录，不会删除重复数据
SELECT id,username FROM employee UNION ALL SELECT username,age FROM cms_user; #将两个表中的两个字段进行查询合并


-- 由【NOT】 IN引发的子查询
SELECT id FROM department;
SELECT id,username FROM employee WHERE depId in(1,2,3,4);
SELECT id,username FROM employee WHERE depId NOT in(SELECT id FROM department); #查询员工表中部门名称没在department中的记录

-- 创建学员表student和奖学金表scholarship
-- student   id,username, score
CREATE TABLE IF NOT EXISTS student(
id TINYINT UNSIGNED auto_increment KEY,
username VARCHAR(20) NOT NULL,
score TINYINT UNSIGNED
);
INSERT student(username,score) VALUES('king',95),
('king1',82),
('king2',85),
('king3',77),
('king4',89),
('king5',76),
('king6',96),
('king7',55);
-- scholarship    id,level
CREATE TABLE IF NOT EXISTS scholarship(
id TINYINT UNSIGNED auto_increment key,
level TINYINT UNSIGNED
);
INSERT scholarship(level) VALUES(90),(80),(70);
-- 查询获得一等奖学金的学员
SELECT id,username FROM student WHERE score>=(SELECT level FROM scholarship WHERE id=1);  # 先执行（）内的内容


-- 由 [NOT]EXISTS 引发的查询
SELECT id,username FROM employee WHERE EXISTS(SELECT * FROM department WHERE id=5);

-- 使用ANY|SOME或者ALL的子查询
-- 查询所有获得奖学金的学院
-- =ANY相当于in；<>ANY相当于 NOT in
SELECT id,username,score FROM student WHERE score>=ANY(SELECT level FROM scholarship);
SELECT id,username,score FROM student WHERE score>=SOME(SELECT level FROM scholarship); #关键字 ANY 和 SOME 相同

-- 查询所有学院中获得一等奖学金的学员
SELECT id,username,score FROM student WHERE score>=ALL(SELECT level FROM scholarship);
-- 查询所有学员中没有获得奖学金的学员
SELECT id,username,score FROM student WHERE score<ALL(SELECT level FROM scholarship);
-- 查询没有获得一等奖学金的学员
SELECT id,username,score FROM student WHERE score<ANY(SELECT level FROM scholarship);

-- 将查询结果写入到数据表
CREATE TABLE test1(
id TINYINT UNSIGNED auto_increment KEY,
num TINYINT UNSIGNED
);

INSERT test1(id,num) SELECT id,score FROM student;

-- 创建表时也可以写入，但是字段名称必须一致，此时num字段为null
CREATE TABLE test2(
id TINYINT UNSIGNED auto_increment KEY,
num TINYINT UNSIGNED
)SELECT id,score FROM student;


-- 正则表达式查询
-- 查询用户名以t开始的用户
SELECT *FROM student WHERE username REGEXP '^t';
-- 查询用户名以3结尾的用户
SELECT * FROM student WHERE username REGEXP '3$'
-- . 代表任意字符
SELECT *FROM student WHERE username REGEXP '.';
SELECT *FROM student WHERE username REGEXP 'k.n';
SELECT *FROM student WHERE username LIKE 'k_n%';  # LIKE 'k_n%' 与 REGEXP 'k.n' 相同
-- 【字符集合】 [lto]
SELECT * FROM student WHERE username REGEXP '[lto]';
-- 除了字符集合 [^lto]
SELECT * FROM student WHERE username REGEXP '[^lto]';

INSERT student(username,score) VALUES('lll',95),
('lto',82),
('ttt',85),
('ooo',56);

-- 查询[a-k]的内容
SELECT * FROM student WHERE username REGEXP '[lto]';
-- 匹配qu|ng|df 两个字符串
SELECT * FROM student WHERE username REGEXP 'qu|ng|df';
-- * 代表0个1个或多个
SELECT * FROM student WHERE username REGEXP 'kin*'
-- + 代表至少出现一个或多个
SELECT * FROM student WHERE username REGEXP 'king+' #表示 king至少出现1次
-- {} 代表出现多次
SELECT * FROM student WHERE username REGEXP 'kin{2}' #表示 kin 至少出现2次
SELECT * FROM student WHERE username REGEXP 'kin{1,3}' #表示 kin 至少出现1次，最多出现3次