1. 命令行模式MySQL

• 设置完环境变量后即可在命令行中通过密码进入mysql
• 设置环境变量的方法是将mysql安装目录下的bin目录的路径加到系统变量的path中
  之后通过命令mysql -u root -p来输入密码进入mysql

2. 创建表查询表的sql语句

• 显示当前数据库表：show databases;
• 创建一个数据库表：create database student;
• 创建时指定编码方式：create database acwing default charset utf8mb4;
• 删除数据库：drop database acwing;
• 使用某一个数据库：use acwing;
• 查看当前在哪一个数据库中：select database();
• 创建一个表(前提是进入某个数据库中)：

 create table user(
    id int comment '编号',
    name varchar(20) comment '姓名',
    birsthday date comment '生日',
    age tinyint unsigned comment '年龄'
    );

• 查看表的字段：desc user;
• 在表中添加字段：alter table user add nickname varchar(20) comment '小名';
• 修改字段名和字段类型：alter table user change nickname username varchar(50) comment '用户名';
• 删除表中的字段：alter table user drop username;
• 修改表名：alter table user rename to user3;
• 删除表：drop table user3;
• 在表中插入数据：insert into test(id, name, age) VALUES(1, 'zzw', 10); 插入多组值时用,分割并列的内容
• 修改某个字段的值：update test set name = 'yxc' where id = 1;
• 删除某个字段的值：delete from test where id = 1;
• 查询不重复的字段的值：select distinct id from test;
• 查询某一字段为空的所有值：select * from test where name is null;
• 范围查询：select * from test where age >= 10 and age <= 12; 或 select * from test where age between 10 and 12;
select * from test where age = 10 or age = 11 or age = 12; 或 select * from test where age in(10, 11, 12);
• 模糊查询：select * from test where name = '__'; %是匹配任意个字符
• 聚合函数：
  统计员工个数：select count(*) from test;
  统计平均年龄：select avg(age) from test;
  统计id是1的年龄之和的平均值：select avg(age) from test where id = 1;
• 分组查询：

where是在分组之前过滤的，having是在分组之后过滤的
where与having区别:
执行时机不同:where是分组之前进行过滤，不满足where条件，不参与分组;而having是分组之后对结果进行过滤。
判断条件不同:where不能对聚合函数进行判断，而having可以。

1. 根据性别分组，统计男性员工和女性员工的数量：select gender, count(*) from test group by gender;
2. 查询年龄小于45的员工，并根据工作地址分组，获取员工数量大于等于3的工作地址：select workaddress, count(*) from test where age < 45 group by workaddress having count(*) > 3; 先是对年龄<45的进行分组，分完组后对组内进行过滤。所以先是where group by 再是having
3. 排序查询：
   select * from test order by age asc;按年龄升序
   select * from test order by age desc;按年龄降序
   select * from test order by age, id desc;按年龄升序 如果年龄相同则按照id降序排序
4. 分页查询：
   分页查询用的关键字是limit, 后面接2个参数，第一个是(页码-1)*每页展示的记录数，第二个是每页展示的记录数
   查询第一页员工数据，每页展示10条数据：select * from test limit 0, 10;
   查询第一页员工数据，每页展示10条数据：select * from test limit 1, 10;
   查询性别为男的前5个员工，且按照年龄升序排序，如果年龄相同则按照id降序排序：select * from test where gender = '男' order by age , id desc limit 0, 5; limit 是在最后写

3.用户管理

• 创建一个用户名为zzw，只能在当前主机访问，密码是123456 create user 'zzw'@'localhost' identified by '123456';
• 能在任意主机访问：create user 'zzw'@'%' identified by '123456';
• 修改用户密码：alter user 'zzw'@'%' identified with mysql_native_password by '1234';
• 删除用户：drop user 'zzw'@'%';
• 查询用户权限：show grants for 'zzw'@'localhost';
• 授予zzw对于acwing这个数据库中所有表的所有权限：grant all on acwing.* to 'zzw'@'localhost';
• 撤销权限：revoke all on acwing.* from 'zzw'@'localhost';

4.函数

4.1 字符串函数

• select concat('Hello', ' world');
• 全部变小写select lower('ACWING');
• select upper('acwing');
• 左填充，填充到总长度为5select lpad('01', 5, '=');
• select rpad('01', 5, '=');
• 去掉前后的空格select trim(' hello world ');
• select substring('hello, world', 1, 5);
• 对数据库中表的某一字段用函数修改：update test set id =lpad(id,5,'0'); 将id补成5位

4.2 数值函数

• select ceil(1.1);
• select floor(1.5);
• select mod(2, 3);2%3的结果
• select rand(); 0~1之间的随机数
• select round(3.1456, 2);四舍五入保留2位小数
• 生成6位数的随机验证码select lpad(ceil(rand()*1000000), 6, 0);

4.3日期函数

• select curdate();
• select curtime();
• select now();
• select year(now());
• select month(now());
• select day(now());
• 当前时间往后推10天select date_add(now(), INTERVAL 10 day );
• select date_add(now(), INTERVAL 10 month );
• select date_add(now(), INTERVAL 10 year );
• 日期差值天数select datediff('2024-2-1', '2024-1-10');

4.4流程函数

• select if(false, 'ok', 'errror');第一个条件是true返回第二个值 否则返回第三个值
• select ifnull('', 'default');第一个条件是空则返回第二个值 注意null才是空
• select ifnull(null, 'default');
• case when then else end

select
    name,
    (case workaddress when '山西' then '一线城市' else '二线城市' end) as '工作地址' //如果是山西则写成一线城市 否则写成二线城市
from test;

select
    id,
    name,
    (case when math >= 85 then'优秀'when math >=60 then'及格'else'不及格'end ) as '数学'
    from score;

5.约束

create table user
(
    id     int primary key auto_increment comment '主键',
    name   varchar(10) not null unique comment '姓名',  //名字非空意为名字处写null会报错
    age    int check (age > 0 && age <= 120) comment '年龄',
    status char(1) default '1' comment '状态', //跳过状态直接写性别的话就 默认值为1  如下
    gender char(1) comment '性别'
)comment'用户表';

• insert into user(name,age,status,gender) values ('Tom1',19,'1','男'),('Tom2' ,25, '1','男'); 主键递增插入的时候就不用插主键了 直接跳过即可
• insert into user(name,age,gender) values('Tom5',120,'男');
• insert into user(name,age,status,gender) values (null,19,'1','男');报错

5.1 外键约束

• 为员工表添加外键：alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id); fk_emp_dept_id是外键名，最后几个单词是将dept_id和dept表的id关联起来
• 删除员工表的外键：alter table emp drop foreign key fk_emp_dept_id;
• 外键更新及删除行为：
  1、将子表和父表外键的行为设置为级联(cascade) alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id) on update cascade on delete cascade;
  设为级联的意思是当在父表中删除/更新对应记录时，也删除/更新外键在子表中的记录。
  2、将子表和父表外键的行为设置为set null alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id) on update set null on delete set null;
  设为set null的意思是当在父表中删除对应记录时，设置子表中该外键值为空。

6. 多表关系

• 一对多
  
• 多对多
  
• 一对一
  

7.多表查询

7.1 内连接

• 查询每一个员工的姓名，及关联的部门的名称(隐式内连接实现)：select emp.name ,dept.name from emp, dept where emp.dept_id = dept.id ;
• 显示内连接：select emp.name, dept.name from emp inner join dept on emp.dept_id = dept.id' 区别在于显示写出inner join, where换成了on
  如果emp表的部门号为空，那么查询结果不会显示这一条记录

7.2 外连接

查询emp表的所有数据，和对应的部门信息(左外连接)：select e.*,d.name from emp e left outer join dept d on e.dept_id = d.id;
这样查询的话会查到emp表的所有信息，即使emp表有一条部门的记录是空的
右外连接同理

7.3 自连接

• 查询员工所对应的领导：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; 这样的话就算员工没有领导也会显示出来 其领导显示为null.

8. 子查询

• 标量子查询 查询在“方东白”入职之后的员工信息：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 ='市场部');

9. 事务

事务 是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败(比如A给B转账)。

一个mysql语句就是一个事务，不进行手动设置的话sql语句执行后会自动提交到数据库

set @@autocommit = 0表示要设置成手动提交，表示在这个窗口内的sql要想执行成功，就必须先commit一下
执行完sql后 要再执行一下commit这样数据库中的表内容才会发生更改。这样相当于将sql语句绑定在一块，要么同时执行成功，要么都不执行

10. 并发事务问题

脏读：一个事务读到另外一个事务还没有提交的数据。
不可重复读：一个事务先后读取同一条记录，但两次读取的数据不同，称之为不可重复读。
幻读：一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在，好像出现了幻影”。

• 当设置事务的隔离级别是read uncommited时它不可解决脏读问题。脏读就是开启两个cmd窗口，各自开启事务(start transcation)，其中一个事务对数据进行修改，但是没有提交，但是在另一个窗口可以发现数据库内的信息已经发生变化，就是所谓的脏读。
  但将事务的隔离级别设置成read commited时，就不会发生这种现象。

• 不可重复读：开启两个cmd窗口，各自开启事务，其中一个事务对数据进修修改，并且提交了，这时在另一个窗口内读的时候发现数据根上次读取的数据不同了，这就是不可重复读
  脏读和不可重复读的区别在于，前者是对数据进行了修改但是没有提交，这时发现另一个窗口数据已经发生变化，后者是修改后进行了提交，发现另一个窗口数据发生变化。

• 幻读：开启两个cmd窗口，各自开启事务，在其中一个窗口查询id为10内容发现没查到，这时在另一个窗口对id为10的内容进行插入，并且提交了，此时我们在第一个窗口以为id为10的数据行不存在，所以想插入id是10的内容，发现插入失败，已经存在id是10的内容，但是我们查询一下发现在这个窗口并没有id为10的内容(只是在另一个窗口有id是10的内容)，这就是幻读。serializable可以解决这个问题。加上serializable后我们只有在一个事务提交后，另一个事务才能执行下去。解决了幻读问题。