MySQL数据库_data
目录
1)关键字优先级:from > where > group by > select > distinct > having > order by > limit
13)MySQL子查询(in|not in、exists|not exists和比较运算符)
1.查询数据:
语法格式:
MariaDB [(none)]> select {*|<字段列名>}
-> from <表名1>,<表名2>,...
-> [
-> where <表达式>
-> [group by <group by definition>
-> [having <expression> [{<operator> <expression>}…]]
-> [order by <order by definition>]
-> [limit[<offset>,] <row count>]
-> ];
-
{*|<字段名>}包含星号通配符的字段列表,表示所要查询字段的名称。使用通配符“*”查询时,只能按照数据表中字段的顺序进行排列,不能改变字段的排列顺序。而使用自定义字段名顺序,这可以改变字段的排列顺序。
-
<表名1>,<表名2>…,表名1和表名2表示查询数据的来源,可以是单个或多个。
-
WHERE <表达式>是可选项,如果选择该项,将限定查询数据必须满足该查询条件。
-
GROUP BY<字段>,该子句告诉MySQL如何显示查询出来的数据,并按照指定的字段分组。
-
[ORDER BY<字段>],该子句告诉MySQL按什么样的顺序显示查询出来的数据,可以进行的排序有升序(ASC)和降序(DESC),默认情况下是升序。
-
[LIMIT[<offset>,]<row count>],该子句告诉MySQL每次显示查询出来的数据条数。
(1)单表查询:
1)关键字优先级:from > where > group by > select > distinct > having > order by > limit
-
from:找到表
-
where:拿到约束条件,去表中取记录
-
group by:取出记录进行分组,若没有group by关键字,则整体作为一组
-
having:将分组结果进行过滤
-
select:查找
-
distinct:去重
-
order by:将结果按条件进行排序
-
limit:限制结果的显示条数
① 查询表中所有字段:
#设为对照组
MariaDB [db_test]> select*from tab_courses;
+-----------+-------------+--------------+------------------+
| course_id | course_name | course_grade | course_info |
+-----------+-------------+--------------+------------------+
| 1 | Network | 3 | Computer Network |
| 2 | Database | 3 | MySQL |
| 3 | Java | 4 | Java EE |
| 4 | System | 3 | Operation System |
+-----------+-------------+--------------+------------------+
4 rows in set (0.00 sec)
MariaDB [db_test]> select course_id,course_name,course_grade,course_info from tab_courses;
+-----------+-------------+--------------+------------------+
| course_id | course_name | course_grade | course_info |
+-----------+-------------+--------------+------------------+
| 1 | Nrtwork | 3 | Computer Network |
| 2 | Database | 3 | MySQL |
| 3 | Java | 4 | Java EE |
| 4 | System | 3 | Operation System |
+-----------+-------------+--------------+------------------+
4 rows in set (0.00 sec)
② 查询表中指定的字段:
#对照组不变
#查询tab_courses表中学分为4的那条数据
MariaDB [db_test]> select course_name,course_grade from tab_courses
-> where course_grade=4;
+-------------+--------------+
| course_name | course_grade |
+-------------+--------------+
| Java | 4 |
+-------------+--------------+
1 row in set (0.06 sec)
#查询tab_courses表中学分为4的那条数据
MariaDB [db_test]> select course_name,course_grade from tab_courses
-> where course_grade=5;
Empty set (0.00 sec) #如果根据指定的条件进行查询时,数据表中没有符合查询条件的记录,系统会提示“Empty set(0.00sec)”
② 多条件的查询语句:
-
AND:记录满足所有查询条件时,才会被查询出来
-
OR:记录满足任意一个查询条件时,才会被查询出来
-
XOR:记录满足其中一个条件,并且不满足另一个条件时,才会被查询出来
#对照组不变
#查询tab_courses表中课程名为Database且课程学分为3的那条数据
MariaDB [db_test]> select course_name,course_grade from tab_courses
-> where course_name='Database' and course_grade=3;
+-------------+--------------+
| course_name | course_grade |
+-------------+--------------+
| Database | 3 |
+-------------+--------------+
1 row in set (0.04 sec)
#查询tab_courses表中课程名为Database或者课程学分为3的那条数据
MariaDB [db_test]> select course_name,course_grade from tab_courses
-> where course_name='Database' or course_grade=3;
+-------------+--------------+
| course_name | course_grade |
+-------------+--------------+
| Network | 3 |
| Database | 3 |
| System | 3 |
+-------------+--------------+
3 rows in set (0.01 sec)
#查询tab_courses表中课程名为Database或者课程学分为4的所有数据
MariaDB [db_test]> select course_name,course_grade from tab_courses
-> where course_name='Database' xor course_grade=4;
+-------------+--------------+
| course_name | course_grade |
+-------------+--------------+
| Database | 3 |
| Java | 4 |
+-------------+--------------+
2 rows in set (0.00 sec)
① GROUP BY关键字单独使用:
单独使用GROUP BY关键字时,查询结果会只显示每个分组的第一条记录
#对照组不变
#对课程名进行分组查询
MariaDB [db_test]> select course_name,course_grade from tab_courses
-> group by course_name;
+-------------+--------------+
| course_name | course_grade |
+-------------+--------------+
| Database | 3 |
| Java | 4 |
| Network | 3 |
| System | 3 |
+-------------+--------------+
4 rows in set (0.04 sec)
#对课程学分进行分组查询
MariaDB [db_test]> select course_name,course_grade from tab_courses
-> group by course_grade;
+-------------+--------------+
| course_name | course_grade |
+-------------+--------------+
| Network | 3 |
| Java | 4 |
+-------------+--------------+
2 rows in set (0.01 sec)
② GROUP BY关键字与GROUP_CONCAT()函数一起使用:
GROUP_CONCAT()函数会把每个分组的字段值都显示出来
#对照组如上
#对课程学分进行分组,用group_concat()函数将每个分组字段的所有值都显示出来
MariaDB [db_test]> select course_grade,group_concat(course_name) from tab_courses
-> group by course_grade;
+--------------+---------------------------+
| course_grade | group_concat(course_name) |
+--------------+---------------------------+
| 3 | Network,Database,System |
| 4 | Java |
+--------------+---------------------------+
2 rows in set (0.00 sec)
③ GROUP BY关键字与聚合函数一起使用:
聚合函数包括COUNT(),SUM(),AVG(),MAX() 和 MIN()。其中,COUNT()用来统计记录的条数;SUM()用来计算字段值的总和;AVG()用来计算字段值的平均值;MAX()用来查询字段的最大值;MIN()用来查询字段的最小值。
#对照组不变
#对课程学分进行分组,用count函数统计学分为3和4的课程名分别有多少
MariaDB [db_test]> select course_grade,count(course_name) from tab_courses
-> group by course_grade;
+--------------+--------------------+
| course_grade | count(course_name) |
+--------------+--------------------+
| 3 | 3 |
| 4 | 1 |
+--------------+--------------------+
2 rows in set (0.01 sec)
-
DISTINCT关键字只能在SELECT语句中使用,在对一个或多个字段去重时,DISTINCT关键字必须在所有字段的最前面
-
如果DISTINCT关键字后有多个字段,则会对多个字段进行组合去重,也就是说,只有多个字段组合起来完全是一样的情况下才会被去重
#对照组不变
#对课程学分字段进行去重
MariaDB [db_test]> select distinct course_grade from tab_courses;
+--------------+
| course_grade |
+--------------+
| 3 |
| 4 |
+--------------+
2 rows in set (0.01 sec)
##对课程名和课程学分字段进行去重
MariaDB [db_test]> select distinct course_name,course_grade from tab_courses;
+-------------+--------------+
| course_name | course_grade |
+-------------+--------------+
| Network | 3 |
| Database | 3 |
| Java | 4 |
| System | 3 |
+-------------+--------------+
4 rows in set (0.00 sec)
#对所有字段去重
MariaDB [db_test]> select distinct*from tab_courses;
+-----------+-------------+--------------+------------------+
| course_id | course_name | course_grade | course_info |
+-----------+-------------+--------------+------------------+
| 1 | Network | 3 | Computer Network |
| 2 | Database | 3 | MySQL |
| 3 | Java | 4 | Java EE |
| 4 | System | 3 | Operation System |
+-----------+-------------+--------------+------------------+
4 rows in set (0.00 sec)
where和having过滤的区别:
-
一般情况下,where用于过滤数据行,而having用于过滤分组。
-
where查询条件中不可以使用聚合函数,而having查询条件中可以使用聚合函数。
-
where在数据分组前进行过滤,而having在数据分组后进行过滤 。
-
where针对数据库文件进行过滤,而having针对查询结果进行过滤。也就是说,where根据数据表中的字段直接进行过滤,而having是根据前面已经查询出的字段进行过滤。
-
where查询条件中不可以使用字段别名,而having查询条件中可以使用字段别名。
#对照组不变
MariaDB [db_test]> select course_name,course_grade from tab_courses
-> having course_grade=3;
+-------------+--------------+
| course_name | course_grade |
+-------------+--------------+
| Network | 3 |
| Database | 3 |
| System | 3 |
+-------------+--------------+
3 rows in set (0.01 sec)
#使用having过滤时,如果前面没有事先选定having后紧跟的字段会报错
MariaDB [db_test]> select course_name from tab_courses
-> having course_grade=3;
ERROR 1054 (42S22): Unknown column 'course_grade' in 'having clause'
#使用where过滤时,如果前面没有事先选定where后面紧跟的字段不会报错
MariaDB [db_test]> select course_name from tab_courses
-> where course_grade=3;
+-------------+
| course_name |
+-------------+
| Network |
| Database |
| System |
+-------------+
3 rows in set (0.00 sec)
-
ORDER BY关键字主要用来将查询结果中的数据按照一定的顺序进行排序,后面可接子查询
-
当排序的字段中存在空值时,ORDER BY会将该空值作为最小值来对待
-
ORDER BY指定多个字段进行排序时,MySQL会按照字段的顺序从左到右依次进行排序
-
默认情况下,查询数据按字母升序进行排序(A~Z),但数据的排序并不仅限于此,还可以使用ORDER BY中的DESC对查询结果进行降序排序(Z~A)
① 单字段排序:
#对照组不变
#按照课程学分进行排序,默认为升序
MariaDB [db_test]> select*from tab_courses
-> order by course_grade;
+-----------+-------------+--------------+------------------+
| course_id | course_name | course_grade | course_info |
+-----------+-------------+--------------+------------------+
| 1 | Network | 3 | Computer Network |
| 2 | Database | 3 | MySQL |
| 4 | System | 3 | Operation System |
| 3 | Java | 4 | Java EE |
+-----------+-------------+--------------+------------------+
4 rows in set (0.00 sec)
② 多字段排序:
#对照组不变
#按课程学分和课程名共同排序,默认为升序
MariaDB [db_test]> select*from tab_courses
-> order by course_grade,course_name;
+-----------+-------------+--------------+------------------+
| course_id | course_name | course_grade | course_info |
+-----------+-------------+--------------+------------------+
| 2 | Database | 3 | MySQL |
| 1 | Network | 3 | Computer Network |
| 4 | System | 3 | Operation System |
| 3 | Java | 4 | Java EE |
+-----------+-------------+--------------+------------------+
4 rows in set (0.00 sec)
#按课程学分为降序,而课程名为生序进行排序
MariaDB [db_test]> select*from tab_courses
-> order by course_grade desc,course_name asc; #DESC为降续,ASC为升序
+-----------+-------------+--------------+------------------+
| course_id | course_name | course_grade | course_info |
+-----------+-------------+--------------+------------------+
| 3 | Java | 4 | Java EE |
| 2 | Database | 3 | MySQL |
| 1 | Network | 3 | Computer Network |
| 4 | System | 3 | Operation System |
+-----------+-------------+--------------+------------------+
4 rows in set (0.01 sec)
-
LIMIT是MySQL中的一个特殊关键字,用于指定查询结果从哪条记录开始显示,一共显示多少条记录
-
LIMIT关键字有3种使用方式,即指定初始位置、不指定初始位置以及与OFFSET组合使用
-
LIMIT后的两个参数必须都是正整数
语法格式:LIMIT 初始位置,记录数
-
其中,初始位置表示从哪条记录开始显示;记录数表示显示记录的条数。第一条记录的位置是0,第二条记录的位置是1。后面的记录依次类推。
-
当LIMIT关键字不指定初始位置时,记录从第一条记录开始显示。显示记录的条数由LIMIT关键字指定。
① 指定初始位置:
#对照组不变
#查询tab_courses表中从第2条到第4条的数据
MariaDB [db_test]> select*from tab_courses
-> limit 1,3;
+-----------+-------------+--------------+------------------+
| course_id | course_name | course_grade | course_info |
+-----------+-------------+--------------+------------------+
| 2 | Database | 3 | MySQL |
| 3 | Java | 4 | Java EE |
| 4 | System | 3 | Operation System |
+-----------+-------------+--------------+------------------+
3 rows in set (0.00 sec)
② 不指定初始位置:
#对照组不变
#查询tab_courses表中前3条数据
MariaDB [db_test]> select*from tab_courses
-> limit 3;
+-----------+-------------+--------------+------------------+
| course_id | course_name | course_grade | course_info |
+-----------+-------------+--------------+------------------+
| 1 | Network | 3 | Computer Network |
| 2 | Database | 3 | MySQL |
| 3 | Java | 4 | Java EE |
+-----------+-------------+--------------+------------------+
3 rows in set (0.01 sec)
③ LIMIT和OFFSET组合使用:
#对照组不变
#查询tab_courses表中从第2条后面的2条数据
MariaDB [db_test]> select*from tab_courses
-> limit 2 offset 2;
+-----------+-------------+--------------+------------------+
| course_id | course_name | course_grade | course_info |
+-----------+-------------+--------------+------------------+
| 3 | Java | 4 | Java EE |
| 4 | System | 3 | Operation System |
+-----------+-------------+--------------+------------------+
2 rows in set (0.02 sec)
-
当表名很长或者执行一些特殊查询的时候,为了方便操作,可以为表指定一个别名,用这个别名代替表原来的名称
-
表的别名不能与该数据库的其它表同名,字段的别名不能与该表的其它字段同名
① 为表指定别名:
#对照组不变
#将表名tab_courses设定别名为tcs
MariaDB [db_test]> select*from tab_courses
-> as tcs;
+-----------+-------------+--------------+------------------+
| course_id | course_name | course_grade | course_info |
+-----------+-------------+--------------+------------------+
| 1 | Network | 3 | Computer Network |
| 2 | Database | 3 | MySQL |
| 3 | Java | 4 | Java EE |
| 4 | System | 3 | Operation System |
+-----------+-------------+--------------+------------------+
4 rows in set (0.01 sec)
② 为字段指定别名:
#对照组不变
#将字段名course_name和course_grade分别设为cname和cgrade
MariaDB [db_test]> select course_name as cname,course_grade as cgrade from tab_courses;
+----------+--------+
| cname | cgrade |
+----------+--------+
| Network | 3 |
| Database | 3 |
| Java | 4 |
| System | 3 |
+----------+--------+
4 rows in set (0.01 sec)
-
“%”是MySQL中最常用的通配符,它能代表任何长度的字符串,字符串的长度可以为0
-
“_”只能代表单个字符,字符的长度不能为0
-
默认情况下,LIKE关键字匹配字符的时候是不区分大小写的。如果需要区分大小写,可以加入BINARY关键字
-
匹配的字符串必须加单引号或双引号
-
注意事项:①注意大小写;②注意尾部不能有空格;③注意NULL为空值,%匹配不到NULL值。
① 带有“%”通配符的查询:
#对照组不变
#查询课程名中含有字母a的所有课程
MariaDB [db_test]> select course_name from tab_courses
-> where course_name like '%a%';
+-------------+
| course_name |
+-------------+
| Database |
| Java |
+-------------+
2 rows in set (0.01 sec)
② 带有“_”通配符的查询:
#对照组不变
#查询课程名中第四位为字母a的课程
MariaDB [db_test]> select course_name from tab_courses
-> where course_name like '___a';
+-------------+
| course_name |
+-------------+
| Java |
+-------------+
1 row in set (0.00 sec)
③ LIKE区分大小写:
-
BETWEEN AND需要两个参数,即范围的起始值和终止值。如果字段值在指定的范围内,则这些记录被返回。如果不在指定范围内,则不会被返回
语法格式:[NOT] BETWEEN 取值1 AND 取值2
-
NOT:可选参数,表示指定范围之外的值。如果字段值不满足指定范围内的值,则这些记录被返回
-
取值1:表示范围的起始值
-
取值2:表示范围的终止值
#对照组不变
#查询课程学分在1-3之间的数据
MariaDB [db_test]> select course_grade from tab_courses
-> where course_grade between 1 and 3;
+--------------+
| course_grade |
+--------------+
| 3 |
| 3 |
| 3 |
+--------------+
3 rows in set (0.00 sec)
##查询课程学分不在1-3之间的数据
MariaDB [db_test]> select course_grade from tab_courses
-> where course_grade not between 1 and 3;
+--------------+
| course_grade |
+--------------+
| 4 |
+--------------+
1 row in set (0.00 sec)
-
如果字段的值是空值,则满足查询条件,该记录将被查询出来。如果字段的值不是空值,则不满足查询条件
-
通过子查询可以实现多表查询
-
子查询指将一个查询语句嵌套在另一个查询语句中
-
子查询可以在SELECT、UPDATE和DELETE语句中使用,而且可以进行多层嵌套
-
在实际开发时,子查询经常出现在WHERE子句中
语法格式:WHERE <表达式> <操作符> (子查询)
-
IN | NOT IN:当表达式与子查询返回的结果集中的某个值相等时,返回TRUE,否则返回FALSE;若使用关键字NOT IN,则返回值正好相反
-
EXISTS | NOT EXISTS:用于判断子查询的结果集是否为空,若子查询的结果集不为空,返回TRUE,否则返回FALSE;若使用关键字NOT EXISTS,则返回的值正好相反
#查询tab_students表中的
MariaDB [db_test]> select*from tab_students;
+----+----------+-------------+--------------+
| id | name | course_name | course_grade |
+----+----------+-------------+--------------+
| 1 | zhangsan | Network | 3 |
| 2 | lisi | Database | 3 |
| 3 | xiaoming | Java | 4 |
| 3 | xiaohong | System | 3 |
+----+----------+-------------+--------------+
4 rows in set (0.00 sec)
MariaDB [db_test]> select course_name from tab_courses
-> where course_name='java';
+-------------+
| course_name |
+-------------+
| Java |
+-------------+
1 row in set (0.02 sec)
#使用子查询在tab_course表和tab_students表中查询学习Java课程的学生姓名
MariaDB [db_test]> select name from tab_students
-> where course_name in (select course_name from tab_courses where course_name='java');
+----------+
| name |
+----------+
| xiaoming |
+----------+
1 row in set (0.06 sec)
#查询tab_courses表里是否有Java课程,如果有则找出需要上Java课程的学生姓名
MariaDB [db_test]> select name from tab_students
-> where course_name='Java' and exists(select*from tab_courses where course_name='Java');
+----------+
| name |
+----------+
| xiaoming |
+----------+
1 row in set (0.01 sec)
-
正则表达式主要用来查询和替换符合某个模式(规则)的文本内容
语法格式:属性名 REGEXP '匹配方式'
-
属性名表示需要查询的字段名称
-
匹配方式表示以哪种方式来匹配查询
选项
|
说明
|
例子
|
匹配值示例
|
^
|
匹配文本的开始字符
|
'^b'匹配以字母b开头的字符串
|
book、big、banana、bike
|
$
|
匹配文本的结束字符
|
'st$'匹配以st结尾的字符串
|
test、resist、persist
|
.
|
匹配任何单个字符
|
'b.t'匹配任何b和t之间有一个字符
|
bit、bat、but、bite
|
*
|
匹配零个或多个在它前面的字符
|
'f*n'匹配字符n前面有任意个字符f
|
fn、fan、faan、abcn
|
+
|
匹配前面的字符1次或多次
|
'ba+'匹配以b开头,后面至少紧跟一个a
|
ba、bay、bare、battle
|
<字符串>
|
匹配包含指定字符的文本
|
'fa'匹配包含'fa'的文本
|
fan、afa、faad
|
[字符集合]
|
匹配字符集合中的任何一个字符
|
'[xz]'匹配x或者z
|
dizzy、zebra、x-ray、extra
|
[^]
|
匹配不在括号中的任何字符
|
'[^abc]'匹配任何不包含a、b 或c的字符串
|
desk、fox、f8ke
|
字符串{n,}
|
匹配前面的字符串至少n次
|
'b{2}'匹配2个或更多的b
|
bbb、bbbb、bbbbbbb
|
字符串
{n,m}
|
匹配前面的字符串至少n次, 至多m次
|
'b{2,4}'匹配最少2个,最多4个b
|
bbb、bbbb
|
1.查询以特定字符或字符串开头的记录:^
#查询course_name字段里以字母“j”开头的数据,不区分大小写
MariaDB [db_test]> select*from tab_courses where course_name regexp '^j';
+-----------+-------------+--------------+-------------+
| course_id | course_name | course_grade | course_info |
+-----------+-------------+--------------+-------------+
| 3 | Java | 4 | Java EE |
+-----------+-------------+--------------+-------------+
1 row in set (0.00 sec)
#查询course_name字段里以字母“j”开头的数据,区分大小写
MariaDB [db_test]> select*from tab_courses where course_name regexp binary '^j';
Empty set (0.00 sec)
2.查询以特定字符或字符串结尾的记录:$
#查询course_name字段里以字母“a”结尾的数据
MariaDB [db_test]> select*from tab_courses where course_name regexp 'a$';
+-----------+-------------+--------------+-------------+
| course_id | course_name | course_grade | course_info |
+-----------+-------------+--------------+-------------+
| 3 | Java | 4 | Java EE |
+-----------+-------------+--------------+-------------+
1 row in set (0.00 sec)
3.替代字符串中的任意一个字符:.
#查询course_name字段里字符串中第2位和第4位均为字母“a”的数据
MariaDB [db_test]> select*from tab_courses where course_name regexp '.a.a';
+-----------+-------------+--------------+-------------+
| course_id | course_name | course_grade | course_info |
+-----------+-------------+--------------+-------------+
| 2 | Database | 3 | MySQL |
| 3 | Java | 4 | Java EE |
+-----------+-------------+--------------+-------------+
2 rows in set (0.00 sec)
#一个.代表一个字符,多个点代表多个字符
MariaDB [db_test]> select*from tab_courses where course_name regexp '..a.a';
+-----------+-------------+--------------+-------------+
| course_id | course_name | course_grade | course_info |
+-----------+-------------+--------------+-------------+
| 2 | Database | 3 | MySQL |
+-----------+-------------+--------------+-------------+
1 row in set (0.01 sec)
4.匹配多个字符:+表示至少1个字符,而*可以表示0个字符
#查询name字段里包含字母“xiao”,且后面的“m”字母可以出现0次的数据
MariaDB [db_test]> select*from tab_students where name regexp '^xiaom*';
+----+----------+-------------+--------------+
| id | name | course_name | course_grade |
+----+----------+-------------+--------------+
| 4 | xiaohong | System | 3 |
| 3 | xiaoming | Java | 4 |
+----+----------+-------------+--------------+
2 rows in set (0.00 sec)
#查询name字段里包含字母“xiao”,且后面的“m”字母最少出现1次的数据
MariaDB [db_test]> select*from tab_students where name regexp '^xiaom+';
+----+----------+-------------+--------------+
| id | name | course_name | course_grade |
+----+----------+-------------+--------------+
| 3 | xiaoming | Java | 4 |
+----+----------+-------------+--------------+
1 row in set (0.00 sec)
5.匹配指定字符串:|可以指定多个字符串,且只需匹配任意一个即可
#查询name字段里字符串为“xiaoming”的数据
MariaDB [db_test]> select*from tab_students where name regexp 'xiaoming';
+----+----------+-------------+--------------+
| id | name | course_name | course_grade |
+----+----------+-------------+--------------+
| 3 | xiaoming | Java | 4 |
+----+----------+-------------+--------------+
1 row in set (0.01 sec)
#查询name字段里字符串为“xiao”或者“ming”的数据
MariaDB [db_test]> select*from tab_students where name regexp 'xiao|ming';
+----+----------+-------------+--------------+
| id | name | course_name | course_grade |
+----+----------+-------------+--------------+
| 4 | xiaohong | System | 3 |
| 3 | xiaoming | Java | 4 |
+----+----------+-------------+--------------+
2 rows in set (0.00 sec)
6.匹配指定字符串中的任意一个:[]
#查询name字段里字符串中字母为“a”或者“o”的数据
MariaDB [db_test]> select*from tab_students where name regexp '[ao]';
+----+----------+-------------+--------------+
| id | name | course_name | course_grade |
+----+----------+-------------+--------------+
| 1 | zhangsan | Network | 3 |
| 4 | xiaohong | System | 3 |
| 3 | xiaoming | Java | 4 |
+----+----------+-------------+--------------+
3 rows in set (0.01 sec)
#方括号[]还可以指定集合的区间。例如“[a-z]”表示从a~z的所有字母;“[0-9]”表示从0~9的所有数字;“[a-z0-9]”表示包含所有的小写字母和数字;“[a-zA-Z]”表示匹配所有字符。
MariaDB [db_test]> select*from tab_students where name regexp '[a-zA-Z]';
+----+----------+-------------+--------------+
| id | name | course_name | course_grade |
+----+----------+-------------+--------------+
| 1 | zhangsan | Network | 3 |
| 2 | lisi | Database | 3 |
| 4 | xiaohong | System | 3 |
| 3 | xiaoming | Java | 4 |
+----+----------+-------------+--------------+
4 rows in set (0.00 sec)
7.匹配指定字符以外的字符:[^字符集合]
#查询name字段里除字符串中字母为“a~y”以外的数据,即查字母为“z”的数据
MariaDB [db_test]> select*from tab_students where name regexp '[^a-y]';
+----+----------+-------------+--------------+
| id | name | course_name | course_grade |
+----+----------+-------------+--------------+
| 1 | zhangsan | Network | 3 |
+----+----------+-------------+--------------+
1 row in set (0.00 sec)
8.使用{n,}或者{n,m}来指定字符串连续出现的次数:字符串{n,}表示字符串连续出现n次;字符串{n,m}表示字符串连续出现至少n次,最多m次。
#查询name字段里字符串中字母“a”最少连续出现1次的数据
MariaDB [db_test]> select*from tab_students where name regexp 'a{1,}';
+----+----------+-------------+--------------+
| id | name | course_name | course_grade |
+----+----------+-------------+--------------+
| 1 | zhangsan | Network | 3 |
| 4 | xiaohong | System | 3 |
| 3 | xiaoming | Java | 4 |
+----+----------+-------------+--------------+
3 rows in set (0.00 sec)
#查询name字段里字符串中字母“a”最少连续出现1次,最多出现3次的数据
MariaDB [db_test]> select*from tab_students where name regexp 'a{1,3}';
+----+----------+-------------+--------------+
| id | name | course_name | course_grade |
+----+----------+-------------+--------------+
| 1 | zhangsan | Network | 3 |
| 4 | xiaohong | System | 3 |
| 3 | xiaoming | Java | 4 |
+----+----------+-------------+--------------+
3 rows in set (0.01 sec)
(2)多表查询:
-
交叉连接(CROSS JOIN)一般用来返回连接表的笛卡尔积
-
在MySQL中,多表查询一般使用内连接和外连接,它们的效率要高于交叉连接
语法格式:select <字段名> from 表1 cross join 表2 [where 条件]
#查询tab_courses表的所有数据
MariaDB [db_test]> select*from tab_courses;
+-----------+-------------+--------------+------------------+
| course_id | course_name | course_grade | course_info |
+-----------+-------------+--------------+------------------+
| 1 | Network | 3 | Computer Network |
| 2 | Database | 3 | MySQL |
| 3 | Java | 4 | Java EE |
| 4 | System | 3 | Operation System |
+-----------+-------------+--------------+------------------+
4 rows in set (0.00 sec)
#查询tab_students表的所有数据
MariaDB [db_test]> select*from tab_students;
+----+----------+-------------+--------------+
| id | name | course_name | course_grade |
+----+----------+-------------+--------------+
| 1 | zhangsan | Network | 3 |
| 2 | lisi | Database | 3 |
| 4 | xiaohong | System | 3 |
| 3 | xiaoming | Java | 4 |
+----+----------+-------------+--------------+
4 rows in set (0.01 sec)
#使用CROSS JOIN查询出两张表中的笛卡尔积
MariaDB [db_test]> select*from tab_courses cross join tab_students;
+-----------+-------------+--------------+------------------+----+----------+-------------+--------------+
| course_id | course_name | course_grade | course_info | id | name | course_name | course_grade |
+-----------+-------------+--------------+------------------+----+----------+-------------+--------------+
| 1 | Network | 3 | Computer Network | 1 | zhangsan | Network | 3 |
| 2 | Database | 3 | MySQL | 1 | zhangsan | Network | 3 |
| 3 | Java | 4 | Java EE | 1 | zhangsan | Network | 3 |
| 4 | System | 3 | Operation System | 1 | zhangsan | Network | 3 |
| 1 | Network | 3 | Computer Network | 2 | lisi | Database | 3 |
| 2 | Database | 3 | MySQL | 2 | lisi | Database | 3 |
| 3 | Java | 4 | Java EE | 2 | lisi | Database | 3 |
| 4 | System | 3 | Operation System | 2 | lisi | Database | 3 |
| 1 | Network | 3 | Computer Network | 4 | xiaohong | System | 3 |
| 2 | Database | 3 | MySQL | 4 | xiaohong | System | 3 |
| 3 | Java | 4 | Java EE | 4 | xiaohong | System | 3 |
| 4 | System | 3 | Operation System | 4 | xiaohong | System | 3 |
| 1 | Network | 3 | Computer Network | 3 | xiaoming | Java | 4 |
| 2 | Database | 3 | MySQL | 3 | xiaoming | Java | 4 |
| 3 | Java | 4 | Java EE | 3 | xiaoming | Java | 4 |
| 4 | System | 3 | Operation System | 3 | xiaoming | Java | 4 |
+-----------+-------------+--------------+------------------+----+----------+-------------+--------------+
16 rows in set (0.00 sec)
-
内连接(INNER JOIN)主要通过设置连接条件的方式,来移除查询结果中某些数据行的交叉连接。简单来说,就是利用条件表达式来消除交叉连接的某些数据行
-
内连接使用INNER JOIN关键字连接两张表,并使用ON子句来设置连接条件。如果没有连接条件,INNER JOIN和CROSS JOIN在语法上是等同的,两者可以互换
语法格式:select <字段名> from 表1 inner join 表2 [on子句] [where条件]
-
字段名:需要查询的字段名称
-
<表1><表2>:需要内连接的表名
-
INNER JOIN:内连接中可以省略INNER关键字,只用关键字JOIN
-
ON 子句:用来设置内连接的连接条件
#在tab_students和tab_courses表之间,使用内连接查询学生姓名和相对应的课程名
MariaDB [db_test]> select tab_students.name,tab_courses.course_name from tab_students inner join tab_courses
-> on tab_students.id=tab_courses.course_id;
+----------+-------------+
| name | course_name |
+----------+-------------+
| zhangsan | Network |
| lisi | Database |
| xiaoming | Java |
| xiaohong | System |
+----------+-------------+
4 rows in set (0.01 sec)
-
外连接可以分为左外连接和右外连接
语法格式:select <字段名> from 表1 [left|right] [outer] join 表2 <on子句>;
-
字段名:需要查询的字段名称
-
<表1><表2>:需要左连接的表名
-
LEFT OUTER JOIN:左连接中可以省略OUTER关键字,只使用关键字LEFT JOIN
-
RIGHT OUTER JOIN:右连接中可以省略OUTER关键字,只使用关键字RIGHT JOIN
-
ON子句:用来设置左连接的连接条件,不能省略
#查询tab_courses表的所有数据
MariaDB [db_test]> select*from tab_courses;
+-----------+-------------+--------------+------------------+
| course_id | course_name | course_grade | course_info |
+-----------+-------------+--------------+------------------+
| 1 | Network | 3 | Computer Network |
| 2 | Database | 3 | MySQL |
| 3 | Java | 4 | Java EE |
| 4 | System | 3 | Operation System |
| 5 | PHP | 4 | Dynamic Web |
+-----------+-------------+--------------+------------------+
5 rows in set (0.00 sec)
#查询tab_students表的所有数据
MariaDB [db_test]> select*from tab_students;
+----+----------+-------------+--------------+
| id | name | course_name | course_grade |
+----+----------+-------------+--------------+
| 1 | zhangsan | Network | 3 |
| 2 | lisi | Database | 3 |
| 4 | xiaohong | System | 3 |
| 3 | xiaoming | Java | 4 |
| 5 | xiaolan | | 4 |
+----+----------+-------------+--------------+
5 rows in set (0.01 sec)
1.外连接之左连接查询:优先把左表的记录全部显示
MariaDB [db_test]> select tab_students.name,tab_courses.course_name from tab_students left outer join tab_courses on tab_students.id=tab_courses.course_id;
+----------+-------------+
| name | course_name |
+----------+-------------+
| zhangsan | Network |
| lisi | Database |
| xiaohong | System |
| xiaoming | Java |
| xaolan | NULL |
+----------+-------------+
5 rows in set (0.05 sec)
2.外连接之右连接查询:优先把右表的记录全部显示
MariaDB [db_test]> select tab_students.name,tab_courses.course_name from tab_students right outer join tab_courses on tab_students.id=tab_courses.course_id;
+----------+-------------+
| name | course_name |
+----------+-------------+
| zhangsan | Network |
| lisi | Database |
| xiaohong | System |
| xiaoming | Java |
| NULL | PHP |
+----------+-------------+
5 rows in set (0.03 sec)
内外连接的区别:外连接与内连接不同,内连接时,根据连接条件只保留两个表中有对应数据的记录;而外连接时,当一个表中记录在另一个表中没有对应记录时,会生成一条与null值对应的记录。
2.添加数据:
语法格式1:
MariaDB [(none)]> insert [into] <表名>
-> (列名1,...,列名n)
-> values(值1,...,'值');
语法格式2:
MariaDB [(none)]> insert [into] <表名>
-> set <列名1>=<值1>,
-> <列名2>=<值2>,
-> ...
-> <列名n>=<'值n'>;
语法格式3:
MariaDB [(none)]> insert [into] <新表名>
-> (列名1,...,列名n)
-> select 列名1,...,列名n
-> from <旧表名>;
-
使用insert…values语句可以向表中插入一行数据,也可以插入多行数据。
-
使用insert…set语句可以指定插入行中每列的值,也可以指定部分列的值。
-
使用insert…select语句可以向表中插入其他表的数据。
-
使用insert语句后面的列名称顺序可以不是数据表定义时的顺序,即插入数据时,不需要按照表定义的顺序插入,只要保证值的顺序与列字段的顺序相同就可以。
-
使用insert语句中没有指定插入列表,只有一个值列表。在这种情况下,值列表为每一个字段列指定插入的值,并且这些值的顺序必须和数据表中字段定义的顺序相同。
-
使用insert语句插入记录时,如果某些字段没有指定插入值,MySQL将插入该字段定义时的默认值。
MariaDB [db_test]> create table tab_courses #创建tab_courses数据表
-> (
-> course_id int not null auto_increment,
-> course_name char(40) not null,
-> course_grade float not null,
-> course_info char(100) null,
-> primary key(course_id)
-> );
Query OK, 0 rows affected (0.00 sec)
MariaDB [db_test]> select*from tab_courses; #查询tab_courses数据表里的数据
Empty set (0.00 sec)
(1)向表中的全部字段添加值:
例1:
MariaDB [db_test]> insert into tab_courses #向tab_courses数据表添加第一行数据
-> (course_id,course_name,course_grade,course_info)
-> values(1,'Network',3,'Computer Network');
Query OK, 1 row affected (0.00 sec)
MariaDB [db_test]> select*from tab_courses; #再次查询tab_courses数据表里的数据
+-----------+-------------+--------------+------------------+
| course_id | course_name | course_grade | course_info |
+-----------+-------------+--------------+------------------+
| 1 | Network | 3 | Computer Network |
+-----------+-------------+--------------+------------------+
1 row in set (0.01 sec)
例2:
MariaDB [db_test]> insert into tab_courses #向tab_courses数据表添加第二行数据
-> (course_name,course_info,course_id,course_grade)
-> values('Database','MySQL',2,3); #指定列名顺序的时候,值必须和前一行列名定义的顺序保持一致
Query OK, 1 row affected (0.00 sec)
MariaDB [db_test]> select*from tab_courses;
+-----------+-------------+--------------+------------------+
| course_id | course_name | course_grade | course_info |
+-----------+-------------+--------------+------------------+
| 1 | Network | 3 | Computer Network |
| 2 | Database | 3 | MySQL |
+-----------+-------------+--------------+------------------+
2 rows in set (0.00 sec)
例3:
MariaDB [db_test]> insert into tab_courses #向tab_courses数据表添加第三行数据
-> values(3,'Java',4,'Java EE'); #没有指定列名的时候,值必须和创建时列名定义的顺序保持一致
Query OK, 1 row affected (0.00 sec)
MariaDB [db_test]> select*from tab_courses;
+-----------+-------------+--------------+------------------+
| course_id | course_name | course_grade | course_info |
+-----------+-------------+--------------+------------------+
| 1 | Network | 3 | Computer Network |
| 2 | Database | 3 | MySQL |
| 3 | Java | 4 | Java EE |
+-----------+-------------+--------------+------------------+
3 rows in set (0.00 sec)
(2)向表中指定字段添加值:
例4:
MariaDB [db_test]> insert into tab_courses #向tab_courses数据表添加第四行数据
-> (course_name,course_grade,course_info) #指定部分列名
-> values('System',3,'Operation System');
Query OK, 1 row affected (0.00 sec)
MariaDB [db_test]> select*from tab_courses; #course_id字段自动添加了一个整数值4。这时的course_id字段为表的主键,不能为空,系统自动为该字段插入自增的序列值。
+-----------+-------------+--------------+------------------+
| course_id | course_name | course_grade | course_info |
+-----------+-------------+--------------+------------------+
| 1 | Network | 3 | Computer Network |
| 2 | Database | 3 | MySQL |
| 3 | Java | 4 | Java EE |
| 4 | System | 3 | Operation System |
+-----------+-------------+--------------+------------------+
4 rows in set (0.00 sec)
(3)使用inert into…from语句复制表数据:
MariaDB [db_test]> create table tab_courses_new #创建tab_courses_new数据表
-> (
-> course_id int not null auto_increment,
-> course_name char(40) not null,
-> course_grade float not null,
-> course_info char(100) null,
-> primary key(course_id)
-> );
Query OK, 0 rows affected (0.03 sec)
MariaDB [db_test]> select*from tab_courses_new; #查询tab_courses数据表里的数据
Empty set (0.00 sec)
例5:
MariaDB [db_test]> insert into tab_courses_new
-> (course_id,course_name,course_grade,course_info)
-> select course_id,course_name,course_grade,course_info
-> from tab_courses;
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
MariaDB [db_test]> select*from tab_courses_new;
+-----------+-------------+--------------+------------------+
| course_id | course_name | course_grade | course_info |
+-----------+-------------+--------------+------------------+
| 1 | Network | 3 | Computer Network |
| 2 | Database | 3 | MySQL |
| 3 | Java | 4 | Java EE |
| 4 | System | 3 | Operation System |
+-----------+-------------+--------------+------------------+
4 rows in set (0.00 sec)
例6:
MariaDB [db_test]> insert into tab_courses_new
-> set course_id=5,
-> course_name='Python',
-> course_grade=4,
-> course_info='pachong';
Query OK, 1 row affected (0.01 sec)
MariaDB [db_test]> select*from tab_courses_new;
+-----------+-------------+--------------+------------------+
| course_id | course_name | course_grade | course_info |
+-----------+-------------+--------------+------------------+
| 1 | Network | 3 | Computer Network |
| 2 | Database | 3 | MySQL |
| 3 | Java | 4 | Java EE |
| 4 | System | 3 | Operation System |
| 5 | Python | 4 | pachong |
+-----------+-------------+--------------+------------------+
5 rows in set (0.00 sec)
3.修改数据:
语法格式:
MariaDB [(none)]> update <表名>
-> set 字段1=值1[,...,字段n=值n]
-> [where 子句]
-> [order by 子句]
-> [limit 子句];
-
<表名>:用于指定要更新的表名称。
-
SET子句:用于指定表中要修改的列名及其列值。其中,每个指定的列值可以是表达式,也可以是该列对应的默认值。如果指定的是默认值,可用关键字DEFAULT表示列值。
-
WHERE子句:可选项。用于限定表中要修改的行。若不指定,则修改表中所有的行。
-
ORDER BY子句:可选项。用于限定表中的行被修改的次序。
-
LIMIT子句:可选项。用于限定被修改的行数。
(1)修改表中的数据:
MariaDB [db_test]> update tab_courses_new
-> set course_grade=4; #更新所有course_grade列的值
Query OK, 3 rows affected (0.11 sec)
Rows matched: 4 Changed: 3 Warnings: 0
MariaDB [db_test]> select*from tab_courses_new;
+-----------+-------------+--------------+------------------+
| course_id | course_name | course_grade | course_info |
+-----------+-------------+--------------+------------------+
| 1 | Network | 4 | Computer Network |
| 2 | Database | 4 | MySQL |
| 3 | Java | 4 | Java EE |
| 4 | System | 4 | Operating System |
+-----------+-------------+--------------+------------------+
4 rows in set (0.00 sec)
(2)根据条件修改表中的数据:
MariaDB [db_test]> select*from tab_courses_new;
+-----------+-------------+--------------+------------------+
| course_id | course_name | course_grade | course_info |
+-----------+-------------+--------------+------------------+
| 1 | Nrtwork | 3 | Computer Network |
| 2 | Database | 3 | MySQL |
| 3 | Java | 4 | Java EE |
| 4 | System | 3 | Operation System |
| 5 | Python | 4 | pachong |
+-----------+-------------+--------------+------------------+
5 rows in set (0.00 sec)
MariaDB [db_test]> update tab_courses_new
-> set course_name='Network'
-> where course_id=1; #指定更改某行某列所在的数据
Query OK, 1 row affected (0.05 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [db_test]> select*from tab_courses_new;
+-----------+-------------+--------------+------------------+
| course_id | course_name | course_grade | course_info |
+-----------+-------------+--------------+------------------+
| 1 | Network | 3 | Computer Network |
| 2 | Database | 3 | MySQL |
| 3 | Java | 4 | Java EE |
| 4 | System | 3 | Operation System |
| 5 | Python | 4 | pachong |
+-----------+-------------+--------------+------------------+
5 rows in set (0.00 sec)
4.删除数据:
语法格式:
MariaDB [(none)]> delete from <表名>
-> [where 子句]
-> [order by 子句]
-> [limit 子句];
-
<表名>:指定要删除数据的表名。
-
ORDER BY子句:表示删除时,表中各行将按照子句中指定的顺序进行删除。
-
WHERE子句:表示为删除操作限定删除条件,若省略该子句,则代表删除该表中的所有行。
-
LIMIT子句:用于告知服务器在控制命令被返回到客户端前被删除行的最大值。
-
在不使用WHERE条件的时候,将删除所有数据。
(1)根据条件删除表中的数据:
MariaDB [db_test]> select*from tab_courses_new;
+-----------+-------------+--------------+------------------+
| course_id | course_name | course_grade | course_info |
+-----------+-------------+--------------+------------------+
| 1 | Network | 3 | Computer Network |
| 2 | Database | 3 | MySQL |
| 3 | Java | 4 | Java EE |
| 4 | System | 3 | Operation System |
| 5 | Python | 4 | pachong |
+-----------+-------------+--------------+------------------+
5 rows in set (0.00 sec)
MariaDB [db_test]> delete from tab_courses_new
-> where course_id=5; #指定删除第五行的数据
Query OK, 1 row affected (0.00 sec)
MariaDB [db_test]> select*from tab_courses_new;
+-----------+-------------+--------------+------------------+
| course_id | course_name | course_grade | course_info |
+-----------+-------------+--------------+------------------+
| 1 | Network | 3 | Computer Network |
| 2 | Database | 3 | MySQL |
| 3 | Java | 4 | Java EE |
| 4 | System | 3 | Operation System |
+-----------+-------------+--------------+------------------+
4 rows in set (0.00 sec)
(2)删除表中的全部数据:
MariaDB [db_test]> delete from tab_courses_new;
Query OK, 4 rows affected (0.00 sec)
MariaDB [db_test]> select*from tab_courses_new; #数据表内容已经被全部删除
Empty set (0.00 sec)