MYSQL 基础总结

一、mysql概述

数据库:datebase DB 
数据库管理系统：datebase management system DBMS创建、使用、维护数据库
SQL：编程语言、标准
Mysql启动：社区版本
    net start\stop mysql80
    service.msc\\mysql80
客户端连接：
    mnysql提供的客户端命令工具，MySQL 8.0 Command Line Client - Unicode\\输入密码
    系统自带的命令行工具：mysql -h(主机地址) -u（用户名） root -p（密码）,设置环境变量
关系型数据库RDBMS：多张相互连接的二维表组成
    格式统一，便于维护
    标准统一，使用方便
数据模型：客户端/连接DBMS/创建数据库/创建表/表用来记录

二、sql

sql的通用语法:关键字建议大写，默认分号结尾。
    单行注释：--或#
    多行注释：/* */
SQL分类：

DDL

date delinition lauguage 定义对象(数据，表，字段)
    1、数据：if not exists....
    查询所有数据库：SHOW DATABASES;
    当前数据库查询：SELECT DATABASE();
    创建：CREATE DATABASE； 
    删除：drop database；
    使用：USE 数据库名称；  
    2、表：
    查询所有的表：SHOW TABLES；
    创建表：CREATE TABLE 表名
    查询表结构：DESC 表名；
    查询指定表的建表语句:SHOW CREATE TABLE 表名；
    数据类型：
        数值类型：age TINYINT UNSIGNED    score double(4,1)
        字符串：BLOB二进制数据   TEXT文本数据
            char(10)<=10个都是10......>性能较好  gender性别 char(1)
            varchar(10)超出10报错.....>性能差  用户名username varchar(50)
        日期时间类型：brithday date
    举例：
        create table emp(
            id int comment '编号',
            workno varchar(10) comment '工号',
            name varchar(10) comment '姓名',
            gender char(1) comment '性别',
            age tinyint unsigned comment '年龄',
            idcard char(18) comment '身份证号',
            entrydate date comment '入职时间'
        ) comment '员工表';
    修改表中的字段
        添加字段：ALTER TABLE 表名 ADD 字段名 类型 注释
        修改字段：ALTER TABLE 表名 change 旧字段名 新字段名 类型 注释
        删除字段：ALTER TABLE 表名 DROP 字段名
        修改表名：ALTER TABLE 表名 rename to 新表名
    删除表：DROP TABLE 表名
    删除并重新创建：TRUNCATE TABLE 表名；
    图形化界面工具：Sqlyog\Navicat\DataGrip,安装Datagrip

DML

date manipulation language(操作语言，数据的增删改)
    添加数据INSERT；一一对应，字符串和日期在引号中，规定数量
        指定字段添加：INSERTINTO 表名（字段1，字段2，……）VALUES(值1，值2，……),(值1，值2，……),(值1，值2，……)
        全部字段添加：INSERTINTO 表名 VALUES(值1，值2，……)
    修改数据UPDATE
        UPDATE employe set name = '小昭', gender = '女' where id = 1;
    删除数据DELETE
        delete from employe where id = 1;

DQL

date query lauguage(查询语言，查询数据表的记录),查询的频次高于增删改
    查询关键字：SELECT
    基本查询：
        查询多个字段
            SELECT name,workno,age from emp;
            select id, workno, name, gender, age, idcard, workaddress, entrydate from emp;
            select * from emp;
        设置别名 as
        去除重复记录 distinct
            select distinct workaddress as '工作地址' from emp;--as可以省略    
    条件查询：SELECT 字段列表 FROM 表名 WHERE 条件列表; 
        比较运算符，LIKE占位符，模糊匹配，_匹配单个字符，%匹配任意个字符
        select * from emp where name like '___';
        select * from emp where idcard like '%X';
        逻辑运算符:与或非select * from emp where age between 15 and 20;--between跟最小值，and后跟最大值。
    聚合函数：将一列数据作为整体聚合计算，COUNT,MAX,MIN,SUM等；作用于某一列
        select 聚合函数（字段列表    ) FROM 表名；--Null值不参与聚合函数计算
        select sum(age) from emp where workaddress = '西安';
    分组查询：group By 
        where 和 having 区别
        select gender,count(*) from emp group by gender;
        select gender,avg(age) from emp group by gender;
        select workaddress ,count(*) from emp where age < 45 group by workaddress having count(*) >= 3;
        分组之前过滤用where,分组之后过滤用having（可以判断）,where>聚合函数>having
    排序查询：ORDER BY;asc可以省略;
        select * from emp order by age asc,entrydate desc;
    分页查询：limit;起始索引从0开始，起始索引=（查询页码-1)*每页显示的记录数;
    综合：select * from emp where gender = '男' and (age between 20 and 40 ) and name like '___';
         select gender,count(*) from emp where age <60 group by gender;
         select name,age from emp where age <=35 order by age asc, entrydate desc;
         select * from emp where gender = '男' and (age between 20 and 40)order by age asc, entrydate asc limit 5;
    编写顺序、执行顺序

DCL

date control lauguage（控制语言，创建数据库用户，控制数据库的访问权限）
    用户管理：哪些用户可以管理
        create user 'itcast'@'localhost' identified by '123456';#创建用户
        create user 'heima'@'%' identified by '123456';
        alter user 'heima'@'%' identified with mysql_native_password by '1234';#修改密码
        drop user 'itcast'@'localhost';#删除用户
    权限控制：*通配符，可以使用那些表  itcast数据库名，heima用户名，emp表名
        show grants for 'heima'@'%';#查询权限
        grant all on itcast.* to 'heima'@'%';#授予权限
        revoke all on itcast.* from 'heima'@'%';#撤销权限

三、函数

字符串函数
    select concat('hello',' mysql');#拼接函数
    select lower("Hello");#转小写
    select upper("Hello");#转大写
    select lpad('01',5,'-');#从左填充长度
    select rpad('yu',6,'*');#从右填充长度
    select trim("  yh   00 ");#删除左右两侧空格
    select substring('Hello Mysql',1,5);#取一定长度的字符串
数值函数
    select ceil(1.9);
    select floor(1.9);
    select mod(3,4);#3除以4的余数，求模
    select rand();#0-1之间的随机数
    select round(2.347,2);#四舍五入保留两位小数
    select lpad(round(1000000*rand(),0),6,'0');#生成六位数验证码
日期函数
    select curdate();#当前
    select curtime();
    select now();#当前日期和时间
    select year(now());#当前时间的对应年月日
    select month(now());
    select day(now());
    select date_add(now(), interval 70 day );#添加一定日期
    select datediff('2000-08-06','2022-04-19');#时间差
    select name,datediff(curdate(),entrydate) as entrydates from emp order by entrydates;
流程控制函数
    select if(false,'yyh', 'error');
    select ifnull('','defalut');
    select ifnull(null,'defalut');
    #case when then else end
    select name,
        (case workaddress when '北京' then'一线城市' when '上海' then '一线城市' else '二线城市' end) as '工作地址'
    from emp;

四、约束

    概述：限制表中数据，正确性，有效性，完整性.
        非空约束：字段不能为空 not null
        唯一约束：字段数据的唯一性,主键自增，错误是也要+1
        主键约束：默认约束：检查约束：外键约束：
            create table user(
                id int primary key auto_increment comment '主键',#主键，自动增长
                name varchar(10) not null unique comment '姓名',#多个条件空格隔开
                age int check (age > 0 and age <= 120) comment '年龄',
                status char(1) default '1' comment '状态',
                gender char(1) comment '性别'
            ) comment '用户表';
    约束演示：
    外键约束：两张表数据建立连接，保证数据的一致性和完整性，主表、从表
        ALTER TABLE ADD CONSTRAINT FOR
        --- 外键建立 ---
        alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id);
        ---- 删除外键 ---
        alter table emp drop foreign key fk_emp_dept_id;
    删除更新行为
    alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id) on update cascade on delete cascade;
    alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id) on update set null on delete set null;

五、多表查询

    多表关系：笛卡尔积
        一对多：在多的一方建立外键，指向一的一方的主键
        多对多：建立第三张中间表
        一对一：
    内连接：
        隐式：select e.name , d.name from emp as e , dept as d where e.dept_id = d.id;-- 起别名
        显式：select e.name,d.name from emp as e inner join dept as d on e.dept_id = d.id;-- inner可以省略
    外连接
        select e.* , d.name from emp e left outer join dept d on e.dept_id = d.id;-- 左外连接
        select e.* , d.* from emp e right outer join dept d on e.dept_id = d.id;-- 右外连接
        select d.*, e.* from dept d left outer join emp e on e.dept_id = d.id;-- 右外连接---左外
    自连接（必须取别名)、：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 all , union ，删除all ,去除重复数据
    子查询:
        标量 ： 大于小于等于
        列 ：IN 、NOTIN、(ANY、SOME)、 ALL
        行:select * from emp where (salary,managerid) = (select salary, managerid from emp where name = '张无忌');
        表：-- 2. 查询入职日期是 "2006-01-01" 之后的员工信息 , 及其部门信息
            -- a. 入职日期是 "2006-01-01" 之后的员工信息
            select * from emp where entrydate > '2006-01-01';-- 另做一张表，联合子查询，必须起别名
            -- b. 查询这部分员工, 对应的部门信息;
            select e.*, d.* from (select * from emp where entrydate > '2006-01-01') e left join dept d on e.dept_id = d.id ；

六、事务

事务的操作
        开启事务：START TRANSACTION
        select @@autocommit; -- 查询提交方式
        set @@autocommit = 0; -- 设置为手动提交
        -- 3. 将李四账户余额+1000：update account set money = money + 1000 where name = '李四';
        -- 提交事务：commit;-- 回滚事务：rollback ;
    事务的四大特性
        原子性Atomicity
        一致性Consistency
        隔离性Isolation
        持久性Durability
    并发事务问题：
        脏读：A事务读到B事务没有提交的数据
        不可重复读：一个数据先后读取同一条记录，但两次读取的数据不同
        幻读：一个事务按条件查询数据时，显示数据行不存在，但是在插入数据时，又显示数据已经存在
    事务隔离级别
    查看事务隔离级别：select @@transaction_isolation
    设置事务隔离级别：set session/global transaction_isolation level {READ UNCOMMITTED\READ COMMITTED\REPEATABLE\SERIALIZATION} 
    READ UNCOMMITTED：三个问题都不隔离
    READ COMMITTED：可以隔离脏读
    REPEATABLE：默认，可隔离脏读和不可重复读
    SERIALIZABLE：都可以隔离，包括幻读，隔离级别最高，但是运行效果最差