MySQL知识大全竟然还可以这样玩

最新推荐文章于 2024-07-11 23:43:08 发布

深耕Java

最新推荐文章于 2024-07-11 23:43:08 发布

阅读量104

点赞数

分类专栏： Mysql Java理论笔记

本文链接：https://blog.csdn.net/qq_44905731/article/details/113108449

版权

Java理论笔记同时被 2 个专栏收录

37 篇文章 0 订阅

订阅专栏

Mysql

4 篇文章 0 订阅

订阅专栏

– DQL数据查询语言
– DDL数据定义语言
– DML数据操作语言
– DCL数据控制语言

– CRUD
– Create
– Retrieve
– Update
– Delete

########################### ↓数据库的CRUD操作↓ ###########################

-- 查询数据库
show databases;

-- 查询创建数据库的语法
-- 主要是看数据库的字符集
show create database db1;

-- 创建数据库
create database db1;
create database if not exists db1;
create database db1 character set gbk;
create database if not exists db1 character set gbk;

-- 修改数据库的字符集
alter database db1 character set utf8;

-- 删除数据库【危险！】
drop database db1;
drop database if exists db1;

-- 使用数据库
use db1;

-- 查询当前在用的数据库
select database();

########################### ↑数据库的CRUD操作↑ ###########################


########################### ↓数据表的CRUD操作↓ ###########################

-- 查询某数据库中的所有表名
show tables;

-- 查询表结构
desc table_1;

-- sql数据类型
-- int：整数类型
-- double(4, 1)：小数类型【指定4位数，小数点后保留一位】
-- data：日期类型【年月日 yyyy-MM-dd】
-- datatime：日期类型【年月日时分秒 yyyy-MM-dd HH:mm:ss】
-- timestap：时间戳类型【年月日时分秒 yyyy-MM-dd HH:mm:ss】
        不赋值/空值将会自动接收系统时间
-- varchar(指定字符长度)字符串类型
-- 大文件类型如何存储
    将大文件路径记录在表中，使用IO流将数据存到硬盘内

-- 创建表
create table student(
    id int,
    name varchar(32),
    age int,
    score double(4, 1),
    birthday date,
    insert_time timestamp
);

-- 删除表
drop table student;
drop table if exists student;

-- 复制表
create table stu like student;

-- 修改表
    -- 修改表名
    alter table student rename to stu;
    -- 修改表的字符集
    alter table stu character set utf8;
    -- 添加一列
    alter table stu add gender varchar(10);
    -- 修改列名和类型
    alter table stu change gender sex varchar(20);
    -- 只改类型
    alter table stu modify sex varchar(10);
    -- 删除列
    alter table stu drop sex;

-- 查询创建数据表的语法
-- 主要查看数据表的字符集
show create table stu;

########################### ↑数据表的CRUD操作↑ ###########################

########################### ↓表数据的CRUD操作↓ ###########################

-- 插入数据
-- 列名和值要一一对应
-- 表名后，不一定列名，则默认给所有列添加值
-- 除了数字类型，其他类型要用引号(单双引号都可)
insert into stu(id, name, age) values(1, '张无忌', 18);
insert into stu values(2, '赵敏', 17, 99.9, null, null);
insert into stu values(3, "张三丰", 17, 99.99 "1893-11-11", null);

-- 删除数据
-- 如果不加条件，则删除所有记录
delete from stu where id = 1;
delete from stu;  -- 影响效率
truncate table stu;  -- 删除表，再创建一样的空表

-- 修改数据
-- 如果不加条件则会修改所有相关数据
update stu set age = 117 where id = 3;
update stu set age = 18, score = 100 where id = 2;

---------------------------↓基础查询↓---------------------------

-- 多个字段的查询
select name, age from student;
select address from student;
select * from student;

-- 去重
select distinct address from student;

-- 计算列 可以使用四则运算计算列值，一般只可数值计算
select name, math, english, math+english from student;
-- 有null参与的计算都是null，可以用IFNULL函数解决
select name, math, english, math+IFNULL(english, 0) from student;

-- 起别名
select name as 姓名, math as 数学, english as 英语, math+IFNULL(english, 0) as 总分 from student;
select name 姓名, math 数学, english 英语, math+IFNULL(english, 0) 总分 from student;

---------------------------↑基础查询↑---------------------------

---------------------------↓条件查询↓---------------------------

select * from student where age > 20；
select * from student where age >= 20；

select * from student where age < 20；
select * from student where age <= 20；

select * from student where age = 20；
select * from student where age != 20；

select * from student where age >= 20 && age <= 30; -- 不推荐
select * from student where age >= 20 and age <= 30;
select * from student where age between 20 and 30;

select * from student where age = 22 or age = 18 or age = 25;
select * from student where age = 22 || age = 18 || age = 25;
select * from student where age in (18, 22, 25);

select * from student where english is null;
select * from student where english is not null;

select * from student where name like "马%";
select * from student where name like "_华%";
select * from student where name like "___";
select * from student where name like "%马%";

---------------------------↑条件查询↑---------------------------

---------------------------↓排序查询↓---------------------------

select * from student order by math  -- 默认升序
select * from student order by math asc  -- 升序
select * from student order by math desc  -- 降序

-- 当第一排序条件中数值一样的时候，会按照第二条件排序
select * from student order by math asc, english asc;
select * from student order by math asc, english desc;

---------------------------↑排序查询↑---------------------------

---------------------------↓聚合查询↓---------------------------

-- 聚合函数为纵向计算
-- 聚合函数计算会排除null值
select count(english) from student;
-- 解决办法1
select count(IFNULL(english, 0)) from student;
-- 解决办法2(推荐)
select count(id) from student;

-- 聚合函数计算会排除null值
select max(math) from student;
select min(math) from student;
select sum(math) from student;
select avg(math) from student;

---------------------------↑聚合查询↑---------------------------

---------------------------↓分组查询↓---------------------------

-- 分组之后查询的字段：分组字段，聚合函数
select sex, avg(math) from student group by sex;
select sex, avg(math), count(id) from student group by sex;

-- 分数低于70分不参与分组
select sex, avg(math), count(id) from student where math > 70 group by sex;

-- 分数低于70分不参与分组，分组之后人数要大于2个人
-- 1.where 在分组之前进行限定，如果不满足条件，则不参与分组
    -- having 在分组之后进行限定，如果不满足条件，则不查询出来
-- 2.where后面不可以跟聚合函数的判断
    -- having可以进行聚合函数的条件判断
select sex, avg(math), count(id) from student where math > 70 group by sex
    having count(id) > 2;
select sex, avg(math), count(id) as people from student where math > 70 group by sex
    having people > 2;

-- 此写法中的name既不是分组字段，也不是聚合函数，因此无实际参考意义
select name, sex, avg(math), count(id) from student group by sex;

---------------------------↑分组查询↑---------------------------

---------------------------↓分页查询↓---------------------------

-- limit语法是MySql的"方言"
-- 每页显示3条数据  开始的索引 = 3*(n-1)
select * from student limit 3*(1-1), 3;  -- 第1页
select * from student limit 3, 3;  -- 第2页
select * from student limit 3*(n-1), 3;  -- 第n页

---------------------------↑分页查询↑---------------------------

########################### ↑表数据的CRUD操作↑ ###########################

########################### ↓表约束↓ ###########################

概念：对表中的数据进行限定，保证数据的正确性，有效性，完整性
分类：
    主键约束 primary key
        1.非空且唯一
        2.一张表只能有一个字段为主键
        -- 在创建表时，添加主键约束
        create table stu(
            id int primary key,
            name varchar(20)
        );
        -- 删除主键
        alter table stu drop primary key;
        -- 创建完表后添加主键
        alter table stu modify id int primary key;
        -- 创建表时，添加自增长
        create table stu(
            id int primary key auto_increment,
            name varchar(20)
        );
        -- 删除自动增长
        alter table stu modify id int;
        -- 在创建表后，添加自增长
        alter table stu modify id int auto_increment;

    非空约束 not null
        -- 创建表时候添加非空约束
        create table stu(
            id int,
            name varchar(20) not null,
        );
        -- 删除name的非空约束
        alter table stu modify name varchar(20);
        -- 创建表后添加非空约束
        alter table stu modify name varchar(20) not null;

    唯一约束 unique 唯一约束对null无效
        -- 创建表时添加唯一约束
        create table stu(
            id int,
            phone_number varchar(20) unique
        );
        -- 删除phone_number的唯一约束
        alter table stu drop index phone_number;
        -- 创建表后添加唯一约束
        alter table stu modify phone_number varchar(20) unique;

    外键约束 foreign key
        作用：保持数据一致性，完整性
        -- 外键可以为null，但是不可为不存在的值
        -- 语法格式
        create table 表名(
            ...
            外键列
            constraint 外键名 foreign key (外键列名) references 主表名(主表列名)
        );
        -- 创建表时添加外键
        create table department(
            ...
        );
        create table employee(
            id int primary key auto_increment,
            name varchar(20),
            age int,
            dep_id int,  -- 外键列
            constraint emp_dept_fk foreign key (dep_id)
            references department(id);
        );
        -- 删除外键
        alter table employee drop foreign key emp_dept_fk;
        -- 创建表后添加外键
        alter table employee add constraint emp_dept_fk foreign key (dep_id)
            references department(id);
        -- 设置外和外键的级联更新操作[谨慎设置级联操作]
        alter table employee add constraint emp_dept_fk foreign key (dep_id)
            references department(id) on update cascade;
        -- 设置外和外键的级联删除操作[谨慎设置级联操作]
        alter table employee add constraint emp_dept_fk foreign key (dep_id)
            references department(id) on delete cascade;
        -- 设置外和外键的级联更新和删除操作[谨慎设置级联操作]
        alter table employee add constraint emp_dept_fk foreign key (dep_id)
            references department(id) on update cascade on delete cascade;

######################### ↑表约束↑ ###########################

########################### ↓多表操作↓ ###########################

数据库设计的重要性：
    影响项目完成度
    影响项目性能

数据库的设计：
    多表之间的关系
        一对一(了解)：人和身份证的关联
            实现方式：在任意一方添加外键，关联对方主键(让外键唯一unique)
        一对多(多对一)：部门和员工的关系
            实现方式：在多的一方建立外键，指向一的一方的主键
        多对多：学生和课程的关系
            实现方式：借助第三张中间表。中间表至少包含两个字段，这两个字段作为外键
            分别指向两张表的主键(这两个字段也称为联合主键)

########################### ↑多表操作↑ ###########################

########################### ↓三大范式↓ ###########################

数据库设计的范式：
    设计数据库时候，需要遵循的规范
    越高的范式数据库冗余越小
    目前关系数据库有六种范式：
        第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、
        巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）
    分类：
        第一范式（1NF）：每一列都是不可分割的原子数据项
        第二范式（2NF）：在1NF的基础上，非码属性必须完全依赖于候选码（在1NF基础上消除非主属性对主码的部分函数依赖）
            几个概念：
                1.函数依赖：如果通过A属性(属性组)的值，可以确定唯一B属性的值，则称B依赖于A
                    如：学号确定唯一姓名，学号和课程名称确定唯一分数
                2.完全函数依赖：如果A是一个属性组，则B属性值的确定需要依赖A中所有属性值
                    如：学号和课程名称确定唯一分数
                3.部分依赖：如果A是一个属性组，则B属性值的确定只需要依赖于A属性组中某一部分
                    如：姓名部分依赖于学号和课程名称
                4.传递函数依赖：如果A确定唯一B，B确定唯一C，那么C传递依赖于A
                    如：学号被系名依赖，系名被系主任依赖，所以学号被系主任传递依赖
                5.码：如果一张表中，一个属性或属性组，被其他所有属性完全依赖，则称这个属性(属性组)为该表的码
                    如：学号和课程名称为该表的码
                    主属性：码属性组中的所有属性
                    非主属性：除过码属性组的属性
        第三范式（3NF）：在2NF基础上，
            任何非主属性不依赖于其它非主属性（在2NF基础上消除传递依赖）

########################### ↑三大范式↑ ###########################

########################### ↓数据库备份与还原↓ ####################

1.命令行
    备份：mysqldump -u用户名 -p密码 数据库名 > 保存的路径
    还原：
        1.登录数据库
            mysql -u用户名 -p密码
        2.创建数据库
            create database 数据库名;
        3.使用数据库
            use 数据库名;
        4.执行文件
            source 文件路径
2.图形化工具
    备份：右键数据库，点击备份/到处，点击备份数据库，选择路径，命名备份文件名
    还原：右键执行sql脚本，点击执行，会自动创建数据库，并导入备份的数据

########################### ↑数据库备份与还原↑ ####################

########################### ↓多表查询↓ ###########################
– 笛卡尔积
– A,B集合的所有组成情况叫笛卡尔积
select * from emp, dept;

-- 内连接查询
-- 隐式内连接 使用where条件
select emp.name, emp.gender, dept.name from emp, dept where emp.dept_id = dept.id;
select
    t1.name,
    t1.gender,
    t2.name
from
    emp t1,
    dept t2
where
    t1.dept_id = t2.id;
-- 显示内连接
select * from emp inner join dept on emp.dept_id = dept.id;
-- inner可以省略
select * from emp inner dept on emp.dept_id = dept.id;

-- 外连接查询[左右外连接，一般用左外较多]
-- 左外连接 查询的是左表所有信息以及其交集部分
select t1.*, t2.name from emp t1 left join dept t2 on t1.dept_id = t2.id;
-- 右外连接 查询的是右表所有信息以及其交集部分
select t1.*, t2.name from emp t1 right join dept t2 on t1.dept_id = t2.id;

########################### ↑多表查询↑ ###########################

########################### ↓子查询↓ ###########################

概念：查询中嵌套查询，称嵌套查询为子查询

-- 查询最高工资的员工信息
-- 1.查询最高工资是多少
select max(salary) from emp;
-- 2.查询员工信息，条件为工资等于9000
select * from emp where emp.salary = 9000;
-- 一条sql
select * from emp where emp.salary = (select max(salary) from emp);

-- 子查询情况1，子查询结果是单行单列的
    -- 子查询可以作为条件，使用运算符去判断
    select * from emp where emp.salary < (select avg(salary) from emp);
-- 子查询情况2，子查询结果是多行单列的
    -- 可以使用运算符in来判断
    select * from emp where dept_id in (select id from dept where name = "财务部" or name = "市场部");

-- 子查询情况3，子查询结果是多行多列的
    -- 子查询可以作为一张虚拟表
    -- 查询员工入职日期是2011-11-11日之后的员工信息和部门信息
    select * from dept t1, (select * from emp where emp.join_date > "2011-11-11") t2
    where t1.id = t2.dept_id;
    -- 用普通内连接
    select * from emp t1, dept t2 where t1.dept_id = t2.id
    and t1.join_date > "2011-11-11";

########################### ↑子查询↑ ###########################

########################### ↓事务↓ ###########################

1.事务的基本介绍
    1.概念：
        如果一个包含多个步骤的业务操作，被事务管理，这些操作要么同时成功，要么同时失败
    2.操作
        开启事务 start transaction;
        回滚 rollback;
        提交 commit;
    3.MySQL数据库中事务默认自动提交
        事务提交的两种方式：
            mysql默认自动提交
                一条DML语句会自动提交一次事务
            手动提交
                需要先start transaction，再commit
        修改事务的默认提交方式：
            查看事务的默认提交方式
                -- 1代表自动提交 0代表手动提交
                select @@autocommit;
            修改默认提交方式：
                set @@autocommit = 0;
                当设置为0后，使用DML语句后需要commit数据才生效

2.事务的四大特征
    1.原子性：不可分割的最小操作单位，要么同时成功，要么同时失败
    2.持久性：事务一旦提交或回滚，数据库会持久化保存数据
    3.隔离性：多个事务之间，相互独立
    4.一致性：事务操作前后，数据总量不变

3.事务的隔离级别(了解)
    概念：多个事务之间相互独立的。如果多个事务操作同一批数据，会有问题
    设置不同的隔离级别，就可以解决这些问题
    存在问题：
        1.脏读：一个事务读取到另一个事务中没有提交的数据
        2.不可重复读(虚读)：在同一个事务中，两次读取的数据不一样
        3.幻读：一个事务操作(DML)数据表中所有记录，另一个事务添加了一条数据，则第一个事务查询不到自己的修改
    隔离级别：
        read uncommitted：读未提交
            产生的问题：脏读，不可重复读，幻读
        read committed：读已提交 (Oracle默认级别)
            产生的问题：不可重复读，幻读
        repeatable read：可重复读 (MySQL默认级别)
            产生的问题：幻读
        serializable：串行化
            可解决所有问题
        隔离级别从小到大，安全性越来越高，效率越来越低
    数据库查询隔离级别：
        select @@tx_isolation;
    数据库设置隔离级别：
        set global transaction isolation level 级别关键字;

########################### ↑事务↑ ###########################

########################### ↓用户管理和权限管理↓ ###########################

DBA：数据库管理员
    1.管理用户
        1.添加用户：
            create user '用户名'@'主机名' identified by '密码'';
        2.删除用户：
            drop user '用户名'@'主机名';
        3.修改用户密码：
            update user set password = PASSWORD('新密码') where user = '用户名';
            set password for '用户名'@'主机名' = PASSWORD('新密码');
            忘记了root用户密码的情况，修改root用户密码：
                1. cmd --> net stop mysql 停止sql服务需要dos管理员权限
                2.使用无验证方式，启动mysql服务：mysqld --skip-grant-tables
                3.打开新cmd窗口，直接输入mysql，可以登录
                4.use mysql;
                5.update user set password = PASSWORD('新密码') where user = 'root';
                6.关闭cmd窗口
                7.打开任务管理器，结束mysqld.exe的进程
                8.启动mysql服务
                9.使用新密码登录
        4.查询用户：
            -- %表示可以在任意主机使用用户登录数据库
            select * from user;
    2.授权
        1.查询权限
            show grants for'用户名'@'主机名';
        2.授予权限
            grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';
            grant select, delete, update on db3.account to 'lisi'@'%';
            grant all on *.* to 'zhangsan'@'localhost';
        3.撤销权限
            revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';

########################### ↑用户管理和权限管理↑ ###########################