[大数据]MySQL - 02 SQL结构化查询语言

提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档

1.SQL语言基本概述

1.1 什么是SQL语言

• SQL(Structured Query Language): 结构化查询语言
• 其实就是定义了操作所有关系型数据库的规则。每一种数据库操作的方式可能会存在一些不 一样的地方，我们称为“方言”。

1.2 SQL通用语法

• SQL语句可以单行或者多行书写，以分号结束。
• 可以通过空格和缩进进行格式代码
• MySQL数据库的SQL语言是不区分大小写，建议关键字使用大写。
• 数据库的注释：
  • 单行注释：# 注释的内容
  • 多行注释: /* 注释内容 */

1.3 SQL 分类

• DDL(Data Definition Language)数据定义语言

  • 用来定义数据库对象：数据库，表，列等。关键字：create, drop,alter 等

• DML(Data Manipulation Language)数据操作语言

  • 用来对数据库中表的数据进行增删改。关键字：insert, delete, update 等

• DQL(Data Query Language)数据查询语言

  • 用来查询数据库中表的记录(数据)。关键字：select, where 等

• DCL(Data Control Language)数据控制语言(了解)

  • 用来定义数据库的访问权限和安全级别，及创建用户。关键字：GRANT， REVOKE 等

2.DDL-操作数据库

2.1 创建数据库

语法格式：

create database [if not exists] 数据库名 [character set 字符集名称];

解释：

1. if not exists 可选，意为如果该数据库在数据库系统中不存在，则创建该数据库。
2. charcter set 字符集名称 可选，为数据库绑定字符集，若没有写，则默认使用utf8字符集

代码演示：

# 1.直接创建一个名为mydb1的数据库
create database mydb1;

# 2.创建一个名为mydb2的数据库，判断数据库系统中是否有该数据库，若没有则创建
create database if not exists mydb2;

# 3.创建数据库mydb3并指定字符集gbk
create database mydb3 character set gbk;

2.2 查询数据库

语法格式：

# 查询所有的数据库
show databases;

# 查询某个数据库创建信息
show create database 数据库名;

代码演示:

# 1.查询当前数据库系统中所有的数据库名称
show databases;

# 2.分别查询mydb1、mydb2、mydb3的数据库创建信息
show create database mydb1;
show create database mydb2;
show create database mydb3;

2.3 修改数据库(修改字符集)

语法格式

# 修改数据库字符集
alter database 数据库名称 character set 新的字符集;

代码演示:

# 1.将mydb1数据库的字符集改为utf8
alter database mydb1 character set 'utf8';

2.4 删除数据库

语法格式

drop database 数据库名称;

代码演示:

# 1.删除mydb2数据库
drop database mydb2;
# 2.删除mydb3数据库
drop database mydb3;

2.5 使用数据库

语法格式

# 1.查询当前使用的数据库
select database();

# 2.使用数据库
use 数据库名称;

代码演示:

# 1.使用mydb1数据库
use mydb1;

# 2.查看当前使用的数据库
select database();

3.DDL - 操作数据表

3.1 MySQL数据类型

1.整型
	tinyint 微整形 
	smallint 小整型
	int 整型
	mediumint 中整型，无符号[0,2^24 -1] , 有符号[-2^23 , 2^23-1]
	bigint 大整型
2.浮点型
	float 单精度浮点型
	double 双精度浮点型
	decimal 定点浮点型 , 比如：decimal(5,2), 小数长度最大5位，精确到小数的2位
3.字符类型
	char(长度)  定长字符类型
	varchar(长度) 可变长字符类型
4.时间日期
	Date 以YYYY-MM-DD的格式显示，范围："1000-01-01"到"9999-12-31"
	time 以HH:MM:SS的格式显示，范围："-838:59:59"到"838:59:59" 
	DateTime 以YYYY-MM-DD HH:MM:SS的格式显示
	timestamp 以YYYY-MM-DD HH:MM:SS的格式显示
5.枚举
	enum 枚举类型

3.2 创建表操作

语法格式

# 1.创建表
create table 表名(
	字段1 数据类型,
    字段2 数据类型,
    ...
    字段n 数据类型
);

# 2.复制表
CREATE TABLE 表名 LIKE 被复制的表名;

代码演示

# 1.创建一个学生表，包含姓名、年龄、性别、出生年月日。。
create table t_student(
	name varchar(15),
    age int,
    sax char(1),
    birthday date
);
# 2.创建一个动物表，包含名称、类别、年龄。。
create table t_Anamal(
	name varchar(10),
    an_class varchar(10),
    age int
);

3.3 查询表的操作

语法格式

# 1.查询当前数据库中所有的表
show tables;

# 2.查询创建表的结构
desc 表名;

# 3.查询表的字符集
show table status from 数据库名 like '表名';

代码演示

# 1.查询数据库中所有的表
show tables;

# 2.查询学生表、动物表的结构
desc t_student;
desc t_anamal;

# 3.查询学生表、动物表的字符集
show table status from mydb1 like 't_student';
show table status from mydb1 like 't_anamal';

3.4 修改表的操作

语法格式

# 1.修改表名
alter table 旧表名 rename to 新表名;

# 2.修改表的字符集
alter table 表名 character set 新字符集;

# 3.添加字段
alter table 表名 add 新字段名 数据类型;

# 4.修改字段的数据类型
alter table 表名 modify 字段名 数据类型;

# 5.修改字段名
alter table 表名 change 旧字段名 新字段名 新字段名的数据类型;

# 6.删除字段
alter table 表名 drop 字段名;

代码演示

# 1.修改t_anamal表的名称
alter table t_anamal rename to t_animal;

# 2.将t_animal表的字符集改为utf8
alter table t_animal character set utf8;

# 3.给t_animal表添加一个编号字段
alter table t_animal add id int;

# 4.修改t_animal表中name字段的数据类型长度
alter table t_animal modify name varchar(30);

# 5.修改t_animal表中name字段名称改为username
alter table t_animal change name username varchar(15);

# 6.删除t_animal表中的an_class字段
alter table t_animal drop an_class;

3.5 删除表的操作

语法格式

# 1.直接删除表，若表不存在则报错
drop table 表名;

# 2.判读是否存在表，存在就删除
drop table if exists 表名;

代码演示

# 1.删除t_animal表
drop table t_animal;

4.DMl - 数据库操作表中的数据(记录)

4.1 向表中插入记录操作–insert语句

语法格式

# 1.给定字段添加数据
insert into 表名 (字段1 , 字段2 , ... , 字段n) values (值1 , 值2 , ... ,值n);

# 2.默认添加全部字段
insert into 表名 values (值1 , 值2 , ... ,值n);

# 3.批量添加
insert into 表名 values (值1 , 值2 , ... ,值n) , (值1 , 值2 , ... ,值n) ,....;

代码演示

# 1.向t_student表中默认添加全部数据
insert into t_student values ('张三丰',11,'男','1990-1-1');

# 2.向t_student表中某系字段添加数据
insert into t_student (name , age ,sax) values ('范武吉',15,'男');

# 3.批量添加数据
insert into t_student values ('张三',15,'男','1990-1-5') , ('张三',15,'男','1990-1-5') , ('李四',16,'女','1990-1-5');

4.2 修改表中记录–update语句

语法格式

update 表名 set 字段1 = 值1 , 字段2 = 值2 ,..., 字段n = 值n [where 条件];

代码演示

# 1.将t_student 表中的范武吉生日改成1998-5-3
update t_student set birthday = '1998-5-3' where name = '范武吉';

4.3 删除表中的记录–delete语句

语法格式

# 1.只删除记录
delete from 表名 [where 条件];

# 2.先删除表，再新建表
truncate table 表名;

代码演示

# 1.删除t_student表中的张三记录
delete from t_student where name = '张三';

# 2.删除表中所有数据
truncate table t_student;
delete from t_student;

5.DQL-数据(记录)查询相关操作

5.1 基本查询

语法格式

# 1.查询全部记录
select [distinct] * from 表名;

# 2.查询某个字段记录
select [distinct] 字段1 , 字段2 ,..., 字段n from 表名

代码演示

# 1.查询t_student中所有记录
select * from t_student;
# 2.查询name和age字段的记录
select name , age from t_student;

5.2 别名查询

语法格式

select 字段1 as 别名 from 表名;

代码演示

# 查询name和age字段的记录
select name as '姓名' , age as '年龄' from t_student;

5.3 条件查询

逻辑运算符

> 大于
< 小于
>= 大于等于
<= 小于等于
= 等于
<> 或 != 不等于
BETWEEN ... AND ... 在某个范围之内(都包含)
IN(...) 多选一
LIKE 占位符 模糊查询 _单个任意字符 %多个任意字符
IS NULL 是NULL
IS NOT NULL 不是NULL
AND 或 && 并且
OR 或 || 或者
NOT 或 ! 非，不是

语法格式

select * from 表名 where 条件语句;

代码演示

# 查询t_student表中年龄大于15的学生
select * from t_student where age > 15;

# 查询t_student表中姓张的学生
select * from t_student where name like '张%';

# 查询t_student表中年龄为11，16的学生
select * from t_student where age in(11,16);

5.4 排序查询

语法格式

/*
	排序规则:
	desc : 降序排列
	asc : 升序排列
*/
select * from 表名 order by 字段名 排序规则;

代码演示

# 将t_student表中的学生按照年龄升序排序
select * from t_student order by age asc;

5.5 聚合函数

函数名称

概述：将一列数据作为一个整体，进行纵向的计算
聚合函数分类
    count(字段名) 统计数量(一般选用不为null的列)
    max(字段名) 最大值
    min(字段名) 最小值
    sum(字段名) 求和
    avg(字段名) 平均值

语法格式

select 函数名(字段名) from 表名;

代码演示

# 1.计算t_student表中总人数
select count(*) from t_student; 

# 2.计算t_student表中年龄的和
select sum(age) as '年龄和' from t_student;

# 3.计算t_student表中平均年龄
select avg(age) as '平均年龄' from t_student;

# 4.展示t_student表中最大年龄的信息
select name, age , birthday ,sax , max(age) from t_student;

# 5.展示t_student表中最小年龄信息
select name, age , birthday , sax , min(age) from t_student;

5.6 分组统计

语法格式

select * from 表名 group by 字段名称;

代码演示

# 将 t_student 表中按男女分组统计人数
select count(*) from t_student group by sax;

5.7 分页查询

语法格式

/*
	startInde : 起始索引
	size : 要查询多少条记录数
*/
select * from 表名 limit startIndex , size;

代码演示

# 查询 t_student 表中从1索引开始查，查2个记录
select * from t_student limit 1 , 2;

5.8 合并查询的顺序

select
	字段列表
from
	表名列表
where
	条件列表
group by
	分组字段
having
	分组之后的条件
order by
	排序
limit
	分页限定

6.DCL - 数据库访问权限相关

6.1 用户管理

语法格式

# 1.查询用户
use mysql;
select * from user;

# 2.创建用户
create user '账户'@'主机' identified by '密码'；

# 3.删除账户
drop user '账户'@'主机';

# 4.修改账户
set password for '账户'@'主机' = password('新密码');

代码演示

#创建账户
create user 'admin'@'%' identified by 'admin';
#修改账户
set password for 'adminn'@'%' = password('123456');
#删除账户
drop user admin;

6.2 权限管理

语法格式

# 1.查询用户权限
show grants for '账户'@'主机';

# 2.添加用户权限
grant 权限1,权限2,... on 数据库名.表名 to '账户'@'主机';

# 3.删除用户权限
revoke select on 数据库名.表名 from '账户'@'主机';

# 4.远程访问权限
update user set host='访问地址' where user ='账户';

# 5.刷新权限
flush privileges;

代码演示

# 1.查询用户权限
show grants for 'root'@'localhost';
show grants for 'admin'@'%';

# 2.给admin授予mydb1.t_student的select权限
grant select on mydb1.t_student to 'admin'@'%';

# 3.撤销admin的mydb1.t_student的select权限
revoke select on mydb1.t_student from 'admin'@'%';

# 4.为adim账户添加访问客户端权限
update user set host='%' where user ='admin';

# 5.刷新权限
flush privileges;

6.3 数据库备份与还原

概述

• 备份： 将mysql服务器中的数据备份到磁盘中；
• 还原：将磁盘中的数据还原到mysql服务器中。

语法格式

#备份
mysqldump -uroot -p 数据库名 > sql文件的路径

#还原
mysql -uroot -proot 数据库名 < sql文件的路径