数据库（头歌）

目录

一. MySQL数据库 -初始MySQL

第1关：创建数据库

第2关：创建表

第3关：使用主键约束

第4关：外键约束

第5关：添加常用约束

二. MySQL开发技巧 -索引

普通索引：

唯一索引：

主键索引：

组合索引：

删除索引

查询表中索引

三. MySQL数据库 -单表查询（一）

第1关：基本查询语句

第2关：带 IN 关键字的查询

第3关：带 BETWEEN AND 的范围查询

四. MySQL数据库 -单表查询（二）

第1关：带 LIKE 的字符匹配查询

第2关：查询空值与去除重复结果

第3关：带 AND 与 OR 的多条件查询

五. MySQL数据库 -单表查询（三）

第1关：对查询结果进行排序

第2关：分组查询

第3关：使用 LIMIT 限制查询结果的数量

六. MySQL数据库 -使用聚合函数查询

第1关：COUNT( )函数

第2关：SUM( )函数

第3关：AVG( )函数

第4关：MAX( )函数

第5关：MIN( )函数

七.数据查询（一）

第1关：按条件查询单表的所有字段

第2关：查询唯一值

第3关：统计查询

第4关：分组查询

第5关：数据排序

八.数据查询（二）

第1关：多表查询

第2关：多表查询及统计分组

第3关：子查询

第4关：多表子查询

九. MySQL数据库 -连接查询

第1关：内连接查询

第2关：外连接查询

第3关：复合条件连接查询

十. MySQL数据库 – 子查询

第1关：带比较运算符的子查询

第2关：关键字子查询

十一. MySQL数据库 -分组选择数据

第1关：GROUP BY 与 聚合函数

第2关：使用 HAVING 与 ORDER BY

十二. MySQL数据库 -数据库和表的基本操作（一）

第1关：查看表结构与修改表名

第2关：修改字段名与字段数据类型

第3关：添加与删除字段

第4关：修改字段的排列位置

第5关：删除表的外键约束

十三. MySQL数据库 -数据库和表的基本操作（二）

第1关：插入数据

第2关：更新数据

第3关：删除数据

十四. MySQL开发技巧 – 视图

视图的定义

创建视图

操作视图

删除视图

十五. MySQL开发技巧 – 分页和索引

第1关：MySQL 分页查询

第2关：索引（单列索引）

第3关：索引（组合索引）

十六. MySQL数据库 – 其他函数的使用

第1关：字符函数

第2关：数学函数

第3关：日期时间函数和流程控制类函数

十七. 数据库E-R图

一. MySQL数据库 -初始MySQL

第1关：创建数据库

• 连接数据库

mysql -u用户名 -p密码

mysql -uroot -p123123 -h127.0.0.1

（在平台上连接数据库还需要加上一句-h127.0.0.1）

• 查看已存在的数据库

show databases;

• 创建数据库

create database MyDb(数据库名);

第2关：创建表

• 选择数据库

USE MyDb;

• 创建表

CREATE TABLE 表名
(
字段名 数据类型,
字段名 数据类型,
字段名 数据类型
);

• 查看表的基本结构

DESCRIBE 表名;

DESC 表名; （简写）

第3关：使用主键约束

表的主键就可以看做是人的身份证

• 添加主键约束

1. 在定义列的同时指定主键

2. 在定义完所有列之后指定主键

• 多字段联合主键

CREATE TABLE t_emp2
(
name VARCHAR(32),
deptId INT,
salary FLOAT,
PRIMARY KEY(name,deptId)
)

第4关：外键约束

外键就是另一张表中的主键,外键的主要作用就是保持数据的一致性，完整性

• 添加外部关键字

CONSTRAINT 外键名 FOREIGN KEY 字段名 REFERENCES 主表名(主键名)

第5关：添加常用约束

• 添加唯一约束

唯一约束（Unique Constraint）要求该列唯一，允许为空，但是只能有一个空值。唯一约束可以确保一列或者几列不出现重复值。

CREATE TABLE t_dept (
id INT PRIMARY KEY,
name VARCHAR(22) UNIQUE,
location VARCHAR(50)
)

• 添加非空约束

CREATE TABLE t_dept(
id INT PRIMARY KEY,
name VARCHAR(22) NOT NULL,
location VARCHAR(50)
)

• 默认约束

给字段一个默认值

CREATE TABLE t_emp (
id INT PRIMARY KEY,
name VARCHAR(22),
sex VARCHAR(2) DEFAULT ‘男’
) DEFAULT CHARSET=utf8;

注：1.如果是添加字符串型默认值要使用单引号，如果是整型则不需要加任何符号；

2.如果要添加的是中文默认值，则需要加上DEFAULT CHARSET=utf8，使用英文字符则不需要

• 自动增加属性值

经常有个需求就是，在每次插入新纪录时，系统自动生成字段的主键值，默认情况下初始值和增量都为1。

CREATE TABLE t_tmp
(
id int PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(32)
)

在数据库MyDb中创建表t_user，表结构如下：

字段名称	数据类型	备注
id	INT	用户ID，主键，自动增长
username	varchar(32)	用户名，非空，唯一
sex	varchar(4)	性别，默认“男”

CREATE DATABASE MyDb;

use MyDb;

create table t_user

(id int PRIMARY KEY AUTO_INCREMENT,

username VARCHAR(32) NOT NULL UNIQUE,

sex varchar(4) DEFAULT '男'

)DEFAULT CHARSET =utf8;

二. MySQL开发技巧 -索引

数据库索引是一种提高数据库系统性能的方法。索引能让数据库服务器更快地查找和获取表中指定的行，但是索引也给数据库系统带来了一定的开销，所以我们应该谨慎地使用它们。

• 普通索引：

仅加速查询， 最基本的索引，没有任何限制，是我们大多数情况下使用到的索引

1. 创表时创建普通索引

CREATE table mytable(
id INT NOT NULL,
username VARCHAR(16) NOT NULL,
INDEX [indexName] (username)
);

2. 建表后创建普通索引

create INDEX 索引名称 on 表名(字段名);

#或者

ALTER TABLE 表名 ADD INDEX 索引名称 (字段名);

创建名为idx_age的普通索引：

create index idx_age on student(age);

• 唯一索引：

索引列中的值必须是唯一的，但允许为空值

CREATE UNIQUE INDEX 索引名称 ON 表名(字段名);

#或者

ALTER TABLE 表名 ADD UNIQUE (字段名);

创建名为uniq_classes的唯一索引：

CREATE UNIQUE INDEX uniq_classes ON student(classes);

• 主键索引：

主键索引一般在建表时创建，会设为 int 而且是 AUTO_INCREMENT自增类型的，例如一般表的id字段。

CREATE TABLE mytable (

id int(11) NOT NULL AUTO_INCREMENT,

PRIMARY KEY (id)

);

创建名为pk_student的主键索引：

create table student(

stu_id int not null ,

name varchar(25) not null,

age int not null,

sex char(2) not null,

classes int not null,

grade int not null,

primary key (stu_id)

);

• 组合索引：

组合索引就是在多个字段上创建一个索引。（应用场景：当表的行数远远大于索引键的数目时，使用这种方式可以明显加快表的查询速度）

CREATE INDEX 索引名称 ON 表名(字段1,字段2，字段3);

#或者

ALTER TABLE 表名 ADD INDEX 索引名称(字段1，字段2，字段3);

创建名为idx_group的组合索引：

CREATE INDEX idx_group ON student(name,sex,grade);

• 删除索引

#使用drop删除索引

drop index index_name on table_name ;

#使用alter删除索引

alter table table_name drop index index_name ;

alter table table_name drop primary key ; #删除主键索引

• 查询表中索引

show index from 表名;

三. MySQL数据库 -单表查询（一）

第1关：基本查询语句

查询数据表中指定字段的内容:

SELECT 字段名 FROM 表名;

SELECT 字段名1, 字段名2 FROM 表名;

提示： MySQL 中的 SQL 语句对关键字的大小写不敏感， SELECT 和 select 是一样的。 但是许多开发者更喜欢将关键字大写，表名和字段名使用小写，养成这种习惯，以后写出来的脚本就更容易阅读和维护了。

查询数据表中的所有内容:

SELECT * FROM 表名;

提示： 虽然使用星号 * 看起来是一件很方便的事情，但是建议在不想获取整张表的情况下，使用字段名来进行查询。很显然，它会降低查询的效率。

• 查询字段Name和Salary的内容；

SELECT Name, Salary

FROM tb_emp;

• 查询整张表的内容。

SELECT *

FROM tb_emp;

第2关：带 IN 关键字的查询

带IN关键字的查询

IN关键字被用在WHERE语句的后边，用来过滤所需要查询的内容。更形象的说，IN关键字的使用情形就像点名，点到谁谁就要站出来。

SELECT 字段名 FROM 表名 WHERE 字段名 IN (n1,n2,n3,…);

带NOT IN关键字的查询

查询的是除了括号中出现内容外的所有内容。

SELECT 字段名 FROM 表名 WHERE 字段名 NOT IN (n1,n2,n3,…);

查询当Id不等于1时，字段Name和Salary的内容:

SELECT Name ,Salary

FROM tb_emp

WHERE Id not IN (1);

第3关：带 BETWEEN AND 的范围查询

带BETWEEN AND关键字的查询

某一范围内符合条件的数据,BETWEEN AND需要两个参数支持，一个是范围的开始值，另一个是结束值。如果字段值满足指定的范围查询条件，就返回这些满足条件的数据内容。

SELECT 字段名 FROM 表名 WHERE 字段名 BETWEEN n1 AND n2;

带NOT BETWEEN AND关键字的查询

表示查询指定范围之外的值。

SELECT 字段名 FROM 表名 WHERE 字段名 NOT BETWEEN n1 AND n2;

查询当字段Salary范围在3000~5000时，字段Name和Salary的内容。

SELECT Name,Salary

FROM tb_emp

WHERE Salary BETWEEN 3000 AND 5000;

四. MySQL数据库 -单表查询（二）

第1关：带 LIKE 的字符匹配查询

使用通配符%模糊匹配数据内容

百分号通配符%可以匹配任意长度的字符，甚至包括零字符。

SELECT 字段名 FROM 表名 WHERE 字段名 LIKE ‘字符%’;

其中 % 的位置可以根据需要在字符间变化。

使用通配符_模糊匹配数据内容

下划线通配符_只能模糊匹配1个字符。

SELECT 字段名 FROM 表名 WHERE 字段名 LIKE ‘字符_’;

其中`_`的位置可以根据需要在字符间变化。

查询所有Name以字母C为起始的员工的Name和Salary的内容

SELECT Name,Salary

FROM tb_emp

WHERE Name LIKE ‘C%’;

第2关：查询空值与去除重复结果

查询空值

这个NULL既不代表0，也不代表空字符，而是代表一种未知的状态，比如不适用或者放着等将来有合适数据了再添加进去。

SELECT 字段名 FROM 表名 WHERE 字段名 IS NULL;

去除重复结果

消除数据表中重复的数据,DISTINCT

SELECT DISTINCT 字段名 FROM 表名;

• 使用关键字IS NULL返回数据表中字段DeptId为NULL的所有字段的内容，注意，返回的是指定行所有字段的内容；

SELECT *

FROM tb_emp

WHERE DeptId IS NULL;

• 使用关键字DISTINCT返回数据表中字段Name不重复的内容，注意，只需返回字段Name的内容。

SELECT DISTINCT Name

FROM tb_emp;

第3关：带 AND 与 OR 的多条件查询

带AND关键字的多条件查询

满足所有条件的查询才会被返回。

SELECT 字段名 FROM 表名 WHERE 表达式1 AND 表达式2;

带OR关键字的多条件查询

满足两个条件中的其中一个条件即可返回结果。

SELECT 字段名 FROM 表名 WHERE 表达式1 OR 表达式2;

提示：

1. OR 可以和 AND 一起使用。但是 AND 的优先级要高于 OR 的优先级。

2. 关键字IN，它能实现与OR相同的功能，虽然两种写法功能一样，但是更推荐使用 IN 关键字。因为它不仅逻辑清晰，执行的速度也会快于 OR 关键字。更重要的是，使用 IN 关键字，以后可以执行更加复杂的嵌套语句。

• 使用关键字AND返回数据表中字段DeptId为301并且薪水大于3000的所有字段的内容，其中DeptId的倒数第二个字母为i的大写；

SELECT *

FROM tb_emp

WHERE DeptId = 301 AND Salary > 3000;

• 使用关键字IN返回数据表中字段DeptId为301和303的所有字段的内容。

SELECT *

FROM tb_emp

WHERE DeptId IN(‘301’, ‘303’);

五. MySQL数据库 -单表查询（三）

第1关：对查询结果进行排序

对查询结果排序

对读取的语句进行排序，使用Order By子句来设定

SELECT 字段名 FROM 表名 ORDER BY 字段名 [ASC[DESC]];

指定排序方向

• ASC升序关键字
• DESC降序关键字

查询1班同学的所有信息以成绩降序的方式显示结果

SELECT *

FROM tb_score

WHERE class_id IN(1)

ORDER BY score DESC;

第2关：分组查询

分组查询的单独使用

查询的是每个分组中首次出现的一条记录。

SELECT 字段名 FROM 表名 GROUP BY 字段名;

对班级名称进行分组查询

SELECT *

FROM tb_class

GROUP BY class_id ;

第3关：使用 LIMIT 限制查询结果的数量

LIMIT的使用

限制查询结果的数量。

SELECT 字段名 FROM 表名 LIMIT [OFFSET,] 记录数;

参数说明：

• 第一个参数，OFFSET，可选参数，表示偏移量，如果不指定默认值为0，表示从查询结果的第一条记录开始，若偏移量为1，则从查询结果中的第二条记录开始，以此类推。

• 第二个参数，记录数，表示返回查询结果的条数

查询班级中第2名到第5名的学生信息，并根据学生成绩进行降序排序。

SELECT *

FROM tb_score

WHERE score

ORDER BY score DESC LIMIT 1,4;

六. MySQL数据库 -使用聚合函数查询

第1关：COUNT( )函数

COUNT()函数基本使用

用来统计记录的总条数

select count(*/字段名) from 数据表;

查询该表中一共有多少条数据

select count(*)

from tb_class;

查询此表中367 班有多少位学生,以班级id和对应人数格式输出

select classid,count(*)

from tb_class

where classid=367;

第2关：SUM( )函数

SUM()函数基本使用

对数据表的某列进行求和操作

select sum(字段名) from 数据表;

查询该表中学生的总成绩

select sum(score)

from tb_class;

查询学生语文课程的总分数

select course, sum(score)

from tb_class

where course=‘语文’;

第3关：AVG( )函数

AVG()函数基本使用

对数据表的某列进行求平均值操作

select avg(字段名) from 数据表;

查询表中该班级三位同学语文的平均分数以及对应的课程名

select course, avg(score)

from tb_class

where course=‘语文’;

查询表中该班级三位同学英语的平均分数以及对应的课程名

select course, avg(score)

from tb_class

where course=‘英语’;

第4关：MAX( )函数

MAX()函数基本使用

MAX()函数是求某列的最大数值

select max(字段名) from 数据表;

查询语文课程中的最高分数

select course ,max(score)

from tb_class

where course=‘语文’;

查询英语课程中的最高分数

select course ,max(score)

from tb_class

where course=‘英语’;

第5关：MIN( )函数

MIN()函数基本使用

求某列的最小数值

select min(字段名) from 数据表

查询语文课程中的最低分数

select course,min(score)

from tb_class

where course=‘语文’;

查询英语课程中的最低分数

select course,min(score)

from tb_class

where course=‘英语’;

七.数据查询（一）

第1关：按条件查询单表的所有字段

select * from 数据表 where 查询条件

1.查询街道信息(jdxx)数据表的开福区(qxmc)的所有字段
select * from jdxx where qxmc=“开福区” ;

2.查询街道信息(jdxx)数据表的开福区和岳麓区(qxmc)的所有字段

select * from jdxx where qxmc=“开福区” or qxmc=“岳麓区”;

3.查询街道信息(jdxx)数据表的长沙市(cs)的西湖街道（name)所有字段

select * from jdxx where name=“西湖街道” and cs=“长沙市”;

第2关：查询唯一值

distinct 去掉重复的查询结果

根据jdxx数据表

查询湖南省(sf)所有的区县名称(qxmc)，每个区县只出现一次

select distinct qxmc from jdxx where sf="湖南省";

查询长沙市(cs)所有的区县名称(qxmc)，每个区县只出现一次

select distinct qxmc from jdxx where cs="长沙市";

第3关：统计查询

统计查询函数:

SUM计算数值列的和
AVG计算数值列的平均值
MAX计算列（数值、日期、字符）的最大值
MIN计算列（数值、日期、字符）的最小值
COUNT计算查询结果的数目

对jdxx统计查询

查询湖南省的街道个数

select count(name) from jdxx where sf=“湖南省” ;

查询长沙市的街道个数

select count(name) from jdxx where cs=“长沙市” ;

查询湖南省的区县个数

select count(distinct qxmc) from jdxx where sf=“湖南省” ;

查询长沙市的区县个数

select count(distinct qxmc) from jdxx where cs=“长沙市” ;

第4关：分组查询

select 表达式列表 from 数据表 where 条件 group by 分组字段 having 分组条件

统计每个省的名称和街道数目

select sf ,count(*) from jdxx

group by sf;

显示街道数目大于200个的城市和街道数目

select cs ,count(*) from jdxx

group by cs having count(name)>200;

显示长沙市每个区县的街道数目

select qxmc ,count(*) from jdxx

where cs=‘长沙市’

group by qxmc;

第5关：数据排序

排序数据

Order by ASC | DESC, ASC | DESC……

• 首先按照的顺序排列记录，若多条记录的值相同，则按的顺序排列 ASC升序 DESC降序 （默认升序）。
• 使用limit 位置偏移量 指定查询结果的数量 ，位置偏移量指示从第几行开始显示，0表示从第1行开始，若省略默认为0，指定返回的记录条数。

显示jdtj 数据表中街道个数最多的10个的所有字段

select * from jdtj

order by jdgs desc

limit 10;

显示 jdtj 数据表中街道个数最少的10个的所有字段

select * from jdtj

order by jdgs asc

limit 10;

按街道个数从高到底，街道个数相同的按省份升序排序显示 jdtj 数据表中街道个数大于35的所有字段

select * from jdtj

where jdgs>35

order by jdgs desc,sf asc;

八.数据查询（二）

第1关：多表查询

在连接查询中引用两个表的公共字段时，必须在字段前添加表名作为前缀，否则系统会提示出错。

二表查询:

select * from 数据表1，数据表2 where 连接条件

或

from 数据表1 join 数据表2 on 连接条件

三表查询:

from 数据表1 ,数据表2 ,数据表3 where 连接条件1 and 连接条件2

根据读者(reader)和借阅(borrow)数据表，查询王颖珊的借阅记录，包括条形码txm、借阅日期jyrq、还书日期hsrq

select txm , jyrq , hsrq

from borrow , reader

where reader.dzzh = borrow.dzzh and xm=‘王颖珊’;

根据图书(book)和借阅(borrow)数据表，查询李白全集被借阅的情况：包括读者证号dzzh、借阅日期jyrq、还书日期hsrq

select dzzh , jyrq , hsrq

from borrow , book

where borrow.txm = book.txm and book.sm=‘李白全集’;

根据读者(reader)、图书(book)和借阅(borrow)数据表查询没有被归还的借阅信息：包括读者证号dzzh、姓名xm、电话dhhm、条形码txm、书名sm、借阅日期jyrq

提示：通过isnull(表达式)可以判断表达式是否NULL值

select reader.dzzh , xm , dhhm , book.txm , sm , borrow.jyrq

from reader,book,borrow

where reader.dzzh=borrow.dzzh and book.txm = borrow.txm and isnull(hsrq);

第2关：多表查询及统计分组

注意：order by , 表示首先按第一个表达式的值排序，第一个表达式的值相同的再按第二个表达式的值排序

第一题： 统计每本书借阅的次数,显示书名和借阅次数（借阅次数命名为jycs),按借阅次数降序排列,借阅次数相同的按书名降序排列 （提示：borrow数据表的一条数据对应一次借阅）

select sm,count(*)jycs

from borrow left join book on book.txm=borrow.txm

group by sm

order by count(*) desc,sm desc;

第二题： 统计借阅次数在2次以上的图书的借阅的次数,显示书名和借阅次数，按借阅次数降序排列,借阅次数相同的按书名降序排列

select sm,count(*)jycs

from borrow left join book on book.txm=borrow.txm

group by sm having(count(*)>=2)

order by count(*) desc,sm desc;

第三题 统计每个出版社的图书的借阅次数,显示出版社的名称和借阅次数，按借阅次数降序排列,借阅次数相同的按出版社降序排列

select cbs,count(*)jycs

from borrow left join book on book.txm=borrow.txm

group by cbs

order by count(*) desc,cbs desc;

第四题： 统计每位读者借阅的次数,显示姓名和借阅次数,按借阅次数降序排列，借阅次数相同的按姓名降序排列

select xm,count(*)jycs

from borrow left join reader on borrow.dzzh=reader.dzzh

group by xm

order by count(*) desc,xm desc;

第五题： 统计研究生读者借阅的次数,显示姓名和借阅次数，按借阅次数降序排列，借阅次数相同的按姓名降序排列

select xm,count(*)jycs

from borrow left join reader on borrow.dzzh=reader.dzzh

where sf=‘研究生’

group by xm

order by count(*) desc,xm desc;

第3关：子查询

在select语句中，一个查询语句完全嵌套在另一个查询语句的where或having的条件短语中，称为子查询或嵌套查询。 通常把条件短语中的查询成为子查询，父查询则使用子查询的查询结果作为查询条件。

查询与李白全集同一个出版社的图书的书名（不包括李白全集）

select sm

from book

where cbs = (

select cbs

from book

where sm =‘李白全集’

) and sm !=‘李白全集’;

查询高于图书的平均售价(sj)的图书的书名和售价

select sm, sj

from book

where sj > (

select avg(sj)

from book);

查询售价最高的图书的条形码、书名和售价

select txm, sm, sj

from book

where sj = (

select max(sj)

from book);

查询售价最低的图书的条形码、书名和售价

select txm, sm, sj

from book

where sj = (

select min(sj)

from book);

第4关：多表子查询

查询曾经借过图书的读者的读者证号和姓名

select distinct reader.dzzh,xm

from reader,borrow

where reader.dzzh = borrow.dzzh

order by reader.dzzh asc;

查询曾经没有被借阅的图书的条形码和书名

select book.txm, sm

from book left join borrowon book.txm = borrow.txm

where jyrq is null;

查询与孙思旺借过相同图书的读者的读者证号和姓名，按读者证号升序排列

select distinct reader.dzzh, xm

from reader,borrow

where reader.dzzh = borrow.dzzh and txm in(

select borrow.txm

from reader,borrow,book

where reader.dzzh = borrow.dzzh and borrow.txm = book.txm and xm = ‘孙思旺’

) and xm !=‘孙思旺’

order by reader.dzzh asc;

查询借阅过李白全集的读者所借过的其他图书的书名 按书名升序排列

select distinct sm

from borrow,book

where borrow.txm = book.txm and dzzh in(

select dzzh

from borrow,book

where borrow.txm = book.txm and sm =‘李白全集’

)and sm !=‘李白全集’

order by sm asc;

九. MySQL数据库 -连接查询

第1关：内连接查询

内连接查询

• 仅将两个表中满足连接条件的行组合起来作为结果集，称为内连接；

• 关键字：[inner] join ... on

表1 [inner] join 表2 on 表1.字段=表2.字段

从表1中取出每一条记录，去表2中与所有的记录进行匹配，匹配必须是某个条件在表1中与表2中相同，最终才会保留结果，否则不保留。inner 关键字可省略不写；on 表示连接条件：条件字段就是代表相同的业务含义，大多数情况下为两张表中的主外键关系

查询数据表tb_student，tb_class中学生姓名（studentName）和对应的班级名称（className）

select tb_student.name as studentName , tb_class.name as className

from tb_student join tb_class on tb_class.id = tb_student.class_id ;

第2关：外连接查询

外连接查询

• 以某张表为主，取出里面的所有记录，然后每条与另外一张表进行连接，不管能不能匹配上条件，最终都会保留。能匹配，正确保留；不能匹配，其它表的字段都置空（null），称为外连接。

• 外连接查询分为左外连接查询和右外连接查询

• 关键字：left/right [outer] join ... on

表1 left/right [outer] join 表2 on 表1.字段=表2.字段

1. 左外连接：在内连接的基础上，还包含表1中所有不符合条件的数据行，并在其中的表2列填写 NULL；
2. 右外连接：在内连接的基础上，还包含表2中所有不符合条件的数据行，并在其中的表1列填写 NULL。

使用左外连接查询所有学生姓名和对应的班级

selecttb_student.name as studentName , tb_class.name as className

fromtb_class right join tb_student on tb_class.id=tb_student.class_id;

使用右外连接查询所有学生姓名和对应的班级

selecttb_student.name as studentName , tb_class.name as className

fromtb_class left join tb_student on tb_class.id=tb_student.class_id;

第3关：复合条件连接查询

复合条件连接查询

复合条件连接查询，就是在连接查询的过程中，通过添加过滤条件来限制查询结果，使查询结果更加精确。

查询所有班级里分数在90分以上的学生的姓名和学生的成绩以及学生所在的班级

select s1.name as studentName,score,s2.name as className

from tb_student as s1 ,tb_class as s2

where s1.class_id=s2.id and s1.score>90 order by score desc;

十. MySQL数据库 – 子查询

第1关：带比较运算符的子查询

子查询

嵌套在查询内部，且必须始终出现在圆括号内

分为四类：

• 标量子查询：返回单一值的标量，最简单的形式；

• 列子查询：返回的结果集是 N 行一列；

• 行子查询：返回的结果集是一行 N 列；

• 表子查询：返回的结果集是 N 行 N 列。

eg: SELECT * FROM t1 WHERE col1=(SELECT col2 FROM t2);

带比较运算符的子查询

• 带有比较运算符的子查询是指父查询和子查询使用比较运算符连接的嵌套查询

• 使用场景：当用户能够确切的知道内层查询返回的是单个值时,可以使用比较运算符

运算符	说明
>	大于
>=	大于或等于
=	等于
!= 或	不等于
<	小于
<=	小于或等于

查询大于所有平均年龄的员工姓名与年龄

select name,age

from tb_emp

where age > (

select avg(age)

from tb_emp ) ;

第2关：关键字子查询

ALL关键字

ALL必须接在一个比较运算符的后面，表示与子查询返回的所有值比较都为 TRUE则返回TRUE。

eg:SELECT col1 FROM table1 WHERE col1 > ALL (SELECT col2 FROM table2)

ANY和SOME关键字

ANY与比较操作符联合使用，表示与子查询返回的任何值比较为TRUE ，则返回TRUE。 SOME是ANY的别名，一般用的比较少。

IN关键字

• IN的意思就是指定的一个值是否在这个集合中，如果在就返回TRUE；否则就返回FALSE了，同IN(项1，项2，…)

• IN 是 = ANY 的别名，二者相同，但 NOT IN 的别名却不是 ANY 而是 SOME

使用 ALL 关键字进行查询

select position,salary

from tb_salary

where salary > ALL (

select salary

from tb_salary

where position="java");

使用 ANY 关键字进行查询

select position,salary

from tb_salary

where salary > ANY (

select salary

from tb_salary

where position="java");

使用 IN 关键字进行查询

select position,salary

from tb_salary

where salary IN (

select salary

from tb_salary

where position="java");

十一. MySQL数据库 -分组选择数据

第1关：GROUP BY 与 聚合函数

GROUP BY与聚合函数的使用

select [聚合函数] 字段名 from 表名 [where 查询条件]　[group by 字段名]

注:select指定的字段要么就要包含在Group By语句的后面，作为分组的依据；要么就要被包含在聚合函数中。

查询表中2,3,4年级中分别男女的总人数

select gradeId,sex,count(*)

from student

where gradeId in(2,3,4)

group by gradeId,sex;

第2关：使用 HAVING 与 ORDER BY

使用having子句进行分组筛选

having子句用来对分组后的数据进行筛选,常与Group by连用

select [聚合函数] 字段名 from 表名 [where 查询条件] [group by 字段名] [having 字段名 筛选条件]

select语句中，where、group by、having子句和聚合函数的执行次序如下：

1. where子句从数据源中去除不符合条件的数据；
2. 然后group by子句搜集数据行到各个组中；
3. 接着统计函数为各个组计算统计值；
4. 最后having子句去掉不符合其组搜索条件的各组数据行。

Having与Where的区别

where子句都可以用having代替，区别在于where过滤行，having过滤分组

• where子句的作用是在对查询结果进行分组前，将不符合where条件的行去掉，where条件中不能包含聚组函数，使用where条件过滤出特定的行

• having子句筛选满足条件的组，在分组之后过滤数据，条件中经常包含聚组函数，使用having条件过滤出特定的组，也可以使用多个分组标准进行分组

Group By和Order By

select [聚合函数] 字段名 from 表名 [where 查询条件]　[group by 字段名] [order by 字段名 排序方向]

查询表中至少有两门课程在90分以上的学生信息

select sno,count(*)

from tb_grade

where score>=90

group by sno having count(*)>=2;

查询表中平均成绩大于90分且语文课在95分以上的学生信息

select sno,avg(score)

from tb_grade

where sno in (

select sno

from tb_grade

where score>=95 and pno='语文')

group by sno having avg(score)>=90;

十二. MySQL数据库 -数据库和表的基本操作（一）

第1关：查看表结构与修改表名

查看数据表基本结构

describe表名；

查看数据表详细结构

show create table表名;

修改表名

alter table旧表名 rename新表名;

• 把数据表tb_emp改名为jd_emp；

ALTER TABLE tb_emp RENAME jd_emp;

• 查看该数据库下数据表的列表；

SHOW TABLES;

• 查看数据表jd_emp的基本结构。

DESCRIBE jd_emp;

第2关：修改字段名与字段数据类型

修改字段名

alter table表名 change旧字段名 新字段名 新数据类型;

提示： 如果不需要修改字段的数据类型，可以把新字段的数据类型设置为和原来一样，不要空着它

修改字段数据类型

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

• 把数据表tb_emp的字段Id改名为prod_id，数据类型不变

alter table tb_emp change Id prod_id int(11);

• 把数据表tb_emp字段Name的数据类型改为varchar(30)

alter table tb_emp modify Name varchar(30);

第3关：添加与删除字段

添加字段

ALTER TABLE 表名 ADD 新字段名 数据类型 [约束条件] [FIRST|AFTER] 已存在字段名;

在表的最后一列添加字段

不做[FIRST|AFTER]的位置说明，在添加字段时MySQL会默认把新字段加入到表的最后一列。

eg: 字段prod_country添加到表Mall_products的最后一列

alter table Mall_products add prod_country varchar(30);

在表的第一列添加字段

做FIRST的位置说明

eg:字段prod_country添加到表Mall_products的第一列

alter table Mall_products add prod_country varchar(30) frist;

在表的指定列后添加字段

做AFTER的位置说明，注明添加在哪个字段后面

eg:prod_country添加到表Mall_products的 prod_name字段的后面。

alter table Mall_products add prod_country varchar(30) after prod_name;

删除字段

alter table表名 drop字段名;

eg: 字段prod_price从表Mall_products中删除

alter table Mall_products drop prod_price;

• 在数据表tb_emp的Name字段后添加字段Country，数据格式为varchar(20)

alter table tb_emp addCountry varchar(20) afterName;

• 删除数据表tb_emp中的字段Salary

alter tabletb_emp drop Salary;

第4关：修改字段的排列位置

修改字段的排列位置

ALTER TABLE 表名 MODIFY 字段1 数据类型 FIRST|AFTER 字段2;

字段1指要修改位置的字段，FIRST与AFTER 字段2为可选参数

修改字段为表的第一个字段

做FIRST的位置说明

eg: 把字段prod_price调整到表Mall_products的第一列

alter table Mall_products modify prod_price float first;

修改字段到表的指定列之后

做AFTER 字段2的位置说明

eg: 字段prod_price调整到字段prod_country的后面

alter table Mall_productsmodifyprod_price floatafterprod_country;

• 将数据表tb_emp的Name字段移至第一列，数据格式不变

alter tabletb_emp modify Name varchar(25) first;

• 将DeptId字段移至Salary字段的后边，数据格式不变

alter table tb_emp modifyDeptId INT(11) after Salary;

第5关：删除表的外键约束

删除表的外键约束

ALTER TABLE 表名 DROP FOREIGN KEY 外键约束名;

删除数据表tb_emp的外键约束emp_dept

alter tabletb_emp drop foreign key emp_dept;

十三. MySQL数据库 -数据库和表的基本操作（二）

第1关：插入数据

为表的所有字段插入数据

INSERT INTO 表名 (字段名) VALUES (内容);

insert into MyUser(name,age) values('zhnagsan',18);

为表的指定字段插入数据

insert into MyUser(name) values('lisi'),('fawaikuangtu'),('zhangsan');

• 为空数据表tb_emp同时添加3条数据内容

insertinto tb_emp (Id,Name,Deptid,Salary)

values(1,‘Nancy’,301,2300.00),(2,‘Tod’,303,5600.00),(3,‘Carly’,301,3200.00);

第2关：更新数据

更新表中指定的内容

UPDATE 表名 SET 字段名1 = 内容1, 字段名2 = 内容2, 字段名3 = 内容3 WHERE 过滤条件;

eg: 表Mall_products2 中 Span换成Pakistan,地区代码换为92 。

UPDATE Mall_products2

SET country_name = "Pakistan", country_id = 92

WHERE id = 2;

• 数据表tb_emp中Carly改为Tracy，相应的，301改为302，3200.00改为4300.00

updatetb_emp

setName = “Tracy”, Deptid = 302, Salary = 4300.00

whereId=3;

第3关：删除数据

删除表中的指定行

DELETE FROM 表名 WHERE 条件语句;

eg:表Mall_products2同时删除包含Span和Italy的两行数据内容

deletefrom Mall_products2

where id=2 or id=3;

删除表中的所有行

DELETE FROM 表名;

eg: 同时删除表Mall_products2的所有数据内容

deletefrom Mall_products2;​​​​​​

提示： TRUNCATE TABLE 语句也可以用来删除表中的所有记录。但是与 DELETE 不同的是，TRUNCATE TABLE 语句直接删除的是表，而不是表中的内容，删除结束后还会重新创建一个表。所以它的执行速度会比 DELETE 语句快。 语法为：truncate table表名;

• 将数据表tb_emp中Salary大于3000的数据行删除。

deletefromtb_emp whereSalary>3000;

十四. MySQL开发技巧 – 视图

• 视图的定义

视图（view）是一种虚拟存在的表，是一个逻辑表，本身并不包含数据。通过视图，可以展现基表（用来创建视图的表）的部分数据；视图数据来自定义视图的查询表，使用视图的大部分情况是为了保障数据安全性，提高查询效率。

优点：

①. 简单：使用视图的用户完全不需要关心后面对应的表的结构、关联条件和筛选条件，对用户来说已经是过滤好的复合条件的结果集

②. 安全：使用视图的用户只能访问他们被允许查询的结果集，对表的权限管理并不能限制到某个行或列，但是通过视图就可以简单的实现

③. 数据独立：一旦视图的结构确定了，可以屏蔽表结构变化对用户的影响，源表增加列队视图没有影响；源表修改列名，则可以通过修改视图来解决，不会造成对访问者的影响

• 创建视图

CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]

VIEW view_name [(column_list)]

AS select_statement

[WITH [CASCADED | LOCAL] CHECK OPTION]

参数说明：

1. OR REPLACE：表示替换已有视图；

2. ALGORITHM：表示视图选择算法，默认算法是UNDEFINED(未定义的)： MySQL 自动选择要使用的算法 ；merge合并；temptable临时表；

3. column_list：可选参数，指定视图中各个属性的名词，默认情况下与select语句中查询的属性相同；

4. select_statement：表示select语句；

5. [WITH [CASCADED | LOCAL] CHECK OPTION]：表示视图在更新时保证在视图的权限范围之内；cascade是默认值，表示更新视图的时候，要满足视图和表的相关条件；local表示更新视图的时候，要满足该视图定义的一个条件即可。

• 操作视图

视图是逻辑表，也就是说视图不是真实的表，但操作视图和操作普通表的语法是一样的。用户可以在视图中无条件地使用select语句查询数据。但使用insert、update和delete操作需要在创建视图时满足以下条件（满足以下条件的视图称为可更新视图）：

1. from子句中只能引用有1个表（真实表或可更新视图）；

2. 不能包含 with、distinct、group by、having、limit等子句；

3. 不能使用复合查询，即不能使用union、intersect、except等集合操作；

4. select子句的字段列表不能包含聚合、窗口函数、集合返回函数。

• 删除视图

若视图不再被需要，我们可以将其删除，且视图的删除并不影响源表中的数据

DROP VIEW view_name;

• 在student表上创建视图stu_view，查询math、chinese字段并定义一个math+chinese成绩之和字段，值为两者之和；

CREATE VIEW stu_view

AS select math , chinese , math+chinese

FROM student;

• 在student表和stu_info表上，创建stu_classes视图，查询出stu_id、姓名和班级，查询条件为两表中的stu_id字段相同。

CREATE VIEW stu_classes

ASselect student.stu_id , student.name , stu_info.classes

FROM student , stu_info

WHERE student . stu_id = stu_info . stu_id;

十五. MySQL开发技巧 – 分页和索引

第1关：MySQL 分页查询

limit 和 offset 实现的分页查询

eg: 检索记录行符合条件的10条数据
select * from table where xxx=”xxx” limit 10;

eg: 检索记录行符合条件的11-20条数据

select * from table where xxx="xxx" limit 10 offset 10;

或

select * from table where xxx="xxx" limit 10,10;

提示：limit之后的数字代表偏移量，offset代表返回记录的最大值，可以直接把offset直接省略掉

大数据量下分页查询优化

select * from table where xxx="xxx" limit 100,100;

select * from table where xxx="xxx" limit 1000,100;

select * from table where xxx="xxx" limit 10000,100;

select * from table where xxx="xxx" limit 100000,100;

select * from table where xxx="xxx" limit 1000000,100;

优化

直接定位到偏移量所在记录，先查询到偏移量位置，再进行分页：

select *

from table

where xxx="xxx" and id>=(

select id

from table

where xxx="xxx"limit 100000,1)

limit 100;

• 按prod_id升序查询表products中第6-10行数据,只要求prod_id字段即可（prod_id从1开始）；

select prod_id

from products

limit 5,5;

• 利用子查询优化查询出按prod_id升序的第10-15行数据，只要求prod_id字段即可。

select prod_id

from products

where prod_id>=(

select prod_id

from products

limit 10,1)

limit 5;

第2关：索引（单列索引）

索引是一张特殊的表，该表保存了主键与索引字段，并指向实体表的记录。

单列索引分类和创建

1.主键索引

主键索引一般在建表时创建，不允许有空值并且值唯一，一般会设为 int 而且是 AUTO_INCREMENT 自增类型的

create table `student` (

`ID` int(11) not nullauto_increment,

primary key(`ID`)

) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

2.普通索引

createindexname_index on`student`(`name`);

或

alter table student addindexname_index(`name`);

注：这里的 ` 不是单引号，而是键盘 1 数字左边的符

3.唯一索引

和主键索引类似，要求字段唯一，但是可以允许字段为空

createunique indexname_index on`student`(`name`);

唯一索引可以用来对数据进行强制性要求，可以禁止某表的某个字段出现重复数据

• 创建student表结构并且设置id为主键索引

create table student (

id int(11) not null auto_increment,

primary key(`id`),

name varchar(20) not null,

score int(10)

);

• 对name建立唯一索引 (索引名name_index)

create unique index name_index on`student`(`name`);

• 对score建立普通索引(索引名score_index)

create index score_index on `student`(`score`);

SHOW INDEX FROM student;

第3关：索引（组合索引）

创建组合索引

eg: 创建三个索引：name,name city,name city score

alter table student add index name_city_score (name,city,score);

• 在表person中的name、age、address字段上创建组合索引，索引名为name_city_score

alter table person add index name_city_score(name, age, address);

十六. MySQL数据库 – 其他函数的使用

第1关：字符函数

字符串的常用函数：

函数	作用
CONCAT()	字符串的拼接
CONCAT_WS()	使用指定的字符将目标字符串进行拼接
FORMAT()	数字格式化
LOWER()	字符串全部转换为小写
UPPER()	字符串全部转换为大写
LEFT()	获取左侧的字符串
RIGHT()	获取右侧的字符串
LENGTH()	计算字符串的长度
LTRIM()	删除前导的空格
RTRIM()	删除后续的空格
TRIM()	删除前导和后续的空格
SUBSTRING()	字符串截取
SUBSTRING_INDEX()	分隔字符串
REPLACE()	字符串替换
[NOT] LIKE	模式匹配

1. CONCAT()与CONCAT_WS()

将字符进行连接

• concat()

• concat_ws()

使用指定字符进行连接，最少需要三个参数，第一个参数为指定连接符：

2. left()与right()

左右侧的字符获取，需要两个参数，第一个参数是从哪个字符串中获取，第二个参数是获取的长度

3. LTRIM()、RTRIM() 与 TRIM()

去掉字符串中含有的空格

• ltrim()

去掉其中字符前导的空格

• rtrim()

去掉其中字符后导的空格

• trim()

去掉其中字符全部的空格，删除字符串中自定字符

删除前导" />4.SUBSTRING()、SUBSTRING_INDEX()与REPLACE()

• substring()

用于字符串的截取

从第一位（注意第一位为1而非0）开始截取MySQL的二位字符

• substring_index()

通过特殊符号将字符串分隔

将字符串www.educoder.net通过.进行分隔，可从前后取字符串

• replace()

替换字符串中间自定字符

以首字母大写，其他字母小写的方式显示所有员工的姓名。

select concat(upper( left ( Name, 1 ) ) , lower(substr( Name from 2 ) ) )

as 'Name'

from employee;

第2关：数学函数

数值运算符

常与数值结合使用的函数：

函数	说明
CEIL()	进一（向上）取整
FLOOR()	舍一（向下）取整
POWER()	幂运算
SQRT()	平方根运算
ROUND()	四舍五入
ABS()	绝对值计算
RAND()	生产0-1的随机数
TRUNCATE()	数字截取
DIV	整数除法
MOD	取余数(取模)

1. CEIL()和FLOOR()

• CEIL()函数 : 向上取整
• FLOOR(): 向下取整

2. DIV和MOD

• div整数除法函数

• mod取模函数

3. ROUND()、POWER()、SQRT()和TRUNCATE()

• ROUND()函数：四舍五入

• POWER()函数：幂运算

2^3=8

• SQRT()函数：平方根运算

• TRUNCATE()函数：数字截取，只做截取，不发生四舍五入， 下面示例为：

比较运算符

函数	说明
[NOT] BETWEEN...AND...	[不]在范围内
[NOT] IN()	[不]在列出值范围内
IS [NOT] NULL	[不]为空

• BETWEEN...AND...：指定一个范围，只要选择的数字在这个范围内则为1否则为0

• IN()：指定列出值，只要选择的数字存在其中则为1否则为0

• IS NULL：判断字段是否为空

根据下面表达式修改学生的最终成绩，结果使用四舍五入保留两位小数：

update Score set s_score=TRUNCATE(s_score - ROUND(SQRT((POWER(4,4)-POWER(3,3))/POWER(2,2)),2),2);

第3关：日期时间函数和流程控制类函数

日期函数

常用的函数：

函数	说明
NOW()	当前日期和时间
CURDATE()	当前日期
CURTIME()	当前时间
DATE_ADD()	日期变化
DATEDIFF()	日期差值
DATE_FORMAT()	日期格式化
YEAR()	返回日期的年份
MONTH()	返回日期的月份
WEEK()	返回日期的周号

1. NOW()、CURDATE()和CURTIME()

• now():返回当前日期和时间

• curdate(): 单独返回当前日期
• curtime():返回当前时间

2. DATE_ADD()、DATEDIFF()和DATE_FORMAT()

• date_add():日期的变化，第一个参数为指定时间，第二个参数为增加（减少）的单位值（YEAR、MONTHS、WEEK、DAY）

• datediff(): 返回二个日期之间的差值

• DATE_FORMAT(): 日期格式化, 日期格式转换成另一种格式

以下是在format字符串中常用的说明符:

格式	描述
%d	月的天，数值（00-31）
%H	小时 （00-23）
%h	小时 （01-12）
%m	月，数值（00-12）
%p	AM 或 PM
%s	秒（00-59）
%T	时间，24-小时（hh:mm:ss）
%Y	年，4位

3. YEAR()和MONTH()

流程控制类函数

函数	说明
IF()	判断，流程控制
IFNULL()	字段值是否为空
CASE	搜索语句

• IF()函数：IF(a,b,c)如果a为真返回b，否则返回c

• IFNULL()函数：IFNULL(a,b)如果a不为null返回a，否则返回b

• CASE：CASE WHEN a then b end判断如果a为真返回b。

查询所有学生的出生年份及至2019-01-01时的年龄

select year(s_birth) year,'2019-01-01'-s_birth '年龄' from Student;

查询所有课程中的最高分、最低分、平均分和及格率，按课程id升序输出

select c.c_id 课程id,

c_name 课程名,

max(s_score) 最高分,

min(s_score) '最低分',

round(avg(s_score),2) '平均分',

round((count(s_score>=60 or null)/count(s_score))*100,2) '及格率'

from Score s,Course c

where s.c_id=c.c_id

group by s.c_id;

十七. 数据库E-R图

编程要求

绘制六个模块的 E-R 图，分别为课程、选课、学生、班级、学院、教师。

课程包含以下字段名： 课程编号、课程名称、学时、学分、人数上限、上课时间、上课教室、开课学期、开始选课时间、选课结束时间、课程编号。

选课包含以下字段名： 选课时间、成绩。

学生包含以下字段名： 学号、姓名、密码、性别、出生年月、电话、家庭住址、学生简介。

班级包含以下字段名： 班级编号、班级名称、年度。

学院包含以下字段名： 学院编号、学院名称。

教师包含以下字段名： 教师编号、教师姓名、密码、性别、专业、职称、教师简介。

各个模块之间的关系如下所示：

课程与学生之间的关系为选课：学生与课程的关系为多对多。

学生与班级的关系为所在：学生与班级的关系为多对 1。

学生与学院的关系为所属：学生与学院的关系为多对 1。

班级与学院直接的关系为所属：班级与学院的关系为多对 1。

教师与学院直接的关系为所属：教师与学院的关系为多对 1。

教师与课程的关系为负责：教师与课程的关系为 1 对多。