MySQL进阶

MySQL进阶

一、多表查询

为了减少数据的冗余度

1.迪卡尔积

SELECT 要查询的列名 FROM 表名1，表名2，…；

会返回表之间数据组合的结果，比如集合A={a,b}，集合B={0,1,2}，则两个集合的笛卡尔积为{(a,0),(a,1),(a,2),(b,0),(b,1),(b,2)}

2.内连接查询

1）隐式内连接

SELECT 要查询的列名 FROM 表名1，表名2，… WHERE 表名1.列名 = 表名2.列名；

连接点、分类编号不匹配或者某一个为空，不会被查询出来

查询所有商品的名称和分类名称
SELECT p.productName,pd.dirName FROM product p,productdir pd WHERE p.dir_id = pd.id;

2）显示外连接（常用）

SELECT 要查询的列名 FROM 表名1 INNER JOIN 表名2 ON 表名1.列名 = 表名2.列名；（INNER可以省略）

查询所有商品的名称和分类名称
1.SELECT p.productName,pd.dirName FROM product p INNER JOIN productdir pd ON p.dir_id = pd.id;
2.SELECT p.productName,pd.dirName FROM product p JOIN productdir pd ON p.dir_id = pd.id;
查询零售价大于200的无线鼠标
SELECT pd.dirName,p.salePrice FROM product p INNER JOIN productdir pd ON p.dir_id = pd.id WHERE pd.id = 2 AND p.salePrice >200 ;

3.外连接查询

1）左外连接

左边表的全部数据查询出来，右边的表不匹配的数据使用NULL来填充，是以左边为标准

SELECT 要查询的列名 FROM 表名1 LEFT JOIN 表名2 ON 表名1.列名 = 表名2.列名；

查询员工对应的部门
SELECT * FROM emplyee e LEFT JOIN dept d ON e.dept_id = d.id;

2）右外连接

右边表的全部数据查询出来，左边的表不匹配的数据使用NULL来填充，是以右边为标准

SELECT 要查询的列名 FROM 表名1 RIGHT JOIN 表名2 ON 表名1.列名 = 表名2.列名；

查询部门对应的员工
SELECT * FROM emplyee e RIGHT JOIN dept d ON e.dept_id = d.id;

4.自连接查询

把一张表看成两张来做查询，要取别名

SELECT 要查询的列名 FROM 表名 别名1 JOIN 表名 别名2 ON 别名1.列名 = 别名2.列名；

查询国家对应的省
SELECT c1.id,c2.cityname FROM city c1 INNER JOIN city c2 ON c1.id = c2.parentid WHERE c1.id = 1;
查询国家对应的省对应的市
SELECT c1.id,c2.cityname,c3.cityname FROM city c1 INNER JOIN city c2 ON c1.id = c2.parentid INNER JOIN city c3 ON c2.id = c3.parentid WHERE c1.id = 1;

5.子查询

内层查询的结果可以作为外层查询条件，一般嵌套在WHERE或者FROM字句中

1）单行单列子查询

SELECT 要查询的列名 FROM 表名 WHERE 列名 比较运算符（SELECT 要查询的列名 FROM 表名 WHERE 条件）；

查询零售价比罗技MX1100更高的所有商品信息
SELECT * FROM product WHERE salePrice > (SELECT salePrice FROM product WHERE productName = '罗技MX1100');

2）单行多列子查询

SELECT 要查询的列名 FROM 表名 WHERE （列名1，列名2，…）比较运算符（SELECT 要查询的列名 FROM 表名 WHERE 条件）；

查询分类编号和折扣与罗技M100相同的所有商品信息
SELECT * FROM product WHERE (dir_id,cutoff) = (SELECT dir_id,cutoff FROM product WHERE productName = '罗技M100');

二、数据操作（DML）

1.插入数据

1）INSERT INTO 表名 (字段1，字段2,…) VALUES (value1,value2,…);

INSERT INTO emplyee(name,dept_id) VALUES ('小王',3);

2）按照字段的顺序依次插入

INSERT INTO 表名 VALUES (value1,value2,value3,…);

INSERT INTO emplyee VALUES(5,'小李',2);

3）INSERT INTO 表名 (字段1，字段2,…)VALUE(valu1,value2,…),(valu1,value2,…)

INSERT INTO emplyee (name,dept_id) VALUE ('二狗',3),('三狗',3);

4)查询的结果进行插入

INSERT INTO 表名 (字段1，字段2,…) SELECT 字段名1，字段名字2 FROM 表名

INSERT INTO emplyee (name,dept_id) SELECT cityname,parentid FROM city;

2.修改数据

如果省略了where子句，则全表的数据都会被修改

UPDATE 表名 SET 列名 = 值 WHERE 条件;

将零售价大于300的货品零售价上调0.2倍
UPDATE product SET salePrice = salePrice*1.2 WHERE salePrice >300;
将零售价大于300的有线鼠标的货品零售价上调0.1倍
UPDATE product SET salePrice = salePrice*1.1 WHERE salePrice >300 AND dir_id = 2;

3.删除数据

如果省略了where子句，则全表的数据都会被删除，但表还存在

DELETE FROM 表名 WHERE 条件;

DELETE FROM dept WHERE id = 3;

三、 数据库的备份与恢复

1.在dos命令行窗口进行

导出

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

2）mysqldump -uroot -padmin jdbcdemo> C:/shop_bak.sql

导入

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

2）mysql -uroot -padmin jdbcdemo< C:/shop_bak.sql

2.使用可视化

四、函数

1.聚集函数

1)count

2)sum

3)avg

4)max

5)min

6)group by

2.cast函数

相当于转换，因为数据库字段与Java字段类型要对应

SELECT CAST (值 AS 类型)

将字符串类型转换为int类型
SELECT CAST('123' AS SIGNED);

3.decimal类型

SELECT CAST (值 AS DECIMAL（有效数字数的精度,小数点后的位数）) ;

可以存储3位小数的9位数
SELECT CAST('123456.123456' AS DECIMAL(9,3));
超出了位数，结果为99999.999
SELECT CAST('123456.123456' AS DECIMAL(8,3));
将零售价转换成整数类型
SELECT CAST(saleprice AS SIGNED) FROM product;

4.conver5t()函数

和CAST()函数的功能相同，只是语法不同

1)二进制同带binary前缀的效果 : BINARY

2. 字符型可带参数 : CHAR()

3)日期 : DATE

4. 时间: TIME

5)日期时间型 : DATETIME

6)浮点数 : DECIMAL

7)整数 : SIGNED

8)无符号整数 : UNSIGNED

SELECT CONVERT (值 ,类型)；

将字符串类型转换为int类型
SELECT CONVERT('123',SIGNED);

5.ifnull

接受两个参数，如果不是NULL则返回第一个参数，否则返回第二个参数

SELECT 列名,IFNULL(列名,参数) FROM 表名;

如果零售价为NULL，返回0
SELECT id,productName,IFNULL(saleprice,0) FROM product

6.SUBSTR

SELECT SUBSTR(列名,开始截取位置,截取个数) FROM 表名;

产品名留2个字符
SELECT SUBSTR(productname,1,2) FROM product