day 03(SQL基础应用+Information_Schem

1、SQL介绍

结构化查询语言
SQL标准：SQL92     SQL99
5.7版本以后符合SQL92标准的严格模式
通过SQL_MODE来控制

2、SQL作用

SQL 用来管理和操作MySQL内部的对象
对象？
库：库名，库属性 
表：表名，表属性，列，记录，列属性，约束

3、SQL语句的类型

DDL：数据定义语言    data definition languague
DCL：数据控制语言    data  control  languague
DML：数据操作语言    data  manipulation language
DQL：数据查询语言    data query languague

4、数据类型

4.1、作用

控制数据的规范性，让数据有具体含义，在列上进行控制。

4.2、种类

4.2.1、字符串

char(32)：定长长度为32的字符串。存储数据时，一次性提供32位长度的存储空间，存不满用空格填充
varchar(32)：可变长度的字符串类型。存数据时，首先进行字符串长度判断，按需分配存储空间
             单独占用一个字符长度来记录此次的字符长度，字符长度超过255后需要两个字节长度记录字符长度。
enum：枚举类型
enum('bj','sh','sz','cq',...........)
数据行较多时，会影响到索引的应用
注意：数字类禁止使用enum类型

面试题：
1.char和varchar的区别？
  （1）255   65535
  （2）定长（固定存储空间）  可变长度（按需进行分配）
2.char和varchar如何选择？
    （1）char类型：固定长度的字符串列，比如手机号，身份证号，银行卡号，性别等
    （2）varchar：不确定长度的字符串选择varchar.

4.2.2、数字

1、tinyint（0到255   -128到127 ）
2、int（-2^31到2^31-1）

4.2.3、时间

timestamp
    1970-01-01 00:00:00.000000 至 2038-01-19 03:14:07.999999。
datetime
    范围为从 1000-01-01 00:00:00.000000 至 9999-12-31 23:59:59.999999。

5、表属性

存储引擎：engine = InnoDB
字符集：charset = utf8mb4

utf8            中文三个字节长度
utf8mb4         中文四个字节长度    才是真正的utf8    支持emoji字符


排序规则（校对规则）
 针对英文字符串大小写问题

6、列的属性和约束

1)主键：primary key(PK)   将来的值必须唯一，非空，数字列，整数列，无关列，自增的，聚集索引列
       是一种约束，也是一种索引，在一张表中只能有一个主键。


2)非空 ： Not NULL
     我们建议，对于普通列来讲，尽量设置not null
默认值 default：数字列的默认值使用0，字符串类型，设置为nil null

3)唯一：unique  不能重复

4)自增：auto_increment  针对数字列，自动生成顺序值

5)无符号：unsigned  针对数字列

6)注释：comment

7、SQL语句应用

7.1、DDL：数据定义语言

7.1.1、库

1)建库
  create database wanwan charset utf8mb4;
  show databases;
  show create database wanwan;

2)改库
   alter database wanwan2 charset utf8mb4;
   show create database wanwan2;
3)删库
   drop database wanwan2;

7.1.2、表

建表建库规范：
1、库名、表名是小写字母
    为啥？
    开发和生产平台可能会出现问题。
2、不能以数字开头
3、不支持-   支持_
4、内部函数名不能使用
5、名字和业务功能有关（his,jf,yz,oss,erp,crm......）

1）建表
create table wanwan2 (
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）改表
1、改表结构
例子：

添加一列telnum(手机号)
alter table 学生表 add telnum char(11) not null unique comment '手机号';

添加一列state(状态列），默认值为1
alter table 学生表 add state tinyint unsigned not null default 1 comment '状态列';

在name列后添加一列 qq  
alter table 学生表 add qq varchar(255) not null unique comment 'qq'after name;

在name之前添加一列wchat(微信）
alter table 学生表 add wechat varchar(255) not null unique comment 'weixin'after id;

在首列添加一列sid(学生号）
alter table 学生表 add sid varchar(255) not null unique comment '学生号' first;

2、查询表
desc 学生表;

3、删除列 
use oldboy;                       ----------（切到库里）
desc 学生表                       -----------（查看表列信息）
show create table 学生表;         --------------（查看建表语句）
create table stu like 学生表;     --------------（创建一个相同表结构的，空表）
alter table 学生表 drop sid;
alter table 学生表 drop qq;
alter table 学生表 drop wechat;
alter table 学生表 drop telnum;

7.2、DML 数据操作语言

7.2.1、insert

----简单录入数据
desc 学生表;
insert into 学生表 values(1,'wanwan','526434934',26);
----规范录入数据
insert into 学生表(name,qq,age) values('oldboy','2222222',50);
----查询表数据（不代表生产环境）
select * from 学生表;

7.2.2、update(注意谨慎操作)

---更新id为3的qq号
update 学生表 set qq='123456' where id=3;
----查看表数据
select * from 学生表;

7.2.3、delete

delete  from 学生表 where id=3;
select * from 学生表;


---需求： 将一个大表数据全部清空
truncate table 学生表;


delete和truncate区别
1. delete逻辑逐行删除，不会降低自增长的起始值，效率很低，碎片较多，会影响到性能。
2. truncate属于物理删除，将表段中的区进行清空，不会产生碎片，性能较高。


---需求：使用update替代delete，进行伪删除
1. 添加状态列state（1代表存在，0代表删除）
alter table 学生表 add state tinyint unsigned  not null default 1 comment '状态列';
update 学生表 set state=0 where id=2; 
select * from 学生表 where state=0 ;