mysql 高级语法_3、sql高级语法（一）

1、规定要返回的数目

mysql中

#从websites表中返回5条记录，没有5条记录则返回全部

select * from websites limit 5;

2、like语法

#1 筛选出name以g开头的所有记录

select * from websites where name like 'g%';

#2 筛选出name包含rr的所有记录

select * from websites where name like '%rr%'

#3 筛选出name不包含rr的所有记录

select * from websites where name not like '%rr%'

#4 筛选出name倒数第二个字母为q的所有记录

select * from websites where name like '%q_'

3、sql通配符

% 替代0个或者多个字符

_ 代替一个字符

[charlist] 字符列中的任何单一字符

[^charlist] 不在字符类中的任何单一字符【注意^在中括号内】

利用_匹配baidu字样

| Id | name | url | alexa | country |

+----+--------+-----------+-------+---------+

| 1 | jh | jhurl | 12 | cn |

| 2 | baidu | baiduurl | 12 | cn |

| 3 | 163 | 163url | 12 | cn |

| 7 | wangyi | wangyiurl | 32 | cn |

| 4 | ali | aliurl | 23 | cn |

| 8 | google | googleurl | 233 | cn |

+----+--------+-----------+-------+---------+

6 rows in set (0.00 sec)

mysql> select * from websites where name like

+----+-------+----------+-------+---------+

| Id | name | url | alexa | country |

+----+-------+----------+-------+---------+

| 2 | baidu | baiduurl | 12 | cn |

+----+-------+----------+-------+---------+

利用[charlist]和[^charlist]进行正则匹配，注意 regexp的使用

mysql> select * from websites ;

+----+--------+-----------+-------+---------+

| Id | name | url | alexa | country |

+----+--------+-----------+-------+---------+

| 1 | jh | jhurl | 12 | cn |

| 2 | baidu | baiduurl | 12 | cn |

| 3 | 163 | 163url | 12 | cn |

| 7 | wangyi | wangyiurl | 32 | cn |

| 4 | ali | aliurl | 23 | cn |

| 8 | google | googleurl | 233 | cn |

+----+--------+-----------+-------+---------+

6 rows in set (0.00 sec)

mysql> select * from websites where name regexp '^[a-c]';

+----+-------+----------+-------+---------+

| Id | name | url | alexa | country |

+----+-------+----------+-------+---------+

| 2 | baidu | baiduurl | 12 | cn |

| 4 | ali | aliurl | 23 | cn |

+----+-------+----------+-------+---------+

2 rows in set (0.00 sec)

mysql> select * from websites where name regexp '^[^c-z]';

+----+-------+----------+-------+---------+

| Id | name | url | alexa | country |

+----+-------+----------+-------+---------+

| 2 | baidu | baiduurl | 12 | cn |

| 3 | 163 | 163url | 12 | cn |

| 4 | ali | aliurl | 23 | cn |

+----+-------+----------+-------+---------+

3 rows in set (0.00 sec)

4 、in的使用

作为where字句，可以匹配多个参数

mysql> select * from websites where name in ('163','baidu');

+----+-------+----------+-------+---------+

| Id | name | url | alexa | country |

+----+-------+----------+-------+---------+

| 2 | baidu | baiduurl | 12 | cn |

| 3 | 163 | 163url | 12 | cn |

+----+-------+----------+-------+---------+

与=的区别，=是只可以匹配一个值，而in是多个

5、between的使用

如字面意思，就是筛选在两个值范围的条目,数字及字母都适用，具体包不包括边界值，看数据库而定。

mysql> select * from websites where id between 2 and 4;

+----+-------+----------+-------+---------+

| Id | name | url | alexa | country |

+----+-------+----------+-------+---------+

| 2 | baidu | baiduurl | 12 | cn |

| 3 | 163 | 163url | 12 | cn |

| 4 | ali | aliurl | 23 | cn |

+----+-------+----------+-------+---------+

6、别名的使用

别名就是在显示时的一个列表名，可以以另一个名字显示。也可以把几个列合在一起显示

as语句取别名，concat是连接字符的

mysql> select * from websites;

+----+--------+-----------+-------+---------+

| Id | name | url | alexa | country |

+----+--------+-----------+-------+---------+

| 1 | jh | jhurl | 12 | cn |

| 2 | baidu | baiduurl | 12 | cn |

| 3 | 163 | 163url | 12 | cn |

| 7 | wangyi | wangyiurl | 32 | cn |

| 4 | ali | aliurl | 23 | cn |

| 8 | google | googleurl | 233 | cn |

+----+--------+-----------+-------+---------+

6 rows in set (0.00 sec)

mysql> select name as n,url as u from websites;

+--------+-----------+

| n | u |

+--------+-----------+

| jh | jhurl |

| baidu | baiduurl |

| 163 | 163url |

| wangyi | wangyiurl |

| ali | aliurl |

| google | googleurl |

+--------+-----------+

6 rows in set (0.00 sec)

mysql> select name,concat(url,alexa,country) from websites;

+--------+---------------------------+

| name | concat(url,alexa,country) |

+--------+---------------------------+

| jh | jhurl12cn |

| baidu | baiduurl12cn |

| 163 | 163url12cn |

| wangyi | wangyiurl32cn |

| ali | aliurl23cn |

| google | googleurl233cn |

+--------+---------------------------+

7、join

inner join:两个数据库都有的条目显示出来

left join:除了两者都有的，左边的全部显示

right join:除了两者都有的，右边的全部显示

full join:全部显示

mysql> select * from websites;

+----+--------+-----------+-------+---------+

| Id | name | url | alexa | country |

+----+--------+-----------+-------+---------+

| 1 | jh | jhurl | 12 | cn |

| 2 | baidu | baiduurl | 12 | cn |

| 3 | 163 | 163url | 12 | cn |

| 7 | wangyi | wangyiurl | 32 | cn |

| 4 | ali | aliurl | 23 | cn |

| 8 | google | googleurl | 233 | cn |

+----+--------+-----------+-------+---------+

6 rows in set (0.00 sec)

mysql> select * from access_log;

+----+-------+-------+

| Id | webid | count |

+----+-------+-------+

| 1 | 2 | 8 |

| 2 | 4 | 74 |

| 3 | 2 | 24 |

| 4 | 3 | 45 |

+----+-------+-------+

4 rows in set (0.00 sec)

mysql> select websites.id,websites.name,websites.url,access_log.count from websi

tes inner join access_log on websites.id=access_log.webid;

+----+-------+----------+-------+

| id | name | url | count |

+----+-------+----------+-------+

| 2 | baidu | baiduurl | 8 |

| 4 | ali | aliurl | 74 |

| 2 | baidu | baiduurl | 24 |

| 3 | 163 | 163url | 45 |

+----+-------+----------+-------+

4 rows in set (0.00 sec)