mysql关系模型的完整性约束是对关系的某种约束条件。其主要是为了保障数据的正确性和一致性，防止数据库存在不符合语义的，

一、mysql数据库的启动与关闭

1、windows

启动服务(cmd)：mysql --console 或 net start mysql
关闭服务(cmd)：mysqladmin -uroot shudown 或 net stop mysql

2、Linux

启动服务：service mysql start
关闭服务：service mysql stop
重启服务：service restart stop

二、mysql编程语言

1、mysql语言组成

1.1 数据定义语言：DDL

数据定义语言主要用于对数据库以及数据库中的各种对象进行创建、删除、修改等操作。
create：用于创建数据库或数据库对象。
alter：用于对数据库或数据库对象进行修改。
drop：用于删除数据库或数据库对象

1.2 数据操纵语言：DML

数据操纵语言主要用于操纵数据库中各种对象，特别是检索和修改数据。
select：用于从表或视图中的检索数据。
insert：用于将数据插入到表或视图中。
update：用于修改表或视图中的数据。可以同时修改多行数据。
delete：用于从表或视图中删除数据。可根据指定条件删除指定数据

1.3 数据控制语言：DCL

数据控制语言主要用于安全管理，例如确定哪些用户可以查看或修改数据库中的数据
crant：用于赋予权限。
revoke：用于回收权限。

2、mysql函数

2.1 聚合函数

count()：返回表中所选集合中非null值的行的数量
sum()：求和
avg()：平均值
max()：最大值
min()：最小值

2.2 数字函数

abs()：求出表中某个字段取值的绝对值
floor()：返回小于或等于参数x的最大整数
rand()：返回0-1之间的随机数
truncate(x,y)：返回x保留到小数点后y位的值
sqtr(x)：求参数x的平方根

2.3 字符串函数

UPPER(s)/UCASE(s)：将字符串s中的所有字母变成大写字母
LEFT(s，n)：返回字符串s的前n个字符
SUBSTRING(s，n，len)：从字符串s的第n个位置开始获取长度为len的字符串

2.4 日期函数

CURDATE()/CURRENT_DATE()：获取当前日期
CURTIME()/CURRENT_TIME()：获取当前时间
NOW()/CURRENT_TIMESTAMP()/LOACLTIME()/SYS-DATE()/LOCALTIMESTAMP()：获取当前日期和时间

2.5 其他函数

IF(expr,v1,v2)：条件判断函数，如果表达式v1不为空，则显示v1的值，否则显示v2的值
IFNULL(v1，v2)：条件判断函数，如果表达式v1不为空，则显示v1的值，否则显示v2的值
VERSION()：获取数据库版本号

三、mysql数据的定义

1、数据库的定义

1.1 创建数据库

create database [if not exists] db_name 
default character set = charset_name
defalut collate = collation_name

# db_name：数据库名
# if not exists:创建数据库前进行判断数据库是否已存在，不存在则创建
# character set：指定数据库字符集()character 
# charset_name：字符集名称，简体中文字符集：gb2312
# collate：指定字符集的校对规则
# collation_name：校对规则名称，简体中文字符集的校对规则为gb2312_chinese_ci

示例：
create database db_school
defalut character set gb2312
defalut collate gb2312_chinese_ci

1.2 选择数据库

use db_name;

1.3 查看所有数据库

show {databases | schemas}; 

# mysql：用户访问权限
# information_schema：保存关于mysql服务器所维护的所有他数据库的信息，如：数据库名，数据库的表，表字段的数据类型与访问权限。
# performance_schema：主要用于收集数据库服务器性能参数等
# test：用户利用该数据库进行测试工作

1.4 修改数据库

# mysql中，数据库的默认字符集为latin1，默认校对规则为lation1_swedish_ei。
# 使用ALTER DATABASE或ALTER SCHEMA修改数据库的默认字符集和默认校对规则。

alter DATABASE db_school
default character set gb2312
default collate gb2312_chinese_ci;

1.5 删除数据库

drop database [if not exists] db_name;

1.6 退出数据库

\q  或  exit;

1.7 查看当前登录的用户

select user();

1.8 删除用户

drop user 用户名;

2、表的定义

2.1 数值类型

BIT()：位字段类型，范围为1-64，默认为1
TINYINT()：很小的整数，带符号范围为-128-127，无符号范围为0-255
BOOL.BOOLEAN()：TINYINT(1)的同义词
SMALLINT()：小的整数，带符号范围为-32768-32767，无符号的范围为0-65535
MEDIUMINT()：中等大小的整数，带符号范围为-8388608-8388607，无符号范围为0-16777215
INT()：普通大小的整数，带符号范围为-2147483648-2147483647，无符号范围为0-4294967295
INTEGER()：INT的同义词
BIGINT()：大整数，带符号范围为-9223372036854775808-9223372036854775807，无符号范围为0-18446744073709551615
DOUBLE(M，D)：普通大小的浮点数，M是小数总位数，D是小数点后面的位数
DECIMAL(M，[D])：压缩的浮点数，M是小数位数(精度)的总数，D是小数点(标度)后面的位数，
DEC()：DECIMAL同义词

unsigned不允许负值。
zerofill：当插入mysql中该字段的值的长度小于定义的长度时，会在数值前面补全相应数据的0。

2.2 日期和时间类型

DATE：日期型，范围：1000-01-01~9999-12-31
DATETIME：日期和时间组合，范围：1000-01-01 00:00:00~9999-12-31 23:59:59
TIMESTAMP()：时间戳，范围：1970-01-01 00:00:00到2037年
TIME：时间型，范围：-838:59:59~838:59:59
YEAR(2|4)：两位或四位格式的年，默认是四位格式，四位格式范围：19012155，两位格式范围：7069

# mysql时间格式显示为：YYYY-MM-DD HH:MM:SS，同时允许使用字符串或数字为类型分配值

2.3 字符串类型

CHAR()/CHARACTER()：固定长度的字符数据类型，用于保存以文本格式存储的信息，范围：0~255
VARCHAR()：可变长的字符数据类型，用于保存以文本格式存储的信息，范围：0~65535
TINYTEXT()：最大长度为255(28-1)字符的TEXT列
TEXT()：最大长度为255(216-1)字符的TEXT列

2.4 创建表

# 基本语法：
CREATE TABLE tb_name(
    字段名1 数据类型 [列级完整性条件] [默认值]
    字段名2 数据类型 [列级完整性条件] [默认值]
)[ENGINE=引擎类型];

# 示例：创建tb_student表
create table tb_student(
        studentNo CHAR(10) NOT NULL UNIQUE,       # 学号
        studentName VARCHAR(20) NOT NULL,         # 姓名
        sex CHAR(2),                              # 性别
        birthday DATE,                            # 出生日期
        native VARCHAR(10),                       # 籍贯
        classNo CHAR(6),                          # 所属班级
)ENGINE=InnoDB;

# 约束条件
# NULL：允许在插入记录时不给出该列的值。
# NOT NULL：插入或更新数据时，该列必须要有值
# AUTO_INCREMEN：自增长约束，初始值为1，只要整型列才能设置此属性，每个表只能设置一个自增长约束。
# DEFAULT：向数据表中插入数据时，如果没有明确给出某个字段所对应的值，则允许为此字段指定一个值。
# ENGINE=InnoDB：为新建表指定存储引擎。
# 存储引擎：如何存储数据，如何为存储的数据建立索引和如何更新、查询数据等技术的实现方法。Oracle、SQLserver等数据库只要一种存储引擎，而MYSQL提供了多种存储引擎(核心)，
# SHOW ENGINES：查看系统所支持的引擎类型和默认引擎，InnoDB是默认引擎。

2.5 查看表

# 查看当前数据库所有的表
show tables;

# 查看非当前数据库所有的表
show tables {from | in} db_name;

# 查看表结构
show columns {from | in} tb_name [{from | in} db_name];
或
{describe | desc} tb_name;

# 查看数据库的详细结构()原始SQL：
show create table tb_name;

2.6 重命名表

alter table 原表名 rename to 新表名;

rename table 原表名_1 to 新表名_1,原表名_2 to 新表名_2,.....;

2.7 删除表

drop table [if exists] 表1,表2,...;

2.8 修改表-添加字段

alter table tb_name add [collmn] 新字段名 数据类型 [约束条件] [first | after 已有字段名]

# [约束条件]：用于字段字段不为空，字段的默认值，主键，候选键等约束
# [FIRST | AFTER 已有字段名]：指定新增字段在表中的位置；
# FIRST表示将新添加的字段设置为表的第一个字段;
# AFTER 已有字段名表示将新添加的字段设置为数据表的最后一列；

# 示例：向表中添加一个INT型字段id，要求不能为NULL，取值唯一且自动增加，并将字段添加到表的第一个字段
alter table tb_student add column id  INT NOT NULL UNIQUE AUTO_INCREMENT FIRST；

# 示例：向表中添加一个VARCHAR(16)型字段department，要求设置其默认值为'信息学院'，并将字段添加到nation列之后
alter table tb_student add colum department varchar(16) default "信息学院" AFTER nation;

2.9 修改表-修改字段

alter table tb_name change [column] 原字段名 新字段名 数据类型 [约束条件];
# CHANGE [COLUMN]：可同时修改表中指定列的名称和数据类型。可同时添加多个CHANGE [COLUMN]，用逗号隔开。

# 示例：将表中字段birthday重命名为age，并将数据类型修改为TINYINT，允许为NULL，默认值为18
alter table tb_student change column birthday age TINYINT NULL DEFAULT 18;

ALTER TABLE tb_name ALTER [COLUMN] 字段名 {SET | DROP} DEFAULT;
# ALTER [COLUMN]：可以修改或删除表中指定列的默认值

# 示例：将表中tb_student的字段department的默认值删除
alter table tb_student alter column department drop default;

ALTER TABLE tb_name MODIFY [COLUMN] 字段名 数据类型 [约束条件] [FIRST | AFTER 已有字段名];
# MODIFY [COLUMN]：修改指定列的数据类型
# [FIRST | AFTER 已有字段名]：修改指定列在表中的位置

# 示例：将表中的字段department的数据类型修改为VARCHAR(20)，取值不能为空，并将字段移到字段studentName之后
alter table tb_student MODIFY COLUMN department VARCHAR(20) NOT NULL AFIER studentName;

2.10 删除字段

alter table tb_name drop [column] 字段名;

# 示例：删除表中id字段
alter table tb_student drop column id;

3、数据完整性约束

关系模型的完整性约束是对关系的某种约束条件。其主要是为了保障数据的正确性和一致性，防止数据库存在不符合语义的，不正确的数据。

3.1 定义实体完整性

实体完整性规则指关系的主属性不能取空值，即主键和候选键在关系中所对应的属性不能为空。

主键约束：主键是表中某一列或某些列构成的一个组合。其中，由多个列组合而成的主键又称为复合主键。

主键约束条件：

每一个表只能定义一个主键。
主键的值，也称为键值，必须能唯一标识表中的每一行记录，且不能为NULL。
复合主键不能包含不必要的多余列。
一个列名在复合主键的列表中只能出现一次。

# 示例：
CREATE TABLE tb_student(
    studentNo CHAR(10) PRIMARY KEY,         # 确定主键(方法一)
    studentName VARCHAR(20) NOT NULL,
    sex CHAR(2) NOT NULL,
    birthday DATE,
    native VARCHAR(20),
    nation VARCHAR(10) DEFAULT '汉',
    classNo CHAR(6),
    # PRIMARY KEY(studentNo)                # 确定主键(方法二)
)ENGINE=InnDB;

完整性约束的命名：命名完整性约束的方法是，在各种完整性约束的定义说明之前加上关键字CONSTRAINT和该约束的名字。

CONSTRAINT<指定的约束名>
{
    |PRIMARY KEY(主键字段列表)
    |UNIQUE(候选键字段列表)
    |FOREIGN KEY(外键字段列表) REFERENCES tb_被参照关系(主键字段列表)
    |CHECK(约束条件表达式)
};

# 示例：
CREATE TABLE tb_student(
    studentNo CHAR(10),    
    studentName VARCHAR(20) NOT NULL,
    sex CHAR(2) NOT NULL,
    birthday DATE,
    native VARCHAR(20),
    nation VARCHAR(10) DEFAULT '汉',
    classNo CHAR(6),
    CONSTRAINT PK_student PRIMARY KEY(studentNo)   
)ENGINE=InnDB;

候选键约束：候选键的值必须唯一，且不能为NULL。

# 定义在列级的完整性约束
CREATE TABLE tb_class(
    classNo CHAR(6) PRIMARY KEY,
    className VARCHAR(20) NOT NULL UNIQUE,
    department VARCHAR(30) NOT NULL,
    grade SMALLINT,
    classNum TINYINT 
)ENGINE=InnDB;

# 定义在表级的完整性约束
CREATE TABLE tb_class(
    classNo CHAR(6) PRIMARY KEY,
    className VARCHAR(20) NOT NULL,
    department VARCHAR(30) NOT NULL,
    grade SMALLINT,
    classNum TINYINT,
    CONSTRAINT UQ_class UNIQUE(className)
)ENGINE=InnDB;

mysql中PRIMARY KEY与UNIQUE之间的区别：
    一个表只能创建一个PRIMARY KEY，但可以定义多个UNIQUE.
    定义为PRIMARY KEY的列不允许有空值，但UNIQUE的字段允许。
    定义约束时，系统会自动产生索引。

3.2 定义参照完整性

外键是一个表中的一个或一组属性，外键对应另一张表的主键。主要作用是保证数据引用的完整性，一致性。外键所属的表称为参照关系，相关联的主键的表称为被参照关系。参照完整性规则定义的是外键与主键之间引用的规则，即外键的取值或者为空，或者等于被参照关系中某个主键的值。

# 以列级完整性约束定义外键
create table tb_student(
    studentNo CHAR(10),
    studentName VARCHAR(20) NOT NULL,
    sex CHAR(2) NOT NULL,
    birthday DATE,
    native VARCHAR(20),
    nation VARCHAR(10) DEFAULT '汉',
    CONSTRAINT PK_student PRIMARY KEY(studentNo)
)ENGINE=InnoDB;

# 以表级完整性约束定义外键
create table tb_student(
    studentNo CHAR(10),
    studentName VARCHAR(20) NOT NULL,
    sex CHAR(2) NOT NULL,
    birthday DATE,
    native VARCHAR(20),
    nation VARCHAR(10) DEFAULT '汉',
    classNo CHAR(6),
    CONSTRAINT PK_student PRIMARY KEY(studentNo),
    CONSTRAINT FK_student FOREIGN KEY(classNo) REFERENCES tb_class(classNo)
)ENGINE=InnoDB;

RESTRICT：限制策略，即当要删除或修改被参照表中被参照列上且在外键中出现的值时，系统拒绝对被参照表的删除或修改操作。
CASCADE：级联策略，即当被参照表中删除或修改记录中，自动删除或修改参照表中匹配的记录。
SET NULL：置空策略，即当从被参照表中删除或修改记录时，设置参照表中与之对应的外键列的值为NULL。
NO ACTION：表示不采取实施策略，即当一个相关的外键值在被参照表中时，删除或修改被参照表中键值的动作不被允许。
SET DEFAULT：默认值策略，即当从被参照表中删除或修改记录行，设置参照表中与之对应的外键列的值为默认值。

# 定义外键时定义相关的参照动作
create table tb_student(
    stuudentNo CHAR(10),
    studentName VARCHAR(20) NOT NULL，
    sex CHAR(2) NOT NULL,
    birthday DATE,
    native VARCHAR(20),
    nation VARCHAR(10) DEFAULT '汉',
    classNo CHAR(6),
    CONSTRAINT PK_student PRIMARY KEY(studentNo),
    CONSTRAINT FK_student FOREIGN KEY(classNo) REFERENCES tb_class(classNo),
    ON UPDATE RESTRICT,     # 不允许修改classNo
    ON DELETE CASCADE       # 级联删除
)ENGINE=InnoDB;

3.3 用户定义的完整性

设置非空约束：非空约束指字段的值不能为空，非空约束的定义可以使用CREATE TABLE或ALTER TABLE语句。，在列后面加上NOT NULL。
check约束：CHECK约束是在创建表(TABLE)或修改表(ALTER TABLE)的同时，根据用户的实际完整性要求来定义的。列级CHECK约束定义的是单个字段需要满足的要求。表级CHECK约束定义的是表中多个字段之间应满足的条件。

# 示例：创建表，自定义约束：每16个课时对应1学分
create table tb_course(
          courseNo CHAR(6),                                   # 课程号
          courseName varchar(20) not null,                    # 课程名   
          credit int not null,                                # 学分
          courseHour int not null,                            # 课时数
          term char(2),                                       # 开课学期
          priorCourse char(6),                                # 先修课程
          CONSTRAINT PK_course PRIMARY KEY(courseNo),    
          CONSTRAINT FK_course FOREIGN KEY(priorCourse) REFERENCES tb_course(courseNo),
          CONSTRAINT CK_course CHECK(credit=courseHour/16)    # 自定义约束：每16个课时对应1学分。
);

# 示例：创建成绩表，要求成绩取值只能在0-100之间
create table tb_score(
          studentNo char(10),                                # 学号
          courseNo char(6),                                  # 课程号
          score FLOAT check(score>=0 and score<=100) ,       # 成绩，自定义约束
          CONSTRAINT PK_score PRIMARY KEY(studentNo, courseNo),
          CONSTRAINT FK_score1 FOREING KEY(studentNo) REFERENCES tb_student(studentNo),
          CONSTRAINT FK_score2 FOREIGN KEY (courseNo) REFERENCES tb_course(courseNo),
);

4、更新完整性约束

4.1 删除外键约束

alter table 表名 drop foreing key 外键约束;

4.2 删除主键约束

drop table 表名 drop primary key;

4.3 删除候选键约束

# 如果没有给候选键命名，使用DROP INDEX子句删除的是定义候选键的字段名
alter table 表名 drop index 候选键字段名;

# 如果使用了CONSTRAINT子句给候选键命名，使用DROP INDEX子句删除的是约束名
alter table 表名 drop index 约束名;

4.4 添加主键约束

alter table 表名 add [CONSTRAINT 约束名] primary key 主键字段;
# []内容可省略

4.5 添加外键约束

alter table 表名 add [CONSTRAINT 约束名] foreing key (外键字段名) references 被参照表(主键字段);

4.6 添加候选键约束

alter table 表名 add [CONSTRAINT 约束名] UNIQUE KEY (字段名);

四、mysql数据查询

1、创建查询表

1.1 创建班级表

字段	字段名	数据类型
班级编号	classNo	字符型
班级名称	className	字符型
所属院系	department	字符型
年级	grade	数值型
班级人数	classNum	数值型

classNo	className	department	grade	classNum
AC1301	会计13-1开班	会计学院	2013	35
AC1302	会计12-2班	会计学院	2013	35
CS1401	计算机14-1班	计算机学院	2014	35
IS1301	信息系统13-1班	数值型	信息学院	NULL
IS1401	信息系统14-1班	信息学院	NULL	30

create table tb_class(
classNo char(6) primary key,
className varchar(20) not null UNIQUE,
department varchar(30) not null,
grade SMALLINT,
classNum TINYINT,
)ENGINE=InnoDB;

insert into tb_class(classNo,className,department,grade,classNum) 
value
('AC1301','会计13-1开班','会计学院','2013','35'),
('AC1302','会计12-2班','会计学院','2013','35'),
('CS1401','计算机14-1班','计算机学院','2014','35'),
('IS1301','信息系统13-1班','信息学院','2013',null),
('IS1401','信息系统14-1班','信息学院',null,'30');

1.2 创建学生表

含义	字段名	数据类型
学号	studentNo	字符型
姓名	studentName	字符型
性别	sex	字符型
出生日期	birthday	日期型
籍贯	native	字符型
民族	nation	字符型
所属班级	classNo	字符型

studentNo	studentName	sex	birthday	native	nation	classNo
2013110101	张晓勇	男	1997-12-11	山西	汉	AC1301
2013110103	王一敏	女	1996-03-25	河北	汉	AC1301
2013110201	江山	女	1996-09-17	内蒙古	锡伯	AC1302
2013110202	李明	男	1996-01-14	广西	壮	AC1302
2013310101	黄菊	女	1995-09-30	北京	汉	IS1301
2013310103	吴昊	男	1995-11-18	河北	汉	IS1301
2014210101	刘涛	男	1997-04-03	湖南	侗	CS1401
2014210102	郭志坚	男	1997-02-21	上海	汉	CS1401
2014310101	王林	男	1996-10-09	河南	汉	IS1401
2014310102	李怡然	女	1996-12-31	辽宁	汉	IS1401

create table tb_student(
studentNo char(10) not null UNIQUE,
studentName varchar(20) not null,
sex char(2),
birthday date,
native varchar(20),
nation varchar(10) default '汉',
classNo char(6),
)ENGINE=InnoDB;

insert into tb_student(studentNo,studentName,sex,birthday,native,nation,classNo)
value
('2013110101','张晓勇','男','1997-12-11','山西','汉','AC1301'),
('2013110103','王一敏','女','1996-03-25','河北','汉','AC1301'),
('2013110201','江山','女','1996-09-17','内蒙古','锡伯','AC1302'),
('2013110202','李明','男','1996-01-14','广西','壮','AC1302'),
('2013310101','黄菊','女','1995-09-30','北京','汉','IS1301'),
('2013310103','吴昊','男','1995-11-18','河北','汉','IS1301'),
('2014210101','刘涛','男','1997-04-03','湖南','侗','CS1401'),
('2014210102','郭志坚','男','1997-02-21','上海','汉','CS1401'),
('2014310101','王林','男','1996-10-09','河南','汉','IS1401'),
('2014310102','李怡然','女','1996-12-31','辽宁','汉','IS1401');

1.3 创建课程表

含义	字段名	数据类型
课程号	courseNo	字符型
课程名	courseName	字符型
学分	credit	数值型
课时数	courseHour	数值型
开课学期	term	字符型
先修课程	priorCourse	字符型

courseNo	courseName	credit	courseHour	term	priorCourse
11003	管理学	2	32	2	NULL
11005	会计学	3	48	3	NULL
21001	计算机基础	3	48	1	NULL
21002	office高级应用	3	48	2	21001
21004	程序设计	4	64	2	21001
21005	数据库	4	64	4	21004
21006	操作系统	4	64	5	21001
31001	管理信息系统	3	48	3	21004
31002	信息系统_分析与设计	2	32	4	31001
31005	项目管理	3	48	5	31001

create table tb_course(
courseNo char(6),
courseName varchar(20) not null,
credit int not null,
courseHour int not null,
term char(2),
priorCourse char(6),
constraint PK_course primary key(courseNo),
constraint FK_course foreign key(priorCourse)  references tb_course(courseNo),
constraint CK_course check(credit=courseHour/16)
)ENGINE=InnoDB;

1.4 创建成绩表

含义	字段名	数据类型
学号	studentNo	字符型
课程号	courseNo	字符型
成绩	score	数值型

studentNo	courseNo	score
2013110101	11003	90
2013110101	21001	86
2013110103	11003	89
2013110103	21001	88
2013110201	11003	78
2013110201	21001	92
2013110202	11003	82
2013110202	21001	85
2013310101	21004	83
2013310101	31002	68
2013310103	21004	80
2013310103	31002	76
2014210101	21004	93
2014210101	21002	89
2014210102	21002	95
2014210102	21004	88
2014310101	21001	79
2014310101	21004	80
2014310102	21001	91
2014310102	21004	87

create table tb_score(
studentNo char(10),
courseNo char(6),
score float check(score>=0 and score<=100),
constraint PK_score primary key(studentNo,course),
constraint FK_score1 foreign key(studentNo) references tb_student(studentNo),
constraint FK_score2 foreign key(courseNo) references tb_course(courseNo)
)ENGINE=InnoDB;

insert into tb_score(studentNo,courseNo,score) value(2013110101,11003,90);
insert into tb_score(studentNo,courseNo,score) value(2013110101,21001,86);
insert into tb_score(studentNo,courseNo,score) value(2013110103,11003,89);
insert into tb_score(studentNo,courseNo,score) value(2013110103,21001,88);
insert into tb_score(studentNo,courseNo,score) value(2013110201,11003,78);
insert into tb_score(studentNo,courseNo,score) value(2013110201,21001,92);
insert into tb_score(studentNo,courseNo,score) value(2013110202,11003,82);
insert into tb_score(studentNo,courseNo,score) value(2013110202,21001,85);
insert into tb_score(studentNo,courseNo,score) value(2013310101,21004,83);
insert into tb_score(studentNo,courseNo,score) value(2013310101,31002,68);
insert into tb_score(studentNo,courseNo,score) value(2013310103,21004,80);
insert into tb_score(studentNo,courseNo,score) value(2013310103,31002,76);
insert into tb_score(studentNo,courseNo,score) value(2014210101,21004,93);
insert into tb_score(studentNo,courseNo,score) value(2014210101,21004,89);
insert into tb_score(studentNo,courseNo,score) value(2014210102,21002,95);
insert into tb_score(studentNo,courseNo,score) value(2014210102,21004,88);
insert into tb_score(studentNo,courseNo,score) value(2014310101,21001,79);
insert into tb_score(studentNo,courseNo,score) value(2014310101,21004,80);
insert into tb_score(studentNo,courseNo,score) value(2014310102,21001,91);
insert into tb_score(studentNo,courseNo,score) value(2014310102,21004,87);

2、单表查询

2.1 选择字段

(1).查询指定字段

# 查询所有班级的班级编号、班级名称和所属学院。
select classNo,className,department from tb_class;

# 从班级表tb_class中查询出所有的学院名称。
select department from tb_class;
select all department from tb_class;

# 从班级表tb_class中查询出所有的学院名称(去除重复的值)。
select DISTINCT department from tb_class;
select DISTINCTRON department from tb_class;

(2).查询所有字段

# 查询全体学生的详细信息。
select * from tb_student;
select studentNo,studentName,sex,birthday,native,nation,classNo from tb_student;

(3).查询经过计算的值

# 查询全体学生的姓名、性别和年龄。
select studentName,sex,YEAR(now())-YEAR(birthday) as age from tb_student;

# YEAR(NW())-YEAR(birthday)：计算表达式，用于计算学生的年龄。
# NOW()：返回当前日期和时间。也可以使用CURDATE()函数返回当前日期。
# YEAR()：返回指定日期对应的年份。

(4).定义字段的别名

表达式：字段名 [as] 字段别名
# 查询全体学生的姓名、性别和年龄，要求给目标表达式取别名
select studentName as '姓名',sex as '性别',YEAR(NOW())-YEAR(birthday) as '年龄' from tb_student;

2.2 选择指定记录查询

where子句常用的查询条件：

查询条件	操作符
比较	=，<>，!=，<，<=，!<，>，>=，!>
确定范围	between and，not between and
确定集合	in，not in
字符匹配	like，not like
空值	is null，is not null
多重条件	and，or

(1).比较运算符

# 查询课时大于等于48学时的课程名称及学分。
select courseName,credit,courseHour from tb_course where courseHour>=48;
select courseName,credit,courseHour from tb_course where not courseHour<48;

# 查询少数民族学生的姓名、性别、籍贯和民族。
select studentName,sex,native,nation from tb_student where nation != '汉';
select studentName,sex,native,nation from tb_student where nation <> '汉';
select studentName,sex,native,nation from tb_student where not nation='汉';

(2).范围查询：between...end...

# 查询出生日期在1997-01-01和1997-12-31之间的学生姓名，性别和出生日期。
select studentName,sex,birthday from tb_student where birthday between '1997-01-01' and '1997-12-31';

# 查询出生日期不在1997-01-01和1997-12-31之间的学生姓名，性别和出生日期。
select studentName,sex,birthday from tb_student where birthday not between '1997-01-01' and '1997-12-31';

(3).带in关键字的集合查询

# 查询籍贯是北京，天津和上海的学生信息。
select * from tb_student where native in ('北京','天津','上海');

# 查询籍贯不是北京，天津和上海的学生信息。
select * from tb_student where native not in ('北京','天津','上海');

(4).带like关键字的字符串匹配查询

# 查询学号为2013110201的学生的详细情况
select * from tb_student where studentNo like '2013110201';

# 查询所有姓'王'的学生的学号，姓名和班号。
select studentNo,studentName,classNo from tb_student where studentName like '王%';

# 查询所有不姓'王'的学生的学号，姓名和班号。
select studentNo,studentName,classNo from tb_student where studentName not like '王%';

# 查询姓名中包含'林'字的学生学号，姓名和班号。
select studentNo,studentName,classNo from tb_student where studentName like '%林%';

# 查询姓'王'并且姓名长度为三个中文字的学生的学号，姓名和班号。
select studentNo,studentName,classNo from tb_student where studentName like '王__';

# 查询课程号名称中含有下划线'_'的课程信息
select * from tb_course where courseName like '%#_%'ESCAPE'#';
# 关键字ESCAPE指定一个转义字符'#'。使'#'后面的'_'不再是通配符。

(5).使用正则表达式查询

# 语法格式：[NOT] [REGEXP | RLIKE] <正则表达式>

选项	说明	例子	匹配值
<字符串>	匹配包含指定字符串的文本	'fa'	fan，afa，faad
[ ]	匹配[ ]中的任何一个字符	'[ab]'	bay，big，app
[ ^ ]	匹配不在[ ]中的任何一个字符	'[ ^ab]'	desk，cool，six
^	匹配文本的开始字符	'^b'	bed，bridge，book
$	匹配文本的结尾字符	'er$'	driver，worker，farmer
.	匹配任意单个字符	b.t	better，bit，bite
*	匹配0个或多个*前面指定的字符	'f*n'	fn，fan，begin
+	匹配+前面的字符1次或多次	'ba+'	bat，baa，battle，bala

# 查询课程名称中带有中文'系统'的课程信息
select * fromtb_course where courseName REGEXP '系统';

# 查询课程名称中含有'管理'，'信息'或'系统'中文字符的所有课程信息
select * from tb_course where courseName REGEXP '管理|信息|系统';

(6).带is null关键字的空值查询

# 查询缺少先行课的课程信息
select * from tb_course where priorCourse is null;

# 查询所有先行课的课程信息
select * from tb_course where priorCourse is not null;

(7).带and或or的多条件查询

# 查询学分大于等于3且学时数大于32的课程名称，学分和学时数。
select courseName,credit,courseHour from tb_course where credit>=3 and courseHour>32;

# 查询籍贯是北京或上海的学生的姓名，籍贯和民族
select studentName,native,nation from tb_student where native='北京' or native='上海';

# 查询籍贯是北京或湖南的少数民族男生的姓名，籍贯和民族
select studentName,native,nation from tb_student where (native='北京' or native='湖南') and nation!='汉' and sex='男';

2.3 对查询结果进行排序

关键字ASC表示按升序排列，再按，关键字desc表示按降序排列。默认使用ASC

# 查询学生的姓名，籍贯和民族，并将查询结果按姓名升序排列
select studentName,native,nation from tb_student order by studentName;

# 查询学生选课成绩大于85分的学号，课程号和成绩信息，并将查询结果先按学号升序排列，再按成绩降序排列
select * from tb_score where score>85 order by studentNo,score desc;

2.4 限制查询结果的数量

# 语法格式：LIMIT [位置偏移量,] 行数

# 查询成绩排名第3至第5的学生学号，课程号和成绩
select studentNo,courseNo,score from tb_score order by score DESC limit 2,3;
select studentNo,courseNo,score from tb_score order by score DESC limit 3 offset 2;
(mysql5.0，使用：limit 行数 offset 位置偏移量)

3、分组聚合查询

聚合函数是通过将聚合函数添加到一个带有GROUP BY分组子句的select语句中实现的。

3.1 使用聚合函数查询

mysql中常用的聚合函数：

函数名	说明
count()	统计数据表中的记录数
count()	统计数据表的一列中值的个数
max()	求数据表的一列值中的最大值
min()	求数据表的一列值中的最小值
sum()	计算数据表的一列中值的总和
avg()	计算数据表的一列中值的平均值

函数中加入：DISTINC：表示在计算时取消指定列中的重复值。
除count(*)外，其余聚合函数都会忽略空值。

# 查询学生总人数。
select count(*) from tb_student;
select count(all *) from tb_student;

# 查询选修了课程的学生总人数。
select count(DISTINCT studentNo) from tb_score;

# 查询选修课程编号为‘21001’的学生平均数。
select avg(score) from tb_score where courseNo='21001';

# 计算选修课程编号为‘21001’的学生最高分。
select max(score) from tb_score where courseNo='21001';

3.2 分组聚合查询

# 基本语法：[GROUP BY 字段列表] [HAVING <条件表达式>]

# 查询各个课程号以及相应的选课人数。
select courseNo,count(studentNo) from tb_score GROUP BY courseNo;

# 查询每个学生的选课门数，平均分和最高分。
select courseNo,count(*) 选课门数,avg(score) 平均分,max(score) 最高分 from tb_score GROUP BY studentNo;

# 查询平均分在80分以上的每个同学的选课门数，平均分和最高分。
select studentNo,count(*) 选课门数,avg(score) 平均分,max(score) 最高分 from tb_score GROUP BY studentNo HAVING avg(score)>=80;

# 查询有2门以上(含2门)课程成绩大于88分的学生学号及(88分以上的)课程数。
select studentNo,count(*) 课程数 from tb_score where score>88 GROUP BY studentNo count(*)>=2;

# 查询所有学生选课的平均成绩，但只有当平均成绩大于80的情况下才输出。
select avg(score) 平均分 from tb_score HAVING avg(score)>=80;

4、连接查询

4.1 交叉连接

交叉连接又称为笛卡尔积，即将一张表的每一行与另一张表的每一行连接起来，返回两张表的每一行相连接后所有可能的搭配结果，但是其连接的结果会产生一些没有意义的记录。所以这种查询实际很少使用。

# 语法结构：
select * from 表1 cross json 表2;
select * from 表1,表2;

# 查询学生表与成绩表的交叉连接。
select * from tb_student cross json tb_score;
select * from tb_student,tb_score;

交叉连接返回的查询结果集的记录行数等于其所连接的两张表记录行数的乘积。

4.2 内连接

内连接通过在查询中设置连接条件来移除交叉连接查询结果集中某些数据行。其目的是为了清除交叉连接中某些没有意义的数据行。

# 语法结构_1：使用INNER JSOIN的显式语法结构
select 目标表达式1,目标表达式2,.. from table [INNER JOIN] table2 ON 连接条件 [where 过滤条件];

# 语法结构_2：使用where子句定义连接条件的隐式语法结构：
select 目标表达式_1，目标表达式_2.... from table1，table2 where 连接条件 [AND 过滤条件];

(1).等值与非等值连接

# 语法结构：[<表名1>.]<字段名1> <比较运算符> [<表名2>.]<字段名2>

# 查询每个学生选修课程的情况。
select tb_studnet.*,tb_score.* from tb_student,tb_score where tb_student.studentNo = tb_score.studentNo;

select tb_student.*,tb_score.* from tb_student INNER JSON tb_score ON tb_student.studentNo = tb_score.studentNo;

# 查询会计学院全体同学的学号，姓名，籍贯，班级编号和所在班级名称。
select studentNo,studentName,native,tb_student.classNo,className from tb_student,tb_class where tb_student.classNo = tb_class.classNo and department='会计学院';

select studentNo,studentName,native,tb_student.classNo,className from tb_student,tb_class where tb_student.classNo = tb_class.classNo and department='会计学院';
# 'tb_student.classNo=tb_class.classNo'为连接条件，department='会计学院'为筛选条件。

# 查询选修了课程名称为'程序设计'的学生学号，姓名和成绩。
select a.studentNo,studentName,score from tb_student as a,tb_course as b,tb_score as c where a.studentNo=c.studentNo and b.courseNo = c.courseNo and courseName = '程序设计';

select a.studentNo,studentName,score from tb_student as a JOIN tb_course as b JOIN tb_score as c on a.studentNo = c.studentNo and b.courseNo = c.courseNo where courseName='程序设计';

(2).自连接
若某个表与自身进行连接，称为自表连接或自身连接，简称自连接。

# 查询与'数据库'这门课学分相同的课程信息。
select a.* from tb_course as a,tb_course as b where a.credit = b.credit and b.courseName='数据库' and a.courseName!='数据库';

select a.* from tb_course as a join tb_course as b on a.credit = b.credit where b.courseName='数据库' and a.courseName!='数据库';

(3).自然连接
自然连接只有当连接字段在两张表中的字段名都相同时才可以使用，否则返回的是笛卡尔积的结果集。

# 用自然连接查询每个学生及其选修课程的情况，要求显示学生学号，姓名，选修的课程号和成绩。
select a.studentNo,studentName,courseNo,score from tb_student as a NATURAL JOIN tb_score b;

4.3 外连接

外连接将连接的两张表分为基表和参考表，然后再以基表为依据返回满足和不满足连接条件的记录。
(1).左外连接

以左边表为基表，返回基表的所有记录。并且与参考表中的记录进行匹配。
如果左表的某些记录在右表中没有匹配的记录，则右表对应的字段值均被设置为空值NULL。

# 使用左外连接查询所有学生及其选修课程的情况，包括没有选修课程的学生，要求显示学号，姓名。性别，班号，选修的课程号和成绩。
select a.studentNo,studentName,sex,classNo,courseNo,score from tb_student as a left outer join tb_score as b on a.studentNo=b.studentNo;

(2).右外连接

以右表为基表，返回基表的所有记录。并且与参考表中的记录进行匹配。
如果右表的某些记录在左表中没有匹配的记录，则左表对应的字段值均被设置为空值NULL。

# 使用右外连接查询所有学生及其选修课程的情况，包括没有选修课程的学生，要求显示学号，姓名，性别，班号，选修的课程号和成绩。
select b.studentNo,studentName,sex,classNo,courseNo,score,from tb_score as a right outer join tb_student as b on a.studentNo=b.studentNo;

5、子查询

子查询也称为嵌套查询，是将一个查询语句嵌套在另一个查询语句的where子句或HAVING短语中，前者被称为内层查询或子查询，后者被称为外层查询或父查询。

5.1 带in关键字的子查询

# 查询选修了课程的学生姓名。
select studentName from tb_student where tb_student.studentNo in (select DISTINCT studentNo from tb_score);

select DISTINCT studentName from tb_student.studentNo = tb_score.studentNo;(连接查询)

5.2 带比较运算符的子查询 带比较运算符的子查询指父查询与子查询之间用比较运算符进行连接。当用户能够确切知道内层查询返回的是单值时，可以用比较运算符构造子查询。

# 查询班级'计算机14-1班'所有学生的学号，姓名。
select studentNo,studentName from tb_student where classNo=(select classNo from tb_class where className='计算机14-1班');

# 查询与'李明'在同一个班学习的学生学号，姓名和班号
select studentNo,studentName,classNo from tb_student where classNo=(select classNo from tb_student where studentName='李明') and studentName!='李明';

# 查询男生中比某个女生出生年份晚的学生姓名和出生年份。
select studentName,YEAR(birthday) from tb_student where sex='男 and YEAR(birthday) > any(select YEAR(birthday) from tb_student where sex='女');

# 查询男生中比所有女生出生年份晚的学生姓名和出生年份。
select studentName,YEAR(birthday) from tb_student where sex='男' and YEAR(birthday) > all(select YEAR(birthday) from tb_student where sex='女');

5.3 带exists关键字的子查询
使用关键字exists构造子查询时，系统对子查询进行运算以判断它是否返回结果集，如果子查询的结果集不为空，则EXISTS返回的结果为true，此时外层查询语句将进行查询；如果子查询的结果集为空，则EXISTS返回的结果为false，此时外层查询语句将不进行查询。

# 查询选修了课程号为'31002'的学生姓名
select studentName from tb_student as a where exists (select * from tb_score as b where a.studentNo = b.studentNo and courseNo='31002');

# 查询没有选修课程号为'31002'的学生姓名
select studentName from tb_student as a where not exists (select * from tb_score as b where a.studentNo = b.studentNo and courseNo='31002');

# 查询选修了全部课程的学生姓名
select studentName from tb_student as a where not exists (select * from tb_course as c where not exists (select * from tb_score where studentNo=a.studentNo and courseNo=c.courseNo));

# 将关键字NOT EXISTS成对使用，达到用双重否定表示肯定的效果，即将一个NOT EXISTS子查询嵌套在另一个NOT EXISTS子查询的where子句中。

6、联合查询

使用UNION关键字将多个select语句的结果组合到一个结果集中，这种查询方式称为联合查询。合并时，多个select子句中对应的字段数和数据类型必须相同。

# 语法格式：
    select - from - where
    UNION [ALL]
    select - from -where
    [...UNION [ALL]
    select - from -where]

# 关键字all的作用是不去掉重复的记录，也不对结果进行排序。

# 使用UNION查询选修了'管理学'或'计算机基础'的学生学号
select studentNo from tb_score,tb_course where tb_score.courseNo = tb_course.courseNo and courseName='管理学' UNION select studentNo from tb_score,tb_course where tb_score.courseNo=tb_course.courseNo and courseName='计算机基础';
等价于：
select DISTINCT studentNo from tb_score,tb_course where tb_score.courseNo=tb_course.courseNo and (courseName='管理学' or courseName='计算机基础');

# 使用UNION ALL查询选修了'管理学'或'计算机基础'的学生学号
select studentNo from tb_score,tb_course where tb_score.courseNo = tb_course.courseNo and courseName='管理学' UNION all select studentNo from tb_score,tb_course where tb_score.courseNo=tb_course.courseNo and courseName='计算机基础';
等价于：
select studentNo from tb_score,tb_course where tb_score.courseNo=tb_course.courseNo and (courseName='管理学' or courseName='计算机基础');

-UNION语句必须由两条及以上的select语句组成，且彼此之间使用关键字UNION分隔。
-UNION语句中的每个SELECT子句必须包含相同的列，表达式或聚合函数。
-每个select子句中对应的目标列的数据类型必须兼容。
-第一个select子句中的目标列名称会被作为UNION语句结果集的列名称。
-联合查询中只能使用一条order by子句或limit子句，且它们必须置于最后一条select语句之后。

# 查询选修了'计算机基础'和'管理学'的学生学号。
select studentNo from tb_score,tb_course where tb_score.courseNo=tb_course.courseNo and courseName='计算机基础' and studentNo in (select studentNo from tb_score,tb_course where tb_score.courseNo=tb_course.courseNo and courseName='管理学');

# 查询选修了'计算机基础'但没有选修'管理学'的学生学号
select studentNo from tb_score,tb_course where tb_score.courseNo=tb_course.courseNo and courseName='计算机基础' and studentNo not in (select studentNo from tb_score,tb_course where tb_score.courseNo=tb_course.courseNo and courseName='管理学');