MySQL基础之数据管理

插入记录操作

添加记录：INSERT [INTO] tbl_name[(col_name,.…)] {VALUE|VALUES}(VALUES.….);
- 不指定具体的字段名称：INSERT tbl_name VALUE(value...)
列出指定字段：INSERT tbl_name(字段名称,...) VALUES(值,.….)
INSERT ...SET 的形式：INSERT tbl_name SET 字段名称=值,..;
INSERT...SELECT： INSERT tbl_name[(字段名称...)] SELECT 字段名称,....FROM tbl_name [WHERE 条件]
一次添加多条记录：lNSERT tbl_name[(字段名称,...)] VALUES(值,...),(值,..….),(值,...)

-- 测试添加记录
CREATE DATABASE IF NOT EXISTS king DEFAULT CHARACTER SET 'UTF8';
USE king;
CREATE TABLE IF NOT EXISTS user(
    id INT UNSIGNED AUTO_INCREMENT KEY COMMENT '编号',
    username VARCHAR(20) NOT NULL UNIQUE COMMENT '用户名',
    age TINYINT UNSIGNED DEFAULT 18 COMMENT '年龄',
    email VARCHAR(50) NOT NULL DEFAULT 'imooc@qq.com' COMMENT '邮箱'
)ENGINE=INNODB CHARSET=UTF8;
 SELECT DATABASE();
  SHOW TABLES;
  DESC USER;
-- 不指定具体的字段名称
INSERT user VALUE(1,'king',24,'382771946@qq.com');
INSERT user VALUES(NULL,'queen',25,'queen@qq.com');
INSERT user VALUES(DEFAULT,'lily',26,'lily@qq.com');
SELECT * FROM user;
-- 列出指定字段的形式
INSERT user(username,email) VALUES('rose','rose@qq.com');
INSERT user(age,email,id,username) VALUES(34,'imooc@qq.com',5,'imooc');
-- 一次插入 3 条记录
INSERT user VALUES(NULL,'a',DEFAULT,DEFAULT),
(NULL,'b',56,'b@qq.com'),
(NULL,'c',14,'c@qq.com');
-- INSERT ...SET 的形式
INSERT user SET username='d',age=45,email='d@qq.com';
-- INSERT SELECT
INSERT user(username) SELECT a FROM test;

修改和删除记录操作

修改记录：UPDATE tbl_name SET 字段名称=值,字段名称=值 [WHERE 条件]
- 如果不添加条件，整个表中的记录都会被更新
删除记录：DELETE FROM tbl_name [WHERE 条件]
- 如果不添加条件，表中所有记录都会被删除
- DELETE 清空数据表的时候不会重置 AUTO_INCREMENT 的值，可以通过 ALTER 语句将其重置为 1
- 彻底清空数据表：TRUNCATE [TABLE] tbl_name; 【清除表中所有记录、会重置 AUTO_INCREMENT 的值】
修改 AUTO_INCREMENT 的值： ALTER TABLE tbl_name AUTO_INCREMENT=值;

-- 测试更新语句
-- 修改第一个用户的信息 id=1
UPDATE user SET age=29 WHERE id=1;
-- 修改 id=3 的用户，username age email
UPDATE user SET age=47,email='lilys@qq.com',username='lilys' WHERE id=3;
-- 所有用户年龄+10
UPDATE user SET age=age+10;
-- 将 id<=5 的用户年龄改为-20，将邮箱改为默认值
UPDATE user SET age=age-20,email=DEFAULT WHERE id<=5;
-- 测试删除语句
-- 删除用户名为 king
DELETE FROM user WHERE username='king';
-- 删除年龄为 24 的用户
DELETE FROM user WHERE age=24;
-- 删除表中所有记录
DELETE FROM user;
INSERT user VALUES(NULL,'queen',25,'queen@qq.com');
INSERT user VALUES(DEFAULT,'lily',26,'lily@qq.com');
-- 自增从 1 开始
ALTER TABLE user AUTO_INCREMENT=1;

SELECT 语句的基本形式

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 tbl_name
库名.表名：SELECT 字段名称,... FROM db_name.tbl_name;
给字段起别名：SELECT 字段名称 [AS] 别名名称,... FROM db_name.tbl_name;
给数据表起别名：SELECT 字段名称 ,... FROM tbl_name [AS] 别名;
表名.字段名的：SELECT tbl_name.col_name,... FROM tbl_name;

-- 测试查询操作
CREATE TABLE user1(
id INT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20) NOT NULL UNIQUE COMMENT '编号',
age TINYINT UNSIGNED NOT NULL DEFAULT 18  COMMENT '年龄',
sex ENUM('男','女','保密') NOT NULL DEFAULT '保密' COMMENT '性别',
addr VARCHAR(20) NOT NULL DEFAULT '北京',
married TINYINT(1) NOT NULL DEFAULT 0 COMMENT '0 代表未结婚，1 代表已婚',
salary FLOAT(8,2) NOT NULL DEFAULT 0 COMMENT '薪水'
)ENGINE=INNODB CHARSET=UTF8;
INSERT user1 VALUES(1,'king',23,'男','北京',1,50000);
INSERT user1(username,age,sex,addr,married,salary) VALUES('queen',27,'女','上海',0,25000);
INSERT user1 SET username='imooc',age=31,sex='女',addr='北京',salary=40000;
INSERT user1 VALUES(NULL,'张三',38,'男','上海',0,15000),
(NULL,'张三风',38,'男','上海',0,15000),
(NULL,'张子怡',39,'女','北京',1,85000),
(NULL,'汪峰',42,'男','深圳',1,95000),
(NULL,'刘德华',58,'男','广州',0,115000),
(NULL,'吴亦凡',28,'男','北京',0,75000),
(NULL,'奶茶妹',18,'女','北京',1,65000),
(NULL,'刘嘉玲',36,'女','广州',0,15000);
-- 查询表中所有记录
SELECT * FROM user1;
-- username,addr,age
SELECT username,addr,age FROM user1;
-- 查询 king 数据库下 user1 表中的所有记录
SELECT * FROM king.user1;
-- 查询 user1 表中的 id 编号 username 用户名 sex 性别
SELECT id AS '编号',username AS '用户名', sex AS '性别'
FROM user1;
SELECT id age,age id,username FROM user1;
-- 给表起别名
SELECT id,username FROM user1 AS u;
-- 测试表名.字段名
SELECT user1.id,user1.username,user1.age FROM user1 ;
SELECT u.id,u.username,u.addr,u.sex FROM user1 AS u;

WHERE 条件筛选记录

-- 测试 WHERE 条件的比较运算符
-- 查询 id,username,age id=5 的用户
SELECT id,username,age FROM user1
WHERE id=5;
SELECT id,username,age FROM user1
WHERE id=50;
-- 添加 desc 字段 VARCHAR(100)
ALTER TABLE user1
ADD userDesc VARCHAR(100);
-- 更新 id<=9 的用户 userDesc='this is a test'
UPDATE user1 SET userDesc='this is a test'
WHERE id<=9;
-- 查询用户 userDesc 为 NULL 的用户
SELECT id,username,age,userDesc FROM user1
WHERE userDesc=NULL;
-- 检测 NULL 值
SELECT id,username,age,userDesc FROM user1
WHERE userDesc<=>NULL;
-- IS [NOT] NULL 检测 NULL 值
SELECT id,username,age,userDesc FROM user1
WHERE userDesc IS NULL;
-- 测试范围 BETWEEN AND
-- 查询年龄在 18~30 之间的用户
SELECT id,username,age,sex FROM user1
WHERE age BETWEEN 18 AND 30;
-- 查询薪水在 10000~50000 之间的用户
SELECT id,username,age,salary FROM user1
WHERE salary BETWEEN 10000 AND 50000;
-- 查询薪水不在 10000~50000 之间的用户
SELECT id,username,age,salary FROM user1
WHERE salary NOT BETWEEN 10000 AND 50000;
-- 测试指定集合 IN
-- 查询编号为 1,3,5,7,9
SELECT id,username,age FROM user1
WHERE id IN(1,3,5,7,9,29,45,78);
SELECT id,username,age FROM user1
WHERE username IN('king','queen','lily','rose');
-- 测试逻辑运算符
-- 查询性别为男并且年龄>=20 的用户
SELECT id,username,age,sex FROM user1
WHERE sex='男' AND age>=20;
-- id>=5 && age<=30
SELECT id,username,age,sex FROM user1
WHERE id>=5 AND age<=30;
SELECT id,username,age,sex FROM user1
WHERE id>=5 AND age<=30 AND sex='男';
-- 要求 sex='女' 并且 addr='北京'
SELECT id,username,age,sex,addr FROM user1
WHERE sex='女' AND addr='北京';
-- 查询薪水范围在 60000~10000 并且性别为男 addr='北京'
SELECT id,username,age,sex,salary,addr FROM user1
WHERE salary BETWEEN 60000 AND 100000 AND sex='男' AND addr='北京';
-- 查询 id=1 或者 用户名为 queen
SELECT id,username,age FROM user1
WHERE id=1 OR username='queen';
-- 测试模糊查询
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 '%三%';
-- 用户名中包含 n
SELECT id,username,age FROM user1
WHERE username LIKE '%in%';
-- 要求查询出姓张的用户
SELECT id,username,age FROM user1
WHERE username LIKE '张%';
-- 查询以风结尾的用户
SELECT id,username,age 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,sex FROM user1
WHERE username LIKE '张 _%';
SELECT id,username,age,sex FROM user1
WHERE username LIKE '%K%';--king

GROUP BY 对记录进行分组


-- 测试分组
-- 按照性别分组 sex
SELECT id,username,age,sex FROM user1
GROUP BY sex;
-- 按照 addr 分组
SELECT username,age,sex,addr FROM user1
GROUP BY addr;
-- 按照性别分组，查询组中的用户名有哪些
SELECT GROUP_CONCAT(username),age,sex,addr FROM user1
GROUP BY sex;
SELECT GROUP_CONCAT(username),age,sex,GROUP_CONCAT(addr) FROM user1
GROUP BY sex;
-- 测试 COUNT()
SELECT COUNT(*) FROM user1;
SELECT COUNT(id) FROM user1;
-- 按照 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;
-- 按照 sex 分组，统计组中总人数、用户名详情，得到薪水总和，薪水最大值、最小值、平均值
SELECT sex,
GROUP_CONCAT(username) AS usersDetail,
COUNT(*) AS totalUsers,
SUM(salary) AS sum_salary,
MAX(salary) AS max_salary,
MIN(salary) AS min_salary,
AVG(salary) AS avg_salary
FROM user1
GROUP BY sex;
--配合 WITH ROLLUP 关键使用 会在记录末尾添加一条记录，是上面所有记录的总和
SELECT GROUP_CONCAT(username) AS usersDetail,
COUNT(*) AS totalUsers
FROM user1
GROUP BY sex
WITH ROLLUP;
-- 按照字段的位置来分组
SELECT id,sex,
GROUP_CONCAT(username) AS usersDetail,
COUNT(*) AS totalUsers,
SUM(salary) AS sum_salary,
MAX(salary) AS max_salary,
MIN(salary) AS min_salary,
AVG(salary) AS avg_salary
FROM user1
GROUP BY 2;
-- 查询 age>=30 的用户并且按照 sex 分组
SELECT sex,GROUP_CONCAT(username) AS usersDetail,
COUNT(*) AS totalUsers
FROM user1
WHERE age>=30
GROUP BY sex;
-- 按照 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;
-- 按照 addr 分组，
SELECT addr,
GROUP_CONCAT(username) AS usersDetail,
COUNT(*) AS totalUsers,
SUM(salary) AS sum_salary,
MAX(salary) AS max_salary,
MIN(salary) AS min_salary,
AVG(salary) AS avg_salary
FROM user1
GROUP BY addr;
-- 要求平均薪水>=40000
SELECT addr,
GROUP_CONCAT(username) AS usersDetail,
COUNT(*) AS totalUsers,
SUM(salary) AS sum_salary,
MAX(salary) AS max_salary,
MIN(salary) AS min_salary,
AVG(salary) AS avg_salary
FROM user1
GROUP BY addr
HAVING avg_salary>=40000;

ORDER BY 实现排序效果

-- 测试排序
-- 按照 id 降序排列
SELECT id,username,age
FROM user1
ORDER BY id DESC;
-- 按照 age 升序
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
WHERE age>=30;
SELECT id,username,age
FROM user1
WHERE age>=30
ORDER BY age DESC;
-- 实现随机记录
SELECT id,username,age
FROM user1
ORDER BY RAND();

LIMT 限制结果集的显示条数

-- 测试 LIMIT 语句
-- 显示结果集的前 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 3,3;--3 下标 3 条数据
-- 更新前 3 条记录，将 age+5
UPDATE user1 SET age=age+5 LIMIT 3;
-- 按照 id 降序排列，更新前三条记录，将 age-10
UPDATE user1 SET age=age-10 ORDER BY id DESC LIMIT 3;
-- 删除前三条记录
DELETE FROM user1
LIMIT 3;
--按照 id 降序排列,删除前三条记录
DELETE FROM user1
ORDER BY id DESC
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;
--对分组结果进行二次筛选
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;
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;
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;

MySQL 中的多表联查

内连接用的多

CREATE DATABASE IF NOT EXISTS test2 DEFAULT CHARACTER SET 'UTF8';
USE test2;
CREATE TABLE emp(
id INT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20) NOT NULL UNIQUE COMMENT '编号',
age TINYINT UNSIGNED NOT NULL DEFAULT 18  COMMENT '年龄',
sex ENUM('男','女','保密') NOT NULL DEFAULT '保密' COMMENT '性别',
addr VARCHAR(20) NOT NULL DEFAULT '北京',
depId TINYINT UNSIGNED NOT NULL COMMENT '部门对应的编号'
)ENGINE=INNODB CHARSET=UTF8;
INSERT emp(username,age,depId) VALUES('king',24,1),
('queen',25,2),
('imooc',26,1),
('lily',27,1),
('rose',28,3),
('john',29,3);
INSERT emp(username,age,depId)  VALUES('测试用户',39,6);
CREATE TABLE dep(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
depName VARCHAR(50) NOT NULL UNIQUE,
depDesc VARCHAR(100) NOT NULL DEFAULT ''
)ENGINE=INNODB CHARSET=UTF8;
INSERT dep(depName,depDesc) VALUES('PHP 教学部','研发 PHP 课件'),
('JAVA 教学部','研发 JAVA 课件'),
('WEB 前端教学部','研发 WEB 前端课件'),
('IOS 教学部','研发 IOS 课件');
-- 需求：查询 emp id username age  部门名称 dep depName
SELECT emp.id,emp.username,emp.age,dep.depName FROM emp,dep;
SELECT e.id,e.username,e.age,d.depName
FROM emp AS e
INNER JOIN dep AS d
ON e.depId=d.id;
-- 查询 emp id username age addr dep id depName depDesc
SELECT e.id,e.username,e.age,e.addr,
d.id,d.depName,d.depDesc
FROM dep AS d
JOIN emp AS e
ON d.id=e.depId;
-- 测试左外连接
SELECT e.id,e.username,e.age,d.depName,d.depDesc
FROM emp AS e
LEFT OUTER JOIN dep AS d
ON e.depId=d.id;
-- 测试右外连接
SELECT e.id,e.username,e.age,d.depName,d.depDesc
FROM emp AS e
RIGHT JOIN dep AS d
ON e.depId=d.id;

多表联查的操作

-- 创建管理员表
CREATE TABLE user(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20) NOT NULL UNIQUE,
email VARCHAR(50) NOT NULL DEFAULT '382771946@qq.com',
proName VARCHAR(10) NOT NULL DEFAULT '北京'
);
INSERT user(username,proName) VALUES('a','北京'),
('b','哈尔滨'),
('c','上海'),
('d','深圳'),
('e','广州'),
('f','重启');
-- 创建省份表
CREATE TABLE provinces(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
proName VARCHAR(10) NOT NULL UNIQUE
);
INSERT provinces(proName) VALUES('北京'),
('上海'),
('深圳');
--
CREATE TABLE user(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20) NOT NULL UNIQUE,
email VARCHAR(50) NOT NULL DEFAULT '382771946@qq.com',
proId TINYINT UNSIGNED NOT NULL
);
INSERT user(username,proId) VALUES('a',1);
INSERT user(username,proId) VALUES('b',1);
INSERT user(username,proId) VALUES('c',1);
INSERT user(username,proId) VALUES('d',2);
INSERT user(username,proId) VALUES('e',3);
INSERT user(username,proId) VALUES('f',1);
INSERT user(username,proId) VALUES('g',1);
-- 查询 user id ,username provinces proName
SELECT u.id,u.username,p.proName
FROM user AS u
JOIN provinces AS p
ON u.proId=p.id;
--四表之间三表。。联查
-- 创建省份表
CREATE TABLE provinces(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
proName VARCHAR(10) NOT NULL UNIQUE
);
-- 管理员 admin id username email proId
CREATE TABLE admin(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20) NOT NULL UNIQUE,
email VARCHAR(50) NOT NULL DEFAULT '382771946@qq.com',
proId TINYINT UNSIGNED NOT NULL
);
INSERT admin(username,proId) VALUES('king',1);
INSERT admin(username,proId) VALUES('queen',2);
-- 商品分类 cate id cateName cateDesc
CREATE TABLE cate(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
cateName VARCHAR(50) NOT NULL UNIQUE,
cateDesc VARCHAR(100) NOT NULL DEFAULT ''
);
INSERT cate(cateName) VALUES('母婴');
INSERT cate(cateName) VALUES('服装');
INSERT cate(cateName) VALUES('电子');
-- 商品表 products id productName, price,cateId
CREATE TABLE products(
id INT UNSIGNED AUTO_INCREMENT KEY,
productName VARCHAR(50) NOT NULL UNIQUE,
price FLOAT(8,2) NOT NULL DEFAULT 0,
cateId TINYINT UNSIGNED NOT NULL,
adminId TINYINT UNSIGNED NOT NULL
);
INSERT products(productName,price,cateId,adminId)
VALUES('iphone9',9888,3,1),
('adidas',388,2,2),
('nike',888,2,2),
('奶瓶',288,1,1);
-- 查询 products id productName price --- cate cateName
SELECT p.id,p.productName,p.price,c.cateName
FROM products AS p
JOIN cate AS c
ON p.cateId=c.id;
-- 查询管理员 id username email -- provinces proName
SELECT a.id,a.username,a.email,p.proName
FROM admin AS a
JOIN provinces AS p
ON a.proId=p.id;
-- 查询 products id productName price
-- cate cateName
-- admin username email
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;
-- products id productName price
-- cate cateName
-- admin username email
-- provinces proName

外键约束的使用

-- 测试外键
-- 新闻分类表 news_cate
CREATE TABLE news_cate(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
cateName VARCHAR(50) NOT NULL UNIQUE,
cateDesc VARCHAR(100) NOT NULL DEFAULT ''
);
INSERT news_cate(cateName) VALUES('国内新闻'),
('国际新闻'),
('娱乐新闻'),
('体育新闻');
-- 新闻表 news
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
);
INSERT news(title,content,cateId) VALUES('a1','aaaa1',1),
('a2','aaaa2',1),
('a3','aaaa3',4),
('a4','aaaa4',2),
('a5','aaaa5',3);
-- 查询 news id title content
-- news_cate cateName
SELECT n.id,n.title,n.content,c.cateName
FROM news AS n
JOIN news_cate AS c
ON n.cateId=c.id;
INSERT news(title,content,cateId) VALUES('a6','aaaa6',45);
-- ------------添加外键
-- 新闻分类表 news_cate
CREATE TABLE news_cate(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
cateName VARCHAR(50) NOT NULL UNIQUE,
cateDesc VARCHAR(100) NOT NULL DEFAULT ''
)ENGINE=INNODB;
-- 新闻表 news
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;
INSERT news_cate(cateName) VALUES('国内新闻'),
('国际新闻'),
('娱乐新闻'),
('体育新闻');
INSERT news(title,content,cateId) VALUES('a1','aaaa1',1),
('a2','aaaa2',1),
('a3','aaaa3',4),
('a4','aaaa4',2),
('a5','aaaa5',3);
-- 测试非法记录
INSERT news(title,content,cateId) VALUES('b1','bbbb1',8);
-- 测试删除父表中的记录 和删除父表
DELETE FROM news_cate WHERE id=1;
UPDATE news_cate SET id=10 WHERE id=1;
INSERT news_cate(cateName) VALUES('教育新闻');
-- 将教育新闻 改成教育
UPDATE news_cate SET cateName='教育' WHERE id=5;
UPDATE news_cate SET id=50 WHERE cateName='教育';

动态创建外键及删除外键操作

RESTRICT 同 no action, 都是立即检查外键约束，拒绝对父表做更新或者删除操作

-- ------添加外键名称
-- 新闻分类表 news_cate
CREATE TABLE news_cate(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
cateName VARCHAR(50) NOT NULL UNIQUE,
cateDesc VARCHAR(100) NOT NULL DEFAULT ''
)ENGINE=INNODB;
-- 新闻表 news
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 news
DROP FOREIGN KEY cateId_fk_newsCate;
-- 添加外键
ALTER TABLE news
ADD FOREIGN KEY(cateId) REFERENCES news_cate(id);
-- 删除外键
ALTER TABLE news
DROP FOREIGN KEY news_ibfk_1;
ALTER TABLE news
ADD CONSTRAINT cateId_fk_newsCate FOREIGN KEY(cateId) REFERENCES news_cate(id);
INSERT news_cate(cateName) VALUES('国内新闻'),
('国际新闻'),
('娱乐新闻'),
('体育新闻');
INSERT news(title,content,cateId) VALUES('a1','aaaa1',1),
('a2','aaaa2',1),
('a3','aaaa3',4),
('a4','aaaa4',2),
('a5','aaaa5',8);--删除了
DELETE FROM news WHERE id=5;
ALTER TABLE news
ADD FOREIGN KEY(cateId) REFERENCES news_cate(id);
SHOW CREATE TABLE news;
-- 指定级联操作 DELETE CASCADE UPDATE CASCADE
ALTER TABLE news
ADD FOREIGN KEY(cateId) REFERENCES news_cate(id)
ON DELETE CASCADE ON UPDATE CASCADE;
SHOW CREATE TABLE news;-- 多了一个 ON DELETE CASCADE ON UPDATE CASCADE
SELECT * FROM news_cate;
SELECT * FROM news;
UPDATE news_cate SET id=11 WHERE id=1;
SELECT * FROM news_cate;-- 1 变为 11 news 第 1、2 的 cateId 都变了

子查询的使用

-- 测试子查询
-- 测试由 IN 引发的子查询
SELECT * FROM emp
WHERE depId IN (SELECT id FROM dep);
SELECT * FROM emp
WHERE depId NOT IN (SELECT id FROM dep);
-- 学员 stu
CREATE TABLE stu(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20) NOT NULL UNIQUE,
score TINYINT UNSIGNED NOT NULL
);
INSERT stu(username,score) VALUES('king',95),
('queen',75),
('zhangsan',69),
('lisi',78),
('wangwu',87),
('zhaoliu',88),
('tianqi',98),
('ceshi',99),
('tiancai',50);
-- 分数级别 level
CREATE TABLE level(
id tinyint UNSIGNED AUTO_INCREMENT KEY,
score TINYINT UNSIGNED COMMENT '分数'
);
INSERT level(score) VALUES(90),(80),(70);
-- 查询出成绩优秀的学员
SELECT score FROM level WHERE id=1;
SELECT id,username,score FROM stu
WHERE score>=(SELECT score FROM level WHERE id=1);
-- 查询出没有得到评级的学员
SELECT id,username,score FROM stu
WHERE score<=(SELECT score FROM level WHERE id=3);
-- 由 EXISTS 引发的子查询
SELECT * FROM emp WHERE EXISTS (SELECT depName FROM dep WHERE id=10);
SELECT * FROM emp WHERE EXISTS (SELECT depName FROM dep WHERE id=1);

带有 ANY、SOME、ALL 关键字的子查询

-- 带有 ANY SOME ALL 关键字的子查询
-- 大于里面的最小值
SELECT * FROM stu
WHERE score>= ANY(SELECT score FROM level);
SELECT * FROM stu
WHERE score>= SOME(SELECT score FROM level);
SELECT * FROM stu
WHERE score>= ALL(SELECT score FROM level);
SELECT * FROM stu
WHERE score< ALL(SELECT score FROM level);
SELECT * FROM stu
WHERE score=ANY(SELECT score FROM level);
SELECT * FROM stu
WHERE score!=ALL(SELECT score FROM level);

联合查询的使用

-- 创建一个 user1 表,id username
CREATE TABLE user1(
    id int UNSIGNED AUTO_INCREMENT KEY,
    username VARCHAR(20)
)SELECT id,username FROM emp;
DESC user1;
SELECT * FROM user1;
-- 将 user 表中的用户名写入到 user1 表中
INSERT user1(username) SELECT username FROM user;
CREATE TABLE user2 LIKE user1;
INSERT user2 SELECT id,username FROM user1;
SELECT * FROM user1;
SELECT * FROM STU;
-- 将 stu 表中的 tiancai 用户名添加到 user2 表中,user2 和 user1 一样的
INSERT user2 SET username=(SELECT username FROM stu WHERE id=9);
-- 去掉字段的重复值
SELECT DISTINCT(username) FROM user2;
-- 将 user1 和 user2 数据合并到一起
SELECT * FROM user1
UNION
SELECT * FROM user2;

无限级分类数据表的设计及实现

自身连接查询无限级分类的实现形式

-- 测试自身连接
CREATE TABLE cate(
    id SMALLINT UNSIGNED AUTO_INCREMENT KEY,
    cateName VARCHAR(100) NOT NULL UNIQUE,
    pId SMALLINT UNSIGNED NOT NULL DEFAULT 0
);
INSERT cate(cateName,pId) VALUES('服装',0);
INSERT cate(cateName,pId) VALUES('数码',0);
INSERT cate(cateName,pId) VALUES('箱包',0);
INSERT cate(cateName,pId) VALUES('男装',1);
INSERT cate(cateName,pId) VALUES('女装',1);
INSERT cate(cateName,pId) VALUES('内衣',1);
INSERT cate(cateName,pId) VALUES('电视',2);
INSERT cate(cateName,pId) VALUES('冰箱',2);
INSERT cate(cateName,pId) VALUES('洗衣机',2);
INSERT cate(cateName,pId) VALUES('爱马仕',3);
INSERT cate(cateName,pId) VALUES('LV',3);
INSERT cate(cateName,pId) VALUES('GUCCI',3);
INSERT cate(cateName,pId) VALUES('夹克',4);
INSERT cate(cateName,pId) VALUES('衬衫',4);
INSERT cate(cateName,pId) VALUES('裤子',4);
INSERT cate(cateName,pId) VALUES('液晶电视',10);
INSERT cate(cateName,pId) VALUES('等离子电视',10);
INSERT cate(cateName,pId) VALUES('背投电视',10);
-- 查询所有的分类信息，并且得到其父分类
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;
sCate 表
id   | cateName       | pId |
+----+-----------------+-----+
|  1 | 服装            |   0 |
|  2 | 数码            |   0 |
|  3 | 玩具            |   0 |
|  4 | 男装            |   1 |
|  5 | 女装            |   1 |
|  6 | 内衣            |   1 |
| 10 | 电视            |   2 |
| 11 | 冰箱            |   2 |
| 12 | 洗衣机          |   2 |
| 13 | 爱马仕          |   3 |
| 14 | LV             |   3 |
| 15 | GUCCI          |   3 |
| 16 | 夹克            |   4 |
| 17 | 衬衫            |   4 |
| 18 | 裤子            |   4 |
| 19 | 液晶电视         |  10 |
| 20 | 等离子电视       |  10 |
| 21 | 背投电视         |  10 |
pCate
id | cateName        | pId |
+----+-----------------+-----+
|  1 | 服装            |   0 |
|  2 | 数码            |   0 |
|  3 | 玩具            |   0 |
|  4 | 男装            |   1 |
|  5 | 女装            |   1 |
|  6 | 内衣            |   1 |
| 10 | 电视            |   2 |
| 11 | 冰箱            |   2 |
| 12 | 洗衣机          |   2 |
| 13 | 爱马仕          |   3 |
| 14 | LV              |   3 |
| 15 | GUCCI           |   3 |
| 16 | 夹克            |   4 |
| 17 | 衬衫            |   4 |
| 18 | 裤子            |   4 |
| 19 | 液晶电视        |  10 |
| 20 | 等离子电视      |  10 |
| 21 | 背投电视        |  10  |

插入记录操作

修改和删除记录操作

SELECT 语句的基本形式

WHERE 条件筛选记录

GROUP BY 对记录进行分组

ORDER BY 实现排序效果

LIMT 限制结果集的显示条数

MySQL 中的多表联查

多表联查的操作

外键约束的使用

动态创建外键及删除外键操作

子查询的使用

带有 ANY、SOME、ALL 关键字的子查询

联合查询的使用

无限级分类数据表的设计及实现

Hi，您需要填写昵称和邮箱！