1. SQL介绍
1.简介
结构化查询语言.
2.SQL标准
SQL89 SQL92 SQL99 SQL03 SQL05
3.SQL_MODE
除数为零
日期
mysql> select @@sql_mode;
4.SQL 类型
DDL : 数据定义语言 : 库名,库属性,表名,表属性,列(列名,列属性)
DCL : 数据控制语言 : 权限
DML : 数据操作语言 : 数据行
DQL : 数据查询语言 : 数据行
5.SQL功能
管理,操作数据库对象:
库: 库名,库属性
表: 表名,表属性,列(列名,列属性),数据行
2.MySQL规范性存储限制
1.字符集Charset
utf8 : 最大字节长度3个.
utf8mb4 : 最大字节长度4个. 可以存储emoji表情字符.
mysql> show charset;
2.排序规则 (校对规则)
show collation;
默认是大小写不敏感.
大小写敏感：utf8mb4_general_ci
大小写不敏感：utf8mb4_bin
3.数据类型
数字类型
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位数)字符串
char(10) : 定长类型,最多10个字符,占用存储空间一定.最多存储255个字符.
varchar(10):
变长类型,最多10个字符,按需分配存储空间.
需要额外1个字符或2个字符存储字符长度
因素: 变长的字符串列,90%几率都是varchar
具体原因是什么?
节省空间,还有没有别的原因?
遗留的问题..
enum('m','f')
1 2时间类型
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会受到时区的影响二进制类型
JSON(8.0)
3.约束
1.PrimaryKey(PK): 主键
特点: 唯一+非空,一张表中只能有一个主键约束.一般是一个数字列.最好是无意义的.
2.NOT NULL 非空
特点: 不能为空,我们建议业务关键列(索引列),尽量设置成非空.
3.UNIQUE 唯一约束
特点: 不能有重复值.一般像手机号,身份证号,qq,邮箱.....
4.unsigned 数字列无符号.
特点: 必须要加载数字列后,表示数字无负数.一般适用于年龄....
4.其他属性
1.AUTO_INCREMENT 自增长
特点: 适用于ID主键列.
2.DEFAULT 默认值
特点: 使用在NOT NULL 列中,不填写值时,自动生成默认值。
3.COMMENT 注释
特点: 建议每个列都有一个注释.
10张表: -----> 每个表每个列加注释
5.库
1.增
CREATE DATABASE student CHARSET utf8mb4 COLLATE utf8mb4_bin;
2.删 (危险,不代表生产操作.)
DROP DATABASE student;
3.改
ALTER DATABASE student CHARSET utf8mb4 COLLATE utf8mb4_bin;
4.查(DQL)
mysql> show databases;
mysql> show create database oldboy;
5.规范
(1) 库名要和业务有关
(2) 库名不能有大写字母,可以有小写字符,数字,特殊符号.
为什么?
mysql在win7，win10上安装使用时，默认数据库名称，表名称都是小写，如果SQL语句中写的数据库名称为大写，自动转换成小写进行使用。
(3) 库名不能是数字开头
(4) 库名不能是预留字符
(5) 库名不要超过18个字符.
(6) 必须要设置字符集.尽量是utf8mb4.
(7) 收回所有用户的DROP权限.
6.表定义
1.增
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;
建表规范
- 表名: 不能大写字母,和业务有关,不能数字开头,长度控制在18字符以内,不能和关键字同名.
- 要设置存储引擎类型:INNODB,要设置字符集
- 列名要有意义
- 合适的,完整的,简短的数据类型.(会影响到索引的性能)
- 每个表要有主键,实在不知道怎么设置,也要找一个无关的自增长列设置为主键.
- 尽量每个列都有not null (特别是将来要做为索引的列)
- 每个列要有注释信息
复制表
- 创建一个相同表结构的表
create table teacher_bak like teacher； - 复制一张表（包括内容）
create table teacher_bak select * from teacher - 完全复制一张表
create table teacher_bak like teacher
insert into table teacher_bak select * from teacher
2.删(危险! 谨慎操作!)
mysql> drop table teacher_bak;
mysql> truncate table teacher;
面试: 请你说明 drop table truncate delete table 区别 ?
drop table : 表结构+数据(物理性删除)
truncate table : 数据(清空数据页)
delete from table: 清空数据行(逐行删除)
3.改
添加列
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'删除列
ALTER TABLE xuesheng DROP mail;
ALTER TABLE xuesheng DROP xqq;
ALTER TABLE xuesheng DROP wechat;修改
- 修改表名:
ALTER TABLE xuesheng RENAME TO student;
面试题:
上亿行的数据规划:
1. 按月归档表
2. 没用的历史表进行挪走或删除
pt-archiver
2.修改某一列的属性信息
ALTER TABLE student MODIFY xname VARCHAR(128) NOT NULL COMMENT '姓名';
- 修改列名和属性
ALTER TABLE student CHANGE xsex xgender CHAR(2) NOT NULL DEFAULT 'm' COMMENT '性别';
注意:
执行alter语句,都是需要进行锁表操作的,此时只能发生查询操作,不能做修改操作.
我们建议,alter语句,尽量在业务不繁忙期间发生.如果非得线上操作,建议使用pt-osc工具进行.
4.查
- 查看多少表
show tables ; - 查看表结构
desc teacher; - 查看创建表的记录
show create table xx;
6.DML 数据操作语言
1.insert 插入数据
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),
2.update 更新数据
SELECT * FROM student;
UPDATE student SET xname='王钢蛋' WHERE xid=6;
UPDATE student SET xname='李铁锤' WHERE xid=5;
3.delete删除数据
- 先插入
INSERT INTO student VALUES(21,'王二麻子',22,'f','921','345','上海市','2020-01-01'); - 删除
DELETE FROM student WHERE xid=21;
伪删除
添加状态列 is_del (1代表删除,0代表有效)
ALTER TABLE student ADD is_del TINYINT NOT NULL DEFAULT 0 COMMENT '1代表删除,0代表有效';delete ---> update
原语句:
delete from student where xid=20;
改为 :
update student set is_del=1 where xid=20;更改业务查询方法
原语句:
SELECT * FROM student;
改为:
SELECT * FROM student where is_del=0;
