一、给定一张表1,根据要求进行排序:
+--------+----------+---------+----------+
| emp_id | emp_name | dept_id | emp_wage |
+--------+----------+---------+----------+
| 1001 | toni | 1 | 32000 |
| 1002 | lMcy | 3 | 2500 |
| 1003 | toM | 4 | 3200 |
| 1004 | mamy | 2 | 9500 |
+--------+----------+---------+----------+
1.根据emp_wage,员工工资升序排序:mysql> select * from employee order by emp_wage ASC; (注:ASC=ascending升序)
+--------+----------+---------+----------+
| emp_id | emp_name | dept_id | emp_wage |
+--------+----------+---------+----------+
| 1002 | lMcy | 3 | 2500 |
| 1003 | toM | 4 | 3200 |
| 1004 | mamy | 2 | 9500 |
| 1001 | toni | 1 | 32000 |
+--------+----------+---------+----------+
2.根据emp_id降序排序:mysql> select * from employee order by emp_id DESC; (注:DESC=descending降序)
+--------+----------+---------+----------+
| emp_id | emp_name | dept_id | emp_wage |
+--------+----------+---------+----------+
| 1004 | mamy | 2 | 9500 |
| 1003 | toM | 4 | 3200 |
| 1002 | lMcy | 3 | 2500 |
| 1001 | toni | 1 | 32000 |
+--------+----------+---------+----------+
二、根据以上表1,根据要求进行模糊查询
1、不区分大小写查询员工名中含有m的员工资料:mysql> select * from employee where emp_name like '%m%';
+--------+----------+---------+----------+
| emp_id | emp_name | dept_id | emp_wage |
+--------+----------+---------+----------+
| 1002 | lMcy | 3 | 2500 |
| 1003 | toM | 4 | 3200 |
| 1004 | mamy | 2 | 9500 |
+--------+----------+---------+----------+
2、查询员工名中含有大写M的员工资料:mysql> select * from employee where emp_name like binary('%M%');
+--------+----------+---------+----------+
| emp_id | emp_name | dept_id | emp_wage |
+--------+----------+---------+----------+
| 1002 | lMcy | 3 | 2500 |
| 1003 | toM | 4 | 3200 |
+--------+----------+---------+----------+
3、查询员工名中以“t”打头以“i”结尾的员工资料:mysql> select * from employee where emp_name like 't%' and emp_name like '%i';
+--------+----------+---------+----------+
| emp_id | emp_name | dept_id | emp_wage |
+--------+----------+---------+----------+
| 1001 | toni | 1 | 32000 |
+--------+----------+---------+----------+
4、查询员工名中第三位是大写“M”的员工资料:mysql> select * from employee where emp_name like binary('%_ _M%');
+--------+----------+---------+----------+
| emp_id | emp_name | dept_id | emp_wage |
+--------+----------+---------+----------+
| 1003 | toM | 4 | 3200 |
+--------+----------+---------+----------+