学习内容
- 了解开发中常见的数据库
- 使用工具连接mysql数据库
- 了解SQL语句
- SQL之数据库操作
- SQL之数据表操作
- SQL之数据表中数据的操作
- SQL约束
- SQL之数据查询
- SQL之多表操作
- MySql中的索引
- MySql开窗函数
01_数据库介绍
阶段目标:了解数据库的基本概念
学习主线:
-
数据库的基本介绍
-
开发中常用的数据库
1.1 数据库概述
数据库就是存储数据的仓库, 其本质是一个文件系统, 数据按照特定的格式将数据存储起来, 用户可以对数据库中的数据进行增加, 修改, 删除以及查询操作.
随着互联网的高速发展, 大量的数据在不断的产生,伴随而来的是如何高效安全的存储数据和操作数据,而这一问题成为了信息时代的一个非常大的问题。
使用数据库可以高效、有条理的储存数据:
(1)可以结构化存储大量的数据
(2)可以有效的保持数据的一致性、完整性
1.2 常见的数据库
数据库又分为关系型数据库和非关系型数据库
常用的数据库:MySql, Oracle, Redis, Hbase
02_SQL语句
阶段目标:了解数据库中的SQL语句
学习主线:
-
SQL介绍
-
SQL语句的分类
-
SQL语法
2.1 SQL语句介绍
结构化查询语言(Structured Query Language)简称SQL,是关系型数据库管理系统都需要遵循的规范,是数据库认识的语句。不同的数据库生产厂商都支持SQL语句,但都有特有内容。
普通话:各数据库厂商都遵循的标准
方言:数据库特有的关键字
2.2 SQL语句分类
SQL分类:
(1)数据定义语言:简称DDL(Data Definition Language),用来定义数据库对象:数据库,表,列等。关键字:create,alter,drop等
(2)数据操作语言:简称DML(Data Manipulation Language),用来对数据库中表的记录进行更新。关键字:insert,delete,update等
(3)数据控制语言:简称DCL(Data Control Language),用来定义数据库的访问权限和安全级别,及创建用户
(4)数据查询语言:简称DQL(Data Query Language),用来查询数据库中表的记录。 关键字:select,from,where等
2.3 SQL通用语法
SQL语句可以单行或多行书写,以分号结尾
可使用空格和缩进来增强语句的可读性
MySQL数据库的SQL语句不区分大小写,关键字建议使用大写。例如:SELECT * FROM user
可以使用注释:/*多行注释*/ --(空格)单行注释 #单行注释
03_DDL之数据库操作
阶段目标:学习数据库中的DDL语句
学习主线:
-
操作数据库的SQL语言
-
操作数据表的SQL语言
3.1 数据库:database
3.1.1 创建数据库
格式:
create database 数据库名; #直接创建数据库,如果存在则报错
create database if not exists bigdata_db; #如果数据库不存在则创建
create database 数据库名 character set 字符集; #创建数据库时设置字符集
3.1.2 查看数据库
show databases;
3.1.3 删除数据库
格式:drop database 数据库名称
示例:
drop database bigdata_db; #删除数据库bigdata_db
3.1.4 数据库切换
选择数据库
格式:use 数据库名字
示例:
use 数据库名; #切换到某个数据库
3.2 数据表: table
3.2.1 创建表
格式:
create table 表名 (
字段名 类型(长度) [约束] ,
字段名 类型(长度) [约束] ,
...
);
类型:
varchar(n) 字符串
int 整形
double 小数
date 时间
timestamp 时间戳
约束:
primary key 主键,被主键修饰字段中的数据,不能重复、不能为null
MySQL中数据类型:
示例:
-- 创建分类表
CREATE TABLE category (
cid varchar(20) primary key,#分类ID
cname VARCHAR(100) #分类名称
);
3.2.2 查看表
查看数据库中的所有表:show tables;
查看表结构:desc 表名; 例如:desc category;
3.2.3 修改表结构
格式:alter table 表名 增/删/改 列名 类型(长度) [约束]
(1)添加列 add
alter table 表名 add 列名 类型(长度) [约束]
示例:
-- 为分类表添加一个新的字段为分类描述 varchar(20)
alter table category ADD `desc` varchar(20);
(2)修改列名 change
alter table 表名 change 旧列名 新列名 类型(长度) 约束;
例如:
-- 为分类表的分类描述字段更换为 description varchar(30)
alter table category change `desc` description VARCHAR(30);
(3)删除列名 drop
alter table 表名 drop 列名;
示例:
-- 删除分类表中description这列
alter table category drop description;
(4)修改表名
rename table 表名 to 新表名;
示例:
-- 为分类表category改名成 category2
rename table category TO category2;
3.2.4 删除表
格式:==drop table 表名;==
示例:
drop table category2;
04_DML操作
阶段目标: 学习数据库中的DML语言
学习主线:
-
数据表之insert
-
数据表之update
-
数据表之delete
针对数据表中存储的数据进行相关操作:向表中添加数据、修改表中数据、删除表中的数据记录
准备表:
-- 创建分类表
CREATE TABLE category (
cid varchar(20) primary key,#分类ID
cname VARCHAR(100) #分类名称
);
4.1 插入表记录:insert
语法:
-- 向表中插入某些字段
insert into 表 (字段1,字段2,字段3..) values(值1,值2,值3..);
-- 向表中插入所有字段,字段的顺序为创建表时的顺序
insert into 表 values(值1,值2,值3..)
注意:
-
值与字段必须对应,个数相同,类型相同
-
值的数据大小必须在字段的长度范围内
-
除了数值类型外,其它的字段类型的值必须使用引号引起。(建议单引号)
-
如果要插入空值,可以不写字段,或者插入 null
示例:
INSERT INTO category(cid,cname) VALUES('c001','电器');
INSERT INTO category(cid,cname) VALUES('c002','服饰');
INSERT INTO category(cid,cname) VALUES('c003','化妆品');
INSERT INTO category(cid,cname) VALUES('c004','书籍');
INSERT INTO category(cid) VALUES('c005');
insert into category values('06','玩具'),('07','蔬菜');
4.2 更新数据:update
用来修改指定条件的数据,将满足条件的记录指定列修改为指定值
语法:
-- 更新所有记录的指定字段
update 表名 set 字段名=值,字段名=值,...;
-- 更新符号条件记录的指定字段
update 表名 set 字段名=值,字段名=值,... where 条件;
示例:
update category set cname = '家电'; #将所有行的cname改为'家电'
update category set cname = '水果' where cid = 'c001'; #将cid为c001的cname修改为水果
注意:
-
列名的类型与修改的值要一致
-
修改值得时候不能超过最大长度
-
除了数值类型外,其它的字段类型的值必须使用引号引起
4.3 删除表记录:delete
语法:
delete from 表名 [where 条件];
示例:
delete from category where cid = 'c005'; #删除cid为005的纪录
05_SQL约束
阶段学习目标:学习数据库中的约束
学习主线:
-
主键约束
-
非空约束
-
唯一约束
5.1 主键约束
primary key 约束,表示非空唯一
使用primary key约束的字段 我们成为主键.
被primary key约束的==字段的值是唯一的,并且不能为null.==
5.1.1 添加主键
方式一:创建表时,在字段描述处,声明指定字段为主键
CREATE TABLE Persons1
(
id int PRIMARY KEY ,
lastName varchar(255) ,
firstName varchar(255) ,
address varchar(255) ,
city varchar(255)
);
方式二:创建表时,在constraint约束区域,声明指定字段为主键
-
格式:[constraint 名称] primary key (字段列表)
-
关键字constraint可以省略,如果需要为主键命名(主键名称一般没用),constraint不能省略
-
字段列表需要使用小括号括住,如果有多字段需要使用逗号分隔。声明两个以上字段为主键,我们称为联合主键
CREATE TABLE Persons2
(
firstName varchar(255) ,
lastName varchar(255) ,
address varchar(255) ,
city varchar(255) ,
CONSTRAINT pk_personID PRIMARY KEY (FirstName, LastName)
);
5.1.2 删除主键
如需撤销 PRIMARY KEY 约束,可以使用下面的 SQL:
alter table Persons2 drop primary key;
5.1.3 自动增长列
我们通常希望在每次插入新记录时,数据库自动生成字段的值。
我们可以在表中使用 auto_increment(自动增长列)关键字,自动增长列类型必须是整形,自动增长列必须为键(一般是主键)。
下列 SQL 语句把 "Persons" 表中的 "Id" 列定义为 auto_increment 主键
CREATE TABLE Persons3
(
Id int PRIMARY KEY AUTO_INCREMENT,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);
向persons3添加数据时,可以不为Id字段设置值,也可以设置成null,数据库将自动维护主键值:
INSERT INTO Persons3 (FirstName,LastName) VALUES ('Bill','Gates');
INSERT INTO Persons3 (Id,FirstName,LastName) VALUES (NULL,'Bill','Gates');
扩展:默认AUTO_INCREMENT 的开始值是 1,如果希望修改起始值,可以使用下列 SQL 语法:
ALTER TABLE Persons AUTO_INCREMENT=100
5.2 非空约束
NOT NULL 约束强制列不接受 NULL 值
NOT NULL 约束强制字段始终包含值。这意味着,如果不向字段添加值,就无法插入新记录或者更新记录。
下面的 SQL 语句强制 "Id" 列和 "LastName" 列不接受 NULL 值:
CREATE TABLE Persons4
(
Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
5.3 唯一约束
UNIQUE 约束唯一标识数据库表中的每条记录。
UNIQUE 和 PRIMARY KEY 约束均为列或列集合提供了唯一性的保证。
PRIMARY KEY 拥有自动定义的 UNIQUE 约束。
请注意,每个表可以有多个 UNIQUE 约束,但是每个表只能有一个 PRIMARY KEY 约束。
添加唯一约束
创建表时,在字段描述处,声明唯一:
CREATE TABLE Persons5
(
Id int UNIQUE,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);
5.4 外键约束
FOREIGN KEY 表示外键约束,将在多表中学习
06_DQL之数据查询
阶段目标:学习数据库中的DQL语言
学习主线:
-
查询数据的语法
-
简单查询
-
条件查询
-
排序查询
-
聚合查询
-
分组查询
-
insert into select语句
6.1 准备工作
创建商品表:
#创建商品表
create table product(
pid int primary key,
pname varchar(20),
price double,
category_id varchar(32)
);
插入测试数据:
INSERT INTO product(pid,pname,price,category_id) VALUES(1,'联想',5000,'c001');
INSERT INTO product(pid,pname,price,category_id) VALUES(2,'海尔',3000,'c001');
INSERT INTO product(pid,pname,price,category_id) VALUES(3,'雷神',5000,'c001');
INSERT INTO product(pid,pname,price,category_id) VALUES(4,'杰克琼斯',800,'c002');
INSERT INTO product(pid,pname,price,category_id) VALUES(5,'真维斯',200,'c002');
INSERT INTO product(pid,pname,price,category_id) VALUES(6,'花花公子',440,'c002');
INSERT INTO product(pid,pname,price,category_id) VALUES(7,'劲霸',2000,'c002');
INSERT INTO product(pid,pname,price,category_id) VALUES(8,'香奈儿',800,'c003');
INSERT INTO product(pid,pname,price,category_id) VALUES(9,'相宜本草',200,'c003');
INSERT INTO product(pid,pname,price,category_id) VALUES(10,'面霸',5,'c003');
INSERT INTO product(pid,pname,price,category_id) VALUES(11,'好想你枣',56,'c004');
INSERT INTO product(pid,pname,price,category_id) VALUES(12,'香飘飘奶茶',1,'c005');
INSERT INTO product(pid,pname,price,category_id) VALUES(13,'海澜之家',1,'c002');
6.2 查询语法
查询语法格式:
select [distinct] * | 列名,列名
from 表
where 条件
6.3 简单查询
示例1:查询所有的商品
select * from product;
示例2:查询商品名称和商品价格
select pname,price from product; #2.查询商品名和商品价格
示例3:别名查询,使用关键字 as
select * from product as p; #表别名
select pname as pn from product; # 列别名
单独给表名取别名是没有意义的 我们要结合多表查询使用
示例4:去掉重复值
select distinct price from product; #去重查询
示例5:查询结果是表达式
运算查询:将所有商品的价格+10元进行显示
select pname,price+10 from product; #所有商品的价格+10后显示
6.4 条件查询
条件查询时经常使用的运算符号:
示例1:查询商品名称为“花花公子”的商品所有信息
SELECT * FROM product WHERE pname = '花花公子';
示例2:查询价格为800商品
SELECT * FROM product WHERE price = 800;
示例3:查询价格不是800的所有商品
SELECT * FROM product WHERE price != 800;
SELECT * FROM product WHERE price <> 800;
SELECT * FROM product WHERE NOT(price = 800);
示例4:查询商品价格大于60元的所有商品信息
SELECT * FROM product WHERE price > 60;
示例5: 查询商品价格在200到1000之间所有商品
SELECT * FROM product WHERE price >= 200 AND price <=1000;
SELECT * FROM product WHERE price BETWEEN 200 AND 1000;
示例6:查询商品价格是200或800的所有商品
SELECT * FROM product WHERE price = 200 OR price = 800;
SELECT * FROM product WHERE price IN (200,800);
示例7:查询含有'霸'字的所有商品
SELECT * FROM product WHERE pname LIKE '%霸%';
示例8:查询以'香'开头的所有商品
SELECT * FROM product WHERE pname LIKE '香%';
示例9: 查询第二个字为'想'的所有商品
SELECT * FROM product WHERE pname LIKE '_想%';
示例10: 查询没有分类的商品
SELECT * FROM product WHERE category_id IS NULL;
示例11: 查询有分类的商品
SELECT * FROM product WHERE category_id IS NOT NULL;
6.5 排序查询
通过order by语句,可以将查询出的结果进行排序。暂时放置在select语句的最后。
格式:
SELECT * FROM 表名 ORDER BY 排序字段 ASC|DESC;
ASC 升序 (默认)
DESC 降序
示例1:使用价格排序(降序)
SELECT * FROM product ORDER BY price DESC;
示例2:在价格排序(降序)的基础上,以分类排序(降序)
SELECT * FROM product ORDER BY price DESC,category_id DESC;
示例3:显示商品的价格(去重复),并排序(降序)
SELECT DISTINCT price FROM product ORDER BY price DESC;
6.6 聚合函数
之前我们做的查询都是横向查询,它们都是根据条件一行一行的进行判断,而使用聚合函数查询是纵向查询,它是对一列的值进行计算,然后返回一个单一的值;另外聚合函数会忽略空值。
常用的五个聚合函数:
【附】
round(小数,保留位置)
format(小数,保留位置)(每三位一个逗号)
length():一个汉字是算三个字符,一个数字或字母算一个字符
char_length():不管汉字还是数字或者是字母都算是一个字符
示例1: 查询商品的总条数
SELECT COUNT(*) FROM product;
示例2: 查询价格大于200商品的总条数
SELECT COUNT(*) FROM product WHERE price > 200;
示例3: 查询分类为'c001'的所有商品的总和
SELECT SUM(price) FROM product WHERE category_id = 'c001';
示例4: 查询分类为'c002'所有商品的平均价格
SELECT AVG(price) FROM product WHERE category_id = 'c002';
示例5: 查询商品的最大价格和最小价格
SELECT MAX(price),MIN(price) FROM product;
6.7 分组查询
分组查询是指使用group by字句对查询信息进行分组。
格式:
SELECT 字段1,字段2… FROM 表名 GROUP BY分组字段 HAVING 分组条件;
分组操作中的having子语句,是用于在分组后对数据进行过滤的,作用类似于where条件。
having与where的区别:
-
having是在分组后对数据进行过滤.,where是在分组前对数据进行过滤
-
having后面可以使用分组函数(统计函数),where后面不可以使用分组函数
示例:
#1 统计各个分类商品的个数
SELECT category_id ,COUNT(*) FROM product GROUP BY category_id ;
#2 统计各个分类商品的个数,且只显示个数大于1的信息
SELECT category_id ,COUNT(*) FROM product GROUP BY category_id HAVING COUNT(*) > 1;
6.8 分页查询
分页查询在项目开发中常见,由于数据量很大,显示屏长度有限,因此对数据需要采取分页显示方式。例如数据共有30条,每页显示5条,第一页显示1-5条,第二页显示6-10条。
格式:
SELECT 字段1,字段2... FROM 表明 LIMIT M,N;
M: 整数,表示从第几条索引开始,计算方式 (当前页-1)*每页显示条数
N: 整数,表示查询多少条数据
SELECT 字段1,字段2... FROM 表明 LIMIT 0,5; # 查询前5条记录
SELECT 字段1,字段2... FROM 表明 LIMIT 5,5;
实例:查询product表的前5条记录
SELECT * FROM product LIMIT 0,5;
6.9 insert into select语句
INSERT INTO SELECT 语句从 一个表复制数据,然后把数据插入到一个已存在的表中。
基本语法:
INSERT INTO table2
SELECT column_name(s)
FROM table1;
示例:
create table product2(
pid int primary key,
pname varchar(20),
price double
);
insert into product2 select pid,pname,price from product where category_id = 'c001';