Web-第六天 MySQL回顾学习

Web-第六天 MySQL回顾学习

第1章 数据库

1.1 数据库介绍

数据库就是存储数据的仓库，其本质是一个文件系统，数据按照特定的格式将数据存储起来，用户可以通过sql语句对数据库中的数据进行增加，修改，删除及查询操作

1.2 关系型数据库

关系数据库(Relationship DataBase Management System 简写:RDBMS) ，描述是建立在关系模型基础上的数据库，借助于集合代数等数学概念和方法来处理数据库中的数据。说白了就是描述实体与实体之间的关系的数据库.例如用户购物下订单,订单包含商品.他们之间的关系可以通过E-R图表示.

数据库发展历程如下所示：

• 没有数据库，使用磁盘文件存储数据；
• 层次结构模型数据库；
• 网状结构模型数据库；
• 关系结构模型数据库，使用二维表格来存储数据；
• 关系-对象模型数据库；

神奇校车·图画书版（全12册，新增《科学博览会》1册）

作者：(美)乔安娜柯尔 著 ,(美)布鲁斯·迪根 图

当当 广告

购买

1.2.1 常见的关系型数据库

• Oracle数据库：Oracle是殷墟（yīn Xu）出土的甲骨文（oracle bone inscriptions）的英文翻译的第一个单词，在英语里是“神谕”的意思，由Larry Ellison 和另两个编程人员在1977创办，他们开发了自己的拳头产品，在市场上大量销售，1979 年，Oracle公司引入了第一个商用SQL 关系数据库管理系统。Oracle公司是最早开发关系数据库的厂商之一，其产品支持最广泛的操作系统平台。目前Oracle关系数据库产品的市场占有率名列前茅。
• SQL Server数据库：1987 年，微软和 IBM合作开发完成OS/2，IBM 在其销售的OS/2 ExtendedEdition 系统中绑定了OS/2Database Manager，而微软产品线中尚缺少数据库产品。为此，微软将目光投向Sybase，同Sybase 签订了合作协议，使用Sybase的技术开发基于OS/2平台的关系型数据库。1989年，微软发布了SQL Server 1.0 版。
• DB2数据库： 作为关系数据库领域的开拓者和领航人，IBM在1997年完成了System R系统的原型，1980年开始提供集成的数据库服务器—— System/38，随后是SQL/DSforVSE和VM，其初始版本与SystemR研究原型密切相关。DB2 forMVSV1 在1983年推出。该版本的目标是提供这一新方案所承诺的简单性，数据不相关性和用户生产率。1988年DB2 for MVS 提供了强大的在线事务处理（OLTP）支持，1989 年和1993 年分别以远程工作单元和分布式工作单元实现了分布式数据库支持。最近推出的DB2 Universal Database 6.1则是通用数据库的典范，是第一个具备网上功能的多媒体关系数据库管理系统，支持包括Linux在内的一系列平台。
• Sybase数据库：Sybase公司成立于1984年，公司名称“Sybase”取自“system”和 “database” 相结合的含义。Sybase公司的创始人之一Bob Epstein 是Ingres 大学版（与System/R同时期的关系数据库模型产品）的主要设计人员。公司的第一个关系数据库产品是1987年5月推出的Sybase SQLServer1.0。Sybase首先提出Client/Server 数据库体系结构的思想，并率先在Sybase SQLServer 中实现。
• MySQL数据库： mySQL是一个小型关系型数据库管理系统，开发者为瑞典MySQL AB公司。在2008年1月16号被Sun公司收购。而2009年，Sun公司又被Oracle公司收购。目前MySQL被广泛地应用在Internet上的中小型网站中。由于其体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，许多中小型网站为了降低网站总体拥有成本而选择了MySQL作为网站数据库

1.3 数据库管理系统

我们通常将数据库管理系统(DataBase Management System,简写dbms)称为数据库,大白话就是我们安装的软件，当我们安装了数据库之后（数据库服务器），就可以在数据库服务器中创建数据库，每个数据库中还可以包含多张表.

通过上述图我们知道了数据库与表之间的关系，那么我们的数据又是怎样存储在数据库中。

数据库中的表就是一个多行多列的表格。在创建表时，需要指定表的列数，以及列名称，列类型等信息。而不用指定表格的行数，行数是没有上限的。

表中的列我们称之为字段，表中的行我们称之为记录。

用我们熟悉的java程序来与关系型数据对比，就会发现以下对应关系。

类-------表

类中属性-------表中的字段

对象--------记录。

1.4 mysql数据库安装,卸载与配置

mysql的安装与配置详见附件:MySQL的安装.doc http://www.javahelp.com.cn下载

1.5 mysql登录

进入命令窗口

格式:mysql [-h主机地址] -u用户名 -p[密码]

启动mysql服务命令 net start mysql

关闭mysql服务命令 net stop mysql

1.6 mysql数据库密码重置

a. 停止mysql服务器 运行输入services.msc 停止mysql服务

b. 在cmd下,输入mysqld --console --skip-grant-tables 启动服务器,出现一下页面,不要关闭该窗口

c. 新打开cmd,输入mysql -uroot 不需要密码

use mysql;

update user set password=password('abc') WHERE User='root';

d. 关闭两个cmd窗口

第2章 SQL语句

2.1 SQL介绍

SQL:结构化查询语言(Structured Query Language),是一种数据库查询和程序设计语言，用于存取、查询、更新数据以及管理关系数据库系统.

2.2 SQL分类

2.2.1 数据定义语言：简称DDL(Data Definition Language)

用来定义数据库对象：数据库，表，列等，例如创建、删除、修改数据库和表结构等；

2.2.2 数据操作语言：简称DML(Data Manipulation Language)

用来对数据库中表的记录进行更新，例如：增、删、改表记录；

2.2.3 数据查询语言：简称DQL(Data Query Language)

用来查询数据库中表的记录。

2.2.4 数据控制语言：简称DCL(Data Control Language)

用来定义数据库的访问权限和安全级别，及创建用户；

DDL是对数据库或表的结构进行操作，而DML是对表的记录进行操作(增、删、改)。我们以后最常用的就是DDL、DML、DQL。

2.3 书写注意事项

SQL语句可以单行或多行书写，以分号结尾

MySQL数据库的SQL语句不区分大小写，建议使用大写，例如：SELECT * FROM user

2.4 DDL数据定义语言

2.4.1 对数据库操作

2.4.1.1 创建数据库create database 数据库名称;

例如:create database day04;-- 使用数据库默认字符集.

扩展:

格式:

create database [if not exists] 数据库名称 [character set 字符集] [collate 校对规则];

例如:

create database day0401 character set utf8;-- 使用指定的编码创建数据库

create database day0402 character set utf8 collate utf8_general_ci;-- 使用指定的编码和校对规则创建数据库

2.4.1.2 删除数据库drop database数据库名称;

例如:drop database day0402;

2.4.1.3 修改数据库alter database 数据库名称 character set 编码 collate 校对规则

修改数据库编码或者校对规则

例如:alter database day0401 character set gbk;-- 修改其字符集为gbk

2.4.2 操作数据库常见命令

2.4.2.1 查看所有数据库show databases;

2.4.2.2 查看数据库定义信息show create database 数据库名称

例如:show create database day04;

2.4.2.3 查看字符集和校对规则show character set;

2.4.2.4 切换数据库:use 数据库名称;

例如:use day04;

2.4.2.5 查看当前使用的数据库select database();

2.4.2.6 查看当前数据库下所有的表show tables;

2.4.3 mysql的数据类型

2.4.4 对数据表的操作（建表前要切换到数据库use 数据库名;）

2.4.4.1 创建表create table 表名(字段描述 ,字段描述 ,....);-- 最后一个字段没有逗号

字段描述格式:

字段名称 字段类型 [字段约束]

例如:

create table user(

id int,

username varchar(50),

password varchar(20)

);

练习:

创建一个张表user,该表具有以下字段:

id 整型 主键 自动增长

username 字符串 长度20 非空

password 字符串 长度20 非空

gender 字符串 长度10

email 字符串 长度50 唯一 非空

role 字符串 长度10 默认值 “admin”

registTime 时间戳

2.4.4.2 修改表

修改表添加列alter table 表名 add 列名 类型(长度) [约束];

例如：

#1，为分类表添加一个新的字段为 分类描述 varchar(20)

ALTER TABLE category ADD `desc` VARCHAR(20);

修改表修改列的类型长度及约束alter table 表名 modify 列名 类型(长度) 约束;

例如：

#2, 为分类表的描述字段进行修改，类型varchar(50) 添加约束 not null

ALTER TABLE category MODIFY `desc` VARCHAR(50) NOT NULL;

修改表修改列名alter table 表名 change 旧列名 新列名 类型(长度) 约束;

例如：

#3, 为分类表的分类名称字段进行更换 更换为 snamesname varchar(30)

ALTER TABLE category CHANGE `desc` description VARCHAR(30);

修改表删除列alter table 表名 drop 列名;

例如：

#4, 删除分类表中snamename这列

ALTER TABLE category DROP description;

修改表名rename table 表名 to 新表名;

例如：

#5, 为分类表category 改名成 category2

RENAME TABLE category TO category2;

修改表的字符集alter table 表名 character set 字符集;

例如：

#6, 为分类表 category 的编码表进行修改，修改成 gbk

ALTER TABLE category CHARACTER SET gbk;

2.4.4.3 删除表drop table 表名;

例如:drop table user;

2.4.5 常见的命令

2.4.5.1 查看表结构desc 表名称;

例如:desc user;

2.4.5.2 查看建表语句show create table 表名称;

例如: show create table user01;

2.5 约束

2.5.1 主键约束PRIMARY KEY

PRIMARY KEY 约束唯一标识数据库表中的每条记录。

主键必须包含唯一的值。

主键列不能包含 NULL 值。

每个表都应该有一个主键，并且每个表只能有一个主键。

2.5.1.1 添加主键约束

方式一：创建表时，在字段描述处，声明指定字段为主键：

CREATE TABLE Persons

(

Id_P int PRIMARY KEY,

LastName varchar(255),

FirstName varchar(255),

Address varchar(255),

City varchar(255)

)

方式二：创建表时，在constraint约束区域，声明指定字段为主键：

• 格式：[constraint 名称] primary key (字段列表)
• 关键字constraint可以省略，如果需要为主键命名，constraint不能省略，主键名称一般没用。
• 字段列表需要使用小括号括住，如果有多字段需要使用逗号分隔。声明两个以上字段为主键，我们称为联合主键。

CREATE TABLE Persons

(

FirstName varchar(255),

LastName varchar(255),

Address varchar(255),

City varchar(255),

CONSTRAINT pk_PersonID PRIMARY KEY (FirstName,LastName)

)

或

CREATE TABLE Persons

(

FirstName varchar(255),

LastName varchar(255),

Address varchar(255),

City varchar(255),

PRIMARY KEY (FirstName,LastName)

)

方式三：创建表之后，通过修改表结构，声明指定字段为主键：

ALTER TABLE Persons ADD [CONSTRAINT 名称] PRIMARY KEY (字段列表)

CREATE TABLE Persons

(

FirstName varchar(255),

LastName varchar(255),

Address varchar(255),

City varchar(255)

)

ALTER TABLE Persons ADD PRIMARY KEY (FirstName,LastName)

2.5.1.2 删除主键约束

如需撤销 PRIMARY KEY 约束，请使用下面的 SQL：

ALTER TABLE Persons DROP PRIMARY KEY

2.5.2 自动增长列auto-increment

我们通常希望在每次插入新记录时，数据库自动生成字段的值。

我们可以在表中使用 auto-increment（自动增长列）关键字，自动增长列类型必须是整形，自动增长列必须为键(一般是主键)。

• 下列 SQL 语句把 "Persons" 表中的 "P_Id" 列定义为 auto-increment 主键

CREATE TABLE Persons

(

P_Id int PRIMARY KEY AUTO_INCREMENT,

LastName varchar(255),

FirstName varchar(255),

Address varchar(255),

City varchar(255)

)

• 向persons添加数据时，可以不为P_Id字段设置值，也可以设置成null，数据库将自动维护主键值：

INSERT INTO Persons (FirstName,LastName) VALUES ('Bill','Gates')

INSERT INTO Persons (P_Id,FirstName,LastName) VALUES (NULL,'Bill','Gates')

• 面试：delete和truncate的区别
  • Delete删除表中的数据，但不重置auto-increment记录数。
  • Truncate删除表中的数据，auto-increment记录数将重置。Truncate其实先删除表然后再创建表。
• 扩展：默认地，AUTO_INCREMENT 的开始值是 1，如果希望修改起始值，请使用下列 SQL 语法：

ALTER TABLE Persons AUTO_INCREMENT=100

2.5.3 非空约束NOT NULL

NOT NULL 约束强制列不接受 NULL 值。

NOT NULL 约束强制字段始终包含值。这意味着，如果不向字段添加值，就无法插入新记录或者更新记录。

• 下面的 SQL 语句强制 "Id_P" 列和 "LastName" 列不接受 NULL 值：

CREATE TABLE Persons

(

Id_P int NOT NULL,

LastName varchar(255) NOT NULL,

FirstName varchar(255),

Address varchar(255),

City varchar(255)

)

2.5.4 唯一约束UNIQUE

UNIQUE 约束唯一标识数据库表中的每条记录。

UNIQUE 和 PRIMARY KEY 约束均为列或列集合提供了唯一性的保证。

PRIMARY KEY 拥有自动定义的 UNIQUE 约束。

请注意，每个表可以有多个 UNIQUE 约束，但是每个表只能有一个 PRIMARY KEY 约束。

2.5.4.1 添加唯一约束

方式1：创建表时，在字段描述处，声明唯一：

CREATE TABLE Persons

(

Id_P int UNIQUE,

LastName varchar(255) NOT NULL,

FirstName varchar(255),

Address varchar(255),

City varchar(255)

)

方式2：创建表时，在约束区域，声明唯一：

CREATE TABLE Persons

(

Id_P int,

LastName varchar(255) NOT NULL,

FirstName varchar(255),

Address varchar(255),

City varchar(255),

CONSTRAINT 名称UNIQUE (Id_P)

)

方式3：创建表后，修改表结构，声明字段唯一：

ALTER TABLE Persons ADD [CONSTRAINT 名称] UNIQUE (Id_P)

2.5.4.2 删除唯一约束

• 如需撤销 UNIQUE 约束，请使用下面的 SQL：

ALTER TABLE Persons DROP INDEX 名称

• 如果添加唯一约束时，没有设置约束名称，默认是当前字段的字段名。

2.5.5 默认约束（default）

还可以使用default,给定一个默认值.

注意：不插入该字段时 使用默认值

2.5.6 外键约束FOREIGN KEY

FOREIGN KEY 表示外键约束，将在多表中学习

2.6 创建表加入约束示例：

id 主键自增长

username 字符串 长度20 不能为空

password 字符串 长度20 不能为空

gender 字符串10

age 整型 默认25

email 字符串 长度50 非空 唯一

salary 浮点型 长度8其中2位小数

state 整型 默认值0

dept 字符串长度10 默认咨询部

registTime 日期

create table user(

id int primary key auto_increment,

username varchar(20) not null,

password varchar(20) not null,

gender varchar(10),

age int default 25,

email varchar(50) not null unique,

salary double(8,2),

state int default 0,

dept varchar(10) default '咨询部',

registTime date

);

2.7 字段类型

2.8 DML数据操作语言

2.8.1 插入数据insert

2.8.1.1 不指定列插入

格式:insert into 表名 values(字段值1,字段值2,...);

例如:insert into user values(null,'laobai','1234','male','laobai@126.com',null,null);

注意:

• 没有给出要插入的列，表示插入所有列；

值的个数必须是该表的列的个数、插入的值与列名相对应；

值的顺序，必须与表创建时给出的列的顺序相同。

2.8.1.2 插入指定列值

格式:insert into 表名(字段名1,字段名2,...) values(字段值1,字段值2,...);

例如:insert into user(username,password,email,registTime)

values('laoqi','1234','laoqi@126.com',null);

注意:表名后面是当前表中部分字段名称

2.8.1.3 插入全部列值

格式:

insert into 表名(字段名1,字段名2,...) values(字段值1,字段值2,...);

例如:

insert into user(id,username,password,gender,email,role,registTime)

values(1,'shijin','1234','male','shijin@126.com',null,null);

注意:

表名后面是当前表中所有字段(列)

多个字段之间使用逗号分隔

字段值必须使用引号（建议单引号）,如果是整型数据引号可以省略。

2.8.1.4 注意(编码问题):

插入中文的时候会报错,如下图:

因为mysql的客户端编码的问题我们的是utf8,而系统的cmd窗口编码是gbk.

查看所有的mysql编码

show variables like 'character%';

解决方案:

1.临时修改

set 变量名称=gbk;

例如:

set character_set_client=gbk,character_set_connection=gbk,character_set_results=gbk;

重启mysql服务器后,就回复原样了.

2.永久修改

在mysql安装目录下,

2.8.2 更新数据update

2.8.2.1 更新所有记录的指定字段

update 表名 set 字段名1=字段值1, 字段名2=字段值2, ..... ;

2.8.2.2 更新符合条件记录的指定字段

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

例如:

1.update user set gender='male' ;## 修改所有人的性别为’male’

2.update user set username='xusanduo',email='xusanduo@qq.com' where id=3;## 修改id为3的人username和email.

2.8.2.3 注意：

• 列名的类型与修改的值要一致.
• 修改值得时候不能超过最大长度.
• 除了数值类型外，其它的字段类型的值必须使用引号引起

2.8.3 删除数据delete

格式:delete from 表名 [where 条件];

例如:

1.delete from user;## 删除所有

2.delete from user where id=6; ##删除id为6的记录.

2.8.3.1 删除整张表的内容,两种实现方式:

1.delete from 表名;

2.truncate [table] 表名;

区别:

1.delete属于DML语句 ,truncate属于DDL语句

2.delete是一行一行删除,truncate是将表结构销毁,再重新创建表结构,数据多的时候,效率高.

2.8.3.2 面试题删除表中所有记录使用delete from 表名; 还是用truncate table 表名;

删除方式：delete 一条一条删除，不清空auto_increment记录数。受事务控制。

truncate 直接将表删除，重新建表，auto_increment将置为零，从新开始。不受事务控制

2.9 DQL数据查询语言

2.9.1 准备工作

#创建商品表：

create table product(

pid int primary key auto_increment,

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,'JACK JONES',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,'果9',1,NULL);

2.9.2 语法：

select [distinct]

* | 列名,列名

from 表

where 条件

2.9.3 基本查询select

2.9.3.1 查询指定列select 字段 from 表名;

例如：查询商品名和商品价格. select pname,price from product;

2.9.3.2 查询指定字段信息,如果要查询多个字段select 字段1,字段2,...from 表名;

2.9.3.3 查询所有列select * from 表名;

例如：

查询所有的商品. select * from product;

注意:使用"*"在练习，学习过程中可以使用，在实际开发中，不建议使用。

2.9.3.4 去掉重复记录select distinct 字段 from 表名;

例如：去掉重复值. select distinct price from product;

代表当pname与price都重复时才去重两个条件

select distinct pname,price from product;

注意:distinct它的作用是去除重复.

2.9.3.5 使用别名

别名查询.使用的关键字是as（as可以省略的）.

1.表别名: select * from product as p;

2.列别名：select pname as pn from product;

2.9.3.6 在查询中可以直接对列进行运算

我们在sql操作中，可以直接对列进行运算。

例如：查询结果是表达式（运算查询）：将所有商品的价格+10元进行显示.

select pname,price+10 from product;

注意：null进行运算结果还是null，想要null运算有值，则使用ifnull函数即可；

2.9.3.7 ifnull函数使用，给null赋值

在对数值类型的列做运算的时候，如果做运算的列的值为null的时，运算结果都为null，为了解决这个问题可以使用ifnull函数,使用方法 ifnull(字段,0)

2.9.4 条件查询>/</<=/>=/=/<>/!=/BETWEEN..AND.../IN()/LINK/IS NULL/IS NOT NULL/AND/OR/NOT

null值操作

is null; 判断为空

is not null; 判断不为空

单条件查询

#查询商品名称为“花花公子”的商品所有信息：

SELECT * FROM product WHERE pname = '花花公子'

#查询价格为800商品

SELECT * FROM product WHERE price = 800

#查询价格不是800的所有商品

SELECT * FROM product WHERE price != 800

SELECT * FROM product WHERE price <> 800

SELECT * FROM product WHERE NOT(price = 800)

#查询商品价格大于60元的所有商品信息

SELECT * FROM product WHERE price > 60;

范围查询和BETWEEN AND

#查询商品价格在200到1000之间所有商品

SELECT * FROM product WHERE price >= 200 AND price <=1000;

SELECT * FROM product WHERE price BETWEEN 200 AND 1000;

and or和枚举in查询

#查询商品价格是200或800的所有商品

SELECT * FROM product WHERE price = 200 OR price = 800;

SELECT * FROM product WHERE price IN (200,800);

模糊查询

#查询含有'霸'字的所有商品

SELECT * FROM product WHERE pname LIKE '%霸%';

#查询以'香'开头的所有商品

SELECT * FROM product WHERE pname LIKE '香%';

#查询第二个字为'想'的所有商品

SELECT * FROM product WHERE pname LIKE '_想%';

空查询

#商品没有分类的商品

SELECT * FROM product WHERE category_id IS NULL

#查询有分析的商品

SELECT * FROM product WHERE category_id IS NOT NULL

2.9.5 排序升序ASC、降序DESC

通过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;

2.9.6 聚合COUNT/SUM/MAX/MIN/AVG

之前我们做的查询都是横向查询，它们都是根据条件一行一行的进行判断，而使用聚合函数查询是纵向查询，它是对一列的值进行计算，然后返回一个单一的值；另外聚合函数会忽略空值。

今天我们学习如下五个聚合函数：

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

注意：聚合函数忽略null的存在；

注意：聚合函数一般情况下（除了分组的字段外）不与其他不字段一起查询；

#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;

2.9.7 分组GROUP BY--having&where区别

分组查询是指使用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;

2.9.8 分页查询limit index,length

分页查询相应的sql是方言(不同的数据库特有的语法sql)

mysql：limit index,length

oracle: rownum

sqlServer: top

select * from user limit 0,3;

推断：页面与起始索引的关系

页数 每页条数 起始索引 关系

1 3 0 (1-1)*3

2 3 3 (2-1)*3

3 3 6 (3-1)*3

4 3 9 (4-1)*3

公式： 起始索引 = (页数-1)*每页显示条数

2.9.9 DQL语句操作总结

综合我们学习的查询相关关键字：select，from，where，group by，having，order by；它们的执行顺序是如下：

lfrom：首先执行from，找到要查询的表；

lwhere：判断条件，筛选符合条件所有记录；

lgroup by：根据之前操作对记录按照指定列进行分组

lhaving：对分组后的信息进行筛选；

lselect：选择所需要的列信息；

lorder by：对查询信息进行排序。

在SQL语言中，第一个被处理的子句是from字句，尽管select字句最先出现，但是几乎总是最后被处理。

2.10 DQL练习

2.10.1 数据准备

id 主键自增长

username 字符串 长度20 不能为空

password 字符串 长度20 不能为空

gender 字符串10

age 整型 默认25

email 字符串 长度50 非空 唯一

salary 浮点型 长度8其中2位小数

state 整型 默认值0

dept 字符串长度10 默认咨询部

registTime 日期

create table user(

id int primary key auto_increment,

username varchar(20) not null,

password varchar(20) not null,

gender varchar(10),

age int default 25,

email varchar(50) not null unique,

salary double(8,2),

state int default 0,

dept varchar(10) default '咨询部',

registTime date

);

insert into user values(null,'张青青','zs123','男',18,'zs@javahelp.com.cn',5000,1,'财务部','2015-09-10 12:18:38');

insert into user values(null,'边海鹏','bhp','男',32,'bhp@javahelp.com.cn',4500,0,'咨询部','2015-09-12 12:18:38');

insert into user values(null,'张淑敏','753951','女',42,'zsm@javahelp.com.cn',5200,1,'技术部','2015-09-18 12:18:38');

insert into user values(null,'杨志勇','pangzi','男',29,'yzy@javahelp.com.cn',null,0,'技术部','2015-08-10 12:18:38');

insert into user values(null,'范瑾','fj123','女',22,'fj@javahelp.com.cn',3000,1,'咨询部','2014-09-19 12:18:38');

insert into user values(null,'多博','db123','女',25,'db@javahelp.com.cn',8000,0,'技术部','2014-09-10 12:18:38');

insert into user values(null,'张章婉航','zsf123','女',58,'zsff@javahelp.com.cn',12000,0,'技术部','2015-03-28 12:18:38');

insert into user values(null,'杨秀清','yxq123','女',42,'yxq@javahelp.com.cn',5900,1,'财务部','2015-09-10 12:18:38');

insert into user values(null,'孙志刚','szg123','男',18,'szg@javahelp.com.cn',8000,0,'财务部','2015-09-10 12:18:38');

insert into user values(null,'张英','wj123','男',33,'zyj@javahelp.com.cn',8500,1,'技术部','2014-12-12 12:18:38');

2.10.2 数据的查询操作

2.10.2.1 查询全部select * from user;

2.10.2.2 查询部分select 字段,字段 from user;

2.10.2.3 去重查询----distinct ：select distinct gender,username from user;

代表当gender与username都重复时才去重

2.10.2.4 运算查询

select age+50 as '年龄' from user;

select salary+1000 '薪资' from user;

注意：null进行运算结果还是null

注意：函数 ifnull(字段,值) --- 当字段为null时 赋值为多少

select ifnull(salary,0)+1000 '薪资' from user;

2.10.2.5 聚合函数

count():计数

sum():求和

avg():平均值

max():最大值

min():最小值

注意：聚合函数忽略null的存在

注意：聚合函数一般情况下(除了分组的字段外)不与其他字段一起查询

2.10.2.6 排序查询----order by 升序asc、降序desc

select * from user order by salary;

select * from user order by salary,age desc;多字段排序

2.10.2.7 条件查询

1) 单条件 =/!=/<>/>=/<=/>/<

查询年龄小于35的

select * from user where age<35; = != <> >= <= > <

2) 多条件 --- and or

查询年龄小于35并且性别是女的

select * from user where age<35 and gender='女';

查询年龄小于35并且性别是女的，或者性别是男工资大于8000

select * from user where age<35 and gender='女' or gender='男' and salary>8000;

注意：and的优先级大于or

3) 空查询is null、is not null

select * from user where salary is null;

select * from user where salary is not null;

4) 范围查询 ---- between and

查询工资大于等于3000并且小于等于8000的

select * from user where salary>=3000 and salary<=8000;

select * from user where salary between 3000 and 8000;

查询14年注册的人

select * from user where registTime>='2014-01-01 00:00:00' and registTime<='2014-12-31 23:59:59';

select * from user where registTime between '2014-01-01 00:00:00' and '2014-12-31 23:59:59';

5) 枚举查询 --- 对or关键字的增强 in

select * from user where age=18 or age=22 or age=25;

select * from user where age in(18,22,25);

6) 模糊查询 like % _

%:代表任意多个字符

_:代表任意一个字符

select * from user where username like '张%';

select * from user where username like '张__';

select * from user where username like '___';

2.10.2.8 分组查询 ---- group by ... having

查询每个部门的总薪资

select sum(salary) from user group by dept;

select dept,sum(salary) from user group by dept;

select dept,sum(salary) from user group by dept having sum(salary)>10000;

having 与 where的区别？

where对分组前的筛选

having对分组后的数据筛选

select dept,sum(salary) from user where salary>5000 group by dept having sum(salary)>10000;

2.10.2.9 分页查询 limit index,length

分页查询相应的sql是方言(不同的数据库特有的语法sql)

mysql：limit index,length

oracle: rownum

sqlServer: top

select * from user limit 0,3;

推断：页面与起始索引的关系

页数 每页条数 起始索引 关系

1 3 0 (1-1)*3

2 3 3 (2-1)*3

3 3 6 (3-1)*3

4 3 9 (4-1)*3

公式： 起始索引 = (页数-1)*每页显示条数

第3章 数据库备份与恢复

3.1 命令方式

3.1.1 备份

数据库的备份是指将数据库转换成对应的sql文件。

数据库导出sql脚本的格式：

mysqldump -u用户名 -p密码 数据库名>生成的脚本文件路径

例如:

mysqldump -uroot -p1234 day0401>d:\day04.sql

以上备份数据库的命令中需要用户名和密码，即表明该命令要在用户没有登录的情况下使用

3.1.2 恢复

数据库的恢复指的是使用备份产生的sql文件恢复数据库，即将sql文件中的sql语句执行就可以恢复数据库内容。因为数据库备份只是备份了数据库内容，所以备份产生的sql文件中没有创建数据库的sql语句，在恢复数据库之前需要自己动手创建数据库。

• 在数据库外恢复

格式:mysql -uroot -p密码 数据库名 < 文件路径

例如:mysql -uroot -p1234 day0401<d:\day04.sql

注意:要求数据库必须先创建出来.

• 在数据库内恢复

格式:source SQL脚本路径

例如:source d:\day0401.sql

注意:使用这种方式恢复数据，首先要登录数据库.

3.2 图形化SQL yog

3.2.1 SQL备份

选中数据库，右键 ”备份/导出” ， 指定导出路径，保存成.sql文件即可。

3.2.2 SQL恢复

数据库列表区域右键“从SQL转储文件导入数据库”， 指定要执行的SQL文件，执行即可。

第4章 多表操作

实际开发中，一个项目通常需要很多张表才能完成。例如：一个商城项目就需要分类表(category)、商品表(products)、订单表(orders)等多张表。且这些表的数据之间存在一定的关系，接下来我们将在单表的基础上，一起学习多表方面的知识。

4.1 外键约束

现在我们有两张表“分类表”和“商品表”，为了表明商品属于哪个分类，通常情况下，我们将在商品表上添加一列，用于存放分类cid的信息，此列称为：外键

此时“分类表category”称为：主表，“cid”我们称为主键。“商品表products”称为：从表，category_id称为外键。我们通过主表的主键和从表的外键来描述主外键关系，呈现就是一对多关系。

外键特点：

• 从表外键的值是对主表主键的引用。
• 从表外键类型，必须与主表主键类型一致。
• 声明外键约束

语法：alter table 从表 add [constraint] [外键名称] foreign key (从表外键字段名) references 主表 (主表的主键);

[外键名称] 用于删除外键约束的，一般建议“_fk”结尾

alter table 从表 drop foreign key 外键名称

• 使用外键目的:

保证数据的一致性和完整性

4.2 表与表之间的关系

4.2.1 一对多关系：

• 常见实例：客户和订单，分类和商品，部门和员工.
• 一对多建表原则：在从表(多方)创建一个字段，字段作为外键指向主表(一方)的主键.

4.2.2 多对多关系：

• 常见实例：学生和课程、用户和角色
• 多对多关系建表原则：需要创建第三张表,中间表中至少两个字段，这两个字段分别作为外键指向各自一方的主键.

4.2.3 一对一关系：(了解)

• 在实际的开发中应用不多.因为一对一可以创建成一张表.
• 两种建表原则：
  • 外键唯一：主表的主键和从表的外键（唯一），形成主外键关系，外键唯一unique。
  • 外键是主键：主表的主键和从表的主键，形成主外键关系。

4.3 多表设计原则

实际开发中表与表的关系包括如下三种：

一对一：

一对一的两张表一般都可以合并成为一张表，但基于如下两个原因可能会将一张表拆分张两张一对一关系的表

原因一：要使两张表的语义更加明确，例如用户表和用户帐户表

原因二：将经常查询的字段放到一张表中，不经常查询的数据放到一张表中

一对多：

如：类别表和商品表、用户表和订单表

一对多建表原则：在多的一方（从表）中创建外键与一的一方（主表）的主键进行关联。 父子关系。

多对多：

如：订单表与商品表、用户表和角色表、角色表和功能表

多对多的建表原则：在两张表的中间建立一个关系表维护两张表的多余多的关系

注意：怎么看两张表的关系？

看一条分方向

4.4 一对多操作

4.4.1 分析

• category分类表，为一方，也就是主表，必须提供主键cid
• products商品表，为多方，也就是从表，必须提供外键category_id

4.4.2 实现：分类和商品

###创建分类表

create table category(

cid varchar(32) PRIMARY KEY ,

cname varchar(100) #分类名称

);

# 商品表

CREATE TABLE `products` (

`pid` varchar(32) PRIMARY KEY ,

`name` VARCHAR(40) ,

`price` DOUBLE

);

#添加外键字段

alter table products add column category_id varchar(32);

#添加约束

alter table products add constraint product_fk foreign key (category_id) references category (cid);

4.4.3 操作

#1 向分类表中添加数据

INSERT INTO category (cid ,cname) VALUES('c001','服装');

#2 向商品表添加普通数据,没有外键数据，默认为null

INSERT INTO products (pid,pname) VALUES('p001','商品名称');

#3 向商品表添加普通数据，含有外键信息(数据存放在)

INSERT INTO products (pid ,pname ,category_id) VALUES('p002','商品名称2','c001');

#4 向商品表添加普通数据，含有外键信息(数据不存在) -- 不能异常

INSERT INTO products (pid ,pname ,category_id) VALUES('p003','商品名称2','c999');

#5 删除指定分类(分类被商品使用) -- 执行异常

DELETE FROM category WHERE cid = 'c001';

4.5 多对多

4.5.1 分析

• 商品和订单多对多关系，将拆分成两个一对多。
• products商品表，为其中一个一对多的主表，需要提供主键pid
• orders 订单表，为另一个一对多的主表，需要提供主键oid
• orderitem中间表，为另外添加的第三张表，需要提供两个外键oid和pid

4.5.2 实现：订单和商品

### 商品表[已存在]

### 订单表

create table `orders`(

`oid` varchar(32) PRIMARY KEY ,

`totalprice` double #总计

);

### 订单项表

create table orderitem(

oid varchar(50),-- 订单id

pid varchar(50)-- 商品id

);

###---- 订单表和订单项表的主外键关系

alter table `orderitem` add constraint orderitem_orders_fk foreign key (oid) references orders(oid);

###---- 商品表和订单项表的主外键关系

alter table `orderitem` add constraint orderitem_product_fk foreign key (pid) references products(pid);

### 联合主键（可省略）

alter table `orderitem` add primary key (oid,pid);

4.5.3 操作

#1 向商品表中添加数据

INSERT INTO products (pid,pname) VALUES('p003','商品名称');

#2 向订单表中添加数据

INSERT INTO orders (oid ,totalprice) VALUES('x001','998');

INSERT INTO orders (oid ,totalprice) VALUES('x002','100');

#3向中间表添加数据(数据存在)

INSERT INTO orderitem(pid,oid) VALUES('p001','x001');

INSERT INTO orderitem(pid,oid) VALUES('p001','x002');

INSERT INTO orderitem(pid,oid) VALUES('p002','x002');

#4删除中间表的数据

DELETE FROM orderitem WHERE pid='p002' AND oid = 'x002';

#5向中间表添加数据(数据不存在) -- 执行异常

INSERT INTO orderitem(pid,oid) VALUES('p002','x003');

#6删除商品表的数据 -- 执行异常

DELETE FROM products WHERE pid = 'p001';

第5章 多表关系实战

5.1 实战1：省和市

• 方案1：多张表，一对多

• 方案2：一张表，自关联一对多

5.2 实战2：用户和角色

• 多对多关系

5.3 实战3：角色和权限

• 多对多关系

5.4 实战4：客户和联系人(可选)

• 一对多：一个客户服务于多个联系人

第6章 多表查询

CREATE TABLE category (

cid VARCHAR(32) PRIMARY KEY ,

cname VARCHAR(50)

);

CREATE TABLE products(

pid VARCHAR(32) PRIMARY KEY ,

pname VARCHAR(50),

price INT,

flag VARCHAR(2), #是否上架标记为：1表示上架、0表示下架

category_id VARCHAR(32),

CONSTRAINT products_fk FOREIGN KEY (category_id) REFERENCES category (cid)

);

6.1 初始化数据

#分类

INSERT INTO category(cid,cname) VALUES('c001','家电');

INSERT INTO category(cid,cname) VALUES('c002','服饰');

INSERT INTO category(cid,cname) VALUES('c003','化妆品');

#商品

INSERT INTO products(pid, pname,price,flag,category_id) VALUES('p001','联想',5000,'1','c001');

INSERT INTO products(pid, pname,price,flag,category_id) VALUES('p002','海尔',3000,'1','c001');

INSERT INTO products(pid, pname,price,flag,category_id) VALUES('p003','雷神',5000,'1','c001');

INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p004','JACK JONES',800,'1','c002');

INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p005','真维斯',200,'1','c002');

INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p006','花花公子',440,'1','c002');

INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p007','劲霸',2000,'1','c002');

INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p008','香奈儿',800,'1','c003');

INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p009','相宜本草',200,'1','c003');

6.2 多表查询

6.2.1 交叉连接查询(基本不会使用-得到的是两个表的乘积) [了解]

• 语法：select * from A,B;

6.2.2 内连接查询(使用的关键字 inner join -- inner可以省略)

6.2.2.1 隐式内连接：select * from A,B where 条件;

6.2.2.2 显示内连接：select * from A inner join B on 条件;

6.2.3 外连接查询(使用的关键字 outer join -- outer可以省略)

6.2.3.1 左外连接：left outer join

• select * from A left outer join B on 条件;

6.2.3.2 右外连接：right outer join

• select * from A right outer join B on 条件;

#1.查询哪些分类的商品已经上架

#隐式内连接

SELECT DISTINCT c.cname FROM category c , products p

WHERE c.cid = p.category_id AND p.flag = '1';

#内连接

SELECT DISTINCT c.cname FROM category c

INNER JOIN products p ON c.cid = p.category_id

WHERE p.flag = '1';

#2.查询所有分类商品的个数

#左外连接

INSERT INTO category(cid,cname) VALUES('c004','奢侈品');

SELECT cname,COUNT(category_id) FROM category c

LEFT OUTER JOIN products p

ON c.cid = p.category_id

GROUP BY cname;

6.3 子查询一条select语句结果作为另一条select语法一部分（查询条件，查询结果，表等）。

select ....查询字段 ... from ... 表.. where ... 查询条件

#3 子查询, 查询“化妆品”分类上架商品详情

#隐式内连接

SELECT p.* FROM products p , category c

WHERE p.category_id=c.cid AND c.cname = '化妆品';

#子查询

##作为查询条件

SELECT * FROM products p

WHERE p.category_id =

(

SELECT c.cid FROM category c

WHERE c.cname='化妆品'

);

##作为另一张表

SELECT * FROM products p ,

(SELECT * FROM category WHERE cname='化妆品') c

WHERE p.category_id = c.cid;

#查询“化妆品”和“家电”两个分类上架商品详情

SELECT * FROM products p

WHERE p.category_id in

(

SELECT c.cid FROM category c

WHERE c.cname='化妆品' or c.name='家电'

);

第7章 多表查询练习

7.1 多表查询-内连接

数据准备:

练习:

1.查询用户的订单,没有订单的用户不显示

2.查询所有用户的订单详情

3.查询所有订单的用户详情

内连接(inner join)是应用程序中用的普遍的"连接"操作，它一般都是默认连接类型。内连接基于连接谓词将两张表(如 A 和 B)的列组合在一起，产生新的结果表。查询会将 A 表的每一行和 B 表的每一行进行比较，并找出满足连接谓词的组合。当连接谓词被满足，A 和 B 中匹配的行会按列组合(并排组合)成结果集中的一行。连接产生的结果集，可以定义为首先对两张表做笛卡尔积(交叉连接) -- 将 A 中的每一行和 B 中的每一行组合，然后返回满足连接谓词的记录。实际上 SQL 产品会尽可能用其他方式去实现连接，笛卡尔积运算是非常没效率的.

SQL 定义了两种不同语法方式去表示"连接"。首先是"显式连接符号"，它显式地使用关键字 JOIN，其次是"隐式连接符号"，它使用所谓的"隐式连接符号"。隐式连接符号把需要连接的表放到 SELECT 语句的 FROM 部分，并用逗号隔开。这样就构成了一个"交叉连接"，WHERE 语句可能放置一些过滤谓词(过滤条件)。那些过滤谓词在功能上等价于显式连接符号.

常见的内连接有相等连接和交叉连接.

7.1.1 交叉连接(cross join)

又称笛卡尔连接(cartesian join)或叉乘(Product)，它是所有类型的内连接的基础。把表视为行记录的集合，交叉连接即返回这两个集合的笛卡尔积。这其实等价于内连接的链接条件为"永真"，或连接条件不存在.笛卡尔积引用自数学，在数学中，两个集合X和Y的笛卡尔积（Cartesian product），又称直积。假设集合A={a,b}，集合B={0,1,2}，则两个集合的笛卡尔积为{(a,0),(a,1),(a,2),(b,0),(b,1),(b,2)}。可以扩展到多个集合的情况。

7.1.2 显示内连接（inner join…on…）

例如:

select * from user inner join orders on user.id =orders.user_id;

通过on后面的条件，将两张表中的数据过滤。这种写法可以将inner省略，因为join默认就是内连接(inner join),，省略inner后的语句变为

select * from user join orders on user.id=orders.user_id;

效果与写上inner是一样的。

执行的结果如下:

7.1.3 隐式内连接（省略inner join和on）

我们在实际开发中,它的使用频率是最高的，其实就是将inner join省略，也不在使用on进行条件过滤，而是直接使用where进行过滤。如下面

select * from user ,orders where user.id=orders.user_id;

结果与inner join on一样。

总结:

inner join on 它可以省略inner,在on后面直接书写比较条件，通过条件来消除迪卡尔积。

隐式内连接，它在开发应用比较多，我们可以省略inner join 表与表之间使用逗号分开，通过where条件来消除迪卡尔积

7.2 多表查询-外连接

如果我们在开发中需要将所有用户及其订单查询出来，如果用户没有订单，也要查询出用户信息。那么使用内连接是做不到了，这时就需要使用外连接。

外连接并不要求连接的两表的每一条记录在对方表中都一条匹配的记录. 连接表保留所有记录,甚至这条记录没有匹配的记录也要保留. 外连接可依据连接表保留左表, 右表或全部表的行而进一步分为左外连接, 右外连接和全连接.

注意:mysql只支持左外连接与右外连接，而不支持全连接。

7.2.1 左外连接 left outer join on 注意outer 可以省略

例如:select * from user left outer join orders on user.id=orders.user_id;

上面这条sql我们使用的是左外连接，它的意思就是说以user表为准，保留user表中所有数据，右表orders中没有关联的数据，那么就以null关联显示出来,

执行的结果如下:

7.2.2 右外连接 right outer join on 注意 outer可以省略

例如:select * from user right outer join orders on user.id=orders.user_id;

这条sql我们使用的是右外连接，它的意思就是说以orders表为准，保留orders表中所有数据，左表user中没有关联的数据，那么就以null关联显示出来

7.3 多表查询-子查询

练习:

1.查看用户为张三的订单详情

2.查询出订单的价格大于300的所有用户信息。

3.查询订单价格大于300的订单信息及相关用户的信息。

在sql语言中，当一个查询是另一个查询的条件时，称之为子查询。

7.3.1 单行单列子查询：相当于一个值

可以使用关系运算符操作

例如:

查看用户为张三的订单详情。

i.先根据用户名查询出张三的id

select id from user where username='张三';----得到了一个结果 1

ii.根据用户的id查询该用户的订单

select * from orders where user_id=(select id from user where username='张三');

7.3.2 单列多行子查询：相当于一个数组

可以使用in,any或all操作

>any：大于子查询中的最小值。

>all： 大于子查询中的最大值。

<any：小于子查询中的最大值。

<all： 小于子查询中的最小值。

!=any或<>any：不等于子查询中的任意值。

!=all或<>all：不等于子查询中的所有值。

=any：等于子查询中任意值。

例如:

查询出订单的价格>300的所有用户信息。

i.先查询出所有订单价格>300的用户的id.

select distinct user_id from orders where price >=300;

ii.根据上面查询出的结果，在用户表中查询出用户信息

select * from user where id in(select distinct user_id from orders where price >300);

7.3.3 多行多列子查询：相当于一张虚拟表

子查询返回的就是一张表，我们直接对这张表操作即可。

例如:

查询订单价格大于300的订单信息及相关用户的信息。

i.先得到订单价格大于300的订单信息

select * from orders where price>300;

ii.在查询出用户信息，与上面的多行多列关联

select * from user u ,(select * from orders where price>300) o where o.user_id=u.id;

上面的操作，不使用子查询也可以完成:

select * from user u,orders o where o.price>300 and o.user_id =u.id;