MySQL(常用命令)
mysql安装路径:https://editor.csdn.net/md/?articleId=131282782
sqlyog使用:
- https://blog.csdn.net/weixin_44689966/article/details/120352415
- https://blog.csdn.net/weixin_52203618/article/details/128198071
连接数据库
MySQL服务器启动完毕后,然后再使用如下指令,来连接MySQL服务器:
mysql -u用户名 -p密码 [-h数据库服务器的IP地址 -P端口号]
-h 参数不加,默认连接的是本地 127.0.0.1 的MySQL服务器
-P 参数不加,默认连接的端口号是 3306
关系型数据库(RDBMS)
概念:建立在关系模型基础上,由多张相互连接的二维表组成的数据库。
DDl(数据定义语言)
查询所有数据库:
show databases;
查询当前数据库:
select database();
创建数据库
create database [ if not exists ] 数据库名;
-- 数据库不存在,则创建该数据库;如果存在则不创建
create database if not extists itcast;
使用数据库
use 数据库名 ;
删除数据库
drop database [ if exists ] 数据库名 ;
如果删除一个不存在的数据库,将会报错。
可以加上参数 if exists ,如果数据库存在,再执行删除,否则不执行删除。
创建表
create table 表名(
字段1 字段1类型 [约束] [comment 字段1注释 ],
字段2 字段2类型 [约束] [comment 字段2注释 ],
......
字段n 字段n类型 [约束] [comment 字段n注释 ]
) [ comment 表注释 ] ;
注意: [ ] 中的内容为可选参数; 最后一个字段后面没有逗号
约束
约束 | 描述 | 关键字 |
---|---|---|
非空约束 | 限制该字段值不能为null | not null |
唯一约束 | 保证字段的所有数据都是唯一、不重复的 | unique |
主键约束 | 主键是一行数据的唯一标识,要求非空且唯一 | primary key |
默认约束 | 保存数据时,如果未指定该字段值,则采用默认值 | default |
外键约束 | 让两张表的数据建立连接,保证数据的一致性和完整性 | foreign key |
主键自增:auto_increment
- 每次插入新的行记录时,数据库自动生成id字段(主键)下的值
- 具有auto_increment的数据列是一个正数序列开始增长(从1开始自增)
注意:约束是作用于表中字段上的,可以在创建表/修改表的时候添加约束。
- 案例
create table tb_user (
id int primary key auto_increment comment 'ID,唯一标识', #主键自动增长
username varchar(20) not null unique comment '用户名',
name varchar(10) not null comment '姓名',
age int comment '年龄',
gender char(1) default '男' comment '性别'
) comment '用户表';
查询当前数据库所有表
show tables;
查看指定表结构
desc 表名 ;#可以查看指定表的字段、字段的类型、是否可以为NULL、是否存在默认值等信息
查询指定表的建表语句
show create table 表名 ;
添加字段
alter table 表名 add 字段名 类型(长度) [comment 注释] [约束];
- 案例: 为tb_emp表添加字段qq,字段类型为 varchar(11)
alter table tb_emp add qq varchar(11) comment 'QQ号码';
修改数据类型
alter table 表名 modify 字段名 新数据类型(长度);
alter table 表名 change 旧字段名 新字段名 类型(长度) [comment 注释] [约束];
- 案例:修改qq字段的字段类型,将其长度由11修改为13
alter table tb_emp modify qq varchar(13) comment 'QQ号码';
- 案例:修改qq字段名为 qq_num,字段类型varchar(13)
alter table tb_emp change qq qq_num varchar(13) comment 'QQ号码';
删除字段
alter table 表名 drop 字段名;
案例:删除tb_emp表中的qq_num字段
alter table tb_emp drop qq_num;
修改表名
rename table 表名 to 新表名;
案例:将当前的tb_emp表的表名修改为emp
rename table tb_emp to emp;
删除表语法:
drop table [ if exists ] 表名;
if exists :只有表名存在时才会删除该表,表名不存在,则不执行删除操作(如果不加该参数项,删除一张不存在的表,执行将会报错)。
- 案例:如果tb_emp表存在,则删除tb_emp表
drop table if exists tb_emp; -- 在删除表时,表中的全部数据也会被删除。
DML(数据操作语言)
insert语法:
-
向指定字段添加数据
insert into 表名 (字段名1, 字段名2) values (值1, 值2);
-
全部字段添加数据
insert into 表名 values (值1, 值2, ...);
-
批量添加数据(指定字段)
insert into 表名 (字段名1, 字段名2) values (值1, 值2), (值1, 值2);
-
批量添加数据(全部字段)
insert into 表名 values (值1, 值2, ...), (值1, 值2, ...);
-
案例1:向tb_emp表的username、name、gender字段插入数据
-- 因为设计表时create_time, update_time两个字段不能为NULL,所以也做为要插入的字段
insert into tb_emp(username, name, gender, create_time, update_time)
values ('wuji', '张无忌', 1, now(), now());
- 案例2:向tb_emp表的所有字段插入数据
insert into tb_emp(id, username, password, name, gender, image, job, entrydate, create_time, update_time)
values (null, 'zhirou', '123', '周芷若', 2, '1.jpg', 1, '2010-01-01', now(), now());
- 案例3:批量向tb_emp表的username、name、gender字段插入数据
insert into tb_emp(username, name, gender, create_time, update_time)
values ('weifuwang', '韦一笑', 1, now(), now()),
('fengzi', '张三疯', 1, now(), now());
Insert操作的注意事项:
- 插入数据时,指定的字段顺序需要与值的顺序是一一对应的。
- 字符串和日期型数据应该包含在引号中。
- 插入的数据大小,应该在字段的规定范围内。
外键约束:(https://blog.csdn.net/Jenny_xuexi/article/details/127154039)
-- 创建表时指定
create table 表名(
字段名 数据类型,
...
[constraint] [外键名称] foreign key (外键字段名) references 主表 (主表列名)
);
-- 建完表后,添加外键
alter table 表名 add constraint 外键名称 foreign key(外键字段名) references 主表(主表列名);
update语法:
update 表名 set 字段名1 = 值1 , 字段名2 = 值2 , .... [where 条件] ;
- 案例1:将tb_emp表中id为1的员工,姓名name字段更新为’张三’
update tb_emp set name='张三',update_time=now() where id=1;
- 案例2:将tb_emp表的所有员工入职日期更新为’2010-01-01’
update tb_emp set entrydate='2010-01-01',update_time=now();
注意事项:
- 修改语句的条件可以有,也可以没有,如果没有条件,则会修改整张表的所有数据。
- 在修改数据时,一般需要同时修改公共字段update_time,将其修改为当前操作时间。
delete语法:
delete from 表名 [where 条件] ;
案例1:删除tb_emp表中id为1的员工
delete from tb_emp where id = 1;
案例2:删除tb_emp表中所有员工
delete from tb_emp;
注意事项:
• DELETE 语句的条件可以有,也可以没有,如果没有条件,则会删除整张表的所有数据。
• DELETE 语句不能删除某一个字段的值(可以使用UPDATE,将该字段值置为NULL即可)。
• 当进行删除全部数据操作时,会提示询问是否确认删除所有数据,直接点击Execute即可。
DQL(数据查询语言)
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 表名;
条件查询
常用的比较运算符如下:
select 字段列表 from 表名 where 条件列表 ; -- 条件列表:意味着可以有多个条件
比较运算符 | 功能 |
---|---|
> | 大于 |
>= | 大于等于 |
< | 小于 |
<= | 小于等于 |
= | 等于 |
<> 或 != | 不等于 |
between … and … | 在某个范围之内(含最小、最大值) |
in(…) | 在in之后的列表中的值,多选一 |
like 占位符 | 模糊匹配(_匹配单个字符, %匹配任意个字符) |
is null | 是null |
常用的逻辑运算符如下:
逻辑运算符 | 功能 |
---|---|
and 或 && | 并且 (多个条件同时成立) |
or 或 || | 或者 (多个条件任意一个成立) |
not 或 ! | 非 , 不是 |
聚合函数
select 聚合函数(字段列表) from 表名 ;
注意 : 聚合函数会忽略空值,对NULL值不作为统计。
常用聚合函数:
函数 | 功能 |
---|---|
count | 统计数量 |
max | 最大值 |
min | 最小值 |
avg | 平均值 |
sum | 求和 |
count :按照列去统计有多少行数据。
- 在根据指定的列统计的时候,如果这一列中有null的行,该行不会被统计在其中。
sum :计算指定列的数值和,如果不是数值类型,那么计算结果为0
max :计算指定列的最大值
min :计算指定列的最小值
avg :计算指定列的平均值
分组查询
select 字段列表 from 表名 [where 条件] group by 分组字段名 [having 分组后过滤条件];
注意事项:
分组其实就是按列进行分类(指定列下相同的数据归为一类),然后可以对分类完的数据进行合并计算。
分组查询通常会使用聚合函数进行计算。
注意事项:
• 分组之后,查询的字段一般为聚合函数和分组字段,查询其他字段无任何意义
• 执行顺序:where > 聚合函数 > having
where与having区别(面试题)
- 执行时机不同:where是分组之前进行过滤,不满足where条件,不参与分组;而having是分组之后对结果进行过滤。
- 判断条件不同:where不能对聚合函数进行判断,而having可以。
排序查询
select 字段列表
from 表名
[where 条件列表]
[group by 分组字段 ]
order by 字段1 排序方式1 , 字段2 排序方式2 … ;
-
排序方式:
- ASC :升序(默认值)
- DESC:降序
分页查询
select 字段列表 from 表名 limit 起始索引, 查询记录数 ;
特殊案例
if(表达式, tvalue, fvalue) :当表达式为true时,取值tvalue;当表达式为false时,取值fvalue
case 表达式 when 值1 then 结果1 [when 值2 then 结果2 …] [else result] end
- 员工职位统计:
-- case 表达式 when 值1 then 结果1 when 值2 then 结果2 ... else result end
select (case job
when 1 then '班主任'
when 2 then '讲师'
when 3 then '学工主管'
when 4 then '教研主管'
else '未分配职位'
end) AS 职位 ,
count(*) AS 人数
from tb_emp
group by job;
内连接
隐式内连接语法:
select 字段列表 from 表1 , 表2 where 条件 ... ;
显式内连接语法:
select 字段列表 from 表1 [ inner ] join 表2 on 连接条件 ... ;
外连接
左外连接语法结构:
select 字段列表 from 表1 left [ outer ] join 表2 on 连接条件 ... ;
左外连接相当于查询表1(左表)的所有数据,当然也包含表1和表2交集部分的数据。
右外连接语法结构:
select 字段列表 from 表1 right [ outer ] join 表2 on 连接条件 ... ;
右外连接相当于查询表2(右表)的所有数据,当然也包含表1和表2交集部分的数据。
标量子查询
- 案例1:查询"教研部"的所有员工信息
可以将需求分解为两步:
- 查询 “教研部” 部门ID
- 根据 “教研部” 部门ID,查询员工信息
-- 1.查询"教研部"部门ID
select id from tb_dept where name = '教研部'; #查询结果:2
-- 2.根据"教研部"部门ID, 查询员工信息
select * from tb_emp where dept_id = 2;
-- 合并出上两条SQL语句
select * from tb_emp where dept_id = (select id from tb_dept where name = '教研部');
列子查询
操作符 | 描述 |
---|---|
IN | 在指定的集合范围之内,多选一 |
NOT IN | 不在指定的集合范围之内 |
案例:查询"教研部"和"咨询部"的所有员工信息
分解为以下两步:
- 查询 “销售部” 和 “市场部” 的部门ID
- 根据部门ID, 查询员工信息
-- 1.查询"销售部"和"市场部"的部门ID
select id from tb_dept where name = '教研部' or name = '咨询部'; #查询结果:3,2
-- 2.根据部门ID, 查询员工信息
select * from tb_emp where dept_id in (3,2);
-- 合并以上两条SQL语句
select * from tb_emp where dept_id in (select id from tb_dept where name = '教研部' or name = '咨询部');
行子查询
常用的操作符:= 、<> 、IN 、NOT IN
案例:查询与"韦一笑"的入职日期及职位都相同的员工信息
可以拆解为两步进行:
- 查询 “韦一笑” 的入职日期 及 职位
- 查询与"韦一笑"的入职日期及职位相同的员工信息
-- 查询"韦一笑"的入职日期 及 职位
select entrydate , job from tb_emp where name = '韦一笑'; #查询结果: 2007-01-01 , 2
-- 查询与"韦一笑"的入职日期及职位相同的员工信息
select * from tb_emp where (entrydate,job) = ('2007-01-01',2);
-- 合并以上两条SQL语句
select * from tb_emp where (entrydate,job) = (select entrydate , job from tb_emp where name = '韦一笑');
表子查询
子查询返回的结果是多行多列,常作为临时表,这种子查询称为表子查询。
案例:查询入职日期是 “2006-01-01” 之后的员工信息 , 及其部门信息
分解为两步执行:
- 查询入职日期是 “2006-01-01” 之后的员工信息
- 基于查询到的员工信息,在查询对应的部门信息
select * from emp where entrydate > '2006-01-01';
select e.*, d.* from (select * from emp where entrydate > '2006-01-01') e left join dept d on e.dept_id = d.id ;
DCL(数据控制语言)
登录数据库:
mysql -u root -p
查看本机MySQL中所有的用户
select user ,host from mysql.user;
查看所有数据库
show databases;
选择数据库:
user 数据库名
显示数据库所有表格
show tables;
显示表的列信息:
show columns from 表名:也可以用: describe 表名;
#查看mysql8.0生产的密码
grep “A temporary password” /arog/mysqld.log
#查看密码策略
show global variables like %validate password%% .
#验证策略
0–>low 1–>MEDIUM 2–>strongset global validate password.policy=0;
#密码最小长度
set global validate_password.length=3;
#修改密码
ALTER userroot@localhost IDENTIFIED BY123456;
事务
事务作用:保证在一个事务中多次操作数据库表中数据时,要么全都成功,要么全都失败。
MYSQL中有两种方式进行事务的操作:
- 自动提交事务:即执行一条sql语句提交一次事务。(默认MySQL的事务是自动提交)
- 手动提交事务:先开启,再提交
事务操作有关的SQL语句:
SQL语句 | 描述 |
---|---|
start transaction; / begin ; | 开启手动控制事务 |
commit; | 提交事务 |
rollback; | 回滚事务 |
手动提交事务使用步骤:
- 第1种情况:开启事务 => 执行SQL语句 => 成功 => 提交事务
- 第2种情况:开启事务 => 执行SQL语句 => 失败 => 回滚事务
使用事务控制删除部门和删除该部门下的员工的操作:
-- 开启事务
start transaction ;
-- 删除学工部
delete from tb_dept where id = 1;
-- 删除学工部的员工
delete from tb_emp where dept_id = 1;
- 上述的这组SQL语句,如果如果执行成功,则提交事务
-- 提交事务 (成功时执行)
commit ;
- 上述的这组SQL语句,如果如果执行失败,则回滚事务
-- 回滚事务 (出错时执行)
rollback ;
四大特性
- 原子性(Atomicity):事务是不可分割的最小单元,要么全部成功,要么全部失败。
- 一致性(Consistency):事务完成时,必须使所有的数据都保持一致状态。
- 隔离性(Isolation):数据库系统提供的隔离机制,保证事务在不受外部并发操作影响的独立环境下运行。
- 持久性(Durability):事务一旦提交或回滚,它对数据库中的数据的改变就是永久的。
事务的四大特性简称为:ACID
-
原子性(Atomicity) :原子性是指事务包装的一组sql是一个不可分割的工作单元,事务中的操作要么全部成功,要么全部失败。
-
一致性(Consistency):一个事务完成之后数据都必须处于一致性状态。
如果事务成功的完成,那么数据库的所有变化将生效。
如果事务执行出现错误,那么数据库的所有变化将会被回滚(撤销),返回到原始状态。
- 隔离性(Isolation):多个用户并发的访问数据库时,一个用户的事务不能被其他用户的事务干扰,多个并发的事务之间要相互隔离。
一个事务的成功或者失败对于其他的事务是没有影响。
- 持久性(Durability):一个事务一旦被提交或回滚,它对数据库的改变将是永久性的,哪怕数据库发生异常,重启之后数据亦然存在。
索引
优点:
- 提高数据查询的效率,降低数据库的IO成本。
- 通过索引列对数据进行排序,降低数据排序的成本,降低CPU消耗。
缺点:
- 索引会占用存储空间。
- 索引大大提高了查询效率,同时却也降低了insert、update、delete的效率。
创建索引
create [ unique ] index 索引名 on 表名 (字段名,... ) ;
案例:为tb_emp表的name字段建立一个索引
create index idx_emp_name on tb_emp(name);
注意事项:
- 在创建表时,如果添加了主键和唯一约束,就会默认创建:主键索引、唯一约束
查看索引
show index from 表名;
案例:查询 tb_emp 表的索引信息
show index from tb_emp;
删除索引
drop index 索引名 on 表名;
案例:删除 tb_emp 表中name字段的索引
drop index idx_emp_name on tb_emp;
注意事项:
主键字段,在建表时,会自动创建主键索引
添加唯一约束时,数据库实际上会添加唯一索引
- 持久性(Durability):一个事务一旦被提交或回滚,它对数据库的改变将是永久性的,哪怕数据库发生异常,重启之后数据亦然存在。
索引
优点:
- 提高数据查询的效率,降低数据库的IO成本。
- 通过索引列对数据进行排序,降低数据排序的成本,降低CPU消耗。
缺点:
- 索引会占用存储空间。
- 索引大大提高了查询效率,同时却也降低了insert、update、delete的效率。
创建索引
create [ unique ] index 索引名 on 表名 (字段名,... ) ;
案例:为tb_emp表的name字段建立一个索引
create index idx_emp_name on tb_emp(name);
注意事项:
- 在创建表时,如果添加了主键和唯一约束,就会默认创建:主键索引、唯一约束
查看索引
show index from 表名;
案例:查询 tb_emp 表的索引信息
show index from tb_emp;
删除索引
drop index 索引名 on 表名;
案例:删除 tb_emp 表中name字段的索引
drop index idx_emp_name on tb_emp;
注意事项:
主键字段,在建表时,会自动创建主键索引
添加唯一约束时,数据库实际上会添加唯一索引