Skip to content

Latest commit

 

History

History
2364 lines (1546 loc) · 75.7 KB

3. MySQL命令.md

File metadata and controls

2364 lines (1546 loc) · 75.7 KB

目录

[TOC]

安装

检查安装

rpm -qa | grep mysql

linux管理命令

systemcrl (start/restart/stop) mysql.service

service mysqld start 			
systemcrl start mysql.service  			开启MySQL服务
（只要没有错误信息就表示已经正常启动了）
systemcrl stop mysql.service			关闭MySQL服务
systemctl restart mysql.servic			重启MySQL服务 
service mysqld status 					查看服务状态

systemcrl enable mysql.service 			设置开机自启动(一般默认都开着)

Windows

管理员身份启动cmd

C:\WINDOWS\system32> net start mysql57 			启动
C:\WINDOWS\system32> net stop mysql57 			关闭

目前主要使用两种框架方式：

  • LAMP
  • WAMP

SQL语言

结构化查询语言，简称SQL（Structured Query Language），是一种特殊目的的编程语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统；同时也是数据库脚本文件的扩展名。

核心SQL的四个组成部分：

  1. 数据定义语言（DDL）：用来定义数据库的表、库、列等对象。保留字：create、alter、drop、truncate。
  2. 数据操纵语言（DML）：用来操纵数据库记录。保留字：insert、update、delete。
  3. 数据控制语言（DCL）：用来定义访问权限和安全级别。保留字：commit，rollback、savepoint、grant、revoke。
  4. 数据查询语言（DQL）：用来查询记录。保留字：select、where、order by、group by、having。
  5. 嵌入式SQL语言的使用规则

img

注意：MySQL中不分大小写

SQL的特点：

  • 综合统一：集数据定义语言DDL、数据操纵语言DML、数据控制语言DCL的功能于一体，语言风格统一，可以独立完成数据库生命周期中的全部活动。定义关系模式，建立数据库，对数据进行查询和更新，数据库重构和维护，数据库完整性安全性控制等。可以在不影响数据库运行的情况下对数据库进行模式的修改。实体及实体间的联系都用关系表示，数据结构的单一性带来操作符的统一，简化了操作。
  • 高度非过程化：用户只需提出“做什么”，无须告诉“怎么做”，不必了解存取路径，路径的选择及SQL的操作过程由系统自动完成，减轻了用户的负担，有利于提高数据独立性。
  • 面向集合的操作方式：集合SQL采用集合操作方式，操作对象和操作结果都是关系 --- 元组，可以一次插入、删除、更新整个集合的元组。
  • 两种使用方式，统一的语法结构：SQL既是自含式语言(用户使用)，又是嵌入式语言(程序员使用), 不同的使用方式, 但语法结构基本是一致的。
  • 语言简洁，语法简单，接近英语口语，易学易用：SQL完成核心的功能只用了9个命令动词。

基本表：基本表是独立、存在的表，SQL中一个关系就对应一个基本表（基表）；一个基表上可以带多个索引，索引存放在存储文件里；

视图：视图是一个或多个基表导出的表，数据库中只存放视图定义以及与其关联的基本表名等信息，而不存放视图对应的数据；数据仍存放在导出视图的基本表中，因此视图又称为虚拟表。

存储文件：由多个基本表、视图、相关索引和存储过程等数据库对象组成；存储文件的逻辑结构对应数据库的内模式。

  1. 数据库
  2. 表
  3. 字段(列)：某一个事物的一个特征，或者说是属性。
  4. 记录(元组)：事物特征的组合，可以描述一个具体的事物。一整行。
  5. 数据(约束)

例句标识：

[]可省略

{|}左右任意选择

db_name库名称

tbl_name表名称

col_name字段，数据列名列表

expr常量、变量、表达式

select_expr要查询的内容

1.使用

登录mysql

mysql -p

登录mysql的root

mysql -u root -p

退出

mysql> ctrl+c
mysql> exit

授予权限

grant privileges on dbname.tablename to‘username’@’host’
  1. privileges：可以是SELECT，INSERT，UPDATE等，如果要授予所的权限则用ALL
  2. dbname：数据库名
  3. tablename：表名，若要授予该用户对数据库中所有表的操作权限则可用表示，如.*

例如：grant select on BookStore.* to UserA

撤销权限

revoke privilege on dbname.tablename from ‘username’@’host’;

使用

use mysql;

设置/重置MySQL登录密码

set password = password('我是密码');

新刷系统权限

flush privileges;

查看所有编码字符

show variables like "%character%";

2.DATABASE

查看所有库

  • like关键字用于匹配名称
  • where关键字用于指定查找范围条件
SHOW DATABASES [LIKE 'pattern'|WHERE expr];

【注意】是 databases 不是 database。

新建数据库

  • if not exists表示查看是否已存在，避免出现创建已存在的数据库时出现的错误
create database 数据库名称;
CREATE SCHEMA 库名称;

CREATE {DATABASE|SCHEMA} [IF NOT EXISTS] db_name

修改数据库

ALTER DATABASE 库名称;

删除数据库

  • IF EXISTS判断是否存在，避免出现删除不存在的数据库时出现的错误
drop database [IF EXISTS] 数据库名称;

连接数据库

use 数据库名称;

查看当前数据库信息

status;
--------------
mysql  Ver 14.14 Distrib 5.7.30, for Win64 (x86_64)

Connection id:          3
Current database:
Current user:           root@localhost
SSL:                    Cipher in use is ECDHE-RSA-AES128-GCM-SHA256
Using delimiter:        ;
Server version:         5.7.30 MySQL Community Server (GPL)
Protocol version:       10
Connection:             localhost via TCP/IP
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    utf8mb4
Conn.  characterset:    utf8mb4
TCP port:               3306
Uptime:                 1 day 2 hours 17 min 39 sec

Threads: 1  Questions: 5  Slow queries: 0  Opens: 105  Flush tables: 1  Open tables: 98  Queries per second avg: 0.000
--------------
1.当前链接的id号
2.当前选择的使用的数据库
3.当前链接的登录用户
4.是否使用SSL
5.行终结符是分号
6.当前mysql服务器的版本号
7.协议版本
8.使用的链接类型
9.服务器使用的字符类型
10.数据库使用的字符类型
11.客户端使用的字符类型
12.链接使用的字符类型
13.链接端口号
14.时间

3.TABLE

类型 描述
CHAR(固定长度) 表示字符串类型, 数字
VARCHAR(变长长度) 可以存放数字，字母
INT(长度) 整型
FLOAT/DOUBLE 浮点类型
BOOL 布尔型
DATE 表示日期类型(不包含时分秒)
DATETIME 时间戳：表示时间类型(包含时分秒)
TIME 时间型
TEXT 放大文本字符串
BLOB 用于存放二进制文件,例如图片,电影,音乐

char与varchar后面接的数据大小为存储的字符数，而不是字节数。

char定义的是固定长度，长度范围为0-255，存储时，如果字符数没有达到定义的位数，会在后面用空格补全存入数据库中。

varchar是变长长度，长度范围为0-65535，存储时，如果字符没有达到定义的位数，也不会在后面补空格。

创建表的操作属于**DDL(数据库定义语言)**操作，所以是由命名要求的，对于表名称以及列名称的定义要求如下：

  1. 必须字母开头
  2. 长度为1~30个字符
  3. 对于同一用户不能用相同的表名称
  4. 不能使用保留字

创建表create

（表创建定义+表选项+分区选项...）

create table 表名称(
	字段名称 数据类型 [DEFAULT 默认值],
	字段名称 数据类型 [DEFAULT 默认值],
	其他字段...,
    [表级完整性约束条件]
)[engine=引擎类型];


创建表例子：
mysql> create table user_1(
    -> id int AUTO_INCREMENT PRIMARY KEY,
    -> name varchar(10) NOT NULL,
    -> sex char(5) DEFAULT 0,
    -> age int,
    -> )
    -> ;

  • TEMPORARY关键字表示该语句创建的是临时表，默认是持久表

  • AUTO_INCREMENT关键字可以为表中的int（整型）列设置自增属性。每个表只能有一个自增列，并且它必须被索引

  • DEFAULT关键字可以给列设置默认值

  • NOT NULL关键字表示列不允许为空值，默认为NULL，表示可以为空

  • PRIMARY KEY关键字设置表的主键，主键必须唯一，必须是NOT NULL，如果有多个主键，则用都逗号隔开。

    PRIMARY KEY(id)
PRIMARY KEY(id, id2)

复制其他表结构

-- 只复制表结构
CREATE TABLE table2 LIKE table1;

-- 复制表结构和表内容
CREATE TABLE copy_table1 AS (select * from table1);

查看库所有表show

SHOW [FULL] TABLES [FROM 库名称] [LIKE 'pattern'|WHERE expr];

-- 例如：
show tables;

删除表drop

drop table 表名;

-- 例
drop table if exists user;

if exists：判断存在，防报错。

查看表信息desc

SHOW [FULL] COLUMNS {FROM|IN} 表名 [{FROM|IN} 数据库名] [LIKE 'pattern'|WHERE expr];

-- 或者简写：
{DESCRIBE|desc} 表名称;


-- 例如
describe user;
desc user;

查看创建信息

show create table 表名

-- 例如
show create table user;

重命名表rename

rename table 旧表名 to 新表名;

alter table 旧表名 rename [to] 新的表名;

-- 例
rename table user to user1;

修改表字段alter

字段（属性，列）

表中添加字段ADD

alter table 操作的表名 
add [column] 字段名称 字段类型 [NOT NULL] [default 默认内容][添加位置（after 后者字段）];

-- 例如
ALTER TABLE user1 ADD sx char(1) null;
  • 添加位置那里，AFTER则会添加到后者字段的后面，还可以使用first添加到表的第一列，否则则默认添加到末尾。

修改字段名CHANGE

alter table 表名字 
change [column] 字段原名 字段新名 字段类型 [NOT NULL][DEFAULT],
change 字段原名2 字段新名2 字段类型2;

-- 例
ALTER TABLE user1 CHANGE sx sxl char(1) null;

可以同时修改多个字段，只需要用逗号隔开

修改默认值ALTER

alter table 表 ALTER [COLUMN] 字段名 SET DEFAULT 默认值;

-- 例
alter table user1 ALTER sxl SET DEFAULT 0;

修改字段类型MODIFY

只能修改字段类型，通过FIRST和AFTER在表中的位置，不能修改字段名

alter table 表 modify [COLUMN] 字段名 字段类型 FIRST;

-- 例
alter table user1 MODIFY sxl char(2);

-- 放到第一个字段
alter table user1 MODIFY sxl char(2) first;

删除字段DROP

alter table 表名 drop [COLUMN] 字段名;

-- 例
ALTER TABLE user1 DROP sxl;

4.数据更新

插入数据INSERT

  • IDENTITY属性的，系统会自动生成序列号值来唯一标识
  • DEFAULT默认属性，可以填DEFAULT来设置为默认值
  • AUTO_INCREMENT自增属性的，在insert语句中最后只会引用0，所以可以直接填0
insert [into] 表(列1,列2) {values|value} ('内容','内容');

-- 例子
insert into user1(id,name) values(1,'admin');

-- 如果插入所有数据，可以不指定插入的字段名：但是带有字段名更加安全。
insert into user1 values(0,'admin',1,DEFAULT);

插入部分列值数据：

INSERT 表 SET 列="内容";

插入子查询数据

INSERT 表 select ...

修改数据UPDATE

update 表 set 列名1='内容', 行名2='内容' [where 条件][order by...][limit...];

-- 例子
update user1 set name='user' where id=1;

删除数据DELETE

delete删除的是表内的数据，不是关于表的定义

delete from 表 [where 条件][order by...][limit...];

-- 例子:
delete from user1 where id=0010;

-- 清空表:
delete from user1;

-- truncate table
truncate table emp;

delete 与 truncate 命令的区别

相同

  • 二者都能删除表中的数据
  • 一般情况下， 主流的关系型数据库都支持这两个命令

不同

  1. SQL 性质的不同
  • delete：属于 DML(数据操作语言) 范畴
  • truncate：属于 DDL(数据定义语言) 范畴
  1. 操作对象不同
  • delete：能删除表中或基于真实表创建的视图中的数据
  • truncate：只能删除表中的数据，无法应用在视图上
  1. 条件限制区别
    • delete：删除表中数据时，可以通过 where 关键字，进行选择性s删除
    • truncate：不能使用 where 关键字进行选择性删除
  2. 事务
    • delete：删除表中数据时，涉及到事务处理：回退（缓存）、提交、撤退。会存放日志。所以delete更安全。
    • truncate：删除缓存时，不涉及缓存事务处理。
  3. 删除效率
    • delete：由于 delete 删除数据时要考虑事务管理（缓存、回退机制、日志记录），所以当删除大批量数据时，速度慢，效率低，甚至达不到删除的目的。
    • truncate：删除大批量数据时，速度快、效率高、但无法撤销。
  4. 激活触发器
    • delete：在进行删除操作时，可能激活触发器的处理
    • truncate：不存在激活触发器处理
  5. 外键约束
    • delete：可以运用 SQL 语句、按照业务逻辑、按照一定的先后顺序可以删除相关表中的数据
    • truncate：不能删除对于由 foreign key` 约束引用的表，不能删除该表中的数据

应用场景：

  1. 需要根据一定的业务条件删除数据时、且数据量、性能可控的情况下，可以考虑使用 delete from xxx where 业务条件
  2. 需要考虑事务提交、撤销的管理机制时，可以使用 delete
  3. 当需要删除大批量数据时，同时要求速度快，效率高并且w无需撤销时，可以使用 truncate
  4. 另外，在企业级开发中，实际上都是进行逻辑删除（将数据进行删除标识处理）、而并不进行物理上的删除
  5. 在实际生产环境中，一般情况下删除业务处理（过渡表）中的数据
  6. 在实际企业开发、维护过程中，不管使用 delete 还是 truncate 命令前，都要考虑数据的备份

5.数据查询SELECT

select可以进行各种运算，例如执行下面的语句：

select 1+1;

会返回数值2

必须按照下面的顺序：

SELECT [ALL|distinct|distinctrow] 字段名 FROM 表名
[WHERE <条件>]

[group by <列>]
[having <条件表达式>]
[order by <列> [ASC|DESC]]

[limit <记录数>]
[union]
  • distinct和distinctrow是同义词，用于消除查询结果集中的重复的数据，没有指定这些选项则默认为ALL。

SELECT子句：指定要显示的属性列。 FROM子句：指定查询对象（基本表或视图）。 WHERE子句：指定查询条件。 GROUP BY子句：对查询结果按指定列的值分组，该属性列值相等的元组为一个组。通常会在每组中使用聚集函数。 HAVING短语：只有满足指定条件的组才予以输出。 ORDER BY子句：对查询结果表按指定列值的升序或降序排序。 LIMIT 记录数：LIMIT是可选参数，用于限制查询结果的数量。记录数表示返回查询记录的条数。

SELECT 可以完成投影、选择、连接。

在SELECT语句中，SELECT子句与FROM子句是不可缺少的，其余的是可选的。

关系运算

关系代数是一种过程化查询语言。它包括一个运算的集合，这些运算以一个或两个关系为输入，产生一个新的关系作为结果。关系代数的基本运算有：

名称 英文 符号
选择 select σ
投影 project Π
并 union ∪
集合差 set-difference -
笛卡儿积 Cartesian-product ×
更名 rename ρ

除了上面的6种基本运算之外，还有一些其他运算，其他运算是可以用基本运算来表示的。但是在实际中，我们为了方便使用，单独的建立了一种运算来表示，其他运算有：

名称 英文 符号
集合交 intersection ∩
自然连接 natural join ⋈
赋值 assignment ←

选择σ

英文： select 字符： σ 选择运算在关系中选择出能够满足给定谓词的元组。将那些不满足谓词的元组剔除，组成一个新的关系。在σ后面小写谓词代表查询条件，括号中写要操作的关系。可以使用=，≠，＞，＜，≤，≥来进行谓词的判断。另外，也可以使用and(∧)or(∨)not(﹁)将多个谓词组合成一个较大的谓词。 $$ σ_{条件}(表) $$

$σ_{salsry&gt;10000}(instructor)$：从集合instructor中取出工资salary大于10000的大佬。

$σ_{age&gt;20∧salary&gt;10000}(User)$：在User关系中查找出年龄大于20并且工资高于10000的所有元组并返回这些元组组成的关系

你可能更加熟悉SQL，同样的操作，写成SQL就是

select * from instructor where salary > 2000;

投影∏

英文： project 字符： Π 如果我们只对一个关系中的某些属性感兴趣而不是所有，那么我们使用投影关系来选择出我们想要的属性，投影关系返回一个仅含有这些属性的关系。因为关系是集合，所以将返回的关系中所有重复的元组将被剔除。 $$ π_{字段}(表)\ 或者:\ ∏_{字段}(表) $$

如想从集合instructor中取出姓名name和性别gender，写成关系代数就是：$∏_{name,gender}(instructor)$。

关系代数和SQL语句一样，可以「嵌套」，写法非常自然。从上面条件筛查出来的高工资大佬中拿到他们的姓名和性别，写成关系代数就是：$∏_{name,gender}(σ_{salsry>10000}(instructor))$。

并∪

英文： union 字符： ∪ 有时我们需要把两个关系中的内容联系起来，或者一个关系经过不同的查询，我们希望把结果联系在一起。这就要使用并运算。没有什么不同的，和集合中的并很相似。需要注意的是，并运算处理的两个关系必须具有相同的属性，否则并运算将没有意义。

$Π_{name}(σ_{age&gt;18}(User))∪Π_{name}(σ_{address="NewYork"}(Home))$ ：在User关系中找出所有年龄大于18的姓名，在Home关系中找出所有家在NewYork的人的姓名，将这两个关系取并集，得到一个并关系。

差 -

英文： set-difference 字符： - 我们用一个关系A差运算另个一个关系B，表示剩下A中有但是B中没有的元组组成的关系。和并运算相同的，我们必须保证-运算在相容的关系之间进行。

$Π_{name}(σ_{age&gt;18}(User))-Π_{name}(σ_{address="NewYork"}(Home))$ 在User关系中找出所有年龄大于18的姓名，在Home关系中找出所有家在NewYork的人的姓名，得到User中存在而Home中不存在的人的姓名的关系。

交∩

英文： intersection 字符： ∩ 集合交运算表示出在A和B两个关系中都存在的元组的新关系。A和B两个元组应该是属性相同的。交运算是其他运算而不是基础运算。我们可以通过 A-(A-B) 得到A∩B。

$Π_{name}(σ_{age&gt;18}(User))∩Π_{name}(σ_{address="NewYork"}(Home))$ 在User关系中找出所有年龄大于18的姓名，在Home关系中找出所有家在NewYork的人的姓名，将这两个关系中都存在的姓名取出作为新的关系。

笛卡尔乘积×

英文： Cartesian-product 字符： × 有时我们需要把两个不相关的关系连接起来，但是这两个关系之中的属性却各不相同。对于这种不相容的情况我们不能使用交并差运算。笛卡儿乘积，用第一个关系A中的一个元组和第二个关系B中的所有元组结合，创造出一条包含着所有属性的新元组（如果在两个关系中有同名属性c，则在新关系中用A.c和B.c分别来表示）。这样得到的新关系中一共有A的元组数乘以B的元组数条信息。

img

如上图所示，最右边的表是左边两个表的乘积。表1的每一行一共可以和表2的3行组合，表1一共3行，故最终一共3×3=9行。

若两个表做笛卡尔乘积，结果集的行数是两个表行数的乘积。

User × Home 将User关系和Home关系做笛卡儿乘积运算

连接⋈

英文： natural join 字符： ⋈ 有的时候我们需要把两个属性并不是完全相同的关系连接在一起，就像笛卡儿积做的那样。但是我们又不想直接使用笛卡儿积，因为这种方法实在是耗时耗力，我们希望得到更为简练有效的数据。这时我们就需要自然连接。自然连接将两个关系A和B的共同属性作为根本，将两个表中这些共同属性的值都相同元组拼接在一起作为一个新的元组，而将剩下不能拼接的部分全部舍弃，这样得到一个新的关系。

自然连接也是一个其他运算。我们可以先将A和B关系做笛卡儿积，然后基于两个关系中相同的属性做相等性判断，去掉不等的元组，最后将属性简化，得到自然连接的结果。

不过在操作多个表的时候，通常使用连接，而不是笛卡尔积。 $$ R>{iθj}<S=σ{iθj}(R×S) $$

R、S代表两个字段； i、j代表两个字段第i列和第j列的属性； θ代表运算符； ×笛卡尔积。

连接两个表格，只需要筛选结果集就可以了。如有两个表，一个表示授课教师instructor，一个是课程信息course，他们分别包含name属性和teacher属性，都表示「教师姓名」。

$⋈_{instructor.name=course.teacher}(instructor,course)$

User ⋈ Home 将User关系和Home关系做自然连接

更名ρ

英文： rename 字符： ρ 读音： rho 关系表达式的结果没有给我们能够引用的名字。如果想要引用一个产生的关系，最基础的就是把这句语句再写一遍，但是这种方法不是很好的。我们可以通过ρ表示更名或是命名，为一个关系起个名。

$ρ_{adult}(Π_{name}(σ_{age&gt;18}(User)))$将User关系中所有年龄大于18的人的姓名取出作为一个新的关系，并把这个关系起名为adult。

赋值运算←

英文： assignment 字符： ← 实际上，赋值和更名很像，只不过赋值可以单独的写在一句话中，下面可以使用箭头左侧的名字作为右边关系的表示。

temp1 ← $Π_{name}(σ_{address=”NewYork”}(Home))$ temp2 ← $Π_{name}(σage&gt;18(User))$ temp1 ∩ temp2

在User关系中找出所有年龄大于18的姓名，在Home关系中找出所有家在NewYork的人的姓名，将这两个关系中都存在的姓名取出作为新的关系。

例题：

S(SNO, SN, AGE, SEX) C(CNO, CN, CREDIT) SC(SNO, CNO, SCORE)

  1. 查询课程号为C1的学生的学号和成绩：

$∏_{SNO,SCORE}(σ_{CNO='C1'}(SC))$

  1. 查询课程号为C2的学生的学号和姓名：

$∏_{SNO,SN}(σ_{CNO='C2'}(S⋈SC))$

或者写为（先查找C2的学生号，在连接学生表S）：

$∏_{SNO,SN}( ∏_{SNO}(σ_{CNO='C2'}(SC))⋈S)$

课件上是这个：$∏_{SNO,SN}( ∏_{SNO,CNO}(σ_{CNO='C2'}(SC))⋈S)$

  1. 查询课程名字为maths的学生的学号和姓名：

$∏_{SNO,SN}(σ_{CN='maths'}(S⋈SC⋈C))$

  1. 查询课程号为C3或C4的学生的学号：

$∏_{SNO}(σ_{CNO='C3'∨CNO='C4'}(SC))$

  1. 查询没有选修课程号为C5的学生的学号，姓名：

$∏_{SNO,SN}(S)-∏_{SNO,SN}(σ_{CNO='C5'}(SC⋈S))$

所有数据

使用*通配符

select * from 表名;

投影

显示部分数据（列）

select 字段名 from 表名;

-- 例子：
select id, name from user1;

别名AS

在打出e.后会自动出现后面的英文字段名，方便。

【注意】AS可以省略。

select 别名.字段 [AS] 列别名, 别名.字段 [AS] 列别名 from 表 别名;

-- 例子：
select e.sex, e.name from user1 e;
select e.sex as'性别', e.name '名字' from user1 e;

数据别名例子：

-- 这个user1表中所有记录的sex字段的值，但是会将'0'转换为'女'，将其他值（假设是'1'）转换为'男'，并将结果列命名为性别。

SELECT
    CASE   
        WHEN e.sex = '0' THEN '女'  
        ELSE '男'  
    END AS '性别'
FROM user1 e;

连接

select concat("内容1:", 字段, "内容2:", 字段) as '别名' from 表;

-- 例子：
select concat("内容1:", name, "内容2:", job) as '工作信息' from user1;

-- 则显示：
工作信息
内容1:name内容2:job

限定查询WHERE

运算符

运算符分类 运 算 符
比较运算符 >、>=、<、<=、<>、!=、!>、!<
范围运算符 BETWEEN…AND、NOT BETWEEN…AND
列表运算符 IN、NOT IN
模式匹配符 LIKE、NOT LIKE
空值判断符 IS NULL、IS NOT NULL
逻辑运算符 AND、OR、NOT

比较运算符

比较运算符 说明
= 等于
<>、!= 不等于
< 小于
<= 小于等于
> 大于
>= 大于等于
<=> 不会返回unknown

例：

select * from 表 where 条件;

select * from emp where sal>=2000 and sal<=8000;
select * from emp where sal <> '2000';				-- 这里<>是!=的意思
  1. between...and...

仅限于数值、日期、时间

SELECT * FROM emp WHERE sal BETWEEN 2000 AND 8000;
  1. IN
select * from emp where sal in(2000,8000); -- 2000和8000
select * from emp where sal=2000 or sal=8000;
  1. NULL
select * from emp where sal not in(2000);			-- 不是2000的其他数据
select * from emp where comm is not null;			-- comm不是空
select * from emp where comm is null;				-- comm是空
  1. 模糊查询Like
select * from 表 where 字段 like 'm%';					-- 以 s 开头的
select * from emp where job like '_m%';				  -- 第二的字母是m
select * from emp where job like '%m%';				  -- 包含 m 的
select * from emp where job not like 'm%';			  -- 不是m开头的

分组查询GROUP BY

将结果集的数据行根据选择列的值进行逻辑分组，汇总表内容的子集，即实现对每个组的的聚合计算。 例如对每门课程成绩的平均分等。

SELECT 1,2 count<*> as '总数' FROM 表 group by {col_name|expr|position} [ASC|DESC], ...[WITH ROLLUP];

-- 求各个课程号及相应的选课人数。
-- GROUP BY Course_no; 就是按照Course_no进行分组。
SELECT Course_no, COUNT(Stu_no)
FROM Score
GROUP BY Course_no; 

-- 查询班级号为“18010101”的班级的男生和女生的人数。
SELECT 
	stu_sex as 性别,
	count(stu_sex) as 人数 
FROM student
WHERE class_no='18010101'
GROUP BY stu_sex;
  • ASC表示升序分组，默认是升序
  • DESC表示降序分组
  • with rollup指定在结果集中不仅包含分组后的数据行，还包含汇总行，以及所有分组的整体汇总行

如果关键字GROUP BY后面只有一个字段，则数据将按该字段的值进行分组。 使用GROUP BY可以对多个字段按层次进行分组，首先按第一个字段分组，然后在第一个字段值相同的每个分组中再根据第二个字段值进行分组，依次类推。

-- 在学生表student中按班级统计出男生和女生的平均年龄及人数。
SELECT 
	class_no as 班级, 
	stu_sex as 性别,
	AVG(YEAR(now())-Year(stu_birth)) AS  平均年龄,
	COUNT(*) AS 人数
FROM student
GROUP BY class_no,stu_sex;

这里第一个分组是class_no，第二个分组是stu_sex。

过滤分组HAVING

GROUP BY子句常和HAVING子句配合使用。HAVING子句只能出现在GROUP BY子句后，用于将分组后的结果进行条件筛选。

都是进行条件筛选功能的子句，WHERE 句和 HAVING子区的区别表现在：

  1. WHERE子句设置的查询筛选条件在GROUP BY子句之前发生作用，并且条件中不能使用集合函数；
  2. HAVING子句设置的筛选条件在GROUP BY子句之后（分组之后）发生作用，并且条件中允许使用集合函数。

在HAVING子句中可以使用集合函数，并且其统计运算的集合是组内的所有列值，而WHERE子句中不能使用集合函数。

SELECT 1,2 FROM 表 GROUP BY 1,2 HAVING 条件;

例子：

-- 查询成绩表socre中平均成绩大于等于80分的学生的学号、平均分。
SELECT stu_no AS 学号, AVG(score) AS  平均成绩
FROM score
GROUP BY  stu_no
HAVING AVG(score)>=80;

排序ORDER BY

数据行的排列顺序是按它们在表中的顺序进行排列的。可以使用ORDER BY子句对结果集中的数据行按指定列的值重新排列顺序。

-- 升序：由小到大排序(默认是asc)
select * from 表 order by 字段 (ASC);
-- 降序：由大到小排序(desc)
select * from 表 order by 字段 desc;

-- 例子：
SELECT * FROM 表 ORDER BY 字段1 DESC, 字段2 DESC;

【注意】对于空值，（空值看作无穷大）

  1. 若按升序排，空值的元组将最后显示。
  2. 若按降序排，空值的元组将最先显示。

例子：

-- 按年龄从小到大的顺序显示女学生的姓名、性别及出生时间。
SELECT stu_name,stu_sex,stu_birth
FROM student
WHERE stu_sex='女'
ORDER BY stu_birth DESC;

-- 查询成绩表socre中平均成绩大于等于80分的学生的学号、平均分，并按平均成绩升序。
SELECT stu_no AS 学号, AVG(score) AS 平均成绩
FROM score
GROUP BY  stu_no
HAVING AVG(score)>=80
ORDER BY 平均成绩;

-- 查询课程表course中，学时大于52的课程信息，先按学时降序，学时相同则按课程号升序。
SELECT *
FROM course
WHERE course_hour>52
ORDER BY course_hour desc,course_no;

分页LIMIT

当select返回的结果集中行数有很多时，使用limit来限制用户对结果数据的浏览和操纵

  • offset偏移量：从第几行开始显示（从0开始）

数量：显示几行

LIMIT {[offset,]行数|行数 OFFSET 从第几行-1}

-- 从第5行开始输出3行，两种写法
SELECT * 1,2 FROM 表 ORDER BY 1 LIMIT 4,3;
SELECT * 1,2 FROM 表 ORDER BY 1 LIMIT 3 OFFSET 4;

多表查询

交叉连接cross join

会产生笛卡尔积效应：

如果表1有100条数据，表2有100条数据，那么结果集就有100*100=10000条

select * from 表1,表2;
select * from 表1 cross join 表2;

内连接inner join

添加表与表之间的关联条件，消除笛卡尔积效应：

select * from 表1 e, 表2 d where e.字段1 = d.字段1;

SELECT * FROM table1 e inner join table2 d on e.字段 = d.字段;
-- or
SELECT * 
FROM table1 e join table2 d 
on e.字段 = d.字段;

内连接是系统默认的表连接，所以在from字句中可以省略inner只用join就可以

  1. 等值连接

在ON字句中的连接条件使用运算符=

  1. 非等值连接（不等连接）

在ON字句中的连接条件使用运算符=之外的比较运算符

  1. 自链接
select e.1, e.2, e.3, m.1, m.2 from 表1 e, 表1 m where e.3 = m.2;

-- eg在学生表student中查询和“朱凡”在同一个班的所有男同学的信息。
SELECT B.*
FROM student A,student B
WHERE A.stu_name='朱凡' AND B.class_no=A.class_no AND B.stu_sex='男' AND B.stu_name<>'朱凡';

外连接

连接的两张表分为基表、参考表

  1. 左链接left join

以左边的数据为主（基表），所以保留的是左边关系是所有元组。右边数据（参考表）的如果匹配到了就显示，没有匹配到就用空值代替

select e.1, e.2, d.1, d.2 from emp1 e left [OUTER] join emp2 d on e.3 = d.3;
  1. 右链接right join

以右面的数据为主（基表）

select e.1, e.2, d.1, d.2 from emp1 e right [OUTER] join emp2 d on e.3 = d.3;
  1. 完全连接full join

full [outer] join

连接关键字为FULL JOIN。该连接查询结果集中包括两个连接表的所有行，左表中每一行在右表中有匹配数据，则结果集中对应的右表的列填入相应数据，否则填为NULL；反之，亦然。

结果集合并UNION

利用UNION关键字，可以给出多条SELECT语句，并将它们的结果组合成单个结果集。合并时，两个表对应的列数和数据类型必须相同。各个SELECT语句之间使用UNION或UNION ALL关键字分隔。

UNION不使用关键字ALL，执行的时候删除重复的记录，所有返回的行都是唯一的。

select * from 表;
union	-- 两个查询显示在一个结果里面,重复不显示
select * from 表;
select * from dept;
union ALL	-- 返回查询结果的全部内容,重复数据也会显示
select * from dept;

例子：

-- 查询学生表student女同学的学号、姓名、性别信息，查询学生表student中年龄19岁的学生学号、姓名、性别信息，使用UNION连接查询结果。
SELECT stu_no,stu_name,stu_sex
FROM student WHERE stu_sex='女'
UNION
SELECT stu_no, stu_name,stu_sex 
FROM student 
where (Year(now())-Year(stu_birth))=19;

-- 查询选修了课程“010002”或者选修了课程“010003”的学生。
SELECT Stu_no
FROM Score
WHERE Course_no='010002'
UNION
SELECT Stu_no
FROM Score
WHERE Course_no= '010003';

子查询

子查询就是指在一个完整的查询语句中，嵌套若干个不同功能的小查询，从而完成一个复杂的查询。把一个查询的结果当成另一个查询的条件。

1.比较测试

-- 查询选修了“操作系统原理”课程的学生的学号。
SELECT stu_no
FROM score
WHERE score.course_no=
	(SELECT course_no
     FROM course
     WHERE course_name='操作系统原理');

2.集合成员测试

集合成员测试中的子查询是指将父查询与子查询之间用IN或 NOT IN 进行连接，用于判断某个属性列值是否在子查询的结果中，通常子查询的结果是一个集合。

  • IN 表示属于，即外部查询中用于判断的表达式的值与子查询返回的值列表中的一个值相等；
  • NOT IN 表示不属于。
-- 查询选修了“操作系统原理”课程的学生的学号及姓名。
-- 两个子查询
select stu_name from student
where stu_no in
    (select stu_no
    from score
    where course_no=
       (select course_no from course
        where course_name='操作系统原理'));

3.批量比较测试

3.1使用ANY关键字的比较测试

用比较运算符将一个表达式的值或列值与子查询返回的一列值中的每个值进行比较，只要有一次比较的结果为TRUE，则any测试返回TRUE。

【总结】有一个就行

3.2使用ALL关键字的比较测试

用比较运算符将一个表达式的值或列值与子查询返回的一列值中的每个值进行比较，只要有一次比较的结果为FALSE，则all测试返回FALSE。

【总结】必须全是true才行

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

ANY和ALL都用于一个值与一组值的比较，以>为例，ANY表示大于一组值中的任意一个值，ALL表示大于一组值中的每个值。

如：

>ANY(1,2,3)表示大于1； >ALL(1,2,3)表示大于3。

-- 查询所有同学中年龄最大的学生的姓名和性别。
SELECT stu_name,stu_sex
FROM student
WHERE stu_birth<=ALL
  (SELECT stu_birth FROM student);

4.带有EXISTS和NOT EXISTS谓词的子查询

带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值“true”或逻辑假值“false”：

  • 若内层查询结果非空，则外层的WHERE子句返回真值true。
  • 若内层查询结果为空，则外层的WHERE子句返回假值false。

由EXISTS引出的子查询，其目标列表达式通常都用 ** ，因为带EXISTS的子查询只返回真值或假值，给出列名无实际意义。

NOT EXISTS谓词则相反：

  • 若内层查询结果非空，则外层的WHERE子句返回假值。
  • 若内层查询结果为空，则外层的WHERE子句返回真值。
-- 查询选修了课程的学生的学号和姓名。
SELECT stu_no,stu_name 
FROM student
WHERE  EXISTS
       (SELECT * FROM score WHERE   
        student.stu_no=score.stu_no );
        
-- 查询没有选修课程的学生的学号和姓名。
SELECT stu_no,stu_name 
FROM student
WHERE  NOT EXISTS
       (SELECT * FROM score WHERE   
        student.stu_no=score.stu_no );

一些带EXISTS或NOT EXISTS谓词的子查询不能被其他形式的子查询等价替换，但所有带IN谓词、比较运算符、ANY和ALL谓词的子查询都能用带EXISTS谓词的子查询等价替换。

子查询在增删改命令中的应用

-- 删除所有“18010101”班生的选课记录。
DELETE FROM  score WHERE  stu_no  IN
		          	(SELECT  stu_no
		             FROM  Student
		             WHERE class_no= '18010101');

计算列值-统计函数

select id+1000 FROM user1;

-- 当前时间
select now() as '时间';

-- 年龄计算
SELECT stu_no,stu_name,Year(now())-Year(stu_birth) as age FROM student;

--               count
-- 查询学生总人数
SELECT COUNT(*) as 学生总人数 FROM student; 

-- 查询选修“020001”课程的学生人数
SELECT COUNT(stu_no) as 选课人数
FROM score 
WHERE course_no='020001'; 

-- 查询选修了课程的学生人数。
-- distinct表示取消重复项
SELECT COUNT(DISTINCT stu_no) as 选课人数
FROM score;


--               avg
-- 计算选修“200101”号课程的学生平均成绩。
SELECT AVG(score) FROM score WHERE Course_no= '200101';

--             max min
-- 查询选修“200101”课程的学生的最高分数。
SELECT MAX(score) AS 课程最高分
FROM score
WHERE course_no ='200101';
函数名称 描述 函数类型
COUNT(*列) 求出全部的记录数（项数），返回int类型的整数。
COUNT(*)计算数据表中数据记录的行数，包括值为NULL的记录；
COUNT(字段名)计算数据表中某一字段的行数，忽略值为NULL的记录；		 统计函数
SUM(列) 求出总和（表达式中所有值的和），忽略值为NULL的记录。 列=字段
AVG(列) 平均值，忽略值为NULL的记录。
MAX(列) 最大值
MIN(列) 最小值
STD或stddev 返回给定表达式中的所有值的标准值
variance 返回给定表达式中的所有值的方差
group_concat 返回由属于一组的列值连接而成的结果
BIT_AND 逻辑或
BIT_OR 逻辑与
BIT_XOR 逻辑异或
ADDDATE(日期，数字) 制定的日期加上指定的天数，求出新的日期 日期函数
LAST_DAY(日期) 求出指定日期的最后一天
now() 获取当前时间
ROUND(数字，保留位数) 对小数进行四舍五入，可以指定保留的位数，如果不指定则表示将小数点之后的数字全部进行四舍五入如 数值函数
MOD(数字，数字) 取模
UPPER(列，字符串) 将字符串的内容全部转为大写 字符函数
LOWER(列，字符串) 将字符串的内容全部转为小写
LENGTH(列，字符串) 求出字符串的长度
SUBSTR(列，字符串，起始索引,长度) 截取字符串
REPLACE(列，字符串) 字符串替换
TRIM(列，字符串) 去掉左右空格
INSTR(列，字符串,要查找的字符串) 查找字符串中某个字符的索引，如果匹配到就返回索引，匹配不到就返回0

6.完整性约束

数据完整性约束指的是为了防止不符合规范的数据进入数据库，在用户对数据进行插入、修改、删除等操作时，DBMS自动按照一定的约束条件对数据进行监测，使不符合规范的数据不能进入数据库，以确保数据库中存储的数据正确、有效、相容。

数据完整性约束是一组完整性规则的集合。三种约束包含：

  1. 实体完整性约束

    • 主键约束
    • 替代键约束

  2. 参照完整性约束

    • 外键约束

  3. 用户定义完整性约束

三者都满足即满足了数据完整性约束。因此本概念的定义是一个描述性定义。

MySQL中约束:

语言 名称 描述
primary key 主键约束(pk) 表示一个唯一的标识，一般都是自动递增，不能为空，实现数据的唯一性。
foreign key 外键约束(fk) 是在两种表上建立关联约束，加入关联约束后两张表就产生关系。也叫主外键约束。
not null 非空约束(nk) 如果使用非空约束，在此字段的内容不允许为null。
unique 唯一约束(uk) 此列的内容不允许出现重复。
default 默认值约束 用于设置字段的默认值。

主键约束

主键是表中的一列或多个列的组合，其值能唯一地标识表中的每一行，是实体完整性的实现。

可以用两种方式定义主键，作为列或表的完整性约束。

  1. 作为列的完整性约束时，只需在列定义的时候加上关键字PRIMARY KEY。
  2. 作为表的完整性约束时，需要在语句最后加上一条PRIMARY KEY(列名,…)子句。
create table 表名(
    字段名1 数据类型 [列级约束条件] [默认值],
    字段名2 数据类型 [列级约束条件] [默认值],
);


-- 例
create table user_1(
    id int primary key auto_increment,     	-- primary key主键约束，表示一个唯一的标识，auto_increment表示数据自增
    name varchar(20) not null,				-- 非空约束，此字段的内容不允许为null
    email varchar(50) unique, 				-- 唯一约束，此列的内容不允许出现重复
    sex char(4),
    phone varchar(20)
);

有auto_increment的数据不输入也会自增填入数据，必须是int类型，只能有一个和primary key搭配。

复合主键，即多字段联合主键，在最后加上主键的定义

primary key(id1, id2)

-- eg
CREATE TABLE score(
    stu_no char(10),
    course_no char(6),
    score float,
    
    PRIMARY KEY score_pri(stu_no,course_no) -- 复合主键, 这里score_pri是约束名称
);

替代键约束（唯一约束）

（UNIQUE CONSTRAINT）

替代键约束像主键一样，是表的一列或多列，它们的值在任何时候都是唯一的。替代键是没有被选作主键的候选键。

定义替代键的关键字是UNIQUE。

替代键和主键的区别：

  1. 一个数据表只能有一个主键。但是一个表可以有若干个UNIQUE键，并且它们甚至可以重合。例如，在C1和C2列上定义了一个替代键，同时又在C2和C3上定义了另一个替代键。
  2. 主键字段的值不允许为NULL，而UNIQUE字段的值可以取NULL，但是必须使用NULL或NOT NULL声明。
  3. 一般创建PRIMARY KEY约束时，系统会自动产生PRIMARY KEY索引。创建UNIQUE约束时，系统自动产生UNIQUE索引。（都会产生对应索引）。

外键约束

外键用来在两个表的数据之间建立链接，它可以是一列或者多列。一个表可以有一个或多个外键。外键对应的是参照完整性，一个表的外键可以为空值，若不为空值，则每一个外键值必须等于另一个表中主键的某个值。

foreign key是外键，references：是参照性。

foreign key (这个表里的字段名) references 关联表 (关联表的字段) [on delete restrict|cascade|set null|no action] [on update restrict|cascade|set null|no action]
  • restrict因为子表中有引用，则父表不能删除。
  • cascade更新后，级联更新。
  • set null若父表更新，则子表内容变为空。
  • no action不允许有动作，与RESTRICT同义。
  • set null若父表更新，则子表内容变为默认值。（这个也是什么也不填时的默认）
create table worker(
    
	id  int primary key auto_increment,
    
	name varchar(30),
	user_id int references work_1,
);

-- 或者：
-- 外键约束，我们实际上使用的是上面一行（实际开发用的少）
create table worker(
    
	id  int primary key auto_increment,
    
	name varchar(30),
	user_id int,
    foreign key (user_id) references work_1(id) 
);

当指定一个外键时，注意以下几点：

  1. 被参照表必须已经用一条CREATE TABLE语句创建了，或者必须是当前正在创建的表。在后一种情况下，参照表与被参照表是同一个表。

  2. 必须为被参照表定义主键。

  3. 必须在被参照表的表名后面指定列名或列名的组合。这个列或列的组合必须是这个表的主键或替代键。

  4. 尽管主键是不能够包含空值的，但允许在外键中出现一个空值。这意味着，只要外键和每个非空值出现在指定的主键中，这个外键的内容就是正确的。

  5. 外键中列的数目必须和被参照表的主键中列的数目相同。

  6. 外键中列的数据类型必须和被参照表的主键中列的数据类型对应相等。

如果外键相关的被参照表和参照表是同一个表，称为自参照表，这种结构称为自参照完整性。

用户定义完整新

-- eg：Student1表的Ssex只允许取“男”或“女”。
CREATE TABLE Student1(
    Sno  CHAR(9) PRIMARY KEY,
    Sname CHAR(8) NOT NULL,
    /*性别属性Ssex只允许取'男'或'女' */
    Ssex  CHAR(2)  CHECK (Ssex IN ('男','女')),
    Sage  SMALLINT,
    Sdept  CHAR(20)
);

-- 同理，表约束
CONSTRAINT chk_stu CHECK (Ssex IN ('男','女'))

命名完整性约束

完整性约束名在完整性约束的前面被定义，在数据库里这个名字必须是唯一的。

如果它没有被给出，则MySQL自动创建这个名字。只能给表完整性约束指定约束名，而无法给列完整性约束指定名字。

在定义完整性约束的时候应当尽可能地分配约束名，以便在删除完整性约束时可以更容易引用它们。

constraint 约束名字 foreign key (这个表里的字段名) references 关联表 (关联表的字段)

例：

-- eg:创建xs2表，字段包含学号、姓名、性别和出生时间，学号定义为主键，约束名为xs_pri。
CREATE TABLE xs2(
    学号 char(10) not null,
    姓名 char(10) not null,
    性别 enum('男', '女') not null,
    出生时间 date not null,
    
    CONSTRAINT xs_pri PRIMARY KEY(学号)
);

-- eg:创建score表，将Stu_no与Course_no设置为组合主键，并分别将stu_no和course_no设置为外键。
CREATE TABLE score(
    Stu_no char(10),
    Course_no char(6),
    Score float,
    PRIMARY KEY(stu_no,course_no),
    
    CONSTRAINT fk_stu_sc FOREIGN KEY(Stu_no) REFERENCES student(Stu_no),
    CONSTRAINT fk_course_sc FOREIGN KEY(Course_no) REFERENCES course(Course_no)
);

如果一条INSERT、UPDATE或DELETE语句违反了完整性约束，则MySQL返回一条出错消息并且拒绝更新，一个更新可能会导致多个完整性约束的违反。

在这种情况下，应用程序获取几条出错消息。

为了确切地表示出是违反了哪一个完整性约束，可以为每个完整性约束分配一个名字，出错消息会包含这个名字，从而使得消息对于应用程序更有意义。

查增删约束

  • 查看约束
show keys from 表名;
  • 增加约束
-- 方法1
alter table 表 add constraint 约束名 约束 (字段名);
-- 方法2
ALTER TABLE 表名 modify 字段名 数据类型 约束;

例：

-- 给phone添加唯一约束
alter table 表 add constraint weiyi_phone unique(phone);

-- 为course表的Course_name列添加非空约束。
ALTER TABLE course MODIFY Course_name varchar(16) not null;

-- 给t_info添加外键
ALTER TABLE t_info ADD FOREIGN KEY (user_id) REFERENCES t_user(id);
  • 删除约束
-- 删除唯一约束
alter table 表 drop index 字段;
DROP INDEX 字段 ON 表;

-- 删除主键约束fk_stu_sc
ALTER TABLE score DROP CONSTRAINT fk_stu_sc;

也可以使用重新定义字段来删除

-- eg
ALTER TABLE course MODIFY Course_name varchar(16) not null;
-- 删除就是：
ALTER TABLE course MODIFY Course_name varchar(16);

7.视图

视图是从基表中导出的逻辑表，它不像基表一样物理地存储在数据库中，视图没有自己独立的数据实体，它是一个虚拟表。视图作为一种基本的数据库对象，是查询一个表或多个表的另一种方法，通过将预先定义好的查询作为一个视图对象存储在数据库中，然后就可以像使用表一样在查询语句中调用它。

**随着数据变化的虚拟表：视图是存储在数据字典里的一条select语句。**所有当基本表中的数据发生变化时，从视图中查询出来的数据也随之改变。

通过视图进行查询没有任何限制，但对视图的更新操作（增、删、改）即是对视图基表的操作，因此有一定的限制条件。

使用视图可以帮助用户建立更加安全的数据库，管理使用者可操作的数据，简化查询过程。

特点：

  • 虚表，是从一个或几个基本表（或视图）导出的表。
  • 只存放视图的定义，不存放视图对应的数据。
  • 基表中的数据发生变化，从视图中查询出的数据也随之改变。
  • 视图之上可以再定义视图。

视图的优点：

  1. 集中分散数据，保证数据的逻辑独立性：可以有选择性的选取数据库里的部分，只需要修改视图定义中的查询部分，而基于视图的查询不用修改。简化查询操作，屏蔽了数据库的复杂性。
  2. 简化查询语句：可以把复杂的查询变的简单，把经常使用的多表查询操作定义成视图，从而用户不用每次写复杂的查询语句，直接使用视图完成查询。
  3. 重用SQL语句：视图不包含数据，可以便捷的重复使用。
  4. 保护数据安全，数据定制：只授予用户使用视图的权限，对不同的用户定义不同的视图，使用户只能看到与自己有关的数据。同时简化了用户权限的管理，增加了安全性。。
  5. 共享所需数据：数据具有独立性（存一次），试图可从多个表检索数据。
  6. 更改数据格式：可以重新格式化检索出的数据，输出到其他应用程序之中。

创建

用户必须拥有数据库所有者授予的创建视图权限才可以创建视图。

视图的命名必须遵循标识符规则，对每一个用户都是唯一的。视图名称不能和创建该视图的用户的其他任何一个表的名称相同。

CREATE VIEW 视图名称 AS 子查询 [with [cascaded|local] check option]
  • 加上with check option可以强制所有通过视图修改的数据必须满足SELECT语句中指定的选择条件。这样可以确保数据修改后，仍可通过视图看到修改的数据。
  • 当视图是根据另一个视图定义时，WITH CHECK OPTION给出LOCAL和CASCADE两个可选参数，它们决定了检查测试的范围。
    • CASCADED为默认选型，他会对所有的视图进行检查。
    • LOCAL则使CHECK OPTION 只对定义的视图进行检查。

分类

  1. 普通视图：创建视图时，没有使用WITH CHECK OPTION子句的视图是普通视图。
  2. 检查视图：使用WITH CHECK OPTION子句的视图称为检查视图。通过检查视图更新基表数据时，只有满足检查条件的更新语句才能成功执行。

普通视图

-- 在jwgl数据库中由学生表student、课程表course、成绩表score三个表创建视图“学生成绩视图”stu_cour_score，包含的列有学号、姓名、性别、课程号、课程名和成绩。代码如下。
CREATE VIEW stu_cour_score
AS

SELECT student.stu_no AS 学号, stu_name AS 姓名, stu_sex AS 性别,
course.course_no AS 课程号, course_name AS 课程名称, score AS 成绩

FROM  score 
INNER JOIN course ON score.course_no =course.course_no 
INNER JOIN student ON score.stu_no =student.stu_no;

测试：普通视图更新数据库表记录时，普通视图并没有对插入语句进行条件检查

-- 建立普通视图VTEST1，其包含成绩>85分学生的学号、课程号和成绩
 -- 方法1
CREATE VIEW VTEST
AS
SELECT stu_no AS 学号, course_no AS 课程号,score AS 成绩
FROM score 
WHERE score>85;

-- 方法2
CREATE VIEW VTEST1(学号,课程号,成绩)
AS
SELECT  stu_no, course_no,score
FROM score 
WHERE score>85;

以上代码执行后，使用INSERT语句通过VTEST视图向score表插入选课信息（成绩<85）：

Insert into VTEST Values('1801020103','020001',65);

从执行结果可以看出，通过普通视图更新数据库表记录时，普通视图并没有对插入语句进行条件检查（成绩>85）。

检查试图

-- 建立检查视图VWTEST，其包含学籍“北京市”学生的学号、姓名、性别、出生日期和学籍，代码如下。
CREATE VIEW VWTEST
AS
SELECT stu_no AS 学号, stu_name AS 姓名, stu_sex AS 性别, stu_birth AS 出生日期, stu_source AS 学籍
FROM student 
WHERE stu_source='北京市'
WITH  CHECK  OPTION;

以上代码执行后，使用INSERT语句通过VWTEST视图向student表插入学生信息时，检查视图更新数据库表记录时会进行条件检查。

例如，插入的信息不是北京市，那么就会报错CHECK OPTION failed。

创建视图时应该注意以下情况：

  1. 只能在当前数据库中创建视图，在视图中最多只能引用1024个列，视图中记录的数目由其基表中的记录数决定。
  2. 如果视图引用的基表或视图被删除，则该视图不能再被使用，直到创建新的基表或者视图。
  3. 如果视图中某一列是函数、数学表达式、常量或者来自多个表的列名相同，则必须为列定义名称。
  4. 视图的名称必须遵循标识符规则，且对每个用户必须是唯一的。此外，该名称不得与该用户拥有的任何表名称相同。

查看表内容

从用户角度：查询视图与查询基本表相同；更新视图与更新基本表相同；通过视图进行查询没有任何限制，用户可以将注意力集中在其关心的数据上，而非全部数据，这样就大大提高了运行效率与用户满意度。

如果数据来源于多个基本表结构，或者数据不仅来自与基本表结构，还有一部分数据来源于其他视图，并且搜索条件又比较复杂，需要编写的查询语句就会比较烦琐，此时定义视图就可以使数据的查询语句变得简单可行。

定义视图可以将表与表之间的复杂的操作连接和搜索条件对用户不可见，用户只需要简单地对一个视图进行查询即可，所以，视图虽然增加了数据的安全性，但是不能提高查询的效率。

SELECT * FROM VIEW 视图名称;

查看定义

查看数据库中已存在的视图定义必须要有SHOW VIEW的权限，MySQL数据库下的user表中保存着这个信息。

查看视图的方法包括：DESCRIBE、SHOW TABLE STATUS和SHOW CREATE VIEW

show create view 视图名字;
-- 或者
desc 视图名字;
DESCRIBE 视图名字;
-- 或者
show table status like '视图名字';

查看所有视图

在views表中查看视图详细信息：

在MySQL中，information_schema数据库下的views表中存储了所有视图的定义。通过对views表的查询，可以查看数据库中所有视图的详细定义，查询语句如下。

SELECT * FROM information_schema.views;

修改

修改视图是指修改数据库中存在的视图，当基本表的某些字段发生变化时，可以通过修改视图来保持与基本表的一致性。

MySQL通过CREATE OR REPLACE VIEW语句和ALTER VIEW语句，两种方式来修改视图。

create or replace view 视图名称[(字段名,…)] as 查询语句 [[WITH [CASCADED|LOCAL] CHECK OPTION];

-- 或者
                                              
alter view 视图名称[(列名,…)]  as 查询语句 [[WITH [CASCADED|LOCAL] CHECK OPTION];
  1. CREATE OR REPLACE VIEW语句就是创建视图的语句，当视图存在时该语句对视图进行修改，当视图不存在时则创建新的视图。
-- 【例】修改视图VTEST，其包含成绩>60分学生的学号、课程号和成绩，代码如下。
CREATE OR REPLACE VIEW VTEST
AS
SELECT  stu_no AS 学号, course_no AS 课程号,score AS 成绩
FROM score 
WHERE score>60;

-- 【例8-37】修改视图VWTEST，其包含学籍“北京市”学生的姓名、性别和学籍，代码如下。
CREATE OR REPLACE VIEW VWTEST
AS
SELECT  stu_name AS 姓名, stu_sex AS 性别, stu_source AS 学籍
FROM student 
WHERE stu_source='北京市'
WITH  CHECK  OPTION;
  1. 命令行中的参数与CREATE VIEW命令中的参数含义相同。
-- 【例8-44】修改学生成绩视图stu_cour_score，使其显示成绩在80分以上的学生的成绩信息。代码如下：
ALTER  VIEW  stu_cour_score
AS
SELECT  student.stu_no AS 学号, stu_name AS 姓名, stu_sex AS 性别,
course.course_no AS 课程号, course_name AS 课程名称, score AS 成绩
FROM  score INNER JOIN course ON score.course_no =course.course_no 
INNER JOIN student ON score.stu_no =student.stu_no
WHERE score>80;

数据更新

视图看上去非常像数据库的物理表，对它的操作同任何表一样。因为视图是一个虚拟表，其中没有数据，当通过视图修改时，实际上是在改变表（视图定义中涉及的表）中的数据；相反地，基本数据的改变也会自动反映在由基表产生的视图中。由于逻辑上的原因，有些Oracle视图可以修改对应的基表，有些则不能（仅能查询）。

如果对视图中的数据进行增加或者删除操作时，实际上就是在对其基本表中的数据进行增加或者删除操作。

要通过视图更新基本表数据，必须保证视图是可更新视图，即可以在INSERT、UPDATE或DELETE等语句当中使用它们。对于可更新视图，视图中的行和基本表中的行必须具有一对一的关系。

查询方法&更新方法：

  • 实体化视图：有效性检查：检查所查询的视图是否存；执行视图定义，将视图临时实体化，生成临时表；查询视图转换为查询临时表；查询完毕删除被实体化的视图(临时表)。
  • 视图消解法：进行有效性检查，检查查询的表、视图等是否存在。如果存在，则从数据字典中取出视图的定义；把视图定义中的子查询与用户的查询结合起来，转换成等价的对基本表的查询；执行修正后的查询。

视图更新有限制，要求定义视图时：

  1. select子句中的目标列不能包含聚集函数；
  2. select子句中不能使用distinct关键字；
  3. 不能包括group by子句；
  4. 不能包括经算术表达式计算出来的列；
  5. 不能从两个以上的基本表中导出；
  6. 视图定义中不能有嵌套查询

一般来说，对于行列子集视图可以更新，其他视图更新有限制或者不能。

  • 插入INSERT
-- 【例8-42】向视图VTEST中插入一条记录，代码如下。
   INSERT  INTO  VTEST  VALUES('1902030103','010004', 93) ;
  • 修改UPDATE
-- 更新视图VWTEST中学生“耿明”的性别为“女”，代码如下。
UPDATE  VWTEST  SET  性别='女' WHERE 姓名='耿明';
  • 删除DELETE
-- 【例8-43】删除视图VTEST中课程号为“100101”的记录，代码如下。
   DELETE  FROM  VTEST  WHERE 课程号='100101';

删除

视图的删除不会影响所依赖的基表数据。删除一个或多个视图可以使用DROP VIEW语句，语法如下：

drop view [if exists] 视图名;
-- or
drop view [if exists] 视图名1,视图名2;
  • IF EXISTS可以防止因为删除不存在的视图时而出现的问题

8.索引

索引是一个单独的、物理的、存储在磁盘上的数据库结构，是对数据库某个表中一列或多列的值进行排序的一种结构，它包含列值的集合以及相应的标识这些值所在的数据页的逻辑指针清单。索引存放在单独的索引页面上。当进行数据检索时，系统先搜索索引页面，从中找到所需数据的指针，再直接通过指针从数据页面中读取数据。

在数据库中，索引通过记录表中的关键值指向表中的记录，这样数据库引擎就不用扫描整个表而定位到相关的记录。

优点

  1. 加快数据查询 在表中创建索引后，MySQL将在数据表中为其建立索引页。每个索引页中的行都含有指向数据页的指针，当进行以索引为条件的数据查询时，将大大提高查询速度。 也就是说，经常用来作为查询条件的列，应当建立索引；相反，不经常作为查询条件的列则可以不建索引。
  2. 加快表的连接、排序和分组工作 在进行表的连接或使用ORDER BY和GROUP BY子句检索数据时，都涉及数据的查询工作，建立索引后，可以显著减少表的连接及查询中分组和排序时间。加速表与表之间的连接，在实现数据的参照完整性方面有特别的意义。 但并不是在任何查询中都需要建立索引。索引带来的查找效率提高是有代价的，因为索引也要占用存储空间，而且为了维护索引的有效性，会使添加、修改和删除数据记录的速度变慢。所以，过多的索引不一定能提高数据库的性能，必须科学地设计索引，才能提高数据库的性能。
  3. 索引能提高WHERE语句的数据提取速度，也可提高更新和删除数据记录的速度。
  4. 确保数据的唯一性。 当创建PRIMARY KEY和UNIQUE约束时，MySQL会自动为其创建一个唯一的索引。唯一索引能确保列的内容绝对不重复，从而确保数据的唯一性。

索引一旦被创建，将由数据库自动管理和维护。在更新数据表数据时，数据库会自动在索引上做出相应的修改。因此索引总是和表的内容保持一致。

索引的优点除了可以提高数据的查询速度，还可以通过创建唯一性索引保证表中数据记录不重复。

虽然索引有诸多优点，但是要注意增加索引也会有许多不利。创建和维护索引需要耗费时间、会占用磁盘空间，会降低系统的维护速度。因此我们在使用索引的时候一定要恰当。

创建原则

给字段添加索引是为了提高查询效率，但不是所有的字段都可以添加索引，创建索引有以下特点：

  1. 在经常需要搜索的列上，可以加快搜索的速度。
  2. 在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构。
  3. 在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度。
  4. 在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间。

总结：

索引并非越多越好 避免对经常更新的表建立过多的索引 数据量小的表最好不要使用索引 在不同值少的列上不要建立索引 为经常需要排序、分组和连接操作的字段建立索引

分类

  • 根据索引列的内容，MySQL的索引可以分为以下几类：
  1. 普通索引

普通索引是由**KEY或INDEX**定义的索引，它是MySQL中的基本索引类型，允许在定义索引的列中插入重复值和空值（没有唯一性之类的限制）该类型索引可以创建在任何数据类型中。

  1. 唯一索引

唯一索引是由**UNIQUE定义的索引，指索引列的值必须唯一，但允许有空值**（不允许其中任何两行具有相同索引值的索引）。如果是在多个列上建立的组合索引，则列值的组合值必须唯一。

例如，如果在 employee 表中职员的姓 (lname) 上创建了唯一索引，则任何两个员工都不能同姓。

  1. 全文索引

全文索引是由**FULLTEXT**定义的索引，是指在定义索引的列上支持值的全文查找。它只能创建在CHAR、VARCHAR或TEXT类型的字段上。

  1. 空间索引

空间索引是由**SPATIAL**定义的索引，是只能在空间数据类型(GEOMETRY、POINT、LINESTRING和POLYGON)的列上建立索引。 需要注意的是，创建空间索引的字段，必须将其声明为NOT NULL。

  • 根据索引列的数目MySQL的索引又可以分为：
  1. 单列索引

单列索引指的是在表中单个字段上创建索引，它可以是普通索引、唯一索引或者全文索引，只要保证该索引只对应表中的一个字段即可。

  1. 多列索引

多列索引指的是在表中多个字段上创建的索引。只有在查询条件中使用了这些字段中的第一个字段时，该索引才会被使用。

创建create index

create [unique|fulltext|spatial] index 索引名 on 表名 (字段名[长度], ... [ASC | DESC])

-- 根据student表的学号列上的前6个字符建立一个升序索引xh_index。
CREATE INDEX xh_index ON student(stu_no(6) ASC);

-- Score表上建立一个学号和课程号的复合索引xh_kh。
CREATE INDEX xh_kh ON score(stu_no, course_no);

  • unique：创建唯一索引，在索引列中不能有相同的列值存在。

  • fulltext：创建全文索引。

  • spatial：创建空间索引。

  • 索引名：该选项表示创建索引的名称。不加此选项，则默认用创建索引的字段名为该索引名称。

  • 长度：该选项指定字段中用于创建索引的长度。不加此选项,则默认用整个字段内容创建索引。

  • ASC|DESC：该选项表示创建索引时的排序方式。其中ASC为升序排列, DESC为降序排列。默认为升序排列。

  • 创建表的同时创建索引

使用CREATE TABLE语句创建表时，除了可以定义表中包含的列的数据类型,还可以定义主键约束、外键约束或者唯一性约束。无论是创建哪种约束，在定义约束的同时相当于在对应的列上创建了一个索引。

Create table student1(
    Stu_no char(10),
    Stu_name char(10) not null,
    Stu_sex enum('男', '女') not null,
    Stu_birth date not null,Stu_source 
    Class_no char(8),
    Stu_tel char(11),
    Credit smallint default 0,
    Stu_picture varchar(30),
    Stu_remark text,
    Stu_pwd  Char(6),
    PRIMARY KEY(Stu_no),
    
    -- 索引
    INDEX(Stu_name)
);

-- 创建course1表，同时表中的Course_name字段创建名为UK_cname的唯一索引，并且按照升序排列。
CREATE TABLE course1(
    Course_no char(6) primary key ,
    Course_name varchar(16) not null,
    Course_credit tinyint not null,
    Course_hour smallint not null,
    Course_term tinyint not null,                 
    UNIQUE INDEX UK_cname(Course_name ASC)
);


-- 创建score1表，在表中的学号Stu_no和课程号Course_no上建立多列索引。
CREATE TABLE score2(
    Stu_no char(10) NOT NULL,
    Course_no char(6) NOT NULL ,
    Score float,
    
    INDEX IDX_multi(stu_no,course_no)
);
  • 通过修改表，创建索引
-- 在student表的stu_name列上创建一个普通索引。

ALTER TABLE student
	ADD INDEX xs_xm(stu_name);

show index from student;


-- 在student表的stu_name列上创建一个普通索引，索引类型为BTREE。
ALTER TABLE student
	ADD INDEX xs_xm using BTREE(stu_name);

show index from student;

查看show index

show index from 表名;

-- eg
SHOW INDEX FROM student;

删除drop index

drop index 索引名 on 表名
-- or
ALTER TABLE 表名 drop index 索引名


-- eg
ALTER TABLE 表名
	DROP PRIMARY KEY /*删除主键*/
	DROP INDEX 索引名 /*删除索引*/
	DROP FOREIGN KEY 外键标识 /*删除外键*/

-- eg:删除student表上的xs_xm索引。
DROP INDEX xs_xm ON student;
-- or
ALTER TABLE student DROP INDEX xs_xm;

如果从表中删除了列，索引可能会受影响。 如果所删除的列为索引的组成部分，则该列也会从索引中删除。 如果组成索引的所有列都被删除，则整个索引将被删除。

9.触发器

详见过 程式数据库对象-触发器

触发器是用户定义在基本表上的一类由事件驱动的特殊过程。由服务器自动激活， 能执行更为复杂的检查和操作，具有更精细和更强大的数据控制能力。使用 CREATE TRIGGER 命令建立触发器。

每个表的一个时刻下每一个事件仅有一个触发器

CREATE TRIGGER 触发器名
AFTER/BEFORE INSERT/UPDATE/DELETE 
ON 表名
FOR EACH ROW 
BEGIN
	SQL语句;  #触发程序
END;

列：创建两种表t1,t2两种表中都有id,name字段，新加t1表记录后自动添加t2

CREATE TRIGGER triggen_1
	after
	insert ON t1
FOR EACH ROW
insert into t2(name) values(new.name);
  • NEW是新值，OLD是旧值
  • INSERT只有NEW UPDATE有NEW和OLD DELETE只有OLD

【注意】一般情况下，Mysql默认是以;号作为结束执行语句。在创建触发器过程中需要用到;号。为了解决这个问题，可以用到delimiter语句。例如delimiter //，可以将结束符号变成//，创建完触发器后再执行delimiter ;将结束符号改回成;号。

-- 【例10-10】创建一个触发器，当从student表删除学生记录时，同时删除该学生在score表中的所有成绩记录。（为了实现这一操作，需要删除score表对于student的外键约束）
DELIMITER //
CREATE TRIGGER student_delete_after AFTER delete ON student 
for each row
BEGIN
  DELETE FROM score WHERE stu_no=old.stu_no;
END //
DELIMITER ;

查看

show TRIGGERS

删除

DROP TRIGGER 触发器的名字

备份与恢复

导出一整个数据库：

C:\WINDOWS\system32> mysqldump -u 用户名 -p 数据库名 > 导出的文件名

C:\WINDOWS\system32> mysqldump -u root -p user > d:\demo.sql

导出一张表：

C:\WINDOWS\system32> mysqldump -u 用户名 -p 数据库名 表名称 > 导出的文件名
C:\WINDOWS\system32> mysqldump -u root -p user user_1 > d\demo.sql

恢复： 数据的回复必须要先登录,登录成功后新建一个数据库,进入到指定的数据库中执行如下命令

mysql> Source 脚本目录(绝对路径)
mysql> Source C:\java\demo.sql

Linux防火墙修改

vim /usr/lib/firewalld/services/ssh.xml
 <port protocol="tcp" port="3306"/>
systemctl restart firewalld

其他机器连接本地的MySQL

mysql> GRANT ALL PRIVILEGES ON *.* TO root@"%" IDENTIFIED BY "root" with grant option;

.就是所有

Linux禁止更新（推荐设置）

我们在安装之后，为了能够正常运行，我们会禁止MySQL进行更新。因为在yum更新了MySQL之后，MySQL会自动重启，这对于我们上线部署项目来说是没有必要的，所以我们可以屏蔽更新。

vim /etc/yum.conf exclude=mysql-community-client,mysql-community-common,mysql-community-libs,mysql-community-server

mysql无法输入中文

mysql数据库“不能插入中文”解决办法 - 付杰博客 (fujieace.com)

报错：

ERROR1366 (HY000): Incorrect string value: '\xD5\xC5\xC8\xFD' for column'username' at row 1

status;						查看客户端编码字符状态

C:\Program Files\MySQL\MySQL Server 5.7\bin\mysql.exe
Ver 14.14 Distrib 5.7.30, for Win64 (x86_64)

Connection id:          4
Current database:
Current user:           root@localhost
SSL:                    Cipher in use is ECDHE-RSA-AES128-GCM-SHA256
Using delimiter:        ;
Server version:         5.7.30-log MySQL Community Server (GPL)
Protocol version:       10
Connection:             localhost via TCP/IP
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    gbk
Conn.  characterset:    gbk
TCP port:               3306
Uptime:                 12 min 16 sec

注意：client characterset:gbk; 一定要是gbk，不能是utf8；

show variables like "%character%";		查看所有编码字符

+--------------------------+---------------------------------------------------------+
| Variable_name            | Value                                                   |
+--------------------------+---------------------------------------------------------+
| character_set_client     | gbk                                                     |
| character_set_connection | gbk                                                     |
| character_set_database   | latin1                                                  |
| character_set_filesystem | binary                                                  |
| character_set_results    | gbk                                                     |
| character_set_server     | latin1                                                  |
| character_set_system     | utf8                                                    |
| character_sets_dir       | C:\Program Files\MySQL\MySQL Server 5.7\share\charsets\ |
+--------------------------+---------------------------------------------------------+
8 rows in set, 1 warning (0.01 sec)

注意两个地方：character_set_client 和 character_set connection ，这两个地方一定要是gbk；

修改为utf8:

mysql>set character_set_database=utf8;
mysql>set character_set_server=utf8;

  1. 解决表无法插入中文

    经过上面的修改后，以后数据库创建的表默认的编码都是utf8，可以插入中文了，但是在修改编码前已经创建的表编码还是latin1，还是不能插入中文，有两种解决办法：

  2. 第一种方法：最直接明了，删表重建，但是有些麻烦

  3. 第二种方法：使用下面语句，tablename换成你的表名，该语句的作用就是把表的编码换成utf8

alter table `tablename` convert to character set utf8;

关于 gbk，为了各方面的兼容，也有人用 utf8mb4 编码