MySQL语句之数据查询数据（DQL）

da990622

已于 2022-05-14 10:28:01 修改

阅读量4k

点赞数

CC 4.0 BY-SA版权

文章标签： sql 数据库 database

于 2022-05-13 09:25:40 首次发布

本文链接：https://blog.youkuaiyun.com/da990622/article/details/124744871

本文详细介绍了创建和管理数据库表格的方法，包括添加、查询、去重、分组统计和数据约束等，同时涵盖了SQL语句实例和数据库备份与恢复的基本操作。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

-- 创建表

CREATE TABLE student(
id INT,
NAME VARCHAR(20),
chinese INT,
english  INT,
math INT
)

INSERT INTO student(id,NAME,chinese,english,math) VALUES
(1,'行哥',89,78,90),
(2,'潘金莲',67,53,95),
(3,'凤姐',87,78,77),
(4,'旺财',88,98,92),
(5,'白小黑',82,84,67),
(6,'白小黄',55,85,45),
(7,'范蹦蹦',75,65,30);

SELECT * FROM student;  -- 查询表中所有信息

-- 查询表中所有学生的姓名和英语成绩。

SELECT NAME,english FROM student;	-- 查询表中所有学生的姓名和英语成绩

-- 过滤表中重复数据。(去重的关键字 distinct)


SELECT  DISTINCT id,NAME,chinese,english,math FROM student;  -- 过滤表中重复数据

-- .统计每个学生的总分


SELECT NAME '姓名',(IFNULL(chinese,0)+IFNULL(math,0)+IFNULL(english,0))'总分' FROM student;	-- 统计每个学生的总分并 以‘总分’列形式展示

-- 在所有学生总分数上加10分特长分。


SELECT NAME '姓名',
(IFNULL(chinese,0)+IFNULL(math,0)+IFNULL(english,0))'总分',
10 '特长分',
(IFNULL(chinese,0)+IFNULL(math,0)+IFNULL(english,0)+10)'理论分+特长总分' 
FROM student;	-- 给所有学生总分数加上十分特长分

-- 使用别名表示学生分数。


SELECT NAME'姓名', 
chinese'汉语成绩',
english'英语成绩',
math'数学成绩'
FROM student;  -- 使用别名查询学生成绩

-- .查询姓名为行哥的学生成绩

SELECT id,NAME,chinese,english,math FROM student WHERE NAME= '行哥'; -- 查询姓名为 行哥 的学生成绩

-- 查询总分大于200分的所有同学

SELECT id,NAME,chinese,english,math FROM student WHERE 
(IFNULL(chinese,0)+IFNULL(math,0)+IFNULL(english,0))>200; -- 查询总分大于200 分的同学

-- 查询英语分数在 80－90之间的同学。

SELECT id,NAME,chinese,english,math FROM
 student WHERE 80<english AND english <90;  -- 查询英语分数在 80－90之间的同学。

-- 查询数学分数为89,90,91的同学。

SELECT id,NAME,chinese,english,math FROM
 student WHERE math IN(89,90,91); -- 查询数学分数为89,90,91的同学。

-- 对数学成绩升序排序

SELECT id,NAME, chinese,english,math FROM
 student ORDER BY math ASC; -- 对数学成绩升序排序

-- 对总分降序排序，然后再按数学从高到低的顺序输出


SELECT id,NAME,chinese,english,math,
(IFNULL(chinese,0)+IFNULL(math,0)+IFNULL(english,0))'总分' 
FROM student 
ORDER BY (IFNULL(chinese,0)+IFNULL(math,0)+IFNULL(english,0)) DESC ,
math DESC;  -- .对总分降序排序，然后再按数学从高到低的顺序输出

-- 新增列并赋值


ALTER TABLE student ADD COLUMN score INT NOT NULL; -- 新增一个列int不为空；

UPDATE student SET score =(chinese+english+math);	-- 更新数据将(chinese+english+math)的值赋给score；

ALTER TABLE student DROP COLUMN score;	-- 删除名为score的列；

-- 查询所有姓白的学生英语成绩。

SELECT NAME,english FROM student WHERE NAME LIKE '%白%'; -- 查询所有姓白的学生英语成绩。

-- 对姓白的学生成绩排序输出


SELECT id,NAME,chinese,english,math,score FROM
 student WHERE NAME LIKE '%白%' ORDER BY score ASC; -- 对姓白的学生成绩排序输出

-- 查询数学成绩大于等于平均分的所有学生信息


SELECT id,NAME,chinese,english,math,score FROM student WHERE
  math >= (SELECT AVG(math) FROM student) ; -- .查询数学成绩大于等于平均分的所有学生信息

-- 查询出英语成绩最高分的学生信息


SELECT id,NAME,chinese,english,math,score FROM student WHERE
 english =(SELECT MAX(english) FROM student) ; -- 查询出英语成绩最高分的学生信息

-- 模糊查询出学生姓名中第二个字是金的学生信息

 
SELECT id,NAME,chinese,english,math,score FROM student WHERE NAME LIKE '_金_'; -- 模糊查询出学生姓名中第二个字是金的学生信息

-- 查询出英语成绩平均分是多少

SELECT AVG(english),'英语平均分' FROM student;		-- 查询english平均分  并以英语平均分列名展示

-- 查询出数学总分是多少

SELECT SUM(math),'数学总分' FROM student;	-- 查询math总和  并以数学总分列名展示

--分页查询

SELECT id,NAME,math,chinese,english FROM exam LIMIT 3,3;  -- 从第三行（程序员的3）数据往后查3行数据

-- 分组查询 group by

select 字段列表 from 表名  group by 分组字段名称;  -- 字段名称需有不同的值，例如性别，阵营
-- 按照性别分组,查询出他们的数学成绩的平均分
SELECT  
     sex '性别', -- 查询的分组字段
     AVG(math) '数学平均分' -- 查询每一个组的数学平均分
FROM
	student
GROUP BY 
	sex ;
带条件分组查询的语法: where 条件必须放在group by 之前,否则语法错误

select 字段列表包含分组字段,聚合函数.. from 表名 where 条件 group by 分组字段;

-- 筛选查询 having

where条件,group by,having 必须先有条件,分组,然后才筛选!

筛选的后面可以使用聚合函数,group by的后面是不能使用聚合函数的
SELECT 
     sex  '性别',
     COUNT(id) 人数,
     AVG(math) '数学平均分'
     
FROM
    student	
WHERE 
	math > 70
GROUP BY 
	sex 
HAVING      -- 后面可以使用聚合函数
	人数 > 2 ;

-- 数据库的备份与还原

方式1.备份:选中库名,右键----->BACKUP/Export 备份导出---->
选中Sql脚本,存储到本地某个磁盘上----->StructureAndData:表的结构和数据都需要备份

        还原:先在SqlYog将myee_2203删除了,新建库右键---->Import 导入---->选中执行execute sql 脚本

方式2

备份:
       以管理员身份进入dos,不需要登录mysql
保存到指定路径
       mysqldump -uroot -p你的密码库名 > 磁盘上的路径xx\xx\xx.sql


   还原: 进入dos控制台,登录mysql
   先将库删除,新建一个库
   使用这个库 use库名;
   加载指定路径的sql脚本
   source 备份的sql脚本路径(xx\xx\xx.sql)

-- 数据库的约束

1默认约束

CREATE TABLE stu(
	
	id INT,
	NAME VARCHAR(20),
	gender VARCHAR(3) DEFAULT '男'    -- 加入默认约束  gender 不写默认为男
);
默认约束 default :如果某个字段没有插入数据,那么默认约束起作用了

通过sql语句将默认约束去掉
ALTER TABLE stu MODIFY gender VARCHAR(3) ;
通过sql语句给gender字段加上默认约束
ALTER TABLE stu MODIFY gender VARCHAR(3) DEFAULT '男' ;

2非空约束

非空约束 not null,当前这个字段值不能为null

CREATE TABLE stu(
   id INT, -- 编号
   NAME VARCHAR(20), -- 姓名
   age INT, -- 年龄
   address  VARCHAR(50) NOT NULL-- 地址 ,加入非空约束
) ;

地址插入一个null值

INSERT INTO stu VALUES(3,'吕凌绮',32,NULL) ;  -- 这种直接插入null也属于非法行为
-- 当address字段设置not null约束,直接限定用户不能插入null值
Column 'address' cannot be null;

通过sql语句将非空约束去掉--修改表的类型

ALTER TABLE stu MODIFY address VARCHAR(50) ;

通过sql语句将非空约束加上

ALTER TABLE stu MODIFY address VARCHAR(50) NOT NULL  ;

3唯一约束

唯一约束unique (属于一种index :索引)

CREATE TABLE stu(
    id INT , -- 编号
    NAME VARCHAR(20), -- 姓名
    phone_number VARCHAR(11) UNIQUE -- 电话号码 必须设置唯一的
) ;

INSERT INTO stu VALUES(1,'吕布','13388886666'),(2,'赵云','13255559999') ;
INSERT INTO stu VALUES(3,'张飞','13388886666') ;  --  重复值 Duplicate entry '13388886666' for key 'phone_number'    
插入失败  因为有重复值

通过sql语句将唯一约束删除

alter table stu modify phone_number varchar(11) ; -- 修改表的字段类型

删除唯一约束：

ALTER TABLE stu DROP INDEX phone_number ;

通过sql语句添加唯一约束

ALTER TABLE stu MODIFY phone_number VARCHAR(11) UNIQUE ;

4主键约束

主键约束 primary key(非空且唯一的) 和auto_increment(自增长约束)
CREATE TABLE stu(
      id INT PRIMARY KEY AUTO_INCREMENT , -- 编号   加入主键约束,加入自增长
      NAME VARCHAR(20) -- 姓名
);
INSERT INTO stu VALUES(1,'阿姆鲁'),(2,'夏亚') ;
INSERT INTO stu VALUES(1,'基拉') ;-- Duplicate entry '1' for key 'PRIMARY' id是一个主键,值不能重复
INSERT INTO stu VALUES(NULL,'阿斯兰') ;-- Column 'id' cannot be null
通过sql将主键约束删除
ALTER TABLE stu DROP PRIMARY  KEY;
通过sql语句将主键约束加入
ALTER TABLE stu MODIFY id INT PRIMARY KEY  ;
加入自增长约束之后,不需要给id赋值了,自己自增自增长约束可以自己插入新的id,然后在之前id上继续自增

5外键约束 foreign key

CREATE TABLE emp(
	id INT PRIMARY KEY AUTO_INCREMENT, -- 员工编号
	emp_name VARCHAR(10) , -- 员工姓名
	emp_gender VARCHAR(5), -- 员工性别
	emp_dept_name VARCHAR(10) -- 员工所在的部门名称
) ;

INSERT INTO emp(emp_name,emp_gender,emp_dept_name)
VALUES('路子明','男','开发部'),
('吕树','男','测试部'),
('罗素','男','运维部'),
('任禾','男','测试部'),
('赵甲第','男','开发部') ,
('沈幼楚','女','运维部') ;  -- 杀陈狗，抢幼楚

DROP TABLE emp;

-- 上面存在的问题:员工表中包含员工信息以及部门信息,
-- 数据部门信息存在冗余,重复性大,优化改进:一张表始终描述一件事情
-- 将员工表拆分出来:将部门信息放在部门表中

-- 创建一张部门表 dept表
CREATE TABLE dept(
	id INT PRIMARY KEY AUTO_INCREMENT, -- 主键自增长约束 部门编号
	dept_name VARCHAR(20)   -- 部门名称
) ;
-- 插入三个部门
INSERT INTO dept(dept_name) VALUES('开发部'),('测试部'),('运维部') ;


-- 创建员工表
CREATE TABLE emp(

	id INT PRIMARY KEY AUTO_INCREMENT, -- 员工编号
	emp_name VARCHAR(10) , -- 员工姓名
	emp_gender VARCHAR(5), -- 员工性别
	dept_id INT -- 员工的部门id
 
) ;

INSERT INTO emp(emp_name,emp_gender,emp_dept_name)
VALUES('路子明','男',1),
('吕树','男',2),
('罗素','男',3),
('任禾','男',1),
('赵甲第','男',2) ,
('沈幼楚','女',3) ;  -- 杀陈狗，抢幼楚

约束 foreign key ,员工表dept_id 和部门表主键id进行关联

外键所在的表----从表 (员工表)
部门表: 主表

加入外键约束:修改和删除是很麻烦,需要先修改或者删除从表数据,然后修改/删除主表数据

DELETE FROM emp WHERE id= 3;
DELETE FROM dept WHERE id = 3;