美文网首页
三、 SQL 基础

三、 SQL 基础

作者: Gq赵 | 来源:发表于2019-12-17 20:50 被阅读0次

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

1. SQL介绍

1.1 简介

结构化查询语言.

1.2 SQL标准

在各大平台都可使用
SQL89 SQL92 SQL99 SQL03 SQL05

1.3 SQL_MODE (控制sql的语法标准)

除数为零
日期
mysql> select @@sql_mode;

1.4 SQL 类型

DDL : 数据定义语言 : 库名,库属性,表名,表属性,列(列名,列属性)
DCL : 数据控制语言 : 权限
DML : 数据操作语言 : 数据行
DQL : 数据查询语言 : 数据行

1.5 SQL功能

管理,操作数据库对象:
库: 库名,库属性
表: 表名,表属性,列(列名,列属性),数据行

2. MySQL规范性存储限制

2.1 字符集Charset

utf8 : 最大字节长度3个. (中文占三字节)
utf8mb4 : 最大字节长度4个. 可以存储emoji表情字符. (中文占三字节)
mysql> show charset;

2.2 排序规则 (校对规则)

show collation;
默认是大小写不敏感.
utf8mb4_general_ci
utf8mb4_bin

2.3 数据类型

2.3.1 数字类型

tinyint 1字节长度数字 ===> 11111111 ====> 0-2^8-1 ===> -27-27-1 (3位)
int 4字节长度 ====> 0-2^32-1 ====> -2^31 - 2^31-1 (10位数)
bigint 8字节长度 ====> 0-2^64-1 ====> -2^63 - 2^63-1 (20位数)

2.3.2 字符串

char(10) : 定长类型,最多10个字符,占用存储空间一定.最多存储255个字符.
varchar(10):
变长类型,最多10个字符,按需分配存储空间.
需要额外1个字符或2个字符存储字符长度

因素: 变长的字符串列,90%几率都是varchar
具体原因是什么?
节省空间,还有没有别的原因?
遗留的问题..

enum('m','f') 枚举类型
1 2

2.3.3 时间类型

DATETIME
范围为从 1000-01-01 00:00:00.000000 至 9999-12-31 23:59:59.999999。

TIMESTAMP
1970-01-01 00:00:00.000000 至 2038-01-19 03:14:07.999999。
timestamp会受到时区的影响

2.3.4 二进制类型

2.3.5 JSON(8.0)

1.约束

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

2. 其他属性

2.1 AUTO_INCREMENT 自增长

特点：适用于ID主键列

2.2 DEFAULT默认值

特点: 使用在NOT NULL列中,不填写值时，自动生成默认值。

2.3 COMMENT 注释

特点：建议每个列都有注释。

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 查

mysql> show databases;  #查看所有库信息
mysql> show create database oldboy;  #查看创建的库时执行的方法

4.1.5 规范

1）库名要和业务有关
2）库名不能有大写字母，可以有小写字符，数字、特殊字符、
3）库名不能用数字开头。如果用：create database 123 加 符号
4）不能和内置关键词重名 比如 table、database
5）库名不要超过18个字符
6）收回所有用户的DROP权限，重要
7）必须要设置字符集，尽量是utf8mb4。

4.2 表的定义

desc 查看表结构

学生表: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

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);
建表规范: *****
  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*
#先建表
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*
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 删除表某行的内容

DELETE FROM student WHERE xid=21;
delete from (指定表名) + where (指定表中哪一行) xid(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;

6. DQL
select

相关文章

网友评论

      本文标题：三、 SQL 基础

      本文链接：https://www.haomeiwen.com/subject/nxmenctx.html

      延伸阅读

      深度阅读

      • 您也可以注册成为美文阅读网的作者，发表您的原创作品、分享您的心情！

      栏目导航

      热点阅读

      关于我们|服务条款|联系我们|三、 SQL 基础|投稿指南|网站地图|RSS订阅|排版工具|手机版

      提供经典美文摘抄,优美散文欣赏,现代诗歌精选,短篇小说,心情随笔,表白情书范文,故事会在线阅读欣赏

      Copyright © 2014-2023 Haomeiwen.com All Rights Reserved. 好美文阅读网 版权所有

      备案信息：桂公网安备 45052102000051号 · 桂ICP备13007215号-3

      本站所收录作品、热点评论等信息部分来源互联网，目的只是为了系统归纳学习和传递资讯

      所有作品版权归原创作者所有，与本站立场无关，如不慎侵犯了你的权益，请联系我们告知，我们将做删除处理！