数据库操作常用SQL

常用SQL"/>

数据库操作常用SQL

目录

一、库常用操作

建库：create  

删库：drop  

查看：show

进入：use

二、表常用操作

建表：create

删表：drop

修改表：alter 

查看所有表：show

查看表结构：desc

三、表数据操作

插入数据：insert into

查看数据：select

修改数据：update

删除数据：delete

四、基础函数

数据转小写：lower

数据转大写：upper

数据长度：length

截取数据：substr

拼接数据：concat

替换数据：replace

非空判断：ifnull

四舍五入：round

向上取整：ceil

向下取整：floor

查看uuid：uuid

当前时间：now

当前日期：year & month & day

转义字符：\

五、条件查询

去除重复：distinct

条件查询：where

模糊查询：like

空值操作：null

范围查询：between and

分页查询：limit

数据排序：order by

六、聚合

统计结果总数：count

求字段最大值：max

求字段最小值：min

计算数据和：sum

计算平均数：avg

七、分组

分组：group by

过滤：having

八、多表联查

多表嵌套查询：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 set 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 empGROUP BY deptno --按照deptno分组SELECT job,MAX(sal),AVG(sal) FROM empGROUP BY job --按照job分组SELECT deptno,job,MAX(sal),AVG(sal) FROM empGROUP 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 empGROUP 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 mwhere 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_idjoin tb_menus m on rm.menu_id=m.idwhere ur.user_id=1;

 join 连接有三种：内连接 inner join、左（外）连接 left join、右（外）连接 right join。

        inner join 两边都对应有记录的才展示，其他去掉；

        left join 左边表中的数据都出现，右边没有数据以NULL填充；

        right join 边表中的数据都出现，左边没有数据以NULL填充。