MySQL-DBA课程-Day03

昨日回顾

  1. 字符集
    show charset;
    utf8 : 3个字节
    utf8mb4 : 4个字节 , 支持emoji

  2. 校对规则
    show collation;
    _ci
    _bin

  3. 数据类型
    tinyint : 1字节
    int : 4字节
    bigint : 8字节

char(64) : 固定长度
varchar(64) : 变化长度
enum(64) : 枚举类型
enum('bj','sh','tj','hb')
.............1 2 3

datetime
timestamp

今日内容

1. 约束
1.1 PrimaryKey(PK): 主键
特点: 唯一+非空,一张表中只能有一个主键约束.一般是一个数字列.最好是无意义的.
1.2 NOT NULL 非空
特点: 不能为空,我们建议业务关键列(索引列),尽量设置成非空.
1.3 UNIQUE 唯一约束
特点: 不能有重复值.一般像手机号,身份证号,qq,邮箱.....
1.4 unsigned 数字列无符号.
特点: 必须要加载数字列后,表示数字无负数.一般适用于年龄....

2. 其他属性
2.1 AUTO_INCREMENT 自增长
特点: 适用于ID主键列.
2.2 DEFAULT 默认值
特点: 使用在NOT NULL 列中,不填写值时,自动生成默认值。
2.3 COMMENT 注释
特点: 建议每个列都有一个注释.
10张表: -----> 每个表每个列加注释.

3. DCL 数据控制语言(略)
grant
revoke

4. DDL应用
4.1 库
4.1.1 增
CREATE DATABASE oldguo CHARSET utf8mb4 COLLATE utf8mb4_bin;

4.1.2 删 (危险,不代表生产操作.)
DROP DATABASE oldguo;

4.1.3 改
ALTER DATABASE oldguo CHARSET utf8mb4 COLLATE utf8mb4_bin;

4.1.4 查(DQL)
mysql> show databases;
mysql> show create database oldboy;

4.1.5 规范
(1) 库名要和业务有关
(2) 库名不能有大写字母,可以有小写字符,数字,特殊符号.
为什么?
CREATE DATABASE OLDGUO CHARSET utf8mb4 COLLATE utf8mb4_bin;
(3) 库名不能是数字开头
(4) 库名不能是预留字符
(5) 库名不要超过18个字符.
(6) 必须要设置字符集.尽量是utf8mb4.
(7) 收回所有用户的DROP权限.

4.2 表定义
4.2.1 增
学生表:student

drop table student;
CREATE TABLE `student` (
  `xid` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '学号',
  `xname` varchar(64) COLLATE utf8mb4_bin NOT NULL COMMENT '姓名',
  `xage` tinyint(3) unsigned NOT NULL DEFAULT '99' COMMENT '年龄',
  `xsex` char(1) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '性别',
  `xtel` char(14) COLLATE utf8mb4_bin NOT NULL COMMENT '手机号',
  `xcard` char(18) COLLATE utf8mb4_bin NOT NULL COMMENT '身份证号',
  `xaddr` enum('北京市','上海市','深圳市','山东省','甘肃省','河北省','山西省','河南省','辽宁省','吉林省','黑龙江省','内蒙古自治区','新疆维吾尔自治区','四川省','陕西省','江苏省','福建省','湖北省','广东省','广西省') COLLATE utf8mb4_bin NOT NULL DEFAULT '北京市' COMMENT '地区',
  `xdate` datetime DEFAULT NULL COMMENT '入学时间',
  PRIMARY KEY (`xid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ;  


INSERT INTO student(xid,   xname , xage    ,xsex , xtel , xcard  ,xaddr ,xdate)
VALUES
(1 ,    '张三'   , 11  ,   'm'  ,   '110'  ,  '660',   '北京市',            '2019-01-01'),
(2 ,    '马六'   , 13  ,   'm'  ,   '111'  ,  '661',   '上海市',            '2019-01-01'),
(3 ,    '李四'   , 14  ,   'm'  ,   '112'  ,  '662',   '北京市',            '2019-01-01'),
(4 ,    '王五'   , 17  ,   'm'  ,   '113'  ,  '663',   '山东省',            '2019-01-01'),
(5 ,    '铁锤'   , 18  ,   'f'  ,   '114'  ,  '664',   '河南省',            '2019-01-01'),
(6 ,    '钢蛋'   , 13  ,   'f'  ,   '115'  ,  '665',   '河北省',            '2019-01-01'),
(7 ,    '孙悟空' , 19  ,   'm'  ,   '116'  ,  '666',   '山西省',            '2019-01-01'),
(8 ,    '猪八戒' , 21  ,   'm'  ,   '117'  ,  '667',   '河北省',            '2019-01-01'),
(9 ,    '唐僧'    ,23   ,  'm'   ,  '118'   , '668' ,  '吉林省',            '2019-01-01'),
(10,    '沙僧'    ,31   ,  'm'   ,  '120'   , '669' ,  '辽宁省',            '2019-01-01'),
(11,    '白龙马'  ,26   ,  'm'   ,  '119'   , '670' ,  '广西省',           '2019-01-01') ,
(12,    '牛魔王'  ,19   ,  'm'   ,  '121'   , '671' ,  '四川省',            '2019-01-01'),
(13,    '张无忌'  ,20   ,  'm'   ,  '122'   , '672' ,  '福建省',            '2019-01-01'),
(14,    '赵敏'    ,28   ,  'f'   ,  '123'   , '673' ,  '广东省',            '2019-01-01'),
(15,    '郭靖'    ,29   ,  'm'   ,  '124'   , '674' ,  '甘肃省',            '2019-01-01'),
(16,    '黄蓉'    ,17   ,  'f'   ,  '125'   , '675' ,  '深圳市',            '2019-01-01'),
(17,    '小龙女'  ,22   ,  'f'   ,  '126'   , '766' ,  '黑龙江省',            '2019-01-01'),
(18,    '杨过'    ,33   ,  'm'   ,  '127'   , '777' ,  '新疆维吾尔自治区',  '2019-01-01'),
(19,    '欧阳峰'  ,25   ,  'm'   ,  '128'   , '888' ,  '内蒙古自治区',      '2019-01-01'),
(20,    '小沈阳'  ,23   ,  'm'   ,  '129'   , '999' ,  '陕西省',            '2019-01-01');

SELECT * FROM student;

教师表 : teacher

CREATE TABLE teacher (
tid CHAR(5) NOT NULL PRIMARY KEY COMMENT '教师编号',
tname VARCHAR(64) NOT NULL  COMMENT '教师姓名',
tage TINYINT NOT NULL  DEFAULT 99 COMMENT '教师年龄',
tsex CHAR(1) NOT NULL DEFAULT 'm' COMMENT '教师性别',
tyear TINYINT NOT NULL  DEFAULT 3 COMMENT '工作年限',
txl  VARCHAR(64) NOT NULL DEFAULT '本科' COMMENT '学历',
tstar TINYINT NOT NULL DEFAULT 5 COMMENT '级别:1-10'
)ENGINE=INNODB CHARSET=utf8mb4;

成绩表 : score

DROP TABLE score;
CREATE TABLE score (
xid INT NOT NULL COMMENT '学生编号',
cid INT  NOT NULL COMMENT '课程编号',
score INT NOT NULL DEFAULT 0 COMMENT '课程分数',
quekao TINYINT NOT NULL DEFAULT 0 COMMENT '是否缺考:1缺考,0未缺考'
)ENGINE=INNODB CHARSET=utf8mb4;

INSERT INTO score(xid  ,   cid  ,  score ,quekao) 
VALUES
(1        ,1001   ,80     ,0),
(1        ,1002   ,70     ,0),
(2        ,1001   ,0      ,1),
(2        ,1003   ,90     ,0),
(3        ,1004   ,80     ,0),
(4        ,1004   ,100    ,0), 
(5        ,1005   ,60     ,0), 
(4        ,1005   ,30     ,0), 
(5        ,1002   ,60     ,0), 
(6        ,1002   ,45     ,0), 
(7        ,1003   ,67     ,0), 
(7        ,1004   ,98     ,0), 
(8        ,1004   ,76     ,0), 
(9        ,1001   ,80     ,0),  
(10       ,1002   ,99     ,0), 
(11       ,1003   ,40     ,0),
(11       ,1004   ,50     ,0),
(12       ,1005   ,0      ,1),
(12       ,1003   ,90     ,0),
(13       ,1001   ,30     ,0),
(14       ,1002   ,100    ,0), 
(15       ,1003   ,60     ,0), 
(14       ,1004   ,30     ,0), 
(15       ,1005   ,60     ,0), 
(16       ,1002   ,45     ,0), 
(17       ,1003   ,67     ,0), 
(17       ,1004   ,98     ,0), 
(18       ,1004   ,76     ,0), 
(19       ,1005   ,75     ,0),  
(20       ,1002   ,68     ,0);

======================================

建表规范:★★★★★

  1. 表名: 不能大写字母,和业务有关,不能数字开头,长度控制在18字符以内,不能和关键字同名.
  2. 要设置存储引擎类型:INNODB,要设置字符集
  3. 列名要有意义
  4. 合适的,完整的,简短的数据类型.(会影响到索引的性能)
  5. 每个表要有主键,实在不知道怎么设置,也要找一个无关的自增长列设置为主键.
  6. 尽量每个列都有not null (特别是将来要做为索引的列)
  7. 每个列要有注释信息

小扩展:创建类似表结构

mysql> create table teacher_bak like teacher;

4.2.2 删(危险! 谨慎操作!)
mysql> drop table teacher_bak;
mysql> truncate table teacher;
面试: 请你说明 drop table truncate delete table 区别
drop table : 表结构+数据(物理性删除)
truncate table : 数据(清空数据页)
delete from table: 清空数据行(逐行删除)

4.2.3 改 ★★★★★
(1) 添加列

DESC xuesheng;
ALTER TABLE xuesheng ADD xqq  BIGINT  NOT NULL UNIQUE COMMENT 'qq号';
ALTER TABLE xuesheng ADD wechat  BIGINT  NOT NULL UNIQUE COMMENT '微信号' AFTER xtel;
ALTER TABLE xuesheng ADD mail  BIGINT  NOT NULL UNIQUE COMMENT '邮箱' FIRST'

(2) 删除列

ALTER TABLE xuesheng DROP mail;
ALTER TABLE xuesheng DROP xqq;
ALTER TABLE xuesheng DROP wechat;
ALTER TABLE xuesheng DROP mail;

(3) 修改

  1. 修改表名:
    ALTER TABLE xuesheng RENAME TO student;
    面试题:
    上亿行的数据规划:
    1. 按月归档表
    2. 没用的历史表进行挪走或删除
    pt-archiver 自己扩展 *****

  2. 修改某一列的属性信息
    DESC student;
    ALTER TABLE student MODIFY xname VARCHAR(128) NOT NULL COMMENT '姓名';

  3. 修改列名和属性
    ALTER TABLE student CHANGE xsex xgender CHAR(2) NOT NULL DEFAULT 'm' COMMENT '性别';

注意:

执行alter语句,都是需要进行锁表操作的,此时只能发生查询操作,不能做修改操作.
我们建议,alter语句,尽量在业务不繁忙期间发生.如果非得线上操作,建议使用pt-osc工具进行.

4.2.4 查

show tables ;
desc teacher;
show create table xx;

5. DML 数据操作语言
5.1 insert 插入数据

# 学生表:student
drop table student;
CREATE TABLE `student` (
  `xid` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '学号',
  `xname` varchar(64) COLLATE utf8mb4_bin NOT NULL COMMENT '姓名',
  `xage` tinyint(3) unsigned NOT NULL DEFAULT '99' COMMENT '年龄',
  `xsex` char(1) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '性别',
  `xtel` char(14) COLLATE utf8mb4_bin NOT NULL COMMENT '手机号',
  `xcard` char(18) COLLATE utf8mb4_bin NOT NULL COMMENT '身份证号',
  `xaddr` enum('北京市','上海市','深圳市','山东省','甘肃省','河北省','山西省','河南省','辽宁省','吉林省','黑龙江省','内蒙古自治区','新疆维吾尔自治区','四川省','陕西省','江苏省','福建省','湖北省','广东省','广西省') COLLATE utf8mb4_bin NOT NULL DEFAULT '北京市' COMMENT '地区',
  `xdate` datetime DEFAULT NULL COMMENT '入学时间',
  PRIMARY KEY (`xid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ;  

INSERT INTO student(xid,   xname , xage    ,xsex , xtel , xcard  ,xaddr ,xdate)
VALUES
(1 ,    '张三'   , 11  ,   'm'  ,   '110'  ,  '660',   '北京市',            '2019-01-01'),
(2 ,    '马六'   , 13  ,   'm'  ,   '111'  ,  '661',   '上海市',            '2019-01-01'),
(3 ,    '李四'   , 14  ,   'm'  ,   '112'  ,  '662',   '北京市',            '2019-01-01'),
(4 ,    '王五'   , 17  ,   'm'  ,   '113'  ,  '663',   '山东省',            '2019-01-01'),
(5 ,    '铁锤'   , 18  ,   'f'  ,   '114'  ,  '664',   '河南省',            '2019-01-01'),
(6 ,    '钢蛋'   , 13  ,   'f'  ,   '115'  ,  '665',   '河北省',            '2019-01-01'),
(7 ,    '孙悟空' , 19  ,   'm'  ,   '116'  ,  '666',   '山西省',            '2019-01-01'),
(8 ,    '猪八戒' , 21  ,   'm'  ,   '117'  ,  '667',   '河北省',            '2019-01-01'),
(9 ,    '唐僧'    ,23   ,  'm'   ,  '118'   , '668' ,  '吉林省',            '2019-01-01'),
(10,    '沙僧'    ,31   ,  'm'   ,  '120'   , '669' ,  '辽宁省',            '2019-01-01'),
(11,    '白龙马'  ,26   ,  'm'   ,  '119'   , '670' ,  '广西省',           '2019-01-01') ,
(12,    '牛魔王'  ,19   ,  'm'   ,  '121'   , '671' ,  '四川省',            '2019-01-01'),
(13,    '张无忌'  ,20   ,  'm'   ,  '122'   , '672' ,  '福建省',            '2019-01-01'),
(14,    '赵敏'    ,28   ,  'f'   ,  '123'   , '673' ,  '广东省',            '2019-01-01'),
(15,    '郭靖'    ,29   ,  'm'   ,  '124'   , '674' ,  '甘肃省',            '2019-01-01'),
(16,    '黄蓉'    ,17   ,  'f'   ,  '125'   , '675' ,  '深圳市',            '2019-01-01'),
(17,    '小龙女'  ,22   ,  'f'   ,  '126'   , '766' ,  '黑龙江省',            '2019-01-01'),
(18,    '杨过'    ,33   ,  'm'   ,  '127'   , '777' ,  '新疆维吾尔自治区',  '2019-01-01'),
(19,    '欧阳峰'  ,25   ,  'm'   ,  '128'   , '888' ,  '内蒙古自治区',      '2019-01-01'),
(20,    '小沈阳'  ,23   ,  'm'   ,  '129'   , '999' ,  '陕西省',            '2019-01-01');

SELECT * FROM student;

# 课程表:course
drop table course;
CREATE TABLE course (
cid  INT NOT NULL PRIMARY KEY COMMENT '课程编号',
cname VARCHAR(64) NOT NULL COMMENT '课程名称',
tid   CHAR(5) NOT NULL COMMENT '讲师名',
cprice INT NOT NULL COMMENT '课程价格'
)ENGINE=INNODB CHARSET=utf8mb4;

insert into course(cid,  cname , tid    ,cprice )
values
(1001, 'linux'  ,'t0001' ,19800),  
(1002, 'python' ,'t0002' ,21800),  
(1003, 'golang' ,'t0003' ,16000),  
(1004, 'DBA'    ,'t0004' ,15000),  
(1005, 'safe'   ,'t0005' ,17800);  

# 教师表 : teacher
CREATE TABLE teacher (
tid CHAR(5) NOT NULL PRIMARY KEY COMMENT '教师编号',
tname VARCHAR(64) NOT NULL  COMMENT '教师姓名',
tage TINYINT NOT NULL  DEFAULT 99 COMMENT '教师年龄',
tsex CHAR(1) NOT NULL DEFAULT 'm' COMMENT '教师性别',
tyear TINYINT NOT NULL  DEFAULT 3 COMMENT '工作年限',
txl  VARCHAR(64) NOT NULL DEFAULT '本科' COMMENT '学历',
tstar TINYINT NOT NULL DEFAULT 5 COMMENT '级别:1-10'
)ENGINE=INNODB CHARSET=utf8mb4;

# 成绩表 : score
DROP TABLE score;
CREATE TABLE score (
xid INT NOT NULL COMMENT '学生编号',
cid INT  NOT NULL COMMENT '课程编号',
score INT NOT NULL DEFAULT 0 COMMENT '课程分数',
quekao TINYINT NOT NULL DEFAULT 0 COMMENT '是否缺考:1缺考,0未缺考'
)ENGINE=INNODB CHARSET=utf8mb4;
INSERT INTO score(xid  ,   cid  ,  score ,quekao) 
VALUES
(1        ,1001   ,80     ,0),
(1        ,1002   ,70     ,0),
(2        ,1001   ,0      ,1),
(2        ,1003   ,90     ,0),
(3        ,1004   ,80     ,0),
(4        ,1004   ,100    ,0), 
(5        ,1005   ,60     ,0), 
(4        ,1005   ,30     ,0), 
(5        ,1002   ,60     ,0), 
(6        ,1002   ,45     ,0), 
(7        ,1003   ,67     ,0), 
(7        ,1004   ,98     ,0), 
(8        ,1004   ,76     ,0), 
(9        ,1001   ,80     ,0),  
(10       ,1002   ,99     ,0), 
(11       ,1003   ,40     ,0),
(11       ,1004   ,50     ,0),
(12       ,1005   ,0      ,1),
(12       ,1003   ,90     ,0),
(13       ,1001   ,30     ,0),
(14       ,1002   ,100    ,0), 
(15       ,1003   ,60     ,0), 
(14       ,1004   ,30     ,0), 
(15       ,1005   ,60     ,0), 
(16       ,1002   ,45     ,0), 
(17       ,1003   ,67     ,0), 
(17       ,1004   ,98     ,0), 
(18       ,1004   ,76     ,0), 
(19       ,1005   ,75     ,0),  
(20       ,1002   ,68     ,0);

=========================================
5.2 update

SELECT * FROM student;
UPDATE student SET xname='王钢蛋' WHERE xid=6;
UPDATE student SET xname='李铁锤' WHERE xid=5;

5.3 delete

INSERT INTO student VALUES(21,'王二麻子',22,'f','921','345','上海市','2020-01-01');
DELETE FROM student WHERE xid=21;
扩展:

伪删除
(1) 添加状态列 is_del (1代表删除,0代表有效)
ALTER TABLE student ADD is_del TINYINT NOT NULL DEFAULT 0 COMMENT '1代表删除,0代表有效';
SELECT * FROM student;

(2) delete ---> update
原语句:
delete from student where xid=20;
改为 :
update student set is_del=1 where xid=20;

(3) 更改业务查询方法
原语句:
SELECT * FROM student;
改为:
SELECT * FROM student where is_del=0;

====================

  1. DQL
    select
    ====================
    作业:
    percona-toolkit ---->www.percona.com
    pt-archiver 自己扩展
    pt-osc 自己扩展
最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 214,904评论 6赞 497
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 91,581评论 3赞 389
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 160,527评论 0赞 350
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 57,463评论 1赞 288
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 66,546评论 6赞 386
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 50,572评论 1赞 293
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 39,582评论 3赞 414
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 38,330评论 0赞 270
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 44,776评论 1赞 307
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 37,087评论 2赞 330
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 39,257评论 1赞 344
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 34,923评论 5赞 338
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 40,571评论 3赞 322
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 31,192评论 0赞 21
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 32,436评论 1赞 268
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 47,145评论 2赞 366
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 44,127评论 2赞 352