数据库 -- MySQL

1.1 数据库简介

数据库就是存放数据的仓库，常用的数据库模型主要分为两种：关系型数据库和非关系型数据库

关系型数据库模型就是把复杂的数据结构归结为简单的二元关系（即二维表格形式）

非关系型数据库也被称为NoSQL数据库，NoSQL的产生并不是要彻底地否定关系型数据库，而是作为传统关系型数据库的一个有

效补充。NoSQL数据库在特定的场景下可以发挥难以想象的高效和高性能

1.2 MySQL命令

1.2.1 连接命令

使用命令连接

打开终端，运行命令（前提需要配置mysql环境变量）

mysql -uroot -p
# 回车后输入密码，当前设置的密码为mysql
# 完整命令 
mysql -h 192.168.10.123 -P 3306 -u root -p 
# 其中-h参数为主机地址 -P为端口值

退出登录

quit
# 或者exit

1.2.2 数据库操作

显示数据库列表：

show databases;

创建数据库：

create database 数据库名 charset=utf8;

删除数据库：

drop database 数据库名;

切换数据库：

use 数据库名;

查看当前选择的数据库：

select database();

1.2.3 表操作

查看当前数据库中所有表：

show tables;

创建表：

create table 表名(列及类型);

create table students(
    id int auto_increment primary key not null,
    sname varchar(10) not null,
);

修改表：

alter table 表名 add|change|drop 列名 类型;

alter table 表名 add 列名 类型；--添加某一列
alter table 表名 change 原列名 新列名 类型； --修改表的列属性名
alter table 表名 drop 列名； --删除表的某一列
alter table 表名 rename 新表名； --修改表名

删除表：

drop table 表名;

查看表结构：

desc 表名;

1.2.4 数据操作CURD

查询：

select * from 表名

增加：

全列插入：insert into 表名 values(...)
缺省插入：insert into 表名(列1,...) values(值1,...)
缺省插入：insert into 表名 set field1=value1,field2=value2
同时插入多条数据：insert into 表名 values(...),(...)...;

修改：

update 表名 set 列1=值1,... where 条件

删除：

delete from 表名 where 条件

1.2.5 外键

MySQL创建关联表可以理解为是两个表之间有个外键关系，但这两个表必须满足三个条件

1.两个表必须是InnoDB数据引擎

2.使用在外键关系的域必须为索引型(Index)

3.使用在外键关系的域必须与数据类型相似

外键的好处：可以使得两张表关联，保证数据的一致性和实现一些级联操作；

定义外键：

constraint FK_TEAID foreign key (teaid) references tea (id) ON DELETE RESTRICT ON UPDATE RESTRICT;

添加外键：

alter table stu add constraint FK_TEAID foreign key(teaid) references tea(id);

删除外键：

alter table stu drop foreign key FK_TEAID;

1.3 数据备份与恢复

进入mysql库目录：

# 运行mysqldump命令
mysqldump –uroot –p 数据库名 > ~/Desktop/备份文件.sql;
# 按提示输入mysql的密码

数据恢复：

# 连接mysqk，创建数据库
# 退出连接，执行如下命令
mysql -uroot –p 数据库名 < ~/Desktop/备份文件.sql
# 根据提示输入mysql密码

1.4 数据的授权

grant all on *.* to zzy@localhost identified by '1234';

grant select on db.stu to zzy@localhost identified by '1234';

删除用户：

use mysql
delete from user where user ='zzy';

修改密码：

use mysql
update user set password=password('新密码') where User="zzy" ;

1.5 知识进阶

1.5.1 as

在select后面的列名部分，可以使用as为列起别名，这个别名出现在结果集中

1.5.2 distinct

消除重复行

在select后面列前使用distinct可以消除重复的行

select distinct gender from students;

1.5.3 where

select * from 表名 where 条件;

比较运算符：

等于=、 大于>、大于等于>=、小于<、小于等于<=、不等于!=或<>

# 查询编号大于3的学生
select * from students where id>3;
# 查询编号不大于4的科目
select * from subjects where id<=4;
# 查询姓名不是“黄蓉”的学生
select * from students where sname!='黄蓉';
# 查询没被删除的学生
select * from students where isdelete=0;

逻辑运算符：

and、or、 not

# 查询编号大于3的女同学
select * from students where id>3 and gender=0;

模糊查询：

like

%表示任意多个任意字符

_表示一个任意字符

# 查询姓黄的学生
select * from students where sname like '黄%';
# 查询姓黄并且名字是一个字的学生
select * from students where sname like '黄_';
# 查询姓黄或叫靖的学生
select * from students where sname like '黄%' or sname like '%靖%';

范围查询：

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

# 查询编号是1或3或8的学生
select * from students where id in(1,3,8);

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

# 查询学生是3至8的学生
select * from students where id between 3 and 8;
# 查询学生是3至8的男生
select * from students where id between 3 and 8 and gender=1;

空判断：

注意：null与""是不同的

# 判空is null
# 查询没有填写地址的学生
select * from students where hometown is null;
# 判非空is not null
# 查询填写了地址的学生
select * from students where hometown is not null;
# 查询填写了地址的女生
select * from students where hometown is not null and gender=0;

1.5.4 聚合

# count(*)表示计算总行数，括号中写星与列名，结果是相同的
# 查询学生总数
select count(*) from students;
# max(列)表示求此列的最大值
# 查询女生的编号最大值
select max(id) from students where gender=0;
# min(列)表示求此列的最小值
# 查询学生最小编号
# sum(列)表示求此列的和
# 查询男生的编号之和
select sum(id) from students where gender=1;
# avg(列)表示求此列的平均值
# 查询女生的编号平均值
select avg(id) from students where gender=0;

1.5.5 分组

#可以对分组后的数据进行统计，做聚合运算
select 列1,列2,聚合... from 表名 group by 列1,列2,列3...
# 查询男女生总数
select gender as 性别,count(*) from students group by gender;
# 查询各城市人数
select hometown as 家乡,count(*) from students group by hometown;

1.5.6 排序

select * from 表名 order by 列1 asc|desc,列2 asc|desc,...

asc从小到大排列，即升序

desc从大到小排序，即降序

# 查询男生学生信息，按学号降序
select * from students where gender=1 order by id desc;

1.5.7 事物

事务： 为了保证数据的完整性，成批的SQL要么全部执行，要么全部不执行。

MySQL中只有使用innerDB引擎才支持事务

ACID特性 原子性：要么全部执行，要么全部不执行

一致性 ：事务开始前结束后对数据的完整性不会破坏

隔离性 ：允许多个并发事务同时对数据的读写，防止多个并发执行引起的不一致

持久性：事务处理结束后，对数据的修改是永久的

常用操作

begin 开始事务

rollback 回滚

commit 结束事务

想要使用事务必须使用INNODB引擎