mysql 连接查询

第三节：连接查询

连接查询是将两个或两个以上的表按照某个条件连接起来，从中选取需要的数据；
3.1，内连接查询
内连接查询是一种最常用的连接查询。内连接查询可以查询两个或者两个以上的表；

SELECT bookName,author,bookTypeName FROM t_book,t_bookType WHERE t_book.bookTypeId=t_bookType.id;

3.2，外连接查询
外连接可以查出某一张表的所有信息；
SELECT 属性名列表 FROM 表名 1 LEFT|RIGHT JOIN 表名 2 ON 表名 1.属性名 1=表名 2.属性名 2；

3.2.1 左连接查询
可以查询出“表名 1”的所有记录，而“表名 2”中，只能查询出匹配的记录；

SELECT * FROM t_book LEFT JOIN t_bookType ON t_book.bookTypeId=t_bookType.id;
SELECT tb.bookName,tb.author,tby.bookTypeName FROM t_book tb LEFT JOIN t_bookType tby ON tb.bookTypeId=tby.id;

3.2.2 右连接查询
可以查询出“表名 2”的所有记录，而“表名 1”中，只能查询出匹配的记录；

SELECT * FROM t_book RIGHT JOIN t_bookType ON t_book.bookTypeId=t_bookType.id;

SELECT tb.bookName,tb.author,tby.bookTypeName FROM t_book tb RIGHT JOIN t_bookType tby ON tb.bookTypeId=tby.id;

3.3，多条件连接查询

第四节：子查询

SELECT * FROM t_book WHERE price>=(SELECT price FROM t_pricelevel WHERE priceLevel=1);

带 In 关键字的子查询
个查询语句的条件可能落在另一个 SELECT 语句的查询结果中
带比较运算符的子查询
子查询可以使用比较运算符。
带 Exists 关键字的子查询
假如子查询查询到记录，则进行外层查询，否则，不执行外层查询；
带 Any 关键字的子查询
ANY 关键字表示满足其中任一条件；
带 All 关键字的子查询
ALL 关键字表示满足所有条件；

SELECT * FROM t_book WHERE booktypeId IN (SELECT id FROM t_booktype);
SELECT * FROM t_book WHERE booktypeId NOT IN (SELECT id FROM t_booktype);
SELECT * FROM t_book WHERE EXISTS (SELECT * FROM t_booktype);
SELECT * FROM t_book WHERE NOT EXISTS (SELECT * FROM t_booktype);
SELECT * FROM t_book WHERE price>= ANY (SELECT price FROM t_pricelevel);
SELECT * FROM t_book WHERE price>= ALL (SELECT price FROM t_pricelevel);

第五节：合并查询结果

UNION
使用 UNION 关键字是，数据库系统会将所有的查询结果合并到一起，然后去除掉相同的记录；

SELECT id FROM t_book UNION SELECT id FROM t_booktype;

UNION ALL
使用 UNION ALL，不会去除掉系统的记录；

SELECT id FROM t_book UNION ALL SELECT id FROM t_booktype;

第六节：为表和字段取别名

为表取别名

格式： 表名 表的别名
SELECT * FROM t_book t WHERE t.id=1;

为字段取别名

格式： 属性名 [AS] 别名
SELECT t.bookName bName FROM t_book t WHERE t.id=1;

插入，更新和删除数据

第一节：插入数据

格式：INSERT INTO 表名 VALUES(值 1，值 2，值 3，...，值 n)；
INSERT INTO t_book VALUES(NULL,'我爱我家',20,'张三',1);

给表的指定字段插入数据

格式：INSERT INTO 表名(属性 1，属性 2，...，属性 n) VALUES(值 1，值 2，值 3，...，值 n

INSERT INTO t_book(id,bookName,price,author,bookTypeId) VALUES(NULL,'我爱我家',20,'张三',1);

同时插入多条记录

INSERT INTO 表名 [(属性列表)]
VALUES(取值列表 1)，(取值列表 2)
...，
(取值列表 n)；
INSERT INTO t_book(id,bookName,price,author,bookTypeId) VALUES (NULL,'我爱我家2',20,'张三',1),(NULL,'我爱我家3',20,'张三',1);

更新数据

UPDATE 表名
SET 属性名 1=取值 1，属性名 2=取值 2，
...，
属性名 n=取值 n
WHERE 条件表达式；


UPDATE t_book SET bookName='Java编程思想',price=120 WHERE id=1;
UPDATE t_book SET bookName='我' WHERE bookName LIKE '%我爱我家%';

删除数据

DELETE FROM 表名 [WHERE 条件表达式]

DELETE FROM t_book WHERE id=5;
DELETE FROM t_book WHERE bookName='我';

索引

索引的引入
索引定义：索引是由数据库表中一列或者多列组合而成，其作用是提高对表中数据的查询速度；
类似于图书的目录，方便快速定位，寻找指定的内容；
索引的优缺点
优点：提高查询数据的速度；
缺点：创建和维护索引的时间增加了；
索引实例
索引分类
1，普通索引
这类索引可以创建在任何数据类型中；
2，唯一性索引
使用 UNIQUE 参数可以设置，在创建唯一性索引时，限制该索引的值必须是唯一的；
3，全文索引
使用 FULLTEXT 参数可以设置，全文索引只能创建在 CHAR，VARCHAR，TEXT 类型的字段上。主要作用
就是提高查询较大字符串类型的速度；只有 MyISAM 引擎支持该索引，Mysql 默认引擎不支持；
4，单列索引
在表中可以给单个字段创建索引，单列索引可以是普通索引，也可以是唯一性索引，还可以是全文索引；
5，多列索引
多列索引是在表的多个字段上创建一个索引；
6，空间索引
使用 SPATIAL 参数可以设置空间索引。空间索引只能建立在空间数据类型上，这样可以提高系统获取空间数
据的效率；只有 MyISAM 引擎支持该索引，Mysql 默认引擎不支持；

创建索引

创建表的时候创建索引

	CREATE TABLE 表名 (属性名 数据类型 [完整性约束条件],
	属性名 数据类型 [完整性约束条件],
	.... 属性名 数据类型
	[UNIQUE | FULLTEXT | SPATIAL ] INDEX| KEY
	[别名] (属性名 1 [(长度)] [ASC | DESC])
	);

1，创建普通索引

CREATE TABLE t_user1(id INT ,
                     userName VARCHAR(20),
                     PASSWORD VARCHAR(20),
                     INDEX (userName)
	             );

2，创建唯一性索引

CREATE TABLE t_user2(id INT ,
                 userName VARCHAR(20),
                 PASSWORD VARCHAR(20),
                 UNIQUE INDEX index_userName(userName)
             );

3，创建全文索引
4，创建单列索引
5，创建多列索引

CREATE TABLE t_user3(id INT ,
                 userName VARCHAR(20),
                 PASSWORD VARCHAR(20),
                 INDEX index_userName_password(userName,PASSWORD)
             );

6，创建空间索引

在已经存在的表上创建索引

CREATE [ UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名
ON 表名 (属性名 [(长度)] [ ASC | DESC])；

CREATE 	INDEX index_userName ON t_user4(userName);
CREATE 	UNIQUE INDEX index_userName ON t_user4(userName);

CREATE  INDEX index_userName_password ON t_user4(userName,PASSWORD);

用 ALTER TABLE 语句来创建索引

ALTER TABLE 表名 ADD [ UNIQUE | FULLTEXT | SPATIAL ] INDEX
索引名 (属性名 [(长度)] [ ASC | DESC])；
ALTER TABLE t_user5 ADD INDEX index_userName(userName);

ALTER TABLE t_user5 ADD UNIQUE INDEX index_userName(userName);

ALTER TABLE t_user5 ADD INDEX index_userName_password(userName,PASSWORD);

删除索引

DROP INDEX 索引名 ON 表名 ；
		
DROP INDEX index_userName ON t_user5;

DROP INDEX index_userName_password ON t_user5;

视图

视图的引入
1，视图是一种虚拟的表，是从数据库中一个或者多个表中导出来的表。
2，数据库中只存放了视图的定义，而并没有存放视图中的数据，这些数据存放在原来的表中。
3，使用视图查询数据时，数据库系统会从原来的表中取出对应的数据。
视图的作用
1，使操作简便化；
2，增加数据的安全性；
3，提高表的逻辑独立性；
创建视图

CREATE [ ALGORITHM ={ UNDEFIEND | MERGE | TEMPTABLE }]
VIEW 视图名 [ ( 属性清单) ]
AS SELECT 语句
[ WITH [ CASCADED | LOCAL ] CHECK OPTION ]；

ALGORITHM 是可选参数，表示视图选择的算法；
“视图名”参数表示要创建的视图的名称；
“属性清单”是可选参数，其指定了视图中各种属性的名词，默认情况下与 SELECT 语句中查询的属性相同；
SELECT 语句参数是一个完整的查询语句，标识从某个表查出某些满足条件的记录，将这些记录导入视图中；
WITH CHECK OPTION 是可选参数，表似乎更新视图时要保证在该视图的权限范围之内；
ALGORITHM 包括 3 个选项 UNDEFINED、MERGE 和 TEMPTABLE。其中，UNDEFINED 选项表示 MySQL 将
自动选择所要使用的算法；MERGE 选项表示将使用视图的语句与视图定义合并起来，使得视图定义的某一部分
取代语句的对应部分；TEMPTABLE 选项表示将视图的结果存入临时表，然后使用临时表执行语句；CASCADED
是可选参数，表示更新视图时要满足所有相关视图和表的条件，该参数为默认值；LOCAL 表示更新视图时，要
满足该视图本身的定义条件即可；

CREATE VIEW v1 AS SELECT * FROM t_book;

CREATE VIEW v2 AS SELECT bookName,price FROM t_book;

CREATE VIEW v3(b,p) AS SELECT bookName,price FROM t_book;


SELECT * FROM v1;

SELECT * FROM v2;

SELECT * FROM v3;

在单表上创建视图
在多表上创建视图
查看视图
DESCRIBE 语句查看视图基本信息
SHOW TABLE STATUS 语句查看视图基本信息
SHOW CREATE VIEW 语句查看视图详细信息
在 views 表中查看视图详细信息

CREATE VIEW v4 AS SELECT bookName,bookTypeName FROM t_book,t_booktype WHERE t_book.bookTypeId=t_booktype.id;

CREATE VIEW v5 AS SELECT tb.bookName,tby.bookTypeName FROM t_book tb,t_booktype tby WHERE tb.bookTypeId=tby.id;

SELECT * FROM v4;

SELECT * FROM v5;

DESC v5;

SHOW TABLE STATUS LIKE 'v5';

SHOW TABLE STATUS LIKE 't_book';

SHOW CREATE VIEW v5;

修改视图

CREATE OR REPLACE VIEW 语句修改视图

CREATE OR REPLACE [ ALGORITHM ={ UNDEFINED | MERGE | TEMPTABLE }]
VIEW 视图名 [( 属性清单 )]
AS SELECT 语句
[ WITH [ CASCADED | LOCAL ] CHECK OPTION ]；

ALTER 语句修改视图

ALTER [ ALGORITHM ={ UNDEFINED | MERGE | TEMPTABLE }]
VIEW 视图名 [( 属性清单 )]
AS SELECT 语句
[ WITH [ CASCADED | LOCAL ] CHECK OPTION ]；

举例

SELECT  * FROM v1;
	
CREATE OR REPLACE VIEW v1(bookName,price) AS SELECT bookName,price FROM t_book;

ALTER VIEW v1 AS SELECT * FROM t_book;

更新视图

更新视图是指通过视图来插入（INSERT）、更新（UPDATE）和删除（DELETE）表中的数据。因为视图是一个虚
拟的表，其中没有数据。通过视图更新时，都是转换基本表来更新。更新视图时，只能更新权限范围内的数据。
超出了范围，就不能更新。
插入(INSERT)
更新(UPDATE)
删除(DELETE)

INSERT INTO v1 VALUES(NULL,'java good',120,'feng',1);

UPDATE v1 SET bookName='java very good',price=200 WHERE id=5;

删除视图

删除视图是指删除数据库中已存在的视图。删除视图时，只能删除视图的定义，不会删除数据；
DROP VIEW [ IF EXISTS ] 视图名列表 [ RESTRICT | CASCADE ]

DELETE FROM v1 WHERE id=5;