作业
#1. 查看岗位是teacher的员工姓名、年龄
mysql> select database();
+------------+
| database() |
+------------+
| test2 |
+------------+
1 row in set (0.00 sec)
mysql> show tables;
+-----------------+
| Tables_in_test2 |
+-----------------+
| teacherinfo |
+-----------------+
1 row in set (0.00 sec)
mysql> desc teacherinfo;
+--------------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+---------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | char(10) | YES | | NULL | |
| age | tinyint(4) | YES | | NULL | |
| describetion | varchar(32) | NO | | | |
| salary | decimal(18,8) | YES | | NULL | |
| year_salary | decimal(18,8) | YES | | NULL | |
+--------------+---------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)
mysql> select name,age from teacherinfo ;
+------------+------+
| name | age |
+------------+------+
| kaifeng | 18 |
| liukaifeng | 18 |
| feng | 18 |
| kai | 20 |
| kaizi | 31 |
| laokai | 35 |
| laokai | 35 |
| laokai | 35 |
| jinsa | 19 |
+------------+------+
9 rows in set (0.00 sec)
# 2. 查看岗位是teacher且年龄大于30岁的员工姓名、年龄
mysql> select name,age from teacherinfo where age > 30 ;
+--------+------+
| name | age |
+--------+------+
| kaizi | 31 |
| laokai | 35 |
| laokai | 35 |
| laokai | 35 |
+--------+------+
4 rows in set (0.00 sec)
#3. 查看岗位是teacher且薪资在9000-1000范围内的员工姓名、年龄、薪资
mysql> select name,age,salary from teacherinfo where salary between 9000 and 10000 ;
Empty set (0.00 sec)
# 4. 查看岗位描述不为NULL的员工信息
mysql> select * from teacherinfo where describetion is null;
Empty set (0.00 sec)
# 5.查看岗位是teacher且薪资是10000或9000或30000的员工姓名、年龄、薪资
mysql> select * from teacherinfo where salary=10000 or salary=9000 or salary=30000;
Empty set (0.00 sec)
# 6.查看岗位是teacher且薪资不是10000或9000或30000的员工姓名、年龄、薪资
mysql> select * from teacherinfo where not (salary=10000 or salary=9000 or salary=30000);
+----+------------+------+--------------+-------------------+------------------+
| id | name | age | describetion | salary | year_salary |
+----+------------+------+--------------+-------------------+------------------+
| 1 | kaifeng | 18 | | 1000000.00000000 | 9999999.00000000 |
| 2 | liukaifeng | 18 | | 1000000.00000000 | 9999999.00000000 |
| 3 | feng | 18 | | 1000000.00000000 | 9999999.00000000 |
| 4 | kai | 20 | | 1000000.00000000 | 9999999.00000000 |
| 5 | kaizi | 31 | | 1000000.00000000 | 9999999.00000000 |
| 6 | laokai | 35 | | 1000000.00000000 | 9999999.00000000 |
| 7 | laokai | 35 | | 100.00000000 | 9999999.00000000 |
| 8 | laokai | 35 | | 100.00000000 | 999999.00000000 |
| 9 | jinsa | 19 | | 99999999.00000000 | 8888888.00000000 |
+----+------------+------+--------------+-------------------+------------------+
9 rows in set (0.00 sec)
# 7. 查看岗位是teacher且名字是jin开头的员工姓名、年薪
mysql> select name,year_salary from teacherinfo where name like 'jin%';
+-------+------------------+
| name | year_salary |
+-------+------------------+
| jinsa | 8888888.00000000 |
+-------+------------------+
1 row in set (0.00 sec)