oracle中prad函数_Mysql基础命令总结（DML语句汇总）

今天我们来聊聊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语句操作请点击：juejin.im/post/5e6ca6…