2.数据表的创建
CREATE TABLE [IF NOT EXISTS] table_name(
column_name data_type,
......
)
mysql> use T2;
Database changed
mysql> SELECT DATABASE(); //这里我们可以看到我们所使用的数据库
+------------+
| DATABASE() |
+------------+
| T2 |
+------------+
1 row in set (0.00 sec)
mysql> CREATE TABLE tb1( //创建一张表
-> username VARCHAR(20),
-> age TINYINT UNSIGNED,
-> salary FLOAT(8,2) UNSIGNED 这里指我们的工资定义为无符号，共8位，小数点占两位
-> );
Query OK, 0 rows affected (0.12 sec)
3.数据表的查看：
SHOW TABLES [FROM db_name]
[LIKE 'pattern' | WHERE expr]
mysql> SHOW TABLES FROM information_schema;
+---------------------------------------+
| Tables_in_information_schema |
+---------------------------------------+
| CHARACTER_SETS |
| COLLATIONS |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| COLUMNS |
| COLUMN_PRIVILEGES |
| ENGINES |
| EVENTS |
| FILES |
| GLOBAL_STATUS |
| GLOBAL_VARIABLES |
| KEY_COLUMN_USAGE |
| PARTITIONS |
| PLUGINS |
| PROCESSLIST |
| PROFILING |
| REFERENTIAL_CONSTRAINTS |
| ROUTINES |
| SCHEMATA |
| SCHEMA_PRIVILEGES |
| SESSION_STATUS |
| SESSION_VARIABLES |
| STATISTICS |
| TABLES |
| TABLE_CONSTRAINTS |
| TABLE_PRIVILEGES |
| TRIGGERS |
| USER_PRIVILEGES |
| VIEWS |
+---------------------------------------+
28 rows in set (0.00 sec)
查看数据表结构：
mysql> SHOW COLUMNS FROM tb1;
+----------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------------+------+-----+---------+-------+
| username | varchar(20) | YES | | NULL | |
| age | tinyint(3) unsigned | YES | | NULL | |
| salary | float(8,2) unsigned | YES | | NULL | |
+----------+---------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
4.插入记录：
INSERT [INTO] tbl_name [(col_name,...)] VALUES(val,...)
下面我们在tb1表里面插入三条记录：
mysql> INSERT INTO tb1 VALUES('Tom',25,8000.25);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO tb1 VALUES('杨海迎',23,10000.25);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT tb1 VALUES('李玉',21,10000.25);
Query OK, 1 row affected (0.00 sec)
我们只给表里面的某个字段赋值：
mysql> INSERT tb1(username,salary) VALUES('John',4500.88);
Query OK, 1 row affected (0.08 sec)
5.查看表中我们上面添加的记录：
mysql> select * from tb1;
+-----------+------+----------+
| username | age | salary |
+-----------+------+----------+
| Tom | 25 | 8000.25 |
| 杨海迎 | 23 | 10000.25 |
| 李玉 | 21 | 10000.25 |
| John | NULL | 4500.88 |
+-----------+------+----------+
4 rows in set (0.00 sec)
6.空值与非空值
NULL，字段值可以为空
NOT NULL，字段值禁止为空
我们新创建一张表指定表结构
mysql> CREATE TABLE tb2(
-> username VARCHAR(20) NOT NULL,
-> age TINYINT UNSIGNED NULL
-> );
Query OK, 0 rows affected (0.13 sec)
mysql> SHOW COLUMNS FROM tb2;
+----------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------------+------+-----+---------+-------+
| username | varchar(20) | NO | | NULL | |
| age | tinyint(3) unsigned | YES | | NULL | |
+----------+---------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> INSERT tb2 VALUES('TOM',NULL);
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM tb2;
+----------+------+
| username | age |
+----------+------+
| TOM | NULL |
+----------+------+
1 row in set (0.00 sec)
如果我们将username字段的值赋为NULL，系统会提示错误，记录不能被插入。
7.自动编号：
AUTO_INCREMENT
自动编号，且必须与主键组合使用
默认情况下，起始值为1，每次的增量为1
mysql> CREATE TABLE tb3(
-> id SMALLINT UNSIGNED AUTO_INCREMENT,
-> username VARCHAR(30) NOT NULL,
-> );
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 4
mysql>
输出上述错误，是因为我们没有定义主键，这里我们需要定义一个主键
8.PRIMARY KEY
主键约束
每张数据表只能存在一个主键
主键保证记录的唯一性
主键自动为NOT NULL
mysql> CREATE TABLE tb3(
-> id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
-> username VARCHAR(30) NOT NULL
-> );
Query OK, 0 rows affected (0.12 sec)
mysql> SHOW COLUMNS FROM tb3;
+----------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+----------------+
| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(30) | NO | | NULL | |
+----------+----------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
mysql> INSERT tb3(username) VALUES('TOM');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT tb3(username) VALUES('John');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT tb3(username) VALUES('marry');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT tb3(username) VALUES('Rose');
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM tb3; //我们去查看它的记录看它的id编号为1-4自动增长
+----+----------+
| id | username |
+----+----------+
| 1 | TOM |
| 2 | John |
| 3 | marry |
| 4 | Rose |
+----+----------+
4 rows in set (0.00 sec)
下面我们来做一个测试如果我们在定义主键的时候不添加AUTO_INCREMENT自动编号，然后查看其是否进行了自动编号，我们看到我们的记录添加是成功的，因此，PRIMARY不一定要与AUTO_INCREMENT一起使用，但是AUTO_INCREMENT必须与PRIMARY一起使用。
mysql> CREATE TABLE tb4(
-> id SMALLINT UNSIGNED PRIMARY KEY,
-> username VARCHAR(20) NOT NULL
-> );
Query OK, 0 rows affected (0.12 sec)
mysql> SHOW COLUMNS FROM tb4;
+----------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+-------+
| id | smallint(5) unsigned | NO | PRI | NULL | |
| username | varchar(20) | NO | | NULL | |
+----------+----------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> INSERT tb4 VALUES(4,'TOM');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT tb4 VALUES(22,'JOHN');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT tb4 VALUES(23,'Rose');
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM tb4;
+----+----------+
| id | username |
+----+----------+
| 4 | TOM |
| 22 | JOHN |
| 23 | Rose |
+----+----------+
3 rows in set (0.00 sec)
9.初涉唯一约束
UNIQUE KEY
唯一约束
唯一约束可以保证记录的唯一性
唯一约束的字段可以为空值（NULL）
每张数据表可以存在多个唯一约束
下面我们来创建一个既有主键约束，又有唯一约束的数据表
mysql> CREATE TABLE tb5(
-> id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
-> username VARCHAR(20) NOT NULL UNIQUE KEY,
-> age tinyint UNSIGNED
-> );
Query OK, 0 rows affected (0.07 sec)
mysql> SHOW COLUMNS FROM tb5;
+----------+----------------------+------+-----+---------+----------------+
| 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 | | NULL | |
+----------+----------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql> INSERT tb5(username,age) VALUES('TOM',22);
Query OK, 1 row affected (0.00 sec)
主键约束一张表只能存在一个，而唯一约束一张表可以存在多个
10.默认约束(DEFAULT)
默认值
当插入记录时，如果没有明确为字段赋值，则自动赋予默认值
mysql> CREATE TABLE tb5(
-> id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
-> username VARCHAR(20) NOT NULL UNIQUE KEY,
-> sex ENUM('1','2','3') DEFAULT '3'
-> );
Query OK, 0 rows affected (0.12 sec)
mysql> SHOW COLUMNS FROM tb6;
+----------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+----------------+
| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(20) | NO | UNI | NULL | |
| sex | enum('1','2','3') | YES | | 3 | |
+----------+----------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql> INSERT tb6(username) VALUES('TOM');
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM tb6;
+----+----------+------+
| id | username | sex |
+----+----------+------+
| 1 | TOM | 3 | //我们可以看到我们的性别为默认值3
+----+----------+------+
1 row in set (0.00 sec)