Mysql基础

关键字：

AS 起别名：用在查询的数据项后面

DISTINCT 去除一列重复查询到的值，放在select 查询属性名的前面

多对多的表格关系。需要借助第三张关系表进行存储。

运算符:

like 模糊查询,是否含有包含内容

in()括号里的值一个个比对是否满足

有null参与的运算结果都为null，null=null结果也为null，想要判断属性是否为null使用is null 进行运算，或者使用<=>安全等于进行比较，它可以比较为null项

between 值1 and 值2 在值1和值2区间均满足

not between .. and .. 不在更改范围内的

#查询价格在20到60之间的
select  * 
from book
where price between 20 and 60;

<=> 安全等于,可以比较null

is null 是null

is not null 不是bull

and / && 并

or / || 或

not / ! 非

结构：

select 列名列表 from 表名

[where 条件]

[group by 分组列名]

[having 分组后的条件过滤]

[order by 排序列名 排序方式]

[limit 当前页数,每页显示条数]

函数：

count(*) 数据条数，是查询的数据项之一

avg(属性名) 查询项，计算该属性的平均值

max(属性名)查询项，该项中的最大值

sum(属性名) 求和

min(属性名)查询项，该项中的最小值

ifnull(属性名,值1)如果属性名的值为null则直接对应值1

greatest(值1,值2,值3...) 查询最大值

least(值1,值2,...)查询这里面的最小值

通配符：

%代表多个字符

select  *
from student
where phone_num  like %4706;

_代表一个字符

select  * 
from student
where name like  '王_'

数据类型

DECIMAL(3,2) 有小数点的数

VARCHAR(10) 字符串

BLOB 图片 视频等二进制文件

DATE YYYY-MM-DD 日期

TIMESTAMP YYYY-MM-DD HH:MM:SS 记录时间

INT 整数

数据库操作

创建数据库:

create database if not exists 数据库名;

查看所有数据库：

show databases;

到数据库下：

use 数据库名;

表格操作

查看表格信息：

describe 表名;

创建表格：

create table 表名(

属性名1 类型 约束,

属性名2 类型 约束,

);

eg：

create table student(
	uid  int primary key   auto_increment,
    name  varchar(10) not null,
    gender varchar(3) not null,
	birthday date
);


格式2:
create table student(
	uid  int,
    name  varchar(10) not null,
    gender varchar(3) not null,
	birthday date,
    primary key ('uid')
);

primary key ：主键约束，该列值唯一

auto_increment：设置自动增长

not null：非空约束

unique：唯一约束

创建表格时加外键：

FOREIGN KEY (子表id) REFERENCES 关联表名(外主表id)

create  table  class(
    class_id  int auto_increment,
	grade  int  unique,
    class_num  int  unique
);

create  table  student(
	uid  int primary key,
    name varchar(20),
    grade  int foreign key  references  class(grade) on delete set null,
    class_num  foreign key  references  class(class_num) on delete set cascade
);

主表(class)存在从表(student),从表引用了外键，不能直接删除(主表)

DROP TABLE class; --会删除失败

在删除表的时候，如果存在外键引用，则必须先删除从表（也就是把存在相关外键约束的表全部删掉），才能删除主表。
以上的外键都是物理外键，属于数据库级别的引用，不推荐使用，使用这个会造成数据耦合度大大增加。

on delete set null表示对应不到时设置为null

on delete set cascade表示对应不到时删除数据项

删除表格：

drop table 表名;

修改表格名：

alter table 表名 rename to 新表名

新增属性(列)：

Alter table 表名 add 新属性名(新列名) 类型 约束;

alter table student  add GPA  decimal(3,2) not null;

删除属性(列):

Alter table 表名 drop column 属性名;

Alter  table  student  drop  column   GPA;

更改属性名同时可以改约束，类型：

alter table 表名 change 旧字段 新字段 字段类型 约束;

更改字段类型，长度，约束：

alter table 表名 modify 字段 字段类型 约束;

增加主键约束/联合主键：

alter table 表名 add primary key(字段名1,字段名2);

删除主键：

alter table 表名 drop primary key;

增加外键：

建表时添加外键约束：

create table 表名(

列名 数据类型 约束,

...

constraint 外键名 foreign key (本表外键列名) references 主表名(主表主键列名)

);

alter table 表名 add constraint 约束名 foreign key 表名(字段名1,字段名2) references 关联表(字段名1,字段名2);

删除外键：

alter table 表名 drop foreign key 外键名;

数据操作：

插入数据：

insert into 表名 values(值1,值2···· ); 要将所有值都填写上，多个值用,()继续添加

insert into 表名(属性名1,属性名2,属性名3) values(值1,值2,值3);

insert into student3 values(1,'haha','男','2010-10-01',3.15);
insert into student3(name,gender,birthday,GPA) values('hehe','男','2010-10-01',3.25);

修改数据值：

update 表名 set 属性名(列名)=值,属性名2(列名)=值2··· where 属性名3(列名)=条件值;

update  student  set  name='哈哈'  where  uid=1;

删除数据：

delete from 表名 where 属性名(列名)=条件值;多个条件用and连接，或条件用or

delete  from  student  where uid=1;

查询操作：

and 多个条件

or 某个条件

in(值1,值2,值3) 在多个值中存在即可

查询所有：

select * from 表名;

条件查询：

select 字段

from 表名

where 属性名=条件值 and 属性值2=条件值2 。。。

多个条件用and连接，或运算用or连接

排序查询限定个数：

select 查询列1, 查询列2, 查询列3, 查询列4

from 表名

where 属性名=条件值

order by 属性名 DESC或ASC /以该属性升序排序查询到的结果 属性名后加上 DESC改为降序 /

limit 数值; /查询满足条件的前几条 /

或

limit 当前页数,每页显示条数;

当前页数=(实际当前页数-1)*每页显示条数

比如:我们想看第一页,每页三条数据limit 0,3

想看第二页每页三条数据 limit 3,3

select  uid,name,gender
from student
where uid>=1  and gender='男'
order by uid  
limit 0,3;

/*出现in（）里的值即满足条件*/
select  *
from  student
where  uid in(1,2,3);

分组查询

select 属性名

from 表名

where 条件

group by 以那列为分组依据 having 查询到的值条件;

union联合查询：

将多个表以一列形式查询出来

select 表1属性名

from 表1

union select 表2属性名

from 表2;

可以使用多个union连接多个表，每个select后的字段个数要一致。

外连接查询：

左外连接：

select 字段名

from 表名1 left [ outer ] join 表名2 on 表名1字段=表名2字段

where 条件;

join左边的表数据全部显示，右边的表无数据项显示为null（查询左表的全部数据，和左右两表的交集部分）

cata是图书类别表cataid是图书的类别编号，Book是图书表，bookcata是图书的类别id

SELECT  * 
FROM book
LEFT JOIN cata ON book.`bookcata`=cata.`cataid`

查询图书及其类别信息

右外连接：

select 字段名

from 表名1 right join 表名2 on 表名1字段=表名2字段

where 条件;

join右边的表数据全部显示，左边的表无数据项显示为null

SELECT * 
FROM book
RIGHT JOIN cata ON book.`bookcata`=cata.`cataid`

查询类别下的所有书籍

一张表也可以自己关联自己。

内连接查询：

查询两表数据的交集

语法：显式

select 列名 from 表名

[ inner ] join 表名2

on 关联条件

隐式

select 列名 from 表名1,表名2

where 关联条件

子查询：

select 属性名

from 表名

where 字段名=/in(

select 字段名

from 表名

where 条件

);

=用于子查询的结果为1个时，多个查询结果时用IN或者NOT IN

/*查询书名为java的图书类别名称*/
SELECT  cataname
FROM cata
WHERE cataid=(
SELECT  bookcata
FROM book
WHERE bookname='java'
);

所查询的结果是多行多列的，可以作为数据表使用

语法：

select * from 表名1 , ( select * from 表名2 [ where 条件 ] )

where 表名1.id=表名2.uid

视图：

视图是虚拟数据表，并不真实存在，作用是将一个较为复杂的查询结果封装到一个虚拟表中，后期可直接查询该虚拟表

创建语法：

create view 视图名 [ (列名列表) ] as 查询语句

这里面的列明列表和查询的结果对应就是给它起个新列名

表结构：

id	grade	class_num
1	1	1
2	2	2

sno	sname	sdept	sage	ssex	cid
1	王五	CS	19	男	1
2	柳柳	IN	19	女	1
3	芜湖	MA	19	女	2
4	喜喜	MA	19	男	2
5	哈哈	CS	19	男	1

eg：创建学生和班级关系表视图

create view stu_class (sname,grade,class_num) 
as select s.sname,c.grade,c.class_num
from student s,class c
where s.cid=c.id

修改数据：

update 视图名 set 列名=值 where 条件

修改查询语句：

alter view 视图名称(列名列表) as 查询语句

删除视图：

drop view [if exists] 视图名

数据库备份和恢复：

备份：

登录到mysql服务器，输入mysqldump -u root -p 数据库名称 > 文件保存路径

输入密码

恢复：

1.登录到mysql数据库

2.删除已备份的数据库（已经不存在省略该步骤）

3.重新创建名称相同的数据库

4.使用该数据库

5.导入文件执行，source备份文件全路径

存储过程和函数

存储过程和函数是事先经过编译并存储在数据库中的一段sql语句的集合

函数必须有返回值，存储过程可有可无。

存储过程：

创建存储过程：

--修改结束分割符

delimiter $

--创建存储过程

create procedure 名称(参数列表)

begin

​ SQL语句

end$

delimiter  $
create  procedure  pro_test()
begin

declare num int  default 10;  -- 定义一个变量默认值10
set num=11; -- 赋值方式1
select sum(score) into num from student; -- 赋值方式2
select num;

end
delimiter;

调用：

call 存储过程名(实际参数);

call pro_test();

存储过程中的if：

语法：

delimiter $

--创建存储过程

create procedure 名称(参数列表)

begin

​ if 条件 then 执行内容

​ elseif 条件 then 执行内容

​ else 执行内容

​ end if;

end$

delimiter  $

create  procedure  pro_test()
begin
declare total int;  -- 定义一个变量
declare info varchar(10);
select sum(score) into total from student; -- 赋值方式2
if total<300 then set info='一般';
elseif total>320 then set into='中等';
else set into='优秀';
end if;
select total,info;
end$

delimiter;

参数传递：

语法：

delimiter$

create procedure 名称(in 名称1 类型,out 名称2 类型)

begin

sql语句;

end$

调用：

call 名字('张三',@info);

select @info;

delimiter $

create procedure pro_test(in name varchar(5),out info varchar(10) )
begin 
declare total int;
declare info varchar(10);
select sum(s.score) into total from student s where s.name=name;
if total < 350 then set info='一般'
else info='优秀'
end if
end$

call pro_test('张三',@info);

while循环

语法：

初始化语句;

while 条件判断语句 do

循环体语句;

条件控制语句;

end while;

计算100以内奇数和：

delimiter $;
create procedure pro_test()
begin
declare result int default 0;
declare num int default 1;
while num<=100 do
	if num%2=1 then 
	set result=result+num;
	end if;
	num=num+1;
end while;
end$

存储函数:

存储函数和存储过程区别在于是否必须有返回值

创建函数:

语法：

delimiter $

create function(参数列表)

returns 返回值类型

begin

​ 执行的sql语句

​ return 结果;

end$

调用：

select 名称(参数);

-- 查询学生人数
delimiter $
create function pro_test()
returns int
begin

	declare num int;
	select count(*) into num from student;
	retun num;
end$


-- 调用
select pro_test();

删除函数:

drop function 名称;

触发器

触发器是与表有关的数据库对象，可以在insert，update，delete之前或者之后触发并执行触发器定义的SQL语句

new.属性名 新修改/插入的值

old.属性名 修改/删除前的值

创建触发器:

delimiter $;

create trigger 名称

after/ before insert/update/delete

on 表名

for each row

begin

sql 语句

end$

-- 插入时触发器
delimiter $
create trigger student_insert
after insert
on student
for each row
begin
-- id,studentId,studentName,insertTime
insert into student_info values(null,new.id,new.name,new());

end$

查看触发器

show triggers;

删除触发器：

drop trigger 名称;

事务

一条或多条sql语句组成的一个执行单元，其特点是这个单元要么同时成功要么同时失败。

执行一半失败回滚。

开启事物：

start transaction;

回滚事物：

rollback;

提交事物：

commit;

查看事物的提交方式：

select @@AUTOCOMMIT;

1为自动提交，0为手动提交

修改事物的提交方式：

set @@AUTOCOMMIT=0;

事物四大特征：

原子性：要么全部成功，要么全部失败回滚

一致性：必须使数据库从一个一致性状态变换到另一个一致性状态

隔离性：

隔离性是当多个用户并发访问数据库时，比如操作同一张表时，数据库为每一个用户开启的事务。
不能被其他事务的操作所干扰，多个并发事务之间要相互隔离。
持久性：

持久性是指一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的。即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。

事务的隔离级别：

多个客户端操作时，各个客户端的事务之间应该是隔离的，相互独立的，不受影响的。而如果多个事务操作同一批数据时，就会产生不同的问题，我们需要设置不同的隔离级别来解决这些问题。

隔离级别/级别字符串	名称	会引发的问题
read uncommitted	读未提交	脏读、不可重复读、幻读
read committed	读已提交	不可重复读、幻读 （oracle）
repeatable read	可重复读	幻读（在另一个事物也commit）（mysql）
serializable	串行化	无

从上到下安全性越来越高，效率越来越低，

脏读：在一个事务处理过程中读取到了另一个未提交事务中的数据，如果另一个事务回滚，导致两次查询结果不一致

不可重复读：先读取了原始数据，然后读取到了另一个事务中修改并已提交的数据，导致两次查询结果不一致。

幻读：查询某数据不存在，准备插入此记录，但执行插入时发现此记录已存在，无法插入（另一个事务插入未提交，后来提交了）。或查询数据不存在执行删除操作，却发现删除成功

查询事物的隔离级别

select @@TX_ISOLATION;

修改数据库的隔离级别：

set global transaction isolation level 级别字符串;

存储引擎

MySQL数据库使用不同的机制存取表文件，包括存储方式、索引技巧、锁定水平等不同的功能。这些不同的技术以及配套的功能称为存储引擎。

名称	事务安全	锁机制	集群索引	外键
MyISAM	不支持	表锁	不支持	不支持
InnoDB	支持	表锁/行锁	支持	支持
MEMORY	不支持	表锁	不支持	不支持

MyISAM:
特点:不支持事务和外键操作。读取速度快，节约资源。
使用场景:以查询操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高!

InnoDB:
特点:MySQL的默认存储引擎，支持事务和外键操作。
使用场景:对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，读写频繁的操作!

MEMORY:
特点:将所有数据保存在内存中，在需要快速定位记录和其他类似数据环境下，可以提供更快的访问。
使用场景:通常用于更新不太频繁的小表，用来快速得到访问的结果!

查询数据库支持的存储引擎：

show engines;

查询某个数据库中所有数据表的存储引擎:

SHOW TABLE STATUS FROM 数据库名称 [where name=表名];

查询某个数据库中某个数据表的存储引擎:

SHOWTABLE STATUS FROM 数据库名称 WHERE NAME='数据表名称;

创建数据表，指定存储引擎：

create table 表名(

列名,数据类型,

......

)engine=引擎名称;

索引

MySQL索引:是帮助MySQL高效获取数据的一种数据结构。所以，索引的本质就是数据结构!

按照功能分类
普通索引:最基本的索引，没有任何限制。
唯一索引:索引列的值必须唯一，但允许有空值。如果是组合索引，则列值组合必须唯一。
主键索引:一种特殊的唯一索引，不允许有空值。在建表时有主键列同时创建主键索引。
联合索引:顾名思义，就是将单列索引进行组合。
外键索引:只有InnoDB引擎支持外键索引，用来保证数据的一致性、完整性和实现级联操作。
全文索引:快速匹配全部文档的方式。InnoDB引擎5.6版本后才支持全文索引。MEMORY引擎不支持。

创建索引:

create [unique/fulltext] index 索引名称

[using 索引类型]

on 表名(列名...);

查看索引：

show index from 表名;

添加索引：

普通索引:ALTER TABLE 表名 ADD INDEX索引名称(列名);
组合索引:ALTER TABLE 表名 ADD INDEX索引名称(列名1.列名2....);
主键索引:ALTER TABLE 表名ADD PRIMARY KEY(主键列名);
外键索引:ALTERTABLE 表名 ADD CONSTRAINT外键名 FOREIGN KEY(本表外键列名)REFERENCES主表名(主键列名);
唯一索引:ALTER TABLE 表名 ADD UNIQUE索引名称(列名);
全文索引:ALTER TABLE 表名 ADD FULLTEXT索引名称(列名);

删除索引：

drop index 索引名称 on 表名;

BTree数据结构：
每个节点中不仅包含key值，还有数据。会增加查询数据时磁盘的10次数
B+Tree数据结构”
非叶子节点只存储key值。
所有数据存储在叶子节点。
所有叶子节点之间都有连接指针。
B+Tree好处
提高查询速度。
减少磁盘的IO次数。
树型结构较小。

创建索引遵循的原则
1.对查询频次较高，且数据量比较大的表建立索引。
2.使用唯一索引，区分度越高，使用索引的效率越高。
3.索引字段的选择，最佳候选列应当从where子句的条件中提取
4.索引虽然可以有效的提升查询数据的效率，但并不是多多益善。

最左匹配原则(适用组合索引)
例如:为user表中的nameaddressphone列添加组合索引
ALTER TABLE user ADDINDEXidxthree(nameaddressphone);
此时，组合索引idxthree实际建立了(name)、(nameaddress)、(nameaddressphone)三个索引
下面的三个SQL语句都可以命中索引
SELECT * FROMuser WHEREaddress北京ANDphone='12345ANDname='张三';
SELECT * FROMuser WHEREname=张三ANDaddress=北京;
SELECT * FROMuser WHERE name=张三';

---

锁

锁机制:数据库为了保证数据的一致性，在共享的资源被并发访问时变得安全所设计的一种规则。
锁机制类似多线程中的同步，作用就是可以保证数据的一致性和安全性。

按操作分类
共享锁:也叫读锁。针对同一份数据，多个事务读取操作可以同时加锁而不互相影响，但是不能修改数据。
排他锁:也叫写锁。当前的操作没有完成前，会阴断其他操作的读取和写入。

存储引擎	表锁	行锁
InnoDB	支持	支持（默认）
MyISAM	支持	不支持
MEMORY	支持	不支持

带索引的列加锁为行锁，不带的为表锁。

innoDB:

创建锁：

SELECT 语句 LOCK IN SHARE MODE;

创建排他锁：

select 语句 for update;

eg:

select * from student  where  id=1 for update;

MyISAm:

读锁:所有连接只能读取数据，不能修改
加锁：
LOCK TABLE 表名 READ;
解锁：
UNLOCK TABLES;

写锁特点
其他连接不能查询和修改数据。

写锁语法格式
加锁：
LOCK TABLE 表名 WRITE;
解锁：
UNLOCK TABLES;