一、数据库相关概念
1.1、数据库
存储数据的仓库,数据是有组织的进行存储。简称DB
1.2、数据库管理系统
管理数据库的大型软件。简称DBMS
1.3、SQL
结构化查询语言,简称SQL
操作关系型数据库的编程语言
定义操作所有关系型数据库的统一标准
二、MySQL数据模型
2.1、关系型数据库
关系型数据库是建立在关系模型上的数据库,简单说,关系型数据库是由多张能互相连接的二维表组成的数据库。
优点:
1.都是使用表格结构,格式一致,易于维护
2.使用通用的SQL语言操作,使用方便,可用于复杂查询
3.数据存储在磁盘中,安全
三、SQL通用语法
1. SQL可以单行或多行书写,以分好结尾
- MySQL数据库的SQL语句不区分大小写,关键字建议使用大写
- 注释
单行注释:-- 注释内容 或 #注释内容(MySQL特有)
多行注释:/* 注释 */
四、SQL分类
DDL(Data Definition Language):数据定义语言,用来定义数据对象:数据库,表,列等
DML(Data Manipulation Language):数据操作语言,用来对数据库中表的数据进行增删改
DQL(Data Query Language):数据查询语言,用来查询数据库中表的记录
DCL(Data Control Language):数据控制语言,用来定义数据库的访问权限和安全级别,及创建用户
4.1、DDL
4.1.1、DDL–操作数据库
1.查询
SHOW DATABASES;
2.创建
创建数据库
CREATE DATABASE 数据库名称;
创建数据库(判断,如果不存在就创建)
CREATE DATABASE IF NOT EXISTS 数据库名称;
3.删除
删除数据库
DROP DATABASE 数据库名称;
删除数据库(判断,如果存在则删除)
DROP DATABASE IF EXISTS 数据库名称;
4.使用数据库
查看当前使用的数据库
SELECT DATABASE();
使用数据库
USE 数据库名称;
4.1.2、DDL–操作表
1.查询
查询当前数据库下所有表名称
SHOW TABLES;
查询表结构
DESC 表名称;
2.创建表
CREATE TABLE 表名(
字段名1 数据类型1,
字段名2 数据类型2,
字段名3 数据类型3,
…
字段名n 数据类型n
);
注意:最后一行末尾,不能加逗号
3.删除表
删除表
DROP TABLE 表名;
删除表时判断表是否存在
DROP TABLE IF EXISTS 表名;
4.修改表
修改表名
ALTER TABLE 表名 RENAME TO 新的表名
添加一列
ALTER TABLE 表名 ADD 列名 数据类型;
修改数据类型
ALTER TABLE 表名 MODIFY 列名 新数据类型;
修改列名和数据类型
ALTER TABLE 表名 CHANGE 列名 新列名 新数据类型;
删除列
ALTER TABLE 表名 DROP 列名;
4.1.3、DDL–数据类型
分为3类:
1.数值
2.日期
3.字符串
详情见同文件夹下excel表
小练习
需求:设计一张学生表,请注重数据类型、长度的合理性
1.编号
2.姓名,姓名最长不超过10个汉字
3.性别,因为取值只有两种可能,因此最多一个汉字
4.生日,取值为年月日
5.入学成绩,小数点后保留两位
6.邮件地址,最大长度不超过64
7.家庭联系电话,不一定是手机号码,可能会出现–等字符
8.学生状态(用数字表示,正常、休学、毕业…)
CREATE TABLE student1(
id int,
name varchar(20),
gender char(2),
birthday date,
score double(5,2),
emile varchar(64),
phonenum varchar(20),
status tinyint
);
4.2、DML
4.2.1、DML–添加(INSERT)
添加数据
给指定列添加数据
INSERT INTO 表名(列名1,列名2,…列名n) VALUES(值1,值2,…值n);
给全部列添加数据
INSERT INTO 表名 VALUES(值1,值2,…);
批量添加数据
INSERT INTO 表名(列名1,列名2,…列名n) VALUES(值1,值2,…),(值1,值2,…),(值1,值2,…);
INSERT INTO 表名 VALUES(值1,值2,…),(值1,值2,…),(值1,值2,…);
4.2.2、DML–修改(UPDATE)
修改数据
修改表数据
UPDATE 表名 SET 列名1=值1,列名2=值2,… [WHERE 条件];
注意:修改语句中如果不加条件,则将所有数据都修改
4.2.3、DML–删除(DELETE)
删除数据
删除数据
DELETE FROM 表名 [WHERE 条件];
注意:删除语句中如果不加条件,则将所有数据都删除!
4.3、DQL
创建一张表
--创建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,'萧炎',18,'男','斗气大陆',86,NULL,'1998-09-01'),
(6,'唐三',18,'男','斗罗大陆',99,99,'1998-09-01'),
(7,'叶凡',24,'男','地球',99,99,'1998-09-01'),
(8,'唐舞麟',16,'女','斗气大陆',56,65,'1999-09-01');
4.3.1、DQL–基础查询
1.查询多个字段
查询指定字段列表
SELECT 字段列表 FROM 表名;
查询所有数据
SELECT * FROM 表名;
去除重复
SELECT DISTINCT 列名 FROM 表名;
输出结果对列名起别名
SELECT 列名1 as 别名1,列名2 as 别名2,… FROM 表名;
**尽量不使用 ***
4.3.2、DQL–条件查询
1.条件查询语法
SELECT 字段列表 FROM 表名 WHERE 条件列表;
2.条件
符号 | 功能 |
> | 大于 |
> | 小于 |
>= | 大于等于 |
<= | 小于等于 |
= | 等于 |
<>或!= | 不等于 |
BETWEEN…AND… | 在某个范围之内(都包含) |
IN(…) | 多选一 |
LIKE 占位符 | 模糊查询,_:单个任意字符、%:多个任意字符 |
IS NULL | 为空 |
IS NOT NULL | 不为空 |
AND 或 && | 并且 |
OR 或 || | 或者 |
NOT 或 ! | 非,不是 |
小练习:
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;
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;
注意:NULL值无法使用 = 或 != 来进行比较,需要使用IS NULL和IS NOT NULL
模糊查询
1.查询姓‘王’的学员信息
SELECT * FROM stu WHERE name LIKE ‘王%’;
2.查询第二个字是’三’的学员信息
SELECT * FROM stu WHERE name LIKE ‘_三%’
3.查询名字中包含’凡’的学员信息
SELECT * FROM stu WHERE name LIKE ‘%凡%’;
4.3.3、DQL–排序查询
1.排序查询语法
SELECT 字段列表 FROM 表名 ORFER BY 排序字段名1 [排序方式1],排序字段2 [排序方式2]…;
2.排序方式
ASC:升序排列(默认值)
DESC:降序排列
注意:如果有多个排序条件,当前边的条件值一样时,才会根据第二条件进行排序
小练习:
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;
4.3.4、DQL–聚合函数
1.概念
将一列数据作为一个整体,进行纵向计算
2.聚合函数分类
函数名 | 功能 |
COUNT(列名) | 统计数量(一般选用不为null的列) |
MAX(列名) | 最大值 |
MIN(列名) | 修小值 |
SUM(列名) | 求和 |
AVG(列名) | 平均值 |
3.聚合函数语法
SELECT 聚合函数名(列名) FROM 表;
小练习:
1.查询班级一共有多少个学员
SELECT COUNT(id) FROM stu;
COUNT只能统计到非空数据,表中的null值无法被统计,可尝试下面的查询语句
SELECT COUNT(english) FROM stu;
在不知道那列数据存在null值时,可在括号中使用 * 查询所有列
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;
注意:null值不参与所有聚合函数运算
4.3.5、DQL–分组查询
1.分组查询语法
SELECT 字段列表 FEOM 表名 [WHERE 分组前条件限定] GROUP BY 分组字段名 [HAVING 分组后条件过滤];
注意:分组之后,查询的字段为聚合函数和分组字段,查询其他字段无任何意义
WHERE 和 HAVING区别:
1.执行的时机不一样,WHERE是分组之前进行限定,不满足WHERE条件,则不参与分组,而HAVING是分组之后对结果进行过滤。
2.可判断的条件不一样,WHERE不能对聚合函数进行判断,HAVING可以
执行顺序:WHERE > 聚合函数 > HAVING
小练习:
1.查询男学员和女学员各自的数学平均分
SELECT sex,AVG(math) FROM stu GROUP BY sex;
2.查询男学员和女学员各自的数学平均分,以及各自人数
SELECT sex,AVG(math),COUNT(id) FROM stu GROUP BY sex;
3.查询男学员和女学员各自的数学平均分,以及各自人数,要求:分数低于70分的不参与分组
SELECT sex,AVG(math),COUNT(id) FROM stu WHERE math > 70 GROUP BY sex;
4.查询男学员和女学员各自的数学平均分,以及各自人数,要求:分数低于70分的不参与分组,分组后人数大于2个的
SELECT sex,AVG(math),COUNT(id) FROM stu WHERE math > 70 GROUP BY sex HAVING COUNT(*) > 2;
4.3.6、DQL–分页查询
1.分页查询语法
SELECT 字段列表 FROM 表名 LIMIT 起始索引,查询条目数;
起始索引:从0开始
计算公式:起始索引=(当前页码-1) * 每页显示的条数
tip:
- 分页查询LIMIT是MySQL数据库的方言
- Oracle分页查询使用rownumber
- SQLServer分页查询使用top
小练习:
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;