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

– 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 '用户名'@'主机名';

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

已标记关键词 清除标记
相关推荐
©️2020 CSDN 皮肤主题: 深蓝海洋 设计师:CSDN官方博客 返回首页