SQL的语言分类
DQL（Data Query Language）：数据查询语言 select
DML(Data Manipulate Language):数据操作语言 insert 、update、delete
DDL（Data Define Languge）：数据定义语言 create、drop、alter
TCL（Transaction Control Language）：事务控制语言 commit、rollback
1.数据库的好处
(1)可以持久化数据到本地
(2)结构化查询
2.数据库的常见概念
DB：数据库，存储数据的容器
DBMS：数据库管理系统，又称为数据库软件或数据库产品，用于创建或管理DB
SQL：结构化查询语言，用于和数据通信的语言。
3.数据库存储的特点
(1)数据库存放到表中，然后表再放到库中
(2)一个库可以有多张表，每张表具有唯一的表名用来标识自己
(3)表中有一个或多个列，即“字段，相当于java中的属性
(4)表中的每一行数据，相当于java中“对象”
4.MySQL的优点
(1) 开源、免费、成本低
(2) 性能高、移植性也好
(3) 体积小，便于安装
5.MySQL服务的启动和停止
方式一：计算机—右键管理—服务
方式二：通过管理员身份运行
net start 服务名 (启动服务)
net stop 服务名 (停止服务)
6.MySQL服务的登陆和退出
方式一：通过mysql自带的客户端，只限于root用户
方式二：通过windows自带的
登陆：mysql [-h 主机名 -P端口号] -u用户名 -p密码
退出：exit 或 ctrl+c
7.MySQL的常见命令
(1)查看当前所有的数据库
show database;
(2)打开指定的库
use 库名
(3)查看当前库的所有表
show tables;
(4)查看其他库的所有表
show tables from 库名;
(5)创建表
create table 表名(
列名 列类型,
…
);
(6)查看表结构
desc 表名;
(7)查看服务器的版本
方式一：登陆到Mysql服务端
select version();
方式二：没有登录到Mysql服务端
mysql –version 或 mysql -V
8.MySQL的语法规范
1.不区分大小写，但建议关键字大写，表名、列名小写
2.每条命令最好用分号结尾
3.每条命令根据需要，可以进行所街或换行
4.注释
单行注释：#注释文字
单行注释：– 注释文字
多行注释：/* 注释文字 */
DQL语言学习
语法
select 查询列表
from 表名
【where 筛选条件】
group by 分组的字段
【having 分组胡的筛选】
【order by 排序列表】 【asc desc】
筛选条件的分类
(1).简单条件运算符
> < = <> != >= <= <=>安全等于
(2).逻辑运算符
&& and
or ! not
(3).模糊查询
like：一般搭配通配符使用，用于判断字符型数值或数值型
通配符：%任意多个字符，_任意单个字符
between and
in
is null / is not null
is null pk <=>
is null ：仅仅可以判断null值，可读性较高。
<=>：既可以判断null值，又可以判断普通的数值，可读性较低。
去重
select distinct id from user;
+的作用
mysql里仅仅只有一个功能：运算符
select 100+90;
select ‘123’+90;其中一方为字符型，若转换成功则继续做加法运算
select ‘john’+90;若转换失败，则将字符型转换为0
select null+0; 若其中一方为null，则结果肯定为null
查询员工名和姓连接成一个字段，并显示为 姓名
select concat(‘a’,’b’,’c’);
ifnull(money,0)
如果为null，则为0
isnull函数
如果为null，返回1,否则返回0
10.单行函数
字符函数：
length 字符串长度
concat 连接字符串
substr 截取字符串
instr 查看字符串中某子字符串位置
trim 去前后相同字符
upper
lower
lpad 左填充
rpad 右填充
数学函数：
round 四舍五入
ceil 向上取整
floor 向下取整
truncate 截断
rand 获取随机数，返回0-1之间的小数
mod 求模
日期函数：
now
curdate curtime
year month
monthname 以英文形式返回月
day hour minute second
str_to_date
date_format
datediff 求相差天数
其他函数:
version
database
user
password(‘字符’) 返回该字符的密码形式
md5(‘字符’) 返回该字符的md5加密形式
控制函数：
if(条件表达式，表达式1,表达式2)
case
case 变量或表达式或字段
when 常量1 then 值1
…
else 值n
end
case 变量或表达式或字段
when 条件1 then 值1
…
else 值n
end
11.分组函数
sum 求和
avg 平均值
max 最大值
min 最小值
count 计算个数
(1)
sum、avg处理数值类型
max、min、count可以处理任何类型
(2)都忽略null值
(3)可以和distinct搭配实现去重的运算
(4)统计行数，效率
count(字段)：统计该字段非空值的个数
count(*)：统计结果集的行数
MYISAM存储引擎下，count(*)的效率最高
INNODB存储引擎下，count(*)和count(1)的效率差不多，比count(字段)要高一些
(5)和分组函数一同查询的字段要求是group by后的字段
可以先写select，from，gourp by，再写where，having
1.分组查询中的筛选条件分为两类
①分组函数做条件肯定是放在having子句中。
②能用分组前筛选的，就优先考虑使用分组前筛选。
| 数据源 | 位置 | 关键字 | |
|---|---|---|---|
| 分组前筛选 | 原始表 | group by子句的前面 | where |
| 分组后筛选 | 分组后的结果集 | group by子句的后面 | having |
2.group by子句支持单个字段分组，多个字段分组(多个字段之间用逗号隔开没有顺序要求)
3.也可以添加排序（排序放在整个分组查询的最后
12.分组查询
select 分组函数,列 (要求出现在group by的后面)
from 表
【where 筛选条件】
group by 分组的列表
【order by 子句】
select avg(salary),department_id,job_id
from employees
gourp by job_id,department_id
having avg(salary)>10000
order by avg(salary) desc;
13.进阶6：连接查询
含义：又称多表查询，当查询的字段来自多个表时，就会用到多表查询
笛卡尔乘积现象：当查询多个表时，没有添加有效的链接条件，导致多个表所有行实现完全连接。
发生原因：没有有效的连接条件
如何避免：添加有效的连接条件
按功能分类：
内连接：
等值连接、非等值连接、自连接
外链接：
左外连接、右外连接、全外连接(mysql不支持)
交叉连接
(1) SQL92语法
①等值连接
select 查询列表 from 表1 别名,表2 别名
where 表1.key = 表2.key
【and 筛选条件】
【group by 分组字段】
【having 分组后的筛选】
【order by 排序字段】
特点：
a.一般为表其别名
b.多表的顺序可以调换
c.n表连接至少需要n-1个连接条件
d.等值连接的结果是多表的交集部分
②非等值连接
select 查询列表 from 表1 别名,表2 别名
where 非等值的连接条件
【and 筛选条件】
【group by 分组字段】
【having 分组后的筛选】
【order by 排序字段】
③自连接
select 查询列表 from 表1 别名1,表1 别名2
where 等值的连接条件
【and 筛选条件】
【group by 分组字段】
【having 分组后的筛选】
【order by 排序字段】
(2) SQL99语法
select 查询列表
from 表1 别名 【连接类型】
join 表2 别名
on 连接条件
【where 筛选条件】
【group by 分组】
【having 筛选条件】
【order by 排序列表】
【limit 子句]
分类：
内连接：inner
外连接：
左外：left 【outer】
右外：right 【outer】
全外：full 【outer】
交叉连接：cross
①内连接
分类：
等值、非等值、自连接
特点：
①表的顺序可以调换
②内连接的结果 = 多表的交集
n表连接至少需要n-1个连接文件
②外连接
应用场景:用于查询一个表中有，另一个表没有的记录
特点：
a.外连接的查询结果为主表中的所有行
如果从表中有和它匹配的，则显示匹配的值
如果从表中没有和它匹配的，则显示null
外连接查询结果 = 内连接结果 + 主表中有而从表没有的记录
b.左外连接，left join 左边的是主表
右外连接，right join 右边的是主表
全外连接，full join 两边都是主表
c.左外和右外交换两个表的顺序，可以实现同样的效果。
d.全外连接 = 内连接的结果 + 表1中有但表2没有的 + 表2中有但表1没有的。
e.一般用于查询除了交集部分的剩余的不匹配的行。
③交叉连接
特点：
类似于笛卡尔积
(3) sql92和sql99 PK
功能：sql99支持的较多。
可读性：sql99实现连接条件和筛选条件的分离，可读性较高。
14.进阶7：子查询
含义：
嵌套在其他语句中的select语句，成为子查询或内查询；
外面的语句可以使insert、update、delete、select等，一般select作为外面语句较多。
分类：
按子查询出现的位置：
select后面：
仅仅支持标量子查询
from后面：
支持表子查询
where或having后面：
标量子查询 (单行) √
列子查询 (多行) √
行子查询
exists后面 （相关子查询）：
标量子查询
列子查询
行子查询
表子查询
按结果集的行列数不同：
标量子查询 (结果集只有一行一列)
列子查询 (结果集只有多行一列)
行子查询 (结果集有多行多列)
表子查询 (结果集一般为多行多列)
where或having后面
特点：
a.子查询放在小括号内
b.子查询一般放在条件的右侧
c.标量子查询，一般搭配着单行操作符使用
> < >= <= = <>
列子查询，一般搭配着多行操作符使用
in、any/some、all
d.子查询的执行优先于主查询执行，主查询的条件用到了子查询的结果
(1)标量子查询
案例：返回job_id与141号员工相同，salary比143号员工多的员工 姓名、job_id和工资
#①查询141号员工的job_id
select job_id
from employees
where employee_id=141
#②查询143号员工的salary
select salary
from employees
where employee_id=143
#③查询员工的姓名，job_id和工资，要求job_id=①并且salary=②
select last_name,job_id,salary
from employees
where job_id = (
select job_id
from employees
where employee_id=141
) and salary>(
select salary
from employees
where employee_id=143
);
案例：查询最低工资的员工姓名和工资
①最低工资
select min(salary) from employees
②查询员工的姓名和工资，要求工资=①
select last_name,salary
from employees
where salary=(
select min(salary) from employees
);
(2)列子查询(多行子查询)
案例：返回location_id是1400或1700的部门中的所有员工姓名
#①查询location_id是1400或1700的部门编号
select distinct department_id
from departments
where locaion_id in(1400,1700)
#②查询员工姓名，要求部门号是①列表中的某一个
select last_name
from employees
where department_id in ( --也可替换为 = any
select distinct department_id
from departments
where locaion_id in(1400,1700)
);
案例：查询所有的是领导的员工姓名
①查询所有员工的manager_id
select distinct manager_id from employees
②查询姓名，employee_id属于①列表的一个
select from employees
where employee_id in(
select manager_id from employees
);
(3)行子查询（结果集一行多列或多行多列）
案例：查询员工编号最小并且工资最高的员工信息
select *
from employees
where (employee_id,salary)=(
select min(employee_id),max(salary)
from employees
);
15.进阶8：分页查询
应用场景：当要显示的数据，一页显示不全，需要分页提交sql请求
语法：
select 查询列表 7
from 表 1
join type join 表2 2
on 连接条件 3
where 筛选条件 4
group by 分组字段 5
having 分组后的筛选 6
order by 排序的字段 8
limit offset,size; 9
-- offset要显示条目的起始索引（起始索引从0开始）
-- size 要显示的条目个数
特点：
①limit语句放在查询语句的最后
②公式
要显示的页数page，每页的条目数size
select 查询列表
from 表
limit (page-1)*size,size;
16.进阶8：联合查询
union 联合 合并：将多条查询语句的结果合并成一个结果
语法：
查询语句1
union
查询语句2
union
...
应用场景：
要查询的结果来自于多个表，且多个表没有直接的连接关系，但查询的信息一致时
特点：
①要求多条查询语句的查询列数是一致的
②要求多条查询语句的查询的每一列的类型和顺序最好一直
③union关键字默认去重，如果使用union all 可以包含重复项
DML语言
1.插入
方式一
语法：
insert into 表名(字段名...) values(值...);
特点：
(1) 要求值的类型和字段的类型要一致或兼容
(2) 字段的个数和顺序不一定与原始表中的字段个数和顺序一致
但必须保证值和字段一一对应
(3) 假如表中有可以为null的字段，注意可以通过以下两种方式插入Null值
①字段和值都省略
②字段写上，值使用null
(4) 字段和值的个数必须一致
(5) 字段名可以省略，默认所有列
方式二
语法：
insert into 表名 set 字段=值,字段=值,…;
两种方式的区别：
1.方式一支持一次插入多行，语法如下
insert into 表名 values(值,…), (值,…),… ;
2.方式一支持子查询，语法如下：
insert into 表名 查询语句：
2.修改
修改单表的记录
语法：
update 表名 set 字段=值,字段=值 【where 筛选条件】;
修改多表的记录
语法：
update 表1 别名
left right inner join 表2 别名 on 连接条件
set 字段=值,字段=值
【where 筛选条件】;
3.删除
方式一：使用delete
(1)删除单表的记录
语法：delete from 表名 【where 筛选条件】 【limit 条目数】
(2)级联删除
delete 别名1,别名2 from 表1 别名1
inner left right join 表2 别名2 on 连接条件
【where 筛选条件】
方式二：使用truncate
语法：truncate table 表名
两种方式的区别【面试题】
1.truncate删除后，如果再插入，标识列从1开始
delete删除后，如果再插入，标识列从断点开始
2.delete可以添加筛选条件
truncate不可以添加筛选条件
3.truncate效率较高
4.truncate没有返回值
delete可以返回受影响的行数
5.truncate不可以回滚
delete可以回滚
DDL语言
1、库的管理
1.创建库
create database 【if not exists】 库名 【character set 字符集名】;
2.修改库
alter database 库名 character set 字符集名；
3.删除库
delete database 【if exists】 库名;
2、表的管理
1.创建表
create table 【if not exists】表名(
字段名 字段类型 【约束】,
...
);
2.修改表
(1)添加列
alter table 表名 add column 列名 类型 【first|after 字段名】
(2)修改列
alter table 表名 modify column 列名 新类型 【新约束】;
(3)修改列名
alter table 表名 change column 旧列名 新列名 类型;
(4)删除列
alter table 表名 drop column 列名;
(5)修改表名
alter table 表名 rename 【to】 新表名;
3.删除表
drop table 【if exists】表名
4.复制表
(1)复制表的结构
create table 表名 like 旧表;
(2)复制表的结构+数据
create table 表名
select 查询列表 from 旧表 【where 筛选】;
3、数据类型
1.数值型
(1)整型
| tinyint | smallint | mediumint | int/integer | bigint | |
|---|---|---|---|---|---|
| 字节 | 1 | 2 | 3 | 4 | 8 |
特点：
①都可以设置无符号和有符号，默认有符号，通过unsigned设置无符号
②如果超出了范围，会报out or range 异常，插入临界值
③长度可以不指定，默认会有一个长度
长度代表显示的最大宽度，如果不够则左边用0填充，但需要搭配zerofill，并且默认变为无符号类型
(2)浮点型
定点数：decimal(M,D)
浮点数：
float(M,D) 4
double(M,D) 8
特点:
①M代表整数部位+小数部位的个数，D代表小数部位
②如果超出范围，则保 out or range异常，并且插入临界值
③M和D都可以胜率，但对于定点数，M默认为10，D默认为0
④如果精度要求较高，则优先考虑使用定点数
(3)字符型
char、varchar、binary、varbinary、enum、set、text、blob
char：固定长度的字符，写法为char(M)，最大长度不能超过M，其中M可以省略，默认为1
varchar：可变长度的字符varchar(M)，最大长度不能超过M，其中M可以省略
4、常见的约束
| not null | 非空，该字段的值必填 |
|---|---|
| unique | 唯一，该字段的值不可重复 |
| default | 默认，该字段的值不用手动插入就有默认值 |
| check | 检查，mysql不支持 |
| primary key | 主键，该字段的值不可重复并且非空 |
| froeign key | 外键，该字段的值引用了另外的表的字段 |
主键和唯一的区别【面试题】
区别：
1.一个表至多有一个主键，但可以有多个唯一
2.主键不允许为空，唯一可以为空
相同点：
都具有唯一性
都支持组合键，但不推荐
外键
1.用于限制两个表的关系，从表的字段值引用了主表的某字段值
2.外键列和主表的被引用列要求类型一致，意义一样，名称无要求
3.主表的被引用列要求是一个key（一般就是主键）
4.插入数据，先插入主表
删除数据，先删除从表
方式一：级联删除
alter table stuinfo add constraint fk_stu_major foreign key(majorid) references major(id) on delete cascade;
方式二：级联置空
alter table stuinfo add constraint fk_stu_major foreign key(majorid) references major(id) on delete set null;
2.创建表时添加约束
create table 表名(
字段名 字段类型 not null,
字段名 字段类型 unique,
constraint 约束名 foreign key(字段名) reference 主表 (被引用列)
);
注意：
| 支持类型 | 可以起约束名 | |
|---|---|---|
| 列级约束 | 除了外键 | 不可以 |
| 表级约束 | 除了非空和默认 | 可以，但对主键无效 |
列级约束可以在一个字段上追加多个，中间用空格隔开，没有顺序要求。
3.修改表时添加或删除约束
(1)非空
添加非空
alter table 表名 modify column 字段名 字段类型 not null;
删除非空
alter table 表名 modify column 字段名 字段类型;
(2)默认
添加默认
alter table 表名 modify column 字段名 字段类型 default 值;
删除默认
alter table 表名 modify column 字段名 字段类型;
(3)主键
添加主键
alter table 表名 add 【constraint 约束名】 primary key(字段名);
删除主键
alter table 表名 drop primary key;
(5)唯一
添加唯一
alter table 表名 add unique(字段名);
删除唯一
alter table 表名 drop index 索引名;
(5)外键
添加外键
alter table 表名 add foreign key(字段名) references 主表(被引用列);
删除外键
alter table 表名 drop foreign key 约束名;
TCL语言
1、事务:一条或多条sql语句组成一个执行单位，一组sql语句要么都执行，要么都不执行。
2、特点(ACID)：
A.原子性（Atomicity）：一个事务是不可再分割的整体，要么都执行要么都不执行；
B.一致性（Consistency）：一个事务可以使数据从一个一致状态切换到另外一个一致的状态；
C.隔离性（Isolation）：一个事务不受其他事务的干扰，多个事务互相隔离的；
D.持久化（Durubility）：一个事务一旦提交了，则永久的持久化到本地。
3、事务的使用步骤：
隐式(自动)事务：没有明显的开始和结束，本身就是一条事务可以自动提交，比如insert、update、delete
显示事务：具有明显的开启和结束
使用显示事务：
①开启事务
set autocommit=0;
start transaction; --可省略
②编写一组逻辑sql语句
注意：sql语句支持的是insert、update、delete
设置回滚点：
savepoint 回滚点名；
③结束事务
提交：commit
回滚：rollback
回滚到指定的地方：roolback to 回滚点名；
4、并发事务
1.事务的并发问题是如何发生的？
多个事务同时操作同一个数据库的相同数据时
2.并发问题都有哪些？
脏读：一个事务读取了其他事务还没有提交的数据，读到的是其他事务“更新”的数据。
不可重复读：一个事务多次读取，结果不一样
幻读：一个事务读取了其他事务还没有提交的数据，只是读到的是 其他事务“插入”的数据。
3.如何解决并发问题？
通过设置隔离级别来解决并发问题。
4.隔离级别
mysql默认的事务处理级别是’REPEATABLE-READ’,也就是可重复读。
默认系统事务隔离级别是READ COMMITTED,也就是读已提交。
| 脏读 | 不可重复读 | 幻读 | |
|---|---|---|---|
| read uncommitted：读未提交 | × | × | × |
| read committed：读已提交 | √ | × | × |
| repeatable read：可重复读 | √ | √ | × |
| serializable：串行化 | √ | √ | √ |
其他
1、含义：mysql5.1版本出现的新特性，本身是一个虚拟表，它的数据来自于表，通过执行时动态生成。
好处：
1.简化了Sql语句
2.提高了sql的重用性
3.保护基表的数据，提高了安全性
2、创建
create view 视图名
as
查询语句;
3、修改
方式一：
create or replace view 视图名
as
查询语句;
方式二：
alter view 视图名
as
查询语句;
4、删除
drop view 视图1,视图2,...；
5、查看
desc 视图名；
show create view 视图名;
6、使用
insert、update、delete、select
注意：视图一般用于查询的，而不是更新的，所以具备以下特点的视图都不允许更新
①包含分组函数、group by、distinct、having、union
②join
③常量视图
④where后的子查询用到了from中的表
⑤用到了不可更新的视图
7、视图和表的对比
| 关键字 | 是否占用物理空间 | 使用 | |
|---|---|---|---|
| 视图 | view | 占用较小，只保存sql逻辑 | 一般用于 查询 |
| 表 | table | 保存实际的数据 | 正增删改查 |
1、系统变量
说明：变量由系统提供的，不用自定义
语法：
①查看系统变量
show 【global|session】 variables like '';--默认是session
②查看指定的系统变量的值
select @@【global|session】.变量名; --默认是session
③为系统变量赋值
set 【global|session】 变量名=值；
set @@global.变量名=值
set @变量名 = 值;
2、自定义变量
1.用户变量
作用域：针对于当前连接（会话）生效
位置：begin end 里面，也可以放在外面
使用：
①声明并赋值：
set @变量名=值：
set @变量名:=值;
select @变量名:=值;
②更新值
--方式一：
set @变量名=值：
set @变量名:=值;
select @变量名:=值;
--方式二：
select xx into @变量名 from 表;
③使用
select @变量名;
2.局部变量
作用域：仅仅在定义它的begin end 中有效。
位置：只能放在begin end 中，而且只能放在第一句。
create procedure pro1()
begin
declare i int default 1;
end
①声明
declare 变量名 类型 【default 值】
②赋值或更新
--方式一：
set @变量名=值：
set @变量名:=值;
select @变量名:=值;
--方式二：
select xx into 变量名 from 表;
③使用
select @变量名;
3、存储过程和函数
说明：都类似于java中的方法，将一组完成特定功能的逻辑语句包装起来，对外暴露名字。
好处：
①提高了重用性
②sql语句简单
③减少了和数据库服务器连接的次数，提高了效率
(1)创建
create procedure 存储过程名(参数模式 参数名 参数类型)
begin
存储过程体
end
注意：
①参数模式：in、out、inout，其中in可以省略。
②存储过程体的每一条sql语句都需要用分号结尾。
(2)调用
call 存储过程名(实参列表);
#举例
调用in模式的参数：call sp1('值');
调用out模式的参数：set @name; call sp1(@name);select @name;
调用inout模式的参数：set @name = 值; call sp1(@name); select @name;
(3)查看
show create procedure 存储过程名;
(4)删除
drop procedure 存储过程名;
(1)创建
create function 函数名(参数名 参数类型) returns 返回类型
begin
函数体
end
注意，函数体中必须要有return 语句。
(2)调用
select 函数名(实参列表);
(3)查看
show create function 函数名;
(4)删除
drop function 函数名;
4、流程控制结构
1.分支结构
(1) if 函数
语法：
if(条件,值1,值2)
位置：可以作为表达式放在任何位置
(2)case结构
语法1：
case 表达式或字段
when 值1 then 语句1;
...
else 语句n;
end [case];
语法2：
case 表达式或字段
when 条1 then 语句1;
...
else 语句n;
end [case];
位置：可以放在任何位置
如果放在begin end 外面，作为表达式结合着其他语句使用。
如果放在begin end 里面，一般作为独立的语句使用。
(3) if 结构
语法：
if 条件1 then 语句1
elseif 条件2 then 语句2
...
else 语句n;
end if;
位置：只能放在begin end 中。
2.循环结构
位置：只能放在begin end中
特点：都能实现循环结构
对比：
①这三种循环都可以省略名称，但如果循环中添加了循环控制语句(leave或iterate)则必须添加名称。
②loop 一般用于实现简单的死循环
while 先判断后执行
repeat 先执行后判断，无条件至少执行一次。
(1)while
语法：
【名称:】while 循环条件 do
循环体
end while 【名称】;
(2)loop
语法：
【名称：】loop
循环体
end loop 【名称】;
(3)repeat
语法：
【名称:】repeat
循环体
until 结束条件
end repeat 【名称】;
3.循环控制语句
leave：类似于break，用于跳出所在的循环
iterate：类似于continue，用于结束本次循环，继续下一次