Mysql的数据表创建操作

1 创建、查看数据表

mysql> use test_db;
Database changed
mysql> CREATE TABLE tb_empl
    -> (
    -> id INT(11),
    -> name VARCHAR(25),
    -> deptId INT(11),
    -> salary FLOAT
    -> );
Query OK, 0 rows affected (0.31 sec)

mysql> show tables;
+-------------------+
| Tables_in_test_db |
+-------------------+
| tb_empl           |
+-------------------+
1 row in set (0.00 sec)

mysql>

2 创建数据表（使用主键约束，2种方式）

mysql> CREATE TABLE tb_emp2
    ->  (
    ->  id INT(11) PRIMARY KEY,
    ->  name VARCHAR(25),
    ->  deptId INT(11),
    ->  salary FLOAT
    ->  );
Query OK, 0 rows affected (0.36 sec)
mysql>

mysql> CREATE TABLE tb_emp3
    ->  (
    ->  id INT(11),
    ->  name VARCHAR(25),
    ->  deptId INT(11),
    ->  salary FLOAT,
    ->  PRIMARY KEY(id)
    ->  );
Query OK, 0 rows affected (0.33 sec)

mysql>

3 创建数据表（多字段联合主键）

mysql> CREATE TABLE tb_emp4
    ->  (
    ->  name VARCHAR(25),
    ->  deptId INT(11),
    ->  salary FLOAT,
    ->  PRIMARY KEY(name,deptId)
    ->  );
Query OK, 0 rows affected (0.52 sec)

mysql>

4 创建数据表（使用外键约束）

mysql> CREATE TABLE tb_dept1
    ->  (
    ->  id INT(11) PRIMARY KEY,
    ->  name VARCHAR(22) NOT NULL,
    ->  location VARCHAR(50)
    ->  );
Query OK, 0 rows affected (0.35 sec)

mysql> CREATE TABLE tb_emp5
    ->  (
    ->  id INT(11) PRIMARY KEY,
    ->  name VARCHAR(25),
    ->  deptId INT(11),
    ->  salary FLOAT,
    ->  CONSTRAINT fk_emp_dept1 FOREIGN KEY (deptId) REFERENCES tb_dept1(id)
    ->  );
Query OK, 0 rows affected (0.29 sec)

mysql>

5 创建数据表（非空约束）

mysql> CREATE TABLE tb_emp6
    ->  (
    ->  id INT(11) PRIMARY KEY,
    ->  name VARCHAR(25) NOT NULL,
    ->  deptId INT(11),
    ->  salary FLOAT
    ->  );
Query OK, 0 rows affected (0.27 sec)

mysql>

6 创建数据表（唯一性约束，2种方式）

mysql> CREATE TABLE tb_dept2
    -> (
    -> id      INT(11) PRIMARY KEY,
    -> name    VARCHAR(22) UNIQUE,
    -> location  VARCHAR(50)
    -> );
Query OK, 0 rows affected (0.31 sec)

mysql> SHOW CREATE TABLE tb_dept2\G
*************************** 1. row ***************************
       Table: tb_dept2
Create Table: CREATE TABLE `tb_dept2` (
  `id` int(11) NOT NULL,
  `name` varchar(22) DEFAULT NULL,
  `location` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql>

mysql> CREATE TABLE tb_dept3
    -> (
    -> id      INT(11) PRIMARY KEY,
    -> name    VARCHAR(22),
    -> location  VARCHAR(50),
    -> CONSTRAINT STH UNIQUE(name)
    -> );
Query OK, 0 rows affected (0.23 sec)

mysql> SHOW CREATE TABLE tb_dept3\G
*************************** 1. row ***************************
       Table: tb_dept3
Create Table: CREATE TABLE `tb_dept3` (
  `id` int(11) NOT NULL,
  `name` varchar(22) DEFAULT NULL,
  `location` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `STH` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql>

7 创建数据表（使用默认约束）

mysql> CREATE TABLE tb_emp7
    -> (
    -> id      INT(11) PRIMARY KEY,
    -> name   VARCHAR(25) NOT NULL,
    -> deptId  INT(11) DEFAULT 1111,
    -> salary  FLOAT
    -> );
Query OK, 0 rows affected (0.28 sec)

mysql> SHOW CREATE TABLE tb_emp7\G
*************************** 1. row ***************************
       Table: tb_emp7
Create Table: CREATE TABLE `tb_emp7` (
  `id` int(11) NOT NULL,
  `name` varchar(25) NOT NULL,
  `deptId` int(11) DEFAULT '1111',
  `salary` float DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql>

8 创建数据表（设置属性值的自加功能）

mysql> CREATE TABLE tb_emp8
    ->  (
    ->  id INT(11) PRIMARY KEY AUTO_INCREMENT,
    ->  name VARCHAR(25) NOT NULL,
    ->  deptId INT(11),
    ->  salary FLOAT
    ->  );
Query OK, 0 rows affected (0.28 sec)

mysql> SHOW CREATE TABLE tb_emp8\G
*************************** 1. row ***************************
       Table: tb_emp8
Create Table: CREATE TABLE `tb_emp8` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(25) NOT NULL,
  `deptId` int(11) DEFAULT NULL,
  `salary` float DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql>

9 对表格插入数据

mysql> insert into tb_emp8(name,deptId,salary)value('lucy',111,100),('liu',111,200),('wang',111,300);
Query OK, 3 rows affected (0.10 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from tb_emp8
    -> ;
+----+------+--------+--------+
| id | name | deptId | salary |
+----+------+--------+--------+
|  1 | lucy |    111 |    100 |
|  2 | liu  |    111 |    200 |
|  3 | wang |    111 |    300 |
+----+------+--------+--------+
3 rows in set (0.00 sec)

mysql>