MySQL学习笔记
这篇文章的笔记是基于黑马程序员MySQL的教程。
1.1MySQL的安装
参考(110条消息) 2023 年 MySQL 8.0 安装配置 最简易(保姆级)_mysql8.0安装配置教程_mobeicanyue的博客-CSDN博客
1.2.MySQL的启动和停止
MySQL默认是开启的,也可以手动关闭和开启。
开启:net start MySQL
关闭:net stop MySQL
这里的MySQL
名称是在安装的时候设置的。
使用系统命令来连接数据库
mysql [-h 127.0.0.1] [-P 3306] -u root -p
参数:
-h : MySQL服务所在的主机IP
-P : MySQL服务端口号, 默认3306
-u : MySQL数据库用户名
-p : MySQL数据库用户名对应的密码
内为可选参数,如果需要连接远程的MySQL,需要加上这两个参数来指定远程主机IP、端口,如果
连接本地的MySQL,则无需指定这两个参数
1.3.关系型数据库的概念
建立在关系模型基础上,由**多张相互连接的二维表(例如excel表)**组成的数据库
简单说,基于二维表存储数据的数据库就成为关系型数据库,不是基于二维表存储数据的数据库,就是非关系型数据库。
- 从电脑通过DBMS使用SQL语言来操作数据库。
- 一个数据库服务器中可以创建多个数据库,一个数据库中也可以包含多张表,而一张表中又可以包
含多行记录
2.1 SQL通用语法
1). SQL语句可以单行或多行书写,以分号结尾。
2). SQL语句可以使用空格/缩进来增强语句的可读性。
3). MySQL数据库的SQL语句不区分大小写,关键字建议使用大写。
4). 注释:
单行注释:-- 注释内容 或 # 注释内容
多行注释:/* 注释内容 */
2.2 SQL分类
- 数据定义 DDL
- 数据的增删改 DML
- 数据的差 DQL
- 对用户权限的设置 DCL
2.3 DDL
定义数据库各个对象的操作:
- 库对象
- 表
- 字段
2.3.1 数据库的操作
查询所有数据库
show databases;
查询当前数据库
select database();
创建数据库
create database [ if not exists ] 数据库名 [ default charset 字符集 ] [ collate 排序
规则 ] ;
删除数据库
drop database [ if exists ] 数据库名 ;
切换数据库
我们要操作某一个数据库下的表时,就需要通过该指令,切换到对应的数据库下,否则是不能操作的。
比如,切换到itcast数据,执行如下SQL:
use itcast
2.3.2 表操作
查询当前数据库所有表
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 表注释 ] ;
注意: […] 内为可选参数,最后一个字段后面没有逗号
2.3.3 数据类型
MySQL中的数据类型有很多,主要分为三类:数值类型、字符串类型、日期时间类型
数值类型:
有时候需要使用无符号的,则使用
1). 年龄字段 – 不会出现负数, 而且人的年龄不会太大
age tinyint unsigned
2). 分数 – 总分100分, 最多出现一位小数
score double(4,1)
字符串类型
char 与 varchar 都可以描述字符串,char是定长字符串,指定长度多长,就占用多少个字符,和
字段值的长度无关 。而varchar是变长字符串,指定的长度为最大占用长度 。相对来说,char的性
能会更高些(定长存储相比变长少去了计算的过程)
如:
1). 用户名 username ------> 长度不定, 最长不会超过50
username varchar(50)
2). 性别 gender ---------> 存储值, 不是男,就是女
gender char(1)
3). 手机号 phone --------> 固定长度为11
phone char(11)
日期时间类型
如:
1). 生日字段 birthday
birthday date
2). 创建时间 createtime
createtime datetime
小练习:
对应建表语句:
create table person(
-> user_id int,
-> card_id varchar(10),
-> name varchar(10),
-> gender int,
-> age tinyint unsigned,
-> id_card varchar(18),
-> workentertime Date
-> );
2.3.4 表操作修改
添加字段
alter table 表名 add 字段名 类型(长度) [ COMMENT 注释 ] [ 约束 ];
为emp表增加一个新的字段”昵称”为nickname,类型为varchar(20)
ALTER TABLE emp ADD nickname varchar(20) COMMENT '昵称';
修改字段数据类型
alter table 表名 modify 字段名 类型(长度) [ COMMENT 注释 ] [ 约束 ];
修改字段名和字段类型:
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型 (长度) [ COMMENT 注释 ] [ 约束 ];
删除字段名
ALTER TABLE 表名 DROP 字段名;
修改表名
ALTER TABLE 表名 RENAME TO 新表名;
2.3.5 表删除
删除表
DROP TABLE [ IF EXISTS ] 表名;
删除指定表,并创建一个新表
TRUNCATE TABLE 表名;
注意: 在删除表的时候,表中的全部数据也都会被删除。
2.4 图形化工具
2.5 DML
数据操作语言
2.5.1 添加数据:(insert info)
给指定字段添加数据:
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, ...) ;
插入注意事项
- 插入数据时,指定的字段顺序需要与值的顺序是一一对应的
- 字符串和日期型数据应该包含在引号中
- 插入的数据大小,应该在字段的规定范围内
- 如果只指定了某个字段,则其它字段为空。
2.5.2 更新数据(update && set)
UPDATE 表名 SET 字段名1 = 值1 , 字段名2 = 值2 , .... [ WHERE 条件 ] ;
Tips:
- 如果没有条件的话,就是选择所有的条件。
2.5.3 删除数据(delete)
DELETE FROM 表名 [ WHERE 条件 ] ;
删除所有员工:
delete from 表名;
Tips:
- DELETE 语句的条件可以有,也可以没有,如果没有条件,则会删除整张表的所有数据
- DELETE 语句不能删除某一个字段的值(可以使用UPDATE,将该字段值置为NULL即可)
2.6 DQL
2.6.1 基本语法
SELECT 字段列表 FROM 表名列表 WHERE 条件列表 GROUP BY 分组字段列表 HAVING 分组后条件列表 ORDER BY 排序字段列表 LIMIT 分页参数
2.6.2 基本查询
查询多个字段
select(字段1,字段2,字段3) from 表
SELECT * FROM 表名 ;
注意 : * 号代表查询所有字段,在实际开发中尽量少用(不直观、影响效率)
字段设置别名
SELECT 字段1 [ AS 别名1 ] , 字段2 [ AS 别名2 ] ... FROM 表名;
SELECT 字段1 [ 别名1 ] , 字段2 [ 别名2 ] ... FROM 表名;
可以不用写as
去除重复记录
SELECT DISTINCT 字段列表 FROM 表名;
2.6.3 条件查询
SELECT 字段列表 FROM 表名 WHERE 条件列表 ;
条件:
2.6.4 聚合函数
作用于某一列数据的。
SELECT 聚合函数(字段列表) FROM 表名 ;
NULL值是不参加任何运算的。
2.6.5 分组查询 – 重点
SELECT 字段列表 FROM 表名 [ WHERE 条件 ] GROUP BY 分组字段名 [ HAVING 分组后过滤条件 ]
Tip:
where
是分组之前过滤,having
是分组之后对结果进行过滤。- 分组之后,查询的字段一般为聚合函数和分组字段,查询其他字段无任何意义。
- 执行顺序: where > 聚合函数 > having
例题:
# 统计各个工作地址上班的男性及女性员工的数量d 分组然后再进行统计count
select workaddress, gender, count(*) '数量' from emp group by gender , workaddress;
2.6.6 排序查询
SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1 , 字段2 排序方式2 ;
排序方式
- ASC:升序
- DESC:降序
- 如果是升序, 可以不指定排序方式ASC
- 如果是多字段排序,当第一个字段值相同时,才会根据第二个字段进行排序 ;
2.7.7 分页查询
语法:
SELECT 字段列表 FROM 表名 LIMIT 起始索引, 查询记录数 ;
Tips:
- 分页查询是数据库的
方言
,不同的数据库有不同的实现,MySQL中是LIMIT。- 起始索引从0开始,起始索引 = (查询页码 - 1)* 每页显示记录数。
- 如果查询的是第一页数据,起始索引可以省略,直接简写为 limit 10。
2.7.8 执行顺序
2.7 DCL
2.7.1 查询用户
select * from mysql.user;
Tip:
- Host代表当前用户访问的主机, 如果为localhost, 仅代表只能够在当前本机访问,是不可以远程访问的。 User代表的是访问该数据库的用户名。在MySQL中需要通过Host和User来唯一标识一个用户。
2.7.2 创建用户
CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
2.7.3 修改用户密码
ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码' ;
2.7.4 删除用户
DROP USER '用户名'@'主机名' ;
Tips:
- 在MySQL中需要通过用户名@主机名的方式,来唯一标识一个用户
- 这类SQL开发人员操作的比较少,主要是DBA( Database Administrator 数据库管理员)使用。
2.7.5 权限控制
上述只是简单罗列了常见的几种权限描述,其他权限描述及含义,可以直接参考官方文档
查询权限
SHOW GRANTS FOR '用户名'@'主机名' ;
授予权限
GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';
撤销权限
REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';
DCL内容不做重点
3.函数
3.1 字符串函数
3.2 数值函数
生成随机验证码1
3.3 日期函数
3.4 流程函数
4.约束
概念:作用于表中的字段,限制存储在表中的数据。
目的:保证数据的一致性,有效性和完整性。
4.1 分类
- 约束时作用于表中的字段的,可以在
创建/添加表
的时候添加约束。
4.2 约束展示使用
前面所说的约束是在建表的时候约定的,因此建表语句为
CREATE TABLE tb_user(
id int AUTO_INCREMENT PRIMARY KEY COMMENT 'ID唯一标识',
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 '性别'
);
通过DataGrip
图形化界面来创建:
4.3.3 外键约束
用来让两张表的数据之间建立连接,从而保证数据的一致性和完整性。
删除一张表的数据,另一张表==逻辑上关联了==这张表,但是数据库并没有关联,这个时候就需要使用外键约束了。
CREATE TABLE 表名(
字段名 数据类型,
...
[CONSTRAINT] [外键名称] FOREIGN KEY (外键字段名) REFERENCES 主表 (主表列名)
)
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名)
REFERENCES 主表 (主表列名) ;
案例:为emp表的dept_id字段添加外键约束,关联dept表的主键id。
alter table emp add contraint foreign_id FOREIGN KEY(dept_id) references dept(id)
添加了外键约束之后,我们再到dept表(父表)删除id为1的记录,然后看一下会发生什么现象。 此时将会报错,不能删除或更新父表记录,因为存在外键约束
删除外键
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称
例如:
alter table emp drop foreign key fk_emp_dept_id;
4.3.4 删除/更新行为
添加了外键之后,再删除父表数据时产生的约束行为,我们就称为删除/更新行为。
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段) REFERENCES 主表名 (主表字段名) ON UPDATE CASCADE ON DELETE CASCADE;
在添加外键的时候来设置这个行为规则。下面来看一下案例:
由于NO ACTION 是默认行为,我们前面语法演示的时候,已经测试过了,就不再演示了,这里我们再
演示其他的两种行为:CASCADE、SET NULL。
- cascade
alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references
dept(id) on update cascade on delete cascade ;
子表中与之相关联的数据也被删除了。
- set null
与之对应:如果删除了父表中的字段那么子表相关的就会将置
null
5.多表查询
5.1 多表关系
- 由于业务之间相互关联,所以各个表结构之间也存在着各种联系
关系大致可以分为三种:
- 一对多,多对一
- 一对一
- 多对多
一对多:部门与员工之间的关系
一个部门对应多个员工,一个员工对应一个部门
实现:在员工的一方建立主键,去关联部门。
多对多:学生与课程之间的关系
关系: 一个学生可以选修多门课程,一门课程也可以供多个学生选择
实现: 建立第三张中间表,中间表至少包含两个外键,分别关联两方主键
一对一:用户和用户详情之间的关系
关系: 一对一关系,多用于单表拆分,将一张表的基础字段放在一张表中,其他详情字段放在另
一张表中,以提升操作效率
实现: 在任意一方加入外键,关联另外一方的主键,并且设置外键为唯一的(UNIQUE)
5.2 多表查询
从多张表中查询数据
select * form emp,dept;
从emp
,dept
中查询数据。得到的是emp
&& dept
的笛卡尔积。
在多表查询中,我们并不需要笛卡尔积,因此需要消除无关的笛卡尔积,只保留两张表的关键部分。
在SQL语句中,如何来去除无效的笛卡尔积呢? 我们可以给多表查询加上连接查询的条件即可
5.3 分类
- 连接查询
- 内连接:A和B交集部分
- 外连接
- 左外连接:左边所有加上交集部分
- 右外连接:右边所有加上交集部分
- 自连接:自己与自己进行连接
- 子查询
5.4 内连接
隐式内连接:
SELECT 字段列表 FROM 表1 , 表2 WHERE 条件 ... ;
显式内连接:
SELECT 字段列表 FROM 表1 [ INNER ] JOIN 表2 ON 连接条件 ... ;
例子:
查询每一个员工的姓名 , 及关联的部门的名称
select emp.name , dept.name from emp , dept where emp.dept_id = dept.id ;
-- 为每一张表起别名,简化SQL编写
select e.name,d.name from emp e , dept d where e.dept_id = d.id;
- 一旦为表起了别名,就不能==再使用表名来指定对应的字段了,此时只能够使用别名来指定字段==
5.5 外连接
左外连接
SELECT 字段列表 FROM 表1 LEFT [ OUTER ] JOIN 表2 ON 条件 ... ;
右外连接
SELECT 字段列表 FROM 表1 RIGHT [ OUTER ] JOIN 表2 ON 条件 ... ;
- 左外连接相当于查询表1(左表)的所有数据,当然也包含表1和表2交集部分的数据。
- 右外连接相当于查询表2(右表)的所有数据,当然也包含表1和表2交集部分的数据
- 注意事项:
- 左外连接和右外连接是可以相互替换的,只需要调整在连接查询时SQL中,表结构的先后顺
序就可以了。而我们在日常开发使用时,更偏向于左外连接。
5.6 自连接
自连接查询,顾名思义,就是**自己连接自己,也就是把一张表连接查询多次**。
语法
SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件 ... ;
Tips:
- 自连接必须要有别名
5.7 联合查询
对于union
查询,就是把多次查询的结果合并起来,形成一个新的查询结果集。
SELECT 字段列表 FROM 表A ...
UNION [ ALL ]
SELECT 字段列表 FROM 表B ....;
union all
会将全部的数据直接合并在一起,union
会对合并之后的数据去重。- 对于联合查询的多张表的列数必须保持一致,字段类型也需要保持一致。
- 如果多条查询语句查询出来的结果,字段数量不一致,在进行union/union all联合查询时,将会报错。
5.8 子查询
SQL语句中嵌套SELECT语句,称为嵌套查询,又称子查询。
SELECT * FROM t1 WHERE column1 = ( SELECT column1 FROM t2 );
- 子查询外部的语句可以是INSERT / UPDATE / DELETE / SELECT 的任何一个。
分类:
- 标量子查询(子查询结果为单个值)
- 列子查询(子查询结果为一列)
- 行子查询(子查询结果为一行)
- 表子查询(子查询结果为多行多列)
标量子查询
示例:查询**‘销售部’**所有员工的信息
- 首先查询销售部的id
- 根据id去查询部门所有员工的消息
select id from dept where name='销售部';
select * from emp where dept_id=4;
select * from emp where dept_id=(select id from dept where name='销售部');
查询方东白入职之后的员工信息
# 查询在 "方东白" 入职之后的员工信息
select * from emp where entrydate >= (select entrydate
from emp where name='方东白');
列子查询
返回的是一列或者多行,因此,进行操作符进行操作
select * from emp where dept_id in (select id from dept where name='市场部' or name='销售部');
例子:
查询比 财务部 所有人工资都高的员工信息
- 查询财务部所有员工的工资
- 查询比财务部所有员工工资高的人
行子查询
案例:
查询与 “张无忌” 的薪资及直属领导相同的员工信息
- 查询张无忌的薪资及其直属领导
- 查询与 “张无忌” 的薪资及直属领导相同的员工信息 ;
select * from emp where (salary,managerid)=(select salary,managerid from emp where name = '张无忌');
注意:
- 判断多重条件的时候,用
()
将其括起来。
表子查询
返回的是多行多列
常见的操作符是IN
案例:
- 查询与 “鹿杖客” , “宋远桥” 的职位和薪资相同的员工信息
为什么用in,如下
# 查询与 "鹿杖客" , "宋远桥" 的职位和薪资相同的员工信息
select * from emp where (job,salary) in (select job,salary from emp where name in ('鹿杖客','宋远桥'));
# 查询与 "鹿杖客" , "宋远桥" 的职位和薪资相同的员工信息
select * from emp where (job,salary) = (select job,salary from emp where name in ('鹿杖客','宋远桥')); // 报错
因为表查询返回的不只一行。
工信息
select * from emp where entrydate >= (select entrydate
from emp where name=‘方东白’);
列子查询
[外链图片转存中...(img-kEGHfTJV-1692278713835)]
返回的是一列或者多行,因此,进行操作符进行操作
```sql
select * from emp where dept_id in (select id from dept where name='市场部' or name='销售部');
例子:
查询比 财务部 所有人工资都高的员工信息
- 查询财务部所有员工的工资
- 查询比财务部所有员工工资高的人
行子查询
案例:
查询与 “张无忌” 的薪资及直属领导相同的员工信息
- 查询张无忌的薪资及其直属领导
- 查询与 “张无忌” 的薪资及直属领导相同的员工信息 ;
select * from emp where (salary,managerid)=(select salary,managerid from emp where name = '张无忌');
注意:
- 判断多重条件的时候,用
()
将其括起来。
表子查询
返回的是多行多列
常见的操作符是IN
案例:
- 查询与 “鹿杖客” , “宋远桥” 的职位和薪资相同的员工信息
为什么用in,如下
# 查询与 "鹿杖客" , "宋远桥" 的职位和薪资相同的员工信息
select * from emp where (job,salary) in (select job,salary from emp where name in ('鹿杖客','宋远桥'));
# 查询与 "鹿杖客" , "宋远桥" 的职位和薪资相同的员工信息
select * from emp where (job,salary) = (select job,salary from emp where name in ('鹿杖客','宋远桥')); // 报错
因为表查询返回的不只一行。
到这为止,SQL语法基本上介绍完了🧐🧐🧐,主要包括DDL,DQL,DCL,DML。后续将会更新mysql中高级文档教程