一、MySQL数据库表操作
MySQL表的基本概念
在windows中有个程序叫做excel. 而Excel文件中存在了如sheet1、sheet2、sheet3的表, 所有的sheet都存储在这个Excel文件中, 在某个sheet中有相应的数据.
回到数据库和表的关系上来说, 这个Excel文件就是一个数据库, 所有的sheet就是存储在库中的表, 表中去存储数据, 而我们学习的MySQL程序就是Excel程序,它是用来集中管理这些Excel文件的工具. 而我们在工作中又称这种机制类型为: 关系型数据库
1.1、MySQL表的操作DDL
数据库DDL操作
系统数据库(了解)
information_schema: 虚拟库,主要存储了系统中的一些数据库对象的信息,例如用户表信息、列信息、权限信息、字符信息等
performance_schema: 主要存储数据库服务器的性能参数
mysql: 授权库,主要存储系统用户的权限信息
sys: 主要存储数据库服务器的性能参数(目标是把performance_schema的把复杂度降低)
创建数据库:DDL
1. mysqladmin -u root -p1 create db1
2. 直接去创建数据库目录并且修改权限
3. mysql> create database newrain;
数据库命名规则:
区分大小写
唯一性
不能使用关键字如 create SELECT
不能单独使用数字
查看数据库
mysql> show databases; //查看所有数据库
mysql> show create database laowang; //查看创建的库信息
mysql> SELECT database(); //查看当前库
切换数据库
mysql> use laowang
mysql> show tables;
删除数据库
DROP DATABASE 数据库名;
表的DDL操作
这些操作都是数据库管理中最基本,也是最重要的操作。
内容包括:
创建表 create table
查看表结构 desc table, show create table
表完整性约束
修改表 alter table
复制表 create table ...
删除表 drop table
表是数据库存储数据的基本单位,由若干个字段组成,主要用来存储数据记录。
命令行操作数据库(脚本)
mysql>create database db2;
mysql>use db2
mysql>create table t1(name char(20),age int(2));
# mysql -u root -p1 -e "create database newrain"
# mysql -u root -p1 -e "use newrain;create table t2(name char(20),age int(3))"
在mysql客户端内执行系统命令
mysql> system ls
mysql> \! ls
edit
创建表
表:school.student1
字段 字段 字段 字段
id name sex age
1 tom male 23 记录
2 jack male 21 记录
3 alice female 19 记录
语法:
create table 表名(
字段名1 类型[(宽度) 约束条件],
字段名2 类型[(宽度) 约束条件],
字段名3 类型[(宽度) 约束条件]
)[存储引擎 字符集];
==在同一张表中,字段名是不能相同
==宽度和约束条件可选
==字段名和类型是必须的
mysql> CREATE DATABASE school;
mysql> use school;
mysql> create table student1(
-> id int,
-> name varchar(50),
-> sex enum('m','FROM'),
-> age int
-> );
Query OK, 0 rows affected (0.03 sec)
查看表(当前所在库)
mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| student1 |
+------------------+
1 row in set (0.00 sec)
mysql> desc student1; //查看表结构
mysql> show create table student1;
mysql> show table status like 'student1' \G 显示数据的详细信息
查看表内容
mysql> SELECT id,name,sex,age from student1; //查询表中指定字段的值
Empty set (0.00 sec)
mysql> SELECT * from student1; //查询表中所有字段的值
Empty set (0.00 sec)
mysql> SELECT name,age from student1; //查询表中指定字段的值
Empty set (0.00 sec)
修改表:
alter 修改表名称 修改字段名称 修改字段数据类型 修改字段的修饰符
insert 插入数据
delete 删除数据
update 更新数据
修改表名称
mysql> rename table emp to abc;
mysql> alter table abc rename emp;
添加新字段
mysql> create table t1(id int(5),name varchar(20));
mysql> alter table t1 add math int(10);
mysql> alter table t1 add (chinese int(10),english int(10));
修改字段数据类型、修饰符(约束)
mysql> alter table t1 modify chinese int(5) not null;
修改字段名称、数据类型、修饰符(约束)
mysql> alter table t1 change chinese china int(6);
mysql> alter table t1 change english en int(6) after id;
mysql> alter table t1 change en en int(6) after name;
mysql> alter table t1 modify en int(6) first;
mysql> alter table t1 modify en int(6) after id;
删除字段
mysql> alter table t1 drop en;
插入数据(添加记录)
字符串必须引号引起来
mysql> insert into t1(id,name,math,china) values(1,"wing",80,90);
mysql> insert into t1(id,name,math,china) values(2,"king",70,100),(3,"tom",50,70);
mysql> insert into t1 values(4,"xiaosan",50,100);
mysql> insert into t1(id,math) values(5,70);
mysql> insert into t1 set id=6,math=65;
更新记录
mysql> update t1 set name="lili" WHERE id=5;
删除记录
mysql> delete from t1 WHERE id=6;
mysql> delete from t1; //删除所有记录
表复制:
复制一张表
mysql> create table t10(SELECT * from t3);
mysql> create table t10(SELECT id,name from t3);
复制表结构
mysql> desc t3;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id1 | int(11) | YES | | NULL | |
| id2 | int(6) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.01 sec)
mysql> create table t13(SELECT id1,id2 from t3 WHERE 5=4);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc t13;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id1 | int(11) | YES | | NULL | |
| id2 | int(6) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> create table t14(SELECT id1 from t3 WHERE 5=4);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc t14;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id1 | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)
mysql> create table t4(SELECT * from t3 WHERE 5=4);
mysql> create table t4(SELECT id,name from t3 WHERE 5=4);
复制记录
mysql> insert into t3 SELECT * from t10 WHERE id=9; 俩表格字段要相同
删除表
mysql> drop table t1;
删除库
mysql> drop database newrain;
了解(!!!)
表完整性约束
作用:用于保证数据的完整性和一致性
==============================================================
约束条件 说明
NOT NULL 标识该字段不能为null
UNIQUE KEY (UK) 标识该字段的值是唯一的,可以为空,一个表中可以有多个UNIQUE KEY
AUTO_INCREMENT 标识该字段的值自动增长(整数类型,而且为主键)
DEFAULT 为该字段设置默认值
UNSIGNED 无符号(正数)
ZEROFILL 使用0填充,例如0000001
说明:
1. 是否允许为空,默认NULL;可设置NOT NULL,字段不允许为空,必须赋值
2. 字段是否有默认值,缺省的默认值是NULL,如果插入记录时不给字段赋值,此字段使用默认值
sex enum('male','female') not null default 'male'
age int unsigned NOT NULL default 20 必须为正值(无符号) 不允许为空 默认是20
1.2、MySQL数据类型
一、MySQL常见的数据类型
常见的数据类型
数值类型:
整数类型 TINYINT SMALLINT MEDIUMINT INT BIGINT
整型可以指定是有符号的和无符号的,默认是有符号的
可以通过UNSIGNED来说明某个字段是无符号的。
浮点数类型 FLOAT DOUBLE
字符串类型:
CHAR系列 CHAR VARCHAR
BINARY系列 BINARY VARBINARY
枚举类型: ENUM
集合类型: SET
时间和日期类型:
DATE TIME DATETIME TIMESTAMP YEAR
二、数据类型测试
===整数类型测试:tinyint(有符号型最大值127),int(有符号型最大值2147483647)
作用:用于存储用户的年龄、游戏的Level、经验值等。
LAB1(案例1):
mysql> create database db1;
mysql> use db1;
mysql> create table test1(
-> tinyint_test tinyint,
-> int_test int
-> );
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)
mysql> insert into test1 values (111,111);
Query OK, 1 row affected (0.09 sec)
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(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
//测试结果,默认有符号,超过存储范围出错。
LAB2: 无符号整型测试
mysql> create table test2(
-> tinyint_test tinyint unsigned, //约束条件unsigned限定只能存正值(无符号)
-> int_test int unsigned
-> );
Query OK, 0 rows affected (0.00 sec)
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)
mysql> insert into test2(tinyint_test) values(111);
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
测试整数类型的显示宽度(int不限制宽度)
mysql> create table t1 (
-> id1 int,
-> id2 int(6)
-> );
mysql> desc t1;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id1 | int(11) | YES | | NULL | |
| id2 | int(6) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
mysql> insert into t1 values(1,1);
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * from t1;
+------+------+
| id1 | id2 |
+------+------+
| 1 | 1 |
+------+------+
1 row in set (0.00 sec)
mysql> create table t2 (
-> id1 int zerofill,
-> id2 int(6) zerofill
-> );
Query OK, 0 rows affected (0.05 sec)
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)
mysql> insert into t2 values(2,2);
Query OK, 1 row affected (0.01 sec)
mysql> SELECT * from t2;
+------------+--------+
| id1 | id2 |
+------------+--------+
| 0000000002 | 000002 |
+------------+--------+
1 row in set (0.00 sec)
mysql> insert into t2 values(3,2222222); //插入大于宽度限制的值,仍然可以存储
Query OK, 1 row affected (0.03 sec)
mysql> SELECT * from t2;
+------------+---------+
| id1 | id2 |
+------------+---------+
| 0000000002 | 000002 |
| 0000000003 | 2222222 |
+------------+---------+
2 rows in set (0.00 sec)
结论:整形的宽度仅为显示宽度,不是限制。因此建议整形无须指定宽度。
===浮点数类型测试:
作用:用于存储用户的身高、体重、薪水等
浮点数和定点数都可以用类型名称后加(M,D)的方式来表示,(M,D)表示一共显示M位数字(整数位
+小数位),其中D位于小数点后面,M和D又称为精度和标度。
float表示的精度大约是7位。
mysql> create table test4(float_test float(5,2)); //一共5位,小数占2位
Query OK, 0 rows affected (0.00 sec)
mysql> desc test4;
+------------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+------------+------+-----+---------+-------+
| float_test | float(5,2) | YES | | NULL | |
+------------+------------+------+-----+---------+-------+
1 row in set (0.00 sec)
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
mysql> SELECT * from test4;
+------------+
| float_test |
+------------+
| 10.20 |
| 70.24 |
| 70.25 |
+------------+
3 rows in set (0.00 sec)
可以观察到,超出表示的范围,MySQL在保存值时会进行四舍五入。
mysql> insert into test4 values (1111.2);
ERROR 1264 (22003): Out of range value for column 'float_test' at row 1
mysql> create table t111(FROM1 float,FROM2 float(10,2));
mysql> insert into t111 values(123.123,12345678.90);
mysql> SELECT * from t111;
+---------+-------------+
| FROM1 | FROM2 |
+---------+-------------+
| 123.123 | 12345679.00 |
+---------+-------------+
注意:如果数据精度丢失,那么浮点型是按照四舍五入的方式进行计算
mysql> insert into t111 values(123.12345678,123456789.90);
ERROR 1264 (22003): Out of range value for column 'FROM2' at row 1
超出范围报错
mysql> insert into t111 values(123.12345678,99999999.99);
Query OK, 1 row affected (0.01 sec)
mysql> SELECT * from t111;
+---------+--------------+
| FROM1 | FROM2 |
+---------+--------------+
| 123.123 | 12345679.00 |
| 123.123 | 100000000.00 |
+---------+--------------+
2 rows in set (0.00 sec)
double(精度较高,更精确)精度有15位左右。
mysql> insert into t112 values(123.12345678,99999999.99);
mysql> SELECT * from t112;
+--------------+-------------+
| FROM1 | FROM2 |
+--------------+-------------+
| 123.12345678 | 99999999.99 |
+--------------+-------------+
1 row in set (0.00 sec)
定点数decimal测试(精度最高):
mysql> create table test17(id int,a float(10,8),b decimal(10,8));
Query OK, 0 rows affected (0.01 sec)
mysql> insert into test17 values (100,23.12345612,23.12345612);
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * from test17;
+------+-------------+-------------+
| id | a | b |
+------+-------------+-------------+
| 100 | 23.12345695 | 23.12345612 |
+------+-------------+-------------+
1 row in set (0.00 sec)
注:float表示的精度大约是7位。
decimal整数大位数m为65。支持小数大位数d是30。
===时间和日期类型测试:year、date、time、datetime、timestamp
作用:用于存储用户的注册时间,文章的发布时间,文章的更新时间,员工的入职时间等
mysql> create table test_time(
-> d date,
-> t time,
-> dt datetime
-> );
Query OK, 0 rows affected (0.03 sec)
mysql> desc test_time;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| d | date | YES | | NULL | |
| t | time | YES | | NULL | |
| dt | datetime | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.01 sec)
mysql> SELECT now();
+---------------------+
| now() |
+---------------------+
| 2019-08-19 06:17:28 |
+---------------------+
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)
mysql> SELECT * from test_time;
+------------+----------+---------------------+
| d | t | dt |
+------------+----------+---------------------+
| 2013-12-18 | 00:06:10 | 2013-12-18 00:06:10 |
+------------+----------+---------------------+
1 row in set (0.00 sec)
timestamp
mysql> create table d(name char(20),time timestamp);
Query OK, 0 rows affected (0.01 sec)
mysql> insert into d values("laowang",null);
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * from d;
+-----------+---------------------+
| name | time |
+-----------+---------------------+
| laowang | 2019-08-20 15:06:23 |
+-----------+---------------------+
1 row in set (0.00 sec)
注意事项:
其它的时间,按要求插入
==插入年份时,尽量使用4位值
==插入两位年份时,<=69,以20开头,比如65, 结果2065
>=70,以19开头,比如82,结果1982
mysql> create table t3(born_year year);
Query OK, 0 rows affected (0.40 sec)
mysql> desc t3;
+-----------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------+------+-----+---------+-------+
| born_year | year(4) | YES | | NULL | |
+-----------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)
mysql> insert into t3 values
-> (12),(80);
Query OK, 2 rows affected (0.06 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> SELECT * from t3;
+-----------+
| born_year |
+-----------+
| 2012 |
| 1980 |
+-----------+
2 rows in set (0.00 sec)
===字符串类型测试:CHAR、VARCHAR
作用:用于存储用户的姓名、爱好、发布的文章等
CHAR 列的长度固定为创建表时声明的长度: 0 ~ 255
VARCHAR 列中的值为可变长字符串,长度: 0 ~ 65535
注:在检索的时候,CHAR列删除了尾部的空格,而VARCHAR则保留这些空格
mysql> create table vc (
-> v varchar(4),
-> c char(4)
-> );
Query OK, 0 rows affected (0.03 sec)
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)
mysql> insert into vc values('ab ','ab ');
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> SELECT * from vc;
+------+------+
| v | c |
+------+------+
| ab | ab |
+------+------+
1 row in set (0.00 sec)
mysql> SELECT length(v),length(c) from vc;
+-----------+-----------+
| length(v) | length(c) |
+-----------+-----------+
| 4 | 2 |
+-----------+-----------+
1 row in set (0.00 sec)
mysql> SELECT concat(v,'='), concat(c,'=') from vc; //在后面加字符'=',看的更清楚
+---------------+---------------+
| concat(v,'=') | concat(c,'=') |
+---------------+---------------+
| ab = | ab= |
+---------------+---------------+
1 row in set (0.00 sec)
字符串类型测试:BINARY、VARBINARY
BINARY 和 VARBINARY类似于CHAR 和 VARCHAR,不同的是它们包含二进制字符而不包含
非二进制字符串
mysql> create table binary_t (c binary(3));
mysql> insert into binary_t values ('10');
Query OK, 1 row affected (0.01 sec)
mysql> create table binary_d (c varbinary(3));
mysql> insert into binary_d values ('10');
Query OK, 1 row affected (0.01 sec)
mysql> SELECT length(c) from binary_t WHERE c=10;
+-----------+
| length(c) |
+-----------+
| 3 |
+-----------+
1 row in set, 2 warnings (0.01 sec)
mysql> SELECT length(c) from binary_d WHERE c=10;
+-----------+
| length(c) |
+-----------+
| 2 |
+-----------+
1 row in set (0.00 sec)
===字符串类型
===ENUM类型即枚举类型、集合类型SET测试
字段的值只能在给定范围中选择
常见的是单选按钮和复选框
enum 单选 只能在给定的范围内选一个值,如性别 sex 男male/女female
set 多选 在给定的范围内可以选择一个或一个以上的值(爱好1,爱好2,爱好3...)
表school.student3
姓名 name varchar(50)
性别 sex enum('m','FROM')
爱好 hobby set('music','book','game','disc')
mysql> use school
mysql> create table student3(
-> name varchar(50),
-> sex enum('m','FROM'),
-> hobby set('music','book','game','disc')
-> );
Query OK, 0 rows affected (0.31 sec)
mysql> desc student3;
+-------+-----------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------------------------------+------+-----+---------+-------+
| name | varchar(50) | YES | | NULL | |
| sex | enum('m','FROM') | YES | | NULL | |
| hobby | set('music','book','game','disc') | YES | | NULL | |
+-------+-----------------------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> insert into student3 values
-> ('tom','m','book,game');
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * from student3;
+------+------+-----------+
| name | sex | hobby |
+------+------+-----------+
| tom | boy | book,game |
+------+------+-----------+
1 row in set (0.00 sec)
mysql> insert into student3 values ('jack','m','play');
ERROR 1265 (01000): Data truncated for column 'hobby' at row 1
mysql> show create table student3\G
*************************** 1. row ***************************
Table: student3
Create Table: CREATE TABLE `student3` (
`name` varchar(50) default NULL,
`sex` enum('m','FROM') default NULL,
`hobby` set('music','book','game','disc') default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
========================================================
MySQL约束
约束条件 说明
- NULL 标识是否允许为空,默认为NULL。
- NOT NULL 标识该字段不能为空,可以修改。
- UNIQUE KEY (UK) 标识该字段的值是唯一的,可以为空,一个表中可以有多个UNIQUE KEY
- DEFAULT 为该字段设置默认值
- UNSIGNED 无符号,正数
- PRIMARY KEY (PK) 标识该字段为该表的主键,可以唯一的标识记录,不可以为空
- AUTO_INCREMENT 标识该字段的值自动增长(整数类型,而且为主键)
- UNIQUE + NOT NULL
- FOREIGN KEY (FK) 标识该字段为该表的外键,实现表与表(父表主键/子表1外键/子表2外键)之间的关联
not null default例子
mysql> create table t7 (id int not null default 8);
Query OK, 0 rows affected (0.02 sec)
mysql> desc t7;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | NO | | 8 | |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)
mysql> insert into t7 values();
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * from t7;
+----+
| id |
+----+
| 8 |
+----+
1 row in set (0.00 sec)
mysql> insert into t7 values(NULL);
ERROR 1048 (23000): Column 'id' cannot be null
auto_increment例子
自增键,每张表只能一个字段为自增
mysql> create table t8(id int unique auto_increment,name char(10));
mysql> desc t8;
+-------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | char(10) | YES | | NULL | |
+-------+----------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
mysql> insert into t8 (name) values("newrain");
Query OK, 1 row affected (0.01 sec)
mysql> SELECT * from t8;
+----+----------+
| id | name |
+----+----------+
| 1 | newrain |
+----+----------+
1 row in set (0.00 sec)
mysql> insert into t8 (name) values("ehome");
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * from t8;
+----+----------+
| id | name |
+----+----------+
| 1 | newrain |
| 2 | ehome |
+----+----------+
2 rows in set (0.00 sec)
default例子:
mysql> create table db1.student4 (
-> id int not null,
-> name varchar(50) not null,
-> sex enum('m','FROM') default 'm' not null,
-> age int unsigned default 18 not null,
-> hobby set('music','disc','dance','book') default 'book,dance');
mysql> desc db1.student4;
+-------+------------------------------------+------+-----+------------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------------------------+------+-----+------------+-------+
| id | int(11) | NO | | NULL | |
| name | varchar(50) | NO | | NULL | |
| sex | enum('m','FROM') | NO | | m | |
| age | int(10) unsigned | NO | | 18 | |
| hobby | set('music','disc','dance','book') | YES | | dance,book | |
+-------+------------------------------------+------+-----+------------+-------+
5 rows in set (0.00 sec)
mysql> insert into student4 values (1,'jack','m',20,'book');
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * from student4;
+----+----------+-----+-----+------------+
| id | name | sex | age | hobby |
+----+----------+-----+-----+------------+
| 12 | newrain | m | 18 | dance,book |
+----+----------+-----+-----+------------+
2 rows in set (0.00 sec)
mysql> insert into student4 (id,name) values (12,"newrain");
Query OK, 1 row affected (0.01 sec)
mysql> SELECT * from student4;
+----+----------+-----+-----+------------+
| id | name | sex | age | hobby |
+----+----------+-----+-----+------------+
| 12 | newrain | m | 18 | dance,book |
| 1 | jack | m | 20 | book |
+----+----------+-----+-----+------------+
2 rows in set (0.00 sec)
mysql> insert into student4 values (3,NULL,'m',22,'book');
ERROR 1048 (23000): Column 'name' cannot be null
unique例子 unique(key) 唯一的
第一种写法:
mysql> create table department1(
-> dept_id INT,
-> dept_name varchar(30) unique,
-> comment varchar(50)
-> );
第二种写法:
mysql> create table department1(
-> dept_id INT,
-> dept_name varchar(30) unique,
-> comment varchar(50),
-> UNIQUE(dept_name)
-> );
mysql> desc department1;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| dept_id | int(11) | YES | | NULL | |
| dept_name | varchar(30) | YES | UNI | NULL | |
| comment | varchar(50) | YES | | NULL | |
+-----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> insert into department1 values (1,'zhangsan','yyy');
Query OK, 1 row affected (0.00 sec)
mysql> insert into department1 values (1,'zhangsan','yyy');
ERROR 1062 (23000): Duplicate entry 'zhangsan' for key 'dept_name'
mysql> insert into department1 values (1,'wangsi','yyy');
Query OK, 1 row affected (0.00 sec)
primary key(key)
每张表里只能有一个主键
不能为空,而且唯一
mysql> use db1;
mysql> create table t7(hostname char(20) primary key,ip char(150));
mysql> create table t9(hostname char(20),ip char(150),primary key(hostname));
mysql> alter table t7 drop primary key; //删除主键
Query OK, 3 rows affected (0.24 sec)
Records: 3 Duplicates: 0 Warnings: 0
联合主键(有一个主键不一样即可)
mysql> create table t10(hostname char(20),ip char(150),primary key(hostname,ip));
Query OK, 0 rows affected (0.02 sec)
mysql> insert into t10 values ("hah","ksk");
Query OK, 1 row affected (0.01 sec)
mysql> insert into t10 values ("hah","ksk");
ERROR 1062 (23000): Duplicate entry 'hah-ksk' for key 'PRIMARY'
mysql> insert into t10 values ("hah","ks");
Query OK, 1 row affected (0.00 sec)
mysql> insert into t10 values ("ha","ks");
Query OK, 1 row affected (0.01 sec)
索引和外键(拓展)
index(key)
索引 优化查询速度
mysql> create table t105(hostname char(20) primary key,ip char(150),index dizhi(ip));
//添加索引到列名ip,索引名为dizhi
mysql> create index dizhi on t105(ip); //单独创建索引
mysql> drop index dizhi on t105; //删除index索引
mysql> alter table t101 drop index dizhi; //删除index索引
外键foreign key (key)
mysql> create table t1(id int,manager char(10) primary key) engine = innodb;
mysql> create table t2(id int,admin char(10),foreign key (admin) references t1 (manager)) engine = innodb ;