SQL常用操作

目录

数据库操作

创建数据库

删除数据库

查看所有数据库

表操作

使用数据库

创建表

添加列

删除表

查看所有表

查看表结构

向表中插入记录

查询表中所有记录

修改表中id为1的记录

删除id为2的记录

查询表中的总记录数

字符类型 char与varchar区别

数字类型

日期

文件

字段约束

基础函数

lower转小写 upper转大写

length(字段名)数据的长度

substr(name,1,3)截取[1,3] concat(name,'123')拼接 replace(name,'a','555')替换

ifnull

round  ceil  floor

uuid

now

year month day

转义字符

条件查询

distinct去重

where 不能使用别名

通配符like

null

betwneen and

limit

order by

聚合 aggrefation

count

max/min

sum/avg

分组group by

group by

having

---

数据库操作

创建数据库

create database 数据库名 DEFAULT CHARACTER SET utf8;

删除数据库

drop database 数据库;

查看所有数据库

show databases;

表操作

使用数据库

use 库名;

创建表

create table aaa(
id int primary key auto_increment,#主键自增
bbb varchar(100)
);

添加列

alter table aaa add column cc NUMERIC(7,2)
NUMERIC精确数字数据类型，7为总位数，2为小数点后的位数，也就是整数位为5

删除表

drop table aaa;

查看所有表

show tables;

查看表结构

desc aaa;

向表中插入记录

insert into aaa values(null,"雷猴");

查询表中所有记录

SELECT * FROM aaa;

修改表中id为1的记录

update aaa set bbb="雷猴" where id=1;

删除id为2的记录

delete from aaa where id=2;

查询表中的总记录数

select count(*) from aaa;

字符类型 char与varchar区别

char长度固定，不足使用空格填充，最多容纳2000个字符，char(11)存储abc，占11位，查询速度快，但浪费空间

archar变长字符串，最多容纳4000个字符，varchar(11)存储abc，只占3位。查询稍慢，但节省空间。Oracle为varchar2

utf8编码下，一个汉字占3个字节

数字类型

• tinyint,int整数类型
• float,double小数类型
• numeric(5,2) decimal(5,2)—也可以表示小数,表示总共5位,其中可以有两位小数
• decimal和numeric表示精确的整数数字

日期

• date 包含年月日
• time时分秒
• datetime包含年月日和时分秒
• timestamp时间戳，不是日期，而是从1970年1月1日到指定日期的毫秒数

文件

• blob 二进制数据，可以存放图片、声音，容量4g。早期有这样的设计。但其缺点非常明显，数据库庞大，备份缓慢，这些内容去备份多份价值不大。同时数据库迁移时过大，迁移时间过久。所以目前主流都不会直接存储这样的数据，而只存储其访问路径，文件则存放在磁盘上。

字段约束

create table abc(
id int primary key auto_increment,//主键自增
password varchar(50) not null,//非空约束
username varchar(50) unique//唯一约束
);

基础函数

lower转小写 upper转大写

select upper(name) from aaa;

length(字段名)数据的长度

substr(name,1,3)截取[1,3] concat(name,'123')拼接 replace(name,'a','555')替换

SELECT dname,SUBSTR(dname,1,3) FROM dept; --截取[1,3]
select dname,concat(dname,'123') X from dept --拼接数据
select dname,replace(dname,'a','666') X from dept --把a字符替换成666

ifnull

select ifnull(comm,10) comm from aaa; #判断,如果comm是null,用10替换

round  ceil  floor

select comm,round(comm) from emp//四舍五入
select comm,round(comm,1) from emp//四舍五入并保留一位小数
select comm,ceil(comm) ,floor(comm) from empceil向上取整,floor向下取整

uuid

select uuid()

now

select now() -- 年与日 时分秒
select curdate() --年与日
select curtime() --时分秒      

year month day

hour()时 minute()分 second()秒
select now(),hour(now()),minute(now()),second(now()) from emp ;
year()年 month()月 day()日
select now(),year(now()),month(now()),day(now()) from emp ;

转义字符

'作为sql语句符号，内容中出现单撇就会乱套，进行转义即可
select 'ab'cd' -- 单引号是一个SQL语句的特殊字符
select 'ab\'cd' --数据中有单引号时,用一个\转义变成普通字符

条件查询

distinct去重

SELECT DISTINCT loc FROM dept;

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 --相当于两个条件的|关系
select name, sal from emp where sal=1400 or sal=1600 or sal=1800;
-- 或
select name, sal from emp where sal in(1400,1600,1800);
select name, sal from emp where sal not in(1400,1600,1800);

通配符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后面有两个字符的 _代表一个字符位置

null

select * from emp where mgr is null --过滤字段值为空的
select * from emp where mgr is not null --过滤字段值不为空的

betwneen and

SELECT * FROM emp
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条记录--前三条

order by

SELECT * FROM emp order by sal #默认升序
SELECT * FROM emp order by sal desc #降序

聚合 aggrefation

count

select count(*) from emp --底层优化了
select count(1) from emp --效果和*一样
select count(字段) from emp --慢,只统计非NULL的

max/min

select max(sal) from emp --求字段的最大值
select max(sal) sal,max(comm) comm from emp
select min(sal) min from emp --获取最小值
select min(sal) min,max(sal) max from emp --最小值最大值
SELECT ename,MAX(sal) FROM emp group by ename --分组 

sum/avg

select count(*) from emp --总记录数
select sum(sal) from emp --求和
select avg(sal) from emp --平均数

分组group by

group by

SELECT deptno,MAX(sal),AVG(sal) FROM emp
GROUP BY deptno #按照deptno分组
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

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 #次数多的