一,SQL 语句的分类:
1. DDL(Data Definition Language) 数据定义语言
建库,建表等操作
create / alter / drop
2. DML 数据操作语言
对数据进行增删改操作
insert / update / delete
3. DQL 数据查询语言
查询数据
select / show
4. DCL 数据控制语言
grant / revoke
对用户权限管理,数据库管理员使用多。
5.MySQL 中三种注释:
-- 注释的内容 单行注释(有空格)
/* */ 多行注释 (SQL 语句标准写法)
# 单行注释 (MySQL 特有)
二,MySQL 管理数据库
1. 查看所有数据库
show databases;information_schema: 数据库中的基本数据
test:演示的数据库
2.创建数据库
create database 数据库名;
● 使用指定的字符集
create database 数据库名 default character set 字符集;
●显示数据库的创建语句
show create database 数据库名;
● 什么是校对规则?
在同一种字符集,如 GBK,字符的排序规则不同,排序的结束也不同。
GBK 中有二进制的编码,可以按二进制的方式进行排序。
也可以使用汉字拼音的方式进行排序。不同的排序规则,称为校对规则。
每种字符集有多种校对规则。
● 查看字符集和校对规则:
show collation like 'gbk%';
● 指定数据库的默认字符集为 gbk 和校对规则 gbk_bin
create database db3 default character set gbk collate gbk_bin;
create database 数据库名 default character set 字符集 collate 校对规则;
3.删除数据库
drop database 数据库名;
4.修改数据库默认字符集为utf-8
alter database db2 default character set utf8;
三,表的管理
1.表的操作
1.选择数据库
2.查看数据库中的所有表
3.创建一个student表,有 id(int), name(varchar), birthday(date) 类型
2.常用数据类型
1. 数值类型
整数:● BIT 占 1 位,只有 2 个位 1 或 0
● TINYINT 或 BOOLEAN 微整型,占 1 个字节。8 位。 UNSIGNED(无符号)
有符号-128-127,无符号 0-255
● SMALLINT 小整型,占 2 个字节。 64K
● INT 或 INTEGER 整型,占 4 个字节,32 位。16M
● BIGINT 大整型,占 8 个字节,64 位。4G
小数:
● FLOAT[(M,d)] 单精度
● DOUBLE[(M,d)] 或 REAL 双精度
● DECIMAL[(M[,D])] [UNSIGNED] [ZEROFILL] 自定义精确,M 总长,D 小数位数 如:decimal(10,3)
2. 文本、二进制类型
● CHAR(SIZE): 字符串(定长型)char(10) 每一行这个值都是 10 个字符
● VARCHAR(SIZE):
varchar(20) 可变长的字符串,使用几个字符就占用几个。
● BLOB:
Binary Large Object: 二进制的大对象
TINYBLOB | 微 | 255 |
BLOB | 正常 | 64K |
MEDIUMBLOB | 中等 | 16M |
LONGBLOB | 长 | 4G |
● TEXT:
(CLOB)
Character Large Object :文本大对象
其中
TINYTEXT 微 255
TEXT 正常 64K
MEDIUMTEXT 中等 16M
LONGTEXT 长 4G
3 .日期和时间类型
● DATE : 日期● TIME: 时间
● DATETIME: 日期和时间
3.查看一个表结构
1.以SQL格式返回,查看student表
2.以表格格式返回
3.如何快速构建相同表结构
4.删除表
1.删除s1这张表
2.同时删除s2 和s3 两张表
5.修改表
1.添加字段:add
2.修改字段类型:modify
3.修改字段名称:change
4.删除字段:drop
5.修改表名:rename
四,管理数据:数据增删改的操作
1.插入数据
1. 插入所有列
1) 值与数据类型要相同
2) 长度不能大于建表的列的长度,如果超出,则会自己截断。
3) 表中有多少列,值也就是多少列
4) 字符和日期型格式:使用单引号。在 MySQL 中可以使用双引号,不建议。
5) null:插入一个空的值
2.插入部分列
2.修改数据
1.语法
2.修改所有的记录
3.修改符合某些条件的一列数据(用的比较多)
4.修改多个列(set 列明=值,列名=值...)
3.删除数据
1.删除表中的所以数据(用的比较少)
2.删除部分行的数据
3.删除所有数据
五.查询数据(重点)
1.查询所有列
2.查询指定列
3.查询时指定别名(as)
4.合并列查询
5.查询时添加常量列
6.去除重复数据(distinct)
select distinct(resume) from student;
7.条件查询
1. 比较运算符
● > 、 < 、 <= 、 >=、 =、 <> (不等于类似 !=)-- 查询所有数学大于 80
select * from student where math > 80;
● BETWEEN...AND
在一个范围之内,包头包尾
select * from student where math between 88 and 99;
● IN
查询的条件,在几个数值中
-- 查询 id 是 1,3,5
select * from student where id in(1,3,5);
● LIKE 模糊查询
% 匹配任意的字符
_ 匹配一个字符
-- 查询所有带“白”字学生
select * from student where name like '%白%';
-- 查询姓“白”
select * from student where name like '白%';
-- 查询白结尾
select * from student where name like '%白';
-- 以白结尾,只有 2 个字姓名
select * from student where name like '_白';
● IS NULL
为空的条件
-- 查询所有性别为 null 的学生
select * from student where gender is null;
2. 逻辑条件: and or not(与,或,非)
-- 数学大于 90,英语大于 90select * from student where math > 90 and english > 90;
-- 数学大于 90 或英语大于 90
select * from student where math > 90 or english > 90;
-- 查询性别不为空的学生
select * from student where gender is not null;
3. 判空条件:
1) 判断是否为空串=''
2) 判断是否为空
is null
3) null 和 空字符串的区别:
null 没有数据
空串有数据,空字符串
8.order by 子句排序查询结果
1. order by 的作用:
1) order by :对表中指定的列进行排序2) 升序: asc 默认,可以省略
降序:desc
3) 位置:放在查询语句的最后面
2. 示例:
-- 1) 对数学成绩从小到大排序后输出select * from student order by math asc;
select * from student order by math;
-- 2) 对总分按从高到低的顺序输出
select *,(math+english) as 总分 from student order by 总分 desc;
-- 3) 查所有广州的学生成绩从小到大排序输出
select *,(math+english) as 总分 from student where resume='广州' order by 总分;
-- 4) 按英语成绩的降序排序,成绩相同的,再按数学升序排序
select * from student order by english desc, math asc;
9.聚合查询
sum() 求和
avg() 求平均
max() 最大值
min() 最小值
count() 统计个数
-- 1. 需求: 查询所有学生 english 的总分
select sum(english) as 英语总分 from student;
-- 2. 需求: 查询所有学生 english 的平均分
select avg(english) as 英语平均分 from student;
-- 3. 需求:查询最高的 english 分数
select max(english) as 最高分 from student;
-- 4. 需求:查询最低的 english 分数
select min(english) as 最低分 From student;
-- 5. 需求: 一共几个学生
select * from student;
select count(*) as 总数 from student;
-- 如果表的列数多,行数多,使用*,速度会比较慢
select count(id) from student; -- 主键
select count(resume) from student;
10.分页查询
好处:
1) 减少网络的压力
2) 提高显示的速度
3) 提高用户体验
1. 格式:
limit 起始行数(从 0 开始), 返回的行数,写在 select 最后
2. 需求:分页查询 共 5 条数据,每页显示 2 条数据,共 3 页
/*
需求:分页查询 共 5 条数据,每页显示 2 条数据,共 3 页
*/
# 需求: 查询第 1,2 条数据(第 1 页数据)
select * from student limit 0,2;
# 需求: 查询第 3,4 条数据(第 2 页数据)
select * from student limit 2,2;
# 需求: 查询第 5 条数据(第 3 页数据)
select * from student limit 4, 2;
11.分组查询
1. 分组查询(GROUP BY)
● 需求: 查询男女的数量各自多少分步:
1). 对性别进行分组(group by)
2). 在分组基础上进行聚合函数总计
-- 需求: 查询男女的数量各自多少
select count(gender) as 男 from student where gender='男';
select count(gender) as 女 from student where gender='女';
-- group by
select gender as 性别, count(gender) as 个数 from student group by gender;
2、分组筛选(group by + having(条件))
1) 需求:查询各地区的人数2) 需求: 查询人数大于 2 或等于地区
1)分组 2)统计 3)条件
-- 查询各地区的人数
select resume, count(resume) from student group by resume;
-- 查询人数大于 2 或等于地区
12.数据库的备份与恢复
1.1 备份数据库(导出数据库中所有的表和数据)
语法格式:
mysqldump -u 用户名 -p 密码 数据库 > d:/sql 文件
示例:备份 day21 中表
mysqldump -uroot -proot day21 > d:/day21.sql
1.2 还原数据库(导入)
方式一:在 Windows 命令行中使用 mysql 命令
语法格式: mysql -u 用户名 -p 密码 数据库 < sql 文件
示例:
mysql -uroot -proot day21 < d:/day21.sql
方式二:在 MySQL 命令行中使用 source 命令把 sql 文件导入到当前数据库中;
语法格式: source 文件名;
注:如果使用 source 导入表,首先要先选择数据库。 use 数据库;
提示:通过 help 查看所有的 mysql 的命令
如: source d:/day21.sql;
2. 方法二:通过 SQLyog 工具:
2.1. 导出:
在数据库上点右键 --> 导出/备份
2.2 导入:
在数据库上点右键 --> 执行 -> SQL 脚本
六,约束
1. 数据约束概述
1. 什么是数据约束1) 数据类型的作用:在一定程度上保证了数据的正确性
年龄: 整型
姓名: 字符串
2) 数据的约束:
约束作用:为了保证插入到表中的数据是正确的,我们必须给表添加约束。
约束一般在创建表结构的时候来指定
2. 约束种类
通用的约束:
非空、默认、唯一、主键、外键(难点)、检查(MySQL 不支持)
2. 默认值约束 (default)
1. 什么是默认约束:如果某一列指定了默认约束,如果没有给这一列插入记录,则会使用默认约束。
2. 示例:创建一个学生表 s1,字段:(编号,姓名,地址(默认值是:广州)),插入 2条记录,地址使用默认值。
3. 插入默认值的写法:
1) 写法一:只插入前面 2 列,第 3 列不写
2) 写法二:VALUES 前面的列名不写,第 3 列使用 DEFAULT 关键字
3) 如果第 3 列使用 NULL 的常量,会不会插入默认值呢? 不会,插入一个 null
create table s1 (
id int,
name varchar(20),
address varchar(20) default '广州'
)
select * from s1;
-- 使用默认值
insert into s1 (id,name) values (1,'刘备');
insert into s1 values (2,'张飞', '深圳');
-- 使用默认值
insert into s1 values (3,'关羽', default);
3. 非空约束 (not null)
某一列必须有数据,不能为空
2. 示例:创建表学生表 s2,字段(id,name, gender),其中姓名不能为 null
3. 插入测试数据:
1)第 2 列不插入数据
2)第 2 列直接写入 NULL
create table s2 (
id int,
name varchar(20) not null,
gender char(1) default '男'
)
select * from s2;
-- Column 'name' cannot be null
insert into s2 (id,name,gender) values (1, null, '男');
-- Field 'name' doesn't have a default value
insert into s2 (id,gender) values (1,'男');
4. 唯一约束 (unique)
1. 唯一:表中某列的值是唯一的,不能重复。
2. 示例:创建学生表 s3,列(id,name),学生姓名这一列设置成唯一约束,即不能出现同名的学生。
3. 测试数据:
问:出现多个 null 的时候会怎样? 可以出现多个 null,因为 null 是表示没有数据。
create table s3(
id int,
name varchar(20) unique
)
select * from s3;
insert into s3 (id, name) values (1, '张三') ;
-- 错误代码: 1062 Duplicate entry '张三' for key 'name'
insert into s3 (id, name) values (2, '张三疯') ;
5. 主键约束 (primary key )
1) 主键的作用:用来唯一标识表中的一条记录,每条记录的主键都是唯一的值。不出现重复
2) 主键的特点:唯一约束+非空约束
只要在表中创建了主键,自动创建唯一索引
2. 问题:哪个字段应该作为表的主键?
正常情况下可以使用表中不会重复的列做为主键,如:学号,身份证号,车辆编号
原则:不建议使用与记录中业务相关的列做为主键列。主键一般不建议去修改或删除。使用一个与业务无关的列做为主键。
主键的使用者:不是最终的用户,而是程序员。主键有没有含义并不重要。
如:随机产生的一个字符串做为主键。
3. 示例:创建表学生表 s4(id, name)将 id 做为主键
4. 测试数据:
1) 唯一:插入重复的主键值
2) 非空:插入 NULL 的主键值
- 创建表学生表 s4(id, name)将 id 做为主键
create table s4 (
id int primary key, -- 唯一,非空
name varchar(20) unique
)
-- 1) 唯一:插入重复的主键值
insert into s4 values (1,'张三');
-- 错误代码: 1062 Duplicate entry '1' for key 'PRIMARY'
insert into s4 values (1,'李四');
select * from s4;
-- 2) 非空:插入 NULL 的主键值
-- 错误代码: 1048 Column 'id' cannot be null
insert into s4 values (null, '王五');
6. 自增长字段( auto_increment 不是约束)
1. 什么是自增长字段:1) 特点: 可以指定某个整数列值每次都加 1,让这一列不出现重复值。往往用在主键列上
2).AUTO_INCREMENT
1> 作用: 让指定的这一列数值自动增长
2> 修改起始值:
alter table 表名 auto_increment = 起始值
3). ZEROFILL:0 填充
用于整数显示,如果整数的位数没有达到指定位数的话,会使用 0 进行填充。
位置放在数据类型的后面
4). 示例:创建学生表 s5,id为整数,长 4位,零填充,主键,自动增长。名字 varchar(20),
唯一约束
create table s5 (
id int primary key auto_increment,
name varchar(20) unique
)
-- 修改起始值
alter table s5 auto_increment = 1000;
desc s5;
-- 插入三条记录
insert into s5 (name) values('孙悟空'),('猪八戒'),('牛魔王');
select * from s5 order by id;
-- 删除一条记录
delete from s5 where id=2;
delete from s5 where id=2;
delete from s5 where id='0002';
delete from s5 where id=0002;
insert into s5(name) values('铁扇公主');
drop table s5;
-- int(4) 并不是表示长度只有 4 位,显示为 4 位,长度还是 11 位。最高位是+/-,数字长度 10 位
create table s5 (
id int(4) zerofill primary key auto_increment,
name varchar(20) unique
)
2. 删除所有的记录
对比 delete 和 truncate 的区别有什么区别?
1) delete 并不会重置自动增长的主键值
2) truncate 会重置主键,重新从 0 开始。
七,多表操作
1. 外键约束
constraint 约束名 foreign key (当前表的外键列) references 主表(主表中主键)
简写:
foreign key (当前表的外键列) references 主表(主表中主键)
-- 没有名字,但系统会自动分配一个名字
2.注意事项
2.级联操作
级联更新:如果更新主表,则从表相应的数据也自动同步更新。 on update cascade
级联删除:如果删除主表中记录,从表中的记录相应删除。on delete cascade
语法:
3.表与表之间的关系
2. 多对多(如:学生和课程)
3. 一对一 (使用少)
八,表连接查询(多表查询,重点)
1.交叉连接(笛卡尔积)
特点:左表中每一行与右表中的每一行都匹配一次,在实际应用中并不多,因为数据没有太多含义。查询员工表和部门表中所有的列:
select employee.name, depart.name from employee,depart;
2.内连接查询
1. 内连接:
语法:表
语法:表 1 inner join 表 2 on 条件从表中外键与主表中的主键相等,做为查询条件
2. 方式一:
1) 进行交叉连接
2) 在结果上进行过滤,从表中的外键与主表中的主键相等。
select employee.name as 员工名, depart.name 部门名 from employee,depart where employee.depart_id = depart.id;
3. 方式二:
直接写内连接
语法:表 1 inner join 表 2 on 条件
select e.name 员 工 名 , d.name 部 门 名 from employee e inner join depart d on e.depart_id = d.id;
3.左外连接
特点:左表中所有的记录都会出现,如果右表中没有对应的记录,则右表中使用 null进行填充
4.右外连接
语法:表 1 right [outer] join 表 2 on 外键=主键特点:右表中所有的记录都会出现,如果左表中没有对应的记录,则左表中使用 null进行填充
5. 自连接
-- 多级员工表(自关联)
create table emp2 (
id int primary key,
name varchar(20),
boss_id int, -- 外键
foreign key(boss_id) references emp2(id)
)
-- 插入记录
insert into emp2 values (1,'张三',null),(2,'李四',1),(3,'王五',1),(4,'赵六',2),(5,'田七',3);
select * from emp2;
-- 查询所有员工名和上司名
select e.name as 员工名, b.name 上司名 from emp2 e left join emp2 b on e.boss_id= b.id;
九,子查询
特点:1. 一个查询的结果做为另一个查询的条件
2. 有查询的嵌套,外面的查询称为父查询,内部的查询称为子查询
1.子查询的三种情况:
1>子查询是单行单列的情况:
父查询使用:子查询是单行单列的情况,父查询的运算符使用比较运算符:= 、>、 <、<>
select * from employee where salary = (select max(salary) from employee);
2>子查询是多行单列的情况:
select * from employee where dept_id in (select id from dept where name in('开发部','财务部'));
3>子查询是多行多列的情况 :
需求:查询 2011 年以后入职的员工信息和部门信息
● 使用子查询:
1) 查询出 2011 年以后入职的员工信息
2) 查询所有的部门信息,与上面的虚拟表中的信息比对,找出所有部门 ID 相等的员工。
-- 1) 查询出 2011 年以后入职的员工信息
select * from employee where join_date >= '2011-1-1';
-- 2) 查询所有的部门信息,与上面的虚拟表中的信息比对,找出所有部门 ID 相等的员工。
select e.*, d.* from dept d, (select * from employee where join_date >= '2011-1-1') e where d.id = e.dept_id
● 使用表连接:
select * from employee e inner join dept d on e.dept_id = d.id where join_date >='2011-1-1';
select * from employee e inner join dept d where e.dept_id = d.id and join_date >='2011-1-1';
十,数据库设计
1NF
1. 概念:表中的每一列都不可再拆分成更小的列,满足原子性。
2NF
1. 概念:不产生局部依赖,一般用于使用复合主键的情况。不要出现某些列只依赖其中的一部分主键。
表中的每一列都完全依赖于主键。
原则:一张表只描述一件事
3NF:
1. 概念:第三范式一定要先满足第二范式
表中的每一列都直接依赖于主键,而不是通过其它列间接依赖于主键。不产生传递依赖
实际应用
1. 性能与规范的权衡如果性能与规范冲突的时候,优先满足性能