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=utf81 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=utf81 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=utf81 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=utf81 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>
本文由职坐标整理发布,欢迎关注职坐标MySQL频道,学习更多数据库知识!