数据库MySQL入门到入坟

数据库MySQL

一、数据库基础

  • 用管理员身份运行CMD,输入以下代码

    mysql -h -u root -p
    /*-h(主机名)
    -u(用户名)
    -p(密码)
    */
    

即可以连接成功。

  • 查看全部数据库

    show databases;
    
  • 创建数据库

    CREATE DATABASE IF NOT EXISTS test_db;
    #[IF NOT EXISTS ]:表示如果存在则不报错,不存在就创建数据库。
    create database if not exists mydb2 character set gbk;
    #创建一个gbk字符集的数据库
    create database mydb3 character set gbk collate gbk_bin;
    #创建一个gbk数据库,gbk校验字符集
    
  • 显示数据库

    show databases;
    
  • 显示数据库创建语句

    show create databases db_name;
    
  • 删除数据库

    drop databases [if exists] db_name; 
    
  • 修改数据库

    # 5.把mydb2的字符集修改为utf8;
    alter database mydb2 character set utf8 collate utf8_bin;
    
  • 进入数据库和创建表

    #进入数据库
    use db_name;
    #创建表,三个int字段
    create table t_a(
    	a int,
        b int,
        c int
    )character set utf8 collate utf8_bin;
    
  • 查看数据库中所有表

    #查看当前数据库中所有表
    show tables; 
    #查看指定数据库中所有表
    show tables in db_name;
    
  • 数据类型

    ######################################## 数据类型 #####################################
    # 整数 tinyint(1字节), smallint(2字节), mediumint(3字节), int(4字节), bigint(8字节)
    # 小数 float(M,D)(4字节), double(M,D)(8字节), decimal(M,D)(M+2字节)
    #其中,M称为精度,表示总共的位数;D称为标度,表示小数的位数。
    #DECIMAL 类型不同于 FLOAT & DOUBLE,DECIMAL 实际是以字符串存放的,它的存储空间并不固定,而是由精度 M 决定的。
    # 时间 year, time, date, datetime, timestamp
    #datetime系统默认值是null,而timestamp的系统默认值是当前时间now。
    #datetime存储时间与时区无关,而timestamp与时区有关。
    
  • 字符串类型

    类型名称占用字节说明
    char(M)M, 1 <= M <= 255固定长度字符串
    varchar(M)L+1, L <=M, 1 <=M <=255变长字符串
    TINYTEXTL+1, L < 2^8非常小的文本字符串
    TEXTL+2, L < 2^16小的文本字符串
    MEDIUMTEXTL+3, L < 2^24中等大小的文本字符串
    LONGTEXTL+4, L < 2^32大的文本字符串
    ENUM1 或者 2个字节,取决于枚举的数目,最大 65535个枚举类型
    SET1,2,3,4或8个字节集合类型
  • 二进制类型

    类型名称占用字节说明
    BIT(M)[(m+7)/8]位字节类型
    BINARY(M)M固定长度的二进制数据
    VARBINARY(M)L+1可变长度的二进制数据
    TINYBLOB(M)L+1, L < 2^8非常小的 BLOB
    BLOB(M)L+2, L < 2^16小的 BLOB
    MEDIUMBLOB(M)L+3, L < 2^24中等大小的BLOB
    LONGBLOB(M)L+4, L < 2^32非常大的BLOB
  • 添加时间表

    create table t_time (
    	t1 datetime,
        t2 timestamp
    );
    
    insert into t_time (t1) values (now());
    insert into t_time (t2) values (now());
    select * from t_time;
    
    set time_zone='+10:00'; 
    select * from t_time;
    set time_zone='+8:00';
    
  • 枚举类型

    create table t_enum (
    	gender enum('female', 'male'),
        sex enum('f', 'm') not null
    );
    
    insert into t_enum(gender) values('female');
    # insert into t_enum(gender) values('unkonw');
    # insert into t_enum(gender) values('female,male');
    insert into t_enum (sex) values('f');
    
    select * from t_enum;
    
  • 集合类型

    create table t_set(
    	'score' set('a','b','c','d')
    );
    #添加null
    insert into t_set values(null);
    #添加a
    insert into t_set values('a');
    #添加ab
    insert into t_set values('a','b');
    #添加abc,,,无论添加顺序如何,结果都是abc
    insert into t_set values('a','c','b'); 
    
  • 创建员工表

    create table t_employees (
    	id int,
        name varchar(20),
        gender enum('female', 'male'),
        birthday date,
        entry_date date,
        job varchar(20),
        salary decimal(10, 2),
        `resume` blob
    );
    # 查看表的结构
    describe t_employees;
    desc  t_employees;
    # 修改
    # 添加一列,uid,int类型
    alter table t_employees add column uid int;
    #在gender之后添加一个sex枚举
    alter table t_employees add column sex enum('female','male') after gender;
    #在第一列添加idcard varchar
    alter table t_employees add column idcard varchar(20) first;
    #把 resume修改为 varchar(50) 类型
    alter table t_employees modify column 'resume' varchar(50);
    #把name列修改为username varchar(50)
    alter table t_employees change column name username varchar(50);
    #删除列
    alter table t_employees drop id_card,drop uid,drop sex;
    #修改表的名称
    rename table t_employees to employees;
    #移动表到另外一个数据库
    rename table employees to mydb_2.t_employees;
    #删除表
    drop table t_a;
    drop table  if exists t_a;
    #复制表结构,不复制数据
    create table employees like mydb_2.t_employees;
    #复制表结构和数据
    create table t_employees(select * from mydb2.t_employees);
    ################################DML#############################################
    #插入数据
    insert into employees values (1,'lishuai','male','1996-4-21','1996-4-21',
                                  'master',10000,null);
    insert into employees (id, username, gender) values (2, 'zhangshuai', 'male');
    insert into employees (id,username,gender) values (2, 'zhangshuai', 'male'),(3, 'zhangshuai', 'male');
    #修改
    #将所有员工的薪资改为5000
    update employees set salary=5000;
    # 将姓名为'dashuai'的员工薪水修改为3000
    update employees set salary=3000 where username='dashuai';
    #删除
    delete from employees where username='dashuai';
    delete from employees;
    
  • 数据库的备份与导入

    1.#登录mysql进入数据库,source 文件路径
    2.#cmd, mysql -u root -p dbname<heros.sql
    ##########备份###############
    #mysqldump -u root -p db_name > honor_of_kings_backup.sql
    
  • 查询语句

    select version();
    #时间和日期相关函数
    select now();
    select year(now());
    select date(now());
    #和字符串相关的函数
    select concat ('_',trim(' ab cd '),'_');
    select length('ab中国');
    #查询单列
    select name from heros;
    # 3. 查看多列, 查看所有英雄名字, 最大生命值, 最大法力值, 主要角色定位
    select name,max_hp,max_mp,role_main from heros;
    #4. 查看所有列, 查看所有英雄的所有字段 (最好不要在生产环境中使用)
    select * from heros;
    # 5. 通过where子句过滤记录, (在MySQL中0表示false, 非0表示true)
    select * from heros where 0;
    ################################# 运算符 ################################
    # 查找name='花木兰'的数据
    select * from heros where name = '花木兰';
    select * from heros where name <=> '花木兰';#安全等于
    # 查看所有没有辅助角色定义的英雄名字
    select name,role_assist from heros where role_assist = null;
    select name,role_assist from heros where role_assist <>null;
    select name,role_assist from heros where role_assist  is null;
    # 查看所有有辅助角色定位的英雄名字
    select name from heros where role_assist is not null;
    select name from heros where role_assist <> null;
    ## 查看最大生命值在[7350, 8341]范围内的所有英雄, between and
    select * from heros where max_hp between 7350 and 8341;
    # 查看主要角色定位为战士和坦克的英雄, in
    select * from heros where role_main in ('战士','坦克');
    # 查看主要角色定位不为辅助和法师的英雄, not in
    select name ,role_main from heros where role_main not in('辅助','法师');
    # 模糊查询, Like一般是与一些通配符一起使用, 在MySQL中'_'表示匹配一个字符, '%'表示匹配任意个字符。
    # 在不同的DBMS中, 通配符可能不一样. 
    # 查询名字中包含'太'的英雄。
    select name from hreos where name like '%太%';
    # 查询名字中除第一个字符外包含'太'字的英雄 
    select name from heros where name like '%_太%';
    # 查询heros有哪些主要角色定位
    select role_main from heros;
    select distinct role_main from heros;
    # 注意事项:
    # 1. 对所有的字段进行去重, 所有字段都相同, 才是相同的记录。
    # 2. disctinct 必须位于所有查询字段的前面
    
    # 在MySQL里面, 我们可以通过 limit 关键字限制数量。不同的DBMS, 限制结果集的关键字可能是不一样的。
    # 查看前三条记录
    select * from heros limit 3 offset 0;
    select * from heros limit 3;
    # 查看[6, 10]之间的记录
    select * from heros limit 5 offset 5;
    # 分页查询, [rows, pageNum] = [8, 3]
    select * from heros limit 8 offset 16;
    # 根据最大生命值从低到高进行排序
    select name,hp_max from heros order by hp_max asc;
    # 先根据最大生命值从高到低排序,然后在根据最大法力值从低到高排序。
    select name,hp_max ,mp_max from heros order by hp_max desc,mp_max asc;
    # 查看最大英雄的最大生命值和最大法力值之和
    select name,(hp_max + mp_max) as hmp from heros order by hmp desc; 
    
  • 聚合函数

    # 聚合函数,统计的是列,不是行。(统计行用计算字段)
    # 注意事项:
    ## 1) 聚合函数往往是搭配分组使用的, 如果没有分组,那么统计的就是整张表的数据。
    ## 2) 如果分组了, 那么统计的就是每一组的数据。
    # count()
    select count(*) from heros; # 69
    select count(role_assist) from heros; # 不会统计null行
    
    # sum()
    select sum(hp_max) from heros; # 不会统计null行
    select sum(name) from heros; 
    select null + 0; # null
    
    # avg()
    select avg(hp_max) from heros; # 不会统计null行
    select avg(name) from heros; # 
    
    # max()
    select max(hp_max) from heros; # 不会统计null行
    select max(name) from heros; 
    
    # min()
    select min(hp_max) from heros; # 不会统计null行
    select min(name) from heros;
    
  • 分组

    # 分组 group by
    # 注意事项:虽然DBMS实现的时候,往往会对分组进行排序。但是如果没有明确的order by子句
    # 我们就不应该认为结果是有序的。
    
    select role_main from heros group by role_main;
    # select name, role_main from heros group by role_main;
    select group_concat(name), role_main from heros group by role_main;
    
    # 1) 搭配聚合函数使用
    select count(*), role_main from heros group by role_main;
    # 2) 对多字段进行分组
    select role_main, attack_range from heros group by role_main, attack_range;
    	
    select role_main, attack_range from heros group by role_main, attack_range having count(*) > 5;
    
    # where和having都可以过滤, 那它们之间的区别是什么?
    
  • 约束

    # 1. 实体完整性 
    # primary key, 独一无二的标识。要求不能为null, 不能重复。一张表最多只有一个主键。
    create table t_student (
    	id int primary key,
        name varchar(20),
        chinese int,
        math int,
        english int
    );
    drop table t_student;
    
    create table t_student (
    	id int,
        name varchar(20),
        chinese int,
        math int,
        english int,
        primary key(id)
    );
    show index in t_student;
    # priamry key 可以是多个字段
    create table xuanke(
    	sid int,
        cid int,
        primary key(sid, cid)
    );
    show index from xuanke;
    
    # 创建表之后,再指定主键。(不推荐!)
    drop table xuanke;
    create table xuanke(
    	sid int,
        cid int
    );
    show index from xuanke;
    alter table xuanke add primary key(sid, cid);
    show index from xuanke;
    
    ## auto_increment 往往是和 primary key 一起使用。
    ## 1) auto_increment 只能作用于primary key或者是 unique.
    ## 2) auto_increment 只能作用于数值字段。
    ## 3) 一张表只能有一个自增长字段。
    
    show tables;
    drop table t_student;
    create table t_student (
    	id int primary key auto_increment,
        name varchar(20),
        chinese int,
        math int,
        english int
    );
    
    insert into t_student values (null, '刘亦菲', 100, 100, 100), (null, '迪丽热巴', 80, 80, 80);
    select * from t_student;
    
    drop table t_student;
    create table t_student (
    	id int unique auto_increment,
        name varchar(20),
        chinese int unique auto_increment,
        math int,
        english int
    );
    
    #2. 域(field)的完整性
    #2.1 not null
    create table t_student (
    	id int primary key auto_increment,
        name varchar(20) not null,
        chinese int,
        math int,
        english int
    );
    insert into t_student values (null, null, 100, 100, 100);
    insert into t_student values (null, 'Thomas_He', 100, 100, 100);
    
    #2.2 unique 
    #注意事项:null可以有多条。
    create table t_unique(
    	a int unique
    );
    insert into t_unique values (1);
    # insert into t_unique values (1);
    insert into t_unique values (null);
    insert into t_unique values (null);
    select * from t_unique;
    select count(*) from t_unique;
    
    #2.3 default 默认值
    create table t_default(
    	a int default 100,
        b int
    );
    
    insert into t_default(a) values(50);
    insert into t_default(b) values(50);
    select * from t_default;
    
    #2.4 check, 自定义约束. MySQL没有实现这个功能,Oracle实现了。
    create table t_check(
    	height float(3, 2) check(height between 0.00 and 3.00)
    );
    insert into t_check values (1.89);
    insert into t_check values (3.11);
    select * from t_check;
    
    # 3. 参照性约束 foreign key(外键)
    show tables;
    drop table t_student;
    drop table xuanke;
    
    create table t_student (
    	id int primary key auto_increment,
        name varchar(20) not null,
        chinese int,
        math int,
        english int,
        cid int 
    );
    
    create table t_class(
    	id int primary key auto_increment,
        name varchar(20) not null,
        teacher varchar(20) not null
    );
    
    insert into t_class values (null, '一班', '张三'),(null, '二班', '李四'),(null, '三班', '王五');
    insert into t_student values (null, '赵六', 100, 100, 100, 100);
    select * from t_class;
    select * from t_student;
    
    # 创建外键
    create table t_student (
    	id int primary key auto_increment,
        name varchar(20) not null,
        chinese int,
        math int,
        english int,
        cid int,
        foreign key(cid) references t_class(id)
    );
    
    show index in t_student;
    drop table t_student;
    create table t_student (
    	id int primary key auto_increment,
        name varchar(20) not null,
        chinese int,
        math int,
        english int,
        cid int,
        constraint fk_cid foreign key(cid) references t_class(id)
    );
    show index in t_student;
    drop table t_student;
    create table t_student (
    	id int primary key auto_increment,
        name varchar(20) not null,
        chinese int,
        math int,
        english int,
        cid int
    );
    alter table t_student add constraint fk_cid foreign key(cid) references t_class(id);
    
    # insert into t_student values (null, '赵六', 100, 100, 100, 100);
    insert into t_student values (null, '赵六', 100, 100, 100, 1);
    select * from t_student;
    
    select * from t_class;
    insert into t_student values (null, '刘亦菲', 100, 100, 100, 1);
    insert into t_student values (null, '王语嫣', 100, 100, 100, 2);
    insert into t_student values (null, '赵灵儿', 100, 100, 100, 3);
    
    ## t_students 不能随意的添加和修改。
    # insert into t_student values (null, '白秀珠', 100, 100, 100, 4);
    # update t_student set cid = 4 where name='王语嫣';
    
    ## t_class 不能随意删除和修改。
    # delete from t_class where id = 1;
    insert into t_class values (null, '四班', 'Thomas_He');
    delete from t_class where id = 4;
    # update t_class set id=4 where id = 1;
    
    ## 级联删除
    drop table t_student;
    drop table t_class;
    create table t_class(
    	id int primary key auto_increment,
        name varchar(20) not null,
        teacher varchar(20) not null
    );
    insert into t_class values (null, '一班', '张三'),(null, '二班', '李四'),(null, '三班', '王五');
    create table t_student (
    	id int primary key auto_increment,
        name varchar(20) not null,
        chinese int,
        math int,
        english int,
        cid int,
        constraint fk_cid foreign key(cid) references t_class(id) on delete cascade
    );
    insert into t_student values (null, '刘亦菲', 100, 100, 100, 1);
    insert into t_student values (null, '王语嫣', 100, 100, 100, 2);
    insert into t_student values (null, '赵灵儿', 100, 100, 100, 3);
    
    select * from t_class;
    select * from t_student;
    
    delete from t_class where id = 1;
    
    ## 级联置空
    drop table t_student;
    drop table t_class;
    create table t_class(
    	id int primary key auto_increment,
        name varchar(20) not null,
        teacher varchar(20) not null
    );
    insert into t_class values (null, '一班', '张三'),(null, '二班', '李四'),(null, '三班', '王五');
    create table t_student (
    	id int primary key auto_increment,
        name varchar(20) not null,
        chinese int,
        math int,
        english int,
        cid int,
        constraint fk_cid foreign key(cid) references t_class(id) on delete set null
    );
    insert into t_student values (null, '刘亦菲', 100, 100, 100, 1);
    insert into t_student values (null, '王语嫣', 100, 100, 100, 2);
    insert into t_student values (null, '赵灵儿', 100, 100, 100, 3);
    
    select * from t_class;
    select * from t_student;
    
    delete from t_class where id = 1;
    # 注意事项: <阿里巴巴开发者手册> 不要在数据库中设置外键,一切的参照完整都应该在业务层中完成。
    # 外键缺点:
    # 1) 影响程序的性能
    # 2)在高并发的场景中容易引起死锁。
    # 3) 当数据量很多的时候,就需要分表。一旦分表,就不能保证参照完整性了。
    
    # 如果参照完整性都在业务中完成,也会导致一些问题。
    # 1) 业务和数据耦合了
    # 2) 增加了业务层的逻辑
    # 3)并不能够从数据库层面保证参照完整性。
    
    # 到底什么时候使用外键
    # 1) 正确性 > 性能
    # 2)并发度不高
    # 3) 数据量不大
    
  • 子查询

    # 子查询
    # a. 非关联子查询. 非关联子查询只需要查找一次。
    # b. 关联子查询。
    
    # 查询身高最高的球员有哪些?并且最高身高是多少? player_id, player_name, height;
    # select player_id, player_name, height from player where height = max(height);
    
    # a. 非关联子查询
    select player_id, player_name, height from player where height = (select max(height) from player);
    
    # b. 关联子查询
    # 查询每个球队中大于该球队平均身高的球员有哪些? player_id, team_id, player_name, height;
    select  player_id, team_id, player_name, height 
    from player AS a
    where (height > (select avg(height) from player AS b where b.team_id = a.team_id));
    
    # b.1 exists 子查询
    # 如果子查询结果集为空,exist返回false, 否则返回true;
    # 统计出过场的球员有哪些?player_id, player_name, team_id;
    select player_id, player_name, team_id 
    from player
    where exists (select * from player_score where player_score.player_id = player.player_id);
    # not exists: 如果子查询结果集为空,exist返回true, 否则返回false;
    # 统计没有出过场的球员有哪些?player_id, player_name, team_id;
    select player_id, player_name, team_id 
    from player
    where not exists (select player_id from player_score where player_score.player_id = player.player_id);
    
    # b.2 集合比较子查询
    # IN: 判断是否在集合中
    # 统计出过场的球员有哪些?player_id, player_name, team_id;
    select player_id, player_name, team_id
    from player
    where player_id in (select distinct player_id from player_score);
    
    select player_id, player_name, team_id
    from player
    where player_id in (select player_id from player_score where player_score.player_id = player.player_id);
    
    # 问题:哪一种性能比较好呢?
    ## SELECT * FROM A WHERE EXISTS (SELECT c FROM B WHERE B.c = A.c);
    ## SELECT * FROM A WHERE A.c IN (SELECT c FROM B)
    ## 1) 如果A表的数据比B表大, IN子查询效率比Exists要高。
    ## 2) 如果A表的数据比B表小, Exists子查询比IN子查询效率要高。
    
    # ANY (SOME) 一般和比较操作一起使用,与子查询集合的某个值比较
    # 找出所有比步行者中某一个球员身高要高的球员,并且不是步行者的球员。
    select player_name, team_id, height from player
    where height > any(select distinct height from player where team_id = 1002) 
    and team_id != 1002;
    
    select player_name, team_id, height from player
    where height > (select min(height) from player where team_id = 1002) 
    and team_id != 1002;
    
    # ALL 一般和比较操作一起使用,与子查询集合的所有值比较
    # 找出所有比步行者中所有球员身高要高的球员,并且不是步行者的球员。
    select player_name, team_id, height from player
    where height > all(select distinct height from player where team_id = 1002) 
    and team_id != 1002;
    
    select player_name, team_id, height from player
    where height > (select  max(height) from player where team_id = 1002) 
    and team_id != 1002;
    
    # 子查询作为计算字段
    # 查询每个球队的球员数
    select team_id, team_name, (select count(*) from player where player.team_id = team.team_id) AS team_num from team;     
    
    
  • 连接查询

    # Cross Join
    use nba;
    select * from player;
    select count(*) from player; # 37
    select * from team;
    select count(*) from team; # 3
    
    select * from player cross join team; 
    select count(*) from player cross join team; # 111
    
    # 等值连接
    ## 自然连接 natural join
    SELECT * FROM player NATURAL JOIN team;
    
    ## USING: 指定哪些相同的字段做等值判断。
    SELECT * FROM player JOIN team USING(team_id);
    
    ## ON: 后面接连接的条件。
    SELECT * FROM player JOIN team ON player.team_id = team.team_id;
    
    # 非等值连接
    # 我们想查询每个球员的身高级别
    select * from player;
    select * from height_grades;
    select player_name, height, height_level from player join height_grades 
    on height between height_lowest and height_highest;
    
    # 外连接
    create table t_boys (
    	id int primary key,
        name varchar(20) not null,
        gid int
    );
    insert into t_boys values (1, 'Albert', 1);
    insert into t_boys values (2, 'Ben', 2);
    insert into t_boys values (3, 'Calvin', 100);
    insert into t_boys values (4, 'David', null);
    
    create table t_girls (
    	id int primary key,
        name varchar(20) not null,
        bid int
    );
    insert into t_girls values (1, 'Allen', 1);
    insert into t_girls values (2, 'Beyonce', 2);
    insert into t_girls values (3, 'Cindy', 100);
    insert into t_girls values (4, 'Diana', null);
    
    ## 左外连接 left [outer] join
    ## 查看所有男生的情况
    select * from t_boys left join t_girls on t_boys.gid = t_girls.id;
    
    ## 右外连接 right [outer] join
    ## 查看所有女生的情况
    select * from t_boys right join t_girls on t_girls.bid = t_boys.id;
    
    ## 全外连接 full [outer] join:  MySQL不支持全外连接。
    ## 查看boys and girls 的情况
    select * from t_boys full join t_girls on t_girls.bid = t_boys.id;
    
    # 5. 自连接
    # 比如我们想要查看比布雷克-格里芬高的球员都有谁?
    select * from player where player_name = '布雷克-格里芬';
    # 子查询
    select player_name, height from player 
    where height > (select height from player where player_name = '布雷克-格里芬');
    # 自连接
    select a.player_name, a.height from player as a join player as b
    on b.player_name = '布雷克-格里芬'
    where a.height > b.height;
    
    # 推荐使用自连接, DBMS会对自连接进行优化。
    
    # 联合查询
    ## 用左外连接和右外连接实现全外连接。
    select * from t_boys left join t_girls on t_boys.gid = t_girls.id
    union
    select * from t_boys right join t_girls on t_girls.bid = t_boys.id;
    
    ## union all 不会去重。
    select * from t_boys left join t_girls on t_boys.gid = t_girls.id
    union all
    select * from t_boys right join t_girls on t_girls.bid = t_boys.id;
    
    # 不同的DBMS在使用规范上会存在一些差异,并且对SQL标准的支持程度也不一样。
    # a. 不是所有的 DBMS 都支持全外连接。
    # b. Oracle 不能使用 AS 给表起别名。 player p;
    
    # 连接查询需要的注意事项:
    # a. 连接表的数量不能太多。
    # b. 不要忘记连接条件。
    # c. 自连接优于子查询
    
  • 事务

    select @@autocommit; # 默认等于1, 
    # autocommit=1, 自动提交事务,会把每一条语句当成一条事务。
    use nba;
    
    # 显示开启事务方式1, start transaction
    start transaction;
    insert into t_boys values (5, 'Thomas_He', null);
    commit;
    
    # autocommit设置成0, 相当于开启了事务。
    
    set @@autocommit=0;
    insert into t_boys values (6, 'Thomas_He', null);
    commit;
    
    
    ## rollback
    start transaction;
    use nba;
    insert into t_boys values (7, 'Thomas_He', null);
    rollback;
    commit;
    
    ## rollback savepoint;
    start transaction;
    insert into t_boys values (7, 'Thomas_He', null);
    savepoint sp1;
    insert into t_boys values (8, 'Thomas_He', null);
    rollback to sp1;
    commit;
    
    use nba;
    show create table t_boys;
    start transaction;
    insert into t_boys values (8, 'Thomas_He', null);
    insert into t_boys values (9, null, null);
    insert into t_boys values (8, 'Thomas_He', null);
    commit;
    
    ## 隔离级别
    select @@tx_isolation; # 查询的会话的隔离级别。
    select @@session.tx_isolation; # 查询的会话的隔离级别。
    select @@global.tx_isolation; # 查询的全局的隔离级别。
    
    set transaction isolation level read uncommitted; # 设置下一个事务的隔离级别。
    set session transaction isolation level read uncommitted; # 设置会话的隔离级别。
    set global transaction isolation level read uncommitted; # 设置全局的隔离级别。
    
    
    select @@tx_isolation; ## RR
    select @@global.tx_isolation; ## RR
    # global 隔离级别影响的是以后的会话,当前会话不会影响。
    set global transaction isolation level repeatable read; 
    select @@tx_isolation; ## RR
    select @@global.tx_isolation; ## RU
    
    # read uncommitted
    set session transaction isolation level read uncommitted;
    select @@tx_isolation;
    
    use nba;
    ## 脏写(不存在)
    start transaction;
    select * from t_boys;
    update t_boys set gid = gid + 100 where id = 1;
    commit;
    
    ## 脏读(存在)
    start transaction;
    update t_boys set gid = gid + 100 where id = 1;
    commit;
    
    # read committed
    set session transaction isolation level read committed;
    select @@tx_isolation;
    
    ## 脏写(不存在)
    start transaction;
    update t_boys set gid = gid + 100 where id = 1;
    commit;
    
    ## 脏读(不存在)
    start transaction;
    update t_boys set gid = gid + 100 where id = 1;
    commit;
    
    ## 不可重复读(存在)
    ## 幻读(存在)
    start transaction;
    insert into t_boys values (9, 'Thomas_He', null);
    commit;
    
    # repeatable read
    set session transaction isolation level repeatable read;
    select @@tx_isolation;
    
    ## 脏写(不存在)
    start transaction;
    update t_boys set gid = gid + 100 where id = 1;
    commit;
    
    ## 脏读(不存在)
    start transaction;
    update t_boys set gid = gid + 100 where id = 1;
    commit;
    
    ## 不可重复读(不存在)
    ## 幻读
    start transaction;
    insert into t_boys values (10, 'Thomas_He', null);
    commit;
    
    # 注意事项:MySQL的RR级别,在一定程度上避免幻读现象。
    
    # serialize 
    
    ################################################ 练习 #####################################
    create database stu_db;
    use stu_db;
    create table student(
    	sid varchar(10),
        sname varchar(10),
        sage datetime,
        sgender varchar(10)
    );
    insert into student values('01' , '赵雷' , '1990-01-01' , '男');
    insert into student values('02' , '钱电' , '1990-12-21' , '男');
    insert into student values('03' , '孙风' , '1990-12-20' , '男');
    insert into student values('04' , '李云' , '1990-12-06' , '男');
    insert into student values('05' , '周梅' , '1991-12-01' , '女');
    insert into student values('06' , '吴兰' , '1992-01-01' , '女');
    insert into student values('07' , '郑竹' , '1989-01-01' , '女');
    insert into student values('09' , '张三' , '2017-12-20' , '女');
    insert into student values('10' , '李四' , '2017-12-25' , '女');
    insert into student values('11' , '李四' , '2012-06-06' , '女');
    insert into student values('12' , '赵六' , '2013-06-13' , '女');
    insert into student values('13' , '孙七' , '2014-06-01' , '女');
    
    create table course(
    	cid varchar(10),
        cname varchar(10),
        tid varchar(10)
    );
    insert into course values('01' , '语文' , '02');
    insert into course values('02' , '数学' , '01');
    insert into course values('03' , '英语' , '03');
    
    create table teacher(
    	tid varchar(10),
        tname varchar(10)
    );
    insert into teacher values('01' , '张三');
    insert into teacher values('02' , '李四');
    insert into teacher values('03' , '王五');
    
    create table sc(
    	sid varchar(10),
        cid varchar(10),
        score float(5,2)
    );
    insert into sc values('01' , '01' , 80);
    insert into sc values('01' , '02' , 90);
    insert into sc values('01' , '03' , 99);
    insert into sc values('02' , '01' , 70);
    insert into sc values('02' , '02' , 60);
    insert into sc values('02' , '03' , 80);
    insert into sc values('03' , '01' , 80);
    insert into sc values('03' , '02' , 80);
    insert into sc values('03' , '03' , 80);
    insert into sc values('04' , '01' , 50);
    insert into sc values('04' , '02' , 30);
    insert into sc values('04' , '03' , 20);
    insert into sc values('05' , '01' , 76);
    insert into sc values('05' , '02' , 87);
    insert into sc values('06' , '01' , 31);
    insert into sc values('06' , '03' , 34);
    insert into sc values('07' , '02' , 89);
    insert into sc values('07' , '03' , 98);
    
    select * from student;
    select * from course;
    select * from teacher;
    select * from sc;
    
    # 查询「李」姓老师的数量
    select count(*) from teacher where tname like '李%';
    
    # 查询选修「张三」老师课的同学的信息
    # 1. 涉及到哪些表?teacher, course, sc, student
    # 2. 张三老师叫的课程
    	  select cid, cname, tname
          from teacher join course using(tid) where tname='张三';
    # 3. 
    	select sid from teacher 
    		join course using(tid)
    		join sc using(cid)
    		where tname='张三';
    
    # 4. 
    	select * from student where sid in(
    		select sid from teacher 
            join course using(tid)
    		join sc using(cid)
    		where tname='张三'
        );
        
    # 2. 查询 "01" 课程比 "02" 课程成绩高的学生的信息及课程分数 
    # 涉及到哪些表?student, sc
    # 上01课程有哪些学生?(sid, score1)
       select sid, score as score1 from sc where cid = '01';    
    # 上02课程有哪些学生?(sid, score2)
       select sid, score as score2 from sc where cid = '02';
    # 同时上'01'课程和'02'课程的同学有哪些?(sid, score1, score2);
       select sid, score1, score2 
       from (select sid, score as score1 from sc where cid = '01') as t1
       join (select sid, score as score2 from sc where cid = '02') as t2
       using(sid);
    
    # 关联student
       select student.*, score1, score2 from student
       join (select sid, score as score1 from sc where cid = '01') as t1 using(sid)
       join (select sid, score as score2 from sc where cid = '02') as t2 using(sid)
       where score1 > score2;
       
    # 3. 查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
      # 涉及那些表? student, sc
      select sid, sname, avg(score) as avg_score from student join sc using(sid)
      group by sid having avg_score >= 60;
    

二、MySQL架构

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Ad1AO4Ny-1585737256383)(C:\Users\86133\Desktop\markdown\王道笔记\MySQL\图片1.png)]

MySQL分为server层和储存引擎层。

server层包括连接器,查询缓存,解析器,优化器和执行器涵盖了MySQL大多数核心服务功能。

存储引擎层负责数据的存储和提取。其架构模式是插件式的,支持innoDB,MyISAM,Mermory等多个存储引擎。

Q:修改管理权限以后,可以立即生效吗? A:不能,下一次生效。

Q:默认连接时长多少?A:28800s,8小时。

三、MySQL是如何工作的?

  1. 连接器

    当你在客户端输入 mysql –u $user –p $pwd 连接 mysql 的时候,接待你的就是连接器。连接器的作用就是和客户端建立连接、获取权限、维持和管理连接。

  2. 查询缓存

    建立连接后,就可以执行select语句了。首先MySQL会去查看查询缓存,看下之前是否已经执行过这条查询语句。如果命中缓存,就直接返回。否则就扔给解析器。

  3. 解析器

    MySQL需要知道你想做什么,因此我们就来到了解析器。解析器会做词法分析和语法分析。词法分析主要是分析每个词的含义,语法分析会判断你写的 SQL 语句是否满足 SQL 语法。

  4. 优化器

    经过解析器,MySQL就知道你想做什么了。但是在开始执行之前,还需要经过优化器的处理。优化器会优化你的SQL语句。生成最终的执行方案 (execution plan)。然后进入执行器阶段。

  5. 执行器

    执行器首先会判断你对这张表有没有相应的权限。如果没有,就报错。如果有,就调用相应的存储引擎接口,执行语句。然后将结果集返回给客户端。

Q:为什么不使用查询缓存?

A:一条更新语句就可以清除查询缓存,辛辛苦苦建立起来的缓存容易被擦除掉。MySQL8以后直接取消了这个功能。默认关闭。只在不会更新的表里使用–(不建议使用)

#查看mysql支持哪些引擎
show engines;
# 查看默认存储引擎
SHOW VARIABLES LIKE ‘%storage_engine%’;
# 查看某张表的存储引擎
SELECT ENGINE FROM information_schema.TABLES
WHERE TABLE_SCHEMA='$db'
AND TABLE_NAME='$table';

四、MyISAM

  1. 特点:
    • 查询速度快
    • 支持表锁
    • 支持全文索引
    • 不支持事务
  2. 使用MyISAM储存表,会生成三个文件:
    • frm 存储表结构,是任何存储引擎都有的。
    • mvd 存放数据
    • mvi 存放索引
  3. 数据和索引分开存放,这样得索引叫非聚集索引。

五、Memory

  1. 特点:
    • 所有数据都存放在内存中,因此数据库重启后会丢失
    • 支持表锁
    • 支持Hash和BTree索引
    • 不支持Blob和Text字段
  2. Memory由于数据都放在内存中,以及支持Hash索引,它的查询速度是最快的。一般使用 Memory 存放临时表。临时表:在单个连接中可见,当连接断开时,临时表也将不复存在。

六、innoDB

  1. MySQL 5.5 以及以后版本默认的存储引擎。没有特殊应用,Oracle官方推荐使用InnoDB引擎。

  2. 特点:

    • 支持事务
    • 支持行锁
    • 支持MVCC
    • 支持崩溃恢复
    • 支持外键一致性约束
  3. 使用 InnoDB 存储表,会生成两个文件.

    .frm # 存储表结构,是任何存储引擎都有的

    .ibd # 存放数据和索引

    索引和数据存放在一起,这样的索引叫聚集索引。

七、磁盘IO原理

磁盘上的数据可以用一个三维地址标识: 柱面号, 盘号, 块号(磁道上的扇区)

读/写数据的步骤:

  1. 移动磁头到指定的柱面号,这个过程被称为定位或查找。

​ 由于是机械移动, 这部分耗时最高, 最大可达 0.1s.

  1. 根据盘面号确定从哪个磁盘读取数据

  2. 盘组开始旋转,将指定的块号移动到读/写头下

​ 磁盘旋转的速度很快, 一般为7200rpm。旋转一圈大约需要 0.0083s.

​ 4. 读写数据

​ 数据通过系统总线传送到内存。一般传输一个字节大概需要 0.02us.

​ 读写 4KB 大约需要 80us.

磁盘读取数据是以盘块(block)为单位的, 一般为4KB。位于同一盘块的所有数据会被一次性全部读取出来。磁盘IO的代价主要花费在第 1 步。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-4xEvbCGC-1585737256385)(C:\Users\86133\Desktop\markdown\王道笔记\MySQL\图片2-1585475286999.jpg)]

结论:IO的速度是很慢很慢的,我们应该尽量少地读写磁盘!

八、innoDB数据页格式

页是 InnoDB 磁盘管理的最小单位。在 InnoDB 存储引擎中, 页默认大小为16KB。

可以通过参数 innodb_page_size 将页的大小设置为 4K、8K 和 16K。

InnoDB 每次至少会将 1 个页的数据从磁盘读取到内存,每次至少也会将 1 个页的数据从内存写到磁盘。

在InnoDB存储引擎中,有很多种页类型。其中最重要的是数据页,也叫 B-tree Node。里面存储了索引和数据的信息。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ckAzRqlK-1585737256385)(C:\Users\86133\Desktop\markdown\王道笔记\MySQL\图片3-1585475286999.png)]

File Header

​ 主要存储表空间相关信息

Page Header

​ 主要存储数据页的元信息

Infimum + Supremum Records

​ 每个数据页中有两个虚拟行记录用来限定记录的边界,infimum record 是数据页上最小的记录,supremum record 是数据页上最大的记录。

User Records

​ 用户数据,实际存储的行记录。

Free Space

​ 空闲空间。

Page Directory

​ 页目录,存放了记录的相对位置。

File Trailer

​ 位于数据页的最后,用来检测页是否完整地写入磁盘。

九、索引

  1. 索引是什么

    简单来说,索引的目的就是为了提高数据的查询效率,就像书的目录一样。

    一本800页的书,如果想在书中查找某个知识点。在不借助目录的情况下,估计得找好一会儿。同样,对于数据库的表而言,索引就是它的 ”目录”。

    **索引:**在 MySQL 中也叫做键 (key),是存储引擎用于快速找到记录的一种数据结构。

  2. 哪些数据结构可以作为索引?

    • 有序数组
    • 哈希表
    • 平衡二叉树
    • B树
      ​ 数据通过系统总线传送到内存。一般传输一个字节大概需要 0.02us.

​ 读写 4KB 大约需要 80us.

磁盘读取数据是以盘块(block)为单位的, 一般为4KB。位于同一盘块的所有数据会被一次性全部读取出来。磁盘IO的代价主要花费在第 1 步。

[外链图片转存中…(img-4xEvbCGC-1585737256385)]

结论:IO的速度是很慢很慢的,我们应该尽量少地读写磁盘!

八、innoDB数据页格式

页是 InnoDB 磁盘管理的最小单位。在 InnoDB 存储引擎中, 页默认大小为16KB。

可以通过参数 innodb_page_size 将页的大小设置为 4K、8K 和 16K。

InnoDB 每次至少会将 1 个页的数据从磁盘读取到内存,每次至少也会将 1 个页的数据从内存写到磁盘。

在InnoDB存储引擎中,有很多种页类型。其中最重要的是数据页,也叫 B-tree Node。里面存储了索引和数据的信息。

[外链图片转存中…(img-ckAzRqlK-1585737256385)]

File Header

​ 主要存储表空间相关信息

Page Header

​ 主要存储数据页的元信息

Infimum + Supremum Records

​ 每个数据页中有两个虚拟行记录用来限定记录的边界,infimum record 是数据页上最小的记录,supremum record 是数据页上最大的记录。

User Records

​ 用户数据,实际存储的行记录。

Free Space

​ 空闲空间。

Page Directory

​ 页目录,存放了记录的相对位置。

File Trailer

​ 位于数据页的最后,用来检测页是否完整地写入磁盘。

九、索引

  1. 索引是什么

    简单来说,索引的目的就是为了提高数据的查询效率,就像书的目录一样。

    一本800页的书,如果想在书中查找某个知识点。在不借助目录的情况下,估计得找好一会儿。同样,对于数据库的表而言,索引就是它的 ”目录”。

    **索引:**在 MySQL 中也叫做键 (key),是存储引擎用于快速找到记录的一种数据结构。

  2. 哪些数据结构可以作为索引?

    • 有序数组
    • 哈希表
    • 平衡二叉树
    • B树
    • B+树

未完待续-------

  • 0
    点赞
  • 3
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
1. 基础概念 汇编语言是一种低级语言,它是计算机硬件指令的助记符。汇编语言直接反映了计算机的硬件结构,可以直接操作计算机的硬件资源。 汇编语言的基本单位是汇编指令,它由操作码和操作数两部分组成。操作码表示要执行的操作,操作数表示操作的对象或数据。 汇编语言的程序由若干条汇编指令组成,程序的执行顺序由程序中的指令顺序决定。 2. 寄存器 寄存器是计算机中用来暂时存储数据的高速存储器件,是汇编语言中最常用的数据存储方式。 常用的寄存器有通用寄存器、段寄存器、指针寄存器、标志寄存器等。 通用寄存器包括AX、BX、CX、DX等,可以用来存储数据、地址、偏移量等。 段寄存器包括CS、DS、SS、ES等,用来存储段地址。 指针寄存器包括SP、BP、SI、DI等,用来存储栈指针、基址指针、源地址指针、目的地址指针等。 标志寄存器包括CF、PF、AF、ZF、SF、OF等,用来存储运算结果的状态信息。 3. 指令集 汇编语言的指令集包括数据传送指令、算术指令、逻辑指令、比较指令、跳转指令、循环指令等。 数据传送指令用来把数据从一个地方传送到另一个地方,包括MOV、XCHG、LEA等。 算术指令用来进行加、减、乘、除等运算,包括ADD、SUB、MUL、DIV等。 逻辑指令用来进行位运算,包括AND、OR、NOT、XOR等。 比较指令用来比较两个数据的大小关系,包括CMP、TEST等。 跳转指令用来改变程序的执行顺序,包括JMP、JZ、JNZ、JE、JNE等。 循环指令用来重复执行一段程序,包括LOOP、LOOPE、LOOPZ、LOOPNE、LOOPNZ等。 4. 程序设计 汇编语言的程序设计需要掌握一定的技巧和方法,包括程序的结构、数据的处理、流程控制等。 程序的结构包括程序的头部、数据段、代码段、堆栈段等。 数据的处理包括数据类型、数据的存储和读取、数据的转换等。 流程控制包括条件判断、循环控制、函数调用等。 5. 汇编器和调试器 汇编器是将汇编语言程序翻译成机器语言程序的工具,可以将汇编语言程序转换成目标代码或可执行文件。 调试器是用来调试程序的工具,可以帮助程序员查找程序中的错误、调试程序的执行流程等。 6. 总结 学习汇编语言需要掌握基本概念、寄存器、指令集、程序设计、汇编器和调试器等知识,需要进行大量的实践和练习,才能掌握汇编语言的编程技巧和方法。

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值