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

6.6.2、left join

LEFT JOIN 关键字从左表（table1）返回所有的行，即使右表（table2）中没有匹配。如果右表中没有匹配，则结果为 NULL。

image.png

SELECT * 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.png

SELECT * 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