MySQL-SQL基础应用+Infomation_Schema介绍-Day3
1、SQL 介绍
结构化查询语言
SQL标准：SQL 92 SQL 99（两者变化不大）
5.7 以后符合SQL92严格模式，通过sql_mode参数来控制
2、SQL 作用
SQL 用来管理和操作MySQL内部的对象
对象？
库：库名，库属性
表：表名，表属性，列名，记录（行），数据类型，列属性和约束
3、SQL语句的类型
DDL：数据定义语言 data definition language
DCL：数据控制语言 data control language
DML：数据操作语言 data manipulation language
DQL：数据查询语言 data query language
4、数据类型
官方文档
4.1、作用?
保证数据的规划性，让数据具有具体的含义，在列上进行控制
字符类型表
image
数值类型表
image
时间类型表
image
4.2、种类
#4.2.1、字符串
1、char（32）
定长长度为32的字符串。
存储数据时，一次性提供32字符长度的存储空间，存不满用空格填充。
2、varchar（32）
可变长度的字符串类型（最多是32位字符）。
存数据时，首先先对字符串长度判断，按需分配存储空间会单独占用一个字符长度来记录此次字符长度，按需进行存储空间分配。
超过255之后，需要两个字节长度记录字符长度。
面试题：
1、char和varchar的区别？
（1）255 65535
（2）定长（固定存储空间）变长（按需）
2、char和varchar如何选择？
（1）char类型，固定长度的字符串列，比如说手机号，身份证号，银行卡号，性别
（2）varchar类型，不确定长度的字符串，可以使用。
悬念：
为什么呢？影响到索引的高度？
3、enum 枚举类型
enum（'bj','sh','cq','hb',.......）
1 2 3 4 ..........
数据行比较多时，会影响到索引的应用
切记：数据类型禁止使用enum类型
#4.2.2、数字
1、tinyint： -128~127 也可理解为（0-255）
2、int： -2^31~2^31-1
说明：手机号是无法存储到int的。一般是使用char、varchar类型来存储收集号
====================================================
#4.2.3、时间
1、timestamp
1970-01-01 00:00:00.000000 至 2038-01-19 03:14:07.999999。
2、datetime
范围为从 1000-01-01 00:00:00.000000 至 9999-12-31 23:59:59.999999。
timestamp会受到时区的影响
====================================================
二进制
====================================================
5、表属性
存储引擎：engine
InnoDB（默认的）
字符集：eharset
字符集和排序规则:
utf8
utf8mb4 （企业中常用）
utf8和utf8mb4的区别？
utf8 中文 三个字节长度
utf8mb4 中文 四个字节长度 才是真正的utf8 支持emoji字符
排序规则（校队规则）collation
针对英文字符串大小写问题
字符集，排序规则，Unicode官方文档
6、列的属性和约束
1、**主键：primary key（PK）**
数字列、整数列、无关列、自增列、
聚集索引列？
设置为主键的列，此列的值必须非空且唯一，主键在一个表中只能有一个，但是可以有多个列一起构成。
====================================================
2、**非空：Not NULL**
列值不能为空，也是表设计的规范，尽可能将所有的列设置为非空。可以设置默认值为0
说明：
我们建议，对于普通列来讲，尽量设置not null
默认值 default ： 数字列的默认值使用0 ,字符串类型，设置为一个nil null
====================================================
3、**唯一：unique**
列值不能重复
====================================================
4、自增：auto_increment
针对数字列，顺序的自动填充数据（默认是从1开始，将来可以设定起始点和偏移量）
====================================================
5、无符号：unsigned
针对数字列，非负数。
====================================================
6、注释：comment
7、SQL语句应用
官方文档
7.1、DDL：数据定义语言
DDL
建库语法：
create table stu(
列1 属性（数据类型、约束、其他属性） ，
列2 属性，
列3 属性
)
库：
（1）建库
mysql> create database bbs charset utf8mb4;
mysql> show databases;
mysql> show create database bbs;
（2）改库
mysql> alter database bao charset utf8mb4;
（3）删库（不代表生产环境，生产环境禁止）
mysql> drop database bbs;
====================================================
表：
建表建库规范；
1、库名，表名是小写字母
为啥？
开发和生产平台会出现问题
2、不能以数字开头、特殊符号
3、不支持中划线、支持下划线
4、内部函数名不能使用
5、名字和业务功能有关（his，jf，yz，oss，erp，crm...）
（1）建表
create table oldguo (
ID int not null primary key AUTO_INCREMENT comment '学号',
name varchar(255) not null comment '姓名',
age tinyint unsigned not null default 0 comment '年龄',
gender enum('m','f','n') NOT null default 'n' comment '性别'
)charset=utf8mb4 engine=innodb;
（2）改表（重点）
在上表中添加一个手机号列+86156123298765
alter table duanyang add telnum char(14) not null unique comment '手机号';
（3）删表上述telnum列
alter TABLE duanyang DROP telnum;
（4） 查看列的信息（重点）
DESC duanyang;
====================================================
练习：
-- 在上表中加一个状态列state,非空，默认值为1.
alter table duanyang add state tinyint unsigned not null default 1 comment '状态';
-- 删除state列（不代表生成操作）
alter TABLE duanyang DROP state;
-- 查看列的信息
DESC duanyang;
-- 在name后添加QQ列 varchar(255)
alter TABLE duanyang add qq VARCHAR(255) NOT NULL UNIQUE COMMENT '在name后面添加qq列' AFTER name;
-- 在name之前添加wechat列
alter TABLE duanyang add wechat VARCHAR(255) NOT NULL UNIQUE COMMENT '在name之前添加微信列' AFTER id;
-- 在首页列上添加 学号：sid（linux_00001）
ALTER TABLE duanyang add sid VARCHAR(255) not null UNIQUE COMMENT '学生好' FIRST;
-- 修改name数据类型的属性
ALTER TABLE duanyang MODIFY name VARCHAR(128) NOT NULL ;
-- 将name改为sname 数据类型改为CHAR类型
ALTER TABLE duanyang CHANGE name sname CHAR(1) NOT NULL DEFAULT 'n';
-- 删除以上添加的列（不代表生产环境）
mysql > show create table duanyang;
USE duanyang; #切换到duanyang表；
DESC duanyang; #查看表列信息
SHOW CREATE TABLE duanyang; #查看建表的语句
CREATE TABLE duanyan LIKE duanyang; #创建一个相同表结构空表
ALTER TABLE duanyang DROP sid;
ALTER TABLE duanyang DROP wechat;
ALTER TABLE duanyang DROP gender;
ALTER TABLE duanyang DROP telnum;
ALTER TABLE duanyang DROP state;
====================================================
7.2、DML 数据操作语言
DML
-- INSERT
-- 最简单的方法插入数据
INSERT INTO duanyang VALUES(1,'1454028762','1454028762','18');
-- 最规范的方法插入数据
insert into duanyang(wechat,qq,age) values ('14540287621','14540287621','100');
-- 查看表信息
DESC duanyang;
-- 查看表数据（不代表生产环境）
SELECT * FROM duanyang;
-- UPDATE(注意谨慎操作)
UPDATE duanyang SET qq='654866250' WHERE id=1;
-- DELETE
DELETE FROM duanyang WHERE id=1;
====================================================
需求：将一个大表格全部数据清空
DELETE FROM duanyang;
TRUNCATE TABLE duanyang;
DELETE 和 TRUNCATE 区别
1、DELETE 逻辑逐行删除，不会降低自增长得起始值。
效率很低，碎片较多，会影响到性能
2、TRUNCATE，属于物理删除，将表段的区进行清空，不会产生碎片，性能较高。
生产需求：使用update替代delete，进行伪删除
1、添加状态列state（0代表存在，1代表不存在）
ALTER TABLE oldguo ADD state TINYINT NOT NULL DEFAULT 0 ;
2. 使用update模拟delete
DELETE FROM oldguo WHERE id=6;
替换为
UPDATE oldguo SET state=1 WHERE id=6;
SELECT * FROM oldguo ;
3. 业务语句修改
SELECT * FROM oldguo ;
改为
SELECT * FROM oldguo WHERE state=0;
====================================================
案例
按需求创建一下表结构:
use school
student ：学生表
sno： 学号
sname：学生姓名
sage： 学生年龄
ssex： 学生性别
teacher ：教师表
tno： 教师编号
tname：教师名字
course ：课程表
cno： 课程编号
cname：课程名字
tno： 教师编号
score ：成绩表
sno： 学号
cno： 课程编号
score：成绩
====================================================
-- 项目构建
mysql > create database school charset utf8mb4;
USE school
-- 创建seacher表
create table student (
sno int not null primary key AUTO_INCREMENT comment '学号',
sname varchar(255) not null comment '姓名',
sage tinyint unsigned not null default 0 comment '年龄',
ssex enum('m','f','n') NOT null default 'n' comment '性别'
)charset=utf8mb4 engine=innodb;
-- 创建teacher表
create table teacher (
tno int not null primary key AUTO_INCREMENT comment '教师编号',
tname varchar(255) not null comment '教师姓名'
)charset=utf8mb4 engine=innodb;
-- 创建course 课程表
create table course (
cno int not null primary key AUTO_INCREMENT comment '课程编号',
cname varchar(255) not null comment '教师名字',
tno int not null comment '教师编号'
)charset=utf8mb4 engine=innodb;
-- 创建score 成绩表
create table score (
sno int not null comment '学号',
cno int not null comment '课程编号',
score TINYINT not null DEFAULT 0 COMMENT '成绩'
)charset=utf8mb4 engine=innodb;
====================================================
SELECT * FROM course;
insert into course(cno,cname,tno)
values
('1001','linux','101'),
('1002','python','102'),
('1003','mysql','103');
====================================================
SELECT * FROM score;
INSERT INTO score(sno,cno,score)
VALUES
(1,1001,80),
(1,1002,59),
(2,1002,90),
(2,1003,100),
(3,1001,99),
(3,1003,40),
(4,1001,79),
(4,1002,61),
(4,1003,99),
(5,1003,40),
(6,1001,89),
(6,1003,77),
(7,1001,67),
(7,1003,82),
(8,1001,70),
(9,1003,80),
(10,1003,96);
====================================================
show tables;
SELECT * FROM student;
INSERT INTO student(sno,sname,sage,ssex)
VALUES (1,'zhang3',18,'m');
INSERT INTO student(sname,sage,ssex)
VALUES
('zhang4',18,'m'),
('li4',18,'m'),
('wang5',19,'f'),
('zh4',18,'m'),
('zhao4',18,'m'),
('ma6',19,'f'),
('oldboy',20,'m'),
('oldgirl',20,'f'),
('oldp',25,'m');
====================================================
show tables;
SELECT * FROM teacher;
INSERT INTO teacher(tno,tname)
VALUES
(101,'oldboy');
INSERT INTO teacher(tname) VALUES
('hesw'),
('oldguo');
====================================================
