C++基础 MySQL
- c++ 数据库基础 MySQL
-
- 数据库概述
- 术语
- 主流的关系型数据库产品
- SQL语言
- MySQL的特点
- 启动和停止服务
- 连接MySQL
- MySQL数据库管理
- MySQL表管理
- MySQL表结构的管理
- 创建"选课系统"数据库
-
- 创建表设置自增长字段
- 复制表结构
- DML语句
-
- 向表中写入数据
- 向表中批量写入多行数据insert into
- update语句
- delete语句
- truncate语句 (不属于dml语句)
- select语句
- 基本查询语句
- distinct排重复
- limit限制返回行数 --分页
- 多表查询
- 内连接
- 外连接
- where子句
- between and
- in
- is null
- like -- 模糊查询
- 逻辑运算符
- 逻辑非 !
- 逻辑与 and
- 逻辑或 or
- order by子句
- 组函数(聚合函数)
- 分组语句
- group by子句
- having子句
- 子查询
- 子查询返回多个值
- in与not in
- from子句使用子查询
- 索引 index
- 创建索引
- 查看索引
- 在已有表上创建索引
- 删除索引
- 视图view
- 创建视图
- 查看视图
- 删除视图
- MySQL编程的基础知识
- 流程控制语句
- 系统函数
- 条件控制函数
- trigger触发器
- procedure存储过程
- 错误触发条件和错误处理
- cursor游标
- 事务机制
- 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的特点
- 性能高效
- 跨平台支持
- 简单易用
- 开源
- 支持多用户
启动和停止服务
-
启动
service mysql start -
重启
service mysql restart -
停止
service mysql stop
连接MySQL
- 语法
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
- 示例
mysql -h 10.10.5.2 -u root -p -P 3307
mysql -u root -p
MySQL数据库管理
创建数据库
- 语法
create database 数据库名; - 示例
– 创建"选课系统"数据库
create database choose;
查看数据库
-
查看所有数据库的列表
show databases; -
显示指定数据库结构
– 语法
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 );
查看表
-
查看当前数据库中表的列表
show tables; -
查看指定表的详细信息
– 语法
show create table 表名;
– 示例
show create table stu_info\G – \G表示纵向显示结果 -
查看指定表的表结构
– 语法
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) );
- 添加测试数据
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);
- 删除表
先删除从表,再删除主表
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));
创建表设置自增长字段
-
语法
字段名 数据类型 auto_increment
自增长字段的数据类型必须是整型,必须设置为主键
auto_increment primary key
或
primary key auto_increment -
示例
– 创建班级表,班级编号设置为自增长
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语句的区别
-
truncate语句不能应用于主表
#错误
truncate table student; -
对于有自增长字段的表,使用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
- 使用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 关联条件;
示例
- 列出学生的信息,包括学号、姓名和班级名称
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子句实现内连接
- 语法
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
- 作用
判断一个字段或表达式的值是否在给定的列表中 - 语法
字段或表达式 in(值1,值2,…) - 示例
– 列出选修了’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
-
作用
判断字段或表达式的值是否为空 -
语法
字段或表达式 is null -
示例
列出考试成绩为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子句使用子查询
子查询返回单值
运算符可以使用比较运算符
使用子查询列出’张三’的成绩信息
-
查询’张三’的学号
select student_no from student where student_name=‘张三’; – 2018001 -
根据学号查询成绩
select student_no,course_no,score from choose where student_no=‘2018001’; -
合并
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
检索没有申请选修课的教师的信息
-
使用表连接方式实现
select t.* from teacher t left join course c on t.teacher_no=c.teacher_no where c.course_name is null; -
使用子查询实现
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