6、mysql增删改查
6.1、创建表
#创建表
CREATE TABLE `student` (
`id` bigint NOT NULL AUTO_INCREMENT,
`username` varchar(255) DEFAULT NULL,
`password` varchar(255) DEFAULT NULL,
`img` longblob,
`time_u` date DEFAULT NULL,
`u_num` decimal(6,3) DEFAULT NULL,
`stamp_u` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`date_u` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
^: ENGINE--指定存储引擎 InnoD 为默认引擎 ; BDEFAULT CHARSET--指定编码集; DEFAULT CURRENT_TIMESTAMP --默认为当前时间; ON UPDATE CURRENT_TIMESTAMP -- 更新行数据 自动更新为当前时间
#创建关联表
CREATE TABLE `rel_student` (
`id` bigint NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`age` int DEFAULT NULL,
`sex` varchar(255) DEFAULT NULL,
`number` varchar(255) DEFAULT NULL,
`s_id` bigint DEFAULT NULL,
PRIMARY KEY (`id`),
CONSTRAINT `rel_student_ibfk_1` FOREIGN KEY (`id`) REFERENCES `student` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
^: ENGINE 指定存储引擎 InnoD 为默认引擎 ; DEFAULT CHARSET指定编码集
^: CONSTRAINT rel_student_ibfk_1
FOREIGN KEY (id
) REFERENCES student
(id
) 创建外键 将关联表中的id 与学生表中的id 绑定
^: eg: student表为学生表 rel_student 为学生表的关联表 如果删除学生表的信息 而没有删除学生关联表中的数据 会生成脏数据 将关联表的id 与学生表的id 绑定(关联表生成外键), 这样删除学生表的时候 sql会提示必须要先删除对应的关联表中的数据,从而加强关联关系, 学生表中的id与关联表中的id 一 一对应关系 ,这种对应关系 可以自行指定
6.2、修改表
ALTER TABLE rel_student ADD phone VARCHAR(255); #rel_student 添加字段
ALTER TABLE rel_student ADD CONSTRAINT fk_sid FOREIGN KEY(id) REFERENCES student(id) #添加外键 fk_sid外键名称
alter table rel_student drop foreign key fk_sid; #删除外键
ALTER TABLE rel_student MODIFY phone VARCHAR(11); #rel_student 修改字段数据类型
ALTER TABLE rel_student CHANGE tel telephone VARCHAR(25); #rel_student 修改字段名称类型
ALTER TABLE rel_student ALTER telephone set DEFAULT '152' #rel_student 修改字段默认值
ALTER TABLE rel_student MODIFY id BIGINT auto_increment; #rel_student 修改字段id 为自增长
6.3、新增数据
INSERT into student(username,password) VALUES('ls1','123456')#插入一条数据
INSERT into student(username,password) VALUES('ls1','123456'),('ls2','123456'),('ls3','123456') #批量插入
INSERT INTO student(img) VALUES(UNHEX('ffd8ffe000104a46494600010100000')) #将图片转换为byte数组,byte数组转换为16进制字符串 然后通过UNHEX('16进制字符串')存储图片 , HEX(img)获取16进制字符串
image.png
6.4、修改数据
update student set username='test2',password='123456' where id=2;
image.png
6.5、删除数据
DELETE from student where id=17;
image.png
6.6、关联查询
6.6.1、自然连接查询(笛卡尔积)
SELECT * from student;#结果1
SELECT * from rel_student;#结果2
SELECT * from student,rel_student ;#结果3
SELECT * from student,rel_student where student.id=rel_student.id#结果4
image.png
image.png
image.png
image.png
6.6.2、left join
LEFT JOIN 关键字从左表(table1)返回所有的行,即使右表(table2)中没有匹配。如果右表中没有匹配,则结果为 NULL。
image.pngSELECT * from student as s LEFT JOIN rel_student as rs on s.id= rs.id and s.username='test1';
SELECT * from student as s LEFT JOIN rel_student as rs on s.id= rs.id and s.username='test2';
SELECT * from student as s LEFT JOIN rel_student as rs on s.id= rs.id ;
image.png
6.6.3、right join
RIGHT JOIN 关键字从右表(table2)返回所有的行,即使左表(table1)中没有匹配。如果左表中没有匹配,则结果为 NULL。
image.pngSELECT * from student as s right JOIN rel_student as rs on s.id=rs.id ;
SELECT * from student as s right JOIN rel_student as rs on rs.id=1 ;
image.png
6.6.4、inner join
INNER JOIN 关键字在表中存在至少一个匹配时返回行。
image.png SELECT * from student as s inner JOIN rel_student as rs on s.id=rs.id ;
image.png
6.6.5、union all/unino
(SELECT pwd ,username from student WHERE username = 'ls' LIMIT 1) UNION ALL (SELECT username ,pwd from student WHERE username = 'ls') ;
image.png
^: UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名
^: UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时,每条 SELECT 语句中的列的顺序必须相同。
^: UNION对结果集去重 ,UNION ALL不会
6.7、关联表更新
UPDATE student as s LEFT JOIN rel_student as rs ON s.id = rs.id and s.username='test1' SET s.password='p',rs.age=16;
^: 关联查询后,更新左表所有行的password内容为p 更新右表匹配到左表的行数据,将右表age改为1
UPDATE student as s LEFT JOIN rel_student as rs ON s.id = rs.id SET s.password='123456',rs.age=20 where s.id=1;
6.8、关联表删除
DELETE student ,rel_student from student left JOIN rel_student on student.id=rel_student.id
DELETE student ,rel_student from student left JOIN rel_student on student.id=rel_student.id where student.id=2;
^: 不加where 删除全部左表 删除匹配关联表, 后面加了where的话 只删除where筛选条件后 筛选出的左表数据和匹配到的右表数据
6.9、基本查询语法
SELECT * from student #查询表的所有数据
SELECT * from student where id>19 #查询id大于19的数据
SELECT * from student where id<19 #查询id小于19的数据
SELECT * from student where img IS null #查询img是null数据
SELECT * from student where img IS NOT null #查询img不是null数据
SELECT * FROM student WHERE id <> 12 #查询id不等于12的数据 (ps: <> 可替换为!=)
SELECT COUNT(*) from student #查询总条数
SELECT count(img) from student # 查询img不为null的总条数
SELECT sum(`id`) from student #查询id总和 (ps:只会把数字相加)
SELECT AVG(id) from student #查询id平均值
SELECT MAX(id) from student #查询id最大值
SELECT MIN(id) from student #查询id最小值
SELECT * from student WHERE username LIKE 'ls%' #查询以ls开头的数据
SELECT * from student WHERE username LIKE '%ls' #查询以ls结尾的数据
SELECT * from student WHERE username LIKE '%ls%' #查询包含ls的数据
###数据起始位置从0开始
SELECT * from student LIMIT 5 #从第0条开始 返回5条数据
###数据起始位置从0开始
SELECT * from student LIMIT 10,20 #从第11条开始 返回20条数据
SELECT * from student ORDER BY id ASC LIMIT 10,20 # ORDER BY需要放在LIMIT前面 ,先按id正序排序 ,从第11条开始 返回20条数据
SELECT * from student ORDER BY id DESC LIMIT 10,20 # ORDER BY需要放在LIMIT前面 ,先按id倒序排序 ,从第11条开始 返回20条数据
SELECT * from student ORDER BY id DESC,stamp_u ASC #先按id 降序 再按 stamp_u 升序排序
6.10、group by 与distinct
6.10.1、group by(以某个或多个字段分组)
SELECT count(*),username,pwd,sum(id),MIN(id), MAX(id) ,AVG(id) ,GROUP_CONCAT(id)from student group by username,pwd
image.png
SELECT count(*)as count,username,pwd,sum(id),MIN(id), MAX(id) ,AVG(id) ,GROUP_CONCAT(id) from student where id<19 group by username,pwd HAVING count>=1 order by count LIMIT 1,2
image.png
6.10.2、distinct(以某个或多个字段去重)
select distinct username ,pwd from student;
image.png
6.11、正则表达式查询过滤
select * from student where username regexp 'l' #查询username包含l的数据
select username from student where username regexp '^l'; #查询username以l开头的数据
select username from student where username regexp '2$'; #查询username以2结尾的数据
select * from student where username regexp '^ls2.*ls2$'; #查询username以ls2开头和结尾的数据
select * from student where username regexp '[la]'; #查询username包含l或者a的数据
select * from student where username regexp '[^ls]'; #查询username同时不包含l和s字符的数据
select * from student where username regexp 'ls|a'; #查询username包含ls或者a的数据
select * from student where username regexp '(ls2){2}'; #查询username为至少有两个ls2的数据,如ls2ls2,ls2ls2ls2
SELECT * from student where pwd REGEXP '^\\d{1}$' #查询pwd为一个数字的数据
SELECT * from student where pwd REGEXP '^\\w{1}$' #查询pwd为一个数字或一个字母或为下划线的数据
6.12、子查询
6.12.1、IN关键字
SELECT username,pwd from student WHERE username in (SELECT username from student WHERE username REGEXP 'ls' );
image.png
^: select ...from ... where a > in(...); 等同于 select ...from ... where a in(result1,result2,result3);
6.12.2、ANY关键字
SELECT id ,username from student WHERE id >ANY (SELECT id from student WHERE id >19);
image.png
^: select ...from ... where a > any(...); 等同于 select ...from ... where a > result1 or a > result2 or a > result3;
6.12.3、ALL关键字
SELECT id ,username from student WHERE id >ALL (SELECT id from student WHERE id =19);
image.png
^: select ...from ... where a > all(...); 等同于 select ...from ... where a > result1 and a > result2 and a > result3;
6.12.4、SOME关键字
some关键字和any关键字是一样的功能
6.12.5、EXISTS关键字
SELECT id ,username from student WHERE EXISTS( SELECT * from student where username='u' ); #子查询存在username为u的数据,则返回id ,username
SELECT id ,username from student WHERE NOT EXISTS( SELECT * from student where username='u1' ); #子查询不存在username为u1的数据,则返回id ,username
drop table if exists 表A;#如果存在表A,则删除表A
image.png
网友评论