C++ MySql

c++ 数据库基础 MySQL

数据库概述

数据管理技术的发展
人工管理阶段: 20世纪50年代中期
文件系统阶段: 20世纪50年代末~60年代中
缺点:
数据冗余
数据不一致
数据联系若

数据库阶段: 20世纪60年代末~至今
三大标志性事件:
1968年,IBM 推出基于层次模型的数据库系统 IMS
1969年,DBTG 发布了一系列报告
数据库和数据库的环境的标准的规范
1970年,IBM 研究院 E.F.Codd发表论文,首次提出关系模型的概念

特点:
采用复杂的结构化的数据模型
最低的冗余度
较高的数据完整性
多个方面的数据库控制功能:
并发控制
安全性
数据库的恢复
系统更加灵活

术语

数据(Data): 数据库存储的基本对象
数据库(Database): 是长期存储在计算机内、有组织、可共享的大量数据的集合
数据库管理系统(DBMS):介于用户和操作系统之间的一层数据库管理软件 科学的组织和存储数据、高效的获取和检索数据
数据库管理员(DBA)

主流的关系型数据库产品

层次、网状、关系型、非关系型
1.3.1 Oracle
1978年 Oracle 1版本
1.3.2. Microsoft SQL Server
1.3.3 DB2
IBM
1.3.4 MySQL
瑞典的MySQL AB
2008年被sun公司收购
2009年sun公司被Oracle收购

SQL语言

SQL(Structured Query Language):结构化查询语言
SQL分为：
数据查询语句(DQL)： select
用来检索数据库中的数据。使用最广泛、语法最复杂灵活

数据操作语句(DML): insert update delete
用来改变数据库表中的数据

数据定义语句(DDL): create alter drop
用来创建、修改和删除数据库对象

事务控制语句(TCL): commit rollback savepoint
用来确保数据的一致性

数据控制语句(DCL): create user、grant、revoke
用来创建用户、权限的授予和回收等。

MySQL的特点

1. 性能高效
2. 跨平台支持
3. 简单易用
4. 开源
5. 支持多用户

启动和停止服务

1. 启动
   service mysql start

2. 重启
   service mysql restart

3. 停止
   service mysql stop

连接MySQL

1. 语法

    mysql [ -h host ]  [-u user] [-p[passwd]] [db] [-P port]

配置【1】：mysql.cnf
character-set-server = utf8

或配置【2】 conf.d/mysql.cnf
[mysql]
default-character-set = utf8

验证：登录数据库
show variables like ‘%char%’ ;

客户端工具
其中：
host: 要连接的服务器的主机名或ip地址 本机： 可以省略 或localhost 127.0.0.1
user: 登录名
passwd: 密码
db: 数据库名
port: 端口号 默认3306

2. 示例
   mysql -h 10.10.5.2 -u root -p -P 3307
   mysql -u root -p

MySQL数据库管理

创建数据库

1. 语法
   create database 数据库名;
2. 示例
   – 创建"选课系统"数据库
   create database choose;

查看数据库

1. 查看所有数据库的列表
   show databases;

2. 显示指定数据库结构
   – 语法
   show create database 数据库名;
   – 示例
   show create database choose;

选择当前数据库

– 语法
use 数据库名;
– 示例
use choose;

删除数据库

– 语法
drop database 数据库名;
– 示例
drop database choose;

MySQL表管理

创建表

– 语法
create table 表名(
字段名 数据类型,
…
字段名 数据类型
);

示例
– 创建数据库
create database choose;

– 选择当前数据库
use choose;

– 创建表
create table stu_info( ID char(10), Name char(10), Score int );

查看表

1. 查看当前数据库中表的列表
   show tables;

2. 查看指定表的详细信息
   – 语法
   show create table 表名;
   – 示例
   show create table stu_info\G – \G表示纵向显示结果

3. 查看指定表的表结构
   – 语法
   desc[ribe] 表名;
   – 示例
   desc stu_info;

删除表

– 语法
drop table 表名;
– 示例
drop table stu_info;

单行注释: – (后面必须有一个空格)
#
多行注释: /* */

MySQL表结构的管理

MySQL数据类型

数值类型

整数

tinyint 1字节
smallint 2字节
mediumint 3字节
int 4字节
bigint 8字节
如果使用无符号整数时，在数据类型后加unsigned
比如:
age tinyint unsigned

小数

浮点数：
float 4字节
double 8字节
精确小数：
decimal(len,p)
比如：
salary decimal(9,2)

字符串

char(n) 定长字符串 n最大为255
varchar(n) 变长字符串 n的取值和字符集有关

日期和时间类型

date 3字节 ‘YYYY-MM-DD’ ‘1000-01-01’~‘9999-12-31’
time 3字节 ‘HH:MM:SS’ ‘-838:59:59’~‘838:59:59’
datetime 8字节 ‘YYYY-MM-DD HH:MM:SS’ ‘1000-01-01 00:00:00’~‘9999-12-31 23:59:59’

创建表设置约束

语法
create table 表名(
字段名 数据类型[ 约束条件],
…
字段名 数据类型[ 约束条件]
[,其他约束条件]
)[其他选项(引擎、字符集)];

约束类型
1) 主键约束 primary key
主键字段的值不允许为空，不允许重复
一个表中只能有一个主键

2) 唯一约束 unique
不允许重复，允许为空

3) 非空约束 not null

4) 默认约束 default

5) 检查约束 check – 目前MySQL不支持 例：age int check(age>=18 and age<=60)

6) 外键约束 foreign key
references

约束的案例

主键约束
– 创建学生表，学号设置为主键
create table student( stu_no char(11) primary key, stu_name char(10) );

– 测试
– insert语句
insert into 表名[(字段列表)] values(值列表);
insert into student values(‘1’,‘张三’);

#错误 主键字段的值不允许重复
insert into student values(‘1’,‘张三’);

#错误 主键字段的值不允许为空
insert into student values(null,‘张三’);
select * from student;

#错误 一个表中只能有一个主键
create table exam( exam_time date primary key, exam_address char(10) primary key, course_name char(20) );

– 复合主键(组合主键)
create table exam(exam_time date, exam_address char(10), course_name char(20), primary key(exam_time,exam_address) );

– 测试
insert into exam values(‘2019-12-16’,‘一教’,‘高数’);
insert into exam values(‘2019-12-16’,‘二教’,‘概率’);
insert into exam values(‘2019-12-17’,‘一教’,‘英语’);
#错误
insert into exam values(‘2019-12-16’,‘一教’,‘高数’);

唯一约束
– 创建学生表，身份证号设置为唯一
drop table student;
create table student( stu_no char(11) primary key, stu_name char(10), uid char(18) unique );

测试
insert into student values(‘1’,‘张三’,‘12345’);
#错误 唯一约束字段的值不允许重复的
insert into student values(‘2’,‘李四’,‘12345’);
– 唯一字段的值允许为空，且可以有多个
insert into student values(‘2’,‘李四’,null);
insert into student values(‘3’,‘王五’,null);

非空约束
– 创建学生表，设置姓名为非空
drop table student;
create table student( stu_no char(11) primary key, stu_name char(10) not null );

– 测试
insert into student values(‘1’,‘张三’);
insert into student values(‘2’,null);

一个字段添加多个约束，顺序任意
stu_name char(10) not null unique
或
stu_name char(10) unique not null

默认约束
– 创建学生表，班级默认为’csd1909’
drop table student;
create table student( stu_no char(11) primary key, stu_name char(10) not null, classname char(20) default ‘csd1909’ );

– 示例
– 使用默认值
insert into student values(‘1’,‘张三’,default);
insert into student(stu_no,stuname) values(‘2’,‘李四’);

– 不使用默认值
insert into student values(‘3’,‘张三’,‘csd1908’);
insert into student values(‘4’,‘赵六’,null);

外键约束
外键约束用于定义表与表之间的关系
主表（父表）：提供数据的表
从表（子表）：外键所在的表
外键字段的值要么来自于主表的对应字段，要么为null

语法
constraint 约束名 foreign key(从表外键字段名) references 主表(字段);

创建表，添加外键约束
1)先创建主表，然后再创建从表,同时外键字段引用的主表中的字段必须是唯一性字段，一般选择主键
创建班级表
create table classes( class_no int primary key, class_name char(15) );

创建学生表
create table student( stu_no char(11) primary key, stu_name char(10) not null, c_no int, constraint classes_student_fk foreign key(c_no) references classes(class_no) );

2. 添加测试数据
   insert into student values(‘1’,‘张三’,null); #错误 违反了外键约束 主表classes中不存在class_no=1的数据
   insert into student values(‘2’,‘李四’,1);

向班级表中插入测试数据
insert into classes values(1,'2018自动化1班');
insert into student values('2','李四',1);

3. 删除表
   先删除从表，再删除主表
   drop table student;
   drop table classes;

创建"选课系统"数据库

数据名：choose
五个表：teacher 、course、choose、student、classes

教师表： teacher

描述	字段名	数据类型	约束
教师工号	teacher_no	char(10)	主键(primary key)
教师姓名	teacher_name	char(10)	非空
联系方式	teacher_contack	char(20)	非空

课程表： course

描述	字段名	数据类型	约束
课程编号	course_no	int	主键、自增长
课程名	course_name	char(10)	非空
上限人数	up_limit	int	默认 60
描述信息	description	varchar(100)	非空
状态	status	char(6)	默认’未审核’
授课教师工号	teacher_no	char(10)	非空 、唯一、外键(引用teacher表中的teacher_no)

选课表： choose

描述	字段名	数据类型	约束
选课编号	choose_no	int	主键、自增长
学号	student_no	char(11)	非空、外键(引用student 表中的student_no)
课程号	course_no	int	非空、外键(引用course表中的course_no)
选课时间	choose_time	datatime	非空
成绩	score	tinyint unsigned

学生表：student

描述	字段名	数据类型	约束
学号	student_no	char(11)	主键
姓名	student_name	char(10)	非空
联系方式	student_contact	char(20)	非空
班号	class_no	int	外键(引用班级表classes中class_no)

班级表：classes

描述	字段名	数据类型	约束
班号	classes_no	int	主键、自增长
班级名称	class_name	char(20)	非空、唯一
所属院系	department_name	char(20)	非空

关联

代码实现：

#创建库：
create database choose default charset utf8; 
  
#创建教师表
create table teacher(teacher_no char(10) primary key,
teacher_name char(10) not null,
teacher_contact char(20) not null);

#创建班级表
create table classes(class_no int primary key auto_increment,
class_name char(20) not null unique,
department_name char(20) not null);

#创建学生表
create table student(student_no char(11) primary key,
student_name char(10) not null,
student_contact char(20) not null,
class_no int,
constraint class_student_fk foreign key(class_no) references classes(class_no));

#创建课程表
create table course(course_no int primary key auto_increment,
course_name char(10) not null,
up_limit int default 60,description varchar(100) not null,
status char(6) default '末审核',
teacher_no char(10) not null unique,
constraint teacher_course_fk foreign key(teacher_no) references teacher(teacher_no));

#创建选课表
create table choose(choose_no int primary key auto_increment,
student_no char(11) not null,
course_no int not null,
choose_time datetime not null,score tinyint unsigned,
constraint student_choose_fk foreign key(student_no) references student(student_no),
constraint course_choose_fk foreign key(course_no) references course(course_no));

创建表设置自增长字段

1. 语法
   字段名 数据类型 auto_increment
   自增长字段的数据类型必须是整型，必须设置为主键
   auto_increment primary key
   或
   primary key auto_increment

2. 示例
   – 创建班级表，班级编号设置为自增长
   create table classes(class_no int auto_increment primary key, class_name char(15) unique not null );

测试
insert into classes(class_name) values('2018自动化1班');
insert into classes values(null,'2018计算机1班');

复制表结构

方式1： create table 新表名 like 源表名;
– 复制学生表（按照student表结构创建新的学生表stu1）
create table stu1 like student;

注意：
1. 复制表结构，不包括源表中的数据
2. 新表中不包含源表中的外键约束

测试
insert into stu1 values(‘1’,‘张三’,‘12345’,1); #错误 违反了主键约束
insert into stu1 values(‘1’,‘张三’,‘12345’,1); #错误 违反了非空约束
insert into stu1 values(‘2’,‘张三’,null,1);

方式2：create table 新表名 select 语句;
示例
create table stu2 select * from stu1;

注意：
1. 使用结果集创建新表，新表中不包含结果集中的数据
2. 新表中不包含主键、唯一、外键等约束，但是不包含非空约束

测试
insert into stu1 values(‘1’,‘李四’,‘2344354’,1);

DML语句

向表中写入数据

insert语句
1) 作用
向指定表中插入一行数据

2) 语法
insert into 表名[(字段列表)] values(值列表);
insert into 表名[(字段列表)] values(值列表1),(值列表2),… ;
insert into 表名[(字段列表)] select 字段列表2 from 表名 … ;

其中，值列表和字段列表的数量、类型、顺序必须一致
缺省字段列表时，相当于提供包含表结构中所有字段的字段列表

3) 示例
向指定表中的所有字段插入数据
向教师表teacher中插入如下数据：
±----------------------------------------------+
teacher_no teacher_name teacher_contact
±----------------------------------------------+
‘001’ ‘李老师’ ‘11000000000’
‘002’ ‘王老师’ ‘12000000000’
‘003’ ‘孙老师’ ‘13000000000’
±----------------------------------------------+

SQL语句如下：
insert into teacher (teacher_no,teacher_name,teacher_contact) values(‘001’,‘李老师’,‘11000000000’);
insert into teacher (teacher_name,teacher_no,teacher_contact)values(‘王老师’,‘002’,‘12000000000’);
insert into teacher values(‘003’,‘孙老师’,‘13000000000’);

在指定表的部分字段插入数据(自增长)
向班级表classes中插入如下测试数据
±----------------------------------------+
class_name department_name
±----------------------------------------+
‘2018机械自动化1班’ ‘机械学院’
‘2018机械自动化2班’ ‘机械学院’
‘2018电子信息1班’ ‘信息学院’
±----------------------------------------+

sql语句如下：
insert into classes(class_name,department_name) values(‘2018机械自动化1班’,‘机械学院’);
insert into classes values(null,‘2018机械自动化2班’,‘机械学院’);
insert into classes values(3,‘2018电子信息1班’,‘信息学院’);

表中字段有默认值
向课程表course中插入如下数据：
±----------------------------------------------------+
course_name up_limit description status teacher_no
±----------------------------------------------------+
‘音乐欣赏’ 60 ‘暂无’ ‘已审核’ ‘001’
‘c语言’ 150 ‘暂无’ ‘已审核’ ‘002’
‘Java语言’ 230 ‘暂无’ ‘已审核’ ‘003’
±----------------------------------------------------+

SQL语句如下：
使用默认值
insert into course values(null,‘音乐欣赏’,default,‘暂无’,‘已审核’,‘001’);
或
insert into course(course_name,description,status,teacher_no) values(‘音乐欣赏’,‘暂无’,‘已审核’,‘001’);

不使用默认值
insert into course values(null,‘c语言’,150,‘暂无’,‘已审核’,‘002’);
insert into course values(null,‘Java语言’,230,‘暂无’,‘已审核’,‘003’);

insert into course values(null,‘html’,150,‘暂无’,default,‘001’); #错误 违反了唯一约束
insert into course values(null,‘html’,150,‘暂无’,default,‘004’); #错误 违反了外键约束

向教师表中插入一行数据
insert into teacher values(‘004’,‘宋老师’,‘14000000000’);
insert into course values(null,‘html’,150,‘暂无’,default,‘004’);

向表中批量写入多行数据insert into

1) 语法
insert into 表名[(字段列表)] values
(值列表1),
(值列表2),
…
(值列表n);

2) 示例
向学生表student中插入如下数据:
±----------------------------------------------------+
student_no student_name student_contact class_no
±----------------------------------------------------+
‘2018001’ ‘张三’ ‘15000000000’ 1
‘2018002’ ‘李四’ ‘16000000000’ 1
‘2018003’ ‘王五’ ‘17000000000’ 3
‘2018004’ ‘马六’ ‘18000000000’ 2
‘2018005’ ‘田七’ ‘19000000000’ 2
±----------------------------------------------------+

SQL语句如下：
insert into student values
(‘2018001’,‘张三’,‘15000000000’,1),
(‘2018002’,‘李四’,‘16000000000’,1),
(‘2018003’,‘王五’,‘17000000000’,3),
(‘2018004’,‘马六’,‘18000000000’,2),
(‘2018005’,‘田七’,‘19000000000’,2);

使用insert…select语句插入结果集
1)语法
insert into 目标表名[(字段列表1)]
select 字段列表2 from 源表名 …;
其中，字段列表1和字段列表2的字段的数量和类型必须匹配

2) 示例
将学生表student中的数据一次性插入stu1中
insert into stu1 select * from student;

update语句

作用
根据条件修改表中符合条件的数据行

语法
update 表名 set 字段名=新值[,字段名=新值,… ] [ where 条件表达式 ];

示例
创建测试表
create table exam(stu_no int primary key auto_increment,exam_score int unsigned,regular_score int unsigned);

插入测试数据
insert into exam values(null,78,80);
insert into exam values(null,83,85);
insert into exam values(null,98,90);
insert into exam values(null,26,80);
insert into exam values(null,52,90);
insert into exam values(null,58,80);
insert into exam values(null,45,70);

更新
1) 每人加5分(没有where子句的update语句)
update exam set exam_score = exam_score+5;

2) 成绩超过100，改为100(带有where子句的update语句)
update exam set exam_score=100 where exam_score>100;

3) 成绩处于[55,59]之间，改为60(多个条件)
update exam set exam_score=60 where exam_score>=55 and exam_score<=59;

4) 更改多个字段
update exam set exam_score=26,regular_score=60 where stu_no=4;

#错误 违反了外键约束 classes中不存在class_no=4的数据
update student set class_no=4 where student_name=‘张三’;

#错误 违反了外键约束 class_no=1的数据被从表引用
update classes set class_no=4 where class_no=1;

delete语句

作用
根据条件删除表中符合条件的数据行

语法
delete from 表名 [where 条件表达式];
truncate table 表名； --截断表

示例
删除exam表中不及格的信息
delete from exam where exam_score<60;
delete from exam where stu_no =7;

删除exam表中全部数据
delete from exam;

truncate语句 (不属于dml语句)

语法
truncate table 表名;
截断表 功能上相当于没有where子句的delete语句和delete语句的区别

1. truncate语句不能应用于主表
   #错误
   truncate table student;

2. 对于有自增长字段的表，使用delete删除数据行后，插入数据时，继续增长。使用truncate截断表后，插入数据时，自增长字段的值恢复初始值。
   delete from exam;
   insert into exam values(null,80,90); – stuno为8

truncate table exam;
insert into exam values(null,80,90); -- stuno为1

3. 使用truncate语句截断表，不能回滚

select语句

select语句概述
语法
select 字段列表 from 数据源 [where 条件表达式] [group by 分组字段列表 [having 条件表达式] ] [order by 排序字段 排序方式];

其中：
字段列表 指定检索字段
数据源 指定检索的表或视图
where子句 用于指定数据行的筛选条件
group by子句 根据分组字段把数据行分成若干个组，并进行汇总统计
having子句 对分组后的数据进行筛选
order by子句 对结果集进行排序，默认升序 asc 缺省 desc – 降序

使用select子句指定字段列表
字段列表的指定方式：
* : 代表数据源中的所有字段
字段列表： 使用逗号隔开的多个字段
表名.* : 多表查询时，指定某张表中的全部字段
表名.字段: 多表查询时，指定某张表中的某个字段
表达式： 表达式中可以包含算术运算、函数等

1) 检索MySQL的版本号、服务器当前时间
select version(),now();

2) 命名别名
字段或表达式 [as] 别名
select version() as 版本号,now() 服务器时间;

基本查询语句

1) 语法
select 字段列表 from 表名;

2) 示例
查询表中全部数据
select * from student;

查询表中的部分字段
select student_no,student_name from student;
select table_schema,table_name from information_schema.tables;

使用表达式
select stuno,exam_score,regular_score, exam_score0.7+regular_score0.3 from exam;

使用别名
select stuno 学号,exam_score 考试成绩,regular_score 平时成绩, exam_score0.7+regular_score0.3 综合成绩 from exam;

distinct排重复

语法
select distinct 字段列表 from 表名 …;
去掉结果集中的重复行

示例
use information_schema;
desc tables;

单列排重
select table_schema from information_schema.tables;
select distinct table_schema from information_schema.tables;

多列排重
select distinct table_schema,table_type from information_schema.tables;
select distinct tablle_schema 数据库名,table_type 表的类型 from information_schema.tables;

limit限制返回行数 --分页

限定返回的行数
语法
select 字段列表 from 表名 limit [start],length;

其中：
start 表示从第几行开始检索数据，默认为0 表示第一行
length 检索的行数(每页的行数)

示例
use information_schema;
select table_schema,table_name from information_schema.tables;

列出information_schema.tables中的前10行
select table_schema 数据库名,table_name 表名 from information_schema.tables limit 10;
或
select table_schema as 数据库名,table_name as 表名 from information_schema.tables limit 10;

练习：每页显示10行，列出第7页
select table_schema 数据库名,table_name 表名 from information_schema.tables limit 60,10;

多表查询

需求：列出学生的信息，包括学号、姓名和班级名称
学号、姓名： student
班级名称： classes
关联字段： student.class_no 、classes.class_no
关联条件： student.class_no=classes.class_no

多表连接的分类
内连接：符合关联条件的数据行被检索出来，不符合的被过滤掉
外连接：外连接的结果集 = 内连接的结果集 + 匹配不上的数据行

内连接

语法
select 字段列表 from 表1 [inner] join 表2 on 关联条件;

示例

1. 列出学生的信息，包括学号、姓名和班级名称
   select student.student_no 学号,student.student_name 姓名, classes.class_name 班级 from student join classes on student.class_no = classes.class_no;

向学生表中插入数据
insert into student values(‘2018006’,‘张三’,‘20000000000’,null);
insert into student values(‘2018007’,‘李四’,‘20000000000’,null);

向班级表中插入数据
insert into classes values(null,‘2018计算机1班’,‘计算机’);
insert into classes values(null,‘2018计算机2班’,‘计算机’);

表的别名
表 [as] 别名
select s.student_no 学号,s.student_name 姓名, c.class_name 班级 from student s join classes c on s.class_no = c.class_no;

#错误表一旦命名别名，该语句中就只能使用别名，不能再用原表名
select student.student_no 学号,s.student_name 姓名, c.class_name 班级 from student s join classes c on s.class_no = c.class_no;

外连接

左外连接：左外连接的结果集 = 内连接的结果集+左表中匹配不上的数据
select 字段列表 from 左表 left [outer] join 右表 on 连接条件;
select s.student_no,s.student_name,c.class_name from student s left join classes c on s.class_no = c.class_no;

右外连接：右外连接的结果集 = 内连接的结果集+右表中匹配不上的数据
select 字段列表 from 左表 right [outer] join 右表 on 连接条件;
select s.student_no,s.student_name,c.class_name from student s left join classes c on s.class_no = c.class_no;

例：
select s.student_no,s.student_name,c.class_name from student s left join classes c on s.class_no = c.class_no union select s.student_no,s.student_name,c.class_name from student s right join classes c on s.class_no = c.class_no;

3) 关联的多个表中，如果字段名没有重名，则可以省略该字段名前的表名或别名的修饰
select student_no 学号,student_name 姓名,class_name 班级 from student s join classes c on s.class_no = c.class_no;

练习：
列出教师及其所授课程的信息，包括教师工号、姓名、课程名和上限人数
select t.teacher_no,t.teacher_name, c.course_name,c.up_limit from teacher t join course c on t.teacher_no = c.teacher_no;

4) 三表内连接
语法
select 字段列表 from 表1 join 表2 on 连接条件1 join 表3 on 连接条件2;

示例：
列出学生的选课信息，包括学号、姓名、课程和成绩
select s.student_no 学号,s.student_name 姓名, cs.course_name 课程名,ch.score 成绩 from choose ch join student s on ch.student_no=s.student_no join course cs on cs.course_no = ch.course_no;

练习：
向选课表choose中插入如下数据：
±-----------------------------------------------------+
student_no course_no score choose_time
±-----------------------------------------------------+
‘2018001’ 2 40 ‘2018-12-23 10:20:30’
‘2018001’ 1 50 ‘2018-12-02 15:30:20’
‘2018002’ 3 60 ‘2018-12-30 12:50:35’
‘2018002’ 2 70 ‘2019-01-02 09:40:45’
‘2018003’ 1 80 ‘2019-01-05 16:30:24’
‘2018004’ 2 90 ‘2019-01-03 11:30:12’
‘2018005’ 3 null ‘2019-06-12 10:12:12’
‘2018005’ 2 null ‘2019-06-10 12:30:25’
±-----------------------------------------------------+

SQL语句如下：
insert into choose values(null,‘2018001’,2,40,‘2018-12-23 10:20:30’);
insert into choose values(null,‘2018001’,1,50,‘2018-12-02 15:30:20’);
insert into choose values(null,‘2018002’,3,60,‘2018-12-30 12:50:35’);
insert into choose values(null,‘2018002’,2,70,‘2019-01-02 09:40:45’);
insert into choose values(null,‘2018003’,1,80,‘2019-01-05 16:30:24’);
insert into choose values(null,‘2018004’,2,90,‘2019-01-03 11:30:12’);
insert into choose values(null,‘2018005’,3,null,‘2019-06-12 10:12:12’);
insert into choose values(null,‘2018005’,2,null,‘2019-06-10 12:30:25’);

where子句

作用
根据条件表达式，筛选符合条件的数据行

语法
where 条件表达式

比较运算符

< > = < = = !=(<>)

列出’张三’的成绩信息
select s.student_no,s.student_name,c.score from student s join choose c on s.student_no=c.student_no where s.student_name=‘张三’;

使用where子句实现内连接

1. 语法
   select 字段列表 from 表1,表2 where 连接条件;

2)示例
select s.student_no,s.student_name,c.score from student s join choose c on s.student_no=c.student_no;
等效于：
select s.student_no,s.student_name,c.score from student s,choose c where s.student_no=c.student_no;

SQL提供的运算符

between and

1) 作用
判断一个字段或表达式是否处于给定的闭区间

2) 语法
字段或表达式 between 值1 and 值2

3) 示例
列出成绩在[70,90]之间的信息
select student_no,course_no,score from choose where score between 70 and 90;

列出选课时间为2018年的信息
select student_no,course_no,choose_time from choose where choose_time between ‘2018-01-01 00:00:00’ and ‘2018-12-31 23:59:59’;

in

1. 作用
   判断一个字段或表达式的值是否在给定的列表中
2. 语法
   字段或表达式 in(值1,值2,…)
3. 示例
   – 列出选修了’c语言’或’Java语言’的信息
   select ch.student_no,cs.course_name,ch.score
   from course cs join choose ch
   on cs.course_no = ch.course_no
   where cs.course_name in(‘c语言’,‘Java语言’);

is null

1. 作用
   判断字段或表达式的值是否为空

2. 语法
   字段或表达式 is null

3. 示例
   列出考试成绩为null的信息
   select student_no,course_no,score from choose where score is null;

 列出没有班级的学生的信息
 select student_no,student_name,student_contact from student where class_no is null;

思考题：列出没有学生的班级的信息
select c.*
  from classes c left join student s
    on c.class_no=s.class_no
      where s.stuent_no is null;

like – 模糊查询

1) 作用
判断字段或表达式的值是否符合给定的模式(格式)

2) 语法
字段或表达式 like ‘模式’

3) 通配符
% 任意长度的任意字符
_ 一个任意字符

4) 示例
列出学生表中姓’王’的同学的信息
select * from student where student_name like ‘王%’;

列出information_schema.tables中表名包含’user’的信息
select table_name from information_schema.tables where table_name like ‘%user%’;

列出information_schema.tables中表名以’user’开头的
select table_name from information_schema.tables where table_name like ‘user%’;

列出information_schema.tables中表名以’user_'开头的
### 匹配的模式中包含通配符(% _),必须进行转义
① 方式一： 使用
select table_name from information_schema.tables where table_name like ‘user_%’;

② 方式二： 使用 escape自定义转义字符
select table_name from information_schema.tables where table_name like ‘user#_%’ escape ‘#’;

逻辑运算符

逻辑非 !

select * from choose where score<60;

条件表达式取反：
select * from choose where !(score<60);
select * from choose where not score<60;
select * from choose where score>=60;

运算符取反：

<	>=
>	<=
=	! =
between …and	not between … and
in	not in(注意NULL值)
like	not like
is null	is not null

列出班号为(1,2)的学生的信息
select * from student where class_no in(1,2); where class_no=1 or class_no=2;

select * from student where class_no not in(1,2); where not(class_no=1 or class_no=2);c_no in(1,2,null)
= =>c_no=1 or c_no=2 or c_no=null
NULL
= =>c_no=1 or c_no=2
c_no not in(1,2,null)= =>not(c_no=1 or c_no=2 or c_no=null)
= = >c_no!=1 and c_no!=2 and c_no!=null
NULL
= =>NULL

逻辑与 and

列出’张三’的’C语言’的成绩
select s.student_name,cs.course_name,ch.score from student s join choose ch on s.student_no=ch.student_no join course cs on cs.course_no=ch.course_no where s.student_name=‘张三’ and cs.course_name=‘C语言’;

列出’张三’的成绩信息
select s.student_no,s.student_name,c.score from student s,choose c where s.student_no=c.student_no and s.student_name=‘张三’;

使用where子句实现三表查询
select s.student_name,cs.course_name,ch.score from student s join choose ch on s.student_no=ch.student_no join course cs on cs.course_no=ch.course_no;

等效于：
select s.student_name,cs.course_name,ch.score from student s,choose ch,course cs where s.student_no=ch.student_no and cs.course_no=ch.course_no;

逻辑或 or

select ch.student_no,cs.course_name,ch.score from choose ch,course cs where ch.course_no = cs.course_no
and cs.course_name in(‘C语言’,‘Java语言’);
and (cs.course_name=‘C语言’ or cs.course_name=‘Java语言’);

order by子句

作用
对结果集排序

语法
order by 排序字段 [ 排 序方式 ][ 排序字段 [排序方式 ], … ]

其中：
排序字段: 用于排序得到字段或表达式
排序方式：
asc 缺省方式 升序
desc 降序

示例
按照成绩排序，降序显示学号、课程号和成绩
select student_no,course_no,score from choose
order by score desc;

NULL值默认按照最小值处理
select student_no,course_no,score from choose
order by score;

多列排序
select * from student
order by class_no,student_no desc;

带where子句的排序
列出’张三’的成绩信息，并按成绩降序显示
select s.student_no,s.student_name,c.score from student s,choose c where s.student_no = c.student_no and s.student_name=‘张三’ order by c.score desc;

组函数(聚合函数)

组函数用于对一组数据进行汇总统计，并返回一个统计结果

常用的组函数

count	统计一组数据的个数 参数可以是任意类型 可以使用*
max	统计一组数据的最大值 参数可以是数值、字符串、日期时间
min	统计一组数据的最小值 参数可以是数值、字符串、日期时间
sum	统计一组数据的累加和 参数是数值类型
avg	统计一组数据的平均值 参数是数值类型

统计考试成绩不及格的人数
select count(*) from choose where score<60;

统计class_no =1的班级学生人数
select count(student_no) cnt from student where class_no =1; – cnt 是别名

统计数据‘choose’ 中表的数量
select count(*) from information_schema.tables where table_schema = ‘choose’;

列出选课时间最早和最晚的数据
select min(choose_time),max(choose_time) from choose;

列出’C语言’的总分和平均分
select sum(ch.score) 总分,avg(ch.score) 平均分 from course cs,choose ch where cs.course_no = ch.course_no and cs.course_name=‘C语言’;

组函数对NULL的处理 – 忽略
select count(student_no),count(class_no) from student;

组函数的参数可以使用distinct修饰(排重统计)
select count(table_schema),count(distinct table_schema) from information_schema.tables;
select count(*),count(class_no), count(distinct class_no) from student; – distinct class_no 非空且不重复的数据数量

分组语句

group by子句

作用
将查询结果按照一个或多个字段进行分组并汇总(通常和组函数一起使用)

语法
group by 分组字段或表达式的列表

示例
统计每个数据库中表的数量(information_schema.tables)
select table_schema,count(table_name) cnt from information_schema.tables group by table_schema;

在分组语句中，select子句的字段列表只能使用分组字段、依赖于分组字段的字段和组函数
select s.class_no,c.class_name,count(s.student_no) cnt from student s left join classes c on s.class_no=c.class_no group by s.class_no;
select c.class_no,c.class_name,count(s.student_no) cnt from student s,class c where s.class_no=c.class_no group by s.class_no;

多列分组
select table_schema,table_type,count(table_name) cnt from information_schema.tables group by table_schema,table_type;

思考题：列出表的数量多于50个数据库的信息
select table_schema,count(table_name) cnt from information_schema.tables
– where cnt>50 where子句中不能使用字段或表达式的别名
– where count(table_name)>50 where子句中不能使用组函数
group by table_schema;

having子句

作用
在分组语句中，根据条件筛选符合条件的组

语法
group by
having 条件表达式

示例
select table_schema,count(table_name) cnt from information_schema.tables group by table_schema having cnt>50;
having count(table_name)>50;

select语句
select table_schema db,count(table_name) cnt from information_schema.tables where 1=1 group by db – having cnt>50; having count(table_name)>50 order by cnt desc limit 2;

执行顺序：
from–>where–>select–>group by–>having–>order by–>limit

子查询

概念
如果一个select语句嵌套在另一条SQL语句(例如select、insert、update、delete)中，那么该select语句称为子查询，包含子查询的SQL语句称为主查询(外层查询)。
通常把子查询写在一对小括号中

where子句使用子查询
子查询返回单值
运算符可以使用比较运算符
使用子查询列出’张三’的成绩信息

1. 查询’张三’的学号
   select student_no from student where student_name=‘张三’; – 2018001

2. 根据学号查询成绩
   select student_no,course_no,score from choose where student_no=‘2018001’;

3. 合并
   select student_no,course_no,score from choose where student_no=( select student_no from student where student_name=‘张三’);

子查询返回多个值

运算符可以使用 in /not in >any < any > all

in与not in

检索没有申请选修课的教师的信息

1. 使用表连接方式实现
   select t.* from teacher t left join course c on t.teacher_no=c.teacher_no where c.course_name is null;

2. 使用子查询实现
   select * from teacher where teacher_no not in( select teacher_no from course );

练习：
使用子查询列出没有学生的班级
select * from classes where class_no not in( select distinct class_no from student where class_no is not null );

没有学生的班级信息
select *from classes where class_no not in(select class_no from student);

from子句使用子查询

每一个select语句可以看成是一个虚拟的内存表，可以在结果集的基础上进行进一步的查询。
在from字句中使用子查询时，结果集必须命名别名

列出标的数量大于50的数据库的信息
select table_schema,count(table_name) cnt from infomation_schema.tables group by table_schema having cnt>50;
select * from (select table_schema,count(table_name) cnt from information_schema.tables group by table_schema) db where cnt>50;

列出比平均分高的成绩信息
select student_no,course_no,score from choo