08数据库复习02

1.数据的操作

-- 更新名字为4为的用户，让其年龄+100
UPDATE  cms_user  SET age = age + 100 WHERE username LIKE '____';

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

注意：不能使用偏移量的分页限制方式。

-- 删除用户性别为男的用户，按照年龄降序排列，删除前2条记录
DELETE FROM cms_user WHERE sex = '男' ORDER BY  age DESC LIMIT 2;

2.连接查询

-- 查询cms_user id,username
-- provinces,proName
SELECT cms_user.id,cms_user.username,provinces.proName FROM cms_user,provinces;-- 这样得到的结果是一个笛卡尔积的形式

SELECT cms_user.id,cms_user.username,provinces.proName FROM cms_user,provinces WHERE cms_user.proId = provinces.`id`;

-- 内连接的方式进行查询
SELECT u.`id`,u.`username`,p.`proName`
FROM cms_user AS u
INNER JOIN provinces AS p
ON u.`proId` = p.`id`

SELECT u.`id`,u.`username`,p.`proName`
FROM cms_user AS u
CROSS JOIN provinces AS p
ON u.`proId` = p.`id`

SELECT u.`id`,u.`username`,p.`proName`
FROM cms_user AS u
JOIN provinces AS p
ON u.`proId` = p.`id`

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

SELECT u.`id`,u.`username`,p.`proName`
FROM cms_user AS u
JOIN provinces AS p
ON u.`proId` = p.`id`
WHERE u.`sex`='男'
GROUP BY p.`proName`;

SELECT u.`id`,u.`username`,p.`proName`,COUNT(*) AS totalUser
FROM cms_user AS u
JOIN provinces AS p
ON u.`proId` = p.`id`
WHERE u.`sex`='男'
GROUP BY p.`proName`;

SELECT u.`id`,u.`username`,p.`proName`,COUNT(*) AS totalUser
FROM cms_user AS u
JOIN provinces AS p
ON u.`proId` = p.`id`
WHERE u.`sex`='男'
GROUP BY p.`proName`
HAVING totalUser > 1;

-- 查询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 c.`id`=n.`cId`
JOIN cms_admin AS a
ON a.`id` = n.`aId`

-- 插入一条错误的数据
INSERT cms_user(username,PASSWORD,regTime,proId)
VALUES('test2','test2',1234,20);

SELECT * FROM cms_user;

-- test2那条数据使用内连接查询查不出来，因为是个错误数据，不满足查询条件
SELECT u.`id`,u.`username`,p.`proName`
FROM cms_user AS u
JOIN provinces AS p
ON u.`proId` = p.`id`

-- 左外连接，左表为主表
SELECT u.`id`,u.`username`,p.`proName`
FROM cms_user AS u
LEFT JOIN provinces AS p
ON u.`proId` = p.`id`

-- 左外连接，左表为主表
SELECT u.`id`,u.`username`,p.`proName`
FROM provinces AS p
LEFT JOIN cms_user AS u
ON u.`proId` = p.`id`

3.外键

-- 创建部门表department（主表）
-- id depName 
CREATE TABLE IF NOT EXISTS department(
    id TINYINT UNSIGNED AUTO_INCREMENT KEY,
    depName VARCHAR(20) NOT NULL UNIQUE
)ENGINE = INNODB;

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

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


INSERT employee(username,depId) VALUES('king',1),
('queen',2),
('zhangsan',3),
('lisi',4),
('wangwu',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='督导部';

SELECT * FROM employee;

-- 删除之前创建的员工表和部门表
DROP TABLE employee,department;

-- 重新创建带有外键的员工表和部门表
-- 创建部门表department（主表）
-- id depName 
CREATE TABLE IF NOT EXISTS department(
    id TINYINT UNSIGNED AUTO_INCREMENT KEY,
    depName VARCHAR(20) NOT NULL UNIQUE
)ENGINE = INNODB;

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

-- 创建员工表employee（子表）
-- id,username,depId
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;


INSERT employee(username,depId) VALUES('king',1),
('queen',2),
('zhangsan',3),
('lisi',4),
('wangwu',1);

-- 先删除属于1部门的员工
DELETE FROM employee WHERE depId = 1;
-- 然后才可以删除1部门
DELETE FROM department WHERE id = 1;

INSERT employee(username,depId) VALUES('test',11); -- 因为有了外键约束，所以不能随便插入

-- 删除员工表
DROP TABLE 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)
)ENGINE = INNODB;

INSERT employee(username,depId) VALUES('king',3),
('queen',2),
('zhangsan',3),
('lisi',4),
('wangwu',2);

-- 删除外键
ALTER TABLE employee DROP FOREIGN KEY emp_fk_dep;

-- 添加外键
ALTER TABLE employee ADD CONSTRAINT emp_fk_dep FOREIGN KEY(depId) REFERENCES department(id);

-- 删除之前创建的表
DROP TABLE employee,department;

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

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

-- 创建员工表employee（子表）
-- id,username,depId
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) ON DELETE CASCADE -- 级联操作，当删除父表中的记录，对应的字表中的记录也会被删除
)ENGINE = INNODB;


INSERT employee(username,depId) VALUES('king',1),
('queen',2),
('zhangsan',3),
('lisi',4),
('wangwu',1);

SELECT * FROM department;

DELETE FROM department WHERE id=1;

SELECT * FROM employee;

4.联合查询

-- 联合查询
SELECT username FROM cms_user; -- 有10条记录

SELECT username  FROM employee; -- 有3条记录

SELECT username FROM cms_user UNION SELECT username  FROM employee; -- 有12条记录，去除了重复

SELECT username FROM cms_user UNION ALL SELECT username  FROM employee; -- 有13条记录，不去除重复

5.子查询

-- 由[not] in 引发的子查询
SELECT id FROM department; -- 答案为2,3,4

SELECT id,username FROM employee WHERE depId IN(2,3,4);

-- 利用子查询
SELECT id,username FROM employee WHERE depId IN(SELECT id FROM department);

-- 创建student表
-- id，username，score
CREATE TABLE IF NOT EXISTS student(
    id TINYINT UNSIGNED AUTO_INCREMENT KEY,
    username VARCHAR(20) NOT NULL UNIQUE,
    score TINYINT UNSIGNED
);
INSERT student(username,score) VALUES('king1',90),
('king2',80),
('king3',40),
('king4',50),
('king6',70),
('king7',90),
('king8',55),
('king9',25);

-- 创建奖学金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);

-- 查询一等奖学金的学员有,id,username
SELECT LEVEL FROM scholarship WHERE id=1; -- 查询结果为90

SELECT id,username FROM student WHERE score >= 90;

-- 使用子查询
SELECT id,username FROM student WHERE score>=(SELECT LEVEL FROM scholarship WHERE id=1);

SELECT id,username FROM employee WHERE EXISTS(SELECT * FROM department WHERE id = 10); -- 没有查询结果

SELECT id,username FROM employee WHERE EXISTS(SELECT * FROM department WHERE id = 2);

-- 查询所有获得奖学金的学员信息id,username
SELECT id,username FROM student WHERE score >= ANY (SELECT LEVEL FROM scholarship)

-- 查询所有获得一等奖学金的学员信息id,username
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);

CREATE TABLE test1(
    id TINYINT UNSIGNED AUTO_INCREMENT KEY,
    num TINYINT UNSIGNED
);
-- 将查询的结果插入到另一个表中
INSERT test1(id,num) SELECT id,score FROM student;

SELECT * FROM test1;

-- 在创建表时，将查询结果写入到表中
CREATE TABLE test2(
    id TINYINT UNSIGNED AUTO_INCREMENT KEY,
    num TINYINT UNSIGNED
) SELECT id,score FROM student;

SELECT * FROM test2;

-- 在创建表时，将查询结果写入到表中
CREATE TABLE test3(
    id TINYINT UNSIGNED AUTO_INCREMENT KEY,
    score TINYINT UNSIGNED
) SELECT id,score FROM student;

SELECT * FROM test3;

6.正则表达式

-- 查询用户名以r开始的用户
SELECT * FROM cms_user WHERE username REGEXP '^r';

-- 查询用户名以g结尾的用户
SELECT * FROM cms_user WHERE username REGEXP 'g$';

-- 查询用户名以r开始，以g结束，中间有两个字符 .
SELECT * FROM cms_user WHERE username REGEXP 'r..g';

SELECT * FROM cms_user WHERE username LIKE 'r__g';

-- 查询用户名中包含tlo字符的用户信息 [tlo]
SELECT * FROM cms_user WHERE username REGEXP '[tlo]'; 

-- 查询用户名中不包含tlo字符的用户信息 [tlo]
SELECT * FROM cms_user WHERE username REGEXP '[^tlo]'; -- 注意要求用户名中仅仅包含t字符或者仅仅包含l字符或者仅仅包含o字符

-- 查询用户名中包括ng|qu|te的用户信息
SELECT * FROM cms_user WHERE username REGEXP 'ng|qu|te';

7.运算符

SELECT username,username = 'king' FROM cms_user;

SELECT id,username, score >= 70 FROM student;

8.数学函数

9.字符串函数

 

10.日期函数

 

11.条件判断函数和系统函数

11.1条件判断函数

SELECT id,username, score,IF(score>=60,'及格','不及格') FROM student;

SELECT id,username,age,IFNULL(age,0) FROM cms_user;

SELECT id,username,score,CASE WHEN score>60 THEN '不错' WHEN score=60 THEN '刚及格' ELSE '没及格' END FROM student;

11.2系统函数

12.加密函数

13.其他常用的函数