文章目录：
一：前言
1.定义
1..1 SQL定义
SQL是什么 a.SQL 指结构化查询语言，全称是 Structured Query Language b.用于数据库操作 和 数据管理:存取数据以及查询、更新和管理关系数据库系统 c.SQL是一种用于管理和操作关系数据库的标准语言 包括数据查询、数据插入、数据更新、数据删除、数据库结构创建和修改等功能 特点 a.SQL本身不实现具体的数据库系统，而是作为一种语言被嵌入到各种数据库系统中 b.采用客户端-服务器架构时，SQL语句在服务器端执行 功能与用途 a.主要用于数据查询（SELECT）、数据定义（DDL如CREATE、ALTER、DROP）、 数据操纵（DML如INSERT、UPDATE、DELETE）和数据控制（DCL如GRANT、REVOKE） b.SQL的功能是标准化的，不同数据库系统间的SQL语法虽然略有差异，但基本保持一致 SQL分类 DML数据操作语言，操作数据库中存储的数据(insert、update、delete) DDL数据定义语言，创建、删除、修改数据库、数据表(create、drop、alter) DOL数据查询语言，查询数据库(select) DCL数据控制语言，用来控制数据库组件的存取(事务commit、回滚rolback)
1.2 MySQL定义
什么是MySQL MySQL是一个关系型数据库管理系统（RDBMS），软件产品 MySQL是一个服务，在服务中可以创建很多数据库，在数据库中创建数据表，数据表存储数据 特点 a.MySQL是一个完整的数据库系统，实现了SQL语言的标准和扩展 b.MySQL采用客户端-服务器架构，客户端通过网络连接到MySQL服务器，执行SQL语句并接收结果 c.MySQL的性能和优化依赖于服务器的配置、存储引擎的选择以及SQL语句的编写 d.MySQL提供了丰富的安全特性，如用户权限管理、数据加密等，以保护数据库的安全 功能与用途 a.提供了对数据库的多用户访问、存储、检索、修改和管理等功能 b.支持多种存储引擎，如InnoDB、MyISAM等，以适应不同的应用场景 c.提供了丰富的管理工具和优化选项，以提高数据库的性能和安全性 数据库存储引擎：MySQL支持多种存储引擎，默认是InnoDB 存储引擎就是如何存储数据、如何建立索引、如何更新、查询数据等技术的具体实现 也可以称为表的类型 show engines;
1.3 sql和mysql语法的异同
相同点 1.都是关系型数据库相关： SQL是一种用于管理关系型数据库的编程语言 MySQL是一个关系型数据库管理系统（RDBMS） 它们都基于关系型数据库的概念，即数据被组织成表格，表格之间通过关系（如外键）相互连接 2.使用SQL语言： MySQL使用SQL作为其主要的查询和管理语言 这意味着，在MySQL中执行的大多数数据库操作（如查询、更新、插入和删除数据）都是通过SQL语句来完成的 3.数据存储和管理： 两者都支持数据的存储、检索、更新和删除等基本操作，以及更复杂的查询和事务处理 不同点 1.本质属性： SQL：是一种编程语言，用于与关系型数据库进行交互。它不是特定的数据库系统，而是可以应用于任何支持SQL的数据库系统 MySQL：是一个具体的数据库管理系统，它实现了SQL语言，并提供了额外的功能和工具来管理数据库 2.功能范围： SQL本身只定义了与数据库交互的语法和规则，不直接提供数据库管理系统的所有功能 MySQL则是一个完整的数据库管理系统，除了支持SQL语言外，还提供了数据库安装、配置、优化、备份、恢复等一系列管理功能 3.数据类型和特性： 虽然两者都支持多种数据类型，但MySQL可能提供了一些特定于系统的数据类型和特性，如JSON数据类型、空间数据类型等 4.性能和优化： SQL的性能取决于所使用的数据库系统的实现和硬件配置 MySQL的性能则可以通过调整服务器的配置、优化查询语句、使用索引等方式来提高 5.社区和支持： MySQL作为一个开源项目，拥有庞大的社区和丰富的文档资源，用户可以获得来自社区的支持和帮助 SQL作为一种广泛使用的语言，也有大量的学习资源和社区支持，但更侧重于语言本身的学习和使用 6.应用场景： SQL是通用的，可以用于任何支持SQL的数据库系统 MySQL则因其开源、高性能、可靠性等特点，在Web应用程序、动态网站、嵌入式系统等领域有广泛的应用
区别主要体现在以下几个方面：
1.数据类型：MySQL作为一种具体的数据库管理系统，在SQL是一种编程语言的标准的数据类型基础上进行了扩展 例如，MySQL支持DATE、DATETIME和TIMESTAMP等数据类型 这些在标准SQL中可能以不同的方式或不完全相同的形式存在 2.函数：MySQL提供了一系列自己的函数，这些函数在标准SQL中可能不存在或具有不同的名称 例如，MySQL中的DATE_FORMAT()和NOW()函数在标准SQL中可能没有直接对应的函数 3.关键字保留：MySQL保留了一些特定的关键字，这些关键字在标准SQL中可能不是保留的 例如，MATCH、AGAINST和FULLTEXT等关键字在MySQL中有特殊的意义，但在标准SQL中可能只是普通的标识符 4.语法糖：MySQL支持一些语法上的“糖衣”，这些特性在标准SQL中可能不被支持 例如，MySQL允许使用单引号或双引号括起标识符（如表名或字段名），而标准SQL可能对此有更严格的规定 5.存储过程和触发器：MySQL支持存储过程和触发器的使用，这些特性在标准SQL中可能不是所有数据库系统都支持的 6.外键约束：MySQL在外键约束的语法上与标准SQL有所不同 在MySQL中，外键约束通常使用FOREIGN KEY子句定义 而在标准SQL中，可能使用REFERENCES子句或其他方式定义 7.其他特定语法：MySQL还提供了一些特定的语法特性 如枚举类型（ENUM）和集合类型（SET），这些在标准SQL中可能不是所有数据库系统都支持的
2.语法规范
1.SQL 对大小写不敏感：SELECT 与 select 是相同的 关键字大写；表名 列名和其他名称小写 2.每条SQL语句的末端使用分号“；” 也可以不加，但是建议加上分号 3.多个列 和 多个值 之间使用逗号“，”隔开 4.注释 -- 这是一个单行注释 #这是一个单行注释 /*这是多行注释*/ %23 5.空格 %20 + 6.占位符："？" 7.字符型相关的必须加引号：单引号 双引号都可以
3.表的概念
记录(横向的) 行：记录/元组/具体的某个事物 字段(纵向的) 列标题：字段/事物属性 值：交叉的小格子/每一个方格数据 域：值的限定范围 主键外键 主键（主关键字）：可以定位到某一条具体的数据、非空、不能重复 方便和其他的表进行关联：因为不会重复、可以定位到一条具体的数据 外键（外部关键字）：在另外一张表中与”主键“相互产生关联 表关系 单表：单独一张表就可以将信息保持 一对多：需要两张表来存储信息，且两张表存在一对多 或 多对一关系 多对多：需要三张表来存储信息，两张单表+关系表，创造出两个单表之间多对多的关系
4.其他概念
4.1 数据类型
注意:文本用引号包裹、日期用#包裹
| 数值类型 | |||
| 整型 | tinyin | 1byte | 非常小的整数 |
| smallintt | 2byte | 小的整数 | |
| mediumint | 3byte | 中等大小的整数 | |
| integer、int | 4byte | 标准的整数 | |
| bigint | 8byte | 大整数 | |
| 浮点类型 | float(总长度不含小数点,小数点后几位) | 4byte | 单精度 |
| double(总长度不含小数点,小数点后几位) | 8byte | 双精度 | |
| 定点类型 | numberic | -2^38-1 ~ 2^38-1byte | 数字 |
| decimal | -2^38-1 ~ 2^38-1byte | 十进制 | |
| DATE日期/时间型 | |||
| year | 1byte | 年份值 | |
| time | 3byte | 时间值 | |
| date | 3byte | 日期值 | |
| timestamp时间戳 | 4byte | 1970-01-01 00：00：01到现在的毫秒数 | |
| datetime | 8byte | 日期+时间值 | |
| 字符串类型 | |||
| char(M) | M个字符 | 固定长度的字符串 | |
| varchar(M) | M个字符 | 可变长度的字符串 | |
| tinytext | 2^8-1 byte | 非常小的字符串 | |
| text | 2^16-1 byte | 小型的字符串 | |
| mediumtext | 2^24-1 byte | 中等大小的字符串 | |
| longtext | 2^32-1 byte | 大型的字符串 | |
| enum | 1到255个元素 占1byte | 枚举 | |
| 256到65535个元素 占2byte | |||
| 集合 | |||
| set | 组织元素的方式存在 这些元素可以是数字、字母、符号，甚至可以是其他集合 | ||
| 对于1到8个元素， 对于9到16个元素， 对于17到24个元素， 对于25到32个元素， 对于33到64个元素， | |||
| 二进制类型 | |||
| bit(M) | M位二进制数据 | 小的二进制数据 | |
| binary(M) | M byte | 普通的二进制数据 | |
| varblob(M) | 0~M 的变长二进制 | 普通的二进制数据 | |
| tinyblob | 255byte | 大的二进制数据 | |
| blob | 2^16-1 byte | 大的二进制数据 | |
| mediumblob | 2^24-1 byte | 大的二进制数据 | |
| longblob | 2^32-1 byte | 大的二进制数据 | |
4.2 正则表达式regexp
* 任意多个字符 （王* 姓王、*王* 包含王） ？ 任意单个字符 （王？ 姓王，名字两个字） [] 括号内任意单个字符 （[王李]？ 姓王或李，名字两个字） ！ 不在括号内的字符 （![王李]？ 不姓王或李，名字两个字） - 范围内的任意一个字符 （[a-j]bd abd,cbd,jbd） # 单个数字 （5#1 501,502,503） ^ 开头 $ 结尾 a|b 或
二：安装配置
| 软件名 | 下载 | |
| 提供数据存储和服务 | ||
| MySQL Server | 点我下载 | |
| 可视化的MySQL数据库管理工具 | ||
| MysQL Workbench | 点我下载 | |
| DBeaver | 点我下载 | |
| Navicat | ||
| MariaDB | 点我下载 | |
| DataGrip | 点我下载 | |
| 执行数据库管理任务：备份数据库、管理集群 | ||
| MySQL Shell | 点我下载 | |
| 交互式JS Python SQL的终端 mysqlsh启动 \connect root@localhost连接数据库 | ||
方法一：官方
Windows环境
Windows环境：只需要安装MySQL Server（包含Mysql-Workbench）
运行Mysql-installer-community安装包安装MySQL 1.遇到"Execute"就不要点其他的 2.身份认证选择“Use Legacy Authentication Method(Retain MySQL 5.x Compatibility)” 汉化MySQL 第一步：找到Mysql Workbench安装目录 1.默认里面：C:\Program Files\MySQL 2.找到MySQL Workbench\data 第二步：把里面“main_menu.xml”文件进行替换 卸载MySQL 第一步：win+R、输入services.msc、点击确定、找到MySQL程序、右键选择停止运行 第二步：打开控制面板、点击卸载程序、点击卸载MySQL相关组件 启动和停止MySQL MySQL安装完成之后开机自启默认为启动状态 第一种：win+R 、输入services.msc找到 MySQL进行停止或者启动 第二种：需要以管理员的身份执行 启动：net start mysql80 停止：net stop mysql80 连接MySQL 第一种 1.打开Mysql-Workbench 2.点击小鲸鱼图标 3.再点击“Local instance MySQL80”输入密码，点击确认和OK 第二种 1.win+r 2.打开MySQL Workbench所在目录：cd C:\Program Files\MySQL\MySQL Workbench 8.0 3.执行：mysql -u root -p 4.输入自己的密码 第三种：配置环境变量 1.此电脑、属性、高级系统设置、环境变量 2.系统变量下双击path、点击新建、输入MySQL Workbench所在目录C:\Program Files\MySQL\MySQL Workbench 8.0、并依次点击确认保存退出 3.win+r 4.执行：mysql -u root -p 5.输入自己的密码 修改MySQL密码 第一种：需要先登陆，需知原来密码 1.win+r 2.执行：mysql -u root -p 3.输入自己的密码 4.set password for 用户名@localhost = password('新密码'); 第二种：不需先登录，需知原来密码 1.win+r 2.mysqladmin -u用户名 -p旧密码 password 新密码 第三种：忘记了原来密码 1.关闭正在运行的MySQL服务、win+r、services.msc、找到MySQL关闭服务 2.win+r 3.打开MySQL Workbench所在目录：cd C:\Program Files\MySQL\MySQL Workbench 8.0 4.输入：mysqld --skip-grant-tables 5.重新打开一个新终端（第二三步） 6.输入：mysql、use mysql update user set password=password("修改的密码") where user="root"; 7.刷新权限：flush privileges;
Linux环境
Ubuntu的apt：sudo apt install mysql-server 查看服务状态：systemctl status mysql 手动启动：systemctl start mysql 默认的用户名密码：sudo cat /etc/mysql/debian.cnf nysql -u user名 -p password密码 查看root权限：select user,host from user where user='root'; 修改root权限：update user set host='%' where user='root'; 配置文件中监听地址（默认本机）：sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf 修改监听所有地址 bind-address=0.0.0.0 mysqlx-bind-address=0.0.0.0 重启mysql服务：systemctl restart mysql CentOS的yum Fedora的dnf
MAC环境
MAC环境：需要安装MySQL Server、Mysql-Workbench
第一步:先运行Mysql安装包，将MySQL Server安装到Mac系统 第二步:再运行Mysql-Workbench安装包到Mac系统
方法二：操作系统自带
安装mysql：brew install mysql 启动服务：mysql.server start 设置开机自动启动：brew services start mysql 连接mysql：mysql -u -root -p
方法三：docker安装
docker pull mysql
三：[语法执行顺序]
SQL执行顺序:select...from...where...group by...order by...limit... ——>1.获取表，没有表啥也做不了 from——>on——>join ——>2.获取结果 ——>where——>group by——>having——>select ——>3.结果处理 ——>distinct——>order by——>limit 4.关键字 distinct：去重 as取别名：可以省略，在from之前搭配使用 like模糊查询：在where之后搭配使用 comment：注释 not和and和or结合运算符：在where之后搭配使用 between...and...在某个范围内：在where之后搭配使用 all所有的 any任意的 in指定多个值：在where之后搭配使用 on:通常用于指定连接（JOIN）操作的条件 参考结构： select 查询目标 from 表名称 join 加入 on 连接条件 where 筛选记录的条件 group by 分组（每字开头）-分组字段，把选定的记录分成特定的组 having 分组条件（和上面搭配使用）-筛选分组的条件，说明每个组需要满足的条件；替换where select 查询 distinct 去重 order by 排序字段名 limit m,n 限制查询结果集输出的行数,m从那开始,n获取几条数据 语法 select [distinct|top n] * | 字段1 [as] 别名1,字段2 [as] 别名2.字段n [as] 别名n from 表名1,表名2... [where 条件] [group by 字段] [having 条件] [order by 字段... [asc默升|desc降]] [limit n[,m]]
四：基础语法
数据库中数据的组织结构：数据库(database)、数据表(table)、数据行(row)、字段(field)1.管理操作-数据库dabase
创建数据库：create databases 数据库名称 [default charset set utf8 collate utf8_general_ci]; collate设置数据库的校验字符集：对某个字符串类型的数据进行排序查询的时候，数据排序的方式 utf8_general_ci：不区分大小写 utf8_bin：区分大小写 删除数据库：drop datebase 数据库名称; 使用数据库：use 数据库名称; 退出命令行环境：exit; 查看所有数据库：show databases; 查看当前使用的数据库：select database(); 显示部分数据库：show databases like '匹配模式' 显示数据库创建语句：show create database 数据库名字;导入数据 mysql -u root -p 数据库名称 < 导入文件名.sql 导出数据 mysqldump -u root -p 数据库名 > 导出文件名.sql
2.管理操作-数据表table
2.1 增create（创建表 ）
创建表create create table 表名( 列1 数据类型1 [约束1] [是否为主键/是否可以为空/默认值], 列2 数据类型2 [约束2] [是否为主键/是否可以为空/默认值], … 列n 数据类型n [约束n] [是否为主键/是否可以为空/默认值] ); -------------------------------------------------------------------------------- 创建学生表: CREATE TABLE 学生( 学号 INTEGER Primary Key, 姓名 VARCHAR(4) Not Nul, 性别 VARCHAR(1),出生日期 DATE, 家庭住址 VARCHAR(30),学分 NUMBER );
constraint约束
constraint约束 主键约束PK：primary key 外键约束FK：foreign key 唯一约束UQ：unique 空值约束NN：not null、is null 值自动增长Al：auto_increment check：保证列中的值符合指定的条件 默认值default：规定没有给列赋值时的默认值说明举例
PRIMARY KEY 约束：是一种特殊的唯一约束，表中每行都必须有一个唯一的标识符 主键约束自动具有 NOT NULL 约束 CREATE TABLE Persons ( ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), PRIMARY KEY (ID) ); FOREIGN KEY 约束：用于在两个表之间创建链接。外键是一个表中的字段，是另一个表的主键 CREATE TABLE Orders ( OrderID int PRIMARY KEY NOT NULL AUTO_INCREMENT, NAME VARCHAR(11), PersonID int, FOREIGN KEY (PersonID) REFERENCES Persons(OrderID) ); UNIQUE 约束：确保所有值在表中是唯一的 CREATE TABLE Persons ( ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), UNIQUE (ID) ); NOT NULL 约束：确保列不能有 NULL 值 CREATE TABLE Persons ( ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255) ); AUTO_INCREMENT 约束：用于生成一个唯一的数字，每当向表中插入新行时，该数字会自动增加 这通常用于主键列，以确保每条记录都有一个唯一的标识符 CREATE TABLE Users ( UserID int NOT NULL AUTO_INCREMENT, Username varchar(255) NOT NULL, Email varchar(255), PRIMARY KEY (UserID) ); CHECK 约束：确保列中的值满足特定条件 CREATE TABLE Persons ( ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int, CHECK (Age>=18) ); DEFAULT 约束：为列指定默认值 CREATE TABLE Persons ( ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Country varchar(255) DEFAULT 'China' );
comment注释
给表添加注释 CREATE TABLE employees ( id INT PRIMARY KEY, name VARCHAR(100), salary DECIMAL(10, 2) ) COMMENT='员工信息表'; 给列添加注释 CREATE TABLE employees ( id INT PRIMARY KEY COMMENT '员工ID', name VARCHAR(100) COMMENT '员工姓名', salary DECIMAL(10, 2) COMMENT '员工薪水' ); 修改表或列的注释 ALTER TABLE employees COMMENT = '更新后的员工信息表'; ALTER TABLE employees MODIFY COLUMN name VARCHAR(100) COMMENT '员工的姓名';
2.2 删drop
drop table 表名; -------------------------------------------------------------------------------- DROP TABLE 学生;
2.3 改alter（修改表结构-字段操作）
2.3.1 增add
alter table 表名 add 新字段 数据类型和大小 [约束]; -------------------------------------------------------------------------------- ALTER TABLE 学生 ADD 手机号 VARCHAR(11) Unique;
2.3.2 删drop
alter table 表名 drop 新列名; 删除主/外键：alter table 表名 drop primary/foreign key 主/外键名称; -------------------------------------------------------------------------------- ALTER TABLE 学生 DROP 家庭住址;
2.3.3 改change
重命名字段 alter table 表名 change 旧字段 新字段 数据类型和大小 [约束]; alter table 表名 modify column 字段 数据类型和大小 [约束]; alter table 表名 alter column 字段 数据类型和大小 [约束]; 给字段设置默认值 alter table 表名 alter column 字段 set dafault 默认值; -------------------------------------------------------------------------------- ALTER TABLE 学生 ALTER 姓名 VARCHAR(10):
2.3.4 查desc
desc 表名; describe描述 --------------------------------------------------------------------------------
2.4 查show
查看数据表：show tables：
3.管理操作-数据date
3.1 增insert
insert into 表名(字段1,字段2..字段n) values(值1,值2...值n); -------------------------------------------------------------------------------- INSERT INTO 学生 VALUES(2,"李四"“女",#2019-9-1#,"上海市",99.5); INSERT INTO 学生(学号,姓名,性别,出生日期,家庭住址,学分) VALUES(1,"张三""男,#2020-1-1#,“北京市",98); 省略字段相当于添加全部字段：insert into VALUES(1,"张三""男,#2020-1-1#,“北京市",98);
3.2 删delete
delete from 表名 [where 条件]; delete from 表名 limit 限制的行数量; -------------------------------------------------------------------------------- DELETE FROM 学生 WHERE 姓名="张三";
3.3 改update
update 表名 set 字段1=值1, 字段2 = 值2.... [where 条件]; -------------------------------------------------------------------------------- UPDATE 学生 SET 学分=学分+1 WHERE 姓名="张三";
3.4 查select
语法 select [distinct|top n] * | 字段1 as 别名1,字段2 as 别名2.字段n as 别名n from 表名1,表名2... [where 条件] [group by 字段] [having 条件] [order by 字段... [asc默升|desc降]] [limit n[,m]] exists判断查询是否有结果 select ecists(查询语句);
3.4.1 简单查询
select * from 表名称; select 字段列表 from 表名称; -------------------------------------------------------------------------------- SELECT*FROM 员工: SELECT 姓名,性别,手机号,工资 AS 薪资 FROM 员工; SELECT DISTINCT 性别 FROM 员工;
3.4.2 条件查询where
运算符
| 算术运算符 | + | 加 |
| - | 减 | |
| * | 乘 | |
| / | 除 | |
| 比较运算符 | = | 等于 |
| > | 大于 | |
| < | 小于 | |
| >= | 大于等于 | |
| <= | 小于等于 | |
| <> 或 != | 不等于 | |
| 逻辑运算符 | and && | 与 |
| or || | 或 | |
| not ! | 非 | |
| 字符串运算符 | || | 连接，某些数据库使用+ |
| like | 模糊匹配 | |
| ilike | 不区分大小写的like，某些数据库特有 | |
| 特殊运算符 | is null | 判断是否为空 |
| between...and,,, | 判断值在某个区间之内 | |
| in | 判断某个值是否在某个确定的聚合之内 | |
| like | 模糊查询 通配符——%0个或多个字符、_1个字符 |
关键字
与where相关
SELECT 姓名,性别,手机号,工资,部门 FROM 员工 WHERE 部门="咨询部"; ---------------------------------------------------------------------------------------- not关键字 SELECT * FROM employees WHERE NOT department_id = 4; and关键字 SELECT * FROM employees WHERE salary > 50000 AND department_id = 2; SELECT 姓名,性别,手机号,工资,部门 FROM 员工 WHERE 工资>5000 AND 工资<20000; SELECT 姓名,性别,手机号,工资,部门 FROM 员工 WHERE 工资 Between 5000 AND 20000; or关键字 SELECT * FROM employees WHERE position = 'Manager' OR department_id = 1; SELECT * FROM employees WHERE (salary > 50000 AND department_id = 2) OR (position = 'Manager' AND NOT department_id = 4); ---------------------------------------------------------------------------------------- in关键字（指定多个值） SELECT 姓名,性别,手机号,工资,部门 FROM 员工 WHERE 部门 IN("咨询部""销售部"); SELECT * FROM employees WHERE department_id IN (1, 2, 3); SELECT name, position FROM employees WHERE position IN ('Manager', 'Director', 'VP'); SELECT name, department_id FROM employees WHERE department_id NOT IN (4, 5, 6); on关键字：指定连接条件，它经常与 JOIN 语句一起使用 SELECT employees.name, departments.department_name FROM employees JOIN departments ON employees.department_id = departments.id; ---------------------------------------------------------------------------------------- like关键字（模糊查询）：通配符——%0个或多个字符、_1个字符 SELECT 姓名,性别,手机号,工资,部门 FROM 员工 WHERE 姓名 LIKE"王*"; ---------------------------------------------------------------------------------------- between...and..关键字 SELECT * FROM employees WHERE salary BETWEEN 50000 AND 100000; ---------------------------------------------------------------------------------------- is null关键字、is not null关键字：null和任何东西比较都是假的，包括它本身 SELECT * FROM employees WHERE email IS NULL; SELECT * FROM employees WHERE email IS NOT NULL; ---------------------------------------------------------------------------------------- all关键字 SELECT * FROM employees WHERE salary > ALL (SELECT salary FROM interns); any关键字 SELECT * FROM employees WHERE department_id = ANY (SELECT department_id FROM departments WHERE location = 'New York'); ----------------------------------------------------------------------------------------与where不相关
as关键字：取别名 为列指定别名 SELECT employee_id AS id, first_name AS name, salary * 12 AS annual_salary FROM employees; 为表指定别名 SELECT e.id, e.name, d.department_name FROM employees AS e JOIN departments AS d ON e.department_id = d.id; ---------------------------------------------------------------------------------------- distinct关键字：去重 SELECT DISTINCT department_id FROM employees;
3.4.3 排序查询order by
asc升序：默认升序排序 SELECT TOP 3 姓名,性别,手机号,工资,部门 FROM 员工 WHERE 部门="咨询部" ORDER BY 工资 ASC; desc降序 SELECT 姓名,工资 FROM 员工 ORDER BY 工资 DESC:
3.4.4 分组查询group by having
通常和SQL函数在select之后挨着使用 或 group by分组查询 having中使用
SELECT 部门,COUNT(员工编号) AS 人数 FROM 员工 GROUP BY 部门; SELECT 部门,AVG(工资) AS 平均工资 FROM 员工 GROUP BY 部门 HAVING AVG(工资)>15000;
3.4.5 多表查询
select 列名称 from 表1 as 别名1, 表2 as 别名2， ...表n [where 连接条件进行关联比对筛选] -------------------------------------------------------------------------------- SELECT 客户.姓名,咨询信息.咨询事务,咨询信息.咨询方式 FROM 客户,咨询信息 WHERE 客户.客户编号=咨询信息.客户编号; SELECT 客户.姓名,员工.姓名,咨询信息.咨询事务,咨询信息.咨询方式 FROM 客户,咨询信息,员工 WHERE 客户.客户编号 = 咨询信息.客户编号 AND 咨询信息.员工编号=员工.员工编号; select 姓名,性别,英语 from student where 性别="男" and 英语=(select max(英语) from student where 性别="男")
3.4.6 子查询
SELECT 姓名,工资 FROM 员工 WHERE 工资>(SELECT AVG(工资) FROM 员工); SELECT 姓名,工资 FROM 员工 WHERE 部门=“产品部" AND (工资 > ALL(SELECT 工资 FROM 员工 Where 部门=“销售部")); SELECT 姓名,工资 FROM 员工 WHERE 部门 ="产品部"AND (工资 > ANY(SELECT 工资 FROM 员工 Where 部门=“销售部”));
3.4.7 关联查询
a 内关联inner（交集）
内关联（交集 双向奔赴 可能都没有不能相互关联） ：两个表都有的数据
第一种：标准的SQL JOIN 语法 select 列名称 from 表1 [as 别名1] [inner] join 表2 [as 别名2] on 别名1.连接字段 = 别名2.连接字段; 第二种：隐式连接（逗号分隔的连接） select 列名称 from 表1 [as 别名1] ,表2 [as 别名2] where 别名1.连接字段 = 别名2.连接字段; -------------------------------------------------------------------------------- select * from player inner join equip on player.id=equip.player_id select * from player,equip where player.id=equip.player_id 加 WHERE 子句来进一步过滤结果 SELECT 列名称 FROM 表1 AS 别名1 INNER JOIN 表2 AS 别名2 ON 别名1.连接字段 = 别名2.连接字段 WHERE 别名1.某个字段 = '某个值' OR 别名2.另一个字段 > 100;
b.外关联outer（左外_左边和交集 右外_右边和交集）
外关联（单向奔赴 以什么为主：主表有多少数据，最终显示多少条数据）
第一种：标准的SQL JOIN 语法 select 列名称 from 表1 [as 别名1] (left|right) [outer] join 表2 [as 别名2] on 别名1.连接字段 = 别名2.连接字段; 第二种：隐式连接（逗号分隔的连接） select 列名称 from 表1 [as 别名1] ,表2 [as 别名2] where 别名1.连接字段 = 别名2.连接字段; -------------------------------------------------------------------------------- select * from player left outer join equip on player.id=equip.player_id select * from player,equip where player.id=equip.player_id解释
左连接（LEFT JOIN/LEFT OUTER JOIN）：左表中所有的数据 与 右表匹配的数据；右表没有用NULL填充 左连接用于从“左表”中获取所有行，并尝试与“右表”中的行进行匹配 如果右表中存在与左表连接条件相匹配的行，则这些行会一起出现在结果中 如果右表中没有匹配的行，则结果中右表的部分将包含NULL 这通常用于确保结果中包含左表中的所有记录，即使它们在右表中没有对应的匹配项 右连接（RIGHT JOIN/RIGHT OUTER JOIN）：右表中所有的数据 与 左表匹配的数据；左表没有用NULL填充 右连接与左连接相反，它从“右表”中获取所有行，并尝试与“左表”中的行进行匹配 如果左表中存在与右表连接条件相匹配的行，则这些行会一起出现在结果中 如果左表中没有匹配的行，则结果中左表的部分将包含NULL 然而，由于可以通过交换表的顺序和使用左连接来达到相同的效果，右连接在实际应用中相对较少见 外连接（OUTER JOIN） 外连接是一个更广泛的概念，它包括了左外连接和右外连接 然而，在SQL查询中，你通常会明确指定是LEFT OUTER JOIN还是RIGHT OUTER JOIN，因为单独的OUTER JOIN可能在不同的数据库系统中具有不同的解释或不被直接支持 外连接的主要目的是确保结果中包含至少一个表中的所有行，即使它们在另一个表中没有对应的匹配项。这些未匹配的行在结果中将以NULL填充另一个表的部分
c.全连接union
union并集：合并两个表中的数据，生成新表，默认去重
union all并集：不会去除重复的记录
intersect交集-查询结果：相当于内关联-关联关系
except差集
select 姓名,性别,手机号 from 员工 union select 姓名,性别,手机号 from 客户; --------------------------------------------------------------------------------
五：语法进阶
1.SQL笛卡儿积
定义 SQL中的笛卡尔积（Cartesian product） 是指两个或更多表在没有指定连接条件的情况下进行连接时所产生的结果集 产生原因：当执行没有连接条件 如使用JOIN关键字而没有ON子句 使用逗号分隔表名而没有WHERE子句）的查询时 导致的结果 结果集中的每一行都是第一个表中的一行与第二个表中的每一行组合而成的 如果涉及到更多表，则结果集将是所有表行的组合 如何避免 会通过添加适当的连接条件（如使用ON子句）来避免这种情况，以获取更有意义的结果 有什么用处 笛卡尔积通常不是期望的结果，因为它包含了大量无用的数据组合 然而，有时它们可以用于特定的分析或操作，比如生成测试数据举例
employees（员工）表 1 Alice 2 Bob departments（部门）表 101 Engineering 102 Marketing 执行以下查询，将产生笛卡尔积： SELECT * FROM employees, departments; 结果将是： 1 Alice 101 Engineering 1 Alice 102 Marketing 2 Bob 101 Engineering 2 Bob 102 Marketing
2.语法糖
SQL 语法糖"（Syntactic Sugar）在 SQL 领域中指的是那些不改变程序功能，但可以使代码更加简洁、易读或更易于编写的 SQL 特性或表达方式
这些特性通常是数据库管理系统（DBMS）为了提升开发者的开发效率和代码的可读性而提供的别名（Alias）：使用
AS关键字为表或列指定别名，可以使查询结果更易于理解和处理SELECT column_name AS alias_name FROM table_name;隐式连接（Implicit Join）：通过
WHERE子句而不是明确的，用JOIN语句来连接表SELECT * FROM table1, table2 WHERE table1.id = table2.foreign_id;插入多行（Insert Multiple Rows）：某些数据库允许在单个
INSERT语句中插入多行数据，这减少了需要执行的语句数量，提高了效率INSERT INTO table_name (column1, column2, column3) VALUES (value1, value2, value3), (value4, value5, value6), (value7, value8, value9);使用
WITH（公用表表达式/CTE）：将一个查询的结果集临时命名，以便在主查询中多次引用with CTE AS ( SELECT column_name FROM table_name WHERE condition ) SELECT * FROM CTE;窗口函数（Window Functions）：允许对一组行执行计算，而无需将行组合成单个输出行。这在进行排名、行号分配或计算累计和等操作时特别有用
row_number()：为结果集中的每一行分配一个唯一的序号 SELECT employee_id, department_id, salary, ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS salary_rank FROM employees; rank() dense_rank()：类似于row_number()但它们在遇到相等值时会有不同的行为 RANK() 会为相等值分配相同的排名，并留下间隔 DENSE_RANK() 会为相等值分配相同的排名，但不留下间隔 sum()、avg()、min（）、max()等聚合函数的窗口版本：这些函数可以对窗口内的行进行聚合计算，而不是对整个结果集进行聚合 SELECT sale_date, salesperson_id, sales_amount, SUM(sales_amount) OVER (PARTITION BY salesperson_id ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_sales FROM sales; lead() lag()：这两个函数用于访问结果集中当前行的前一行或后一行的数据 SELECT employee_id, salary, LAG(salary, 1) OVER (ORDER BY salary DESC) AS previous_salary FROM employees; first_value()、last_value()、nth_value()：这些函数返回窗口内第一行、最后一行或第 N 行的值字符串连接操作符
-- Oracle, PostgreSQL SELECT 'Hello, ' || 'World!' AS greeting; -- SQL Server SELECT 'Hello, ' + 'World!' AS greeting; -- 跨数据库兼容 SELECT CONCAT('Hello, ', 'World!') AS greeting;条件表达式（如
CASE语句）： 查询中进行条件逻辑判断，这在处理复杂的逻辑和条件时非常有用SELECT column_name, CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 ELSE default_result END AS new_column FROM table_name;
3.SQL函数
通常在在select之后挨着使用 或 group by分组查询 having中使用
3.1 数学函数
求绝对值：abs() select abs(score) from course where id=1; 向下取整：floor select floor(score) from course where id=1; 向上取整：ceil() select ceil(score) from course where id=1;
3.2 日期函数
curdate()：获取当前日期 select curdate(); curtime()：获取当前时间 select curtime(); now()：获取当前日期+时间 select now(); datediff(d1,d2)：计算d1和d2之间间隔的天数 select datediff('2024-01-01','2025-12-30'); adddate(d,n)：d日期累加n天之后的日期 select adddate('2025-01-01',100); subdate(d,n)：d日期累加n天之前的日期 select subdate('2025-01-01',100);
3.3 字符串函数
insert(s1,index,len,s2) s1：是目标字符串 index：是下标 len：是添加的长度 s2：是追加的内容 select insert(name,2,2'MySQL') from course where id=1; upper()：将字母转为大写 select upper(name) from course where id=1; lower()：将字母转化为小写 select lower(name) from course where id=1; left(s,len)：返回s字符串的前len个字符 select left(name,2) from course where id=1; right(s,len)：返回s字符串的后len个字符 select right(name,2) from course where id=1; substr()/substring(s,index,len)：截取s字符串，从index开始，长度len select substring(name,1,2) from course where id=1; reverse()：反序输出 select reverse(name) from course where id=1; length()：返回字符串的长度 ascii()：返回字符串的ascll码 sleep()：将程序执行延迟 s秒
3.4 聚合函数
聚合函数：在from之前搭配使用;括号里面填写对象;where不能使用聚合函数_使用having count(*)函数：用于返回查询结果的总数据条数 avg()函数：平均值 sum()函数：求和 max()函数：最大值 min()函数：最小值 group_concat()函数：指定的字段进行合并/字符串连接 concat_ws()函数：“使用分隔符连接”将多个字符串值连接成一个字符串 if(exp1,xpe2,xpe3)：如果第一个语句/条件 正确，它就执行xep2，否则xep3说明举例
count()：计算行数 select count(id) from course; avg()：计算数值列中值的平均值 select avg(id) from course; 平均薪水大于50000的所有部门及其平均薪水 SELECT department, AVG(salary) AS average_salary FROM employees GROUP BY department HAVING AVG(salary) > 50000; sum()：计算数值列中值的总和 select sum(id) from course; max()：找出列中的最大值 select max(max) from course; min()：找出列中的最小值 select min(id) from course; group_count()：将列值连接成一个字符串 select group_count(id) from course; concat_ws()函数：“使用分隔符连接”将多个字符串值连接成一个字符串 如果任何字符串值为 NULL，则 concat_ws() 会忽略该 NULL 值 SELECT CONCAT_WS(' ', first_name, last_name) AS full_name FROM employees; SELECT CONCAT_WS(', ', first_name, middle_name, last_name) AS full_name FROM employees;
4.索引index
索引是用来提高查询效率的数据结构，它可以帮助我们快速定位到我们想要的数据 创建添加索引： create [unique唯一|fulltext全文|special空间] index 索引名称 on 表名(一个或多个字段名); alter table 表名 add index 索引名称; 查看索引 show index from 表名; 删除索引 drop index 索引名称 on 表名; alter table 表名 drop index 索引名称;
5.视图view
数据库中一张虚拟的表，允许不同的用户或者应用程序以不同的方式查看同一张表中的数据
是一种虚拟存在的表，本身并不包含数据，而是作为一个查询语句保持在数据字典中
当我们查询视图的时候，它会根据查询语句的定义，来动态的生成数据
比如不同级别查看工资表创建视图 create view 视图名称 as select 字段名1, 字段名2, ... from 表名称 where 条件; 查看/使用视图 select * from 视图名称; 修改视图 alter view 视图名称 as select 字段名1, 字段名2, ... from 表名称 where 条件; 删除视图 drop view 视图名称;
6.触发器trigger
触发器中定义了一系列操作，可以在对指定表进行插入、更新、删除的时候自动执行这些操作语句，完成对目标表的管理创建触发器 delimiter $$ create trigger 触发器名 [before|after] [insert|update|delete] on table_name for each row begin -- 触发器逻辑，即当触发条件满足时执行的SQL语句 -- 这里可以包含多条SQL语句 end $$ delimiter ; 注意： BEFORE 或 AFTER关键字：指定了触发器是在数据更改之前还是之后执行 INSERT、UPDATE、DELETE 关键字：指定了触发器响应的数据更改类型 table_name：是触发器所关联的表名 FOR EACH ROW：表示触发器会对受影响的每一行数据执行一次 在 BEGIN 和 END 之间：你可以编写任何合法的SQL语句，这些语句将作为触发器的逻辑部分执行 delimiter：这是MySQL特有的，用于更改命令的结束符 因为触发器内部可能会包含多条SQL语句，而这些语句通常是以分号(;)结束的 为了避免与触发器定义语句中的分号混淆，我们使用delimiter来更改命令的结束符 例如上面示例中的$$。在触发器定义完成后，再将结束符改回原来的; 删除触发器 drop trigger 触发器名;举例
delimiter $$ create trigger 触发器名 after insert on 表1 for each row begin insert into 表2(表2字段名) values(new.表1字段名); end $$ delimiter ; delimiter $$ create trigger 触发器名 after delete on 表1 for each row begin delete from 表2 where 表2字段名=old.表1字段名; end $$ delimiter ;
7.存储过程procedure
存储过程：是一组为了完成特点功能的SQL语句的集合，存储在数据库中的，用户通过指定存储过程的名字和参数进行调用
存储过程：相当于在MySQL中的方法，开发者可以直接调用，相当于函数方法调用
优点：
只需要创建一次，就可以任意调用
执行速度更快
更好的安全机制
参数：输入输出类型、参数名称、参数数据类型
in入参：Java方法中的参数，仅用于传入参数
out出参：Java方法中的返回值，仅用于返回值
inout：既可以传入，又可以当作返回值入参SQL
创建存储过程 delimiter $$ create procedure 存储过程名(in target int) begin declare 变量名 类型(大小); if target=1 then set 变量名="值1"; else set 变量名="值2"; end if; insert into 表名(变量名) values(变量名); end $$ delimiter ; 调用存储过程 call 存储过程名(参数); 删除存储过程 drop procedure 存储过程名;出参SQL
创建存储过程 delimiter $$ create procedure 存储过程名(out target int) begin select count(*) into target from 表名; end $$ delimiter ; 设置传入的参数 set @参数=值; 调用存储过程 call 存储过程名(@参数); 查询参数 select @参数; 删除存储过程 drop procedure 存储过程名;
8.事务transaction
SQL事务（Transaction）：是数据库管理系统执行过程中的一个逻辑单位，它由一系列SQL语句组成
这些语句作为一个整体一起向系统提交，要么全部执行成功，要么全部不执行，以保证数据库的一致性
小明 拿出100元，-100元钱包
小红 接受100元，+100元钱包
事务的四大特性通常简称为ACID：即原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）开始事务：InnoDB存储引擎支持事务 start transaction; begin; 执行事务 SQL代码 提交事务 commit; 回滚事务：从事务开始之后的操作，都要回滚到原来未修改的状态 rollback;
9.锁locks
锁（Locks）:并不是直接通过SQL语句来显式地创建或管理的，因为锁是数据库管理系统（DBMS）内部用于管理并发访问和数据完整性的机制
不过，你可以通过特定的SQL语句和事务控制来影响锁的行为
排他锁（Exclusive Locks）：一个事务在对某个数据项进行修改时，其他事务不能对该数据项进行修改或加锁
他锁常用于更新操作中，以保证数据的一致性和完整性
共享锁（Shared Locks）：允许一个事务读取一个数据项，而阻止其他事务对该数据项进行更新
如果数据项被加上了共享锁，那么其他事务也可以对其加共享锁，但无法加排他锁排他锁 开启锁：begin; 添加锁：在sql语句后添加 for update;语句 释放锁：commit; rollback; 举例 BEGIN; SELECT * FROM accounts WHERE id = 100 FOR UPDATE; -- 后续可以更新这条记录 UPDATE accounts SET balance = balance - 100 WHERE id = 100; COMMIT; 共享锁 开启锁：begin; 添加锁：在sql语句后添加 lock in share mode;语句 释放锁：commit; rollback; 举例 BEGIN; SELECT * FROM some_table WHERE some_column = 'some_value' LOCK IN SHARE MODE; COMMIT;
10.游标cursor
SQL游标（Cursor）是一种数据库查询工具，它允许你逐行访问查询结果集中的数据
游标特别适用于需要逐行处理数据的场景，如批量处理大量数据、逐行更新或检查数据等
游标在存储过程、触发器或复杂的SQL脚本中非常有用
游标的缺点
性能问题：游标操作通常比集合操作（如直接对表进行UPDATE或DELETE）要慢得多，因为游标是逐行处理的
资源消耗：游标会消耗额外的数据库资源，如内存和CPU时间，特别是在处理大量数据时
复杂性：编写使用游标的代码比编写基于集合的SQL代码要复杂得多，且更容易出错声明游标：cursor for declare cursor_name cursor for select colum_name(s) form table_name wherecondition; 打开游标:open open cursor_name; 从游标中获取数据:fetch next fetch next from cursor_name into variable_name(s); 关闭游标:close close cursor_name; 释放游标资源: deallocate cursor cursor_name;
六：项目中操作MySQL(检验 增删改查)
在项目js中引入如下步骤：举例
第一步：安装操MySQL数据库的第三方模块(mysql) npm install mysql 第二步：通过mysql模块连接到MySQL数据库 1．导入mysql模块 const mysql = require( ' mysql') 2．建立与MySQL数据库的连接 const db = mysq1.createPool({ host: '127.0.0.1'， //数据库的IP地址 user: 'root ' , //登录数据库的账号 password: '密码'， //登录数据库的密码 database: '数据库名' //指定要操作哪个数据库 }) 第三步：通过mysql模块执行SQL语句 1.检测mysql模块能否正常工作 调用db.query函数，指定要执行的SQL语句 db.query( ' SELECT 1 ' , (err,results) => { if (err) return console.log(err.message) //只要能打印出〔 RowDataPacket { '1':1}]的结果，就证明数据库连接正常 console.log(results) }) 2.增加 插入(使用insert into是对象形式展示) 要插入到users表中的数据对象 const user = { username: 'liuxinlei ' , password: '123456' } 待执行的SQL语句，其中英文的﹖表示占位符(下面两个等价) const sqlStr = 'INSERT INTO users (username，password) VALUES (?，?)' const sqlStr = 'INSERT INTO users SET ?' 使用数组的形式，依次为﹖占位符指定具体的值(下面两个等价) db.query(sqlStr，user，(err，results) =>{ db.query(sqlStr，[user.username，user.password]，(err，results) =>{ if (err) return console.log(err.message)//失败 if(results.affectedRows === 1) {console.log("插入数据成功') }//成功 }) 3.删(使用delete是对象形式展示) 推荐使用唯一标识id删除 要执行的SQL语句 const sqlStr = 'DELETE FROM users WHERE id=?' 调用db.query()执行SQL语句的同时，为占位符指定具体的值 db.query(sqlStr，666，(err, results) => { 标记删除:使用UPDATE 语句替代DELETE 语句;只更新数据的状态，并没有真正删除(下面两个等价) db.query(sqlStr，[1，666]，(err,results) =>{ db.query( 'UPDATE USERS SET status=1 WHERE id=?',666, (err, results) =>{ if (err) return console.log(err.message)//失败 if (results.affectedRows === 1) { console.log('删除数据成功! ')}//成功 }) 4.改 更新(使用update是对象形式展示) 要更新的数据对象 const user = { id: 666,username: 'liuxinlei ', password: '123456'} 要执行的SQL语句(下面两个等价) const sqlStr = "UPDATE users SET ? WHERE id=?' const sqlStr = "UPDATE users SET username=?，password=? WHERE id=?' 调用db.query()执行SQL语句的同时，使用数组依次为占位符指定具体的值(下面两个等价) db.query(sqlStr，[user,user.id]，(err,results) =>{ db.query(sq1lStr，[user.username，user.password，user.id]，(err，results)=> { if (err) return console.log(err.message)//失败 if (results.affectedRows === 1) { console.log('更新数据成功! ')}//成功 }) 5.查询(使用select是对象数组形式展示) users表中所有的用户数据 db.query( 'SELECT * FROM users ', (err, results) =>{ //查简失败 if (err) return console.log(err.message) //查询成功 console.log(results) })
扫一扫
429
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
