1、基础
1、初始操作
连接MySQL
mysql [-h 127.0.0.1] [-P 3306] -u root -p 参数: -h : MySQL服务所在的主机IP(可选) -P : MySQL服务端口号, 默认3306(可选) -u : MySQL数据库用户名 -p : MySQL数据库用户名对应的密码
启停MySQL
以管理员身份运行cmd,进入命令行执行如下指令:
net start mysql80 #启动。mysql80是安装MySQL时mysql系统服务名 Windows Service Name。 net stop mysql80 #停止
2、库、表操作
数据库:
show databases ; # 查询所有数据库 select database() ; # 查询当前数据库 create database [ if not exists ] 数据库名 [ default charset utf8mb4 ] [ collate 排序 规则 ] ; # 创建数据库 drop database [ 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 表注释 ] ; # 注释可选,最后一个字段后边没有逗号 ALTER TABLE 表名 ADD 字段名 类型 (长度) [ COMMENT 注释 ] [ 约束 ]; # 表修改-添加字段 ALTER TABLE 表名 MODIFY 字段名 新数据类型 (长度); # 修改指定字段的数据类型 ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型 (长度) [ COMMENT 注释 ] [ 约束 ]; # 修改指定字段名和字段类型 ALTER TABLE 表名 DROP 字段名; # 删除字段 ALTER TABLE 表名 RENAME TO 新表名; # 修改表名 DROP TABLE [ IF EXISTS ] 表名; # 删除表,包括表结构 TRUNCATE TABLE 表名; # 仅删除表数据,但是不删除表结构
3、表数据类型
数值类型
例如: 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
4、用户及权限管理
xshell、安装MySQL(Windows、Linux、docker)
查询用户
select * from mysql.user; # 查询用户,在MySQL中需要通过用户名@主机名的方式,来唯一标识一个用户。
其中 Host代表当前用户访问的主机, 如果为localhost, 仅代表只能够在当前本机访问,是不可以远程访问的。 User代表的是访问该数据库的用户名。在MySQL中需要通过Host和User来唯一标识一个用户。
CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码'; # 创建用户 ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码' ; # 修改用户密码 DROP USER '用户名'@'主机名' ; # 删除用户
用户权限
MySQL中定义了很多种权限,但是常用的就以下几种:
SHOW GRANTS FOR '用户名'@'主机名' ; # 查询用户权限 GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名'; # 授予用户权限 REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名'; # 撤销权限 # 多个权限之间,使用逗号分隔 # 授权时, 数据库名和表名可以使用 * 进行通配,代表所有。
2、常用SQL语句
1、增删改
INSERT INTO 表名 (字段名1, 字段名2, ...) VALUES (值1, 值2, ...); # 给指定字段添加数据 INSERT INTO 表名 VALUES (值1, 值2, ...); # 给全部字段添加数据 INSERT INTO 表名 (字段名1, 字段名2, ...) VALUES (值1, 值2, ...), (值1, 值2, ...), (值1, 值2, ...) ; # 批量添加数据 UPDATE 表名 SET 字段名1 = 值1 , 字段名2 = 值2 , .... [ WHERE 条件 ] ; # 修改数据 DELETE FROM 表名 [ WHERE 条件 ] ; # 删除数据
2、基本查询
SELECT [DISTINCT] # [DISTINCT]去重 字段列表 as 别名 FROM 表名列表 WHERE 条件列表 # where是分组之前进行过滤,不满足where条件,不参与分组;不能对聚合函数进行判断 GROUP BY 分组字段列表 HAVING 分组后条件列表 # having是分组之后对结果进行过滤,可以对聚合函数进行判断(此时聚合函数作用的对象是每一个单独的分组)。 ORDER BY 排序字段列表 [ASC/DESC] # ASC升序,是默认值,DESC降序。有了分组,就是在分组内排序 LIMIT 起始索引, 查询记录数; # 起始索引从0开始,记录数是指一共要查多少个数据。
条件中常用的的比较运算符
查询SQL执行顺序
3、常用函数
条件中常用的聚合函数
字符串函数:
数值函数:
日期函数:
流程函数:
流程函数示例:
select id, name, (case when math >= 85 then '优秀' when math >=60 then '及格' else '不及格' end ) '数学', (case when english >= 85 then '优秀' when english >=60 then '及格' else '不及格' end ) '英语', (case when chinese >= 85 then '优秀' when chinese >=60 then '及格' else '不及格' end ) '语文' from score;
4、多表查询
1、连接查询
内连接:两个集合交集部分数据。
# 查询每一个员工的姓名 , 及关联的部门的名称 (隐式内连接实现) select e.name, d.name from emp e join dept d on e.dept_id = d.id;
左外连接:
# 左外连接相当于查询表1(左表)的所有数据,当然也包含表1和表2交集部分的数据。 SELECT 字段列表 FROM 表1 LEFT [ OUTER ] JOIN 表2 ON 条件 ... ;
右外连接:左外连接和右外连接是可以相互替换的,只需要调整在连接查询时SQL中,表结构的先后顺 序就可以了。
# 右外连接相当于查询表2(右表)的所有数据,当然也包含表1和表2交集部分的数据。 SELECT 字段列表 FROM 表1 RIGHT [ OUTER ] JOIN 表2 ON 条件 ... ;
自连接:是自己连接自己,也就是把一张表连接查询多次。
# 一张员工表,表中包括基层员工和领导,表字段有:员工id、员工姓名、所属领导的员工id。这个查询我咋一看很陌生,可见我对sql语句不熟悉,努力吧,少年! select a.name '员工', b.name '领导' from emp a left join emp b on a.managerid = b.id;
在自连接查询中,必须要为表起别名,要不然我们不清楚所指定的条件、返回的字段,到底 是哪一张表的字段。
2、联合查询
-
对于union查询,就是把多次查询的数据合并起来,形成一个新的查询结果集;
-
对于联合查询的多张表的列数必须保持一致,字段类型也需要保持一致;
-
union all 会将全部的数据直接合并在一起,union 会对合并之后的数据去重。
# 将薪资低于 5000 的员工 , 和 年龄大于 50 岁的员工全部查询出来. select * from emp where salary < 5000 union all # 也可以使用or筛选条件 select * from emp where age > 50;
3、子查询
-
SQL语句中嵌套SELECT语句,称为嵌套查询,又称子查询。
-
子查询的位置,可以是
WHERE、FROM、SELECT
之后。
标量子查询:子查询结果为单个值,通常与比较运算符(= <> > >= < <= )结合。
# 根据 "销售部" 部门ID, 查询员工信息 select * from emp where dept_id = (select id from dept where name = '销售部');
列子查询:子查询结果为一列。常用的操作符:IN 、NOT IN 、 ANY 、SOME 、 ALL,除了 IN 之外,都会与比较运算符结合使用。
# 查询 "销售部" 和 "市场部" 的所有员工信息 select * from emp where dept_id in (select id from dept where name = '销售部' or name = '市场部'); # 查询比 财务部 所有人工资都高的员工信息 select * from emp where salary > all ( select salary from emp where dept_id = (select id from dept where name = '财务部') ); # 查询比研发部其中任意一人工资高的员工信息 select * from emp where salary > any ( select salary from emp where dept_id = (select id from dept where name = '研发部') );
行子查询:子查询返回的结果是一行(可以是多列)。常用的操作符:= 、<> 、IN 、NOT IN。
# 查询与 "张无忌" 的薪资及直属领导相同的员工信息 ; select * from emp where (salary,managerid) = (select salary, managerid from emp where name = '张无忌');
表子查询:子查询结果为多行多列。常用的操作符:IN。
# 查询与 "鹿杖客" , "宋远桥" 的职位和薪资相同的员工信息 select * from emp where (job,salary) in ( select job, salary from emp where name = '鹿杖客' or name = '宋远桥' );