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 DATABASES;
选择数据库：USE 数据库名;
查看数据库的定义信息：SHOW CREATE DATABASE 数据库名;

数据库修改

ALTER DATABASE 数据库名 CHARACTER SET utf8;

数据库删除

DROP DATABASE 数据库名;
DROP DATABASE IF EXISTS 数据库名; //如果数据库存在，则删除这个数据库

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

数据表创建

创建表语法：

create table 数据表名 (
    列名1 属性,
    列名2 属性…
   )


* 列属性：列名 数据类型 [NOT NULL | NULL] [DEFAULT 默认值] [AUTO_INCREMENT]
[PRIMARY KEY ] [注释]
  *  NOT NULL | NULL：该列是否允许是空值，默认为NULL
  *  DEFAULT：表示默认值
  *  AUTO_INCREMENT：表示是否是自动编号
  *  PRIMARY KEY：表示是否为主键

复制表语法： create table 数据表名 like 源数据表
- LIKE 源数据表名：必选项，指定依照哪个数据表来创建新表

数据表查询

查看表名语法：SHOW TABLES;
查看表结构语法：
- 查看表结构：DESCRIBE 数据表名; / DESCRIBE 数据表名列名;
- 查看表结构简写：DESC 数据表名; / DESC 数据表名列名;

数据表修改

添加新列：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 条件];
- GROUP BY 分组列：按哪些列进行分组，搜集数据行到各个组中，统计函数为各个组计算统计值
- HAVING：对分组结果再次过滤（where是在结果之前做过滤）

限制 - 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) )
- 给存在的表添加主键，通过 DDL 语句进行设置
  - ALTER TABLE 表名 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

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

多表查询 - 内连接查询

内连接（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……
- WITH temp_dept AS (SELECT order_list.dept_id,city, manager,SUM( volume ) total_volume FROM order_list INNER JOIN department ON order_list.dept_id = department.dept_id GROUP BY order_list.dept_id ORDER BY SUM( volume ) DESC ) ;
  SELECT * FROM temp_dept WHERE total_volume >(SELECT AVG( total_volume ) FROM temp_dept);