数据库基本信息查询

作业：
1. 查看岗位是teacher的员工姓名、年龄
2. 查看岗位是teacher且年龄大于30岁的员工姓名、年龄、薪资
3. 查看岗位是teacher且薪资在9000-1000范围内的员工姓名、年龄、薪资
4. 查看岗位描述不为NULL的员工信息
5. 查看岗位是teacher且薪资是10000或9000或30000的员工姓名、年龄、薪资
6. 查看岗位是teacher且薪资不是10000或9000或30000的员工姓名、年龄、薪资
7. 查看岗位是teacher且名字是jin开头的员工姓名、年薪

新建数据库

mysql> create database test2;
Query OK, 1 row affected (0.00 sec)

新建表

mysql> create table teacher(
-> id int auto_increment primary key,
-> name varchar(10),
-> age int,
-> wage float,
-> position enum('teacher','student')
-> )charset=utf8;
Query OK, 0 rows affected (0.10 sec)

mysql> desc teacher;
+----------+---------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(10) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| wage | float | YES | | NULL | |
| position | enum('teacher','student') | YES | | NULL | |
+----------+---------------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

新建数据行

mysql> insert into teacher(id,name,age,wage,position) values
-> (1,'wang',20,9500.234,'teacher'),
-> (2,'zhang',25,9000,'student'),
-> (3,'jin',30, 8000,'teacher');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0

查看新建数据行的信息

mysql> select * from teacher;
+----+-------+------+---------+----------+
| id | name | age | wage | position |
+----+-------+------+---------+----------+
| 1 | wang | 20 | 9500.23 | teacher |
| 2 | zhang | 25 | 9000 | student |
| 3 | jin | 30 | 8000 | teacher |
+----+-------+------+---------+----------+
3 rows in set (0.00 sec)

查看岗位是teacher的员工姓名、年龄

mysql> select name,age from teacher where position = 'teacher';
+------+------+
| name | age |
+------+------+
| wang | 20 |
| jin | 30 |
+------+------+
2 rows in set (0.00 sec)

查看岗位是teacher且年龄大于等于30岁的员工姓名、年龄，工资

mysql> select name,age,wage from teacher where age >= 30 and position = 'teacher';
+------+------+------+
| name | age | wage |
+------+------+------+
| jin | 30 | 8000 |
+------+------+------+
1 row in set (0.00 sec)

查看岗位是teacher且薪资在9000-1000范围内的员工姓名、年龄、薪资

mysql> select name,age,wage from teacher where wage between 9000 and 10000 and position = 'teacher';
+------+------+---------+
| name | age | wage |
+------+------+---------+
| wang | 20 | 9500.23 |
+------+------+---------+
1 row in set (0.00 sec)

查看岗位描述不为NULL的员工信息

mysql> select name,age,wage from teacher where position != null;
Empty set (0.00 sec)

查看岗位是teacher且薪资是10000或9000或30000的员工姓名、年龄、薪资

mysql> select name,age,wage from teacher where wage in (10000,9000,30000) and position = 'teacher';
Empty set (0.00 sec)

查看岗位是teacher且薪资不是10000或9000或30000的员工姓名、年龄、薪资

mysql> select name,age,wage from teacher where wage not in (10000,9000,30000) and position = 'teacher';
+------+------+---------+
| name | age | wage |
+------+------+---------+
| wang | 20 | 9500.23 |
| jin | 30 | 8000 |
+------+------+---------+
2 rows in set (0.00 sec)

查看岗位是teacher且名字是jin开头的员工姓名、年薪

mysql> select name,wage from teacher where name like 'jin%' and position = 'teacher';
+------+------+
| name | wage |
+------+------+
| jin | 8000 |
+------+------+
1 row in set (0.00 sec)