Mysql知识大全

Mysql

数据库是全部软件体系中最核心的存在 DBA
1.1什么是数据库

数据库（DB，DataBase）
概念：数据堆栈，软件，安装在使用体系上（window，linux...）
作用：存储数据
1.2DBMS数据库管理体系

数据库分类
关系型数据库：
1.Mysql ,Oracel,SqlServer,DB2,SQLlite
2.通过表和表之间，行和列之间的关系举行数据的存储

非关系型数据库：(NoSQL)
1.Redis ，MongDB
2.非关系型数据库，对象存储，通过对象的自身属性来决定
DBMS：
数据库的管理软件，维护和获取数据
1.3MySQL(开源的数据库软件)

MySQL 是最盛行的关系型数据库管理体系，在 WEB 应用方面 MySQL 是最好的 RDBMS(Relational Database Management System：关系数据库管理体系)应用软件之一。

2. sc delete mysql,清空mysql，然后重新安装

复制代码

2. C:\Windows\System32>mysql -u root -p123456 --在命令提示符里连接数据库
4. ​
6. --所有的语句都用分号结尾
7. show databases;--查看所有的数据库
8. use school--切换数据库，使用use命令
9. Database changed
10. show tables；--查看数据库中所有的表
11. describe student;--显示数据库中所有表的信息
12. create databbase westos;--创建一个数据库
13. exit；--退出连接
14. --
15.   单行注释
16. /* */ 多行注释

复制代码

数据库xxx语言

DDL 界说
DML 使用
DQL 查询
DCL 控制
2.使用数据库

使用数据库>使用数据库中的表>使用数据库中表的数据
2.1使用数据库(相识)

mysql关键字不区分巨细写

1.创建数据库

2. CREATE DATABASE [IF NOT EXISTS] westos
3. --  []代表可选

复制代码

2.删除数据库

2. DROP DATABASE [IF EXISTS] westos

复制代码

3.使用数据库

2. USE `school`
3. -- tab上面的键 如果你的表明或者字段名是一个特殊字符，就需要带`` 飘

复制代码

4.检察数据库

2. SHOW DATABASES

复制代码

2.2数据库列的数据范例

1.数值

•         tinyint 非常小的数据 1个字节
  
•         smallint 较小的数据 2个字节
  
•         mediumint 中等巨细的数据 3个字节
  
•         int 标准的整数 4个字节
  
•         bigint 较大的数据 8个字节
  
•         float 浮点数 4个字节
  
•         double 浮点数 8个字节(精度题目)
  
•         decimal 字符串情势的浮点数 (金融)
         
  

• 
  
  
  
  •                 char 字符串固定巨细的 0-255
    
  •                 varchar 可变字符串 0-65535 常用的变量 String
    
  •                 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到如今的毫秒数 较为常用
         
          4.null
  

不要用null举行结算，效果为null
2.3数据库的字段属性

1.Unsigned:
无符号的整数
声明白该列不能声明为负数
2.zerofill:
0添补的
不敷的位数，使用0来添补 ，int(3) 5 005
3.自增：
通常明白为自增，自动在上一条记录的底子上+1(默认)
通常用来计划唯一的主键，必须是整数范例
可以自界说计划主键自增的起始值和步长
4.非空
如果选上非空 ，如果不给她赋值，就会报错
null，如果不填写值，默认就是null
5.默认：
默认的值
6.拓展
每个表都必须存在的5个字段

2. /*
3.  
4. id  主键
5. `version` 乐观锁
6. is_delete
7.   伪删除
8. gmt_create  创建时间
9. gmt_update
10.   修改时间
12. ​
14. ​
16. */
18. ​
20. ​

复制代码

2.4创建数据库表

2. -- 表的名称和字段尽量使用``括起来
3. -- AUTO_INCREMENT自增
4. -- 字符串使用单引号括起来
5. -- 所有的语句后面加,最后一句不加
6. --PRIMARY KEY 一般一个表只有一个唯一的主键

复制代码

3. CREATE TABLE IF NOT EXISTS `student`(
4. `id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号 ',
5. `name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
6. `sex` VARCHAR (20) NOT NULL DEFAULT '女'  COMMENT '性别',
7. `birthday` DATETIME  DEFAULT NULL COMMENT'出生日期',
8. `address` VARCHAR(100) DEFAULT NULL COMMENT'地址',
9. `email` VARCHAR (50) DEFAULT NULL COMMENT '邮件',
10. PRIMARY KEY(`id`)
12. ​
14. ​
16. )ENGINE=INNODB DEFAULT
17. CHARSET =utf8
19. ​

复制代码

3. ​
5. ​
7. 格式：create table [if not exists] `表名`(
9.  
10.  '字段名' 列类型[属性][索引][注释],
12.  
13.  '字段名' 列类型[属性][索引][注释],
15.  
16.  '字段名' 列类型[属性][索引][注释],
18.    ......
20.  
21.  '字段名' 列类型[属性][索引][注释]
23. ​
25. ​
27. )[表类型][字符集设置][注释]

复制代码

2.4.1常用下令

2. SHOW CREATE DATABASE school -- 查看创建数据库的语句
3. CREATE DATABASE `school` /*!40100 DEFAULT CHARACTER SET utf8 */
5. ​

复制代码

3. ​
5. SHOW CREATE TABLE student -- 查看数据表student的定义语句
6. CREATE TABLE `student` (
7.   `id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号 ',
8.   `name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
9.   `sex` VARCHAR(20) NOT NULL DEFAULT '女' COMMENT '性别',
10.   `birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
11.   `address` VARCHAR(100) DEFAULT NULL COMMENT '地址',
12.   `email` VARCHAR(50) DEFAULT NULL COMMENT '邮件',
13.   PRIMARY KEY (`id`)
14. ) ENGINE=INNODB DEFAULT CHARSET=utf8

复制代码

3. ​
5. ​
7. DESC student -- 显示表的结构

复制代码

2.5数据表的范例

2. /*
3.  
4. 关于数据库引擎
5. INNOB  默认使用~  安全性高，事务的处理，多表多用户操作
6. MYISAM
7.   早些年使用的 节约空间，速度较快
9. */

复制代码

MYISAMINNODB变乱支持不支持支持数据行锁定不支持支持外键束缚不支持支持全文索引支持不支持表空间的巨细较小较大，约为2倍全部的数据库文件都存在data目次下，一个文件夹就对应一个数据库
本质还是文件的存储
MySQL引擎在物理文件上的区别：
INNODB在数据库表中只有一个.frm文件，以及data目次下的ibdata1文件
MYISAM对应文件：
.frm 表布局的界说文件
.MYD 数据文件(data)
.MYI 索引文件(index)
2.5.1设置数据库表的字符集编码

2. CHARSET =utf8

复制代码

也可以在my.ini中设置默认的编码

2. character -set-server = utf8

复制代码

2.6修改数据表

2. -- 修改表名  ALTER TABLE 旧表名 RENAME AS 新表名
3. ALTER TABLE teacher RENAME AS teacher1
4. -- 增加表的字段  ALTER TABLE 表名 ADD 字段名 列属性
5. ALTER TABLE teacher1 ADD age INT(11)
6. -- 修改表的字段(重命名，修改约束)
7. ALTER TABLE teacher1 MODIFY age VARCHAR(11) -- modify 只能修改字段的数据类型   
8. ALTER TABLE teacher1 CHANGE age age1 INT(2)
9.  -- change既能重命名也可以改约束
10. -- 删除表的字段
11. ALTER TABLE  teacher1 DROP age1
12. -- 删除表
13. DROP TABLE  IF EXISTS teacher1
15. ​
17. ​

复制代码

3.MySQL数据管理

3.1外键

2. CREATE TABLE `grade` (
3.   `gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年纪id',
4.   `gradename` VARCHAR(50) NOT NULL COMMENT '年纪名称',
5.   PRIMARY KEY (`gradeid`)
6. ) ENGINE=INNODB DEFAULT CHARSET=utf8

复制代码

2. -----------------------------------------------------
3. 定义外键key
5. ​
7. ​
9. ​

复制代码

2. CREATE TABLE `student` (
3.   `id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号 ',
4.   `name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
5.   `sex` VARCHAR(20) NOT NULL DEFAULT '女' COMMENT '性别',
6.   `birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
8.   `gradeid` INT(10) NOT NULL COMMENT '学生年纪',
9.   `address` VARCHAR(100) DEFAULT NULL COMMENT '地址',
10.   `email` VARCHAR(50) DEFAULT NULL COMMENT '邮件',
11.   PRIMARY KEY (`id`),
12.   KEY  `FK_gradeid`(`gradeid`),
13.   CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES  `grade`(`gradeid`)
14. ) ENGINE=INNODB DEFAULT CHARSET=utf8

复制代码

3. ​
5. ​
7. -----------------------------------------------------
8. 在表外面创建外键
9. alter table `student`
10. add  CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES  `grade`(`gradeid`);
11. alter table 表名 add  CONSTRAINT 约束名 FOREIGN KEY(作为外键的列) REFERENCES  (引用哪一个表)(哪一个字段)

复制代码

3.2DML语言（全部记着）

数据库意义： 数据存储，数据管理
DML语言：数据使用语言
3.2.1添加(insert)

3. ​

复制代码

1. INSERT INTO `student1` (`email`) VALUES ('119')-- INSERT INTO `表名` (` 字段`) values ('数据')
3. ​
5. ​
7. INSERT INTO `student1` (`email`)
8.  VALUES('222'),('666') -- 一个字段插入多个数据
10. ​
12. INSERT INTO  `student1` (`sex` ,`email`,`address`) VALUES ('女','9999','**')-- 多个字段插入多个数据

复制代码

3. ​
5. --字段可以省略，但是反面的数据要逐一对应
7. ​

复制代码

3.2.2修改（update）

1. UPDATE  `student1` SET `name`='长江一号' -- 不指定，会改变全部UPDATE `student1` SET `name`='长江二号' WHERE id=1 -- where 指定改那里UPDATE `student1` SET `name`='长江二号' ,`sex` ='男' WHERE id=1 -- 修改多个属性用逗号隔开
3. ​
5. ​
7. update 表名 set `字段` ='值或变量' where
9. ​
11. UPDATE `student1` SET `name`='长江5号' ,`sex` ='男' WHERE id=3 AND `name`='长江一号'

复制代码

逻辑运算符寄义范围效果=便是5=6false<>或!=不便是5<>6true><>=<=BETWEEN....and...在某个范围内，闭区间[2,5]AND 与我和你&&OR 或||not ! 非not a ! a使用符会返回布尔值

3.2.3删除(delete)

2. DELETE FROM `student1` WHERE id=1
3.  -- 删除指定数据

复制代码

truncate 作用：
完全清空一个数据库表 ，表的布局和索引束缚不会变

2. TRUNCATE `student1`
3.  -- 清空表

复制代码

雷同点：都能删除，不会删除表布局
差别： truncate 重新设置自增列，计数器会归零
delete自增不会归零

1. CREATE TABLE `test`(`id` INT(4) NOT NULL AUTO_INCREMENT,`coll` VARCHAR(20) NOT NULL, PRIMARY KEY  (`id`)
3. ​
5. )ENGINE=INNODB DEFAULT CHARSET=utf8

复制代码

1. INSERT INTO `test` (`coll`)
2.  VALUES ('1'),('2')
4. ​
6. TRUNCATE TABLE `test`-- 自增归零

复制代码

相识：
delete删除以后，重启数据库
引擎是innodb，自增会从1开始（存在内存当中的，断电即失）
是myisam 继承从上一个自增量开始(存在文件中的，不会丢失)
4.DQL查询数据（最重点）

4.1DQL

(Data Query LANGUAGE：数据查询语言)
1.全部的查询使用都用它 Select
2.简单的查询 复杂的查询都能做
3.数据库中最核心的语言，最告急的语句
4.使用频率最高的语句
4.2查询

1. -- select 字段,... from 表名 SELECT *FROM student
2.  -- 查询全部的门生 SELECT `studentno` ,`studentname` FROM student
3.  -- 查询指定的字段SELECT `studentno` AS  学号,`studentname` AS 门生姓名 FROM student AS s-- 别名，给效果起一个名字 as 字段，表名都可以 SELECT CONCAT ( '姓名： ',studentname) AS 新名字 FROM student
4.   -- 函数 concat（a，b）
6. ​

复制代码

去重 distinct

2. SELECT *FROM result
3. SELECT `studentno` FROM result
4. SELECT
5.   DISTINCT `studentno` FROM result -- 发现重复数据 去重

复制代码

2. SELECT VERSION() -- 查询系统版本 （函数）
3. SELECT
4.  100*2-1 AS 计算结果
5.  -- 用来计算  （表达式）
6. SELECT @@auto_increment_increment
7.  -- 查询自增的步长（变量）

复制代码

4.3where条件（返回布尔值）

作用： 检索数据中符合条件的值
见3.2.2 逻辑与算符

暗昧查询：比力运算符
运算符语法形貌is nulla is null如果使用符为null，效果为真is not nulla is not null如果使用符不为null，效果为真betweena between b and c若a在b和c之间，效果为真likea like b 小红lile小红1，真sql匹配，如果a能匹配到b，效果为真ina in （a1，a2，a3...）如果a在a1大概a2此中的某一个值，效果为真

2. -- 查询姓刘的同学
3.     -- like结合 %(代表0到任意一个字符)  _（一个字符）   
4.     SELECT`studentno`,`studentname`FROM student WHERE studentname LIKE '刘%'
5.     -- 查询姓刘的同学 ，名字后面只有一个字   
6.             
7.     SELECT`studentno`,`studentname`FROM student WHERE studentname LIKE '刘_'
8.                     
9.     -- 查询姓刘的同学 ，名字后面只有2个字                              
10.    
11.     SELECT`studentno`,`studentname`FROM student WHERE studentname LIKE '刘__'   
12.    
13.     -- 查询名字中有楠的
14.                
15.     SELECT`studentno`,`studentname`FROM student WHERE studentname LIKE '%楠%'   

复制代码

2.     ------------------------------------------------------------------------------------
3.     -- in (具体的精确的一个或者多个值)--
4.     -- 查询 1001，1002，1003学员

复制代码

2.    SELECT `studentno``studentname` FROM student WHERE IN (1001,1002,1003)

复制代码

2.    
3.     --  null
4.   not null
5.  
6.  
7.  
8.   --
9.    
10.     --  查询地址为空的学生

复制代码

3.  
4.  
5.  
6.  SELECT `studentno``studentname` FROM student WHERE address=''
7.  OR address IS NULL

复制代码

3.  
4.  
5.  
6.   -- 查询有出生日期的同学
8.  
9.  
10.  
11.  
13.  
14.  
15.  

复制代码

3.   SELECT `studentno``studentname` FROM student WHERE `borndate` IS NOT NULL
5.  
6.  
7.  
8.  

复制代码

3.  
4.  
5.        
6.                     

复制代码

4.4联表查询

join on 毗连查询 语法
where （等值查询 ）

JOIN
使用形貌Inner join交集left join会返回左表中全部的值，纵然右表中是空值right join返回右表中全部的值，纵然左表是空值

1. SELECT s.`studentno`,`studentname`, `subjectno`,`studentresult` FROM student AS s
2.   INNER JOIN result AS r WHERE s.studentno = r.studentno
3.   --  两个表的交集
5. ​

复制代码

3. ​

复制代码

3. ​
5. SELECT s.`studentno`,`studentname`, `subjectno`,`studentresult` FROM student AS s
6.   RIGHT JOIN result AS r ON s.studentno = r.studentno
7.  

复制代码

3. ​
5. SELECT s.`studentno`,`studentname`, `subjectno`,`studentresult` FROM student AS s
6.   LEFT JOIN result AS r ON s.studentno = r.studentno
7.  

复制代码

2. SELECT s.`studentno`,`studentname`, `subjectname`,`studentresult`
3. FROM student AS s  RIGHT JOIN result AS r
4. ON s.studentno = r.studentno
5.  
6. INNER JOIN `subject` AS sub
7. ON r.subjectno = sub.subjectno

复制代码

1. -- 要查 学号 门生姓名 科目名 分数 /*从三张表查  student  result subjectSELECT s.`studentno`,`studentname`, `subjectname`,`studentresult` FROM student AS s  RIGHT JOIN result AS r ON s.studentno = r.studentno
2.    这里查询到 student和result中学号姓名 分数内联一个 从subject表中找到和result表中学科名一样的 就找到了
4. ​
6. */
8. ​
10. from表  xxx join 毗连的表
11.  on 交错条件 /*思绪分析查询的字段来自那几张表确定使用哪种毗连查询 7种确定交错点 (这两个表中谁人数据是雷同的)判断条件：s.studentno = r.studentno
12.  
14. ​
16. */

复制代码

4.5自毗连

本身的表和本身的表毗连，核心：一张表实际上是两张表构成的

2. SELECT a.`categoryName` AS '父栏目' ,b.`categoryName` AS '子栏目'
3. FROM `category` AS a ,`category` AS b
4. WHERE  a.`categoryid`=b.`pid`

复制代码

4.6分页和查询

2. -- 排序  升序：asc  降序：desc

复制代码

3. ​
5. SELECT s.`studentno`,`studentname`, `subjectname`,`studentresult`
6. FROM student AS s  RIGHT JOIN result AS r
7. ON s.studentno = r.studentno
8.  
9. INNER JOIN `subject` AS sub
10. ON r.subjectno = sub.subjectnoWHERE subjectname ='数据库布局'ORDER BY studentresult ASC

复制代码

2. -- 排序  升序：asc  降序：desc
3. --  limit 分页  (起始值，页面的巨细)-- limit 0，5  表现1-5的数据  页面巨细都是5-- limit  1，5  表现2-6的数据 页面巨细都是5
4.  -- limit  6，5

复制代码

2. SELECT s.`studentno`,`studentname`, `subjectname`,`studentresult`
3. FROM student AS s  RIGHT JOIN result AS r
4. ON s.studentno = r.studentno
5.  
6. INNER JOIN `subject` AS sub
7. ON r.subjectno = sub.subjectnoWHERE subjectname ='数据库布局'ORDER BY studentresult ASCLIMIT 0,5

复制代码

2. -- pagesize 页面大小  n：当前页
3. -- 第n页  (n-1)*5,5
4.  
5.  
6.  
7.  
8.  
9.  
10.  
11.  
12.    (当前页-1)*页面大小，页面大小
13. -- 数据总数/页面大小=总页数  有余数+1

复制代码

4.7子查询

本质：在where语句中嵌套一个子查询语句
where( select *from)

2. -- 查询 学号 学科号 学生成绩并且科目为数据库结构的学生成绩，降序排序
3. -- 联表查询

复制代码

2. SELECT `studentno`,r.`subjectno`,`studentresult`
3. FROM `result` r
4. INNER JOIN `subject` sub
5. ON r.subjectno= sub.subjectno
6. WHERE `subjectname`='数据库结构'
7. ORDER BY studentresult DESC
8. -- 子查询 (由里及外)

复制代码

3. ​
5. SELECT `studentno`,r.`subjectno`,`studentresult`FROM `result` rWHERE (SELECT `subjectname`FROM `subject`
6.  
7.  
8.  
9.  WHERE subjectname= 'z数据库布局')ORDER BY  studentresult DESC

复制代码

4.8 分组和过滤

3.  
5.  ----------------查询不同课程的平均分，最高分，最低分，平均分>80  核心;(根据不同的课程进行分组)-------
7.  SELECT `subjectname`,AVG(`studentresult`) AS 平均分,MAX(`studentresult`),MIN(`studentresult`)FROM result r
8.   INNER JOIN `subject` sub
10.  ON r.`subjectno`= sub.`subjectno`
12.  GROUP BY r.`subjectno`
13.  -- 通过什么字段来分组
15.  HAVING 平均分>80
17.  
18.  
19.  

复制代码

5.MySQL函数

5.1常用函数

2. -- 数学运算

复制代码

1. SELECT ABS(-8)
2.  -- 绝对值SELECT CEILING(9.4) -- 向上取整SELECT FLOOR(9.4) -- 向下取整SELECT RAND() -- 返回一个0-1之间的随机数SELECT SIGN(5)
3.  --返回参数的符号  0是0 负数是-1 正数是1-- 字符串函数SELECT CHAR_LENGTH ('我爱你')
4.  --  字符串长度 3SELECT CONCAT ('老鼠','爱','大米')
5.   -- 拼接字符串SELECT INSERT('我非常热爱编程',2,4,'特别特别')
6.  -- 效果是 我特别特别编程
7.    从某个位置开始更换某个长度SELECT LOWER ('qiannan') -- 转为小写SELECT UPPER('qiannan')
8.  -- 转为大写SELECT INSTR ('qiannan','n')
9.  -- 返回第一次出现的字串的索引SELECT REPLACE ('我说对峙就能乐成','对峙','积极')-- 更换出现的指定字符串SELECT SUBSTR ('狂神说对峙就能乐成',4,6) -- 返回指定的字符串（4，6  起始位置，截取长度） SELECT REVERSE ('狂神说对峙就能乐成')
10.  -- 反转 ------------------------ 时间和日期函数 ---------- SELECT CURRENT_DATE()
11.  -- 获取当前日期 SELECT NOW()
12.  -- 获取当前的时间 SELECT LOCALTIME () -- 本地时间 SELECT SYSDATE() -- 体系十几件 SELECT YEAR (NOW()) SELECT MONTH (NOW()) SELECT DAY (NOW()) SELECT HOUR (NOW()) SELECT MINUTE (NOW())
13.  SELECT SECOND (NOW()) ---------  体系 -------- SELECT USER()
15. ​

复制代码

3. ​

复制代码

5.2聚合函数（常用）

函数名称形貌count()计数sum()求和avg()匀称值max()最大值min()最小值

2. -------------------------- 计数  ----------------

复制代码

2. SELECT COUNT(`borndate`) FROM student
3.  -- count(字段)，会忽略所有的null值
4. SELECT COUNT(*) FROM student
5.  -- count(*)  不会忽略null值，本质 计算行数
7.  SELECT COUNT(1) FROM student
8.  -- count(1)  不会忽略null值，本质 计算行数
10.  SELECT SUM(`studentresult`) AS 总和 FROM result
11.  -- 总分
13.  SELECT AVG(`studentresult`) AS 总和 FROM result
14.  -- 平均分
16.  SELECT MAX(`studentresult`) AS 总和 FROM result
17.  -- 最高分
19.   SELECT MIN(`studentresult`) AS 总和 FROM result
20.  -- 最低分

复制代码

数据库级别的MD5数据加密

不可逆

1. CREATE TABLE `testmd5`(`id`
2.  INT(4)
3.  NOT NULL, `name` VARCHAR(20) NOT NULL, `pwd` VARCHAR(50) NOT NULL, PRIMARY KEY (`id`)
5. ​
7. )ENGINE=INNODB  DEFAULT CHARSET=utf8

复制代码

3. ​
5. INSERT INTO testmd5 VALUES(1,'zhangsan','123456'),(2,'lisi','123456'),(3,'wangwu','123456')  -- 加密全部的暗码--

复制代码

2. UPDATE testmd5
3.  SET pwd=MD5(pwd)

复制代码

3. -- 插入的时候加密 --

复制代码

2. INSERT INTO testmd5 VALUES(4,'zhangsan',MD5('123456'))

复制代码

6.变乱

要么都乐成，要么都失败
变乱原则： ACID原则 原子性 划一性（终极划一性） 隔离性 恒久性 (脏读，幻读..）
原子性：要么都乐成，要么都失败
划一性：变乱前后的数据完备性要包管划一
恒久性 变乱提交： 变乱一旦提交则不可逆，被恒久化到数据库中
隔离性：变乱的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的变乱，不能被其他变乱的使用数据所干扰，多个并发变乱之间要相互隔离。
脏读：指一个变乱读取了别的一个变乱未提交的数据
不可重复读：在一个变乱内读取表中的某一行数据，多次读取效果差别。（这个不愿定是错误，只是某些场合不对）
虚读(幻读)：是指在一个变乱内读取到了别的变乱插入的数据，导致前后读取数量总量差别等。多了一行

2. -- 模拟转账  事务

复制代码

1. SET autocommit = 0; -- 关闭自动提交START  TRANSACTION
2.  -- 开启一个变乱
4. ​
6. UPDATE  ACCOUNT SET  money=money-50
7.  WHERE  `name` = 'A' -- A-500UPDATE  ACCOUNT SET  money=money+50
8.  WHERE  `name` = 'B' -- A+500
10. ​
12. COMMIT -- 提交变乱 就被恒久化 ROLLBACK
13.   -- 回滚   SET autocommit  = 1
14.  -- 规复默认值

复制代码

7.索引

索引是资助MySQL高效获取数据的数据布局
索引的分类:
1.主键索引(primary key)
唯一的标识，主键不可重复，只能有一个列作为主键 非空且唯一
2.唯一索引 (unique key)
克制重复的列出现，唯一索引可以重复，多个列都可以标识为唯一索引 索引列的值必须唯一，可以出现空值
3.通例索引 (key/index)
默认的，index，key关键字来设置
4.全文索引 (fulltext)
在特定的数据库引擎下才有 myism
快速定位数据

2. -- 显示所有的索引信息

复制代码

2. SHOW INDEX FROM  student

复制代码

2. -- 增加一个全文索引 (索引名) 列名

复制代码

3. ALTER TABLE school.student ADD  FULLTEXT INDEX `studentname`(`studentname`)

复制代码

3. -- explain 分析sql执行情况

复制代码

2. EXPLAIN SELECT * FROM student
3.  -- 非全文索引

复制代码

2. EXPLAIN SELECT * FROM student WHERE MATCH(studentname)AGAINST('刘')
3.  -- 非全文索引

复制代码

2. -- id_表名_字段名
3. -- create index 索引名 on 表名 (字段)
4. --  创建完索引以后在查找 速度就很快 因为给每一条数据都创建了一个btree

复制代码

1. CREATE INDEX id_appuser_name ON appuser(`name`)
3. ​
5. SELECT *FROM appuser WHERE `name` = '用户9999'
7. ​
9. EXPLAIN SELECT *FROM appuser WHERE `name` = '用户9999'

复制代码

索引原则：
1.索引不是越多越好
2.不要对进程变动数据加索引
3.小数据量的表不须要加索引
4.索引一样平常加在常用来观察的字段上
索引的数据布局：
Hash范例的索引
Btree： InnoDB的默认数据布局
8.权限管理和备份

8.1用户管理

SQLyog可视化管理
SQL下令使用：
用户表： mysql.user

3. ​
5. -- 创建用户  CREATE USER 用户名 IDENTIFIED BY 暗码CREATE USER kuangshen  IDENTIFIED BY '123456'-- 修改暗码 （修改当前用户暗码）SET PASSWORD = PASSWORD('123456')
7. ​
9. ​
11. ​
13. -- 修改暗码 （修改指定用户暗码）SET PASSWORD FOR kuangshen  = PASSWORD('111111')
15. ​
17. -- 重定名  RENAME USER 原名字 TO 新名字RENAME USER kuangshen TO kuangshen2
19. ​
21. -- 用户授权 ALL PRIVILEGES 全部的权限， 库.表-- ALL PRIVILEGES 除了给别人授权，其他都夺目 GRANT ALL PRIVILEGES ON *.* TO kuangshen2  -- 查询权限 SHOW GRANTS FOR  kuangshen2 -- 检察指定用户的权限 -- GRANT ALL PRIVILEGES ON *.* TO 'kuangshen2'@'%' SHOW GRANTS FOR root@localhost -- root用户权限
22.  
23.   GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION  -- 取消权限 revoke哪些权限 ，在哪个库取消，给谁取消 REVOKE  ALL  PRIVILEGES ON *.*FROM kuangshen2  -- 删除用户 DROP USER kuangshen2

复制代码

8.2数据库备份

为什么要备份：
1.包管告急的数据不丢失
2.数据转移
MySQL数据库备份的方式：
1.直接拷贝物理文件
2.在sqlyog这种可视化工具中手动导出
3.使用管理员身份下令行导出 mysqldump 下令使用用

1. mysqldump -hlocalhost -uroot -p123456 school student >D:/a.sqlmysqldump -h主机 -u 用户名 -p暗码 数据库  表1 表2 > 磁盘位置/文件名
3. ​
5. ​
7. 导入登录的情况下，切换到指定的数据库source D：/a.sql
9. ​
11. mysql -u用户名
12.   -p暗码 库名<备份文件
14. ​

复制代码

9.规范数据库计划

软件开辟之中，关于数据库的计划
1.分析需求：分析业务和须要处理处罚的数据库的需求
2.概要计划：计划关系图E-R图
9.1三大范式

为什么须要数据规范化？

•         信息重复
  
•         更新非常
  
•         插入非常 无法正常表现信息
  
•         删除非常 丢失有用的信息
         
  

三大范式：

•         第一范式(1NF)
          原子性：包管每一列不可再分
  
•         第二范式(2NF)
          满意原子性
          每张表只形貌一件事故
  
•         第三范式(3NF)
          满意第一范式和第二范式
          确保每一列数据和主键直接相干，不能间接相干
  

规范性和性能题目：
关联查询的表不得凌驾3张表
1.思量贸易化的需求和目的，（本钱，用户体验）数据库的性能更加告急
2.在规范性能题目的时间，须要得当的思量一下规范性
3.故意给某些表增长一些冗余的字段（从多表查询变为单表查询）
4.故意增长一些盘算列（从大数据量低沉为小数据列的查询 索引）
10.JDBC

数据库驱动：
我们的步调会通过数据库驱动和数据库打交道！

SUN公司为了简化开辟职员的(对数据库的同一)使用，提供了一个(java使用数据库的)规范，俗称JDBC，这些规范的实现由具体的厂商去做
对于开辟职员来说，我们只须要把握JDBC接口的使用即可
框架：没有什么是加一层办理不了的

2.  

复制代码

1. //1.加载驱动
2.  
3.  
4.   // DriverManager.registerDriver(new com.mysql.jdbc.Driver());
5.  
6.  
7.  
8.  Class.forName("com.mysql.jdbc.Driver") ; //固定写法 //2.用户信息和url
9.  
10.  
11.  
12.  //useUnicode=true&characterEncoding=utf8&useSSL=true"
13.  
14.  
15.  
16.  String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true";
17.  
18.  
19.  
20.  String username =
21.  "root";
22.  
23.  
24.  
25.  String password = "123456";// mysql -- 3306//协议：//主机所在：端标语/数据库名?参数1&参数2&参数3// oralce  --1521//jdbc:oracle:thin:@localhost:1521:sid
26.   //3.毗连乐成，数据库对象
27.   Connection代表数据库
28.  
29.  
30.  
31.  Connection connection = DriverManager.getConnection(url, username, password);
32.  
33.  
34.  
35.  //connection.commit();//变乱提交
36.  
37.  
38.   // connection.rollback();//变乱回滚
39.  
40.  
41.   // connection.setAutoCommit();//数据库设置自动提交
42.  
43.  //4.实行sql的对象 Statement实行sql的对象
44.  
45.  
46.  
47.  Statement statement = connection.createStatement(); //statement.executeQuery();//查询使用返回ResultSet
48.  
49.  
50.  
51.  //statement.execute();//实行任何sql
52.  
53.  
54.  
55.  //statement.executeUpdate();//更新，插入，删除都是用这个，返回一个受影响的行数
57. ​
59.  
60.  
61.  
62.  //5.实行sql的对象去 实行sql  大概存在效果，检察返回效果
63.  
64.  
65.  
66.  String sql = "SELECT *FROM users ";
67.  
68.  
69.  
70.  ResultSet resultSet = statement.executeQuery(sql); //返回的效果集封装了我们全部查询的效果
71.  
72.  
73.  
74.  while (resultSet.next()){
75.  
76.  
77.  
78.  
79.  
80.  System.out.println("id=" + resultSet.getObject("id"));
81.  
82.  
83.  
84.  
85.  
86.  System.out.println("name=" + resultSet.getObject("Name"));
87.  
88.  
89.    }
91. ​
93.  
94.  
95.  
96.  //6.开释毗连
97.  
98.  
99.  
100.  resultSet.close();
101.  
102.  
103.  
104.  statement.close();
105.  
106.  
107.  
108.  connection.close();

复制代码

10.1statement对象详解

1.编写工具类

1. package lesson02.util;
3. ​
5. import java.io.IOException;import java.io.InputStream;import java.sql.*;import java.util.Properties;
7. ​
9. public class jdbcutils {
11. ​
13.  
14.  private
15.  static
16.  String driver = null;
17.  
18.  private
19.  static
20.  String url = null;
21.  
22.  private
23.  static
24.  String username = null;
25.  
26.  private
27.  static
28.  String password = null;
29.  
30.  static{
31.  
32.  
33.  
34.  
35.  
36.  
37.  
38.  try{
39.  
40.  
41.  
42.  
43.  
44.  InputStream inputStream = jdbcutils.class.getClassLoader().getResourceAsStream("db.properties");
45.  
46.  
47.  
48.  
49.  
50.  Properties properties = new Properties();
51.  
52.  
53.  
54.  
55.  
56.  properties.load(inputStream);
58. ​
60.  
61.  
62.  
63.  
64.  
65.   driver
66.  = properties.getProperty("driver");
67.  
68.  
69.  
70.  
71.  
72.   url
73.  = properties.getProperty("url");
74.  
75.  
76.  
77.  
78.  
79.  username
80.  = properties.getProperty("username");
81.  
82.  
83.  
84.  
85.  
86.  password
87.  = properties.getProperty("password");
88.  
89.  
90.  
91.  
92.  
93.  //驱动只用加载一次
94.  
95.  
96.  
97.  
98.  
99.  Class.forName(driver);
101. ​
103. ​
105.  
106.  
107.    } catch (IOException e) {
108.  
109.  
110.  
111.  
112.  
113.  throw new RuntimeException(e);
114.  
115.  
116.    } catch (ClassNotFoundException e) {
117.  
118.  
119.  
120.  
121.  
122.  throw new RuntimeException(e);
123.  
124.  
125.    }
126.    }
127.  
128.  //获取毗连
129.  
130.  public static Connection
131.  getConnection() throws SQLException {
132.  
133.  
134.  
135.   return DriverManager.getConnection(url, username, password);
136.    }
138. ​
140.  
141.  //开释毗连
142.  
143.  public static void
144.  release(Connection co, Statement st, ResultSet rs) throws SQLException {
145.  
146.  if(rs!=null){
147.  
148.  
149.  
150.  rs.close();
151.    }
152.  
153.  if(st!=null){
154.  
155.  
156.  
157.  st.close();
158.    }
159.  
160.  if(co!=null){
161.  
162.  
163.  
164.  co.close();
165.    }
166.    }}

复制代码

2.代码实现 都用 executeUpdate 增编削

1. public class Testinsert {
2.  
3.  public static void main(String[] args) throws SQLException {
4.  
5.  
6.  
7.  Connection co = null;
8.  
9.  
10.  
11.  Statement st = null;
12.  
13.  
14.  
15.  ResultSet rs = null;
17. ​
19.  
20.  
21.  
22.  co= jdbcutils.getConnection();
23.  //获取数据库毗连
24.  
25.  
26.  
27.  st = co.createStatement();
28.  //获取实行sql的对象
29.  
30.  
31.  
32.  String sql ="INSERT INTO `users`(id,`NAME`,`PASSWORD`,`email`,`birthday`)" +
33.  
34.  
35.  
36.  
37.  
38.  
39.  
40.  "VALUES (4,'qiannan','123456','[email protected]','2003-02-25')";
41.  
42.  
43.  
44.  int i = st.executeUpdate(sql);
45.  
46.  
47.  
48.  if(i>0){
49.  
50.  
51.  
52.  
53.  
54.  System.out.println("插入乐成");
55.  
56.  
57.    }
58.  
59.  
60.  
61.  jdbcutils.release(co,st,rs);
63. ​
65.    }}

复制代码

查 executeQuery

1. public class TestSlect {
2.  
3.  public static void main(String[] args) throws SQLException {
4.  
5.  
6.  
7.  Connection co = null;
8.  
9.  
10.  
11.  Statement st = null;
12.  
13.  
14.  
15.  ResultSet rs = null;
17. ​
19.  
20.  
21.  
22.  co = jdbcutils.getConnection();
23.  
24.  
25.  
26.  st = co.createStatement();
27.  
28.  
29.  
30.  //sql
31.  
32.  
33.  
34.  String sql = "select * from users where id=1";
35.  
36.  
37.  
38.  rs = st.executeQuery(sql);//查询完毕
39.  
40.  
41.  
42.  while(rs.next()){
43.  
44.  
45.  
46.  
47.  
48.  System.out.println(rs.getString("NAME"));
49.  
50.  
51.    }
52.  
53.  
54.  
55.  jdbcutils.release(co,st,rs);
57. ​
59. ​
61.    }}

复制代码

sql注入题目
sql存在弊端，会被攻击导致数据走漏 sql会被拼接or

3. ​
5. public class TestSlect {
6.  
7.  public static void main(String[] args) {
8.  
9.  
10.  
11.  //login("kuangshen","123456")
12.  
13.  
14.  
15.  login(" 'or'1=1"," 'or' 1=1"); //空大概为1
16.    }
17.  
18.  public static void login (String username,String password) throws SQLException {
19.  
20.  
21.  
22.  Connection co = null;
23.  
24.  
25.  
26.  Statement st = null;
27.  
28.  
29.  
30.  ResultSet rs = null;
32. ​
34.  
35.  
36.  
37.  co = jdbcutils.getConnection();
38.  
39.  
40.  
41.  st = co.createStatement();
42.  
43.  
44.  
45.  //sql
46.  
47.  
48.  
49.  String sql = "select * from users where id=1";
50.  
51.  
52.  
53.  rs = st.executeQuery(sql);//查询完毕
54.  
55.  
56.  
57.  while(rs.next()){
58.  
59.  
60.  
61.  
62.  
63.  System.out.println(rs.getString("NAME"));
64.  
65.  
66.    }
67.  
68.  
69.  
70.  jdbcutils.release(co,st,rs);
72. ​
74. ​
76.    }}

复制代码

3. ​

复制代码

10.2PreparedStatement对象

PreparedStatement可以防止SQL注入，而且服从更高
本质是把转达进来的参数看成字符，假设此中存在转义字符，好比说''会被直接转义
插入

1. public class Testinsert02 {
2.  
3.  public static void main(String[] args) throws SQLException {
4.  
5.  
6.  
7.  Connection co = null;
8.  
9.  
10.  
11.  PreparedStatement st = null;
13. ​
15. ​
17.  
18.  
19.   co = jdbcutils.getConnection();
20.  
21.  
22.   //区别
23.  
24.  
25.  
26.  //使用？占位符代替参数
27.  
28.  
29.  
30.  String sql = "INSERT INTO `users`(id,`NAME`,`PASSWORD`,`email`,`birthday`)values(?,?,?,?,?)";
31.  
32.  
33.   st = co.prepareStatement(sql); //预编译sql ，先写sql，然后不实行
34.  
35.  
36.  
37.   //手动给参数赋值
38.  
39.  
40.  
41.  st.setInt(1,4);
42.  
43.  
44.  
45.  st.setString(2,"qiannan");
46.  
47.  
48.  
49.  st.setString(3,"123456");
50.  
51.  
52.  
53.  st.setString(4,"[email protected]");
54.  
55.  
56.  
57.  //留意点
58.  
59.  
60.  
61.  // sql.date 数据库  java.sql.Date（时间戳）
62.  
63.  
64.  
65.  // util.date  java
66.   new Date().getTime()  得到时间戳
67.  
68.  
69.  
70.  st.setDate(5,new java.sql.Date(new Date().getTime()));
71.  
72.  
73.  
74.  //实行
75.  
76.  
77.  
78.  int i = st.executeUpdate();
79.  
80.  
81.  
82.  if(i>0){
83.  
84.  
85.  
86.  
87.  
88.  System.out.println("插入乐成");
89.  
90.  
91.    }
92.  
93.  
94.  
95.  jdbcutils.release(co,st,null);
96.  
97.  
98.  
99.  
100.    }
102. ​
104. }

复制代码

删除

1. public class TestDelete {
2.  
3.  public static void main(String[] args) throws SQLException {
4.  
5.  
6.  
7.  Connection co = null;
8.  
9.  
10.  
11.  PreparedStatement st = null;
13. ​
15. ​
17.  
18.  
19.  
20.  co = jdbcutils.getConnection();
21.  
22.  
23.  
24.  //区别
25.  
26.  
27.  
28.  //使用？占位符代替参数
29.  
30.  
31.  
32.  String sql = "delete from users where id=?";
33.  
34.  
35.  
36.  st = co.prepareStatement(sql); //预编译sql ，先写sql，然后不实行
37.  
38.  
39.  
40.  //手动给参数赋值
41.  
42.  
43.  
44.  st.setInt(1,4);
46. ​
48.  
49.  
50.  
51.  //留意点
52.  
53.  
54.  
55.  // sql.date 数据库  java.sql.Date（时间戳）
56.  
57.  
58.  
59.  // util.date  java
60.   new Date().getTime()  得到时间戳
61.  
62.  
63.  
64.  st.setDate(5,new java.sql.Date(new Date().getTime()));
65.  
66.  
67.  
68.  //实行
69.  
70.  
71.  
72.  int i = st.executeUpdate();
73.  
74.  
75.  
76.  if(i>0){
77.  
78.  
79.  
80.  
81.  
82.  System.out.println("删除乐成");
83.  
84.  
85.    }
86.  
87.  
88.  
89.  jdbcutils.release(co,st,null);
90.  
91.  
92.  
93.  
94.    }}

复制代码

改

1. public class Testupdate {
2.  
3.  public static void main(String[] args) throws SQLException {
4.  
5.  
6.  
7.  Connection co = null;
8.  
9.  
10.  
11.  PreparedStatement st = null;
13. ​
15. ​
17.  
18.  
19.  
20.  co = jdbcutils.getConnection();
21.  
22.  
23.  
24.  //区别
25.  
26.  
27.  
28.  //使用？占位符代替参数
29.  
30.  
31.  
32.  String sql = "update users set  `name`=? where id=?";
33.  
34.  
35.  
36.  st = co.prepareStatement(sql); //预编译sql ，先写sql，然后不实行
37.  
38.  
39.  
40.  //手动给参数赋值
42. ​
44.  
45.  
46.  
47.  st.setString(1,"qiannan");
48.  
49.  
50.  
51.  st.setInt(2,1);
53. ​
55.  
56.  
57.  
58.  //留意点
59.  
60.  
61.  
62.  // sql.date 数据库  java.sql.Date（时间戳）
63.  
64.  
65.  
66.  // util.date  java
67.   new Date().getTime()  得到时间戳
68.  
69.  
70.  
71.  st.setDate(5,new java.sql.Date(new Date().getTime()));
72.  
73.  
74.  
75.  //实行
76.  
77.  
78.  
79.  int i = st.executeUpdate();
80.  
81.  
82.  
83.  if(i>0){
84.  
85.  
86.  
87.  
88.  
89.  System.out.println("更新乐成");
90.  
91.  
92.    }
93.  
94.  
95.  
96.  jdbcutils.release(co,st,null);
97.    }}

复制代码

查

1. public class TestSelect {
2.  
3.  public static void main(String[] args) throws SQLException {
4.  
5.  
6.  
7.  Connection co =null;
8.  
9.  
10.  
11.  PreparedStatement st = null;
12.  
13.  
14.  
15.  ResultSet rs = null;
16.  
17.  
18.  
19.  co = jdbcutils.getConnection();
20.  
21.  
22.  
23.  String sql = "select *from users where id=?"; //编写sql
24.  
25.  
26.  
27.  st = co.prepareStatement(sql);
28.  //预编译
29.  
30.  
31.  
32.  st.setInt(1,1);//转达参数
33.  
34.  
35.  
36.  //实行
37.  
38.  
39.  
40.  rs
41.  = st.executeQuery();//实行
42.  
43.  
44.  
45.  if(rs.next()){
46.  
47.  
48.  
49.  
50.  
51.  System.out.println(rs.getString("NAME"));
52.  
53.  
54.    }
55.  
56.  
57.  
58.  
59.   jdbcutils.release(co,st,rs);//开释
61. ​
63. ​
65.    }}

复制代码

10.3变乱

要么都乐成，要么都失败
ACID原则
原子性： 要么全部完成，要么都不完成
划一性：总数稳固
隔离性：多个进程互不干扰
题目：
脏读： 一个变乱读取了另一个没有提交的变乱
不可重复读：在同一个变乱内，重复读取表的数据，表数据发生了改变
虚读(幻读)： 在一个变乱内，读取到了别人插入的数据，导致前后读出来的效果差别等

恒久性:一旦提交不可逆

3. ​
5. public class TestTransaction {
6.  
7.  public static void main(String[] args) throws SQLException {
8.  
9.  
10.   Connection co = null;
11.  
12.  
13.   PreparedStatement st =null;
14.  
15.  
16.   ResultSet rs = null;
17.  
18.  
19.  
20.  co = jdbcutils.getConnection();
21.  
22.  
23.  
24.  //关闭数据库的自动提交，自动会开启变乱
25.  
26.  
27.  
28.  co.setAutoCommit(false);
29.  
30.  
31.  
32.  String sql1 = "update account set money  = money-100 where  name = 'A'";
33.  
34.  
35.  
36.  st = co.prepareStatement(sql1);
37.  
38.  
39.  
40.  st.executeUpdate();
42. ​
44.  
45.  
46.  
47.  String sql2 = "update account set money  = money+100 where  name = 'B'";
48.  
49.  
50.  
51.  st = co.prepareStatement(sql2);
52.  
53.  
54.  
55.  st.executeUpdate();
56.  
57.  
58.  
59.  //业务完毕，提交变乱
60.  
61.  
62.  
63.  co.commit();
64.  
65.  
66.  
67.  System.out.println("乐成");
68.  
69.  
70.  
71.  jdbcutils.release(co,st,rs);
72.    }}
74. ​

复制代码

10.4数据库毗连池

数据库毗连 - 实行完毕-开释 毗连--开释 非常浪费体系资源
池化技能：准备一些预先的资源，过来就毗连预先准备好的
最小毗连数
最大毗连数
期待超时

编写毗连池，实现一个接口 DataSource
DBCP
C3P0
Druid： 阿里巴巴
使用了这些数据库毗连池后，我们在项目开辟中就不须要编写毗连数据库的代码了
DBCP
无论使用什么数据源，本质还是一样的， DataSource不会变，方法也稳固