网站内容关键词,建网站难吗?,住建部四库一平台查询入口,做哪些网站流量最大# 数据库的基本概念 * 数据库简介 * 数据库分类 * 常用数据库简介 * 使用场景 * 数据库(DataBase) 就是一个以某种有组织的方式存储的数据集合 * 是存储和管理数据的仓库 * 其本质是一个文件系统 * 数据库管理系统#xff08;DBMS#xff09;是一款管理软件 ## 数据库分类
*…# 数据库的基本概念 * 数据库简介 * 数据库分类 * 常用数据库简介 * 使用场景 * 数据库(DataBase) 就是一个以某种有组织的方式存储的数据集合 * 是存储和管理数据的仓库 * 其本质是一个文件系统 * 数据库管理系统DBMS是一款管理软件 ## 数据库分类
* 关系型数据库RDB: Relationship DataBase * 非关系型数据库NoSQL ## 常用数据库简介
* 关系型数据库RDBMS * MySQL、Oracle、Postgres、SQLite、SQLServer * NoSQL 数据库MongoDB、Redis、HBase、Neo4j * NewSQL
## 使用场景
* 关系型数据库 * 需要做复杂处理数据 * 数据量不是特别大的数据 * 对安全性能要求高的数据 * 数据格式单一的数据 * 非关系型数据库NoSQL * 数据模型比较简单 * 需要灵活性更强的 IT 系统 * 对数据库性能要求较高 * 不需要高度的数据一致性
## 数据库分类
* 关系型数据库RDB: Relationship DataBase * 非关系型数据库NoSQL ## 常用数据库简介
* 关系型数据库RDBMS * MySQL、Oracle、Postgres、SQLite、SQLServer * NoSQL 数据库MongoDB、Redis、HBase、Neo4j * NewSQL ## 使用场景
* 关系型数据库 * 需要做复杂处理数据 * 数据量不是特别大的数据 * 对安全性能要求高的数据 * 数据格式单一的数据 * 非关系型数据库NoSQL * 数据模型比较简单 * 需要灵活性更强的 IT 系统 * 对数据库性能要求较高 * 不需要高度的数据一致性
# MySQL 的安装与配置
* Mac 系统安装 MySQL * Mac 系统环境变量配置 * Mac 系统启动与关闭 MySQL 服务 * Windows 系统安装 MySQL * Windows 系统环境变量配置 * Windows 系统启动与关闭 MySQL 服务 * 数据库的命令行操作
## Mac 系统安装 MySQL
* 官方下载https://dev.mysql.com/downloads/mysql/ * 网盘下载 * https://pan.baidu.com/s/1VtEXIogo_GS7iGh3f0nklw * 提取码gxow * 安装步骤https://ceshiren.com/t/topic/16070/4
## Mac 系统环境变量配置
* 进入 .bash_profile 文件 * 添加内容 export PATH$PATH:/usr/local/mysql/bin export PATH$PATH:/usr/local/mysql/support-files
## Mac 系统启动与关闭 MySQL 服务
* 系统偏好设置 * 点击 MySQL 图标 * 点击 Start MySQL Server 启动服务 * 点击 Stop MySQL Server 关闭服务
## Windows 系统安装 MySQL
* 官方下载https://dev.mysql.com/downloads/cluster/ * 网盘下载 * https://pan.baidu.com/s/1VtEXIogo_GS7iGh3f0nklw * 提取码gxow * 安装步骤https://ceshiren.com/t/topic/16070
## Windows 系统环境变量配置
* 新建系统变量 mysql值为 mysql 安装路径 * path 变量中添加 %mysql%\bin
## Windows 系统启动与关闭 MySQL 服务
* 右键此电脑选择管理 * 选择服务 * 找到MySQL 服务 * 鼠标右键选择启动或者停止
## 数据库的命令行操作
* 开启 mysql 服务net start mysql * 登录mysql -h主机IP -u用户名 -p密码 * 修改密码alter user rootlocalhost identified by 密码; * 退出exit * 关闭 mysql 服务net stop mysql
# 数据库客户端工具 ## 目录
* WorkBench * Navicat
## WorkBench 安装
* 官方提供的图形界面交互工具 * 官网下载https://dev.mysql.com/downloads/workbench/ * 网盘下载 * https://pan.baidu.com/s/1VtEXIogo_GS7iGh3f0nklw * 提取码gxow
## WorkBench 使用 - 连接 MySQL
* Name: 可以自定义 * Method: TCP/IP * Hostname: sql 服务地址如果是本地就保持127.0.0.1 * Port: 3306 ## SQL 练习数据库
* hostmysql.hogwarts.ceshiren.com * usernamestu * passwordhogwarts_stu ## WorkBench 使用 - 创建新数据库 ## WorkBench 使用 - 创建表 ## WorkBench 使用 - 查看表数据 ## Navicat 安装
* 非常流行的数据库管理 * 官网下载https://www.navicat.com.cn/products#navicat ## Navicat 使用 - 连接数据库 ## Navicat 使用 - 创建新数据库 ## Navicat 使用 - 创建表 ## Navicat 使用 - 查看表数据 ## MySQL 目录结构
* 安装目录 * 配置文件my.ini 文件 * 数据表目录data
|目录|内容| | --- | --- | |bin|可执行文件| |lib|依赖库| |docs|文档| |include|包含文件| |share|存放字符集等信息|
* 表包含数据库中所有数据的数据库对象 * 表名每个表的唯一标识 * 模式schema关于数据库和表的布局及特性的信息 * 列表中每列称为一个字段 * 行表中的一个记录
## SQL 是什么
* 结构化查询语言Structured Query Language简称 SQL * 一种特殊目的的编程语言 * 一种数据库查询和程序设计语言 * 用于存取数据以及查询、更新和管理关系数据库系统
* 可以单行或者多行书写以分号结尾 ; * 可以使用空格和缩进来增加语句的可读性 * 不区分大小写一般关键字大写数据库名 表名列名小写 * 注释方式 # 单行注释MySQL 特有的单行注释
-- 单行注释
/* 多行注释 */ ## 分类
* 数据定义语言DDL用来定义数据库对象比如数据库表列等 * 数据操作语言DML用来对数据库中表的记录进行更新 * 数据查询语言DQL用来查询数据库中表的记录 * 数据控制语言DCL用来定义数据库的访问权限和安全级别及创建用户
# DDL 数据库操作 - 创建 ## 创建数据库语法
* IF NOT EXISTS可选项创建前先判断未存在时才执行创建语句 * 数据库名必须指定的 * CHARACTER SET 字符集可选项用于指定数据库的字符集 -- 创建数据库 CREATE {DATABASE|SCHEMA} [IF NOT EXISTS] 数据库名 CHARACTER SET [] 字符集 ## 创建数据库注意事项
* 不能与其他数据库重名 * 名称可以由任意字母、阿拉伯数字、下划线_和“$”组成但不能使用单独的数字 * 名称最长可为 64 个字符别名最长为 256 个字符。 * 不能使用 MySQL 关键字作为数据库名 * 建议采用小写来定义数据库名 ## 创建基本数据库 -- 创建名为 test_db 的数据库 CREATE DATABASE test_db; ## 创建指定字符集的数据库 -- 创建名为 test_db2 的数据库并指定字符集为 utf8 CREATE DATABASE test_db2 CHARACTER SET utf8;
## 创建数据库前判断是否存在同名数据库 -- 如果数据库 test_db3 不存在则创建名为 test_db3 的数据库 CREATE DATABASE IF NOT EXISTS test_db3 CHARACTER SET utf8;
## 查看数据库语法
* DATABASES必选项用于列出当前用户权限范围内所能查看到的所有数据库名称 -- 查看所有数据库 SHOW DATABASES; ## 选择数据库语法 -- 选择数据库为当前数据库 USE 数据库名; ## 查看数据库的定义信息语法 -- 查看数据库定义信息 SHOW CREATE DATABASE 数据库名;
## 实例 -- 查看当前所有数据库 SHOW DATABASES; -- 选择数据库 test_db USE test_db; -- 查看 test_db 数据库的定义信息 SHOW CREATE DATABASE 数据库名; ## 改数据库语法
* DATABASE必选项 * 数据库名可选项如果不指定要修改的数据库那么将表示修改当前默认的数据库 * CHARACTER SET 字符集可选项用于指定数据库的字符集 -- 修改数据库相关参数 ALTER {DATABASE} [数据库名] CHARACTER SET [] 字符集
## 修改数据库字符集 -- 创建数据库 db1指定字符集为 GBK CREATE DATABASE db1 CHARACTER SET GBK; -- 将数据库 db1 的字符集修改为 utf8 ALTER DATABASE db1 CHARACTER SET utf8; ## 删除数据库语法
* DATABASES必选项 * IF EXISTS用于指定在删除数据前先判断该数据库是否已经存在可以避免删除不存在的数据库时产生异常 -- 删除数据库 DROP DATABASE [IF EXISTS] 数据库名;
## 删除某个数据库 -- 查看当前所有数据库 SHOW DATABASES; -- 删除某个数据库 DROP DATABASE test_db; -- 如果某个数据库存在则删除这个数据库 DROP DATABASE IF EXISTS test_db2;
## MySQL 的数据类型
* 数字类型 * 字符串类型 * 日期和时间类型
## 数字类型
|数据类型|说明| | --- | --- | |TINTINT|0255 或 -1281271字节最小的整数| |SMALLINT|065535 或 -32768327672字节小型整数| |MEDIUMINT|016777215 或 -838860883886073字节中型整数| |INT|04294967295 或 -214768364821476836474字节标准整数| |BIGINT|8字节大整数| |FLOAT|单精度浮点值| |DOUBLE|双精度浮点值| |BOOLEAN|布尔值|
## 字符串类型
|数据类型|说明| | --- | --- | |CHAR|1255 个字符固定长度字符串| |VARCHAR|长度可变最多不超过 255 个字符| |TEXT|最大长度为 64K 的变长文本| |TINYTEXT|与 TEXT 相同但最大长度为 255 字节| |MEDIUMTEXT|与 TEXT 相同但最大长度为 16K| |LONGTEXT|与 TEXT 相同但最大长度为 4GB| ## 日期和时间类型
|数据类型|说明| | --- | --- | |DATE|日期格式 YYYY-MM-DD| |TIME|时间格式 HH:MM:SS| |DATETIME|日期和时间格式 YYYY-MM-DD HH:MM:SS| |TIMESTAMP|时间标签功能和 DATETIME 相同但范围较小| |YEAR|年份可指定两位数字和四位数字的格式|
## 常用数据类型
* INT整型 * DOUBLE浮点型 * VARCHAR字符串型 * DATE日期类型
## 创建表语法 -- 创建表 CREATE TABLE 数据表名 ( 列名1 属性, 列名2 属性… ); ## 列属性
* NOT NULL | NULL该列是否允许是空值 * DEFAULT表示默认值 * AUTO_INCREMENT表示是否是自动编号 * PRIMARY KEY表示是否为主键 列名 数据类型 [NOT NULL | NULL] [DEFAULT 默认值] [AUTO_INCREMENT] [PRIMARY KEY ] [注释] ## 创建学员表
* 创建在 db1 数据库中 * 表名为 student * 包含两个字段 * 学员 id * 学员姓名 -- 切换到数据库 db1 USE db1;
-- 创建学员表 CREATE TABLE student( id INT, name VARCHAR(20) );
## 复制表语法
* 数据表名表示新创建的数据表的名 * LIKE 源数据表名必选项指定依照哪个数据表来创建新表 -- 复制表 CREATE TABLE数据表名 {LIKE 源数据表名 | (LIKE 源数据表名)} ## 复制结构相同的表 -- 创建一个表结构与 student 相同的 s2 表 CREATE TABLE s2 LIKE student;
## 查看表名语法 -- 查看当前数据库中所有的表名 SHOW TABLES; ## 查看表结构语法 -- 查看表结构 DESCRIBE 数据表名; DESCRIBE 数据表名 列名;
-- 查看表结构简写 DESC 数据表名; DESC 数据表名 列名; ## 实例 -- 选择数据库 USE db1; -- 查看数据库中的表 SHOW TABLES; -- 查看学员表的表结构 DESC student; -- 查看学员表中 name 列的信息 DESC student name; ## 表数据修改语法
* SET 子句必选项用于指定表中要修改的字段名及其字段值 * WHERE 子句可选项用于限定表中要修改的行 -- 修改表中数据 UPDATE 数据表名 SET 列名1值1 [, 列名2值2...] [WHERE 条件表达式] ## 实例 -- 选择 db1 为当前数据库 USE db1;
-- 创建 student 表 CREATE TABLE student( id INT, name VARCHAR(20), sex CHAR(1), age TINYINT, city VARCHAR(50) );
-- 插入 5 条数据 INSERT INTO student VALUES(1,小李,男, 18, 北京), (2,小白,女, 20, 成都), (3,小王,男, 23, 上海), (4,小赵,女, 21, 深圳), (5,小周,男, 25, 杭州);
-- 不带条件修改将所有的性别改为女 UPDATE student SET sex 女;
-- 带条件的修改将 id 为 3 的学生性别改为男 UPDATE student SET sex 男 WHERE id 3;
-- 一次修改多个列 将 id 为 2 的学 ## 通过 DELETE 语句删除数据
* 数据表名指定要删除的数据表的表名 * WHERE 子句限定表中要删除的行 -- 删除表中指定行的数据 DELETE FROM 数据表名 WHERE 条件表达式 ## 通过 TRUNCATE TABLE 语句删除数据 -- 删除表中全部数据 TRUNCATE TABLE 数据表名 ## 实例 -- 选择 db1 为当前数据库 USE db1;
# 数据准备 -- 创建 student 表 CREATE TABLE student( id INT, name VARCHAR(20), sex CHAR(1), age TINYINT, city VARCHAR(50) );
-- 插入 5 条数据 INSERT INTO student VALUES(1,小李,男, 18, 北京),(2,小白,女, 20, 成都),(3,小王,男, 23, 上海),(4,小赵,女, 21, 深圳),(5,小周,男, 25, 杭州);
-- 删除 id 为 1 的数据 DELETE FROM student WHERE id 1;
-- 删除 student 表中所有数据 DELETE FROM student;
-- 删除 student 表中所有数据 TRUNCATE TABLE student;
## 数据准备
* 测试数据库 * https://github.com/datacharmer/test_db * 网盘下载 * [网盘下载地址](https://pan.baidu.com/s/1VtEXIogo_GS7iGh3f0nklw) * 提取码gxow cd 数据所在目录 mysql -h 127.0.0.1 -uroot -p employees.sql ## 单表查询
* 单表查询从一张表中查询所需要的数据所有查询操作都比较简单 * * 代表所有的列 * 语法SELECT * FROM 表名; -- 查询部门表中的信息 SELECT * FROM departments; ## 字段查询
* 查询多个字段列可以使用 , 对字段进行分隔 * 语法SELECT 列名 FROM 表名; -- 查询部门的名称 SELECT dept_name FROM departments; ## 起别名
* 为表起别名 * SELECT 列名 FROM 表名 表别名; * 为字段起别名 * SELECT 列名 AS 别名 FROM 表名; -- 查询员工信息并将列名改为中文 SELECT emp_no AS 编号, first_name AS 名, last_name AS 姓, gender AS 性别, hire_date AS 入职时间 FROM employees emp; ## 去重
* DISTINCT 关键字去掉重复信息 * 语法SELECT DISTINCT 列名 FROM 表名; -- 去掉重复职级信息 SELECT DISTINCT title FROM titles; ## 运算查询
* 查询结果参与运算 * SELECT (列名 运算表达式) FROM 表名; -- 所有员工的工资 1000 元进行显示 SELECT emp_no , salary 1000 FROM salaries; # DQL 表查询操作 - 条件查询 ## 条件查询语法 -- 条件查询 SELECT 列名 FROM 表名 WHERE 条件表达式 ## 比较运算符
|运算符|说明| | --- | --- | | !|大于、小于、小于等于、大于等于、等于、不等于| |BETWEEN...AND...|范围限定| |IN|子集限定| |LIKE %or%|模糊查询| |IS NULL|为空| ## 比较大小
* 语法WHERE 列名 [ !] 值 -- 查询出生日期晚于 1965-01-01 的员工编号、姓名和生日 SELECT emp_no, first_name, last_name, birth_date FROM employees WHERE birth_date 1965-01-01; ## 使用 BETWEEN 进行模糊查询
* 语法WHERE 列名 [NOT] BETWEEN 起始表达式 AND 结束表达式 * 起始表达式 和 结束表达式 的顺序不能颠倒 -- 查询年薪介于 70000 到 70003 之间的员工编号和年薪 SELECT emp_no, salary FROM salaries WHERE salary BETWEEN 70000 AND 70003; ## 使用 IN 进行模糊查询
* 语法WHERE 列名 IN (常量列表) * (常量列表) 中各常量值用逗号隔开 -- 查询入职日期为 1995-01-27 和 1995-03-20 日的员工信息 SELECT * FROM employees WHERE hire_date IN (1995-01-27, 1995-03-20);
## 判断是否为空
* 语法WHERE 列名 IS [NOT] NULL -- 选择 hog_demo 为当前数据库 USE hog_demo;
-- 更新 student 表中 id 为 2 的 age 值为 NULL UPDATE student SET age NULL WHERE id 2;
-- 查询学生表中年龄为 NULL 的学生信息 SELECT * FROM student WHERE age IS NULL; ## 逻辑运算符
|运算符|说明| | --- | --- | |AND |多个条件同时成立| |OR |多个条件任一成立| |NOT|不成立| -- 查询名字为 Lillian 并且姓氏为 Haddadi 的员工信息 SELECT * FROM employees WHERE first_name Lillian AND last_name Haddadi;
-- 查询名字为 Lillian 或者姓氏为 Terkki 的员工信息 SELECT * FROM employees WHERE first_name Lillian OR last_name Terkki;
-- 查询名字为 Lillian 并且性别不是女的员工信息 SELECT * FROM employees WHERE first_name Lillian and not genderF; ## 通配符
|运算符|说明| | --- | --- | |%|匹配任意多个字符| |-|匹配一个字符| -- 查询名字中包含 fai 的员工的信息 SELECT * FROM employees WHERE first_name LIKE %fai%;
-- 查询名字中 fa 开头的名字长度为 3 位的员工信息 SELECT * FROM employees WHERE first_name LIKE fa_; # DQL 表查询操作 - 排序 ## 排序语法
* ASC 表示升序排序默认 * DESC 表示降序排序 -- 对查询结果进行排序 SELECT 列名 FROM 表名 [WHERE 条件表达式] ORDER BY 列名1 [ASC / DESC], 列名2 [ASC / DESC] ## 单列排序
* 只按照某一个列进行排序, 就是单列排序 -- 使用 salary 字段,对 salaries 表数据进行升序排序 SELECT * FROM salaries ORDER BY salary;
-- 使用 salary 字段,对 salaries 表数据进行降序排序 SELECT * FROM salaries ORDER BY salary DESC;
-- 查询员工的编号和入职日期按照员工入职日期从晚到早排序 SELECT emp_no, hire_date FROM employees ORDER BY hire_date DESC; ## 组合排序
* 同时对多个字段进行排序 * 如果第一个字段相同就按照第二个字段进行排序 -- 在入职时间排序的基础上再使用 emp_no 进行排序 -- 组合排序 SELECT emp_no, hire_date FROM employees ORDER BY hire_date DESC, emp_no DESC; # DQL 表查询操作 - 聚合函数 ## 聚合函数
* COUNT()统计指定列不为 NULL 的记录行数 * MAX()计算指定列的最大值 * MIN()计算指定列的最小值 * SUM()计算指定列的数值和 * AVG()计算指定列的平均值 ## 聚合查询
* 语法SELECT 聚合函数(列名) FROM 表名; -- 查询职级名称为 Senior Engineer 的员工数量 SELECT COUNT(*) FROM titles WHERE title Senior Engineer;
-- 查询员工编号为 10002 的员工的最高年薪 SELECT MAX(salary) FROM salaries WHERE emp_no 10002;
-- 查询员工编号为 10002 的员工的最低年薪 SELECT MIN(salary) FROM salaries WHERE emp_no 10002;
-- 查询员工编号为 10002 的员工的薪水总和 SELECT SUM(salary) FROM salaries WHERE emp_no 10002;
-- 查询员工编号为 10002 的员工的平均年薪 SELECT AVG(salary) FROM salaries WHERE emp_no 10002; # DQL 表查询操作 - 分组
## 分组查询语法
* 分组列按哪些列进行分组 * HAVING对分组结果再次过滤 -- 分组查询 SELECT 分组列/聚合函数 FROM 表名 GROUP BY 分组列 [HAVING 条件]; ## 实例 -- 查询每个员工的薪资和 SELECT emp_no, SUM(salary) FROM salaries GROUP BY emp_no;
-- 查询员工编号小于 10010 的薪资和小于 400000 的员工的薪资和 SELECT emp_no, SUM(salary) FROM salaries WHERE emp_no 10010 GROUP BY emp_no HAVING SUM(salary) 400000; ## 子句区别
* WHERE 子句从数据源中去掉不符合其搜索条件的数据 * GROUP BY 子句搜集数据行到各个组中统计函数为各个组计算统计值 * HAVING 子句去掉不符合其组搜索条件的各行数据行 # DQL 表查询操作 - LIMIT 关键字 ## LIMIT 关键字
* 限制查询结果的数量 * 开始的行数从 0 开始记数, 如果省略则默认为 0 * 查询记录的条数返回的行数 -- 限制查询结果行数 SELECT 列名1, 列名2... FROM 表名 LIMIT [开始的行数], 查询记录的条数
-- 使用 OFFSET 关键字指定开始的行数 SELECT 列名1, 列名2... FROM 表名 LIMIT 查询记录的条数 OFFSET 开始的行数 ## LIMIT 关键字
* 限制查询结果的数量 * 开始的行数从 0 开始记数, 如果省略则默认为 0 * 查询记录的条数返回的行数 -- 限制查询结果行数 SELECT 列名1, 列名2... FROM 表名 LIMIT [开始的行数], 查询记录的条数
-- 使用 OFFSET 关键字指定开始的行数 SELECT 列名1, 列名2... FROM 表名 LIMIT 查询记录的条数 OFFSET 开始的行数 ## 实例 -- 展示前 10 条员工信息 SELECT * FROM employees LIMIT 10; SELECT * FROM employees LIMIT 0, 10; SELECT * FROM employees LIMIT 10 OFFSET 0;
-- 显示年薪从高到低排序第 15 位到第 20 位员工的编号和年薪 SELECT emp_no, salary FROM salaries ORDER BY salary DESC LIMIT 14, 6;
SELECT emp_no, salary FROM salaries ORDER BY salary DESC LIMIT 6 OFFSET 14; ## 单表查询总结 -- 基础查询语法 SELECT DISTINCT 列名 FROM 表名 WHERE 查询条件表达式 GROUP BY 分组的列名 HAVING 分组后的查询条件表达式 ORDER BY 排序的列名 [ASC / DESC] LIMIT [开始的行数], 查询记录的条数
## SQL 语句执行顺序 startuml autonumber
原始数据库 - 虚拟表1: FROM 子句 虚拟表1 - 虚拟表2: WHERE 子句 虚拟表2 - 虚拟表3: GROUP BY 子句 虚拟表3 - 虚拟表4: HAVING 子句 虚拟表4 - 虚拟表5: SELECT 虚拟表5 - 虚拟表6: DISTINCT 虚拟表6 - 虚拟表7: ORDER BY 子句 虚拟表7 - 最终结果: LIMIT
enduml
# SQL 约束 - 主键约束 ## SQL 约束
* 对表中的数据进行进一步的限制 * 保证数据的正确性、有效性、完整性 * 违反约束的不正确数据无法插入到表中 * 常见的约束 * 主键PRIMARY KEY * 非空NOT NULL * 唯一UNIQUE * 默认DEFAULT * 外键FOREIGN 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_INCREMENT100;
删除主键约束 -- 删除表中的主键 ALTER TABLE 表名 DROP PRIMARY KEY;
-- 使用 DDL 语句删除表中的主键 ALTER TABLE emp2 DROP PRIMARY KEY; -- 查看表结构 DESC emp2; 选择主键原则 针对业务设计主键往建议每张表都设计一个主键 主键可以没有业务意义只需要保证不重复 SQL 约束 - 非空约束
目录 非空约束 添加非空约束
非空约束 非空约束特点: 某一列不予许为空 语法列名 字段类型 NOT NULL 添加非空约束 -- 添加非空约束 CREATE TABLE emp5( eid INT PRIMARY KEY AUTO_INCREMENT, -- ename 字段不能为空 ename VARCHAR(20) NOT NULL, sex CHAR(1) );
SQL 约束 - 唯一约束 唯一约束 唯一约束: 表中的某一列的值不能重复 对 NULL 不做唯一的判断 语法列名 字段类型 UNIQUE
添加唯一约束 -- 创建带有唯一约束的表 CREATE TABLE emp6( eid INT PRIMARY KEY AUTO_INCREMENT, -- 为 ename 字段添加唯一约束 ename VARCHAR(20) UNIQUE, sex CHAR(1) );
主键约束与唯一约束的区别 主键约束唯一且不能够为空 唯一约束唯一但是可以为空 一个表中只能有一个主键但是可以有多个唯一约束 SQL 约束 - 默认值 默认值 字段指定默认值 默认值 默认值约束用来 指定某列的默认值 语法列名 字段类型 DEFAULT 默认值 字段指定默认值 -- 创建带有默认值的表 CREATE TABLE emp7( eid INT PRIMARY KEY AUTO_INCREMENT, ename VARCHAR(20), -- 为 sex 字段添加默认值 sex CHAR(1) DEFAULT 女 ); 多表查询-内连接查询 内连接 内连接INNER JOIN使用比较运算符进行表间某些列数据的比较操作并列出这些表中与连接条件相匹配的数据行组合成新的记录。匹配上显示匹配不上不显示。 例子 比如使用外键主键这个条件过滤掉无效数据 按语法结构分为 隐式内连接和显式内连接
内连接 内连接INNER JOIN使用比较运算符进行表间某些列数据的比较操作并列出这些表中与连接条件相匹配的数据行组合成新的记录。匹配上显示匹配不上不显示。 例子 比如使用外键主键这个条件过滤掉无效数据 按语法结构分为 隐式内连接和显式内连接
隐式内连接 在笛卡尔积的的基础上使用where条件过滤无用的数据这种连接方式是隐式内连接.
语法select [字段名称] from 表1表2 where [条件]
例1 筛选出运营部的员工的id姓名以及所在城市 SELECT emp_id,ename,dept_location FROM emp_part,dept WHERE dept_idid and dept_name运营部; 显式内连接 显式内连接 使用 select [字段名称] from [表1]inner join [表2] on [条件] 这种方式 列子 用显式内连接查询运营部的员工的id姓名以及所在城市 SELECT emp_id,dept_location,ename FROM emp_part INNER JOIN dept ON dept_idid AND dept_name运营部
多表查询-外连接查询 外连接 外连接查询查询多个表中相关联的行有时候需要包含没有关联的行中数据即返回查询结果集合中不仅包含符合连接条件的行还包括左表左连接、右表右连接中的所有数据行。 左外连接 , 使用 LEFT OUTER JOIN , OUTER 可以省略 右外连接 , 使用 RIGHT OUTER JOIN , OUTER 可以省略
左连接 左连接以左表为基准匹配右表的数据右表中没有的项显示为空
语法SELECT [字段] FROM [左表] LEFT JOIN [右表] ON [条件]
例子公司新成立人力资源部还未招聘员工请使用左连接查询方式查询出公司所有部门员工的员工号姓名性别以及他们所在的部门名称和城市
#向部门表中插入人力资源部 INSERT INTO dept VALUES(4,人力资源部,甄嬛,北京); #查询出需要的数据 SELECT emp_id,ename,gender,dept_name,dept_location FROM dept LEFT JOIN emp_part ON dept.idemp_part.dept_id
右连接 右连接以右表为基准匹配左表的数据左表中没有的项显示为空 语法SELECT [字段] FROM [左表] RIGHT JOIN [右表] ON [条件] 使用右连接的方式查询出所有员工信息以及他们所在的部门名称和城市
总结 内连接: inner join 左连接: left join 右连接: right join 内连接和左连接使用居多  子查询简介 定义子查询指一个查询语句嵌套在另一个查询语句内部在SELECT子句中先计算子查询子查询的结果作为外层另一个查询的过滤条件查询可以基于一个表或者多个表。 这个特性从MySQL 4.1开始引入。 子查询作为过滤条件时需要用 包裹 子查询的常见分类 From型子查询将子查询的结果作为父查询的表来使用 in/not in 型子查询子查询的结果是单列多行作为where的过滤条件 where型子查询查询结果作为过滤条件出现在比较运算符的一端 带From关键词的子查询 子查询是一张多行多列的表将子查询作为父查询的表来嵌套查询 子查询语句必须用包裹且需要有别名 计算出各部门性别为男性的员工人数
select dept_name,count(emp_id) from (select dept_name, emp_id,ename,gender from dept inner join emp_part where iddept_id and gender男)b group by dept_name; 带IN关键词的子查询 将子查询作为where语句后的过滤条件常用于子查询结果是单列多行的情况 子查询语句必须用包裹 in/not in 查询出北京地区所有的员工信息 SELECT * FROM emp_part WHERE dept_id IN (SELECT id FROM dept WHERE dept_location 北京) 带比较运算符的子查询 将子查询的结果作为过滤条件放在比较运算符的一端 常用于子查询结果为单个结果的情况 子查询语句必须用包裹
#查询出薪资大于公司平均薪资的员工id,姓名及薪资 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 iddept_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 iddept_id and avg_salary (select avg(avg_salary) from dept_avg);
创建数据库并导入相关数据 部门表字段 dept_id 部门id city 所在城市 manager 部门经理 订单表 dept_id 部门id order_id 订单号 volume 客单价 sales_date 销售日期
计算销售业绩并排序 # 计算出各部门最近两周的总业绩并按业绩由高到低排名 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
查询出最近两周销售额超平均销售额的部门 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)
知识扩展-视图 定义视图是一种虚拟的表它并不会在你的存储空间复制一份数据而是对原有数据的一种引用。可以将视图理解为一种存储起来的sql语句 视图可以简化多表查询 视图也可以用于控制用户权限 使用关键词view来创建视图 语法CREATE VIEW [视图名称] AS SELECT…..
使用视图简化练习 CREATE VIEW 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);
#查询出最近两周的冠军销售部门
SELECT * FROM temp_dept WHERE total_volume(SELECT max(total_volume) FROM temp_dept);
