mysql中创建数据表
时间:2015-05-02 14:34 来源:linux.it.net.cn 作者:IT
从今天开始学习mysql数据库，希望以后能够记录自己学习的点点滴滴
mysql中创建数据表
1.创建一个数据库为test_db
mysql> create database test_db;
Query OK, 1 row affected (0.00 sec)
切换到test_db上
mysql> use test_db;
Database changed
mysql>
2.创建一个表tb_emp1
create table tb_emp1
(
id INT(11),
name VARCHAR(25),
deptid INT (11),
salary FLOAT
);
mysql> create table tb_emp1
-> (
-> id INT(11),
-> name VARCHAR(25),
-> deptid INT (11),
-> salary FLOAT
-> );
Query OK, 0 rows affected (0.09 sec)
mysql> show tables;
+-------------------+
| Tables_in_test_db |
+-------------------+
| tb_emp1 |
+-------------------+
1 row in set (0.01 sec)
mysql> show create table tb_emp1 \G;
*************************** 1. row ***************************
Table: tb_emp1
Create Table: CREATE TABLE `tb_emp1` (
`id` int(11) DEFAULT NULL,
`name` varchar(25) DEFAULT NULL,
`deptid` int(11) DEFAULT NULL,
`salary` float DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
ERROR:
No query specified
mysql>
3.使用主键约束
语法是字段名数据类型 primary key [默认值]
create table tb_emp2
(
id INT(11) PRIMARY KEY,
name VARCHAR(25),
deptid INT (11),
salary FLOAT
);
或者如下
create table tb_emp2
(
id INT(11) ,
name VARCHAR(25),
deptid INT (11),
salary FLOAT
PRIMARY KEY (id)
);
mysql> create table tb_emp2
-> (
-> id INT(11) PRIMARY KEY,
-> name VARCHAR(25),
-> deptid INT (11),
-> salary FLOAT
-> );
Query OK, 0 rows affected (0.03 sec)
mysql> show tables;
+-------------------+
| Tables_in_test_db |
+-------------------+
| tb_emp1 |
| tb_emp2 |
+-------------------+
2 rows in set (0.00 sec)
mysql> show create table tb_emp2 \G;
*************************** 1. row ***************************
Table: tb_emp2
Create Table: CREATE TABLE `tb_emp2` (
`id` int(11) NOT NULL,
`name` varchar(25) DEFAULT NULL,
`deptid` int(11) DEFAULT NULL,
`salary` float DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
ERROR:
No query specified
mysql>
4.表中使用外键
语法[constraint <外键名>] foreign key 字段名1 [,字段名2,…] references <主表名>
主键列1[,主键列2,…]
主表：
create table tb_dept
(
deptid int(11) primary key,
name varchar(22) not null,
location varchar(50)
)
从表：
create table tb_emp3
(
id int(11) primary key,
name varchar(25),
location varchar(50),
deptid int(11),
salary float,
constraint fk_emp_dept foreign key (deptid) references tb_dept (deptid)
);
mysql> create table tb_dept
-> (
-> deptid int(11) primary key,
-> name varchar(22) not null,
-> location varchar(50)
-> )
-> ;
Query OK, 0 rows affected (0.05 sec)
mysql> create table tb_emp3
-> (
-> id int(11) primary key,
-> name varchar(25),
-> location varchar(50),
-> deptid int(11),
-> salary float,
-> constraint fk_emp_dept foreign key (deptid) references tb_dept (deptid)
-> );
Query OK, 0 rows affected (0.06 sec)
mysql>
mysql> show tables;
+-------------------+
| Tables_in_test_db |
+-------------------+
| tb_dept |
| tb_emp1 |
| tb_emp2 |
| tb_emp3 |
+-------------------+
4 rows in set (0.00 sec)
mysql> show create table tb_emp3 \G;
*************************** 1. row ***************************
Table: tb_emp3
Create Table: CREATE TABLE `tb_emp3` (
`id` int(11) NOT NULL,
`name` varchar(25) DEFAULT NULL,
`location` varchar(50) DEFAULT NULL,
`deptid` int(11) DEFAULT NULL,
`salary` float DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `fk_emp_dept` (`deptid`),
CONSTRAINT `fk_emp_dept` FOREIGN KEY (`deptid`) REFERENCES `tb_dept` (`deptid`
)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
ERROR:
No query specified
mysql>
5.表空使用非空约束(not null )和唯一约束(unique)
语法字段名数据类型 not null , 字段名数据类型 unique
create table tb_dept1
(
deptid int(11) primary key,
name varchar(22) unique,
location varchar(50) not null
);
mysql> create table tb_dept1
-> (
-> deptid int(11) primary key,
-> name varchar(22) unique,
-> location varchar(50) not null
-> );
Query OK, 0 rows affected (0.05 sec)
mysql> show create table tb_dept1 \G;
*************************** 1. row ***************************
Table: tb_dept1
Create Table: CREATE TABLE `tb_dept1` (
`deptid` int(11) NOT NULL,
`name` varchar(22) DEFAULT NULL,
`location` varchar(50) NOT NULL,
PRIMARY KEY (`deptid`),
UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
ERROR:
No query specified
6.使用默认值
语法字段名数据库类型 default
mysql> create table tb_emp4
-> (
-> id int(11) primary key,
-> name varchar(25),
-> location varchar(50),
-> deptid int(11) default 1111,
-> salary float
-> );
Query OK, 0 rows affected (0.05 sec)
mysql> show create table tb_emp4 \G;
*************************** 1. row ***************************
Table: tb_emp4
Create Table: CREATE TABLE `tb_emp4` (
`id` int(11) NOT NULL,
`name` varchar(25) DEFAULT NULL,
`location` varchar(50) DEFAULT 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)
ERROR:
No query specified
mysql>
7.设置表的属性字段增加（auto_increment）
mysql> create table tb_emp5
-> (
-> id int(11) primary key auto_increment,
-> name varchar(25),
-> location varchar(50),
-> deptid int(11) default 1111,
-> salary float
-> );
Query OK, 0 rows affected (0.13 sec)
mysql> show tables ;
+-------------------+
| Tables_in_test_db |
+-------------------+
| tb_dept |
| tb_dept1 |
| tb_emp1 |
| tb_emp2 |
| tb_emp3 |
| tb_emp4 |
| tb_emp5 |
+-------------------+
7 rows in set (0.00 sec)
mysql> desc tb_emp5;
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(25) | YES | | NULL | |
| location | varchar(50) | YES | | NULL | |
| deptid | int(11) | YES | | 1111 | |
| salary | float | YES | | NULL | |
+----------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
mysql> show create table tb_emp5 \G;
*************************** 1. row ***************************
Table: tb_emp5
Create Table: CREATE TABLE `tb_emp5` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(25) DEFAULT NULL,
`location` varchar(50) DEFAULT 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)
ERROR:
No query specified
mysql>
插入如下语句验证；
mysql> insert into tb_emp5 (name,location,salary) values('ww','dd','1000'),('ja
ck','ds','3000'),('rrw','dd','1500');
Query OK, 3 rows affected (0.03 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from tb_emp5;
+----+------+----------+--------+--------+
| id | name | location | deptid | salary |
+----+------+----------+--------+--------+
| 1 | ww | dd | 1111 | 1000 |
| 2 | jack | ds | 1111 | 3000 |
| 3 | rrw | dd | 1111 | 1500 |
+----+------+----------+--------+--------+
3 rows in set (0.00 sec)
mysql>
(责任编辑：IT)
从今天开始学习mysql数据库，希望以后能够记录自己学习的点点滴滴 mysql中创建数据表 1.创建一个数据库为test_db mysql> create database test_db; Query OK, 1 row affected (0.00 sec) 切换到test_db上 mysql> use test_db; Database changed mysql> 2.创建一个表tb_emp1 create table tb_emp1 ( id INT(11), name VARCHAR(25), deptid INT (11), salary FLOAT ); mysql> create table tb_emp1 -> ( -> id INT(11), -> name VARCHAR(25), -> deptid INT (11), -> salary FLOAT -> ); Query OK, 0 rows affected (0.09 sec)
mysql> show tables; +-------------------+ | Tables_in_test_db | +-------------------+ | tb_emp1 | +-------------------+ 1 row in set (0.01 sec)
mysql> show create table tb_emp1 \G; *************************** 1. row *************************** Table: tb_emp1 Create Table: CREATE TABLE `tb_emp1` ( `id` int(11) DEFAULT NULL, `name` varchar(25) DEFAULT NULL, `deptid` int(11) DEFAULT NULL, `salary` float DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec)
ERROR: No query specified mysql> 3.使用主键约束 语法是字段名数据类型 primary key [默认值] create table tb_emp2 ( id INT(11) PRIMARY KEY, name VARCHAR(25), deptid INT (11), salary FLOAT ); 或者如下 create table tb_emp2 ( id INT(11) , name VARCHAR(25), deptid INT (11), salary FLOAT PRIMARY KEY (id) );
mysql> create table tb_emp2 -> ( -> id INT(11) PRIMARY KEY, -> name VARCHAR(25), -> deptid INT (11), -> salary FLOAT -> ); Query OK, 0 rows affected (0.03 sec)
mysql> show tables; +-------------------+ | Tables_in_test_db | +-------------------+ | tb_emp1 | | tb_emp2 | +-------------------+ 2 rows in set (0.00 sec)
mysql> show create table tb_emp2 \G; *************************** 1. row *************************** Table: tb_emp2 Create Table: CREATE TABLE `tb_emp2` ( `id` int(11) NOT NULL, `name` varchar(25) DEFAULT NULL, `deptid` int(11) DEFAULT NULL, `salary` float DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec) ERROR: No query specified mysql>
4.表中使用外键 语法[constraint <外键名>] foreign key 字段名1 [,字段名2,…] references <主表名> 主键列1[,主键列2,…] 主表： create table tb_dept ( deptid int(11) primary key, name varchar(22) not null, location varchar(50) ) 从表： create table tb_emp3 ( id int(11) primary key, name varchar(25), location varchar(50), deptid int(11), salary float, constraint fk_emp_dept foreign key (deptid) references tb_dept (deptid) );
mysql> create table tb_dept -> ( -> deptid int(11) primary key, -> name varchar(22) not null, -> location varchar(50) -> ) -> ; Query OK, 0 rows affected (0.05 sec) mysql> create table tb_emp3 -> ( -> id int(11) primary key, -> name varchar(25), -> location varchar(50), -> deptid int(11), -> salary float, -> constraint fk_emp_dept foreign key (deptid) references tb_dept (deptid) -> ); Query OK, 0 rows affected (0.06 sec)
mysql>
mysql> show tables; +-------------------+ | Tables_in_test_db | +-------------------+ | tb_dept | | tb_emp1 | | tb_emp2 | | tb_emp3 | +-------------------+ 4 rows in set (0.00 sec)
mysql> show create table tb_emp3 \G; *************************** 1. row *************************** Table: tb_emp3 Create Table: CREATE TABLE `tb_emp3` ( `id` int(11) NOT NULL, `name` varchar(25) DEFAULT NULL, `location` varchar(50) DEFAULT NULL, `deptid` int(11) DEFAULT NULL, `salary` float DEFAULT NULL, PRIMARY KEY (`id`), KEY `fk_emp_dept` (`deptid`), CONSTRAINT `fk_emp_dept` FOREIGN KEY (`deptid`) REFERENCES `tb_dept` (`deptid` ) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec)
ERROR: No query specified mysql>
5.表空使用非空约束(not null )和唯一约束(unique) 语法字段名数据类型 not null , 字段名数据类型 unique create table tb_dept1 ( deptid int(11) primary key, name varchar(22) unique, location varchar(50) not null );
mysql> create table tb_dept1 -> ( -> deptid int(11) primary key, -> name varchar(22) unique, -> location varchar(50) not null -> ); Query OK, 0 rows affected (0.05 sec)
mysql> show create table tb_dept1 \G; *************************** 1. row *************************** Table: tb_dept1 Create Table: CREATE TABLE `tb_dept1` ( `deptid` int(11) NOT NULL, `name` varchar(22) DEFAULT NULL, `location` varchar(50) NOT NULL, PRIMARY KEY (`deptid`), UNIQUE KEY `name` (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec)
ERROR: No query specified
6.使用默认值 语法字段名数据库类型 default
mysql> create table tb_emp4 -> ( -> id int(11) primary key, -> name varchar(25), -> location varchar(50), -> deptid int(11) default 1111, -> salary float -> ); Query OK, 0 rows affected (0.05 sec) mysql> show create table tb_emp4 \G; *************************** 1. row *************************** Table: tb_emp4 Create Table: CREATE TABLE `tb_emp4` ( `id` int(11) NOT NULL, `name` varchar(25) DEFAULT NULL, `location` varchar(50) DEFAULT 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)
ERROR: No query specified
mysql>
7.设置表的属性字段增加（auto_increment） mysql> create table tb_emp5 -> ( -> id int(11) primary key auto_increment, -> name varchar(25), -> location varchar(50), -> deptid int(11) default 1111, -> salary float -> ); Query OK, 0 rows affected (0.13 sec)
mysql> show tables ; +-------------------+ | Tables_in_test_db | +-------------------+ | tb_dept | | tb_dept1 | | tb_emp1 | | tb_emp2 | | tb_emp3 | | tb_emp4 | | tb_emp5 | +-------------------+ 7 rows in set (0.00 sec) mysql> desc tb_emp5; +----------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(25) | YES | | NULL | | | location | varchar(50) | YES | | NULL | | | deptid | int(11) | YES | | 1111 | | | salary | float | YES | | NULL | | +----------+-------------+------+-----+---------+----------------+ 5 rows in set (0.00 sec)
mysql> show create table tb_emp5 \G; *************************** 1. row *************************** Table: tb_emp5 Create Table: CREATE TABLE `tb_emp5` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(25) DEFAULT NULL, `location` varchar(50) DEFAULT 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)
ERROR: No query specified
mysql> 插入如下语句验证；
mysql> insert into tb_emp5 (name,location,salary) values('ww','dd','1000'),('ja ck','ds','3000'),('rrw','dd','1500'); Query OK, 3 rows affected (0.03 sec) Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from tb_emp5; +----+------+----------+--------+--------+ | id | name | location | deptid | salary | +----+------+----------+--------+--------+ | 1 | ww | dd | 1111 | 1000 | | 2 | jack | ds | 1111 | 3000 | | 3 | rrw | dd | 1111 | 1500 | +----+------+----------+--------+--------+ 3 rows in set (0.00 sec)
mysql> (责任编辑：IT) |