通配符

一般用于模糊搜索。

select * from info where name like "%本%";
select * from info where name like "%伟";
select * from info where email like "%@live.com";
select * from info where name like "卢%伟";
select * from info where name like "k%y";
select * from info where email like "lubenwei%";
select * from info where email like "_@live.com";
select * from info where email like "_ubenwei@live.com";
select * from info where email like "__benwei@live.com";
select * from info where email like "__benwei_live.co_";

注：数量少的时候用。

映射

想要获取的列。

select * from info;
select id, name       from info;
select id, name as NM     from info; 给name起了个别名
select id, name, 123  from info; 输出的时候又额外增加一列全是123
select id, name, 123 as age  from info; 全是123的表头起名为age
注意：少些select * ,自己需求。
select 
  id,
  name,
  666 as num,
  ( select max(id) from depart ) as mid, -- max/min/sum
  ( select min(id) from depart) as nid, -- max/min/sum
  age
from info;

select 
  id,
  name,
  ( select title from depart where depart.id=info.depart_id) as x1
from info;
# 注意：效率很低

case when then else

select 
  id,
  name,
  case depart_id when 1 then "第1部门" end v1
from info;
select 
  id,
  name,
  case depart_id when 1 then "第1部门" else "其他" end v2
from info;
select 
  id,
  name,
    -- 如果depart_id等于1
  case depart_id when 1 then "第1部门" end v1,  --注：没有就是Null
  case depart_id when 1 then "第1部门" else "其他" end v2,
  case depart_id when 1 then "第1部门" when 2 then "第2部门" else "其他" end v3,
    -- 如果age<18  范围外为Null
  case when age<18 then "少年" end v4,
  case when age<18 then "少年" else "油腻男" end v5,
  case when age<18 then "少年" when age<30 then "青年" else "油腻男" end v6
from info;
-- 直接case when不能用于等于如case when depart_id=1 报错
-- 别忘记逗号 且最后一条数据都不加逗号
select 
  id,
  name,
  case depart_id when 1 then "开发" when 2 then "运营" else "销售" end v3,
  case when age<18 then "少年" when age<30 then "青年" else "油腻男" end v6
from info;

如：打印出表中信息，年龄小于18的为少年，年龄。。。。

排序

select * from info order by age asc;  -- 顺序
select * from info order by age desc; -- 倒序
select * from info order by id desc;
select * from info order by id asc;
select * from info order by age asc,id desc; -- 优先按照age从小到大；如果age相同则按照id从大到小。
select * from info where id>10 order by age asc,id desc;
select * from info where id>6 or name like "%y" order by age asc,id desc;

取部分 limit offset

一般要用于获取部分数据。

select * from info limit 5;                       -- 获取前5条数据
select * from info order by id desc limit 3;            -- 先排序，再获取前3条数据
select * from info where id > 4 order by id desc limit 3;     -- 先排序，再获取前3条数据
select * from info limit 3 offset 2;  -- 从位置2开始，向后获取前3数据

数据库表中：1000条数据。

第一页：select * from info limit 10 offset 0;

第二页：select * from info limit 10 offset 10;

第三页：select * from info limit 10 offset 20;

第四页：select * from info limit 10 offset 30;

分组

这个要注意不用where用having了。

select age,max(id),min(id),count(id),sum(id),avg(id) from info group by age;
select age,count(1) from info group by age;
select depart_id,count(id) from info group by depart_id;
select depart_id,count(id) from info group by depart_id having count(id) > 2;
select count(id) from info;
select max(id) from info;
select age,max(id),min(id),sum(id),count(id) from info group by age;
select age,name from info group by age;  -- 不建议
select * from info where id in (select max(id) from info group by age);
select age,count(id) from info group by age having count(id) > 2;
select age,count(id) from info where id > 4 group by age having count(id) > 2;  -- 聚合条件放在having后面

到目前为止SQL执行顺序：

where

group by

having

order by

limit

在id大于2的数据中统计不同年龄的各有多少个，把个数大于1的按照年龄从大到小显示出来，且只显示一个。

select age,count(id) from info where id > 2 group by age having count(id) > 1 order by age desc limit 1;

- 要查询的表info

- 条件 id>2

- 根据age分组

- 对分组后的数据再根据聚合条件过滤 count(id)>1

- 根据age从大到小排序

- 获取第1条

左右连表

多个表可以连接起来进行查询。

展示用户信息&部门名称：

主表 left outer join 从表 on 主表.x = 从表.id 
select * from info left outer join depart on info.depart_id = depart.id;
select info.id,info.name,info.email,depart.title from info left outer join depart on info.depart_id = depart.id;

从表 right outer join 主表 on 主表.x = 从表.id
select info.id,info.name,info.email,depart.title from info right outer join depart on info.depart_id = depart.id;

为了更加直接的查看效果，我们分别在 depart 表和 info 中额外插入一条数据。

insert into depart(title) values("运维");

这样一来主从表就有区别：

info主表，就以info数据为主，depart为辅。

select info.id,info.name,info.email,depart.title from info left outer join depart on info.depart_id = depart.id;

depart主表，，就以depart数据为主，info为辅。

select info.id,info.name,info.email,depart.title from info right outer join depart on info.depart_id = depart.id;
select * from info left outer join depart on ....
select * from depart left outer join info on ....

简写：select * from depart left join info on ....

-- 内连接:    表  inner join 表  on 条件
select * from info inner join depart on info.depart_id=depart.id;
到目前为止SQL执行顺序：
    join 
    on 
    where 
    group by
    having 
    order by
    limit

写在最后：多张表也可以连接。

联合

select id,title from depart 
union
select id,name from info;
select id,title from depart 
union
select email,name from info;
-- 列数需相同

select id from depart 
union
select id from info;
-- 自动去重

select id from depart 
union all
select id from info;
-- 保留所有

表关系

单表：

略

一对多：

create table depart(
  id int not null auto_increment primary key,
    title varchar(16) not null
)default charset=utf8;
create table info(
  id int not null auto_increment primary key,
    name varchar(16) not null,
    email varchar(32) not null,
    age int,
    depart_id int not null,
    constraint fk_info_depart foreign key (depart_id) references depart(id)
)default charset=utf8;

如果表结构已创建好了，额外想要增加外键：

alter table info add constraint fk_info_depart foreign key info(depart_id) references depart(id);

删除外键：

alter table info drop foreign key fk_info_depart;

多对多

create table boy(
  id int not null auto_increment primary key,
    name varchar(16) not null
)default charset=utf8;
create table girl(
  id int not null auto_increment primary key,
    name varchar(16) not null
)default charset=utf8;
create table boy_girl(
  id int not null auto_increment primary key,
    boy_id int not null,
    girl_id int not null,
    constraint fk_boy_girl_boy foreign key boy_girl(boy_id) references boy(id),
    constraint fk_boy_girl_girl foreign key boy_girl(girl_id) references girl(id)
)default charset=utf8;

如果表结构已创建好了，额外想要增加外键：

alter table boy_girl add constraint fk_boy_girl_boy foreign key boy_girl(boy_id) references boy(id);
alter table boy_girl add constraint fk_boy_girl_girl foreign key boy_girl(girl_id) references girl(id);

删除外键：

xxxxxxxxxx alter table info drop foreign key fk_boy_girl_boy;alter table info drop foreign key fk_boy

用户管理

在MySQL的默认数据库 mysql 中的 user 表中存储着所有的账户信息（含账户、权限等）。

创建和删除用户

create user '用户名'@'连接者的IP地址' identified by '密码';
create user wuyou@127.0.0.1 identified by 'root123';
drop user wuyou@127.0.0.1;

create user wuyou@'127.0.0.%' identified by 'root123';
drop user wuyou@'127.0.0.%';

create user wuyou@'%' identified by 'root123';
drop user wuyou@'%';

create user 'wuyou'@'%' identified by 'root123';
drop user 'wuyou'@'%';

修改用户

rename user '用户名'@'IP地址' to '新用户名'@'IP地址';
rename user wuyou@127.0.0.1 to wuyou@localhost;

rename user 'wuyou'@'127.0.0.1' to 'wuyou'@'localhost';

修改密码

set password for '用户名'@'IP地址' = Password('新密码')
set password for 'wuyou'@'%' = Password('123123');

授权管理

创建好用户之后，就可以为用户进行授权了。

授权

grant all privileges on *.* TO 'wuyou'@'localhost';         -- 用户wuyou拥有所有数据库的所有权限
grant all privileges on day26.* TO 'wuyou'@'localhost';     -- 用户wuyou拥有数据库day26的所有权限
grant all privileges on day26.info TO 'wuyou'@'localhost';  -- 用户wuyou拥有数据库day26中info表的所有权限
grant select on day26.info TO 'wuyou'@'localhost';          -- 用户wuyou拥有数据库day26中info表的查询权限
grant select,insert on day26.* TO 'wuyou'@'localhost';      -- 用户wuyou拥有数据库day26所有表的查询和插入权限
grant all privileges on day26db.* to 'wuyou'@'%';

注意：flush privileges; -- 将数据读取到内存中，从而立即生效。

对于权限
all privileges 除grant外的所有权限

select 仅查权限

select,insert 查和插入权限

...

usage 无访问权限

alter 使用alter table

alter routine 使用alter procedure和drop procedure

create 使用create table

create routine 使用create procedure

create temporary tables 使用create temporary tables

create user 使用create user、drop user、rename user和revoke all privileges

create view 使用create view

delete 使用delete

drop 使用drop table

execute 使用call和存储过程

file 使用select into outfile 和 load data infile

grant option 使用grant 和 revoke

index 使用index

insert 使用insert

lock tables 使用lock table

process 使用show full processlist

select 使用select

show databases 使用show databases

show view 使用show view

update 使用update

reload 使用flush

shutdown 使用mysqladmin shutdown(关闭MySQL)

super 􏱂􏰈使用change master、kill、logs、purge、master和set global。还允许mysqladmin􏵗􏵘􏲊􏲋调试登陆

replication client 服务器位置的访问

replication slave 由复制从属使用

对于数据库和表

数据库名.* 数据库中的所有

数据库名.表名指定数据库中的某张表

数据库名.存储过程名指定数据库中的存储过程

*.* 所有数据库

查看授权

show grants for 'wuyou'@'localhost';
show grants for 'wuyou'@'%';

取消授权

revoke ALL PRIVILEGES on day26.* from 'wuyou'@'localhost';
revoke ALL PRIVILEGES on day26db.* from 'wuyou'@'%';

注意：flush privileges; -- 将数据读取到内存中，从而立即生效。

哪怕没有A这个数据库，被授权了A数据库的操作，一样不会报错。

这里的localhost与127.0.0.1不一样。

一般情况下，在很多的正规公司，数据库都是由 DBA 来统一进行管理，DBA为每个项目的数据库创建用户，并赋予相关的权限。

索引

在数据库中索引最核心的作用是：加速查找。例如：在含有300w条数据的表中查询，无索引需要700秒，而利用索引可能仅需1秒。

索引原理

为什么加上索引之后速度能有这么大的提升呢？因为索引的底层是基于B+Tree的数据结构存储的。

数据库的索引是基于上述B+Tree的数据结构实现，但在创建数据库表时，如果指定不同的引擎，底层使用的B+Tree结构的原理有些不同。

myisam引擎，非聚簇索引（数据和索引结构分开存储）
innodb引擎，聚簇索引（数据和主键索引结构存储在一起）
非聚簇索引（mysiam引擎）
聚簇索引（innodb引擎）

上述聚簇索引和非聚簇索引底层均利用了B+Tree结构结构，只不过内部数据存储有些不同罢了。

在企业开发中一般都会使用 innodb 引擎（内部支持事务、行级锁、外键等特点），在MySQL5.5版本之后默认引擎也是innodb。

常见索引

在innodb引擎下，索引底层都是基于B+Tree数据结构存储（聚簇索引）。

在开发过程中常见的索引类型有：

主键索引：加速查找、不能为空、不能重复。 + 联合主键索引
唯一索引：加速查找、不能重复。 + 联合唯一索引
普通索引：加速查找。 + 联合索引

主键和联合主键索引

create table 表名(
    id int not null auto_increment primary key,   -- 主键
    name varchar(32) not null
);
create table 表名(
    id int not null auto_increment,
    name varchar(32) not null,
    primary key(id)
);
create table 表名(
    id int not null auto_increment,
    name varchar(32) not null,
    primary key(列1,列2)          -- 如果有多列，称为联合主键（不常用且myisam引擎支持）
);

alter table 表名 add primary key(列名);
alter table 表名 drop primary key;

注意：删除索引时可能会报错，自增列必须定义为键。

ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

alter table 表 change id id int not null;

create table t7(
    id int not null,
    name varchar(32) not null,
    primary key(id)
);
alter table t6 drop primary key;

唯一和联合唯一索引

create table 表名(
    id int not null auto_increment primary key,
    name varchar(32) not null,
    email varchar(64) not null,
    unique ix_name (name),
    unique ix_email (email),
);
create table 表名(
    id int not null auto_increment,
    name varchar(32) not null,
    unique (列1,列2)               -- 如果有多列，称为联合唯一索引。
);

create unique index 索引名 on 表名(列名);
drop unique index 索引名 on 表名;

索引和联合索引

create table 表名(
    id int not null auto_increment primary key,
    name varchar(32) not null,
    email varchar(64) not null,
    index ix_email (email),
    index ix_name (name),
);
create table 表名(
    id int not null auto_increment primary key,
    name varchar(32) not null,
    email varchar(64) not null,
    index ix_email (name,email)     -- 如果有多列，称为联合索引。
);

create index 索引名 on 表名(列名);
drop index 索引名 on 表名;

MySQL数据库（2）

通配符

映射

排序

分组

左右连表

联合

表关系

用户管理

授权管理

索引

索引原理

常见索引

热门文章

最新文章

相关课程

相关电子书

相关实验场景