文章目录
数据的操作DML
重命名数据表
-- 表的格式:
#CREATE TABLE [IF NOT EXISTS] tbl_name(
#字段名称 字段类型 [UNSIGNED|ZEROFILL] [NOT NULL] [DEFAULT 默认值][[PRIMARY] KEY| UNIQUE [KEY]] [AUTO_INCREMENT]
#)ENGINE=INNODB CHARSET=UTF8 AUTO_INCREMENT=100;
-- 创建用户表
CREATE TABLE IF NOT EXISTS user10(
id SMALLINT UNSIGNED KEY AUTO_INCREMENT, #一般都是固定的
username VARCHAR(20) NOT NULL UNIQUE,
password CHAR(32) NOT NULL,
email VARCHAR(50) NOT NULL DEFAULT '1641924500@qq.com',
age TINYINT UNSIGNED DEFAULT 18,
sex ENUM('男','女','保密') DEFAULT '保密',
addr VARCHAR(200) NOT NULL DEFAULT '北京',
salary FLOAT(6,2),
pubTime INT UNSIGNED,
face CHAR(100) NOT NULL DEFAULT 'default.jpg' #头像的意思,一般都是传路径,所以100就够了
);
-- 修改表名,将user10重命名成user11
ALTER TABLE user10 RENAME TO user11;
ALTER TABLE user11 RENAME AS user10;
ALTER TABLE user11 RENAME user11; #TO可以省略
RENAME TABLE user11 TO user10; #TO不可以省略
添加和删除字段
-- 测试添加card字段CHAR(18),以user10为例
ALTER TABLE user10 ADD card CHAR(18); #默认添加到末尾
ALTER TABLE user10 ADD test1 VARCHAR(100) NOT NULL UNIQUE;
ALTER TABLE user10 ADD test2 VARCHAR(20) NOT NULL FIRST; #加到表的第一个位置上
ALTER TABLE user10 ADD test3 INT NOT NULL DEFAULT 100 AFTER username; #添加到username字段之后,用AFTER
-- 选中一次表,完成多个操作
ALTER TABLE user10
ADD test4 INT NOT NULL DEFAULT 123 AFTER password,
ADD test5 FLOAT(6,2) FIRST,
ADD test6 SET('A','B','C');
-- 删除指定字段
-- 例如删除表名为user10的test6字段
ALTER TABLE user10 DROP test6;
-- 一次选中表,删除多个字段,例如一次删除test2,test3,test4,test5
ALTER TABLE user10
DROP test2,
DROP test3,
DROP test4,
DROP test5;
-- 添加test字段删除addr字段
ALTER TABLE user10
ADD test INT UNSIGNED NOT NULL DEFAULT 10 AFTER sex,
DROP addr;
修改字段
-- 修改字段MODIFY
-- 例如将email VARCHAR(50)修改成VARCHAR(200)
ALTER TABLE user10 MODIFY email VARCHAR(200); #但是没写完整性约束条件,所以Null的位置从NO变成了YES
#如果想保留完整性约束条件,进行如下操作:就是都写上
ALTER TABLE user10 MODIFY email VARCHAR(50) NOT NULL DEFAULT '1641924500@qq.com';
-- 将card字段移动到test之后
ALTER TABLE user10 MODIFY card CHAR(18) AFTER test;
-- 将test字段修改为CHAR(32) NOT NULL DEFAULT '123' 并且移动到第一个位置上
ALTER TABLE user10 MODIFY test CHAR(32) NOT NULL DEFAULT '123' FIRST;
-- 修改字段名称 CHANGE
-- 将test字段改为test1
ALTER TABLE user10 CHANGE test test1 CHAR(32) NOT NULL DEFAULT '123';
ALTER TABLE user10 CHANGE test1 test VARCHAR(200) NOT NULL AFTER username;
ALTER TABLE user10 CHANGE test test INT;
添加和删除默认值
-- 添加默认值SET DEFAULT
CREATE TABLE IF NOT EXISTS user11(
id TINYINT UNSIGNED KEY AUTO_INCREMENT,
username VARCHAR(20) NOT NULL UNIQUE,
age TINYINT UNSIGNED
);
ALTER TABLE user11 ALTER age SET DEFAULT 18;
ALTER TABLE user11 ADD email VARCHAR(50);
ALTER TABLE user11 ALTER email SET DEFAULT '1641924500@qq.com';
-- 删除默认值DROP DEFAULT
ALTER TABLE user11 ALTER age DROP DEFAULT;
ALTER TABLE user11 ALTER email DROP DEFAULT;
添加删除主键
-- 添加主键
CREATE TABLE IF NOT EXISTS test13(
id INT
);
ALTER TABLE test13 ADD PRIMARY KEY(id);
CREATE TABLE IF NOT EXISTS test14(
id INT,
card CHAR(18),
username VARCHAR(20) NOT NULL
);
ALTER TABLE test14 ADD PRIMARY KEY(id,card); #添加复合主键
-- 删除主键
-- 例如删除test13表的主键
ALTER TABLE test13 DROP PRIMARY KEY;
ALTER TABLE test14 DROP PRIMARY KEY; #复合主键也被删除了
ALTER TABLE test13 ADD CONSTRAINT symbol PRIMARY KEY index_type(id); #完整形式,添加主键
-- 可能遇到的问题:
CREATE TABLE IF NOT EXISTS test15(
id INT UNSIGNED KEY AUTO_INCREMENT
);
# ALTER TABLE test15 DROP PRIMARY KEY; #报错!因为一个表只能有一个自增长,有自增长的必须是主键,故不能删除,要先把自增长改掉,如下:
ALTER TABLE test15 MODIFY id INT UNSIGNED; #这样就不是自增长了,就可以删除主键了
ALTER TABLE test15 DROP PRIMARY KEY;
添加删除唯一索引
-- 添加唯一索引
CREATE TABLE IF NOT EXISTS user12(
id TINYINT UNSIGNED KEY AUTO_INCREMENT,
username VARCHAR(20) NOT NULL,
card CHAR(18) NOT NULL,
test VARCHAR(20) NOT NULL,
test1 VARCHAR(32) NOT NULL
);
ALTER TABLE user12 ADD UNIQUE(username); #将username设为唯一性索引
ALTER TABLE user12 ADD CONSTRAINT symbol UNIQUE KEY uni_card(card); #完整形式,注意顺序
ALTER TABLE user12 ADD CONSTRAINT symbol UNIQUE INDEX mulUni_test_test1(test,test1); #复合索引
-- 删除唯一索引
ALTER TABLE user12 DROP INDEX username;
ALTER TABLE user12 DROP KEY uni_card; #另一种写法
ALTER TABLE user12 DROP KEY mulUni_test_test1; #删除复合索引
-- 修改表的存储引擎为MyISAM
ALTER TABLE user12 ENGINE=MyISAM;
ALTER TABLE user12 ENGINE=INNODB;
-- 设置自增长的值,要保证表中已经有字段是自增长的
ALTER TABLE user12 AUTO_INCREMENT=100;
删除数据表
-- 删除数据表
DROP TABLE user12;
DROP TABLE IF EXISTS user12; #不会报错,但是会出现WARNINGS
DROP TABLE IF EXISTS user10,user11,user9; #删除三张表
DROP TABLE IF EXISTS user7,user123; #如果删除一个不存在的,会出现warning,但是user7也会被删除,对user7的删除没有影响
插入记录
-- 测试插入记录INSERT
CREATE TABLE IF NOT EXISTS user(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20) NOT NULL UNIQUE,
password CHAR(32) NOT NULL,
email VARCHAR(50) NOT NULL DEFAULT '1641924500@qq.com',
age TINYINT UNSIGNED DEFAULT 18
);
INSERT INTO user VALUES(1,'KING','KING','KING@QQ.com',20); #INTO可以省略
INSERT user VALUE(2,'queen','queen','queen@qq.com',30); #VALUE或VALUES均可
-- 列出指定字段
INSERT user(username,password) VALUES('A','AAA');
INSERT user(password,username) VALUES('BBB','B'); #顺序要对应!别的字段没传值就是默认值
INSERT user(username,id,password,email,age) VALUES('C',55,'CCC','CCC@QQ.COM',DEFAULT);
-- 同时插入多条记录
INSERT user VALUES(6,'D','DDD','DDD@QQ.COM',35),
(8,'E','EEE','EEE@QQ.COM',9),
(18,'F','FFF','FFF@QQ.COM',32);
-- 通过SET形式插入记录
INSERT user SET id=98,username='test',password='1234556',email='123@qq.com',age=18;
INSERT user SET username='maizi',password='maizixueyuan';
-- 将查询结果插入表中,将其他表中数据插入表中
CREATE TABLE IF NOT EXISTS testUser(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20) NOT NULL UNIQUE
);
INSERT testUser SELECT id,username FROM user; #user表中的id和username的数据插入到表testUser中的id和username中
#要保证字段一致,数量一致,否则报错
INSERT testUser(username) SELECT usesrname FROM user; #只把表user中的username数据插入到表testUser中的username中
更新和删除记录
-- 更新数据
-- 注:如果不加WHERE条件,表中所有数据都会更新
-- 将用户表中所有的用户年龄更新为15
UPDATE user SET age=5;
UPDATE user SET age=20,email='123@qq.com';
-- 将第一条记录的password,email,age改变,利用主键
UPDATE user SET password='hj123',email='hj@qq.com',age=99
WHERE id=1;
UPDATE user SET age=age-5 WHERE id>=3; #加条件
UPDATE user SET age=DEFAULT WHERE username='A';
-- 删除数据 #一定要谨慎!!!!
-- 例如删除testUser表中的数据
DELETE FROM testUser;
-- 删除user表中id为1的用户
DELETE FROM testUser WHERE id=1;
-- 但是没删掉AUTO_INCREMENT的值,需要重置
-- 彻底清空数据表:删除记录并重置自增长的值
TRUNCATE TABLE user;#TABLE可以不加,但不能带WHERE条件
查询数据DQL
查询表达式
-- 查询数据操作DQL
-- 查询记录
SELECT * FROM user;
SELECT user.* FROM user; #*代表所有列,*所在的位置应该写你要查询的字段
-- 查询某些字段,显示时按你查询的顺序显示,多个字段之间以逗号,分隔
SELECT id,username FROM user;
-- 查询表来自于哪个数据库:形式:库名.表名,即db_name.tbl_name
SELECT id,username FROM maizi1.user;
-- 字段来自于哪张表
SELECT user.id,user.username FROM maizi1.user; #先是表名.字段名,后来是库名.表名
-- 给表名起别名
SELECT id,username FROM user AS u; #给表名user起个别名u,AS可以省略,最好不要省略
SELECT u.id,u.username,u.email FROM user AS u;
-- 给字段起别名
SELECT id AS '编号',username AS '用户名',email AS '邮箱' FROM user;
-- 表名和字段名都起了别名
SELECT u.id AS id1,u.username AS n,u.email AS e FROM user AS u;
带条件的查询
-- WHERE条件
-- 查询编号为1的用户
SELECT id,username,email FROM user WHERE id=1;
SELECT id,username,email FROM user WHERE username='hj'; #查不到就为空empty
SELECT * FROM user WHERE id>=5;
-- 查询编号不为1的用户
SELECT * FROM user WHERE id!=1;
SELECT * FROM user WHERE id<>1;
-- 添加age1字段
ALTER TABLE user ADD age1 TINYINT UNSIGNED DEFAULT 18;
INSERT user(username,password,email,age,age1)
VALUES('test1','test','12@qq.com',15,NULL);
-- 查询表中记录age1值为NULL
SELECT * FROM user WHERE age1<=>NULL; #不能用=
SELECT * FROM user WHERE age1<=>18;
-- IS NULL 或者IS NOT NULL
SELECT * FROM user WHERE age IS NOT NULL;
SELECT * FROM user WHERE age IS NULL;
范围查询
-- 筛选某一范围 BETWEEN AND
-- 查询编号在3-10之间的用户
SELECT * FROM user WHERE id BETWEEN 3 AND 10;
SELECT * FROM user WHERE id NOT BETWEEN 3 AND 10;
-- 筛选指定集合 IN 或NOT IN
-- 查询编号为1,3,5,9,11,13,100的用户
SELECT * FROM user WHERE id IN(1,3,5,7,9,11,13,100); #没有的话就查不出来,有哪个编号就能查出来哪个编号
-- 查询age为18和20的用户
SELECT * FROM user WHERE age IN(18,20);
-- 查询字符串
-- 查询用户名为queen和A的用户
SELECT * FROM user WHERE username IN('queen','A'); #大小写均可,例如queen写成QUeen查询结果是一样的
SELECT * FROM user WHERE username NOT IN('queen','A');
模糊查询
-- 模糊查询LIKE 、NOT LIKE
-- %:代表0个1个或者多个任意字符
-- _:代表1个任意字符
-- 查询含st的用户
SELECT * FROM user WHERE username LIKE '%st%';
-- 查询以te开头的用户
SELECT * FROM user WHERE username LIKE 'te%';
SELECT * FROM user WHERE username LIKE '%'; #即所有用户
-- 查询用户名为4位的用户
SELECT * FROM user WHERE username LIKE '____'; #4个下划线
-- 用户名_i%
SELECT * FROM user WHERE username LIKE '_I%'; #I前面有一位的,后面无所谓
SELECT * FROM user WHERE username NOT LIKE '_I%'; #取反
分组查询
-- 对记录按某些字段进行分组
-- GROUP BY
-- 按照用户的age进行分组
SELECT * FROM user GROUP BY age; #同一个age的只会显示第一个记录
-- 动态添加字段
-- 向用户表中添加性别字段
ALTER TABLE user ADD sex ENUM('男','女','保密');
UPDATE user SET sex='男' WHERE id IN(1,3,6,18,98,100);
UPDATE user SET sex='女' WHERE id IN(2,4,8);
UPDATE user SET sex='保密' WHERE id IN(55,99);
-- 按照性别分组
SELECT * FROM user GROUP BY sex;
-- 按照字段位置分组
SELECT * FROM user GROUP BY 6; #按第六个字段分类
-- 按照多个字段分组
SELECT * FROM user GROUP BY sex,age; #先按sex分,再按age分
-- 查询编号>=5的用户,并且按照sex分组
-- 规则:先写WHERE条件,再进行分组
SELECT * FROM user WHERE id>=5 GROUP BY sex;
分组查询配合聚合函数
-- 查询id,sex,用户名详情,按照sex分组
SELECT id,sex,GROUP_CONCAT(username) FROM user GROUP BY sex;
-- 查询email,sex详情,用户名详情,按照email来分
SELECT email,GROUP_CONCAT(username),GROUP_CONCAT(sex) FROM user GROUP BY email;
UPDATE user SET age=11 WHERE id=1;
UPDATE user SET age=23 WHERE id=2;
UPDATE user SET age=24 WHERE id=3;
UPDATE user SET age=45 WHERE id=4;
UPDATE user SET age=90 WHERE id=6;
UPDATE user SET age=65 WHERE id=8;
UPDATE user SET age=78 WHERE id=18;
UPDATE user SET age=35 WHERE id=55;
UPDATE user SET age=64 WHERE id=98;
UPDATE user SET age=22 WHERE id=99;
-- 查询编号,sex,用户名详情以及组中总人数,按照sex分组
SELECT id,sex,GROUP_CONCAT(username) AS users, COUNT(*) AS totalUsers FROM user GROUP BY sex;
#即查询的是男、女、保密都有哪个用户,每个性别的总人数
-- 统计表中所有记录
SELECT COUNT(*) AS totalUsers FROM user; #一共多少条记录
SELECT COUNT(id) AS totalUsers FROM user; #统计id字段有多少个值,即表中有多少记录,一样的
-- COUNT(字段)不统计NULL值
SELECT COUNT(age1) AS totalUsers FROM user; #NULL不算数
-- 查询编号,性别,用户名详情,组中总人数,组中最大年龄,最小年龄,平均年龄,年龄总和,并按照性别来分组
SELECT id,sex,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 user
GROUP BY sex WITH ROLLUP;
#加WITH ROLLUP末尾多一行记录,id为一共多少个用户,totalUsers为以上的总和,max_age为组的最大年龄,min_age为组的最小年龄,以此类型
#即对组取命令
HAVING语句对分组结果进行二次筛选
-- 对分组之后的结果进行二次筛选
-- HAVING子句
-- 只能使用到分组之后
-- 查询性别,用户名详情,组中总人数,最大年龄,年龄总和,按照性别分组
SELECT sex,GROUP_CONCAT(username) AS users,
COUNT(*) AS totalUsers,
MAX(age) AS max_age,
SUM(age) AS sum_age
FROM user
GROUP BY sex #要对分组结果进行二次筛选,则此处不能加分号;,否则报错
-- 查询组中人数大于2的
HAVING COUNT(*)>2;
-- 查询组中人数大于2并且最大年龄大于66的
SELECT sex,GROUP_CONCAT(username) AS users,
COUNT(*) AS totalUsers,
MAX(age) AS max_age,
SUM(age) AS sum_age
FROM user
GROUP BY sex
HAVING COUNT(*)>2 AND MAX(age)>66; #用AND连接
-- 查询编号大于等于2的用户
SELECT sex,GROUP_CONCAT(username) AS users,
COUNT(*) AS totalUsers,
MAX(age) AS max_age,
SUM(age) AS sum_age
FROM user
WHERE id>=2 #在此加条件
GROUP BY sex
HAVING COUNT(*)>2 AND MAX(age)>66;
ORDER BY对查询结果排序
-- 测试ORDER BY
-- 按照id降序排列DESC 默认的是ASC
SELECT * FROM user ORDER BY id; #升序,ASC可省略
SELECT * FROM user ORDER BY id ASC;
SELECT * FROM user ORDER BY id DESC; #降序
-- 按照年龄升序排列
SELECT * FROM user ORDER BY age ASC;
SELECT * FROM user ORDER BY 1 DESC; #也可以使用字段的位置,例如此位置为1的字段为id
UPDATE user SET age=12 WHERE id=6;
-- 按照年龄升序,id降序排列
SELECT * FROM user ORDER BY age ASC, id DESC; #age相同时,则按照id降序排列
SELECT id,age,username,GROUP_CONCAT(username),COUNT(*) AS totalUsers,SUM(age) AS sum_age
FROM user
WHERE id>=2
GROUP BY sex
HAVING COUNT(*)>=2
ORDER BY age DESC,id ASC;
-- 实现随机提取记录
SELECT * FROM user ORDER BY RAND();
通过LIMIT限制显示条数
-- LIMIT 限制查询结果显示条数
-- 查询表中前三条记录
SELECT * FROM user LIMIT 3; #显示前三条,跟id没关系
SELECT * FROM user ORDER BY id DESC LIMIT 5; #对降序排列后的结果进行查询前5条
-- 查询表中第一条记录
SELECT * FROM user LIMIT 1;
SELECT * FROM user LIMIT 0,1; #0代表偏移量,第一条偏移量为0,后面的1表示从你选定的偏移量开始,要显示几条记录
SELECT * FROM user LIMIT 1,1; #从第2条开始,查询前1条
SELECT * FROM user LIMIT 0,5; #从第1条开始,查询前5条
SELECT * FROM user LIMIT 5,5; #相当于显示下5条,即下一页
SELECT * FROM user LIMIT 1,5; #从第2条开始,查询前5条
通过ORDER BY和LIMIT来更新删除
-- 针对单表查询的完整形式
-- 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 user
WHERE id>=1
GROUP BY sex
HAVING COUNT(*)>=2
ORDER BY age DESC
LIMIT 0,2;
MySQL连接查询
多表查询
内连接
-- 更新用户名为4位的用户,让其已有年龄减3
UPDATE user SET age=age-3 WHERE username LIKE '____'; #四个下划线,用到了模糊查询
-- 更新前3条记录,让已有年龄加10
UPDATE user SET age=age+10 LIMIT 3;
# 报错 UPDATE user SET age=age+10 LIMIT 0,3; #更新时limit参数只能有1个
-- 按照id降序排列,更新前3条
UPDATE user SET age=age+10 ORDER BY id DESC LIMIT 3; #也只能limit一个参数
-- 删除用户性别为男的用户,按照年龄降序排列,删除前1条数据
DELETE FROM user WHERE sex='男' ORDER BY age DESC LIMIT 1;
-- 连接查询:是将两个或两个以上的表按照某个条件连接起来,从中选取需要的数据。
-- 连接查询是同时查询两个或两个以上的表时使用的。
-- 当不同的表中存在相同意义的字段时,可以通过该字段连接这几个表。
CREATE TABLE IF NOT EXISTS `provinces`(
id SMALLINT,
proName VARCHAR(200)
);
INSERT provinces VALUES(1,'北京');
INSERT provinces VALUES(2,'上海');
INSERT provinces VALUES(3,'深圳');
INSERT provinces VALUES(4,'杭州');
INSERT provinces VALUES(5,'沈阳');
UPDATE user SET proID=1 WHERE id=1;
UPDATE user SET proID=3 WHERE id=2;
UPDATE user SET proID=2 WHERE id=3;
UPDATE user SET proID=4 WHERE id=4;
UPDATE user SET proID=5 WHERE id=6;
UPDATE user SET proID=1 WHERE id=8;
UPDATE user SET proID=3 WHERE id=55;
UPDATE user SET proID=4 WHERE id=98;
UPDATE user SET proID=2 WHERE id=99;
UPDATE user SET proID=5 WHERE id=100;
-- 查询user表中id,username
-- 查询另一张表provinces中的proName
SELECT user.id,username,proName FROM user,provinces;
-- user的proID对应省份表provinces中的id
SELECT user.id,username,proName FROM user,provinces
WHERE user.proID=provinces.id;
-- 内连接 INNER JOIN或CROSS JOIN和ON
-- 查询user表中的id,username,email,sex
-- 查询provinces表中的proName
SELECT u.id,u.username,u.email,u.sex,p.proName
FROM user AS u
INNER JOIN provinces AS p #和哪个表结合,并起别名
ON u.proID=p.id; #连接条件
-- 另一种写法 CROSS JOIN
SELECT u.id,u.username,u.email,u.sex,p.proName
FROM provinces AS p
CROSS JOIN user AS u
ON u.proID=p.id;
-- 另一种写法 直接写JOIN
SELECT u.id,u.username,u.email,u.sex,p.proName
FROM provinces AS p
JOIN user AS u
ON u.proID=p.id;
-- 查询user表中的id,username,sex
-- 查询provinces表中的proName
-- 条件是use的性别为男的用户
-- 根据proName分组
-- 得到每组的组中人数
SELECT u.id,u.username,u.sex,p.proName,COUNT(*) AS totalUsers,GROUP_CONCAT(username)
FROM 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 user AS u
JOIN provinces AS p
ON u.proID=p.id
WHERE u.sex='男'
GROUP BY p.proName
HAVING COUNT(*)>=1; #多加的
-- 按照user表中id升序排列
SELECT u.id,u.username,u.sex,p.proName,COUNT(*) AS totalUsers,GROUP_CONCAT(username)
FROM 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; #多加的
-- 限制显示条数,前两条
SELECT u.id,u.username,u.sex,p.proName,COUNT(*) AS totalUsers,GROUP_CONCAT(username)
FROM 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;
-- 查询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;
-- 查询cmd_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 user(username,password,proId)
VALUES('HJ','HJ','20'); #而provinces表中没有id为20的数据,故内连接永远显示不出此条
外连接
-- 外连接:
-- 左外连接:显示左表的全部记录及右表符合连接条件的记录
-- 右外连接:显示右表的全部记录及左表符合连接条件的记录
-- 左外连接
SELECT u.id,u.username,u.email,u.sex,p.proName
FROM user AS u
LEFT JOIN provinces AS p
#如果是INNER JOIN则看不到HJ这个数据,但此处因为是左连接,先显示左表,故HJ会显示出来,只不过proName下的数据显示成NULL
ON u.proID=p.id;
# 调换了两个表的顺序,主表变为provinces,则全部显示provinces表,对应到user表中没有的,则显示NULL。
# 且HJ对应数据不显示了,因为HJ对应20,在provinces表中没有20
SELECT u.id,u.username,u.email,u.sex,p.proName
FROM provinces AS p
LEFT JOIN user AS u
ON u.proID=p.id;
-- 右外连接
SELECT u.id,u.username,u.email,u.sex,p.proName
FROM user AS u
RIGHT JOIN provinces AS p
ON u.proID=p.id;
外键
-- 外键:外键是表的一个特殊字段。被参照的表是主表,外键所在字段的表为子表。
-- 设置外键的原则需要记住,就是依赖于数据库中已经存在的表的主键。
-- 外键的作用是建立该表与其父表的关联关系。父表中对记录做操作时,子表中与之对应的信息也应有相应的改变。
-- 外键的作用是保持数据的一致性和完整性,可以实现一对一或一对多的关系。
-- 注意:1.父表和子表必须使用相同的存储引擎,而且禁止使用临时表。
-- 2.数据表的存储引擎只能为InnoDB。
-- 3.外键列和参照列必须具有相似的数据类型。其中数字的长度或是否有符号位必须相同;而字符的长度则可以不同。
-- 4.外键列和参照列必须创建索引。如果外键列不存在索引的话,MySQL将自动创建索引。
-- 创建部门表department(主表),先有主表部门,才能在部门下加入员工
-- id depName depDesc
CREATE TABLE IF NOT EXISTS department(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
depName VARCHAR(20) NOT NULL UNIQUE
)ENGINE=INNODB;
INSERT department(depName) VALUES('教育部'),
('市场部'),
('运营部'),
('督导部');
-- 创建员工表(子表)
-- id,username,deId
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('HJ1',1),
('HJ2',2),
('HJ3',3),
('HJ4',4),
('HJ5',1);
SELECT e.id,e.username,d.depName FROM
employee AS e
JOIN
department AS d
ON e.depId=d.id;
-- 删除督导部,所以部分下不能有员工了,故应先安排好员工,再解散部门
-- 以下操作虽删掉了部门,但是employee表中还有督导部的员工
DELETE FROM department WHERE depName='督导部';
-- 应进行下面的操作:
DELETE FROM employee WHERE depId=1;
DELETE FROM department WHERE id=1;
#比较麻烦吧,现在删掉两个表,重新来,学习外键
DROP TABLE employee,department;
-- 创建部门表department(主表),先有主表部门,才能在部门下加入员工
-- id depName depDesc
CREATE TABLE IF NOT EXISTS department(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
depName VARCHAR(20) NOT NULL UNIQUE
)ENGINE=INNODB;
INSERT department(depName) VALUES('教育部'),
('市场部'),
('运营部'),
('督导部');
-- 创建员工表(子表)
-- id,username,deId
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) #创建外键depId,参照主表department的主键id
)ENGINE=INNODB;
INSERT employee(username,depId) VALUES('HJ1',1),
('HJ2',2),
('HJ3',3),
('HJ4',4),
('HJ5',1);
-- 删除主表中的记录
#DELETE FROM department WHERE id=1;
#报错!因为有constraint,只能先删除员工(子表),才能删除部门(主表)
-- 删除员工表中的属于1部门的人
DELETE FROM employee WHERE depId=1;
DELETE FROM department WHERE id=1; #再删除主表部门
-- 测试插入垃圾数据
#INSERT employee(username,depId) VALUES('HJ6',10);
#报错!因为外键约束,主表中没有10对应的部门,故子表中不能插入
#不能插入也不能更新主表中没有的
添加删除外键
-- 删除员工表
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('HJ1',3),
('HJ2',2),
('HJ3',3),
('HJ4',4),
('HJ5',2);
-- 删除外键
ALTER TABLE employee DROP FOREIGN KEY emp_fk_dep;
-- 添加外键
DELETE FROM employee WHERE depId=2;
#由于我自己删除了父表部门id为2的数据,所以直接进行下面的操作是不行的,要先删掉子表对应的数据
ALTER TABLE employee ADD CONSTRAINT emp_fk_dep FOREIGN KEY(depId) REFERENCES department(id);
-- 级联操作
-- CASCADE:从父表删除或更新且自动删除或更新子表中匹配的行。
-- SET NULL:从父表删除或更新行,并设置子表中的外键列为NULL。如果使用该选项,必须保证子表列没有指定NOT NULL。
-- RESTRICT:拒绝对父表的删除或更新操作。
-- NO ACTION:标准SQL的关键字,在MySQL中与RESTRICT相同。
-- 先删掉表
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('教育部'),
('市场部'),
('运营部'),
('督导部');
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('HJ1',1),
('HJ2',2),
('HJ3',3),
('HJ4',4),
('HJ5',1);
-- 删除部门表的第1个部门
DELETE FROM department WHERE id=1; #则子表中对应id=1的部分也自动删除了
#UPDATE department SET id=id+10;
#报错,不让你更新,应把子表先全删了,再更新
#如果把FOREIGN KEY(depId) REFERENCES department(id) ON DELETE CASCADE改成如下形式:
#FOREIGN KEY(depId) REFERENCES department(id) ON DELETE CASCADE ON UPDATE CASCADE
#UPDATE department SET id=id+10;就不会报错了,子表会自动更新
-- SET NULL
#如果把FOREIGN KEY(depId) REFERENCES department(id) ON DELETE CASCADE改成如下形式:
#FOREIGN KEY(depId) REFERENCES department(id) ON DELETE SET NULL ON UPDATE SET NULL
#被删除的数据变成NULL
联合查询
-- 联合查询
-- UNION和UNION ALL
-- 区别:UNION去掉相同记录,UNION ALL是简单的合并到一起
SELECT username FROM employee UNION SELECT username FROM user; #会去掉相同的记录
SELECT username FROM employee UNION ALL SELECT username FROM user; #只是简单的合并
#SELECT id,username FROM employee UNION ALL SELECT username FROM user;
#报错,两个查询的字段数量必须相同
SELECT id,username FROM employee UNION ALL SELECT username,age FROM user; #可以,但是很少这样做,因为无关,结果无用
子查询
子查询的形式
-- 子查询:是将一个查询语句嵌套在另一个查询语句中。
-- 内层查询语句的查询结果,可以作为外层查询语句提供条件
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('教育部'),
('市场部'),
('运营部'),
('督导部');
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('HJ1',1),
('HJ2',2),
('HJ3',3),
('HJ4',4),
('HJ5',1);
#不用子查询的方式很复杂:SELECT id,username FROM employee WHERE depId IN (1,2,3,4);
-- 由[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);
-- 插入垃圾数据
INSERT employee(username,depId) VALUES('HJ6',8); #因为表department中没有8,是垃圾数据
SELECT id,username FROM employee WHERE depId IN(SELECT id FROM department); #查询不到HJ6
SELECT id,username FROM employee WHERE depId NOT IN(SELECT id FROM department); #NOT IN 则能查询到HJ6
-- 创建学员表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('HJ1',95),
('HJ2',35),
('HJ3',45),
('HJ4',55),
('HJ5',65),
('HJ6',75),
('HJ7',85),
('HJ8',90),
('HJ9',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);
-- 查询获得一等奖学金的学员(则为分数大于等于90的)
#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);
-- [NOT] EXISTS
-- 查询部门表中,然而部门表中一共有4个部门,则第五个部门不存在
SELECT * FROM department WHERE id=5; #得到empty
SELECT id,username FROM employee WHERE EXISTS(SELECT * FROM department WHERE id=5);
#要先执行括号里的语句,如果为真,才执行外层,id=5为假,则不执行外层,得到的结果为Empty set
-- ANY|SOME 或 ALL
-- 查询所有获得奖学金的学生
# >=ANY,为>=ANY括号里的最小值
SELECT id,username,score FROM student WHERE score>=ANY(SELECT level FROM scholarship);
# >=SOME,为>=SOME括号里的最小值
SELECT id,username,score FROM student WHERE score>=SOME(SELECT level FROM scholarship);
-- 查询所有学员中获得一等奖学金的学员
# >=ALL,为>=ALL括号里的最大值
SELECT id,username,score FROM student WHERE score>=ALL(SELECT level FROM scholarship);
-- 查询哪些学员没有获得奖学金
#<ALL 即小于括号里的最小值
SELECT id,username,score FROM student WHERE score<ALL(SELECT level FROM scholarship);
#<ANY为<括号内的最大值
SELECT id,username,score FROM student WHERE score<ANY(SELECT level FROM scholarship);
#ANY|SOME 相当于IN,不可以=ALL
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);
#<>ALL或!=ALL 相当于NOT IN
SELECT id,username,score FROM student WHERE score!=ALL(SELECT level FROM scholarship);
SELECT id,username,score FROM student WHERE score NOT IN (SELECT level FROM scholarship);
-- 将查询结果写进数据表中
CREATE TABLE test0(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
num TINYINT UNSIGNED
);
INSERT test0(id,num)
SELECT id,score FROM student; #字段数量一定要一致
CREATE TABLE test01(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
num TINYINT UNSIGNED
)SELECT id,score FROM student; #此处变了 #num下都是NULL,id和score都被写进来了
CREATE TABLE test02(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
score TINYINT UNSIGNED #此处变了
)SELECT id,score FROM student; #id,score部分被写进来了,且只有这两部分
正则表达式查询
-- 正则表达式
-- REGEXP '匹配方式'
-- ^匹配字符开始的部分
-- 查询用户名以t开始的用户
SELECT * FROM user WHERE username REGEXP '^t'; #也不区分大小写
-- $匹配字符串结尾的部分
-- 查询以j结尾的用户
SELECT * FROM user WHERE username REGEXP 'j$';
-- .代表任意字符
SELECT * FROM user WHERE username REGEXP '.'; #即查询所有记录
SELECT * FROM user WHERE username REGEXP 't..t';
SELECT * FROM user WHERE username LIKE 't__t'; #和上面操作一样,此为模糊查询
-- [字符集合] 匹配[lto]中的任何一个都可以,只要字符中含有l或t或o都可以
SELECT * FROM user WHERE username REGEXP '[lto]';
-- [^字符集合] 除了字符集合中的任何一个字符
SELECT * FROM user WHERE username REGEXP '[^lto]';
-- 查询在一个范围中的
SELECT * FROM user WHERE username REGEXP '[a-s]';
-- S1|S2|S3 匹配S1、S2、S3任意一个字符串
SELECT * FROM user WHERE username REGEXP 'ng|qu|te';
-- *代表0个1个或者多个其前的字符
SELECT * FROM user WHERE username REGEXP 'que*'; #e出现0、1或者多次
-- +代表1个或者多个其前的字符
SELECT * FROM user WHERE username REGEXP 't+'; #t至少出现1次
-- String{N} 字符串出现N次
SELECT * FROM user WHERE username REGEXP 'que{2}'; #指定e出现2次
-- 字符串{M,N} 字符串至少出现M次,最多N次
SELECT * FROM user WHERE username REGEXP 'que{1,3}'; #e最少出现1次最多出现3次