SQL基本使用

【第一章】数据库的基本概念

数据库的介绍

为什么要学习数据库?

更加方便的对开发中的数据进行管理！

数据介绍

方便后期学习，可以在windows上安装MySQL，将来在企业生产环境下肯定是使用Linux中的MySQL，具体安装步骤参考相关文档，此处省略500字。

==连接数据库【补充，重点】==

命令连接数据库【重点】

数据库的安装

 

1. 用于存储和管理数据的仓库。

2. 英文单词为 DataBase。简称 DB！

3. 它的存储空间很大，可以存放百万条、千万条、上亿条数据。

4. 使用一种统一的语言操作数据库 —— SQL

   MySQL数据库介绍

5. MySQL 是一个最流行的关系型数据库管理系统之一。由瑞典 MySQL AB 公司开发，后被 Oracle 公司收购。

6. 关系型数据库是将数据保存在不同的数据表中，而不是将所有数据放在一个大仓库内，而且表与表之间还可以有关联关系。这样就提高了访问速度以及提高了灵活性。

7. MySQL 所使用的 SQL 语句是用于访问数据库最常用的标准化语言。

8. 免费(6 版本之前)。

默认用户名是root，密码每个人设置的都不一样。

SQLyog连接【重点】

 

==连接异常【重点】==

异常一：mysql服务没开

异常信息

 

解决

• windows中开启MySQL服务

 Linux中开启MySQL服务

 

异常二：mysql密码错误

异常信息

 

解决

设置正确的密码或者修改root用户密码之后重新连接

数据库、数据表、数据的关系

问题1：数据库管理系统指的是什么？和数据库有什么区别？

问题2：数据库、表、数据之间的包含关系是什么？

 

SQL介绍

问题：SQL语句分为哪几类？

概念

Structured Query Language：==结构化查询语言== 其实就是定义了操作所有关系型数据库的规则。每一种数据库操作的方式存在不一样的地方，称为“方言”。

==SQL通用语法【重点】==

1. SQL 语句可以单行或多行书写，以分号结尾。
2. 可使用空格和缩进来增强语句的可读性。
3. MySQL 数据库的 SQL 语句不区分大小写，关键字建议使用大写。
4. 3 种注释
    * 单行注释: -- 注释内容 或 # 注释内容(mysql 特有)
    * 多行注释: `/* 注释 */`

SQL语句分类

1) DDL(Data Definition Language)数据定义语言
    用来定义数据库对象：数据库，表，列等。关键字：create, drop,alter 等
2) DML(Data Manipulation Language)数据操作语言【重点】
    用来对数据库中表的数据进行增删改。关键字：insert, delete, update 等
3) DQL(Data Query Language)数据查询语言【重点】
    用来查询数据库中表的记录(数据)。关键字：select, where 等
4) DCL(Data Control Language)数据控制语言(了解)
    用来定义数据库的访问权限和安全级别，及创建用户。关键字：GRANT， REVOKE 等

 

【第二章】DDL-操作数据库和数据表

DDL语句-操作数据库

问题：创建数据库的语法是什么？

说明：操作数据库无非就有四种方式 C(Create 创建数据库)、R(Retrieve 查询数据库)、U(Update 修改数据库)、D(Delete 删除数据库)

查询数据库【了解】

-- 查询所有数据库的名称
show databases;
-- 查询某个数据库的字符集
show create database 数据库名称;

==创建数据库【重点】==

-- 创建数据库基本格式
create database 数据库名称;
-- 创建数据库，判断不存在，再创建
create database if not exists 数据库名称;
-- 创建数据库，并指定字符集 sql格式
create database 数据库名称 character set 字符集名;

修改数据库【了解】

说明：关于修改数据库只能修改字符集，不能修改数据库的名称。

-- 修改数据库的字符集
alter database 数据库名称 character set 字符集名称;
-- 例如：修改db1数据库 的字符集为 utf8
alter database db1 character set 字符集名称;

 

删除数据库【了解】

-- 删除指定数据库
drop database 数据库名称;

使用数据库【了解】

-- (切换)当前使用的数据库
use 数据库名称;

-- 查询当前正在使用的数据库名称
select database();

DDL语句-操作表

查询表结构【了解】

-- 查询数据库中的所有数据表
show tables;
-- 查询表结构
desc 表名;
-- 查询表状态，状态中包括表的字符集
SHOW TABLE STATUS FROM mysql LIKE '表名称';

==创建表【重点】==

问题：创建表的语法是什么？

创建表语法

-- 创表格式,最后一列不要加逗号
create table 表名(  
    列名1 数据类型1,
    列名2 数据类型2,
    ....
    列名n 数据类型n
);
-- 或者
create table if not exists 表名(
    列名1 数据类型1,
    列名2 数据类型2,
    ....
    列名n 数据类型n
);

MySQL常见数据类型

关键字	数据类型	备注
int	整数类型	int(5)-->最大整数五位数,也就是99999
double/float	小数类型	如果需要指定小数的长度，那么可以这么写： 列名 double(数据总位数,小数位数); 例如：price double(5,2),表示最大值是999.99
date	日期类型	只包含年月日，yyyy-MM-dd
datetime	日期+时间	包含年月日时分秒 yyyy-MM-dd HH:mm:ss
timestamp	时间戳类型	包含年月日时分秒 yyyy-MM-dd HH:mm:ss，不赋值默认为系统时间毫秒值
varchar	字符串类型	该类型==必须==指定字符串的最大长度，例如：name varchar(20);

创建表举例

-- 创建一个product商品表(商品编号、商品名称、商品价格、商品库存、上架时间)
CREATE TABLE product( 
	id INT,
	NAME VARCHAR(20),
	price DOUBLE,
	stock INT,
	insert_time DATE
);

修改表【了解】

说明：修改表主要是对表结构的修改，如修改表名、修改表的字符集、修改表的字段(列)等

修改表名称

-- 修改表名
alter table 表名 rename to 新的表名;

修改表字符集

-- 修改表的字符集格式
alter table 表名 character set 字符集名称;

添加表字段(列)

-- 添加表字段(列)
alter table 表名 add 列名 数据类型;

修改表字段(列)

-- 修改列名以及数据类型格式
alter table 表名 change 列名 新列别 新数据类型;

-- 修改指定列数据类型
alter table 表名 modify 列名 新数据类型;

删除表字段

-- 删除表字段(列)
alter table 表名 drop 列名;

删除表【了解】

-- 删除表格式
drop table 表名;
-- 先判断存在，再删除表
drop table if exists 表名 ;

==【第三章】DML-表数据的增删改【重点】==

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

1 添加数据【重点】

问题：添加数据的语法是什么?

1.1 添加数据语法

-- 给指定列添加数据
INSERT INTO 表名(列名1,列名2,…) VALUES (值1,值2,…);
-- 给全部列添加数据
INSERT INTO 表名 VALUES (值1,值2,…);
-- 批量添加数据
INSERT INTO 表名(列名1,列名2,…) VALUES (值1,值2,…), (值1,值2,…),…;
INSERT INTO 表名 VALUES (值1,值2,…), (值1,值2,…),…;

==注意：列名和值的数量以及数据类型要对应，除了数字类型，其他数据类型的数据都需要加引号(单引双引都行，推荐单引)。==

1.2 添加数据练习

-- 向product表添加一条数据
INSERT INTO product (id,NAME,price,stock,insert_time) VALUES (1,'手机',1999.99,25,'2020-02-02');
-- 向product表添加指定列数据
INSERT INTO product (id,NAME,price) VALUES (2,'电脑',3999.99);
/*
	给全部列添加数据
	标准语法：
		INSERT INTO 表名 VALUES (值1,值2,值3,...);
*/
-- 默认给全部列添加数据
INSERT INTO product VALUES (3,'冰箱',1500,35,'2030-03-03');
/*
	批量添加所有列数据
	标准语法：
		INSERT INTO 表名 VALUES (值1,值2,值3,...),(值1,值2,值3,...),(值1,值2,值3,...);
*/
-- 批量添加数据
INSERT INTO product VALUES (4,'洗衣机',800,15,'2030-05-05'),(5,'微波炉',300,45,'2030-06-06');

2 修改数据【重点】

问题：带条件修改数据的语法是什么?

2.1 修改数据语法

UPDATE 表名 SET 列名1 = 值1,列名2 = 值2,... where 条件;

==注意：修改语句中必须加条件，如果不加条件，则会将所有数据都修改。==

2.2 修改数据练习

-- 修改手机的价格为3500
UPDATE product SET price=3500 WHERE NAME='手机';

-- 修改电脑的价格为1800、库存为36【练习】
UPDATE product SET price=1800,stock=36 WHERE NAME='电脑';

3 删除数据【重点】

问题：带条件删除数据的语法是什么?

3.1 删除数据语法

DELETE FROM 表名 WHERE 条件;

==注意：删除语句中必须加条件，如果不加条件，则会将所有数据都删除。==

3.2 删除数据练习

-- 删除product表中的微波炉信息
DELETE FROM product WHERE NAME='微波炉';

-- 删除product表中库存为10的商品信息【练习】
DELETE FROM product WHERE stock=10;

==【第四章】DQL-表数据的查询【重点】==

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

1 准备表数据

-- 创建db1数据库
CREATE DATABASE db1;

-- 使用db1数据库
USE db1;

-- 创建数据表
CREATE TABLE product(
	id INT,			-- 商品编号
	NAME VARCHAR(20),	-- 商品名称
	price DOUBLE,		-- 商品价格
	brand VARCHAR(10),	-- 商品品牌
	stock INT,		-- 商品库存
	insert_time DATE        -- 添加时间
);

-- 添加数据
INSERT INTO product VALUES 
(1,'华为手机',3999,'华为',23,'2088-03-10'),
(2,'小米手机',2999,'小米',30,'2088-05-15'),
(3,'苹果手机',5999,'苹果',18,'2088-08-20'),
(4,'华为电脑',6999,'华为',14,'2088-06-16'),
(5,'小米电脑',4999,'小米',26,'2088-07-08'),
(6,'苹果电脑',8999,'苹果',15,'2088-10-25'),
(7,'联想电脑',7999,'联想',NULL,'2088-11-11');

2 查询全部【重点】

问题1：查询所有数据的语法是什么？

问题2：查询所有数据并且给字段取别名的语法是什么？

查询全部的表数据

-- 查询表中所有数据
SELECT * FROM 表名;

查询指定字段数据

-- 查询指定列的记录
select 列名1,列名2,... from 表名；

去除重复查询

-- 标准语法
SELECT DISTINCT 列名1,列名2,... FROM 表名;

计算列查询(四则运算)

-- 运算符可以是+ - * /
SELECT 列名1 运算符(+ - * /) 列名2 FROM 表名;
-- 如果某一列为null，可以将其替换为0 
ifnull(列名,0)

-- 查询商品名称和库存，库存数量在原有基础上加10。进行null值判断
SELECT NAME,IFNULL(stock,0)+10 FROM product;

起别名查询

SELECT 列名1 AS 别名1,列名2 AS 别名2,... FROM 表名;  -- AS关键字可以省略不写

查询全部练习

-- 1 查询product表所有数据【练习】
SELECT * FROM product;
-- 2 查询名称、价格、品牌【练习】
SELECT NAME,price,brand FROM product;
-- 3 查询品牌
SELECT brand FROM product;
-- 4 查询品牌，去除重复【练习】
SELECT DISTINCT brand FROM product;
-- 5 查询商品名称和库存，库存数量在原有基础上加10
SELECT NAME,stock+10 FROM product;
-- 6 查询商品名称和库存，库存数量在原有基础上加10。进行null值判断
SELECT NAME,IFNULL(stock,0)+10 FROM product;
-- 7 查询商品名称和库存，库存数量在原有基础上加10。进行null值判断。起别名为getSum【练习】
SELECT NAME,IFNULL(stock,0)+10 AS getSum FROM product;
SELECT NAME,IFNULL(stock,0)+10 getSum FROM product;

3 按条件查询【重点】

条件查询语法

SELECT 列名列表 FROM 表名 WHERE 条件;

查询条件分类

 

条件查询练习

-- 1 查询库存大于20的商品信息
SELECT * FROM product WHERE stock > 20;

-- 2 查询品牌为华为的商品信息
SELECT * FROM product WHERE brand='华为';

-- 3 查询金额在4000 ~ 6000之间的商品信息【练习】
SELECT * FROM product WHERE price >= 4000 AND price <= 6000;
-- where 列名 between 值1 and 值2
SELECT * FROM product WHERE price BETWEEN 4000 AND 6000;
-- 4 查询库存为14、30、23的商品信息【练习】
SELECT * FROM product WHERE stock=14 OR stock=30 OR stock=23;
-- in(值1,值2,值3,...)：表示等于值1或者等于值2或者等于值3,....
SELECT * FROM product WHERE stock IN(14,30,23);
-- 5 查询库存为null的商品信息【练习】
SELECT * FROM product WHERE stock IS NULL;
-- 6 查询库存不为null的商品信息
SELECT * FROM product WHERE stock IS NOT NULL;
/*
	模糊查询语法： where 列名 like '带占位符的字符串' 
	占位符：_表示一个字符的占位符，%表示0个或者多个字符的占位符。
	使用场景：常用于搜索。  
*/
-- 7 查询名称以小米为开头的商品信息
SELECT * FROM product WHERE NAME LIKE '小米%';
-- 8 查询名称第二个字是为的商品信息
SELECT * FROM product WHERE NAME LIKE '_为%';
-- 9 查询名称为四个字符的商品信息
SELECT * FROM product WHERE NAME LIKE '____';
SELECT * FROM product WHERE CHAR_LENGTH(NAME)=4;
-- 10 查询名称中包含电脑的商品信息【练习】
SELECT * FROM product WHERE NAME LIKE '%电脑%';

4 聚合查询【重点】

聚合函数的介绍

用于统计的函数称做聚合函数，是将一列数据作为一个整体，进行纵向的计算。

聚合函数的分类

 

聚合函数查询语法

SELECT 函数名(列名) FROM 表名 [WHERE 条件];

聚合函数查询练习

-- 计算product表中总记录条数
SELECT COUNT(*) FROM product;
-- 获取最高价格
SELECT MAX(price) FROM product;
-- 获取最低库存
SELECT MIN(stock) FROM product;
-- 获取总库存数量
SELECT SUM(stock) FROM product;
-- 获取品牌为苹果的总库存数量
SELECT SUM(stock) FROM product WHERE brand='苹果';
-- 获取品牌为小米的平均商品价格
SELECT AVG(price) FROM product WHERE brand='小米';

5 排序查询【重点】

排序查询语法

SELECT 列名 FROM 表名 [WHERE 条件] ORDER BY 列名1 排序方式1,列名2 排序方式2,...;

==排序方式：ASC-升序，DESC-降序 如果有多个排序条件，只有当前边的条件值一样时，才会判断第二条件。==

排序查询练习

-- 按照库存升序排序
SELECT * FROM product ORDER BY stock ASC;

-- 查询名称中包含手机的商品信息。按照金额降序排序【练习】
SELECT * FROM product WHERE NAME LIKE '%手机%' ORDER BY price DESC;

-- 按照金额升序排序，如果金额相同，按照库存降序排列【练习】
SELECT * FROM product ORDER BY price ASC,stock DESC;

6 分组查询【重点】

分组查询语法

SELECT 列名 FROM 表名 [WHERE 分组前条件] GROUP BY 分组列名 [HAVING 分组后条件] [ORDER BY 列名 排序方式];
/*
	注意1：分组之后如果结果写*，那么查到的是每组第一条数据，没有意义。
	注意2：分组之后的结果集字段名一般是聚合函数或者分组字段。
*/

==注意：如果有分组又有排序，必须要先分组后排序。==

分组查询练习

-- 按照品牌分组，获取每组商品的总金额
SELECT brand,SUM(price) FROM product GROUP BY brand;

-- 对金额大于4000元的商品，按照品牌分组,获取每组商品的总金额【练习】
SELECT brand,SUM(price) FROM product WHERE price > 4000 GROUP BY brand;

-- 对金额大于4000元的商品，按照品牌分组，获取每组商品的总金额，只显示总金额大于7000元的【练习】
SELECT brand,SUM(price) getSum FROM product WHERE price > 4000 GROUP BY brand HAVING getSum > 7000;

-- 对金额大于4000元的商品，按照品牌分组，获取每组商品的总金额，只显示总金额大于7000元的、并按照总金额的降序排列【练习】
SELECT brand,SUM(price) getSum FROM product 
WHERE price > 4000 
GROUP BY brand 
HAVING getSum > 7000 
ORDER BY getSum DESC;

7 分页查询【重点】

分页查询格式

SELECT 列名 FROM 表名 
[WHERE 条件] 
[GROUP BY 分组列名]
[HAVING 分组后条件过滤] 
[ORDER BY 排序列名 排序方式] 
LIMIT 起始索引,每页显示的条数;
-- 公式：起始索引 = (当前页数-1) * 每页显示的条数

==注意：分页查询是一条SQL语句中最后操作。==

分页查询练习

-- 第1页  起始索引=(1-1) * 3 = 0，索引从0开始
SELECT * FROM product LIMIT 0,3;
-- 第2页  起始索引=(2-1) * 3 = 3，索引从3开始
SELECT * FROM product LIMIT 3,3;
-- 第3页  起始索引=(3-1) * 3 = 6，索引从6开始
SELECT * FROM product LIMIT 6,3;

总结：查询语句标准格式

select 	查询的字段列表

from	查询的表

[where	查询的条件]

[group by 分组的列]

[having 分组后的条件]

[order by 排序条件]

[limit	起始索引,每页条数]

总结

一、DDL-数据定义语言，操作数据库(CRUD)和表(CRUD)
1 创建数据库(指定字符集)
create database 数据库名称 character set utf8;
2 创建表
create table 表名称(
   字段名 数据类型,
   字段名 数据类型,
   ... ...
   字段名 数据类型
);

二、DML-数据操作语言，对表中的数据进行增删改
1 添加数据
-- 给指定字段添加数据值
insert into 表名(字段名,字段名,...) values (值1,值2,...);
-- 给所有字段添加数据
insert into 表名 values (值1,值2,...);
-- 批量添加多条数据
insert into 表名(字段名,字段名,...) values (值1,值2,...),(值1,值2,...),...;
insert into 表名 values (值1,值2,...),(值1,值2,...),...;
注意：值和字段要一一对应，个数一样，类型和长度要符合字段要求。

2 修改数据
-- 带条件修改
update 表名 set 字段名=值,字段名=值,... where 条件;
-- 不带条件修改，修改当前列所有数据【慎用】
update 表名 set 字段名=值,字段名=值,...;

3 删除数据
-- 带条件删除
delete from 表名 where 条件;
-- 不带条件删除，删除表中所有数据【慎用】
delete from 表名;

三、DQL-数据查询语言，对表中的数据进行查询
1 查询全部数据
-- 查询指定字段
select 字段名,字段名,... from 表名;
-- 查询所有字段
select * from 表名;
-- 查询字段取别名
select 字段名 AS 别名,字段名 别名,... from 表名;
-- 说明：ifnull(字段名,0)  如果该字段的值是null，那么用0代替参与四则运算。

2 按条件查询
-- 基础条件： > 、 < 、 >= 、 <= 、!= 、 <>(不等于)、  is null 、 is not null
select * from 表名 where 字段名>值;
select * from 表名 where 字段名 is null;
select * from 表名 where 字段名 is not null;
-- 条件连接符：and  or
select * from 表名 where 字段名>=值1 and 字段名<=值2;
-- between 值1 and 值2  等价于 where 字段名>=值1 and 字段名<=值2;
select * from 表名 where 字段名>=值1 and 字段名<=值2;
-- 等价于
select * from 表名 where 字段名 between 值1 and 值2;

-- in()查询
select * from 表名 where 字段名=值1 or 字段名=值2 or 字段名=值3;
-- 等价于
select * from 表名 where 字段名 in(值1,值2,值3);

-- like模糊查询： where 字段名 like '带占位符的字符串';
-- 占位符: _表示一个字符的占位符，%表示任意个数字符的占位符
select * from 表名 where 字段名 like '%电脑%';
	
3 聚合函数
max(字段名)、min(字段名)、sum(字段名)、avg(字段名)、count(字段名)
4 分组查询
关键字：group by 字段名
select 分组字段名/聚合函数 from 表名 [where 条件] group by 字段名 [having 条件];
where和having的区别？
	分组之前使用where，分组之后筛选使用having。
	
5 排序查询
关键字：order by 字段名 ASC/DESC,字段名 ASC/DESC,...
说明1：只有前面的排序过程中两个值相等，才会按照后面的字段去排序。
说明2：如果不写ASC/DESC，那么默认是ASC升序。
select * from 表名 [where 条件] [group by 字段名] [having 条件] order by 字段名 ASC/DESC,字段名 ASC/DESC,...;

6 分页查询
关键字：limit 当前页数据索引,每页条数;  -- 表中的数据索引从0开始
当前页数据索引=(当前页-1)*每页条数;
select * 
from 表名 
[where 条件] 
[group by 字段名] 
[having 条件] 
[order by 字段名 ASC/DESC,字段名 ASC/DESC,...] 
limit  当前页数据索引,每页条数;