mysql数据库

最新推荐文章于 2024-07-23 13:22:23 发布

辣椒炒鸡蛋

最新推荐文章于 2024-07-23 13:22:23 发布

阅读量124

点赞数

分类专栏：数据库，JAVA Java服务端开发文章标签： mysql java

本文链接：https://blog.csdn.net/qq_37708991/article/details/115805861

版权

Java服务端开发同时被 2 个专栏收录

4 篇文章 0 订阅

订阅专栏

数据库，JAVA

3 篇文章 0 订阅

订阅专栏

SQL 语言

DML 数据操作语言增删改查

DCL 数据控制语言分配权限用户创建事物管理

DDL 数据定义语言管理逻辑裤数据表视图索引

SQL 语句不区分大小写
分号结尾
空格分割
‘’#" 单行注释 /* 多行注释 */

DDL 语句逻辑库(逻辑空间)

CREATE DATABASE 逻辑库名称； 创建一个逻辑库(逻辑空间)
SHOW DATABASES ; 显示所有的逻辑库
DROP DATABASE 逻辑库名称； 删除指定逻辑库

逻辑空间下面有：表视图函数事件查询备份

创建数据表

	CREATE TABLE 数据表(
	列名1 数据类型 [约束][COMMENT 注释],
	列名2 数据类型 [约束][COMMENT 注释]
)[COMMENT 注释]; [] 里面的内容可写可不些

USE test; # 先使用一个逻辑空间 然后在里面创建表 里面填上要创建的属性
CREATE TABLE student(
 id INT UNSIGNED PRIMARY KEY, # id属性是 int 类型 无符号(正数) PRIMARY KEY(唯一的 key 不重复且不为空)
 name VARCHAR(20) NOT NULL,  # VARCHAR 是不固定的字符大小 后面(20)给个最大长度 NOT NULL是不能为空
 sex CHAR(1) NOT NULL, # CHAR(1) 性别固定一个字符
 birthday DATE NOT NULL, # DATE 日期对象
 tel CHAR(11) NOT NULL, # 电话为11位
 remark VARCHAR(200)  # 可以 不加约束 最后一个属性结尾可以不加逗号
)
INSERT INTO student VALUES(1,"小明","男","2020-11-17","18500000000","评论内容");# 在指定的数据表中插入数据

SHOW TABLES;# 显示当前逻辑空间中所有的表名
DESC student;# 显示当前表的所有字段
SHOW CREATE TABLE student;# 显示建表语句
DROP TABLE student;# 删除表

修改表结构

ALTER TABLE student# ALTER 修改表 此处后面没有分号
MODIFY name VARCHAR(19) NOT NULL,# 修改已有字段的 约束
ADD address VARCHAR(200) NOT NULL,#, ADD 是添加一个字段
DROP address,# 删除已有字段
CHANGE tel phoneTel CHAR(11) NOT NULL;# 将已有的字段替换

字段约束

范式

原子性（字段不能再拆分）
唯一性（数据要有唯一标识做区分）
关联性（每列都与主键有直接关系不存在传递依赖）

约束

主键约束

主键约束 PRIMARY KEY 值唯一切不能为 NULL
建议主键一定使用数字类型数字的检索速度会非常快
如果主键是数字类型可设置自动增长 AUTO_INCREMENT

非空约束

非空约束要求字段值不能为 NULL
NULL 值以为是没有值而不是“”空字符串
可以用 DEFAULT 添加默认值

唯一约束

唯一约束要求字段值如果不为 NULL 那么在全表必须唯一

索引

CREATE TABLE t_message(
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
content VARCHAR(200) NOT NULL,
type ENUM("公告","通报","个人通知") NOT NULL,
INDEX index_type (type)# 对 type 这个字段添加了一个名字为 index_type 的索引
);

DROP INDEX index_type ON t_message;# 在某个表删除索引
CREATE INDEX index_type ON t_message(type);# 在某个表添加索引
SHOW INDEX FROM t_message;# 显示表中的所有索引

索引的使用原则

索引的本质就是用二叉树结构来保证查询速度，缺点是为了保持这个结构需要开销

数据量很大而且经常被查询的数据表可以设置索引
索引只添加在经常被用作索引条件的字段上面
不要在大字段上创建索引

简单查询

USE demo;
SELECT * FROM t_emp;# * 代表表里面所有的字段
SELECT empno,ename FROM t_emp;# 也可只查询对应字段中间逗号分隔
SELECT empno,sal*12 FROM t_emp;# 可以对属性进行运算
SELECT empno,sal*12 AS "年收入" FROM t_emp;# 可以对 属性运算的结果用 AS 或空格起个别名
SELECT empno,sal*12 "年收入啊" FROM t_emp;

执行数据顺序 1.读取文件 2. FROM 语句 3.SELECT 语句

数据分页

SELECT empno,ename FROM t_emp LIMIT 10,5;# 用 LIMIT 可以做限制处理 0,5 是0-5条 10,5是10往后数5条 如果不够则显示存在的条数
SELECT empno,ename FROM t_emp LIMIT 5;#0可以不写 默认就是从0开始到指定的数据进行查询

执行数据顺序 1.读取文件 2.FROM 语句 3.SELECT 语句 4.LIMIT 进行限制

查询排序

SELECT empno,ename,sal FROM t_emp ORDER BY sal  ASC;# ORDER BY排序 DESC代表降序 ASC 代表升序
SELECT empno,ename,sal,hiredate FROM t_emp ORDER BY sal  DESC, hiredate  ASC;# 多重排序
SELECT
 empno,ename,sal,deptno FROM t_emp ORDER BY deptno ,sal DESC;# 第一个是增序 第二个是降序
 SELECT
 empno,ename,sal FROM t_emp ORDER BY  sal DESC LIMIT 5;# 选择工资排名前5位的员工

执行数据顺序 1.读取文件 2.FROM 语句 3.SELECT 语句 4.ORDER BY 5.LIMIT 进行限制

查询去重

USE demo;
SELECT 
DISTINCT job# DISTINCT 是限制的意思 将查询的数据的重复部分删除 不会影响源数据
FROM t_emp;

使用DISTINCT的 SELECT 子句中只能查询一列数据如果查新多列去除重复记录就会失效
DISTINCT 关键字只能在 SELECT子句中使用一次多次书写会报错

条件查询

USE demo;
SELECT empno,ename,sal,deptno
FROM t_emp
WHERE sal>2000 AND (deptno=10 OR deptno=20);# 通过 WHERE 可以对查询的数据进行筛选 AND 是同时满足 OR 是满足其中之一

打印的数据默认是按主键升序排序

四目运算符

USE demo;
SELECT empno,ename,sal,hiredate
FROM t_emp
WHERE deptno=10 AND (sal+ISNULL(comm,0))*12>=15000#ISNULL() 是将属性如果为 NULL 则替换为指定值
AND DATEDIFF(NOW(),hiredate)/365>=20;# DATEDIFF() 函数返回两个日期之间的时间（日）

比较运算符

USE demo;
SELECT empno,ename,sal,hiredate,deptno,job
FROM t_emp
WHERE deptno IN(10,30) AND job!="SALESMAN"# IN 可以填写多个符合的数据
AND hiredate<"1985-01-01";

SELECT
ename,comm,sal
FROM t_emp
WHERE comm IS NOT NULL#非空 IS NULL 为空
AND sal BETWEEN 1000 AND 3000# BETWEEN AND 指一个范围
AND ename REGEXP "^[\\u4e00-\\u9fa5]{2,4}$";# 使用正则表达式

逻辑运算符

SELECT
ename,deptno,sal
FROM t_emp
WHERE NOT deptno IN(10,20) XOR sal>=2000;# NOT 表示不包括 取反的意思 
#XOR是异或 如果A为真，则查出不满足B条件数据;如果A为假，则查出满足B条件数据；

WHERE 子句中条件执行的顺序是从左往右应该把索引条件或筛选记录最多的条件写在最左侧
执行数据顺序 1.读取文件 2.FROM 语句 3.WHERE 4.SELECT 语句 5.ORDER BY 6.LIMIT 进行限制

高级查询

聚合函数

聚合函数在数据的查询分析中，应用十分广泛。聚合函数可以对数据求和 求最大值和最小值 求平均值等
SELECT
AVG(sal+IFNULL(comm,0)) "平均工资" #AVG 是对查询数据的非空值求取平均值 非数字数据统计结果为0
FROM t_emp;

SELECT SUM(sal) FROM t_emp
WHERE deptno IN (10,20);# 对求和的属性添加条件约束

SELECT MAX(sal) FROM t_emp# MAX 求最大值
WHERE deptno IN (10,20);# 对求最大值的属性添加条件约束
SELECT MAX(LENGTH(ename)) FROM t_emp# 名字字符串最长的

SELECT MIN(hiredate)# 求取数据的最小值
FROM t_emp;

SELECT count(*)# 打印所有数据的个数 包括空值
FROM t_emp;

SELECT count(comm)# 打印字段的数据的个数 不包括空值
FROM t_emp;

#查询10和20部门中 底薪超过2000并且工龄超过15年的员工个数
SELECT COUNT(*)
FROM t_emp
WHERE deptno in(10,20) AND sal>2000 
AND (DATEDIFF(NOW(),hiredate)/365)>15;

分组查询

why？

默认情况下汇总函数是对全表范围内的数据做统计
GROUP BY字句的作用是通过一定的规则将一个数据集划分成若干个小的区域，然后针对每个小区域分别进行数据汇总处理

SELECT deptno, ROUND(AVG(sal)) 
FROM t_emp GROUP BY deptno;# 不写 GROUP BY 会报错 这是基于每一组的数据进行操作查询 会查询出当前分组内容个数条数据

逐级分组

数据库支持多列分组条件，执行的时候逐级分组
查询每个部门里，每种职位的人员数量和平均底薪

SELECT deptno,job,COUNT(*),AVG(sal)
FROM t_emp
GROUP BY deptno,job# 对里面的多个属性进行分组 两个属性有一个不同则算独立的一个数据
ORDER BY deptno;
# select字句中的内容要遵守 select字句中可以包括聚合函数 或group by子句的分组列 起于内容均不可以出现在select子句中 因为数据匹配会出现问题

对分组的结果进行汇总计算

SELECT deptno,AVG(sal),SUM(sal),MIN(sal),COUNT(*)
FROM t_emp
GROUP BY deptno  WITH ROLLUP;# WITH ROLLUP对数据进行一次汇总计算

GROUP_CONCAT

# 将非分组的数据 符合条件的某个字段拼接成一个字符串 打印出来
SELECT deptno,COUNT(*),GROUP_CONCAT(ename)# 将符合条件的内容按组打印出来
FROM t_emp
WHERE sal>=2000
GROUP BY deptno;

执行顺序 1.读取文件 2.FROM 语句 3.WHERE 4.GROUP_BY 5.SELECT 语句 6.ORDER BY 7.LIMIT 进行限制

HAVING字句

why？

因为GROUP BY语句的局限性: WHERE执行会在GROUP BY之前所以WHERE后面如果有聚合函数的话不知道最终得到的数据（无法确定数据范围）

要和GROUP BY 语句联合使用

SELECT deptno
FROM t_emp # 直接在此处用 WHERE AVG(sal)>=2000会报错 因为WHERE执行会在GROUP BY之前 所以不知道最终的数据
GROUP BY deptno HAVING AVG(sal)>=2000;# HAVING在GROUP BY执行后立即执行

# 查询每个部门中 1982年以后入职的员工超过两个的部门编号
SELECT deptno 
FROM t_emp
WHERE hiredate>="1982-01-01"# 由于where语句在GROUP BY 之前执行 所以不能使用聚合函数 但可以用作普通的条件判断
GROUP BY deptno HAVING  COUNT(*)>=2;#HAVING 的执行顺序在 GROUP BY 后面立即执行 可以使用聚合函数

sal>=AVG(sal)# 普通字段不能和聚合函数直接比较
# GROUP BY 1;
SELECT deptno,COUNT(*)
FROM t_emp #  
WHERE deptno IN(10,20)
GROUP BY 1;# 1  代表用select 中的第一个字段作为分组的属性

表连接查询

从多张表中提取数据

多张表提取数据，必须指定关联的条件，不然就会出现无条件连接，多张表数据会交叉连接，产生笛卡尔积

# 查询每名员工的部门信息
SELECT e.empno,e.ename,d.dname
FROM t_emp e JOIN t_dept d ON e.deptno=d.deptno;# 使用别名

表连接分为两种：内连接和外连接
内连接是结果集中只保留符合连接条件的记录
外连接是不管符合不符合连接条件，记录都要保留在结果集中

SELECT e.empno,e.ename,d.dname
FROM t_emp e JOIN t_dept d ON e.deptno=d.deptno;# 使用别名 

SELECT e.empno,e.ename,d.dname
FROM t_emp e JOIN t_dept d WHERE e.deptno=d.deptno;# 使用WHERE

SELECT e.empno,e.ename,d.dname
FROM t_emp e , t_dept d WHERE e.deptno=d.deptno;# 使用WHERE

内连接

SELECT … FROM 表1 JOIN 表2 ON 条件 JOIN 表3 ON 条件 …

SELECT … FROM 表1 JOIN 表2 ON 条件 JOIN 表3 WHERE 条件 …

SELECT … FROM 表1 , 表2 ON 条件 , 表3 WHERE 条件 …

# 连接表 如果没有相同字段 可以按逻辑来关联
SELECT t.empno,t.ename,d.deptno,t.sal,t.job,s.grade
FROM t_emp t JOIN t_dept d ON t.deptno=d.deptno
JOIN t_salgrade s ON t.sal BETWEEN s.losal AND s.hisal;# 拿员工的工资信息和 薪水等级表 做对比

# 查询与SCOTT相同部门的员工都有谁 
SELECT e1.ename
FROM t_emp e1
WHERE e1.deptno=(SELECT deptno  FROM t_emp e2 WHERE e2.ename="SCOTT" )# 每当查询一个数据 这个条件语句就会被执行一遍 效率很低
AND e1.ename!="SCOTT";

# 使用表连接的方式替换上面的实现
SELECT e1.ename
FROM t_emp e1 JOIN t_emp e2 ON e1.deptno = e2.deptno# 同一个表被多次查询需要用join 分隔下
WHERE e1.ename!="SCOTT" AND e2.ename="SCOTT"; 

# 查询底薪超过公司平均底薪的员工
SELECT e1.empno,e1.ename,e1.sal
FROM t_emp e1 JOIN t_emp e2 ON e1.sal > AVG(e2.sal);# 这种写法报错

# 可以将要查询的内容 用select包起来
SELECT e1.empno,e1.ename,e1.sal
FROM  t_emp e1 JOIN (SELECT AVG(sal) avg  FROM t_emp) e2 ON e1.sal >= e2.avg;# 这种写法ok

# 查询 RESEARCH 部门的人数 最高底薪 最低底薪 平均底薪 平均工龄
SELECT COUNT(*),MAX(sal),MIN(sal),AVG(sal),FLOOR(AVG(DATEDIFF(NOW(),hiredate)/365))
FROM t_emp e JOIN t_dept d ON d.deptno=e.deptno #AND d.dname="RESEARCH"
WHERE d.dname="RESEARCH";# WHERE 和后面添加 AND 结果一样

# 查询每个底薪超过部门平均底薪的员工信息
# 1.先把每个部门的平均工资打印出来
# 2.把上面的信息包起来 跟要查询的数据进行部门编号比较 
SELECT e1.ename,e1.sal
FROM t_emp e1 JOIN (SELECT deptno, AVG(sal) avg FROM t_emp GROUP BY deptno) e2#
ON e1.deptno=e2.deptno AND e1.sal>e2.avg;

外连接

有些数据是临时的，没有对应的联系，但是需要查询到它，所以引入了外连接
跟内连接的区别就是不符合条件的记录也会保留
关键字 LEFT JOIN 和 RIGHT JOIN
左连接就是保留左表所有的记录，与右表做连接。如果右表有符合条件的记录就与左表连接，反之用null与左表连接

# 陈浩是没有部门信息的
SELECT e.ename,e.sal,e.deptno
FROM t_emp e LEFT JOIN t_dept s#会将不匹配的陈浩给显示出来
ON e.deptno=s.deptno;

# 等同上面那个 使用了RIGHT 调换了两个查询表的位置
SELECT e.ename,e.sal,e.deptno
FROM t_dept s RIGHT JOIN t_emp e#会将不匹配的陈浩给显示出来
ON e.deptno=s.deptno;

SELECT e.deptno,COUNT(e.deptno) 
FROM t_dept d LEFT JOIN t_emp e#会将不匹配的陈浩给显示出来
ON d.deptno=e.deptno
GROUP BY d.deptno;

(SELECT COUNT(d.deptno)
FROM t_emp e LEFT JOIN t_dept d 
ON e.deptno = d.deptno
GROUP BY d.deptno)
UNION # 使用union关键词把两个外连接查询的结果连在一起
(SELECT COUNT(*)
FROM t_emp e RIGHT JOIN t_dept d 
ON e.deptno = d.deptno
GROUP BY d.deptno);

# 查询每名员工的编号 姓名 部门 月薪 工资等级 工龄 上司编号 上司姓名 上司部门 deptno  编号
SELECT 
e.empno,e.ename,e.deptno,e.sal,  
s.grade,

FLOOR(DATEDIFF(NOW(),e.hiredate)/365),
t.empno,t.ename,t.dname

FROM t_emp e LEFT JOIN t_dept d ON e.deptno=d.deptno
LEFT JOIN t_salgrade s ON e.sal BETWEEN s.losal AND  s.hisal# 工资的范围在工资等级表之间
LEFT JOIN 
(SELECT e1.empno,e1.ename,d1.dname
FROM t_emp e1 JOIN t_dept d1
ON e1.deptno=d1.deptno) t ON e.mgr=t.empno;

内连接 where 和 on 的用法没有区别效率和结果一样
外连接里面 where 和 on 不一样，where 会把不符合条件的记录给过滤掉 on会以null值保留下来所以多数用where

子查询

WHERE 子查询效率较慢（每条数据查询都会被执行一遍）所以条件要改成表连接
FROM 只执行一次所以查询效率很高
SELECT 每输出一条记录都要执行一次效率很低

单行子查询和多行子查询

单行子查询的结果集只有一条记录，多行子查询结果集有多行记录
多行子查询只能出现在WHERE字句和FROM字句中
子查询要转换成表连接不然效率太低

# 如何用子查询查找 FORD 和 MARTIN 两个人的同事？

SELECT ename
FROM t_emp
WHERE
# 比较部门相同的部门
deptno IN # 此处用in 因为返回的结果不止一个

(SELECT deptno
FROM t_emp
WHERE ename IN ("FORD","MARTIN"))# 先查一下 这两个人所在的部门 取出来

AND ename NOT IN ("FORD","MARTIN");# 去掉他们本身

WHERE字句中可以使用IN ALL ANY EXISTS 关键字来处理多行表达式结果集的条件判断

# 查询比 "FORD","MARTIN" 底薪高的员工
SELECT ename
FROM t_emp
WHERE sal >=ALL # ALL是要对比所有的数据
(SELECT sal
FROM t_emp
WHERE ename IN ("FORD","MARTIN"))# 先把他俩的工资拿出来做对比
AND ename NOT IN ("FORD","MARTIN");

# 查询比 "FORD","MARTIN" 底薪高的员工
SELECT ename
FROM t_emp
WHERE sal >=ANY # ANY是结果集中任意的数据
(SELECT sal
FROM t_emp
WHERE ename IN ("FORD","MARTIN"))# 先把他俩的工资拿出来做对比
AND ename NOT IN ("FORD","MARTIN");

# 查询工资等级是3级或者4级的员工信息
SELECT ename,sal,empno
FROM t_emp

WHERE EXISTS (# EXISTS 可以把所有的字查询条件都写在这里面 返回结果不为空即可
SELECT * FROM t_salgrade s
WHERE sal BEWEEN s.losal AND s.hisal 
AND s.grade >=4
);

INSERT语句

INSERT INTO t_dept VALUES(40,"技术部","广州"),(50,"客服部","上海");# 插入多个数据
# 通过查询 向技术部添加一条员工记录
INSERT INTO t_emp

VALUES(8001,"小明","SALESMAN",8000,"1988-12-20",2000,NULL,
(SELECT deptno FROM t_dept WHERE dname="技术部"));#  这个查询必须是单行单列的才行

INSERT 方言

INSERT t_emp # INTO 可以省略
SET empno=8002,ename="JACK",job="CODE",mgr=8000,
hiredate="1999-3-14",sal=2500,comm=NULL,deptno=50;

DELETE FROM t_emp WHERE empno=8002; #删除一条数据

IGNORE

插入多个数据时只插入不重复的数据，遇到重复数据不会冲突会继续执行

INSERT IGNORE INTO t_emp # IGNORE 会忽略重复字段 继续往下插入
VALUES(8003,"小红","SALESMAN",8000,"1988-12-20",2000,NULL,40);

**UPDATE语句 **

用于修改数据

# 把每个员工的编号和上司的编号+1 用 ORDER BY 字句完成
UPDATE t_emp
SET empno=empno+1,mgr=mgr+1
ORDER BY empno DESC;#分组 SET语句是最后执行的 

# 把月收入前三名的员工底薪剪100元，用LIMIT字句完成
# 先降序排列 然后取前三条
UPDATE t_emp
SET sal=sal-100
ORDER BY sal DESC
LIMIT 3;#分组 SET语句是最后执行的

UPDATE的表连接

UPDATE … JOIN … [ON] 条件(可不写)

SET 字段1 = 值1，字段2 = 值2，…;

# 多表修改
# 把 ALLEN 调往 RESEARCH 部门 职务调整为 ANALYST
UPDATE t_emp e JOIN t_dept d #没有使用 ON 连接条件
SET e.deptno=d.deptno,e.job="ANALYST",d.loc="北京"
WHERE e.ename="ALLEN" AND d.dname="RESEARCH";

# 把底薪低于公司平均水平低薪的员工 底薪增加150元
UPDATE t_emp e JOIN (SELECT AVG(sal) avg FROM t_emp) a ON e.sal<a.avg#表连接方法 效率高
SET e.sal = e.sal+150;

UPDATE外连接

# 把没有部门的员工 或 SALES 部门低于2000底薪的员工 都调往20部门
UPDATE t_emp e LEFT JOIN t_dept d ON e.deptno=d.deptno #先将部门匹配下 外链接会把数据为null的也打印出来
SET e.deptno = 20
WHERE e.deptno IS NULL OR (d.dname="SALES" AND e.sal<2000);# 将数据中部门为NULL的 和部门为SALES且底薪小于2000的

DELETE语句

IGNORE 遇到异常继续往下执行删除操作
删除表的数据不是删除表删除表用DROP
执行顺序 1.FROM 2.WHERE 3.ORDER BY 4.LIMIT 5.DELETE

# 删除10部门 工龄 超过20年的员工
DELETE FROM t_emp #删除某个表
WHERE deptno=10 AND DATEDIFF(NOW(),hiredate)/365>=20;

# 删除20部门中 工资最高的那个
DELETE FROM t_emp #
WHERE  deptno=20
ORDER BY sal DESC LIMIT 1;

# 删除SALES部门和该部门的全部员工记录
DELETE e,d
FROM t_emp e JOIN t_dept d #
ON e.deptno = d.deptno 
WHERE d.dname="SALES"; # 删除指定部门

# 删除每个低于部门平均底薪的员工记录
DELETE t
FROM t_emp t JOIN
(SELECT deptno, AVG(sal) avg FROM t_emp GROUP BY deptno) e# 先取出平均底薪 deptno 属性后期要用到 所以此处要弄出来
ON t.deptno=e.deptno AND t.sal<e.avg; 

# 删除员工KING和他的直接下属的员工记录，用表连接实现
DELETE e #不能直接删除 l 

FROM t_emp e JOIN 
(SELECT empno FROM t_emp WHERE ename="KING") l #先找到KING的下属
WHERE e.mgr = l.empno OR e.empno = l.empno # 找到上司等于L的数据 或者 部门编号跟L一样的就是KING本人

# 删除 SALES部门的员工，以及没有部门的员工 
# 外连接实现
 
DELETE e # 选择要删除的表 
FROM t_emp e LEFT JOIN t_dept d # 通过外连接会匹配null值
ON e.deptno = d.deptno # 连接条件
WHERE d.dname="SALES" OR e.deptno IS NULL;# 这个是删除的条件

快速删除数据表全部记录

DELETE语句是在事务机制下删除记录删除记录之前先把将要删除的记录保存到日志文件中然后再删除记录
TRUNCATE 语句在事务机制之外删除记录速度远超过DELETE语句

# TRUNCATE 清空一个数据库 每次只能清空一个表
TRUNCATE TABLE t_emp;

MYSQL函数

函数	功能	用例
ABS	绝对值	ABS(-100)
ROUND	四舍五入	ROUND(4.62)
FLOOR	向下取整	FLOOR(9.9)
CEIL	向上取整	CEIL(3.2)
POWER	幂函数	POWER(2,3)
LOG	对数函数	LOG(7,3)
LN	对数函数	LN(10)

函数	功能	用例
SQRT	开平方	SQRT(9)
PI	圆周率	PI()
SIN	三角函数	SIN(1) 参数是弧度
COS	三角函数	COS(1) 参数是弧度
TAN	三角函数	TAN(1) 参数是弧度
COT	三角函数	COT(1) 参数是弧度
RADIANS	角度转换弧度	RADIANS(30)
DEGREES	弧度转换角度	DEGREES(1)

日期函数

# 日期函数    # 日期      # 时间
SELECT NOW(), CURDATE(), CURTIME();

# 日期格式化函数
SELECT DATE_FORMAT(NOW(),"%Y");# 提取哪一年

占位符	作用	占位符	作用
%Y	年份	%m	月份
%d	日期	%w	星期(数字)
%W	星期(名称)	%j	本年第几天
%U	本年第几周	%H	小时(24)
%h	小时(12)	%i	分钟
%s	秒	%r	时间(12)
%T	时间(24)

# 1981上半年入职的员工多少人
SELECT COUNT(*)
FROM t_emp
WHERE DATE_FORMAT(hiredate,"%Y")=1981
AND DATE_FORMAT(hiredate,"%m") <=6 ;

日期偏移计算

DATE_ADD()函数可以实现日期的偏移计算 而且时间单位很灵活
（日期,INTERVAL 偏移量 时间单位）
SELECT DATE_ADD(NOW(),INTERVAL 10 DAY),DATE_ADD(NOW(),INTERVAL -300 MINUTE);
DATE_DIFF(日期，日期)# 两个日期相差多少天

字符函数

函数	功能	用例
LOWER	转换小写字符	LOWER
UPPER	转换大写字符	UPPER
LENGTH	字符数量	LENGTH
CONCAT	连接字符串	CONCAT(sal,"$")
INSTR	字符出现的位置	INSTR(ename,“A”)
INSERT	插入/替换字符	INSERT(“你好”,1,0,“先生”)
REPLACE	替换字符	REPLACE(“你好先生”,“先生”,“女士”)

函数	功能	用例
SUBSTR	截取字符串	SUBSTR(“你好世界”,3,4)
SUBSTRING	截取字符串	SUBSTRING(“你好世界”,3,2)
LPAD	左侧填充字符	LPAD(“Hello”,10,"*")
RPAD	右侧填充字符	RPAD(“Hello”,10,"*")
TRIM	去除首尾空格	TRIM(" 你好先生 ")

SELECT SUBSTR("你好世界",1,4)# 从那个下标开始 截取多少个字符
SELECT SUBSTRING("你好世界",1,4) # 从那个下标开始 截取多少个字符
SELECT LPAD("Hello",10,"*")# 左侧填充字符 下标从0开始 为空则填充
SELECT RPAD("Hello",10,"*")# 右侧填充字符 下标从0开始 为空则填充
SELECT TRIM(" 你好 先生 啊") ;# 去除首尾空格

条件函数

IFNULL(表达式,值)
IF(表达式,值1,值2)

# SALES 部门发礼品A 其余部门发放礼品B 打印每名员工获得的礼品
SELECT 
e.empno,ename,d.dname,
IF(d.dname="SALES","礼品A","礼品B")
FROM t_emp e JOIN t_dept d ON e.deptno = d.deptno;

复杂的条件判断可以用条件语句来实现比IF语句功能更强大

/*
SALES 部门去P1地点
ACCOUNTING 部门去P2地点
RESEARCH 部门去P3地点
*/
SELECT e.empno,e.ename,
CASE 
	WHEN d.dname="SALES"THEN"p1"
  WHEN d.dname="ACCOUNTING"THEN"p2"
  WHEN d.dname="RESEARCH"THEN"p3"
END num
FROM t_emp e JOIN t_dept d ON e.deptno=d.deptno;

/*
SALES 部门中工龄超过20年 涨薪10%
SALES 部门中工龄不满20年 涨薪5%
ACCOUNTING部门 涨薪300
RESEARCH 部门里低于部门平均底薪，涨薪200
没有部门的员工 涨薪100
*/
UPDATE t_emp e LEFT JOIN t_dept d ON e.deptno=d.deptno
# 取一下平均底薪
LEFT JOIN (SELECT deptno,AVG(sal) avg FROM t_emp GROUP BY deptno) t
ON e.deptno = t.deptno
SET e.sal=(# 开始赋值
CASE# 根据条件改值
WHEN d.dname="SALES" AND DATEDIFF(NOW(),e.hiredate)/365>=20
THEN e.sal*1.1
WHEN d.dname="SALES" AND DATEDIFF(NOW(),e.hiredate)/365<20
THEN e.sal*1.05
WHEN d.dname="ACCOUNTING部门"  
THEN e.sal+300
WHEN d.dname="RESEARCH" AND  e.sal<t.avg
THEN e.sal+100
WHEN e.deptno IS NULL
THEN e.sal+100
ELSE e.sal
END);

事务机制

5.0版本以后引入事务机制
避免数据的写入直接操作数据文件//如果写到一半宕机就。。。

Mysql总共有5种日志，其中只有redo日志和undo日志和事务有关

数据拷贝进undo日志里面然后记录修改到redo日志里面最后同步数据，如果出现意外再次启动数据库时候查看redo日志就可以继续执行被中断的操作

事务是一个或多个SQL语句组成的整体，要么全部执行成功，要么全都执行失败
默认情况下 MYSQL执行每条SQL语句都会自动开启和提交事务
为了让多条SQL语句纳入到一个事务之下可以手动管理事务

START TRANSACTION# 开启手动管理事务
# 逐条开启
DELETE FROM t_emp;
DELETE FROM t_dept;

SELECT * FROM t_emp;
SELECT * FROM t_dept;
COMMIT;# 不提交的话 真实数据是没有同步的
ROLLBACK;# 不让redo日志跟数据文件做修改

事务四个特性

原子性：一个事务中的所有操作要么全部完成要么全部失败不允许停留在中间某个状态
一致性：不管给定时间内并发事务有多少必须保证运行结果的一执行
隔离性：事务不受其他并发事务的影响，给点时间内该事务是数据库唯一运行的事务
持久性：事务一旦提交，结果便是永久性的，即便宕机仍然可以依靠事务日志完成数据的持久化

事务的四个隔离级别

序号	隔离级别	功能
1	read uncommitted	读取未提交数据
2	read committed	读取已提交数据
3	repeatable read	重复读取
4	serializable	序列化

使用场景

read uncommitted 适用于买票允许读取其他事务中的为提交的临时数据，当票被别人下单时

SET SESSION TRANSACTION ISOLATION LEVEL
READ UNCOMMMITTED;
# 此时可以读取到别的表的临时数据

read committed 适用于转账，当同时有转入转出操作的时候，应该已最终的提交数据为准，不然转账错误的临时数据被别的表读取就会导致数据最终错误
```
SET SESSION TRANSACTION ISOLATION LEVEL
READ COMMMITTED;
# 此时可以读取到别的表提交后的数据
```
repeatable read 适用于特价商品的时候，下单后的商品提交的时候哪怕商品的价格有改动依然不受影响还是老的价格

SET SESSION TRANSACTION ISOLATION LEVEL
repeatable read;
# 此时一旦执行后会产生undo日志,原表的数据之后发生的变动不会受到影响

serializable 事务的序列化，由于事务并发执行所带来的各种问题，前三种隔离级别只适用在某些业务场景中，但是序列化的隔离性，让事务逐一执行，就不会产生上述问题了

SET SESSION TRANSACTION ISOLATION LEVEL
serializable;
# 当前的事务执行要等待其他事务结束之后才能执行，不再出现并发执行，别的事务没有提交就一直等待,缺点是并发性会急剧下降

数据导入和导出

辣椒炒鸡蛋

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql数据库

SQL 语言DML 数据操作语言增删改查DCL 数据控制语言分配权限用户创建事物管理DDL 数据定义语言管理逻辑裤数据表视图索引SQL 语句不区分大小写分号结尾空格分割‘’#" 单行注释 /* 多行注释 */DDL 语句逻辑库(逻辑空间)CREATE DATABASE 逻辑库名称；创建一个逻辑库(逻辑空间)SHOW DATABASES ; 显示所有的逻辑库DROP DATABASE 逻辑库名称；删除指定逻辑库逻辑
复制链接

扫一扫

专栏目录