1 #创建数据库
2 CREATE DATABASE db_book;
3
4 #使用数据库
5 USE `db_book`;
6
7 #表如果存在则删除
8 DROP TABLE IF EXISTS `t_book`;
9
10 #创建表
11 CREATE TABLE `t_book` (
12 `id` int(11) NOT NULL AUTO_INCREMENT,
13 `bookName` varchar(20) DEFAULT NULL,
14 `price` decimal(6,2) DEFAULT NULL,
15 `author` varchar(20) DEFAULT NULL,
16 `bookTypeId` int(11) DEFAULT NULL,
17 PRIMARY KEY (`id`)
18 ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
19
20 #插入数据
21 insert into `t_book`(`id`,`bookName`,`price`,`author`,`bookTypeId`) values (1,'Java编程思想','100.00','埃史尔',1),(2,'Java从入门到精通','80.00','李钟尉',1),(3,'三剑客','70.00','大仲马',2),(4,'生理学(第二版)','24.00','刘先国',4);
22
23 #表如果存在则删除
24 DROP TABLE IF EXISTS `t_booktype`;
25
26 #创建表
27 CREATE TABLE `t_booktype` (
28 `id` int(11) NOT NULL AUTO_INCREMENT,
29 `bookTypeName` varchar(20) DEFAULT NULL,
30 PRIMARY KEY (`id`)
31 ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
32
33 #插入数据
34 insert into `t_booktype`(`id`,`bookTypeName`) values (1,'计算机类'),(2,'文学类'),(3,'教育类');
35
36
37
38 #第三节:连接查询
39 #连接查询是将两个或两个以上的表按照某个条件连接起来，从中选取需要的数据；
40 #笛卡尔乘积
41 SELECT * FROM t_book,t_booktype;
42
43 #3.1内连接查询
44 #内连接查询是一种最常见的连接查询，内连接查询可以查询两个或者两个以上的表。
45 SELECT * FROM t_book,t_booktype WHERE t_book.bookTypeId=t_booktype.id;
46 SELECT bookName,author,bookTypeName FROM t_book,t_booktype WHERE t_book.bookTypeId=t_booktype.id;
47 SELECT tb.bookName,tb.author,tby.bookTypeName FROM t_book AS tb,t_booktype AS tby WHERE tb.bookTypeId=tby.id;
48
49 #3.2:外连接查询
50 #外连接查询可以查出某一张表的所有信息
51 #3.2.1:左连接查询
52 #可以查询出"表名1"的所有记录。而"表名2"中，只能查询出匹配的记录；
53 #SELECT 属性名列表 FROM 表名1 LEFT JOIN 表名2 ON 表名1.属性名1=表名2.属性名2;
54 SELECT * FROM t_book AS tb LEFT JOIN t_bookType AS tby ON tb.bookTypeId=tby.id;
55 SELECT tb.bookName,tb.author,tby.bookTypeName FROM t_book AS tb LEFT JOIN t_bookType AS tby ON tb.bookTypeId=tby.id;
56
57 #3.2.1:左连接查询
58 #可以查询出"表名2"的所有记录。而"表名1"中，只能查询出匹配的记录；
59 #SELECT 属性名列表 FROM 表名1 RIGHT JOIN 表名2 ON 表名1.属性名1=表名2.属性名2;
60 SELECT * FROM t_book AS tb RIGHT JOIN t_bookType AS tby ON tb.bookTypeId=tby.id;
61 SELECT tb.bookName,tb.author,tby.bookTypeName FROM t_book AS tb RIGHT JOIN t_bookType AS tby ON tb.bookTypeId=tby.id;
62
63 #3.3:多条件查询
64 SELECT tb.bookName,tb.author,tby.bookTypeName,tb.price FROM t_book AS tb,t_booktype AS tby WHERE tb.bookTypeId=tby.id AND tb.price>75;
65 SELECT tb.bookName,tb.author,tby.bookTypeName,tb.price FROM t_book AS tb,t_booktype AS tby WHERE tb.bookTypeId=tby.id AND tb.price>75 AND tb.author="埃史尔";
