开源数据库MySQL-DBA运维实战第2章-SQL1（名词解释、DDL库、数据类型）超长文!!超级详细!!!

本文分三大章节对SQL的语言分类和名词解释以及DDL库进行了详细解释。

一、SQL语言分类

SQL（Structured Query Language 即结构化查询语言）

SQL语言主要用于存取数据、查询数据、更新数据和管理关系数据库系统，SQL语言由IBM开发。
分类

• DDL语句（data definition language）： 数据库定义语言： 数据库、表、视图、索引、存储过程，例如CREATE DROP ALTER

• DML语句（data manipulation language）：数据库操纵语言： 插入数据INSERT、删除数据DELETE、更新数据UPDATE

• DQL语句（Data Query Language ） :数据库查询语言： 查询数据SELECT

• DCL语句（Data Control Language）： 数据库控制语言： 例如控制用户的访问权限GRANT、REVOKE

二、名词解释

1.数据库服务器

运行着数据库应用程序的设备:
DELL R760+CENTOS+Mysql
硬件+系统软件+MYSQL软件

2.数据库

默认数据库的查看：mysql>show databases

• information_schema： 虚拟库，主要存储了系统中的一些数据库对象的信息，例如用户表信息、列信息、权限信息、字符信息等
• performance_schema：
  主要存储数据库服务器的性能参数
  1.提供进程等待的详细信息，包括锁、互斥变量、文件信息；
  2.保存历史的事件汇总信息，为提供MySQL服务器性能做出详细的判断；
  3.对于新增和删除监控事件点都非常容易，并可以随意改变mysql服务器的监控周期
• mysql： 授权库，主要存储系统用户的权限信息
• sys： 主要存储数据库服务器的性能参数
  SYS database中，可以获取mem page、io 、latch/mutex/lock等各种性能数据，方便做peformance turning 和 troubleshooting。比如可以方便获取2个sql发生 lock block，用户占用的资源等信息。
• 创建需要的业务主库…例如上图中的Test数据库
  表(EXCEL)的管理单元

3.表：

记录的管理单元

4.记录（行）

信息的载体，字段的管理单元 例如：张三，男，23，云计算工程师，月薪25K

5.字段(列)

字段名，字段类型（长度），字段约束组成（可选）
注意：姓名，文字，不能为空

6.类型

字符，数字，日期

7.约束

唯一，不能为空，自动增长

三、DDL

DDL-库介绍

定义库

• 创建业务数据库
  语法：

CREATE DATABASE 数据库名;
CREATE DATABASE discuz;

• 数据库名要求
  区分大小写，唯一性，不能使用关键字如 create select不能单独，使用数字和特殊符号“如-"
  正常的：用拼音或单词即可。

选择/进入数据库

USE 数据库名
SELECT database();   调用函数，查询当前库

删除数据库

DROP DATABASE 数据库名;

系统中的位置/var：b/mysql/ ，该位置为数据库的实体，可以用于清理和备份。

数据类型

1.数值类型

• 整数类型 int
• 浮点数类型 float:小数

2.字符串类型

• 字符系列 CHAR和VARCHAR: char的长度不可变,varchar的长度可以增加。
• 枚举类型 ENUM:单选
• 集合类型 SET:多选

3.时间和日期类型

• 年 YEAR
• 日期 DATE
• 时间 TIME
• 日期和时间 DATETIME

4.数据类型(扩展)

4.1数值类型：

整数类型 INT(以长度区分) :

• TINYINT
• SMALLINT
• MEDIUMINT
• BIGINT

阅读资料

1、bigint(2^64)
从 -2^63 (-9223372036854775808) 到 2^63-1 (9223372036854775807) 的整型数据（所有数字），无符号的范围是0到
18446744073709551615。一位为 8 个字节。
2、int（2^32）
一个正常大小整数。有符号的范围是-2^31 (-2,147,483,648) 到 2^31 - 1 (2,147,483,647) 的整型数据（所有数字），无符号的范围是0到4294967295。一位大小为 4 个字节。
int 的 SQL-92 同义词为 integer。
3、mediumint(2^24)
一个中等大小整数，有符号的范围是-8388608到8388607，无符号的范围是0到16777215。 一位大小为3个字节。
4、smallint(2^16)
一个小整数。有符号的范围是-2^15 (-32,768) 到 2^15 - 1 (32,767) 的整型数据，无符号的范围是0到65535。一位大小为 2 个字节。MySQL提供的功能已经绰绰有余，而且由于MySQL是开放源码软件，因此可以大大降低总体拥有成本。
5、tinyint(2^8)
有符号的范围是-128 - 127，无符号的范围是 从 0 到 255 的整型数据。一位大小为 1 字节。

浮点数类型 FLOAT DOUBLE:注意小数点
定点数类型 DEC
位类型 BIT

4.2字符串类型：

• CHAR系列 CHAR VARCHAR
• TEXT系列 TEXT ----TINYTEXT MEDIUMTEXT LONGTEXT ( ASCII)
• BLOB 系列 BLOB —TINYBLOB MEDIUMBLOB LONGBLOB 特殊符号
• BINARY系列 BINARY VARBINARY
• 枚举类型： ENUM----65536个元素----单选择题
• 集合类型： SET-----64个元素----多选择题.

4.3时间和日期类型：

DATE TIME DATETIME TIMESTAMP YEAR

5类型测试

5.1 整数类型测试：tinyint,in-

作用：用于存储用户的年龄、游戏的Level、经验值等。

LAB1:（int，tinyint的最大值）

前言
TINYINT有符号型最大127
INT有符号型最大2147483647
1 创建一个表

mysql> create table test1( 
tinyint_test tinyint,  //注意TINYINT类型
int_test int );

2查询表结构
mysql> desc test1;

mysql> desc test1;
+--------------+------------+------+-----+---------+-------+
| Field        | Type       | Null | Key | Default | Extra |
+--------------+------------+------+-----+---------+-------+
| tinyint_test | tinyint(4) | YES  |     | NULL    |       |
| int_test     | int(11)    | YES  |     | NULL    |       |
+--------------+------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

Field字段名称（列名）
TYPE字段类型（字？数？日？）
NULL KEY DEFAULT EXTRA 略

3插入数值

• 插入合法数值

mysql> insert into test1 values (111,111);
Query OK, 1 row affected (0.09 sec)

• 查询表内容

select * from test1

• 插入非法数值

错误的示例：大于127报错

mysql> insert into test1(tinyint_test) values(128);
ERROR 1264 (22003): Out of range value for column 'tinyint_test' at row 1

正确的示例：

mysql> insert into test1(tinyint_test) values(127);
TINYINT有符号型最大127

• 插入合法数值

mysql> insert into test1(int_test) values(2147483647);
Query OK, 1 row affected (0.05 sec)

• 插入非法数值

mysql> insert into test1(int_test) values(2147483648);
ERROR 1264 (22003): Out of range value for column 'int_test' at row 1
INT有符号型最大2147483647

LAB2：（无符号unsigned）

若数值无符号，就只能输入正值，不能输入负值
tinyint无符号最多可以存储255个字符 可以理解为8位二进制文件。原本符号占一位之后剩下7位，最大值127

1 创建一个表

mysql> create table test2(
 tinyint_test tinyint unsigned,		      
int_test int unsigned
);
Query OK, 0 rows affected (0.00 sec)  //约束条件unsigned限定只能存正值（无符号）

2 查询表结构

mysql> desc test2;
+--------------+---------------------+------+-----+---------+-------+
| Field        | Type                | Null | Key | Default | Extra |
+--------------+---------------------+------+-----+---------+-------+
| tinyint_test | tinyint(3) unsigned | YES  |     | NULL    |       | 
| int_test     | int(10) unsigned    | YES  |     | NULL    |       | 
+--------------+---------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

3 插入数据
插入合法数据

	mysql> insert into test2(tinyint_test) values(255);
Query OK, 1 row affected (0.06 sec)

插入合法数据

mysql> insert into test2(int_test) values(2147483648);
Query OK, 1 row affected (1.87 sec)

插入非法数据

mysql> insert into test2 values(-20,-20);
ERROR 1264 (22003): Out of range value for column 'tinyint_test' at row 1

注意：mysql和mariadb不同。mysql提示输入错误,mariadb会输入0到表中。但结果是肯定的，无符号只能输入正值*

LAB3：（整数型，长度可变）

前言

插入大于INT宽度限制的值，仍然可以存储。但不能超过上限2147483647
INT整形的宽度仅为显示宽度，不是限制。因此建议整形无须指定宽度。字符型需要使用宽度

1 创建一个表

mysql> create table t1 (
 id1 int,
 id2 int(6)
 );

2查询表结构

mysql> desc t1;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id1   | int(11) | YES  |     | NULL    |       |
| id2   | int(6)  | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+

3插入数据

mysql> insert into t1 values(22,22222222);
Query OK, 1 row affected (0.01 sec)

4查询数据

mysql> select * from t1;
插入大于INT宽度限制的值，仍然可以存储。
但不能超过上限2147483647

LAB4：（零填充zerofill）

前言

zerofill 自动填充0

1 创建一个表

mysql> create table t2 (
 id1 int zerofill,
id2 int(6) zerofill
);
Query OK, 0 rows affected (0.05 sec)

2查询表结构

mysql> desc t2;
+-------+---------------------------+------+-----+---------+-------+
| Field | Type                      | Null | Key | Default | Extra |
+-------+---------------------------+------+-----+---------+-------+
| id1   | int(10) unsigned zerofill | YES  |     | NULL    |       |
| id2   | int(6) unsigned zerofill  | YES  |     | NULL    |       |
+-------+---------------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

3插入数据

mysql> insert into t2 values(2,2);
Query OK, 1 row affected (0.01 sec)

4查询表内容

mysql> select * from t2;
+------------+--------+
| id1        | id2    |
+------------+--------+
| 0000000002 | 000002 |
+------------+--------+
1 row in set (0.00 sec)

5.2类型测试：float

作用：用于存储用户的身高、体重、薪水等
浮点数和定点数都可以用类型名称后加(M,D)的方式来表示，(M,D)表示一共显示M位数字（总数位+小数位），
其中D位于小数点后面，M和D又称为精度和标度。

LAB1：（浮点数float）

• 浮点数float类型测试

1 创建一个表

mysql> create table test4(float_test float(5,2));//一共5位，小数占2位，整数3位
Query OK, 0 rows affected (0.00 sec)

2 查询表结构

mysql> desc test4;
+------------+------------+------+-----+---------+-------+
| Field      | Type       | Null | Key | Default | Extra |
+------------+------------+------+-----+---------+-------+
| float_test | float(5,2) | YES  |     | NULL    |       | 
+------------+------------+------+-----+---------+-------+
1 row in set (0.00 sec)

其中float(5,2)中 5是整数加小数的总长,2是小数长度。整数意味只有3位长度。

3 插入合法数据，非法数据

mysql> insert into test4 values (10.2), (70.243), (70.246);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

4 查询表内容

mysql> select * from test4;
+------------+
| float_test |
+------------+
|      10.20 | 
|      70.24 | 
|      70.25 | 
+------------+
3 rows in set (0.00 sec)

5 插入非法数据

mysql> insert into test4 values (1111.2);
ERROR 1264 (22003): Out of range value for column 'float_test' at row 1

LAB2：（精准小数decimal）

定点数decimal类型测试：
定点数在MySQL内部以字符串形式存储，比浮点数更精确，
适合用来表示货币等精度高的数据。
decimal在不指定精度时，默认的整数位为10，默认的小数位为0**
1 创建一个表

mysql>  create table test5(decimal_test decimal(5,2));
总长5位，小数占2位

2 插入数据

mysql> insert into test5 values (70.245);
Query OK, 1 row affected, 1 warning (0.05 sec)

注意：有警告超长部分不记录。会四舍五入
mysql> select * from test5;
3 请思考如何创建整数9位，小数5位的数据类型

区别

三者的区别介绍
float:浮点型，含字节数为4，32bit，数值范围为-3.4E38~3.4E38（7个有效位,小数点占一位）
double:双精度实型，含字节数为8，64bit数值范围-1.7E308~1.7E308（15个有效位）
decimal:数字型，128bit，常用于银行帐目计算。（28个有效位）

5.3时间和日期类型测试：year、date、time、datetime、timestamp

作用：用于存储用户的注册时间，文章的发布时间，文章的更新时间，员工的入职时间等

LAB1：（日期，时间）

日期date和时间time类型测试
1 创建一个表

create table test_time(
 d date,
 t time,
 dt datetime);

2查看表结构

mysql> desc test_time;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| d     | date     | YES  |     | NULL    |       |
| t      | time     | YES  |     | NULL    |       |
| dt    | datetime | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+

3插入时间
了解一个函数

MySQL [(none)]> select now();
+---------------------+
| now()               |
+---------------------+
| 2020-08-29 10:46:05 |
+---------------------+
1 row in set (0.00 sec)
mysql> insert into test_time values(now(),now(),now());
Query OK, 1 row affected, 1 warning (0.02 sec)
insert into test5 (d) values (20170412);

4查看表内容

mysql> select * from test_time;
+------------+----------+---------------------+
| d          | t        | dt                  |
+------------+----------+---------------------+
| 2020-08-29 | 10:46:51 | 2020-08-29 10:46:51 |
+------------+----------+---------------------+

1 row in set (0.00 sec)

时间日期分门别类存储

LAB2：(当前时间)（了解）

当前时间timestamp类型测试
1 创建一个表

mysql> create table t(id timestamp);
Query OK, 0 rows affected (0.01 sec)

2查询表结构

mysql> desc t;
+-------+-----------+------+-----+-------------------+-----------------------------+
| Field | Type      | Null | Key | Default           | Extra                       |
+-------+-----------+------+-----+-------------------+-----------------------------+
| id    | timestamp | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------+-----------+------+-----+-------------------+-----------------------------+
1 row in set (0.00 sec)

3插入空值

mysql> insert into t values(null);
Query OK, 1 row affected (0.00 sec)
timestamp 该列插入空值时，会自动填入当前时间。
mysql> insert into t values (121112111211);

手动插入时间吗，以插入时间为准。年月日时分秒

mysql> insert into t values(20200809092030);
Query OK, 1 row affected (0.00 sec)

4查询内容

mysql> select * from t;
+---------------------+
| id                  |
+---------------------+
| 2016-12-18 00:08:41 |
+---------------------+
1 row in set (0.00 sec)

LAB3:(年)

年YEAR类型测试
注意：其它的时间，按要求插入
==插入年份时，尽量使用4位值
==插入两位年份时，

• 小于等于69，默认以20开头，比如65, 结果2065
• 大于等于70，默认以19开头，比如82，结果1982

1创建表

mysql> create table t5(born_year year);
Query OK, 0 rows affected (0.40 sec)

2表结构

mysql> desc t5;
+-----------+---------+------+-----+---------+-------+
| Field     | Type    | Null | Key | Default | Extra |
+-----------+---------+------+-----+---------+-------+
| born_year | year(4) | YES  |     | NULL    |       |
+-----------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

3插入数据

mysql> insert into t5 values (12),(80);
观察年份的边界

4查询结果

mysql> select * from t5;
+-----------+
| born_year |
+-----------+
|      2012 |
|      1980 |
+-----------+
2 rows in set (0.00 sec)

5请思考插入四位值会怎样？

2099/1901

5.4 字符串类型测试：CHAR、VARCHAR

LAB1:(字符、变长字符)

作用：用于存储用户的姓名、爱好、发布的文章等
注意

• CHAR 列的长度固定为创建表时声明的长度： 0 ~ 255
• VARCHAR 列中的值为可变长字符串，长度： 0 ~ 65535
• CHAR的长度是固定的
• VARCHAR长度是可以变化的

固定与可变是针对存储介质（硬盘）来说的
-假如-：
CHAR和VARCHAR的默认长度都设为10，两个字段都分别写入“abc”
CHAR 损耗了硬盘10字节 = “abc”长度 + 7个空字符
VARCHAR损耗了硬盘 3字节 = “abc”长度
设定默认值n（假如是 10 ） 则该字段内能写入的字符串长度最大只能为 10
在检索的时候，CHAR列删除了尾部的空格，而VARCHAR则保留这些空格

1创建表

create table vc (
v varchar(4),
 c char(4));

2 查看表结构

mysql> desc vc;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| v     | varchar(4) | YES  |     | NULL    |       |
| c     | char(4)    | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

3插入数据

mysql> insert into vc values('a','a');
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> insert into vc values('ab  ','ab ');
Query OK, 1 row affected, 1 warning (0.00 sec)

4查询表内容

mysql> select * from vc;
+------+------+
| v    | c    |
+------+------+
| a    |  a   |
| ab   | ab   |
+------+------+
1 row in set (0.00 sec)

看不出来区别

5调动函数

mysql> select length(v),length(c) from vc;
+-----------+-----------+
| length(v) | length(c) |
+-----------+-----------+
|         1 |         1|
|         4 |         2 |
+-----------+-----------+
1 row in set (0.00 sec)

length(v) 统计长度的函数

mysql> select concat(v,'='), concat(c,'=') from vc;	 //在后面加字符'='，看的更清楚
+---------------+---------------+
| concat(v,'=') | concat(c,'=') |
+---------------+---------------+
| a=            | a=            |
| ab  =         | ab=           |
+---------------+---------------+
1 row in set (0.00 sec)

concat(v,’=’) 拼接函数

LAB2：二进制字符（了解）

前言
二进制字符串 类型包括：binary、varbinary、blob，主要是处理图像、视频、音频等文件，与字符集无关。

这类文件一般是放在服务器硬盘里，而不是数据库里，如果要放在数据库，就一定不能指定字符集类型，否则会把二进制字段转换成相应的非二进制字符，图像和视频就不能正常显示了。
关于非二进制字符串：非二进制字符串类型：包括：char、varchar、text，主要用于处理文本格式的文件。

字符串类型测试：BINARY、VARBINARY
BINARY 和 VARBINARY类似于CHAR 和 VARCHAR，不同的是它们包含二进制字符，而不包含非二进制字符串

mysql> create table binary_t (c binary(3));
Query OK, 0 rows affected (0.03 sec)

mysql> desc      binary_t;
+-------+-----------+------+-----+---------+-------+
| Field | Type      | Null | Key | Default | Extra |
+-------+-----------+------+-----+---------+-------+
| c     | binary(3) | YES  |     | NULL    |       |
+-------+-----------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> insert into binary_t set c='aaa';
Query OK, 1 row affected (0.00 sec)

mysql> select *,hex(c) from binary_t;
+------+--------+
| c    | hex(c) |
+------+--------+
| aaa  | 616161 |
+------+--------+
1 row in set (0.00 sec)

• set解释
  在标准的SQL语句中，一次插入一条记录的INSERT语句只有一种形式。

INSERT INTO tablename(列名…) VALUES(列值);

而在MySQL中还有另外一种形式。

INSERT INTO tablename SET column_name1 = value1, column_name2 = value2，…;

第一种方法将列名和列值分开了，在使用时，列名必须和列值的数一致。如下面的语句向users表中插入了一条记录：

INSERT INTO users(id, name, age) VALUES(123, '姚明', 25);

第二种方法允许列名和列值成对出现和使用，如下面的语句将产生中样的效果。

INSERT INTO users SET id = 123, name = '姚明', age = 25;616161解释，a是字符的话，对应ascII码是97，则二进制为01100001,转成16进制为61。

5.5 枚举类型、集合类型：ENUM类型，SET测试

LAB1：（单选、多选）

作用
字段的值只能在给定范围中选择
enum 单选 只能在给定的范围内选一个值，如性别 sex 男male/女female
set 多选 在给定的范围内可以选择一个或一个以上的值（爱好1,爱好2,爱好3…）

目标

创建表school.student3
姓名 name varchar(50)
性别 sex enum(‘m’,‘f’)
爱好 hobby set(‘music’,‘book’,‘game’,‘disc’)

1 创建表

use school
 create table student3(
 name varchar(50),
 sex enum('m','f'),
 hobby set('music','book','game','disc') );

2查看表结构

mysql> desc student3;
+-------+-----------------------------------+------+-----+---------+-------+
| Field | Type                              | Null | Key | Default | Extra |
+-------+-----------------------------------+------+-----+---------+-------+
| name  | varchar(50)                       | YES  |     | NULL    |       |
| sex   | enum('m','f')                     | YES  |     | NULL    |       |
| hobby | set('music','book','game','disc') | YES  |     | NULL    |       |
+-------+-----------------------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> show create table student3\G
*************************** 1. row ***************************
Table: student3
Create Table: CREATE TABLE `student3` (
  `name` varchar(50) default NULL,
  `sex` enum('m','f') default NULL,
  `hobby` set('music','book','game','disc') default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

3插入数据

mysql> insert into student3 values  ('tom','m','book,game');
Query OK, 1 row affected (0.00 sec)

4查询数据

mysql> select * from student3;
+------+------+-----------+
| name | sex  | hobby     |
+------+------+-----------+
| tom  | boy  | book,game | 
+------+------+-----------+
1 row in set (0.00 sec)

5插入非法数据

mysql>  insert into student3 values ('jack','m','film');
ERROR 1265 (01000): Data truncated for column 'hobby' at row 1

超出范围

6.完整性约束

6.1目的

由于生活中需要避免重名的情况，所以数据库中某列使用完整性约束来限定此类要求。
用于保证数据的完整性和一致性。

6.2约束类型

约束条件	说明
PRIMARY KEY (PK)	标识该字段为该表的主键，可以唯一的标识记录，不可以为空（员工信息表 UNIQUE + NOT NULL）
NOT NULL	标识该字段不能为空，是否允许为空，默认NULL，可设置NOT NULL，字段不允许为空，必须赋值
FOREIGN KEY (FK)	标识该字段为该表的外键，实现表与表（父表主键/子表1外键/子表2外键）之间的关联 （员工工资表）
DEFAULT	为该字段设置默认值
UNIQUE KEY (UK)	标识该字段的值是唯一的，可以为空，一个表中可以有多个UNIQUE KEY
UNSIGNED	无符号，正数
AUTO_INCREMENT	标识该字段的值自动增长（整数类型，而且为主键）
ZEROFILL	使用0填充，例如0000001

注:字段是否有默认值，缺省的默认值是NULL，如果插入记录时不给字段赋值，此字段使用默认值。

sex enum('male','female') not null default 'male' //默认值male
age int unsigned NOT NULL default 20		//必须为正值（无符号） 不允许为空  默认是20

6.3约束类型测试

===默认值、空值：DEFAULT、NOT NULL

目的：

• 某列设置默认值，并不输入数值，请思考该列是会显示什么。
• 某列设置为“NOT NULL”，请思考能不能为空呢。
  1创建表
  表school.student4

create table student.student4(
 id int not null, 
name varchar(50) not null, 
sex enum ('m','f') default 'm' not null, 
age int unsigned default 18 not null, 
hobby set('music','disc','dance','book') default 'book,dance' );

2 查看表结构

MariaDB [student]> desc student4;
+-------+------------------------------------+------+-----+------------+-------+
| Field | Type                               | Null | Key | Default    | Extra |
+-------+------------------------------------+------+-----+------------+-------+
| id    | int(11)                            | NO   |     | NULL       |       |
| name  | varchar(50)                        | NO   |     | NULL       |       |
| sex   | enum('m','f')                      | NO   |     | m          |       |
| age   | int(10) unsigned                   | NO   |     | 18         |       |
| hobby | set('music','disc','dance','book') | YES  |     | dance,book |       |
+-------+------------------------------------+------+-----+------------+-------+
5 rows in set (0.00 sec)

3 插入数据

mysql> insert into student4 values(1,'jack','m',20,'book');
Query OK, 1 row affected (0.00 sec)

mysql> select * from student4;

mysql> insert into student4(id,name) values(2,'robin');
Query OK, 1 row affected (0.00 sec)

mysql> select * from student4;

4插入非法数据,随便找一列，插入空值

mysql> insert into student4 values(3,NULL,'m',40,'book');
ERROR 1048 (23000): Column 'name' cannot be null

注意观察输出错误的结果，理解默认值和空值的含义。NULL函数是空值的意思。
请注意insert into student5 values (4,'','m',22,'music');是空格，不是空值

===设置主键约束 PRIMARY KEY

目的

• primary key 字段的值是不允许重复，且不允许NULL（UNIQUE + NOT NULL）
• 单列做主键
• 多列做主键（复合主键）

1创建表
表school.student6 方法一

mysql> create table student6(
 id int primary key not null auto_increment,
 name varchar(50) not null,
 sex enum('male','female') not null default 'male', 
 age int not null default 18
 );

表school.student7 方法二

mysql> create table student7(
    -> id int auto_increment not null,
    -> name varchar(50) not null,
    -> sex enum('male','female') not null default 'male', 
    -> age int not null default 18,
    ->  primary key(id)
    -> );
Query OK, 0 rows affected (0.00 sec)

可以同时为多个字段同时设置为主键。
 primary key(id,name)

2查询表结构

desc student6;
+-------+-----------------------+------+-----+---------+----------------+
| Field | Type                  | Null | Key | Default | Extra          |
+-------+-----------------------+------+-----+---------+----------------+
| id    | int(11)               | NO   | PRI | NULL    | auto_increment |
| name  | varchar(50)           | NO   |     | NULL    |                |
| sex   | enum('male','female') | NO   |     | male    |                |
| age   | int(11)               | NO   |     | 18      |                |
+-------+-----------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

3插入数据

mysql> insert into student6 values (1,'alice','female',22);

mysql> insert into student6(name,sex,age) values
 ('jack','male',19),
 ('tom','male',23);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

4查询表内容

mysql> select * from student6;
+----+-------+------+-----+
| id | name  | sex  | age |
+----+-------+------+-----+
|  1 | alice | female |  22 | 
|  2 | jack  | male  |  19 | 
|  3 | tom   | male  |  23 | 
+----+-------+------+-----+
3 rows in set (0.00 sec)

注意观察id列，并没有输入内容，自动增长。如果在此列插入空值呢？
5 插入非法数据

MariaDB [company]> insert into student6(name,sex,age) values  (3,'jack','male',19);
ERROR 1136 (21S01): Column count doesn't match value count at row 1

6 主键设置了自动增长，再次尝试插入数据。成功（注意不要插入主键）

===设置唯一约束 UNIQUE

目的

• unique唯一的特性。
• unique是可以为空的。

1 创建表
为部门创建一张员工信息表 company.department1
方法1

CREATE TABLE company.department1 (
dept_id INT,
dept_name VARCHAR(30) UNIQUE,
comment VARCHAR(50));

方法2

表company.department2		
CREATE TABLE company.department2 (
		dept_id INT,
		dept_name VARCHAR(30),
		comment VARCHAR(50),
		 UNIQUE(dept_name)
		);
CONSTRAINT uk_name UNIQUE(dept_name)

可以再设置约束时，为列起一个名称。

2查看表结构
观察UNIQUE的显示位置

3插入数据
1 插入合法数据

insert into department1 values (1,'zhangsan','yyy');

2插入空值数据

insert into department1 values (1,NULL,'yyy');

3插入空值数据（空值是允许重复的）

insert into department1 values (1,NULL,'yyy');

4插入非法数据

insert into department1 values (1,'zhangsan','yyy');
ERROR 1062 (23000): Duplicate entry 'zhangsan' for key 'dept_name'

===设置外键约束 FOREIGN KEY

目的
使两张表产生关联，同步更新内容。创建员工信息表，创建员工薪资表。观察同步效应

• 1创建父表
  父表 company.employees

mysql> create table employees(
 name varchar(50) not null, 
 mail varchar(20),
 primary key(name) 	
 )engine=innodb;
primary key(name) 	

• 2创建子表
  子表company.payroll

mysql> create table payroll(
id int not null auto_increment,
name varchar(50) not null,	   
payroll float(10,2) not null,
primary key(id),		
foreign key(name)  references employees(name) on update cascade on delete cascade
 )engine=innodb; 

子表name外键，关联父表（employees 主键name），同步更新，同步删除

• 3查看表结构

> desc employees;
> desc payroll;

图示

• 4 输入测试数据

insert into employees values ('zhangsan','zhangsan@126.com');
insert into payroll values (1,'zhangsan',20000.23);
select * from employees;
select * from payroll;

• 5.插入数据

父表更新，子表会如何？

update employees set name='zhangsansss' where name='zhangsan';
select * from payroll;

父表删除，子表会如何

delete from employees where name='zhangsansss';

总结
当父表中某个员工的记录修改时，子表也会同步修改
当父表中删除某个记录，子表也会同步删除该记录。

===设置复合主键约束 PRIMARY KEY（略）

目的
解决单列主键无法保持唯一性的问题
例如：记录主机地址和服务的数据表是否运行的表。
目标

创建表school.service
host_ip 主机IP
service_name 服务名
por 服务对应的端口
allow(Y,N) 服务是否允许访问
主键： host_ip + port = primary key
1创建数据表

mysql> create table service(
host_ip varchar(15) not null,
service_name varchar(10) not null,
port varchar(5) not null,
allow enum('Y','N') default 'N',
primary key(host_ip,port)
);
Query OK, 0 rows affected (0.00 sec)

关键部分，复合主键设置方法：primary key(host_ip,port)
2查看表结构
图示

mysql> desc service;
+--------------+---------------+------+-----+---------+-------+
| Field        | Type          | Null | Key | Default | Extra |
+--------------+---------------+------+-----+---------+-------+
| host_ip      | varchar(15)   | NO   | PRI | NULL    |       |
| service_name | varchar(10)   | NO   |     | NULL    |       |
| port         | varchar(5)    | NO   | PRI | NULL    |       |
| allow        | enum('Y','N') | YES  |     | N       |       |
+--------------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

3.插入数据
mysql> insert into service values (‘192.168.2.168’,‘ftp’,‘21’,‘Y’);
mysql> insert into service values (‘192.168.2.168’,‘httpd’,‘80’,‘Y’);
4.总结
复合主键是当多列值组合唯一。
5.附加复合主键案例
在mysql系统内部对于mysql账户的记录就是复合主键（用户名+主机地址）

mysql> desc mysql.user;
+------------------------+-----------------------------------+------+-----+---------+-------+
| Field                  | Type                              | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+---------+-------+
| Host                   | char(60)                          | NO   | PRI |         |       |
| User                   | char(16)                          | NO   | PRI |         |       |
| Password               | char(41)                          | NO   |     |         |       |
| Select_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Insert_priv            | enum('N','Y')                     | NO   |     | N       |       |

===设置字段值增 AUTO_INCREMENT（略）

目的
配合整数型，主键使用自动增长约束。
1创建表插入数据

// 创建表company.department3
CREATE TABLE department3 (
	dept_id INT PRIMARY KEY AUTO_INCREMENT,
	dept_name VARCHAR(30),
	comment VARCHAR(50)
	);

2.插入数据

• 合法数据

mysql> insert into department3 values(1,'zs','manager');
Query OK, 1 row affected (0.01 sec)

• 非法数据

mysql> insert into department3 values(1,'zs','manager');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

• 合法数据

mysql> insert into department3 values(2,'zs','manager');
Query OK, 1 row affected (0.01 sec)

合法数据。未插入序号，但是在查询时会自动增加。

mysql> insert into department3 (dept_name,comment) values('zs','manager'');
Query OK, 1 row affected (0.01 sec)

3.查看信息

mysql> select * from department3;
+---------+-----------+---------+
| dept_id | dept_name | comment |
+---------+-----------+---------+
|       1 | zs        | manager |
|       2 | zs        | manager |
|       3 | zs        | manager |
+---------+-----------+---------+
3 rows in set (0.00 sec)

7.DDL-表

7.1创表目的：

• 表是数据库存储数据的基本单位
• 表由若干个字段（列）组成
• 主要用来存储数据记录（行）

7.2操作数据库实例1

创建1列的表格-序号

• 创库

create    database    haha;

• 使用库

use    haha;

创建  表格  表名t1  (列名id   类型int );
CREATE  TABLE    t1    (id int);

• 查看所有表名

show   tables;

• 插入数据

插入  到   表名t1   值  （1）；
INSERT   INTO    t1  VALUES   (1)；

• 查询所有数据

查询  所有列   从   表名t1
SELECT  *  FROM  t1；

• 删除表

drop   table  t1；

7.3操作数据库实例2

• 创两列的表格-序号和姓名

 创建  表格  表名t2  (第一列名id   数字类型,  第二列名name   字符类型(长度))
create table  t2   (id        int,        name        varchar(20));

• 查看表结构
  描述 表t2 desc t2；
  总结
  mysql> show tables;查看表名
  mysql> select * from t2;看表中的内容
  mysql> desc t2;查看表结构
• 插入数据

插入  到     表       t2   值 （第一列信息，第二列信息）;
INSERT     INTO      t2   VALUES （1,"zhangsan"）；

!!!注意!!!—数字不能加引号，字符必须加引号（转义符）

• 查询所有数据select * from t2；

7.4操作数据库实例3

• 创建表
  要求：创建库school

创建表student1
结构如图：
语法create table 表名(字段名1 类型,字段名2 类型,字段名3 类型 );

示例:

mysql> CREATE  DATABASE school;创建数据库school
mysql> use school;使用库
mysql> create table student1(  id int, name varchar (20) ,   sex enum('m','f'), age int );
//创建一个表，四列
Query OK, 0 rows affected (0.03 sec)

• 查看表名（需要进入一个数据库）

mysql> show tables;	
+------------------+
| Tables_in_school |
+------------------+
| student1         |
+------------------+
1 row in set (0.00 sec)

• 表中插入内容
  语法insert into 表名 values(字段值列表...);
  插入数据

insert into student1 values (1,'zhangsan','m',33) ,
		insert into student1 values (2,'lisi','f',20) ,
		insert into student1 values (1,'wangwu','m',40) ,

• 查看表内容
  查询表中所有字段的值

mysql> select * from student1;

• 查看表结构

desc student1;

注意：表结构和表内容是两个概念。