Day03 SQL应用-DDL（create、drop、alter）、DML（inster、update、delete）
1. SQL介绍
SQL全称是“结构化查询语言（Structured Query Language）”。
SQL语言在关系型数据库中基本上是通用的，只有少许细微偏差。
之所以能够通用，是因为SQL标准：SQL 92、SQL 99
5.7版本之前，没有严格按照SQL标准来执行，这样可能会出现不兼容的情况出现。
到了5.7版本后，加入SQL 92的SQL_Mode严格模式，杜绝了出现不兼容的情况出现。
2. SQL作用
SQL的基本作用是专门用来管理和操作MySQL内部的对象
那么什么是对象呢？
就是数据库里面的“库”和“表”。
库的属性：库名、库属性
表的属性：表名、表属性、列、记录（行）、列属性、约束
3. SQL语句的类型
常用的为以下4类：
DDL（Data Definition Language）：数据模式定义语言
DCL（Data Control Language）：数据控制语言
DML（Data Manipulation Language）：数据模式操作语言
DQL（Data Query Language）：数据模式查询语言
4. 数据类型
4.1 数据类型的基本作用
控制数据的规范性，让数据有具体含义，它是在列上面进行控制的。
4.2 数据类型的种类（常用）
4.2.1 字符串
char：固定长度
char(32)：固定长度32个字符。存储数据时，一次性提供32字符长度的存储空间，如果存不满，用空格代替，直到32字符长度用完为止。
varchar：可变长度
varchar(32)：可变长度的32个字符长度。存数据时，首先进行字符串长度判断，比如需存储32个字符串，只会分配32个字符长度的存储空间，并且会单独占用一个字节来记录此次的字符长度。也就是32+1。
字符长度超过255之后，需要2个字节长度记录字符长度。
面试题：（重点）
简单版：
1. char和varchar的区别？
参考上面的就行。
char(255)：定长（固定存储空间）
varchar(65535)：变长（按需求分配）
困难版：
2. char和varchar如何选择？
（1）char类型：固定长度的字符串列，如手机号、身份证号、银行卡号、性别等。
（2）varchar类型：不确定长度的字符串，可以使用。为什么呢？
会影响到索引的高度。
enum：枚举类型
enum('bj','sh','cq','hb',……)#值只能是括号里面的这些
定义完值之后，会自动产生枚举下标（下标索引）：1 2 3 4 ……，分别对应括号里面的值。
在数据行比较多时，会影响到索引的应用
注意：数字类禁止使用
4.2.2数字（不需要定义长度）
1. tinyint
最多存储3位数（0-255）
2. int
最多存储10位数（-2^31-2^31-1）
4.2.3 时间
1. timestamp
1970-01-01 00：00:00.000000 至 2038-01-19 03:14：07.9999999
2. datetime
1000-01-01 00:00:000000 至 9999-12-31 23:59:59 .999999
4.2.4 二进制
5. 表属性
5.1.1 存储引擎设定
engine
engine = InnoDB
5.1.2 字符集设定
charset
charset = utf8mb4
utf8与utf8mb4的区别：
utf8 存储中文是 3个字节长度。
utf8mb4存储中文是4个字节长度，才是真正的utf8。
8.0版本的数据库以后默认就是utf8mb4。
utf8mb4还支持emoji字符（表情字符）。
而且每个字符及都会有一个排序规则（校对规则，collation），主要是针对英文字符串大小写问题。
6. 列的属性和约束
属性：
（1）主键（primary key\PK）：
值必须是唯一的，而且不能为空。
数字列、整数列、无关列、自增的，比较适合设置主键。
凡是设置了主键的，都为聚集索引列。
算是一种约束，也是一种索引类型，在一个表中，只能有一个主键。
主键一定要在创建表之前就设定好。
思考什么是聚集索引列？什么是无关列？
（2）非空（Not NULL）：
不能为空。建议：对于普通列来讲，尽量设置为非空（not null）
（3）默认值（default）：
数字列的默认值使用0。字符串类型，设置为null或nil
（4）唯一（unique）：
不能重复，但可以为空，可以有多个空值，它不会对空值进行匹配
（5）自增（auto_increment）
针对数字列，自动生成顺序值。如123456,13579,2468 10
（6）无符号（unsigned）
针对数字列
（7）注释（comment）
描述
（8）默认（default）
7. SQL语句应用
7.1 DDL：数据定义语言（对库、表定义。建库、改库、删库）
定义数据库：
建库
3306 [(none)]>create database oldguo charset utf8mb4;
查库（不算DDL类型的，查库操作是属于DQL类型的）
3306 [(none)]>show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| oldguo |
| performance_schema |
| sys |
| world |
| zhihu |
+--------------------+
7 rows in set (0.12 sec)
3306 [(none)]>show create database oldguo;
+----------+--------------------------------------------------------------------+
| Database | Create Database |
+----------+--------------------------------------------------------------------+
| oldguo | CREATE DATABASE `oldguo` /*!40100 DEFAULT CHARACTER SET utf8mb4 */ |
+----------+--------------------------------------------------------------------+
1 row in set (0.00 sec)
改库
改字符集：
3306 [(none)]>show create database oldboy1;
+----------+--------------------------------------------------------------------+
| Database | Create Database |
+----------+--------------------------------------------------------------------+
| oldboy1 | CREATE DATABASE `oldboy1` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+--------------------------------------------------------------------+
1 row in set (0.00 sec)
3306 [(none)]>alter database oldboy1 charset utf8mb4;
Query OK, 1 row affected (0.00 sec)
3306 [(none)]>show create database oldboy1;
+----------+---------------------------------------------------------------------+
| Database | Create Database |
+----------+---------------------------------------------------------------------+
| oldboy1 | CREATE DATABASE `oldboy1` /*!40100 DEFAULT CHARACTER SET utf8mb4 */ |
+----------+---------------------------------------------------------------------+
1 row in set (0.00 sec)
删库（生产中禁止）：
3306 [(none)]>drop database oldboy1;
Query OK, 0 rows affected (0.03 sec)
7.1.2 表（建表、改表、删表）
建表建库规范：
1、库名与表名必须是小写字母，因为开发和生产平台不同，可能会出问题。如windows系统不区分大小写，Linux中严格区分大小写。
2、不能以数字和特殊符号开头。
3、不支持- 支持_。
4、不能有内部函数名（如表名为inta，database）。
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;
如果不知道如何写SQL语句，就使用help create database;
（2）改表
1. 改表结构（添加列、删除列）
添加列
例子 1：
-- 在oldguo表中添加一个手机号17612093732
SQL语句：
alter table oldguo add telnum char(11) not null unique comment '手机号'；
例子 2：
-- 在上表中加一个状态列state,非空，默认值为1.
SQL语句：
alter table oldguo add state tinyint unsigned not null default 1 comment '状态列';
删除列（不代表生产操作，生产环境禁用）
例子 3：
-- 删除state列
SQL语句：
alter table oldguo drop state;
注意：ALTER TABLE会对生产环境造成比较大的影响，添加列的时候，会把全表进行锁定，当表锁定后，除了查询，其他什么操作都不能用。
ALTER TABLE应用场景：
（1）业务不繁忙期间。
（2）online-DDL（pt-osc），在线更改，不锁表。（自行扩展）
查列
-- 查询所有列
desc oldguo;
指定位置添加列
-- 在name后添加qq列
alter table oldguo add qq varchar(255) not null unique comment 'QQ' after name;
-- 在name前添加wechat列
alter table oldguo add wechat varchar(255) not null unique comment '微信' after ID;
-- 在首列添加学号列：sid(linux_58_00001)
alter table oldguo add sid varchar(255) not null unique comment '学号' first;
-- 修改name数据类型属性（修改属性：modify）
alter table oldguo modify name varchar(128) not null;
注意：用modify改变属性，属性会完全变成SQL语句中定义的属性,原表中的属性将不会存在。
-- 将gender改为gg 数据类型改为char类型：change
ALTER TABLE oldguo CHANGE gender g CHAR(1) NOT NULL DEFAULT 'n' ;
批量更改,在每张表上加个列
alter table 表名 add state tinyint not null default 1;
select concat("alter table",table_name," add state tinyint not null default 1") form information_schema.tables where table_schema='world';
-- 删除刚才所有添加某1列
alter table oldguo drop sid;
注意：删除列时，会把列的数据也全部删掉
查看表语句
use oldboy;
desc oldguo; #查看表的列信息
show create table oldguo; #查看具体建表语句
-- 创建相同表结构的空表
use oldboy;
create table oguo like oldguo;
（3）删表
drop table table_name;
7.2 DML 数据操作语言（针对数据行的操作）（insert、update、delete）
7.2.1 insert
（1）最简单的插入方法
insert into oldguo values(1,'oldguo','1184964356',18);
（2）最规范的插入方法（重点记忆）
insert into oldguo(name,qq,age) values ('oldboy','74110',49);
（3）查询全表数据（不代表生产操作）
select * from oldgou;
7.2.2 update（更新数据行的值）
select * from oldguo;
update oldguo set qq='123456' where id=5;#改指定行的值
update oldguo set qq='123456';#改全表qq列的值
7.2.3 delete（删除行）
delete from oldguo where id=2;#删除指定行
delete from oldguo;#删除这个表中的所有数据
需求：将一个大表全部数据清空（delete、truncate）
delete from oldguo;#逐行删除，速度慢，而且不会删除自增长的值(逻辑删除)
truncate table oldguo;#（物理删除）删除表段里面所有的区（所有数据）
面试题：
delete和truncate的区别
1. delete 逻辑逐行删除，不会降低自增长的起始值,效率很低，而且碎片比较多，会影响到性能。如果有经常性的delete操作，也会产生碎片。
2. truncate，属于物理删除，原理是将表段中的区清空，不会产生碎片，性能较高。但是truncate只能进行“全表删除”
需求：使用update替代delete，进行"伪删除(查不到，没有真正删除)"
1. 添加状态列 (1)存在 (0)代表删除
alter table oldguo add state tinyint not noull default 0 ;
2. 使用update模拟delete
delete from oldguo where id=6;
替换为
update oldguo set state=1 where id=6;
3. 业务句修改
select * from oldguo;
改为
select * from set state=1 where state=0;
作业 ：
1. 按需求创建一下表结构:
use school
student ：学生表
sno： 学号
sname：学生姓名
sage： 学生年龄
ssex： 学生性别
teacher ：教师表
tno： 教师编号
tname：教师名字
course ：课程表
cno： 课程编号
cname：课程名字
tno： 教师编号
score ：成绩表
sno： 学号
cno： 课程编号
score：成绩
2.录入数据
INSERT INTO student(sno,sname,sage,ssex)
VALUES (1,'zhang3',18,'m');
INSERT INTO student(sno,sname,sage,ssex)
VALUES
(2,'zhang4',18,'m'),
(3,'li4',18,'m'),
(4,'wang5',19,'f');
INSERT INTO student
VALUES
(5,'zh4',18,'m'),
(6,'zhao4',18,'m'),
(7,'ma6',19,'f');
INSERT INTO student(sname,sage,ssex)
VALUES
('oldboy',20,'m'),
('oldgirl',20,'f'),
('oldp',25,'m');
INSERT INTO teacher(tno,tname) VALUES
(101,'oldboy'),
(102,'hesw'),
(103,'oldguo');
DESC course;
INSERT INTO course(cno,cname,tno)
VALUES
(1001,'linux',101),
(1002,'python',102),
(1003,'mysql',103);
DESC sc;
INSERT INTO sc(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);
查看结果
SELECT * FROM student;
SELECT * FROM teacher;
SELECT * FROM course;
SELECT * FROM sc;
7.3 DQL （预习）
select
show
