Mysql查询操作

判断是否为空

-- 判断是否为空

is null

-- 判断是否不为空

is not null

如:

DROP TABLE IF EXISTS t1;
CREATE TABLE t1(id INT, name VARCHAR(10));
INSERT INTO t1(id) VALUES(1),(2);
INSERT INTO t1 VALUES(3,'tongyao');

SELECT * FROM t1 WHERE name IS NOT NULL;

SELECT * FROM t1 WHERE name IS NULL;

逻辑判断

1. and

2. or

3. not

 

and, or, not逻辑符操作

 

select * from students where 16<=age and age<=21;

 

select * from students where age<=16 or age >=22;

 

select * from students where not age=16;

 

注意:students, grades 和 subjects这三张表格,需要下载sql文件, 下载sql文件的网址: http://121.40.144.4:880/mysql_file/new_data.sql

 

打开Navicat, 然后在表格那里鼠标右键, 选择执行sql文件

 

 

然后选择最右侧的三个点的地方

 

 

 

找到已经下载好的sql文件, 选择它, 然后点击开始

等加载完后,直接关闭小窗口就可以

 

排序

关键字order by

asc升序, desc降序, 排序默认是升序

--- 升序(order by后面默认是升序, 所以没有加asc, 要加也可以)

-- 按照年龄升序排列
select * from students order by age;

--- 降序

-- 按照年龄降序排列
select * from students order by age desc;

限制

关键词limit

 

--- 展示5条数据
select * from students limit 5;

--- 从第4条数据开始输出3条(第一个3,相当于编程里面的角标index, 所以是第四条数据开始)
select * from students limit 3,3;

模糊查询

关键词like

--- % 任意多个

select * from students where name like 'xi%';

--- _匹配任意一个
select * from students where name like 'li_';

!!!没有查询到li后面只有一个的数据!!!

因为lisi,在li后面有两个,所以后面要添加两个_才能查询出结果,而不是一个

 

--- 这里有两个_ , 所以它查询了任意两个

select * from students where name like 'li__';

范围查询

关键字between, in

--- age 在 (1,16,17,22)这里面的就全部展示
select * from students where age in (1,16,17,22);

--- 一个区间内所有的数据      age在16到22所有的数据
select * from students where age between 16 and 22;

聚合函数

count()计数, sum()求和, avg()求平均数

select count(*) from students;

select count(name) from students;

select  sum(age) from students;

select  avg(age) from students;

分组

关键字group by

--- 通过subject_number,grade进行分组 然后做筛选
select subject_number,grade,count(*) from grades group by subject_number,grade having grade>=80;

where    having

子查询

--- 每一个派生表 必须有自己的名字   as tongyao 这种方式
select * from (select * from students order by age limit 5) as tongyao  where age <18;

 

连接查询

 

MYSQL中inner join、left join 和 right join的区别

 

 

DROP TABLE IF EXISTS t2;
CREATE TABLE t2(id INT, name1 VARCHAR(10));
INSERT INTO t2 VALUES(3,'java'),(4,'web'),(5,'python');

--- 内连接

SELECT * FROM t1 JOIN t2 ON t1.id = t2.id;

SELECT t1.id, t1.name1 AS "t1.name", t2.name1 AS "t2.name" FROM t1 JOIN t2 ON t1.id = t2.id;

 

--左外连接

SELECT * FROM t1 LEFT JOIN t2 ON t1.id = t2.id;

 

 

--- 右外连接

SELECT * FROM t1 RIGHT JOIN t2 ON t1.id = t2.id;

去重

关键字distinct

SELECT DISTINCT subject_number FROM grades;

-------------------------------------------------------------------------------------------------------------------------------

以上就是mysql查询操作的内容

 

思考以下问题(相关表格:grades, students, subjects):

1. 如何统计出0001和0002分别有多少人
2. 统计出0001中age 大于18岁的人数
3. 10条相同的数据,怎么做到只保留一条,把其余的9条数据全部都删除掉(tips:这个第三题,我们创建一个表叫做t3, 包含两个字段,分别是id和name1, 连续插入10条数据,然后按照第三题要求来做)

 

一、SELECT subject_number, COUNT(*) AS "人数" FROM grades GROUP BY subject_number;

二、SELECT COUNT(*) AS "人数" FROM students WHERE age > 18;

三、

DROP TABLE IF EXISTS t3;
CREATE TABLE t3(id INT, name1 VARCHAR(10));
INSERT INTO t3 VALUES(3,'tongyao'),(3,'tongyao'),(3,'tongyao'),(3,'tongyao'),(3,'tongyao'),(3,'tongyao'),(3,'tongyao'),(3,'tongyao'),(3,'tongyao'),(3,'tongyao'),(3,'tongyao'),(3,'tongyao'),(3,'tongyao'),(3,'tongyao'),(3,'tongyao');
SELECT DISTINCT * FROM t3; (这里面的DISTINCT关键字千万不要忘记咯)

 

第三题再没有去重之前,是这样:

SELECT * FROM t3;

你们都做对了吗,如果都做对的话,给自己鼓掌哦!!!

 

如果有哪里不明白的,知识点模糊的,可以单独加我qq哦, 我的qq号是1175235190, 如果有讲错的地方,也希望大家指正。希望大家互相学习,互相帮助。人生路漫漫，白鹭常相伴!!!