MYSQL 数据库详解

HY丶浪潮

已于 2022-07-02 11:16:48 修改

阅读量3.9k

点赞数 1

文章标签：数据库 mysql database

于 2022-01-13 03:56:57 首次发布

本文链接：https://blog.csdn.net/qq_54726480/article/details/122466032

版权

认识数据库【兼容性】

数据库管理系统的主要功能如下：
（1）数据模式定义：即为数据库构建其数据框架。
（2）数据存取的物理构建：为数据模式的物理存取与构建提供有效的存取方法与手段。
（3）数据操纵：为用户使用数据库提供方便，如查询、拆入、修改、删除以及简单的运算及统计。
（4）数据的完整性、安全性定义与检查。
（5）数据库并发控制与故障恢复。
（6）数据的服务：如拷贝、转存、重组、性能监测和分析等。

关旭模式表示了实体数据关系结构。因为数据结构本身就是非常抽象的东西，我们可以通过E—R关系图来进行宏观描叙。数据与数据之间关联。【就是为了就是为了更好的表现数据】

1.关系模式的规范化

构造数据库必须遵循一定的规则。关系数据库中，这种规则就是范式。一公分为五个范式分别为一范式二范式三范式四范式五范式

注意：数据库中讲到的规范化就是要我们尽量按提供给我们使用的范化来设计我们的数据库。

1.1第一范式

是指数据库的每一列都是不可分割的基本的基本数据项，同一列中不能有多个值或者不

能有重复的属性。简而言之，第一范式就是无重复的列。

例如：Teachers表

教师编号	教师姓名	性别	职称	联系方式
001	王老师	男	中级	xxxx
002	刘老师	男	中级	xxxx

上面teachers老师表中的联系方式字段，不满足1NF。因为联系方式是一个不可再分割的字段。
例如：联系方式有；微信、qq、手机号、固话等等这一些都是要以存在联系方式当中。

1.2第二范式

第二范式（2NF）是在第一范式（1NF）的基础上建立起来的，即满足第二范式（2NF）必须满足第一范式（1NF）。

注意：只有满足第1NF的条件下才能满足第2NF。

（1）表必须有一个主键，要求数据库表中的每个记录必须可以被唯一地区分。
（2）实体的属性完全依赖于主键，而不能只依赖于主键的一部分（有时主键是由多个列组成的复合主键）。如果存在，那么这个属性和主键的这一部分应该分离出来一个新的实体，新实体与原实体之间是一对多关系。

2.常见不满足2n的问题

（1）插入异常
（2）数据冗长
（3）更新异常
（4）删除异常

问题总结：出现以上问题最大根本就是没办法做到数据区分以及数据的完整性。

3.第三范式3NF

满足第三范式（3NF）必须满足第二范式（2NF），并且，要消除传递函数依赖。

4.二范式和三范式的区别

（1）2NF：非主键列是否完全依赖于主键，还是依赖主键的一部分。
（2）2NF：非主键列是直接依赖于主键，还是依赖于非主键列。

2.数据库应用系统体系结构

Mysql的两种结构模式分为：

a.客户机/服务端（Clicent/server）=>c/s

b.三层客户机/服务端（Browse/serve）=>b/s

3.通过DOS的方式来进行连接mysql

$mysql -h服务器主机地址 -u用户名 -p密码

//如果是本机那么我们-h可以省略不写，如果一定要写那就是 . 或者127.0.0.1</font>

进入到了mysql命令时，如果要退出则可以输入exit或者quit

-h的选择可以如下几种：
	-h.
	-h127.0.0.1
	-h local host

4.MySQL数据库

4.1系统数据库

Informatiin_schema
Performance_schema
mysql
test

4.2用户数据库

在mysql当中我们自己建立的数据就是用户数据库，也是我们要操作和管理的数据库。

4.3创建数据库

语法：
```
#在sql中井号是注释符
CREATE DATABASE 数据库名；
```
注意：在mysql中是不区分大小的
```
#创建一个名为Mycompany数据库
CREATE DATABASE Mycompany；
```
执行结果：

(1 row(s) affected) 操作影响的行数

Execution Time : 00:00:00:000 sql命令执行所需时间

Transfer Time : 00:00:01:031 返回结果时间

Total Time : 00:00:01:031 总共所需时间

4.4查看数据列表
```
#查看数据库所有的数据库名
SHOW DATBASES;
```

执行结果：

同时才会执行相关信息：

(5 row(s) returned)

Execution Time : 00:00:00:000

Transfer Time : 00:00:00:000

Total Time : 00:00:00:000

4.5选择使用数据库

#选择使用哪一个数据为当前使用数据库
USE mycompany;

注意： 在我们使用数据库之前如果我们不进行指定库，数据库系统不知道我们要使用哪一个库当前操作数据库，因此我们需要通过USE数据库；来进行指定。

4.6删除制定数据库

#删除制定数据库
DROP DATABASE mycompany;

执行结果：

(0 row(s) affected)

Execution Time : 00:00:00:000

Transfer Time : 00:00:00:016

Total Time : 00:00:00:016

4.7数据结构化查询语言

名称	解释	命令举例
DML(数据操作语言)	用来操作数据库中所包含的数据	INSERT、UPDATE、DELETE
DDL(数据定义语言)	用于创建和删除数据库对象等操作	CREATE、DROP
DQL(数据查询语言)	用来对数据库中的数据进行查询	SELECT
DCL(数据控制语言)	用来控制数据库组件的存取许可、存取	GRANT、COMMIT、

我们对于数据库数据的操作以及对数据库的管理都会有对应的命令。所有命令都会在上面表格进行集合。

5.mysql数据类型

为什么在数据库当中会有数据的类型说法呢？计算当中切都是数据，但所有的数据都会有个类型，

那么这个类型我们称之为数据类型。下图为常用的mysql数据类型：

5.1 数值类型

类型	说明	最小值(带符号的/无符号的）	最大值(带符号的/无符号的）	存储需求	举例
TINYINT	非常少的数据	-128	127	1字节	字段：年龄、性别
		0	255
SMALLINT	较小的数据	-32768	32767	2字节	字段：分数、体重
		0	65535
MEDIUMINT	中等大小的数据	-8388608	8388607	3字节	字段：商品ID
		0	16777215
INT	标准整数	-2147483648 0	2147483647	4字节	字段：时间、统计字段
		0	4294967295
BIGINT	最大整数	-92233720368547758080	9223372036854775807	8字节
		0	18446744073709551615
FLOAT	单精度浮点数			4字节
DOUBLE	双精度浮点数	-1.7976931348623157E+308	-2.2250738585072014E	8字节
DECIMAL	字符串形式的浮点数	Decimal（M，D）		M+2 个字节

在我们定义字段数据类型时，如果给某个字段的数据类型设置为int、tinyint等类型，不加上unsigend属性时，那么这个字段可以出现正负数。否则相反，但我们也可以通过 ZEROFILL 属性设置字段，这样系统就默认加上 unsigend 属性。

# 我们要创建一个user表,表中有一个字段为age 
create table user ( 
    age int(3) 
);
上面代码，age字段是否可以出现负数吗？可以 

create table user(
    age int(3) unsigend 
);
# 此时加上unsigend时只能是无符号数 
create table user( 
    age int(3) zerofill 
);
#此时加上zerofill属性时，系统也会自动加上unsigend属性。同时，在字段设置的位置没达到时会以0方 式进行填充。

示例：

#创建一张tmp_user表，只有1个字段 
CREATE TABLE tmp_user(
    id INT(3)
);
#往数据表(tmp_user)插入数据 
INSERT INTO tmp_user(id)VALUES (-1); 
INSERT INTO tmp_user(id) VALUES (12); 
#查看tmp_user表的所有数据 
SELECT * FROM tmp_user;

示例结果：

带zerofill属性的示例：

#最创建一个新
CREATE TABLE tmp_user1( 
    id INT(3) ZEROFILL 
);
#入新当中插入一条数据 
INSERT INTO tmp_user1( id) VALUES(12); 

SELECT * FROM tmp_user1;

5.2 字符串类型

类型	说明	长度
CHAR[(M)]	定长字符串	M字节
VARCHAR[(M)]	可变字符串	可变长度
TINYTEXT	微型文本串	0-28-1字节
TEXT	文本档	0-216-1字节

char是字符串同时(M)]是可选项，也就是可填可不填。作用就是给这个字段的长度设置一个固定的值。一但这个值设定之后就算保存的值没有等于设置长度那么数据库也会把这个值当成设定长度。例：char(4) => 保存值为 12 （12的长度只2个）那么实际上在数据库里也是4个长度。注意varchar是可变长度，也就是根据实际值来计算长度。

5.3 日期类型

类型	格式化	取值范围
DATE	YYYY-MM-DD，日期格式	1000-01-01~ 9999-12-31
DATETIME	YY-MM-DD hh:mm:ss:	1000-01-01 00：00：00 ~9999-12-31 23：59：59
TIME	hh:mm:ss:	-835:59:59 ~ 838:59:59
TIMESTAMP	YYYYMMDDHHMMSS	1970年某时刻-2038年某时刻，精度为1秒
YEAR	YYYY格式的年份	1901-21555

注意：关于日期一般都会设置为 timestamp 、 int

6.创建表

在使用数据库的过程当中，我们会使用sql语法对数据库进行表的创建。CREATE TABLE用于创建带给定名称的表。您必须拥有表CREATE权限。

# 语法 
CREATE TABLE [IF NOT EXISTS] 表名 ( 
    字段1 数据类型 [字段属性|约束][索引][注释], 
    …… 
    字段n 数据类型 [字段属性|约束][索引][注释]
)[表类型][表字符集][注释]

实例

#创建老师表teachers 
#create TABLE teachers(); #如果直接执行这sql，会创建一个空表 
CREATE TABLE teachers( 
    #字段名 数据类型 [属性] [注释]... 
    t_no CHAR(8) NOT NULL COMMENT '教师编号', 
    t_name VARCHAR(12) NOT NULL COMMENT '教师姓名', 
    d_no CHAR(8) NOT NULL COMMENT '院系编号', 
    #为我们teachers这个表设置一个主键 
    PRIMARY KEY (t_no) 
)ENGINE=INNODB DEFAULT CHARSET=utf8;

分析：

NOT NULL 是一个字段属性，表达意思为非空，也就是不能为空的意思

COMMENT 是为这个字段进行备注

PRIMARY KEY 为此表设置一个主键

ENGINE=INNODB 为此表指定数据存储引擎

DEFAULT CHARSET 为此表设置字符集

#查看表的设计结构 
DESC teachers;

创建departments表->院系表

# 创建院系表 
CREATE TABLE departments( 
    d_no CHAR(8) NOT NULL COMMENT '院系编号', 
    PRIMARY KEY (d_no), 
    d_name VARCHAR(12) NOT NULL COMMENT '院系名称' 
)ENGINE=INNODB DEFAULT CHARSET=utf8; 
# 查看所有表 
SHOW TABLES;

#创建students 
CREATE TABLE students( 
    s_no char(8) not NULL PRIMARY KEY COMMENT '学生编号', 
    s_name VARCHAR(16) not NULL COMMENT '学生姓名', 
    sex ENUM('男','女') DEFAULT '男' COMMENT '性别', 
    birthday date NOt NULL COMMENT '出生日期',
    d_no char(8) not NULL COMMENT '所在部系', 
    address VARCHAR(100) not NULL COMMENT '家庭地址', 
    phone CHAR(11) NOt NULL COMMENT '手机号码', 
    photo BLOB COMMENT '照片' 
)engine=INNODB DEFAULT CHARSET=utf8 COMMENT '学生表';

创建course

CREATE TABLE course( 
    c_no CHAR(8) NOT NULL PRIMARY KEY, 
    c_name VARCHAR(12) NOT NULL, 
    d_no CHAR(8) NOT NULL, 
    hours INT NOT NULL, 
    credit INT NOT NULL, 
    c_type ENUM('必修课','选修课') DEFAULT '必修课' 
)ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT '课程表'; 

CREATE TABLE score( 
    s_no char(8) not NULL PRIMARY KEY, 
    c_no CHAR(8) not NULL, 
    report FLOAT(3,1) DEFAULT 0 
    # 精度为4,保留1位小数 
)engine=INNODB DEFAULT CHARSET=utf8 COMMENT '分数表'; 

CREATE TABLE teach( 
    t_no CHAR(8) NOT NULL, 
    c_no CHAR(8) NOT NULL, 
    KEY t_no (t_no), 
    KEY c_no (c_no) 
)ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT '认课表';

key

key 是数据库的物理结构，它包含两层意义和作用。

一是约束（偏重于约束和规范数据库的结构完整性），

二是索引（辅助查询用的）。

6.1字段的约束及属性

名称	关键字	说明
非空约束	NOT NULL	字段不允许为空
默认约束	DEFAILT	赋予某字段默认值
唯一约束	UNIQUE KEY（UK）	设置字段的值是唯一的，允许为空，但只能有一个空
主键约束	PRIMARY KEY(PK)	设置字段为表的主键，可唯一标识该记录
外键约束	FOREIGN KEY(FK)	用于两表之间建立关系，需要指定引用主表的哪个字段
自动增长	AUTO_INCREMENT	设置该列为自增长字段，默认为条自增1，通常用主键

7.数据表的增删改

ALTER TABLE 只是对数据表的修改，而非数据。

7.1数据表添加字段

# 语法
ALTER TABLE table_name ADD column_name datatype

示例：

#为学生添加speciality字段 
ALTER TABLE students ADD speciality VARCHAR(10) NOT NULL AFTER photo;

7.2删除指定字段

# 语法 
ALTER TABLE table_name 
DROP COLUMN column_name

# 删除students表中speciality字段
ALTER TABLE students DROP speciality;

7.3 修改表字段

ALTER TABLE 表名 CHANGE [column] 旧字段名 新字段名 新数据类型;

# 修改表中字段名称 
ALTER TABLE students CHANGE s_name s_name_new VARCHAR(12) NOT NULL COMMENT '学生名 称';

7.4修改表名

ALTER TABLE 旧表名 RENAME TO 新表名 ;

ALTER TABLE students RENAME TO studentsnew;

7.5修改表的注释

ALTER TABLE 表名 COMMENT '新注释'

# 修改表的注释 
ALTER TABLE students COMMENT 'new学生表';

7.6 修改表字段数据类型

alter table 表名 modify 字段名 字段类型;

# 把students表中的phone类型改成int
ALTER TABLE students MODIFY phone INT(11);

7.7把表中的某个字段设置为第一个字段

# 把表中的某个字段设置为第一个字段 
ALTER TABLE students MODIFY d_no INT FIRST;

7.8修改数据表的存储引擎

# 把students表的存储引擎修改为MyISAM
ALTER TABLE students ENGINE=INNODB;

7.9查看数据表更多的信息

# 查看表更多的信息
SHOW TABLE STATUS LIKE 'students';

7.10综合练习

新建表名为test01的数据表，表中字段有id、name分别为属性为id主键且主键不能为空；主键自动增长正整数；长度为10；name字段为字符串字段，长度为8且不能为空字符utf8。要求如下：

1.修改表为test01为test02

2.增长字段password，且只能保存字符串不得大于8位长度以及不能为空

3.修改数据库中name为username，且长度为12位

4.删除字段为password

8.MYSQL 储存引擎

8.1什么是储存引擎

数据库引擎是数据库底层软件组件，数据库管理系统使用数据库引擎进行创建、查询、更和删除数据操作。不同的储存引擎提供不同的储存机制、索引技巧、锁定水平等功能，使用不同的存储引擎还可以获得特定功能。

提示：lnnoDB事物的首选引擎，支持事务锁定和外键。MySQL5.5.5.之后lnnoDB作为默认存储引擎。

MylSAM是基于ISAM的存储引擎，并对其进行扩展，是在web、数据存储和其他应用环境下最常使用的存储引擎之一。MySQL拥有较高的拆入、查询速度、但不支持事务。

8.2查看数据库支持存储引擎

#查看安装的数据库支持哪一些引擎
SHOW ENGIONES；

在这里插入图片描述

常用举例：

功能	MyISAM	MEMORY	lnnoDB
支持外键	No	No	Yes
存储限制	256TB	RAM	56TB
支持事务	No	No	Yes
支持全文索引	Yes	No	No
支持B树索引	Yes	Yes	Yes
支持哈希索引	No	Yes	Yes
支持集群索引	No	No	Yes
支持数据索引	No	Yes	Yes
支持数据压缩	Yes	No	No
空间使用率	底	N/A	高

8.3MyISAM存储引擎

使用这个引擎，每个MyISAM在磁盘上存储成三个文件

1、frm文件：存储表的定义数据源

2、MYD文件：存放表具体记录的数据

3、MYI文件：存储索引

注意：使用MylSAM引擎之后，每一张表都会生上面三个文件，保存在电脑硬盘上。

8.4lnnoDB

在mysql数据库5.5.5之后默认选择存储引擎就是lnnoDB；

（1）可以通过自动增长列，方法就是ayto_increment。

（2）支持事务，默认浏览器的事务隔离级别可重复度，通过MVCC（并发版本控制）来实现的。

（3）使用的锁粒度为行级锁，可以支持更高的并发；

（4）支持外键约束；外键约束其实降低了表的查询速度，但是增加了表之间的耦合度。

（5）配合一些热力工具可以支持在线热备份；

（6）在lnnoDB中存在着缓冲管理，通过缓冲池，将索引和数据全部缓存起来，加快查询的速度；

（7）对lnnoDB类型的表，其数据的物理组织形式是聚簇表。所有的数据按照主键来组织。数据和索引放在一块，都位于B+数的叶子节点上；

8.5lnnoDB和索引的两种形式

（1）使用共享空间存储：所有的表和索引放在同一个表空间中。

（2）使用多个表空间存储：表结构放在frm文件，数据和索引放在IBD文件中。分区表的话，每个分区对应单独的IBD文件，分区表的定义可以查看我的其他文章。使用分区表的好处在于提升查询效率。

总结：对于lnnoDB来说，最大的优点在于支持事务。但是这是以损失效率来换取的。

8.6MEMORY

将数据存在内存，为了提高数据访问速度，每一个实际上和一个磁盘文件关联。文件frm。

（1）支持的数据类型有限制，比如：不支持TEXT和BLOB类型，对于字符串的数据，只支持固定长度的行VACHAR会被自动存储为CHAR类型；

（2）支持的锁粒度为表级锁，所以，在访问量比较大时，表级锁会成为MEMORY存储引擎的瓶颈；

（3）由于数据是存放在内存中，一旦服务器出现故障，数据都会丢失；

（4）查询的时候，如果有用到临时表，而且临时表中有BLOB，TEXT类型的字段，那么这个临时表就会转化为MylSAM类型的表，性能会急剧下降；

（5）默认使用hash索引。

（6）如果一个内部表很大，会转化为磁盘表。

8.7关于引擎使用场景

使用MylSAM：不需事务，空间小，以查询访问为主
使用lnnoDB：多删除、0更新操作，安全性高，事务处理及并发控制

8.8查看当前数据库默认引擎

SHOW VARIABLES LIKE 'storage_enginex';#如果有设置会显示出来

关于默认浏览器引擎我们是可以通过my.ini配置文件来进行修改

# The default storage engine that will be used when create new tables when

default-storage- engine= INNODB

8.9数据表的储存位置

能通过数据库配置文件my.ini的datadir或者innodb_data_home_dir’

# Path to the database root

datadir=C:/ProgramData/MySQL/MySQL Server 5.7\Data

根据指定的数据存放目录如下图：

在这里插入图片描述

9.DML语句

9.1插入单条数据记录

#语法
INSERT INTO 表名 [(字段名列表)] VALUES (值列表);

在我们已经新建好数据的情况下，可以使用DML语句中的INSERT INTO来进行往表里插入单独数据。

#往departtments表中插入一个计算机系
INSERT INTO departments VALUES('A0001','计算机系');

#查看是否插入数据成功
SELECT * FROM departments;

在这里插入图片描述

注意：在语法中[(字段名列表)]是可以省略的，如果采用此写法那么表中有多少个字段我们就可以多少值。否则下如

#往departments表中插入一个数学系
INSERT INTO departments VALUES('数学系');

错误码: 1136 Column count doesn't match value count at row 1 

Execution Time : 00:00:00:000 
Transfer Time  : 00:00:00:000
Total Time     : 00:00:00:000

使用表中字段列表显示方式来进行insert into

#往departments表中插入一个数学系
INSERT INTO departments VALUES('数学系');

注意：如果采用此方式，那么字段列表有几个字段名，我们列表当中就要传递几个值。如果字段个数和值个数不相等：

错误码: 1136
Column count doesn't match value count at row 1 
Execution Time : 00:00:00:000 
Transfer Time  : 00:00:00:000
Total Time     : 00:00:00:000

总结：

1.字段名是可选的，如省略则依次插入所有字段

2.多个列表和多个值之间使用逗号分隔

3.值列表和字段名列表一一对应

4.如插入的是表中部分数据，字段名列表必填

9.2插入多条数据

#语法
INSERT INTO departments(d_no,d_name) VALUES('A0003','语文系')，（'A0004,'英语系'）；

单条数据插入时，字段名列表和值列表要一一对应。那么在多条数据插入时，实际上就是把字段名和值列表一一对应的前提条件；把值当成单位再以逗号进行隔开。

#多条数据一起进行INSERT INTO操作
INSERT INTO departments(d_no,d_name) VALUES('A0003','语文系'),('A0004','英语系');

+---------------------+ 
|    d_no  |  d_name  |
+---------------------+ 
|  A0001    | 计算机系 |
|  A0002	|  数学系	|
|  A0003	| 语文系 	|
|  A0004	| 英语系 	|
4 rows in set (0.00 sec)

9.3将查询结果插入新表

编写SQL语句实现从部门表提取编号、名称两列数据存储到名单表中，也称之为把部门复制到新表当中。

mysql> show tables;
+---------------------+ 
| Tables_in_mycompany | 
+---------------------+ 
| course 			  |
| departments 		  |
| score 			  |
| students 			  |
| teach 			  |
| teachers			  |
| tmp_user			  |
| tmp_user1 		  |
| tmptable 			  |
+---------------------+
9 rows in set (0.00 sec)

#将depatmemts表中的d_no，d_no字段和值复制到新表deplist中
CREATE TABLE depList(SELECT d_no,d_name FROM departs);

#执行结果：
Query OK, 4 rows affected (0.04 sec) 
Records: 4 Duplicates: 0 Warnings: 0

执行上面sql语句之后

mysql> show tables;
+---------------------+ 
| Tables_in_mycompany | 
+---------------------+ 
| course 			  |
| departments 		  |
| deplist 			  |
| score 			  |
| students 			  |
| teach 			  |
| teachers			  |
| tmp_user			  |
| tmp_user1 		  |
| tmptable 			  |
+---------------------+ 
10 rows in set (0.00 sec)

同时我们也可以进行sql语句来进行查看新表deplist的结构；

mysql> desc deplist; 
+--------+-------------+------+-----+---------+-------+
| Field  |     Type    | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
|  d_no  |    char(8)  |  NO  |     |  NULL   |       |
| d_name | varchar(12) |  NO  |     |  NULL   |       |
+--------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

查看deplist

mysql> select * from deplist;
+---------------------+ 
|    d_no  |  d_name  |
+---------------------+ 
|  A0001    | 计算机系 |
|  A0002	|  数学系	|
|  A0003	| 语文系 	|
|  A0004	| 英语系 	|
4 rows in set (0.00 sec)

9.4数据的更新

此操作是更新数据表的数据源，如果把张三改李四

#语法
UPDATE 表名
SET 字段1=值1，字段2=值2,字段n=值n
[WHERE 条件]；

#在部门表里，把部门名称计算机改成计算机科学
UPDATE departments SET d_name='计算机科学' WHERE d_name='计算机科学';	

#执行结果
+-------+------------+
| d_no  |    d_name  | 
+-------+------------+
| A0001 |   计算机科学 | 
| A0002 |    数学系   |
| A0003 |    语文系   | 
| A0004 |    英语系   |
+-------+------------+ 
4 rows in set (0.00 sec)

注意：update语句操作一定要跟where条件，如果没有在执行update操作时没有赋予条件时，此操作会把整个表的数据进行修改。

mysql> update deplist set d_name='计算机系';
Query OK, 3 rows affected (0.01 sec) 
Rows matched: 4 Changed: 3 Warnings: 0

+-------+----------+
| d_no  |  d_name  |
+-------+----------+ 
| A0001 |  计算机系 | 
| A0002 |  计算机系 | 
| A0003 |  计算机系 |
| A0004 |  计算机系 | 
+-------+----------+ 
4 rows in set (0.00 sec)

9.5删除数据记录

#语法
DELETE FROM 表名 [WHERE条件];

# 在执行DELETE语句没有跟上条件 
mysql> delete from deplist; 
Query OK, 4 rows affected (0.01 sec)

# 查看deplist表中数据 
mysql> select * from deplist; 
Empty set (0.00 sec)

结论：在使用DELETE语句删除数据时如果没有跟上条件那么久会把整个表中的数据进行清空，而不会被删除。

# 在部门表中，删除部门名称为计算机科学的数据
DELETE FROM deplist WHERE d_name='计算机科学';

#执行结果
+-------+--------+ 
| d_no  | d_name | 		
+-------+--------+ 
| A0002 |  数学系 | 
| A0003 |  语文系 | 
| A0004 |  英语系 |
+-------+--------+ 
3 rows in set (0.00 sec)

在使用DELETE语句可以删除数据之外还有一种方式也可以实现

#语法
TRUNCATE TABLE 表名；
# 清空deplist表，表不会删除，会真正清空此表数据 
mysql> truncate table deplist; 
Query OK, 0 rows affected (0.04 sec)

CREATE TABLE depnew( 
    id INT(4) NOT NULL PRIMARY KEY AUTO_INCREMENT，
    user_name VARCHAR(10) NOT NULL 
);

INSERT INTO depnew(user_name) VALUES('aaaa'),('bbb'),('ccc');

# 查看depnew表中数据 
mysql> select * from depnew; 
+----+-----------+ 
| id | user_name | 
+----+-----------+ 
| 1  |    aaaa   |
| 2  |    bbb    |
| 3  |    ccc    | 
+----+-----------+ 
3 rows in set (0.00 sec)

#使用delete来进行清空此表数据 
DELETE FROM depnew;
INSERT INTO depnew(user_name) VALUES('aaaa'),('bbb'),('ccc');
mysql> select * from depnew; 
+----+-----------+ 
| id | user_name | 
+----+-----------+ 
| 1  |    aaaa   |
| 2  |    bbb    |
| 3  |    ccc    | 
+----+-----------+ 
3 rows in set (0.00 sec)

# 使用truncate来进行清空数据表 
TRUNCATE TABLE depnew; 
# 再次插入数据
INSERT INTO depnew(user_name) VALUES('aaaa'),('bbb'),('ccc'); 
mysql> select * from depnew;
+----+-----------+ 
| id | user_name | 
+----+-----------+ 
| 1  |    aaaa   |
| 2  |    bbb    |
| 3  |    ccc    | 
+----+-----------+ 
3 rows in set (0.00 sec)

注意：TRUNCATE语句删除后将重置自增列，表结构及其字段、约束、索引保持不变，执行速度比DELETE语句块

10.DQL语句

只用一个语法，那就是SELECT。专门用来服务于数据的查询，在所以应用程序当中数据的查询占据sql使用的80%。

#语法
SELECT <字段名列表> 
FROM <表名或视图> 
[WHERE <查询条件>] 
[GROUP BY <分组的字段名>]
[HAVING <条件>]
[ORDER BY <排序的字段名> [ASC 或 DESC]]
[LIMIT [位置偏移量, ]行数];

分析：

SELECT关键字就是告诉mysql，我这是查询语句。MYSQL他知道你要查询什么数据吗？所以我们要通过 <字段名列表> 来告诉MYSQL。
FROM是告诉MYSQL，你要查询哪一张表的数据。怎么来告诉MYSQL， <表名>。
WHERE是条件，可以传也可以不传。WHERE条件是MYSQL用来筛选用的。例如：找出学校名字是 张三 的学生。where 名字=张三
GROUP BY也是可选参数，目的就是把数据进行分组
HAVING也是可选参数，一般是配合 GROUP BY 一起使用，作用分组的筛选条件。
ORDER BY可选参数，对查出的数据进行升序或者降序

注意：在mysql当中*是一个通配符号，也就是代表表中所有字段

#把计算机科学部门的编号找出来
#已知条件是 =》 部门名的名称是计算机科学
#求的是计算机科学的编号
SELECT d_no FROM departments WHERE d_name='计算机科学';
#执行结果
+-------+ 
| d_no  | 
+-------+ 
| A0001 | 
+-------+ 
1 row in set (0.00 sec)

10.1LIMIT

#部门表中从第5个位置开查找部门是否有生物系的编号
SELECT d_no,d_name FROM departments WHERE d_name='生物系' LIMIT 1,1;

#输出结果
+-------+--------+ 
| d_no |  d_name |
+-------+--------+ 
| A0010 |  生物系 | 
+-------+--------+

分析：

WHERE是对于数据表的一个筛选，筛选的结果我们称之为临时表。而LIMT则是在WHERE结果上的一个偏移。

11.MYSQL函数的使用

11.11聚合函数

函数名	作用
AVG()	返回某字段的平均值
COUNT()	返回某字段的行数
MAX()	返回某字段最大值
MIN()	返回某字段的最小值
SUM()	返回某字段的总和

#查询成绩表所有的分数的平均分
mysql > SELECT AVG(report) FROM score;
+-------------+ 
| AVG(report) | 
+-------------+
|   75.66667  | 
+-------------+
1 row in set (0.01 sec)

#查询分数表中一共有多少数据
mysql> SELECT COUNT(s_no) FROM score; 
+-------------+ 
| COUNT(s_no) |
+-------------+ 
|      3      |
+-------------+
1 row in set (0.00 sec)

#求出分数表中分数最高是多少
mysql > SELECT MAX(report) FROM score;
+-------------+ 
| MAX(report) |
+-------------+ 
|     89.0    |
+-------------+
1 row in set (0.00 sec)

#求出分数表中分数最低是多少
mysql > SELECT MIN(report) FROM score;
+-------------+ 
| MIN(report) |
+-------------+ 
|     59.0    |
+-------------+
1 row in set (0.00 sec)

#求出分数表中所有分数的总和
mysql > SELECT SUM(report) FROM score;
+-------------+ 
| SUM(report) |
+-------------+ 
|    227.0    |
+-------------+
1 row in set (0.00 sec)

11.2字符串处理函数

函数名	作用	举例
CONCA(str1,str1…str1)	字符串连接	SELECT CONCAT（‘My’，‘S’，‘群里’）
INSERT（str1,pos,len,newstr）	字符串替换	SELECT INSERT（‘这是SQL Server数据库’,3,10,‘MySql’）;
LOWER(str)	将字符串转换为小写	SELECTLOWER(‘MySql’);
UPPER(str)	将字符串转换为大写	SELECT UPPER(‘mysql’)
SUBSTRING(str,num,len)	字符串截取	SELECT SUBSTRNG(‘JavaMySQLOracle’,5,5)

函数名	作用	举例（结果与当前时间有关）
CURDATE（）	获取当前日期	SELECT CURDATE();返回：2018-04-23
CURTIME（）	获取当前时间	SELECT CURTIME(); 返回：09:37:35
NOW()	获取当前日期和时间	SELECT CURTIME(); 返回：2018-04-23 09:37:35
WEEK(date)	返回日期date为一年中的第几周	SELECT WEEK(NOW); 的第几周返回：16
YEAR(dat)	返回日期 date的年份	SELECT YEAR(NOW);返回：2018
HOUR(time)	返回时间time的小时值	SELECT HOUR(NOW);返回：9
MINUTE(time)	返回时间time的分钟值	SELECT MINUTE(NOW);返回：38
DATEDIFF(date1,date2)	返回日期参数date1和date2之间相隔的天数	SELECT DATEDIFF((NOW),‘2017-8-8’);返回258
ADDDATE(date,n)	计算日期参数date加上n天后的日期	SELECT ADDDATE(NOW(),5)；返回：2018-04-23 09:40:21

#返回当前日期
mysql> select curdate(); 
+------------+ 
| curdate()  |
+------------+ 
| 2021-05-12 |
+------------+ 
1 row in set (0.00 sec)

#获取当前时间
mysql> select curtime();
+-----------+
| curtime() |
+-----------+
| 15:06:20  |
+-----------+
1 row in set (0.00 sec)

#返回当前日期时间
mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2021-05-15 15:04:47 |
+---------------------+
1 row in set (0.00 sec)

#返回日期date为一年中的第几周
mysql> select week(now());
+-------------+
| week(now()) |
+-------------+
|          19 |
+-------------+
1 row in set (0.00 sec)

#返回时间time的分钟值
mysql> select minute(now());
+---------------+
| minute(now()) |
+---------------+
|            10 |
+---------------+
1 row in set (0.00 sec)

#返回日期参数date1和date2之间相隔的天数
mysql>  select datediff(now(), '1999-8-19');
+------------------------------+
| datediff(now(), '1999-8-19') |
+------------------------------+
|                         7940 |
+------------------------------+
1 row in set (0.00 sec)

#计算日期参数date加上n天后的日期
mysql> select adddate(now(),5);
+---------------------+
| adddate(now(),5)    |
+---------------------+
| 2021-05-20 15:13:16 |
+---------------------+
1 row in set (0.00 sec)

11.3数学函数

函数名	作用	举例
CEIL(x)	返回大于或等于数值x的最小整数	SELECT CEIL(2.3)返回：3
FLOOP(x)	返回小于等于数值X的最大整数	SELECT CEIL(2.3)返回：2
RAND()	返回0-1间的随机数	SELECT CEIL(2.3)返回：0-1间的小数

12.高级查询

12.1子查询

子查询指一个查询语句嵌套在另一个查询语句内部的查询，这个特性从 MySQL 4.1 开始引入，在SELECT 子句中先计算子查询，子查询结果作为外层另一个查询的过滤条件，查询可以基于一个表或者多个表。

子查询中常用的操作符有 ANY（SOME）、ALL、IN 和 EXISTS。

子查询可以添加到 SELECT、UPDATE 和 DELETE 语句中，而且可以进行多层嵌套。子查询也可以使用比较运算符，如“<”、“<=”、“>”、“>=”、“!=”等。

子查询在WHERE语句中的一般用法：

SELECT … FROM 表1 WHERE 字段1 比较运算符(子查询)

练题：

从employeemes表中，求出比李四年龄大的员工？？？

# 通过学习的SELECT语句，先求出李四的出生日期，再来进行WHERER查询得到小于自己的员工 
mysql> # 求出张三的出生日期
mysql> select borndate from employeemes where employeename='李四'; 
+---------------------+ 
| borndate            |
+---------------------+ 
| 2008-03-14 09:17:00 | 
+---------------------+ 
1 row in set (0.00 sec) 
mysql> select borndate from employeemes where borndate < '2008-03-14 09:17:00';

+---------------------+ 
| borndate            |
+---------------------+ 
| 2008-03-14 09:17:00 | 
+---------------------+

使用子查询方式来完成上面需求

mysql> select employeename,employeeaccount,borndate from employeemes where borndate > (select borndate from employeemes where employeename='李四'); 
+--------------+-----------------+---------------------+
| employeename | employeeaccount | borndate 		   | 
+--------------+-----------------+---------------------+ 
| 李四四 | A0002 | 2018-03-14 09:17:00 				 | 
+--------------+-----------------+---------------------+ 
1 row in set (0.00 sec)

注意：上面子查询SELECT查询字段能是一个也就是 bornDate ，否则报错。也就是将子查询和比较运算符联合使用，必须保证子查询返回的值不能多于一个

ERROR 1241 (21000): Operand should contain 1 column(s)

12.2 INNER JOIN子句查询

INNER JOIN字句将一个表中的行与其他表的行进行匹配，并允许从两个表中查询含列的行记录。

INNER JOIN子句是SELECT语句的可选部分，他出现在FROM子句之后。

在使用INNER JOIN子句之前，必须指定一下条件：

首先，在FROM子句中指定主表。
其次，表中药连接的主表应该出现在INNER JOIN子句中。理论上说，可以连接多个其他表。但是，为了获得更好的性能，应该限制要连接的表的数量（最好不要超过三个表）。
第三，连接条件或连接谓词。连接条件出现在INNER JOIN子句的ON关键词之后。连接条件是将主表的行与表中的行进行匹配的规则。

INNER JOIN子句的语法如下：

#column_list 字段列表
#join_condition 连接条件
#where_conditions是所有主表和子表交集完成之后筛选条件

SELECT column_list
FROM t1
INNER JOIN t2 ON join_condition1
INNER JOIN t3 ON join_condition2
...
WHERE WHERE where_conditions;

假设使用INNER JOIN子句连接两个表：t1和t2，我们简化上面的语法。

SELECT colunm_list
FROM t1 
INNER JOIN t2 ON join_condition;

对于t1表中的每一行，INNER JOIN子句将他与t2 的表的每一行进行比较，以检查他们是否都满足连接条件。当满足连接条件时。INNER JOIN将返回有t1和t2表中的列组成的新行。

请注意：t1和 t2表中的行必须根据连接条件进行匹配。如果找不到匹配项，查询将返回一个空结果集。当连接超过2两个表时，也应用此逻辑。

一下维恩图说明了INNER JOIN子句的工作原理。结果集中的行必须出现在两个表中：t1和t2，如两个圆交叉部分所示

INNER JOIN 中避免错误：

如果连接具有相同列名称的多个表，则必须使用限定符引用SELECT和ON子句的列，避免列错误。

例如，如果t1和t2表具有名为c的一个相同列名，则必须在SELECT 和ON子句中使用表限定符，如使用t1.c或t2.c指定引用是哪个表的c列。

综合练题：查询“销售理念”绩效考核至少一次并且绩效刚好大于60分的员工？？

#第一种方式
mysql> SELECT employeename,a.employeeResult,emp.employeeAccount,com.compnentName 
	-> FROM employeemes AS emp 
	-> INNER JOIN assscore AS a 
	-> ON emp.employeeaccount = a.employeeaccount 
	-> INNER JOIN AssComponent AS com ON a.componentId = com.componentId 
	-> WHERE com.compnentName='销售理念' AND a.employeeResult > 60; 
+--------------+----------------+-----------------+--------------+ 
| employeename | employeeResult | employeeAccount | compnentName |
+--------------+----------------+-----------------+--------------+ 
| 李四 		  | 	     79    | 		A0001 	 |	 销售理念	 |
| 李五五    	 | 			89    | 		A0003   |   销售理念    | 
+--------------+----------------+-----------------+--------------+ 
2 rows in set (0.00 sec)

# 第二种方式实现
SELECT * FROM 
employeemes AS mes,AssScore AS a,AssComponent AS ac 
WHERE mes.employeeAccount=a.employeeAccount 
AND ac.componentId=a.componentId 
AND ac.compnentName='销售理念' AND a.employeeResult > 60;

# 第三种方式实现
SELECT employeeAccount 
FROM employeemes 
WHERE employeeAccount = (SELECT ass.employeeAccount FROM AssScore AS ass 
INNER JOIN AssComponent AS ac 
ON ass.componentId = ac.componentId
WHERE ass.employeeResult > 60 AND ac.compnentName='销售理念' LIMIT 1);
# 小结：此方法对于返回结果有多行时会报错，应用不灵活

小结：

子查询比较灵活、方便、常作为增删改查的筛选条件，适合于操纵一个表的数据
表连接更适合于查看多表的数据

12.3 IN子查询

# 使用in子查询来实现并解决第三种方式的缺点
SELECT * FROM employeemes WHERE employeeAccount IN ( 
    SELECT employeeAccount FROM AssScore 
    WHERE componentId=( 
    SELECT componentId FROM AssComponent WHERE compnentName='销售理念') 
    AND AssScore.employeeResult > 80 
);

12.4 GROUP BY 子句

GROUP BY 是分组的意思，根据数据中的字段来进行分组。

#语法
CROUP BY{字段名 | 表达式 | 正整数}[ASC | DESC],...[WITH ROLLUP]

说明：

（1）GROUP BY子句后通常包含列名或表达式。也可以用正整数表示列，如指定3，则表示按第3列分组。

（2）ASC为升序，DESC为降序，系统默认是ASC，则按分组的第一列升序排序输出结果。

（3）可以指定多列分组。若指定多列分组，则先按指定的第一列分组再对指定的第二列分组，以此类推。

（4）使用带ROLLUP操作符的CROUP BY子句：指定在结果集内不仅包含由CROUP BY提供的正常行还包含汇总行。

# 按系统计各系的学生人数，SQL语句如下。 
SELECT d_no,COUNT(s_no) FROM students GROUP BY d_no;
+-------+-------------+ 
| d_no  | COUNT(s_no) | 
+-------+-------------+ 
| 10001 | 	   4 	  | 
| 10002 | 	   4 	  |
+-------+-------------+ 
2 rows in set (0.01 sec)

12.5 GROUP BY 排序

#语法
CROUP BY{列 | 表达式 | 正整数}[ASC | DESC]

说明：

(1)GROUP BY是一个后可以是一个列、一个表达式，也可以用正整数表示列，如指定3，则表示按第3列排序

(2)关键字ASC表示升序排列，DESC表示降序排列，系统默认值为ASC

(3)指定要排序的列可以多列。如果多列，系统按照第一列排序，当该列出现重复值时，按第二列排序，以此类推。

# BOO1课程的学生学号和成绩从大到小排列 
SELECT * FROM score WHERE c_no='B0001' ORDER BY report DESC 
+-------+-------+--------+ 
| s_no  | c_no  | report |
+-------+-------+--------+ 
| A0001 | B0001 |  87.0  |
| A0004 | B0001 |  87.0  |
| A0001 | B0001 |  77.0  |
+-------+-------+--------+ 
3 rows in set (0.00 sec)

13 综合练习

USE xuesheng;

CREATE TABLE teachers(
	#字段名 数据类型 [属性] [注释]...
	t_no CHAR(8) NOT NULL COMMENT '教师编号',
	t_name VARCHAR(12) NOT NULL COMMENT '教师姓名',
	d_no CHAR(8) NOT NULL COMMENT '院系编号',
	#为我们teachers这个表设置一个主键
	PRIMARY KEY (t_no)
)ENGINE=INNODB DEFAULT CHARSET=utf8;

#创建院系表
CREATE TABLE departments(
	d_no CHAR(8) NOT NULL COMMENT '院系编号',
	PRIMARY KEY (d_no),
	d_name VARCHAR(12) NOT NULL COMMENT '院系名称'
)ENGINE=INNODB DEFAULT CHARSET=utf8;

#创建students
CREATE TABLE students(
	s_no CHAR(8) NOT NULL PRIMARY KEY COMMENT '学生编号',
	s_name VARCHAR(16) NOT	NULL COMMENT '学生姓名',
	sex ENUM('男','女') DEFAULT '男' COMMENT '性别',
	birthday DATE NOT NULL COMMENT '出生日期',
	d_no CHAR(8) NOT NULL COMMENT '所在部系',
	address VARCHAR(100) NOT NULL COMMENT '家庭地址',
	phone CHAR(11) NOT NULL COMMENT '手机号码',
	photo BLOB COMMENT '照片'
)ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT '学生表';


CREATE TABLE course(
	c_no CHAR(8) NOT NULL PRIMARY KEY,
	c_name VARCHAR(12) NOT NULL,
	d_no CHAR(8) NOT NULL,
	hours INT NOT NULL COMMENT '学习时长',
	credit INT NOT NULL COMMENT '学分',
	c_type ENUM('必修课','选修课') DEFAULT '必修课' 
)ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT '课程表';

CREATE TABLE score(
	s_no CHAR(8) NOT NULL COMMENT '学生编号',
	c_no CHAR(8) NOT NULL COMMENT '课程编号',
	report FLOAT(3,1) DEFAULT 0
)ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT '分数表';

# 修改分数表，把paimary key 去掉
ALTER TABLE score CHANGE s_no s_no CHAR(8) NOT NULL COMMENT '学生编号';
# 删除主键
ALTER TABLE score DROP PRIMARY KEY;

CREATE TABLE teach(
	t_no CHAR(8) NOT NULL COMMENT '教师编号',
	c_no CHAR(8) NOT NULL COMMENT '课程编号',
	KEY t_no (t_no),
	KEY c_no (c_no)
)ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT '认课表';

# 先进行院系数据的插入
INSERT INTO departments VALUES
('10001','中南大学'),
('10002','湖南大学');
SELECT * FROM departments;

INSERT INTO students
(`s_no`,`s_name`,`sex`,`birthday`,`d_no`,`address`,`phone`)
VALUES
('A0001','李旺财','男','2000-7-13','10001','天堂三路坡子街001商铺','13800138000')
,('A0002','肖全才','女','1999-7-13','10001','天堂三路坡子街002商铺','13800138001')
,('A0003','李德财','男','2001-2-23','10001','大漠豪杰徽章路坡子街003商铺','13800138002')
,('A0004','贝牛逼','女','1988-7-13','10001','浏阳无路坡子河001蒸菜馆','13800138003');
SELECT * FROM students;

INSERT INTO teachers VALUES
('T0001','王鹏','10001')
,('T0002','张无忌','10001')
,('T0003','李云龙','10001')
,('T0004','潘子','10001')
,('T0005','马保国','10001');

INSERT INTO course VALUES
('B0001','马克思与理论','10001',80,5,'必修课')
,('B0002','闪电五连鞭','10001',110,10,'选修课')
,('B0003','毛泽东思想','10001',50,5,'必修课')
,('B0004','邓小平理论','10001',72,3,'选修课')
,('B0005','JAVA入门到放弃','10001',100,8,'必修课');

INSERT INTO teach VALUES
('T0005','B0002')
,('T0003','B0003')
,('T0002','B0001')
,('T0004','B0004')
,('T0001','B0005')
,('T0004','B0001');

INSERT INTO score VALUES 
('A0001','B0002',88)
,('A0002','B0004',49)
,('A0003','B0005',89)
,('A0004','B0005',70)
,('A0002','B0003',97)
,('A0004','B0001',87);

#1.去除重复行
SELECT DISTINCT d_no FROM students;

#2.统计男生的人数
SELECT COUNT(*) AS '男生人数' FROM students WHERE sex='男';
#3.查询男生的基本信息
SELECT * FROM students WHERE sex='男';

#4.查询学生的姓名、系部名称和联系地址
SELECT s.s_name '学生姓名',d.d_name '所在系部',
s.address '学生地址' FROM 
students AS s,departments AS d WHERE s.d_no = d.d_no;

#5.查询选修了B0001课程且成绩在80分以上的学生
SELECT DISTINCT s_no FROM score WHERE c_no='B0001' AND report > 50;

#6.查询选修了B0001课程且成绩在80分以上的学生姓名
SELECT s_name '学生姓名',phone '联系电话' FROM students 
WHERE s_no IN (SELECT DISTINCT s_no FROM score WHERE c_no='B0001' AND report > 50);
# SELECT s_name,phone from students where s_no IN('A0001','A0004');

#7.查询出生日期在1988-7-1和2000-7-1出生的学生。
 SELECT * FROM students WHERE birthday BETWEEN '1988-7-1' AND '2000-7-1';
# BETWEEN 是指值在指定的范围内，命名如：80 >= age and <= 90是一个意思

#8.查询院系编号为10001或10002的学生。 
SELECT * FROM departments; 
SELECT * FROM students WHERE d_no IN ('10001','10002');

#9.查询成绩在60～70之间的学生和课程信息 
SELECT stu.s_name AS '学生姓名',s.report '分数',c.c_name '考核项目' 
FROM score s INNER JOIN students stu 
	ON s.s_no=stu.s_no 
INNER JOIN course AS c
	ON s.c_no=c.c_no 
WHERE s.report BETWEEN 60 AND 70;

#10.查询电话不为空的学生信息 
SELECT * FROM students WHERE phone <> ''; 
SELECT * FROM students WHERE phone IS NOT NULL;

#11.查询姓李的学生信息
SELECT * FROM students WHERE s_name LIKE '李%';
-- like 是模糊查询也就是不明确的查询例如：我只一个人的姓李
-- like % 是在李的后面那也就是说明李小明，李双，李小双大又
-- like %是在李的前面那也就是只要包含李字就ok

-- 查询名字中包含德学生有哪一些
SELECT * FROM students WHERE s_name LIKE '%德%';
# % 多个字符 
# _ 单个字符 
# [] 指定字符的取值范围 如：[x_z]表示[xyz]中的任意单个字符 
# [^] 指定字符要排除的取值范围，[^x_z] 表示不在集合中的[xyz]任意一个字符

#12.查询名字中包含德学生有哪一些
SELECT * FROM students WHERE s_name LIKE '%德%';

#13.查询名字只有二个字的学生信息  _下划线代表一个任意字符
SELECT * FROM students WHERE s_name LIKE '__';

#14.统计各系男女生人数(多字段分组) 
SELECT d_no 院系编号,COUNT(*),sex 性别 FROM students GROUP BY d_no,sex;

#15.求选修的各门课程的平均成绩和选修该课程的人数 
SELECT c_no '课程编号',AVG(report) '平均分',COUNT(c_no) '人数' FROM score GROUP BY c_no;

#16.统计各系教师人数,包括汇总行 
SELECT COUNT(*)AS '各系教师人数',d_no FROM teachers GROUP BY d_no WITH ROLLUP;	

#17.按系部和出生日期排序 
SELECT s_no,s_name,birthday,d_no FROM students ORDER BY d_no DESC ,birthday DESC;

#18.按学生的平均成绩从高到低排序，显示学号和平均成绩 
SELECT s_no,AVG(report) FROM score GROUP BY s_no ORDER BY AVG(report) DESC;

#19.查询选修了两门以上课的学生学号
SELECT s_no,COUNT(*) FROM score GROUP BY s_no HAVING COUNT(*) > 2;

#20.查询讲授了2门课的老师
SELECT t_no FROM teach GROUP BY c_no HAVING COUNT(*) > 1; --方式1

SELECT s_no,s_name FROM students WHERE s_no IN (SELECT s_no FROM score GROUP BY 
s_no HAVING COUNT(*) > 2); --方式2

#21.查询课程号为‘B0001’成绩前五名
SELECT s_no,report FROM score WHERE c_no='b0001' ORDER BY report DESC LIMIT 5;

#22.查询成绩第2名至第5名的学生
SELECT * FROM score WHERE c_no = 'b0001' ORDER BY report DESC LIMIT 1,4;

#23.查询课程为b0001的最高分、最低分
SELECT MAX(report),MIN(report) FROM score GROUP BY c_no HAVING c_no='B0001';

#24.求各课程选修的学生人数以及课程名
SELECT COUNT(*) '人数' FROM score GROUP BY c_no; -- 实现方法1

SELECT c.c_name,l.aaa,l.c_no 
FROM (SELECT c_no,COUNT(*) 'aaa' FROM score GROUP BY c_no) AS l 
INNER JOIN course AS c ON l.c_no=c.c_no; -- 实现方式2

#25.所有学生选过的课程名和课程号
SELECT DISTINCT course.c_name,course.c_no FROM score,course WHERE score.c_no=course.c_no;

#26.查询中南大学学生所选修的课程和成绩
SELECT  course.c_no,
score.report FROM  students,score,course,
departments WHERE students.s_no=score.s_no
AND course.c_no=score.c_no
AND students.d_no=departments.d_no
AND departments.d_name='中南大学';


#27.查询选修了“马保国”老师课程的学生
SELECT score.s_no,course.c_no,
t_name FROM teachers,teach,course, 
score WHERE course.c_no=score.c_no 
AND teach.c_no= course.c_no 
AND teachers.t_no=teach.t_no 
AND teachers.t_name='马保国';

#28.查找讲授JAVA入门到放弃的老师
SELECT t_name,c_name FROM teachers,teach,
course WHERE teach.c_no= course.c_no 
AND teachers.t_no=teach.t_no 
AND c_name='JAVA入门到放弃';

#29.查询中南大学成绩80以上的学生信息包含课程信息
SELECT students.s_no,d_name,
report FROM score JOIN students USING(s_no)JOIN departments USING(d_no)WHERE d_name='中南大学' AND report>80;

#30.查询已选课学生，以及未选修课的学生信息(左外连接完成)
SELECT stu.s_name,s.report,c.c_name FROM
students AS stu
LEFT OUTER JOIN score AS s ON stu.s_no = s.s_no
LEFT OUTER JOIN course AS c ON s.c_no=c.c_no;

#31.查询已选课学生，以及未选修课的学生信息(右外连接完成)
SELECT stu.s_name,s.report,c.c_name FROM score AS s 
RIGHT OUTER JOIN course AS c ON s.c_no = c.c_no 
RIGHT OUTER JOIN students AS stu ON stu.s_no = s.s_no;

14 索引

索引是一种特殊的数据库结构，可以用来快速查询数据库表中的特定记录。在MySQL中，所有的数据类型都可以被索引

MySQL支持的索引主要有HASH索引和B-Tree索引。目前，大部分MySQL索引都是以B-树（BTREE）方式储存的，是MySQL数据库中使用最为频繁的索引类型。除了Archive储存引擎之外的其他所有储存引擎都支持B-Tree索引

B-Tree索引储存结构在数据库的数据检索中有着非常优异的表现也就是检索快

14.1索引分类

MySQL的索引包括普通索引（INDEX）、唯一索引（UNIQUE）、主键（PRIMARY KEY）、全文索引（FULLTEXT）和空间索引（SPATIAL）

INDEX

索引的关键字是INDEX，这是最基本的索引，它没有任何限制。给定字段为index索引时，表示每一行的索引字段为特殊位置。

唯一性索引（UNIQUE）

关键字是UNIQUE。与普通索引类似，但是UNIQUE索引列的值必须唯一，允许有空值

，则列值的组合必须唯一。在一个表上可以创建多个UNIQUE索引

主键索引（PRIMARY KEY）

它是一种特殊的唯一索引创建主键索引，不允许有空值。一般是在建表的时候同时创建主键索引。也可通过修改表的方法增加主键，但是一个表只能有一个主键索引

全文索引（FULLTEXT）

FULLTEXT索引只能对CHAR、VARCHAR和TEXT类型的列编制索引，并且只能在MYISAM表中编制。在MySQL默认情况下，对于中文作用不大。

索引建立在一列还是多列上，有可以分为单列索引、多列索引（复合索引）

14.2索引的设计原理

1.在主键创建索引，在InnoDB中如果通过主键来访问数据效率是非常高的。

2.为经常需要排序、分组和联合操作的字段建立索引，即那些将用于JOIN、WHERE判断和OPDER BY 排序的字段上。

3.为经常作为作为查询条件的字段建立索引，如用JOIN、WHERE判断的字段。

4.尽量不要对数据库当中某个含有大量重复的值的字段建立索引，如“性别”字段，在这样的字段上建立索引将不会有什么帮助；相反，还有可能降低数据库的性能。

5.限制索引的数目。

6.尽量使用数据量少的索引。

7.尽量使用前缀来索引。

8.删除再使用或者很少使用的索引。

14.3 创建表时创建索引

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name 
			[ ( [column_definition] , ... | [index_definition] ) ] 				[table_option] [select_statement]; 
			
# 其中，index_definition为索引项。 
			[CONSTRAINT [symbol]]PRIMARY KEY [index_type] (index_col_name,...) 
			|{INDEX | KEY} [index_name] [index_type] (index_col_name,...)
			| [CONSTRAINT [symbol]] UNIQUE [INDEX] [index_name] [index_type] (index_col_name,...)
			| [FULLTEXT|SPATIAL] [INDEX] [index_name] (index_col_name,...) 
			| [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name,...) 
[reference_definition]

例1：创建students3表，s_no为主键索引，s_name为唯一性索引，并在address列上前5位字符创建索引

CREATE TABLE students3(
	s_no CHAR(8) NOT NULL COMMENT '学生编号',
	s_name VARCHAR(16) NOT NULL COMMENT '学生姓名',
	sex ENUM('男','女') DEFAULT '男' COMMENT '性别',
	birthday DATE NOT NULL COMMENT '出生日期',
	d_no CHAR(8) NOT NULL COMMENT '所在部系',
	address VARCHAR(100) NOT NULL COMMENT '家庭住址',
	phone CHAR(11) NOT NULL COMMENT '手机号码',
	photo BLOB COMMENT '照片',
	PRIMARY KEY(s_no),
	UNIQUE INDEX name_index(s_name),
	INDEX ad_index(address(5)) #前缀索引
)ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT='学生表3';

索引名称	索引字段	索引类型
PRIMARY	s_no	Unipue
name_index	s_name	Unipue
add_index	address

14.4 用CEEATE INDEX语句创建

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
			[USING index_type] 
			ON tbl_name (index_col_name,...) 
# 其中，index_col_name的格式如下。 c
			ol_name [(length)] [ASC | DESC]

为便于按地址进行查询，为students3表的address列上的6个字符建立一个升序索引address_index

CREATE INDEX address_index ON students3(address(6) DESC)

Indexes	Columns	Columns
address_index	address

studens的d_no字段经常作为查询条件，建立普通索引

CREATE INDEX d_no_index ON students3(d_no);

索引信息

Indexes	Columns	Columns
address_index	address
d_no_index	d_no

在course表c_name列上建立一个唯一索引c_name_index

CREATE UNIQUE INDEX c_name_index ON course(c_name);

索引信息

Indexes	Columns	Columns
PRIMARY	c_no	Unipue
c_name_index	c_name	Unipue

teachers表的t_name字段建立一个唯一索引t_name_index

–浪潮的teachaeers表在test数据库

CREATE UNIQUE INDEX t_name_index ON teachers(t_name);

索引信息

Indexes	Columns	Columns
PRIMARY	t_no	Unipue
t_name_index	t_name	Unipue

可以在一个索引的定义中包含多个列，中间用逗号个隔开，但是他们要属于同一个表。这样的索引叫做复合索引

在score表的s_no和c_no列上建立一个复合索引score_index

CREATE INDEX score_index ON score(s_no,c_no);

索引信息

Indexes	Columns	Columns
score_index	s_no,c_no

14.5 通过ALTER TABLE语句创建索引

在已经存在的表上可以用ALTER TABLE语句创建索引

ALTER TABLE tbl_name 
ADD [PRIMARY KEY| UNIQUE | FULLTEXT | SPATIAL ] INDEX 				  			index_name（col_name [(LENGTH)] [ASC|DESC]）;

在students3表上建立s_no主键索引

ALTER TABLE students3 DROP PRIMARY KEY ; -- 已存在主键就先删除
ALTER TABLE students3 ADD PRIMARY KEY (s_no);

索引信息

Indexes	Columns	Columns
PRIMARY	t_no	Unipue
t_name_index	t_name	Unipue
mark	t_name,d_no

在depatemens表中的d_name创建唯一索引

ALTER TABLE departments ADD UNIQUE INDEX d_name_index(d_name);

索引信息

Indexes	Columns	Columns
PRIMARY	d_no	Unipue
d_name_index	d_name	Unipue

14.6索引的查看

想要查看表中创建的情况，使用 SHOW INDEX FROM 表名；语句

students3索引的查看

SHOW INDEX FROM students3;

14.7用DROP INDEX语句删除索引

DROP INDEX index_name ON tbl_name ; # index_name为要删除的索引名，tb1_name为索引所在的表

删除teachers表的mark索引

DROP INDEX mark ON teachers;

删除students3中的主键和d_no_index、address_index

ALTER TABLE students3 DROP PRIMARY KEY,DROP INDEX address_index,DROP INDEX d_no_index;

15 事务、视图、索引、备份和恢复

15.1事务

事务（TRANSACTION）是作为单个逻辑工作单元执行的一系列操作。
多个操作为一个整体向系统提交，要么都执行、要么都不执行。
事务是一个不可分割的工作逻辑单元

1、包含一组数据操作命令，作为一个整体一起提交或撤销。

2、特性：原子性、一致性、隔离性、持久性

3、创建事务：开始事务：BGGIM/START TRANSACTION

提交事务：COMMIT

回滚事务：ROLLOBACK

MySQL中支持事务的储存引擎InnoDB和BDB

-- 事务
#事务必须具有以下四个属性，简称ACID属性
#原子性（Atomicity）事务是一个完整的操作，事务的各步操作是不可分割的（原子的）,要么都执行，要么都不执行
#一致性（Consistecy）当事务完成时，数据必须处于一致状态
#隔离性（Isilation）并发事务之间彼此隔离、独立，它不应以任何方式依赖于或影响其他事务
#持久性（Durability）事务完成后，它对数据库的修改被永久保持
CREATE TABLE bank(
	customeName CHAR(10) NOT NULL COMMENT '用户名',
	currentMoney DECIMAL(10,2) NOT NULL COMMENT '当前余额'
)ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT '账户表';
INSERT INTO bank VALUES('张三丰','100'),('陈思明','50');
SELECT * FROM bank;

#模拟现实生活中的转账
try{
	UPDATE bank SET currentMoney = currentMoney +50 WHERE customeName='张三丰';
	UPDATE bank SET currentMoney = currentMoney -50 WHERE customeName='陈思明';
}cach(){
	ROLLBACK;
}
COMMIT;

15.2 自动关闭和开启事务

默认情况下，没一条单独的SQL语句视为一个事务
关闭默认提交状态后，可手动、关闭事务
```
#开启事务
SET autocommit=0;
BEGIN;

UPDATE bank SET currentMoney = currentMoney -50 WHERE customeName='张三丰';
UPDATE bank SET currentMoney = currentMoney +50 WHERE customeName='陈思明';

#提交事务
COMMIT;

#回滚
ROLLBACK;
SET autocommit=1;
```
15.3 视图

1、视图是一张虚拟表

1.表示一张表的部分数据或多张表的综合数据

2.其结构和数据是建立在对表的查询基础上

2、视图中不存放数据

1.数据存放在视图所引用的原始表中

3、一个原始表，根据不同用户不同需求，可以创建不同的视图