MySQL基础

CMD启动MySQL

第一步：win+R，输入cmd，打开cmd窗口；
第二步：通过输入“net start mysql”命令，启动mysql服务；
第三步：通过DOS命令进入到mysql安装目录的bin目录下；
第四步：在命令行输入“mysql -u数据库用户名 -p密码”，然后回车。root是mysql默认用户名，如果设置了密码可以在-p后直接输入，如果没有设置，则直接回车即可。

SQL语句

SQL通用语法

1. SQL语句可以单行或多行书写，以分号结尾。
2. SQL语句可以使用空格/缩进来增强语句的可读性。
3. MySQL数据库的SQL语句不区分大小写，关键字建议使用大写。
4. 注释：
   • 当行注释：–注释内容或#注释内容（MySQL特有）
   • 多行注释：/注释内容/

SQL分类

1. DDL数据定义语言，用来定义数据库对象（数据库，表，字段）。
2. DML数据操作语言，用来对数据库表中的数据进行增删改。
3. DQL数据查询语言，用来查询数据库中表的记录。
4. DCL数据控制语言，用来创建数据库用户、控制数据库的访问权限。

DDL

查询所有数据库	SHOW DATABASES;

查询当前数据库	SELECT DATABASE();

创建	CREATE DATABASE [ IF NOT EXISTS ] 数据库名 [ DEFAULT CHARSET 字符集 ] [ COLLATE 排序规则 ];

删除	DROP DATABASE [ IF EXISTS ] 数据库名;

使用	USE 数据库名；

查询当前数据库所有表	SHOW TABLES;
查询表结构	DESC 表名;
查询指定表的建表语句 SHOW CREATE TABLE 表名;
创建 
CREATE TABLE 表名(
	字段1 字段1类型[COMMENT 字段1注释],
	字段n 字段n类型[COMMENT 字段n注释],
)[COMMENT 表注释];

添加字段
ALTER TABLE 表名 ADD 段名 类型(长度) [COMMENT 注释] [约束];
修改字段类型
ALTER TABLE 表名 MODIFY 段名 类型(长度) [COMMENT 注释] [约束];
修改字段名和字段类型
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型(长度) [COMMENT 注释] [约束];
删除字段
ALTER TABLE 表名 DROP 字段名;
修改表名
ALTER TABLE 表名 RENAME TO 新表名;
删除表
DROP TABLE [ IF EXISTS ] 表名;
删除指定表，并重新创建该表
TRUNCATE TABLE 表名;

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,...);

修改数据
UPDATE 表名 SET 字段名1 = 值1,字段名2 = 值2,... [WHERE 条件];

删除数据
DELETE FROM 表名 [ WHERE 条件 ];

DQL

SELECT
	字段列表
FROM
	表名列表
WHERE
	条件列表
GROUP BY
	分组字段列表
HAVING
	分组后条件列表
ORDER BY
	排序字段列表
LIMIT
	分页参数

DQL-基本查询

1.查询多个字段
SELECT 字段1,字段2,字段3,... FROM 表名;
SELECT * FROM 表名;
2.设置别名
SELECT 字段1 [AS 别名1],字段2 [AS 别名2],... FROM 表名;
3.去除重复记录
SELECT DISTINCT 字段列表 FROM 表名;

DQL-条件查询

SELECT 字段列表 FROM 表名 WHERE 条件列表;

SELECT 字段列表 FROM 表名 LIMIT 起始索引,查询记录数;
SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序规则，字段2 排序规则;
注意还用分组和聚合函数没写

DCL

1.查询用户
USE mysql;
SELECT * FROM user;
2.创建用户
CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
CREATE USER '用户名'@'%' IDENTIFIED BY '密码';
3.修改用户密码
ALTER USE '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码';
4.删除用户
DROP USER '用户名'@'主机名';

1.查询权限
SHOW GRANTS FOR '用户名'@'主机名';
2.授予权限
GRANTS 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';
3.撤销权限
REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';

函数

字符串函数

-- concat 字符串拼接
select concat('Hello','MySQL');
-- lower 转换为小写
select lower('HELLO');
-- upper 转换为大写
select upper('hello');
-- lpad	从左边填充-到五位
select lpad("01",5,'-');
-- rpad	从右边填充-到五位
select rpad("01",5,'-');
-- trim 去除两边空格
select trim(' Hello MySQL ');
-- substring 字符串截取(从第1截取5位)
select substring('Hello MySQL',1,5);
update employee set workno = lpad(workno,5,'0');

数值函数

-- ceil 向上取整结果为2
select ceil(1.1);
-- floor 向下取整结果为1
select floor(1.9);
-- mod 求模运算 5%4
select mod(5,4);
-- rand 求随机输0到1之间
select rand();
-- round 四舍五入保留2位
select round(2.345,2);
-- 随机生成一个六位的验证码
select lpad(round(rand()*1000000,0),6,'0');

日期函数

-- curdate() 当前日期
select curdate();
-- curtime() 当前时间
select curtime();
-- now() 当前日期+时间
select now();
-- YEAR, MONTH, DAY 年月日
select YEAR(now());
select MONTH(now());
select DAY(now());
-- date_add 往后70年
select date_add(now(),INTERVAL 70 YEAR);
-- datediff 两个时间的差异的天数
select datediff('2023-02-28','2023-02-27');
-- 查询所有员工的入职天数，并根据入职天数倒序排序。
select name,datediff(curdate(),entrydate) entrydays from employee order by entrydays  desc;

流程函数

-- if 第一个值为true返回Ok否则Error
select if(false,'Ok','Error');
-- ifnull 如果第一个值不为null返回第一个，如果为null返回第二个
select ifnull('Ok','Default');
select ifnull('','Default');
select ifnull(null,'Default');
-- case when then else end
select name, (case workaddress when '北京' then '一线城市' when '上海' then '一线城市' else '二线城市' end) '工作地址'
from employee
-- 统计班级各个学员的成绩，展示规则如下：
-- >= 85,优秀
-- >= 60,及格
-- 否则，不及格
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;

约束

1. 非空约束：NOT NULL
2. 唯一约束：UNIQUE
3. 主键约束：PRIMARY KEY（自增：AUTO_INCREMENT）
4. 默认约束：DEFAULT
5. 检查约束：CHECK
6. 外键约束：FOREIGN KEY

create table user(
    -- 主键 自增
	id int primary key auto_increment comment'主键',
    -- 不为空，唯一
	name varchar(10) not null unique comment '姓名',
	age int  comment '年龄',
    -- 在0到120之间
	check (age > 0 && age <= 120),
    -- 默认值为1
	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 外键名称;
删除/更新行为(父表删除/更新子表跟着删除/更新)
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY(外键字段名) REFERENCES 主表(主表列名) ON UPDATE CASCADE ON DELETE CASCADE;

连接查询

隐式内连接

SELECT 字段列表 FROM 表1,表2 WHERE 条件...;

显示内连接

SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 连接条件...;
表1+表2的关联数据

显示外连接

左外连接（表1的全部数据+表2的关联数据）
SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件...;
右外连接（表2的全部数据+表1的关联数据）
SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件...;

自连接

可内连接也可外连接
SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件...;

联合查询-union，union all

对于union查询，就是把多次查询的结果合并起来，形成一个新的查询结果集。
SELECT 字段列表 FROM 表A ...
UNION[ALL]
SELECT 字段列表 FROM 表B ...;
对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致。
union all 会将全部的数据直接合并在一起，union 会对合并之后的数据去重。

子查询

SQL语句中嵌套SELECT语句，称为嵌套查询，又称子查询。
SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2);
子查询外部的语句可以是INSERT/UPDATE/DELETE/SELECT的任何一个。

根据子查询结果不同，分为
标量子查询（子查询结果为单个值）
列子查询（子查询结果为一列）

IN（满足集合任意一个条件）、 NOT IN（不在集合范围内）、ANY（子查询返回列表中，有任意一个满足即可）、SOME（与ANY等同）、ALL（子查询返回列表中的所有值都必须满足）

行子查询（子查询结果为一行）

SELECT * FROM EMP WHERE (salary,managerid) = (SELECT salary,managerid FROM EMP WHERE name = '张三');

表子查询（子查询结果为多行多列）

查询与张三和李四的职位和薪资相同的员工信息
SELECT * FROM emp where (job,salary) in (SELECT job,salary from emp where name in('张三','李四'));

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

事务

操作

select @@autocommit;
set @@autocommit = 0; -- 设置为手动提交

select * from account where name = '张三';

update account set money = money - 1000 where name = '张三';

程序报错....

update account set money = money + 1000 where name = '李四';

commit; -- 提交事务
rollback; -- 回滚事务

开启事务 START TRANSACTION 或 BEGIN
start transaction;

select * from account where name = '张三';

update account set money = money - 1000 where name = '张三';

程序报错....

update account set money = money + 1000 where name = '李四';

commit;
rollback;

事务四大特性

原子性、一致性、隔离性、持久性

并发事务问题

脏读、不可重复读、幻读

事务隔离级别

未提交读、提交读（Oracle默认解决了脏读）、可重复读（MySQL默认解决脏读、不可重复读）、序列号（全部解决）

-- 查看事务隔离级别
SELECT @@TRANSACTION_ISOLATION;
-- 设置事务隔离级别
SET [SESSION|GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE RED SERIALIZABLE}