mysql基础
MySQL概述
1、数据库相关概念
主流的关系型数据库：
总结
1)数据库:数据存储的仓库
2)数据库管理系统:操纵和管理数据库的大型软件
3)SQL:操作关系型数据库的编程语言，是一套标准
2、MySQL数据库下载及安装
- 版本
MySQL官方提供了两种不同的版本:
社区版(MySOLCommunity Server) =>免费，MySQL不提供任何技术支持
商业版(MySQLEnterprise Edition)=>收费，可以试用30天，官方提供技术支持
-
下载
下载地址:https://dev.mysql.com/downloads/windows/installer/8.0.htm -
安装后WinDos下的数据默认保存路径
C:\ProgramData\MySQL\MySQL Server 8.0\Data
3、启动和停止
mysql在安装时默认是开机自动cmd
windos下：用管理员身份启动命令
net start mysql80
net stop mysql80
4、客户端连接
//系统自带的命令行工具
mysql [-h 127.0.0.1][-P 3306] -u root -p//需要配置环境变量Path C:\Program Files\MySQL\MySQL Server 8.0\bin
关系型数据库(补充)
- 概念:建立在关系模型基础上，由多张相互连接的二维表组成的数据库。
- 特点:
1、使用表存储数据，格式统一，便于维护
2、使用SQL语言操作，标准统一，使用方便
SQL
1、SQL的通用语法
SQL语句可以单行或多行书写，以分号结尾。
SQL语句可以使用空格/缩进来增强语句的可读性。(空格和缩进不受个数影响)
MySQL数据库的SQL语句不区分大小写，关键字建议使用大写。
注释:
单行注释: -- 注释内容 或 #注释内容(MySQL特有--后面要有空格)
多行注释: /注释内容/
2、SQL语句的分类
| 分类 | 全称 | 说明 |
|---|---|---|
| DDL | Data Definition Language | 数据定义语言，用来定义数据库对象(数据库，表，字段) |
| DML | Data Manipulation Language | 数据操作语言，用来对数据库表中的数据进行增删改 |
| DQL | Data Query Language | 数据查询语言，用来查询数据库中表的记录 |
| DCL | Data Control Language | 数据控制语言，用来创建数据库用户、控制数据库的访问权限 |
DDL
- 数据库操作
查询:
SHOW DATABASES;//查询所有数据库
SELECT DATABASE();//查询当前数据库
创建数据库:
CREATE DATABASE[IF NOT EXISTS]数据库名[DEFAULT CHARSET 字符集][COLLATE 排序规则];
IF NOT EXISTS - 如果不存在在进行创建
DEFAULT CHARSET - 如utf8,推荐使用utf8mb4可以占用4个字节
删除:
DROP DATABASE [IF EXISTS]数据库名
IF EXISTS - 如果存在
使用:
USE 数据库名;//切换数据库
- 表操作
查询:
SHOW TABLES;//查询当前数据库下的所有表
DESC 表名;//查询表结构
SHOW CREATE TABLE 表名;查询指定表的建表语句
创建:
CREATE TABLE 表名(
字段1 字段1类型[COMMENT 字段1注释],
字段2 字段2类型[COMMENT 字段2注释],
字段3 字段3类型[COMMENT 字段3注释],
字段n 字段n类型[COMMENT 字段n注释]
)[COMMENT 表注释];//创建表
-
数据类型
mysql中主要的数据类型分为三类:数值类型，字符串类型，日期时间类型
-
修改
ALTER TABLE 表名 ADD 字段名 类型(长度)[COMMENT 注释][约束];//添加字段
ALTER TABLE 表名 MODIFY 字段名 新数据类型(长度);//修改字段数据类型
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型 (长度)[COMMENT 注释][约束];//修改字段名称和数据类型
ALTER TABLE 表名 RENAME TO 新表名;//修改表名
- 删除
ALTER TABLE 表名 DROP 字段名;//删除字段
DROP TABLE [IF EXISTS]表名;//删除表
TRUNCATE TABLE 表名;//删除指定表，并重新创建该表
DML(增删改)
DML英文全称是Data Manipulation Language(数据操作语言)，用来对数据库中表的数据记录进行增删改操作。
- 添加-inseter
INSERT INTO 表名 (字段名1,字段名2,..) VALUES(值1,值2,….);//给全部字段添加数据’
INSERT INTO 表名 VALUES (值1,值2,….);//批量添加数据
//批量添加数据
INSERT INTO 表名 (字段名1,字段名2,..) VALUES(值1,值2,...),(值1,值2,.),(值1,值2,.);
INSERT INTO 表名 VALUES (值1,值2,….),(值1,值2,….)(值1,值2,…);
注意:
插入数据时，指定的字段顺序需要与值的顺序是-一对应的。
字符串和日期型数据应该包含在引号中。
插入的数据大小，应该在字段的规定范围内。
- 修改-update
UPDATE 表名 SET 字段名1=值1,字段名2=值2,[WHERE 条件];//修改数据
注意:修改语句的条件可以有，也可以没有，如果没有条件，则会修改整张表的所有数据。
- 删除-delete
DELETE FROM 表名「WHERE 条件]
注意:
DELETE 语句的条件可以有，也可以没有，如果没有条件，则会删除整张表的所有数据。
DELETE 语句不能删除某一个字段的值(可以使用UPDATE)。
DQL(重点)
语法:
SELECT//字段列表
FROM//表名列表
WHERE//条件列表
GROUP BY//分组字段列表
HAVING//分组后条件列表
ORDER BY//排序字段列表
LIMIT//分页参数
- 基本查询
SELECT 字段1,字段2,字段3 ...FROM 表名;//查询指定字段
SELECT * FROM 表名;//查询全部字段
SELECT 字段1「AS 别名1],字段2「AS 别名2] …FROM 表名;//查询并设置别名
SELECT DISTINCT 字段列表 FROM 表名;去除重复记录
- 条件查询 (where)
语法:
SELECT 字段列表 FROM 表名 WHERE 条件列表；
条件:
| 比较运算符 | 功能 |
|---|---|
| > | 大于 |
| >= | 大于等于 |
| < | 小于 |
| <= | 小于等于 |
| = | 2 |
| <>或!= | 不等于 |
| between...and | 在xxx范围内(含最小值和最大值) |
| in(...) | 在...范围内 |
| like 占位符 | 模糊匹配,_匹配单个字符。%匹配任意字符 |
| is null | 是null |
| 逻辑运算符 | 功能 |
|---|---|
| and或'&&' | 并且 |
| or | 或者 |
| not或'!' | 非 |
- 聚合查询(count、max、min、avg、sum)
介绍:
将一列数据作为一个整体，进行纵向计算。
常见的聚合函数:
| 逻辑运算符 | 功能 |
|---|---|
| count | 统计数量 |
| max | 最大值 |
| min | 最小值 |
| avg | 平均值 |
| sum | 求和 |
语法:
SELECT 聚合函数(字段列表) FROM 表名；
注意:
null不参与聚合函数运算
- 分组查询
语法:
SELECT 字段列表 FROM 表名 [WHERE 条件] GROUP BY 分组字段名 [HAVING 分组后过滤条件];
where与having区别:
执行时机不同:where是分组之前进行过滤，不满足where条件，不参与分组;而having是分组之后对结果进行过滤。
判断条件不同:where不能对聚合函数进行判断而having可以。
注意:
执行顺序: where >聚合函数>having
分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义**
- 排序查询
语法:
SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1,字段2 排序方式2;
排序方式:
ASC:升序(默认值)
DESC:降序
注意:如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序
- 分页查询
语法:
SELECT 字段列表 FROM 表名 LIMIT 起始索引,查询记录数;
注意:
起始索引从0开始，起始索引=(查询页码-1)*每页显示记录数。
分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中是LIMIT。
如果查询的是第一页数据，起始索引可以省略，直接简写为limit 10。
- 执行顺序
FROM表名列表
WHERE条件列表
GROUP BY分组字段列表
HAVING分组后条件列表
SELECT字段列表
ORDER BY排序字段列表
LIMIT分页参数
DCL
DCL英文全称是Data ControlLanguage(数据控制语言)，用来管理数据库用户 、控制数据库的访问权限
- 管理用户
USE mysql;//指定数据库
SELECT * FROM user;//查询用户
CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';//创建用户（一般与权限配合使用）
-主机名:是指在哪台主机上可以访问这个数据库，写localhost的话设置只能在当前主机访问，%可以在任何主机
ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql native password BY '新密码';//修改用户密码
DROP USER '用户名'@'主机名';//删除用户
- 权限控制
| 权限 | 说明 |
|---|---|
| ALL,ALL PRIVILEGES | 所有权限 |
| SELECT | 查询数据 |
| INSERT | 插入数据 |
| UPDATE | 修改数据 |
| DELETE | 删除数据 |
| ALTER | 修改表 |
| DROP | 删除数据库/表/试图 |
| CREATE | 创建数据库/表 |
查询权限:
SHOW GRANTS FOR'用户名'@'主机名';
授予权限:
GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';
撤销权限:
REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';
注意:
多个权限之间，使用逗号分隔
授权时，数据库名和表名可以使用*进行通配，代表所有，权限列表all代表所有权限
函数
函数是指一段可以直接被另一段程序调用的程序或代码
- 字符串函数-常用
| 函数 | 功能 |
|---|---|
| CONCAT(S1,S2,...Sn) | 字符串拼接，将S1，S2，..Sn拼接成一个字符串 |
| LOWER(str) | 将字符串str全部转为小写 |
| UPPER(str) | 将字符串str全部转为大写 |
| LPAD(str,n,pad) | 左填充，用字符串pad对str的左边进行填充，达到n个字符串长度 |
| RPAD(str,n,pad) | 右填充，用字符串pad对str的右边进行填充，达到n个字符串长度 |
| TRIM(str) | 去掉字符串头部和尾部的空格 |
| SUBSTRING(str,start,len) | 返回从字符串str从start位置起的len个长度的字符串 |
- 数值函数
| 函数 | 功能 |
|---|---|
| CEIL(x) | 向上取整 |
| FLOOR(x) | 向下取整 |
| MOD(x,y) | 返回x/y的模，模的意味x除y取余 |
| RAND() | 返回0~1内的随机数 |
| ROUND(x,y) | 求参数x的四舍五入的值，保留y位小数 |
- 日期函数:
| 函数 | 功能 |
|---|---|
| CURDATE() | 返回当前日期 |
| CURTIME() | 返回当前时间 |
| NOW() | 返回当前日期和时间 |
| YEAR(date) | 获取指定date的年份 |
| MONTH(date) | 获取指定date的月份 |
| DAY(date) | 获取指定date的日期 |
| DATE ADD(date, INTERVAL expr type) | 返回一个日期/时间值加上一个时间间隔expr后的时间值 |
| DATEDIFF(date1,date2) | 返回起始时间date1 和 结束时间date2之间的天数 |
- 流程控制
| 函数 | 功能 |
|---|---|
| IF(value ,t , f) | 如果value为true，则返回t，否则返回f |
| IFNULL(value1 ,value2) | 如果value1不为空，返回value1，否则返回value2 |
| CASE WHEN [val1 ]THEN [res1].. ELSE [default]END | 如果val1为true，返回res1，…否则返回default默认值 |
| CASE [expr ] WHEN [val1 ] THEN [res1]..ELSE[ default] END | 如果expr的值等于val1，返回res1，..否则返回default默认值 |
约束
- 概念:
约束是作用于表中字段上的规则，用于限制存储在表中的数据 - 目的:
保证数据库中数据的正确、有效性和完整性
| 约束 | 描述 | 关键字 |
|---|---|---|
| 非空约束 | 限制该字段的数据不能为null | NOT NULL |
| 唯一约束 | 保证该字段的所有数据都是唯一、不重复的 | UNIQUE |
| 主键约束 | 主键是一行数据的唯一标识，要求非空且唯一 | PRIMARY KEY(自增:AUTO INCREMENT) |
| 默认约束 | 保存数据时，如果未指定该字段的值，则采用默认值 | DEFAULT |
| 检查约束(8.0.16版本之后) | 保证字段值满足某一个条件 | CHECK |
| 外键约束 | 用来让两张表的数据之间建立连接，保证数据的一致性和完整性 | FOREIGN KEY |
约束是作用于字段的，可以在创建或修改表时添加约束,对于一个字段可以添加多个约束
| 字段名 | 含义 | 字段类型 | 约束条件 | 约束关键字 |
|---|---|---|---|---|
| id | id唯一标识 | int | 主键自动增长 | 5 |
| name | 姓名 | varchar(10) | 非空且唯一 | 5 |
| age | 年龄 | int | 大于0小于等于120 | 5 |
| status | 状态 | char(1) | 默认值为1 | 5 |
| gender | 性别 | char(1) | 无 | 5 |
create table user(
id int primary key auto_increment comment '主键'
name varchar(10) not null unique comment '姓名'
age int check(age>0 && age <= 120)comment'年龄'
status char(1)default'1'comment'状态',
gender char(1)comment'性别
comment'用户表'
- 外键约束
概念
外键用来让两张表的数据之间建立连接，从而保证数据的一致性和完整性
注意:目前上述的两张表，在数据库层面，并未建立外键关联，所以是无法保证数据的一致性和完整性的
添加外键:
CREATE TABLE 表名(
字段名 数据类型，
...
[CONSTRAINT] [外键名称] FOREIGN KEY(外键字段名) REFERENCES 主表 (主表列名);//创建时直接添加
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY(外键字段名) REFERENCES 主表 (主表列名);//创建之后再添加外键约束
删除外键:
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;
外键的删除和更新行为:
| 行为 | 说名 |
|---|---|
| NO ACTION | 当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新。(与RESTRICT一致) |
| RESTRICT | 当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除更新。(与 NO ACTON一致) |
| CASCADE(级联) | 当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有，则也删除/更新外键在子表中的记录。 |
| SET NULL | 当在父表中删除对应记录时，首先检查该记录是否有对应外键，如果有则设置子表中该外键值为nul(这就要求该外键允许取nul) |
| SET DEFAULT | 父表有变更时，子表将外键列设置成一个默认的值(Innodb不支持) |
实例:
ALTERTABLE 表名 ADD CONSTRAINT 外键名称 FOREIGNKEY(外键字段)REFERENCES 主表名(主表字段名) ON UPDATE CASCADE ON DELETE CASCADE,
多表查询
- 多表关系
概念
项目开发中，在进行数据库表结构设计时，会根据业务需求及业务模块之间的关系，分析并设计表结构，由于业务之间相互关联。
所以各个表结构之间也存在着各种联系，基本上分为三种:
一对多(多对一)
案例:部门与员工
关系:一个部门对应多个员工，一个员工对应一个部门
实现:在多的一方建立外键，指向一的一方的主键
多对多
案例: 学生与课程
关系:一个学生可以选修多门课程，一门课程也可以供多个学生选择
实现:建立第三张中间表，中中间表至少包含两个外键，分别关联两方主键
一对一
案例: 用户 与 用户详情的关系
关系: 一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他详情字段放在另一张表中，以提升操作效率
实现:在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的(UNIQUE)
-
多表查询概述
概述: 指从多张表中查询数据
笛卡尔积:笛卡尔乘积是指在数学中，两个集合A集合 和 B集合的所有组合情况。(在多表查询时，需要消除无效的笛卡尔积) -
内连接
相当于查询A、B交集部分数据
隐式内连接:
SELECT字段列表 FROM 表1,表2 WHERE 条件…;
**显式内连接:推荐性能更好**
SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 连接条件 …;//INNER可以省略
- 外连接
左外连接
SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件…;//OUTER可以省略
相当于查询表1(左表)的所有数据 包含 表1和表2交集部分的数据
右外连接
SELECT 字段列表 FROM 表1 RIGHT[OUTER]JOIN 表2 ON 条件…;//OUTER可以省略
相当于查询表2(右表)的所有数据 包含 表1和表2交集部分的数据
- 自连接
当前表与自身的连接查询，自连接必须使用表别名
SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名 B ON 条件... ;
select a.nameb.name from emp a , emp b where a.managerid =b.id；//内连接查询
select a.name '员工',b.name '领导'from emp a left join emp b on a.managerid = b.id;//外连接查询
自连接查询，可以是内连接查询，也可以是外连接查询
- 联合查询union、union all
对于union查询，就是把多次查询的结果合并起来，形成一个新的查询结果集。
对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致
区别:
union all会把查询的结果直接进行叠加这样可能会导致重复数据的产生，union则会对查询出的结果进行去重。
SELECT 字段列表 FROM 表A …
UNION [ALL ]
SELECT 字段列表 FROM 表B
例如:将薪资低于 5000 的员工，和年龄大于 50 岁的员工全部查询出来。
select * from emp where salary < 5000
union all
select * from emp where age >50;
- 子查询
概念: SQL语句中嵌套SELECT语句，称为嵌套查询又称子查询。
子查询外部的语句可以是INSERT/UPDATE/DELETE/SELECT的任何一个
根据子查询结果不同可分为:标量子查询、列子查询、行子查询、表子查询
1、标量子查询(子查询结果为单个值)
SELECT*FROM t WHERE column1=(SELECT column1 FROM t2);
子查询返回的结果是单个值(数字、字符串、日期等)，最简单的形式，这种子查询成为标量子查询。
常用的操作符:= 、<> 、> 、>= 、< 、<=
例:查询“销售部”的所有员工信息
1.查询“销售部”部门ID
select id from dept where name ='销售部'
2.根据销售部部门TD，查询员工信息
select * from emp where dept id = (select id from dept where name ='销售部');
2、列子查询(子查询结果为一列)
子查询返回的结果是一列(可以是多行)，这种子查询称为列子查询
常用的操作符:IN、NOTIN、ANY、SOME、ALL
| 操作符 | 描述 |
|---|---|
| IN | 在指定的集合范围之内，多选一 |
| NOT IN | 不在指定的集合范围之内 |
| ANY | 子查询返回列表中，有任意一个满足即可 |
| SOME | 与ANY等同，使用SOME的地方都可以使用ANY |
| ALL | 子查询返回列表的所有值都必须满足 |
例1:“销售部”和“市场部”的所有员工信息
1、查询“销售部”和“市场部”的部门ID
select id from dept where name ='销售部'or name ='市场部';
2、根据部门ID，查询员工信息
select * from emp where id in (select id from dept where name ='销售部'or name ='市场部');
例2:查询比 财务部 所有工资都高的员工信息
a.查询所有 财务部 人员工资
select id from dept where name ='财务部';
select salary from emp where dept_id =(select id from dept where name ='财务部');
b.比财务部 所有人工资都高的员工信息h..
select * from emp where salary > all (select salary from emp where dept id = (select id from dept where name = "财务部’));
例3:查询比研发部其中任意一人工资高的员工信息
a.查询研发部所有人工资
select salary from emp where dept id =(select id from dept where name ='硏发部');
b.比研发部其中任意一人工资高的员工信息
select * from emp where salary > any (select salary from emp where dept id = (select id from dept where name = '硏发部'));
3、行子查询(子查询结果为一行)
子查询返回的结果是一行(可以是多列)，这种子查询称为行子查询常用的操作符:=、<>、IN、NOT IN
例:查询与"张无忌”的薪资及直属领导这两条数据都相同的员工信息;
a-查询“张无忌”的薪资及直属领导
select salary,managerid from emp where name ='张无忌';
b-查询与"张无忌”的薪资及直属领导相同的员工信息:
select * from emp where (salary,managerid) = (select salary, managerid from emp where name = '张无忌');
4、表子查询(子查询结果为多行多列)
概念:子查询返回的结果是多行多列，这种子查询称为表子查询
例1:查询与“鹿杖客"“宋远桥”的职位和薪资相同的员工信息
a查询“鹿杖客""宋远桥”的职位和薪资0 .
select job,salary from emp where name ='鹿杖客'or name ='宋远桥';
b:查询与“鹿杖客"，“宋远桥”的职位和薪资相同的员工信息
select * from emp where (job,salary) in ( select job, salary from emp where name ='鹿杖客'or name ='宋远桥');
例2:查询入职日期是“2006-01-01”之后的员工信息，及其部门信息
--a:入职日期是“2006-01-01”之后的员工信息
select *from emp where entrydate>'2006-01-01';
--b:查询这部分员工，对应的部门信息:
select e.*, d.* from (select * from emp where entrydate > '2006-01-01') e left join dept d on e.dept id = d.id ;
