MySQL数据库

SQL学习

注：自己学习数据库的笔记，用来自己复习，谨慎参考
数据库知识还要不断学习。。。

---

前言

MySQL在设计开发中经常使用，三层结构如下：

一、SQL语句分类

DDL： 数据定义

DML：数据操作

DQL：数据查询

DCL：数据控制

二、常用的数据库语句

1、开启/关闭数据库服务

net start mysql

net stop mysql

2、备份数据库

这里以备份db01和db02为例，备份到D盘以bak.sql文件存储。使用管理员模式进入doc命令。见下图代码：

mysqldump -u root -p -B db01 db02 > d:\\bak.sql

如果只想备份该数据库中的一部分表，则在对应的数据库名后加入表名。这里以备份数据库db02表名 t2 为例，保存到D盘

mysqldump -u root -p  db02 t2  > d:\\bkg1.sql

3、恢复数据库

如删除了数据dbo2,则必须进入到doc下的mysql命令下（通过mysql -u root -p 进入mysql命令），输入以下数据库恢复语句恢复数据库。

source d:\\bak.sql    #这里的bak.sql为之前备份过数据库的文件

MySQL root密码忘记了怎么办？

在MySQL中，若密码丢失则无法直接找回，只能通过特殊方式来修改密码。
1.Linux环境
步骤1：先停止MySQL服务
步骤2：启动 MySQL 服务
mysqld_safe --defaults-file=/etc/my.cnf --skip-grant-tables &
2.Windows环境
若 MySQL 是 8.0 且安装在 Windows 上，则需要加上–shared-memory 参数：
G:\mysql-8.0.23-winx64\bin\mysqld --datadir=G:\mysql-8.0.23-winx64\data80323308 --console --skip-grant-tables --shared-memory
然后再开一个窗口，执行下面命令
cd G:\mysql-8.0.23-winx64\bin
注意，此时可以以任意一个密码登陆也可以以一个空密码登陆 MySQL，
登陆之后你就可以修改你的root密码喽

3.1补充数据类型以及时间戳

时间戳timestamp

#建表时使用时间戳，添加数据时如果不单独插入对应字段，默认为当前时间，更新数据时，时间戳也会更新为当前时间段
login_time TIMESTAMP NOT NULL 
DEFAULT CURRENT_TIMESTAMP 
ON UPDATE CURRENT_TIMESTAMP

4、数据库下建表

（1）进入数据库：输入mysql -u root -p 回车后输入密码登录
（2）查看已存在的数据库：show databases;回车
（3）使用数据库(这里使用数据库db02)：use db02;回车
（4）在数据库db02下创建表table_02,该表有INT型的id，VARCHAR的name。回车（注：当表名和一些关键字重复，在表名前后加上单反引号）

CREATE TABLE `table_02`(id INT,`name` VARCHAR(255));

如果创建一个table_0201，字符集为utf8，排序规则为utf8_bin，引擎INNODB,语句为

CREATE TABLE table_0201 ( 
					id INT, 
					NAME VARCHAR (255), 
					ttime datetime ) CHARSET utf8 COLLATE utf8_bin ENGINE INNODB

（5）给表talbe_02增加值，

insert into table_02 values(23,'ed');

（6）修改表的列操作
注：显示表的结构，能够查看表的所有列，如查看table_02

DESC table_02;

（a）给表增加列，比如：在（4）中name的后增加一个varchar(32)的job，job非空，默认值为空字符。

ALTER TABLE table_02 ADD `job` varchar(32) 

NOT NULL DEFAULT '' AFTER name;

（b）修改列,将job修改为varchar(60)

ALTER TABLE table_02 MODIFY `job` VARCHAR(60) 

NOT NULL DEFAULT '';

（c）删除列（谨慎使用）

ALTER TABLE table_02 DROP `job`;

（7）修改表名，将table_02表修改为table_2;

ALTER TABLE table_02 to table_2;

（8）修改表的字符集为utf8

ALTER TABLE table_2 CHARACTER SET utf8;

（9）将table_2中的列/字段name 修改为user_name

ALTER TABLE table_2 CHANGE `name` `user_name` VARCHAR(32) NOT NULL DEFAULT '';

三、增删改

CRUD(create read update delete)
注：创建一个表名为table_33的表

1、给表增加数据

INSERT INTO `table_33`(id,name_s,hobby )VALUES(10,'张三','足球');

2、更新数据

（1）没有where进行限制

UPDATE `table_33` 	SET hobby = '篮球'; #将该表中所有用户的爱好都修改为篮球1

（2）用where限制

UPDATE `table_33` SET hobby = '乒乓球' WHERE id = 10;	

3、删除数据

（1）删除该表中所有数据

DELETE FROM `table_33` ;

（2）删除hobby为乒乓球所在行的数据

DELETE FROM `table_33` WHERE hobby = '乒乓球';

注：如果只想删除乒乓球那一个属性，可以使用更新语句将那个地方置空。 DROP TABLE table_33是删除该表。
三者都表示删除，但是三者有一些差别：
在不再需要一张表的时候，用drop；
在想删除部分数据行时候，用delete；
在保留表而删除所有数据的时候用truncate。

四、查询语句

（1）查询表中所有数据

select * from 

ASC DESC distinct like count() sum()
常用查询语句：
（1）限制页 limit 0,3;
（2）查询去掉重复distinct
（3）多子句查询中，优先级group by > having >order by >limit
（4）多表查询，当将一张表当作两张表来使用时，需要给该表增加别名，并用表名加上需要调用的属性
（5）自我复制.如复制table_33
（6）左外连接left join on

insert into `table_33
select * from `table_33

（7）主键
primary key是不允许表中有重复的，通常会跟自增长一起使用auto_increment 。
（8）函数
count 其中count(*)会统计一共多少列,count(字段）会排除该字段为null的数据个数

五、索引

1、增加索引

给表table_12中的name列增加索引，索引名为index_name

create index index_name on table_12(name);

或者

alter table table_12 add index index_name(name); 

2、删除索引

drop index index_name on table_12;

3、修改索引

先删除索引，然后创建新的索引

4、查询索引

从表table_12查询索引
a.方法一：

show index from table_12;

b.方法二：

show indexes from table_12;

c.方法三：

show keys from table_12;

六、事务

1、事务执行步骤

（1）创建事务start transaction或者set autocommit = off;
（2）设立保存点savepoint a
（3）回退到a ：rollback to a
回退到创建事务处：rollback
（4）提交事务: commit

2、事务细节如下:

• 保存点可以创建多个。
• 一旦从现在时刻回退到某个保存点，那从现在时刻到保存点中间的所有保存点都会被失效。
• MySQL事务机制需要innodb存储引擎才可以使用

3、事务隔离级别

前提：离开了事务就不谈隔离级别，需要两个以及两个以上事务才需要考虑隔离级别。

• MySQL隔离级别主要有四种：
  读未提交（Read uncommitted） 脏读、不可重复读、幻读、不加锁读

  读已提交（Read committed ) 脏读、不加锁读

  可重复读（Repeatable read） 不加锁读

  可串行化（Serializable ）
  [可串行化是加锁读，其它三个不加锁读。]

• 注：
  脏读：操作过程中读到另一个事务没提交的数据

  不可重复读：操作过程中读到另一个事务对数据进行删除或者修改的操作且已提交

  幻读：操作过程中读到另一个事务对数据进行插入的操作且已提交。

  加锁读：查询时另一个事务没有提交，本事务在查询数据库表时会卡住，待另一个事务提交了会自动执行。

查询当前会话隔离级别
SELECT @@tx_isolation;
查询系统当前隔离级别
select @@global.tx_isolation
设计****当前会话隔离级别(如可重复读)
set session transaction isolation level repeatable read;
设计****系统隔离级别(如可重复读)
set global transaction isolation level repeatable read;

4、事务ACID特性

原子性（atomicity）、一致性(consistency)、隔离性(isolation)、持久性(durability

七、引擎

INNODB 、MYISAM、MEMORY
innodb是支持外键和事务
memory存在内存中。

八、视图

（1）创建视图：对stu表查询姓名年龄建立视图

create view stu_view as select `name`,`year` 

from stu;

（2）查询视图：show create view stu_view;
（3）删除视图：drop view stu_view;

九、用户管理

1、创建用户

创建一个test_1的用户,本地主机，密码为123456

CREATE USER 'test_1'@'localhost' IDENTIFIED BY '123456';

2、查询用户权限

SELECT `host`,`user`,authentication_string
	FROM mysql.user

3、删除用户

DROP USER 'test_1'@'localhost';

4、修改用户密码

（1）test_1用户自己修改

set password = password('123123');

（2）root用户修改test_1的密码

set password for 'test_1'@'localhost' = password('123123');

5、给用户授权

（1）root用户给test_1用户授予对数据库（如数据库db下的表db_test）插入更新操作

grant insert,update on db.db_test to  'test_1'@'localhost' ;

（2）回收（1）中的授权

revoke insert,update on db.db_test from 'test_1'@'localhost'

九、MySQL细节（常用数学函数）

表示等于用一个=

• decimal(M,D),M为总位数，D为小数位数 ，其中存入数据，decimal会自动补0 M最大65，D最大30
  需要增加索引，create index 索引名 on 表名（列名），
• ifnull(a,0) 如果a处为空则返回第二个表达式，不为空返回a本身
• LAST_DAY(NOW()) 查询当前时间月最后一天
• 比如求查询db_membe表雇佣时间超过10年的员工

select *
	from db_member
    where date_add(hiredate,interval 10 year) < now();

• replace(str,'A','a') 用a代替A
  每页显示5条，第五页则是:limit 20,5;