MySql学习笔记

1. MySql简介

MySQL是一个关系型数据库管理系统，由瑞典MySQL AB 公司开发，目前属于 Oracle 旗下产品。MySQL 是最流行的关系型数据库管理系统之一，在 WEB 应用方面，MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件之一。
MySQL是一种关系数据库管理系统，关系数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。
MySQL所使用的 SQL 语言是用于访问数据库的最常用标准化语言。MySQL 软件采用了双授权政策，分为社区版和商业版，由于其体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，一般中小型网站的开发都选择 MySQL 作为网站数据库。

2. 使用MySql

2.1sql语言种类

  • DQL (Data Query Language(数据查询语言))
    select

  • DML (Data Manipulation Language(数据操作语言))
    insert updata delete

  • DDL (Data Definition Language(数据定义语言))
    create alter drop

  • DCL (Data Control Language(数据控制语言))
    grant revole

  • TCL(Transaction Control Language(事务控制语言))
    savepoint rollback set transaction commit

2.2 创建数据库
create database 数据库名 default character set 字符编码;
2.3 查看数据库
show databases
2.4 查看数据库字符编码
select schema_name,default_character_set_name
from information_schema.schemata where schema_name='test3';
2.5 删除数据库
drop database 数据库名称;
2.6 选择数据库
use 数据库名

3. MySql中的数据类型

3.1 数值类型

MySql支持所有Sql标准数值类型
作为SQL标准的扩展，MySql也支持整数类型TINYINT、MEDIUMINT 和 BIGINT。


image.png

数据类型中的m是指显示长度，并不表示存储长度，只有字段指定zerofill时有用
例如：int(3)，如果实际值是2，如果列指定了zerofill，查询结果就是002，左边用0来填充。

3.2 浮点型
image.png
3.3 字符串型

image.png

char和varchar的区别

  • char(n)，如果存入的字符数少于n，则以空格补在后面，查询时再将空格去掉。所以char类型存储的数据不能有空格，varchar不限于此。
  • char类型的字符串检索速度比varchar快

varchar和text的区别

  • varcahr可以指定n，text不能，内部存储varchar存入的是实际字符+1(n<=255)个字节或2(n>255)个字节，text是实际字符数+2个字节。
  • text类型没有默认值
  • varcahr可直接创建索引，text创建索引要指定前多少个字符。varchar查询速度优于text，在都创建索引的情况下text的索引似乎不起作用。
3.4 日期类型
image.png
3.5 二进制数据(BLOB)
  • BLOB 和 TEXT 存储方式不同，TEXT 以文本方式存储，英文存储区分大小写，而BLOB是以二进制方式存储，不分大小写。
  • BLOB存储的数据只能整体读出。
  • TEXT可以指定字符集，BLOB不能。

4. 创建表与删除表

4.1 创建表
4.1.1 通过DDL语句创建表

示例
创建一个employee表，其中包含雇员id，雇员姓名，雇员薪水。

create table employee(
    employee_id int,
    last_name varchar(30),
    salary float(8,2)
);

查看自己创建的表

show tables;
4.2 删除表
4.2.1 通过DDL语句删除表

示例
删除employee表

drop table employee;

5. 修改表

5.1 使用DDL语言修改表名
alter table 表名 rename 新表名;
5.2 使用DDL语言修改列名
alter table 表名 change column 旧列名 新列名 列类型;
5.3 使用DDL语言修改列的类型
alter table 表名 modify 列名 新列类型;
5.4 使用DDL语言添加列
alter table 表名 add column 新列名 类型及长度;
5.5 使用DDL语言删除列
alter table 表名 drop column 列名;

6.MySql中的约束

6.1 约束类型
  • 非空约束（not null）
  • 唯一性约束（unique）
  • 主键约束（primary key）PK
  • 外键约束（foreign key）FK
  • 检查约束（目前MySql不支持Oracle支持）
6.2 创建表时添加约束

查询表中的约束信息

show keys from 表名;
6.2.1 示例1

创建department表包含department_id该列为主键且自动增长，department_name列不允许重复，location_id列不允许为空

create table department(
    department_id int(5) primary key auto_increment,
    department_name varchar(10) unique,
    location_id int(5) not null
);

注意：自动增长的列的类型只能是int类型，一个表中只能有一个列是自动增长的，自动增长的列必须包含主键约束或者唯一性约束。

6.2.2 示例2

创建employee表，包含employee_id该列为主键且自动增长，last_name列不允许有空值，email列不允许有重复且不能为空，dept_id为外键参照department表的主键

create table employee(
    employee_id int(5) primary key auto_increment,
    last_name varchar(10) not null,
    email varchar(20) unique not null,
    dept_id int(5), 
    constraint emp_fk foreign key(dept_id) references department(department_id)
);

create table employee(
    employee_id int(5) primary key auto_increment,
    last_name varchar(10) not null,
    email varchar(20) unique not null,
    dept_id int(5), 
    constraint 外键名 foreign key(需要添加外键的字段) references 引用的表名(引用的表的字段)
);
6.3 修改表实现约束的添加与删除
6.3.1 主键约束
  • 添加主键约束
    将emp表中的emp_id修改为主键且自动增长
alter table 表名 add primary key(列名)

#添加主键
alter table emp add primary key(emp_id);
#添加自动增长
alter table emp modify emp_id int auto_increment;
  • 删除主键约束
    删除emp_id的主键约束
alter table 表名 drop primary key;

注意：删除主键时，如果主键具有自动增长能力，需要先去掉自动增长然后再删除主键

#去掉自动增长
alter table emp modify emp_id int;
#去掉主键
alter table emp drop primary key;
6.3.2 非空约束
  • 添加非空约束
    向emp表中的salary添加非空约束
alter table 表名 modify 列名 类型 not null;

#为emp表中的salary添加非空约束
alter table emp modify salary double not null;
  • 删除非空约束
#为emp表中的salary删除非空约束
alter table emp modify salary double null;
6.3.3 唯一约束
  • 添加唯一约束
    向emp表中的name添加唯一约束
alter table 表名 add constraint 约束名 unique(列名)

alter table emp add constraint emp_uk unique(name);
  • 删除唯一约束
    删除name的唯一约束
alter table 表名 drop key 约束名

alter table emp drop key emp_uk;
6.3.4 外键约束
  • 添加外键约束
alter table 表名 add constraint 约束名 foreign key(列名) references 参照的表名(参照的列名)

修改emp表，添加dept_id列

alter table emp add column dept_id int;

向emp表中的dept_id列添加外键约束

alter table emp add constraint e_fk 
foreign key(dept_id) references department(department_id);
  • 删除外键约束
    删除外键
alter table 表名 drop foreign key 约束名

删除外键索引(索引名与约束名相同)

alter table 表名 drop index 索引名

删除dept_id的外键约束

#删除外键
alter table emp drop foreign key e_fk;
#删除索引
alter table emp drop index e_fk;

因为在mysql中外键存储在索引中，删除完外键后需要在删除索引，这样才能彻底删除外键

7.MySql的DML操作

7.1 添加数据(INSERT)
  • 插入数据（选择插入）
    向department表中添加一条数据，部门名称为market，工作地点ID为1
insert into 表名(列名1,列名2,列名3...) values(值1,值2,值3...)

insert into department(department_name, location_id)
values('market', 1);
  • 插入数据（完全插入）
insert into 表名 values(值1,值2,值3...)

如果主键是自动增长的，需要使用default或者null或0占位

向department表中插入一条数据，部门名称为development，工作地点ID为2。使用default占位

insert into department values(default, 'development', 2);

向department表中插入一条数据，部门名称为human，工作地点为3。使用null占位

insert into department values(null, 'human', 3);

向department表中插入一条数据，部门名称为teaching，工作地点为4。使用0占位

insert into department values(0, 'teaching', 4);
  • 创建表时指定列的默认值
    创建表emp3，该表包含emp_id主键且自增，包含name，包含address该列默认值为'unknown'
create table emp3(
    emp_id int(5) primary key auto_increment,
    name varchar(30),
    address varchar(50) default 'unknow'
);
  • 修改表添加列的默认值
    修改表emp3，添加job_id该列默认值为0
alter table emp3 add column job_id int default 0;

插入数据时并未制定该列的值，那么MySql会将默认值添加到该列中，如果使用完全插入可以使用default进行占位

向emp3表中添加数据，要求address列与job_id列使用默认值作为该列的值

insert into emp3 values(default, 'alice', default, default);
7.2 更新数据（UPDATE）
update 表名 set 列名=值,列名=值 where 条件

mysql的update的特点

  • 更新的表不能在set和where中用于子查询
  • update后面可以做任意的查询

更新emp3表中的id为1的数据，添加address为北京

update emp3 set address='北京' where emp_id=1;

方式1：更新emp3表的id为2的数据，将地址修改为与id为1的用户的地址相同

update emp3 e, (select address from emp3 where emp_id=1)t 
set e.address = t.address where emp_id=2;

方式2：更新emp3表的id为2的数据，将地址修改为与id为1的用户的地址相同

update emp3 e 
set e.address = 
(select t1.address from(select emp_id,address from emp3)t1 
where t1.emp_id=1)
where e.emp_id = 2;
7.3 删除数据（DELETE）
delete from 表名 where 条件

删除emp3表中emp_id为1的雇员信息

delete from emp3 where emp_id=1;
7.4 使用TRUNCATE清空表
truncate table 表名

删除emp3表中的所有数据

truncate table emp3;

delete与truncate的区别

  • truncate是整体删除（速度快），delete是逐条删除（速度慢）
  • truncate不写服务器log，delete写服务器log，这也就是truncate比delete效率高的原因
  • truncate会重置自增值，相当于自增列会被设置为初始值，又从1开始记录，而不是接着原来的值，而使用delete删除后，自增值仍会累加

8.MySql中的事务处理

在MySql中，默认情况下事务会自动提交，也就是说在执行一条sql语句后就会自动提交事务。

8.1 关闭MySql事务自动提交
start transaction
DML...
commit|rollback

向emp3表中添加一条数据，要求手动提交事务

9.MySql查询数据

9.1 MySql列的选择
select *|投影列 from 表名

查询department表中的所有数据

select * from department;
9.2 MySql的行的选择
select *|投影列 from 表名 where 条件

查询department表中部门id为4的部门名称与工作地点id

select department_name,location_id
from department
where department_id=4;
9.3 select语句中的算数表达式

+：加法运算
-：减法运算
*：乘法运算
/：除法运算，返回商
%：求余运算，返回余数

修改employee表添加salary

alter table employee add column salary double(9,2);

计算员工的年薪

select last_name, salary*12 from employee;

计算employee表中的员工全年薪水加100以后的薪水是多少

select last_name, salary*12+100 from employee;
9.4 MySql中定义空值

包含空值的算数表达式计算结果为空

在employee中添加commission_pct，计算年薪包括佣金

#添加佣金
update employee e set e.commission_pct = 
(select t.commission_pct from 
(select employee_id, commission_pct from employee where employee_id=1)t
where t.employee_id = 1
)where e.employee_id=3;

#计算年薪加佣金
select salary*12+commission_pct
from employee;
9.5 MySql中的列的别名
select 列名 as 列别名 from 表名 where 条件
  • 查询employee表，将雇员last_name列名改为name
select last_name as name
from employee;
9.6 MySql中的连字符

MySql中并不支持||连字符，需要使用concat字符。在参数数量上与oracle的concat函数有区别。

  • 查询雇员表中的所有数据，将所有数据连接到一起，每列值中用#分割
select concat(employee_id, '#', last_name, '#', email,'#', salary,'#',commission_pct)
from employee;
image.png
9.7 MySql中去除重复

在select语句中用distinct关键字去除相同的行

  • 查询employee表，显示唯一的部门id
select distinct dept_id from employee;

10. 约束和排序数据

10.1 MySql中的比较运算符

比较运算符
等于：=
大于：>
大于等于：>=
小于：<
小于等于：<=
不等于：!=或<>

  • 查询employee表，员工薪水大于等于3000的员工的姓名和薪水
select last_name, salary
from employee
where salary>=3000;
  • 查询employee表，员工薪水大于等于5000的员工姓名和薪水
select last_name,salary
from employee
where salary>=5000;
10.2 模糊查询

like
%表示任意多个任意字符
_表示一个任意字符

  • 查询employee中雇员名字第二个字母是o的雇员信息
select * from employee where last_name like '_o%';
10.3 逻辑运算符

and
or
not

  • 查询employee表中雇员薪水是5000的并且名字中包含d的雇员信息
select *
from employee
where salary=5000 and last_name like '%d%';
  • 查询employee表中雇员名字中不包含u的雇员信息
select *
from employee
where last_name not like '%u%';
10.4 范围查询

between...and
in表示在一个非连续的范围内

  • 查询employee表，薪水在3000-8000之间的雇员信息
select * 
from employee
where salary between 3000 and 8000;
  • 查询employee表，找出薪水是5000,6000,8000的雇员
select *
from employee
where salary in(5000,6000,8000);
10.5 控制判断

判断空 is null
判断非空 is not null

  • 找出employee表中那些没有佣金的雇员
select *
from employee
where commission_pct is null;
  • 找出employee表中那些有佣金的雇员
select *
from employee
where commission_pct is not null;
10.6 使用order by排序

用order by子句排序
asc：升序排序
desc：降序排序

  • 查询employee表中的所有雇员，薪水按升序排列
select *
from employee
order by salary asc;
  • 查询employee表中的所有雇员，雇员名字按降序排列
select *
from employee
order by last_name desc;

11.MySql中常见的单行函数

11.1 大小写控制函数

lower(str)：转换大小写混合的字符串为小写字符串
upper(str)：转换大小写混合的字符串为大写字符串

11.2 字符处理

concat(str1, str2...) ：将str1，str2等字符串连接起来
substr(str,pos,len) ：从str的第pos为开始，截取长度为len的字符串
length(str) ：获取str的长度
inser(str,substr)： 获取substr在str中的位置
lpad(str,len,padstr)/rpad(str,len,padstr)
trim(str)：从str中删除开头和结尾的空格(不会处理字符串中间含有的空格)
ltrim(str)：从str中删除左侧的空格
rtrim(str)：从str中删除右侧的空格
replace(str,from_str,to_str)：将str中的from_str替换为to_str（会替换掉所有符合from_str的字符串）

11.3 数字函数

round(arg1,arg2)：四舍五入指定小数的值
round(arg1)：四舍五入保留整数
trunc(arg1,arg2)：截断指定小数的值，不做四舍五入处理
mod(arg1,arg2)：取余

11.4 日期函数

sysdate()或者now()：返回当前系统时间，格式为YYYY-MM-DD hh-mm-ss
curdate()：返回系统当前日期，不返回时间
curtime()：返回系统当前时间，不返回日期
dayofmonth(date)：计算日期d是本月的第几天
dayofweek(date)：计算日期d是星期几，1.星期1，2.星期2....
dayofyear(date)：返回指定年份的天数
dayname(date)：返回date日期是星期几
last_day(date)：返回date日期当月的最后一天

11.5 转换函数

date_format(date, format)：将日期转换成字符串（类似oracle中的to_char()）
str_to_date(str,format)：将字符串转换成日期（类似oracle中的to_date()）


image.png

image.png

image.png
  • 向employee表中添加hire_date类型为date类型
alter table employee add column hire_date date;
  • 向employee表中添加一条数据，名字：King，email：king@abc.com，部门ID:1，薪水：9000，入职日期：2019年11月1日，佣金0.6
insert into employee 
values(default, 'King', 'king@abc.com', 1, 9000, 0.6,STR_TO_DATE('2019年11月01','%Y年%m月%d日'));
  • 查询employee表中雇员名称为King的雇员的入职日期，要求格式以yyyy年MM月dd日
select DATE_FORMAT('2019-11-01','%Y年%m月%d日')
from employee
where last_name='King';
11.6 通用函数

ifnull(expr1,expr2) 判断expr1是否为null，如果为null，则用expr2来替代null，（类似oracle的NVL()函数）
nullif(expr1,expr2) 判断expr1和expr2是否相等，如果相等则返回null，如果不相等则返回expr1
if(expr1,expr2,expr3) 判断expr1是否为真(是否不为null)，如果为真，则使用expr2替换expr1，如果为假则使用expr3替换expr1（类似oracle的NVL2()函数）
coalesce(value...)判断value的值是否为null，如果不为null，则返回value，如果为null，则判断下一个value是否为null...直至出现不为null的value并返回或者返回最后一个为null的value
case when then else end 条件函数

12.多表连接查询

12.1 等值连接
  • 查询雇员King所在的部门名称
select department_name
from employee e, department d
where e.dept_id = d.department_id and e.last_name = 'King';
12.2 非等值连接
  • 创建sal_level表，包含lowest_sal，highest_sal，level
create table sal_level(
    lowest_sal int,
    highest_sal int,
    level varchar(30)
);
  • 插入数据
insert into sal_level values(1000, 2999, 'A');
insert into sal_level values(2000, 4999, 'B');
insert into sal_level values(5000, 7999, 'C');
insert into sal_level values(8000, 12000, 'D');
  • 查询所有雇员的薪水级别
select e.last_name, s.level
from employee e, sal_level s
where e.salary between s.lowest_sal and s.highest_sal;
12.3 自连接
  • 修改employee表，添加manager_id列
alter table employee add column manager_id int(5);
  • 修改数据King是jack和alice的经理,abcd是tom的经理
  • 查询每个雇员经理的名字以及雇员的名字
select emp.last_name, man.last_name
from employee emp, employee man
where emp.manager_id = man.employee_id;

13.外连接（OUTER JOIN）

13.1 左外连接（left outer join）
  • 向employee表中添加一条数据，名字：Lee，email：lee@abc.com，入职日期为今天，他没有薪水，没有经理，没有佣金
insert into employee(last_name, email, hire_date)
values('Lee', 'lee@abc.com', sysdate());
  • 查询所有雇员的名字以及他们的部门名称，包含那些没有部门的雇员
select e.last_name, d.department_name
from employee e left outer join department d
on d.department_id = e.dept_id;
13.2 右外连接
  • 向department表中添加一条数据，部门名称为Testing，工作地点ID为5
insert into department(department_name, location_id)
values('Testing', 5);
  • 查询所有雇员的名字以及他们的部门名称，包含哪些没有雇员的部门
select e.last_name, d.department_name
from employee e right outer join department d
on e.dept_id = d.department_id;
13.3 全外连接

注意：MySql中不支持full outer join连接
可以使用union实现全外连接

union
可以将两个查询结果集合并，返回的行都是唯一的，如同对整个结果集合使用的distinct

union all
只是简单的将两个结果合并后就返回。这样，如果返回的两个结果集中有重复的数据，那么返回的结果集就会包含重复的数据了

语法结构

select 投影列 from 表名 left outer join 表名 on 连接条件 union select 投影列 from 表名 right outer join 表名 on 连接条件
  • 查询所有雇员的名字以及他们的部门名称，包含那些没有雇员的部门以及没有部门的雇员
select e.last_name, d.department_name
from employee e left outer join department d
on e.dept_id = d.department_id union
select e.last_name, d.department_name
from employee e right outer join department d
on e.dept_id = d.department_id;

14.SQL99标准中的查询

MySql5.7 支持SQL99标准

14.1 SQL99中的交叉连接（CROSS JOIN）
  • 使用交叉连接查询employee表与department表
select * from employee cross join department
14.2 SQL99中的自然连接（NATURAL JOIN）
  • 修改employee表中的dept_id列将该列的名称修改为department_id
alter table employee change column dept_id department_id int
  • 使用自然连接查询所有有部门的雇员的名字以及部门名称
select e.last_name, d.department_name
from employee e natural join department d
14.3 SQL99中的内连接（INNER JOIN）
  • 查询固原名字为tom的雇员ID、薪水与部门名称
select e.employee_id, e.salary, d.department_name
from employee e inner join department d
on e.department_id = d.department_id
where e.last_name = 'tom';

15. 聚合函数

15.1 AVG(arg)函数

对分组数据做平均值计算
arg：参数类型只能是数字类型

select AVG(e.salary) from employee e
15.2 SUM(arg)函数

对分组数据求和
arg：参数类型只能是数字类型

select SUM(e.salary) from employee e;
15.3 MIN(arg)函数

求分组中最小数据
arg：参数类型只能是字符、数字、日期

select MIN(e.salary) from employee e;
15.4 MAX(arg)函数

求分组中最大数据
arg：参数类型只能是字符、数字、日期

select MAX(e.salary) from employee e;
15.5 COUNT函数

返回一个表中的行数
count函数有三种格式：

  • count(*)
  • count(expr)
  • count(distinct expr)

16.数据组（group by）

16.1 创建数据组
  • 计算每个部门的平均薪水
select avg(e.salary) from employee e group by e.department_id
16.2 约束分组条件（HAVING）
  • 显示那些最高薪水大于5000的部门的部门号和最高薪水
select e.department_id, max(e.salary)
from employee e
group by e.department_id
having max(e.salary) > 5000;

17. 子查询

可以将子查询放在许多SQL子句中，包括：

  • where 子句
  • having 子句
  • from 子句
17.1 使用子查询的原则

1.子查询放在圆括号里
2.将子查询放在比较条件的右边
3.在单行子查询中用单行运算符，在多行子查询中用多行运算符

  • 谁的薪水比tom高
select salary from employee where salary > 
(select salary from employee where last_name='tom');
  • 查询tom的同事，但不包含他自己
select emp.last_name
from employee emp
where emp.department_id = 
(select e.department_id
from employee e
where e.last_name = 'tom')
and emp.last_name != 'tom';

in：等于列表中的任意成员
any：比较子查询返回的每个值
all：比较子查询返回的全部值

  • 查询各部门收入为部门最低的那些雇员，显示他们的名字、薪水以及部门id
select emp.last_name, emp.salary, emp.department_id
from employee emp where emp.salary in (select min(e.salary)
from employee e
group by e.department_id);

18MySql中的正则表达式

MySql中允许使用正则表达式定义字符串的搜索条件，性能要高于like
MySql中的正则表达式可以对整数类型或者字符串进行检索。
使用REGEXP关键字表示正则表达式
默认忽略大小写，如果区分大小写，使用binary关键字。

18.1正则表达式的模式及其含义
image.png
18.2 “^”符号

^在正则表达式中表示开始
语法：查询以x开头的数据（忽略大小写）

select 列名 from 表名 where 列名 regexp '^x';

查询雇员表中名字是k开头的雇员名字与薪水

select last_name,salary from employee where last_name regexp '^k';
18.3 “$”符号

查询以x结尾的数据（忽略大小写）

select 列名 from 表名 where regexp 'x$'
  • 查询雇员表中名字以n结尾的雇员的名字与薪水
select last_name, salary from employee where last_name regexp 'x$';
18.4 “.”符号

英文的点，它匹配任何一个字符，包括回车、换行等

select 列名 from 表名 where 列名 regexp 'x'
  • 查询雇员表中名字含有'o'的雇员的姓名和薪水
select last_name, salary from employee where last_name regexp 'o';
18.5 “*” 符号

“*”：星号匹配0个或多个字符，在他之前必须有内容。

18.6 “+”符号

“+”：加号匹配1个或多个字符，在它之前必须有内容。

select 列名 from 表名 where 列名 regexp 'x+';

匹配大于1个的任意字符

18.7 “?”符号

“?”：问号匹配0次或1次。

select 列名 from 表名 where regexp 'x?';

匹配0个或1个字符

18.8 “|”符号

“|”：表示或者含义

select 列名 from 表名 where 列名 regexp 'abc|bcd';

匹配包含abc或bcd

  • 查询雇员表中名字含有“ke”或者“lu”的雇员名字与薪水
select last_name, salary from employee where last_name regexp 'ke|lu';
18.9 “[a-z]”

“[a-z]”：字符范围
“^[...]”：以什么字符开头的
“[^...]”：匹配不包含在[]的字符

select 列名 from 表名 where regexp '[a-z]';

匹配内容包含a-z范围的数

  • 查询雇员表中名字包含x、y、z字符的雇员的名字和薪水
select last_name, salary from employee where last_name regexp '[x-z]';
  • 查询雇员名字是t、f开头的雇员名字与薪水
select last_name, salary from employee where last_name regexp '^[t|f]';
  • 查询雇员名字与薪水，不包含tom
select last_name, salary from employee where last_name regexp '[^tom]';
18.10 “{n}”

“{n}”：固定次数（n为整数）

select * from student where name regexp 's{2}';

匹配以s连续出现两次的所有数据

  • 查询雇员名字含有两个e的雇员的姓名与薪水
select last_name, salary from employee where last_name regexp 'e{2}';
  • 查询名字中含有两个o的雇员的名字与薪水
select last_name, salary from employee where last_name regexp 'o.{2}';
18.11 “{n,m}”

“{n,m}”：范围次数

select * from student where name regexp '^s{2,5}';

匹配以s开头且重复2到5次的所有数据

  • 查询雇员名字中包含1个或者两个o的雇员名称和薪水
select last_name, salary from employee where last_name regexp 'o.{1,2}';

19.MySql中的其他对象

1. 索引

MySQL索引的建立对MySql的高效运行是很重要的，索引的产生对数据检索的速度提升非常大

1.1 MySql中的索引类型
  • 普通索引
  • 唯一索引
  • 主键索引
  • 组合索引
  • 全文索引
1.2 普通索引

是最基本的索引，它没有任何限制
在创建索引时，可以指定索引长度。length为可选参数，表示索引的长度，只有字符串类型的字段才能指定索引长度，如果是BLOB和TEXT类型，必须指定length
创建索引时需要注意：
如果指定单索引长度，length必须小于这个字段所允许的最大字符数
查询索引

show index from table_name
1.2.1 直接创建索引
create index index_name on table(column(length));
  • 为emp3表中的name创建一个索引，索引名为emp3_name_index
create index emp3_name_index on emp3(name);
1.2.2 修改表添加索引
alter table table_name add index index_name (column(length));
  • 修改emp3表，为address列添加索引，索引名为emp3_address_index
alter table emp3 add index emp3_address_index (address);
1.2.3 创建表时指定索引列
create table 'table'(
  column type,
  primary key ('id'),
  index index_name (column(length))
);
  • 创建emp4表，包含emp_id,name,address列，同时为name列创建索引，索引名为emp4_name_index。
create table emp4(
    emp_id int primary key auto_increment,
    name varchar(30),
    address varchar(50),
    index emp4_name_index (name)
);
1.2.4 删除索引
drop index index_name on table
  • 删除emp3表中的索引名为emp3_address_index的索引
drop index emp3_address_index on emp3;
1.3 唯一索引

唯一索引与普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。它有以下几种创建方式：

1.3.1 创建唯一索引
create unique index indexName on table(column(length));
  • 为emp表中的name创建唯一索引，索引名为emp_name_index
create unique index emp_name_index on emp(name);
1.3.2 修改表添加唯一索引
alter table table_name add unique indexName (columb(length));
  • 修改emp表，为salary列添加唯一索引，索引名为emp_address_index
alter table emp add unique emp_address_index (salary);
1.3.3 创建表时指定唯一索引
create table 'table'(
  column type,
  primary key ('id'),
  unique index_name (column(length))
);
  • 创建emp5表，包含emp_id,name,address列，同时为name列创建唯一索引。索引名为emp5_name_index
create table emp5(
    emp_id int primary key auto_increment,
    name varchar(30),
    address varchar(50),
    unique emp5_name_index (name)
);
1.4 主键索引

主键索引是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。一般是在建表的同时创建主键索引。

1.4.1 修改表添加主键索引
alter table 表名 add primary key(列名);
  • 修改emp表为emp_id添加主键索引
alter table emp add primary key (emp_id);
1.4.2 创建表时指定主键索引
create table 'table'(
  column type,
  primary key (column)
);
  • 创建emp6表，包含emp_id,name,address列，同时为emp_id创建主键索引
create table emp6(
    emp_id int primary key auto_increment,
    name varchar(30),
    address varchar(50)
);
1.5 组合索引

组合索引是指使用多个字段创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用（最左前缀原则）

1.5.1 最左前缀原则

就是最左有优先
如：我们使用表中的name，address，salary创建组合索引，那么想要组合索引生效，我们只能使用如下组合：
name/address/salary
name/addess
name
如果只使用address/salary或者salary索引不会生效

1.5.2 修改添加组合索引
alter table table_name add index index_name (column(length),column(length));
  • 修改emp6表，为name，address创建组合索引
alter table emp6 add index emp6_index_n_a(name, address);
1.5.3 创建表时创建组合索引
create table 'table'(
  column type,
  index index_name (column(length), column(length))
);
  • 创建emp7表，包含emp_id,name,address列，同时为name，address添加组合索引
create table emp7(
    emp_id int primary key auto_increment,
    name varchar(30),
    address varchar(50),
    index emp7_index_n_a (name, address)
);
1.6 全文索引

全文索引（fulltext index）主要用来查找文本中的关键字，而不是直接与索引中的值相比较。fulltext index跟其他索引大不相同，它更像是一个搜索引擎，而不是简单的where语句参数匹配，fulltext index索引配合match against操作使用，而不是一般的where语句加like

全文索引可以从char、varchar或text列中作为create table 语句的一部分被创建，或是使用alter table添加。不过切记对大容量的数据表生成全文索引，生成全文索引非常耗费时间和硬盘空间。

1.6.1 修改添加全文索引
alter table table_name add fulltext index_content(content);
  • 修改emp7表添加content列类型作为text
alter table emp7 add column content text;
  • 修改emp7表，为content列创建全文索引
alter table emp7 add fulltext emp7_content_fullindex(content);
1.6.2 创建表时创建全文索引
create table 'table'(
  column type,
  fulltext index_name (column)
);
  • 创建emp8表包含emp_id列，content列该列类型为text，并为该列添加名为emp8_content_fulltext的全文索引
create table emp8(
    emp_id int primary key auto_increment,
    content text,
    fulltext emp8_content_fulltext(content)
);
1.6.3 删除全文索引
drop index index_name on table
  • 删除emp8表中名为emp8_content_full的索引
drop index emp8_content_fulltext on emp8;
1.7 使用全文索引

全文索引的使用与其他索引不同。在查询语句中需要使用match(column) against('content')来检索数据

1.7.1 全文解析器

全文索引中基本单位是“词”，分词，全文索引是以词为基础的，MySql默认的分词是所有非字母和数字的特殊符号都是分词符，在检查数据时我们给定的检索条件也是词。
MySql中默认的全文解析器不支持中文分次。如果数据含有中文需要跟换全文解析器ngram

1.7.2 使用全文索引
select 投影列 from 表名 where match(全文索引列名) against('搜索内容');
  • 修改emp8表，为content列创建名为emp8_content_full的全文索引
alter table emp8 add fulltext emp8_content_full(content);
  • 向emp8表中插入一条数据，content的值默认为'hello world'
insert into emp8 values(default, 'hello world');
  • 查询emp8表中包含'hello'的数据
select * from emp8 where match(content) against ('hello');
1.7.3 更换全文解析器

在创建全文索引时可以指定ngram解析器

alter table table_name add fulltext index_content(content) with parser ngram
  • 删除emp8表中的emp8_content_full全文索引
drop index emp8_content_full on emp8;
  • 修改emp8表，为content列添加名称为emp8_content_full的全文索引，并指定ngram全文解析器
alter table emp8 add fulltext emp8_content_full(content) with parser ngram;
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 212,332评论 6赞 493
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 90,508评论 3赞 385
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 157,812评论 0赞 348
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 56,607评论 1赞 284
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 65,728评论 6赞 386
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 49,919评论 1赞 290
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 39,071评论 3赞 410
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 37,802评论 0赞 268
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 44,256评论 1赞 303
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 36,576评论 2赞 327
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 38,712评论 1赞 341
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 34,389评论 4赞 332
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 40,032评论 3赞 316
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 30,798评论 0赞 21
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 32,026评论 1赞 266
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 46,473评论 2赞 360
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 43,606评论 2赞 350

推荐阅读更多精彩内容