摘要:本文对如何进行MySQL数据库的表创建操作步骤进行了详细讲解,希望学习本文对读者有所帮助,可以熟练的完成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=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频道,学习更多数据库知识!
您输入的评论内容中包含违禁敏感词
我知道了
请输入正确的手机号码
请输入正确的验证码
您今天的短信下发次数太多了,明天再试试吧!
我们会在第一时间安排职业规划师联系您!
您也可以联系我们的职业规划师咨询:
版权所有 职坐标-一站式IT培训就业服务领导者 沪ICP备13042190号-4
上海海同信息科技有限公司 Copyright ©2015 www.zhizuobiao.com,All Rights Reserved.
沪公网安备 31011502005948号