MySQL：语法基础入门使用

一，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.选择数据库

use 数据库名；

2.查看数据库中的所有表

show tables;

3.创建一个student表，有 id(int), name(varchar), birthday(date) 类型

create table student (

id int,

name varchar(20),

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表

show create table student;

2.以表格格式返回

desc student;

3.如何快速构建相同表结构

create table 新表 like 旧表

4.删除表

1.删除s1这张表

删除表结构，则表中的所有的数据也会丢失

drop table 表名；

2.同时删除s2 和s3 两张表

drop table 表名1，表名2；

5.修改表

1.添加字段：add

示例 1：student 表添加一个remark 字段，类型char（10）

alter table student add remark char (10);

示例 2：同时添加两个字段，一个名为 a int,一个名为 b int （要有2个add）

alter table student add a int, add b int;

2.修改字段类型：modify

示例：将student表中的remark字段的类型修改成varchar(100)

alter table student modify remark varchar(100);

3.修改字段名称：change

示例：将student表中的remark字段名改成resume, 类型不变

注意：修改字段名称必须指定数据类型

alter table student change remark resume varcher(100);

4.删除字段：drop

示例：删除student表中的字段a和字段b（使用两个drop）

alter table student drop a, drop b;

5.修改表名：rename

实例：将学生表 student, 改名成employee

alter table student rename employee;

四，管理数据：数据增删改的操作

1.插入数据

1. 插入所有列

示例：向student表中插入3条记录

insert into 表名 values (列值)；

insert into student values (1,'潘金莲'，'2017-11-24','武大郎的妻子'）

● 注意:
1) 值与数据类型要相同
2) 长度不能大于建表的列的长度，如果超出，则会自己截断。
3) 表中有多少列，值也就是多少列
4) 字符和日期型格式：使用单引号。在 MySQL 中可以使用双引号，不建议。
5) null:插入一个空的值

2.插入部分列

注意：列名和值的顺序，数量保持一致！

示例：插入id,name两列数据

insert into student (id,name) values (5,'林冲')；

2.修改数据

1.语法

update: 指定表明

set： 指定要更新的值和列

where: 指定条件

2.修改所有的记录

示例： 将所有的性别改成男

update student set gender = '男'；

3.修改符合某些条件的一列数据（用的比较多）

示例： 将id号为1的学生性别改成女

update student set gender='女' where id = 1;

4.修改多个列（set 列明=值，列名=值...）

示例： 把id为3的学生，birthday改成1995-08-25，resume 改成 '广州人'

update student set birthday = '1995-03-24',resume='广州人' where id = 3;

3.删除数据

1.删除表中的所以数据（用的比较少）

delete from 表名；

如：删除学生表中的所有的记录

delete from student;

2.删除部分行的数据

示例：删除id是3的学生

delete from student where id = 2;

3.删除所有数据

truncate 表名；

五.查询数据（重点）

1.查询所有列

示例：查询所有列

select * from 表名

* 代表所有的列

2.查询指定列

示例：多个列之间以逗号分隔

select name,gender from student;

3.查询时指定别名（as）

示例：查询时给列指定别名（as）

select name as 姓名，gender as 性别 from student;

注：as 可以省略

select name 姓名，gender 性别 from student;

4.合并列查询

1.需求：查询每个学生的数学+英语=总成绩

注意：必须是数值类型

select *,(math+english) as 总成绩 from student

2.和数值以外合并没有意义

5.查询时添加常量列

需求：在查询学生表时都带上一个班级列，内容为'高三101班'

select *,'高三101班' as 班级 from student;

6.去除重复数据(distinct)

需求：查询学生都来自哪里

select distinct resume from student;

细节：使用函数或关键字都可以
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，英语大于 90
select * 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 子句排序查询结果

语法： 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 或等于地区

select resume,count(resume) from student group by resume having count(resume) >=2;

12.数据库的备份与恢复

1. 方法一：通过命令行方式：
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)

1. 非空约束：
某一列必须有数据，不能为空
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. 什么是主键：
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.注意事项

1> 插入数据

先插入主表的记录，再插入从表中的记录

2> 修改或删除数据表的顺序

不能直接更新 ,先把从表的数据从这个主表中移走，或者把外键设置为 null

2.级联操作

什么是级联操作：
级联更新：如果更新主表，则从表相应的数据也自动同步更新。 on update cascade
级联删除：如果删除主表中记录，从表中的记录相应删除。on delete cascade

语法：

foreign key () references () on update cascade on delete cascade

3.表与表之间的关系

1. 一对多(如：部门和员工，客户和订单，分类与商品)
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.左外连接

语法：表 1 left [outer] join 表 2 on 外键=主键
特点：左表中所有的记录都会出现，如果右表中没有对应的记录，则右表中使用 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>子查询是多行单列的情况：

父查询使用： in (数组)

● 先查询开发部与财务部的 id，再查询在这些部门 id 中有哪些员工
select * from employee where dept_id in (select id from dept where name in('开发部','财务部'));

3>子查询是多行多列的情况 :

注：这种情况用在 from 子句中，做成一张虚拟表。相当于二次查询
需求：查询 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. 性能与规范的权衡

2.原则：
如果性能与规范冲突的时候，优先满足性能