MySQL学习笔记 -- 数据表的基本操作
数据库是一个可以存放数据库对象的容器，数据库对象包括：表、视图、存储过程、函数、触发器、事件。其中，表是数据库最基本的元素，是其他数据库对象的前提条件。
表中的一列称为一个字段，一行称为一条记录。
1.数据表的创建、查看数据表、查看数据表结构
mysql> CREATE DATABASE test1; Query OK, 1 row affected (0.02 sec)
mysql> USE test1; Database changed
mysql> CREATE TABLE table1( -> id SMALLINT UNSIGNED, -> username VARCHAR(20), -> age TINYINT -> ); Query OK, 0 rows affected (0.40 sec) mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | test1 | +--------------------+ 5 rows in set (0.00 sec) mysql> SELECT DATABASE(); +------------+ | DATABASE() | +------------+ | test1 | +------------+ 1 row in set (0.00 sec) mysql> SHOW TABLES; +-----------------+ | Tables_in_test1 | +-----------------+ | table1 | +-----------------+ 1 row in set (0.00 sec) mysql> SHOW COLUMNS FROM table1; +----------+----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+----------------------+------+-----+---------+-------+ | id | smallint(5) unsigned | YES | | NULL | | | username | varchar(20) | YES | | NULL | | | age | tinyint(4) | YES | | NULL | | +----------+----------------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) mysql> DESCRIBE table1; +----------+----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+----------------------+------+-----+---------+-------+ | id | smallint(5) unsigned | YES | | NULL | | | username | varchar(20) | YES | | NULL | | | age | tinyint(4) | YES | | NULL | | +----------+----------------------+------+-----+---------+-------+ 3 rows in set (0.00 sec)
2.数据表的删除 DROP TABLE table_name;
mysql> USE test1; Database changed
mysql> SHOW TABLES; +-----------------+ | Tables_in_test1 | +-----------------+ | table1 | | tb2 | +-----------------+ 2 rows in set (0.00 sec) mysql> DROP TABLE tb2; Query OK, 0 rows affected (0.20 sec) mysql> DESCRIBE tb2; ERROR 1146 (42S02): Table 'test1.tb2' doesn't exist
3.插入记录、查看记录 INSERT table_name [(col_name, ...)] VALUES(...);
mysql> SHOW COLUMNS FROM table1; +----------+----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+----------------------+------+-----+---------+-------+ | id | smallint(5) unsigned | YES | | NULL | | | username | varchar(20) | YES | | NULL | | | age | tinyint(4) | YES | | NULL | | +----------+----------------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) mysql> INSERT table1 VALUES(5,'Tom',22); Query OK, 1 row affected (0.05 sec) mysql> INSERT table1 (id,username) VALUES(5,'Tom'); Query OK, 1 row affected (0.10 sec) mysql> SELECT * FROM table1; +------+----------+------+ | id | username | age | +------+----------+------+ | 5 | Tom | 22 | | 5 | Tom | NULL | +------+----------+------+ 2 rows in set (0.00 sec)
4.字段的空值与非空 NULL,NOT NULL
mysql> CREATE TABLE table2( -> username VARCHAR(20) NOT NULL, -> #NULL可加可不加，不加默认可以为空# -> age TINYINT -> ); Query OK, 0 rows affected (0.25 sec) mysql> SHOW COLUMNS FROM table2; +----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+-------+ | username | varchar(20) | NO | | NULL | | | age | tinyint(4) | YES | | NULL | | +----------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql> INSERT table2 VALUES(NULL,25); ERROR 1048 (23000): Column 'username' cannot be null
5.主键约束与自动编号 PRIMARY KEY,AUTO_INCREMENT
※ 一张数据表只能存在一个主键
※ 主键能保证记录的唯一性
※ 主键自动为NOT NULL
※ AUTO_INCREMENT只能配合PRIMARY KEY使用，不能单独使用。PRIMARY KEY可以单独使用。
mysql> CREATE TABLE table3( -> id SMALLINT PRIMARY KEY AUTO_INCREMENT, -> username VARCHAR(20) -> ); Query OK, 0 rows affected (0.32 sec) mysql> DESCRIBE table3; +----------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+----------------+ | id | smallint(6) | NO | PRI | NULL | auto_increment | | username | varchar(20) | YES | | NULL | | +----------+-------------+------+-----+---------+----------------+ 2 rows in set (0.00 sec) mysql> INSERT table3 (username) VALUES('XingyaZhao'); Query OK, 1 row affected (0.05 sec) mysql> INSERT table3 (username) VALUES('XuebiBaby'); Query OK, 1 row affected (0.06 sec) mysql> INSERT table3 VALUES(4,'David'); Query OK, 1 row affected (0.07 sec) mysql> INSERT table3 (username) VALUES('Somebody'); Query OK, 1 row affected (0.09 sec) mysql> SELECT * FROM table3; +----+------------+ | id | username | +----+------------+ | 1 | XingyaZhao | | 2 | XuebiBaby | | 4 | David | | 5 | Somebody | +----+------------+ 4 rows in set (0.00 sec)
mysql> CREATE TABLE table4( -> id SMALLINT UNSIGNED PRIMARY KEY, -> username VARCHAR(20) -> ); Query OK, 0 rows affected (0.30 sec) mysql> SHOW COLUMNS FROM table4; +----------+----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+----------------------+------+-----+---------+-------+ | id | smallint(5) unsigned | NO | PRI | NULL | | | username | varchar(20) | YES | | NULL | | +----------+----------------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql> INSERT table4 VALUES(6,'ChuanDao'); Query OK, 1 row affected (0.07 sec) mysql> INSERT table4 VALUES(3,'Pigiu'); Query OK, 1 row affected (0.04 sec) mysql> INSERT table4 VALUES(3,'York'); ERROR 1062 (23000): Duplicate entry '3' for key 'PRIMARY' mysql> SELECT * FROM table4; +----+----------+ | id | username | +----+----------+ | 3 | Pigiu | | 6 | ChuanDao | +----+----------+ 2 rows in set (0.00 sec)
6.唯一约束 UNIQUE KEY
※ 唯一约束可以保证某个字段中每个记录的唯一性
※ 唯一约束的字段可以为空值
※ 每张数据表可以存在多个唯一约束
mysql> CREATE TABLE table5( -> id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, -> username VARCHAR(20) NOT NULL UNIQUE KEY, -> age TINYINT UNSIGNED NOT NULL -> ); Query OK, 0 rows affected (0.60 sec) mysql> SHOW COLUMNS FROM table5; +----------+----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+----------------------+------+-----+---------+----------------+ | id | smallint(5) unsigned | NO | PRI | NULL | auto_increment | | username | varchar(20) | NO | UNI | NULL | | | age | tinyint(3) unsigned | NO | | NULL | | +----------+----------------------+------+-----+---------+----------------+ 3 rows in set (0.02 sec) mysql> INSERT table5 (username,age) VALUES('XingyaZhao',22); Query OK, 1 row affected (0.05 sec) mysql> INSERT table5 (username,age) VALUES('XuebiBaby',21); Query OK, 1 row affected (0.07 sec) mysql> INSERT table5 (username,age) VALUES('XingyaZhao',18); ERROR 1062 (23000): Duplicate entry 'XingyaZhao' for key 'username' mysql> SELECT * FROM table5; +----+------------+-----+ | id | username | age | +----+------------+-----+ | 1 | XingyaZhao | 22 | | 2 | XuebiBaby | 21 | +----+------------+-----+ 3 rows in set (0.00 sec)
7.默认约束 DEFAULT
mysql> CREATE TABLE table6( -> id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, -> username VARCHAR(20) NOT NULL UNIQUE KEY, -> age TINYINT UNSIGNED DEFAULT 20 -> ); Query OK, 0 rows affected (0.29 sec) mysql> DESCRIBE table6; +----------+----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+----------------------+------+-----+---------+----------------+ | id | smallint(5) unsigned | NO | PRI | NULL | auto_increment | | username | varchar(20) | NO | UNI | NULL | | | age | tinyint(3) unsigned | YES | | 20 | | +----------+----------------------+------+-----+---------+----------------+ 3 rows in set (0.00 sec) mysql> INSERT table6 (username) VALUES('Xingya'); Query OK, 1 row affected (0.08 sec) mysql> INSERT table6 (username) VALUES('Jude'); Query OK, 1 row affected (0.05 sec) mysql> INSERT table6 (username,age) VALUES('Tom',21); Query OK, 1 row affected (0.08 sec) mysql> SELECT * FROM table6; +----+----------+------+ | id | username | age | +----+----------+------+ | 1 | Xingya | 20 | | 2 | Jude | 20 | | 3 | Tom | 21 | +----+----------+------+ 3 rows in set (0.00 sec)
8.外键约束 FOREIGN KEY
※ 父表和子表必须使用相同的存储引擎 InnoDB
※ 外键列和参照列必须具有相似的数据类型。其中数字的长度和是否有符号位必须相同；而字符的长度可以不同。
※ 外键列和参照列必须创建索引。如果外键列不存在索引的话，MySQL将自动创建索引。
mysql> CREATE TABLE provinces( -> id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, -> pname VARCHAR(20) NOT NULL -> ); Query OK, 0 rows affected (0.49 sec) mysql> CREATE TABLE users( -> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, -> username VARCHAR(20) NOT NULL, -> pid SMALLINT UNSIGNED, -> FOREIGN KEY (pid) REFERENCES provinces (id) -> ); Query OK, 0 rows affected (0.34 sec) mysql> SHOW INDEXES FROM provinces\G *************************** 1. row *************************** Table: provinces Non_unique: 0 Key_name: PRIMARY Seq_in_index: 1 Column_name: id Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: 1 row in set (0.00 sec) mysql> SHOW INDEXES FROM users\G *************************** 1. row *************************** Table: users Non_unique: 0 Key_name: PRIMARY Seq_in_index: 1 Column_name: id Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: *************************** 2. row *************************** Table: users Non_unique: 1 Key_name: pid Seq_in_index: 1 Column_name: pid Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: Index_comment: 2 rows in set (0.00 sec)
