Mysql

---

mysql的使用

我们用phpstudy安装mysql方便快捷，phpstudy集成了服务器相关的软件。
phpstudy下载地址：https://www.xp.cn/
下载后配置环境变量即可
关联数据库：mysql -u root -p

一、mysql的基本操作命令（不区分大小写）

1. 查看所有数据库（注意！命令后面要加 ; 结尾）

show databases;

2. 使用数据库

use xxx;

3. 创建数据库

create database my_test;

4.删除数据库

drop database my_test;

5. 查看数据库中的所有数据表

show tables;

6. 创建数据表，需要定义数据表的字段名称和存储值的数据类型，数据表需要指定主键(一张表只能有一个主键)
   一般字符型需要指定长度限制，数据型则不需要
   例：创建users表,primary key 设置主键,auto_increment 自增字段(增加1行，该值递增1)
   engine=InnoDB设置表使用的引擎,default charset 设置默认数据表字符集
   not null 表示该字段值不能为空，必需插入值

create table users(
    id int primary key auto_increment,
    username varchar(100),
    password varchar(50),
    name varchar(50) not null,
    age tinyint,
    create_time datetime
) engine=InnoDB default charset=utf8;

7. 删除数据表(删除users数据表)

drop table users;

二、数据表操作命令

1. 向数据表插入数据(自增字段的值不需要插入)
   值和字段要一一对应，now()是插入当前事件到对应字段

insert into users(username,password,name,age,create_time) values("test1","test1","Anna",28,NOW());
insert into users(username,password,name,age,create_time) values("test2","test2","Jone",16,NOW());
insert into users(username,password,name,age,create_time) values('test3','test3',"zhang",12,NOW());

2. select * 查看数据表数据所有字段的值

select * from users;

3. 查看指定字段的值(只查询id,anme字段的值)

select id,name from users;

4. where 条件查询,where 后面可以接查询条件
   4.1 (where后面可以用=或者>,!=等操作符号)

select id,name,age from users where age>=15;

4.2 可以使用and / &&连接多个查询语句，与逻辑，多个条件必需同时满足

select id,name from users where username="test1" && password="test1";

4.3. 用or 连接多个查询语句,或运算，多个条件满足其中一个就可以

select id,name from users where username="test1" or age>15;

5. like 模糊查询，接在where条件后面，属于一种查询条件
   5.1 %表示通配符(表示任意值)

select id,name from users where name like 'J%';

5.2 查询name值中以g结尾的数据

select id,name from users where name like '%g';

5.3 查询name值中包含n字符的数据

select id,name from users where name like '%n%';

6. order by 字段值排序
   按age 升序排序(默认) asc

select * from users order by age asc;

按age 降序排序 desc

select * from users order by age desc;

7. update 更新字段值,一般配合where使用，不然就是更新整列数据
   修改id=1的数据name值和age值

update users set name="Liys",age=24 where id=3;

8. delete 删除数据，也需要带条件,不然就是删除表所有数据

delete from users where id=2;

9. count() 统计某个字段的行数, as 给查询的结果取别名
   * 表示统计users表所有行数

select count(*) as total from users;

10. group by 根据一个或者多个列的结果集进行分组
    例：创建一个统计学生迟到次数表格

create table user_late(
    id int primary key auto_increment,
    uid int not null,
    count int,
    create_time datetime
)engine=InnoDB default charset=utf8;

insert into user_late(uid,count,create_time) values(1,2,NOW());
insert into user_late(uid,count,create_time) values(3,1,NOW());
insert into user_late(uid,count,create_time) values(1,3,NOW());
insert into user_late(uid,count,create_time) values(2,1,NOW());
insert into user_late(uid,count,create_time) values(1,1,NOW());
insert into user_late(uid,count,create_time) values(2,2,NOW());
insert into user_late(uid,count,create_time) values(3,1,NOW());

sum() 统计某个字段值的总和
10.1 查询uid总共迟到次数(count值的总和)

select uid,sum(count) as total from user_late group by uid;

10.2 聚合显示所有用户迟到的总数并和总计次数
coalesce() 设置rollup 中 null的显示值

select coalesce(uid,"总计") as uid,sum(count) as total 
from user_late 
group by uid with rollup;

11. limit 限制显示数据的条数
    如果数据量比较大，一次显示所有的数据，消耗的资源比较多，用户不一定能看完，可以控制limit显示数量，提高性能
    从第二条开始展示，总共展示2条

select * from users limit 2,2;

实际项目中一般需要用到分页显示: 分页公式,page 当前第几页，pageSize 是每页显示的条数： limit (page-1)*pageSize,pageSize

12. show columns 展示数据表的字段信息

show columns from users;

13. union操作符: 链接两条以上的select查询语句的结果并合并一起显示， 有重复的字段只显示一个（链接两个表的结果）
注意：查询的字段个数要统一

select name,id from users 
union 
select count,uid from user_late;

三、多表查询

同时查询2张表以上的信息,表与表之间一般需要存在一个关联关系。

1. 等值查询，多张表中至少有一个值是相同的。如果字段名称在多张表中都有，则需要带上表名。

select name,age,uid,users.create_time from users,user_late where users.id=user_late.uid;

2. 内连查询，inner join,和等值查询类似

select name,age,uid,users.create_time 
from users 
inner join user_late 
where users.id=user_late.uid;

3. 左连接查询，left join … on。先查询左边表的所有信息(users)，再去关联表中查询与之匹配的信息，没有匹配的则用null填充

select name,age,uid,users.create_time 
from users 
left join user_late 
on users.id=user_late.uid; 

4. 右链接查询,right join …on。先查询右边表的所有信息(users)，再去关联表中查询与之匹配的信息，没有匹配的则用null填充

select name,age,uid,users.create_time 
from users 
right join user_late 
on users.id=user_late.uid; 

四、事务

主要用于多条数据命令操作，复杂度比较高的数据，需要保证数据的完整性和一致性。
(同时向数据库插入一堆数据，只要有一条失败，所有的都数据都不应该插入。)

购物： 商品数据库商品数量修改数据,订单数据库插入数据,用户金额表数据更新。(需要保证所有的数据成功，只要有一条数据操作失败，整个都失败)

开始事务: begin;
回滚事务: rollback;(只要有一条数据操作失败，就需要执行rollback回滚，撤销begin事务的所有操作)
提交事务: commit;

begin;
insert into user_late(uid,count,create_time) values(7,1,now());
insert into user_late(uid,count,create_time) values(8,2,now());

rollback;
commit;

五、更新数据表字段信息alter

查看表字段信息：show columns from user_late;
查看数据库的表：show tables;

1. add 添加字段(添加test 字段(默认是0))
   默认添加的字段是放在表的后面

alter table user_late add test int not null;

after 可以指定添加字段放在某个字段后面(添加name字段放在uid字段后面)

alter table user_late add name varchar(30) not null after uid;

2. drop 删除字段

alter table user_late drop test;

3. modify 修改字段的数据类型

alter table user_late modify name char(20);

4. change 修改字段名和数据类型

alter table user_late change name Name_ varchar(50);

5. rename to 修改表名

alter table user_late rename to users_late;

六、外键

外键: foreign key，外键主要用于两张表关联，外键必需是另外一张的表主键。
外键可以有多个，外键主要是为了保证数据表数据的完整性。

1. 外键关联的两张表，外键值在主键表中没有，则不能插入/修改数据
2. 外键关联的表，外键值在主键表中的数据是不能被删除

例：创建一个班级表class_table，使用班级表的主键作为学生表users的外键(保证每个学生都有一个班级)

 create table class_table(
    id int primary key auto_increment,
    title varchar(30),
    create_time datetime
 )engine=InnoDB default charset=utf8;
 
insert into class_table(title,create_time) values("高一1班",now());
insert into class_table(title,create_time) values("高一2班",now());

给users学生表添加cid 关联班级表的id字段

alter table users add cid int;

创建外键,给users表的cid字段创建外键(外键名称叫c1),外键是class_table表的id字段值

alter table users add constraint c1 foreign key(cid) references class_table(id);

update users set cid=1;
update users set cid=2 where age>=20;
insert into users(username,password,name,age,create_time,cid) values("li","li1","lili",19,now(),1);

// 以下数据报错，有外键约束（班级只有1和2两个班）
update users set cid=3; 
insert into users(username,password,name,age,create_time,cid) values("li","li1","lili",19,now(),4);
delete from class_table where id=1;

七、索引

索引是mysql提高查询速度的一种优化手段，一般可以给查询条件中比较频繁的字段添加索引。

1. primary key 主键，是一种索引，速度最快的索引(一张表只能有一个)。
2. index 普通索引，没有数量限制(创建索引会降低插入数据的速度)

//查看表的索引信息
show index from users; 

给age字段添加索引，索引名称为ageindex

alter table users add index ageindex(age)

3. unique index 唯一值，该索引的值不能重复。(主键也是一种unique index)
   给username创建唯一索引，索引名称叫uniname

alter table users add unique index uniname(username);

4. drop 删除索引
   删除users表的 ageindex索引

alter table users drop index ageindex;

八、临时表

普通的数据表数据是存放在硬盘上，零时表是存放在内存中，如果数据连接断开，零时表会被释放。
零时表的目的是把查询频繁的数据存入零时表，由于零时表是在内存中，速度比从硬盘中快很多。

create temporary table catch_users(
    id int primary key not null,
    name varchar(30) not null,
    age int,
    cid int
);
insert into catch_users(id,name,age,cid) values(1,"anna",19,1);
insert into catch_users(id,name,age,cid) values(2,"jone",26,1);

九、备份还原数据库

1.备份数据库数据

(不要在链接的命令窗口，重开一个命令窗口)
把名为my_test的数据库备份到 e盘下(e盘就会存入此文件)
mysqldump -u root -p my_test > e://my_test.sql
删除原来数据库的该数据库
drop database my_test;

2.还原数据库数据

链接数据库，还原备份中的数据库
创建一个对应名的数据库
create database my_test;
use my_test;
source f://my_test.sql;
show tables;