文章目录
DDL操作数据库
1.查询数据库
- 语法
-- 查询全部数据库
SHOW DATABASES;
- 示例
-- 查询全部数据库
SHOW DATABASES;
2.创建数据库
- 语法
-- 创建数据库
CREATE DATABASE 数据库名称;
-- 创建数据库(判断:如果不存在则创建)
CREATE DATABASE IF NOT EXISTS 数据库名称;
- 示例
-- 创建db1数据库
CREATE DATABASE db1;
-- 创建db2数据库,如果不存在则创建
CREATE DATABASE IF NOT EXISTS db2;
3.删除数据库
- 语法
-- 删除数据库
DROP DATABASE 数据库名称;
-- 删除数据库(判断:如果存在则删除)
DROP DATABASE IF EXISTS 数据库名称;
- 示例
-- 删除db1数据库
DROP DATABASE db1;
-- 删除db2数据库,如果存在则删除
DROP DATABASE IF EXISTS db2;
4.使用数据库
- 语法
-- 查看当前使用的数据库
SELECT DATABASE();
-- 使用某个数据库
USE 数据库名称;
- 示例
-- 查看当前正在使用的数据库
SELECT DATABASE();
-- 使用db1数据库
USE db1;
DDL操作表
查询表
.查询当前数据库中所有数据表
- 语法
-- 查询所有数据表
SHOW TABLES;
- 示例
-- 使用mysql数据库
USE mysql;
-- 查询所有数据表
SHOW TABLES;
2.查询某个数据表的详细结构
- 语法
-- 查询某个表的详细结构
DESC 表名称;
- 示例
-- 查询user表详细结构
DESC user;
创建表
创建数据表
- 语法
CREATE TABLE 表名 (
字段名1 数据类型1,
字段名2 数据类型2,
…
字段名n 数据类型n
);
-- 注意:最后一行末尾不要写分号
- 示例
-- 创建db1数据库
CREATE DATABASE db1;
-- 使用db1数据库
USE db1;
-- 创建tb_user数据表
CREATE TABLE tb_user(
id int,
username varchar(20),
password varchar(20)
);
-- 查询tb_user表详细结构
DESC tb_user;
修改和删除
删除数据表
- 语法
-- 删除某个数据表
DROP TABLE 表名;
-- 删除某个数据表(判断:如果存在则删除)
DROP TABLE IF EXISTS 表名;
- 示例
-- 删除tb_user表
DROP TABLE tb_user;
-- 删除tb_user表(判断,如果存在则删除)
DROP TABLE IF EXISTS tb_user;
2.修改数据表
- 语法
-- 修改表名
ALTER TABLE 表名 RENAME TO 新的表名;
-- 添加一列
ALTER TABLE 表名 ADD 列名 数据类型;
-- 修改数据类型
ALTER TABLE 表名 MODIFY 列名 新数据类型;
-- 修改列名和数据类型
ALTER TABLE 表名 CHANGE 列名 新列名 新数据类型;
-- 删除列
ALTER TABLE 表名 DROP 列名;
- 示例
-- 将student表修改为stu
ALTER TABLE student RENAME TO stu;
-- 添加一列address
ALTER TABLE stu ADD address varchar(50);
-- 将address列的数据类型改为char
ALTER TABLE stu MODIFY address char(50);
-- 将address修改为addr、数据类型为varchar
ALTER TABLE stu CHANGE address addr varchar(50);
-- 将addr列删除
ALTER TABLE stu drop addr;
DML操作数据
添加数据
- 语法
-- 给指定列添加数据
INSERT INTO 表名(列名1,列名2,…) VALUES(值1,值2,…);
-- 给全部列添加数据(注意:需要给出全部列的数据)
INSERT INTO 表名 VALUES(值1,值2,…);
-- 给指定列批量添加数据
INSERT INTO 表名(列名1,列名2,…) VALUES(值1,值2,…),(值1,值2,…),(值1,值2,…)…;
-- 给全部列批量添加数据(注意:需要给出全部列的数据)
INSERT INTO 表名 VALUES(值1,值2,…),(值1,值2,…),(值1,值2,…)…;
- 示例
-- 查询全部数据
SELECT * FROM stu;
-- 给id和name添加数据
INSERT INTO stu(id,name) VALUES (1,'张三');
-- 给全部列添加数据
INSERT INTO stu(id,name,gender,birthday,score,email,tel,status) VALUES (2,'李四','男','1988-08-08',95.88,'lisi@itcast.cn','18888888888',1);
-- 给全部列添加数据(简化列名书写)
INSERT INTO stu VALUES (3,'王五','女','1988-10-10',98.88,'wangwu@itcast.cn','18666666666',1);
-- 批量添加数据
INSERT INTO stu VALUES (4,'王五','女','1988-10-10',98.88,'wangwu@itcast.cn','18666666666',1);
INSERT INTO stu VALUES (5,'王五','女','1988-10-10',98.88,'wangwu@itcast.cn','18666666666',1);
修改和删除数据
1.修改数据
- 语法
-- 注意:必须加修改的条件、否则将所有数据都修改
UPDATE 表名 SET 列名1=值1,列名2=值2,… [WHERE 条件];
- 示例
-- 查询全部数据
SELECT * FROM stu;
-- 将张三的性别改为女(where后面的条件一般是唯一的一列)
UPDATE stu SET gender = '女' WHERE name = '张三';
-- 将李四的生日改为1999-09-09、成绩改为88.88
UPDATE stu SET birthday = '1999-09-09',score = 88.88 WHERE name = '李四';
2.删除数据
- 语法
-- 注意:必须加删除的条件、否则将所有数据都删除
DELETE FROM 表名 [WHERE 条件];
- 示例
-- 查询全部数据
SELECT * FROM stu;
-- 将张三这条记录删除
DELETE FROM stu WHERE name = '张三';
-- 将性别为女的全部删除
DELETE FROM stu WHERE gender = '女';
DQL查询
基础查询
1. 查询语法组成
- 分类
- 基础查询
- 条件查询(WHERE)
- 分组查询(GROUP BY)
- 排序查询(ORDER BY)
- 分页查询(LIMIT)
- 组成 [注意:语法有先后使用顺序]
SELECT
字段列表
FROM
表名列表
WHERE
条件列表
GROUP BY
分组字段
HAVING
分组后条件
ORDER BY
排序字段
LIMIT
分页限定
2.基础查询
- 语法
-- 查询多个字段
SELECT 字段列表 FROM 表名;
-- 查询所有数据
SELECT * FROM 表名;
-- 去除重复查询
SELECT DISTINCT 字段列表 FROM 表名;
-- 起别名(as 可以省略)
AS 别名
- 示例
/*
基础查询
*/
-- 将stu表删除后并重新创建,添加测试数据
DROP TABLE IF EXISTS stu;
CREATE TABLE stu(
id int, -- 编号
name varchar(20), -- 姓名
age int, -- 年龄
sex varchar(5), -- 性别
address varchar(100), -- 地址
math double(5,2), -- 数学成绩
english double(5,2), -- 英语成绩
hire_date date -- 入学时间
);
INSERT INTO stu VALUES
(1,'马运',55,'男','杭州',66,78,'1995-09-01'),
(2,'马花疼',45,'女','深圳',98,87,'1998-09-01'),
(3,'马斯克',55,'男','香港',56,77,'1999-09-02'),
(4,'柳白',20,'女','湖南',76,65,'1997-09-05'),
(5,'柳青',20,'男','湖南',86,null,'1998-09-01'),
(6,'刘德花',57,'男','香港',99,99,'1998-09-01'),
(7,'张学右',22,'女','香港',99,99,'1998-09-01'),
(8,'德玛西亚',18,'男','南京',56,65,'1994-09-02');
-- 查询全部
SELECT * FROM stu;
-- 查询name和age
SELECT name,age FROM stu;
-- 查询所有列数据(不使用*)
SELECT id,name,age,sex,address,math,english,hire_date FROM stu;
-- 查询address列
SELECT address FROM stu;
-- 查询address列,去除重复
SELECT DISTINCT address FROM stu;
-- 查询name、math、english 并起别名
SELECT name AS 姓名,math AS 数学成绩,english AS 英语成绩 FROM stu;
条件查询
1.条件查询语法
- 语法
SELECT 字段列表 FROM 表名 WHERE 条件列表;
-
条件分类
-
>、<、<=、>=、=、<> <>在 SQL 中表示不等于,在 mysql 中也可以使用!= 没有== BETWEEN…AND 在一个范围之内,如:between 100 and 200 相当于条件在 100 到 200 之间,包头又包尾 IN(集合) 集合表示多个值,使用逗号分隔 AND、&& 并且 OR、|| 或者 IS NULL 查询某一列为 NULL 的值,注:不能写=NULL LIKE ‘张%’ 模糊查询,配合占位符"_“,”%"使用
2.条件查询案例
-- 1.查询年龄大于20岁的学员信息
SELECT * FROM stu WHERE age > 20;
-- 2.查询年龄大于等于20岁的学员信息
SELECT * FROM stu WHERE age >= 20;
-- 3.查询年龄大于等于20岁 并且 年龄 小于等于 30岁 的学员信息
SELECT * FROM stu WHERE age >= 20 AND age <= 30;
SELECT * FROM stu WHERE age BETWEEN 20 AND 30;
-- 4.查询入学日期在'1998-09-01' 到 '1999-09-01' 之间的学员信息
SELECT * FROM stu WHERE hire_date BETWEEN '1998-09-01' AND '1999-09-01';
-- 5.查询年龄等于18岁的学员信息
SELECT * FROM stu WHERE age = 18;
-- 6.查询年龄不等于18岁的学员信息
SELECT * FROM stu WHERE age != 18;
-- 7.查询年龄等于18岁 或者 年龄等于20岁 或者 年龄等于22岁的学员信息
SELECT * FROM stu WHERE age = 18 OR age = 20 OR age = 22;
SELECT * FROM stu WHERE age IN (18,20,22);
-- 8.查询英语成绩为 null的学员信息
SELECT * FROM stu WHERE english is null;
SELECT * FROM stu WHERE english is not null;
模糊查询
模糊查询语法
- 语法
SELECT 列名列表 FROM 表名 WHERE 字段 LIKE 通配符;
- 通配符
- _ 代表单个任意字符
- % 代表任意个数字符
2.模糊查询案例
-- 1.查询姓'马'的学员信息
SELECT * FROM stu WHERE name LIKE '马%';
-- 2.查询第二个字是'花'的学员信息
SELECT * FROM stu WHERE name LIKE '_花%';
-- 3.查询名字中包含 '德' 的学员信息
SELECT * FROM stu WHERE name LIKE '%德%';
排序查询
排序查询语法
- 语法
SELECT 字段列表 FROM 表名 ORDER BY 排序字段名1 [排序方式1],排序字段名2 [排序方式2] …;
- 排序方式
- ASC 升序排列(默认)
- DESC 降序排列
- 注意事项
- 如果有多个排序条件,当前边的条件值一样时,才会根据第二条件进行排序
2.排序查询案例
-- 1.查询学生信息,按照年龄升序排列
SELECT * FROM stu ORDER BY age ASC;
-- 2.查询学生信息,按照数学成绩降序排列
SELECT * FROM stu ORDER BY math DESC;
-- 3.查询学生信息,按照数学成绩降序排列,如果数学成绩一样,再按照英语成绩升序排列
SELECT * FROM stu ORDER BY math DESC,english ASC;
聚合函数
聚合函数语法
- 聚合函数:将一列数据作为一个整体,进行纵向计算。
- 语法
SELECT 聚合函数名(列名) FROM 表;
- 分类
- 注意事项:null 值不参与所有聚合函数运算。
2.聚合函数案例
-- 1.统计班级一共多少个学生
SELECT count(*) FROM stu;
-- 2.查询数学成绩最高分
SELECT max(math) FROM stu;
-- 3.查询数学成绩最低分
SELECT min(math) FROM stu;
-- 4.查询数学成绩总分
SELECT sum(math) FROM stu;
-- 5.查询数学成绩平均分
SELECT avg(math) FROM stu;
-- 6.查询英语成绩最低分
SELECT min(english) FROM stu;
分组查询
分组查询语法
- 语法
SELECT 字段列表 FROM 表名 [WHERE 分组前条件限定] GROUP BY 分组字段名 [HAVING 分组后条件过滤];
- 注意事项
- 分组之后,查询的字段为聚合函数和分组字段,查询其他字段无任何意义
- where和having区别
- 执行时机不一样:where 是分组之前进行限定,不满足where条件,则不参与分组,而having是分组之后对结果进行过滤。
- 可判断的条件不一样:where 不能对聚合函数进行判断,having 可以。
- 执行顺序:where > 聚合函数 > having
2.分组查询案例
-- 1.查询男同学和女同学各自的数学平均分
SELECT sex,avg(math) FROM stu GROUP BY sex;
-- 注意:分组之后,查询的字段为聚合函数和分组字段,查询其他字段无任何意义
SELECT name,avg(math) FROM stu GROUP BY sex;
-- 2.查询男同学和女同学各自的数学平均分,以及各自人数
SELECT sex,avg(math),count(*) FROM stu GROUP BY sex;
-- 3.查询男同学和女同学各自的数学平均分,以及各自人数,要求:分数低于70分的不参与分组
SELECT sex,avg(math),count(*) FROM stu WHERE math > 70 GROUP BY sex;
-- 4.查询男同学和女同学各自的数学平均分,以及各自人数,要求:分数低于70分的不参与分组,分组之后人数大于2个的。
SELECT sex,avg(math),count(*) FROM stu WHERE math > 70 GROUP BY sex HAVING count(*) > 2;
分页查询
分页查询语法
- 语法
SELECT 字段列表 FROM 表名 LIMIT 起始索引 , 查询条目数;
- 计算公式
- 起始索引 = (当前页码-1) * 每页显示的条数
- 起始索引:从0开始。
- 小提示
- 分页查询 limit 是MySQL数据库的方言
- Oracle 分页查询使用 rownumber
- SQL Server分页查询使用 top
2.分页查询案例
-- 1.从0开始查询,查询3条数据
SELECT * FROM stu LIMIT 0,3;
-- 2.每页显示3条数据,查询第1页数据
SELECT * FROM stu LIMIT 0,3;
-- 3.每页显示3条数据,查询第2页数据
SELECT * FROM stu LIMIT 3,3;
-- 4.每页显示3条数据,查询第3页数据
SELECT * FROM stu LIMIT 6,3;
查询总结
- 查询语法 [注意:语法有先后顺序]
SELECT
字段列表
FROM
表名列表
WHERE
条件列表
GROUP BY
分组字段
HAVING
分组后条件
ORDER BY
排序字段
LIMIT
分页限定