MySQL数据库概念和基本使用

基本数据库概念

MySQL是一个关系型数据库管理系统，由瑞典MySQL AB 公司开发，目前属于 Oracle 旗下产品。MySQL 是最流行的关系型数据库管理系统之一，在 WEB 应用方面，MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件。
关系型数据库功能：

• 永久保存数据
• 提供数据的安全
• 提供数据的并发访问
• 提供了多种数据类型
• 提供事务支持
• 提供了SQL语言操作数据
  一些著名的数据库：
• Oracle 甲骨文
• MySQL 甲骨文
• Sql Server 微软 (主要用在windows环境)
• DB2 IBM公司
• SQLlite 用在移动端开发

一、安装：

解压
bin/mysql.exe 客户端程序
bin/mysqld.exe 服务器端程序
bin/mysqldump.exe 数据备份程序

1. 初始化
   bin/mysqld --initialize --console
   初始化结束会生成data的文件夹，其中保存系统和用户的数据（注意记录临时密码）
2. 启动数据库服务
   bin/mysqld --console
   端口用的是3306，可以使用Ctrl+C 停止服务程序，或者直接关闭窗口
3. 使用客户端连接
   bin/mysql -u 用户名 -p
   退出用quit命令
4. 修改密码
   alter user 'root'@'localhost' identified by 'root';
5. 配置mysql的bin目录至环境变量
6. 将mysql安装为系统服务
   bin\mysqld install 服务名 （默认为MySQL）
   卸载服务：
   sc delete 服务名
   安装服务或删除时出现权限不足，用管理员身份运行cmd
7. 建库
   设置默认的字符编码
   建库SQL语句(在mysql提示符下运行)
   create database 数据库名 character set utf8mb4;
   删除数据库
   drop database 数据库名;
   查看建库信息
   show create database 数据库名;
   修改全局的设置，在mysql解压目录添加 my.ini 配置文件（常用）
   [mysqld]
   character-set-server=utf8mb4
   停止旧服务，删除旧服务，安装新服务，启动新服务
   net stop mysql
   sc delete mysql
   mysqld install
   net start mysql

二、基本操作

建表(table),一个库中有多张表，每张表里有多条数据,表分成行（row 横向）与列（column 纵向。

1. 选库

			user  库名；

2. 建表

			create table 表名(
   				 列1名 类型,
   				 列2名 类型,
  				  ...
				);

整数类型：
tinyint(1个字节), smallint（2个字节）, int(4个字节), bigint（8个字节）
无符号数字 tinyint unsigned (0~255)
浮点类型：
float, double
定点小数： decimal(总位数, 小数位数)
字符类型：
char(长度) char(10) 表示最多存10个字符, 定长，效率高
varchar(长度) varchar(10) 表示最多存储10个字符，变长
日期类型：
date 只有日期
datetime 日期+时间
timestamp 时间戳

3. 插入数据

	insert into 表名(列1, 列2, ... 列n) values (值1, 值2, ... 值n);
  			 *注意：值个数要与列个数一致*
  	```	
 4. 查询数据
```sql
			select 列1, 列2, ... from 表名;

5. 查看所有库

				show databases;

6. 查看所有表

				show tables;

7. 唯一主键
   每张表只能有一个主键，主键的值必须是唯一，且非空的

				create table 表名(
   				 列1名 类型 primary key,
    				列2名 类型,
  				  ...
				);

自增列，用来解决主键冲突问题
在主键列后加入：auto_increment
使主键从指定位置开始：

// 1.方法1
create table b (
    id int primary key auto_increment,
    name varchar(20)
) auto_increment=1001;
insert into b (name) values('aaa'),('bbb'),('ccc');
// 2.方法2
create table b (
    id int primary key auto_increment,
    name varchar(20)
);
alter table b auto_increment=1001;

删表语法：

				drop table 表名;

一次插入多条记录（mysql独有）

				insert into student(name,sex) values('李四', '男'),('王五', '男'),('赵柳', '男');

8. 查询所有列

				select * from student;

9. 删除记录（只删除数据，不删表）

				delete from 表 where 条件;

只删除id=6的记录

				delete from student where id=6;

三、SQL语句

(一) DDL 数据定义语言

create database 数据库名;
create table 表名(列定义); (重点)
drop database 数据库名;
drop table 表名;
alter table 表 ... (添加列, 修改列, 删除列, 重命名列8.0才有)
alter user 用户
create -- 创建xx定义， drop -- 删除xx定义， alter -- 修改xx定义

1. 添加列

alter table 表名 add 列名 数据类型;

2. 修改列

alter table 表名 modify 列名 新类型;

3. 删除列

alter table 表名 drop 列名;

4. 重命名列

alter table 表名 change 旧列名  新列名 新类型;

(二) DML 数据操控语言

1. insert，插入数据

insert into 表名(列...) values(值...);  插入一行
insert into 表名(列...) values(值...), (值...), (值...), (值...);  插入多行
insert into 表2 select *  from 表1; 从表1查询，把查询结果插入表2
insert into 表2 select 表1列名，表2列名 from 表1; 当两张表结构不一样时，需在select后加具体列名，以便和新表列匹配

2. load data，把外部文本文件内容导入到数据库中
   语法：

load data infile '文件路径\文件名字' into table 表名;  //先创建对应表名再导入
load data infile '文件路径\文件名字' into table 表名 fields TERMINATED BY '其他列分隔符'; //如果文件的列分割符是其他分割符例如`,`不是默认`\t`键,则需使用fields TERMINATED BY 来指定分隔符。
//注意文件名转义字符

使load data命令生效必须修改配置文件设置：
[mysqld]
character-set-server=utf8mb4
secure-file-priv=
其中secure-file-priv默认问null值，表示不允许加载文件，可以在等号后加具体目录名，表示只能从这个目录加载文件，等号后什么都不加表示可以从任意目录加载文件

3. source，加载外部文件
   语法：

source 文件路径/文件名

文件内容必须是合法的sql语句，与load data区别是不用引号，最好用/分割文件，文件编码与操作系统最好一致（gbk）

4. update,更新
   语法：

update 表名 set 列名=新值 where 条件;
update person set sex='男';             // 把所有记录性别都改为男
update person set sex='男' where id=1;  // 只修改id=1的性别为男

5. delete，删除
   语法：

delete from 表名; // 删除表中所有记录(危险操作)
delete from 表名 where 条件; // 删除满足条件的记录

6. select，查询

select 列名... from 表 where 条件 group by 分组条件 having 分组筛选条件 order by 排序条件 limit;

where条件：

= 等值匹配
!= 不等值匹配
> 大于
< 小于
>= 大于等于
<= 小于等于

逻辑运算符组合多个条件： 

逻辑与（两个条件同时成立） and  例如：
select * from hero where sex='女' and loc='建业'; 

逻辑或（两个条件有一个成立，结果就是真） or 
select * from hero where name='小乔' or id=200;

逻辑非 (条件取反) not 
select * from hero where sex not  in('女');

列 between 值1 and 值2  等价于   列 >= 值1 and 列 <= 值2， 注意小值要在前面，包含边界的值

列 in (值1,值2,... 值n) 等价于   列=值1 or 列=值2 ... or 列=值n 注意值列表的长度

like 模糊查询  其中匹配通配符 % 表示匹配0~多个任意字符,通配符 _ 表示匹配1个任意字符   

排序条件 order by：

升序，从小到大 asc
降序，从大到小 desc
select * from 表名 order by 列名 desc;<--如果省略升降序关键字，则默认为asc-->

多列排序：排序条件1，排序条件2...
先按照1排序，条件1中取值相同的，再按照条件2排序
select * from hero order by 列名1 desc ,列名2 desc limit 10;

限制返回结果个数条件 limit：

limit m;    最多返回m个结果
limit n,m;  最多返回m个结果，n代表起始下标，下标从0开始

分组条件 group by：

select count(*)，分组条件列名 from 表名 group by 分组条件列名;
count(*) 求每组的个数
max(列)	求最大值
min(列)	求最小值
sum(列)	求和
avg(列)	求平均值

分组后，
* select 子句中只能出现分组条件列和组函数，其他列不能出现在select中
* order by 子句中只能出现分组条件和组函数，其他列不能出现在order by中

having 过滤条件：

sql语句有执行顺序：where>group by>having>select>order by>limit

select count(*), deptno from emp where count(*) >=5 group by deptno; // 因为where先执行，这时候还没有分组，不知道个数,错误
select count(*), deptno from emp group by deptno having count(*)>=5;

当条件既可以写在where上也可以写在having上时，优先采用where(可以达到提前筛选的目的，提高效率)
select count(*), deptno from emp where deptno=10 or deptno=30 group by deptno;
select count(*), deptno from emp group by deptno having deptno=10 or deptno=30;

多列分组：

group by 分组条件列名1，分组条件列名2...
多列分组时，列的顺序不影响结果

多表结构和连接查询 inner join：

select ... from 表1 inner join 表2 on 连接条件 where group by having order by limit;

当两表中列名相同时：表名.列名
select empno,ename,sal,emp.deptno,dept.deptno,dname,loc from emp inner join dept on emp.deptno = dept.deptno;

但表名很长时，可以给表起表别名： 表1 表1别名
select empno,ename,sal,e.deptno,d.deptno,dname,loc from emp e inner join dept d on e.deptno = d.deptno;

几种连接查询：

表1 inner join 表2 on 连接条件 (内连接，两张表的记录必须完全满足连接条件，才会出现在最后的结果中)
表1 left outer join 表2 on 连接条件 (左外连接，位于连接左侧的表，不管是否连接到了记录，都会出现在结果中。符合连接条件的记录，和内连接效果一样，不符合连接条件的记录，对应另一张表的列都是null值)
表1 right outer join 表2 on 连接条件 (右外连接，位于连接右侧的表，不管是否连接到了记录，都会出现在结果中)

outer 可以省略

连接查询的等价写法：

内连接的等价写法：
select ... from 表1，表2 where 连接条件；
select e.empno,e.ename,e.deptno,d.deptno,d.dname from emp e, dept d where e.deptno=d.deptno;

mysql独有的：
select ... from 表1 inner|left join 表2 using(连接列名); //两张表的连接列名要相同
select e.empno,e.ename,e.deptno,d.deptno,d.dname from emp e inner join dept d using(deptno);

常用函数方法：

• heip Functions/或具体函数名 可以用来查询函数语法

Bit Functions 位运算函数
Comparison operators 比较运算符
Control flow functions 流程控制
Date and Time Functions 日期函数

    year() 截取年份
    month()
    date()
    date_add(日期 时间间隔); 其中时间间隔的语法：interval n 单位
    select empno,ename,date_add(hiredate, interval 1 month ),hiredate from emp; 加一个月
    select empno,ename,date_add(hiredate, interval 3 day ),hiredate from emp; 加3天
    SELECT EXTRACT(DAY_MINUTE FROM '2009-07-02 13:02:03'); 提取日期中的从天到分钟的部分
    select now() 获取当前时间

Encryption Functions 加密
Information Functions
Logical operators 逻辑运算符
Miscellaneous Functions 剩余的函数
Numeric Functions 数学函数

    rand() 生成一个从[0.0 ~ 1.0) 之间的随机小数， 小于1.0
    floor() 舍去小数
    round() 四舍五入

String Functions 字符串函数

    left(字符串, n)  n代表从左边要截取的字符
    lower()
    upper()
    substr(字符串,下标, 长度) 下标从1开始
    求字符串长度的例子：select * from hero where char_length(name)=4;

7. 导出数据

• 在cmd窗口下执行 mysqldump -uroot -p 库名 >> 文件路径\要保存的文件.sql （source的逆操作, 内部是sql语句）
  mysqldump -uroot -p test >> e:\1.sql
• 把表中的数据保存到文本文件中 （load data infile的逆操作）
  select * from 表 into outfile '文件路径\文件名’
  select * from hero into outfile 'e:\\1.txt;'

8. 子查询 sub query
   当我们需要求出最高工资的员工信息时，直接select max(sal),e.* from emp e;会报错，因为最大值只有一行记录，但名字却又两个。所以需要用到子查询。
   把子查询当做一个值：
   分解问题：

第一步：把最高工资5000当作一个值
select max(sal) from emp;
第二步：写出主查询语句
select * from emp where sal=5000;
第三步：把第一步中的语句替换到第二步中
select * from emp where sal=(select max(sal) from emp);

把子查询当作一张表：
当需要求出每个部门的最高工资的员工时：

第一步：按照部门分组
select max(sal) msal,deptno from emp group by  deptno) a
第二步：把上面的查询结果看做一张临时表，让它与其他表之间做连接操作
select * from emp b inner join a on b.deptno=a.deptno and b.sal=a.msal;
第三步：把第一步的子查询代入进第二步内
select * from emp b inner join (select max(sal) msal,deptno from emp group by deptno) a on b.deptno-a.deptno and b.sal=a.sal;

9. case when，条件判断
   可以配合select工作，把一列的取值根据不同条件进行翻译，类似与java中的if else if
   语法：

case 
	when 条件1 then 结果1
	when 条件2 then 结果2
	...
	else 结果 n
	end

例：
对工资级别进行分类，2000以下为低工资，2000~3000为中等工资，3000以上显示高工资
select empno,ename,sal, 
case
	when sal<=2000 and sal > 0 then '低工资'
	when sal>2000 and sal<=3000 then '中等工资'
	when sal>3000 then '高工资'
	else '数据出现异常'
end 列别名：工资级别 from emp;

(三) DCL 数据控制语言

用来定义数据库的访问权限和安全级别，及创建用户。主要包括创建用户、给用户授权、对用户撤销授权、查询用户授权和删除用户等。

1. 创建用户

create user 用户名 identified by '密码';

2. 给用户授权

grant 权限1,权限2,...权限n on 数据库名.* to 用户名;
将所有权限授权给某个用户·：
grant all on 数据库名.* to 用户名;

3. 收回用户权限

revoke  权限1,权限2,...权限n on 数据库名.* from 用户名;

4. 查询用户权限

show grants for 用户名;

5. 删除用户

drop user 用户名;

四、事务和锁

(一) transaction 事务

把多条sql语句视为一个整体执行。这些sql语句要么都成功，其中若有一个失败了，则之前的操作也需要撤销。
mysql的事务控制：
默认情况下，是一条语句一个事务。要多条语句一个事务，需要通过以下语句来控制事务，

• begin（表示事务的起点，可以等价为：start transaction）
• commit（提交，表示结果都将生效）
• rollback（回滚，用来撤销事务内的更改）

begin;
第一条sql语句
第二条sql语句
...
commit; 或rollback; (表示事务的终结)

事务的四特性（ACID）：
原子性 A —— 多个sql要作为一个整体运行，不可分割
一致性 C —— 一个事务内结果应当一致
隔离性 I
持久性 D —— 事务一旦提交，事务内的修改就应当永久生效

(二) 锁

当多个客户端同时操作mysql数据时，为保证数据安全，mysql提供了锁来保证数据的安全。

1. InnDB 行级锁，只要两个客户端更新的是不同的行，则不会互相干扰。
2. MyISAM 表锁，是锁住整个表。
3. x锁：增删改（insert update delete）都会在行上加排他锁
4. s锁：查询操作可以加共享锁lock in share mode，可以支持多个客户端同时查询数据，但其他人不得执行增删改操作。

查询时加共享锁：
select * from 表 lock in share mode;  添加共享锁-别人可以再加共享锁，但不能再加排他锁

查询时加排他锁：
select * from 表 for update;   添加排他锁， -别人不能再加共享锁和排他锁

5. mysql对查询有特别优化，不用锁也能实现并发访问，称为多版本并发访问(MVCC)。
   mysql默认的查询方式就是利用多版本的并发查询，优点是并发性高。是通过建立副本的方式实现的，更新是一张表，查询是一张表，当更新数据后，查询操作还能查到旧的值，当修改事务提交后，会用副本代替旧的内容。
   java中的多版本并发访问：
   java.util.concurrent.CopyOnWriteArrayList 线程安全的ArrayList: 修改时会产生一个集合的副本，修改都是在副本上进行，查询查的是旧集合的内容，所以查询和修改可以并行执行，等修改结束会用副本替换旧的内容
   线程安全的List： Vector 全部方法加锁

(三) 事务的隔离性

事务的隔离有不同的隔离级别，
隔离级别越低，并发性越好，但数据的一致性差
隔离级别越高，并发性越差，但数据的一致性高

事务隔离级别由低到高分为四个级别：
读未提交 < 读提交 < 可重复读(mysql默认) < 序列化读

错误的隔离级别：
脏读 > 不可重复读 > 幻读

脏读 (读未提交)：

一边修改，另一边进行查询
7369的工资1000
事务1                                              事务2
begin;                                              begin;
修改7369的工资为8000
                                                   select * sal from emp where empno=7369;// 8000
rollback;

将隔离级别提高到`读提交`,可以避免脏读

不可重复读(读提交)：

一边查询，另一边做update操作
一个事务内多次查询结果不一致

7369的工资1000
事务1                                              事务2
begin                                              begin
                                                   select * sal from emp where empno=7369; // 1000
修改7369的工资为8000
commit;
                                                   select * sal from emp where empno=7369; // 8000
                                                   
要避免脏读、不可重复读：将隔离级别提高到`可重复读`隔离级别

幻读(可重复读)：

一边查询，另一边做insert操作

事务1                                              事务2
begin                                              begin
查询到10,20,30,40条记录
                                                   insert 50号部门
                                                   commit
insert 50号部门 报错，主键冲突

要避免脏读、不可重复读、幻读：将隔离级别提高到`序列化读`
所谓的`序列化读`就是把多版本并发退化到锁的并发控制：select语句上会被偷偷加上共享锁

五、其他sql总结

查看有哪些库： show databases;
查看库的创建语句： show create database 库名;
使用库： use 库名;
查看有哪些表：show tables;
查看表结构： desc 表名;
查看建表语句： show create table 表名;
\G可以取代分号;，把表行转列 select * from emp where empno =7369\G
查看系统变量的值：select @@系统变量名;

select @@transaction_isolation; 查看事务隔离级别
select @@port;                  查看服务器端口号
select @@character_set_server;  查看默认字符集
select @@secure_file_priv;      查看是否允许load data

show variables like `character%`; 查看具体以那一部分开头的那一部分系统变量