Mysql基础语法

MySQL数据库管理

查看数据库

show databases

创建数据库

如果没有修改my.ini配置文件的默认字符集，在创建数据库时，指定字符集

create database 数据库名 character set 'utf8';

特殊字符（关键字）用反引号

例如：create database `create`;

显示数据库创建信息

show database 数据库名

删除数据库

drop database 数据库名

进入(使用)数据库

use 数据库名;

显示当前打开的数据库

select database();

表结构管理

创建数据表

create table (if not exists)表名(字段 字段类型...)(engine=数据引擎 default charset=编码格式)  ;   //红色可选(括号不用加上)，表示如果表不存在则创建表，避免表存在时，再创建时报错。

mysql\data目录下的数据库目录中将生成一个对应名称的.fm文件

删除数据表

drop table (if exists) 表名；//红色可选(括号不用加上)，表示如果表存在则删除表，避免表不存在，删除时报错。

修改数据表结构

给数据表增加一个字段：alter table 表名 add (column) 新增属性 属性类型;

修改表名称：alter table 表名 rename 新表名;

修改表的存储引擎：alter table 表名 engine= MyISAM;

删除字段：alter table 表名 drop 字段名称;

修改字段名称及重新定义字段类型：alter table 表名 change 字段名 新字段名 新字段类型;

单独修改字段的类型：alter table 表名 modify 字段名 新的字段类型;

指定位置后面添加字段：alter table 表名 add (column) 新增字段 字段类型 after 指定的字段;

在第一个位置添加字段：alter table 表名 add (column) 新增字段 字段类型 first;

查看数据表

show tables; 查看当前数据库所有的数据表

查看字母‘abc'开头的表

show table like 'abc%';

%是通配符

查看表创建信息

show create table 表名；(查看表创建的语句)

查看数据表结构

desc 表名；

MySQL用户管理

登录

MySQL是基于C/S架构，必须在客户端通过终端窗口，连接MySQL服务器，进行操作。

mysql -h host - u usr -p   / mysql -u root -p密码

输入密码：xxxx

用户管理

超级用户root

修改账号密码：

例：DOS命令下修改，将root账号密码修改为1234

mysqladmin -u root password 1234 语句最后不要加分号，否则密码就是1234；

例：mysql命令

set password for 'root'@'localhost' =password('1234');

创建用户

使用create语句进行创建用户，语句格式如下：

create user 'username'@’host' identified by 'password';

其中，username表示要创建的用户名，host表示指定该用户在哪个主机上可以登录，如果是本地用户可用localhost，如果想让该用户可以从任意远程主机登录，可以使用通配符%；password表示该用户的登录密码，密码可以为空，如果为空则该用户可以不需要密码登录服务器。

例：create user 'zhangsan'@'localhost' identified by'123456';

删除用户

删除用户使用drop语句，语句格式如下：

drop user 'username'@'host';

修改配置文件my.ini

字符集

MySQL默认字符集是Latin，改变为utf8才能正确显示中文

[mysql]下添加

prompt="mysql(\d)>"

默认数据库

information_schema

提供了访问数据库元数据的方式。什么是元数据呢？元数据是关于数据的数据，如数据库名或表名，列的数据类型，或访问权限等。有些时候用于表述该信息的其他术语包括”数据词典“和”系统目录“。

performance_schema

mysql5.5版本新增了一个性能优化的引擎。

mysql

这个是MySQL的核心数据库，主要负责存储数据库的用户，权限设置，关键字符MySQL自己需要使用的控制和管理信息。不可以删除，也不要轻易修改这个数据库里面的信息。

test

安装时候创建的一个测试用数据库，空数据库，没有任何表，可以删除(新版本MySQL已取消)。

SQL基本语法

 基本规范

SQL对大小写不敏感，一般数据库名称，表名称，字段名称全部小写

MySQL要求在每条SQL命令的末端使用分号。

注释

#这个注释直到该行结束

-这个注释直到该行结束(在dos好像没效果了)

/*这是一个在行中间的注释*/

/*

这个是一个

多行注释格式

*/

MySQL基本数据类型

字段类型

数据类型是指列，存储过程，表达式和局部变量的数据特征，它决定了数据的存储方式，代表了不同的信息类型，不同的数据库，数据类型有所不同，MySQL数据库有以下几种数据类型：

字符串型

 注意：char和varchar需要指定长度，例如：char(10)

整数型

 很多人喜欢定义数据时，这样写：

create table tbl_name(
 age int(10)
);

int 后面（）中的数字，不代表占用空间容量，而代表最小显示位数。这个东西基本没有意义，除非你对字段指定zerofill，MySQL会自动分配长度；int(11),tinyint(4),smallint(6),mediumint(9),big(20)。所以，建议在使用时，就用这些默认的显示长度就可以了，不用再去自己填长度。（比如：int(10), tinyint(1)之类的基本没用).

 浮点型

 M(精度)，代表总长度(整数位和小数位)限制

D（标度），代表小数位的长度限制。

M必须大于等于D

 日期型

 列举与枚举

create table students(
 id tinyint, #微小整型
 name varchar(10), #变长字符
 age int, #整型
 sex enum('m',w'), #单选
 birthday date, #日期型
 tel char(11), #定长字符
 city char(1), #城市
 hobby set('1','2','3','4'), #多选
 introduce   text #个人介绍
);

字段属性

 数据的增、删、改

增删改查（简称：CURD）

增

#方法1：指定字段
insert into students(name,age) values('张三',20);

#方法2：省略字段名，字段位置一一对应，不能跳过(auto_increment 字段，可以使用null 或 default )
insert into students values(1,'张三','m',null,'110','2','3','haha',23);

#方法3:批量增加数据
insert into students(name,age) values('张三',20),('李四“,21),('王五',32),......

删

#用delete删除积累，一定要加where条件，否则表数据全部删除！！

delete from 表名 where xxx=xxx;

#用truncate删除记录，不能加where条件，直接删除全部记录，id索引重新从1开始

truncate table 表名； //注意truncate 一旦执行，数据不可恢复。

改

#单条修改

update 表名 set xx=xx,xxx=xx where xxx=xx and xxx=xxx;

#多条修改
update students
    set name = case id #id字段
        when 1 then 'zhangsan'
        when 2 then 'lisi'
        when 3 then 'wangwu'
        when 4 then 'zhaoliu'
    end,
    city = case id
        when 1 then '2'
        when 2 then '4'
        when 3 then '1'
        when 4 then '2'
    end
where id in (1,2,3,4);

数据的查

查询表达式

#当前使用的数据库
select database();

#查看当前MySQL版本
select version();

#查看当前用户
select user();

#查看运算结果
select 1+2;

条件表达式

from 子句

#字段用逗号隔开，至少有一个字段，最终结果集按照这个顺序显示
select 字段1,字段2.. from 表名;

#  *代表所有字段
select * from 表名;

distinct（去重）

#去重后的结果，distinct 必须紧挨着select 后面
select distinct 字段 from 表名;

#统计不重复的个数
select count(distinct 字段) from 表名;

where 子句

where子句适用于对记录的删，改，查等操作

对记录进行过滤，如果没有指定where子句，则显示所有记录

在where表达式中，可以使用函数或运算符，运算符包括：

#搜索id<20的所有数据
select * from students where id < 20;

#搜索id编号为偶数的数据
select * from students where id % 2 = 0;

where条件关键字

in: 查询一个集合的数据

#搜索id 在（1，3，7）之中的数据
select * from students where id = 1 || id =3 || id =7;
select * from students where id in (1,3,7);

#一次删除多条记录
delete from students where id = 3 || id =15 || id =23;
delete from students where id in (3,15,23);

between..and..：查询一个区间的数据

#搜索id在20-40之间的数据
select * from students where id > 20 && id < 40;
select * from students where id between 20 and 40;

#删除id在20-40之间的数据
delete from students where id between 20 and 40;

not ：排除

#搜索id除了20-40之间的数据
select * from students where id not between 20 and 40;

like子句

用于模糊查询   %：任意字符长度 

_  ： 一个字符长度

#搜索name名字以5结尾的数据
select * from students where name like '%5’；

#搜索name名字包含字母s的数据
select * from students like '%s%';

#搜索id以5结尾的两位数 数据
select * from students where id like '_5';

limit子句

控制查询记录条数，数据表中的记录，索引从0开始

select * from students limit 2 #返回两条记录
select * from students limit 3,4 #从索引为3的记录开始，返回4条记录

#php中的分页功能，偏移值的计算：(当前页-1) * 每页记录数

select name from students limit 3 offset 4;
#还可以使用offset(偏移)：从索引为3的记录开始，返回4条

group by(结果分组)

根据给定数据列的每个成员对查询结果进行分组统计，最终得到一个分组汇总表利用group by分组信息进行统计，常见的是配合max等聚合函数筛选数据后分析。

select指定的字段要么作为分组的依据（Group By语句的后面），要么就要被包含在聚合函数中。

#简单分组，根据性别分成两组
select sex from students group by sex;

#聚合函数分组，根据cityf分组，计算每个城市的学生有几个
select count(*),city from students group by city;

order by（结果排序）

按照给定的字段进行排序，asc:升序(默认)，desc：降序

如果同时选择多个字段，先按第一个字段排序，如果第一个字段值相等，再尝试第二个字段，以此类推

#默认升序
select * from students order by birthday;

#降序
select * from students order by birthday desc;

查询语句的书写顺序

select -—> 字段-—> from -—> 表名 -—> where -—> group by -—> order by -—> limit

别名

 

给表起别名，使用as关键字，但是可省略as.

比如 select a.name Aname from students a; 这里给a.name起了别名Aname,也给students起了别名a.

 

 多表查询

score表和course表的创建语句，数据就自己插入吧。

#score表创建
create table score(
    user_id int,
    course_id int,
    score int
);


#course表创建
create table course(
    id int,
    name varchar(4)
);

分别查询张三同学对应科目的成绩。

这里先查询张三同学的语文成绩：

a 表 人名
select a.name from student as a where a.id=6;

b表 分数
select b.score from score as b where b.user_id=6 and b.course_id=4;

c表 科目
select c.name from course as c where c.id=4;

上面3条语句  合并语句 并添加 字段别名

select a.name,c.name as course,b.score from 
students as a, score as b, course as c
where a.id=6 and b.user_id=6 and b.course_id=4 and c.id=4;

最终结果

#上面的语句中的where 语句还可以修改
select a.name,c.name,b.score from students a, score b ,course c where b.user_id=a.id and b.course_id=c.id;

表连接

 

 

 

 

 内连接

join : 如果表中有至少一个匹配，则返回行

select 需要查询的信息 from 表1 表1别名 join 表2 表2别名 on 表1和表2的连接条件

select sc.id,s.name from score sc join students s on sc.user_id = s.id;

内连接和上面的=连接区别在于，例子：

=连接：select sc.id,s.name from score sc,students s where sc.user_id=s.id;

对比上面的内连接明显发现，=连接是多个表在一起，用逗号隔开，内连接是使用join分开每个表，然后使用on代替where使用，而且添加on之后，还可以加where，但是=连接使用了where，后面就不能再出现where了。

外连接

左外连接 left join：即使右表中没有匹配，也从左表返回所有的行.(左表指的是left join 语句左边的表)

select 需要查询的信息 from 表1 表1别名 left join 表2 表2别名 on 表1和表2的连接条件

　　select sc.user_id,s.name from score sc left join students s on sc.user_id=s.id #这里的语句意思是：无论students的信息是否匹配score表，都会返回score表中所有数据

右外连接 right join ：即使左表中没有匹配，也从右表返回所有的行. (右表指的是right join 语句右边的表)

select 需要查询的信息 from 表1 表1别名 right join 表2 表2别名 on 表1和表2的连接条件

　　select sc.user_id,s.name from students s right join score sc on sc.user_id=s.id;   #这和上面的左连接已经换了students表和score表的位置了，这里的语句意思是：无论students的信息是否匹配score表，都会返回score表中所有数据

全连接（比较少使用）

 full join : 只要其中一个表中存在匹配，就返回行（MySQL不支持），但是可以通过另类的方式实现全连接。

mysql不支持全连接，但可以通过左外连接+ union+右外连接实现。

额外：自连接

自连接不属于一类，应该是属性内连接的一种，自连接是把一张表当成两张表使用。

应用场景：找出当前表中高于平均值的所有数据，就要先把当前表的平均值计算出来，把结果当作一张表，然后再对这张结果表比较原来的表，就可以找出高于平均值的数据了。

子查询

子查询是指出现在其他SQL语句内的select子句（嵌套在查询内部，且必须始终出现在圆括号内）

#城市表创建语句
create table city(id int , name varchar(10));

#查询城市名称是北京的
#普通方式查询
select * from students where city=2;#2是北京

#子查询方式
select * from students where city=(select id from city where name = '北京');

注意：on关键字和where关键字作用不相同，on只是作用于连接，where是条件过滤，如果使用on作为条件过滤会出现“笛卡儿积”。笛卡儿积：两表数据记录条数相乘，比如a表有3条记录，b表有2条记录，那么笛卡儿积就是6条记录。

子查询可以包含多个关键字或条件，如：distinct，group by ，order by ，limit，函数等

子查询的外层可以是：select，insert，update

视图与事务

视图

1. 视图是从一个或几个基本表(或视图）中导出的虚拟的表。在系统的数据字典中仅存放了视图的定义，不存放视图对应的数据。视图是原始数据库数据的一种变换，是查看表中数据的另外一种方式。可以将视图看成是一个移动的窗口，通过它可以看到感兴趣的数据。视图时从一个或多个实际表中获得的，这些表的数据存放在数据库中。那些用于产生视图的表叫做该视图的基表。一个是视图也可以从另一个视图中产生。

2. 数据库中视图是一个重要的概念，其优势在于：

　　安全：有的数据是需要保密的，如果直接把表给出来进行操作会造成泄密，那么可以通过创建视图把相应视图的权限给出来即可保证数据的安全，比如创建视图是给原表的字段起别名。

　　高效：复杂的连接查询，每次执行时效率比较低，建立视图，每次从视图中获取，将会提高效率。

　　定制数据：将常用的字段放置在视图中。

3. 对视图进行增删改查，会影响到原表数据。（通过视图影响原表数据的，不是直接操作的原表）

接下来用例子演示操作视图会对原表数据产生影响，如下图：

 

 

 从上图可以发现对视图进行修改操作，结果原表的数据也发生了改变，所以对视图进行增删改查，会影响到原表数据。

注意：在以下几种条件下不能进行对视图的插入/更新/删除操作。

 

• 视图的列中含有统计函数的情况下；

• 视图定义时使用了GROUP BY/HAVING语句，DISTINCT语句、UNION语句的情况下；

• 视图定义时使用了子查询的情况下；

• 进行跨越多个表进行数据的变更操作（多表连接）；

 

创建视图

create view 视图名 as select 字段名 from 表名 where 条件;

修改视图

#alter语句：
alter view 视图名 as select 字段名 from 表名 where 条件;

删除视图

drop view 视图名;

查询视图

show tables;

show tables status;

这两个命令不仅可以显示表名及表信息，而且会显示出所有视图名称及视图信息。

除此之外，使用show create view 命令可以查看某个视图的定义，格式如下：

show create view 视图名;

关系数据库表时用于存储和组织信息的数据结构，数据结构的不同，直接影响操作数据的效率和功能，对于MySQL来说，它提供了很多类型的存储引擎，可以根据对数据处理的需求，选择不同的存储引擎，从而最大限定的利用MySQL强大的功能。

 

事务

3.1、一个事务是一个完整的业务逻辑单元，不可再分。

比如银行账号转账，从A账户向B账户转账10000 ， 需要执行两条update语句。

　　update t_act set balance = balance - 10000 where actno = 'act-001';

　　update t_act set balance = balance + 10000 where actno= 'act-002';

和事务相关的语句只有：DML语句。（insert delete update）

为什么？因为它们这三个语句都是和数据库表中的“数据” 相关的。

事务的存在是为了保证数据的完整性，安全性。

 

3.2、事务的原理？

 

事务(TCL) :只有两条语句，提交事务（commit） ；回滚事务（rollback）。

提交事务就是把操作记录到硬盘中，回滚事务就是清空历史操作，不保存操作记录到硬盘中。

 

3.3、事务的特性？

事务包括四大特性：ACID

A : 原子性：事务是最小的工作单元，不可再分。

C : 一致性：事务必须保证多条DML语句同时成功或者同时失败。

I : 隔离性：事务A和事务B之间具有隔离。

D : 持久性：持久性说的是最终数据必须持久化到硬盘文件中，事务才算成功的结束。

 

3.4、关于事务之间的隔离性

　　事务隔离性存在隔离级别，理论上隔离级别包括4个：

　　　　第一级别：读未提交（read uncommitted）

　　　　　　对方事务还没有提交，我们当前事务可以读取到对方未提交的数据。

　　　　　　读未提交存在脏读（Dirty Read）现象：表示读到了脏的数据。

　　　　第二级别：读已提交（read committed)

　　　　　　对方事务提交之后的数据我方可以读取到。

　　　　　　这种隔离级别解决了：脏读现象没有了。

　　　　　　读已提交存在的问题是：不可重复读。（共享数据被对方更改，不能读取与原来相同的数据）

　　　　第三级别：可重复读（repeatable read)

　　　　　　这种隔离级别解决了：不可重复读问题。

　　　　　　这种隔离级别存在的问题是：读取到的数据是幻象。

　　　　第四级别：序列化读/串行化读

　　　　　　解决了所有问题。

　　　　　　效率低。需要事务排队。

　　　　Oracle数据库默认的隔离级别是：读已提交。

　　　　MySQL数据库默认的隔离级别是：可重复读。

 

 3.5、使用两个事务演示以上的隔离级别

 　　第一：演示 读未提交（read uncommitted）：

　　　　设置事务的隔离级别：set global transaction isolation level read uncommitted;

　　　　通过 select @@global.tx_isolation;  这个是msyql5版本的命令  /  select @@global.transaction_isolation;  这个是mysql8版本的命令，查看全局的事务隔离级别。

 

 可以看到事务隔离级别已经设置为 read uncommitted，注意设置好隔离级别后，要重新打开cmd命令提示符，不然不生效。

 

 

　　第二：演示 读已提交（read committed）：

　　　　设置事务的隔离级别：set global transaction isolation level read committed;

　　　　然后退出MySQL，重新登录。

 

 可以发现 读已提交（read committed）是共享数据被对方更改，不能读取与原来相同的数据。

比如：你和同事共享一个数据，你已经做好逻辑处理，但是同事修改了一下你逻辑中的数据，导致结果和你预想的不一致。

 

　　第三：演示 可重复读（repeatable read）：

　　　　设置事务的隔离级别：set global transaction isolation level repeatable read;

　　　　然后退出MySQL，重新登录。

　　　　

 

 可重复读（repeatable read）也称幻读，读取的数据是备份数据，当命令窗1结束当前事务时，命令窗2的事务才能影响到命令窗1的数据，如下图：

　　

 

 

　　第四：演示串行化读

　　　　设置事务的隔离级别：set global transaction isolation level serializable;

　　　　然后退出MySQL，重新登录。

 

 然后命令窗1提交事务，命令窗2的查询语句才能出结果，否则一直等待下去，如下图：

 

 如果命令窗2在命令窗1没有提交事务前执行查询语句，会处于等待状态，等待有时间限制，超过等待时间，会报错，如上图命令窗2 的timeout exceeded.

 

 

3.6、演示事务

　　* MySQL事务默认情况下是自动提交的。

　　　　（什么是自动提交？只要执行任意一条DML语句则提交一次。）怎么关闭自动提交？start transaction；

　　* 准备表：

　　　　drop table if exists t_user;

　　　　create table t_user(

　　　　　　id int primary key auto_increment,

　　　　　　username varchar(255)

　　　　);

　　* 演示：MySQL中的事务是支持自动提交的，只要执行一条DML,则提交一次。

 

 可以发现上图rollback没有用，因为mysql默认事务是自动提交的，也就是自动（commit).

 

　　*演示：使用 start transaction; 关闭自动提交机制。

 

 -------------------------------------------------------------------------------------------------------------------------------------------------

　　　　

 

 

 

 

MyISAM引擎

MyISAM表是独立于操作系统的，这说明可以轻松地将其从Windows服务器移植到Linux服务器，建立一个MyISAM引擎的tb_Demo表，就会生成以下三个文件：

 - mytable.frm存储表结构的定义

 - mytable.MYD存储表行内容，即数据

 - mytable.MYI存储索引。

MyISAM无法处理事务，特别适合以下几种情况使用：

1. 选择密集型的表。MyISAM存储引擎在筛选大量数据时非常迅速，这是它最突出的优点。

2. 插入密集型的表。MyISAM的并发插入特性允许同时选择和插入数据。例如：MyISAM存储引擎很适合管理邮件或Web服务器日志数据。

 

InnoDB引擎

InnoDB是一个健壮的事务型存储引擎，InnoDB还引入了外键约束，在以下场合下，

使用InnoDB是最理想的选择：

1. 更新密集的表。InnDB存储引擎特别适合处理多重并发的更新请求。

2. 事务。InnoDB存储引擎是支持事务的标准MySQL存储引擎。

3. 外键约束。MySQL支持外键的存储引擎只有InnoDB。

4. 自动灾难恢复。与其他存储引擎不同，InnoDB表能够自动从灾难中恢复。

事务处理

以银行转账业务为例，张三——》李四转账100元，这是一个完整事务，需要两步操作：

1. 张三数据表减去100元

2. 李四数据表增加100元

如果1步完成后，操作出现错误（断电，操作异常等），使2步没有完成，此时，张三减去了100元，而李四却没有收到100元。

为了避免这种情况的发生，就将整个操作定义为一个事务，任何操作步骤出现错误，都会回滚到上一次断点位置，避免出现其他错误。

#开始
begin:
    update tb1_a set money=money-100 where name='zhangsan';
    update tb1_bmoney=money+100 where name='lisi';

#提交
commit;

#回滚
rollback;

索引约束分区

索引

 

1. 什么是索引？有什么用？

　　索引就相当于一本书的目录，通过目录可以快速的找到对应的资源。

　　在数据库方面，查询一张表的时候有两种检索方式：

　　　　第一种方式：全表扫描

　　　　第二种方式：根据索引检索（效率很高）

　　索引为什么可以提高检索效率呢？

　　　　其实最根本的原理是缩小了扫描的范围。

　　索引虽然可以提高检索效率，但是不能随意的添加索引，因为索引也是数据库当中的对象，也需要数据库不断的维护。是有维护成本的。

比如，表中的数据经常被修改，这样就不适合添加索引，因为数据一旦修改，索引需要重新排序，进行维护。

　　添加索引是给某一个字段，或者某些字段添加索引。

　　　　select ename, sal from emp where ename = 'SMITH';

　　　　当ename字段上没有添加索引的时候，以上sql语句会进行全表扫描，扫描ename字段中所有的值。

　　　　当ename字段上添加索引的时候，以上sql语句会根据索引扫描，快速定位。

 

2. 怎么创建索引对象？怎么删除索引对象？

　　创建索引语法格式： create index 索引名 on 表名 (字段名);

　　删除索引语法格式：drop index 索引名称 on 表名;

 

3. 什么时候考虑给字段添加索引？（满足什么条件）

　　* 数据量庞大。（根据客户的需求，根据线上的环境）

　　* 该字段很少的DML操作。（因为字段进行修改操作，索引也需要维护）

　　* 该字段经常出现在where子句中。（经常根据哪个字段查询）

 

4. 注意：主键和具有unique约束的字段自动会添加索引。

 

5. 查看sql语句的执行计划：

　　explain select ename,sal from emp where sal = 5000;

 

上图的type就是扫描表的方式，ALL代表全表扫描，rows代表扫描行数，14行是这个表的总记录数。

　　给薪资sal字段添加索引：

　　　　create index emp_sal_index on emp (sal);  

 

 从上图可以发现创建索引后，查询只扫描了1行。

 

6. 索引底层采用的数据结构是：B + Tree

7. 索引的实现原理？

　　通过B Tree 缩小扫描范围，底层索引进行了排序，分区，索引会携带数据在表中的“物理地址”，

最终通过索引检索到数据之后，获取到关联的物理地址，通过物理地址定位表中的数据，效率是最高的。

　　例如：select ename from emp where ename = 'SMITH';

　　通过索引转化为：

　　　　select ename from emp where 物理地址 = 0x3;

下面图解索引实现原理：

 

 8. 索引的分类？

　　单一索引：给单个字段添加索引

　　复合索引：给多个字段联合起来添加1个索引

　　主键索引：主键上会自动添加索引

　　唯一索引：有unique约束的字段上会自动添加索引

　　.......

 

9. 索引什么时候失效？

　　select ename from emp where ename like '%A%';

　　模糊查询的时候，第一通配符使用的是%，这个时候索引是失效的。

 

索引是帮助MySQL高效获取数据的数据结构

数据库在保存数据之外，还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。索引可以大大提高MySQL的检索速度。

在MySQL中，对于一个Primary Key的列，MySQL已经自动对其建立了Unique和Index.

#创建索引
create table 表名(
    id int not null,
    username varchar(16) not null,
    index(username(length))  ###用username字段作为索引
):

#显示索引
show index from 表名;

#删除索引
alter table 表名 drop index name;

约束

约束保证数据的完整性和一致性，根据约束的字段数目的多少，约束又分为表级约束和列级约束

　　列级约束：针对某一字段来使用

　　表级约束：针对两个或两个以上的字段使用

约束类型包括：

　　not null （非空约束）

　　primary key （主键约束）

　　unique key （唯一约束）

　　default（默认约束）

　　foreign key （外键约束）

唯一(unique)约束

unique 约束唯一标识数据库表中的每条记录。

unique 和 primary key 约束均为列提供了唯一性的保证。

primary key 被自动定义为unique 约束。

注意：每个表可以有多个unique 约束，但是每个表只能有一个primary key 约束。

#第一种方式
create table persons(
    id int not null,
    address varchar(255),
    city  varchar(155),
    phone varchar(11) unique #定义字段的同时，定义约束
);


#第二种方式
create table persons(
    id int not null,
    address varchar(155),
    phone varchar(11),
    unique (phone)  #单数一行命令，定义约束
);

#第三种方式
alter table persons add unique (city); #修改表

默认（default）约束

用于约束对应列中的值的默认值（除非默认为空值，否则不可插入空值）

create table persons(
    id tinyint primary key auto_increment,
    name varchar(30),
    sex enum('m','w') default 'm'  #定义sex默认值为：'m'
);

主键（primary key) 约束

每张数据表只能存在一个主键，主键保证记录的唯一性，主键自动为not null （同时作为表的索引）。

 #为没有主键的表添加主键
alter table 表名 add primary key (字段名);

#在创建表的时候，定义主键
create table persons(
    id int not null primary key,
    name varchar(10)
);

#在创建表的最后，定义主键，通常是定义联合主键
create table persons(
    id int not null,
    address varchar(10),
    primary key(id,address)
);

外键（foreign key）约束

外键约束是为了保存数据一致性，完整性，实现一对一或一对多关系

　　子表（具有外键列的表）和 父表（子表所参照的表），存储引擎只能为InnoDB。

　　外键列和参照列必须具有相似的数据类型。

　　　　- 如果是数字类型，数字的长度、是否有符合位 必须相同

　　　　- 字符类型的长度则可以不同

　　外键列和参照列必须创建索引（如果外键列不存在索引的话，MySQL将自动创建索引）。

外键的好处：可以使得两张表关联，保证数据的一致性和实现一些级联操作。保持数据一致性，完整性，主要目的是控制存储在外键表中的数据。 使两张表形成关联，外键只能引用外表中的列的值！可以使得两张表关联，保证数据的一致性和实现一些级联操作；

#先建父表 子表才能建外键  父表和子表必须都是 innodb引擎

#city父表
create table city(
    id tinyint primary key,
    name varchar(10) not null
)engine=INNODB;

#students子表
create table students(
    id tinyint primary key auto_increment, #id
    #定义字段时同时定义
    city tinyint, #外键字段类型要于主表相同
    foreign key(city) references city(id) #city字段作为外键，引用city表中的id
)engine=INNODB;


#主表的数据可以修改，但不能删除
#删除city中的记录
delete from city where id=1;

#创建外键以后，再删除city记录，就会报错：

 因为students的外键引用city的记录，如果直接删除city的记录，students外键列找不到依赖的列就会报错，所以MySQL规定不能删除外键所依赖的数据，子表的外键列也不能插入被引用的父表中不存在的数据。

总结：设置外键的列，就全依赖于父表，外键列的数据全部依赖父表的数据，外键列插入的数据必须是父表被引用的列中存在的，父表的数据可以修改，但不能删除。

补充：通常设置外键会自动生成外键名，不够最好是自己设置外键名。

#给外键设置名字
create table students(
    id tinyint primary key auto_increment, #id
    #定义字段时同时定义
    city tinyint, #外键字段类型要于主表相同
    constraint 外键名 foreign key(city) references city(id)
)engine=INNODB;

删除约束

　　删除primary key

alter table 表名 drop primary key;

　　删除index

alter table 表名 drop index 索引名;

　　删除外键约束

alter table drop foreign key 外键名;

索引于约束的关系

索引是面向数据库本身的，用于查询优化等操作。约束则更多的是业务上的关系。

通常，创建唯一约束就自动获取唯一索引，是因为数据库认为数据库进行唯一检查时，如果该字段上有索引会很快，所以创建唯一约束就默认创建唯一索引。同样，常见的主键即是唯一性的约束，也是个索引。但对于not null 这样的约束，数据库是不会创建索引的。

分区

如果一张表的数据量太大，不仅查找数据的效率低下，而且难以找到一块集中的存储来存放。为了解决这个问题，数据库退出了分区的功能。MySQL表分区主要有以下四种类型：

　　RANGE分区：

RANGE即范围分区，根据区间来判断于哪个分区。这些区间要连续且不能相互重叠，使用VALUES LESS THAN 操作符来进行定义。

create table test(
    id int default null,
    name char(30),
    datedata date
)
partition by range(year(datedata))(
partition part1 values less than(1990),
partition part2 values less than(1995),
partition part3 values less than(2000),
partition part4 values less than MAXVALUE);

　　LIST分区

LIST分区类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。

create table test1(
    id int not null,
    name char(30),
    career varchar(30)
)
partition by LIST(id)(
partition part0 values in (1,5),
partition part1 values in (11,15),
partition part2 values in (6,10),
partition part3 values in (16,20)
);

HASH分区

HASH分区基于用户定义的表达式返回值来选择分区，该表达式对要插入到表的行中列值进行Hash计算。

create table employess(
    id int not null,
    firstname varchar(30),
    lastname varchar(30),
    hired date not null default '1970-01-01',
    separated date not null default '9999-12-31',
    job_code int,
    store_id int
)
partition by HASH(store_id) partition 4;

KEY分区

KEY分区类似HASH，但是HASH允许用户使用自定义表达式，而KEY分区不允许，它需要使用MySQL服务器提供的HASH函数，同时HASH分区只支持整数分区，而KEY分区支持除了BLOB和TEXT类型外其他列。

create table tk(
    col1 int not null,
    col2 char(5),
    col3 date,
    primary key (col1)
)
partition by key(col1) paritions 3;

存储过程/触发器

存储过程

存储过程是一种在数据库中存储复杂程序，以便外部程序调用的一种数据库对象。

存储过程是为了完成特定功能的SQL语句集，经编译创建并保存在数据库中，用户可通过指定存储过程的名字并给参数（需要时）来调用执行。

存储过程思想上很简单，就是数据库SQL语言层面的代码封装于重用。

优点

　　- 存储过程可以封装，并隐藏复杂的商业逻辑。

　　- 存储过程可以回传值，并可以接受参数。

　　- 存储过程无法使用select 指令来运行，因为它是子程序，于查看表，数据表或用户定义函数不同。

　　- 存储过程可以用在数据检验，强制实行商业逻辑等。

缺点

　　- 存储过程，往往定制化于特定的数据库上，因为支持的编程语言不同。当切换到其他厂商的数据库系统时，需要重写原有的存储过程。

　　- 存储过程的性能调校与撰写，受限于各种数据库系统。

存储过程的创建和调用

　　存储过程就是具有名字的一段代码，用来完成一个特定的功能。

　　创建的存储过程保存在数据库的数据字典中。

　　　　create produce 存储过程名(in | out | inout 参数名称 参数类型,.....)

　　　　begin

　　　　　　过程体;

　　　　end

实例

create procedure getStudentCount()
begin
    select count(*) as num from student where classid=8;
end

查询/修改/删除存储过程

　　查询

查看所有存储过程状态：

show procedure status;

查看对应数据库下所有存储过程状态：

show procedure status where db=‘数据库名';

查看名称包含Student的存储过程状态：

show procedure status where name like '%student%';

查询存储过程详细代码：

show create procedure 过程名;

修改

alter procedure 过程名([过程参数[,...]])过程体;

删除

drop procedure 过程名;

注意：不能在一个存储过程中删除另一个存储过程，只能调用另一个存储过程。

调用存储过程

MySQL存储过程用call和过程名以及一个括号，括号里面根据需要，加入参数，参数包括输入参数，输出参数，输入输出参数调用，格式如下：

call 存储过程名([过程参数[,....]])

触发器

触发器（trigger ），也叫触发程序，是与表有关的命名数据库对象，是MySQL中提供给程序员来保证数据完整性的一种方法，它是与表事件insert，update ， delete 相关的一种特殊的存储过程，它的执行是由事件来触发，比如当对一个表进行insert ，update ， delete 事件时就会激活它执行。因此，删除，新增或修改操作可能都会激活触发器，所以不要编写过于复杂的触发器，也不要增加过多的触发器，这样会对数据的插入，修改或删除带来比较严重的影响，同时也会带来可移植性差的后果，所以在设计触发器的时候一定要有所考虑。

创建触发器

create trigger trigger_name trigger_time trigger_event on tb_name for each row trigger_stmt

其创建触发器的参数说明如下表：

 

 触发事件类型介绍如下表所示：

 

 我们想要使班级表中的班内学生数随着学生的添加自动更新，则采用触发器来实现最为合适，创建触发器如下：

create trigger tri_stuInsert aflter insert
on student for each row
begin
    declare c int;
    set c = (select stuCount from class where classID=new.classID);
    update class set stuCount = c + 1 where classID = new.classID;
end

从需求我们可以得知，班内学生数的变化是在插入学生记录之后发生的，所以创建的触发器类型为after insert 类型。

查看触发器

show triggers;

删除触发器

drop trigger [if exists] trigger_name

触发器执行顺序

日常开发中创建的数据库通常都是InnoDB数据库，在数据库上建立的表大都是事务性表，也就是事务安全的，这时触发器的执行顺序主要是：

1. 如果 before 类型的触发器执行失败，SQL无法正确执行。

2. 如果SQL执行失败时，after 类型的触发器不会触发。

3. 如果after 类型的触发器执行失败，数据会回滚。

如果是对数据库的非事务表进行操作，当触发器执行顺序中的任何一步执行出错，那么就无法回滚了，数据可能会出错。

MySQL函数

运算函数

　　abs(x)：返回x的绝对值

　　floor(x)：返回小于x的最大整数值

　　round(x,y)：返回参数x的四舍五入的有y位小数的值

　　mod(x,y)：返回x/y的模（余数）

　　greatest(x1,x2,....,xn)：返回集合中最大的值

　　least(x1,x2,...,xn)：返回集合中最小的值

字符串函数

　　trim(str)：去除字符串首尾两端的空格

　　upper(str)：字符串转大写

　　concat(s1,s2...,sn)：将s1,s2....,sn连接成字符串

#concat
insert into tb1_name values(concat('abc','def'));

日期函数

　　year(date)：返回日期date的年份(1000~9999)

　　month(date)：返回date的月份值(1~12)

　　day(date)：返回date的日(1~31)

　　curdate()：返回当前的日期

　　week(date)：返回日期date为一年中第几周(0~53)

　　now()：返回当前的日期和时间

　　curtime()：返回当前的时间

　　hour(time)：返回time的小时值(0~23)

　　minute(time)：返回time的分钟值(0~59)

聚合函数

　　count(col)：统计记录的条数

　　sum(col)：求和

　　avg(col)：求平均值

　　max(col)：求最大值

　　min(col)：求最小值

#count统计总记录数
select count(*) from tb1_name;

#sum年龄总和
select sum(age) from tb1_name;

#avg平均年龄
select avg(age) from tb1_name;

#最大年龄
select min(birthday) from tb1_name; #日期最小的

#最小年龄
select max(birthday) from tb1_name; #日期最大的

found_rows()

#found_rows函数配合SQL_CLAC_FOUND_ROWS 用于获取总记录数（忽略limit ）

select sql_calc_found_rows * from qa_list limi 3;
select found_rows();

第一个sql里面的SQL_CALC_FOUND_ROWS不可省略，它表示需要取得结果数，也是后面使用FOUND_ROWS()函数的铺垫。

FOUND_ROWS()返回的结果是临时的。如果程序往后会用到这个数字，必须提取把它保存在一个变量中待用。

FOUND_ROWS()与count()的区别：

1. 当SQL限制条件太多时，count()的执行效率不是很高，最好使用FOUND_ROWS()

2. 当SQL查询语句没有where等条件限制时，使用count()函数的执行效率较高。

数据库备份

使用Navicat 可视化工具导入 导出数据，备份 恢复数据

备份数据/导出整个数据库

命令窗：myqldump -u[用户名] -p[密码] [数据库名] > [path]/[名称].sql;   //前面的红色部分可以放在命令的最后也行：myqldump  [数据库名] > [path]/[名称].sql -u[用户名] -p[密码];

例：mysqldump -uroot -pxxx110 test > d:/abc/test.sql

导出指定库下的指定表

命令窗：mysqldump [数据库名] [表名] > [path]/[数据库名称].sql -u[用户名] -p[密码]   //红色部分也可以放在mysqldump后面也行

例：mysqldump -uroot -pxxx110 test emp_bak > d:/abc/emp.sql

上面的命令窗注意是doc，而且没有登录mysql的前提才能执行上面的导出语句。

Navicat备份数据：

恢复数据/导入数据

DOS命令窗：source 路径\数据库文件   //注意导入数据库/数据表  之前要创建对应的数据库

例：source c:\system.sql    //比如这里数据库文件的名字是system，所以要先创建create database system 然后use system ,才能执行source导入语句。

Navicat恢复数据/导入数据

 

 

 

 

 选择导入的数据/恢复的数据文件。

 

数据库设计三范式

1. 第一范式：任何一张表都应该有主键，并且每一字段原子性不可再分。

 

2. 第二范式：建立在第一范式的基础之上，所有非主键字段完全依赖主键，不能产生部分依赖（复合主键）。

多对多？三张表，关系表两个外键。

 

3. 第三范式：建立在第二范式的基础之上，所有非主键字段直接依赖主键，不能产生传递依赖。

一对多？两张表，多的表加外键。

如：班级t_class

cno(pk)     cname

----------------------------

1　　　　  班级1

2  　　　　班级2

 

学生t_student

sno(pk)    　　sname　　　 classno(fk)

---------------------------------------------------------

101　　　　　z1　　　　　　1

102　　　　　z2　　　　　　1

103　　　　　z3　　　　　　2

104　　　　　z4　　　　　　2

学生是多，班级一，一对多，多的表加外键，也就是学生表加外键。

 

注意：实际业务中，需求经常改动，所以不再推荐使用外键，难维护（不过数据库设计还是按照上面三规范，只是不添加外键，还是可以进行表连接）。

提醒：在实际的开发中，以满足客户的需求为主，有的时候会那冗余换执行速度。

 

补充：一对一怎么设计？

比如一个用户信息，字段太多怎么处理？

 

一对一设计有两种方案：

第一：主键共享

t_user_login   用户登录表

id(pk)   username      password

---------------------------------------------

1　　　　zs　　　　123

2　　　　ls　　　　656

 

t_user_detail  用户详细信息表

id(pk)     realname　　　tel

--------------------------------------

1　　　　张三　　　　　　323232323

2　　　　李四　　　　　　323232314

总结：主键共享就是个人信息用两张表存储，第一张表字段查询比较多，所以对立一张表，第二张表字段查询较少，独立一张表，然后使用相同的主键数字，就可以进行表连接。

 

第二：外键唯一

t_user_login   用户登录表

id(pk)   username      password

---------------------------------------------

1　　　　zs　　　　123

2　　　　ls　　　　656

 

t_user_detail  用户详细信息表

id(pk)     realname　　　tel　　　　　　　　userid(fk+unique)

----------------------------------------------------------------------------------

1　　　　张三　　　　　　323232323　　　  1

2　　　　李四　　　　　　323232314　　　　2

总结：第二张表userid外键引用第一张表的id主键，但是可能会出现重复，所以使用了唯一约束，这样第二张表的userid和第一张表的id只能一一对应。