mysql基本语法(2)

数据准备

mysql> create table it_student(
    -> id int unsigned not null auto_increment primary key,
    -> name varchar(30),
    -> age tinyint unsigned,
    -> sex enum('man','woman'),
    -> salary decimal(11,2),
    -> subject varchar(30)
    -> ) engine=innodb default charset=utf8;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into it_student values(null,'xianqian',20,'woman',21000.00,'tester');
insert into it_student values(null,'xiaoming',24,'man',16000.20,'java');
insert into it_student values(null,'laowang',29,'man',15000.00,'java');
insert into it_student values(null,'laowen',26,'man',10000.00,'yunwei');
insert into it_student values(null,'laotian',26,'man',14000.00,'pthon');
insert into it_student values(null,'tom',24,'man',13000.40,'java');
insert into it_student values(null,'jerry',27,'woman',21000.00,'tester');
insert into it_student values(null,'bullen',22,'man',8000.20,'pthon');Query OK, 1 row affected (0.01 sec)

mysql> insert into it_student values(null,'xiaoming',24,'man',16000.20,'java');
Query OK, 1 row affected (0.01 sec)

mysql> insert into it_student values(null,'laowang',29,'man',15000.00,'java');
Query OK, 1 row affected (0.03 sec)

mysql> insert into it_student values(null,'laowen',26,'man',10000.00,'yunwei');
Query OK, 1 row affected (0.87 sec)

mysql> insert into it_student values(null,'laotian',26,'man',14000.00,'pthon');
Query OK, 1 row affected (0.00 sec)

mysql> insert into it_student values(null,'tom',24,'man',13000.40,'java');
Query OK, 1 row affected (0.00 sec)

mysql> insert into it_student values(null,'jerry',27,'woman',21000.00,'tester');
Query OK, 1 row affected (0.00 sec)

mysql> insert into it_student values(null,'bullen',22,'man',8000.20,'pthon');
Query OK, 1 row affected (0.01 sec)

 

一.高级查询语句
1.查询相关符号
(1)比较符号，
等于=
不等于!=,<>
大于>
小于<
小于等于<=
大于等于>=
(2)模糊查询
like:模糊匹配关键字
%:匹配0个或任意多个字符
_:匹配单个字符
(3)逻辑符号
条件1 and(&&) 条件2:和，同时满足条件1和条件2
条件1 or(||) 条件2:或，满足条件1或条件2
not(!) 条件1:不满足条件1即可
(4)判断列所在的返回
field between...and...:field的值在...和...之间的记录
field in (值1，值2...)：field的值等于值1，值2...的记录
field not in (值1，值2...)：field的值不等于值1，值2...的记录
(5)其他字符
regexp:可以匹配正则表达式
distinct:可以去除结果列中的重复值，只保留一个

2.查询案例
(1)比较符号使用

mysql> select * from it_student where name = 'xianqian';
+----+----------+------+-------+----------+---------+
| id | name     | age  | sex   | salary   | subject |
+----+----------+------+-------+----------+---------+
|  1 | xianqian |   20 | woman | 21000.00 | tester  |
+----+----------+------+-------+----------+---------+
1 row in set (0.00 sec)

mysql> select * from it_student where sex != 'man';
+----+----------+------+-------+----------+---------+
| id | name     | age  | sex   | salary   | subject |
+----+----------+------+-------+----------+---------+
|  1 | xianqian |   20 | woman | 21000.00 | tester  |
|  7 | jerry    |   27 | woman | 21000.00 | tester  |
+----+----------+------+-------+----------+---------+
2 rows in set (0.00 sec)

mysql> select id ,name,salary from it_student where age>23;
+----+----------+----------+
| id | name     | salary   |
+----+----------+----------+
|  2 | xiaoming | 16000.20 |
|  3 | laowang  | 15000.00 |
|  4 | laowen   | 10000.00 |
|  5 | laotian  | 14000.00 |
|  6 | tom      | 13000.40 |
|  7 | jerry    | 21000.00 |
+----+----------+----------+
6 rows in set (0.00 sec)

mysql> select id,name,salary from it_student where age<25;
+----+----------+----------+
| id | name     | salary   |
+----+----------+----------+
|  1 | xianqian | 21000.00 |
|  2 | xiaoming | 16000.20 |
|  6 | tom      | 13000.40 |
|  8 | bullen   |  8000.20 |
+----+----------+----------+
4 rows in set (0.00 sec)

mysql> select * from it_student where salary >=20000;
+----+----------+------+-------+----------+---------+
| id | name     | age  | sex   | salary   | subject |
+----+----------+------+-------+----------+---------+
|  1 | xianqian |   20 | woman | 21000.00 | tester  |
|  7 | jerry    |   27 | woman | 21000.00 | tester  |
+----+----------+------+-------+----------+---------+
2 rows in set (0.00 sec)

mysql> select * from it_student where salary <=18000;
+----+----------+------+------+----------+---------+
| id | name     | age  | sex  | salary   | subject |
+----+----------+------+------+----------+---------+
|  2 | xiaoming |   24 | man  | 16000.20 | java    |
|  3 | laowang  |   29 | man  | 15000.00 | java    |
|  4 | laowen   |   26 | man  | 10000.00 | yunwei  |
|  5 | laotian  |   26 | man  | 14000.00 | pthon   |
|  6 | tom      |   24 | man  | 13000.40 | java    |
|  8 | bullen   |   22 | man  |  8000.20 | pthon   |
+----+----------+------+------+----------+---------+
6 rows in set (0.00 sec)

注意：当列值为null时，不能使用比较符号，需要使用field is null 或者field is not null

mysql> select * from it_student where sex is null;
Empty set (0.01 sec)

mysql> select * from it_student where age is not null;
+----+----------+------+-------+----------+---------+
| id | name     | age  | sex   | salary   | subject |
+----+----------+------+-------+----------+---------+
|  1 | xianqian |   20 | woman | 21000.00 | tester  |
|  2 | xiaoming |   24 | man   | 16000.20 | java    |
|  3 | laowang  |   29 | man   | 15000.00 | java    |
|  4 | laowen   |   26 | man   | 10000.00 | yunwei  |
|  5 | laotian  |   26 | man   | 14000.00 | pthon   |
|  6 | tom      |   24 | man   | 13000.40 | java    |
|  7 | jerry    |   27 | woman | 21000.00 | tester  |
|  8 | bullen   |   22 | man   |  8000.20 | pthon   |
+----+----------+------+-------+----------+---------+
8 rows in set (0.00 sec)

(2)模糊匹配

mysql> select * from it_student where name like 'lao%';
+----+---------+------+------+----------+---------+
| id | name    | age  | sex  | salary   | subject |
+----+---------+------+------+----------+---------+
|  3 | laowang |   29 | man  | 15000.00 | java    |
|  4 | laowen  |   26 | man  | 10000.00 | yunwei  |
|  5 | laotian |   26 | man  | 14000.00 | pthon   |
+----+---------+------+------+----------+---------+
3 rows in set (0.00 sec)

mysql> select * from it_student where name like '___';
+----+------+------+------+----------+---------+
| id | name | age  | sex  | salary   | subject |
+----+------+------+------+----------+---------+
|  6 | tom  |   24 | man  | 13000.40 | java    |
+----+------+------+------+----------+---------+
1 row in set (0.00 sec)

(3)逻辑符号使用
select * from it_student where name='xianqian' or sex='woman';
select * from it_student where sex = 'woman' and age <25;
select * from it_student where not sex = 'woman';

mysql> select * from it_student where name='xianqian' or sex='woman';
+----+----------+------+-------+----------+---------+
| id | name     | age  | sex   | salary   | subject |
+----+----------+------+-------+----------+---------+
|  1 | xianqian |   20 | woman | 21000.00 | tester  |
|  7 | jerry    |   27 | woman | 21000.00 | tester  |
+----+----------+------+-------+----------+---------+
2 rows in set (0.00 sec)

mysql> select * from it_student where sex = 'woman' and age <25;
+----+----------+------+-------+----------+---------+
| id | name     | age  | sex   | salary   | subject |
+----+----------+------+-------+----------+---------+
|  1 | xianqian |   20 | woman | 21000.00 | tester  |
+----+----------+------+-------+----------+---------+
1 row in set (0.01 sec)

mysql> select * from it_student where not sex = 'woman';
+----+----------+------+------+----------+---------+
| id | name     | age  | sex  | salary   | subject |
+----+----------+------+------+----------+---------+
|  2 | xiaoming |   24 | man  | 16000.20 | java    |
|  3 | laowang  |   29 | man  | 15000.00 | java    |
|  4 | laowen   |   26 | man  | 10000.00 | yunwei  |
|  5 | laotian  |   26 | man  | 14000.00 | pthon   |
|  6 | tom      |   24 | man  | 13000.40 | java    |
|  8 | bullen   |   22 | man  |  8000.20 | pthon   |
+----+----------+------+------+----------+---------+
6 rows in set (0.00 sec)

(4)判断范围
select * from it_student where age between 22 and 27;
select * from it_student where subject in ('java','python');
select * from it_student where subject not in ('php','yunwei','java');

mysql> select * from it_student where age between 22 and 27;
+----+----------+------+-------+----------+---------+
| id | name     | age  | sex   | salary   | subject |
+----+----------+------+-------+----------+---------+
|  2 | xiaoming |   24 | man   | 16000.20 | java    |
|  4 | laowen   |   26 | man   | 10000.00 | yunwei  |
|  5 | laotian  |   26 | man   | 14000.00 | pthon   |
|  6 | tom      |   24 | man   | 13000.40 | java    |
|  7 | jerry    |   27 | woman | 21000.00 | tester  |
|  8 | bullen   |   22 | man   |  8000.20 | pthon   |
+----+----------+------+-------+----------+---------+
6 rows in set (0.00 sec)

mysql> select * from it_student where subject in ('java','python');
+----+----------+------+------+----------+---------+
| id | name     | age  | sex  | salary   | subject |
+----+----------+------+------+----------+---------+
|  2 | xiaoming |   24 | man  | 16000.20 | java    |
|  3 | laowang  |   29 | man  | 15000.00 | java    |
|  6 | tom      |   24 | man  | 13000.40 | java    |
+----+----------+------+------+----------+---------+
3 rows in set (0.00 sec)

mysql> select * from it_student where subject not in ('php','yunwei','java');
+----+----------+------+-------+----------+---------+
| id | name     | age  | sex   | salary   | subject |
+----+----------+------+-------+----------+---------+
|  1 | xianqian |   20 | woman | 21000.00 | tester  |
|  5 | laotian  |   26 | man   | 14000.00 | pthon   |
|  7 | jerry    |   27 | woman | 21000.00 | tester  |
|  8 | bullen   |   22 | man   |  8000.20 | pthon   |
+----+----------+------+-------+----------+---------+
4 rows in set (0.00 sec)

(5)其他符号使用
select distinct name from it_student;
select * from it_student where name regexp '^xian';
select * from it_student where name regexp 'qian$';

 

mysql> select distinct name from it_student;
+----------+
| name     |
+----------+
| xianqian |
| xiaoming |
| laowang  |
| laowen   |
| laotian  |
| tom      |
| jerry    |
| bullen   |
+----------+
8 rows in set (0.00 sec)

mysql> select * from it_student where name regexp '^xian';
+----+----------+------+-------+----------+---------+
| id | name     | age  | sex   | salary   | subject |
+----+----------+------+-------+----------+---------+
|  1 | xianqian |   20 | woman | 21000.00 | tester  |
+----+----------+------+-------+----------+---------+
1 row in set (0.01 sec)

mysql> select * from it_student where name regexp 'qian$';
+----+----------+------+-------+----------+---------+
| id | name     | age  | sex   | salary   | subject |
+----+----------+------+-------+----------+---------+
|  1 | xianqian |   20 | woman | 21000.00 | tester  |
+----+----------+------+-------+----------+---------+
1 row in set (0.00 sec)

二.SQL查询中的五子句
顺序：where 条件 group by field having 条件 order by field limit 条件
1.where子句可以对列进行过滤，只将满足条件的记录输出，在上面高级查询中都是使用where过滤
2.group by field子句
(1)group by field:field为表中的某一列，表示以该列进行分组，可以统计每一个分组的信息，通常会和一些统计函数联合使用，单独使用得到的数据没有意义
(2常见的统计函数
max(field):获取field列中的最大值
min(field):获取field列中的最小值
sum(field):获取filed列数据的总和
avg(field):获取filed列的平均值
count(*):获取记录的行数
案例：
select max(salary) from it_student;
select min(age) from it_student;
select sum(salary) from it_student;
select avg(salary) from it_student;
select count(*) from it_student;

mysql> select max(salary) from it_student;
+-------------+
| max(salary) |
+-------------+
|    21000.00 |
+-------------+
1 row in set (0.00 sec)

mysql> select min(age) from it_student;
+----------+
| min(age) |
+----------+
|       20 |
+----------+
1 row in set (0.01 sec)

mysql> select sum(salary) from it_student;
+-------------+
| sum(salary) |
+-------------+
|   118000.80 |
+-------------+
1 row in set (0.00 sec)

mysql> select avg(salary) from it_student;
+--------------+
| avg(salary)  |
+--------------+
| 14750.100000 |
+--------------+
1 row in set (0.00 sec)

mysql> select count(*) from it_student;
+----------+
| count(*) |
+----------+
|        8 |
+----------+
1 row in set (0.01 sec)

(3)group by和统计函数的联合使用
select subject,count(*),sum(salary) from it_student group by subject;
select sex ,avg(salary) from it_student group by sex;

mysql> select subject,count(*),sum(salary) from it_student group by subject;
+---------+----------+-------------+
| subject | count(*) | sum(salary) |
+---------+----------+-------------+
| java    |        3 |    44000.60 |
| pthon   |        2 |    22000.20 |
| tester  |        2 |    42000.00 |
| yunwei  |        1 |    10000.00 |
+---------+----------+-------------+
4 rows in set (0.00 sec)

mysql> select sex ,avg(salary) from it_student group by sex;
+-------+--------------+
| sex   | avg(salary)  |
+-------+--------------+
| man   | 12666.800000 |
| woman | 21000.000000 |
+-------+--------------+
2 rows in set (0.00 sec)

3.having子句
having子句功能和where类似，可以过滤。不同的是having可以对分组后的结果集进行过滤，where不行
一般，可以通过where过滤列的都可以通过having过滤
案例：
select * from it_student having age between 23 and 27;
select subject,count(*) from it_student group by subject having count(*) >=2;

mysql> select * from it_student having age between 23 and 27;
+----+----------+------+-------+----------+---------+
| id | name     | age  | sex   | salary   | subject |
+----+----------+------+-------+----------+---------+
|  2 | xiaoming |   24 | man   | 16000.20 | java    |
|  4 | laowen   |   26 | man   | 10000.00 | yunwei  |
|  5 | laotian  |   26 | man   | 14000.00 | pthon   |
|  6 | tom      |   24 | man   | 13000.40 | java    |
|  7 | jerry    |   27 | woman | 21000.00 | tester  |
+----+----------+------+-------+----------+---------+
5 rows in set (0.00 sec)

mysql> select subject,count(*) from it_student group by subject having count(*) >=2;
+---------+----------+
| subject | count(*) |
+---------+----------+
| java    |        3 |
| pthon   |        2 |
| tester  |        2 |
+---------+----------+
3 rows in set (0.00 sec)

4.order by 子句
order by field:可以对field列进行排序，默认是升序
order by field asc;升序排列，可以不写asc
order by field desc;降序排列
注意：可以排序字符串，数字，日期
案例：
select * from it_student order by salary;
select id, name, age, salary from it_student order by age desc;

mysql> select * from it_student order by salary;
+----+----------+------+-------+----------+---------+
| id | name     | age  | sex   | salary   | subject |
+----+----------+------+-------+----------+---------+
|  8 | bullen   |   22 | man   |  8000.20 | pthon   |
|  4 | laowen   |   26 | man   | 10000.00 | yunwei  |
|  6 | tom      |   24 | man   | 13000.40 | java    |
|  5 | laotian  |   26 | man   | 14000.00 | pthon   |
|  3 | laowang  |   29 | man   | 15000.00 | java    |
|  2 | xiaoming |   24 | man   | 16000.20 | java    |
|  1 | xianqian |   20 | woman | 21000.00 | tester  |
|  7 | jerry    |   27 | woman | 21000.00 | tester  |
+----+----------+------+-------+----------+---------+
8 rows in set (0.00 sec)

mysql> select id, name, age, salary from it_student order by age desc;
+----+----------+------+----------+
| id | name     | age  | salary   |
+----+----------+------+----------+
|  3 | laowang  |   29 | 15000.00 |
|  7 | jerry    |   27 | 21000.00 |
|  4 | laowen   |   26 | 10000.00 |
|  5 | laotian  |   26 | 14000.00 |
|  2 | xiaoming |   24 | 16000.20 |
|  6 | tom      |   24 | 13000.40 |
|  8 | bullen   |   22 |  8000.20 |
|  1 | xianqian |   20 | 21000.00 |
+----+----------+------+----------+
8 rows in set (0.00 sec)

5.limit，限制，可用于分页展示
limit 可以限制输出的记录数及从哪里开始输出
格式1：limit 数字n 输出前n条数据
格式2：limit 偏移量m 数字n 偏移量从0开始计算，0表示第一条记录，1表示第二条记录...，从第m+1条记录开始输出，共输出n条记录
select * from it_student order by salary desc limit 4; 　　#显示工资最高的四位员工
select * from it_student order by age limit 5; 　　#显示年龄最小的五位员工
select id,name,salary from it_student limit 3,3; 　　#显示第4,5,6条数据

mysql> select * from it_student order by salary desc limit 4;
+----+----------+------+-------+----------+---------+
| id | name     | age  | sex   | salary   | subject |
+----+----------+------+-------+----------+---------+
|  1 | xianqian |   20 | woman | 21000.00 | tester  |
|  7 | jerry    |   27 | woman | 21000.00 | tester  |
|  2 | xiaoming |   24 | man   | 16000.20 | java    |
|  3 | laowang  |   29 | man   | 15000.00 | java    |
+----+----------+------+-------+----------+---------+
4 rows in set (0.00 sec)

mysql> select * from it_student order by age limit 5;
+----+----------+------+-------+----------+---------+
| id | name     | age  | sex   | salary   | subject |
+----+----------+------+-------+----------+---------+
|  1 | xianqian |   20 | woman | 21000.00 | tester  |
|  8 | bullen   |   22 | man   |  8000.20 | pthon   |
|  2 | xiaoming |   24 | man   | 16000.20 | java    |
|  6 | tom      |   24 | man   | 13000.40 | java    |
|  4 | laowen   |   26 | man   | 10000.00 | yunwei  |
+----+----------+------+-------+----------+---------+
5 rows in set (0.00 sec)

mysql> select id,name,salary from it_student limit 3,3;
+----+---------+----------+
| id | name    | salary   |
+----+---------+----------+
|  4 | laowen  | 10000.00 |
|  5 | laotian | 14000.00 |
|  6 | tom     | 13000.40 |
+----+---------+----------+
3 rows in set (0.00 sec)

三.四则运算和合并函数
1.四则运算

mysql> select 2+3;
+-----+
| 2+3 |
+-----+
|   5 |
+-----+
1 row in set (0.00 sec)

mysql> select 3-4;
+-----+
| 3-4 |
+-----+
|  -1 |
+-----+
1 row in set (0.00 sec)

mysql> select 9/4;
+--------+
| 9/4    |
+--------+
| 2.2500 |
+--------+
1 row in set (0.00 sec)

mysql> select 2*8;
+-----+
| 2*8 |
+-----+
|  16 |
+-----+
1 row in set (0.00 sec)

也可以使用from dual;dual被称为万能表，它里面可以看作什么都没有

mysql> select 4+5 from dual;
+-----+
| 4+5 |
+-----+
|   9 |
+-----+
1 row in set (0.00 sec)

2.合并函数
concat()函数可以将括号中的所有参数按顺序连接起来
select concat(name,'=',salary) from it_student;

mysql> select concat(name,'=',salary) from it_student;
+-------------------------+
| concat(name,'=',salary) |
+-------------------------+
| xianqian=21000.00       |
| xiaoming=16000.20       |
| laowang=15000.00        |
| laowen=10000.00         |
| laotian=14000.00        |
| tom=13000.40            |
| jerry=21000.00          |
| bullen=8000.20          |
+-------------------------+
8 rows in set (0.00 sec)

四.多表查询(后续补充)
1.内连接
select * from t1,t2 where t1.name = t2.name;
select * from t1,t2 where t1 inner join t2 on t1.name = t2.name;
2.外连接(左外连接和右外连接)
select * from t1,t2 where t1 left join t2 on t1.name = t2.name;
select * from t1,t2 where t1 right join t2 on t1.name = t2.name;