数据库常见查询语句集锦

插入数据

语法:

insert into 表名(列1,列2,列3)

values(值1,值2,值3);

INSERT INTO student(sid,name,phone,sex,age)
​
VALUES(‘WNTF202108025’, ‘张小四'', ‘18612345678’,’男’)

1、如果对整个表的所有列都需要增加数据

insert into 表名

values(值1,值2,值3);

INSERT INTO student
​
VALUES(‘WNTF202108025’, ‘张小四'', ‘18612345678’,’男’, ‘25,'2021-08-20 16:08:38’)

日期格式

to_date()

to_date('2019-12-16','YYYY-MM-DD')

to_date('20081115','YYYYMMDD')

2、如果要批量插入教据,一次性插入多行数据

INSERT INTO student(sid,name,phone,sex,age,createtime)
​
VALUES(202108025,'张小四',18612345678,'男',25,'2021-08-20 16:08:38');
​
INSERT INTO student(sid,name,phone,sex,age,createtime)
​
VALUES(202108025,'张小四',18612345678,'男',25,'2021-08-20 16:08:38');
​
INSERT INTO student(sid,name,phone,sex,age,createtime)
​
VALUES(202108025,'张小四',18612345678,'男',25,'2021-08-20 16:08:38')

3、标准的批量数据的插入方法

#只使用一个VALUES，且数据之间用逗号‘，’链接

INSERT INTO student(sid,name,phone,sex,age,createtime)
​
VALUES
​
(202201,'张三',18612391428,'男',25,'2021-08-20 16:08:38'),
​
(202202,'李四',18634244248,'女',22,'2021-08-20 16:08:38'),
​
(202203,'王五',18612345678,'男',21,'2021-08-20 16:08:38'),
​
(202204,'赵六',18612532448,'男',23,'2021-08-20 16:08:38'),
​
(202205,'小白',18631112448,'女',21,'2021-08-20 16:08:38')
​

删除数据

TRUNCATE TABLE student; #清空表数据

DELETE FROM student;#删除表数据

1.truncate命令的语法 truncate语句的作用是清空表中数据，语法就是truncate table后面直接加表名，但是需要注意的是，该语句只删除表中的全部数据而不删除表结构。

2.truncate命令与delete、drop命令的区别

（1）处理效率上，drop最高，其次是truncate。 这是因为执行truncate命令时，为了提高性能，它避过了删除数据的DML语言，因此它属于DDL语句且不能回滚

（2）truncate只能作用于表；delete，drop可作用于表、视图等

（3）truncate一次性删除表中的所有数据，但是不删除表的结构；delete删除的是表中的部分记录。

（4）delete命令删除的数据将存储在系统回滚段中，需要的时候，数据可以回滚恢复，而truncate命令删除的数据是不可以恢复的。

（5）使用truncate后，表所占用的空间会恢复到初始大小；delete操作不会减少表或索引所占用的空间，drop语句将表所占用的空间全释放掉。

3.谨慎使用truncate命令 如果只需要删除表的部分记录，那么使用delete命令就可以；如果想保留表结构的同时删除表中数据，且不影响事务，就可以使用truncate命令。 但还是要小心使用truncate命令，这是因为：

（1）truncate命令执行后无法回滚。

（2）truncate的执行速度很快，被删除的数据就不能再恢复了。

（3）使用truncate命令之前最好对当前表中的数据做备份

delete from 表名 where 条件

删除student表中 age 大于25的数据

delete from student where age >25;

如果有多个条件 记得使用 and 或者 or 来将条件进行连接

删除 id小于3 或者 id 大于8的数据

delete from student where id < 3 or id >8;

修改数据

update 表名 set 列1=值1,列2=值2 where 条件

1、将student表中，id为202201的 学生的姓名修改为'王五'

update student set name='王五' where id=202201;

2、关于空值(null)，在where中不能够使用 =null 或者 != null ,而是使用 is null 或者 is not null 将student表中，所有name为空的信息，将其id修改为22

update student set sid=22 where name is null;

查询语句

select * from 表名; * 表示所有列

select 列名1,列名2 from 表名;

查询出所有学生的姓名以及年龄

select name,age from student;

带条件的查询 where 条件

查询出学号大于202203的所有的学生的姓名以及性别
​
select name,sex from student where sid> 202203;
​
查询出学号大于202203并且年龄为20 的所有的学生的所有信息
​
select * from student where sid>202203 and age=20;
​
查询学生成绩在90(含) ~100分(含)之间的
​
SELECT *FROM grade WHERE score >=90 AND score <= 100;
​
\#针对上述的闭区间，可以使用between ... and 
​
SELECT*FROM grade WHERE score BETWEEN 90 AND 100;
​
查询学生成绩不在90(含) ~100分(含)之间的
​
SELECT*FROM grade WHERE score NOT BETWEEN 90 AND 100;

去重 distinct

查找出成绩表有多少种科目
​
select distinct cid from grade;
​
查找出学生表有多少种性别
​
select distinct sex from student;

order by 列名/字段

对查询的结果进行一个排序,默认升序

将student表中的信息，根据年龄升序排序

select * from student order by age;

升序的另外一种写法，在列名后面加上asc(默认是可以忽略的)

select * from student order by age asc;

SELECT * FROM student WHERE age > 25 ORDER BY age ASC;#按照age从小到大排序

如果想要降序排序,则在列名后面加上desc

将student表中的信息，根据年龄降序排序

select * from student order by age desc;

SELECT * FROM student WHERE age > 25 ORDER BY age DESC;#按照age从大到小排序

LIMIT完整语法:LIMIT开始行数，查询行数 0，10

c01考试成绩从大到小，取前四名

SELECT* FROM grade WHERE cid='c01’ORDER BY score DESC LIMIT 0，4;

 

order by 多列排序

order by 列1,列2

表示先根据列1排序，如果列1相同的，再根据列2排序

查找学号大于1000的学生的姓名，性别，年龄并且按照年龄降序排序，同一个年龄的按照学号的降序排序

select name,sex,age from student where sid> 1000 order by age desc,sid desc;

in/not in 属于/不属于

查找出年龄是21或者是22或者是23的学生的所有信息
​
select * from student where age in (21,22,23);
​
查找出年龄不是21或者不是22或者不是23的学生的所有信息
​
select * from student where age not in (21,22,23);
​

模糊查询 like

用%代替任意字符，用_代替一个字符

#查询学员中姓”张”学生
​
SELECT * FROM student WHERE name LIKE'张%'
​
SELECT * FROM student WHERE name LIKE'张_'
​
查询包含“小”的姓名的学生
​
SELECT * FROM student WHERE name LIKE '%小%’

分组函数/聚合函数/统计函数

As 对列重命名

SELECT AVG (age)FROM student WHERE sex='男';#查询男的学生的平均年龄

#对上述查询结果进行列的重命名，在列的后面直接跟上新列名，或加一个as均可

SELECT AVG (age)AS AverageAge FROM student WHERE sex='男';

统计某一个条件下的查询绪果的行数

SELECT COUNT(sid) FROM student;

统计某个查询结果的行数，在COUNT的参数中，可以直接使用

SELECT COUNT(*) FROM grade ;

统计某个列的总和

SELECT SUM( age) FROM student;

GROUP BY 列名

根据这个列来进行分组,列的值相同的分到同一个组中 比如group by sex就表示根据性别分组，性别相同的就分到同一个组中。

计算出每个性别的平均年龄
​
思路：先根据性别进行分组，性别相同的分到同一个组中，然后再算出每个组的平均年龄
​
select avg(age) from student group by sex
​
计算出不同性别的总身高。
​
select sex,sum(height) from student group by sex;
​
统计各性别的人数。
​
select sex,count(*) from student group by sex;
​
group by 可以根据多个列来进行分组
比如 group by height,sex先根据sex分组，同一个sex的，再根据身高分组
计算每个性中每种身高各有多少学生
​
思路：先根据性别分组，同一个性别的，再根据身高分组，再算出他的count(*)
​
select sex,height,count(*) from student
​
group by sex,height;

 

IS NULL和IS NOT NULL

SELECT *FROM student WHERE sex IS NULL;

having 条件

having 也是对数据进行过滤和筛选，只不过是对分组后的内容进行的限制 如果使用了having就一定要使用group by

比如查询出sex人数大于4人的sex的sex、sex人数以及他们的平均身高

select sex,count(*),avg(height) from student

group by sex

having count(*) > 4;

 

多表查询

多表查询,如果查询的数据需要从多张表中获取，就需要在where字句后面，把这些表的相同含义的字段用等于号连接起来

查找名字为"小白"的同学的名字、性别和各科所对应的成绩
​
select s.name,s.sex,c.class,g.score from student s,grade g,course c
​
where s.sid=g.sid and g.cid=c.cid and s.name='小白';
​
 
​
select * from student,grade;  #笛卡尔积
​
查询每门学科的平均成绩
​
SELECT cid,AVG(score)FROM grade GROUP BY cid;
​
\#GROUP BY天生自带去重功能，所以可以代替DISTINCT使用
​
SELECT cid FROM grade GROUP BY cid;
​
SELECT DISTINCT(cid)FROM grade;
​
查询平均成绩最高和最低的误程
​
SELECT cid,AVG(score)AS AvgScore FROM grade GROUP BY cid ORDER BY AvgScore DESC LIMIT 1;
​
SELECT cid,AVG(score)AS AvgScore FROM grade GROUP BY cid ORDER BY AvgScore LIMIT 1;

分组函数使用要点

1、分组函数只能出现在select、having、order by 子句中

2、select中除了分组函数外的所有列名，必须出现在 group by 后面，否则，有语法错误（重要）

3、有having 一定有group by,但是有group by 不一定要有having

4、如果在select语句同时含有group by、having、order by，那么他们的顺序是group by、having、order by

子查询

顾名思义，子查询就是在一个查询语句中嵌套使用了其他的查询语句 子查询，必须要用小括号括起来。

SELECT MAX ( age) FROM student;#查询年龄最大的数字
​
SELECT*FROM student WHERE age=25;
​
利用子查询，混合上述两个条件
​
SELECT*FROM student WHERE age=(SELECT MAX(age)FROM student);
​
注意如果子查询查询到了多个值，则就需要使用in/not in
例如：查找出部门10与部门30中工种相同的职工的姓名和工种。
​
select name,height from student
​
where age = 23 and in(select distinct(age) from student where sex = ‘男’);
​
\#ANY:用于条件判断的子查询,>,<,>=,<=,=,<>,找各个子集合中的或运算。
​
\>ANY大于最小的      <ANY:小于最大
​
查询男生比女生年龄最大的小的人
​
SELECT * FROM student WHERE sex='男' and age < 22;
​
SELECT age FROM student WHERE sex='女' GROUP BY age;
​
SELECT * FROM student WHERE sex='男' and age < ANY(SELECT age FROM student 
​
WHERE sex='女' GROUP BY age);
​
\#ALL:等价于在子集合中找各个条件的与运算结果。
​
\>ALL:大于最大的      <ALL:小于最小的
​
查询男生比女生年龄最小的还小人
​
SELECT * FROM student WHERE sex='男' and age < ALL(SELECT age FROM student 
​
WHERE sex='女' GROUP BY age);
​

#对表进行重命令，以便于简化SQL语句，同时，在选择列时，也建议明确指定表名

select s.sid,s.name,s.sex,c.class,g.score from student s,grade g,course c

where s.sid=g.sid and g.cid=c.cid and s.name='小白';

#除了使用FROM AA，BB，CC 这种逗号运算符进行多表连接外，也可以使用JOIN ... ON

SELECT student.sid,name,score,student.createtime FROM student JOIN grade ON

student.sid = grade.sid WHERE cid='c01';

#以上的连接方式,统称为:内连接

#如果是三张表呢?查找成绩最差的学生的班主任老师是谁?

SELECT s.name,c.class,c.teacher,g.score FROM student s,grade g,course c

WHERE s.sid=g.sid and g.cid=c.cid ORDER BY g.score LIMIT 1;

#在进行多表连接查询时，必须指定连接条件〈通常是主外键关系〉，但是主外键关系没有明确在表中建立，也可以使用。表中无主外键，键在心中

无论哪个数据库，要维持主外健约束，是有性能开销的，因为要删除和修改时，要扫描一遍外表，看看能不能删除和修改.