MySQL 基础入门_04SQL基础

1. SQL介绍

• 1.1 SQL的定义
  结构化查询语言，全称是Structured Query Language
• 1.2 SQL标准
  SQL89 SQL92 SQL99 SQL03 SQL05
• 1.3 SQL_MODE
  除数为零
  日期
  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版本)
• 2.4 列的其它属性
• 2.4.1约束
  2.4.1.1 PrimaryKey(PK): 主键
  特点: 唯一+非空,一张表中只能有一个主键约束.一般是一个数字列.最好是无意义的.
  2.4.1.2 NOT NULL 非空
  特点: 不能为空,我们建议业务关键列(索引列),尽量设置成非空.
  2.4.1.3 UNIQUE 唯一约束
  特点: 不能有重复值.一般像手机号,身份证号,qq,邮箱…
  2.4.1.4 unsigned 数字列无符号.
  特点: 必须要加载数字列后,表示数字无负数.一般适用于年龄…
• 2.5 特殊属性
• 2.5.1 AUTO_INCREMENT 自增长
  特点: 适用于ID主键列.
• 2.5.2 DEFAULT 默认值
  特点: 使用在NOT NULL 列中,不填写值时,自动生成默认值。
• 2.6.3 COMMENT 注释
  特点: 建议每个列都有一个注释.
  案例：10张表: -----> 每个表每个列加注释.

3. DCL 数据控制语言

• 3.1 给用户权限 grant
  GRANT 权限1，权限2… ON 数据库名.表名 TO ‘用户名@主机名’;

grant create,alter,inster,update,selecton test.* to 'user1@localhost';

• 3.2 收回权限 revoke
  REVOKE 权限1，权限2… ON 数据库名.表名 FROM ‘用户名@主机名’;

revoke all on test.* from 'user1@localhost';

4. DDL 数据库操作语言

• 4.1 库定义

help create databases

• 规范
  (1) 库名要和业务有关
  (2) 库名不能有大写字母,可以有小写字符,数字,特殊符号.
  CREATE DATABASE OLDGUO CHARSET utf8mb4 COLLATE utf8mb4_bin;
  (3) 库名不能是数字开头
  (4) 库名不能是预留字符
  (5) 库名不要超过18个字符.
  (6) 必须要设置字符集.尽量是utf8mb4.
  (7) 收回所有用户的DROP权限.
• 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 查(DQL)

mysql> show databases;
mysql> show create database oldboy;

• 4.2 表定义
• 建表规范:
  (1) 表名: 不能大写字母,和业务有关,不能数字开头,长度控制在18字符以内,不能和关键字同名.
  (2) 要设置存储引擎类型:INNODB,要设置字符集
  (3) 列名要有意义:
  合适的,完整的,简短的数据类型.(会影响到索引的性能)
  每个表要有主键,实在不知道怎么设置,也要找一个无关的自增长列设置为主键.
  尽量每个列都有not null (特别是将来要做为索引的列)
  每个列要有注释信息.
• 4.2.1 创建表

#教师表: 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;

#创建表数据
INSERT INTO 
teacher(tid,tname,tage,tsex,tyear,txl,tstar)
VALUES
('t0001',  'oldboy' ,48    ,'m',    20   ,'本科', 10),
('t0002',  'oldxu'  ,28    ,'m',    6    ,'本科',  6),
('t0003',  'oldq'   ,30    ,'m',    6    ,'本科',  6),
('t0004',  'oldguo' ,32    ,'m',    10   ,'本科',  6)