数据库使用笔记（1）

连接数据库

在命令行中连接（使用前请将mysql安装目录下的bin文件夹添加至系统PATH环境变量）

1
mysql -u root -p

在弹出Enter password提示后输入密码

1
2
3
4
5
6
7
8
9
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 19
Server version: 8.0.28 MySQL Community Server - GPL

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

如果弹出类似信息和提示，则说明连接成功

下面的所有操作默认在已连接数据库的情况下进行

推荐下载DataGrip，详细操作可参考以下博客：

DataGrip使用入门 - atzuge - 博客园 (cnblogs.com)

下面的所有命令将直接在DataGrip中编辑和运行

创建数据库

语法如下：

1
creat database <数据库名>

详细指令：

1
create database if not exists test default charset UTF8MB3;

如果不存在test这个数据库则创建，并设定编码集为UTF8MB3

删除数据库

1
drop database <数据库名>

选择数据库

1
use <数据库名>

数据类型

数值类型

类型 大小 范围（有符号） 范围（无符号） 用途
TINYINT 1 Bytes (-128，127) (0，255) 小整数值
SMALLINT 2 Bytes (-32 768，32 767) (0，65 535) 大整数值
MEDIUMINT 3 Bytes (-8 388 608，8 388 607) (0，16 777 215) 大整数值
INT或INTEGER 4 Bytes (-2 147 483 648，2 147 483 647) (0，4 294 967 295) 大整数值
BIGINT 8 Bytes (-9,223,372,036,854,775,808，9 223 372 036 854 775 807) (0，18 446 744 073 709 551 615) 极大整数值
FLOAT 4 Bytes (-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38) 0，(1.175 494 351 E-38，3.402 823 466 E+38) 单精度
浮点数值
DOUBLE 8 Bytes (-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) 0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) 双精度
浮点数值
DECIMAL 对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2 依赖于M和D的值 依赖于M和D的值 小数值

​ （表格引用自MySQL 数据类型 | 菜鸟教程 (runoob.com)）

日期和时间类型

类型 大小 ( bytes) 范围 格式 用途
DATE 3 1000-01-01/9999-12-31 YYYY-MM-DD 日期值
TIME 3 ‘-838:59:59’/‘838:59:59’ HH:MM:SS 时间值或持续时间
YEAR 1 1901/2155 YYYY 年份值
DATETIME 8 1000-01-01 00:00:00/9999-12-31 23:59:59 YYYY-MM-DD HH:MM:SS 混合日期和时间值
TIMESTAMP 4 1970-01-01 00:00:00/2038结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07 YYYYMMDD HHMMSS 混合日期和时间值，时间戳

​ （表格引用自MySQL 数据类型 | 菜鸟教程 (runoob.com)）

字符串类型

类型 大小 用途
CHAR 0-255 bytes 定长字符串
VARCHAR 0-65535 bytes 变长字符串
TINYBLOB 0-255 bytes 不超过 255 个字符的二进制字符串
TINYTEXT 0-255 bytes 短文本字符串
BLOB 0-65 535 bytes 二进制形式的长文本数据
TEXT 0-65 535 bytes 长文本数据
MEDIUMBLOB 0-16 777 215 bytes 二进制形式的中等长度文本数据
MEDIUMTEXT 0-16 777 215 bytes 中等长度文本数据
LONGBLOB 0-4 294 967 295 bytes 二进制形式的极大文本数据
LONGTEXT 0-4 294 967 295 bytes 极大文本数据

​ （表格引用自MySQL 数据类型 | 菜鸟教程 (runoob.com)）

char和varchar后面所跟参数表示可代表字符的个数，如varchar(14)代表最多表示14个字符（而非字节），char(3, 20)代表可表示长度为3-20的字符串。

创建操作对象

操作对象 操作方式
创建 删除 修改
模式 create schema drop schema
表 create table drop table alter table
视图 create view drop view
索引 create index drop index alter index

例：

1
2
3
4
5
6
7
8
create table Student
(
    Sno     char(9)     PRIMARY KEY, // 表示主码
    Sname   char(20)    unique,
    Ssex    char(2),
    Sage    smallint,
    Sdept   char(20)
);

请注意括号内除最后一行外，每行需要用 “,” 连接

修改操作对象

增加

1
2
ADD[COLUMN] <新列名> <数据类型> [完整性约束]
ADD <表级完整性约束>

  • ADD子句用于增加新列、新的列级完整性约束条件和新的表级完整性约束条件

  • 不管基本表中原来是否已有数据，新增加的列一律为空值

删除

1
2
DROP [ COLUMN ] <列名> [ CASCADE| RESTRICT ]
DROP CONSTRAINT <完整性约束名> [ RESTRICT | CASCADE ]
  • DROP COLUMN子句用于删除表中的列
    • 如果指定了CASCADE短语，则自动删除引用了该列的其他对象（包括表上建立的索引、视图、触发器等一般也将被删除）
    • 如果指定了RESTRICT短语，则如果该列被其他对象引用，关系数据库管理系统将拒绝删除该列
  • DROP CONSTRAINT子句用于删除指定的完整性约束条件

修改

1
[ALTER COLUMN <列名><数据类型> ]
  • ALTER COLUMN子句用于修改原有的列定义，包括修改列名和数据类型

例：

1
alter table Student add S_entrance DATE

添加名为”S_entrance”的新列，且数据类型为DATE

1
alter table Student alter column Sage INT;

更改Sage的数据类型（如果更改后的数据类型表示范围小于原数据类型可能会引发错误）

1
alter table Course add unique(Cname);

为Course添加Cname不可重复的限定条件

数据查询

1
2
3
4
5
6
SELECT [ALL|DISTINCT] <目标列表达式>[,<目标列表达式>] …
FROM <表名或视图名>[,<表名或视图名> ]…|(SELECT 语句)      
AS<别名>
WHERE <条件表达式>
GROUP BY <列名1> [ HAVING <条件表达式> ]
ORDER BY <列名2> [ ASC|DESC ];

  • SELECT子句：指定要显示的属性列

  • FROM子句：指定查询对象（基本表或视图）

  • WHERE子句：指定查询条件

  • GROUP BY子句：对查询结果按指定列的值分组，该属性列值相等的元组为一个组。通常会在每组中作用聚集函数。

  • HAVING短语：只有满足指定条件的组才予以输出

  • ORDER BY子句：对查询结果表按指定列值的升序或降序排序

查询指定列

1
2
select Sname,Sno,Sdept
from Student;

查询全体学生的学号、姓名和所在系

查询全部列

1
2
select * 
from Student;

等价于

1
2
select Sno,Sname,Ssex,Sage,Sdept 
from Student;

查询全体学生的详细记录

查询经过计算的值

1
2
SELECT Sname,2014-Sage          /*假设当时为2014年*/
FROM Student;

查全体学生的姓名及其出生年份

1
2
SELECT Sname,'Year of Birth: ',2014-Sage,LOWER(Sdept)
FROM Student;

同上，单用小写字母表示系名

选择表中的若干元组

查询选修了课程的学生学号。

1
SELECT Sno FROM SC;

等价于：

1
SELECT ALL Sno FROM SC;

即不去重的显示所有结果

如果指定DISTINCT关键词，则会去掉表中重复的行

1
SELECT DISTINCT Sno FROM SC;

查询满足条件的元组

1
2
3
SELECT Sname,Sage 
FROM     Student    
WHERE  Sage < 20;

查询所有年龄在20岁以下的学生姓名及其年龄

1
2
3
SELECT Sname, Sdept, Sage
FROM     Student
WHERE   Sage BETWEEN 20 AND 23;

查询年龄在20~23岁（包括20岁和23岁）之间的学生的姓名、系别和年龄

1
2
3
SELECT Sname, Ssex
FROM  Student
WHERE Sdept IN ('CS','MA’,'IS' );

查询计算机科学系（CS）、数学系（MA）和信息系（IS）学生的姓名和性别。

1
[NOT] LIKE  ‘<匹配串>’  [ESCAPE ‘ <换码字符>’]

a%b表示以a开头，以b结尾的任意长度的字符串

a_b表示以a开头，以b结尾的长度为3的任意字符串

1
2
3
SELECT *    
FROM Student  
WHERE Sno LIKE ‘201215121';

等价于

1
2
3
SELECT  * 
FROM  Student 
WHERE Sno = '201215121';

查询以”DB_”开头，且倒数第3个字符为 i的课程的详细情况

1
2
3
SELECT  *
FROM    Course
WHERE  Cname LIKE  'DB\_%i_ _' ESCAPE '\ ' ;

多重条件查询

使用AND和 OR来连接多个查询条件

查询结果排序

  • ORDER BY子句

    • 可以按一个或多个属性列排序

    • 升序：ASC;降序：DESC;缺省值为升序

    • 对于空值，排序时显示的次序由具体系统实现来决定

聚集函数

函数名 函数功能
COUNT 计算一列中值的个数
COUNT 统计元组个数
SUM 计算一列值的总和
AVG 计算一列值的平均值
MAX 计算一列中的最大值
MIN 计算一列中的最小值 
1
2
select count(*)
from Student;

查询学生总人数

1
2
select count(DISTINCT Sno)
from sc;

查询选修了课程的学生人数（去重）

1
2
3
4
select emp_no from salaries
where salary
group by emp_no
HAVING avg(salary) > 100000;

查询平均工资高于100000的员工编号

对查询结果分组

  • GROUP字句分组：
    • 细化聚集函数的作用对象
      • 如果未对查询结果分组，聚集函数将作用于整个查询结果
      • 对查询结果分组后，聚集函数将分别作用于每个组
      • 按指定的一列或多列值分组，值相等的为一组
1
2
3
select emp_no, count(salary)
from salaries
group by emp_no;

查询每个雇员领取过工资的年份数量

1
2
3
4
select emp_no from salaries
where salary
group by emp_no
HAVING avg(salary) > 100000;

查询平均工资高于100000的员工编号

HAVING短语和WHERE子句的区别：

  • 作用对象不同
  • where子句作用于基表和视图，从中选择满足条件的元组
  • having短语作用于组，从中选择满足条件的组

连接查询

  • 同时设计两个以上的表的查询
  • 连接条件或连接谓词：用来连接两个表的条件
  • 一般格式
    • [<表名1>.]<列名1> <比较运算符> [<表名2>.]<列名2>
    • [<表名1>.]<列名1> BETWEEN [<表名2>.]<列名2> AND [<表名2>.]<列名3>
  • 连接字段：连接谓词中的列名称
    • 连接条件中的各连接字段类型必须是可比的，但名字不必相同
1
2
3
select employees.*, titles.*
from employees, titles
where titles.emp_no = employees.emp_no;

通过emp_no连接employess和titles。

嵌套循环法（NESTED-LOOP）

  • 首先在表1中找到第一个元组，然后从头开始扫描表2，逐一查找满足连接件的元组，找到后就将表1中的第一个元组与该元组拼接起来，形成结果表中一个元组。

  • 表2全部查找完后，再找表1中第二个元组，然后再从头开始扫描表2，逐一查找满足连接条件的元组，找到后就将表1中的第二个元组与该元组拼接起来，形成结果表中一个元组。

  • 重复上述操作，直到表1中的全部元组都处理完毕。

排序合并法（SORT-MERGE）

  • 常用于=连接

  • 首先按连接属性对表1和表2排序

  • 对表1的第一个元组，从头开始扫描表2，顺序查找满足连接条件的元组，找到后就将表1中的第一个元组与该元组拼接起来，形成结果表中一个元组。当遇到表2中第一条大于表1连接字段值的元组时，对表2的查询不再继续。

索引连接（INDEX-JOIN）

  • 对表2按连接字段建立索引。

  • 对表1中的每个元组，依次根据其连接字段值查询表2的索引，从中找到满足条件的元组，找到后就将表1中的第一个元组与该元组拼接起来，形成结果表中一个元组。

1
2
3
select title, employees.*
from titles, employees
where titles.emp_no = employees.emp_no and employees.gender = 'M';

执行过程：

  • 先从employees中挑选出gender为M的元组。
  • 再和titles中满足链接条件的元组进行连接得到最终的结果关系。

自身连接

自身连接：一个表与其自己进行连接。

1
2
3
select F.to_date, F.from_date
from salaries F, salaries S
where F.to_date = s.from_date;

为了区分，需要给表起别名以示区别。

由于所有属性都是同名属性，所以必须使用别名前缀。

外连接

  • 普通连接操作只输出满足连接条件的元组
  • 外连接操作以指定表为连接主体，将主体表中不满足连接条件的元组一并输出
    • 左外连接:列出左边关系中所有的元组
    • 右外连接:列出右边关系中所有的元组 
1
2
select employees.*, titles.*
from employees left join titles on(employees.emp_no = titles.emp_no);

多表连接

两个以上的表进行连接

1
2
3
select employees.*, titles.*, dept_emp.*
from employees, titles, dept_emp
where titles.emp_no = employees.emp_no and dept_emp.emp_no = employees.emp_no;

嵌套查询

一个SELECT-FROM-WHERE语句称为一个查询块。将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询。

上层的查询块称为外层查询或父查询

下层查询块称为内层查询或子查询

SQL语言允许多层嵌套查询，即一个子查询中还可以嵌套其他子查询，子查询不能使用ORDER BY子句

有些嵌套查询可以用连接运算替代

谨慎使用嵌套查询（严重降低效率且容易出错）

不相关子查询

子查询的查询条件不依赖于父查询

由里向外 逐层处理。即每个子查询在上一级查询处理之前求解，子查询的结果用于建立其父查询的查找条件。

相关子查询

子查询的查询条件依赖于父查询

首先取外层查询中表的第一个元组，根据它与内层查询相关的属性值处理内层查询，若WHERE子句返回值为真，则取此元组放入结果表

然后再取外层表的下一个元组

重复这一过程，直至外层表全部检查完为止

例子：

查询和Georgi Facello同一个部门的所有员工的编号和姓名

我们首先要先通过employees表用Georgi Facello查到他的编号，在通过dept-emp表用他的编号查到他的部门号，在用部门号查到剩下的同一部门的所有人的编号，在用这些编号返回employees查到姓名

嵌套查询：

1
2
3
4
5
6
7
8
9
10
11
select dept_emp.emp_no, first_name, last_name
from employees, dept_emp
where dept_no in (
    select dept_no
    from dept_emp
    where dept_emp.emp_no in (
        select employees.emp_no
        from employees
        where first_name='Georgi' and last_name='Facello'
        )
    ) and employees.emp_no = dept_emp.emp_no;

我们可以看到，最中间的

1
2
3
select employees.emp_no
from employees
where first_name='Georgi' and last_name='Facello'

结果为

image-20220429152811022

可以看到有两位叫这个名字的员工，我们使用他们的编号查询对应部门：

1
2
3
4
5
6
7
select dept_no
from dept_emp
where dept_emp.emp_no in (
    select employees.emp_no
    from employees
    where first_name='Georgi' and last_name='Facello'
    )

结果为

image-20220429152906860

最后我们通过这个部门编号查员工编号并从employees中查姓名

1
2
3
4
5
6
7
8
9
10
11
select dept_emp.emp_no, first_name, last_name
from employees, dept_emp
where dept_no in (
    select dept_no
    from dept_emp
    where dept_emp.emp_no in (
        select employees.emp_no
        from employees
        where first_name='Georgi' and last_name='Facello'
        )
    ) and employees.emp_no = dept_emp.emp_no;

结果为

image-20220429153015198

可用连接查询将中间两层子查询优化为：

1
2
3
4
5
6
7
select dept_emp.emp_no, first_name, last_name
from employees, dept_emp
where dept_no in (
    select dept_no
    from dept_emp, employees
    where dept_emp.emp_no = employees.emp_no and first_name='Georgi' and last_name='Facello'
    ) and employees.emp_no = dept_emp.emp_no;

结果与上文一致。

带有比较运算符的子查询

当能确切知道内层查询返回单值时，可用比较运算符（>，<，=，>=，<=，!=或< >）。

带有ANY（SOME）或ALL谓词的子查询

使用ANY或ALL谓词时必须同时使用比较运算

语义为：

  • n> ANY 大于子查询结果中的某个值
  • n> ALL 大于子查询结果中的所有值
  • n< ANY 小于子查询结果中的某个值
  • n< ALL 小于子查询结果中的所有值
  • n>= ANY 大于等于子查询结果中的某个值
  • n>= ALL 大于等于子查询结果中的所有值
  • n<= ANY 小于等于子查询结果中的某个值
  • n<= ALL 小于等于子查询结果中的所有值
  • n= ANY 等于子查询结果中的某个值
  • n= ALL 等于子查询结果中的所有值（通常没有实际意义）
  • n!=（或<>）ANY 不等于子查询结果中的某个值
  • n!=（或<>）ALL 不等于子查询结果中的任何一个值

集合查询

集合操作的种类

  • 并操作UNION

  • 交操作INTERSECT

  • 差操作EXCEPT

参加集合操作的各查询结果的列数必须相同；对应项的数据类型也必须相同

并操作UNION

  • UNION：将多个查询结果合并起来时，系统自动去掉重复元组

  • UNION ALL：将多个查询结果合并起来时，系统自动去掉重复元组

eg：查询employees表中emp_no为10001或者salaries表中salary大于10000的值

1
2
3
4
5
6
7
select employees.emp_no, first_name, last_name, salary
from employees, salaries
where salary > 10000
union
select employees.emp_no, first_name, last_name, salary
from employees, salaries
where employees.emp_no = 10001;

等价于：

1
2
3
select employees.emp_no, first_name, last_name, salary
from employees, salaries
where salary > 10000 or employees.emp_no = 10001;

一般情况下能使用and和or连接的尽量使用and和or。不仅语法更加简洁，而且效率更高。

image-20220512115748180

image-20220512115810690

交操作INTERSECT

等价于and

差操作EXCEPT

可以使用and + 否原条件替代

基于派生表查询

子查询不仅可以出现在WHERE子句中，还可以出现在FROM子句中，这时子查询生成的临时派生表（Derived Table）成为主查询的查询对象

eg: 找出每个员工薪水高于平均薪水的年限

1
2
3
4
5
select emp_no, salary, from_date, to_date
from salaries,(select emp_no, AVG(salary) from salaries
    group by emp_no)
    as Avg_salary(avg_no, avg_salary)
where salary > Avg_salary.avg_salary;

我们可以看到，中间的嵌套的select部分：

1
2
3
(select emp_no, AVG(salary) from salaries
    group by emp_no)
    as Avg_salary(avg_no, avg_salary)

将查询出来的emp_no和salary的平均值作为一个新的派生表加入查询条件中。

如果子查询中没有聚集函数，派生表可以不指定属性列，子查询SELECT子句后面的列名为其缺省属性（即内层select查询出的列名）

eg：找出所有Marketing部门的成员姓名

1
2
3
4
select dept_emp.emp_no, first_name, last_name;
from employees, dept_emp, (select dept_no from departments
    where dept_name = 'Marketing') as Dept
where Dept.dept_no = dept_emp.dept_no and dept_emp.emp_no = employees.emp_no;