mysql查一整张表

员工id id int

姓名 emp_name varchar

性别 sex enum

年龄 age int

入职日期 hire_date date

岗位 post varchar

职位描述 post_comment varchar

薪水 salary double

办公室 office int

部门编号 depart_id int

#创建表

create table employee(

id intnotnull unique auto_increment,

emp_name varchar(20) notnull,

sex enum('male','female') not null default 'male',

age int(3) unsigned not null default 28,

hire_date datenotnull,

post varchar(50),

post_comment varchar(100),

salary double(15,2),

office int,

depart_id int

);#查看表结构

mysql>desc employee;+--------------+-----------------------+------+-----+---------+----------------+

| Field | Type | Null | Key | Default | Extra |

+--------------+-----------------------+------+-----+---------+----------------+

| id | int(11) | NO | PRI | NULL | auto_increment |

| emp_name | varchar(20) | NO | | NULL | |

| sex | enum('male','female') | NO | | male | |

| age | int(3) unsigned | NO | | 28 | |

| hire_date | date | NO | | NULL | |

| post | varchar(50) | YES | | NULL | |

| post_comment | varchar(100) | YES | | NULL | |

| salary | double(15,2) | YES | | NULL | |

| office | int(11) | YES | | NULL | |

| depart_id | int(11) | YES | | NULL | |

+--------------+-----------------------+------+-----+---------+----------------+

10 rows in set (0.09sec)#插入记录#三个部门：教学，销售，运营

insert into employee(emp_name,sex,age,hire_date,post,salary,office,depart_id) values

('彭哈哈','male',18,'20170301','教学',7300.33,401,1),

('赵晓明','male',78,'20150302','教学',1000000.31,401,1),

('爱祖国','male',81,'20130305','教学',8300,401,1),

('延阻聚','male',73,'20140701','教学',3500,401,1),

('陈继承','male',28,'20121101','教学',2100,401,1),

('李小荷','female',18,'20110211','教学',9000,401,1),

('赵晓明','male',18,'19000301','教学',30000,401,1),

('高富帅','male',48,'20101111','教学',10000,401,1),

('菟丝子','female',48,'20150311','销售',3000.13,402,2),

('张晓敏','female',38,'20101101','销售',2000.35,402,2),

('冯小刚','female',18,'20110312','销售',1000.37,402,2),

('老小月','female',18,'20160513','销售',3000.29,402,2),

('格格','female',28,'20170127','销售',4000.33,402,2),

('彭消息','male',28,'20160311','运营',10000.13,403,3),

('张国','male',18,'19970312','运营',20000,403,3),

('小路小','female',18,'20130311','运营',19000,403,3),

('罗超','male',18,'20150411','运营',18000,403,3),

('张罗好','female',18,'20140512','运营',17000,403,3);

建表

只查看某张表指定的字段：

select 字段1,字段2,字段3,字段n from 表名；

mysql> select id, emp_name, sex, age from employee; #查看员工姓名性别年龄

+----+-----------+--------+-----+

| id | emp_name | sex | age |

+----+-----------+--------+-----+

| 19 | 彭哈哈 | male | 18 |

| 20 | 彭哈哈 | male | 18 |

| 21 | 赵晓明 | male | 78 |

| 22 | 爱祖国 | male | 81 |

| 23 | 延阻聚 | male | 73 |

| 24 | 陈继承 | male | 28 |

| 25 | 李小荷 | female | 18 |

| 26 | 赵晓明 | male | 18 |

| 27 | 高富帅 | male | 48 |

| 28 | 菟丝子 | female | 48 |

| 29 | 张晓敏 | female | 38 |

| 30 | 冯小刚 | female | 18 |

| 31 | 老小月 | female | 18 |

| 32 | 格格 | female | 28 |

| 33 | 彭消息 | male | 28 |

| 34 | 张国 | male | 18 |

| 35 | 小路小 | female | 18 |

| 36 | 罗超 | male | 18 |

| 37 | 张罗好 | female | 18 |

+----+-----------+--------+-----+

19 rows in set (0.00 sec)

只查看指定的字段

查看整张表：

select * from 表名;

mysql> select * from employee; #查看整张表

+----+-----------+--------+-----+------------+--------+--------------+------------+--------+-----------+

| id | emp_name | sex | age | hire_date | post | post_comment | salary | office | depart_id |