MySQL常用语句汇总

day16-mysql 基础 & 约束

学习目标

第 1 章 数据库介绍

1.1 数据库概述

什么是数据库

数据库就是存储数据的仓库，其本质是一个文件系统，数据按照特定的格式将数据存储起来，用户可以对数据库中

的数据进行增加，修改，删除及查询操作。

什么是数据库管理系统

数据库管理系统（ DataBase Management System ， DBMS ）：指一种操作和管理数据库的大型软件，用于建

立、使用和维护数据库，对数据库进行统一管理和控制，以保证数据库的安全性和完整性。用户通过数据库管理系

统访问数据库中表内的数据。

数据库与数据库管理系统的关系

1.2 数据库表

数据库中以表为组织单位存储数据。

表类似我们的 Java 类，每个字段都有对应的数据类型。

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

类 ---------- 表

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

对象 ---------- 记录 1.3 表数据

根据表字段所规定的数据类型，我们可以向其中填入一条条的数据，而表中的每条数据类似类的实例对象。表中的

一行一行的信息我们称之为记录。

表记录与 java 类对象的对应关系

1.4 常见数据库

常见的数据库管理系统

MYSQL ：开源免费的数据库，小型的数据库 . 已经被 Oracle 收购了 .MySQL6.x 版本也开始收费。

Oracle ：收费的大型数据库， Oracle 公司的产品。 Oracle 收购 SUN 公司，收购 MYSQL 。

DB2 ： IBM 公司的数据库产品 , 收费的。常应用在银行系统中 .

SQLServer ： MicroSoft 公司收费的中型的数据库。 C# 、 .net 等语言常使用。

SyBase ：已经淡出历史舞台。提供了一个非常专业数据建模的工具 PowerDesigner 。

SQLite ：嵌入式的小型数据库，应用在手机端。

常用数据库： MYSQL ， Oracle ．

这里使用 MySQL 数据库。 MySQL 中可以有多个数据库，数据库是真正存储数据的地方。

第 2 章 MySql 数据库

2.1 MySql 安装

安装

参考 MySQL 安装图解 .doc

安装后， MySQL 会以 windows 服务的方式为我们提供数据存储功能。开启和关闭服务的操作：右键点击我的电脑

→ 管理 → 服务 → 可以找到 MySQL 服务开启或停止。

也可以在 DOS 窗口，通过命令完成 MySQL 服务的启动和停止（必须以管理员身份运行 cmd 命令窗口）

2.2 登录 MySQL 数据库

MySQL 是一个需要账户名密码登录的数据库，登陆后使用，它提供了一个默认的 root 账号，使用安装时设置的密码

即可登录。

开启 mysql 服务 :net start mysql

关闭 mysql 服务 :net stop mysql

格式 1 ： cmd> mysql –u 用户名 –p 密码

例如： mysql -uroot –proot 第 3 章 SQL 语句

3.1 SQL 概述

SQL 语句介绍

数据库是不认识 JAVA 语言的，但是我们同样要与数据库交互，这时需要使用到数据库认识的语言 SQL 语句，它是数

据库的代码。 结构化查询语言 (Structured Query Language) 简称 SQL ，是关系型数据库管理系统都需要遵循的规

范。不同的数据库生产厂商都支持 SQL 语句，但都有特有内容。

SQL 语句分类

SQL 分类：

数据定义语言：简称 DDL(Data Defifinition Language) ，用来定义数据库对象：数据库，表，列等。关键

字： create ， alter ， drop 等

数据操作语言：简称 DML(Data Manipulation Language) ，用来对数据库中表的记录进行更新。关键

字： insert ， delete ， update 等

数据控制语言：简称 DCL(Data Control Language) ，用来定义数据库的访问权限和安全级别，及创建用

户。

数据查询语言：简称 DQL(Data Query Language) ，用来查询数据库中表的记录。关键字： select ，

from ， where 等

SQL 通用语法

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

可使用空格和缩进来增强语句的可读性

MySQL 数据库的 SQL 语句不区分大小写，关键字建议使用大写

例如： SELECT * FROM user 。

同样可以使用 /**/ 的方式完成注释

MySQL 中的我们常使用的数据类型如下

格式 2 ： cmd> mysql --host=ip 地址 -- user = 用户名 -- password = 密码

例如： mysql --host= 127.0.0.1 -- user =root -- password =root 类型名称

说明

int （ integer ）

整数类型

double

小数类型

decimal （ m,d ）

指定整数位与小数位长度的小数类型

date

日期类型，格式为 yyyy-MM-dd ，包含年月日，不包含时分秒

datetime

日期类型，格式为 YYYY-MM-DD HH:MM:SS ，包含年月日时分秒

timestamp

日期类型，时间戳

varchar （ M ）

文本类型， M 为 0~65535 之间的整数

3.2 DDL 之数据库操作： database

创建数据库

格式：

例如：

###

查看数据库

查看数据库 MySQL 服务器中的所有的数据库 :

查看某个数据库的定义的信息 :

1. 创建数据库

2. 使用数据库

3. 查看某个数据库的定义的信息

4. 查看所有数据库

5. 查看正在使用的数据库

6. 删除数据库

create database 数据库名 ;

create database 数据库名 character set 字符集 ;

# 创建数据库 数据库中数据的编码采用的是安装数据库时指定的默认编码 utf8

CREATE DATABASE webdb_1;

# 创建数据库 并指定数据库中数据的编码

CREATE DATABASE webdb_2 CHARACTER SET utf8;

show databases ; 例如：

删除数据库

例如：

使用数据库

查看正在使用的数据库 :

其他的数据库操作命令

切换数据库：

3.3 DDL 之表操作： table

###

创建表

格式：

show create database 数据库名 ;

show create database webdb_1;

drop database 数据库名称 ;

drop database webdb_2;

select database ();

use 数据库名 ;

例如：

use webdb_1;

1. 创建数据库表

创建分类表 category

id name

2. 设置自动增长从多少开始

ALTER TABLE category AUTO_INCREMENT=200; 例如：创建分类表

查看表

查看数据库中的所有表：

查看表结构：

删除表

格式： drop table 表名 ;

修改表结构格式

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

作用：修改表添加列 .

alter table 表名 modify 列名 类型 ( 长度 ) 约束 ;

create table 表名 (

字段名 类型 ( 长度 ) [ 约束 ],

字段名 类型 ( 长度 ) [ 约束 ],

...

);

类型：

varchar (n) 字符串

int 整形

double 浮点

date 时间

timestamp 时间戳

约束： ( 详情查看第四章 )

primary key 主键，被主键修饰字段中的数据，不能重复、不能为 null 。

CREATE TABLE category (

cid INT primary key , # 分类 ID

cname VARCHAR ( 100 ) # 分类名称

);

格式： show tables ;

格式： desc 表名 ;

例如： desc category;

例如： drop table category;

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

ALTER TABLE category ADD `desc` VARCHAR ( 20 ); 作用：修改表修改列的类型长度及约束 .

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

alter table 表名 drop 列名 ;

作用：修改表删除列 .

rename table 表名 to 新表名 ;

作用：修改表名

alter table 表名 character set 字符集 ( 了解 );

作用：修改表的字符集

3.4 DML 数据操作语言

插入表记录： insert

语法：

注意：

值与字段必须对应，个数相同，类型相同

值的数据大小必须在字段的长度范围内

除了数值类型外，其它的字段类型的值必须使用引号引起。（建议单引号）

如果要插入空值，可以不写字段，或者插入 null 。

例如：

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

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

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

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

例如： #4, 删除分类表中 description 这列

ALTER TABLE category DROP description;

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

RENAME TABLE category TO category2;

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

ALTER TABLE category CHARACTER SET gbk;

-- 向表中插入某些字段

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

-- 向表中插入所有字段 , 字段的顺序为创建表时的顺序

insert into 表 values ( 值 1, 值 2, 值 3..); 更新表记录： update

用来修改指定条件的数据，将满足条件的记录指定列修改为指定值

语法：

更新所有记录的指定字段

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

注意：

列名的类型与修改的值要一致 .

修改值得时候不能超过最大长度 .

除了数值类型外，其它的字段类型的值必须使用引号引起

删除记录： delete

语法：

3.5 DOS 操作数据乱码解决

我们在 dos 命令行操作中文时，会报错

错误原因 : 因为 mysql 的客户端设置编码是 utf8, 而系统的 cmd 窗口编码是 gbk

1. 查看 MySQL 内部设置的编码

2. 需要修改 client 、 connection 、 results 的编码一致（ GBK 编码）

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(cname,cid) VALUES ( ' 耗材 ' , 'c006' );

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

update 表名 set 字段名 = 值 , 字段名 = 值 ,... where 条件 ;

delete from 表名 [ where 条件 ];

insert into category(cid,cname) values (‘c010’,’ 中文 ’);

ERROR 1366 (HY000): Incorrect string value : '\xB7\xFE\xD7\xB0' for column 'cname' at

row 1

show variables like 'character%' ; 查看所有 mysql 的编码 解决方案 1 ：在 cmd 命令窗口中输入命令，此操作当前窗口有效，为临时方案。

解决方案 2 ：安装目录下修改 my.ini 文件，重启服务所有地方生效。

第 4 章 SQL 约束

4.1 主键约束

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

主键必须包含唯一的值。

主键列不能包含 NULL 值。

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

添加主键约束

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

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

格式： [constraint 名称 ] primary key ( 字段列表 )

关键字 constraint 可以省略，如果需要为主键命名， constraint 不能省略，主键名称一般没用。

字段列表需要使用小括号括住，如果有多字段需要使用逗号分隔。声明两个以上字段为主键，我们称为

联合主键。

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

格式： ALTER TABLE persons ADD [CONSTRAINT 名称 ] PRIMARY KEY ( 字段列表 )

set names gbk;

CREATE TABLE persons

(

id_p int PRIMARY KEY ,

lastname varchar ( 255 ),

firstname varchar ( 255 ),

address varchar ( 255 ),

city varchar ( 255 )

)

CREATE TABLE persons

(

firstname varchar ( 255 ),

lastname varchar ( 255 ),

address varchar ( 255 ),

city varchar ( 255 ),

CONSTRAINT pk_personID PRIMARY KEY (firstname,lastname)

) 删除主键约束

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

4.2 自动增长列

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

我们可以在表中使用 auto_increment （自动增长列）关键字，自动增长列类型必须是整形，自动增长列必须为键

( 一般是主键 ) 。

下列 SQL 语句把 "persons" 表中的 "p_id" 列定义为 auto_increment 主键

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

扩展：默认 AUTO_INCREMENT 的开始值是 1 ，如果希望修改起始值，请使用下列 SQL 语法：

面试题

问：针对 auto_increment ，删除表中所有记录使用 delete from 表名 或使用 truncate table 表名，二者有什么区

别？

CREATE TABLE persons

(

firstname varchar ( 255 ),

lastname varchar ( 255 ),

address varchar ( 255 ),

city varchar ( 255 )

)

ALTER TABLE persons ADD PRIMARY KEY (firstname,lastname)

ALTER TABLE persons DROP PRIMARY KEY

CREATE TABLE persons

(

p_id int PRIMARY KEY AUTO_INCREMENT ,

lastname varchar ( 255 ),

firstname varchar ( 255 ),

address varchar ( 255 ),

city varchar ( 255 )

)

INSERT INTO persons (firstname,lastname) VALUES ( 'Bill' , 'Gates' )

INSERT INTO persons (p_id,firstname,lastname) VALUES ( NULL , 'Bill' , 'Gates' )

ALTER TABLE persons AUTO_INCREMENT = 100 4.3 非空约束

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

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

下面的 SQL 语句强制 "id_p" 列和 "lastname" 列不接受 NULL 值：

4.4 唯一约束

UNIQUE 约束唯一标识数据库表中的每条记录。 UNIQUE 和 PRIMARY KEY 约束均为列或列集合提供了唯一性的保

证。 PRIMARY KEY 拥有自动定义的 UNIQUE 约束。 请注意，每个表可以有多个 UNIQUE 约束，但是每个表只能

有一个 PRIMARY KEY 约束。

添加唯一约束

与主键添加方式相同，共有 3 种

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

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

删除方式：

delete 一条一条删除，不清空 auto_increment 记录数。

truncate 直接将表删除，重新建表， auto_increment 将置为零，从新开始。

CREATE TABLE persons

(

id_p int NOT NULL ,

lastname varchar ( 255 ) NOT NULL ,

firstname varchar ( 255 ),

address varchar ( 255 ),

city varchar ( 255 )

)

CREATE TABLE persons

(

id_p int UNIQUE ,

lastname varchar ( 255 ) NOT NULL ,

firstname varchar ( 255 ),

address varchar ( 255 ),

city varchar ( 255 )

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

删除唯一约束

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

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

第 5 章 MySQL 数据库密码重置 ( 扩展 )

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

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

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

4. 关闭两个 cmd 窗口

CREATE TABLE persons

(

id_p int ,

lastname varchar ( 255 ) NOT NULL ,

firstname varchar ( 255 ),

address varchar ( 255 ),

city varchar ( 255 ),

CONSTRAINT 名称 UNIQUE (Id_P)

)

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

ALTER TABLE persons DROP INDEX 名称

use mysql;

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

 

 

 

day17-mysql 查询 DQL& 多表关系

 

第一章 SQL 语句 (DQL)

1.1 DQL 准备工作和语法

准备工作

# 创建商品表：

pid int

pname varchar ( 20 )

price double

category_id varchar ( 32 )

插入一条数据 :

pid= 1 ,pname= ' 联想 ' ,price= 5000 ,category_id= 'c001'

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 , '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' ); 语法

1.2 简单查询

练习

1.3 条件查询

INSERT INTO product(pid,pname,price,category_id) VALUES ( 13 , ' 果 9' , 1 , NULL );

select [ distinct ]

* | 列名 , 列名

from 表

where 条件

# 查询所有的商品 .

select * from product;

# 查询商品名和商品价格 .

select pname,price from product;

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

select * from product as p;

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

select pname as pn from product;

# 去掉重复值 .

select distinct price from product;

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

select pname,price+ 10 from product; 比较运

算符

< <= = != <>

大于、小于、大于 ( 小于 ) 等于、不等于

BETWEEN

...AND...

显示在某一区间的值 ( 含头含尾 )

IN(set)

显示在 in 列表中的值，例： in(100,200)

LIKE ‘ 张

pattern’

模糊查询， Like 语句中， % 代表零个或多个任意字符， _ 代表一个字符， 例

如： first_name like '_a%' ;

IS NULL

判断是否为空

逻辑运

行符

and

多个条件同时成立

or

多个条件任一成立

not

不成立，例： where not(salary>100) ;

练习

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

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 ;

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

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

SELECT * FROM product WHERE price BETWEEN 200 AND 1000 ;

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

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

SELECT * FROM product WHERE price IN ( 200 , 800 ); 1.4 排序查询

通过 order by 语句，可以将查询出的结果进行排序。暂时放置在 select 语句的最后。

格式 :

练习：

1.5 聚合查询

之前我们做的查询都是横向查询，它们都是根据条件一行一行的进行判断，而使用聚合函数查询是纵向查询，它是

对一列的值进行计算，然后返回一个单一的值；另外聚合函数会忽略空值。

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

count ：统计指定列不为 NULL 的记录行数；

sum ：计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为 0 ；

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

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

SELECT * FROM 表名 ORDER BY 排序字段 ASC | DESC ;

#ASC 升序 ( 默认 )

#DESC 降序

# 查询 id 为 1 3 5 7 的商品 并按价格降序排列

# 使用价格排序 ( 降序 )

SELECT * FROM product ORDER BY price DESC ;

# 在价格排序 ( 降序 ) 的基础上，以分类排序 ( 降序 )

SELECT * FROM product ORDER BY price DESC ,category_id DESC ;

# 显示商品的价格 ( 去重复 ) ，并排序 ( 降序 )

SELECT DISTINCT price FROM product ORDER BY price DESC ; max ：计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算；

min ：计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算；

avg ：计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为 0 ；

练习：

1.6 分组查询

分组查询是指使用 group by 字句对查询信息进行分组。

格式：

分组操作中的 having 子语句，是用于在分组后对数据进行过滤的，作用类似于 where 条件。

having 与 where 的区别 :

having 是在分组后对数据进行过滤 .

where 是在分组前对数据进行过滤

having 后面可以使用聚合函数 ( 统计函数 )

where 后面不可以使聚合函数。

# 查询商品的总条数

SELECT COUNT (*) FROM product;

# 查询商品的价格和

select sum (price) as 'totalprice' FROM product;

# 查询价格大于 200 商品的总条数

SELECT COUNT (*) FROM product WHERE price > 200 ;

# 查询分类为 'c001' 的所有商品的总和

SELECT SUM (price) FROM product WHERE category_id = 'c001' ;

# 查询分类为 'c002' 所有商品的平均价格

SELECT AVG (price) FROM product WHERE category_id = 'c002' ;

# 查询商品的最大价格和最小价格

SELECT MAX (price), MIN (price) FROM product;

# 查询 pid 为 1 3 7 商品价格的平均值

# 查询 pid 为 1 3 7 14 商品价格的平均值

SELECT 字段 1, 字段 2… FROM 表名 GROUP BY 分组字段 HAVING 分组条件 ; # 统计每门课程的平均分

# 统计每门课程的平均分 , 且只显示平均分 >70 分的信息

练习：

# 统计各个分类商品的平均价格

SELECT category_id,AVG(price) FROM product GROUP BY category_id;

# 统计各个分类商品的平均价格 , 且只显示平均价格 >800 的信息

SELECT category_id,AVG(price) FROM product GROUP BY category_id HAVING AVG(price)>800;

第二章 SQLyog （ MySQL 图形化开发工具）

/*

学生成绩表 scores:

sid: 学生成绩的 id

score: 成绩

sname: 课程

*/

CREATE TABLE scores(

sid INT PRIMARY KEY,

score INT,

sname VARCHAR(200)

);

INSERT INTO scores(sid,score,sname) VALUES(1,90,' 语文 ');

INSERT INTO scores(sid,score,sname) VALUES(2,70,' 数学 ');

INSERT INTO scores(sid,score,sname) VALUES(3,90,' 外语 ');

INSERT INTO scores(sid,score,sname) VALUES(4,50,' 语文 ');

INSERT INTO scores(sid,score,sname) VALUES(5,90,' 数学 ');

INSERT INTO scores(sid,score,sname) VALUES(6,80,' 外语 ');

INSERT INTO scores(sid,score,sname) VALUES(7,100,' 语文 ');

INSERT INTO scores(sid,score,sname) VALUES(8,20,' 数学 ');

INSERT INTO scores(sid,score,sname) VALUES(9,100,' 外语 ');

INSERT INTO scores(sid,score,sname) VALUES(10,75,' 编程 ');

# 查询所有

# 查询所有课程和对应的成绩

# 统计各个分类商品的个数

SELECT category_id , COUNT (*) FROM product GROUP BY category_id ;

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

SELECT category_id , COUNT (*) FROM product GROUP BY category_id HAVING COUNT (*) > 1 ; 安装：

提供的 SQLyog 软件为免安装版，可直接使用

使用：

输入用户名、密码，点击连接按钮，进行访问 MySQL 数据库进行操作

在 Query 窗口中，输入 SQL 代码，选中要执行的 SQL 代码，按 F8 键运行，或按执行按钮运行。 第三章 SQL 备份与恢复

3.1 SQL 备份

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

MySQL 命令备份

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

例如 :

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

可视化工具备份

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

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

mysqldump -uroot -proot day04>d: \d ay03 .sql 3.2 SQL 恢复

数据库的恢复指的是使用备份产生的 sql 文件恢复数据库，即将 sql 文件中的 sql 语句执行就可以恢复数据库内容。

MySQL 命令恢复

使用数据库命令备份的时候只是备份了数据库内容，产生的 sql 文件中没有创建数据库的 sql 语句，在恢复数据库之

前需要自己动手创建数据库。

在数据库外恢复

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

例如 : mysql - uroot - proot day03<d:\day03.sql

在数据库内恢复

格式 : source SQL 脚本路径

例如 : source d:\day03.sql

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

可视化工具恢复

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

实际开发中，一个项目通常需要很多张表才能完成。例如：一个商城项目就需要分类表 (category) 、商品表

(products) 、订单表 (orders) 等多张表。且这些表的数据之间存在一定的关系，接下来我们将在单表的基础上，一

起学习多表方面的知识。

4.1 表与表之间的关系

一对多关系：

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

多对多关系：

常见实例：学生和课程、用户和角色

多对多关系建表原则：需要创建第三张表 , 中间表中至少两个字段，这两个字段分别作为外键指向各自一

方的主键 .

一对一关系： ( 了解 )

在实际的开发中应用不多 . 因为一对一可以创建成一张表 .

两种建表原则：

外键唯一：主表的主键和从表的外键（唯一），形成主外键关系，外键唯一 unique 。

外键是主键：主表的主键和从表的主键，形成主外键关系。

4.2 外键约束

现在我们有两张表 “ 分类表 ” 和 “ 商品表 ” ，为了表明商品属于哪个分类，通常情况下，我们将在商品表上添加一列，

用于存放分类 cid 的信息，此列称为：外键 此时 “ 分类表 category” 称为：主表， “cid” 我们称为主键。 “ 商品表 products” 称为：从表， category_id 称

为外键。我们通过主表的主键和从表的外键来描述主外键关系，呈现就是一对多关系。

外键特点：

从表外键的值是对主表主键的引用。

从表外键类型，必须与主表主键类型一致。

声明外键约束

使用外键目的：

保证数据完整性

4.3 一对多操作

分析

category 分类表，为一方，也就是主表，必须提供主键 cid

products 商品表，为多方，也就是从表，必须提供外键 category_id

语法：

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

名 ( 主表的主键 );

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

alter table 从表 drop foreign key 外键名称 实现：分类和商品

操作

4.4 多对多

分析

# 创建分类表

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

#1 向分类表中添加数据

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

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

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

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

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

#4 向商品表添加普通数据，含有外键信息 (category 表中不存在这条数据 ) -- 失败 , 异常

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

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

DELETE FROM category WHERE cid = 'c001' ; 商品和订单多对多关系，将拆分成两个一对多。

products 商品表，为其中一个一对多的主表，需要提供主键 pid

orders 订单表，为另一个一对多的主表，需要提供主键 oid

orderitem 中间表，为另外添加的第三张表，需要提供两个外键 oid 和 pid

实现：订单和商品

操作

# 商品表 [ 已存在 ]

# 订单表

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

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

 

 

day18-mysql 多表查询 & 原生 jdbc

 

第一章 多表关系实战

1.1 实战 1 ：省和市

方案 1 ：多张表，一对多

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

id=1 name=' 北京 '

p_id = null;

id=2

name=' 昌平 '

p_id=1

id=3

name=' 大兴 '

p_id=1

id=3

name=' 上海 '

p_id=null

id=4

name=' 浦东 '

p_id=3

1.2 实战 2 ：用户和角色

多对多关系

第二章 多表查询

提供表结构如下： 2.1 初始化数据

2.2 多表查询

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

# 分类表

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 表示下架

cid VARCHAR ( 32 ),

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

);

alter table products add CONSTRAINT products_fk FOREIGN KEY products(cid) REFERENCES

category (cid);

# 分类

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,cid) VALUES ( 'p001' , ' 联想 ' , 5000 , '1' , 'c001' );

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

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

INSERT INTO products (pid, pname,price,flag,cid) VALUES ( 'p004' , 'JACK

JONES' , 800 , '1' , 'c002' );

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

INSERT INTO products (pid, pname,price,flag,cid) VALUES ( 'p006' , ' 花花公

子 ' , 440 , '1' , 'c002' );

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

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

INSERT INTO products (pid, pname,price,flag,cid) VALUES ( 'p009' , ' 相宜草 ' , 200 , '1' , 'c003' ); 语法： select * from A,B ;

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

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

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

#1. 查询所有商品的记录 , 要求显示所属的分类信息

#2. 只查询所有化妆品的记录 , 要求显示所属的分类信息

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

#4. 查询每种分类商品的个数

# 隐式内连接

SELECT DISTINCT c .cname

FROM category c , products p

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

# 内连接

SELECT DISTINCT c .cname

FROM category c INNER JOIN products p

ON c .cid = p .cid

WHERE p .flag = '1' ;

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

左外连接： left outer join

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

右外连接： right outer join

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

#1. 查询所有商品的记录 , 要求显示所属的分类信息

#2. 只查询所有化妆品的记录 , 要求显示所属的分类信息

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

#4. 查询每种分类商品的个数

# 左外连接

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

SELECT cname, COUNT (cid)

FROM category c LEFT OUTER JOIN products p

ON c .cid = p .cid

GROUP BY cname; 下面通过一张图说明连接的区别 :

2.3 子查询

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

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

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

# 隐式内连接

SELECT p.*

FROM products p , category c

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

# 子查询

## 作为查询条件

SELECT *

FROM products p

WHERE p .cid =

(

SELECT c .cid FROM category c

WHERE c .cname = ' 化妆品 '

);

## 作为另一张表

SELECT *

FROM products p ,

( SELECT * FROM category WHERE cname= ' 化妆品 ' ) c 子查询练习：

第三章 JDBC

3.1 JDBC 概述

JDBC （ Java DataBase Connectivity,java 数据库连接）是一种用于执行 SQL 语句的 Java API 。 JDBC 是 Java 访问数据

库的标准规范，可以为不同的关系型数据库提供统一访问，它由一组用 Java 语言编写的接口和类组成。

JDBC 需要连接驱动，驱动是两个设备要进行通信，满足一定通信数据格式，数据格式由设备提供商规定，设备提

供商为设备提供驱动软件，通过软件可以与该设备进行通信。 今天我们使用的是 mysql 的驱动 mysql-connector

java-5.1.37-bin.jar

WHERE p .cid = c .cid ;

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

SELECT *

FROM products p

WHERE p .cid in

( SELECT c .cid

FROM category c

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

); JDBC 规范（掌握四个核心对象）：

DriverManager: 用于注册驱动

Connection: 表示与数据库创建的连接

Statement: 操作数据库 sql 语句的对象

ResultSet: 结果集或一张虚拟表

3.2 JDBC 原理

Java 提供访问数据库规范称为 JDBC ，而生产厂商提供规范的实现类称为驱动。

JDBC 是接口，驱动是接口的实现，没有驱动将无法完成数据库连接，从而不能操作数据库！每个数据库厂商都需

要提供自己的驱动，用来连接自己公司的数据库，也就是说驱动一般都由数据库生成厂商提供。 3.3 JDBC 入门案例

准备数据

之前我们学习了 sql 语句的使用，并创建的分类表 category ，今天我们将使用 JDBC 对分类表进行增删改查操作。

导入驱动 jar 包

创建 lib 目录，存放 mysql 的驱动 mysql-connector-java-5.1.37-bin.jar

选中 mysql 的 jar 包，右键选择 “ Add as Library...” 完成 jar 导入

开发步骤

1. 注册驱动 .

2. 获得连接 .

3. 获得执行 sql 语句的对象

4. 执行 sql 语句，并返回结果

5. 处理结果

6. 释放资源 .

案例实现

# 创建数据库

create database day04;

# 使用数据库

use day04;

# 创建分类表

create table category(

cid int PRIMARY KEYAUTO_INCREMENT ,

cname varchar ( 100 )

);

# 初始化数据

insert into category (cname) values ( ' 家电 ' );

insert into category (cname) values ( ' 服饰 ' );

insert into category (cname) values ( ' 化妆品 ' );

// 查询所有的分类信息

@Test

public void demo1 () throws Exception { 3.4 API 详解

API 详解：注册驱动

DriverManager.registerDriver(new com.mysql.jdbc.Driver()) ; 不建议使用，原因有 2 个：

导致驱动被注册 2 次

强烈依赖数据库的驱动 jar

解决办法：

Class.forName("com.mysql.jdbc.Driver") ;

API 详解：获得链接

static Connection getConnection(String url, String user, String password) : 试图建立到给定数据

库 URL 的连接。

参数说明：

url 需要连接数据库的位置（网址）

user 用户名

password 密码

例如： getConnection("jdbc:mysql://localhost:3306/day04", "root", "root") ;

扩展：

URL:SUN 公司与数据库厂商之间的一种协议。

// 注意：使用 JDBC 规范，采用都是 java.sql 包下的内容

//1 注册驱动

Class . forName ( "com.mysql.jdbc.Driver" );

//2 获得连接

String url = "jdbc:mysql://localhost:3306/mydb" ;

Connection conn = DriverManager . getConnection ( url , "root" , "root" );

//3 获得执行 sql 语句的对象

Statement stmt = conn . createStatement ();

//4 执行 SQL 语句

ResultSet rs = stmt . executeQuery ( "select * from category" );

//5 处理结果集

while ( rs . next ()){

// 获得一行数据

Integer cid = rs . getInt ( "cid" );

String cname = rs . getString ( "cname" );

System . out . println ( cid + " , " + cname );

}

//6 释放资源

rs . close ();

stmt . close ();

conn . close ();

} 协议子协议 IP : 端口号数据库

mysql: jdbc:mysql://localhost:3306/day04 或者 jdbc:mysql:///day04 （默认

本机连接）

oracle 数据库 : jdbc:oracle:thin:@localhost:1521:sid

API 详解： java.sql.Connection 接口：一个连接

接口的实现在数据库驱动中。所有与数据库交互都是基于连接对象的。

Statement createStatement() ; // 创建操作 sql 语句的对象

API 详解： java.sql.Statement 接口 : 操作 sql 语句，并返回相应结果

常用方法：

int executeUpdate(String sql) ; -- 执行 insert update delete 语句 .

ResultSet executeQuery(String sql) ; -- 执行 select 语句 .

boolean execute(String sql) ; -- 仅当执行 select 并且有结果时才返回 true ，执行其他的语句返回 false.

API 详解：处理结果集（注：执行 insert 、 update 、 delete 无需处理）

ResultSet 实际上就是一张二维的表格，我们可以调用其 boolean next() 方法指向某行记录，当第一次调用

next() 方法时，便指向第一行记录的位置，这时就可以使用 ResultSet 提供的 getXXX(int col) 方法来获取指

定列的数据： ( 与数组索引从 0 开始不同，这里索引从 1 开始 )

常用方法：

Object getObject(int index) / Object getObject(String name) 获得任意对象

String getString(int index) / String getString(String name) 获得字符串

int getInt(int index) / int getInt(String name) 获得整形

double getDouble(int index) / double getDouble(String name) 获得双精度浮点型

API 详解：释放资源

与 IO 流一样，使用后的东西都需要关闭！关闭的顺序是先得到的后关闭，后得到的先关闭。

3.5 JDBC 工具类

jdbc:mysql://localhost:3306/day04

String sql = " 某 SQL 语句 " ;

获取 Statement 语句执行平台： Statement stmt = con . createStatement ();

rs . next (); // 指向第一行

rs . getInt ( 1 ); // 获取第一行第一列的数据

rs . close ();

stmt . close ();

con . close (); “ 获得数据库连接 ” 操作，将在以后的增删改查所有功能中都存在，可以封装工具类 JDBCUtils 。提供获取连接对象的

方法，从而达到代码的重复利用。

该工具类提供方法： public static Connection getConnection() 。代码如下：

public class JdbcUtils {

private static String driver = "com.mysql.jdbc.Driver" ;

private static String url = "jdbc:mysql://localhost:3306/webdb_4" ;

private static String user = "root" ;

private static String password = "root" ;

static {

try {

// 注册驱动

Class . forName ( driver );

} catch ( Exception e ) {

throw new RuntimeException ( e );

}

}

/**

* 获得连接

* @return

* @throws SQLException

*/

public static Connection getConnection () throws SQLException {

// 获得连接

Connection conn = DriverManager . getConnection ( url , user , password );

return conn ;

}

/**

* 释放资源

* @param conn

* @param st

* @param rs

*/

public static void closeResource ( Connection conn , Statement st , ResultSet rs ){

if ( rs != null ){

try {

rs . close ();

} catch ( SQLException e ) {

}

}

if ( st != null ){

try {

st . close ();

} catch ( SQLException e ) {

} 3.6 JDBC 增删改查操作

插入

修改

}

if ( conn != null ){

try {

conn . close ();

} catch ( SQLException e ) {

}

}

}

}

@Test

public void demo01 (){

// 添加

Connection conn = null ;

Statement st = null ;

ResultSet rs = null ;

try {

//1 获得连接

conn = JdbcUtils . getConnection ();

// 操作

//1) 获得语句执行者

st = conn . createStatement ();

//2) 执行 sql 语句

int r = st . executeUpdate ( "insert into category(cname) values(' 测试 ')" );

//3) 处理结果

System . out . println ( r );

} catch ( Exception e ) {

throw new RuntimeException ( e );

} finally {

// 释放资源

JdbcUtils . closeResource ( conn , st , rs );

}

}

@Test

public void demo02 (){ 删除

通过 id 查询详情

// 修改

Connection conn = null ;

Statement st = null ;

ResultSet rs = null ;

try {

conn = JdbcUtils . getConnection ();

st = conn . createStatement ();

int r = st . executeUpdate ( "update category set cname=' 测试 2' where cid = 4" );

System . out . println ( r );

} catch ( Exception e ) {

throw new RuntimeException ( e );

} finally {

JdbcUtils . closeResource ( conn , st , rs );

}

}

@Test

public void demo03 (){

// 删除

Connection conn = null ;

Statement st = null ;

ResultSet rs = null ;

try {

conn = JdbcUtils . getConnection ();

// 操作

st = conn . createStatement ();

int r = st . executeUpdate ( "delete from category where cid = 4" );

System . out . println ( r );

} catch ( Exception e ) {

throw new RuntimeException ( e );

} finally {

JdbcUtils . closeResource ( conn , st , rs );

}

}

@Test

public void demo04 (){

// 通过 id 查询详情

Connection conn = null ; 查询所有

Statement st = null ;

ResultSet rs = null ;

try {

conn = JdbcUtils . getConnection ();

// 操作

st = conn . createStatement ();

rs = st . executeQuery ( "select * from category where cid = 30" );

if ( rs . next ()){

String cid = rs . getString ( "cid" );

String cname = rs . getString ( "cname" );

System . out . println ( cid + " @ " + cname );

} else {

System . out . println ( " 没有数据 " );

}

} catch ( Exception e ) {

throw new RuntimeException ( e );

} finally {

JdbcUtils . closeResource ( conn , st , rs );

}

}

@Test

public void demo05 (){

// 查询所有

Connection conn = null ;

Statement st = null ;

ResultSet rs = null ;

try {

conn = JdbcUtils . getConnection ();

// 操作

st = conn . createStatement ();

rs = st . executeQuery ( "select * from category" );

while ( rs . next ()){

String cid = rs . getString ( "cid" );

String cname = rs . getString ( "cname" );

System . out . println ( cid + " @ " + cname );

}

} catch ( Exception e ) {

throw new RuntimeException ( e );

} finally {

JdbcUtils . closeResource ( conn , st , rs );

} }