创建表
创建表
create table 表名(
字段名1 数据类型 [约束条件],
字段名2 数据类型 [约束条件],
...
其它约束条件
)其它选项(例如存储引擎 字符集等选项)
例如:
CREATE TABLE person(
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(20) NOT NULL
)ENGINE=INNODB AUTO_INCREMENT=100 DEFAULT CHARSET=utf8
show create table table_name:查看表的定义信息
mysql> show create table person;
+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| person | CREATE TABLE `person` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=100 DEFAULT CHARSET=utf8 |
+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
describe table_name:查看表结构
mysql> describe person;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
操作表
1.修改表名
ALTER TABLE old_table_name RENAME new_table_name
2.在表的最后一个位置增加字段
ALTER TABLE table_name ADD 属性名 属性类型
3.在表的第一个位置增加字段
ALTER TABLE table_name ADD 属性名 属性类型 FIRST
4.在表的指定位置之后增加字段
ALTER TABLE table_name ADD 属性名 属性类型 AFTER 属性名
5.删除字段
ALTER TABLE table_name DROP 属性名
6.修改字段的数据类型
ALTER TABLE table_name MODIFY 属性名 属性类型
7.修改字段的数据类型和属性
ALTER TABLE table_name CHANGE 旧属性名 新属性名 属性类型
8.修改字段顺序
8.1 ALTER TABLE table_name MODIFY 属性名 数据类型 FIRST
8.2 ALTER TABLE table_name MODIFY 属性名1 数据类型 AFTER 属性名2
9.完整性约束
NOT NULL:约束字段不能为空
DEFAULT:设置默认值
UNIQUE KEY(UK):约束字段的值唯一
PRIMARY KEY(PK):设置主键,作为该表唯一标识
AUTO_INCREMENT:设置外键
FOREIGN KEY(FK)
10.MySQL默认情况是不区分大小写的,比如person里面有两条记录
mysql> select * from person;
+-----+-------+
| id | name |
+-----+-------+
| 100 | admin |
| 101 | ADMIN |
| 102 | aDMIn |
+-----+-------+
3 rows in set (0.00 sec)
使用查询语句select * from person where name="admin";,得到如下结果
mysql> select * from person where name="admin";
+-----+-------+
| id | name |
+-----+-------+
| 100 | admin |
| 101 | ADMIN |
| 102 | aDMIn |
+-----+-------+
3 rows in set (0.00 sec)
解决办法:在字段数据类型后面加上binary
修改字段name
alter table person name varchar(20)binary not null;
此时区分大小写
mysql> select * from person where name="admin";
+-----+-------+
| id | name |
+-----+-------+
| 100 | admin |
+-----+-------+
1 row in set (0.00 sec)
设置外键约束
外键约束主要用于定义表与表之间的关系。表A外键字段的取值,要么是NULL,要么是表B主键字段的取值(此时将表A称为表B的子表,表B称为表A的父表)。在表A中设置外键的语法规则如下(所关联的字段数据类型一定要相等)。constraint 约束名 foreign key(表A字段名或字段名列表)references 表B(字段名或字段名列表)[on delete 级联选项] [on update 级联选项]
级联选项有四种取值,意义如下:
(1)cascade:父表记录的删除(delete) 或者修改(update)操作,会自动删除或者修改表中与之对应的记录。
(2)set null:父表记录的删除(delete) 或者修改(update)操作,会将子表中与之对应记录的外键自动设置为NULL。
(3)no action:父表记录的删除(delete) 或者修改(update)操作,如果子表存在与之对应的记录,n那么删除或修改操作将失败。
(4)restrict:与no action功相似,且为联机选项的默认值。
示例代码:
CREATE TABLE address(
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
post_code CHAR(10) DEFAULT NULL,
city VARCHAR(255) NOT NULL,
PRIMARY KEY(id)
)ENGINE=INNODB AUTO_INCREMENT=100 DEFAULT CHARSET=utf8
CREATE TABLE person(
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
address_id INT unsigned DEFAULT NULL,
name VARCHAR(20)BINARY NOT NULL,
PRIMARY KEY(id),
KEY index_person_address (address_id),
CONSTRAINT fk_person_address FOREIGN KEY(address_id)REFERENCES address(id) ON DELETE CASCADE ON UPDATE CASCADE
)ENGINE=INNODB AUTO_INCREMENT=100 DEFAULT CHARSET=utf8