数据库操作常用SQL
目录
多表嵌套查询：select ... from ... where ... in ( select ...where ...) ;
关联查询方案1：select ... from ... , ... where ... and ... ;
关联查询方案2：select ... from ... join ... on ... where ... ;
SQL语句
定义
结构化查询语言(Structured Query Language)简称SQL，是一种特殊目的的编程语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统；同时也是数据库脚本文件的扩展名。
SQL 是1986年10 月由美国国家标准局（ANSI）通过的数据库语言美国标准，接着，国际标准化组织（ISO）颁布了SQL正式国际标准。
分类
DML（Data Manipulation Language）数据操纵语言
如：insert，delete，update，select（插入、删除、修改、检索）简称CRUD操新增Create、查询Retrieve、修改Update、删除Delete
DDL（Data Definition Language）数据库定义语言
如：create table之类
DCL（Data Control Language）数据库控制语言
如：grant、deny、revoke等，只有管理员才有相应的权限
DQL（Data Query Language）数据库查询语言
一、库常用操作
建库：create
create database db_test DEFAULT CHARACTER SET utf8;
删库：drop
drop database db_test;
查看：show
show databases;
进入：use
use db_test;
二、表常用操作
建表：create
create table tb_test(
id int primary key auto_increment,
test_name varchar(100),
test_tel varchar(50)
);
删表：drop
drop table tb_test;
修改表：alter
alter table table_name add column_name datatype; --表中添加列
alter table table_name drop column column_name; --删除表中的列
alter table table_name alter column column_name datatype; --改变表中列的数据类型查看所有表：show
show tables;
查看表结构：desc
desc tb_test;
三、表数据操作
插入数据：insert into
insert into tb_door values(null,'小明',13800000000);
查看数据：select
select * from tb_test;修改数据：update
update tb_test test_name '张三' where id = 1;删除数据：delete
delete from tb_test where id = 1;四、基础函数
数据转小写：lower
SELECT 'ABC',LOWER('ABC') from tb_test;数据转大写：upper
select upper(test_name) from tb_test;
数据长度：length
select length(test_name) from tb_test; 截取数据：substr
select test_name,substr(test_name,1,3) from tb_test; --截取[1,3]拼接数据：concat
select test_name,concat(test_name,'abc') from tb_test; 替换数据：replace
select test_name,replace(test_name,'a','666') X from tb_test; --把a字符替换成666
非空判断：ifnull
select ifnull(test_tel,10) comm from tb_test; --判断,如果test_tel是null,用10替换
四舍五入：round
select comm,round(comm) from tb_test; --四舍五入取整
向上取整：ceil
select comm,ceil(comm) from tb_test; 向下取整：floor
select comm,floor(comm) from tb_test; 查看uuid：uuid
select uuid();当前时间：now
select now() -- 年与日 时分秒
select curdate() --年与日
select curtime() --时分秒
当前日期：year & month & day
select now(),year(now()),month(now()),day(now()) from tb_test ;
--year()年 month()月 day()日转义字符：\
'作为sql语句符号，内容中出现单撇就会乱套，进行转义即可
select 'ab'cd'; --单引号是一个SQL语句的特殊字符
select 'ab\'cd'; --数据中有单引号时,用一个\转义变成普通字符
五、条件查询
去除重复：distinct
SELECT DISTINCT test_name FROM tb_test;
条件查询：where
select * from tb_test where id =1;模糊查询：like
通配符%代表0到n个字符，通配符下划线_代表1个字符
select * from tb_test where test_name like '%a%';
select * from tb_test where test_name like 'a__'; --a后面有两个符的 _代表一个字符位置空值操作：null
select * from tb_test where test_name is null; --过滤字段值为空的
select * from tb_test where test_name is not null; --过滤字段值不为空的
范围查询：between and
select * from tb_test where test_tel between 100 and 500;分页查询：limit
select * from tb_test limit 2 --列出前两条
select * from tb_test limit 1,2 --从第二条开始,展示2条记录
select * from tb_test limit 0,3 --从第一条开始,展示3条记录--前三条
数据排序：order by
SELECT * FROM tb_test order by test_tel; --默认升序
SELECT * FROM tb_test order by test_tel desc; --降序
六、聚合
统计结果总数：count
select count(*) from tb_test --底层优化了
select count(1) from tb_test --效果和*一样求字段最大值：max
select max(t_num) from tb_test;求字段最小值：min
select min(t_num) from tb_test;计算数据和：sum
select sum(t_num) from tb_test;计算平均数：avg
select avg(t_num) from tb_test;七、分组
对查询结果进行分组统计
group by表示分组, having 子句类似where过滤返回的结果，但是group by只能配合having
分组：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 tb_test
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 --次数多的
八、多表联查
多表嵌套查询：select ... from ... where ... in ( select ...where ...) ;
按查询条件写出每一段查询语句，在进行嵌套。实行简单，但是低效，不推荐使用！
select permission from tb_menus where id in (
select menu_id from tb_role_menus where role_id=(
select role_id from tb_user_roles where user_id=1)
);关联查询方案1：select ... from ... , ... where ... and ... ;
多张表使用逗号" , " 连接，where 后跟关联条件，and 后跟查询条件。
select distinct m.permission from tb_user_roles ur,tb_role_menus rm , tb_menus m
where ur.role_id=rm.role_id and rm.menu_id=m.id
and ur.user_id=1 ；关联查询方案2：select ... from ... join ... on ... where ... ;
多张表使用 join 连接，on 后跟关联条件，where 后跟查询条件。
select distinct m.permission from tb_user_roles ur join tb_role_menus rm
on ur.role_id=rm.role_id
join tb_menus m on rm.menu_id=m.id
where ur.user_id=1;join 连接有三种：内连接 inner join、左（外）连接 left join、右（外）连接 right join。
inner join 两边都对应有记录的才展示，其他去掉；
left join 左边表中的数据都出现，右边没有数据以NULL填充；
right join 边表中的数据都出现，左边没有数据以NULL填充。
下一篇： python可视化来分析冠状病毒疫情