说明

学习MySQL时的一些笔记

基础

启动与停止

// 启动
net start mysql80
// 停止
net stop mysql80

连接

1	mysql [-h 127.0.0.1] [-p 3306] -u root -p

SQL

DDL

查询

show databases;      //查询所有数据库
select database();   //查询当前数据库
show tables;         //查询当前数据库所有表
desc 表名;      //查询表结构
show create table 表名;  //查询指定表的建表语句

创建

create database [IF NOT EXISTS] 数据库名 [DEFAULT CHARSET 字符集] [COLLATE 排序规则];
// 字符集可以是utf8，utf8mb4
create table 表名(
 字段1 字段1类型 [COMMENT '字段1注释'],
 字段2 字段2类型 [COMMENT '字段2注释'],
 字段3 字段3类型 [COMMENT '字段3注释'],
 ······
 字段n 字段n类型 [COMMENT '字段n注释'],
) [COMMENT '表注释'];
// 表创建

删除

1
2
3

drop database [IF EXISTS] 数据库名;
drop table [IF EXISTS] 表名;
truncate table 表名;                                 //删除指定表，并重新创建该表(相当于格式化)

使用

use 数据库名;

修改

alter table 表名 add 字段名 类型(长度) [COMMENT 注释] [约束];     //添加字段
alter table 表名 modify 字段名 新数据类型(长度)         //修改数据类型
alter table 表名 change 旧字段名 新字段名 类型(长度) [COMMENT '注释'] [约束];    
alter table 表名 drop 字段名;                                                     //删除字段
alter table 表名 rename to 新表名;                                                //修改表名

DML

添加数据

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···);                                  //批量添加数据

修改数据

1	update 表名 set 字段名1=值1,字段名2=值2,···[WHERE 条件];

删除数据

1	delete from 表名 [WHERE 条件];

DQL

//编写顺序               //执行顺序(可以使用别名来验证)
select      
 字段列表             4
from
 表名列表             1
where
 条件列表             2
group by
 分组字段列表         3
having
 分组后条件查询       
order by
 排序字段列表         5
limit
 分页参数             6

基本查询
条件查询（WHERE）
聚合函数（count，max，min，avg，sum）
分组查询（GROUP BY）
排序查询（ORDER BY）
分页查询（LIMIT）

基本查询

select 字段1,字段2,字段3··· from 表名;
select * from 表名;
select 字段1 [AS 别名1],字段2 [AS 别名2]··· from 表名;
select distinct 字段列表 from 表名;                         //去除重复记录

条件查询

1	select 字段列表 from 表名 where 条件列表;

聚合函数（count，max，min，avg，sum）

1	select 聚合函数(字段列表) from 表名;

分组查询（GROUP BY）

1	select 字段列表 from 表名 [WHERE 条件] group by 分组字段名 [HAVING 分组后过滤条件];

排序查询（ORDER BY）

1 2	select 字段列表 from 表名 order by 字段1 排序方式, 字段2 排序方式 // 排序方式:asc(升序),desc(降序)--默认升序

分页查询（limit）

1 2	select 字段列表 from 表名 limit 起始索引,查询记录数 // 起始索引从0开始，起始索引=（查询页码-1）* 每页显示记录数

DCL

管理用户

查询用户

1 2	use mysql; select * from user;

创建用户

1 2	create user '用户名'@'主机名' identified by '密码' // 当主机名是'%'时，表示允许任意主机访问数据库

修改用户密码

1	alter user '用户名'@'主机名' identified with mysql_native_password by '新密码';

删除用户

1	drop user '用户名'@'主机名';

权限控制

查询权限

1	show grants for '用户名'@'主机名';

授予权限

1 2	grant 权限列表 on 数据库名.表名 to '用户名'@'主机名'; // grant all on itcast.* to 'qianqianzyk'@'%';

撤销权限

1	revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';

函数

字符串函数

1
2
3

select 函数(参数);
// 注意：使用substring()截取字符串时，第一个字符的索引是1
// update emp set workno = lpad(workno,5,'0');

数值函数

1 2	// 生成一个六位随机数 select lpad(round(rand()*1000000,0),6,'0');

日期函数

1 2	select date_add(now(),INTERVAL 70 DAY); select name,datediff(curdate(),entrydate) as 'entrydays' from emp order by entrydays desc;

流程函数

select if(false,'OK','Error');
select ifnull('OK','Default');          select ifnull(null,'Default');
select name,(case workaddress when '北京' then '一线城市' when '上海' then '一线城市' else '二线城市' end) as '工作地址 ' from emp;
select name,if(workaddress in('上海','北京'),'一线城市','二线城市') from emp;

约束

概述

可以保证数据库中数据的正确，有效性，完整性和一致性

示例

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 emp add constraint fk_emp_dept_id foreign key(dept_id) references dept(id);

// 删除外键
alter table 表名 drop foreign key 外键名称;

1	alter table 表名 add constraint 外键名称 foreign key(外键字段名) references 主表(主表列名) on update cascade on delete cascade;

在实际开发中一般会不使用外键

性能考虑： 外键约束会增加数据库系统的负担，因为它们需要确保引用完整性。当数据库表的数据量很大时，外键约束可能会导致性能下降。在高负载环境下，为了提高数据库的响应速度，可能会牺牲外键约束。

应用层控制： 有时候，开发人员更喜欢在应用层面控制数据的完整性，而不是依赖数据库层面的外键约束。这样做可以更灵活地处理数据逻辑，并且有助于减少数据库之间的耦合度。

数据迁移和维护： 使用外键约束可能会增加数据迁移和维护的复杂性。在修改表结构或者进行数据迁移时，外键约束可能会成为限制因素，增加了维护的难度。

应用程序逻辑的复杂性： 外键约束会强制执行引用完整性，这可能会使得应用程序逻辑更加复杂。有时，为了简化应用程序的开发和维护，开发人员选择在应用层面进行逻辑控制，而不是依赖数据库的外键约束。

多表查询（一对多，多对多，一对一）

查询（消除笛卡尔积）

1	select * from emp , dept where emp.dept_id = dept.id;

内连接

隐式内连接

1 2	select 字段列表 from 表1,表2 where 条件---; // select e.name , d.name from emp e , dept d where e.dept_id = d.id;

显式内连接

1 2	select 字段列表 from 表1 [INNER] join 表2 on 连接条件---; // select e.name , d.name from emp e inner join dept d on e.dept_id = d.id;

显式内连接性能比隐式内连接高，有更快的执行速度，在3张或多张表连接时较为明显；同时代码更直观清晰，容易维护

外连接

左外连接

1 2	select 字段列表 from 表1 left [OUTER] join 表2 on 条件---; // select e.* , d.name from emp e left join dept d on e.dept_id = d.id;

右外连接

1 2	select 字段列表 from 表1 right [OUTER] join 表2 on 条件---; // select d.* , e.* from emp e right join dept d on e.dept_id = d.id;

左外连接指保留左边所有数据，如果有和右边对应的就把右边的数据也带上。举个例子，一张员工表，一张部门表。一个员工对应一个部门，但是如果此时员工还没有部门，不用左外连接的话，就无法查出这名员工的数据。在实际开发中，左外连接用的比较多。

自连接

1
2
3

select 字段列表 from 表A 别名A join 表A 别名B on 条件---;
// select a.name , b.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

select 字段列表 from 表A---
union[ALL]
select 字段列表 from 表B---;
// union 去重，union all 不去重

对于union查询，就是把多次查询的结果合并起来，形成一个新的查询结果集。对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致。那么为什么不用or呢？第一or去重，二，实际应尽量避免在where子句中使用or来连接条件。使用or可能会使索引失效，从而全表扫描。mysql是有优化器的，处于效率与成本考虑，遇到or条件，索引可能失效

子查询

1	select * from t1 where column1 = (select column1 from t2);

子查询外部的语句可以是INSERT / UPDATE/ DELETE/ SELECT的任何一个。

根据子查询结果不同，分为：

标量子查询（子查询结果为单个值）
列子查询（子查询结果为一列）
行子查询（子查询结果为一行）
表子查询（子查询结果为多行多列）

根据子查询位置，分为：WHERE之后、FROM之后、SELECT之后。

标量子查询

1 2	select * from emp where dept_id = (select id from dept where name = "销售部"); // 此时(select id from dept where name = "销售部")的结果为一个值

列子查询

1 2	select * from emp where salary > all( select salary from emp where dept_id = (select id from dept where name = '销售部')); // 查询比财务部所有人工资都高的员工消息

行子查询

1	select * from emp where (salary,managerid) = (select salary, managerid from emp where name = 'zhang');

表子查询

1
2

select * from emp where (job,salary) in (select job,salary from emp where name = 'lu' or name = 'song');
select e.*,d.* from (select * from emp where entrydate > '2006-01-01') e left join dept d on e.dept_id = d.id;