MySQL数据模型
-
关系型数据库
关系型数据库是建立在关系模型基础上的数据库,简单说,关系型数据库是由多张能互相连接的二维表组成的数据库
- 优点
-
都是使用表结构,格式一致,易于维护
-
使用通用的SQL语言操作,使用方便,可用于复杂查询
-
数据存储在磁盘中,安全
-
- 优点
SQL通用语法
- SQL语句可以单行或多行书写,以分号结尾
- MySQL数据库的SQL语句不区分大小写,关键字建议使用大写
- 注释
- 单行注释:-- 注释内容 或 #注释内容(MySQL特有)//-- 后面必须有空格
- 多行注释:/* 注释内容 */
SQL分类
DDL
操作数据库
- 查询
SHOW DATABASES;
- 创建
- 创建数据库
CREATE DATABASE 数据库名称;
- 创建数据库(判断,如果不存在则创建)
CREATE DATABASE IF NOT EXISTS 数据库名称;
删除
- 删除数据库
DROP DATABASE 数据库名称;
- 创建数据库(判断,如果不存在则创建)
CREATE DATABASE IF NOT EXISTS 数据库名称;
- 使用数据库
- 使用当前使用的数据库
SELECT DATABASE();
- 使用数据库
USE 数据库名称;
操作表
查询表
- 查询当前数据库下的所有表名称
SHOW TABLES;
- 查询表结构
DESC 表名称;
创建表
CREATE TABLE 表名(
字段名1 数据类型1,
字段名2 数据类型2,
...
字段名n 数据类型n
);
例:
create table tb_user(
id int,
username varchar(20),
password varchar(30)
);
create table student(
id int,
name varchar(10),
gender char(1),
birthday date,
score double(5,2),
email varchar(64),
tel varchar(15),
status tinyint
);
1.删除表
DROP TABLE 表名;
例:
drop table tb_user;
- 删除表时判断表是否存在
DROP TABLE IF NOT EXISTS 表名;
例:
drop table if not exists tb_user;
修改表
-
修改表名
ALTER TABLE 表名 RENAME TO 新的表名;
例:
alter table tb_user rename to tbuser;
-
添加一列
ALTER TABLE 表名 ADD 列名 数据类型;
例:
alter table tb_user add sex char(1);
-
修改数据类型
ALTER TABLE 表名 MODIFY 列名 数据类型;
例:
alter table tb_usesr modify id varchar(20);
-
修改列名和数据类型
ALTER TABLE 表名 CHANGE 列名 新列名 新数据类型;
例:
alter table tb_user change id stu_id varchar(20);
-
删除列
ALTER TABLE 表名 DROP 列名;
例:
alter table tb_user drop id;
DML
添加数据
-
给指定列添加数据
INSERT INTO 表名(列名1,列名2,…) VALUES(值1,值2,…);
例:
insert into tb_user(id,username,password) values(1,zhangsan,123456);
-
给全部列添加数据
INSERT INTO 表名 VALUES(值1,值2,…);
例insert into tb_user values(2,lisi,12345678);
-
批量添加数据
INSERT INTO 表名(列名1,列名2,…) VALUES(值1,值2,…),(值1,值2,…),(值1,值2,…);
例:
insert into tb_user(id,username,password) values(3,wangwu,12345),(5,zhaoliu,1234);
INSERT INTO 表名 VALUES(值1,值2,…),(值1,值2,…),(值1,值2,…);
例:
insert into tb_user values(6,mazi,111111),(7,wanger,22222);
修改数据
-
修改表数据
UPDATE 表名 SET 列名1=值1,列名2=值2,…[WHERE 条件];
例:
update tb_user set username = lisi , password = 111111 where id = 1;
注意:修改语句中如果不加条件,则将表中所有的数据都修改!!!
删除数据
-
删除数据
DELETE FROM 表名 [WHERE 条件];
例:
delete from tb_user where id = 1;
注意;删除语句中如果不加条件,则将表中所有数据都删除!!!
DQL
基础查询
-
查询多个字段
SELECT 字段列表 FROM 表名;
SELECT * FROM 表名; – 查询所有数据
例:
select * from tb_user;
-
去除重复记录
SELECT DISTINCT 字段列表 FROM 表名;
例:
select distinct id from tb_user;
-
起别名
AS :AS 也可以省略
例:
select id as "编号",username as “姓名” from tb_user;
条件查询
-
条件查询语法
SELECT 字段列表 FROM 表名 WHERE 条件列表;
例:
select * from tb_user where id >= 1;
-
条件
-
基础查询练习
- 查询年龄大于20岁的学员信息
select * from stu where age > 20;
- 查询年龄大于等于20岁的学员信息
select * from stu where age >= 20;
- 查询年龄大于等于20岁并且年龄小于等于30岁的学员信息
select * from stu where age >= 20 and age <=30;
或
select * from stu where age >= 20 && age <=30;
- 查询入学日期在‘1998-09-01’ 到 ’1999-09-01‘之间的学员信息
select * from stu where hire_date between '1998-09-01' and '1999-09-01'
- 查询年龄等于18岁的学员信息
select * from stu where age = 18;
- 查询年龄不等于18岁的学员信息
select * from stu where age != 18;
或
select * from stu where age <> 18;
- 查询年龄等于18岁 或者 年龄等于20岁 或者 年龄等于22岁的学员
select * from stu where age = 18 or age = 20 or age = 22;
或
select * from stu where age in (18,20,22)
- 查询英语成绩为null的学员信息
select * from stu where english is null;
-
模糊查询联系
/*
通配符:
(1)_:代表单个任意字符
(2)%:代表任意个数字符
*/
- 查询性’马‘的学员信息
select * from stu where name like '马%'
- 查询第二个字是’花’的学员信息
select * from stu where name like "_花%"
- 查询名字中包含‘德’的学员信息
select * from stu where name like "%德%"
排序查询
-
排序查询语法
SELECT 字段列表 FROM 表名 ORDER BY 排序字段名1[排序方式1],排序字段名2[排序方式2]…;
例:
select * from stu order by id desc;
排序方式:
- ASC:升序排列(默认值)
- DESC:降序排列
注意:如果有多个排序条件,当前边的条件值一样时,才会根据第二条件进行排序
-
排序查询练习
1.查询学生信息,按照年龄升序排列
select * from stu order by age;
- 查询学生信息,按照数学成绩降序排列
select * from stu order by math desc;
- 查询学生信息,按照数学成绩降序排列,如果数学成绩一样,在按照英语成绩升序排列
select * from stu order by math desc , english asc;
聚合函数
-
概念:
将一列数据作为一个整体,进行纵向计算。
-
聚合函数分类:
-
聚合函数语法:
SELECT 聚合函数名(列名) FROM 表;
例:
select avg(age) from stu;
注意:null值不参与所有聚合函数运算
-
聚合函数练习
- 统计班级一共有多少个学生
select count(id) from stu;
select count(*) from stu;
- 查询数学成绩的最高分
select max(math) from stu;
- 查询数学成绩的最低分
select min(math) from stu;
- 查询数学成绩的总分
select sum(math) from stu;
- 查询数学成绩的平均分
select avg(math) from stu;
- 查询英语成绩的最低分
select min(english) from stu;
分组查询
-
分组查询语法
SELECT 字段列表 FROM 表名 [WHERE 分组前条件限定] GROUP BY 分组字段名 [HAVING 分组后条件过滤]
例:
select * from stu where age >18 group by sex having sex = '男';
注意:分组之后,查询的字段为聚合函数和分组字段,查询其他字段无意义
where和having区别:
- 执行时机不一样:where是分组之前进行限定,不满足where条件,则不参与分组,而having是分组之后对结果进行过滤。
- 可判断的条件不一样:where不能对聚合函数进行判断,having可以。
执行顺序:where > 聚合函数 > having
-
分组查询练习
1.查询男同学和女同学各自的数学平均分
select sex,avg(math) from stu group by sex ;
- 查询男同学和女同学各自的数学平均分,以及各自人数
select sex,avg(math),count(*) from stu group by sex ;
- 查询男同学和女同学各自的数学平均分,以及各自人数,要求:分数低于70分的不参与分组
select sex,avg(math),count(*) from stu where math > 70 group by sex ;
- 查询男同学和女同学各自的数学平均分,以及各自人数,要求:分数低于70分的不参与分组,分组之后人数大于2
select sex,avg(math),count(*) from stu where math > 70 group by sex having count(*) > 2 ;
分页查询
-
分页查询语法
SELECT 字段列表 FROM 表名 LIMIT 起始索引,查询条目数;
例:
select * from stu limit 0,5;
- 起始索引:从0开始
- 计算公式:起始索引=(当前页码-1)* 每页显示的条数
tips:
- 分页查询limit是MySQL数据库的方言
- Oracle分页查询使用rownumber
- SQL Server分页查询使用top
-
分页查询练习
- 从0开始查询,查询3条数据
select * from stu limit 0,3;
- 每页显示3条数据,查询第1页数据
select * from stu limit 0,3;
- 每页显示3条数据,查询第2页数据
select * from stu limit 3,3;
- 每页显示3条数据,查询第3页数据
select * from stu limit 6,3;