训练营-数据库-5月

SQL（Structrued Query Language）结构化查询语言

SQL语言基础
语法特点：
1、SQL对关键字的大小写不敏感
2、SQL语句可以单行或者多行书写，以分号结束
3、SQL的注释：

-- 单行注释，-- 后面有一个空格
#单行注释，#后面可以不加空格

/*
多行注释
多行注释
*/

一、数据库系统

1、数据库：

Database，里面是一张张表格，存放在磁盘上

2、数据库分类：

（1）关系型
①Oracle
②MySql
③Sql Server
④PostgreSQL
⑤SQLite

（2）非关系型
①Redis
②MongoDB
③Elasticsearch
④Cassandra
⑤HBase

二、Mysql基本操作-DDL

DDL（Data Defination Language），数据定义语言
对数据库的常用操作
对表结构的常用操作
修改表结构

1、对数据库的常用操作

功能	SQL
查看所有的数据库	show databases;
创建数据库	create database [if not exists] mydb1 [charset=utf8];
切换数据库	use mydb1;
删除数据库	drop database [if exists] mydb1;
修改数据库编码	alter database mydb1 character set utf8

2、对表结构的常用操作-创建表

创建表格式：
create table [if not exists] 表名(
	字段名1 类型[(宽度)] [约束条件] [comment '字段说明'],
	字段名2 类型[(宽度)] [约束条件] [comment '字段说明'],
	字段名3 类型[(宽度)] [约束条件] [comment '字段说明']
) [表的一些设置];

（1）mysql数据类型
①数值类型
TINYINT，范围：（-128，127）
INT或INTEGER，范围：（-2147483648，2147483647）
DOUBLE

②日期和时间类型
DATE，范围1000-01-01/9999-12-31，格式YYYY-MM-DD
DATETIME，范围1000-01-01 00:00:00/9999-12-31 23:59:59，格式YYYY-MM-DD HH:MM:SS
TIMESTAMP，范围1970-01-01 00:00:00/2038，格式YYYYMMDD HHMMSS

③字符串类型
VARCHAR

3、对表结构的常用-其他操作

功能	SQL
查看当前数据库的所有表名称	show tables;
查看指定某个表的创建语句	show create table 表名;
查看表结构	desc 表名;
删除表	drop table 表名;

4、对表结构常用操作-修改表结构格式

（1）修改表添加列

语法格式：
alter table 表名 add 列名 类型(长度) [约束];

（2）修改列名和类型

语法格式：
alter table 表名 change 旧列名 新列名 类型(长度) [约束];

（3）修改表删除列

语法格式：
alter table 表名 drop 列名;

（4）修改表名

语法格式：
rename table 表名 to 新表名;

三、Mysql基本操作-DML

DML指数据操作语言(Data Manipulation Language)，用来操作(insert、delete、update)数据库中表的记录

1、数据插入

insert into 表(列名1,列名2,列名3...) values (值1,值2,值3...); //向表中指定的某些字段插入

insert into 表 values (值1,值2,值3...); //向表中所有字段插入

两种格式均可，注意SQL要以分号结尾，所以语句可以分行写，其次可以在insert语句中插入多个列表值，value只需要写一次，多个值的括号用逗号隔开

insert into student VALUES
(1002,'李四','男',19,'1997-09-13','广东'),
(1003,'王五','女',17,'1996-11-29','深圳');

2、数据修改

语法格式：
update 表名 set 字段名=值,字段名=值...;
update 表名 set 字段名=值,字段名=值... where 条件;

3、数据删除

语法格式：
delete from 表名 [where 条件];

truncate table 表名 或者 truncate 表名

delete和truncate原理不同，delete只删除内容，truncate类似于drop table，可以理解为是将整个表删除，然后再创建该表

四、MYSQL约束

作用：表在设计的时候加入约束的目的是为了保证表中记录的完整性和有效性，比如用户表有些列的值(手机号) 不能为空，有些列的值（身份证号）不能重复

1、约束分类：

（1）主键约束（primary key） PK
（2）自增长约束（auto_increment）
（3）非空约束（not null）
（4）唯一性约束（unique）
（5）默认约束（default）
（6）零填充约束（zerofill）
（7）外键约束（foreign key） FK’

2、主键约束

该约束可以加个某个列上或者多个列上

（1）概念：

主键约束相当于 唯一约束+非空约束的组合，主键约束列不允许重复，也不允许出现空值

（2）创建单列主键

方式1-语法格式：
create table 表名(
	...
	<字段名> <数据类型> primary key
	...
);

方式2-语法格式：
create table 表名(
	...
	[constraint <约束名>] primary key [字段名];
);
constraint<约束名> 可以省略不写

（3）通过修改表结构添加主键

语法格式：
create table 表名(
	...
);

alter table <表名> add primary key (字段列表);

（4）删除主键约束

语法格式：
alter table <数据表名> drop primary key;

3、自增长约束

当主键定义为自增长后，这个主键的值就不需要用户输入数据了，而由数据库系统根据定义自动赋值，每增加一条记录，主键会自动以相同的步长进行增长

语法格式：
字段名 数据类型 auto_increment

（1）特点：

①默认情况下，auto_increment的初始值是1，每新增一条记录，字段值自动加1
②一个表中只能有一个字段使用auto_increment约束，且该字段必须有唯一，避免序号重复
③auto_increment约束的字段必须具备NOT NULL属性
④auto_increment约束的字段只能是整型
⑤auto_increment最大值受该字段的数据类型的约束，如果达到上限，auto_increment就会失效

（2）指定自增字段初始值

语法格式：
方式1-创建表时指定
create table 表名(
	字段名 字段类型 primary key auto_increment,
	...
) auto_increment=100;

方式2-创建表之后指定
create table 表名(
	字段名 字段类型 primary key auto_increment,
	...
);

alter table 表名 auto_increment = 值;

五、MySQL基本操作-DQL

1、简单查询

#1、查询所有的商品
#2、查询商品名和价格
#3、别名查询，使用的关键字是as（as是可以省略的）
#4、去掉重复值
#5、查询结果是表达式（运算查询），将所有的商品的价格+10元显示

2、运算符

（1）算术运算符：

运算符	说明
+
-
*
/或DIV
%或MOD

（2）比较运算符：

运算符	说明
=
<和<=
>和>=
<=>
<>或!=
IS NULL或ISNULL
IS NOT NULL
LEAST
GREATEST
BETWEEN AND
IN
NOT IN
LIKE
REGEXP

（3）逻辑运算符

运算符	说明
NOT或者!
AND或者&&
OR或者

#查询商品名称为"海尔洗衣机"的商品所有信息
#查询价格为800的商品
#查询价格不是800的所有商品
#查询商品价格大于等于60元的所有商品信息
#查询商品价格在200到1000之间的所有商品
#查询含有"裤"字的所有商品
#查询以"海"字开头的所有商品
#查询第二个字为"蔻"的所有商品
%用来匹配任意字符
_用来匹配单个字符

#查询category_id为null的商品
关于null的匹配不能用= 需要用is

#查询categroy_id不为null的商品

3、排序查询(order by)

语法格式：
select 
	字段名1,字段名2,......
from 表名
order by 字段名1 [asc|desc],字段名2[asc|desc].....

①asc(ascending)代表升序，desc(descending)代表降序，如果不写默认是升序
②order by用于子句中可以支持单个字段，多个字段，表达式，函数，别名
③order by子句，放在查询语句的最后面，LIMIT子句除外

#使用价格排序（降序）
#在价格排序（降序）的基础上，再进行分类排序（降序）
#显示商品的价格（去重复），并排序（降序）

4、聚合查询

聚合函数	作用
count()	统计指定列不为NULL的记录行数
sum()	计算指定列的数值和，如果指定列类型不是数值类型，计算结果为0
max()	计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算
min()	计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算
avg()	计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0

5、分组查询(group by)

格式：
select 字段1,字段2... from 表名 group by 分组字段 having 分组条件;

#统计各个分类商品的个数
#统计各个商品分类的个数，且只显示个数大于4的信息

6、分页查询-limit

语法格式：
#方式1-显示前n条
select 字段1,字段2... from 表名 limit n;
#方式2-分页显示
select 字段1,字段2... from 表名 limit m,n;
m：表示从第几条开始，计算方式(当前页码-1) * 每页显示条数
n：表示查询每页多少条数据

#查询product表的前5条记录
#查询第4页的数据，每页显示5条

7、INSERT INTO SELECT语句

将一张表的数据导入到另一张表中，可以使用INSERT INTO SELECT语句

insert into 表名(字段名1,字段名2,...) select 字段名1,字段名2,... from 表名
或者：
insert into 表名 select * from 表名

SQL执行顺序：from -> where -> group by -> count() ->having -> select -> order by -> limit

#查询表中所有学生的信息
#查询表中所有学生的姓名和对应的英语成绩
#过滤表中的重复数据
#统计每个学生的总分
#在所有学生总分数上加10分特长分
#使用别名表示学生分数
#查询英语成绩大于90分的同学
#查询总分大于200分的所有同学
#查询英语分数在80一90之间的同学
#查询英语分数不在80一90之间的同学
#查询数学分数为89，90，91的同学
#查询所有姓李的学生英语成绩
#查询数学分80并且语文分80的同学
#查询英语80或者总分200的同学
#对数学成绩降序排序后输出
#对总分排序后输出，然后再按从高到低的顺序输出
#对姓李的学生总成绩排序输出
#查询男生和女生分别有多少人，并将人数降序排序输出，查询出人数大于4的性别人数信息