6、mysql增删改查

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.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