创建表
# 语法:
create table 表名 (
字段名 1 类型 [(宽度) 约束条件],
字段名 2 类型 [(宽度) 约束条件],
字段名 3 类型 [(宽度) 约束条件]
);
# 注意:
1. 在同一张表中, 字段名是不能相同
2. 宽度和约束条件可选
3. 字段名和类型是必须的
- MySQL> create database staff;
- Query OK, 1 row affected (0.00 sec)
- MySQL> use staff;
- Database changed
- MySQL> create table staff_info (id int,name varchar(50),age int(3),sex enum('male','female'),phone bigint(11),job varchar(11));
- Query OK, 0 rows affected (0.02 sec)
- MySQL> show tables;
- +-----------------+
- | Tables_in_staff |
- +-----------------+
- | staff_info |
- +-----------------+
- 1 row in set (0.00 sec)
- MySQL> desc staff_info;
- +-------+-----------------------+------+-----+---------+-------+
- | Field | Type | Null | Key | Default | Extra |
- +-------+-----------------------+------+-----+---------+-------+
- | id | int(11) | YES | | NULL | |
- | name | varchar(50) | YES | | NULL | |
- | age | int(3) | YES | | NULL | |
- | sex | enum('male','female') | YES | | NULL | |
- | phone | bigint(11) | YES | | NULL | |
- | job | varchar(11) | YES | | NULL | |
- +-------+-----------------------+------+-----+---------+-------+
- 6 rows in set (0.00 sec)
- MySQL> select id,name,sex from staff_info;
- Empty set (0.00 sec)
- MySQL> select * from staff_info;
- Empty set (0.00 sec)
插入数据
- MySQL> insert into staff_info (id,name,age,sex,phone,job) values (1,'Alex',83,'female',13651054608,'IT');
- Query OK, 1 row affected (0.00 sec)
- MySQL> insert into staff_info values (2,'Egon',26,'male',13304320533,'Teacher');
- Query OK, 1 row affected (0.00 sec)
- MySQL> insert into staff_info values (3,'nezha',25,'male',13332353222,'IT'),(4,'boss_jin',40,'male',13332353333,'IT');
- Query OK, 2 rows affected (0.00 sec)
- Records: 2 Duplicates: 0 Warnings: 0
- MySQL> select * from staff_info;
- +------+----------+------+--------+-------------+---------+
- | id | name | age | sex | phone | job |
- +------+----------+------+--------+-------------+---------+
- | 1 | Alex | 83 | female | 13651054608 | IT |
- | 2 | Egon | 26 | male | 13304320533 | Teacher |
- | 3 | nezha | 25 | male | 13332353222 | IT |
- | 4 | boss_jin | 40 | male | 13332353333 | IT |
- +------+----------+------+--------+-------------+---------+
- 4 rows in set (0.00 sec)
\c 代表此行作废
查案表结构
查看表结构有两种方式:
describe [tablename]; 这种方法和 desc [tablename]; 效果相同; 可以查看当前的表结构
虽然 desc 命令可以查看表的定义, 但是其输出的信息还不够全面, 为了得到更全面的表定义信息, 有时候就需要查看创建表的 SQL 语句, 使用 show create table 语法. 除了可以看到表定义之外, 还可以看到 engine(存储引擎) 和 charset(字符集) 等信息.(\G 选项的含义是是的记录能够竖向排列, 以便更好的显示内容较长的记录.)
- MySQL> describe staff_info;
- +-------+-----------------------+------+-----+---------+-------+
- | Field | Type | Null | Key | Default | Extra |
- +-------+-----------------------+------+-----+---------+-------+
- | id | int(11) | YES | | NULL | |
- | name | varchar(50) | YES | | NULL | |
- | age | int(3) | YES | | NULL | |
- | sex | enum('male','female') | YES | | NULL | |
- | phone | bigint(11) | YES | | NULL | |
- | job | varchar(11) | YES | | NULL | |
- +-------+-----------------------+------+-----+---------+-------+
- 6 rows in set (0.00 sec)
- MySQL> desc staff_info;
- +-------+-----------------------+------+-----+---------+-------+
- | Field | Type | Null | Key | Default | Extra |
- +-------+-----------------------+------+-----+---------+-------+
- | id | int(11) | YES | | NULL | |
- | name | varchar(50) | YES | | NULL | |
- | age | int(3) | YES | | NULL | |
- | sex | enum('male','female') | YES | | NULL | |
- | phone | bigint(11) | YES | | NULL | |
- | job | varchar(11) | YES | | NULL | |
- +-------+-----------------------+------+-----+---------+-------+
- 6 rows in set (0.00 sec)
- MySQL> show create table staff_info\G;
- *************************** 1. row ***************************
- Table: staff_info
- Create Table: CREATE TABLE `staff_info` (
- `id` int(11) DEFAULT NULL,
- `name` varchar(50) DEFAULT NULL,
- `age` int(3) DEFAULT NULL,
- `sex` enum('male','female') DEFAULT NULL,
- `phone` bigint(11) DEFAULT NULL,
- `job` varchar(11) DEFAULT NULL
- ) ENGINE=InnoDB DEFAULT CHARSET=utf8
- 1 row in set (0.01 sec)
- ERROR:
- No query specified
来源: http://www.bubuko.com/infodetail-2799681.html