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="埃史尔";