MySQL笔记 第三天

1. ORDER BY 对查询结果排序
   按照字段顺序来排序

-- 按照id降序排列DESC 默认的是ASC 升序
SELECT * FROM cms_user ORDER BY id ;

SELECT * FROM cms_user ORDER BY id ASC;

SELECT * FROM cms_user ORDER BY id DESC;

-- 按照年龄升序排列
SELECT * FROM cms_user ORDER BY age ASC;

SELECT * FROM cms_user ORDER BY 1 DESC;

UPDATE cms_user SET age=12 WHERE id=5;

-- 按照年龄升序，id降序排列

SELECT * FROM cms_user ORDER BY age ASC,id DESC;

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

结合RAND()函数进行记录随机

-- 实现记录随机
SELECT * FROM cms_user ORDER BY RAND();

2. LIMIT限制查询结果显示条数，实现分页的效果
   LIMIT 显示条数

-- 查询表中前3条记录

SELECT * FROM cms_user LIMIT 3;

SELECT * FROM cms_user ORDER BY id DESC LIMIT 5;

LIMIT 偏移量 显示条数

-- 查询表中前一条记录
SELECT * FROM cms_user LIMIT 1;

SELECT * FROM cms_user LIMIT 0,1;

SELECT * FROM cms_user LIMIT 1,1;

SELECT * FROM cms_user LIMIT 0,5;

select的全部用法

SELECT id,sex,age,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 cms_user
WHERE id>=1
GROUP BY sex
HAVING COUNT(*)>=2
ORDER BY age DESC
LIMIT 0,2;

3. UPDATE 的应用

-- 更新用户名为4位的用户，让其已有年龄-3

UPDATE cms_user SET age=age-3 WHERE username LIKE '____';

-- 更新前3条记录，让已有年龄+10
UPDATE cms_user SET age=age+10 LIMIT 3;
-- 更新或者删除时LIMIT只能写一个参数
UPDATE cms_user SET age=age+10 LIMIT 0,3;

-- 按照id降序排列，更新前3条

UPDATE cms_user SET age=age+10 ORDER BY id DESC LIMIT 3;

-- 删除用户性别为男的用户，按照年龄降序排列，删除1前一条记录

DELETE FROM cms_user WHERE sex='男' ORDER BY age DESC LIMIT 1;

4. 连接查询：连接查询是将两个或两个以上的表按某个条件连接起来，从中选取的数据。连接查询是同事查询两个或两个以上的表时使用的。当不同的表中存在相同意义的字段时，可以通过该字段连接这几个表
   (1)内连接查询
   a.JOIN|CROSS JOIN INNER JOIN
   b.通过ON连接条件
   c.显示两个表中符合连接条件的记录

-- 查询cms_user id,username
-- provinces,proName
SELECT cms_user.id,username,proName FROM cms_user,provinces;

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

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 provinces AS p

CROSS JOIN cms_user AS u

ON u.proId=p.id;


SELECT u.id,u.username,u.email,u.sex,p.proName

FROM provinces AS p

JOIN cms_user AS u

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;

--　　按照id升序排列

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

ORDER BY u.id ASC;


-- 限制显示条数 前2条
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

ORDER BY u.id ASC

LIMIT 0,2;

(2)外连接查询
a.左外连接
LEFT[OUTER] JOIN 显示左表的全部记录及右表符合连接条件的记录
b.RIGHT [OUTER] JOIN 显示右表的全部记录以及左表符合了·条件的记录

-- 插入错误的数据

INSERT cms_user(username,password,regTime,proId)

VALUES('TEST2','TEST2','1381203974',20);



-- 左外连接
SELECT u.id,u.username,u.email,u.sex,p.proName

FROM cms_user AS u

LEFT JOIN provinces AS p

ON u.proId=p.id;


SELECT u.id,u.username,u.email,u.sex,p.proName

FROM provinces AS p

LEFT JOIN cms_user AS u

ON u.proId=p.id;

SELECT u.id,u.username,u.email,u.sex,p.proName

FROM provinces AS p

RIGHT JOIN cms_user AS u

ON u.proId=p.id;




SELECT u.id,u.username,u.email,u.sex,p.proName

FROM provinces AS p

RIGHT JOIN cms_user AS u

ON u.proId=p.id;

5. 外键
   (1)外键是表的一个特殊字段。被参照的表是主表，外键所在字段的表为子表。设置外键的原则需要记住，就是依赖于数据库中已存在的表的主键。外键的作用是建立表与其父表的关联关系。父表中对记录做操作时，子表中与之对应的信息也应有相应的改变。
   (2)外键的作用保持数据的一致性和完整性
   (3)可以实现一对一或一对多的关系
   (4)注意：
   a.父表和子表必须使用相同的存储引擎，而且进制使用临时表
   b.数据表的存储引擎只能为InnoDB
   c.外键列和参照列必须具有相似的数据类型。其中数字的长度或是否有符号位必须相同；而字符的长度则可以不同
   d.外键列和参照列必须创建索引。如果外键列不存在索引的话，MySQL将自动创建索引

在建表时指定外键

-- 创建员工表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),
('张三',3),
('李四',4),
('王五',1);

-- 删除主表中的记录 提示不成功
DELETE FROM department WHERE id=1;

-- 删除employee中的属于1部门的人
DELETE FROM employee WHERE depId=1;

创建外键

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),
('张三',3),
('李四',4),
('王五',2);

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

-- 添加外键

ALTER TABLE employee ADD CONSTRAINT emp_fk_dep FOREIGN KEY(depId) REFERENCES department(id);

(5)外键约束的参照操作
a.CASCADE：从父表删除或更新且自动删除或更新子表中匹配的行
b.SET NULL:从附表删除或更新行，并设置子表中的外键列为NULL。如果使用该选项，必须保证子表列没有指定NOT NULL
c.RESTRICT:拒绝对父表的删除或更新操作
d.NO ACTION: 标准SQL的关键字，在MySQL中与RESRTRICT相同

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 ON UPDATE CASCADE
)ENGINE=INNODB;

INSERT employee(username,depId) VALUES('king',1),
('queen',2),
('张三',3),
('李四',4),
('王五',1);

-- 删除部门表中的第一个部门
DELETE FROM department WHERE id=1;

UPDATE department SET id=id+10;

6. 联合查询
   (1)UNION 查询结果去掉重复的值
   (2)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;

7. 子查询
   (1)子查询是将一个查询语句嵌套语句嵌套在另一个查询语句中。内层查询语句的查询结果，可以作为外层查询语句提供条件。

-- 由[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 IN(SELECT id FROM department);

(2)引发子查询的情况
a.使用[NOT] IN 的子查询
b.使用比较运算符的子查询=、>、<、>=、 <=、 <>、 != 、<=>

--　创建学员表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('king',95),
('king1',35),
('king2',45),
('king3',55),
('king4',65),
('king5',75),
('king6',80),
('king7',90),
('king8',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);



-- 查询获得1等奖学金的学员有

SELECT level FROM scholarship WHERE id=1;

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

SELECT id,username FROM student WHERE score>=(SELECT level FROM scholarship WHERE id=1);

c.使用[NOT] EXISTS 的子查询 内层为假则不会执行外层语句
d.使用ANY|SOME或者ALL的子查询

-- 查询所有获得奖学金的学员

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);

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

-- 相当于IN
SELECT id,username,score FROM student WHERE score=ANY(SELECT level FROM scholarship);

SELECT id,username,score FROM student WHERE score IN(SELECT level FROM scholarship);

-- 相当于NOT IN
SELECT id,username,score FROM student WHERE score NOT IN(SELECT level FROM scholarship);

SELECT id,username,score FROM student WHERE score <> ALL(SELECT level FROM scholarship);

(3)将查询结果写入到数据表
INSERT [INTO] tbl_name [(col_name,……)] SELECT……
(4)创建数据表的同时将查询结果写入到数据表
CREATE TABLE [IF NOT EXISTS] tbl_name [(create_definition,……)]
select_statement

-- 只有字段重复的时候才能赋值
CREATE TABLE test2 (
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
num TINYINT UNSIGNED
)SELECT id,score FROM student;

8. 通过正则表达式查询
   (1)REGEXP ‘匹配方式’
   (2)常用匹配方式
   

-- ^匹配字符开始的部分
-- 查询用户名以t开始的用户
SELECT * FROM cms_user WHERE username REGEXP '^t';

-- $匹配字符串结尾的部分

SELECT * FROM cms_user WHERE username REGEXP 'g$';


-- .代表任意字符

SELECT * FROM cms_user WHERE username REGEXP '.';

SELECT * FROM cms_user WHERE username REGEXP 'r..g';

SELECT * FROM cms_user WHERE username LIKE 'r__g';

-- [字符集合] [lto]  匹配到其中任意一个

SELECT * FROM cms_user WHERE username REGEXP '[lto]';

-- [^字符集合] 除了字符集合中的内容
SELECT * FROM cms_user WHERE username REGEXP '[^lto]';

SELECT * FROM cms_user WHERE username REGEXP '[^l]';

INSERT cms_user(username,password,regTime,proId)
VALUES('lll','lll',138212349,2),
('ttt','lll',138212349,2),
('ooo','lll',138212349,2);

SELECT * FROM cms_user WHERE username REGEXP '[a-k]';

SELECT * FROM cms_user WHERE username REGEXP '[^a-m]';

SELECT * FROM cms_user WHERE username REGEXP 'ng|qu';

SELECT * FROM cms_user WHERE username REGEXP 'ng|qu|te';

SELECT * FROM cms_user WHERE username REGEXP 'que*';


SELECT * FROM cms_user WHERE username REGEXP 't+';

SELECT * FROM cms_user WHERE username REGEXP 'que+';

SELECT * FROM cms_user WHERE username REGEXP 'que{2}';

SELECT * FROM cms_user WHERE username REGEXP 'que{3}';

SELECT * FROM cms_user WHERE username REGEXP 'que{1,3}';