首页>>数据库>>MySQL基本语法及正则表达式应用
MySQL基本语法及正则表达式应用
来源: 时间: 2015-03-11 21:46:30

一、MySQL中select的基本语法形式:
select 属性列表
from 表名和视图列表
[where 条件表达式1]
[group by 属性名1[having 条件表达式2]]
[order by 属性名2[asc|desc]]
说明:
1、如果有where子句,就按照“条件表达式1”指定的条件进行查询。如果没有where子句,就查询所有记录;
2、如果有group by子句,就按照“属性名1”指定的字段 进行分组。如果group by子句后带着having关键字,那么只有满足“条件表达式2”中指定的条件的才能够输出。group by子句通常和count()、sum()等聚合函数一起使用;
3、如果有order by子句,就按照“属性名2”指定的字段进行排序。排序方式由“asc”和“desc”两个参数指出,默认的情况时“asc”。
二、单表查询
    单表查询是指从一张表中查询所需要的数据。查询数据时,可以从一张表中查询数据,也可以从多张表中查询数据。两者的查询方式上有一定的区别。因为单表查询只在一张表上进行操作,所以查询比较简单。
1、查询所有字段:
(1)列出表的所有字段
(2)使用“*”查询所有字段
2、查询指定字段:
在属性列表中列出所要查询的字段,查询结果显示顺序与select属性列表中设定顺序相同。
3、查询指定记录:
where子句可以用来指定查询条件
4、带in关键字的查询:
语法规则 [not]in(元素1,元素2,…,元素n)
5、带between and 的范围查询
select * from employee where age not between 15 and 25
6、带like的字符匹配查询
(1)完整字符串匹配:
select * from employee where name like 'Aric';select * from employee where name = 'Aric';
like跟=相同。
(2)不完整字符串匹配(模糊查询),使用通配符的时候就不能用等号了:
借助于通配符%(%为任意字符或字符串)
select * from employee where homeaddr like '北京%';匹配homeaddr前两个汉字为'北京'的记录,此时用=查询结果为空。
借助于通配符_(_的位置为一个任意字符)
select * from employee where name like 'Ar_c'
not like 与like 用法类同。
7、查询空值(is null):
如果字段的值是空值,则满足查询条件,该记录被查询出来。如果字段的值不是空值,则不满足查询条件。
其语法规则如下:
is [not] null
其中,“not”是可选参数,加上not表示字段不是空值满足条件。
8、带and的多条件查询:
and关键字可以用来联合多个条件进行查询。使用and关键字时,只有同时满足所有查询条件的记录才会被查询出来,如果不满足这些查询条件的其中一个,这样的记录将被排除。
and关键字的语法规则如下:
条件表达式1 and 条件表达式2 […and 条件表达式n]
其中,and可以连接两个条件表达式。而且,可以同时使用多个and关键字,这样可以连接更多的条件表达式。
9、带or的多条件查询
只要满足这几个查询条件中的一个,这样的记录将会被查询出来。如果不满足这些查询条件的任何一个,这样的记录将被排除掉。or关键字的语法规则跟and 类同。
10、查询结果不重复
如果表中某些字段上没有唯一性约束,这些字段可能存在着重复的值。可以使用distinct关键字来消除字段中的重复记录。
例子:select distinct d_id from employee;
11、对查询结果排序
从表中查询出来的数据可能是无序的,或者其排列顺序不是用户所期望的顺序,为了是查询结果的顺序满足用户的要求,可以使用order by对记录进行排序。
语法规则:
order by 属性名[asc|desc]
例子:select * from employee order by age;select * from employee order by d_id asc,age desc;
12、分组查询
group by关键字可以将查询结果按某个字段或者多个字段进行分组。字段中值相等的为一组。
其语法规则:
group by 属性名[having 条件表达式][with rollup]
有以下几种情况:
单独使用group by关键字来分组
group by关键字与group_concat()函数一起使用
group by关键字与集合函数一起使用
group by关键字与having一起使用
按多个字段进行分组
group by 关键字与with rollup一起使用
例子:
 select * from employee
 group by d_id;
当有两条记录的d_id相同时,只能输出第一条。
用group by对多个字段进行分组的时候,首先按照第一个字段分组,在第一个字段相同时再按第二个字段进行分组。
select sex,count(sex) from employee
group by sex with rollup;
with rollup在最后插入一条求和记录。
例子:
mysql> select sex,group_concat(name) from employee
    -> group by sex with rollup;
+------+---------------------+
| sex  | group_concat(name)  |
+------+---------------------+
| 男   | 张三,王五,Aric      |
| 女   | 李四                |
| NULL | 张三,王五,Aric,李四 |
+------+---------------------+
3 rows in set (0.00 sec)
13、用limit限制查询结果的数量:
可以用来指定查询结果从哪条记录开始显示。还可以指定一共显示多少条记录。有两种使用方式。
(1)不指定初始位置
 select * from employee limit 2;
不指定位置时,从第一条开始,按limit指定数量显示。
如果limit值大于表中记录数,会显示表中所有记录。
(2)指定初始位置
 select * from employee limit 0,2;
0指初始位置,2表示记录数。
从第一条记录开始,查询数量为2条。
mysql中0表示第一条,1表示第二条。
三、使用集合函数查询(group by关键字通常需要与集合函数一起使用)
1、count()用来统计记录的条数
select count(*) from employee;
查询employee表中的所有记录数
select d_id,count(*)from employee
group by d_id;
统计employee表中不同d_id值的记录数
2、sum()用来计算字段的值的综合
select num,sum(score) from grade where num=1001;
统计学号为1001的学生课程成绩的总和
select num,sum(score) from grade group by num;
按学号统计每个同学课程成绩的总和
3、avg()用来计算字段的值的平均值
select num,avg(score) from grade group by num;
求每个学生的平均分数
select course,avg(score) from grade group by course;
求每个科目的平均分数
4、max()用来查询字段的最大值
select num,course,max(score) from grade group by course;
例子:
mysql> select * from work;
+------+------+------+---------+
| id   | name | sex  | info    |
+------+------+------+---------+
| 1001 | hjh  | NULL | NULL    |
| 1002 | cch  | NULL | NULL    |
| 1003 | zk   | NULL | student |
+------+------+------+---------+
3 rows in set (0.00 sec)
mysql> select max(name) from work;
+-----------+
| max(name) |
+-----------+
| zk        |
+-----------+
1 row in set (0.00 sec)
字符串比较,先比较第一个字符的asi码,相等时再比较后面的字符。
求出每一科目的最高分的学号、科目和成绩。
5、min()用来查询字段的最小值(类同于max())
四、连接查询
    连接查询是将两个或两个以上的表按某个条件连接起来,从中选择选取需要的数据,连接查询是同时查询两个或两个以上的表时使用的。当不同的表中存在表示相同意义的字段时,可以通过该字段来连接这几个表。
1、内连接查询
    当两个表中存在表示相同意义的字段时,可以通过该字段来连接这两张表。当该字段的值相等时,就查询出该记录
例子:
mysql> select num,name,employee.d_id,age,sex,d_name,function
    -> from employee,department
    -> where employee.d_id=department.d_id;
+-----+------+------+------+------+--------+----------+
| num | name | d_id | age  | sex  | d_name | function |
+-----+------+------+------+------+--------+----------+
|   1 | 张三 | 1001 |   26 | 男   | 科研部 | 研发产品 |
|   2 | 李四 | 1001 |   24 | 女   | 科研部 | 研发产品 |
|   3 | 王五 | 1002 |   25 | 男   | 生产部 | 生产产品 |
+-----+------+------+------+------+--------+----------+
3 rows in set (0.00 sec)
当表中存在两个字段名相同,查询时必须指定是哪张表的哪个字段。
2、外连接查询
    外连接查询可以查询两个或两个以上的表。外连接查询也需要通过指定字段来进行连接。当该字段取值相等时可以查询出该记录。而且,该字段取值不相等的记录也可以查询出来。外连接包括左连接查询和右连接查询。其基本语法如下:
select 属性名列表
from 表名1 left|right join 表名2
on 表名1.属性1=表名2.属性2;
左连接查询中,可以显示表名1中的所有记录,表名2中只有匹配的记录可以显示呢出来。右连接类同。
左连接右连接实例:
mysql> select * from department;
+------+--------+----------+---------------+
| d_id | d_name | function | address       |
+------+--------+----------+---------------+
| 1001 | 科研部 | 研发产品 | 3号楼5层      |
| 1002 | 生产部 | 生产产品 | 5号楼1层      |
| 1003 | 销售部 | 策划销售 | 1号楼销售大厅 |
+------+--------+----------+---------------+
3 rows in set (0.00 sec)
mysql> select * from employee;
+-----+------+------+------+------+--------------+
| num | d_id | name | age  | sex  | homeaddr     |
+-----+------+------+------+------+--------------+
|   1 | 1001 | 张三 |   26 | 男   | 北京市海淀区 |
|   2 | 1001 | 李四 |   24 | 女   | 北京市昌平区 |
|   3 | 1002 | 王五 |   25 | 男   | 湖南省长沙市 |
|   4 | 1004 | Aric |   15 | 男   | England      |
+-----+------+------+------+------+--------------+
4 rows in set (0.00 sec)
mysql>  select num,name,employee.d_id,age,sex,d_name,function
    ->  from employee left join department
    ->  on employee.d_id=department.d_id;
+-----+------+------+------+------+--------+----------+
| num | name | d_id | age  | sex  | d_name | function |
+-----+------+------+------+------+--------+----------+
|   1 | 张三 | 1001 |   26 | 男   | 科研部 | 研发产品 |
|   2 | 李四 | 1001 |   24 | 女   | 科研部 | 研发产品 |
|   3 | 王五 | 1002 |   25 | 男   | 生产部 | 生产产品 |
|   4 | Aric | 1004 |   15 | 男   | NULL   | NULL     |
+-----+------+------+------+------+--------+----------+
4 rows in set (0.00 sec)
mysql>  select num,name,employee.d_id,age,sex,d_name,function
    ->  from employee right join department
    ->  on employee.d_id=department.d_id;
+------+------+------+------+------+--------+----------+
| num  | name | d_id | age  | sex  | d_name | function |
+------+------+------+------+------+--------+----------+
|    1 | 张三 | 1001 |   26 | 男   | 科研部 | 研发产品 |
|    2 | 李四 | 1001 |   24 | 女   | 科研部 | 研发产品 |
|    3 | 王五 | 1002 |   25 | 男   | 生产部 | 生产产品 |
| NULL | NULL | NULL | NULL | NULL | 销售部 | 策划销售 |
+------+------+------+------+------+--------+----------+
4 rows in set (0.00 sec)
4、复合条件连接查询
    在连接查询时。可以增加其他的限制条件。通过多个条件的复合查询,可以使查询结果更加准确。
例子:
内连接查询的方式查询employee表和department表,并且employee表中的age字段值必须大于24,select语句如下:
select num,name,employee.d_id,age,sex,d_name,function
from employee,department
where employee.d_id=department.d_id
and age>24;
内连接查询的方式查询employee表和department表。并且以age字段升序的方式显示查询结果。select语句如下:
select num,name,employee.d_id,age,sex,d_name,function
from employee,department
where employee.d_id=department.d_id
order by age asc;
五、子查询
   子查询是将一个查询语句嵌套到另一个查询语句中。内层查询语句的查询结果,可以为外层查询语句提供查询条件。在特定情况下,一个查询语句需要另一个查询语句来获取。
1、带in关键字的子查询:
 一个查询语句的条件可能落在另一个select语句的查询结果中,这可以通过in关键字来判断。
查询employee表中的记录,这些记录的d_id字段的值必须在department表中出现过:
select * from employee
where d_id in
(select d_id from department);
查询employee表中的记录,这些记录的d_id字段的值必须没有在department表中出现过;
select * from employee
where d_id not in
(select d_id from department);
2、带比较运算符的子查询:
 这些运算符包括=、!=、>、>=、<、<=、<>等,其中<>与!=是等价的。
从computer_stu表中查询获得一等奖学金的学生学号、姓名和分数,各个等级奖学金的最低分存储在scholarship表中。
select id,name,score from computer_stu
where score>=
(select score from scholarship
where level=1);
在department表中查询哪些部门没有年龄为24岁的员工
select d_id,d_name from department
where d_id!=
(select d_id from employee
where age=24);
用<>是一样的结果:
select d_id,d_name from department
where d_id<>
(select d_id from employee
where age=24);
3、带exists关键字的子查询:
    exists关键字表示存在。使用exists关键字时,内层查询语句不返回查询的记录。而是返回一个真假值。如果内层查询语句查询到满足条件的记录,就返回一个真值(true)。否则,将返回一个假植(false)。当返回的值是真值时,外层查询语句将进行查询。当返回假值时,外层查询语句不进行查询或者查询不出任何记录。
如果department表中存在d_id取值为1003的记录,则查询employee表:
select * from employee
where exists
(select d_name from department
where d_id=1003);
如果department表中存在d_id取值为1003的记录,则查询employee表中age>24的记录:
select * from employee
where age>24 and exists
(select d_name from department
where d_id=1003);
如果department表中不存在d_id取值为1003的记录,则查询employee表:
select * from employee
where not exists
(select d_name from department
where d_id=1003);
4、带any关键字的子查询:
?    any关键字表示满足其中任一条件。使用any关键字时,只要满足内层查询语句返回的结果的任何一个,就可以通过该条件来执行外层查询语句。通常与比较运算符一起使用。
从computer_stu表中查询出哪些同学可以获得奖学金。奖学金的信息存储在scholarship表中:
select * from computer_stu
where score>=any
(select score from scholarship);
5、带all关键字的子查询:
    使用all关键字时,只有满足内层查询语句返回的所有结果,才可以执行外层查询语句。
从computer表中查询出哪些同学可以获得一等奖学金(先从scholarship中查询出各种奖学金的最低分,然后从computer_stu表中查询哪些人的分数高于所有奖学金的最低分):
select * from computer_stu
where score>=all
(select score from sholarship);
6、合并查询结果:
    将多个select语句的查询结果合并到一起,因为某种情况下,需要将几个select语句查询出来的结果合并起来显示。
进行合并操作使用union和union all关键字使用union关节囊字时,数据库系统会将所有查询结果合并到一起,然后去除掉相同的记录。而union all关键字则只是简单的合并到一起。
语法规则:
select 语句1
union|union all
select 语句2
union|union all…
select 语句n
从department和employee 表中查询d_id字段的取值,然后通过union关键字将结果合并到一起:
select d_id from department
union
select d_id from employee;
7、为表和字段取别名:
(1)当表的名称特别长时。在查询中直接使用表名很不方便,这时可以为表取一个别名。
为department表取别名d,然后查询表中d_id字段取值为1001的记录。
select * from department d
where d.d_id=1001;
(2)为字段取别名
基本语法 属性名 [as] 别名
例子:
为department表中的d_id字段和d_name字段分别取名为department_id和department_name
 select d_id as department_id,d_name as department_name
 from department;
为表和字段取别名同时使用:
为department表中的d_id字段和d_name字段分别取名为department_id和department_name,为department表取别名d,然后查询department表中d_id字段取值为1001的记录:
select d.d_id as department_id,d.d_name as department_name,d.function,d.address
from department d
where d.d_id=1001;
8、使用正则表达式查询
    正则表达式是用某种模式去匹配一类字符串的一个方式。例如:使用正则表达式可以查询出包含A、B、C其中任一字母的字符串。正则表达式的查询能力比通配字符的查询能力更强大,而且更加的灵活。正则表达式可以应用于非常复杂的查询。
基本形式:
属性名 regexp ‘匹配方式'
例子代码:
 create table info(
 id int,
 name varchar(20)
 );
insert into info values(1,'Aric');
insert into info values(2,'Eric');
insert into info values(4,'Jack');
insert into info values(5,'Lucy');
insert into info values(6,'Lily');
insert into info values(7,'Tom');
insert into info values(8,'aaa');
insert into info values(9,'dadaaa');
insert into info values(10,'aaabd');
insert into info values(11,'abc12');
insert into info values(12,'ad321');
(1)查询以特定字符或字符串开头的记录
使用字符“^”可以匹配特定字符或字符串开头的记录
从info表name字段查询以字母“L”开头的记录:
select* from info where name regexp '^L';
(2)查询以特定字符或字符串结尾的记录
使用字符“$”可以匹配以特定字符或字符串结尾的记录
从info表name字段中查询以字母“c”结尾的记录
select* from info where name regexp 'c$';
(3)用符号“.”来替代字符串中的任意一个字符
从info表name字段中查询以字母“”开头,以字母“y”结尾,中间有两个任意字符的记录
select* from info where name regexp '^L..y$';
(4)匹配指定字符中的任意一个
使用([])可以将需要查询的字符组成一个字符集。只要记录中包含方括号中的任意字符,该记录将会被查询出来。
从info表name字段中查询包含“c”、“e”、“o”这三个字母中任意一个的记录:
select * from info where name regexp '[ceo]';
也可以是数字:
select * from info where name regexp '[0-9]';
从info表name字段中查询包含数字或者字母a、b、c的记录:
select * from info where name regexp '[0-9a-c]';
(5)匹配指定字符以外的字符
从info表name字段中查询包含a到w和数字以外的字符的记录:
select * from info where name regexp '[^a-w0-9]';
(6)匹配指定字符串
从info表name字段查询包含“ic”的记录:
select * from info where name regexp 'ic';
从info表name字段查询包含“ic”“uc”“ab”这三个字符串任意一个的记录:
select * from info where name regexp 'ic|uc|ab';
(7)使用“*”“+”来匹配多个字符
“*”“+”都可以来匹配多个该符号之前的字符,但是“+”至少表示一个字符,而“:*”可以表示零个字符。
从info表name字段查询字母“c”之前出现过a的记录:
select * from info where name regexp 'a*c';
例子:
mysql> select * from info where name regexp 'a*c';
+------+-------+
| id   | name  |
+------+-------+
|    1 | Aric  |
|    2 | Eric  |
|    4 | Jack  |
|    5 | Lucy  |
|   11 | abc12 |
+------+-------+
5 rows in set (0.00 sec)
mysql> select * from info where name regexp 'a+c';
+------+------+
| id   | name |
+------+------+
|    4 | Jack |
+------+------+
1 row in set (0.00 sec)
(8)使用{M}或者{M,N}来指定字符串连续出现的次数
从info表name字段中查询出现过“a”三次的记录:
select * from info where name regexp 'a{3}';
从info表name字段中查询出现过“ab”最少一次,最多三次的记录:
select * from info where name regexp 'ab{1,3}';

本页地址:http://www.easytd.com/shujuku/d_15031121253.html

上一篇:Oracle中REGEXP_SUBSTR函数通过正则获取子字符串 下一篇:Oracle创建大文件表空间