MySQl这一篇就够了（二）

分页查询limit（八）

select 查询列表

from 表名

group by 分组列

having 分组后筛选

order by 排序

limit offset ，size；

offset：要显示条目的起始索引

size 要显示的数量

#查询前五条数据
select * from employees limit 0,5;

#有奖金的员工信息 ，并且工资较高的前10名显示出来
select * from employees
where commission_pct is not null
order by salary desc
limit 10;

特点：limit 语句放到查询的最后 执行流程也在最后

总结

查询语句中所有的关键字和执行顺序

select 查询列表 7

from 表名 1

连接类型 join 表2 2

on 连接条件 3

where 筛选条件 4

group by 分组列 5

having 分组后筛选 6

order by 排序 8

limit offset ，size； 9

#查询工资最低的员工信息
select * from employees
where salary=(
select min(salary)
from employees
);
#查询平均工资最低的部门信息
select * from departments
where department_id = (
select department_id
from employees
group by department_id
order by avg(salary)
limit 1);

基础查询（九）联合查询

union 联合 合并 并将多条查询语句合并成一个结果

语法：

查询语句1

union

查询语句2

…

要求多条查询语句的查询列数是一样的

nuion 默认去重 不想去重就写 union all

DML语言

插入：insert

删除：delete

修改：update

delete pk truncate

1 delete 可以加where 条件 truncate 不能加

2 truncate 效率高

3 truncate没有返回值

4 delete删除数据后 在插入新数据从断电开始

5 truncate 删除数据后 在插入数据从1开始

6truncate 删除后不能回滚

DDL

创建 create

修改 alter、

删除 drop

库的管理

创建库Books

create databases books;

如果不存在就创建

create databases if not exists books;

更改库的字符集

alter datdabase books character set utf8;

库的删除

drop database books

表的管理

desc 表名 查看表结构

创建表

create table 表名(

列名 列的类型 【长度 约束】

)

create table book(

id int,

bName varvhar(20), #最大长度20

price double ,

)

修改表

修改列名

alter table 表名 change [column] 旧列名 新列名 类型

修改类型

alter table 表名 modify column 列名 修改的类型

添加列

alter table 表名 add column 新列名 类型

删除列

ater table 表名 drop cloumn 列名

修改表名

alter table 表名 rename to 列名

删除表

drop table

复制表

复制 结构+数据

create table 表名 select * from 之前的表名

复制结构

create table 表名 like 之前的表

数据类型

小数：

float(M,D)

double(M,D)

定点型

dec(M,D)

decimal(M,D)

特点

M ：整数部位加小数部位长度

N ：小数部分长度

MN、可以省略 如果是decimal类型 M默认10 N默认0 float double 会根据插入数值来确定

定点型：精度较高 如货币运算

字符型

较短的文本

char（M） M为0-255之间的整数 代表最多插入多少字符 固定长度的字符 耗费空间 效率高 M默认为1

varchar（M）0-65535之间的整数 代表最多插入多少字符 可变长度的字符 节省空间 效率低M不可以省略

较长的文本 text

blob(较大的二进制)

日期型

datetime 8字节 范围1000-9999 不受时区影响

timestamp 4字节 范围1970-2038 受时区影响

约束

分类 六大约束

​ NOT NULL 保证该字段的值不能为空

​ DEFAULT 用于保证该字段有默认值

​ PRIMARY KEY 用于保证该字段唯一 不可以为空

​ UNIQUE 用于保证该字段唯一 可以为空

​ CHECK mysql不支持

​ FOREIGN KEY 保证该字段值 必须来自主表的主键 不支持列级约束

添加约束的时机 创建表时 修改表时

列级约束

create table studinfo (
id int primary key ,
stuName varchar(20) not null,
seat int unique,
age int default 18,
majorid int foreign key major(id)); //报错 不支持外键加载列上

表级约束

create table studinfo (
	
	id int 
,
	stuName varchar(20) ,
	gender char(1),
	seat int ,
	age int ,
	majorid int ,
	
	constraint pk primary key(id),
	constraint uq unique (seat),
	constraint fk_major foreign key (majorid) references major(id)
);

自增长列 auto_increment 只能给数值型自增

事务

在mysql中用的最多的存储引擎有：innodb ，myisam ，memory 其中innodb支持事务 myisam memory不支持事务

原子性：一个事务不可再分割，要么都执行 要么都不执行

一致性：一个事务执行会使数据从一个一致状态切换到另一个一致状态

隔离性：一个事务的执行不受其他事务的干扰

持久性：一个事务一旦提交 就会永久改变数据

事务的创建

隐式事务，事务没有明显的开启与创建

比如 insert update delete、

显示事务：有明显开启和结束的标记 前提是关闭自动提交事务功能

set auotcommit=0 关闭事务

commit 提交事务

rollback 提交事务

事务的隔离级别

​ 出现脏读 幻读 不可重复读

read uncomitted √ √ √

read committed × √ √

repeatable read × × ×

seriallizable × × ×

默认repeatable read 级别

查看隔离级别 select @@tx isolation

设置隔离级别 set session|global transaction isloation level 隔离级别

视图

含义 ：虚拟表 和普通表一样使用

mysql5.1 之后出现的新特性 是 通过表动态生成的数据

以前查询 这么查：

select stuname .majorname

from stuinfo s

inner join major

on s.majorid=m.id

where s.stuname like ‘张%’

现在用视图包装一下

create view v1

as

select stuname .majorname

from stuinfo s

inner join major

on s.majorid=m.id

通过视图插

select *from v1 where where stuname like ‘张%’

应用场景

当多个地方用到同样的查询结果或者 该sql语句比较复杂 就使用视图

#查询名字中包含a字符的员工名 部门名 工种信息
create view myvi
as
select last_name,department_name,job_title
from employees e
join departments d on d.department_id=e.department_id
join jobs j on j.job_id=e.job_id;

select * from myvi where last_name like ‘%a%’;

视图的修改

create or replace view 视图名//视图名如果存在就修改 如果不存在就替换

as …

删除视图

drop view 视图名1，视图名 2 。。。。。

修改视图数据

会对原始表发生作用

insert into v1 values(‘aaa’,‘bbb’,'cccc)

update v1 set name =‘zzzz’ where id =11

delete v1 where id =11

变量

系统变量：

​ 全局变量

​ 会话变量

自定义变量

​ 用户变量

​ 局部变量

如果是全局级别

show global variables

如果是会话级别

show session variables

#查看所有的全局变量
show global variables；
#查看部分全局变量
show global variables like ‘%char%’;
#查询自动提交是否开启
select @@session.autocommit;
#查询隔离级别
select @@tx_isolation;

特点 ：

​ 全局变量作用域 ：对所有连接有效 服务器每次重启 全局变量都会变成默认值

会话变量 仅对当前连接有效

自定义变量

用户变量

用户自定义变量只对于当前会话有效

1.声明并初始化三种方式

set @用户变量名=值；

set @用户变量名:=值；

select @用户变量名:=值；

赋值三种

set @用户变量名=值；

set @用户变量名:=值；

select @用户变量名:=值；

局部变量

declare 变量名 数据类型;

declare 变量名 数据类型 default 值;

赋值 select @局部变量名:=值

只能用在 begin end 的第一句话中