MySQL常用语句(CURD)

文章目录

提示：以下是本篇文章正文内容，Java系列学习将会持续更新

一、基础操作

1-1 库表操作

库的相关操作：

-- 建库createdatabase test_db;createdatabase java_0326 defaultcharset utf8mb4;-- 设置默认库，表示之后的操作都是对该库进行的use test_db;-- 展示所有库showdatabases;-- 展示默认库selectdatabase();-- 删库drop datebase ifexists java_0326;

表的相关操作：

-- 建表CREATETABLE 表名称 (
    列名称   类型        约束信息,
    id       intnotnull,
    name     varchar(30), 
    age      int);-- 例如createtable persons (id intprimarykey);-- 展示默认库的所有表名称showtables;-- 删表droptableifexists student;-- 清空表数据（截断）truncatetable student;

1-2 insert

①单行+全列插入

insertinto student values(100,10000, ‘唐三藏’,NULL);insertinto student values(101,10001, ‘孙悟空’,12345);

②多行+指定列插入

insertinto student (id, sn, name)values(102,20001, ‘刘大耳’),(103,20002, ‘曹阿瞒’),(104,20003, ‘孙仲谋’);

1-3 select

①列查询

-- 全列查询-- 不建议使用，因为数据过大的话，容易使系统崩掉select*from student;-- 指定列查询-- 指定的列可以不按定义表的顺序来select id, name, english from student;

②查询字段为表达式

-- 表达式不包含字段select id, name,10from student;-- 表达式包含字段select id, name,(chinese + math + english)/3from student;-- 结果集中，表头列名为别名select id, name, chinese + math + english 总分 from student;-- 结果集分三列，id、math、0/1select id, math, math >60from student;-- 有null参与的运算结果还是null-- 即使是null，作bool值的时候，也看作falseselect qq_mail =nullfrom student;

③去重：distinct

-- 可以列出所有不重复的math成绩集合selectdistinct math from student;-- 此时就没有去重功能了，因为没有id和math同时重复的学生selectdistinct id, math from student;

④排序：order by

-- asc : ascend 升序 不写默认为升序-- desc : descend 降序-- NULL视为比任何数据都小-- 先优先math升序；若math相等，则按Chinese降序；若都相等，按id升序select*from student orderby math asc, chinese desc, id;-- 使用表达式 + 别名排序select  name, math + chinese + english 总分 from student orderby 总分;

⑤条件查询：where

比较运算符说明>, >=, <, <=和Java中一样=等于，NULL不安全，例如 NULL = NULL 的结果是NULL<=>等于，NULL安全，例如NULL <=> NULL 的结果是TRUE!=, <>不等于between a0 and a1范围匹配[a0, a1]，如果a0<= value<= a1，返回TRUEin (option, …)如果是 option 中的任意一个，返回 TRUEis null是 NULLIs not null不是 NULLlike模糊匹配，如 like ‘孙%’ 或 like ‘孙_’逻辑运算符说明and条件都满足，取trueor满足其中一个条件，取truenot条件为true,结果取false

-- 基本查询-- 查询english成绩高于60分的学生select name, english from student where english >60;-- 查询总分高于240分的学生select  name, math + chinese + english 总分 from student 
        where math + chinese + english >240;-- and与orselect*from student where english >60and math >60;select*from student where english >90or math >90;-- 范围查询select*from student where english between80and100;-- in查询-- 查询math成绩是这五个数据之一的学生select*from student where math in(80,85,90,95,100);-- 模糊查询-- %匹配任意多个(包括0个)字符select name from student where name like ‘孙%’;select name from student where name like ‘%孙’;select name from student where name like ‘%孙%’;-- _严格匹配一个任意字符select name from student where name like ‘孙_’;-- NULL查询-- 查询qq_mail已知的同学select name, qq_mail from student where qq_mail isnotnull;-- 查询qq_mail未知的同学select name from student where qq_mail isnull;

⑥分页查询：limit

-- 从下标0开始，筛选5条结果select*from student limit5;-- 从下标2开始，筛选5条结果select*from student limit2,5;-- 筛选5条结果，从下标2开始。这种写法意思更明确select*from student limit5offset2;-- 综合运用-- 查询数学成绩高于80分，且排名前三的学生select name, math from student where math >80orderby math desclimit3;

1-4 update

-- 修改孙悟空的数学成绩为80分update student set math =80where name = ‘孙悟空’;-- 修改曹孟德的数学成绩为80分，语文成绩为90分update student set math =80, chinese =90where name = ‘曹孟德’;-- 将总分倒数三名同学的数学成绩加上20分  (支持limit,不支持offset)update student set math = math +80orderby Chinese + math + english limit3;-- 将所有同学的语文成绩更新为2倍update student set chinese = chinese *2;

1-5 delete

-- 删除孙悟空的考试成绩deletefrom student where name = ‘孙悟空’;-- 将数据表中的某个字段从表中删除altertable 表名 drop 字段名;-- 删除整张表及数据droptableifexists student;-- 仅删除表的全部数据  for-eachdeletefrom student;-- 清空：表截断 O(1)truncatetable student;

二、高阶操作

2-1 复制

--将学生表的数据复制到用户表insertintouser(name, email)select name, qq_email from student;

2-2 聚合查询

①聚合函数

-- 聚合函数是将多行数据合并为一行返回结果-- count-- 统计数据的数量selectcount(*)from student;selectcount(0)from student;-- 统计邮箱的个数，qq_mail为null不计入结果selectcount(qq_mail)from student;-- sum-- 统计所有学生的数学总分selectsum(math)from student;-- avg-- 统计班级数学平均分selectavg(math)from student;-- max-- 统计班级数学最高分selectmax(math)from student;-- min-- 统计班级数学最低分selectmin(math)from student;

student表：

②group by子句

-- group by子句可以对指定列进行分组查询-- 按照规范，select查询到的内容应该是聚合的内容-- 查询每种角色的最高工资、最低工资、平均工资select role,max(salary),min(salary),avg(salary)from emp groupby role;-- 可以先where筛选后再聚合-- 统计每种角色薪资高于500的人数select role,count(*)from emp where salary >500groupby role;-- 多聚合,在不同列的组合下进行聚合查询select company,count(*)from emp2 groupby company;select company, depart,count(*)from emp2 groupby company, depart;select company, depart, role,count(*)from emp2 groupby company,depart,role;

emp2表：

③having

-- having关键字用于聚合之后进行过滤操作select role,avg(salary)from emp groupby role havingavg(salary)>300;

2-3 联表查询

实际开发中往往数据来自不同的表，所以需要多表联合查询。多表查询是对多张表的数据取笛卡尔积。

两张表：

2-3-1 内连接

select

字段

from

表1

join

表2

on

连接条件

where

其他条件;

-- 添加 联表 条件后，得到的结果才是有意义的-- 标准写法select*from users join articles on uid = author_id where users.name ='小红';-- 也可以这么写select*from users, articles where uid = author_id and users.name ='小红';-- inner 可以省略select*from users innerjoin articles on uid = author_id;

内连查询结果：

2-3-2 外连接

① 左外连接，表1完全显示

select

字段名

from

表名1

left join

表名2

on

连接条件;
② 右外连接，表2完全显示

select

字段

from

表名1

right join

表名2

on

连接条件;

-- 左外联select*from users leftouterjoin articles on uid = author_id;select*from users leftjoin articles on uid = author_id;

左外连查询结果：

-- 右外联select*from users rightouterjoin articles on uid = author_id;select*from users rightjoin articles on uid = author_id;

右外连查询结果：

2-3-3 自连接

自连接是指在同一张表连接自身进行查询。

原表： course表、score表

显示所有 “Java” 成绩比 “计算机原理” 成绩低的成绩信息

SELECT s1.student_id, s1.score, s2.score 
FROM score s1
JOIN score s2 
ON s1.student_id = s2.student_id  -- 指向同一个学生AND s1.course_id =1-- 表1指向Java成绩AND s2.course_id =3-- 表2指向计算机原理成绩AND s1.score < s2.score;-- Java < 计算机原理

查询结果：

2-3-4 子查询

单行子查询：返回一行记录的子查询。

案例：查询与“不想毕业” 同学的同班同学：

select*from student where classes_id=(select classes_id from student where name='不想毕业');

多行子查询：返回多行记录的子查询。

案例：查询“语文”或“英文”课程的成绩信息

-- 使用INselect*from score where course_id in(select id from course where name='语文'or name='英文');-- 使用 NOT INselect*from score where course_id notin(select id from course where name!='语文'and name!='英文');-- 使用 EXISTSselect*from score sco whereexists(select sco.id from course cou where(name='语文'or name='英文')and cou.id = sco.course_id);-- 使用 NOT EXISTSselect*from score sco wherenotexists(select sco.id from course cou where(name!='语文'and name!='英文')and cou.id = sco.course_id);

2-3-5 合并查询

在实际应用中，为了合并多个select的执行结果，可以使用

union

和

union all

时，前后查询的结果集中，字段需要一致。

union

: 该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中的重复行。 案例：查询id小于3，或者名字为“英文”的课程.

select*from course where id<3unionselect*from course where name='英文';-- 或者使用or来实现select*from course where id<3or name='英文';

union all

: 该操作符用于取得两个结果集的并集。当使用该操作符时，不会去掉结果集中的重复行。
案例：查询id小于3，或者名字为“Java”的课程.

-- 可以看到结果集中出现重复数据Java select * from course where id<3 union allselect*from course where name='英文';

总结:
提示：这里对文章进行总结：
以上就是今天的学习内容，本文是MySQL的学习，我们学习整理了MySQL常用语句：基础的CURD操作和高阶的聚合查询和联表查询。之后的学习内容将持续更新！！！