MySQL输出半径为2的面积_mysql基本语法(2)

数据准备

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;