数据库MySQL入门到入坟

最新推荐文章于 2024-06-11 17:44:48 发布
最新推荐文章于 2024-06-11 17:44:48 发布
阅读量358 收藏 3
点赞数
文章标签： mysql
本文为博主原创文章，未经博主允许不得转载。
本文链接：https://blog.csdn.net/qq_31702655/article/details/105252592
版权

数据库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+树

未完待续-------

TinhTinh
关注
  • 0
    点赞
  • 踩
  • 3
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
MySQL深度解析，基础+高级篇数据库教程-从入门到入坟
javajavajava
08-19 562
前言 作为一名编程人员，对MySQL一定不会陌生，尤其是互联网行业，对MySQL的使用是比较多的。对于求职者来说，MySQL又是面试中一定会问到的重点，很多人拥有大厂梦，却因为MySQL败下阵来。实际上，MySQL并不难，今天这份最全的MySQL高性能，一共900页，几乎涵盖了MySQL的所有知识，尤其突出了实战技能和高级知识点，无论是工作还是面试看完这篇就足够了！ 话不多说了，一起来看看这份文档里的内容吧 目录 第一章：MySQL架构与历史 第二章：MySQL基准测试 ..
js初学者从入门到精通笔记.docx
09-28
js初学者从入门到精通笔记.docx
猿创征文｜深聊MySQL，从入门到入坟之：应该是全网最详细的MySQL知识点汇总，必须收藏。
商务合作 | 面试培训 | 职场规划 ==>主页扫码
09-01 5054
太详细了， 这就是MySQL的秘籍。
MySql的入门到入坟
herojeo的博客
03-12 341
系列文章目录 本章讲述Mysql的入门和使用。 文章目录系列文章目录前言一、数据库设计分析1.　软件开发周期2. 设计数据库的步骤3. 绘制Ｅ－Ｒ图4. 数据库设计范式二、MySql的入门和使用1. 什么是MySql?2. 启动和停止MySQL服务器服务3. MySql的基本使用MySQL命令行客户端常用数据库管理命令4. DDL之库、表管理SQL简介SQL分类库管理表管理列类型自增列约束MySQL客户端字符集设置修改表结构总结 前言 MySQl的优点： 1. 开源/免费/成本低 2. 性能高/移
SQL从入门到入魔之初入门
weixin_42485712的博客
09-11 172
一、什么是数据库？1.数据库（database）是保存有组织的数据的容器（一个文件或一组文件）;2.数据库是一个以某种有组织的方式存储的数据集合;二、什么是表？表：1.表（table）...
JAVA…… 从入门到入坟……
shaguguji423的博客
07-04 1846
JAVA技术交流：478052716
Python入门到入坟(上)
08-19
想学python从这里走起！！！带你从入门到入坟，让你变成一名堂堂正正的python程序猿
js从新手到精通笔记.docx
09-28
js从新手到精通笔记.docx
JUC高并发编程详细讲解 狂神笔记pdf 从入门到入坟 请与狂神视频配套使用
12-17
JUC高并发编程详细讲解 狂神笔记pdf 从入门到入坟 请与狂神视频配套使用 请去b站搜遇见狂神说同步课程跟进学习 食用效果最佳。
LaTex简单入坟.pdf
02-16
适用于学习latex论文书写，公式编排的同学
Java从入门到入坟系列学习路线目录索引（持续更新中~~~）
Code皮皮虾的博客
08-20 5756
文章目录一、Java基础二、Java数据库（Mysql）三、Java设计模式四、SSM等主流框架SpringSpringMVCMybatis五、SpringBoot六、SpringCloud（微服务） 一、Java基础 1. Java基础-String字符串详解 2.Java基础-异常 3.Java基础-集合 4.Java基础-反射 二、Java数据库（Mysql） 三、Java设计模式 四、SSM等主流框架 Spring SpringMVC Mybatis 五、SpringBoot 六、Spri
testlib.h从入门到入坟
weixin_34409357的博客
09-25 1927
学了这么久OI连个spj都不会写真是惭愧啊。。。 趁着没退役赶紧学一波吧 配置 github下载地址 我是直接暴力复制粘贴的。。 然后扔到MingW的目录里 直接引用就好啦   基本语法 引用testlib.h后，我们可以从三个地方读入数据 inf：输入文件 ouf：选手输出 ans：标准输出 当然，肯定不能直接用scanf读入，testlib里内置了很多...
选课系统
weixin_30783913的博客
02-11 402
要求 角色:学校、学员、课程、讲师 要求: 1. 创建北京、上海 2 所学校 2. 创建linux , python , go 3个课程 ， linux\py 在北京开， go 在上海开 3. 课程包含，周期，价格，通过学校创建课程 4. 通过学校创建班级， 班级关联课程、讲师 5. 创...
中科大研究生信息平台抢课脚本低级版本
江湖人称王某人的程序员
06-18 2692
中科大研究生信息平台抢课脚本 邮箱通知
数据库学习：MYSQL的测试数据库myemployees girls job_grades
热门推荐
GongmissYan的博客
11-06 4万+
实验目的：在B站学习数据库，发现网上的测试数据库大量都存在问题，或者收费的问题，所以自己整理了一份。 实验代码： myemployees.sql文件 /* Navicat Premium Data Transfer Source Server : mysql0815 Source Server Type : MySQL Source Server Version :...
MySQL数据库 | 数据表-查询命令详细记录
weixin_30832351的博客
01-10 199
本篇专门记录数据库增删改查中最常用、花招最多的 查。 【文章结构】 一、数据的准备 二、基本的查询功能 三、条件查询 四、查询排序 五、聚合函数 六、分组查询 七、分页查询 八、连接查询 九、子查询 十、自关联 【正文】 一、数据的准备 首先创建一个数据库，以便后文命令的使用。 -- 创建一个数据库 create database pysql charse...
MySQL数据库（一）
his_girl的博客
05-26 1340
数据库概述 数据库（Database）简称DB，是按照数据结构来组织、存储和管理数据的仓库，其本身可看作电子化的文件柜。用户可以对文件中的数据进行增加、删除、修改、查找等操作。 随着数据库技术的不断发展，数据库产品越来越多，常见的有Oracle、SQL Server、MySQL等，它们各自的特点如下所述 Oracle：Oracle公司推出的数据库管理系统，在数据库领域一直处于领先地位，同时也是目...
mysql-线上常用运维sql-2
最新发布
jll126的博客
06-11 362
在MySQL中，直接查询当前正在执行的事务可能不是非常直观，因为MySQL并没有提供直接的命令或系统视图来列出所有正在运行的事务。但是，你可以通过几种方法间接地获取这些信息。
【MySQL】服务器配置和管理
p_fly的博客
06-09 778
MySQL服务器通常说的是mysqld程序。mysqld 是 MySQL 数据库服务器的核心程序，负责处理客户端的请求、管理数据库和执行数据库操作。管理员可以通过配置文件和各种工具来管理和监控 mysqld 服务器的运行本文将介绍下面三个内容：服务器启动选项：可以在命令行和配置文件中指定，用于配置服务器的行为和特性。服务器系统变量：反映了启动选项的当前状态和值，其中一些变量可以在服务器运行时修改。服务器状态变量：包含了关于运行时操作的计数器和统计信息。
【汇编语言】零基础学习汇编 (从入门到入坟)笔记
03-27
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、付费专栏及课程。

余额充值