初识mysql数据库（DDL、DQL、DML）

一、初识数据库

1、什么是数据库

数据库叫database ，简称DB，是长期存放在计算机内，有组织、可共享的大量数据的集合；数据库就是存放数据的仓库，专业的数据库系统具有较小的数据冗余度，较高的数据安全性，易扩展性。

数据库设计原则：

mysql主要优势：一个应用对应一个数据库

2、数据库分类

关系型数据库（保证数据的一致性，能建立关系）	非关系型数据库(放在不同的服务器上)
MySQL（中小型企业免费）	MongoDB（文档存储）
Oracle（大型电商网站）	Redis（键值存储）
SQL Server（政府网站，大学教育）	Memcached（键值存储）
maradb（mysql开源版本的一个分支）	Hbase（列存储）
db2（银行系统）	Neo4J（图形）
sybase（被淘汰）

补充：ER关系图

实体用	属性	关系
方框	椭圆	菱形

补充：服务器

服务器是一台电脑，这台电脑安装了服务器软件，这些软件会监听不同的端口号，根据用户访问的端口号提供不同的服务

3、DBMS、DBS、DBA区别

1. DBMS是数据库管理系统，是一种系统软件，包括数据库和用于数据库访问管理的接口系统，通常将DBMS直接称为数据库，严格意义上说mysql属于DBMS
2. DBS是一个实际可运行的系统，由软件、数据库、数据库管理员组成
3. DBA数据库管理员

4、MySQL简介

• 概念

  是现行的开源、免费的关系型数据库

• 特点

  1. 运行速度快
  2. 使用成本低
  3. 容易使用
  4. 可移植性强
  5. 适用更多用户

• mysql语句分类

  DDL	DML	DQL	DCL
  数据定义语言	数据操纵语言	数据查询语言	：数据控制语言，定义访问权限、取消访问权限，安全设置
  create、drop、alter	，insert、update、delete	select	grant

5、mysql的安装、卸载及启动mysql服务

• 安装

mysql5.5安装教程

• 运行安装程序、在启动配置教程以前，一路下一步直到finish

• 第一次finish之后启动服务配置教程

• 第一个induce mysql bin dinctory to windows path

• 第二个端口号不要去修改，字符集要选择utf-8，默认端口号：3306

• 卸载

  • 打开控制控制面板删除软件
  • 删除mysql安装目录的所有文件
  • 删除mysql数据存放的文件，C:\ProgramData\MySQL

• 启动mysql服务方式

  • 任务管理器打开
  • 命令行输入：service(查看本地服务)
  • dos窗口输命令：net start mysql

• MysqlInsranceConfig:配置向导

  • programDate：数据文件的保存路径（默认是隐藏的，在C盘下）
  • mysql的安装目录下的my.ini文件：相应的一些配置，可配置编码格式等
  • mysql -uroot -paaaaa123 进入mysql
  • show databases显示所有的数据库
  • select version()显示mysql版本号

二、DDL语句创建数据库和数据表

1、连接数据库（在dos窗口下）

• cmd——以管理员身份运行

• 启动mysql数据库服务：net start mysql

• 登录mysql：mysql -uroot -paaaaa123（或者：mysql -hlocalhost -uroot -paaaaa123）注意-p以后不能有空格

• exit退出mysql

• show databases;（显示数据库，一定要加分号）

• use mysql

• show tables（显示数据库中的表）

• sql语句select * from user\G;显示user用户列表

• 修改user用户密码：update user set password=password’（1234’ ） where user=‘root’;,flush privileges;刷新下

• ？寻求帮助

• 查看mysql所支持的引擎类型、表类型：SHOW ENGINES;

• 查看默认引擎：SHOW VARIABLES LIKE ‘storage_engine’;

• 显示变量：show variables like’auto%’

2、数据库操作

• 创建数据库

  CREATE DATABASE [IF NOT EXISTS ]数据库名;

• 删除数据库

  DROP DATABASE [IF EXISTS] 数据库名;

• 修改数据库字符集

  alter database 数据库名 character set 字符集

• 查看数据库

  • 查看所有数据库：show databases
  • 查看数据库定义：show create databse 数据库名
  • 查看当前正在使用的数据库：select database()

• 选中数据库

  use 数据库名

3、表结构操作

• 创建表

  • create table 表名(

    ​ 列名 列的类型 列的约束,

    ​ 列名 列的类型 列的约束

    ​ )【表类型】【表字符集】【表注释】

  • 代码示例：

    1. #创建学生表，并设置表类型、字符集
    2. CREATE TABLE `student` (
    3. `id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '主键、学号',
    4. `psd` VARCHAR(20) COLLATE utf8_estonian_ci NOT NULL DEFAULT '123456' COMMENT '密码',
    5. `name` VARCHAR(30) COLLATE utf8_estonian_ci NOT NULL DEFAULT '匿名' COMMENT '学生姓名',
    6. `sex` VARCHAR(2) COLLATE utf8_estonian_ci NOT NULL DEFAULT '男' COMMENT '性别',
    7. `birsday` DATETIME DEFAULT NULL,
    8. `email` VARCHAR(20) COLLATE utf8_estonian_ci DEFAULT NULL,
    9. PRIMARY KEY (`id`)
    10. ) ENGINE=INNODB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_estonian_ci

    复制代码

• 注释

  1. 字段注释： comment'注释的内容'
  2. 表注释：comment='注释的内容'

  复制代码

• 列的约束

  primary key 主键约束

  ​ unique : 唯一约束

  ​ not null 非空约束

• 注意事项

  1. 除了最后一个列不用逗号隔开，每一列都要逗号隔开

  2. 表名如果变蓝了表明和数据库有冲突，这时候要加上反引号·

  3. 字符串类型的要用单引号引起来

  4. 有多个属性的时候用空格隔开就好

  5. commend后面加单引号注释内容

  6. #代表整个注释

  7. 设置严格检查模式

     1. SET sql_mode='strict_trane_tables';

     复制代码

• 列类型

  • 数值型
    1. tinyint：非常小的数据
    2. int：标准整数
    3. double：双精度浮点数
    4. decimal【（M,D）】：字符串形式浮点数，M表示总位数，D表示保留小数位数
  • 字符串型
    1. char：固定长度
    2. vachar：可变长度，括号内代表的是字符的个数
    3. text：文本串
  • 日期和时间
    1. datetime：日期和时间，默认是null
    2. timestamp：常用于显示当前时间，默认是当前日期和时间
  • Null值
    1. 理解为没有值或未知值
    2. 不要用null进行算术运算

• 表的类型

  常见的MyISAM，InnoDB

  名称	MyISAM	InnoDB
  事务处理	no	yes
  外键约束	no	yes
  数据行锁定	no	yes
  全文索引	yes	no
  表空间大小	较小	较大

• 数据表的存储位置

  都位于C:\ProgramData\MySQL目录下

  MyISAM	InnoDB
  只有一个frm文件	frm、wyd、myi三个文件

• 数据字段属性

  1. unsigned：无符号，声明该数据不允许为负数
  2. zerofill：0填充，不足位数用0填充，如int（3）5则为005
  3. auto_increment：自动增长，常用于设置主键，且为整数，定义起始值和步长：
     • 定义起始值：auto_increment=100(建表时在右括号右边写，影响的是当前表，对整个数据库的其他表没有影响
     • 定义自增步长：set auto_increment_ncrement=5，会对所有数据库受影响
  4. null和not null ：默认为null，若设置为not null则必须有值
  5. default：设置默认值

• 删除表

  drop table 表名

• 修改表

  1. #修改表名
  2. ALTER TABLE 表名 RENAME AS test1;
  3. #修改表的字符集
  4. alter table 表名 character set 字符集
  5. #添加列
  6. ALTER TABLE 表名 ADD 列名 列类型 属性 ;
  7. #修改列
  8. alter table 表名 modify 列名 列类型 属性 ;
  9. #修改列名
  10. alter table 表名 change 旧列名 新列名 列类型 属性 ;
  11. #删除列
  12. alter table 表名 dop 列名

  复制代码

• 查看表

  1. 查看当前数据库所有的表名
  2. show tables
  3. 查看表的定义结构/创建语句
  4. show create table 表名
  5. 查看表的结构
  6. desc 表名

  复制代码

4、保存sql文件以及打开sql文件

保存Ctrl+s

新建查询编辑器——在同一标签中打开文件（在不同标签中打开）

对于图像声音视频用text、blob存储数据

一般没有必要直接存储图像，而存储的是路径用字符串

---

三、DML语句对表中数据CRUD操作

1、主键&外键

• 主键咋一个表里面可有也可以没有，

• 一个表里面的外键既可以有一个也可以有多个，

• 建立主外键关系的有主键的表为主表，有外键的表成为子表或者从表

• 能够保证数据的完整性和 正确性

• 对于有主外键关系的表，数据插入操作要有一定顺序，先放主键数据，再放外键数据 ，先删除子表，再删除主表

• 主键和外键，列名可以不同，但是数据类型一定要相同

• 主键和外键建立联系第一种方法在建表的时候就联系，关键词是constraint，外键名一般以FK开头 foreign key代表外键，引用主键用关键词reference

• 主键&外键相关的SQL语句

  1. 创建主键&外键
  2. 1、创建此表的同时就创建外键
  3. 主键：PRIMARY KEY可写在列后面，也可以字段都写完了在最后一行写明PRIMARY KEY（字段名 ）
  4. 外键：CONSTRAINT FR_gradeId FOREIGN KEY (要添加主键的字段) REFERENCES 引用表(引用表中的哪个字段)（FR_gradeId字段名）
  5. 2、创建字表完毕后，修改子表增加外键
  6. ALTER TABLE 表名
  7. ADD CONSTRAINT 外键名 FOREIGN KEY (要添加主键的字段) REFERENCES 引用表(引用表中的哪个字段);
  8. 删除外键
  9. ALTER TABLE 表名 DROP FOREIGN KEY 外键名;
  10. （删除了之所以还能看到外键在表中，是因为外键会默默的创建了一个索引，要想彻底删除还要加上一句话删除索引
  11. ALTER TABLE 表名 DROP INDEX 外键名;
  12. 删除外键要有两句话才能彻底删除）

  复制代码

2、插入数据

• insert into 表名（列名1，列名2） values（值1，值2）

• insert into 表名 values（值1，值2）

• insert into 表名（列名1，列名2） values（值1，值2），（值1，值2）

  批量插入数据

• 注意点：

  1. 批量插入比单条插入效率更高，但是更容易出现问题
  2. 插入全部列名可以省略，插入部分列的话就不能省略表名后面的列名。
  3. 有默认值的时候在values里面可以写默认值也可以写default
  4. 如果不写列名的情况下即使有些列可以为空，但是不能不写要写null，最好都写上列名，如果外键值超过了主表的主键数目，会报错

3、删除数据

• delete from 表名 【where条件】
• truncate table 表名
• delete 和 truncate 的区别
  1. delete 是DML语句 一条一条删除表中数据
  2. truncate是DDL 先删除表再重建表
  3. 关于哪条执行效率高，具体要看表中的数据量，如果数据比较少，delete高效，数据多，则truncate效率高
  4. 当使用不带where条件的delete from删除数据时，自增当前数值依然从原来基础上进行，会记录日志，不删除表结构，但使用truncate table删除表中全部数据的时候，自增当前数值不从原来基础上进行，从原来自增初始值开始。如下例子：不会记录日志
  5. 两种不同村粗引擎，在使用delete from删除全部数据后，自增列初始值表现的不同：同样使用delete from清空数据，重启数据库服务后，对于INNODB的表，自增列从初始值从新开始（比如开始默认初始值是1，那就从1再开始），而对于MYISAM型的表，自增序列从上一个自增数据基础上开始，原因是：INNODB的数据是储存在内容中的，重启数据可服务后，之前的也就都没有了，而MYISAM是储存在文件中，就算重启，数据依然存在。

4、修改数据

• update 表名 set 列名=值【where条件】

四、DQL查询语句

1、AS为字段取别名

AS可省略

2、Dinstinct去重复项

select distinct 字段名 from 表名

3、通用格式

1. select [distinct] [*] [列名1,列名2] from 表名 where 条件
2. group by...
3. having
4. order by

复制代码

4、基础查询

1. #查询数据用DQL语句
3. #1、核心查询（*代表查询所有列，效率比较低）
4. SELECT * FROM student;
6. #2、查询指定列，查询student的学号和姓名,在mysql中列名不区分大小写
7. SELECT studentno,studentname FROM student;
8. #3、被查出的列取别名（给列取别名as可以省略）
9. SELECT studentno AS 学号,studentname AS 姓名 FROM student;
11. SELECT studentno 学号,studentname 姓名 FROM student;
13. #6、给表取取别名（给表取别名as可以省略），别名不会该表原表的列名，只是为了看的
14. SELECT studentno 学号,studentname 姓名 FROM student AS 学生表;
16. SELECT studentno 学号,studentname 姓名 FROM student 学生表;
17. #7、as为查询结构（如函数）取一个新的名字
18. SELECT CONCAT('姓名：',studentName)AS 新姓名 FROM student;
20. #8、查看那些同学参加了考试（学号）,去除重复项，用关键字distinct，默认为all代表查询所有行
21. SELECT DISTINCT studentno FROM result;
22. 注意：distinct一定写前面
23. SELECT DISTINCT NAME NAME,english FROM exam;
25. #9、select中可以出现表达式
26. SELECT @@auto_increment_increment;
27. SELECT VERSION();
28. SELECT 100*3-1 AS 计算结果
30. SELECT studentno,studentresult+1 AS 提分后 FROM result;
32. #10、满足条件的查询(where) 考试成绩95-100
33. SELECT studentno,StudentResult
34. FROM result
35. WHERE StudentResult>=95 AND StudentResult<=100;

复制代码

5、模糊查询

1. #13、精确查询
2. SELECT studentno,StudentResult
3. FROM result
4. WHERE studentno=1000;
6. #15、查询除了1000号这个学生，其他学生的考试成绩！=
7. SELECT studentno,StudentResult
8. FROM result
9. WHERE studentno !=1000;
10. #16、或者用not和！=一样
11. SELECT studentno,StudentResult
12. FROM result
13. WHERE NOT studentno =1000;
15. #17、模糊查询between and/ like/in/null
16. #查询姓李的同学的学号和姓名
17. #like及结合使用的通配符，%（0到任意一个字符） _(一个字符)
19. #查询所有姓李的同学
20. SELECT studentno,StudentName
21. FROM student
22. WHERE StudentName LIKE '李%';
23. #查询姓李的，名字是三个字的
24. SELECT studentno,StudentName
25. FROM student
26. WHERE StudentName LIKE '李__';
28. #查询所有带有“文字的”（不管开头和结尾）常用
29. SELECT studentno,StudentName
30. FROM student
31. WHERE StudentName LIKE '%文%';

复制代码

1. 转义字符%——\%
2. _——\_
4. 如何不想用\作为转义字符，可以自定义转义字符，使用自己定义的转衣服：escape ‘你定义的转义符’但是%和_是不可以自行设计的
6. #模糊查询之 in
7. SELECT studentName,studentNo FROM student
8. WHERE studentNo IN(1000,1001,1002,1003);
10. SELECT studentName,studentNo FROM student
11. WHERE Address IN('北京','南京');
14. #模糊查询之 null
15. 注意：只有是显示null的才为空，对于空白什么都没有的并不是空null而是字符串为空，如：

复制代码

1. #查询家庭住址没有写的同学
2. #1、错误用法
3. SELECT studentname FROM student
4. WHERE Address IS NULL;
5. #2、用空字符串
6. SELECT studentname FROM student
7. WHERE Address='';
8. #3、一般这种类型的查询用这种写法
9. SELECT studentname FROM student
10. WHERE Address='' OR Address IS NULL;

复制代码

1. #查询出生日期没有填写的同学，=null是错误的，和null比较要用iis null
2. SELECT studentno FROM student
3. WHERE BornDate IS NULL;
5. #对于不是空的要用is not null
6. SELECT studentname FROM student
7. WHERE BornDate IS NOT NULL;

复制代码

6、分组查询

1. #不同课程的平均分、最高分、最低分
2. SELECT subjectname, AVG(StudentResult) AS 平均分,MAX(StudentResult) AS 最高分,MIN(StudentResult) AS 最低分
3. FROM result r
4. INNER JOIN `subject` sub
5. ON r.SubjectNo=sub.SubjectNo
6. GROUP BY r.subjectno
8. #分组后再筛选执行的sql语句筛选
9. SELECT subjectname, AVG(StudentResult) AS 平均分,MAX(StudentResult) AS 最高分,MIN(StudentResult) AS 最低分
10. FROM result r
11. INNER JOIN `subject` sub
12. ON r.SubjectNo=sub.SubjectNo
13. GROUP BY r.subjectno
14. HAVING 平均分>80
15. ORDER BY 平均分 DESC
16. LIMIT 0,4

复制代码

7、连接查询

内连接	左连接	右连接	自连接	等值连接	非等值连接
nner join	left join	right join	\	\	\
查询两个表结果集中的交集	以左表为基准，右边表来一一匹配，匹配不上的返回左表记录，右表以null填充	以右表为基准，右边表来一一匹配，匹配不上的返回左表记录，左表以null填充	自连接的表一定要取别名（AS）子栏目父栏目问题	和内连一样，只是写法不同	区别于等值连接，不加where

1. 等值连接：
2. SELECT s.StudentNo,StudentName,subjectno,StudentResult
3. FROM student s,result r
4. WHERE s.StudentNo = r.StudentNo;

复制代码

1. #自连接代码
2. CREATE TABLE IF NOT EXISTS category(
3. categoryId INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '当前栏目',
4. pId INT(10) NOT NULL COMMENT '当前栏目的父栏目',
5. categoryName VARCHAR(50) NOT NULL,
6. PRIMARY KEY (categoryId)
7. )
8. INSERT INTO category
9. VALUES (2,1,'美术设计'),
10. (3,1,'软件开发'),
11. (4,3,'数据库基础'),
12. (5,2,'ps基础'),
13. (7,2,'色彩搭配'),
14. (8,3,'PHP基础'),
15. (9,3,'java基础');
19. SELECT * FROM category;
20. SELECT categoryName '知足上进' FROM category;
22. #将各个栏目的父子栏目显示出来（父栏目名称 子栏目名称）
23. SELECT a.categoryName AS '主栏目',b.categoryName AS '子栏目'
24. FROM category AS a,category AS b;
25. WHERE a.categoryId=b.pId;

复制代码

• 一定要先写连接查询再写where

• 多重嵌套连接：

  注意：inner join ,和from以及on后面都不能用,隔开，否则会报1064错误

  1. #参加了参加了考试的同学（学号、学生姓名、科目名、分数）
  2. SELECT s.StudentNo,StudentName,subjectname,StudentResult
  3. FROM student AS s
  4. INNER JOIN result AS r
  5. ON s.StudentNo = r.StudentNo
  6. INNER JOIN `subject` AS sub
  7. ON r.SubjectNo=sub.SubjectNo;

  复制代码

8、排序

group by having

1. #查询《数据库结构—1》的所有考试的同学（学号、学生姓名、科目名、分数）
2. SELECT s.StudentNo,StudentName,subjectname,StudentResult
3. FROM student AS s
4. INNER JOIN result AS r
5. ON s.StudentNo = r.StudentNo
6. INNER JOIN `subject` AS sub
7. ON r.SubjectNo=sub.SubjectNo
8. WHERE SubjectName='数据库结构-1'
9. ORDER BY StudentResult DESC,studentno DESC #1、默认升序asc 2、desc为降序
10. #常见错误：ORDER BY StudentResult ,studentno DESC 这时候StudentResult升序，studentno降序，desc是就近原则

复制代码

9、分页查询

• limit 索引，页容量
• 当前页码-1）*页容量，页容量

1. #分页查询
2. ##查询《数据库结构—1》的所有考试的同学（学号、学生姓名、科目名、分数）
3. #每页显示五条数据出来
4. SELECT s.StudentNo,StudentName,subjectname,StudentResult
5. FROM student AS s
6. INNER JOIN result AS r
7. ON s.StudentNo = r.StudentNo
8. INNER JOIN `subject` AS sub
9. ON r.SubjectNo=sub.SubjectNo
10. WHERE SubjectName='数据库结构-1'
11. ORDER BY StudentResult DESC,studentno DESC
12. #limit 0,5 #从哪条记录开始起始行0，要显示几行
13. LIMIT 5,5 #第二页
14. LIMIT 10,5 #第三页 （等于linit 5 offset 0)

复制代码

10、子查询

• 定义：子查询就是在查询语句中的where条件子句中，又嵌套了另外一个select查询语句

1. #子查询
2. #分部写简单sql语句，然后去嵌套
3. SELECT studentno ,studentname FROM student WHERE studentno IN()
4. SELECT studentno FROM result WHERE StudentResult>=80 AND subjectno=()#1\2\3\4
5. SELECT subjectno FROM `subject` WHERE SubjectName='高等数学-2' #2
8. SELECT studentno, studentname FROM student WHERE studentno IN(
9. SELECT studentno FROM result WHERE StudentResult>=80 AND subjectno=(
10. SELECT subjectno FROM `subject` WHERE SubjectName='高等数学-2' ))

复制代码

五、mysql函数

1、数学函数

ABS（)	CEILING()	FLOOR()	RAND()	RAND()
绝对值	大于等于我的最小的整数	小于等于我的最大的整数	返回0-1之间的随机数	每次生成固定的0-1之间的随机数(以某个数作为种子)
SELECT ABS(-8) ‘-8的绝对值’	SELECT CEILING(9.8)=10	SELECT FLOOR(9.8)=9	\	\

2、字符串函数

1. #返回字符串中包含的字符数
2. SELECT CHAR_LENGTH ( '好好学习数据库')
3. #合并字符串
4. SELECT CONCAT('我','爱')
5. #替换字符串，从某个位置开始，替换某个长度，替换的内容
6. SELECT INSERT('我爱你婺源',1,3,'很爱')
7. SELECT INSERT('我爱你婺源',1,3,'很爱') #如果起始位置超过字符串长度，则返回原长度
8. #变小写
9. SELECT LOWER('I LOVE YOU')
10. #变大写
11. SELECT UPPER('i love you')
12. #从左边截取指定长度的字符串
13. SELECT LEFT('我爱你中国',3)
14. ##从右边截取指定长度的字符串
15. SELECT RIGHT('我爱你中国',3)
16. #替换字符串（要替换的字符串，要替换的文字，替换的内容）
17. SELECT REPLACE('中国欢迎你，你好','你','你们')
18. #截取(从哪个位置开始截取，截取多长）
19. SELECT SUBSTRING('中国欢迎你，你好',1,2)
20. #反转
21. SELECT REVERSE('中国欢迎你')

复制代码

3、日期和时间函数

1. #获得当前日期
2. SELECT CURRENT_DATE()
3. SELECT CURDATE()
4. #获得当前日期和时间
5. SELECT NOW()
6. SELECT LOCALTIME()
7. SELECT SYSDATE()
8. #分别获取日期中的某个部分
9. SELECT YEAR(NOW())
10. SELECT MONTH(NOW())
11. SELECT DAY(NOW())
12. SELECT HOUR(NOW())
13. SELECT MINUTE(NOW())
14. SELECT SECOND(NOW())

复制代码

4、系统信息的函数

1. SELECT VERSION()
2. SELECT USER()

复制代码

5、聚合函数

1. #聚合函数：返回的是一个值，不要出现多个值
2. #count（字段名） 是非空值的计数
3. count(*)返回所有列的统计
4. SELECT COUNT(StudentName) FROM student
5. SELECT COUNT(1) FROM student
6. #sum()求总和
7. SELECT SUM(StudentResult) AS 总分 FROM result
8. SELECT AVG(StudentResult) AS 平均分 FROM result
9. SELECT MAX(StudentResult) AS 最高分 FROM【】 result
10. SELECT MIN(StudentResult) AS 最低分 FROM result

复制代码

count（*)尽量少用

六、MySql事务

1、事务定义

事务就是将一组sql语句放在同一批次内去执行，如果一个sql语句错误，则该批次的所有sql语句都将取消执行，最能理解的就是银行转账

注意： mysql事务只支出innoDB和BDB数据表类型

2、事务的ACID原则

• 原子性（A）

  组sql语句是当做一个整体执行的，不能单独执行其中的某一条，要么全部成功，要么全部失败

• 一致性（C）

  要么都是事务提交前的状态，要么都是事务提交以后的状态，不可能存在事务在中间的什么状态

• 隔离性（I）

  每一个事务处理之间互不影响，彼此独立和透明，事务间不能交叉

• 持久性（D）

  事务一旦提交成功，对事务的影响是永久 的

3、mysql事务实现方法

• set autocomment=0关闭mysql的自动提交

• start transaction

  开启一个事务

• 执行的sql语句

• commit/rollback

  提交或者回滚

• set autocomment=1

  还原mysql默认的自动提交

1. 代码示例：
2. #使用事务模拟实现转账
3. CREATE TABLE IF NOT EXISTS account(
4. id INT(4) PRIMARY KEY AUTO_INCREMENT,
5. `name` VARCHAR(32) NOT NULL,
6. cash DECIMAL(9,2) NOT NULL
7. );
8. INSERT INTO account (`name`,cash)
9. VALUES ('A',2000),('B',10000);
11. SELECT * FROM account;
12. DELETE FROM account;
14. #没有异常情况时候的事务
15. SET autocommit=0;
17. START TRANSACTION;
18. UPDATE account SET cash=cash-500 WHERE `name`='A';
19. UPDATE account SET cash=cash+500 WHERE `name`='B';
20. COMMIT;
22. SET autocommit=1;
24. #有问题时候的事务用rollback撤销，回到事务开始最初的状态
25. SET autocommit=0;
27. START TRANSACTION;
29. UPDATE account SET cash=cash-500 WHERE `name`='A';
30. UPDATE account SET cash=cash+500 WHERE `name`='B';
31. ROLLBACK；
33. SET autocommit=1;

复制代码

七、MySql索引和视图

1、索引分类

主键索引	唯一索引	常规索引	全文索引
primary key	unique	index	fulltext
避免同一个表中某数据列的值重复		不宜添加过多常规索引	只能英语myisam，并且只能是vachar、char、test类型

唯一索引和主键索引区别：

• 主键索引只有一个、唯一索引可以有多个
• 主键索引非空，唯一索引可以null
• 一个列上有很多索引，数据库会去选一个效率高的索引执行

2、添加索引

1. #添加索引
2. #方式一：在创建表申明列的时候添加上
3. CREATE TABLE text1(
4. id INT(3) PRIMARY KEY,
5. testno VARCHAR(10) UNIQUE,
6. c VARCHAR(50),
7. d VARCHAR(20),
8. e TEXT,
9. INDEX `index_c`(c,d),
10. FULLTEXT(e)
11. )ENGINE MYISAM
13. ***添加常规索引的时候，可以添加多个列为常规索引，写在前面的列有优先权，索引名字用``隔开，而不是逗号。
15. #方式二，将所有列都申明完毕后再添加索引，统一添加索引
17. CREATE TABLE text2(
18. id INT(3) ,
19. testno VARCHAR(10) ,
20. c VARCHAR(50),
21. d VARCHAR(20),
22. e TEXT,
23. PRIMARY KEY(id),
24. UNIQUE KEY(testno),
25. INDEX `index_c`(c,d),
26. FULLTEXT(e)
27. )ENGINE MYISAM
29. #方式三：创建表完毕后修改表的时候去添加索引
30. CREATE TABLE text3(
31. id INT(3) ,
32. testno VARCHAR(10) ,
33. c VARCHAR(50),
34. d VARCHAR(20),
35. e TEXT
36. )ENGINE MYISAM
37. ALTER TABLE text3 ADD PRIMARY KEY(id);
38. ALTER TABLE text3 ADD UNIQUE KEY(testno);
39. ALTER TABLE text3 ADD INDEX(c,d);
40. ALTER TABLE text3 ADD FULLTEXT(e);

复制代码

• 注意：EXPLAIN SELECT * FROM student WHERE studentno=‘1000’，explain代表的是查询查询的具体明细，包括如下信息：

• 全文索引，你要设置全文索引的列，查询的条件不能超过全文数据的50%，否则全文索引就没用了（只能用在字符串类型varchar和text上，只能用于MYISAM）

• 设置全文索引

  1. ALTER TABLE student ENGINE=MYISAM;
  2. EXPLAIN SELECT * FROM student WHERE MATCH(StudentName) AGAINST('李%');

  复制代码

3、显示索引信息

• 在目录index下可以看到
• 利用sql语句显示索引信息：SHOW INDEX FROM student;

4、删除索引

1. 1、DROP INDEX 索引名 ON 表名;
2. DROP INDEX testno ON text3;
3. 2、ALTER TABLE 表名 DROP INDEX e索引名
4. ALTER TABLE text3 DROP INDEX e;
5. 3、#删除主键索引
6. ALTER TABLE 表名 DROP PRIMARY KEY;
7. ALTER TABLE text3 DROP PRIMARY KEY;

复制代码

5、复合索引

A-B-C先把A用到

6、索引准则

• 选择建立索引的列
  1. 频繁搜索的列
  2. 经常用作查询的列
  3. 经常排序、分组的列
  4. 经常用作连接的列（主键、外键）
• 不介意使用索引的列
  1. 仅包含几个不同值的列
  2. 小型表

7、视图

• 视图是保存在数据库中的select查询，是一种虚拟表，使用视图原因，一个是出于安全考虑，用户不必看到整个数据库的结构，另一个是复合用户日常业务逻辑

• 创建视图

• 1. create view 视图名
  2. AS 查询语句

  复制代码

• 删除视图
  drop view 视图名

• 查看视图

  select 字段1，字段2 from 视图名

• 注意事项

  1. 每个视图中可以使用多个表
  2. 一个视图可以嵌套另一个视图，单最好不要超过三层
  3. 对视图进行增加、修改、删除操作会直接影响表中数据
  4. 当视图来自多个表时，不允许添加和删除数据
  5. 选中系统数据库information_schnma，然后 select * from vieww;可查看所有视图

八、Mysql数据的备份与恢复

1、使用命令mysqldump备份

1. mysqldump是和mysql同级的一个命令！
2. 备份整个数据库
3. mysqldump -uroot -p school>E:/java/beifen/school.sql
4. 执行备份：
5. *密码可以先不P输出，最后以密码文的形式
6. *>千万别丢了，如果丢了，会报mysqldump couldnot find。。。。
7. 备份特定的表
8. mysqldump -uroot -p school grade student >E:/java/beifen/school1.sql
9. mysqldump设置之指定哪些语句不显示
10. mysqldump -uroot -p --skip-add-drop-table school grade student >E:/java/beifen/school2.sql
11. mysqldump设置之显示insert into加上列名
12. 加上一个-c即可
13. mysqldump -uroot -p --skip-add-drop-table -c school grade student >E:/java/beifen/school2.sql
14. 备份文件解析：
15. 1、/*和*/包裹的数据代表是可以被mysql执行的注释，但是不能被其他数据库执行
16. 2、--包围的是注释
17. 3、 !40101是版本号
19. **mysql --help|more会以一屏显示，一行一行的看

复制代码

2、使用mysql底下的脚本source

1. 1、进入mysql
2. mysql -urrot -p
3. 2、选择要备份到哪里
4. use test
5. 3、选择要备份的数据库
6. source E:/java/beifen/school.sql

复制代码

3、使用sqlyong工具备份

注意点：1、打勾的时候一定要注意，不要勾选use database和carat database

4、用sql命令进行备份数据

1. #将school库中的student表中的学号和姓名这两列备份出去
2. USE school;
3. #注意：备份出去的文件不可以提前存在
4. SELECT studentno,StudentName INTO OUTFILE 'E:/java/beifen/student.sql' FROM student;
6. #将备份出去的数据导入到test库中的stable表里来
7. USE test;
8. CREATE TABLE stable(
9. id INT(4),
10. sname VARCHAR(20)
11. )
12. LOAD DATA INFILE 'E:/java/beifen/student.sql' INTO TABLE stable(id,sname);
14. SELECT * FROM stable;

复制代码

九、数据库设计

1、设计目的

• 效率高
• 节省数据的储存空间
• 便于进一步扩展
• 使应用程序的开发变得更容易

2、设计数据库的步骤

• 收集信息
• 标识实体
• 标识每个实体之间的关系

3、绘制E-R图

矩形表示实体、椭圆表示属性、菱形表示关系

4、绘制数据库模型图

用Visio2007版本及以下版本绘制更好

5、三大范式

• 第一范式（1NF）

  目标是确保每列 原子性，如果每列或者每个属性值都是不可再分的最小数据单元，则满足第一范式

• 第二范式（2NF）

  1. 一个表只描述一件事情，目标是确保表中的每列都和主键相关
  2. 如果一个关系满足第一范式，并且除了主键以外的其他键全部依赖于主键，则满足第二范式

• 第三范式（3NF）

  目标是确保每列值都和主键直接相关，而不是间接相关，如果一个关系满足第二范式，并且除了主键以外的其他列都只能依赖于主键，列于列之间不存在相互依赖关系

十、JDBC连接数据库

1、JDBC概念

JDBC是实现java程序对各种数据库的访问，是一组类和接口，位于java.sql与javax.sql包

2、通过JDBC连接数据库

1. //1、加载驱动
2. Class.forName("com.mysql.jdbc.Driver");
3. //2、建立连接
4. String url="jdbc:mysql://localhost:3306/myschool";
5. String user ="root" ;
6. String password = "aaaaa123";
7. con= DriverManager.getConnection(url, user, password);
8. //创建sql语句
9. String sql ="SELECT studentno,studentName FROM student";
10. //创建对象
11. pstm =con.prepareStatement(sql);
12. //执行sql语句
13. rs=pstm.executeQuery();
14. //处理结果
15. System.out.println("编号：\t姓名：");
16. while(rs.next()) {
17. System.out.println(rs.getInt("studentno")+"\t"+rs.getString("studentName"));
18. }
19. //关闭资源
20. rs.close();
21. pstm.close();
22. con.close();

复制代码

Class.forName() 后加载 反射，事先不知道加载哪个类，运行时进行加载（桥接） 而Student stu =new Student() 先加载类

3、使用JDBC操作数据库（增删改查）

1. //增加数据
2. import java.sql.Connection;
3. import java.sql.DriverManager;
4. import java.sql.PreparedStatement;
5. import java.sql.ResultSet;
6. import java.sql.SQLException;
7. import java.sql.Timestamp;
9. public class Test3 {
11. public static void main(String[] args) {
12. ResultSet rs =null;
13. PreparedStatement pstm=null;
14. Connection con =null;
15. //1、加载驱动
16. try {
17. Class.forName("com.mysql.jdbc.Driver");
18. //2、建立连接
19. String url="jdbc:mysql://localhost:3306/kgcnews";
20. String user ="root" ;
21. String password = "aaaaa123";
22. con= DriverManager.getConnection(url, user, password);
23. //创建sql语句
24. String sql ="INSERT INTO news_category (NAME,createDate) VALUES (?,?)";
25. //创建对象
26. pstm =con.prepareStatement(sql);
27. //给占位符赋值
28. pstm.setString(1, "哈哈");
29. pstm.setTimestamp(2, new Timestamp(10));
30. //执行sql语句
31. int i=pstm.executeUpdate();
32. //处理结果
33. if(i>=1) {
34. System.out.println("增加成功");
35. }else {
36. System.out.println("未增加");
37. }
38. } catch (ClassNotFoundException e) {
39. e.printStackTrace();
40. } catch (SQLException e) {
41. e.printStackTrace();
42. }finally {
43. if(null !=pstm) {
44. try {
45. pstm.close();
46. } catch (SQLException e) {
47. e.printStackTrace();
48. }
49. }
50. if(null !=con) {
51. try {
52. con.close();
53. } catch (SQLException e) {
54. e.printStackTrace();
55. }
56. }
57. }
58. }
59. }

复制代码

增删改操作类似，只是替换sql语句即可

十一、思维导图

1、更改

2、全景

十二、Readme

---

本文章已同步至我的GitHub仓库：Javastudy,期待您的加入?

本文章已同步至苍何的个人博客,可以直接在博客上留言哦?

来我的微信公众号玩耍呗?

扫码无套路关注我的CSDN博客?