MySQL数据库基础操作

第一章

1、常用命令

连接数据库：

mysql -uroot -p 密码
mysql -uroot -p 回车
输入密码：******

查看数据库服务中的所有数据库：

show databases;

选中数据库

use 数据库名称;

当运行完毕后，出现database changed;

当出现以上信息时，表示选中了指定的数据库

查看指定数据库的所有数据表

show tables;

查看表格中的数据信息

select * from 表名;

查看创建数据表的语句

show create table 表名;

查看表的详细信息（字段，字段类型， 属性等信息）

desc(describe) 表名;

2、创建数据库

create database 数据库名称;

3、创建数据表

create table 表名(
	字段名1 字段类型1 属性 索引 注释,
    字段名2 字段类型2 属性 索引 注释,
    字段名3 字段类型3 属性 索引 注释
)

注意：

如果字段是以后一个的时候，不能在末尾添加“,”，否则sql语句报错！

在使用sqlYog时可以使用Tab键进行提示！

如果想执行别人给你的sql脚本的话：

1、直接复制+粘贴

2、将sql文件拖进来，但是并没有执行结果

3、root@localhost 上右键——> 执行sql脚本——>选中需要执行的sql脚本

此时会将sql文件中的内容进行执行，不单单是sql语句的导入，而是结果的执行！

###3.1、创建myisam类型的表
CREATE TABLE test2(
id INT(4) PRIMARY KEY,
name VARCHAR(50) NOT NULL
)ENGINE = MYISAM;

注：

1、myisam类型的表在存储的时候有以下三个文件：

​ *.frm ：数据结构文件

​ *.MYD ：数据文件

​ *.MYI ： 索引文件

2、innodb类型：

​ *.frm

​ 在上一级目录存在一个ibdata1 文件

###3.2、修改表名 AS 可以省略

ALTER TABLE student RENAME AS student2;

3.3、复制表结构as和like的区别

对于MySQL的复制相同表结构方法，有create table as 和create table like 两种，区别是什么呢？

create table t2 as select * from t1 where 1=2;
或者 limit 0;

as创建出来的t2表（新表）缺少t1表（源表）的索引信息，只有表结构相同，没有索引，会拷贝数据到新表中。

create table t2 like t1 ;

like 创建出来的新表包含源表的完整表结构和索引信息，不会拷贝数据到新表中。

二者的用途：

​ as用来创建相同表结构并复制源表数据。
​ like用来创建完整表结构和全部索引。

​ oracle支持as，也是只有表结构没有索引，oracle不支持like。
​ 两种方式在复制表的时候均不会复制权限对表的设置。比如说原本对表B做了权限设置，复制后，表A不具备类似于表B的权限。

##4、添加字段

ALTER TABLE test2 ADD address VARCHAR(50) NOT NULL;

5、修改字段 modify（只修改属性）

ALTER TABLE test2 MODIFY address CHAR(20) NULL;
DESC test2;

##6、修改字段 change（可更改字段名）

ALTER TABLE test2 CHANGE address addr VARCHAR(50) NOT NULL DEFAULT '和平大道';

##7、删除字段

ALTER TABLE test2 DROP addr;

注：

​ 1、删除表 属于DDL语句 （数据定义语言）

drop table 表名

​ 2、删除字段 （属于DDL语句）

alter table 表名 drop 字段名

​ 3、删除数据，（删除一行数据，或者删除全部数据）

delete 

第二章

1、外键：

**作用：**进行数据的约束，例如 student 和grade

grade 表是主表，student表是从表

由grade表对student表进行约束，student表中的数据限制于grade表

**即：**假如年级表中只有1、2、3三个年级，那么在建立了外键之后，学生表中不能随意添加gradeId字段的值。该值必须与grade表中的id（1、2、3）一致才可以！

2、创建外键语法：

方式一：通过外部添加！

alter table student add constraint fk_grade_student FOREIGN KEY(gradeId) REFERENCES grade(`id`);

通过在创建表的时候就添加主外键约束

方式二：通过创建表时规定约束字段

CREATE TABLE student(
	`id` INT(4) PRIMARY KEY AUTO_INCREMENT COMMENT '学生id',
	`name` VARCHAR(30) NOT NULL COMMENT '学生姓名',
	`address` VARCHAR(50) COMMENT '家庭住址',
	`gradeId` INT(4) NOT NULL,
	CONSTRAINT fk_grade_student FOREIGN KEY(gradeId) REFERENCES `grade`(gradeId)    
)COMMENT = '学生表';

3、删除外键

alter table student drop FOREIGN KEY fk_grade_student;

执行以上脚本后发现外键还存在！需要将外键索引删除，才可以彻底删除外键

ALTER TABLE student DROP INDEX fk_grade_student

通过sqlYog删除外键亦是如此！

4、添加完外键约束后删除表

如果直接删除grade表

DROP TABLE grade;

会报以下异常：

Cannot delete or update a parent row: a foreign key constraint fails

意思：不能删除主表，原因是有外键关联

所以在添加完外键之后，应该先删除从表，再删除主表！

5、添加数据

1）语法：

insert into 表名[(字段1，字段2，字段3，·····)] values(值1，值2，值3，····)

注：

​ 1、如果不添加字段，那么要按照表中字段的类型一一对应去添加

​ 2、如果指定字段，那么要按照指定字段的类型一一对应去添加

​ 3、可以通过一条insert语句添加多条数据，多个值列表之间用“，”隔开即可！

例如：

INSERT INTO student(`name`,`gradeId`) VALUES('翠花',1),(NULL,NULL),('刘能',3);

6、修改数据

1）语法：

UPDATE student SET `name`= '王大拿',gradeId = 3 WHERE id = 8;

注：

​ 1、如果不添加where条件，那么将会把数据表中所有的记录信息全部更新为指定信息，不允许！

​ 2、如果设置多个条件，可以通过“，”将多个条件隔开！

7、BETWEEN、 AND、 OR、IN

查询用户id在5到7之间的用户信息

SELECT * FROM student WHERE id >= 5 AND id <= 7;

如果条件为连续区间（闭区间即包括5和7），可以使用BETWEEN AND 的语法来代替and

SELECT * FROM student WHERE id BETWEEN 5 AND 7;

查询用户id为1、3、5、7的用户信息

SELECT * FROM student WHERE id = 1 OR id = 3 OR id = 5 OR id =7;

如果条件不是连续区间，而是间断的，那么可以使用IN(xx,xx,xx,xx)中间用“,”隔开，来代替or

SELECT * FROM student WHERE id IN(1,3,5,7);

8、删除数据

1）DELETE语法：

delete from 表名 where 条件

**注：**在删除数据的时候，必须添加条件，否则将会把数据表中的全部数据删除，不允许！

2）TRUNCATE语法：

truncate [table] 表名;

**注：**使用truncate删除数据的时候，数据没办法回滚，相当于表的截断

9、对比delete和truncate

相同点：

​ 两者都可以将表中的数据清空，并且保留表结构！

不同点：

​ 1）使用delete删除数据，如果id为自增的，那么在添加新数据的时候，id从原来的基础上进行自增

**即：**自增器不清零！

​ 2）如果使用truncate删除数据，如果id为自增的，那么在添加新数据的时候，id从1开始

**即：**自增器清零！

​ 3）事务角度：

​ 使用delete删除数据，可以进行数据的回滚！也就是有反悔的能力

​ 使用truancate删除数据，数据不能回滚，说明truncate对事务没有影响！

不同存储引擎：

​ 例如myisam存储引擎。使用truancate删除后，如果id为自增，那么新添加的数据也是从1开始，说明不论什么样的存储亲情，truncate都会将自增器清零！

第三章

1、查询的语法：

SELECT   [ALL | DISTINCT] 
{  * |  table.* | [ table.field1 [ as  alias1] [, table.field2 [as  alias2]][, …]] }
FROM  table_name  [ as  table_ alias  ]
    [ left|out|inner  join  table_name2 ]    #联合查询
	[ WHERE  … ]   	#指定结果需满足的条件
	[ GROUP BY …]	#指定结果按照哪几个字段来分组
	[ HAVING …]	#过滤分组的记录必须满足的次要条件
	[ ORDER BY… ]	#指定查询记录按一个或者多个条件排序
	[ LIMIT  {   [ offset,] row_count    |   row_count OFFSET offset   }] ;  #指定查询的记录从哪条至哪条

注：

​ 1、[] 括号代表可选的；

​ 2、{} 括号代表必须的；

​ 3、# MySQL语句中的注释符，也可以用 /该处为注释/

案例1：查询所有参加考试的学生信息，（使用等值连接）

SELECT 
  student.studentNo,
  studentName,
  studentResult 
FROM
  student,
  result 
WHERE student.`studentNo` = result.`StudentNo` ;

注：
1、如果在使用等值连接的时候，并没有通用字段的相等，那么结果返回的是笛卡尔乘积
只有当使用where 通用字段的相等 最后的结果才正确！

​ 2、当联表查询的时候，如果两张表中都有相同的字段，那么一定要在相同字段的前面加上数据表的名称

案例2：查询所有参加考试的学生信息，（使用内连接）

SELECT
student.studentNo,studentName,studentResult
FROM
student INNER JOIN result 
ON student.`studentNo`= result.`StudentNo`;

注：

​ 1、当需要表示通用字段的相等的时候，不能使用where，需要使用“ON ” 关键字！

​ 优化：使用AS关键字 给表和字段加别名，从而简化sql语句

SELECT
s.studentNo AS '学生编号',studentName '学生姓名',studentResult '学生成绩'
FROM
student AS s INNER JOIN result r
ON s.`studentNo`= r.`StudentNo`;

案例3：去重distinct

SELECT DISTINCT * FROM student;

**注：**1、只有全部的字段值都相同的时候，才能去重。否则不能去重

例如：

select distinct sex from student

结果只有 男 和 女

案例4：查询所有邮箱为空（null）的用户信息

select * from student where email is null;

案例5：查询分数在80-90之间的学生信息

SELECT 
s.studentNo '学生编号',studentName '学生姓名',StudentResult '学生成绩'
FROM 
student s,result r
WHERE 
r.`StudentResult` BETWEEN 80 AND 90 
AND s.`studentNo` = r.`StudentNo`

案例6：模糊查询

#查询姓李的信息
SELECT * FROM student WHERE studentName LIKE CONCAT('李','%');

#查询名字中包含“秋”字的学生信息
SELECT * FROM student WHERE studentName LIKE CONCAT('%','秋','%');

#查询姓“张”，名字为一个字的学生信息
SELECT * FROM student WHERE studentName LIKE CONCAT('张','_');

使用自定义的转义符进行模糊查询

#查询名字中包含%的学生信息
SELECT * FROM student WHERE studentname LIKE '%\%%' ESCAPE '\\';

SELECT * FROM student WHERE studentname LIKE '%:%%' ESCAPE ':';

案例7：使用左外连接查询

SELECT
s.studentNo,studentName,StudentResult
FROM result r
LEFT JOIN 
student s
ON s.`studentNo` = r.`StudentNo`

注：

​ 如果student是主表，那么将会输出主表中的所有信息，从表中如果没有对应的信息，以null填充

案例7：使用右外连接查询

SELECT
s.studentNo,studentName,StudentResult
FROM student s
RIGHT JOIN 
result r
ON s.`studentNo` = r.`StudentNo`

**注：**如果成绩表为主表，成绩表中的信息会全部输出，如果student表中没有对应的数据，将不会显示！

案例8：自连接

创建表的sql：

CREATE TABLE IF NOT EXISTS category(
     categoryId INT(10)  AUTO_INCREMENT  PRIMARY KEY,
     categoryName   VARCHAR(32) NOT NULL ,
     pid  INT(10)
);

添加数据：

		INSERT INTO category VALUES
			(2,1,"美术设计"),
			(3,1,"软件开发"),
			(4,3,"数据库基础"),
			(5,2,"Photoshop基础"),
			(6,2,"色彩搭配学"),
			(7,3,"PHP基础"),
			(8,3,"一起学JAVA");

查询：

	SELECT 
	c1.`categoryName` "1级",c2.`categoryName`
	FROM category c1 INNER JOIN category c2
	ON c2.`pid` = c1.`categoryId`;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-GttqbMgT-1584173615669)(C:/Users/LucyBoy/Desktop/新建文件夹/image/自连接效果图.png)]

#第四章

1、order by排序

#查询学生成绩，并按照由高到低进行排序
SELECT 
s.studentNo '学生编号',studentName '学生姓名',studentResult '学生成绩'
FROM student s 
INNER JOIN 
result r
ON s.`studentNo` = r.`StudentNo`
ORDER BY StudentResult DESC

注：

​ 1、默认是升序排序，即ASC，即使不写也是升序

​ 2、DESC是降序排序

2、limit 语句

#查询学生成绩，并按照由高到低进行排序
SELECT 
s.studentNo '学生编号',studentName '学生姓名',studentResult '学生成绩'
FROM student s 
INNER JOIN 
result r
ON s.`studentNo` = r.`StudentNo`
ORDER BY StudentResult DESC
LIMIT 6,3;

limit 语句的语法：

​ limit 偏移量 , 页容量

公式：偏移量 = （当前页码数 - 1）* 页容量

3、子查询

在查询语句中的WHERE条件子句中，又嵌套了另外一个查询语句

3.1、普通的子查询

案例1：

#查询出生日期比“李斯文”同学大的学生信息

#1、查询李斯文的出生日期
SELECT bornDate FROM student WHERE studentName = '李斯文';

#2、查询出生日期比1993-07-23大的学生信息
SELECT * FROM student 
WHERE bornDate < '1993-07-23';

#使用子查询优化以上案例
SELECT * FROM student 
WHERE bornDate < (
SELECT 
  bornDate 
FROM
  student 
WHERE studentName = '李斯文'
);

3.2、使用IN子查询

#2、查询考java的学生信息
#1）先查询java的课程编号
SELECT SubJectId FROM `subject`
WHERE subjectName = 'Java'
#2）查询考java的学生编号信息
SELECT studentNo FROM result WHERE
subjectId = (
	SELECT SubJectId FROM `subject`
	WHERE subjectName = 'Java'
)
#3）根据查询出的学生编号去学生表中查询学生信息
SELECT 
	student.studentNo '学生编号',
	studentName '学生姓名',
	phone '联系方式'
FROM student 
WHERE 
studentNo IN (
	SELECT studentNo FROM result WHERE
	subjectId = (
		SELECT SubJectId FROM `subject`
		WHERE subjectName = 'Java'
	)
)

注：

​ 1、当子查询中查询的结果是一个的时候，可以使用 “=”（算数运算符）

​ 2、当子查询中查询的结果不止一个的时候，只能使用IN子查询，表示一个范围。

3.3、EXISTS子查询

SELECT * FROM student WHERE EXISTS(
SELECT student.studentNo 
FROM
  student 
  INNER JOIN result 
    ON student.`studentNo` = result.`StudentNo` 
)

注：

​ 1、exists子查询中的结果对父查询中的输出列没有影响

​ 2、当子查询中有结果返回时，将返回true，此时进行父查询

​ 3、当子查询中没有结果返回，将返回false，此时不进行父查询

补充案例：

​ 1、查询没有参加考试的学生信息

#查询没有参数考试的学生信息
SELECT s.studentNo '学生信息',studentName '学生姓名'
FROM student s WHERE s.studentNo NOT IN(
	SELECT student.studentNo FROM student 
	INNER JOIN result ON student.`studentNo` = result.`StudentNo`
)

​ 2、使用了四层子查询 查询最近一次参加考试的学生信息

#3、查询最近一次考试java的学生信息
#1）查询考java的课程编号
SELECT SubJectId FROM `subject`
WHERE subjectName = 'Java'

#2）查询考java的学生编号信息
SELECT studentNo FROM result WHERE
subjectId = (
	SELECT SubJectId FROM `subject`
	WHERE subjectName = 'Java'
)

#3）查询考试java这门课的考试日期
SELECT ExamDate FROM result 
WHERE subjectId = (
	SELECT subjectId FROM `subject`
	WHERE subjectName = 'Java'
)

#4）查询最近一次考java的学生的信息
SELECT 
	student.studentNo '学生编号',
	studentName '学生姓名',
	phone '联系方式'
FROM student 
WHERE studentNo IN(
	SELECT studentNo FROM result 
	WHERE SubJectId = (
		SELECT subjectId FROM `subject`
		WHERE subjectName = 'Java'
	)AND ExamDate = (
		SELECT MAX(ExamDate) FROM result 
		WHERE subjectId = (
		SELECT subjectId FROM `subject`
		WHERE subjectName = 'Java'
		)
	)
)

4、聚合函数

4.1、常用函数

#1、查询学生表中的总条数
SELECT COUNT(1) '总人数' FROM student;

#2、查询考试的最高分的学生信息
SELECT MAX(StudentResult) '最高分'
FROM student s 
INNER JOIN 
result r 
WHERE s.`studentNo` = r.`StudentNo`

#3、查询考试的平均分
SELECT AVG(StudentResult) FROM result;

#4、查询所有成绩之和
SELECT SUM(StudentResult) FROM result;

4.2、日期函数

#日期函数
SELECT CURDATE();
SELECT CURTIME();
SELECT NOW();
SELECT WEEK(NOW());
SELECT MINUTE(NOW());
SELECT SECOND(NOW());
SELECT DATEDIFF('2020-8-8',NOW());
SELECT ADDDATE(NOW(),10);

4.3、数学函数

#数学函数
SELECT CEIL(2.1);  3
SELECT FLOOR(2.9999);  2
SELECT RAND();返回0-1之间的随机数
SELECT ROUND(3.15);四舍五入

5、group by 分组查询

#按照不同的课程，分别算出其平均分、最高分和最低分，对于低于60分平均分的不予显示

SELECT 
s.SubjectName AS "课程名",
MAX(StudentResult) AS "最高分" ,
MIN(StudentResult) AS "最低分" ,
AVG(StudentResult) AS 平均分
FROM 
result  AS r 
LEFT JOIN 
`subject` AS s 
ON s.subjectId = r.subjectId
GROUP BY  r.subjectId	
HAVING AVG(StudentResult) >= 60 ;

注：

​ having 是对group by分组的数据再进行筛选

​ group by是对 where 筛选出的输出列中的字段进行分组

​ 还可以根据多个字段进行分组，分组的顺序依次进行

SELECT * FROM student
GROUP BY sex,gradeId

​ 先按照性别进行分组，再按照年级编号进行分组。

第五章

1、事务：

​ 事务就是将一组SQL语句放在同一批次内去执行，如果一个SQL语句出错，则该批次内的所有SQL都将被取消执行

2、事务的四大特性

​ 1）原子性 Atomic

​ 2）一致性 Consist

​ 3）隔离性 Isolated

​ 4）持久性 Durable

3、事务控制

1）关闭自动提交

​ 由于在mysql中，每一条sql语句都进行自动提交，所以可以将自动提交关闭，从而实现对事务的控制

语法：

set autocommit = 0 | 1

注:

​ 1、0 表示关闭自动提交

​ 2、1表示开启自动提交

​ 3、在关闭了自动提交之后，可以手动的进行commit或者rollback

2）开启事务

​ 照样打开事务的自动提交，如果想进行事务的控制，可以开启一个事务。（使用DML语句也可以开启事务）

语法：

begin | start transaction;

注：

​ 开启了事务之后，就可以进行手动的commit或rollback了

4、索引

1、作用：

​ 1）提高查询速度

​ 2）确保数据的唯一性

​ 3）可以加速表和表之间的连接，实现表与表之间的参照完整性

​ 4）使用分组和排序子句进行数据检索时，可以显著减少分组和排序的时间

​ 5）全文检索字段进行搜索优化

2、分类

​ 1）主键索引（PRIMARY KEY）

​ 2）唯一索引（UNIQUE）

​ 3）常规索引（INDEX）

​ 4）全文索引（FULLTEXT）（引擎要用Myisam）（查询时用WHERE MATCH(studentName) AGAINST(‘李斯文’)）

​ 5）复合索引：最左前缀规格

3、创建语法：

方式一: 在声明表添加字段的时候创建

方式二: 外部的方式创建索引

#基于student表创建一个附表
CREATE TABLE student_copy AS(
	SELECT * FROM student
)

#给student_copy表添加索引
ALTER TABLE student_copy ADD PRIMARY KEY(`studentNo`);

ALTER TABLE student_copy ADD UNIQUE KEY(`identityCard`);

ALTER TABLE student_copy ADD INDEX(`GradeId`);

ALTER TABLE student_copy ENGINE = MYISAM
#添加全文索引
ALTER TABLE student_copy ADD FULLTEXT(`address`);

可以使用EXPLAIN来解释 在使用索引查询的效率

EXPLAIN SELECT * FROM student_copy WHERE studentNo = '10000'

EXPLAIN SELECT * FROM student_copy 
WHERE address LIKE CONCAT('%','天','%');

EXPLAIN SELECT * FROM student_copy 
WHERE MATCH(`studentName`) AGAINST('李斯文')

ALTER TABLE student_copy ADD FULLTEXT(`studentName`);

EXPLAIN SELECT * FROM student_copy 
WHERE MATCH(`address`) AGAINST('天津市河西区');

**注意：**全文索引不能直接用 = 或者 like

应该使用MATCH AGAINST语法

4、删除索引

#删除字段 -- 迫使索引删除
ALTER TABLE student_copy DROP address;
#直接删除索引
DROP INDEX address ON student_copy;

5、索引的添加规则

1）index和key关键字都可设置常规索引

2）应加在查找条件的字段

3）不宜添加太多常规索引，影响数据的插入、删除和修改操作

索引不是越多越好

不要对经常变动的数据加索引

小数据量的表建议不要加索引

索引一般应加在查找条件的字段

6、查看表中的所有索引信息

SHOW KEYS FROM student_copy 
	或者
SHOW INDEX FROM student_copy 

5、视图

1、视图的作用：

1）使用视图，可以定制用户数据，聚焦特定的数据（不同的用户查看不同的数据）

2）使用视图，可以简化数据操作。

3）使用视图，基表中的数据就有了一定的安全性 （虚拟表）

4）可以合并分离的数据，创建分区视图 （可以跨库创建视图）

2、创建的语法：

create view view_name AS(查询语句) 

2.1、基于单张表创建视图：

CREATE VIEW view_result_copy2 AS(
	SELECT * FROM student_copy
)

注：

​ 基于一张表创建的视图，可以通过修改视图的信息（insert、update、delete）来达到对原数据表数据的更新

2.2、基于多张表创建视图：

CREATE VIEW view_result_copy_student_copy
AS(
SELECT s.studentNo '学生编号',s.studentName '学生姓名',r.studentResult '学生成绩' FROM student_copy s 
INNER JOIN result_copy r
ON s.studentNo = r.studentNo
)

注：

​ 基于多张表创建的视图，只能通过update修改视图的信息，来对原数据表的数据进行修改，不能实现添加（insert）和删除（delete）操作！

原数据表中的约束对视图也是有效的！

2.3、基于不同的数据库创建视图

#根据多个数据库创建视图
CREATE VIEW V_TEST
AS
SELECT A.studentNo,A.studentName,B.*
FROM `school1`.`student` A,school.person B

SELECT * FROM V_TEST;

注：

​ 前提是：两张表中不能存在重复的字段。

​ 如果两张表中都存在id这个字段，是创建不成功的。

2.4、删除视图

DROP VIEW [IF EXISTS]视图名

6、数据库的备份和恢复

1、备份数据库语法

mysqldump -uroot -p666666 -c school1 student_copy > d:/backup/2018_10_10student.sql
备份多张表用空格隔开

2、数据库的恢复（DOS操作）

​ 方法1：使用source命令

第一步：先登录数据库mysql：mysql -u root -p123456
第二步：选择需要恢复至哪个数据库：use db_name
第三步：source d:/backup/2018_10_10student.sql（已备份文件的路径）

​ 方法2：使用mysql命令

无需登录mysql，直接使用命令：mysql -uroot -p666666 serverdb < d:/backup/2018_10_10student.sql

注：

​ 1、在进行数据库的备份的时候，直接在dos窗口执行mysqldump命令。不需要登录数据库。

​ 2、在恢复数据库信息的时候，如果是使用source命令，首先需要先登录mysql数据库，选中一个需要恢复数据的数据库，然后再执行source命令

​ 3、如果使用mysql命令。就直接在控制台执行即可。

7、数据的导出和导入

7.1、数据的导出

#数据的导出
SELECT * FROM student
WHERE gradeId = 1 INTO OUTFILE
'C:/ProgramData/MySQL/MySQL Server 5.5/Uploads/greadeOne.txt';

注：

由于在my.ini 文件中有安全文件的权限问题，导致我在导出的时候只能导出在指定的路径下！

7.2、数据的导入

#导入文件
LOAD DATA INFILE 'C:/ProgramData/MySQL/MySQL Server 5.5/Uploads/greadeOne.txt' INTO TABLE student;

注：

​ 如果要将外部的数据导入到数据库中，那么一定要保证在数据库中有响应的表结构来接收导入的数据！

MySQL 的存储过程

​ MySQL 存储过程是从MySQL5.0 开始增加的新功能。存储过程的优点有一箩筐。不过最主要的还是执行效率和SQL 代码封装。特别是SQL 代码封装功能，如果没有存储过程，在外部程序访问数据库时，要组织很多SQL 语句。特别是业务逻辑复杂的时候，一大堆的SQL 和条件夹杂在代码中，让人不寒而栗。现在有了MySQL 存储过程，业务逻辑可以封装存储过程中，这样不仅容易维护，而且执行效率也高。

1、创建MySQL 存储过程

​ 下面代码创建了一个叫pr_add 的MySQL 存储过程，这个MySQL 存储过程有两个int 类型的输入参数“a”、
“b”，返回这两个参数的和。
1）drop procedure if exists pr_add; （备注：如果存在pr_add 的存储过程，则先删掉）
2）计算两个数之和（备注：实现计算两个整数之和的功能）

create procedure pr_add ( a int, b int ) begin declare c int;
if a is null then set a = 0;
end if;
if b is null then set b = 0;
end if;
set c = a + b;
select c as sum;

2、调用MySQL 存储过程

call pr_add(10, 20);

3、存储过程与SQL 的对比

3.1、优势:

1、提高性能
SQL 语句在创建过程时进行分析和编译。存储过程是预编译的，在首次运行一个存储过程时，查询优化器对其进行分析、优化，并给出最终被存在系统表中的存储计划，这样，在执行过程时便可节省此开销。
2、降低网络开销
存储过程调用时只需用提供存储过程名和必要的参数信息，从而可降低网络的流量。
3、便于进行代码移植
数据库专业人员可以随时对存储过程进行修改，但对应用程序源代码却毫无影响，从而极大的提高了程序的可移植性。
4、更强的安全性
1）系统管理员可以对执行的某一个存储过程进行权限限制，避免非授权用户对数据的访问
2）在通过网络调用过程时，只有对执行过程的调用是可见的。因此，恶意用户无法看到表和数据库对象名称、嵌入自己的Transact-SQL 语句或搜索关键数据。
3）使用过程参数有助于避免SQL 注入攻击。因为参数输入被视作文字值而非可执行代码，所以，攻击者将命令插入过程内的Transact-SQL 语句并损害安全性将更为困难。
4）可以对过程进行加密，这有助于对源代码进行模糊处理。

3.2、劣势：

​ 1)、存储过程需要专门的数据库开发人员进行维护，但实际情况是，往往由程序开发员人员兼职
​ 2)、设计逻辑变更，修改存储过程没有SQL 灵活

MySQL 触发器

​ MySQL 包含对触发器的支持。触发器是一种与表操作有关的数据库对象，当触发器所在表上出现指定事件时，将调用该对象，即表的操作事件触发表上的触发器的执行。
在MySQL 中，创建触发器语法如下：

CREATE TRIGGER trigger_name
trigger_time
trigger_event ON tbl_name
FOR EACH ROW
trigger_stmt

其中：
trigger_name：标识触发器名称，用户自行指定；
trigger_time：标识触发时机，取值为BEFORE 或AFTER；
trigger_event：标识触发事件，取值为INSERT、UPDATE 或DELETE；
tbl_name：标识建立触发器的表名，即在哪张表上建立触发器；
trigger_stmt：触发器程序体，可以是一句SQL 语句，或者用BEGIN 和END 包含的多条语句。

​ 由此可见，可以建立6 种触发器，即：BEFORE INSERT、BEFORE UPDATE、BEFORE DELETE、AFTER INSERT、AFTER UPDATE、AFTER DELETE。

​ 另外有一个限制是不能同时在一个表上建立2 个相同类型的触发器，因此在一个表上最多建立6 个触发器。

1、示例：

假设系统中有两个表：
1)班级表class(班级号classID, 班内学生数stuCount)
2)学生表student(学号stuID, 所属班级号classID)

要创建触发器来使班级表中的班内学生数随着学生的添加自动更新，代码如下：

create trigger tri_stuInsert after insert
on student for each row
begin
declare c int;
set c = (select stuCount from class where classID=new.classID);
update class set stuCount = c + 1 where classID = new.classID;

2、查看触发器：

​ 和查看数据库（show databases;）查看表格（show tables;）一样，查看触发器的语法如下：

SHOW TRIGGERS [FROM schema_name];

​ 其中，schema_name 即Schema 的名称，在MySQL 中Schema 和Database 是一样的，也就是说，可以
指定数据库名，这样就不必先“USE database_name;”了。

3、删除触发器：

​ 和删除数据库、删除表格一样，删除触发器的语法如下：

DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name

4、触发器的作用

1）触发器可通过数据库中的相关表实现级联更改；通过级联引用完整性约束可以更有效地执行这些更改。
2）触发器可以强制比用CHECK 约束定义的约束更为复杂的约束。与CHECK 约束不同，触发器可以引用其它
表中的列。例如，触发器可以使用另一个表中的SELECT 比较插入或更新的数据，以及执行其它操作，如修改数据或显示用户定义错误信息。
3）触发器还可以强制执行业务规则
4）触发器也可以评估数据修改前后的表状态，并根据其差异采取对策。

提高千万级的数据库查询效率

1)数据库设计方面

​ a. 对查询进行优化，应尽量避免全表扫描，首先应考虑在where 及order by 涉及的列上建立索引。
​ b. 应尽量避免在where 子句中对字段进行null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，
如： select id from t where num is null 可以在num 上设置默认值0，确保表中num 列没有null 值，然后这样查询： select id from t where num=0
​ c. 并不是所有索引对查询都有效，SQL 是根据表中数据来进行查询优化的，当索引列有大量数据重复时,查询可能不会去利用索引，如一表中有字段sex，male、female 几乎各一半，那么即使在sex 上建了索引也对查询效率起不了作用。
​ d. 索引并不是越多越好，索引固然可以提高相应的select 的效率，但同时也降低了insert 及update 的效率，因为insert 或update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6 个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。
​ e. 应尽可能的避免更新索引数据列，因为索引数据列的顺序就是表记录的物理存储顺序，一旦该列值改变将导致整个表记录的顺序的调整，会耗费相当大的资源。若应用系统需要频繁更新索引数据列，那么需要考虑是否应将该索引建为索引。
​ f. 尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。
​ g. 尽可能的使用varchar/nvarchar 代替char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，
其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。
​ h. 尽量使用表变量来代替临时表。如果表变量包含大量数据，请注意索引非常有限（只有主键索引）。
​ i. 避免频繁创建和删除临时表，以减少系统表资源的消耗。
​ j. 临时表并不是不可使用，适当地使用它们可以使某些例程更有效，例如，当需要重复引用大型表或常用表
中的某个数据集时。但是，对于一次性事件，最好使用导出表。
​ k. 在新建临时表时，如果一次性插入数据量很大，那么可以使用select into 代替create table，避免造成大量log，以提高速度；如果数据量不大，为了缓和系统表的资源，应先create table，然后insert。
​ l. 如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先truncate table ，然后drop
table ，这样可以避免系统表的较长时间锁定。

2)SQL 语句方面

​ a. 应尽量避免在where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。
​ b. 应尽量避免在where 子句中使用or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：
select id from t where num=10 or num=20 可以这样查询： select id from t where num=10 union all
select id from t where num=20
​ c. in 和not in 也要慎用，否则会导致全表扫描，如： select id from t where num in(1,2,3) 对于连续的
数值，能用between 就不要用in 了： select id from t where num between 1 and 3
​ d. 下面的查询也将导致全表扫描： select id from t where name like ‘%abc%’
​ e. 如果在where 子句中使用参数，也会导致全表扫描。因为SQL 只有在运行时才会解析局部变量，但优化
程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。然而，如果在编译时建立访问计划，变量的
值还是未知的，因而无法作为索引选择的输入项。如下面语句将进行全表扫描： select id from t where
num=@num 可以改为强制查询使用索引： select id from t with(index(索引名)) where num=@num
​ f. 应尽量避免在where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：
select id from t where num/2=100 应改为: select id from t where num=1002
​ g. 应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如：select
id from t where substring(name,1,3)= ‘ abc ’ – name 以abc 开头的id select id from t where
datediff(day,createdate,’2005-11-30′)=0–‘2005-11-30’生成的id 应改为: select id from t where name
like ‘abc%’ select id from t where createdate>=’2005-11-30′ and createdate<’2005-12-1′
​ h. 不要在where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用
索引。
​ i. 不要写一些没有意义的查询，如需要生成一个空表结构： select col1,col2 into #t from t where 1=0 这
类代码不会返回任何结果集，但是会消耗系统资源的，应改成这样： create table #t(…)
​ j. 很多时候用exists 代替in 是一个好的选择： select num from a where num in(select num from b)
用下面的语句替换： select num from a where exists(select 1 from b where num=a.num)
​ k. 任何地方都不要使用select * from t ，用具体的字段列表代替“”，不要返回用不到的任何字段。
​ l. 尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1 万行，那么就应该考虑改写。
​ m. 尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。
​ n. 尽量避免大事务操作，提高系统并发能力。

3)java 方面：重点内容

​ a.尽可能的少造对象。
​ b.合理摆正系统设计的位置。大量数据操作，和少量数据操作一定是分开的。大量的数据操作，肯定不是ORM框架搞定的；
​ c.使用jDBC 链接数据库操作数据；
​ d.控制好内存，让数据流起来，而不是全部读到内存再处理，而是边读取边处理；
​ e.合理利用内存，有的数据要缓存。