SQL基础

sql 是分为好几种的，这种创建数据库、创建表等修改结构的 sql 叫做 <font style="color:rgb(37, 41, 51);">DDL</font>（Data Definition Language），而增删改那种叫做 <font style="color:rgb(37, 41, 51);">DML</font>（Data Manipulate Language），查询数据的叫做 <font style="color:rgb(37, 41, 51);">DQL</font>（Data Query Language）。

一些快捷键

  1. 执行整篇 sql 脚本:【Ctrl】+【Shift】+【Enter】
  2. 执行当前行:【Ctrl】+【Enter】

创表

可使用````包裹有关键字的数据库或表名。顶部使用 USE 指定sql在那个数据库中执行。使用USE后CREATE TABLEhello-mysql.student便可以写成CREATE TABLE student

1
2
3
4
5
6
7
8
9
10
11
12
13
14

USE `hello-mysql`
/* 创表 */
CREATE TABLE student(
  id INT PRIMARY KEY AUTO_INCREMENT COMMENT 'Id',
  name VARCHAR(50) NOT NULL COMMENT '学生名',
  gender VARCHAR(10) NOT NULL COMMENT '性别',
  age INT NOT NULL COMMENT '年龄',
  class VARCHAR(50) NOT NULL COMMENT '班级名',
  score INT NOT NULL COMMENT '分数'
) CHARSET=utf8mb4

/* 删表 */
drop table student;

查表

基础查询

  • where：查询条件，比如 where id=1
  • as：别名，比如 select xxx as ‘yyy’
  • and: 连接多个条件
  • in/not in：集合查找，比如 where a in (1,2)
  • between and：区间查找，比如 where a between 1 and 10
  • limit：分页，比如 limit 0,5
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
/* 整表查询 */
SELECT * FROM student;
/* 指定查询的列 */
SELECT name, score FROM student;
/* DISTINCT 去重 */
SELECT DISTINCT name FROM student;
/* 通过 as 修改返回的列名 */
SELECT name as "名字", score as "分数" FROM student;
/* 带条件查询， 使用关键字 where */
SELECT name as "名字", score as "分数" FROM student where age >= 20;
/* and 指定多个条件 */
SELECT name as "名字", score as "分数" FROM student where gender = '男' and score > 70;
/* like 模糊查询 */
select * from student where name like '王%';
/* in 指定一个集合，匹配该集合的查询 */
select * from student where class in ('一班', '二班');
/* not in 不匹配该集合的查询 */
select * from student where class not in ('三班');
/* between and 区间取值  */
select * from student where age between 18 and 20;
/* limit 限制查询结果：分页小帮手 */
select * from student limit 5,10; -- 从5开始的10条数据

稍微进阶

**<font style="color:rgb(37, 41, 51);">order by</font>** 指定排序的列: <font style="color:rgb(37, 41, 51);">asc</font> 升序 <font style="color:rgb(37, 41, 51);">desc</font> 降序

1
2
/* 根据 score 升序排列，如果 score 相同再根据 age 降序排列 */
select name,score,age from student order by score asc,age desc;

**<font style="color:rgb(37, 41, 51);">GROUP BY</font>**** **分组统计

  • 根据班级来分组是 <font style="color:rgb(37, 41, 51);">GROUP BY</font> class。求平均成绩使用 sql 内置的函数<font style="color:rgb(37, 41, 51);">AVG()</font>。意思就是按 class 分组，查询每个 class 的平均 score 再汇总后输出。
1
SELECT class as '班级', AVG(score) AS '平均成绩' FROM student GROUP BY class ORDER BY '平均成绩' DESC;

**<font style="color:rgb(37, 41, 51);">HAVING</font>** 在使用 分组 <font style="color:rgb(37, 41, 51);">GROUP BY</font> 后还需进一步过滤时使用，这个时候就不能使用 <font style="color:rgb(37, 41, 51);">where</font> 啦， 也不该使用 <font style="color:rgb(37, 41, 51);">order by</font> 了

1
SELECT class,AVG(score) AS avg_score FROM student GROUP BY class HAVING avg_score > 90;

**<font style="color:rgb(37, 41, 51);">distinct</font>** 去重

1
SELECT DISTINCT name FROM student;

增删改

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
/* 插入 */
INSERT INTO student (name, gender, age, class, score)
    VALUES
        ('张三', '男',18, '一班',90),
        ('李四', '女',19, '二班',85),
        ('王五', '男',20, '三班',70),
        ('赵六', '女',18, '一班',95),
        ('钱七', '男',19, '二班',80),
        ('孙八', '女',20, '三班',75),
        ('周九', '男',18, '一班',85),
        ('吴十', '女',19, '二班',90),
        ('郑十一', '男',20, '三班',60),
        ('王十二', '女',18, '一班',95),
        ('赵十三', '男',19, '二班',75),
        ('钱十四', '女',20, '三班',80),
        ('孙十五', '男',18, '一班',90),
        ('周十六', '女',19, '二班',85),
        ('吴十七', '男',20, '三班',70),
        ('郑十八', '女',18, '一班',95),
        ('王十九', '男',19, '二班',80),
        ('赵二十', '女',20, '三班',75);

/* 更新 */
UPDATE `hello-mysql`.`student` SET `email` = 'xxx@qq.com' WHERE (`id` = '10');

/* 删除 */
DELETE FROM `hello-mysql`.`student` WHERE (`id` = '10');

ALTER 修改表结构

修改数据库中已存在的表或者其他数据库对象。常见的使用场景包括添加列、删除列、改变数据类型等。

1：删除列

1
ALTER TABLE 表名 DROP COLUMN 列名

2：增加列

1
ALTER TABLE 表名 ADD COLUMN 列名 属性 【约束】

3：修改列的类型信息

1
ALTER TABLE 表名 CHANGE COLUMN 列名 【新】列名

4：重命名列

1
ALTER TABLE 表名字 CHANGE COLUMN 列名 新列名 属性

5：重命名表

1
ALTER TABLE 表名 RENAME TO 表新名

6：删除表中主键

1
Alter TABLE 表名 DROP primary key

7：添加主键

1
ALTER TABLE 表名 ADD CONSTRAINT 约束名 PRIMARY KEY (添加列)

8：添加索引

1
ALTER TABLE 表名 ADD index 索引名 (列名)

9：修改列的属性

1
ALTER TABLE 表名 MODIFY COLUMN 要修改属性的列名 新属性

内置函数

聚合函数

用于对数据的统计，比如 AVG(平均数)、COUNT(计数)、SUM(总和)、MIN(最小值)、MAX(最大值)。

1
2
/* [*]代表当前行，即查询到的行 */
select avg(score) as '平均成绩',count(*) as '人数',sum(score) as '总成绩',min(score) as '最低分', max(score) as '最高分' from student

字符串函数

用于对字符串的处理，比如 CONCAT(字符串拼接)、SUBSTR(字符串截取)、LENGTH(字符串长度)、UPPER(字符串大写)、LOWER(字符串小写)

<font style="color:rgb(37, 41, 51);">SUBSTR(str, 开始下标, ?结束下标)</font> mysql 下标从 1 开始

1
SELECT CONCAT('xx', name, 'yy'), SUBSTR(name,2,3), LENGTH(name), UPPER('aa'), LOWER('TT') FROM student;

<font style="color:rgb(37, 41, 51);">数值函数</font>

用于对数值的处理，比如 ROUND(四舍五入)、CEIL(向上取整)、FLOOR(向下取整)、ABS(绝对值)、MOD(取模)

取模：即计算两个数相除后的余数： <font style="color:rgb(37, 41, 51);">SELECT MOD(15, 4)</font> 👉 这将返回结果为<font style="color:rgb(37, 41, 51);">3</font>，因为 15 除以 4 的商为 3，余数为<font style="color:rgb(37, 41, 51);">3</font>。

1
SELECT ROUND(1.234567, 2), CEIL(1.234567), FLOOR(1.234567), ABS(-1.234567), MOD(5, 2);

<font style="color:rgb(37, 41, 51);">日期函数</font>

用于对日期、时间的处理，比如 DATE、TIME、YEAR、MONTH、DAY

1
SELECT YEAR('2023-06-01 22:06:03'), MONTH('2023-06-01 22:06:03'),DAY('2023-06-01 22:06:03'),DATE('2023-06-01 22:06:03'), TIME('2023-06-01 22:06:03');

<font style="color:rgb(37, 41, 51);">条件函数</font>

条件是否成立返回不同的值，比如 IF、CASE

<font style="color:rgb(37, 41, 51);">if</font> 函数适合单个条件，

1
select name, if(score >=90, '及格', '不及格') as 'pass' from student;

<font style="color:rgb(37, 41, 51);">case</font> 适合多个条件，和 js 的 swtch 语句很像

<font style="color:rgb(37, 41, 51);">case when ... then ... else ... end</font>

1
select name, case when score > 90 then '优秀' when score > 80 then '良好' else '差' end as 'pass' from student

<font style="color:rgb(37, 41, 51);">系统函数</font>

用于获取系统信息，比如 VERSION、DATABASE、USER。

1
select VERSION(), DATABASE(), USER()

<font style="color:rgb(37, 41, 51);">类型转换函数</font>

转换类型为另一种，比如 CAST、CONVERT、DATE_FORMAT、STR_TO_DATE

CAST 类型转换，可转换的类型如下：

  • signed：整型；
  • unsigned：无符号整型
  • decimal：浮点型；
  • char：字符类型；
  • date：日期类型；
  • time：时间类型；
  • datetime：日期时间类型；
  • binary：二进制类型
1
2
select greatest(1, convert('123', signed),3);
select greatest(1, cast('123' as signed),3);

<font style="color:rgb(37, 41, 51);">DATE_FORMAT</font> 日期格式化

1
SELECT DATE_FORMAT('2022-01-01', '%Y年%m月%d日');

STR_TO_DATE 字符串转 DATE 类型

1
SELECT STR_TO_DATE('2023-06-01', '%Y-%m-%d');

<font style="color:rgb(37, 41, 51);">其他函数</font>

<font style="color:rgb(37, 41, 51);">NULLIF</font>：如果相等返回 null，不相等返回第一个值。

<font style="color:rgb(37, 41, 51);">COALESCE</font>：返回第一个非 null 的值：

<font style="color:rgb(37, 41, 51);">GREATEST</font>：返回几个值中最大。<font style="color:rgb(37, 41, 51);">LEAST</font>：返回几个值中最小。

本博客所有文章除特别声明外，均采用 CC BY-SA 4.0 协议 ，转载请注明出处！