数据库相关知识点汇总

最新推荐文章于 2025-03-15 18:08:59 发布

ChiFeng半截篮

最新推荐文章于 2025-03-15 18:08:59 发布

阅读量1k

点赞数

文章标签： sql

本文链接：https://blog.csdn.net/weixin_45675015/article/details/119634761

版权

定义

结构化查询语言(Structured Query Language)简称SQL，是一种特殊目的的编程语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统；同时也是数据库脚SQL本文件的扩展名。

SQL语句分类

DDL（Data Definition Language）：数据定义语言，包括数据库相关和表相关的SQL语句，在数据库软件中保存数据需要先建库建表之后才能操作数据，例如：create table

DML（Data Manipulation Language）：数据操作语言，包括数据相关的操作例如CRUD，也就是我们常说的增(create)、删(delete)、改(update)、查(retrieve)，对应SQL语句的语言中insert、delete、update、select

DQL（DQL:Data Query Language）：数据查询语言，只包括查询相关的SQL

DCL（Data Control Language）：数据库控制语言，包括用户管理和权限分配相关SQL，如：grant、deny、revoke等，只有管理员才有相应的权限

TCL(Transaction Control Language)：事务控制语言，包括事务相关的SQL

DDL常见数据定义语言

数据库相关

1.查询所有数据库

show databases;

2.创建数据库

格式：create database 数据库名字；

指定字符集格式：create database 数据库名 character set utf8/gbk;

举例：

create database db1;

create database db2 character set utf8;

create database db3 character set gbk;

show databases;

3.查询数据库信息

格式：show create database db1;

4.删除数据库

格式：drop database db1;

5.使用数据库

格式：use db2;

表相关的SQL

注意：操作表的前提必须是先使用完数据库之后

1.查询所有表

格式：show tables;

2.创建表

默认字符集格式：create table 表名(字段1名类型，字段2名类型......);

指定字符集格式：create table 表名(字段1名类型，字段2名类型......)charset=utf8/gbk;

举例：

create table person(name varchar(20),age int);

create table person(name varchar(20),gender varchar(1),age int)charset=utf8;

3.查询表信息

格式：show create table person;

4.查询表字段信息

格式：desc person;

5.删除表

格式：drop table person;

6.修改表名

格式：rename table 原名 to 新名；

举例：

use db1;

rename table person to per;

7.添加表字段

最后面添加格式：alter table 表名 add 字段名类型;

最前面添加格式：alter table 表名 add 字段名类型 first;

在xxx字段后面添加：alter table 表名 add 字段名类型 after xxx;

举例： alter table stu add age int;

alter table stu add id int first;

alter table stu add address varchar(50) after name;

8.删除表字段

格式：alter table 表名 drop 字段名;

举例：

alter table stu drop address;

alter table stu drop age;

9.修改表字段

格式：alter table 表名 change 原名新名新类型；

举例：

alter table stu change score address varchar(20);

10.添加表中的列

alter table stu add column money NUMERIC(7,2);

注意：NUMERIC(7,2)的意思是共有7位，其中小数点后占两位。

DML数据操作语言

举例：首先创建好数据库以及要使用的表

create database db2 character set utf8;

use db2;

create table person(name varchar(20),age int)charset=utf8;

1.插入数据

全表插入格式：insert into 表名 values(值1,值2,值 3)；值的数量和顺序要和表字段一致

指定字段格式：insert into 表名(字段1名,字段2 名)values(值1,值2); 值得数量和顺序和指定的一致

批量插入格式：insert into 表名 values(值1,值2,值 3),(值1,值2,值3),(值1,值2,值3);

举例：

insert into person values("Tom",18);

insert into person(name) values('Jerry');

insert into person values('Lucy',20),('Lily',21);

insert into person(name) values('Lilei'), ('Hanmeimei');

2.查询数据

格式： select 字段信息 from 表名 where 条件;

举例: select name from person;

select name,age from person;

select * from person;

select * from person where age<=20;

select * from person where name='Tom';

3.修改数据

格式： update 表名 set 字段2名=值,字段2名=值 where 条件;

举例：

update person set age=5 where name='Jerry';

update person set name='张学友',age=25 where name='刘德华';

update person set age=15 where age<20;

4.删除数据

格式：delete from 表名 where 条件;

举例：

delete from person where name='Tom';

delete from person where age<20;

delete from person;

主键约束

主键：表示数据唯一性的字段称为主键

约束：创建表时给表字段添加的限制条件

主键约束：唯一且非空

格式：创建表时在主键字段的后面添加primary key

举例：

create table emp(id int primary key,name varchar(20));

insert into emp values(1,'张三');

insert into emp values(1,'李四'); //报错 Duplicate entry 数据重复

insert into emp values(null,'王五');//报错 cannot be null 不能为null

主键约束+自增

自增规律：从历史最大值+1

格式: primary key auto_increment

create table t1(id int primary key auto_increment,name varchar(20));

insert into t1 values(null,'aaa');

insert into t1 values(null,'bbb');

insert into t1 values(10,'ccc');

insert into t1 values(null,'ddd');

delete from t1 where id>=10;

非空约束

非空约束：如果为一个列添加了非空约束，那么这个列的值就不能为空，但可以重复。

举例：

create table user(id int primary key auto_increment,password varchar(50) not null);
show tables;
insert into user values(null,null);//不符合非空约束条件
insert into user values(null,123;);//编译成功

唯一约束

唯一约束：如果为一个列添加了唯一约束，那么这个列的值就必须是唯一的(即不能重复)，但可以为空。

举例：

create table test(id int primary key auto_increment,username varchar(50) unique--唯一约束);
show tables;
insert into test values(null,'lisi');
insert into test values(null,'lisi');--username的值要唯一,重复会报错
select * from test;

别名

举例：

给查询的字段起别名 select ename from emp;

select ename as '姓名' from emp;

select ename '姓名' from emp;

select ename 姓名 from emp;

数值计算 + - * / %

举例：

1. 查询每个员工的姓名,工资和年终奖(年终奖=月工资 *3)

select ename,sal,sal*3 from emp;

2. 查询2号部门中,每个员工的姓名,工资和涨薪5块钱之后的工资

select ename,sal,sal+5 from emp where deptno=2;

3. 让三号部门的每个员工涨薪5块钱

update emp set sal=sal+5 where deptno=3;

比较运算符 > < >= <= = != <>

举例：

1. 查询工资小于等于3000的员工姓名和工资

select ename,sal from emp where sal<=3000;

2. 查询1号部门的员工姓名和工作

select ename,job from emp where deptno=1;

3. 查询不是程序员的员工姓名工资和工作(两种写法)

select ename,sal,job from emp where job!='程序员';

select ename,sal,job from emp where job<>'程序员';

4. 查询有奖金的员工姓名和奖金

select ename,comm from emp where comm>0;

条件查询

distinct

举例：

1. 查询员工表中出现了哪几种不同的工作

select distinct job from emp;

2. 查询员工表中出现的部门编号deptno 去掉重复

select distinct deptno from emp;

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);

注意：使用where时不能使用列别名！

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后面有两个字符的 _代表一个字符位置
注意：通配符%代表0到n个字符，通配符下划线_代表1个字符

null

如果查询的值为null不能使用=, 需要使用is

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

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

between x and y关键字包含x和y

当查询某个字段的值在某个范围之内时使用

举例：

查询工资在2000到3000之间的员工信息

select * from emp where sal between 2000 and 3000;

order by

格式: order by 字段名 asc升序(默认)/desc降序

举例：

1. 查询每个员工的姓名和工资按照工资降序进行排序

select ename,sal from emp order by sal desc;

2. 查询每个员工的姓名,工资和部门编号,按照部门编号升序排序,如果部门编号一致则按照工资降序排序

select ename,sal,deptno from emp order by deptno,sal desc;

分页查询limit

格式: limit 跳过的条数,请求的条数(每页的条数)

举例:

跳过的条数= (请求页数-1)*每页条数

请求第一页的5条数据 limit 0,5

请求第二页的5条数据 limit 5,5

请求第8页的6条数据 limit 42,6

请求第6页的7条数据 limit 35,7

1. 按照工资升序排序请求第一页的5条数据

select * from emp order by sal limit 0,5;

2. 查询员工表中工资降序排序第二页的3条数据

select * from emp order by sal desc limit 3,3;

3. 查询1号部门工资前三的员工信息

select * from emp where deptno=1 order by sal desc limit 0,3;

4. 查询2号部门工资最高的员工姓名和工资

select ename,sal from emp where deptno=2 order by sal desc limit 0,1;

分组查询

定义：将某个字段相同的值划分为一组，按组进行统计查询

group by

1. 查询每个部门的平均工资

select deptno,avg(sal) from emp group by deptno;

2. 查询每种工作的平均工资 select job,avg(sal) from emp group by job;

3. 查询每种工作的人数 select job,count(*) from emp group by job;

4. 查询每个部门工资高于2000的员工人数 select deptno,count(*) from emp where sal>2000 group by deptno;

5. 查询平均工资最高的部门编号和平均工资

select deptno,avg(sal) from emp group by deptno order by avg(sal) desc limit 0,1;

having

注意：having 类似于条件查询的where

where后面只能写普通字段条件, 不能写聚合函数条件

聚合函数条件需要写在having关键字后面

各个关键字的位置: select 查询的字段 from 表名 where 普通字段条件 group by 分组字段名 having 聚合函数条件 order by 排序字段名 limit 跳过条数,请求条数;

having一定要和分组查询group by结合使用,不能单独使用.

举例：查询平均薪资小于5000的部门

select deptno,avg(sal) from emp group by deptno having avg(sal)<5000;

聚合函数

使用聚合函数可以对查询的多条数据进行统计查询

统计方式: 平均值最大值最小值求和计数

计数count

select count(*) from emp --底层优化了

select count(1) from emp --效果和*一样

select count(comm) 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 --平均数
举例：

1. 查询和销售相关工作的工资总和

select sum(sal) from emp where job like '%销售%';

2. 查询1号部门工资高于1500的员工人数

select count(*) from emp where deptno=1 and sal>1500;

3. 查询1号部门中名字包含僧的员工的人数和平均工资起别名

select count(*) 人数,avg(sal) 平均工资 from emp where deptno=1 and ename like '%僧%';

4. 查询1号部门的平均工资,最高工资,最低工资,工资总和,人数并且起别名

select avg(sal) 平均工资,max(sal) 最高工资,min(sal) 最低工资,sum(sal) 工资总和,count(*) 人数 from emp where deptno=1;

数据类型

命名规则

1.字段名必须以字母开头，尽量不要使用拼音
2.长度不能超过30个字符（不同数据库，不同版本会有不同）
3.不能使用SQL的保留字，如where,order,group
4.只能使用如下字符az、AZ、0~9、$ 等
5.Oracle习惯全大写：USER_NAME，mysql习惯全小写：user_name
6.多个单词用下划线隔开，而非java语言的驼峰规则

整数

int(m) 和 bigint(m) m代表的是显示长度 m=10 存18 0000000018

如果需要使用m 创建表时需要添加zerofill关键字,bigint等效java中的long

create table t2(age int(5) zerofill);

insert into t2 values(18);

select * from t2;

浮点数

float(m,d)和double(m,d) m代表总长度 d代表小数长度

保存23.234 m=5 d=3,

decimal(m,d) 超高精度浮点数，只有涉及超高精度运算时使用，numberic(5,2) decimal(5,2)也可以表示小数,表示总共5位,其中可以有两位小数

create table t3(salary double(5,3));

insert into t3 values(23.234);

insert into t3 values(23.12312312);

insert into t3 values(231.12312312); //报错长度超限

字符串

char(m): 固定长度, m=10 存"abc" 占10, 执行效率略高, 最大长度255

varchar(m):可变长度,m=10 存"abc" 占3 , 节省存储空间,最大长度65535 , 建议保存255以内长度

text(m):可变长度,m=10 存"abc" 占3,最大长度 65535 , 建议保存长度大于255

注意：以utf8编码计算的话，一个汉字在u8下占3个字节

日期

date: 只能保存年月日

time: 只能保存时分秒

datetime:保存年月日时分秒, 最大值为9999-12- 31,默认值为null

timestamp(时间戳距离1970年1月1日的毫秒数): 保存年月日时分秒,最大值2038-1-19 ,默认值为当前系统时间

create table t_date(t1 date,t2 time,t3 datetime,t4 timestamp);

insert into t_date values('2021-4- 22','11:04:22',null,null);

insert into t_date values('2021-4- 22','11:04:22','2020-11-25 13:33:22',null);

子查询(嵌套查询)

举例：

1. 查询工资高于1号部门平均工资的员工信息

select avg(sal) from emp where deptno=1;

select * from emp where sal>(select avg(sal) from emp where deptno=1);

2. 查询工资最高的员工信息

先查最高工资 select max(sal) from emp;

再通过最高工资查询员工信息 select * from emp where sal=(select max(sal) from emp);

3. 查询工资高于2号部门最低工资的员工信息

先查2号部门最低工资 select min(sal) from emp where deptno=2;

再查员工信息 select * from emp where sal>(select min(sal) from emp where deptno=2);

4. 查询和孙悟空相同工作的其它员工信息

先查孙悟空工作 select job from emp where ename='孙悟空';

查询和孙悟空工作相同的员工

select * from emp where job=(select job from emp where ename='孙悟空') and ename!='孙悟空';

5. 查询拿最低工资员工的同事们的信息(同事指相同部门)

先查最低工资 select min(sal) from emp;

查询最低工资的员工部门编号 select deptno from emp where sal=(select min(sal) from emp);

最后查询该部门下的员工排除掉最低工资的员工

select * from emp where deptno=(select deptno from emp where sal=(select min(sal) from emp)) and sal!=(select min(sal) from emp);

6. 查询人数最多的部门信息(需要用到dept部门表)

查询最多人数的部门编号

select deptno from emp group by deptno order by count(*) desc limit 0,1;

根据部门编号查询部门信息

select * from dept where deptno=(select deptno from emp group by deptno order by count(*) desc limit 0,1);

7. 有的工作只有一个人, 那么查询有哪些人从事了这种只有一个人的工作

查询只有一个人的工作 select job from emp group by job having count(*)=1;

查询有哪些员工从事上面查询到的工作 select * from emp where job in(select job from emp group by job having count(*)=1);

事务 transaction

含义：

数据库事务(Database Transaction)，是指作为单个逻辑工作单元执行的一系列操作，要么完全地执行，要么完全地不执行。简单的说：事务就是将一堆的SQL语句(通常是增删改操作)绑定在一起执行，要么都执行成功，要么都执行失败，即都执行成功才算成功，否则就会恢复到这堆SQL执行之前的状态。

事务的四大特性ACID

原子性（Atomicity，或称不可分割性）：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中如果发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
一致性（Consistency）：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
隔离性（Isolation，又称独立性）：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。
持久性（Durability）：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

隔离级别

事务隔离分为不同级别，包括以下四点

读未提交（Read uncommitted）安全性最差,可能发生并发数据问题,性能最好
读提交（read committed） Oracle默认的隔离级别
可重复读（repeatable read）MySQL默认的隔离级别,安全性较好,性能一般
串行化（Serializable）表级锁，读写都加锁，效率低下,安全性高,不能并发

查询mysql的隔离级别

在默认情况下，MySQL每执行一条SQL语句，都是一个单独的事务。如果需要在一个事务中包含多条SQL语句，那么需要手动开启事务和结束事务。

开启事务：start transaction；结束事务：commit（提交事务）或rollback（回滚事务）
在执行SQL语句之前，先执行strat transaction，这就开启了一个事务（事务的起点），然后可以去执行多条SQL语句，最后要结束事务，commit表示提交，即事务中的多条SQL语句所做出的影响会持久化到数据库中。或者rollback，表示回滚，即回滚到事务的起点，之前做的所有操作都被撤消。

事务处理

在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务
事务处理可以用来维护数据的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行
事务用来管理 insert、update、delete 语句，因为这些操作才会“破坏”数据，查询select语句是不会的
MySQL默认数据库的事务是开启的，执行SQL后自动提交。
MySQL的事务也可以改成手动提交，那就有两个步骤：先开启，写完SQL后，再手动提交。

索引 index

定义：

索引是一种排好序的快速查找的数据结构，它帮助数据库高效的进行数据的检索。在数据之外，数据库系统还维护着满足特定查找算法的数据结构（额外的存储空间），这些数据结构以某种方式指向数据，这样就可以在这些数据结构上实现高效的查找算法。这种数据结构就叫做索引。

一般来说索引本身也很大，不可能全部存储在内存中，因此往往以索引文件的形式存放在磁盘中。目前大多数索引都采用BTree树方式构建。

索引相关SQL

查看索引，主键会自动创建索引——show index from dept;
创建索引——#create index 索引名字 on 表名(字段名);

create index loc_index on dept(loc); #创建索引
修改表结构,添加普通索引——alter table dept add index loc_index(loc);
创建唯一索引——alter table dept add unique(loc); #创建唯一索引--索引列的值必须唯一
创建复合索引——alter table dept add index fuhe_index(dname,loc);
创建复合唯一索引——alter table dept add unique fuhe_index(dname,loc);
删除索引——alter table dept drop index fuhe_index;

最左特性

当我们创建一个联合索引（复合索引）的时候，如(k1,k2,k3)，相当于创建了（k1）、(k1,k2)和(k1,k2,k3)三个索引，这就是最左匹配原则，也称为最左特性。

索引查询速度快的原因

明显查询索引表比直接查询数据表要快的多，首先，索引表是排序了，可以类似二分查找，非常有效的提高了查询的速度，先到事先排序好的索引表中检索查询，找到其主键后，就直接定位到记录所在位置，然后直接返回这条数据。

总结有以下两个原因：

排序，tree结构，类似二分查找
索引表小

索引优点：

索引是数据库优化
表的主键会默认自动创建索引
每个字段都可以被索引
大量降低数据库的IO磁盘读写成本，极大提高了检索速度
索引事先对数据进行了排序，大大提高了查询效率

索引缺点：

索引本身也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也要占用空间
索引表中的内容，在业务表中都有，数据是重复的，空间是“浪费的”
虽然索引大大提高了查询的速度，但对数据的增、删、改的操作需要更新索引表信息，如果数据量非常巨大,更新效率就很慢,因为更新表时,MySQL不仅要保存数据,也要保存一下索引文件
随着业务的不断变化，之前建立的索引可能不能满足查询需求，需要消耗时间去更新索引

关联关系

在创建表时, 表和表之间存在的业务关系，有哪些关系?

一对一: 有AB两张表,A表中一条数据对应B表中的一条数据, 同时B表中的一条数据也对应A表中的一条数据.
一对多: 有AB两张表,A表中一条数据对应B表中的多条数据, 同时B表中的一条数据对应A表中的一条数据.
多对多:有AB两张表,A表中一条数据对应B表中的多条数据, 同时B表中的一条数据也对应A表中的多条数据.

表和表之间如何建立关系?

在表中通过一个外键字段建立关系

什么是外键: 用来建立关系的字段称为外键

外键约束 foreign key

DROP TABLE IF EXISTS tb_user_address; #如果表存在则删除，慎用会丢失数据
DROP TABLE IF EXISTS tb_user; #如果表存在则删除，慎用会丢失数据
CREATE TABLE tb_user (id INT PRIMARY KEY NOT NULL AUTO_INCREMENT, #自增主键
NAME VARCHAR(50) NOT NULL UNIQUE, #非空，唯一索引
sex CHAR(2) DEFAULT '男', #默认值
phone CHAR(18),
age INT,
CHECK (age>0 AND age<=200),
);
CREATE TABLE tb_user_address (user_id INT PRIMARY KEY NOT NULL,
address VARCHAR(200),
foreign key(user_id) REFERENCES tb_user(id));
DESC tb_user;
tb_user_address中user_id字段录入tb_user表不存在的主键值，将报错

关联查询

同时查询多张表数据的查询方式称为关联查询
关联查询的三种方式：1.等值连接 2.内连接 3.外连接

关联查询——等值连接

格式: select * from A,B where 关联关系 and A.age>18;

举例：

查询工资高于2000的每个员工姓名和对应的部门名

select ename,dname from emp e ,dept d where e.deptno=d.deptno and sal>2000;

关联查询——内连接inner join

格式: select * from A inner join B on 关联关系 where A.age>18;

内连接和等值连接查询到的数据是一样的, 两个表的交集数据

举例：

查询工资高于2000的每个员工姓名和对应的部门名

select ename,dname from emp e inner join dept d on e.deptno=d.deptno where sal>2000;

查询1号部门有领导的员工姓名,工资,部门名,部门地址

select ename,sal,dname,loc from emp e inner join dept d on e.deptno=d.deptno where d.deptno=1 and mgr is not null;

关联查询——外连接

格式: select * from A left/right join B on 关联关系 where A.age>18;

当需要查询一张表的全部以及另外一张表的交集时使用外连接

举例：

查询所有员工姓名以及对应的部门名

insert into emp(empno,ename) values (100,'灭霸');

select ename,dname from emp e left join dept d on e.deptno=d.deptno;

关联查询总结:

如果查询的是两张表交集数据则使用等值连接或内连接(推荐)
如果查询的是一张表的全部以及另外一张表的交集使用外连接

SQL执行顺序

(1) FROM [left_table] 选择表

(2) ON <join_condition> 链接条件

(3) <join_type> JOIN <right_table> 链接

(4) WHERE <where_condition> 条件过滤

(5) GROUP BY <group_by_list> 分组

(6) AGG_FUNC(column or expression),... 聚合

(7) HAVING <having_condition> 分组过滤

(8) SELECT (9) DISTINCT column,... 选择字段、去重

(9) ORDER BY <order_by_list> 排序

(10) LIMIT count OFFSET count; 分页

主键、外键、唯一索引的区别？

- Primary Key 主键约束，自动创建唯一索引
- Foreign Key 外键约束，外键字段的内容是引用另一表的字段内容，不可瞎写
- Unique Index 唯一索引，唯一值但不是主键

对于约束的好处时，数据库会进行检查，违反约束会报错，操作失败。数据库提供了丰富的约束检查，还有其他约束，但现今弱化关系型数据库的前提下，基本已经很少使用，记住上面三个即可。

drop、delete和truncate之间的区别？

drop删除库或者表，数据和结构定义

delete和truncate只是删除表的数据

delete可以指定where条件，删除满足条件的记录，tuncate删除所有记录

对于自增字段的表，delete不会自增值清零，而truncate是把表记录和定义都删除了，然后重建表的定义，所以自增主键会重头开始计数

常见SQL题型

查询所有记录
select * from emp

只查询指定列
SELECT id,ename,sal from emp

查询id为100的记录
select * from emp where id=100

模糊查询记录
select * from emp where ename like 'j%' #以j开头的记录

select * from emp where ename like '%k' #以k结束的记录

select * from emp where ename like '%a%' #包含a的记录

select * from emp where ename not like 'j%' #不以j开头的记录

查询之间范围之间的所有记录
select * from emp where sal between 8000 and 20000 #[8000,20000]

select * from emp where sal>8000 and sal<20000 #(8000,20000)

查询满足两个条件的记录
SELECT * from user where age=19 or age=20 #或者关系

SELECT * from user where age in (19,20)

SELECT * from user where age=20 and name='xiongda' #并且关系

查询用户住址
SELECT distinct addr from user

查询19岁人的名字
SELECT distinct name from user where age=19

按age升序查询记录
SELECT * from user order by age asc #升序,默认

SELECT * from user order by age desc #降序

以name升序、age降序查询记录
SELECT * from user order by name asc,age desc #name升序,age降序

查询总人数
SELECT count(*) from user

SELECT count(1) from user

SELECT count(id) from user

查询各个城市的人数
select addr,count(addr) from user group by addr #聚合函数以外的列要分组

查询至少有2人的地址
SELECT addr,count(name) from user GROUP BY addr

SELECT addr,count(name) X from user GROUP BY addr having X>2 #条件过滤

查询记录中最年长和最年轻
select max(age),min(age) from user

查询大于平均年龄的记录
select * from user where age > (select avg(age) from user)

查询年龄最大的用户信息
select * from user where age = (select max(age) from user)

查询各部门的最高薪
select id,name,sal,max(sal) from emp GROUP BY deptno

查询各科的平均工资
select avg(comm) from emp

select ROUND(avg(comm),1) from emp #保留一位小数

SELECT * from emp where comm > (select avg(comm) from emp)

查询id是100或200的记录
select * from emp where id=100

select * from emp where id=200

select * from emp where id=100 or id=200

select * from emp where id in(100,200)

select * from emp where id=200

#UNION #合并重复内容

union all #不合并重复内容

select * from emp where id=200

查询存在部门的员工信息
select * from emp where deptno in (select id from dept)

查询没划分部门的员工信息
select * from emp where deptno not in(select id from dept)

查询同名的员工记录
select * from emp WHERE ename in (select ename from emp GROUP BY ename HAVING count(ename)>1)

全部学生按出生年月排行
select * from students order by sbirthday #数值从小到大，年龄就是大到小了

每个班上最小年龄的学员
select sname,class,max(sbirthday) from students group by class #数字最大，年龄是最小的

查询学生的姓名和年龄
select sname,year(now())-year(sbirthday) age from students

查询男教师及其所上的课程
SELECT * from teachers a inner JOIN courses b on a.tno=b.tno AND a.tsex='男'

SELECT * from teachers a,courses b where a.tno=b.tno AND a.tsex='男'

查询每个老师教的课程
SELECT c.cname,t.tname,t.prof,t.depart

FROM teachers t

LEFT JOIN courses c ON t.tno = c.tno

查询女老师的信息
SELECT *

FROM teachers t

LEFT JOIN courses c ON t.tno = c.tno

where t.tsex='女'
第一种先连接数据后过滤数据，假如数据量很大，第一种中间过程要构建巨大的临时表。而第二种方式先过滤数据，构建的中间结果集自然就变的很小。所占内存，所加工的时间所网络传输的时间都变少了，所以效率高。

查询得分前3名的学员信息
select * from scores order by degree desc limit 3 #前三条

select * from scores order by degree desc limit 1,3

#从1位置(第二条)开始,总共取3条

查询课程是“计算机导论”的，得分前3名的学员信息
select * from scores where cno = (select cno from courses where cname='计算机导论')

order by degree desc limit 3

课程号“3-105”的倒数最后3名学员排行
select * from scores where cno='3-105' order by degree limit 3

JDBC

JavaDataBaseConnectivity: Java数据库连接.
学习JDBC主要学习的就是如何在Java代码中执行SQL 语句
JDBC是Sun提供的一套Java语言和各种数据库进行连接的API(Application Programma Interface 应用程序编程接口)
为什么使用JDBC接口？
答：如果没有JDBC接口,Java程序员有可能每一种数据库都学习一套新的方法, Sun公司为了避免这种情况,通过 JDBC接口规范了各个数据库厂商的方法名, 各个数据库厂商根据JDBC接口写自己的实现类, 这样对应Java 程序员而言即使换了数据库，代码是不需要改变的,因为不同的数据库方法名都是JDBC接口中定义的方法名.
如何使用JDBC连接数据库？
答：创建maven工程，在pom.xml文件中添加mysql的依赖，如下所示

<!-- 连接MySQL数据库的依赖 -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connectorjava</artifactId>
<version>8.0.15</version>
</dependency>

在main方法中添加如下代码：

//获取数据库连接
Connection conn =
DriverManager.getConnection(
Statement执行SQL语句的对象
execute(sql); execute执行 , 此方法可以执行任意
SQL语句 但是建议执行DDL(数据库相关SQL和表相关
的SQL)
executeUpdate(sql); 执行增删改相关的SQL语句
ResultSet rs = executeQuery(sql);
"jdbc:mysql://localhost:3306/db1?
characterEncoding=utf8&serverTimezone=A
sia/Shanghai",
"root","root");
System.out.println(conn);
//创建执行SQL语句对象
Statement s = conn.createStatement();
//执行SQL语句
String sql = "create table jdbct1(name
varchar(20))";
s.execute(sql);// execute执行
//关闭资源
conn.close();
System.out.println("执行完成!");

DBCP数据库连接池

DataBaseConnectivityPool
使用数据库连接池可以将数据库连接进行复用,大大降低了频繁开关连接的次数,从而提高了执行效率
如果使用? 在pom.xml文件中添加dbcp依赖

SQL注入

什么是SQL注入?
答：往用户输入值的地方，输入进去了SQL语句, 导致原有 SQL语句的逻辑发生改变,这种行为称为SQL注入
例如：select id from user where username='asdf' and password='' or '1'='1';此时逻辑改变了
通过预编译的SQL执行对象PreparedStatement解决SQL注入问题
PreparedStatement如何解决的SQL注入问题?
答：预编译的SQL执行对象,在创建执行对象的时候就使用了SQL语句,同时将SQL语句进行编译(可以理解为将 SQL语句的逻辑部分锁死),用户输入的内容只能以值得形式添加到SQL语句中, 原有逻辑不会发生改变.
什么时候使用Statement和PreparedStatement?
答：如果SQL语句中存在变量则使用PreparedStatement 否则使用Statement.

SQL优化

批量操作

作用: 将多次数据传输合并成一次,从而提高执行效率。默认新增SQL有事务控制，导致每条都需要事务开启和事务提交；而批量处理是一次事务开启和提交，速度肯定变快

创建mysql-db库

CREATE DATABASE /*!32312 IF NOT EXISTS*/`mysql-db` /*!40100 DEFAULT CHARACTER SET utf8 */;

USE `mysql-db`;

准备student表

DROP TABLE IF EXISTS `student`;
 
CREATE TABLE `student` (
 
`id` varchar(4) NOT NULL,
 
`NAME` varchar(20) DEFAULT NULL,
 
`sex` char(2) DEFAULT NULL,
 
`birthday` date DEFAULT NULL,
 
`salary` decimal(7,2) DEFAULT NULL,
 
PRIMARY KEY (`id`)
 
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
insert into `student`(`id`,`NAME`,`sex`,`birthday`,`salary`) values ('1','小明','男','2020-01-01','10000.00'),('2','小红','女','2020-01-02','10000.00'),('3','小黄','男','2020-01-03','10000.00'),('4','小兰','男','2020-01-04','20000.00'),('5','小涛','男','2020-01-05','20000.00'),('6','小王','男','2020-01-06','10000.00'),('7','小军','男','2020-01-07','20000.00'),('8','小刘','男','2020-10-11','3000.00');

准备tb_dept表

DROP TABLE IF EXISTS `tb_dept`;
 
CREATE TABLE `tb_dept` (
 
`id` int(11) NOT NULL AUTO_INCREMENT,
 
`name` varchar(50) DEFAULT NULL,
 
`parent_id` int(11) DEFAULT NULL,
 
`sort` int(11) DEFAULT NULL,
 
`note` varchar(100) DEFAULT NULL,
 
`created` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
 
`updated` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
 
PRIMARY KEY (`id`)
 
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
 
insert into `tb_dept`(`id`,`name`,`parent_id`,`sort`,`note`,`created`,`updated`) values (1,'集团',0,1,'集团总部','2018-10-02 09:15:14','2018-09-27 16:35:54'),(2,'财务部',1,2,'财务管理','2018-09-27 16:35:52','2018-09-27 16:34:15'),(3,'软件部',1,3,'开发软件、运维','2018-09-27 16:35:54','2018-09-27 16:34:51');

准备tb_user表

DROP TABLE IF EXISTS `tb_user`;
 
CREATE TABLE `tb_user` (
 
`id` int(11) NOT NULL AUTO_INCREMENT,
 
`dept_id` int(11) DEFAULT NULL,
 
`username` varchar(50) DEFAULT NULL,
 
`password` varchar(100) DEFAULT NULL,
 
`salt` varchar(50) DEFAULT NULL,
 
`email` varchar(100) DEFAULT NULL,
 
`mobile` varchar(100) DEFAULT NULL,
 
`valid` tinyint(4) DEFAULT NULL,
 
`created` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
 
`updated` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
 
PRIMARY KEY (`id`)
 
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
 
insert into `tb_user`(`id`,`dept_id`,`username`,`password`,`salt`,`email`,`mobile`,`valid`,`created`,`updated`) values (1,1,'陈集团','123456',NULL,'tony@sina.com','13572801415',1,'2018-09-30 09:32:18','2018-09-30 09:32:18'),(2,3,'牛软件','567890',NULL,'niu@sina.com','13208737172',0,'2018-10-02 09:23:19','2018-09-20 09:32:18');

查询SQL尽量不要使用select *，而是具体字段
反例：

SELECT * FROM student

正例：

SELECT id,NAME FROM student

理由：

字段多时，大表能达到100多个字段甚至达200多个字段只取需要的字段，节省资源、减少网络开销select * 进行查询时，很可能不会用到索引，就会造成全表扫描避免在where子句中使用or来连接条件

反例：

SELECT * FROM student WHERE id=1 OR salary=30000

正例：

# 分开两条sql写

SELECT * FROM student WHERE id=1
 
SELECT * FROM student WHERE salary=30000

理由：

使用or可能会使索引失效，从而全表扫描
对于or没有索引的salary这种情况，假设它走了id的索引，但是走到salary查询条件时，它还得全表扫描。也就是说整个过程需要三步：全表扫描+索引扫描+合并。如果它一开始就走全表扫描，直接一遍扫描就搞定。虽然mysql是有优化器的，处于效率与成本考虑，遇到or条件，索引还是可能失效的
使用varchar代替char
反例：`deptname` char(100) DEFAULT NULL COMMENT '部门名称'

正例：`deptname` varchar(100) DEFAULT NULL COMMENT '部门名称'

理由：

varchar变长字段按数据内容实际长度存储，存储空间小，可以节省存储空间
char按声明大小存储，不足补空格
其次对于查询来说，在一个相对较小的字段内搜索，效率更高
尽量使用数值替代字符串类型
主键（id）：primary key优先使用数值类型int，tinyint
性别（sex）：0-代表女，1-代表男；数据库没有布尔类型，mysql推荐使用tinyint
支付方式（payment）：1-现金、2-微信、3-支付宝、4-信用卡、5-银行卡
服务状态（state）：1-开启、2-暂停、3-停止
商品状态（state）：1-上架、2-下架、3-删除
查询尽量避免返回大量数据
如果查询返回数据量很大，就会造成查询时间过长，网络传输时间过长。同时，大量数据返回也可能没有实际意义。如返回上千条甚至更多，用户也看不过来。

通常采用分页，一页习惯10/20/50/100条。

使用explain分析你SQL执行计划
SQL很灵活，一个需求可以很多实现，那哪个最优呢？SQL提供了explain关键字，它可以分析你的SQL执行计划，看它是否最佳。Explain主要看SQL是否使用了索引。

EXPLAIN

SELECT * FROM student WHERE id=1
是否使用了索引及其扫描类型
type：

ALL 全表扫描，没有优化，最慢的方式
index 索引全扫描
range 索引范围扫描，常用语<，<=，>=，between等操作
ref 使用非唯一索引扫描或唯一索引前缀扫描，返回单条记录，常出现在关联查询中
eq_ref 类似ref，区别在于使用的是唯一索引，使用主键的关联查询
const/system 单条记录，系统会把匹配行中的其他列作为常数处理，如主键或唯一索引查询
null MySQL不访问任何表或索引，直接返回结果
key：

真正使用的索引方式
创建name字段的索引
ALTER TABLE student ADD INDEX index_name (NAME)
优化like语句
模糊查询，程序员最喜欢的就是使用like，但是like很可能让你的索引失效

反例：

EXPLAIN
 
SELECT id,NAME FROM student WHERE NAME LIKE '%1'
 
EXPLAIN
 
SELECT id,NAME FROM student WHERE NAME LIKE '%1%'

正例：

EXPLAIN
 
SELECT id,NAME FROM student WHERE NAME LIKE '1%'

字符串怪现象
反例：

#未使用索引

EXPLAIN
 
SELECT * FROM student WHERE NAME=123

正例：

#使用索引

EXPLAIN
 
SELECT * FROM student WHERE NAME='123'

理由：

为什么第一条语句未加单引号就不走索引了呢？这是因为不加单引号时，是字符串跟数字的比较，它们类型不匹配，MySQL会做隐式的类型转换，把它们转换为数值类型再做比较。
索引不宜太多，一般5个以内
索引并不是越多越好，虽其提高了查询的效率，但却会降低插入和更新的效率
索引可以理解为一个就是一张表，其可以存储数据，其数据就要占空间，再者，索引表的一个特点，其数据是排序的，那排序要不要花时间呢？肯定要insert或update时有可能会重建索引，如果数据量巨大，重建将进行记录的重新排序，所以建索引需要慎重考虑，视具体情况来定
一个表的索引数最好不要超过5个，若太多需要考虑一些索引是否有存在的必要
索引不适合建在有大量重复数据的字段上，如性别字段。因为SQL优化器是根据表中数据量来进行查询优化的，如果索引列有大量重复数据，Mysql查询优化器推算发现不走索引的成本更低，很可能就放弃索引了。

where限定查询的数据
数据中假定就一个男的记录

反例：

SELECT id,NAME FROM student WHERE sex='男'

正例：

SELECT id,NAME FROM student WHERE id=1 AND sex='男'

理由：

需要什么数据，就去查什么数据，避免返回不必要的数据，节省开销，避免在where中对字段进行表达式操作
反例：

EXPLAIN
 
SELECT * FROM student WHERE id+1-1=+1

正例：

EXPLAIN
 
SELECT * FROM student WHERE id=+1-1+1

EXPLAIN
 
SELECT * FROM student WHERE id=1

理由：

SQL解析时，如果字段相关的是表达式就进行全表扫描
避免在where子句中使用!=或<>操作符
应尽量避免在where子句中使用!=或<>操作符，否则引擎将放弃使用索引而进行全表扫描。记住实现业务优先，实在没办法，就只能使用，并不是不能使用。如果不能使用，SQL也就无需支持了。

反例：

EXPLAIN
 
SELECT * FROM student WHERE salary!=3000
 
EXPLAIN
 
SELECT * FROM student WHERE salary<>3000

理由：

使用!=和<>很可能会让索引失效
去重distinct过滤字段要少
#索引失效

EXPLAIN
 
SELECT DISTINCT * FROM student

#索引生效

EXPLAIN
 
SELECT DISTINCT id,NAME FROM student
 
EXPLAIN
 
SELECT DISTINCT NAME FROM student

理由：

带distinct的语句占用cpu时间高于不带distinct的语句。因为当查询很多字段时，如果使用distinct，数据库引擎就会对数据进行比较，过滤掉重复数据，然而这个比较、过滤的过程会占用系统资源，如cpu时间
where中使用默认值代替null
#修改表，增加age字段，类型int，非空，默认值0

ALTER TABLE student ADD age INT NOT NULL DEFAULT 0;

批量插入性能提升
大量数据提交，上千，上万，批量性能非常快，mysql独有

多条提交：

INSERT INTO student (id,NAME) VALUES(4,'小明');
 
INSERT INTO student (id,NAME) VALUES(5,'小于');

批量提交：

INSERT INTO student (id,NAME) VALUES(4,'小明'),(5,'小于');

理由：

默认新增SQL有事务控制，导致每条都需要事务开启和事务提交；而批量处理是一次事务开启和提交。自然速度飞升
数据量小体现不出来
批量删除优化
避免同时修改或删除过多数据，因为会造成cpu利用率过高，会造成锁表操作，从而影响别人对数据库的访问。

反例：

#一次删除10万或者100万+？

delete from student where id <100000;

#采用单一循环操作，效率低，时间漫长

for（User user:list）{
 
delete from student;
 
}

正例：

//分批进行删除，如每次500

for(){
 
delete student where id<500;
 
}
 
delete student where id>=500 and id<1000;

理由：

一次性删除太多数据，可能造成锁表，会有lock wait timeout exceed的错误，所以建议分批操作
伪删除设计
商品状态（state）：1-上架、2-下架、3-删除

理由：

这里的删除只是一个标识，并没有从数据库表中真正删除，可以作为历史记录备查
同时，一个大型系统中，表关系是非常复杂的，如电商系统中，商品作废了，但如果直接删除商品，其它商品详情，物流信息中可能都有其引用。
通过where state=1或者where state=2过滤掉数据，这样伪删除的数据用户就看不到了，从而不影响用户的使用
操作速度快，特别数据量很大情况下
提高group by语句的效率
可以在执行到该语句前，把不需要的记录过滤掉

反例：先分组，再过滤

select job，avg（salary） from employee
group by job
having job ='president' or job = 'managent';

正例：先过滤，后分组

select job，avg（salary） from employee
where job ='president' or job = 'managent'
group by job;

复合索引最左特性
创建复合索引，也就是多个字段

ALTER TABLE student ADD INDEX idx_name_salary (NAME,salary)

满足复合索引的左侧顺序，哪怕只是部分，复合索引生效

EXPLAIN

SELECT * FROM student WHERE NAME='小陈'

没有出现左边的字段，则不满足最左特性，索引失效

EXPLAIN

SELECT * FROM student WHERE salary=3000

复合索引全使用，按左侧顺序出现 name,salary，索引生效

EXPLAIN

SELECT * FROM student WHERE NAME='小陈' AND salary=3000

虽然违背了最左特性，但MYSQL执行SQL时会进行优化，底层进行颠倒优化

EXPLAIN

SELECT * FROM student WHERE salary=3000 AND NAME='小陈'

理由：

复合索引也称为联合索引
当我们创建一个联合索引的时候，如(k1,k2,k3)，相当于创建了（k1）、(k1,k2)和(k1,k2,k3)三个索引，这就是最左匹配原则
联合索引不满足最左原则，索引一般会失效，但是这个还跟Mysql优化器有关的
排序字段创建索引
什么样的字段才需要创建索引呢？原则就是where和order by中常出现的字段就创建索引。

#使用*，包含了未索引的字段，导致索引失效

EXPLAIN

SELECT * FROM student ORDER BY NAME;

EXPLAIN

SELECT * FROM student ORDER BY NAME,salary

#name字段有索引

EXPLAIN

SELECT id,NAME FROM student ORDER BY NAME

#name和salary复合索引

EXPLAIN

SELECT id,NAME FROM student ORDER BY NAME,salary

EXPLAIN

SELECT id,NAME FROM student ORDER BY salary,NAME

#排序字段未创建索引，性能就慢

EXPLAIN

SELECT id,NAME FROM student ORDER BY sex

删除冗余和重复的索引

SHOW INDEX FROM student

#创建索引index_name

ALTER TABLE student ADD INDEX index_name (NAME)

#删除student表的index_name索引

DROP INDEX index_name ON student ;

#修改表结果，删除student表的index_name索引

ALTER TABLE student DROP INDEX index_name ;

#主键会自动创建索引，删除主键索引

ALTER TABLE student DROP PRIMARY KEY ;

不要有超过5个以上的表连接，关联的表个数越多，编译的时间和开销也就越大，每次关联内存中都生成一个临时表，应该把连接表拆开成较小的几个执行，可读性更高。如果一定需要连接很多表才能得到数据，那么意味着这是个糟糕的设计了
阿里规范中，建议多表联查三张表以下inner join 、left join、right join，优先使用inner join三种连接如果结果相同，优先使用inner join，如果使用left join左边表尽量小

inner join 内连接，只保留两张表中完全匹配的结果集
left join会返回左表所有的行，即使在右表中没有匹配的记录
right join会返回右表所有的行，即使在左表中没有匹配的记录
理由：

如果inner join是等值连接，返回的行数比较少，所以性能相对会好一点同理，使用了左连接，左边表数据结果尽量小，条件尽量放到左边处理，意味着返回的行数可能比较少。这是mysql优化则，就是小表驱动大表，小的数据集驱动大的数据集，从而让性能更优in子查询的优化

日常开发实现业务需求可以有两种方式实现：

一种使用数据库SQL脚本实现
一种使用程序实现
如需求：查询所有部门的所有员工：

#in子查询

SELECT * FROM tb_user WHERE dept_id IN (SELECT id FROM tb_dept);

#这样写等价于：

#先查询部门表

SELECT id FROM tb_dept

#再由部门dept_id，查询tb_user的员工

SELECT * FROM tb_user u,tb_dept d WHERE u.dept_id = d.id

假设表A表示某企业的员工表，表B表示部门表，查询所有部门的所有员工，很容易有以下程序实现，可以抽象成这样的一个嵌套循环：

List<> resultSet;

for(int i=0;i<B.length;i++) {
for(int j=0;j<A.length;j++) {
if(A[i].id==B[j].id) {
resultSet.add(A[i]);

break;

}

}

}

上面的需求使用SQL就远不如程序实现，特别当数据量巨大时。

理由：

数据库最费劲的就是程序链接的释放。假设链接了两次，每次做上百万次的数据集查询，查完就结束，这样就只做了两次；相反建立了上百万次链接，申请链接释放反复重复，就会额外花费很多实际，这样系统就受不了了，慢，卡顿
尽量使用union all替代union
反例：

SELECT * FROM student

UNION

SELECT * FROM student

正例：

SELECT * FROM student

UNION ALL

SELECT * FROM student

理由：

union和union all的区别是，union会自动去掉多个结果集合中的重复结果，而union all则将所有的结果全部显示出来，不管是不是重复
union：对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序
union在进行表链接后会筛选掉重复的记录，所以在表链接后会对所产生的结果集进行排序运算，删除重复的记录再返回结果。实际大部分应用中是不会产生重复的记录，最常见的是过程表与历史表UNION