MySql数据库操作大总结(持续更新中)

数据库操作

建库

CREATE DATABASE company DEFAULT CHARACTER SET utf8;

查库

show databases;

删库

Drop database company;

表基本操作

建表（部门）

CREATE TABLE department(
deptno INT PRIMARY KEY AUTO_INCREMENT,
dname VARCHAR(20) NOT NULL,
loc VARCHAR(13) NOT NULL
);

删表

Drop table department;

查表

show tables;

存入数据

INSERT department(dname,loc) VALUES('CPU','控制器');
INSERT department(dname,loc) VALUES('ALU','运算器');
INSERT department(dname,loc) VALUES('RAM','存储器');
INSERT department(dname,loc) VALUES('IO','输入输');

删除数据

Delete from department where id=2;

修改数据

update department set tel=555 where id=1;

基本函数

lower

SELECT 'ABC',LOWER('ABC') from dual; --数据转小写

upper

select upper(dname) from dept --数据转大写

length

select length(dname) from dept --数据的长度

substr

SELECT dname,SUBSTR(dname,1,3) FROM dept; --截取[1,3]

concat

select dname,concat(dname,'123') X from dept --拼接数据

replace

select dname,replace(dname,'a','666') X from dept --把a字符替换成666

round,ceil,floor

--直接四舍五入取整

select comm,round(comm)  from emp

--四舍五入并保留一位小数

select comm,round(comm,1)  from emp

--ceil向上取整,floor向下取整

select comm,ceil(comm) ,floor(comm)  from emp

uuid

SELECT UUID()

now,curdate,curtime

select now() --年月日 时分秒

select curdate() --年与日

select curtime() --时分秒

year,month,day

--year()年 month()月 day()日

select now(),year(now()),month(now()),day(now()) from emp;

--hour()时  minute()分 second()秒

select now(),hour(now()),minute(now()),second(now()) from emp;

查询操作

排序

Select * from department order by tel desc;#倒序
Select * from department order by tel asc;#正序

全查

select * from department;

where查询

select * from emp

select * from emp where 1=1 --类似没条件

select * from emp where 1=0 --条件不成立

select * from emp where empno=100 --唯一条件

select * from emp where ename='tony'  and deptno=2 --相当于两个条件的&关系

select * from emp where ename='tony'  or deptno=1 --相当于两个条件的|关系

like查询

select * from emp where ename like 'l%' --以l开头的

select * from emp where ename like '%a' --以a结束的

select * from emp where ename like '%a%' --中间包含a的

select * from emp where ename like 'l__'  --l后面有两个字符的 _代表一个字符位置

去除重复的记录行

select distinct * from department;

between and查询

select * from emp where sal>3000 and sal<10000

select * from emp where sal>=3000 and sal<=10000--等效

select * from emp where sal between 3000 and  10000--等效

limit查询

select * from emp limit 2 --列出前两条

select * from emp limit 1,2 --从第二条开始,展示2条记录

select * from emp limit 0,3 --从第一条开始,展示3条记录--前三条

聚合

查询记录总数

select count(*) from department;
select count(1) from department;#与前者相同
select count(id) from department;#不带null的

max,min,sun,avg

select max(sal) from emp --求字段的最大值
select min(sal) min from emp --获取最小值
select sum(sal) from emp --求和

select avg(sal) from emp --平均数

分组

group by(分组)

SELECT job,MAX(sal),AVG(sal) FROM emp

GROUP BY job #按照job分组

SELECT deptno,job,MAX(sal),AVG(sal) FROM emp

GROUP BY deptno,job #deptno和job都满足的

having(筛选)

#平均工资小于8000的部门
select deptno, AVG(sal)  from emp 
group by deptno  #按部门分组
having AVG(sal) <8000 #查询条件,类似where,但是group by只能配合having

#deptno出现的次数
SELECT deptno,COUNT(deptno)  FROM emp
GROUP BY deptno #按deptno分组
HAVING COUNT(deptno)>1 #次数多的

事务

简单的事务回滚

set @@autocommit = 0;#手动提交自动提交是1
begin#开始事务
#进行的事务
rollback#回滚事务（回滚后类似于事务并没有发生）
commit#事务过程（完美执行后需要提交）

防止错误删除（保存点）

set @@autocommit = 0;#手动提交自动提交是1
savepoint wxn;#名称随意
begin#开始事务
#进行的事务
rollback to wxn#回滚事务（回滚至发生删除前）
commit#事务过程会自动删除保存点

子查询（嵌套查询）

#单值子查询： 必须得是单值（job=一个值）
SELECT * FROM emp
WHERE job = (SELECT job FROM emp WHERE empno=200)

#多值子查询：可以是多个值（job in 多个值）
SELECT * FROM emp
WHERE job IN (SELECT job FROM emp WHERE empno IN (100,200) )