MySQL数据库-表操作-SQL语句（二）

1. mysql多表查询

1.1 外键约束

为了消除多张表查询出现的笛卡尔积的现象，mysql在建表并进行多表之间的关键查询可以使用外键关联查询。

外键：从表1（sub）的某列引用（ref）另外一个表2（main）的某列的值，把表1的这列叫做表2这列的外键。

1.2 外键的设置使用

比如上述最简单的员工(employee)和部门表(department)，设置外键dept_id与id相关联。

步骤如下：

①在多方表中设置fk外键，使用外键dept_id关联一方表中的主键id，并选择好参考表；

 

②修改选项设置中的存储引擎为innodb，支持设置外键操作；

 

注意:在mysql中,innodb支持事务和外键.myisam 不支持事务和外键。

 

上述操作也可以使用sql语句方式修改存储引擎为inndb：

alter table 表名 engine='innodb';

如在命令行界面输入如下语句：

 

同样使用sql语句创建外键关联：

alter table employee add constraint employee_fk(外键名) foreign key (dept_id) references  dept(dept_id)；

 

1.3 连接查询分类

连接查询总的分类可以用一张图来简单描述，主要分为外连接查询（左外连接、右外连接、全连接）、内连接查询（内连接、自连接）。

 

1.4 内连接查询

内连接两张表的情况如下图，连接查询得到的是两张表的交集部分。

 

sql语句写法上可分为显式和隐式写法：

隐式内连接写法：

select <selectlist>
from table1,table2 where table1.列 = table2.列;

显式内连接写法（推荐写法）：

select <selectlist>
from table1 [inner] join table2 on table1.列 = table2.列;

查询实例：

说明：本例以下所有查询以product、product_category、product_stock三张表为例；

 

 

①需求:查询所有商品的名称和分类名称:

隐式写法：

select p.product_name, pc.category_name

from product p, product_category pc

where p.category_id = pc.id;

显式写法：

select p.product_name, pc.category_name

from product p inner join product_category pc

on p.category_id = pc.id;

 
select p.product_name, pc.category_name

from product p join product_category pc

on p.category_id = pc.id;

②需求: 查询货品id，货品名称，货品所属分类名称；

隐式写法：

select *

from product p, product_category pc

where p.category_id = pc.id and

p.sale_price > 200 and pc.category_name = '无线鼠标';

 

显式写法：

select *

from product p

join product_category pc on p.category_id = pc.id

and p.sale_price > 200 and pc.category_name = '无线鼠标';

1.5 外连接查询

外连接查询分为左外连接查询和右外连接查询。

左外连接：查询出join左边表的全部数据与右表满足on条件的部分，join右边表不匹配的数据使用null来填充数据行。

右外连接：查询出join右边表的全部数据与左表满足on条件的部分，join左边表不匹配的数据使用null来填充数据行。

 

语法格式：

select <selectlist>

from table1 left/right [outer] join table2

on table1.列 = table2.列；

查询实例：

查询每种商品名称，分类的名称和包含的具体商品总数（storenum）

 

select p.product_name, pc.category_name, ifnull(ps.store_num,0)

from product p

left join product_category pc

on p.category_id = pc.id

left join product_stock ps

on p.id = ps.product_id;

注：ifnull(expr1,expr2)的使用是如果当前expr1为null，则显示expr2的值。

 

1.6 自连接查询

在特定的查询场景下（商品分类、地区、权限），需要设计将表中的数据进行分类或二级关联时，可能会用到自连接查询的表设计方法。

自连接方式：

查询实例：

查询每个商品分类的名称和父分类名称

#隐式写法

select pare.category_name, sub.category_name from

product_category sub, product_category pare

where sub.id = pare.parent_id;

 

# 显示写法

select pare.category_name, sub.category_name from

product_category sub join product_category pare

on sub.id = pare.parent_id;

查询结果：

 

1.7 子查询

子查询（嵌套查询）：一个查询语句嵌套在另一个查询语句中，内层查询的结果可以作为外层查询条件。（相当于查询出来一个结果，然后把结果当着一张表在进行查询）

一般的，嵌套在where或者from字句中。

 

子查询（嵌套查询）一般分为单行单列子查询和单行多列子查询。

 

查询实例：

① 单行单列子查询

# 单行单列子查询

# 查询零售价比罗技mx1100更高的所有商品信息

select * from product where

sale_price > ( select sale_price from product where product_name = '罗技mx1100' );

查询结果：

 

②单行多列子查询

# 查询分类编号和折扣与罗技m100相同的所有商品信息

select * from product where (category_id, cutoff) in (select category_id, cutoff from product where product_name = '罗技mx1100');

 

select * from product where (category_id, cutoff) = (select category_id, cutoff from product where product_name = '罗技mx1100');

查询结果：

 

2.mysql数据操作dml语句

2.1 插入语句（insert）

插入语句：一次只插入一行。

 

语法：

insert into table_name (column1,column2,column3...) values (value1, value2, value3…);

 

插入多行数据记录。

语法：

insert into table_name (column1,column2,column3...) values

(value1, value2, value3…), (value4, value5, value6…), (value7, value8, value9…);

实例：

# 插入一行

insert into mytable(id, name, age, sex) values (1, '琳', 21, 0);

 

insert into mytable(sex, age, name, id) values (1, 18, '小白', 2); #顺序可以打乱，只要插入的键值一一对应即可

 

# 插入多行-- mysql特有
insert into mytable(id, name, age, sex) 
values (3, 'test01', 26, 0), (4, 'test02', 27, 1), (5, 'test03', 28, 1);

 

2.2 修改语句（update）

修改语法：

update table_name set column1 = value1, column2 = value2, column3 = value3…

[where condition];

注意：如果省略了where语句，则是修改全表的数据。

 

修改实例：

# 修改数据
# 将零售价大于300的货品零售价上调0.2倍
update product set sale_price = sale_price * 1.2 where sale_price > 300;

# 将零售价大于300的有线鼠标的货品零售价上调0.1倍
update product p join product_category pc on p.category_id = pc.id 
set sale_price = sale_price * 1.1 where sale_price > 300 and pc.category_name = '有线鼠标';

 

2.3 删除语句（delete）

语法：

delete from table_name [where condition];

 

注：如果省略了where，则会全表数据都进行删除。

 

实例：

# 删除一条

delete from mytable where id = 4;

# 删除多条

delete from mytable where id >=3;

 

3. mysql数据备份

mysql数据备份有两种方式：通过navicat工具的sql导入/导出和使用命令行的方式导入/导出。这里主要说明使用命令行的方式。

 

语法：

导出：mysqldump -u账户 -p密码 数据库名称>脚本文件存储地

mysql自身的数据库维护

通过cmd命令进入dos窗口：

mysqldump -uroot -padmin jdbcdemo> c:/shop_bak.sql

导入：mysql -u账户 -p密码 数据库名称< 脚本文件存储地址

mysql -uroot -padmin jdbcdemo< c:/shop_bak.sql