MySQL数据库与SQL语言详解-CSDN博客

本文链接：https://blog.csdn.net/weixin_44797182/article/details/119817529

文章目录

1. mysql概述
2. sql概述
- 2.1 SQL介绍
- 2.2 sql分类
3. DDL
4. DML
5. 约束
6.DQL
7.DCL
8. TPL
9. 视图

1. mysql概述

1.1 数据存储方法

先说几个点,通过之前的学习，我们能够使用以下两种方式进行数据存储：

1. 使用变量、对象、数组、集合存储数据，数据保存在内存（RAM）中；

2. 使用文件（File）存储数据，保存在硬盘上。

方式1存在的问题

不能持久化（永久保存数据），程序关闭数据就会消失。

方式2存在的问题

没有数据类型的区分；
存储数据量级较小；
没有访问安全限制；
没有备份、恢复机制。

1.2 什么是数据库

用来存储和管理数据的仓库。

优点：

可存储大量数据；
方便检索；
保持数据的一致性、完整性；
安全、可共享；
通过组合分析，可产生新数据。

3.数据库的好处？

可以持久化存储数据
方便存储和管理数据
使用了统一的方式操作数据库 – SQL

1.3 数据库的发展历程

无数据库，使用磁盘文件存储数据；
层次结构数据库：IBM公司IMS（Information Management System）定向有序的树状结构实现存储和访问；
网状结构数据库：美国通用电气公司IDS（Integrated Data Store），以节点形式存储和访问；
关系结构数据库：Oracle、DB2、MySQL、SQL Server，使用二维表格来存储数据；
非关系型数据库：ElasticSearch、MongoDB、Redis，多数使用哈希表，表中以键值（key-value）的方式实现特定的键和一个指针指向的特定数据。

1.4 常见的关系型数据库产品

Oracle（神谕）：美国Oracle（甲骨文）公司，主要用在电信，金融领域，下载免费，服务需要收费；
DB2：IBM，主要用在金融领域；
SQL Server：微软，只能用在微软平台上；
MySQL：瑞典MySQL AB公司开发，属于 Oracle旗下产品，分为社区版和收费版，在国内互联网公司使用广泛。

1.5 对数据库的理解

我们通常所说的数据库其实是RDBMS（Relational Database Management System，关系型数据库管理系统），其包括两个部分：

管理员，Manager；
仓库，Database。

Database包括：N张表（Table）；

Table包括两个部分：

表结构：定义表的列名和列类型(理解成类)；
表记录：一行一行的记录(理解成对象)。

2. sql概述

2.1 SQL介绍

SQL：Structured Query Language（结构化查询语言），其实就是定义了操作所有关系型数据库的规则,客户端使用SQL来操作数据库，可以应用到所有关系型数据库中。

SQL语言标准由ISO（国际标准化组织）发布，ISO定义了很多SQL标准（例如SQL99）对RDBMS进行统一的操作，相同的语句可以操作Oracle，MySQL。各个数据库厂商有自己的标准，类似于方言，MySQL中的limit。

SQL语法：

SQL语句可以在单行或多行书写，以分号结尾；
可使用空格和缩进来增强语句的可读性；
MySQL不区分大小写，建议大写。
数据库的注释：
- 单行注释：-- 注释内容 #注释内容(mysql特有)
- 多行注释：/* 注释内容 */

经验：通常执行对数据库的“增删改查”，简称C（Create）R（Read）U（Update）D（Delete）。

2.2 sql分类

SQL分类

DDL(Data Definition Language)数据定义语言
- 创建、删除、修改库与表结构 => 用来定义数据库对象：数据库，表，列等。关键字：create, drop,alter 等
DML(Data Manipulation Language)数据操作语言
- 用来对数据库中表的数据进行增删改。关键字：insert, delete, update 等
TPL(Transaction Process Language)事务处理语言：用于对事务进行处理；
DQL(Data Query Language)数据查询语言
- 用来查询数据库中表的记录(数据)。关键字：select, where 等
DCL(Data Control Language)数据控制语言(了解)
- 用来定义数据库的访问权限和安全级别，及创建用户。关键字：GRANT， REVOKE 等

在这里插入图片描述

3. DDL

3.1 DDL - 操作数据库

自带数据库

数据库名称	描述
information_schema	信息数据库，其中保存着关于所有数据库的信息（元数据），元数据是关于数据的数据，如数据库名或表名，列的数据类型，或访问权限等。
mysql	核心数据库，主要负责存储数据库的用户、权限设置、关键字等，以及需要使用的控制和管理信息，不可以删除。
performance_schema	性能优化的数据库，MySQL 5.5版本中新增的一个性能优化的引擎。
sys	系统数据库，MySQL5.7版本中新增的可以快速的了解元数据信息的系统库，便于发现数据库的多样信息，解决性能瓶颈问题。

因为此部分的句子不常用,所以直接整理

命令	解释
show databases	查看所有的数据库
use sys	切换到想要操作的数据库,这里切换到sys数据库
select database()	查询当前使用的数据库
CREATE DATABASE mydb1;	创建名字为mydb1数据库
CREATE DATABASE mydb2 CHARACTER SET utf8;	创建数据库并设置编码格式为utf8
CREATE DATABASE IF NOT EXISTS mydb3;	如果mydb3数据库不存在，则创建；如果存在，则不创建。
SHOW CREATE DATABASE mydb1;	查看数据库的创建信息
ALTER DATABASE mydb1 CHARACTER SET gbk;	修改数据库的字符集
DROP DATABASE mydb1;	删除数据库mydb1
DROP DATABASE IF EXISTS mydb2;	如果存在数据库mydb2就删除

3.2 DDL-操作数据表

3.2.1 数据类型

MySQL支持多种类型，大致可以分为三类：

数值；
字符串(字符)类型；
日期时间。

数据类型对于我们约束数据的类型有很大的帮助。

1. 数值类型

类型	大小	范围（有符号）	范围（无符号）	用途
INT	4 字节	(-2 147 483 648，2 147 483 647)	(0，4 294 967 295)	大整数值
DOUBLE	8 字节	（-1.797E+308,-2.22E-308）	(0,2.22E-308,1.797E+308)	双精度浮点数值
DOUBLE(M,D)	8个字节，M表示长度，D表示小数位数	同上，受M和D的约束 DOUBLE(5,2) -999.99-999.99	同上，受M和D的约束	双精度浮点数值
DECIMAL(M,D)	DECIMAL(M,D)	依赖于M和D的值，M最大值为65	依赖于M和D的值，M最大值为65	小数值，和钱相关，不会出现精度缺失的问题

2.字符串类型

类型	大小	用途
CHAR	0-255字符	定长字符串CHAR(10)10个字符
VARCHAR	0-65535 字节	变长字符串VARCHAR(10)10个字符
BLOB（binary large object）	0-65535字节	二进制形式的长文本数据
TEXT	0-65535字节	长文本数据

关于CHAR和VARCHAR：

CHAR(255) 数据长度不足指定长度，补足到指定长度，用于身份证号，手机号，时间固定长度的内容；
VARCHAR(65535) 数据长度不足指定长度，不补足到指定长度，用于用户名，备注不固定长度的内容；
VARCHAR单独至少花一个字节保存数据长度，如果长度超过一个字节，就要花费两个字节。

3.日期时间类型

类型	大小	范围	格式	用途
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	混合日期和时间值，时间戳

3.2.2 基本操作

语法：

1. 创建数据表

CREATE TABLE [IF NOT EXISTS] 表名（
    列名 数据类型 [约束],
    列名 数据类型 [约束],
    列名 数据类型 [约束]       //最后一列的末尾不加逗号
）[CHARSET=utf8];            //可根据需要指定表的字符编码集

2. 查看表

# 查看当前数据库中所有表名称
SHOW TABLES;
# 查看指定表的创建语句
SHOW CREATE TABLE 表名;
# 查看表结构
DESC 表名；

3,修改表

# 添加列
ALTER TABLE 表名 ADD (
    列名 列类型,
    列名 列类型
)

# 修改列类型(如果被修改的列已存在数据，那么新的类型可能会影响到已存在数据), 修改表中的某列时，也要写全列的名字，数据类型，约束
ALTER TABLE 表名 MODIFY 列名 列类型;   

# 修改列名, 在给定列新名称时，要指定列的类型和约束
ALTER TABLE 表名 CHANGE 原列名 新列名 列类型;

# 删除列, 删除列时，每次只能删除一列
ALTER TABLE 表名 DROP 列名;

# 修改表名称
ALTER TABLE 原表名 RENAME TO 新表名;
ALTER TABLE 原表名 RENAME 新表名;

4,删除表

DROP TABLE 表名;

4. DML

DML(Data Manipulation Language)数据操作语言：增、删、改表记录。

注意：DML是对表的内容进行操作，DDL是对表的结构进行操作，两者有本质区别。

4.1 插入表数据 - insert

用来在表中增加数据，增加的数据可能是一行，也可能是多行，但是不可能是半行。

# 在表名后给出要插入的列名，其他没有指定的列等同与插入null值。所以插入记录总是插入一行，不可能是半行。
# 在VALUES后给出列值，值的顺序和个数必须与前面指定的列对应
INSERT INTO 表名(列名1, 列名2, ...) VALUES(列值1, 列值2, ...);

INSERT INTO stu(id, name, age, gender, addr) VALUES(1, 'zs', 10, '男', '青岛市北区');
INSERT INTO stu(name, age, gender, addr) VALUES('ls', 10, '男', '青岛市南区');
INSERT INTO stu(name, age) VALUES("ww", 20);

# 没有给出要插入的列，那么表示插入所有列
# 值的个数必须是该表列的个数
# 值的顺序，必须与表创建时给出的列的顺序相同。
INSERT INTO 表名 VALUES(列值1, 列值2, ...);

INSERT INTO stu VALUES(1, 'zs', 10, '男', '青岛市北区');
INSERT INTO stu VALUES(2, 'ls', 10, '男', '青岛市南区');

# 一次插入多条数据
INSERT INTO 表名(列名1, 列名2, ...) VALUES(列值1, 列值2, ...), (列值1, 列值2, ...), (列值1, 列值2, ...);

INSERT INTO stu(name, age) VALUES('zs', 20), ('ls', 11), ('ww', 12);

4.2 修改表数据 - update

用来修改表中已有的数据。

# WHERE条件是可选的, 如果没有条件, 就修改所有记录, 多数时候我们都加上WHERE条件
UPDATE 表名 SET 列名1=列值1, 列名2=列值2, ...[WHERE 条件]

关于条件：

条件必须是一个boolean类型的值或表达式；
运算符:=、!=、<>、>、<、>=、<=、between…and… 、in(…)、is null、not、or、and；
和后面学习的删除数据及DQL中的条件通用。

UPDATE stu SET addr='qd', gender='男' WHERE age >= 18 AND age <= 80;
UPDATE stu SET addr='qd', gender='男' WHERE age <> 18;
UPDATE stu SET addr='qd', gender='男' WHERE age BETWEEN 18 AND 80;
UPDATE stu SET addr='qdsnq' WHERE name='zs' OR name='ls';
UPDATE stu SET addr='qdsnq' WHERE name IN('zhangsan','lisi');
UPDATE stu SET addr='qdsnq' WHERE age IS NOT null;

4.3 删除表数据-delete

用来删除表中已有的数据。

# WHERE条件是可选的, 如果没有条件, 就删除所有记录, 多数时候我们都加上WHERE条件
DELETE FROM 表名 [WHERE 条件];

# 删除id为1的学生
DELETE FROM stu WHERE id=1;

#与DELETE不加WHERE删除整表数据不同，TRUNCATE是把表销毁，再按照原表的格式创建一张新表
TRUNCATE TABLE

5. 约束

5.1 是什么

用于限制加入表的数据的类型和规范，约束是添加在列上的，用来约束列的。

分类：

实体完整性约束；
域完整性约束；
引用完整性约束。

下文整理

约束	说明
PRIMARY KEY	主键约束
PRIMARY KEY AUTO_INCREMENT	主键、自动增长
UNIQUE	唯一约束
NOT NULL	非空约束
FOREIGN KEY	外键约束
FOREIGN KEY ON UPDATE CASCADE	外键级联更新
FOREIGN KEY ON DELETE CASCADE	外键级联删除
default	默认值

5.2 实体完整性约束

5.2.1 主键约束

标识表中的一行数据，是该行数据的唯一标识，特性：

非空；
唯一；
被引用（在外键中使用）。

当表的某一列被指定为主键后，该列就不能为空，不能有重复值出现。

# 创建表时指定主键
CREATE TABLE 表名(
    列名 数据类型 PRIMARY KEY,
    列名 数据类型,
    列名 数据类型
);

# 创建表时指定主键
CREATE TABLE 表名(
    列名 数据类型,
    列名 数据类型,
    列名 数据类型,
    PRIMARY KEY(设置为主键的列名)
);

# 修改表时指定主键
ALTER TABLE 表名 ADD PRIMARY KEY(列名);
ALTER TABLE 表名 MODIFY 列名 列类型 PRIMARY KEY;
ALTER TABLE 表名 CHANGE 原列名 新列名 列类型 PRIMARY KEY;

# 删除主键
ALTER TABLE 表名 DROP PRIMARY KEY;

5.2.2 主键自增长

通常指定主键类为整型，然后设置其自动增长，这样可以保证在插入数据时主键列的唯一和非空特性。

# 创建表时指定主键
CREATE TABLE 表名(
    列名 数据类型 PRIMARY KEY AUTO_INCREMENT,
    列名 数据类型,
    列名 数据类型
);

# 修改表时设置主键自增长
ALTER TABLE 表名 CHANGE 原列名 新列名 列类型 PRIMARY KEY AUTO_INCREMENT;
ALTER TABLE 表名 MODIFY 列名 列类型 PRIMARY KEY AUTO_INCREMENT;

# 修改表时删除主键自增长
ALTER TABLE 表名 CHANGE 原列名 新列名 列类型;

5.2.3 唯一约束

某些列不能设置重复的值，所以可以对列添加唯一约束。

# 创建表时指定唯一约束
CREATE TABLE 表名(
    列名 数据类型 UNIQUE,
    列名 数据类型,
    列名 数据类型
);

# 修改表时设置唯一约束
ALTER TABLE 表名 MODIFY 列名 列类型 UNIQUE;
ALTER TABLE 表名 CHANGE 原列名 新列名 列类型 UNIQUE;

# 删除唯一约束
ALTER TABLE 表名 DROP INDEX name;

5.3 域完整性约束

5.3.1 非空约束

此列必须有值，某些列不能设置为NULL值

# 创建表时指定非空约束
CREATE TABLE 表名(
    列名 数据类型 NOT NULL,
    列名 数据类型,
    列名 数据类型
);

# 修改表时设置非空约束
ALTER TABLE 表名 MODIFY 列名 列类型 NOT NULL;
ALTER TABLE 表名 CHANGE 原列名 新列名 列类型 NOT NULL;

# 删除非空约束
ALTER TABLE 表名 MODIFY 列名 列类型;
ALTER TABLE 表名 CHANGE 原列名 新列名 列类型;

5.3.2 默认值

为列赋予默认值，当新增数据不指定值时，书写DEFAULT，以指定的默认值进行填充。

# 创建表时指定默认值
CREATE TABLE 表名(
    列名 数据类型 DEFAULT 默认值,
    列名 数据类型,
    列名 数据类型
);

# 修改表时设置默认值
ALTER TABLE 表名 MODIFY 列名 列类型 DEFAULT 默认值;
ALTER TABLE 表名 CHANGE 原列名 新列名 列类型 DEFAULT 默认值;

# 删除默认值
ALTER TABLE 表名 MODIFY 列名 列类型;
ALTER TABLE 表名 CHANGE 原列名 新列名 列类型;

5.4 引用完整性约束

这里说的引用完整性约束就是指的外键约束，作用：

引用外部表的某个列的值；
新增数据时，约束此列的值必须是引用表中已经存在的值。

外键的特点：

外键必须是另一表的主键的值(外键要引用主键！)；
外键可以重复；
外键可以为空；
一张表中可以有多个外键。

# 创建表时指定外键约束
CREATE TABLE 表名(
    列名 数据类型,
    列名 数据类型,
    列名 数据类型,
    CONSTRAINT 约束名称 FOREIGN KEY(外键列名) REFERENCES 关联表(关联表的主键)
);

# 修改表时添加外键约束
ALERT TABLE 表名 ADD CONSTRAINT 约束名称 FOREIGN KEY(外键列名) REFERENCES 关联表(关联表的主键);

# 修改表时删除外键约束
ALTER TABLE 表名 DROP FOREIGN KEY 约束名称;

6.DQL

关系结构数据库是以表格（Table）进行数据存储，表格由“行”和“列”组成。

经验：执行查询语句返回的结果集是一张虚拟表。

数据准备

DROP DATABASE if exists mytest;
CREATE DATABASE mytest;
USE mytest;

/*
    部门表
    deptno    部门编号
    dname    部门名称
    loc        部门位置
*/
CREATE TABLE dept(
                     deptno        INT     PRIMARY KEY,
                     dname        VARCHAR(50),
                     loc         VARCHAR(50)
);

/*
    雇员表
    empno        员工编号
    ename        员工姓名
    job            员工工种
    mgr            上级编号
    hiredate    入职日期
    sal            薪资
    comm        奖金
    deptno        部门编号
*/
CREATE TABLE emp(
                    empno        INT     PRIMARY KEY,
                    ename        VARCHAR(50),
                    job        VARCHAR(50),
                    mgr        INT,
                    hiredate    DATE,
                    sal        DECIMAL(7,2),
                    comm         DECIMAL(7,2),
                    deptno        INT,
                    CONSTRAINT fk_emp FOREIGN KEY(mgr) REFERENCES emp(empno)
);

/*
    创建工资等级表
    grade    工资等级编号
    losal    最低工资
    hisal    最高工资
*/
CREATE TABLE salgrade(
                         grade        INT     PRIMARY KEY,
                         losal        INT,
                         hisal        INT
);

/*
    创建学生表
    sid            学生编号
    sname        学生姓名
    age            学生年龄
    gander        学生性别
    province    省份
    tuition        学费
*/
CREATE TABLE stu(
                    sid        INT     PRIMARY KEY,
                    sname        VARCHAR(50),
                    age        INT,
                    gander        VARCHAR(10),
                    province    VARCHAR(50),
                    tuition        INT
);

/*插入dept表数据*/
INSERT INTO dept VALUES (10, '研发部', '北京');INSERT INTO dept VALUES (20, '市场部', '上海');INSERT INTO dept VALUES (30, '行政部', '北京');INSERT INTO dept VALUES (40, '财务部', '北京');
/*插入salgrade表数据*/
INSERT INTO salgrade VALUES (1, 7000, 12000);INSERT INTO salgrade VALUES (2, 12010, 14000);INSERT INTO salgrade VALUES (3, 14010, 20000);INSERT INTO salgrade VALUES (4, 20010, 30000);INSERT INTO salgrade VALUES (5, 30010, 99990);
/*插入emp表数据*/
INSERT INTO emp VALUES (1009, 'Smith', '董事长', NULL, '2001-11-17', 50000, NULL, 10);INSERT INTO emp VALUES (1004, 'Tom', '经理', 1009, '2001-04-02', 29750, NULL, 20);INSERT INTO emp VALUES (1006, 'Lucy', '经理', 1009, '2001-05-01', 28500, NULL, 30);INSERT INTO emp VALUES (1007, 'Lily', '架构师', 1009, '2001-09-01', 24500, NULL, 10);INSERT INTO emp VALUES (1008, 'John', '工程师', 1004, '2007-04-19', 30000, NULL, 20);INSERT INTO emp VALUES (1013, '周杰伦', '工程师', 1004, '2001-12-03', 30000, NULL, 20);INSERT INTO emp VALUES (1002, '周星驰', '销售员', 1006, '2001-02-20', 16000, 3000, 30);INSERT INTO emp VALUES (1003, '成龙', '销售员', 1006, '2001-02-22', 12500, 5000, 30);INSERT INTO emp VALUES (1005, '约翰逊', '销售员', 1006, '2001-09-28', 12500, 14000, 30);INSERT INTO emp VALUES (1010, '鲍威尔', '销售员', 1006, '2001-09-08', 15000, 0, 30);INSERT INTO emp VALUES (1012, '史泰龙', '文员', 1006, '2001-12-03', 9500, NULL, 30);INSERT INTO emp VALUES (1014, '马克思', '文员', 1007, '2002-01-23', 13000, NULL, 10);INSERT INTO emp VALUES (1011, '恩格斯', '文员', 1008, '2007-05-23', 11000, NULL, 20);INSERT INTO emp VALUES (1001, '辛格', '文员', 1013, '2000-12-17', 8000, NULL, 20);INSERT INTO emp VALUES (1033, 'Rose', '经理', 1013, '2011-12-17', 18000, NULL, 20);INSERT INTO emp VALUES (1034, 'Robin', '经理', 1009, '2011-12-17', 18000, NULL, 10);INSERT INTO emp VALUES (1038, 'Bob', '司机', 1009, '2011-12-17', 18000, NULL, 50);
/*插入stu表数据*/
INSERT INTO stu VALUES ('1', 'zhangsan', '23', '男', '北京', '21500');INSERT INTO stu VALUES ('2', 'lisi', '25', '男', '北京', '21500');INSERT INTO stu VALUES ('3', 'wangwu', '22', '男', '北京', '23500');INSERT INTO stu VALUES ('4', 'Herry', '25', '男', '北京', '21500');INSERT INTO stu VALUES ('5', '史莱克', '23', '女', '北京', '21000');INSERT INTO stu VALUES ('6', '汉克斯', '22', '女', '山东', '22500');INSERT INTO stu VALUES ('7', '马克', '21', '女', '北京', '21600');INSERT INTO stu VALUES ('8', '马克思', '23', '男', '北京', '23500');INSERT INTO stu VALUES ('9', 'Hans', '23', '女', '加利福尼亚', '22500');INSERT INTO stu VALUES ('10', '古惑仔', '18', '男', '香港', '21500');INSERT INTO stu VALUES ('11', '琼瑶', '23', '男', '台湾', '21500');INSERT INTO stu VALUES ('12', '杰克逊', '24', '男', '科罗拉多', '21500');INSERT INTO stu VALUES ('13', '博尔特', '24', '男', '华盛顿', '22500');INSERT INTO stu VALUES ('14', '巴斯德', '22', '男', '弗吉尼亚', '23500');INSERT INTO stu VALUES ('15', '克里', '25', '男', '北京', '21500');INSERT INTO stu VALUES ('16', '克林顿', '23', '女', '北京', '21000');INSERT INTO stu VALUES ('17', '希拉里', '22', '女', '山东', '22500');INSERT INTO stu VALUES ('18', '川普', '21', '女', '北京', '21600');INSERT INTO stu VALUES ('19', '尤里', '23', '男', '北京', '23500');INSERT INTO stu VALUES ('20', '加加林', '23', '女', '莫斯科', '22500');INSERT INTO stu VALUES ('21', '特斯拉', '18', '男', '纽约', '23500');INSERT INTO stu VALUES ('22', '拉姆斯菲尔德', '23', '丹佛', '湖北', '21500');INSERT INTO stu VALUES ('23', '盖伊', '23', '男', '佛罗里达', '21500');

6.1. 单表查询

6.1.1 基本查询

1. 查询所有列

# 查询所有列
SELECT * FROM 表名
# 查询所有部门的信息
SELECT * FROM dept;
# 查询所有学生的信息
SELECT * FROM student;

2. 查询指定列

# 查询指定列
SELECT 列1 [, 列2, ...列N] FROM 表名;
# 查询所有部门的名称
SELECT dname FROM dept;
# 查询所有的学生姓名和年龄
SELECT sname, age FROM stu;
# 查询所有的学生姓名，年龄和省份
SELECT sname, age, province FROM stu;

3. 完全重复的记录只出现一次

# 查询所有的工种
SELECT job FROM emp;

# 上面查询出的结果有多条是重复的，我们需要将重复记录去除，重复的记录只出现一次
# 去重操作
# 当查询结果中的多行记录一模一样时，只显示一行
# 一般查询所有列时很少会有这种情况，但只查询一列（或几列）时，这种可能就大了
SELECT DISTINCT job FROM emp;

4. 列运算

算数运算

# 列可以进行加、减、乘、除运算
# 查询所有员工的姓名和工资提升50%之后的工资
SELECT ename, sal*1.5 FROM emp;

# 查询所有员工的姓名和总工资（工资加奖金）
SELECT ename, sal+comm FROM emp; # 注意这个结果有问题，后面会进行处理

字符串连接运算

# MySQL使用CONCAT()进行连接运算, 不能用“+”连接字符串
# 查询所有员工的姓名和工资
SELECT ename, CONCAT(sal, '$') FROM emp;

转换NULL值

# 在进行列运算时，如果某一列值为NULL，那么运算之后的结果也为NULL
# 如果我们不希望最终结果为NULL，那么我们需要在运算之前对NULL值进行转换
# 查询所有员工的姓名和总工资（工资加奖金, 如果奖金为NULL则按照奖金为0进行运算）
SELECT ename, sal+IFNULL(comm, 0) FROM emp;
# IFNULL(列名, 转换值)

别名

# 对列进行运算后，查询出的结果中的列名不便于阅读，这时需要给列名起一个别名
# 格式：列名 AS 别名
# AS也可以省略，格式：列名 别名
# 查询所有员工的姓名和总工资（工资加奖金, 如果奖金为NULL则按照奖金为0进行运算）
SELECT ename AS '姓名', sal+IFNULL(comm, 0) AS '总工资' FROM emp;
SELECT ename '姓名', sal+IFNULL(comm, 0) '总工资' FROM emp;
# 除了可以给列起别名，也可以给表起别名，在多表查询中会使用到为表起别名

5. 条件查询

与前面介绍的UPDATE和DELETE语句一样，SELECT语句也可以使用WHERE子句来控制查询出的记录。

# 查询部门编号为20的所有员工的信息
SELECT * FROM emp WHERE deptno=20

# 查询工种为工程师的所有员工的信息
SELECT * FROM emp WHERE job='工程师'

# 查询有奖金的所有员工的信息
SELECT * FROM emp WHERE comm IS NOT NULL and comm <> 0;
SELECT * FROM emp WHERE comm IS NOT NULL and comm != 0;

6. 模糊查询

模糊 – 不精确

"_"匹配一个任意字符，只匹配一个字符而不是多个

"%"匹配0~N个任意字符

模糊查询需要使用运算符：LIKE

# 查询姓“周”的所有员工的信息
SELECT * FROM emp WHERE ename LIKE '周%';

# 查询姓名中包含“杰”的所有员工的信息
SELECT * FROM emp WHERE ename LIKE '%杰%';

# 查询姓“周”并且姓名只有三个字的所有员工的信息
SELECT * FROM emp WHERE ename LIKE '周__';

6.1.2 排序

有些时候我们需要对查询出的结果进行排序

排序分成升序（ASC）和降序（DESC），可以使用多列作为排序条件

排序使用关键字ORDER BY

# 排序
# 规则：列名 升序/降序
SELECT 列1, 列2, 列3 FROM 表名 WHERE 条件 ORDER BY 规则1, 规则2,....,规则n　

# 查询所有员工信息，按照工号升序排列
SELECT * FROM emp ORDER BY empno ASC;
# 如果是升序，ASC可以省略
SELECT * FROM emp ORDER BY empno;

# 如果设置了多个排序规则，如果规则1顺序相同则按照顺序2排序, 如果规则2顺序相同则按照顺序3排序...
# 查询所有员工信息，按照工资升序排列，如果工资相同，按照工号降序排列
SELECT * FROM emp ORDER BY sal ASC, empno DESC;

6.1.3 聚合函数

聚合函数做某列的纵向运算, 为NULL项不参与运算.

函数功能
COUNT 计算个数
MAX 最大值
MIN 最小值
AVG 平均值
SUM 和

函数	功能
COUNT	计算个数
MAX	最大值
MIN	最小值
AVG	平均值
SUM	和

# 查询公司员工个数
SELECT count(1) FROM emp;
SELECT count(*) FROM emp;

# 查询公司最高工资
SELECT MAX(sal) FROM emp;

# 查询公司最低工资
SELECT MIN(sal) FROM emp;

# 查询公司所有员工工资和
SELECT SUM(sal) FROM emp;

# 查询公司员工工资的平均值
SELECT AVG(sal) FROM emp;

6.1.4 分组查询

分组查询是把记录使用某一列进行分组，然后查询组信息。

分组查询查的是组信息，不能带“个人”信息。

组信息包括：

分组列
聚合函数

分组查询使用GROUP BY关键字。

# 查询每个工种的平均工资
SELECT job, AVG(sal) FROM emp GROUP BY job;

# 查询每个工种的
SELECT job, COUNT(1) FROM emp GROUP BY job;

分组查询也可以设置条件，分组后的条件使用HAVING, 分组前的条件使用WHERE

# 查询工资大于15000的员工的工种，以及工种的平均工资
SELECT job, AVG(sal) FROM emp WHERE sal>15000 GROUP BY job;

# 查询工资大于15000的员工的工种，以及工种的平均工资，只显示超过两人的工种
SELECT job, AVG(sal) FROM emp WHERE sal>15000 GROUP BY job HAVING COUNT(*)>=2;

6.1.5 Limit子句

多用在分页查询中

LIMIT用来限定查询结果的起始行，以及查询行数

# 语法
SELECT 列名 FROM 表名 LIMIT 起始行，查询行数;

# 查询员工表中前五名员工的所有信息
# 起始行是从 0 开始，代表了第一行
SELECT * FROM emp LIMIT 0, 5;

#查询员工表中从第4条开始，查询10行
SELECT * FROM emp LIMIT 3,10;

LIMIT多数时候用在分页查询中

起始行计算公式：（当前页-1）* 每页的记录数

# 查询员工表中第一页信息，显示五条
SELECT * FROM emp LIMIT 0, 5;

# 查询员工表中第二页信息，显示五条
SELECT * FROM emp LIMIT 5, 5;

6.1.6 查询语句执行顺序

SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
LIMIT

6.2 多表查询

使用多表关联查询的原因–查询的数据分布在多个表中

6.2.1 合并结果集

合并结果集就是把两个select语句的查询结果合并到一起，结果集就是一个表格。

要求：被合并的两个结果：列数必须相同。

# UNION：去除重复记录
SELECT * FROM t1 UNION SELECT * FROM t2;
# UNION ALL：不去除重复记录
SELECT * FROM t1 UNION ALL SELECT * FROM t2;

6.2.2 内连接

# 方式1(MySQL特有，不符合SQL标准)
SELECT 列名 FROM 表1, 表2 WHERE 表1.列名 条件运算符 表2.列名 [AND 条件];
# 方式2(符合SQL标准)
SELECT 列名 FROM 表1 INNER JOIN 表2 ON 表1.列名 条件运算符 表2.列名 [WHERE 条件];

# 列出员工的姓名和部门名称
SELECT e.ename, d.dname FROM emp e, dept d WHERE e.deptno=d.deptno;
SELECT e.ename, d.dname FROM emp e INNER JOIN dept d ON e.deptno=d.deptno;

上面的查询只能查询出拥有部门的员工和拥有员工的部门，没有部门的员工和没有员工的部门是查询不到的。如果要将所有的员工和部门都查询出来需要使用外连接。

6.2.3外连接

结果集中包含主表所有数据行，如果主表的某行在从表中没有匹配行时，则从表的选择列为NULL值。

1.左外连接

左外连接是以左表为主表，去关联右表(从表)，结果集中包含主表所有数据行，如果主表的某行在从表中没有匹配行时，则从表的选择列为NULL值。

# 语法
SELECT 列名 FROM 左表 LEFT [OUTER] JOIN 右表 ON 左表.列名  条件运算符 右表.列名 [WHERE 条件];

# 列出员工的姓名和部门名称, 包括没有部门的员工
SELECT e.ename, d.dname FROM emp e LEFT JOIN dept d ON e.deptno=d.deptno;

# 列出员工的姓名和部门名称, 包括没有员工的部门
SELECT e.ename, d.dname FROM dept d LEFT JOIN emp e ON e.deptno=d.deptno;

2.右外连接

右外连接是以右表为主表，去关联左表(从表)，结果集中包含主表所有数据行，如果主表的某行在从表中没有匹配行时，则从表的选择列为NULL值。

# 语法
SELECT 列名 FROM 左表 RIGHT [OUTER] JOIN 右表 ON 左表.列名 条件运算符 右表.列名 [WHERE 条件];

# 列出员工的姓名和部门名称, 包括没有员工的部门
SELECT e.ename, d.dname FROM emp e RIGHT JOIN dept d ON e.deptno=d.deptno;

# 列出员工的姓名和部门名称, 包括没有部门的员工
SELECT e.ename, d.dname FROM dept d RIGHT JOIN emp e ON e.deptno=d.deptno;

3.全外连接

完全连接左表和右表中所有行，当某行数据在另一个表中没有匹配时，则另一个表的选择列值为NULL。

# 语法
SELECT 列名 FROM 左表 FULL [OUTER] JOIN 右表 ON 左表.列名 条件运算符 右表.列名 [WHERE 条件]

MySQL不支持这种语法，可以使用合并结果集进行模拟全外连接。

SELECT e.ename, d.dname FROM emp e LEFT JOIN dept d ON e.deptno=d.deptno
UNION
SELECT e.ename, d.dname FROM emp e RIGHT JOIN dept d ON e.deptno=d.deptno;

6.3 子查询

子查询就是嵌套查询，即SELECT中包含SELECT，如果一条语句中存在两个或两个以上SELECT，那么就是子查询语句了。

子查询出现的位置：

WHERE后，作为条件的一部分；
FROM后，作为被查询的一条表。

6.3.1 where后

# 查询工资最高的员工的信息
SELECT * FROM emp WHERE emp.sal=(最高薪资);
# 查询员工的最高薪资
SELECT MAX(sal) FROM emp;
# 综合
SELECT * FROM emp WHERE emp.sal=(SELECT MAX(sal) FROM emp);


# 查询和文员在同一部门的所有员工的信息
SELECT * FROM emp WHERE emp.deptno=(文员的部门编号);
# Tom的部门编号
SELECT DISTINCT deptno FROM emp WHERE emp.job='文员';
# 综合
SELECT * FROM emp WHERE emp.deptno IN (SELECT DISTINCT deptno FROM emp WHERE emp.job='文员');


# 查询高于30部门所有人工资的员工详细信息
SELECT * FROM emp WHERE sal>ALL(SELECT sal FROM emp WHERE deptno=30);
# 查询高于30部门任意一人工资的员工详细信息
SELECT * FROM emp WHERE sal>ANY(SELECT sal FROM emp WHERE deptno=30);
# 查询工资、部门与周杰伦完全相同的员工信息
SELECT * FROM emp WHERE (sal, deptno) IN (SELECT sal, deptno FROM emp WHERE ename='周杰伦');

6.3.2 from后

# 列出在每个部门名称、员工数量、平均工资
SELECT
    d.dname, e.cnt, e.avgsal
FROM
    (SELECT deptno, COUNT(*) cnt, AVG(sal) avgsal FROM emp GROUP BY deptno) e, dept d
WHERE e.deptno=d.deptno;

7.DCL

数据控制语言 (Data Control Language) 在SQL语言中，是一种可对数据访问权进行控制的指令，它可以控制特定用户账户对数据表、查看表、存储程序、用户自定义函数等数据库对象的控制权。

7.1 创建用户

# 语法
CREATE USER 用户名@地址 IDENTIFIED BY '密码';

# 创建用户user1，密码设置为1234
CREATE USER user1@localhost IDENTIFIED BY '1234';

7.2 用户授权

# 语法
GRANT 权限1, …, 权限n ON 数据库.* TO 用户名;

# 将mydb1下的所有表的特定权限都赋给user1
GRANT CREATE, ALTER, DROP, INSERT, UPDATE, DELETE, SELECT ON mydb1.* TO user1@localhost;
# 将mydb1下的所有表的所有权限都赋给user1
GRANT ALL ON mydb1.* TO user1@localhost;

7.3 查看用户权限

# 语法
SHOW GRANTS FOR 用户名@主机名;

# 查看user1的权限
SHOW GRANTS FOR user1@localhost;

7.4 撤销权限

# 语法
REVOKE 权限1, … , 权限n ON 数据库.* FORM 用户名

# 撤销user1的在数据库mydb1上的部分权限
REVOKE create,alter,drop ON mydb1.* from user1@localhost;
# 撤销user1的在数据库mydb1上的所有权限
REVOKE ALL ON mydb1.* from user1@localhost;

7.5 删除用户

# 语法
DROP USER 用户名;

# 删除用户user1
DROP USER user1@localhost;

7.6 修改用户密码

# 语法
USE mysql;
UPDATE USER SET authentication_string=PASSWORD('密码') WHERE USER='用户名' and host='IP';
FLUSH PRIVILEGES;

# 修改user1的密码为user1
USE mysql;
UPDATE USER SET authentication_string=PASSWORD('user1') WHERE USER='user1' and host='localhost';
FLUSH PRIVILEGES;

8. TPL

8.1 什么是事务

一个操作序列，这些操作要么都执行，要么都不执行，它是一个不可分割的工作单位。

事务要处理的问题，把多个对数据库的操作绑定成一个事务，要么都成功，要么都失败。

8.2 MySQL中处理事务

MySQL中处理事务涉及三个操作：

开启事务
提交事务
回滚

# 开启事务
START TRANSACTION;

# 多个SQL操作

# 提交事务
COMMIT;
# 回滚
ROLLBACK;

在执行SQL语句之前，先执行START TRANSACTION，这就开启了一个事务（事务的起点），然后可以去执行多条SQL语句;

后要结束事务，COMMIT表示提交，即事务中的多条SQL语句所做出的影响会持久化到数据库中，或者ROLLBACK，表示回滚，即回滚到事务的起点，之前做的所有操作都被撤消了；

# 开启事务
START TRANSACTION;

# 多个SQL操作
# A账户转账给B账户1000元
# A账户减1000元
UPDATE account SET money=money-1000 WHERE cardnum='10001';
# B账户加1000元
UPDATE account SET money=money+1000 WHERE cardnum='10002';

# 提交事务
COMMIT;
# 回滚
ROLLBACK;

8.3 事务原理

数据库会为每一个客户端都维护一个空间独立的缓存区(回滚段)，一个事务中所有的增删改语句的执行结果都会缓存在回滚段中，只有当事务中所有SQL语句均正常结束（COMMIT），才会将回滚段中的数据同步到数据库。否则无论因为哪种原因失败，整个事务将回滚（ROLLBACK）。

8.4 事务特性【记忆】

原子性（Atomicity）：事务中所有操作作为一个整体，是不可再分割的原子单位。事务中所有操作要么全部执行成功，要么全部执行失败。

一致性（Consistency）：事务执行后，数据库状态与其它业务规则保持一致。如转账业务，无论事务执行成功与否，参与转账的两个账号余额之和应该是不变的。

隔离性（Isolation）：隔离性是指在并发操作中，不同事务之间应该隔离开来，使每个并发中的事务不会相互干扰。

持久性（Durability）：一旦事务提交成功，事务中所有的数据操作都必须被持久化到数据库中，即使提交事务后，数据库马上崩溃，在数据库重启时，也必须能保证通过某种机制恢复数据。

9. 视图

9.1 概念

视图，虚拟表，从一个表或多个表中查询出来的表，作用和真实表一样，包含一系列带有行和列的数据。视图中，用户可以使用SELECT语句查询数据，也可以使用INSERT，UPDATE，DELETE修改记录，视图可以使用户操作方便，并保障数据库系统安全。

9.2 特点

优点
简单化，数据所见即所得。
安全性，用户只能查询或修改他们所能见到得到的数据。
逻辑独立性，可以屏蔽真实表结构变化带来的影响。
缺点
性能相对较差，简单的查询也会变得稍显复杂。
修改不方便，特变是复杂的聚合视图基本无法修改。

9.3 视图的操作

9.3.1 创建视图

# 语法
CREATE VIEW 视图名 AS 查询语句;

# 创建emp_info视图，通过视图从emp表中查询员工编号、姓名、总工资
CREATE VIEW emp_info
AS
SELECT empno '编号', ename '姓名', sal+IFNULL(comm, 0) '总工资' FROM emp;

9.3.2 使用视图

# 查询编号为1003的员工信息
SELECT * FROM emp_info WHERE 编号=1003;

9.3.3 修改视图

# 语法 方式1
CREATE OR REPLACE VIEW 视图名 AS 查询语句;
# 语法 方式2
ALTER VIEW 视图名 AS 查询语句;

CREATE OR REPLACE VIEW emp_info
AS
SELECT empno '编号', ename '姓名', sal '基本工资' FROM emp;

ALTER VIEW emp_info
AS
SELECT empno '编号', ename '姓名', sal '基本工资' FROM emp;

9.3.4 删除视图

删除视图不会影响原表

# 语法
DROP VIEW 视图名;

DROP VIEW emp_info;

9.4 注意事项【记忆】

视图不会独立存储数据，原表发生改变，视图也发生改变。没有优化任何查询性能。

如果视图包含以下结构中的一种，则视图不可更新

聚合函数的结果
DISTINCT 去重后的结果
GROUP BY 分组后的结果
HAVING 筛选过滤后的结果
UNION、UNION ALL 联合后的结果