jck28 - 小柒 -SQL语法与数据库

1269967068_3915 · 2023 年11 月 20 日 10:08

一，基本介绍

1.1，简介

数据库(DataBase) 就是一个以某种有组织的方式存储的数据集合
是存储和管理数据的仓库
其本质是一个文件系统
数据库管理系统（DBMS）是一款管理软件

1.2，分类

关系型数据库（RDB: Relationship DataBase）
非关系型数据库（NoSQL）

1.3，常用数据库

关系型数据库（RDBMS）：MySQL、Oracle、Postgres、SQLite、SQLServer
NoSQL 数据库：MongoDB、Redis、HBase、Neo4j、NewSQL

1.4，使用场景

关系型数据库
- 需要做复杂处理数据
- 数据量不是特别大的数据
- 对安全性能要求高的数据
- 数据格式单一的数据
非关系型数据库（NoSQL）
- 数据模型比较简单
- 需要灵活性更强的 IT 系统
- 对数据库性能要求较高
- 不需要高度的数据一致性

二，SQL安装与分类

2.1 Mac系统安装MySQL

官方下载：https://dev.mysql.com/downloads/mysql/
网盘下载：
- 百度网盘-链接不存在
- 提取码：gxow
安装步骤：Mysql8.0.27.1安装 Win ｜ Mac - #4，来自 haozi

环境变量配置：

进入 .bash_profile 文件
添加内容：
- export PATH=$PATH:/usr/local/mysql/bin
- export PATH=$PATH:/usr/local/mysql/support-files

2.2 Windows系统安装MySQL

官方下载：https://dev.mysql.com/downloads/cluster/
网盘下载：
- 百度网盘-链接不存在
- 提取码：gxow
安装步骤：Mysql8.0.27.1安装 Win ｜ Mac

环境变量配置：

新建系统变量 mysql，值为 mysql 安装路径
path 变量中添加 %mysql%\bin

2.3 数据库的命令行操作

开启 mysql 服务：net start mysql
登录：mysql -h主机IP -u用户名 -p密码
修改密码：alter user 'root'@'localhost' identified by '密码';
退出：exit
关闭 mysql 服务：net stop mysql

2.4 sql介绍

目录结构：

数据库表：

表：包含数据库中所有数据的数据库对象
- 表名：每个表的唯一标识
- 模式（schema）：关于数据库和表的布局及特性的信息
- 列：表中每列称为一个字段
- 行：表中的一个记录

2.5 分类

数据定义语言（DDL）：用来定义数据库对象，比如数据库，表，列等
数据操作语言（DML）：用来对数据库中表的记录进行更新
数据查询语言（DQL）：用来查询数据库中表的记录
数据控制语言（DCL）：用来定义数据库的访问权限和安全级别及创建用户

三，数据库操作

3.1 DDL 数据库操作 - 增删改查

————————————————————————————————————————————

创建

创建数据库语法：create {DATABASE|SCHEMA} [IF NOT EXISTS] 数据库名 CHARACTER SET [=] 字符集
- create DATABASE student; (创建一个名为student的数据库）
- create DATABASE person CHARACTER SET utf8; (创建一个名为person 的数据库，指定字符集为utf8）
- create DATABASE IF NOT EXISTS son CHARACTER SET utf8; (如果数据库son不存在就创建名为son的数据库）
创建数据库注意事项
- 不能与其他数据库重名
- 名称可以由任意字母、阿拉伯数字、下划线（_）和“$”组成，但不能使用单独的数字
- 名称最长可为 64 个字符，别名最长为 256 个字符。
- 不能使用 MySQL 关键字作为数据库名
- 建议采用小写来定义数据库名

————————————————————————————————————————————

查看

查看表名语法：SHOW TABLES;
查看表结构语法：
- 查看表结构：DESCRIBE 数据表名; / DESCRIBE 数据表名列名;
- 查看表结构简写：DESC 数据表名; / DESC 数据表名列名;
实例
- 选择数据库：USE db1;
- 查看数据库中的表：SHOW TABLES;
- 查看学员表的表结构：DESC student;
- 查看学员表中 name 列的信息：DESC student name;

————————————————————————————————————————————

修改

添加新列：ALTER TABLE 表名 ADD 列名列属性;
- ALTER TABLE student ADD email varchar(50) NOT NULL;
修改列定义：ALTER TABLE 表名 MODIFY 列名列属性;
- ALTER TABLE student modify score int;
修改列名：ALTER TABLE 表名 CHANGE 旧列名新列名类型;
- ALTER TABLE student CHANGE COLUMN name stu_name VARCHAR(30) DEFAULT NULL
删除列：ALTER TABLE 表名 DROP 列名;
修改表名：ALTER TABLE 旧表名 RENAME AS 新表名; / RENAME TABLE 旧表名 To 新表名;

————————————————————————————————————————————

删除

删除表语法：DROP TABLE [IF EXISTS] 数据表名

————————————————————————————————————————————

3.2 DML 数据库表操作 - 增删改

————————————————————————————————————————————

插入数据

表数据插入语法：INSERT INTO 数据表名 (列名1, 列名2…) VALUES(值1, 值2…);
完整插入数据
- INSERT INTO user (id,name,age,sex,address) VALUES(1,‘张三’,20,‘男’,‘北京’);
- INSERT INTO user VALUES (2,‘李四’,22,‘女’,‘上海’);
插入数据记录的一部分
- INSERT INTO user (id,name,address) VALUES(3,‘王五’,‘深圳’);
插入多条记录
- INSERT INTO user (id,name,address) VALUES(4,‘赵六’,‘天津’),(5,‘小红’,‘成都’),(6,‘小白’,‘杭州’);
注意事项
1. 值与字段必须要对应，个数相同并且数据类型相同
2. 值的数据大小，必须在字段指定的长度范围内
3. VARCHAR ，CHAR， DATE 类型的值必须使用单引号包裹
4. 如果要插入空值，可以忽略不写，或者插入 NULL
5. 如果插入指定字段的值，必须要上写列名

————————————————————————————————————————————

修改数据

表数据修改语法：UPDATE 数据表名 SET 列名1=值1 [, 列名2=值2…] [WHERE 条件表达式]

————————————————————————————————————————————

删除数据

通过 DELETE 语句删除数据：DELETE FROM 数据表名 WHERE 条件表达式
通过 TRUNCATE TABLE 语句删除表中所有数据：TRUNCATE TABLE 数据表名

————————————————————————————————————————————

3.3 DQL 数据库表操作 - 查询

————————————————————————————————————————————

条件查询

单表查询：SELECT * FROM 表名;
字段查询：SELECT 列名 FROM 表名;
起别名：
- 为表起别名： SELECT 列名 FROM 表名表别名;
- 为字段起别名：SELECT 列名 AS 别名 FROM 表名;
去重：SELECT DISTINCT 列名 FROM 表名;
运算查询： SELECT (列名运算表达式) FROM 表名;
- SELECT emp_no , salary + 1000 FROM salaries;
条件查询：SELECT 列名 FROM 表名 WHERE 条件表达式
比较运算符：
- 符号 > , <, <=, >=, =, <>, != ：大于、小于、小于等于、大于等于、等于、不等于
- BETWEEN…AND… ：范围限定（模糊查询）
- IN ：子集限定（模糊查询）
- LIKE ‘%or%’ ：模糊查询
- IS NULL ：
逻辑运算符：
- AND 、 && ：多个条件同时成立
- OR 、｜：多个条件任一成立
- NOT：不成立
通配符：
- % ：匹配任意多个字符
- ‘- ’ ：匹配1个字符：SELECT FROM employees WHERE first_name LIKE ‘fa_’;

————————————————————————————————————————————

排序

语法：SELECT 列名 FROM 表名 [WHERE 条件表达式] ORDER BY 列名1 [ASC / DESC], 列名2 [ASC / DESC]
ASC - 升序排序（默认） DESC - 降序排序

———————————————————————————————————————————

聚合函数

COUNT()：统计指定列不为 NULL 的记录行数
MAX()：计算指定列的最大值
MIN()：计算指定列的最小值
SUM()：计算指定列的数值和
AVG()：计算指定列的平均值

———————————————————————————————————————————

分组

SELECT 分组列/聚合函数 FROM 表名 GROUP BY 分组列 [HAVING 条件];
- 分组列：按哪些列进行分组
- HAVING：对分组结果再次过滤

———————————————————————————————————————————

限制 - limit关键字

SELECT * FROM 表名 LIMIT [开始的行数], <查询记录的条数>

– 使用 OFFSET 关键字指定开始的行数

SELECT * FROM 表名 LIMIT <查询记录的条数> OFFSET <开始的行数>

———————————————————————————————————————————

主键约束

对表中的数据进行进一步的限制
保证数据的正确性、有效性、完整性
违反约束的不正确数据无法插入到表中
常见的约束
- 主键：PRIMARY KEY
- 非空：NOT NULL
- 唯一：UNIQUE
- 默认：DEFAULT
- 外键：FOREIGN KEY

PRIMARY KEY

主键：一列（或一组列），其值能够唯一标识表中每一行
特点：不可重复，唯一，非空
语法：列名字段类型 PRIMARY KEY
举例：
– 创建一个带主键的表
- CREATE TABLE emp1( eid INT PRIMARY KEY, ename VARCHAR(20), sex CHAR(1) )
  – 给存在的表添加主键
- CREATE TABLE emp2( eid INT , ename VARCHAR(20), sex CHAR(1) )
  – 通过 DDL 语句进行设置
- ALTER TABLE emp2 ADD PRIMARY KEY(eid);
  – 创建主键自增的表 (AUTO_INCREMENT表示自动增长，数据类型必须是整数类型）
- CREATE TABLE emp3( eid INT PRIMARY KEY AUTO_INCREMENT, ename VARCHAR(20), sex CHAR(1) );
  – 创建主键自增的表,自定义自增其实值
- CREATE TABLE emp4( eid INT PRIMARY KEY AUTO_INCREMENT, ename VARCHAR(20), sex CHAR(1) )AUTO_INCREMENT=100;
  – 删除表中的主键
- ALTER TABLE 表名 DROP PRIMARY KEY;

—————————————————————————————————————————————

非空约束

非空约束特点: 某一列不予许为空
语法：列名字段类型 NOT NULL
举例：
– 添加非空约束
- CREATE TABLE emp5( eid INT PRIMARY KEY AUTO_INCREMENT, ename VARCHAR(20) NOT NULL, sex CHAR(1) );

—————————————————————————————————————————————

唯一约束

唯一约束: 表中的某一列的值不能重复
对 NULL 不做唯一的判断
语法：列名字段类型 UNIQUE
举例：
– 创建带有唯一约束的表
- CREATE TABLE emp6( eid INT PRIMARY KEY AUTO_INCREMENT, ename VARCHAR(20) UNIQUE, sex CHAR(1) );
主键约束与唯一约束的区别
- 1. 主键约束，唯一且不能够为空
- 1. 唯一约束，唯一但是可以为空
- 1. 一个表中只能有一个主键，但是可以有多个唯一约束

—————————————————————————————————————————————

默认值

默认值约束：用来指定某列的默认值
语法：列名字段类型 DEFAULT 默认值
举例：
– 创建带有默认值的表
*CREATE TABLE emp7( eid INT PRIMARY KEY AUTO_INCREMENT, ename VARCHAR(20),
sex CHAR(1) DEFAULT ‘女’);

—————————————————————————————————————————————

3.2 多表查询

外键约束

主键：可以唯一标识一条记录的列
外键：从表中与主表的主键对应的字段
主表：外键所指向的表,约束其他表的表
从表：外键所在的表，被约束的表
价值：建立主表与从表的关联关系，为两个表的数据建立连接，约束两个表中数据的一致性和完整性

建立外键约束：

创建表时添加外键约束：CONSTRAINT [外键约束的名称] FOREIGN KEY (外键字段) REFERENCES [主表名称(主键字段)]
添加外键约束：ALTER TABLE [表名] ADD CONSTRAINT [外键约束的名称] FOREIGN KEY [外键字段] REFERENCES [主表名称(主键字段)]

# 创建一个关联到主表的从表
CREATE TABLE emp_part(  
emp_id INT PRIMARY KEY AUTO_INCREMENT,  
ename VARCHAR(20),  
age INT ,  
gender VARCHAR(10),
dept_id INT,
-- 添加外键约束 
CONSTRAINT emp_dept FOREIGN KEY(dept_id) REFERENCES dept(id)

删除外键约束

语法：
- ALTER TABLE [表名] DROP FOREIGN KEY [外键约束名称]
注意事项
- 从表外键数据类型必须与主表的主键一致
- 删除数据时，需先删除从表数据再删除主表的数据
- 添加数据时先添加主表数据，再添加从表数据

# 删除外键约束 
ALTER TABLE emp_part DROP FOREIGN KEY emp_dept 

# 删除主表中的数据 
DELETE FROM dept WHERE id=2

级联删除

删除主表数据的同时,也删除掉从表中相关的数据
- ON DELETE CASCADE ```sql

CREATE TABLE emp_part( 
emp_id INT PRIMARY KEY AUTO_INCREMENT,
ename VARCHAR(20),
age INT ,
gender VARCHAR(10), dept_id INT,
CONSTRAINT emp_dept FOREIGN KEY(dept_id) REFERENCES dept(id) 
– 设置允许级联删除 ON DELETE CASCADE ); 

# 向员工信息表中添加一条数据
 INSERT INTO emp_part VALUES(1,‘cindy’,20,‘female’,‘2’) 
#删除主表中部门id=2的部门 
DELETE FROM dept WHERE id=2
 # 查看从表中的数据是否同时被删除 
SELECT * FROM emp_part

—————————————————————————————————————————————

多表关系

一对多
- 定义：主表的一条记录可以对应从表的多条记录
- 建表原则：在一对多关系中，多的表定位从表，设置外键指向主表
多对多
- 定义：主表的多条记录可以对应从表的多条记录
- 例子：商品信息表，客户表，订单表
- 建表原则：需要创建第三张表作为中间表，中间表需要包含两张表的主键。
一对一
- 定义：从表的一条记录对应主表的一条记录
- 例子：员工信息表与身份证表,联系方式
- 建表原则：这种对应关系的数据，通常放在单表里

—————————————————————————————————————————————

多表查询 - 笛卡尔乘积

语法：select 字段名称 from 表1, 表2 where [条件];

—————————————————————————————————————————————

多表查询 - 内连接查询

内连接（INNER JOIN）：使用比较运算符进行表间某（些）列数据的比较操作，并列出这些表中与连接条件相匹配的数据行，组合成新的记录。匹配上显示，匹配不上不显示。
按语法结构分为：隐式内连接和显式内连接
隐式内连接
- 在笛卡尔积的的基础上，使用where条件过滤无用的数据，这种连接方式是隐式内连接.
- 语法：select [字段名称] from 表1，表2 where [条件]
- 例1： SELECT emp_id,ename,dept_location FROM emp_part,dept WHERE dept_id=id and dept_name=“运营部”;
显式内连接
- 显式内连接：使用 select [字段名称] from [表1]inner join [表2] on [条件] 这种方式
- 例2： SELECT emp_id,dept_location,ename FROM emp_part INNER JOIN dept ON dept_id=id and dept_name=“运营部”;

—————————————————————————————————————————————

多表查询 - 外连接查询

外连接查询：查询多个表中相关联的行，有时候需要包含没有关联的行中数据，即返回查询结果集合中不仅包含符合连接条件的行，还包括左表（左连接）、右表（右连接）中的所有数据行。
根据语法结构分为左连接和右连接
左连接
- 左连接：以左表为基准匹配右表的数据，右表中没有的项，显示为空
- 语法：SELECT [字段] FROM [左表] LEFT JOIN [右表] ON [条件]
- 例子：SELECT emp_id,ename,gender,dept_name,dept_location FROM dept LEFT JOIN emp_part ON dept.id=emp_part.dept_id;
右连接
- 右连接：以右表为基准匹配左表的数据，左表中没有的项，显示为空
- 语法：SELECT [字段] FROM [左表] RIGHT JOIN [右表] ON [条件]
- 例子：SELECT emp_id,ename,gender,dept_name,dept_location FROM dept RIGHT JOIN emp_part ON dept.id=emp_part.dept_id;

—————————————————————————————————————————————

多表查询 - 子查询

定义：子查询指一个查询语句嵌套在另一个查询语句内部，在SELECT子句中先计算子查询，子查询的结果作为外层另一个查询的过滤条件，查询可以基于一个表或者多个表。这个特性从MySQL 4.1开始引入。
子查询作为过滤条件时需要用（） 包裹
常见子查询分类
- From型子查询：将子查询的结果作为父查询的表来使用
  - select dept_name,count(emp_id) from
    (select dept_name, emp_id,ename,gender from dept inner join emp_part where id=dept_id and gender=‘男’)b group by dept_name;
- in/not in 型子查询：子查询的结果是单列多行，作为where的过滤条件
  - SELECT * FROM emp_part WHERE dept_id IN (SELECT id FROM dept WHERE dept_location = ‘北京’)
- where型子查询：查询结果作为过滤条件出现在比较运算符的一端
  - SELECT emp_id,ename,salary FROM emp_part WHERE salary > (SELECT AVG(salary) FROM emp_part);
with as
- 如果一整句查询语句中，某个子查询的结果会被多个父查询引用，通常建议将共用的子查询用简写表示出来
- 语法： with [表名] as （select…)

# 查询出部门平均薪资大于公司平均薪资的部门名称，部门主管，所在地及部门平均薪资

# 不使用 with ...as
select dept_id,dept_name,dept_manager,dept_location,avg_salary from dept inner join 
(select dept_id,avg(salary) avg_salary from emp_part group by dept_id)b on id=dept_id 
and avg_salary > (select avg(avg_salary) from 
(select dept_id,avg(salary) avg_salary from emp_part group by dept_id)b);

# 使用 with ...as
with dept_avg as
(select dept_id,avg(salary) avg_salary from emp_part group by dept_id)select
dept_id,dept_name,dept_manager,dept_location,avg_salary from dept inner join dept_avg 
on id=dept_id 
and avg_salary > (select avg(avg_salary) from dept_avg);

—————————————————————————————————————————————

知识扩展-视图

定义：视图是一种虚拟的表，它并不会在你的存储空间复制一份数据，而是对原有数据的一种引用。可以将视图理解为一种存储起来的sql语句
视图可以简化多表查询
视图也可以用于控制用户权限
使用关键词view来创建视图
语法：CREATE VIEW [视图名称] AS SELECT……