首先建库
mysql>create database homework;
Query OK, 1 row affected (0.00 sec)
使用库
use homework;
建表
mysql>create table employee(
-> id int null unique auto_increment,
-> name varchar(20) not null,
-> sex enum('male','female') not null default 'male',
-> age int(3) not null default 28,
-> hire_date date not null,
-> post varchar(50),
-> post_comment varchar(100),
-> salary double(15,2),
-> office int);
Query OK, 0 rows affected (0.03 sec)
查看建好的表格
mysql> desc employee;
+--------------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-----------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
| sex | enum('male','female') | NO | | male | |
| age | int(3) | 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,在表格中添加字段
mysql> alter table employee add depart_id int(11);
Query OK, 0 rows affected (0.06 sec)
在表格中添加数据
mysql> insert into employee(name,sex,age,hire_date,post,salary,office,depart_id) values
-> ('egon','male',18,'20170301','老男孩驻沙河办事处外交大使',7300.33,401,1),
-> ('alex','male',78,'20150302','teacher',1000000.31,401,1),
-> ('wupeiqi','male',81,'20130305','teacher',8300,401,1),
-> ('yuanhao','male',73,'20140701','teacher',3500,401,1),
-> ('liwenzhou','male',28,'20121101','teacher',2100,401,1),
-> ('jingliyang','female',18,'20110211','teacher',9000,401,1),
-> ('jinxin','male',18,'19000301','teacher',30000,401,1),
-> ('成龙','male',48,'20101111','teacher',10000,401,1),
->
-> ('歪歪','female',48,'20150311','sale',3000.13,402,2),
-> ('丫丫','female',38,'20101101','sale',2000.35,402,2),
-> ('丁丁','female',18,'20110312','sale',1000.37,402,2),
-> ('星星','female',18,'20160513','sale',3000.29,402,2),
-> ('格格','female',28,'20170127','sale',4000.33,402,2),
->
-> ('张野','male',28,'20160311','operation',10000.13,403,3),
-> ('程咬金','male',18,'19970312','operation',20000,403,3),
-> ('程咬银','female',18,'20130311','operation',19000,403,3),
-> ('程咬铜','male',18,'20150411','operation',18000,403,3),
-> ('程咬铁','female',18,'20140512','operation',17000,403,3)
-> ;
查看表格
mysql> select * from employee;
+----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+
| 1 | egon | male | 18 | 2017-03-01 | 老男孩驻沙河办事处外交大使 | NULL | 7300.33 | 401 | 1 |
| 2 | alex | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 |
| 3 | wupeiqi | male | 81 | 2013-03-05 | teacher | NULL | 8300.00 | 401 | 1 |
| 4 | yuanhao | male | 73 | 2014-07-01 | teacher | NULL | 3500.00 | 401 | 1 |
| 5 | liwenzhou | male | 28 | 2012-11-01 | teacher | NULL | 2100.00 | 401 | 1 |
| 6 | jingliyang | female | 18 | 2011-02-11 | teacher | NULL | 9000.00 | 401 | 1 |
| 7 | jinxin | male | 18 | 1900-03-01 | teacher | NULL | 30000.00 | 401 | 1 |
| 8 | 成龙 | male | 48 | 2010-11-11 | teacher | NULL | 10000.00 | 401 | 1 |
| 9 | 歪歪 | female | 48 | 2015-03-11 | sale | NULL | 3000.13 | 402 | 2 |
| 10 | 丫丫 | female | 38 | 2010-11-01 | sale | NULL | 2000.35 | 402 | 2 |
| 11 | 丁丁 | female | 18 | 2011-03-12 | sale | NULL | 1000.37 | 402 | 2 |
| 12 | 星星 | female | 18 | 2016-05-13 | sale | NULL | 3000.29 | 402 | 2 |
| 13 | 格格 | female | 28 | 2017-01-27 | sale | NULL | 4000.33 | 402 | 2 |
| 14 | 张野 | male | 28 | 2016-03-11 | operation | NULL | 10000.13 | 403 | 3 |
| 15 | 程咬金 | male | 18 | 1997-03-12 | operation | NULL | 20000.00 | 403 | 3 |
| 16 | 程咬银 | female | 18 | 2013-03-11 | operation | NULL | 19000.00 | 403 | 3 |
| 17 | 程咬铜 | male | 18 | 2015-04-11 | operation | NULL | 18000.00 | 403 | 3 |
| 18 | 程咬铁 | female | 18 | 2014-05-12 | operation | NULL | 17000.00 | 403 | 3 |
+----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+
18 rows in set (0.00 sec)
- 查看岗位是teacher的员工姓名、年龄
mysql> select name ,age from employee where post = 'teacher';
+------------+-----+
| name | age |
+------------+-----+
| alex | 78 |
| wupeiqi | 81 |
| yuanhao | 73 |
| liwenzhou | 28 |
| jingliyang | 18 |
| jinxin | 18 |
| 成龙 | 48 |
+------------+-----+
7 rows in set (0.00 sec)
- 查看岗位是teacher且年龄大于30岁的员工姓名、年龄
mysql> select name ,age from employee where post = 'teacher' and age >30;
+---------+-----+
| name | age |
+---------+-----+
| alex | 78 |
| wupeiqi | 81 |
| yuanhao | 73 |
| 成龙 | 48 |
+---------+-----+
4 rows in set (0.00 sec)
-
查看岗位是teacher且薪资在9000-1000范围内的员工姓名、年龄、薪资
这里要注意的是在MySQL中没有 1000<xxx<9000的形式，所以要使用两次and
mysql> select name,age,salary from employee where 1000<salary and salary<9000 and post = 'teacher';
+-----------+-----+---------+
| name | age | salary |
+-----------+-----+---------+
| wupeiqi | 81 | 8300.00 |
| yuanhao | 73 | 3500.00 |
| liwenzhou | 28 | 2100.00 |
+-----------+-----+---------+
3 rows in set (0.00 sec)
-
查看岗位描述不为NULL的员工信息
在查找null的时候要使用is 而不是 =号
mysql> select * from employee where post is not null;
+----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+
| 1 | egon | male | 18 | 2017-03-01 | 老男孩驻沙河办事处外交大使 | NULL | 7300.33 | 401 | 1 |
| 2 | alex | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 |
| 3 | wupeiqi | male | 81 | 2013-03-05 | teacher | NULL | 8300.00 | 401 | 1 |
| 4 | yuanhao | male | 73 | 2014-07-01 | teacher | NULL | 3500.00 | 401 | 1 |
| 5 | liwenzhou | male | 28 | 2012-11-01 | teacher | NULL | 2100.00 | 401 | 1 |
| 6 | jingliyang | female | 18 | 2011-02-11 | teacher | NULL | 9000.00 | 401 | 1 |
| 7 | jinxin | male | 18 | 1900-03-01 | teacher | NULL | 30000.00 | 401 | 1 |
| 8 | 成龙 | male | 48 | 2010-11-11 | teacher | NULL | 10000.00 | 401 | 1 |
| 9 | 歪歪 | female | 48 | 2015-03-11 | sale | NULL | 3000.13 | 402 | 2 |
| 10 | 丫丫 | female | 38 | 2010-11-01 | sale | NULL | 2000.35 | 402 | 2 |
| 11 | 丁丁 | female | 18 | 2011-03-12 | sale | NULL | 1000.37 | 402 | 2 |
| 12 | 星星 | female | 18 | 2016-05-13 | sale | NULL | 3000.29 | 402 | 2 |
| 13 | 格格 | female | 28 | 2017-01-27 | sale | NULL | 4000.33 | 402 | 2 |
| 14 | 张野 | male | 28 | 2016-03-11 | operation | NULL | 10000.13 | 403 | 3 |
| 15 | 程咬金 | male | 18 | 1997-03-12 | operation | NULL | 20000.00 | 403 | 3 |
| 16 | 程咬银 | female | 18 | 2013-03-11 | operation | NULL | 19000.00 | 403 | 3 |
| 17 | 程咬铜 | male | 18 | 2015-04-11 | operation | NULL | 18000.00 | 403 | 3 |
| 18 | 程咬铁 | female | 18 | 2014-05-12 | operation | NULL | 17000.00 | 403 | 3 |
+----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+
18 rows in set (0.00 sec)
- 查看岗位是teacher且薪资是10000或9000或30000的员工姓名、年龄、薪资
mysql> select name ,age,salary from employee where salary in (10000,9000,30000);
+------------+-----+----------+
| name | age | salary |
+------------+-----+----------+
| jingliyang | 18 | 9000.00 |
| jinxin | 18 | 30000.00 |
| 成龙 | 48 | 10000.00 |
+------------+-----+----------+
3 rows in set (0.00 sec)
- 查看岗位是teacher且薪资不是10000或9000或30000的员工姓名、年龄、薪资
mysql> select name ,age,salary from employee where salary not in (10000,9000,30000);
+-----------+-----+------------+
| name | age | salary |
+-----------+-----+------------+
| egon | 18 | 7300.33 |
| alex | 78 | 1000000.31 |
| wupeiqi | 81 | 8300.00 |
| yuanhao | 73 | 3500.00 |
| liwenzhou | 28 | 2100.00 |
| 歪歪 | 48 | 3000.13 |
| 丫丫 | 38 | 2000.35 |
| 丁丁 | 18 | 1000.37 |
| 星星 | 18 | 3000.29 |
| 格格 | 28 | 4000.33 |
| 张野 | 28 | 10000.13 |
| 程咬金 | 18 | 20000.00 |
| 程咬银 | 18 | 19000.00 |
| 程咬铜 | 18 | 18000.00 |
| 程咬铁 | 18 | 17000.00 |
+-----------+-----+------------+
15 rows in set (0.00 sec)
-
查看岗位是teacher且名字是jin开头的员工姓名、年薪
模糊查询要使用like，然后使用% 占位符
mysql> select name,salary*12 as annnual_year from employee where name like 'jin%' and post = 'teacher';
+------------+--------------+
| name | annnual_year |
+------------+--------------+
| jingliyang | 108000.00 |
| jinxin | 360000.00 |
+------------+--------------+
2 rows in set (0.00 sec)