MySQL基本操作

一、MySQL函数

官方文档：https://dev.mysql.com/doc/refman/5.7/en/sql-function-reference.html

1、常用函数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- 数学运算
SELECT ABS(-8) -- 返回 8     ，   绝对值
SELECT CEILING(9.4) -- 返回 10   ，   向上取整
SELECT FLOOR(9.4) -- 返回 9   ，   向下取整
SELECT RAND() -- 返回 0～1之间的随机数
SELECT SIGN(10) -- 返回 1，判断一个数的符号， 0-0 ，负数 - -1 ，正数 - 1
SELECT AVG() -- 返回参数的平均值
-- 字符串函数
SELECT CHAR_LENGTH('我的长度是六')-- 字符串长度
SELECT CONCAT('你'，'好') -- 拼接字符串
SELECT LOWER('ABC') -- 变成小写字母
SELECT UPPER('abc') -- 变成大写字母
-- 时间和日期
SELECT CURRENT_DATE() -- 获取当前日期
SELECT NOW() -- 获取当前时间
SELECT LOCALTIME() -- 获取本地时间
SELECT SYSDATE() -- 系统时间

2、聚合函数

1
2
3
4
5
6
7
8
9
10
-- 查询一个表有多少记录
SELECT COUNT([列名]) FROM <表名> -- 统计表里面的列名，不会忽略null
SELECT COUNT(*) FROM <表名> -- 包含了所有的列，相当于行数，不会忽略null，计算行数
SELECT COUNT(1) FROM <表名> -- 忽略了所有的列，用1代表代码行，不会忽略null，计算行数

-- 计算
SUM() -- 总和
AVG() -- 平均值
MAX() -- 最大值
MIN() -- 最小值

二、DML操纵数据语言

Data Manipulation Language, DML数据操纵语言，用于数据库操作，对数据库其中的对象和数据运行访问工作的编程语句

  • insert
  • updat
  • delete

1、添加

1
2
3
4
-- 插入语句（添加）
-- insert into 表名（[字段1,字段2,字段3,字段4]）values ('值1','值'，'值'),('值2'，'值'，'值'),('值3'),('值4'),....
INSERT INTO `grade` (`gradename`) VALUES('大四')
-- 注意要一一对应

2、修改

1
2
3
4
5
6
7
-- 修改指定的信息
UPDATE `student` SET `name`='nie' WHERE id = 1;
-- 修改所有的信息
UPDATE `student` SET `name`='nie'
-- 修改多个属性，逗号隔开
UPDATE `student` SET `name`='nie',email='111@163.com' WHERE id = 1;
-- value可以是一个变量
操作符 含义
= 等于
<>或!= 不等于
> 大于
< 小于
<= 大于等于
>= 小于等于
between…and.. 闭合区间
and &&
or ||

3、删除

1
2
3
4
5
6
7
-- 删除所有数据
DELETE FROM `student`
-- 删除指定数据
DELETE FROM `student` WHERE id = 1;
-- 完全清空数据库的表，表的结构和索引约束不会变
TRUNCATE `student`

清空表的两种操作的区别：

  • 相同点：都能删除数据且不会删除表结构
  • 不同点：
    • TRUNCATE 重新设置自增列，计数器会归零
    • TRUNCATE 不会影响事务

三、DQL查询数据语言

1、DQL

Data QueryLanguage 数据查询语言

  • select基本语法

    1
    2
    3
    4
    5
    6
    7
    8
    SELECT [ALL|DISTINCT] <目标列表达式> [AS 列名]
    [,<目标列表达式> [AS 列名] ...] FROM <表名> [，<表名>…]
    [left | right | inner join table_name2]-- 联合查询
    [WHERE...]-- 指定结果需满足的条件
    [GROUP BY...]-- 指定结果按照哪几个字段来分组
    [HAVING...] -- 过滤分组的记录必须满足的次要条件
    [ORDER BY...]-- 指定查询记录按一个或者多个条件排序
    [LIMIT {[offset,]row_count | row_countOFFSET offset}];-- 指定查询的记录从哪条到哪条

2、指定查询字段

1
2
3
4
5
6
7
8
-- 查询全部的学生
SELECT * FROM student
-- 查询指定字段
SELECT `StudentNo`,`StudentName` FROM student
-- 别号，给结果起一个名字
SELECT `StudentNo` AS 学号,`StudentName` AS 姓名 FROM student 
-- 函数 concat(a,b)拼接字段
SELECT CONCAT（'姓名：',StudentName）AS 新名字 FROM student

3、去重distinct

  • 作用：去除一些重复的数据
1
2
3
-- 查询一下有哪些同学参加了考试，成绩(因为有的人参加了多门考试)
SELECT DISTINCT `StudentNo` FROM result

  • 使用数据库的列（表达式）
1
2
-- 给每个人的成绩加1
SELECT  `StudentResult`+1 AS '提分后的成绩' FROM result

4、where条件子句

检索数据中符合条件的值

运算符 语法 描述
and && a and b a&&b 逻辑与
or || a or b a||b 逻辑或
not ! not a ! a 逻辑非

尽量使用英文字母

1
2
3
4
5
6
-- 查询90-100的成绩(AND)
SELECT studentNo,`StudentResult` FROM result
WHERE StudentResult>=90 AND  StudentResult<=100
-- 查询90-100的成绩(&&)
SELECT studentNo,`StudentResult` FROM result
WHERE StudentResult>=90 &&  StudentResult<=100

5、模糊查询

运算符 语法 描述
IS NULL a is null 如果操作符为null，结果为真
IS NOT NULL a is not null 如果操作符不为null，结果为真
BETWEEN AND a between b and c 若a在b和c之间，结果为真
LIKE a like b SQL匹配，如果a匹配b，结果为真
IN a in(a1,a2,a3…) 假设a在a1，或者a2….其中的某一个值中，结果为真 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- 查询姓王的同学
-- like结合 %（代表0到任意个字符）_(代表一个字符)
SELECT studentNo,`StudentName` FROM `student`
WHERE StudentName LIKE '王%'

-- 查询姓王的同学，姓后面只有一个字的
SELECT studentNo,`StudentName` FROM `student`
WHERE StudentName LIKE '王_'

-- 查询名字中有王的人
SELECT studentNo,`StudentName` FROM `student`
WHERE StudentName LIKE '%王%'

-- 查询100，101，102号学生
SELECT studentNo,`StudentName` FROM `student`
WHERE StudentNo IN (100,101,102);

-- 查询在北京的学生
SELECT studentNo,`StudentName` FROM `student`
WHERE `Address` IN ('北京');

6、联表查询

Xnip2021-04-21_08-44-07

1
2
3
4
5
6
7
8
-- 查询参加了考试的同学的（学号，姓名，科目编号，分数）
SELECT * FROM student
SELECT * FROM result


SELECT s.studentNo,studentName,subjectNo,studentResult FROM student AS s
INNER JOIN result AS r
ON s.studentNo = r.studentNo
操作 描述
Inner join 如果表中至少有一个匹配，就返回匹配的值
left join 返回左表中的所有值，即使右表中没有匹配
right join 返回右表中的所有值，即使左表中没有匹配

7、自连接

自己的表和自己的表连，核心：一张表拆为两张一样的表

  • 例如下表category

    categoryid pid categoryName
    7 5 ps技术
    6 3 web开发
    2 1 信息技术
    3 2 办公技术
    4 3 数据库
    5 1 美术设计
    3 1 软件开发 
  • -- 查询父子信息
SELECT a.`categoryName` AS '父栏目', b.`categoryName` AS '子栏目'
FROM `category` AS a, `category` AS b
WHERE a.`categoryid` = b.`pid`

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23

    - 结果如下

      | 父栏目   | 子栏目   |
      | -------- | -------- |
      | 软件开发 | 数据库   |
      | 软件开发 | web开发  |
      | 美术设计 | ps技术   |
      | 信息技术 | 办公技术 |

      

    ### 8、分页和排序

    - 排序

    ```sql
    -- 排序：升序 ASC , 降序 DESC
    -- 把学生成绩按照降序排
    ORDER BY StudentResult DESC
    -- 把学生成绩按照升序排
    ORDER BY StudentResult ASC

  • 分页

1
2
3
4
5
6
7
8
-- 分页， 语法：limit 起始位置,数据行数
-- 每页只显示五条数据
LIMIT 0,5

-- 第一页 LIMIT 0,5      (1-1)*5
-- 第二页 LIMIT 5,5      (2-1)*5
-- 第三页 LIMIT 10,5      (3-1)*5
-- 第N页 LIMIT x,5      x=(n-1)*pageSize, [pageSize:页面大小]

9、嵌套查询

Where(计算的值)

在where语句里面嵌套一个子查询语句

  • 使用子查询进行基于集合的查询
1
2
3
4
5
6
-- 查询与张三同学在同一个系的同学
-- 第一步先查询张三同学所在的系
-- 然后把系作为已知条件查找同学信息
SELECT * FROM Student
WHERE Sdept IN(
SELECT Sdept FROM Student Where Sname ='张三');
  • 使用子查询进行比较查询
1
2
3
4
5
6
-- 查询选修了语文课程且课程成绩高于此课程平均成绩的的学生学号和该门课成绩
-- 先查出语文课程的平均成绩
-- 然后把平均成绩作为已知条件查找同学学号和成绩
SELECT Sno, Grade FROM SC
WHERECno='语文' AND Grade>(
SELECT AVG(Grade) FROM SC Where Cno ='语文')	;