MySQL数据库

内容概要

存储引擎
mysql中的数据类型
数据库约束
表操作
数据操作
事务与索引

一. 存储引擎

mysql5.6支持的存储引擎包括InnoDB、MyISAM、MEMORY、CSV、BLACKHOLE、FEDERATED、MRG_MYISAM、ARCHIVE、PERFORMANCE_SCHEMA。其中NDB和InnoDB提供事务安全表，其他存储引擎都是非事务安全表。

InnoDB

用于事务处理应用程序，支持外键和行级锁。如果应用对事物的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询之外，还包括很多更新和删除操作，那么InnoDB存储引擎是比较合适的。InnoDB除了有效的降低由删除和更新导致的锁定，还可以确保事务的完整提交和回滚，对于类似计费系统或者财务系统等对数据准确要求性比较高的系统都是合适的选择。

MyISAM

如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不高，那么可以选择这个存储引擎。

Memory

将所有的数据保存在内存中，在需要快速定位记录和其他类似数据的环境下，可以提供极快的访问。Memory的缺陷是对表的大小有限制，虽然数据库因为异常终止的话数据可以正常恢复，但是一旦数据库关闭，存储在内存中的数据都会丢失。

二. mysql中的数据类型

数字类型

int

# 创建表一个是默认宽度的int，一个是指定宽度的int(5)
mysql> create table t1 (id1 int,id2 int(5));
Query OK, 0 rows affected (0.02 sec)

# 像t1中插入数据1，1
mysql> insert into t1 values (1,1);
Query OK, 1 row affected (0.01 sec)

# 可以看出结果上并没有异常
mysql> select * from t1;
+------+------+
| id1  | id2  |
+------+------+
|    1 |    1 |
+------+------+
1 row in set (0.00 sec)

# 那么当我们插入了比宽度更大的值，会不会发生报错呢？
mysql> insert into t1 values (111111,111111);
Query OK, 1 row affected (0.00 sec)

# 答案是否定的，id2仍然显示了正确的数值，没有受到宽度限制的影响
mysql> select * from t1;
+------------+--------+
| id1        | id2    |
+------------+--------+
| 0000000001 |  00001 |
| 0000111111 | 111111 |
+------------+--------+
2 rows in set (0.00 sec)

# 修改id1字段 给字段添加一个unsigned表示无符号
mysql> alter table t1 modify id1 int unsigned;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc t1;
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| id1   | int(10) unsigned | YES  |     | NULL    |       |
| id2   | int(5)           | YES  |     | NULL    |       |
+-------+------------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

# 当给id1添加的数据大于214748364时，可以顺利插入
mysql> insert into t1 values (2147483648,2147483647);
Query OK, 1 row affected (0.00 sec)

# 当给id2添加的数据大于214748364时，会报错
mysql> insert into t1 values (2147483647,2147483648);
ERROR 1264 (22003): Out of range value for column 'id2' at row 1

int 整数示例

float

# 创建表的三个字段分别为float，double和decimal参数表示一共显示5位，小数部分占2位
mysql> create table t2 (id1 float(5,2),id2 double(5,2),id3 decimal(5,2));
Query OK, 0 rows affected (0.02 sec)

# 向表中插入1.23，结果正常
mysql> insert into t2 values (1.23,1.23,1.23);
Query OK, 1 row affected (0.00 sec)

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

# 向表中插入1.234，会发现4都被截断了
mysql> insert into t2 values (1.234,1.234,1.234);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * from t2;
+------+------+------+
| id1  | id2  | id3  |
+------+------+------+
| 1.23 | 1.23 | 1.23 |
| 1.23 | 1.23 | 1.23 |
+------+------+------+
2 rows in set (0.00 sec)

# 向表中插入1.235发现数据虽然被截断，但是遵循了四舍五入的规则
mysql> insert into t2 values (1.235,1.235,1.235);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * from t2;
+------+------+------+
| id1  | id2  | id3  |
+------+------+------+
| 1.23 | 1.23 | 1.23 |
| 1.23 | 1.23 | 1.23 |
| 1.24 | 1.24 | 1.24 |
+------+------+------+
3 rows in set (0.00 sec)

# 建新表去掉参数约束
mysql> create table t3 (id1 float,id2 double,id3 decimal);
Query OK, 0 rows affected (0.02 sec)

# 分别插入1.234
mysql> insert into t3 values (1.234,1.234,1.234);
Query OK, 1 row affected, 1 warning (0.00 sec)

# 发现decimal默认值是(10,0)的整数
mysql> select * from t3;
+-------+-------+------+
| id1   | id2   | id3  |
+-------+-------+------+
| 1.234 | 1.234 |    1 |
+-------+-------+------+
1 row in set (0.00 sec)

# 当对小数位没有约束的时候，输入超长的小数，会发现float和double的区别
mysql> insert into t3 values (1.2355555555555555555,1.2355555555555555555,1.2355555555555555555555);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * from t3;
+---------+--------------------+------+
| id1     | id2                | id3  |
+---------+--------------------+------+
|   1.234 |              1.234 |    1 |
| 1.23556 | 1.2355555555555555 |    1 |
+---------+--------------------+------+
2 rows in set (0.00 sec)

小数示例

字符串类型

字符串，描述的都是字符

 char 定长字段 0-255 相对浪费空间 存取的速度比varchar要快
            char（10）
            'hellohello1'  --> 'hellohello'
            'hello      '

varchar  变长字段 0-65535  相对节省空间 存取的速度比varchar要慢
            varchar(200)
            'hellohello1'  --> '10hellohello'
            'hello'  --> '5hello'

手机号 char(11)
身份证号 char(18)
用户名 char（14）
密码 char（12）
评论 varchar

常见的场景

mysql> create table t9 (v varchar(4),c char(4));
Query OK, 0 rows affected (0.01 sec)

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

# 在检索的时候char数据类型会去掉空格
mysql> select * from t9;
+------+------+
| v    | c    |
+------+------+
| ab   | ab   |
+------+------+
row in set (0.00 sec)

# 来看看对查询结果计算的长度
mysql> select length(v),length(c) from t9;
+-----------+-----------+
| length(v) | length(c) |
+-----------+-----------+
|         4 |         2 |
+-----------+-----------+
row in set (0.00 sec)

# 给结果拼上一个加号会更清楚
mysql> select concat(v,'+'),concat(c,'+') from t9;
+---------------+---------------+
| concat(v,'+') | concat(c,'+') |
+---------------+---------------+
| ab  +         | ab+           |
+---------------+---------------+
row in set (0.00 sec)


# 当存储的长度超出定义的长度，会截断
mysql> create table t3 (a char(5),b varchar(5));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t3 values ("abcdefg","abcdefg");
Query OK, 1 row affected, 2 warnings (0.00 sec)

mysql> select * from t3;
+-------+-------+
| a     | b     |
+-------+-------+
| abcde | abcde |
+-------+-------+
1 row in set (0.00 sec)

char和varchar示例

日期和时间类型

datetime

mysql> create table t4 (date datetime);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t4 values("2018-9-26 12:20:10");
Query OK, 1 row affected (0.00 sec)

mysql> insert into t4 values("2018/9/26 12+20+10");
Query OK, 1 row affected (0.00 sec)

mysql> insert into t4 values("20180926122010");
Query OK, 1 row affected (0.00 sec)

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

mysql> select * from t4;
+---------------------+
| date                |
+---------------------+
| 2018-09-26 12:20:10 |
| 2018-09-26 12:20:10 |
| 2018-09-26 12:20:10 |
| 2018-09-26 12:20:10 |
+---------------------+
4 rows in set (0.00 sec)

datetime示例

ENUM和SET类型

ENUM中文名称叫枚举类型，它的值范围需要在创建表时通过枚举方式显示。ENUM只允许从值集合中选取单个值，而不能一次取多个值。

SET和ENUM非常相似，也是一个字符串对象，里面可以包含0-64个成员。根据成员的不同，存储上也有所不同。set类型可以允许值集合中任意选择1或多个元素进行组合。对超出范围的内容将不允许注入，而对重复的值将进行自动去重。

类型

大小

用途

ENUM

对1-255个成员的枚举需要1个字节存储;

对于255-65535个成员，需要2个字节存储;

最多允许65535个成员。

单选：选择性别

SET

1-8个成员的集合，占1个字节

9-16个成员的集合，占2个字节

17-24个成员的集合，占3个字节

25-32个成员的集合，占4个字节

33-64个成员的集合，占8个字节

多选：兴趣爱好

mysql> create table t5 (name char(20),gender enum("female","male"));
Query OK, 0 rows affected (0.01 sec)

# 选择enum('female','male')中的一项作为gender的值，可以正常插入
mysql> insert into t5 values ("lishichao","male");
Query OK, 1 row affected (0.00 sec)

# 不能同时插入'male,female'两个值，也不能插入不属于'male,female'的值(5.7会报错，5.6不报错，但查看数据为空)
mysql> insert into t5 values ("lishichao","male,female");
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> insert into t5 values ("lishichao","hahaha");
Query OK, 1 row affected, 1 warning (0.00 sec)

5.6不报错，但查看数据为空
mysql> select * from t5;
+-----------+--------+
| name      | gender |
+-----------+--------+
| lishichao | male   |
| lishichao |        |
| lishichao |        |
+-----------+--------+
3 rows in set (0.00 sec)



set数据类型
mysql> create table t2 (name char(20),hobby set("抽烟","喝酒","烫头"));
Query OK, 0 rows affected (0.00 sec)
# 可以任意选择set('抽烟','喝酒','烫头')中的项，并自带去重功能
mysql> insert into t2 values ("lsc","抽烟,喝酒,抽烟");
Query OK, 1 row affected (0.00 sec)

mysql> select * from t2;
+------+---------------+
| name | hobby         |
+------+---------------+
| lsc  | 抽烟,喝酒     |
+------+---------------+
1 row in set (0.00 sec)

# 不能选择不属于set('抽烟','喝酒','烫头')中的项，
mysql> insert into t2 values ('ym','烫头,抽烟,看妹子');
ERROR 1265 (01000): Data truncated for column 'hobby' at row 1

set/nume代码示例

mysql支持的数据类型

三. 数据库约束

为了防止不符合规范的数据进入数据库，在用户对数据进行插入、修改、删除等操作时，DBMS自动按照一定的约束条件对数据进行监测，使不符合规范的数据不能进入数据库，以确保数据库中存储的数据正确、有效、相容。

　　约束条件与数据类型的宽度一样，都是可选参数，主要分为以下几种：

not null ：非空约束，指定某列不能为空；

default：默认值约束，指定某列的默认值

unique：唯一约束，指定某列或者几列组合不能重复；

auto_increment：自增约束，指定某int、非空列自动增加

primary key：主键，指定该列的值可以唯一地标识该列记录

foreign key：外键，指定该行记录从属于主表中的一条记录，主要用于参照完整性

not null

是否可空，null表示空，非字符串
　　not null - 不可空
　　null - 可空

mysql> create table t4 (id int not  null);
Query OK, 0 rows affected (0.01 sec)

mysql> select * from t4;
Empty set (0.00 sec)

# 查看表结构
mysql> desc t4;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | NO   |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

# 不能向ID列插入空元素
mysql> insert into t4 values (null);
ERROR 1048 (23000): Column 'id' cannot be null
mysql> insert into t4 values (1);
Query OK, 1 row affected (0.01 sec)

not null

设置严格模式：
    不支持对not null字段插入null值
    不支持对自增长字段插入”值
    不支持text字段有默认值

直接在mysql中生效(重启失效):
mysql>set sql_mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION";

配置文件添加(永久失效)：
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

not null如果不生效

default

默认值，创建列时可以指定默认值，当插入数据时如果未主动设置，则自动添加默认值

mysql> create table t5 (id1 int not null,id2 int not null default 222);
Query OK, 0 rows affected (0.00 sec)

mysql> desc t5;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id1   | int(11) | NO   |     | NULL    |       |
| id2   | int(11) | NO   |     | 222     |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)



# 只向id1字段添加值，会发现id2字段会使用默认值填充
mysql> insert into t5 (id1) values (111);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t5;
+-----+-----+
| id1 | id2 |
+-----+-----+
| 111 | 222 |
+-----+-----+
1 row in set (0.00 sec)


# id1字段不能为空，所以不能单独向id2字段填充值；
mysql> insert into t5 (id2) values (111);
ERROR 1364 (HY000): Field 'id1' doesn't have a default value

# 向id1，id2中分别填充数据，id2的填充数据会覆盖默认值
mysql> insert into t5 (id1,id2) values (666,888);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t5;
+-----+-----+
| id1 | id2 |
+-----+-----+
| 111 | 222 |
| 666 | 888 |
+-----+-----+
2 rows in set (0.00 sec)

not null和default示例

unique

唯一约束，指定某列或者几列组合不能重复

mysql> create table t6(
    -> id int,
    -> name char(20) unique,
    -> eomment varchar(100)
    -> );
Query OK, 0 rows affected (0.01 sec)


mysql> insert into t6 values (1,'lishichao','技术');
Query OK, 1 row affected (0.01 sec)

# 报错，name列不能重复
mysql> insert into t6 values (1,'lishichao','技术');
ERROR 1062 (23000): Duplicate entry 'lishichao' for key 'name'

unnique示例

mysql> create table t7(id int not null unique);
Query OK, 0 rows affected (0.00 sec)

mysql> desc t7;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | NO   | PRI | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

not null和unique结合

mysql> create table server(
    -> id int primary key auto_increment,
    -> name char(20),
    -> host char(15) not null,
    -> port int not null,
    -> unique(host,port)  #联合唯一
    -> );
Query OK, 0 rows affected (0.01 sec)

# id为主键，并自增加
mysql> mysql> insert into server(name,host,port) values 
    -> ('nginx','10.0.3.80',80),
    -> ('mysql','10.0.3.88',3306),
    -> ('tomcat','10.0.3.90',8080);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from server;
+----+--------+-----------+------+
| id | name   | host      | port |
+----+--------+-----------+------+
|  1 | nginx  | 10.0.3.80 |   80 |
|  2 | mysql  | 10.0.3.88 | 3306 |
|  3 | tomcat | 10.0.3.90 | 8080 |
+----+--------+-----------+------+
3 rows in set (0.00 sec)

mysql> insert into server(name,host,port) values ('nginx','10.0.3.80',80);
ERROR 1062 (23000): Duplicate entry '10.0.3.80-80' for key 'host'

联合唯一

auto_increment

约束字段为自动增长，被约束的字段必须同时被key约束

mysql> create table student(
    -> id int primary key auto_increment,
    -> name char(20),
    -> sex enum('male','female') default 'male'
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> desc student;
+-------+-----------------------+------+-----+---------+----------------+
| Field | Type                  | Null | Key | Default | Extra          |
+-------+-----------------------+------+-----+---------+----------------+
| id    | int(11)               | NO   | PRI | NULL    | auto_increment |
| name  | char(20)              | YES  |     | NULL    |                |
| sex   | enum('male','female') | YES  |     | male    |                |
+-------+-----------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> insert into student(name) values
    -> ('lishichao'),  # id 字段自动增长, sex 字段默认值 male
    -> ('lirenjie');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from student;
+----+-----------+------+
| id | name      | sex  |
+----+-----------+------+
|  1 | lishichao | male |
|  2 | lirenjie  | male |
+----+-----------+------+
2 rows in set (0.00 sec)


mysql> #也可以指定id
mysql> insert into student values (4,'abc','female');
Query OK, 1 row affected (0.00 sec)

mysql> insert into student values (7,'zxc','female');
Query OK, 1 row affected (0.00 sec)

mysql> select * from student;
+----+-----------+--------+
| id | name      | sex    |
+----+-----------+--------+
|  1 | lishichao | male   |
|  2 | lirenjie  | male   |
|  4 | abc       | female |
|  7 | zxc       | female |
+----+-----------+--------+
4 rows in set (0.00 sec)

mysql> #对于自增的字段，在用delete删除后，再插入值，该字段仍按照删除前的位置继续增长
mysql> delete from student;
Query OK, 4 rows affected (0.00 sec)

mysql> select * from student;
Empty set (0.00 sec)

mysql> insert into student(name) values ('lsc');
Query OK, 1 row affected (0.00 sec)

mysql> select * from student;
+----+------+------+
| id | name | sex  |
+----+------+------+
|  8 | lsc  | male |
+----+------+------+
1 row in set (0.00 sec)

mysql> #应该用truncate清空表，比起delete一条一条地删除记录，truncate是直接清空表，在删除大表时用它
mysql> truncate student;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into student(name) values('lsc');
Query OK, 1 row affected (0.00 sec)

mysql> select * from student;
+----+------+------+
| id | name | sex  |
+----+------+------+
|  1 | lsc  | male |
+----+------+------+
1 row in set (0.00 sec)

View Code

primary key

主键为了保证表中的每一条数据的该字段都是表格中的唯一值。换言之，它是用来独一无二地确认一个表格中的每一行数据。
主键可以包含一个字段或多个字段。当主键包含多个栏位时，称为组合键 (Composite Key),也可以叫联合主键。
主键可以在建置新表格时设定 (运用 CREATE TABLE 语句)，或是以改变现有的表格架构方式设定 (运用 ALTER TABLE)。
主键必须唯一，主键值非空；可以是单一字段，也可以是多字段组合。

1.单字段主键

mysql> # 单列做主键
mysql> # 方法一： not null + unique
mysql> create table t7(
    -> id int not null unique, # 主键
    -> name char(20) not null unique,
    -> comment varchar(200)
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> desc t8;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id      | int(11)      | NO   | PRI | NULL    |       |
| name    | char(20)     | NO   | UNI | NULL    |       |
| comment | varchar(200) | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)


mysql> # 方法二：在某一个字段后用primary key
mysql> create table t10(
    -> id int primary key, #主键
    -> name char(20),
    -> comment varchar(200)
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> desc t10;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id      | int(11)      | NO   | PRI | NULL    |       |
| name    | char(20)     | YES  |     | NULL    |       |
| comment | varchar(200) | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)


mysql> #方法三：在所有字段后单独定义primary key
mysql> create table t11(
    -> id int,
    -> name char(20),
    -> comment varchar(200),
    -> primary key(id)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> desc t11;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id      | int(11)      | NO   | PRI | NULL    |       |
| name    | char(20)     | YES  |     | NULL    |       |
| comment | varchar(200) | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)


mysql> # 方法四：给已经建成的表添加主键约束
mysql> create table t12(
    -> id int,
    -> name char(20),
    -> comment varchar(200));
Query OK, 0 rows affected (0.00 sec)

mysql> desc t12;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id      | int(11)      | YES  |     | NULL    |       |
| name    | char(20)     | YES  |     | NULL    |       |
| comment | varchar(200) | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> alter table t12 modify id int primary key;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc t12;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id      | int(11)      | NO   | PRI | NULL    |       |
| name    | char(20)     | YES  |     | NULL    |       |
| comment | varchar(200) | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

View Code

2.多字段主键

mysql> # ==================多列做主键================
mysql> create table service(
    -> ip varchar(20),
    -> port char(5),
    -> service_name varchar(10) not null,
    -> primary key(ip,port));  # 联合主键,IP+port是唯一的
Query OK, 0 rows affected (0.01 sec)

mysql> desc service;
+--------------+-------------+------+-----+---------+-------+
| Field        | Type        | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| ip           | varchar(20) | NO   | PRI | NULL    |       |
| port         | char(5)     | NO   | PRI | NULL    |       |
| service_name | varchar(10) | NO   |     | NULL    |       |
+--------------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> insert into service values
    -> ('10.0.3.82','3306','mysqld'),
    -> ('10.0.3.79','3306','mariadb');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into service values ("10.0.3.192","3306","nginx");
Query OK, 1 row affected (0.00 sec)

mysql> insert into service values ("10.0.3.192","3306","nginx");
ERROR 1062 (23000): Duplicate entry '10.0.3.192-3306' for key 'PRIMARY'  # 不允许重复IP+port
mysql>insert into service values ("10.0.3.192","3307","nginx");
Query OK, 1 row affected (0.00 sec)

View Code

foreign key

多表：

假设我们要描述所有公司的员工，需要描述的属性有这些：工号姓名部门

公司有3个部门，但是有1个亿的员工，那意味着部门这个字段需要重复存储，部门名字越长，越浪费

解决方法：我们完全可以定义一个部门表然后让员工信息表关联该表，如何关联，即foreign key

mysql> create table t13(
    -> dep_id int(4),
    -> dep_name varchar(11)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> desc t13;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| dep_id   | int(4)      | YES  |     | NULL    |       |
| dep_name | varchar(11) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> # 创建外键不成功
mysql> create table t14(
    -> s_id int,
    -> name char(20),
    -> dep_id int,
    -> foreign key(dep_id) references t13(dep_id));
ERROR 1215 (HY000): Cannot add foreign key constraint

# 设置dep_id非空，仍然不能成功创建外键
mysql> alter table t13 modify dep_id int(4) not null;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc t13;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| dep_id   | int(4)      | NO   |     | NULL    |       |
| dep_name | varchar(11) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> create table t14( s_id int, name char(20), dep_id int, foreign key(dep_id) references t13(dep_id));
ERROR 1215 (HY000): Cannot add foreign key constraint

mysql> # 当设置字段为unique唯一字段时，设置该字段为外键成功
mysql> alter table t13 modify dep_id int(4) unique;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc t13;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| dep_id   | int(4)      | YES  | UNI | NULL    |       |
| dep_name | varchar(11) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> create table t14( s_id int, name char(20), dep_id int, foreign key(dep_id) references t13(dep_id));
Query OK, 0 rows affected (0.00 sec)

创造外键的条件

mysql> #表类型必须是innodb存储引擎，且被关联的字段，即references指定的另外一个表的字段，必须保证唯一
mysql> create table department(
    -> id int primary key,
    -> name char(20) not null
    -> ) engine=innodb;
Query OK, 0 rows affected (0.00 sec)

mysql> #dpt_id外键，关联父表（department主键id），同步更新，同步删除
mysql> create table employee(
    -> id int primary key,
    -> name char(20) not null,
    -> dpt_id int,
    -> foreign key(dpt_id)  # 设置dpt_id字段为外键
    -> references department(id)  # 关联department表的id字段
    -> on delete cascade  # 级连删除
    -> on update cascade  # 级连更新
    -> )engine=innodb;
Query OK, 0 rows affected (0.01 sec)


mysql> #先往父表department中插入记录
mysql> insert into department values
    -> (1,'教质部'),
    -> (2,'技术部'),
    -> (3,'人力资源部');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> #再往子表employee中插入记录
mysql> insert into employee values
    -> (1,'yuan',1),
    -> (2,'nezha',2),
    -> (3,'egon',2),
    -> (4,'alex',2),
    -> (5,'wusir',3),
    -> (6,'李沁洋',3),
    -> (7,'皮卡丘',3),
    -> (8,'程咬金',3),
    -> (9,'程咬银',3)
    -> ;
Query OK, 9 rows affected (0.00 sec)
Records: 9  Duplicates: 0  Warnings: 0

mysql> #删父表department，子表employee中对应的记录跟着删
mysql> delete from department where id=2;
Query OK, 1 row affected (0.00 sec)

mysql> select * from employee;   # dep_id为2的也被删除了
+----+-----------+--------+ 
| id | name      | dpt_id |
+----+-----------+--------+
|  1 | yuan      |      1 |
|  5 | wusir     |      3 |
|  6 | 李沁洋    |      3 |
|  7 | 皮卡丘    |      3 |
|  8 | 程咬金    |      3 |
|  9 | 程咬银    |      3 |
+----+-----------+--------+
6 rows in set (0.00 sec)


#更新父表department，子表employee中对应的记录跟着改
mysql> update department set id=2 where id=3;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from employee;
+----+-----------+--------+
| id | name      | dpt_id |
+----+-----------+--------+
|  1 | yuan      |      1 |
|  5 | wusir     |      2 |
|  6 | 李沁洋    |      2 |
|  7 | 皮卡丘    |      2 |
|  8 | 程咬金    |      2 |
|  9 | 程咬银    |      2 |
+----+-----------+--------+
rows in set (0.00 sec)

外键操作示例

mysql表的完整性约束

四. 表操作

创建表

#语法：
create table 表名(
字段名1 类型[(宽度) 约束条件],
字段名2 类型[(宽度) 约束条件],
字段名3 类型[(宽度) 约束条件]
);

#注意：
1. 在同一张表中，字段名是不能相同
2. 宽度和约束条件可选
3. 字段名和类型是必须的

mysql> create database user;
Query OK, 1 row affected (0.00 sec)

mysql> use user;
Database changed

mysql> create table user_info(
    -> id int,
    -> name char(20),
    -> age int(3),
    -> sex enum("male","famale"),
    -> phone int(11),
    -> job char(20)
    -> );
Query OK, 0 rows affected (0.01 sec)

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

mysql> 
mysql> desc user_info;
+-------+-----------------------+------+-----+---------+-------+
| Field | Type                  | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| id    | int(11)               | YES  |     | NULL    |       |
| name  | char(20)              | YES  |     | NULL    |       |
| age   | int(3)                | YES  |     | NULL    |       |
| sex   | enum('male','famale') | YES  |     | NULL    |       |
| phone | int(11)               | YES  |     | NULL    |       |
| job   | char(20)              | YES  |     | NULL    |       |
+-------+-----------------------+------+-----+---------+-------+
6 rows in set (0.01 sec)

mysql> select * from user_info;  # 查看user_info表所有数据
Empty set (0.01 sec)

mysql> select id,name,sex from user_info;  # 指定列查看user_info
Empty set (0.01 sec)

建表

mysql> insert into user_info (id,name,age,sex,phone,job) values (1,"lishichao",18,'male',1500002020,'ops');
Query OK, 1 row affected (0.00 sec)

mysql> insert into user_info (id,name,age,sex,phone,job) values (2,"yanmin",17,'famale',1503002021,'dev');
Query OK, 1 row affected (0.00 sec)

mysql> select * from user_info;
+------+-----------+------+--------+------------+------+
| id   | name      | age  | sex    | phone      | job  |
+------+-----------+------+--------+------------+------+
|    1 | lishichao |   18 | male   | 1500002020 | ops  |
|    2 | yanmin    |   17 | famale | 1503002021 | dev  |
+------+-----------+------+--------+------------+------+
2 rows in set (0.00 sec)

插入数据

# 查看表结构
mysql> desc user_info;
+-------+-----------------------+------+-----+---------+-------+
| Field | Type                  | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| id    | int(11)               | YES  |     | NULL    |       |
| name  | char(20)              | YES  |     | NULL    |       |
| age   | int(3)                | YES  |     | NULL    |       |
| sex   | enum('male','famale') | YES  |     | NULL    |       |
| phone | int(11)               | YES  |     | NULL    |       |
| job   | char(20)              | YES  |     | NULL    |       |
+-------+-----------------------+------+-----+---------+-------+
6 rows in set (0.01 sec)

# 查看建表语句
mysql> show create table user_info\G
*************************** 1. row ***************************
       Table: user_info
Create Table: CREATE TABLE `user_info` (
  `id` int(11) DEFAULT NULL,
  `name` char(20) DEFAULT NULL,
  `age` int(3) DEFAULT NULL,
  `sex` enum('male','famale') DEFAULT NULL,
  `phone` int(11) DEFAULT NULL,
  `job` char(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

查看表结构和建表语句

五. 数据操作

在MySQL管理软件中，可以通过SQL语句中的DML语言来实现数据的操作，包括

使用 INSERT 实现数据的插入
使用 UPDATE 实现数据的更新
使用 DELETE 实现数据的删除
使用 SELECT 查询数据。

insert插入数据

1. 插入完整数据（顺序插入）
    语法一：
    INSERT INTO 表名(字段1,字段2,字段3…字段n) VALUES(值1,值2,值3…值n);

    语法二：
    INSERT INTO 表名 VALUES (值1,值2,值3…值n);

2. 指定字段插入数据
    语法：
    INSERT INTO 表名(字段1,字段2,字段3…) VALUES (值1,值2,值3…);

3. 插入多条记录
    语法：
    INSERT INTO 表名 VALUES
        (值1,值2,值3…值n),
        (值1,值2,值3…值n),
        (值1,值2,值3…值n);
        
4. 插入查询结果
    语法：
    INSERT INTO 表名(字段1,字段2,字段3…字段n) 
                    SELECT (字段1,字段2,字段3…字段n) FROM 表2
                    WHERE …;

create table teacher (
id int primary key auto_increment,
name char(10) not null,
age int,
hire_date datetime,
sex enum('male','female') not null default 'male',
phone char(11) unique,
salary float(8,2),
hooby set('抽烟','喝酒','烫头','洗脚'),
comment varchar(255)
)CHARSET=utf8 ENGINE=InnoDB;

# 插入数据
mysql> insert into teacher (name,age,hire_date,phone,salary,hooby,comment) values ("lishichao",18,'1998-08-09 11:22:09','15032002818',10527.372,'抽烟,喝酒',null);
Query OK, 1 row affected (0.00 sec)

mysql> select * from teacher;
+----+-----------+------+---------------------+------+-------------+----------+---------------+---------+
| id | name      | age  | hire_date           | sex  | phone       | salary   | hooby         | comment |
+----+-----------+------+---------------------+------+-------------+----------+---------------+---------+
|  1 | lishichao |   18 | 1998-08-09 11:22:09 | male | 15032002818 | 10527.37 | 抽烟,喝酒     | NULL    |
+----+-----------+------+---------------------+------+-------------+----------+---------------+---------+
1 row in set (0.00 sec)

插入数据

update更新数据

语法：
    UPDATE 表名 SET
        字段1=值1,
        字段2=值2,
        WHERE CONDITION;

示例：
    UPDATE mysql.user SET password=password(‘123’) 
        where user=’root’ and host=’localhost’;

mysql> select * from teacher;
+----+-----------+------+---------------------+------+-------------+----------+---------------+---------+
| id | name      | age  | hire_date           | sex  | phone       | salary   | hooby         | comment |
+----+-----------+------+---------------------+------+-------------+----------+---------------+---------+
|  1 | lishichao |   18 | 1998-08-09 11:22:09 | male | 15032002818 | 10527.37 | 抽烟,喝酒     | NULL    |
|  3 | yanmin    |   28 | 1998-08-09 11:22:09 | male | 15032002823 |  1017.37 | 抽烟          | NULL    |
|  4 | yan       |   88 | 1998-08-09 11:22:09 | male | 15032002824 |  1017.37 | 抽烟          | NULL    |
|  5 | yan       |  108 | 1998-08-09 11:22:09 | male | 15032003824 |  1017.37 | 抽烟          | NULL    |
+----+-----------+------+---------------------+------+-------------+----------+---------------+---------+
4 rows in set (0.00 sec)

mysql># 把lishichao的年龄改成38
mysql> update teacher set age=38 where name='lishichao';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from teacher;
+----+-----------+------+---------------------+------+-------------+----------+---------------+---------+
| id | name      | age  | hire_date           | sex  | phone       | salary   | hooby         | comment |
+----+-----------+------+---------------------+------+-------------+----------+---------------+---------+
|  1 | lishichao |   38 | 1998-08-09 11:22:09 | male | 15032002818 | 10527.37 | 抽烟,喝酒     | NULL    |
|  3 | yanmin    |   28 | 1998-08-09 11:22:09 | male | 15032002823 |  1017.37 | 抽烟          | NULL    |
|  4 | yan       |   88 | 1998-08-09 11:22:09 | male | 15032002824 |  1017.37 | 抽烟          | NULL    |
|  5 | yan       |  108 | 1998-08-09 11:22:09 | male | 15032003824 |  1017.37 | 抽烟          | NULL    |
+----+-----------+------+---------------------+------+-------------+----------+---------------+---------+
4 rows in set (0.00 sec)

mysql> # 把所有人的年龄改为18
mysql> update teacher set age=18;
Query OK, 4 rows affected (0.01 sec)
Rows matched: 4  Changed: 4  Warnings: 0

mysql> select * from teacher;
+----+-----------+------+---------------------+------+-------------+----------+---------------+---------+
| id | name      | age  | hire_date           | sex  | phone       | salary   | hooby         | comment |
+----+-----------+------+---------------------+------+-------------+----------+---------------+---------+
|  1 | lishichao |   18 | 1998-08-09 11:22:09 | male | 15032002818 | 10527.37 | 抽烟,喝酒     | NULL    |
|  3 | yanmin    |   18 | 1998-08-09 11:22:09 | male | 15032002823 |  1017.37 | 抽烟          | NULL    |
|  4 | yan       |   18 | 1998-08-09 11:22:09 | male | 15032002824 |  1017.37 | 抽烟          | NULL    |
|  5 | yan       |   18 | 1998-08-09 11:22:09 | male | 15032003824 |  1017.37 | 抽烟          | NULL    |
+----+-----------+------+---------------------+------+-------------+----------+---------------+---------+
4 rows in set (0.00 sec)

更新数据

delete删除数据

语法：
    DELETE FROM 表名 
        WHERE CONITION;  # 条件

示例：
    DELETE FROM mysql.user 
        WHERE password=’’;

mysql> select * from teacher;
+----+-----------+------+---------------------+------+-------------+----------+---------------+---------+
| id | name      | age  | hire_date           | sex  | phone       | salary   | hooby         | comment |
+----+-----------+------+---------------------+------+-------------+----------+---------------+---------+
|  1 | lishichao |   18 | 1998-08-09 11:22:09 | male | 15032002818 | 10527.37 | 抽烟,喝酒     | NULL    |
|  3 | yanmin    |   18 | 1998-08-09 11:22:09 | male | 15032002823 |  1017.37 | 抽烟          | NULL    |
|  4 | yan       |   18 | 1998-08-09 11:22:09 | male | 15032002824 |  1017.37 | 抽烟          | NULL    |
|  5 | yan       |   18 | 1998-08-09 11:22:09 | male | 15032003824 |  1017.37 | 抽烟          | NULL    |
+----+-----------+------+---------------------+------+-------------+----------+---------------+---------+
4 rows in set (0.00 sec)

mysql> delete from teacher where name='lishichao'; #删除name='lishichao'的记录
Query OK, 1 row affected (0.00 sec)

mysql> delete from teacher; # 清空表
Query OK, 3 rows affected (0.00 sec)

mysql> select * from teacher;
Empty set (0.00 sec)

delete from 表名;     #逻辑删除，一行一行删。
truncate table 表名;  #物理删除，pages(block)，效率高。

删除数据

select查询数据

1.单表查询

单表查询（https://www.cnblogs.com/root0/articles/10566483.html）

2.联表查询

多表查询（https://www.cnblogs.com/root0/articles/10575320.html）

六. 事务和锁

begin;  # 开启事务
select * from emp where id = 1 for update;  # 查询id值，for update添加行锁；
update emp set salary=10000 where id = 1; # 完成更新
commit; # 提交事务

七. 索引

主键
unique
拥有这两个约束的所有的字段 都自带索引
自己添加索引:
    create index 索引名 on 表名(字段名)

posted @ 2019-03-19 15:45 LiShiChao 阅读(389) 评论(0) 收藏举报

刷新页面返回顶部

平凡之路

生命在于折腾，生命不息，折腾不止。 ----LSC

MySQL数据库

内容概要

一. 存储引擎

二. mysql中的数据类型

数字类型

字符串类型

日期和时间类型

ENUM和SET类型

三. 数据库约束

not null

default

unique

auto_increment

primary key

foreign key

四. 表操作

创建表

五. 数据操作

insert插入数据

update更新数据

delete删除数据

select查询数据

六. 事务和锁

七. 索引

公告