数据准备
mysql> create tableit_student(-> id int unsigned not null auto_increment primary key,-> name varchar(30),-> age tinyintunsigned,-> sex enum('man','woman'),-> salary decimal(11,2),-> subject varchar(30)-> ) engine=innodb default charset=utf8;
Query OK,0 rows affected (0.01sec)
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.01sec)
mysql> insert into it_student values(null,'xiaoming',24,'man',16000.20,'java');
Query OK,1 row affected (0.01sec)
mysql> insert into it_student values(null,'laowang',29,'man',15000.00,'java');
Query OK,1 row affected (0.03sec)
mysql> insert into it_student values(null,'laowen',26,'man',10000.00,'yunwei');
Query OK,1 row affected (0.87sec)
mysql> insert into it_student values(null,'laotian',26,'man',14000.00,'pthon');
Query OK,1 row affected (0.00sec)
mysql> insert into it_student values(null,'tom',24,'man',13000.40,'java');
Query OK,1 row affected (0.00sec)
mysql> insert into it_student values(null,'jerry',27,'woman',21000.00,'tester');
Query OK,1 row affected (0.00sec)
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.00sec)
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.00sec)
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.00sec)
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.00sec)
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.00sec)
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;
Emptyset (0.01sec)
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.00sec)
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.00sec)
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.01sec)
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.00sec)
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.00sec)
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 fromit_student;+----------+
| name |
+----------+
| xianqian |
| xiaoming |
| laowang |
| laowen |
| laotian |
| tom |
| jerry |
| bullen |
+----------+
8 rows in set (0.00sec)
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.01sec)
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) fromit_student;+-------------+
| max(salary) |
+-------------+
| 21000.00 |
+-------------+
1 row in set (0.00sec)
mysql> select min(age) fromit_student;+----------+
| min(age) |
+----------+
| 20 |
+----------+
1 row in set (0.01sec)
mysql> select sum(salary) fromit_student;+-------------+
| sum(salary) |
+-------------+
| 118000.80 |
+-------------+
1 row in set (0.00sec)
mysql> select avg(salary) fromit_student;+--------------+
| avg(salary) |
+--------------+
| 14750.100000 |
+--------------+
1 row in set (0.00sec)
mysql> select count(*) fromit_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 bysubject;+---------+----------+-------------+
| 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.00sec)
mysql> select sex ,avg(salary) from it_student group bysex;+-------+--------------+
| 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.00sec)
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 bysalary;+----+----------+------+-------+----------+---------+
| 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.00sec)
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.00sec)
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.00sec)
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.00sec)
mysql> select 3-4;+-----+
| 3-4 |
+-----+
| -1 |
+-----+
1 row in set (0.00sec)
mysql> select 9/4;+--------+
| 9/4 |
+--------+
| 2.2500 |
+--------+
1 row in set (0.00sec)
mysql> select 2*8;+-----+
| 2*8 |
+-----+
| 16 |
+-----+
1 row in set (0.00 sec)
也可以使用from dual;dual被称为万能表,它里面可以看作什么都没有
mysql> select 4+5 fromdual;+-----+
| 4+5 |
+-----+
| 9 |
+-----+
1 row in set (0.00 sec)
2.合并函数
concat()函数可以将括号中的所有参数按顺序连接起来
select concat(name,'=',salary) from it_student;
mysql> select concat(name,'=',salary) fromit_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;