粤嵌打卡第26天（MYSQL数据库----DML语句操作）

最新推荐文章于 2024-09-14 19:55:29 发布

qq_41986840

最新推荐文章于 2024-09-14 19:55:29 发布

阅读量212

点赞数 1

文章标签：数据库 mysql

本文链接：https://blog.csdn.net/qq_41986840/article/details/104886645

版权

今天我们来聊聊Mysql命令—DML语句

1、增删改查基本语句

1.1 添加语句

-- 1- insert
SELECT * FROM dept;
-- 手写一次插入多条记录
INSERT INTO dept VALUES(1, '行政', '西安'), 
		       (2, '研发', '北京'), 
		       (3, '销售', '深圳');
-- 子查询一次插入多条记录
INSERT INTO dept  SELECT ...
-- 查看表结构
DESC dept;
-- 更改列属性
ALTER TABLE dept MODIFY dname VARCHAR(20);
-- 截断表
TRUNCATE TABLE dept;
-- 动态添加外键
ALTER TABLE emp DROP FOREIGN KEY emp_ibfk_1;
ALTER TABLE emp ADD FOREIGN KEY(deptno) REFERENCES dept(deptno);

1.2 修改语句

-- 2- update ---------
UPDATE dept SET loc = '深圳', dname='研发2部' WHERE deptno = 2;

1.3 删除语句

-- 3- delete -------
DELETE FROM dept WHERE deptno = 3;

1.3.1 删除、更新语句父子表级联操作

-- 添加外键时，指定关联操作
-- 删除外键
ALTER TABLE emp DROP FOREIGN KEY emp_ibfk_1;
-- 添加外键并指定级联删除操作（不安全，视业务逻辑而定）
ALTER TABLE emp ADD FOREIGN KEY (deptno) REFERENCES dept(deptno) ON DELETE CASCADE;
--	ON UPDATE CASCADE 	-- 当变更父表中的主键时， 子表相关数据会级联更新
--  ON UPDATE SET NULL          -- 当变更父表中的主键时， 子表相关数据会级联置空
--	ON DELETE CASCADE;	-- 当删除父表中的记录时， 子表相关数据会级联删除
--	ON DELETE SET NULL;	-- 当删除父表中的记录时， 子表相关数据会级联置空

INSERT INTO emp VALUES (1, '小明', 5000, 2);
DELETE FROM dept WHERE deptno = 1;

UPDATE emp SET deptno = 1 WHERE empno=1;
UPDATE dept SET deptno = 20 WHERE deptno=2;

-- 先查找子表关联记录，做相关处理（将外键置空、修改或者删除子表记录）
UPDATE emp SET deptno = NULL WHERE deptno = 2;
DELETE FROM dept WHERE deptno = 2;

DELETE FROM dept WHERE deptno = 2;
SELECT * FROM emp;
SELECT * FROM dept;

敲重点：

我们在做项目时，往往会有子表和父表相互关联，例如部门表和员工表的关联，在更新或者删除时就不能级联操作，否则就会报错。只能通过加条件查询出来,将外键置为null，有的业务例如发布文章和文章数的字父表就要级联删除或者更新，所以按业务而定。

1.4 查询语句（按照遵循查询原理方可解决一切查询问题）

-- 查询原理1： select 语法顺序
/*
	select [distinct] 列1 列别名[, 列2 列别名2][, ...]   (列筛选   可以用*代替所有列)
	from 数据源  别名[, 数据源2 别名2]
	[where 行筛选条件]
	[group by 分组列[, 分组列2]]
	[having 分组筛选条件[, 分组筛选条件]]
	[order by 排序列 [ASC|DESC] [, 排序列 [ASC|DESC]]]
*/
-- 查询
SELECT d.*, 1+deptno addNum FROM dept d;

1.4.1 字符串连接器 CONCAT(param1,param2,…)

-- 字符串连接器  CONCAT(param1,param2,...)
SELECT deptno "部门 编号", CONCAT(dname,' ',loc) deptinfo FROM dept;

1.4.2 过滤重复数据 DISTINCT (查询结果中每列都相等的数据就是重复数据)

-- 过滤重复数据： 查询结果中每列都相等的数据就是重复数据
SELECT DISTINCT empno,job FROM emp;

注意：在查询数据量较大时慎用DISTINCT，非常影响性能；

1.4.3 where 只能筛选行条件（or 或 and 与）

-- where 行筛选子句； or 或条件  and 与条件
SELECT * FROM emp WHERE deptno != 20 AND sal > 2000 ;
-- col BETWEEN val1 AND  val2 :  等价于　col >= val1 and col <= val2
SELECT * FROM emp WHERE sal BETWEEN 1000 AND 3000;
SELECT * FROM emp WHERE sal>=1000 AND sal<=3000;

1.4.4 in 子句

-- in  注意： in子句中元素最多 999个（可以转换  col in (0,1,..998) or in(999,1000,...1997)）
SELECT * FROM emp WHERE deptno IN (10,30);
SELECT * FROM emp WHERE deptno=10 OR deptno=30;

注意：使用in会降低查询性能（可以使用关联查询替代）

1.4.5 like 模糊查询

-- like 模糊查询  % 匹配0个或多个字符  _ 可匹配一个字符
SELECT * FROM emp WHERE NOT (ename LIKE '_A%');

1.4.6 order by排序子句

-- Order by 排序子句
SELECT * FROM emp WHERE NOT (ename LIKE '_A%')
	ORDER BY sal DESC;
-- 主从排序：主排序一致情况下，从排序才生效。 
SELECT * FROM emp WHERE NOT (ename LIKE '_A%')
	ORDER BY empno,MOD(deptno,20) DESC,sal ASC;

注意：在order by主从排序中，前面的为主，后面的为从，只有当主排序相同时，才按从排序排列

1.4.7 @rn:=0; 定义临时变量

#-- 定义临时变量  select @变量名:=初值;
SELECT @rn:=0;
SELECT @rn:=@rn+1 rownum, emp.*  FROM  emp;

1.5 group by 分组

查询原理2：查询结果必须为矩阵

select 后跟的列，必须参与group by分组或者参与分组函数，否则非矩阵。

#-- 求出每个部门工资最高的人员信息?????????????
--  错误的SQL：
SELECT emp.* , MAX(sal),deptno FROM emp GROUP BY deptno;
-- 正解： ？？？？？？？？？？？
SELECT MAX(sal),deptno FROM emp GROUP BY deptno;

SELECT * FROM emp;

#-- 可以同时有多个分组表达式，
#-- 统计在同一个部门同样工作的员工人数
SELECT deptno,job,COUNT(1) FROM emp GROUP BY deptno,job;

SELECT deptno,COUNT(1) FROM emp GROUP BY deptno%20;

#-- 分组过滤子句  having 分组筛选条件, 
--                having后只能放分组函数的表达式或者分组列
SELECT deptno,SUM(sal) FROM emp GROUP BY deptno HAVING SUM(sal)>9000;

SELECT deptno,SUM(sal) FROM emp GROUP BY deptno HAVING deptno >= 20;

SELECT deptno,SUM(sal) FROM emp WHERE deptno >= 20 GROUP BY deptno ;

敲重点：

where和having的区别：

where是对行数据或者分组列作为删选条件，不能以分组函数作为筛选条件

having是对分组函数的表达式或者分组列作为筛选条件，不能以行数据作为筛选条件

select后面的列必须有group by分组的条件，否则不能构成矩阵，查询结果有误

1.6 limit 从哪页开始，显示多少条数据

-- MySql的分页
-- limit  开始行（第一行从0标号开始）,  取多少条记录。

SELECT * FROM emp 
	ORDER BY empno
	LIMIT 10, 5;

2、字符串函数

2.1 大小写转换函数 UPPER(), LOWER()

# -- 1- 大小写转换 UPPER(), LOWER()
SELECT @val:='Abc';
SELECT @val,UPPER(@val), LOWER(@val) FROM dept WHERE deptno=10;

SELECT deptno,LOWER(dname) FROM dept;
#- 数据库 中 关键字，字段（列名） 不区分大小写； 但值区分大小写
SELECT deptno,dname FROM dept WHERE UPPER(dname) = UPPER('sales');
SELECT deptno,dname FROM dept WHERE dname = 'sales';
UPDATE dept SET dname = LOWER(dname) WHERE deptno=30;

2.2 去前后空格(TRIM)

SELECT @val:=' Ab c  ';
SELECT @val, LENGTH(TRIM(@val));

2.3 获取字符串长度 (LENGTH)

#--  获取字符串的长度 LENGTH()
select LENGTH(comm) from emp;

2.4 ifnull(表达式1,表达式2）

#-- null处理函数： ifnull(表达式1,表达式2）
--                    如果表达式1为null则返回表达式2，否则就返回表达式1；
SELECT LENGTH(IFNULL(comm,'')),comm,empno FROM emp;

SELECT IFNULL(LENGTH(comm),0),comm,empno FROM emp;

SELECT IFNULL(comm,CONCAT(comm,'')),comm,empno FROM emp;

– MySql的容错性：同一列可以放置不同类型的值（ Oracle不允许）

– 但是所有的关系型数据库，对数值加 ‘’ 是许可的

2.5 case表达式：多条件表达式(可以给值起别名)

#-- case表达式： 多条件表达式(可以给值起别名)
SELECT empno, 
	CASE 
		WHEN sal <= 1000 THEN '一级'
		WHEN sal <= 2000 THEN '二级'
		ELSE '1'
	END  sal_level FROM emp;

2.7 取子串 substr( 字符串表达式 , 位置, 长度)

#- 取子串   substr( 字符串表达式 , 位置, 长度)
#-   位置：从1开始 从左向右排布顺序；  如果取负值，则从右向左排布顺序；
#-         如果位置写了0则无法取出子串，返回 '' 字符串
SELECT SUBSTR('I love this game',3,4), SUBSTR('I love this game',-4,4);

2.8 常见字符串函数总结
- CHAR_LENGTH() 得到字符串的字符数 SELECT CHAR_LENGTH(‘ABC’);
- LENGTH() 返回字符串的长度 SELECT LENGTH(‘abc’);
- CONCAT() 如果字符串中包含NULL，返回拼接结果就是null SELECT CONCAT(NULL,‘A’,‘B’,‘C’);
- CONCAT_WS(X,SL,S) 以指定分隔符拼接字符串 SELECT CONCAT_WS(’-’,‘a’,‘b’,‘c’);
- UPPER()|UCASE()|LOWER|LCASE() 转换大小写 SELECT UPPER(‘hello king’);
- REVERS() 字符串反转函数
- LEFT() 得到前两个字符 RIGHT() 得到后两个字符 SELECT LEFT(‘hello’,2);
- LPAD()|PRAD() 用字符串填充到指定长度 SELECT LPAD(‘abc’,10,’?’);
- TRIM()|LTRIM()|RTRIM() 去掉字符串两端的空格 SELECT TRIM(’ abc ');
- REPEAT(s):重复指定的次数 SELECT REPEAT(‘hello’,3);
- REPLACE():替换字符串 SELECT REPLACE(‘hello king’,‘king’,‘queen’);
- SUBSTRING:截取字符串 SELECT SUBSTRING(‘sdcsdc’,1,3);
- STRCMP: 比较字符串 SELECT STRCMP(‘a’,c); 返回-1，0，1

3、数值类函数

3.1 CEIL(x) 取大于等于x的最小整数

# Ceil(x) : 取大于等于x的最小整数
SELECT CEIL(3.14);

3.2 FLOOR(x) : 取小于等于x的最大整数

# FLOOR(x) : 取小于等于x的最大整数
SELECT FLOOR(3.999);

3.3 ROUND(X,s) : 对X四舍五入，指定 s 位小数

# ROUND(X,s) : 对X四舍五入，指定 s 位小数
SELECT ROUND(3.144,2);

3.4 TRUNCATE(X,s) 截断数据，不进行四舍五入，保留s位小数

# TRUNCATE(X,s) 截断数据，不进行四舍五入，保留s位小数
SELECT TRUNCATE(3.149,2);

3.5 MOD(数值，余数个数) 取余

# 取余
SELECT MOD(10,3);
SELECT 10%2;

3.6 POW() 次方

# 次方
SELECT POW(2,3);

3.7 SQRT() 开方

# 开方
SELECT SQRT(9);

3.8常用数值型函数汇总
- CEIL() 进一取整 SELECT CEIL(1,2);
- FLOOR() 舍掉小数取整 SELECT FLOOR(1.9);
- ROUND() 四舍五入 SELECT ROUND(3.56734,2);
- TRUNCATE() 截取小数点后几位 SELECT TRUNCATE(3.23323,2);
- MOD() 取余数 SELECT MOD(5,2);
- POWER() 幂运算
- ABS() 取绝对值
- PI() 圆周率
- RAND()或者RAND(X)
- SIGN(X)得到数字字符
- EXP(X) 计算e的x次方

4、聚合函数

4.1 COUNT(1) 统计数目

#-- 统计emp表的员工数量
SELECT COUNT(1) FROM emp;

4.2 SUM(x) 求和

#-- 统计emp表的员工工资总额
SELECT SUM(sal) FROM emp;

4.3 AVG(x) 求平均值

#-- 统计emp表的员工工资平均值
SELECT AVG(sal) FROM emp;

4.4 MAX(X) 最大值 MIN(X) 最小值

#-- 统计emp表的员工工资最大、最小值
SELECT MAX(sal) max_sal, MIN(sal) min_sal FROM emp;

5、日期时间常用函数

5.1日期时间函数
- CURDATE()|CURRENT_DATE() 返回当前日期
- CURTIME()|CURRENT_TIME() 返回当前时间
- NOW()|CURRENT_TIMESTAMP()|SYSDATE() 返回当前的日期时间
- SELECT MONTH(CURDATE()),MONTHNAME(CURDATE()); 返回日期中的月份和月份的名称
- SELECT DAYNAME(NOW()) 返回星期几
- SELECT DAYOFWEEK(NOW()); 返回一周内的第几天
- SELECT WEEK(NOW());
- SELECT YEAR(NOW()),MONTH(NOW()),DAY(NOW()),HOUR(NOW()),MINUTE(NOW()),SECOND(NOW());
- SELECT DATEDIFF(‘2018-03-23’,‘2018-05-20’); 计算两个时期相差的天数

6、其他函数

6.1其他常用函数
- VERSION() 测试版本
- CONNECTION_ID() 测试当前连接数
- USER() 测试当前用户
- SELECT LAST_INSERT_ID(); 得到上一步插入操作产生AUTO_INCREMENT的值
- MD5(‘king’); 密码加密
- PASSWORD(); 密码加密算法（常用）

好了！今天的内容就分享到这了。想看MYSQL的DML语句操作请点击：https://juejin.im/post/5e6ca64e6fb9a07cb24ab6fe

qq_41986840

关注

1
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫