MySQL完整版详解

一、数据库的操作

1.创建数据库

如果您在可视化软件上创建数据库，请参阅下图

[En]

If you create a database on a visualization software, refer to the following figure

如果要创建的数据库不存在，则创建成功

[En]

If the database you want to create does not exist, the creation is successful

create database if not exists westos;

2.删除数据库

drop database if exists westos;

3.使用数据库

use tmalldemodb;
//tab键的上面，如果你的表名或者字段名是一个特殊字符，就需要带

4.查看数据库

show databases;

5.清空当前命令行界面命令

clear;

6.创建表

• auto_increment：自增
• 字符串使用单引号括起来
• 所有句子后面都有逗号，在英语中，最后一句不加
  
  [En]

  all sentences are followed by a comma, in English, the last one is not added*

• primary key主键，一般一个表只有一个唯一的主键，且必须要有
• engine=innodb：数据库表的引擎

mysql> create table if not exists student(    -> id int(4) not null auto_increment comment '学号',    -> name varchar(30) not null default '匿名' comment '姓名',    -> pwd varchar(20) not null default '123456' comment '密码',    -> sex varchar(2) not null default '女' comment '性别',    -> birthday datetime default null comment '出生日期',    -> address varchar(100) default null comment '家庭地址',    -> email varchar(50) default null  comment '邮箱',    -> primary key(id)    -> )engine=innodb default charset=utf8;

创键成功之后的图

7.常用命令

(1)查看创建数据库的语句

show create database 数据库名;

运行结果图

(2)查看表的创建语句

show create table 表名;

(3)显示表的结构

desc 表名；

8.数据表的类型

(1)数据库的引擎

innodb //默认使用
myisam//早些年使用的

• 事务：假设有两个SQL都执行，要么都成功，要么都失败，如果有一个成功一个失败，它就提交不上去
• 数据行锁定：假设有两条SQL去查同一个表，他会把这个表先锁住，剩下的数据再查的时候就要排队等待
• 外键约束：将一个表连接到另一个表
  
  [En]

  Foreign key constraints: connect one table to another*

• 全文索引：例如，根据您输入的关键字(在数据库中称为字段)在百度上搜索想要的结果。
  
  [En]

  full-text index: for example, search for the desired results on Baidu according to the keywords you enter (called fields in the database).*

myisam innodb 事务支持 不支持 支持 数据行锁定 不支持 （支持表锁） 支持 外键约束 不支持 支持 全文索引 支持 不支持 表空间的大小 较小 较大，约为myisam的2倍

常规使用操作：

• myisam 节约空间，速度较快
• innodb安全性高，事务的处理，多表多用户操作

(2)在物理空间存在的位置

所有的数据库文件都存在data目录下 本质还是文件的存储！

9.修改和删除表的字段

(1)修改表名

alter table 旧表名 rename as 新表名;

alter table teacher rename as teacher1;

(2)增加表的字段

alter table 表名 add 字段名 列属性;

alter table teacher1 add age int(11);

(3)修改表的字段（重命名，修改约束）

①修改约束modify(不能重命名)： alter table 表名 modify 字段名 新的列属性;

alter table teacher1 modify age varchar(11);

②字段重命名(您可以重命名该字段或修改约束)

[En]

② field renaming (you can either rename the field or modify the constraint)

alter table 表名 change 旧字段名 新字段名 列属性;

alter table teacher1 change age age1 int(11);

(4)删除表的字段

alter table 表名 drop 字段名;

alter table teacher1 drop age1;

(5)删除表

如果要删除的表存在，则删除 drop table if exists 表名;

drop table if exists teacher1;

应尽可能判断所有创建和删除操作，以避免上报错误。

[En]

All creation and deletion operations should be judged as far as possible to avoid reporting errors.

二、列的数据类型详解

1.数值

• tinyint 十分小的数据 1个字节
• smallint 较小的数据 2个字节
• int 标准的整数 4个字节 常用
• bigint 较大的数据 8个字节
• float 浮点数 4个字节
• double 浮点数 8个字节
• decimal 字符串形式的浮点数

2.字符串

• char 字符串固定的大小 0-255
• varchar 可变字符串 0-65535 常用
• tinytext 微型文本 2^8-1
• text 保存大文本 2^16-1

3.时间和日期

• date 日期格式：YYYY-MM-DD
• time 时间格式：HH:mm:ss
• datetime 日期格式：YYYY-MM-DD HH:mm:ss 常用
• timestamp 时间戳，从1970.1.1至现在的毫秒数 常用
• year 年份表示

4.null

• 没有值，未知
• 注意，不要使用NULL进行运算，结果为NULL

三、数据库的字段属性（重点）

1.unsigned

• 无符号的整数
• 不能声明为负数

2.zerofill

• 0填充的
• 假设你现在要写一个长度为10的int类型，但是你只写了个1，则他会用自动给你在1前面填充9个零

3.自增

• 通常理解为自我递增，根据之前的记录自动+1(默认)
  
  [En]

  usually understood as self-increment, automatically + 1 based on the previous record (default)*

• 通常用来设计唯一的主键~index,必须是整数类型
• 可自定义自增主键的起始值和步长
  
  [En]

  you can customize the starting value and step size of the self-increasing primary key*

4.非空

• 假设设置为not null ，如果不给它赋值，就会报错！
• Null,如果不填写值，默认就是null！

5.默认

• 设置默认的值
• sex,默认值为男，如果不指定该列的值，则会有默认的值！
• 设置默认的值

四、MySQL数据管理

1.外键(了解即可)

删除具有外键关系的表时，必须先删除单词TABLE，然后才能删除父表

[En]

When you delete a table with a foreign key relationship, you must delete the word table before you can delete the parent table

2.DML语言（全部背住）

• insert
• update
• delete

3.添加

insert into 表名(字段名1，字段名2，字段名3，….) value(值1，值2，值3，….) ==注意：一般写插入语句，我们一定要数据和字段一一对应！==

(1)插入一行数据

insert into grade(gradename) value('大三');

(2)插入多行数据

==注意：给一个字段添加多行值时，每个值都用括号括起来，且中间用逗号隔开。==

insert grade(gradename) value('大二'),('大一');

insert into student(name,pwd,sex)  values('张三','aaaa','男'),('李四','vvvv','女');

4.修改

(1)修改一条数据

格式：update 表名 set colnum_name = value where 条件 下面这行代码的意思是将student表中id=2的name值设置为TWQ

update student set name='TWQ' where id =2;

(2)修改多条数据

条件：where子句，运算符id等于某个值，大于某个值，或在某个区间内修改 操作符 含义 范围 结果 = 等于 5=6 false <>或！= 不等于 5<>6 true < 小于 5

5.删除

(1)delete命令

语法：delete from 表名 where 条件;

delete from student where id =1;

(2)truncate命令

功能：完全清空一个数据库表，表的结构和索引不会改变

[En]

Function: completely empty a database table, the structure and index of the table will not change

truncate table student;

(3)truncate和delete命令区别

• 同一点：所有都可以删除数据，但不会删除表结构。
  
  [En]

  same point: all can delete data, but will not delete table structure.*

• 不同：①truncate重新设置自增列，计数器会归零。②truncate不会影响事务
  使用delete删除表中所有数据，不会影响自增的值
  拓展（了解即可）关于delete删除的问题：删除后重启数据库
• 若该表使用的引擎为innodb，重启数据库后，自增列会从1开始（存在内存当中断电即失）
• 若采用的引擎是myisam 重启数据库后，会继续从上一个自增变量开始（存储在文件中，不会丢失）

delete from grade;

使用truncate删除表中所有数据，自增变量会归零

truncate table grade;

五、DQL查询数据（重点中的重点）

select语法

5.1指定查询字段

(1)查询某个表中全部的数据

如查询student表中所有的数据

select * from student;

(2)查询某个表中指定字段

①查询student表中name和pwd字段的值

select name,pwd from student;

②给字段或表名起别名(as)

select name as 姓名,pwd as 密码 from student;

(3)函数concat(a,b)

select concat('姓名:',name) as 新名字 from student;

(4)去重distinct

①查询哪些学生参加了考试(可能有学生参加了多个科目，导致重复数据需要重复)

[En]

① queries which students have taken the exam (there may be a student who has taken multiple subjects, resulting in duplicate data that needs to be repeated)

select distinct Student_id from result;

(5)查询系统版本

select version();

(6)用来计算

select 100*3-1 as 计算结果;

(7)查看学生成绩+1分之后的结果

select Student_id,StudentResult+1 as 加一分后 from result;

5.2 where条件子句

功能：为了检索数据中符合条件的值，搜索条件由一个或多个表达式组成！结果是一个布尔值

[En]

Function: to retrieve the eligible values in the data the search condition consists of one or more expressions! The result is a Boolean

(1)逻辑运算符

运算符 语法 描述 and a and b 逻辑与 or a or b 逻辑或 Not not a 逻辑非

(2)where的运用

①查询23(学科编号)这个学科成绩在95到100之间的学生

//方式一：
select student_id  as 学生编号,studentresult as 分数 from result
where studentresult>=95 and studentresult<=100 95 100 and subject_id="23;" 方式二： select student_id as 学生编号,studentresult 分数 from result where studentresult between < code></=100>

(3)模糊查询：比较运算符

运算符 语法 描述 is null a is null 如果操作符为null,结果为真 is not null a is not nul 如果操作符为不为null,结果为真 between a between b and c 若a在b和c之间，则结果为真 like a like b sql匹配，如果匹配b,则结果为真 in a in(a1,a2,a3,…..) 假设a在a1,或者a2….其中的一个值中，结果为真

①查询姓唐的同学
like 结合 %(代表0到任意个字符) _（一个字符）

select name,birthday from student
 where name like '唐%';

②查询姓唐的同学,并且名字后面仅有一个字的

select name,birthday from student
 where name like '唐_';

③查询姓唐的同学,并且名字后面仅有个字的

select name,birthday from student
 where name like '唐__';

④查询姓名包含单词JIA%JIA%的学生

[En]

④ query students whose names contain the word Jia% Jia%

select name,birthday from student
 where name like '%嘉%';

⑤查询id为3,4,5号的学生

select name,birthday from student
 where id in (3,4,5);

⑥询问居住在宁波和上海的学生

[En]

⑥ inquires students living in Ningbo and Shanghai

select name,address from student
 where address in ('宁波','上海');

⑦查询邮箱为空的学生

select name,email from student
 where email is null;

⑧查询邮箱不为空的同学

select name,email from student
 where email is not null;

5.3联表查询

(1)join对比

①查询参加了考试的同学（学号，姓名，科目编号，分数）使用inner join来查询
思路：

• 分析需求，分析查询的字段来自哪些表，(Join Query)
  
  [En]

  analyze the requirements, analyze which tables the fields of the query come from, (join query)*

• 确定使用哪种连接查询？ 7种
• 确定交叉点（这两个表中那个数据是相同的）判断条件：学生表中的student_id = 成绩表的student_id

--  这里给student表和result表起别名，就是因为这两张表中都有student_id这个属性，为了不扯皮，所以起别名并写明用哪个表的idselect s.student_id,name,subject_id,studentresultfrom student as sinner join result as rwhere s.student_id=r.student_id;

②使用right join来实现上述操作
join（连接的表） on(判断的条件) 连接查询
where 等值查询

select s.student_id,name,subject_id,studentresult
from student  s
right join result  r
on s.student_id=r.student_id;

③使用left join操作上述问题

select s.student_id,name,subject_id,studentresult
from student  s
left join result  r
on s.student_id=r.student_id;

由结果可知left join会将没有参与考试的学生也查了出来

操作 描述 inner join 如果表中至少有一个匹配，就返回行（hang） left join 会从左表中返回所有的值 ，即使右表中没有匹配 right join 会从右表中返回所有的值 ，即使左表中没有匹配 ④查询缺考的同学

select s.student_id,name,subject_id,studentresult
--  起别名的时候as也可以省略
from student  s
left join result  r
on s.student_id=r.student_id
where studentresult is null;

⑤查询参加考试的学生的信息：学号、姓名、科目名称、分数

[En]

⑤ inquires the information of the students who have taken the exam: student number, name, subject name, score

思路：

• 分析需求，分析查询的字段来自哪些表，student，result，subject（连接查询）
• 确定使用哪种连接查询？ 7种
• 确定交叉点（这两个表中那个数据是相同的）判断条件：学生表中的student_id = 成绩表的student_id ，成绩表中的subject_id =科目表中的subject_id;

select s.student_id,name,subject_name,studetresult
from student s
right join result r
on s.student_id=r.student_id
inner join subject sub
on r.subject_id=sub.subject_id;

⑥查询参加高等数学考试的学生的信息：学号、姓名、科目名称、分数

[En]

⑥ inquires the information of the students who have taken the higher mathematics exam: student number, name, subject name, score

select s.student_id,name,subject_name,studentresult
from student s
right join result r
on s.student_id=r.student_id
inner join subject sub
on r.subject_id=sub.subject_id
where subject_name ='高等数学';

总结：

• 我要查询哪些数据 select…..

• 从哪几个表中查 from 表 xxx join 连接的表 on 交叉条件

• 假设有一个多表查询，慢慢来，先查询两个表，然后慢慢增加
  
  [En]

  suppose there is a multi-table query, take your time, query two tables first, and then slowly increase*

(2)自连接

将您自己的表连接到您自己的表core：将一个表拆分为两个相同的表，然后拆分以下表

[En]

Connect your own table to your own table, core: split a table into two identical tables and split the following table

父类（找pid等于1的为一棵树的最顶端）

categoryid categoryname 2 信息技术 3 软件开发 5 美术技术

子类

pid categoryid categoryname 3 4 数据库 3 6 web 5 7 ps技术 2 8 办公信息

①操作：查询父类对应的子类关系

[En]

① operation: query the subclass relationship corresponding to the parent class

父栏目 子栏目 信息技术 办公信息 软件开发 数据库 软件开发 web 美术技术 ps技术

采用联表查询得到上表只需要让父类的categoryid等于子类的pid即可

//将一张表看成一模一样的表
select a.categoryname as 父栏目,b.categoryname as 子栏目
from category as a,category as b
where a.categoryid = b.pid;

5.4分页(limit)和排序(order by)

(1)排序

①升序asc

查询参加考试的学生信息(姓名、科目、分数)，对分数从小到大进行排序。

[En]

Query the information of the students who have taken the exam (name, subject, score) and sort the scores from small to big.

select  name,subject_name,studentresult
from student s
inner join result r
on s.student_id=r.student_id
inner join subject sub
on r.subject_id = sub.subject_id
order by studentresult asc;

②降序desc

只需在上述例子中将asc改为desc即可

(2)分页

语法：==limit(查询起始下标，pagesize)==

第一页： limit 0,5 起始查询下标:(1-1) _5
第二页： limit 5,5 起始查询下标:(2-1)_5
第三页： limit 10,5 起始查询下标:(3-1) _5
第N页： limit (n-1)_5,5 起始查询下标:(n-1)*pagesize

pagesize:页面大小
（n-1)*pagesize:起始查询下标
n:当前页
数据总数/页面大小 = 总页数

5.5子查询

①查询高等数学 的所有考试结果（学生id,科目编号，成绩）降序排列 方式一：使用连接查询

select student_id,r.subject_id,studentresult
from result r
inner join subject sub
on r.subject_id=sub.subject_id
where subject_name='高等数学'
order by studentresult desc;

方式二：使用子查询

select student_id,subject_id,studentresult
from result
where subject_id =(
  select subject_id
  from subject
  where subject_name='高等数学'
);

六、MySQL函数

1.常用函数

①数学运算

--  绝对值
select abs(-8)

-- 向上取整
select ceiling(9.4)

-- 向下取整
select floor(9.4)

--  返回一个0-1之间的随机数
select rand()

-- 判断一个数的符号 0-0 返回-1，正数返回1
select sign（10）

②字符串函数

-- 字符串长度
select char length('发来的法计算')

-- 拼接字符串
select concat('我','adf','asd')

-- 查询，从某个位置开始替换某个长度
select insert('我爱变成helloworld',1,2,'超级热爱')

-- 小写字母
select lower('KjfdsSJK')

-- 大写字母
select upper('KjfdsSJK')

-- 返回第一次出现的子串的索引
select instr('twq123','3')

-- 替换出现的指定字符串
select replace('坚持就是胜利','坚持','努力')

-- 返回指定的字符串(源字符串，截取的位置，截取的长度)
select substr('坚持就是胜利',3,2)

-- 反转
select reverse('清晨我上马')

运用
将一位姓唐的同学的姓氏改为唐

[En]

Change the surname of a classmate surnamed Tang to Tang

select replace(name,'唐','汤') from student
where name like '唐%';

③时间和日期函数

-- 获取当前的日期
select current date()

-- 获取当前日期
select curdate()

-- 获取当前的时间
select now()

-- 获取本地时间
select localtime()

-- 获取系统时间
select system()

select year(now())
select month(now())
select day(now())
select hour(now())
select minute(now())
select second(now())

--系统
select system_usr()
select user()
select version()

2.聚合函数(常用)

函数名称 描述 count() 计数 sum() 求和 avg() 平均值 max() 最大值 min() 最小值 ….

查询student表中一共有多少人

-- 都能够统计，表中的数据(想查询一个表中有多少个记录，就是用这个count())
select count(name) from student -- count(字段)，会忽略所有的null值
select count(*) from student; -- count(*) 不会忽略null值 ,本质在计算行数
select count(1) from result; -- count(1) 不会忽略null值 ,本质在计算行数

select sum(studentresult) as 总和 from result
select avg(studentresult) as 平均分 from result
select max(studentresult) as  最高分 from result
select min(studentresult) as  最低分 from result

-- 查询不同课程的平均分，最高分，最低分
-- 核心：（根据不同的课程分组）
select subject_name, avg(studentresult),max(studentresult),min(studentresult)
from result r
inner join subject sub
on r.subject_id =sub.subject_id
group by r.subject_id -- 通过什么字段来分组
having 平均分>80; -- 分组之后再添加条件必须使用having，详见select语法的图

注意：执行以上命令可能会如果没有修改MySQL的mode将会报以下错误

1055 – Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘school.sub.subject_name’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
解决方式：
在Navicat或者sqlyog的命令中执行以下命令即可

SET sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

5.3数据库级别的MD5加密(扩展)

-- 明文密码
insert into testmd5 values(1,'zhanshan','123456'),(2,'lisi','abcdefg'),(3,'wangwu','asdfgh');

-- 加密
update testmd5 set pwd=MD5(pwd) where id =1; -- 对单个数据进行加密

update testmd5 set pwd=MD5(pwd); -- 加密全部数据

-- 插入的数据的时候加密
insert into testmd5 values(4,'xiaming',MD5('567890'));

七、事务

7.1什么是事务

要么都成功，要么都失败就是下面这个转账的事务，必须要子啊两条SQL执行完成之后该事务才算结束，体现事物的原子性

①、SQL执行 A给B转账 A最开始有1000 B最开始有200 A给B转200
②、SQL执行 B收到A的钱 此时A有800 B有400

7.2事务原则

ACID 原则：原子性，一致性，隔离性，持久性

(1)一致性

还是A给B转账这个事，事务完成后，符合逻辑运算也就是总钱数始终是1200

(2)隔离性

所谓独立性是指并发(多个用户同时操作)事务不会相互影响。如果一个事务访问的数据正在被另一个事务修改，则只要另一个事务未提交，它访问的数据就不会受到未提交事务的影响。隔离造成的一些问题：

[En]

The so-called independence means that concurrent (multiple users operating at the same time) transactions do not affect each other. If the data accessed by one transaction is being modified by another transaction, as long as the other transaction is not committed, the data it accesses is not affected by uncommitted transactions. Some of the problems caused by isolation:

①脏读：

一个事务从另一个事务中读取未提交的数据。

[En]

One transaction reads uncommitted data from another transaction.

②不可重复读：

在事务内读取表中的一行数据将多次导致不同的结果。(这不一定是错误，但在某些情况下是错误的)。

[En]

Reading a row of data in a table within a transaction will result in different results multiple times. (this is not necessarily an error, but in some cases it is wrong).

③虚读（幻读）：

这意味着由另一个事务插入的数据在一个事务内被读取，导致读取不一致。

[En]

It means that the data inserted by another transaction is read within one transaction, resulting in inconsistent reading.

(3)持久性

事务被持久化到数据库，因为提交是不可逆的

[En]

The transaction is persisted to the database because the commit is irreversible

（4）事务的实际操作

-- MySQL是默认开启事务自动提交的
set autocommit =0  -- 关闭
set autocommit =1; --开启（默认的）

-- 手动处理事务
set autocommit =0 -- 关闭自动提交

-- 事务开启
start transaction -- 标记一个事务的开始，从这个之后的sql都在同一个事务内

-- 提交：持久化（成功）
commit

-- 回滚：回到原来的样子（失败）
rollback

-- 事务结束
set autocommit=1 -- 开启自动提交

（5）模拟事务场景

-- 创建一个转账的数据库
create database shop character set utf8 collate utf8_general_ci;

-- 在此数据库中创建表
create table account(
    id int(3) not null auto_increment,
    name varchar(20) not null,
    money decimal(9,2) not null,
    primary key(id)
    )engine=innodb default charset=utf8;

--往表里添加数据
insert into account(name,money)
values('A',2000.00),('B',10000);

--模拟转账，事务
set autocommit =0; -- 关闭自动提交
start transaction; --开启一个事务
update account set money=money-500 where name='A'; -- A减500
update account set money=money+500 where name='B'; -- B加500

commit; -- 提交事务
rollback; -- 回滚

set autocommit =1; -- 开启自动提交

八、索引

索引的定义：索引是帮助MySQL高效获取数据的数据结构，
通过提取句子的主干，可以得到索引的本质：索引就是数据结构。

[En]

By extracting the trunk of the sentence, you can get the essence of the index: the index is the data structure.

8.1索引的分类

在一个表中，只能有一个主键索引和多个唯一索引。

[En]

In a table, there can be only one primary key index and multiple unique indexes.

• 主键索引(primary key)
• 唯一的标识，主键不可重复，只能有一个列作为主键

*唯一索引 (unique key)

• 避免重复列，唯一索引可重复，多列可标识唯一索引
  
  [En]

  avoid duplicate columns, unique indexes can be duplicated, and multiple columns can identify unique indexes*

• 常规索引（key / index）
• 默认的，index，key关键字来设置
• 全文索引（fulltext）
• 快速定位数据

(1)索引的使用

-- 索引的使用-- 1.在创建表的时候给字段增加索引-- 2.创建完毕后，增加索引show index from student; -- 显示所有的索引信息-- 增加一个全文索引(索引名) 列名alter table school.student add fulltext index name (name);-- explain 分析sql执行的状况explain select * from student;-- 非全文索引explain select * from student where match(name) against('唐');

给student表中的name添加字段之后结果图

8.2测试索引

-- 插入100万条数据
-- 写函数之前必须要写，标志
delimiter $$
create function mock_data()
returns int
begin
  declare num int default 1000000;
  declare i int default 0;

  while i

执行上述函数可能会报告以下错误

[En]

Executing the above function may report the following error

1418 – This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you might want to use the less safe log_bin_trust_function_creators variable)
解决方式：
临时解决方法（重启MySQL后失效）：
在命令行界面中执行以下命令

[En]

Execute the following command in the command line interface

set global log_bin_trust_function_creators=TRUE;

永久解决方法：
在配置文件/etc/my.cnf的[mysqld]配置log_bin_trust_function_creators=1

8.3索引的原则

• 索引不是越多越好
• 不为流程更改数据编制索引
  
  [En]

  do not index process change data*

• 数据量较小的表不需要编制索引
  
  [En]

  tables with small amounts of data do not need to be indexed*

• 索引一般添加到查询常用的字段中
  
  [En]

  the index is generally added to the fields commonly used for query*

(1)索引的数据结构

Hash类型的索引 Btree：innodb的默认数据结构

九、权限管理和数据库备份

1.SQL命令操作

用户表：mysql.user 本质：读这张表进行增删改查

-- 创建用户create user 用户名 identified by '密码';
create user jianduan identified by '123456';

--修改密码（修改当前用户密码）
set password = password('123456');

--修改指定用户密码
setpassword for jianduan = password('111111');

--给用户重命名rename user 原来名字 to 新名字;
rename user jianduan to jianduan2;

--用户授权（最高没有root高）all  privileges  on  *.*(库点表，*表示所有的库和表)  to  用户名;
-- all  privileges除了给别人授权，其它都能干
grant  all  privileges  on  *.*  to  jianduan2;

-- 查询权限
show grants for jianduan2; -- 查看指定用户的权限
show grants for root@localhost;

--撤销权限 revoke 哪些权限 ，在哪个库撤销，给谁撤销
revoke all privileges on  *.*  from jianduan2;

用户创建成功之后的图

2.MySQL备份

MySQL备份的方式：

• 直接拷贝物理文件
• 在sqlyog或Navicat这种可视化工具手动导出
  
• 使用命令行导出 mysqldump 命令行界面(windows的cmd或Mac的终端)使用

-- mysqldump  -h主机  -u用户名  -p密码  数据库名  表名  >物理磁盘位置/文件名
mysqldump -hlocalhost -uroot -proot123456 school student >/Users/twq/Downloads/a.sql

-- mysqldump  -h主机  -u用户名  -p密码  数据库名  表1  表2  表3  >物理磁盘位置/文件名
mysqldump -hlocalhost -uroot -proot123456 school student result >/Users/twq/Downloads/b.sql

-- mysqldump  -h主机  -u用户名  -p密码  数据库名   >物理磁盘位置/文件名
mysqldump -hlocalhost -uroot -proot123456 school  >/Users/twq/Downloads/c.sql

--将表导入指定的数据库（在终端或则cmd界面需要先登录(mysql -u用户名 -p密码)然后输入以下命令）
source /Users/twq/Downloads/b.sql

十、三大范式(了解)

10.1第一范式(1NF)

原子性：确保每列不能再分开

[En]

Atomicity: ensure that each column can no longer be separated

10.2第二范式(2NF)

前提：满足第一范式 每张表只描述一件事情

10.3第三范式(3NF)

前提：满足第一范式和第二范式

[En]

Premise: satisfy the first paradigm and the second paradigm

10.4规范性和性能的问题

关联查询的表不能超过三个表

[En]

The table of an associated query must not exceed three tables

• 考虑商业化的需求和目标(成本、用户体验！)数据库的性能更重要
  
  [En]

  consider the needs and goals of commercialization (cost, user experience!) The performance of the database is more important*

• 在标准化性能问题时，需要适当考虑以下规范
  
  [En]

  when standardizing performance issues, the following specifications need to be properly considered*

• 故意在某些表中添加冗余字段。(从多表查询到单表查询)
  
  [En]

  deliberately add redundant fields to some tables. (from a multi-table query to a single-table query)*

• 故意增加一些计算列(从大数据量降低为小数据量的查询：索引)

十一、JDBC

SUN公司为了简化开发人员的(对数据库的统一)操作，提供了一个Java操作数据库的规范，俗称JDBC。这些规范的实现由具体的厂商去做 对于开发人员来说

11.1导入mysql-connector的jar包

(1)进入官网下载

https://downloads.mysql.com/archives/c-j/

根据自己MySQL的版本选择对应版本的jar包

(2)拷贝jar包

(3)粘贴到IDEA的lib目录下

如果没有lib目录可新建一个

(4)将lib目录添加为库

11.2第一个JDBC程序

package com.Tang.jdbc;

import java.sql.*;

public class jdbcTest {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //1.加载驱动
       // Class.forName("com.mysql.jdbc.Driver");//固定写法，我现在的MySQL版本是8.0.28，在新版本里他是会自动加载驱动，因此没必要手动加载驱动
        //2.用户信息和url
        String url = "jdbc:mysql://localhost:3306/school?useUnicode=true&&characterEncoding=utf8&&useSSL=true";
        String username="root";
        String password="root123456";
        //3.连接成功，数据库对象   connection代表数据库
        Connection connection = DriverManager.getConnection(url, username, password);

        //4.执行SQL对象  statement执行SQL的对象
        Statement statement=connection.createStatement();
        //5.使用SQL的对象去执行SQL，可能存在结果，查看返回结果
        String sql = "select * from student";
        ResultSet resultSet = statement.executeQuery(sql);//返回的结果集,结果集中封装了我们全部的查询出来的结果，只有查询才会有结果集，更新(增加，删除，修改)没有返回值。

        while(resultSet.next()){
            //以下的字段名必须和数据库表中的字段名一一对应

            System.out.println("student_id=" + resultSet.getObject("student_id"));
            System.out.println("name=" + resultSet.getObject("name"));
            System.out.println("pwd=" + resultSet.getObject("pwd"));
            System.out.println("sex=" + resultSet.getObject("sex"));
            System.out.println("birthday=" + resultSet.getObject("birthday"));
            System.out.println("address=" + resultSet.getObject("address"));
            System.out.println("email=" + resultSet.getObject("email"));
            System.out.println("=====================");
        }
        //6.释放连接
        resultSet.close();
        statement.close();
        connection.close();
    }
}

运行结果图

11.3JDBC中对象的解释

(1)URL

  String url = "jdbc:mysql://localhost:3306/school?useUnicode=true&&characterEncoding=utf8&&useSSL=true";

//mysql默认端口号是3306
//jdbc:mysql://主机号:端口号/数据库名?参数1&参数2&参数3
//oralce默认端口号为1521
//jdbc:oralce: thin:@locakhost:1521:sid

(2)DriverManager

Connection connection = DriverManager.getConnection(url, username, password);
//connection :代表数据库
//数据库设置自动提交
connection.setAutoCommit();
//事务提交
connection.commit();
//事务回滚
connection.rollback();

(3)Statement和PrepareStatement

首先这俩都是执行SQL的对象的

String sql = "select * from student";//编写SQL语句
statement.executeQuery();//查询操作返回ResultSet(结果集)
statement.execute();//执行任何SQL
statement.executeUpdate();//更新、插入、删除。都是用这个，返回一个受影响的行数
statement.executeBatch();//可执行多个SQL语句

(4)ResultSet

查询结果集：封装所有查询结果

[En]

Query result set: encapsulates all query results

resultSet.getobject();//在不知道列类型的情况下使用
//如果知道列的类型就使用指定的类型
resultSet.getString();
resultSet.getInt();
resultSet.getFloat();
resultSet.getDate();

//遍历，指针
resultSet.beforeFirst();//移动到最前面
resultSet.afterLast();//移动到最后面
resultSet.next();//移动到下一个数据
resultSet.previous();//移动到前一行
resultSet.absolute(2);//移动到指定行

11.4 statement对象详解

JDBC中statement对象用于向数据库发送SQL语句，想完成对数据库的增删改查，只需要通过这个对象向数据库发送增删改查的语句即可

statement 对象的executeUpdate方法，用于向数据库发送增、删、改的SQL语句，executeupdate执行完后，将会返回一个整数(即增删改语句导致了数据库几行数据发生了变化)

statement.executeQuery方法用于向数据库发送查询语句，executeQuery方法返回代表查询结果的ResultSet对象

代码实现 使用executeUpdate(String sql)方法完成数据添加操作，实例操作：

①提取工具类

package com.Tang.jdbc;

import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

public class jdbcutils {
    private static String driver = null;
    private static String url = null;
    private static String username = null;
    private static String password = null;
    static{
        try{
            InputStream in = jdbcutils.class.getClassLoader().getResourceAsStream("db.properties");
            Properties properties = new Properties();
            properties.load(in);

            driver = properties.getProperty("driver");
            url = properties.getProperty("url");
            username = properties.getProperty("username");
            password = properties.getProperty("password");

            //1.驱动只加载一次
            //Class.forName(driver);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    //获取连接
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url,username,password);
    }
    //释放连接
    public static void release(Connection conn, Statement st, ResultSet rs){
        if(rs != null){
            try {
                rs.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if(st != null){
            try {
                st.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if(conn != null){
            try {
                conn.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
}

提取连接数据库的db.properties代码

//这里不要随便加空格
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/school?useUnicode=true&characterEncoding=utf8&&useSSL=true
username=root
password=root123456

②测试更新(插入,删除,修改)

package com.Tang.jdbc;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class InsertTest {
    public static void main(String[] args) {
        Connection conn = null;
        Statement st = null;
        ResultSet rt = null;
        try {
            conn = jdbcutils.getConnection();//获取数据库的连接
            st = conn.createStatement();//获得SQL的执行对象
            //测试插入
            String sql = "insert into student(name,pwd,sex)"+
                    "values('twq121','cccccc','男')";
            //测试删除
            String sql = "delete from student where name='twq121'";
            //测试修改
            String sql = "update student set name='twqaa',pwd='123qwe' where student_id=1";
            int i = st.executeUpdate(sql);//除了查询(select用executeQuery，增删改都用update)
            if(i>0){
                System.out.println("插入成功");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
}

运行结果图

③SQL注入问题

SQL存在漏洞，会被攻击导致数据泄露 SQL会被拼接or

package com.Tang.jdbc;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class SqlInjection {
    public static void main(String[] args) {
        //login("唐昊","123456");正常的查询只会输出name等于唐昊，密码等于123456的数据

        login(" 'or '1=1"," ' or '1=1");//会将表中所有数据都输出所有数据都输出，传入的username的意思见下方注释
    }
    public static void login(String username,String password){
        Connection conn = null;
        Statement st = null;
        try {
            conn = jdbcutils.getConnection();
            st = conn.createStatement();//为了防止SQL注入的问题可以使用PrepareStatement

            // String sql = "select * from student where name='唐昊' and pwd = '123456'";
            // String sql = "select * from student where name=' 'or '1=1' and pwd = '123456'";//等价于判断name=null,或者1=1是否为真的判断
            //这里username 和 password前后有 + 只是为了使用函数传进来的参数
            String sql = "select * from student where name='"+username+"' and pwd = '"+password+"'";
            ResultSet resultSet = st.executeQuery(sql);

            while (resultSet.next()){
                System.out.println(resultSet.getObject("name"));
                System.out.println(resultSet.getObject("pwd"));
                System.out.println("=============");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
}

运行结果图

11.5 PreparedStatement对象详解

PreparedStatement可以防止SQL注入，效率更好

(1)更新操作(增删改)

package com.Tang.jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Date;

public class preparestatementTest {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement st = null;
        try {
            conn = jdbcutils.getConnection();
            //1.测试插入数据
            //使用?占位符来代表参数
            String sql1 = "insert into student(name,pwd,sex,birthday,address) values (?,?,?,?,?)";

            st=conn.prepareStatement(sql1);//预编译SQL，先写sql但不执行
            //手动给参数赋值
            st.setString(1,"tang123");//给第一个问号赋值
            st.setString(2,"qwerty");//给第二个问号赋值
            st.setString(3,"女");//给第三个问号赋值
            //注意点：
                    //sql.Date 是数据库的
                    //Date.getTime  是当前的时间属于Java
            st.setDate(4,new java.sql.Date(new Date().getTime()) );//给第四个问号赋值
            st.setString(5,"北京");//给第五个问号赋值

            //执行
            int i = st.executeUpdate();
            if(i > 0){
                System.out.println("插入成功");
            }
            //2.测试删除数据
            String sql2 = "delete from student where student_id=?";
            st=conn.prepareStatement(sql2);//预编译SQL，先写sql但不执行
            //手动给参数赋值
            st.setInt(1,8);//给问号赋值，将student_id=5的数据删除

            //执行
            int i1 = st.executeUpdate();
            if(i1 > 0){
                System.out.println("删除成功");
            }
            //3.测试修改数据
            String sql3 = "update student set name=?  where student_id=?;";
            st=conn.prepareStatement(sql3);//预编译SQL，先写sql但不执行
            //手动给参数赋值
            st.setString(1,"唐三");
            st.setInt(2,1);

            //执行
            int i3 = st.executeUpdate();
            if(i3 > 0){
                System.out.println("修改成功");
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
}

插入成功图

删除成功图

修改成功图

(2)查询操作

package com.Tang.jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;

public class preparestatementTest {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;
        try {
            conn = jdbcutils.getConnection();

            //测试查询
            String sql4 = "select * from student where name=?";
            st = conn.prepareStatement(sql4);
            st.setString(1,"唐晨");

            rs = st.executeQuery();
            while(rs.next()){
                System.out.println(rs.getObject("name"));
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
}

运行结果图

(3)解决SQL注入问题

package com.Tang.jdbc;

import java.sql.*;

public class SqlInjection {
    public static void main(String[] args) {
        login("唐晨","888888");//正常的查询只会输出name等于唐昊，密码等于123456的数据

        //login("' ' or 1=1"," '123456");//这里就不会查询到数据
    }
    public static void login(String username,String password){
        Connection conn = null;
        PreparedStatement st = null;
        try {
            conn = jdbcutils.getConnection();
            //PreparedStatement防止SQL注入的本质，把传递进来的参数当做字符
            //假设其中存在转义字符，比如说 ' 就会被直接转义
            String sql = "select * from student where name=? and pwd =?";
            st = conn.prepareStatement(sql);

            st.setString(1,username);
            st.setString(2,password);

            ResultSet resultSet = st.executeQuery();

            while (resultSet.next()){
                System.out.println(resultSet.getObject("name"));
                System.out.println(resultSet.getObject("pwd"));
                System.out.println("=============");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
}

运行结果图

11.6 IDEA连接数据库

连接的前提是将mysql-connect的jar包导入才行

您可以在成功连接后选择数据库

[En]

You can select a database after a successful connection

更新数据

11.9数据库连接池

共享技术：准备一些预先准备好的资源，来连接预先准备好的

[En]

Pooling technology: prepare some pre-resources, come and connect the pre-prepared

编写连接池，实现一个接口DataSource

(1)开源数据源实现

DBCP C3P0 Druid:阿里巴巴 使用了这些数据库连接池之后，我们在项目开发中就不需要编写连接数据库的代码了

①DBCP

需要用到jar包
commons-pool-1.6.jar点我下载
commons-dbcp-1.4.jar点我下载

dbcpconfig.properties里的代码

#连接设置，这里的名字是dbcp数据源中定义好的
driverClassName=com.mysql.jdbc.Driver可以省略不写，新驱动是自动加载的
url=jdbc:mysql://localhost:3306/school?useUnicode=true&characterEncoding=utf8&&useSSL=true
username=root
password=root123456

jdbcutils_DBCP代码

package com.Tang.jdbc.DBCP;

import org.apache.commons.dbcp.BasicDataSourceFactory;

import javax.sql.DataSource;
import javax.xml.crypto.Data;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

public class jdbcutils_DBCP {
    private static DataSource dataSource = null;
    static{
        try{
            InputStream in = jdbcutils_DBCP.class.getClassLoader().getResourceAsStream("dbcpconfig.properties");
            Properties properties = new Properties();
            properties.load(in);

            //创建数据源 工厂模式
            dataSource = BasicDataSourceFactory.createDataSource(properties);

        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    //获取连接
    public static Connection getConnection() throws SQLException {
        return dataSource.getConnection();//从数据源中获取连接
    }
    //释放连接
    public static void release(Connection conn, Statement st, ResultSet rs){
        if(rs != null){
            try {
                rs.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if(st != null){
            try {
                st.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if(conn != null){
            try {
                conn.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
}

使用DBCP实现数据的删除测试

package com.Tang.jdbc.DBCP;

import com.Tang.jdbc.jdbcutils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class dbcpTest {
    public static void main(String[] args) {
        Connection conn = null;
        Statement st = null;
        ResultSet rt = null;
        try {
            conn = jdbcutils_DBCP.getConnection();//获取数据库的连接
            st = conn.createStatement();//获得SQL的执行对象
//            String sql = "insert into student(name,pwd,sex)"+
//                    "values('twq121','cccccc','男')";
            String sql = "delete from student where student_id=9";
            int i = st.executeUpdate(sql);
            if(i>0){
                System.out.println("删除成功");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
}

②C3P0

需要的jar包 新打开界面打开此链接

c3p0-0.9.2.1.jar点我下载

mchange-commons-java-0.2.3.4.jar.zip点我下载

c3p0-config.xml的配置文件

        root
        root123456

        jdbc:mysql://localhost:3306/school?useUnicode=true;characterEncoding=utf8;useSSL=true

        5

        5

        20

        2

jdbcutils_c3p0代码

package com.Tang.jdbc.C3P0;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import org.apache.commons.dbcp.BasicDataSourceFactory;

import javax.sql.DataSource;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

public class jdbcutils_C3P0 {
    private static ComboPooledDataSource dataSource = null;
    static{
        try{
            //创建数据源 工厂模式
            dataSource = new ComboPooledDataSource("MySQL");//配置文件写法

        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    //获取连接
    public static Connection getConnection() throws SQLException {
        return dataSource.getConnection();//从数据源中获取连接
    }
    //释放连接
    public static void release(Connection conn, Statement st, ResultSet rs){
        if(rs != null){
            try {
                rs.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if(st != null){
            try {
                st.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if(conn != null){
            try {
                conn.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
}

c3p0增加数据测试

package com.Tang.jdbc.C3P0;

import com.Tang.jdbc.DBCP.jdbcutils_DBCP;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class C3P0Test {
    public static void main(String[] args) {
        Connection conn = null;
        Statement st = null;
        ResultSet rt = null;
        try {
            conn = jdbcutils_C3P0.getConnection();//获取数据库的连接
            st = conn.createStatement();//获得SQL的执行对象
            String sql = "insert into student(name,pwd,sex)"+
                    "values('twq121','cccccc','男')";
//            String sql = "delete from student where student_id=10";
            int i = st.executeUpdate(sql);
            if(i>0){
                System.out.println("删除成功");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
}

Original: https://www.cnblogs.com/twq46/p/16485022.html
Author: 剑断青丝ii
Title: MySQL完整版详解