02数据类型与操作数据表

1.数据类型



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)