皮特王同学

# 添加记录

INSERT [INTO] tbl_name[(col_name, ...)] {VALUE|VALUES}(VALUES ...);

# 不指定字段名称

INSERT tbl_name VALUE(value...);
INSERT user VALUE(1, 'king', 24, 'king@qq.com');
INSERT user VALUES(NULL, 'queue', 24, 'queue@qq.com');
INSERT user VALUES(DEFAULT, 'lily', 24, 'lily@qq.com');

# 指定字段名称

INSERT tbl_name(字段名称,...) VALUES(值,...);
INSERT user(username,email) VALUES('rose', 'rose@qq.com');

# INSERT ... SET

INSERT tbl_name SET 字段名称=值,...;

# INSERT ... SELECT

INSERT tbl_name[(字段名称,...)] SELECT 字段名称,... FROM tbl_name [WHERE 条件];

# 一次添加多条记录

INSERT tbl_name[(字段名称,...)] VALUES(值,...),(值,...),(值,...);

# 修改记录

UPDATE tbl_name SET 字段名称=值,字段名称=值 [WHERE 条件];
UPDATE user SET age=29 WHERE id=1;
UPDATE user SET age=age-20,email=DEFAULT WHERE id<=5;

如果不添加条件，整个表中的记录都会被更新

# 删除记录

DELETE FROM tbl_name [WHERE 条件];
DELETE FROM user WHERE username='king';
DELETE FROM user WHERE age=24;
DELETE FROM user;

如果不添加条件，表中所有记录都会被删除
DELETE清空数据表的时候不会重置AUTO_INCREMENT的值，可以通过ALTER语句将其重置为1

# 彻底清空数据表

TRUNCATE [TABLE] tbl_name

清除表中所有记录并重置AUTO_INCREMENT的值

# 查询记录

SELECT select_expr,... FROM tbl_name
[WHERE 条件] 
[GROUP BY {col_name|position} HAVING 二次筛选]
[ORDER BY {col_name|position|expr} [ASC|DESC]]
[LIMIT 限制结果集的显示条数]

# 查询表中所有记录

SELECT * FROM tbl_name;
-- *表示所有字段
SELECT * FROM user1;

# 查询指定字段

SELECT 字段名称,... FROM tbl_name;
SELECT username,addr,age FROM user1;

# 不打开数据库查询

SELECT 字段名称,... FROM db_name.tbl_name;

# 给字段起别名

SELECT 字段名称 [AS] 别名名称, ... FROM tbl_name;

SELECT id AS '编号',username AS '用户名',sex AS '性别' FROM  user1;

# 给数据表起别名

SELECT 字段名称,... FROM tbl_name [AS] 别名；

SELECT id,username FROM user1 AS u;

# 表名.字段名的形式

SELECT tbl_name.col_name,... FROM tbl_name;

SELECT user1.id, user1.username FROM user1;

# WHERE 条件

筛选出符合条件的记录

# 比较运算符

> >= < <= = != <> <=>

SELECT id,username FROM user1
WHERE id=5;

-- 检测NULL值
SELECT id,username,age,userDesc FROM user1 WHERE userDesc<=>NULL;

<=> 和 = 的区别：<=>可以检测NULL值

# IS [NOT] NULL

检测是否为NULL或者NOT NULL

SELECT id,username,age,userDesc FROM user1 WHERE userDesc IS NULL;

# 指定范围

[NOT] BETWEEN...AND

SELECT id,username,age,sex FROM user1
WHERE age BETWEEN 18 AND 30;

# 指定集合

[NOT] IN(值,...)

SELECT id,username,age FROM user1
WHERE id IN(1,3,5,7,9);

SELECT id,username,age FROM user1
WHERE username IN('king','queue');

# 逻辑运算符

  • AND 逻辑与

    SELECT id,username,age,sex FROM user1
WHERE sex='男' AND age>=20;

SELECT id,username,age,sex,salary,addr FROM user1 WHERE salary BETWEEN 60000 AND 100000 AND sex='男' AND addr='北京';

  • OR 逻辑或

    SELECT id,username,age FROM user1
WHERE id=1 OR username='queue';

# 匹配字符

[NOT] LIKE

SELECT id,username,age FROM user1
WHERE username='king';

SELECT id,username,age FROM user1
WHERE username LIKE 'king';

通配符

  • % 任意长度的字符串
  • _ 任意一个字符
SELECT id,username,age,sex FROM user1
WHERE username LIKE '%三%';

-- 用户名长度为三位的用户
SELECT id,username,age,sex FROM user1
WHERE username LIKE '___';

SELECT id,username,age,sex FROM user1
WHERE username LIKE '张_%';

-- 默认忽略大小写
SELECT id,username,age FROM user1
WHERE username LIKE '%K%';

# GROUP BY 分组

把值相同放到一个组中，最终查询出的结果只会显示组中一条记录

-- 按照性别分组sex
SELECT id,username,age,sex FROM user1
GROUP BY sex;

# GROUP_CONCAT()

分组配合GROUP_CONCAT()查看组中某个字段的详细信息

-- 按照性别分组，查询组中的用户名有哪些
SELECT GROUP_CONCAT(username),age,sex,addr FROM user1
GROUP BY sex;

# 配合聚合函数使用

  • COUNT():统计记录总数
  • SUM()：求和
  • MAX()：求最大值
  • MIN()：求最小值
  • AVG()：求平均值
SELECT COUNT(*) AS total_users FROM user1;
SELECT COUNT(id) FROM user1;

如果COUNT（字段名称),字段中的值为NULL，不统计进来,写COUNT(*) 统计NULL值

-- 按照sex分组，得到用户名详情，并且分别统计组中的总人数
SELECT sex,GROUP_CONCAT(username) AS usersDetail,COUNT(*) AS totalUsers FROM user1 GROUP BY sex;

-- 按照addr分组，得到用户名的详情，总人数，得到组中年龄的综合，年龄的最大值、最小值、平均值
SELECT addr,GROUP_CONCAT(username) AS usersDetail,
COUNT(*) AS totalUsers,
SUM(age) AS sum_age,
MAX(age) AS max_age,
MIN(age) AS min_age,
AVG(age) AS avg_age
FROM user1
GROUP BY addr;

# 配合WITH ROLLUP关键词

会在记录末尾添加一条记录，是上面所有记录的总和

SELECT GROUP_CONCAT(username) AS usersDetail,
COUNT(*) AS totalUsers
FROM user1
GROUP BY sex
WITH ROLLUP;

# 按照字段位置分组

SELECT addr,GROUP_CONCAT(username) AS usersDetail,
COUNT(*) AS totalUsers,
SUM(age) AS sum_age,
MAX(age) AS max_age,
MIN(age) AS min_age,
AVG(age) AS avg_age
FROM user1
GROUP BY 1;
-- GROUP BY 1表示按照addr分组

# HAVING子句，对分组结果进行二次筛选

-- 按照addr分组，统计总人数
SELECT addr,
GROUP_CONCAT(username) AS usersDetail,
COUNT(*) AS totalUsers
FROM user1
GROUP BY addr;

-- 对分组结果进行二次筛选，条件是组中总人数>=3
SELECT addr,
GROUP_CONCAT(username) AS usersDetail,
COUNT(*) AS totalUsers
FROM user1
GROUP BY addr
HAVING COUNT(*)>=3;

SELECT addr,
GROUP_CONCAT(username) AS usersDetail,
COUNT(*) AS totalUsers
FROM user1
GROUP BY addr
HAVING totalUsers>=3;

# ORDER BY 排序

ORDER BY 字段名称 ASC|DESC

SELECT id,username,age
FROM user1
ORDER BY id DESC;

-- 默认升序
SELECT id,username,age
FROM user1
ORDER BY age;

-- 按照多个字段排序
SELECT id,username,age
FROM user1
ORDER BY age ASC, id ASC;

-- 实现随机记录
SELECT id,username,age
FROM user1
ORDER BY RAND();

# LIMIT 限制结果集显示条数

-- 显示结果集的前几条记录
LIMIT row_count
-- 从offset开始，显示几条记录
-- offset从0开始
LIMIT offset,row_count

-- 显示结果集中前5条记录
SELECT id,username,age,sex
FROM user1
LIMIT 5;

SELECT id,username,age,sex
FROM user1
LIMIT 0,5;

-- 显示前3条记录
SELECT id,username,age,sex
FROM user1
LIMIT 0,3;

-- 分页
SELECT id,username,age,sex
FROM user1
LIMIT 0,3;

SELECT id,username,age,sex
FROM user1
LIMIT 3,3;

SELECT id,username,age,sex
FROM user1
LIMIT 6,3;

-- 更新user1表前三条记录，将age+5
UPDATE user1 SET age=age+5 LIMIT 3;

-- 删除user1表前三条记录
DELETE FROM user1 LIMIT 3;

# 完整select语句形式

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

# 多表查询

# 笛卡尔积形式

SELECT emp.id,emp.username,emp.age,dep.depName FROM emp,dep;

# 内连接形式（用的较多）

查询两个表中符合连接条件的记录

SELECT 字段名称,... FROM tbl_name1 [INNER]   JOIN tbl_name2 ON 连接条件;

SELECT e.id,e.username,e.age,d.depName 
FROM emp AS e
INNER JOIN dep AS d
ON e.depId=d.id;

SELECT p.id,p.productName,p.price,c.cateName,a.username,a.email
FROM products AS p
JOIN admin AS a
ON p.adminId=a.id
JOIN cate AS c
ON p.cateId=c.id
WHERE p.price<1000
ORDER BY p.price DESC
LIMIT 0,2;

# 外连接形式

# 左外连接

先显示左表全部记录，再去右表中查询符合条件的记录，不符合的以NULL代替

SELECT 字段名称,... FROM tbl_name1
LEFT [OUTER] JOIN tbl_name2
ON 条件;

SELECT e.id,e.username,e.age,d.depName 
FROM emp AS e
LEFT OUTER JOIN dep AS d
ON e.depId=d.id;

# 右外连接

先显示右表全部记录，再去左表中查询符合条件的记录，不符合的以NULL代替

SELECT 字段名称,... FROM tbl_name1
RIGHT [OUTER] JOIN tbl_name2
ON 条件;

SELECT e.id,e.username,e.age,d.depName 
FROM emp AS e
RIGHT OUTER JOIN dep AS d
ON e.depId=d.id;

# 外键约束

只有InnoDB存储引擎支持外键

# 创建外键

# 建表时指定外键

字表的外键字段和主表的主键字段类型要相似；如果是数值型要求一致，并且无符号也要一致；如果是字符型，要求类型一致，长度可以不同；

如果外键字段没有创建索引，MySQL会自动帮我们添加索引

子表的外键必须是父表的主键

[CONSTRAINT 外键名称] FOREIGN KEY(字段名称) REFERENCES 主表(字段名称);

CREATE TABLE news(
    id INT UNSIGNED AUTO_INCREMENT KEY,
    title VARCHAR(100) NOT NULL UNIQUE,
    content VARCHAR(1000) NOT NULL,
    cateId TINYINT UNSIGNED NOT NULL,
    FOREIGN KEY(cateId) REFERENCES news_cate(id)
)ENGINE=INNODB;

-- 添加外键名称
CREATE TABLE news(
    id INT UNSIGNED AUTO_INCREMENT KEY,
    title VARCHAR(100) NOT NULL UNIQUE,
    content VARCHAR(1000) NOT NULL,
    cateId TINYINT UNSIGNED NOT NULL,
    CONSTRAINT cateId_fk_newsCate FOREIGN KEY(cateId) REFERENCES news_cate(id)
)ENGINE=INNODB;

# 动态添加外键

ALTER TABLE tbl_name
ADD [CONSTRAINT 外键名称] FOREIGN KEY(外键字段) REFERENCES 主表(主键字段)

ALTER TABLE news
ADD FOREIGN KEY(cateId) REFERENCES news_cate(id);

ALTER TABLE news
ADD CONSTRAINT cateId_fk_newsCate FOREIGN KEY(cateId) REFERENCES news_cate(id);

动态添加外键之前表中的记录一定合法的记录，没有脏值，否则外键添加不成功。

# 动态删除外键

ALTER TABLE tbl_name
DROP FOREIGN KEY fk_name;

ALTER TABLE news
DROP FOREIGN KEY cateId_fk_newsCate;

# 外键约束的参照操作

  • CASCADE: 级联，从父表删除或更新，字表也跟着删除或更新
  • SET NULL: 从父表删除或更新记录，并设置子表的外键列为NULL
  • NO ACTION|RESTRICT: 拒绝对父表做更新或者删除操作
-- 指定级联操作 DELETE CASCADE UPDATE CASCADE

ALTER TABLE news
ADD FOREIGN KEY(cateId) REFERENCES news_cate(id)
ON DELETE CASCADE 
ON UPDATE CASCADE;

# 特殊形式的查询

# 子查询

将一个查询语句嵌套在另一个查询语句中 内层语句查询的结果可以作为外层语句查询的条件

SELECT 字段名称 FROM tbl_name
WHERE col_name=(SELECT col_name FROM tbl_name);

# 由IN引发的子查询

SELECT * FROM emp
WHERE depId IN (SELECT id FROM dep);

SELECT * FROM emp
WHERE depId NOT IN (SELECT id FROM dep);

# 由比较运算符引出的子查询

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

# 由EXISTS引发的子查询

-- 子查询有值（返回真）则前面语句执行
SELECT * FROM emp
WHERE EXISTS (SELECT depName from dep
WHERE id=10);

# ANY SOME ALL关键字子查询

-- >= 中 ANY 表示最小值
SELECT * FROM stu
WHERE score>=ANY(SELECT score FROM level);

-- >= 中 SOME 表示最小值
SELECT * FROM stu
WHERE score>=SOME(SELECT score FROM level);

-- >= 中 ALL 表示最大值
SELECT * FROM stu
WHERE score>=ALL(SELECT score FROM level);

# INSERT ... SELECT

插入另一张数据表的值

INSERT user1(username) 
SELECT username FROM user;

INSERT user2 SET username=(SELECT username FROM stu WHERE id=9);

-- 去掉字段重复值
SELECT DISTINCT(username) FROM user2;

# CREATE ... SELECT

创建表并写入数据

CREATE TABLE user1(
    id INT UNSIGNED AUTO_INCREMENT KEY,
    username VARCHAR(20)
) SELECT id,username FROM emp;

# CREATE ... LIKE

CREATE TABLE tbl_name LIKE tbl_name1;

# 联合查询

# UNION

合并时去除重复记录

SELECT 字段名称,... FROM tbl_name1
UNION
SELECT 字段名称,... FROM tbl_name2;

-- 将user1和user2数据合并到一起
SELECT * from user1
UNION
SELECT * FROM user2;

# UNION ALL

合并时不去除重复记录

SELECT 字段名称,... FROM tbl_name1
UNION ALL
SELECT 字段名称,... FROM tbl_name2;

# 自身连接查询

无限极分类的实现形式

-- 查询所有的分类信息，并且得到其父分类
SELECT s.id,s.cateName AS sCateName,p.cateName AS pCateName
FROM cate AS s
LEFT JOIN cate AS p
ON s.pId=p.id;

-- 查询所有的分类及其子分类
SELECT p.id,p.cateName AS pCateName,s.cateName AS sCateName
FROM cate AS s
RIGHT JOIN cate AS p
ON s.pId=p.id;

-- 查询所有的分类并且得到子分类的数目
SELECT p.id,p.cateName AS pCateName,COUNT(s.cateName) AS count
FROM cate AS s
RIGHT JOIN cate AS p
ON s.pId=p.id
GROUP BY p.cateName
ORDER BY id ASC;
上次更新: 2/13/2025, 3:29:47 AM

← 数值型 →