共计 7099 个字符,预计需要花费 18 分钟才能阅读完成。
数据库是一个可以存放数据库对象的容器,数据库对象包括:表、视图、存储过程、函数、触发器、事件。其中,表是数据库最基本的元素,是其他数据库对象的前提条件。
表中的一列称为一个字段,一行称为一条记录。
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) |
本文永久更新链接地址:http://www.linuxidc.com/Linux/2016-11/137207.htm
正文完
星哥玩云-微信公众号
