-- 建库create DATABASE db_book;use db_book;-- 建表CREATE TABLE t_bookType( id int primary key auto_increment, bookTypeName varchar(20), bookTypeDesc varchar(200));CREATE TABLE t_book( id int primary key auto_increment, bookName varchar(20), author varchar(10), price decimal(6,2), bookTypeId int, constraint `fk` foreign key (`bookTypeId`) references `t_bookType`(`id`));-- 查看表结构desc t_bookType;-- 查看表ddl（建表语句）show create table t_bookType;-- 重命名表alter table t_book rename t_book2;
-- 建表create table `t_student` ( `id` double , `stuName` varchar (60), `age` double , `sex` varchar (30), `gradeName` varchar (60)); -- 插入记录insert into `t_student` (`id`, `stuName`, `age`, `sex`, `gradeName`) values('1','张一','23','男','一年级');insert into `t_student` (`id`, `stuName`, `age`, `sex`, `gradeName`) values('2','张二','25','男','二年级');insert into `t_student` (`id`, `stuName`, `age`, `sex`, `gradeName`) values('3','张三','23','男','一年级');insert into `t_student` (`id`, `stuName`, `age`, `sex`, `gradeName`) values('4','张四','22','男','三年级');insert into `t_student` (`id`, `stuName`, `age`, `sex`, `gradeName`) values('5','张五','21','女','一年级');insert into `t_student` (`id`, `stuName`, `age`, `sex`, `gradeName`) values('6','李一','26','女','二年级');insert into `t_student` (`id`, `stuName`, `age`, `sex`, `gradeName`) values('7','李二','20','男','三年级');insert into `t_student` (`id`, `stuName`, `age`, `sex`, `gradeName`) values('8','李三','21','女','二年级');insert into `t_student` (`id`, `stuName`, `age`, `sex`, `gradeName`) values('9','李四','22','男','一年级');insert into `t_student` (`id`, `stuName`, `age`, `sex`, `gradeName`) values('10','李五','25','男','二年级');insert into `t_student` (`id`, `stuName`, `age`, `sex`, `gradeName`) values('11','小黑','21',NULL,'二年级');insert into `t_student` (`id`, `stuName`, `age`, `sex`, `gradeName`) values('12','小白','23','男','二年级');insert into `t_student` (`id`, `stuName`, `age`, `sex`, `gradeName`) values('13','小红','24',NULL,'二年级');
-- 查询SELECT id,stuName,age,sex,gradeName FROM t_student ;SELECT * FROM t_student;SELECT * FROM t_student WHERE id=1;SELECT * FROM t_student WHERE age>22;-- in 相当于集合吧，别和between混淆SELECT * FROM t_student WHERE age IN (21,22,23);SELECT * FROM t_student WHERE age NOT IN (21,23);-- [21,24]SELECT * FROM t_student WHERE age BETWEEN 21 AND 24;SELECT * FROM t_student WHERE age NOT BETWEEN 21 AND 24;-- 模糊查询SELECT * FROM t_student WHERE stuName LIKE '张三';SELECT * FROM t_student WHERE stuName LIKE '张%';SELECT * FROM t_student WHERE stuName LIKE '%张%';-- 交集SELECT * FROM t_student WHERE gradeName='一年级' AND age=23;-- 并集SELECT * FROM t_student WHERE gradeName='一年级' OR age=23;-- DISTINCT去重SELECT DISTINCT gradeName FROM t_student;-- 升序SELECT * FROM t_student ORDER BY age ASC;-- 降序SELECT * FROM t_student ORDER BY age DESC;-- 分组查询SELECT gradeName,GROUP_CONCAT(stuName) FROM t_student GROUP BY gradeName;-- 分页查询(index,size)SELECT * FROM t_student LIMIT 2,5;
再建单表
create table `t_grade` ( `id` int , `stuName` varchar (60), `course` varchar (60), `score` int ); insert into `t_grade` (`id`, `stuName`, `course`, `score`) values('1','张三','语文','91');insert into `t_grade` (`id`, `stuName`, `course`, `score`) values('2','张三','数学','90');insert into `t_grade` (`id`, `stuName`, `course`, `score`) values('3','张三','英语','87');insert into `t_grade` (`id`, `stuName`, `course`, `score`) values('4','李四','语文','79');insert into `t_grade` (`id`, `stuName`, `course`, `score`) values('5','李四','数学','95');insert into `t_grade` (`id`, `stuName`, `course`, `score`) values('6','李四','英语','80');insert into `t_grade` (`id`, `stuName`, `course`, `score`) values('7','王五','语文','77');insert into `t_grade` (`id`, `stuName`, `course`, `score`) values('8','王五','数学','81');insert into `t_grade` (`id`, `stuName`, `course`, `score`) values('9','王五','英语','89');
-- 聚合查询，还是分组聚合比较多SELECT COUNT(*) FROM t_grade;SELECT stuName,COUNT(*) FROM t_grade GROUP BY stuName;SELECT stuName,SUM(score) FROM t_grade GROUP BY stuName;SELECT stuName,AVG(score) FROM t_grade WHERE stuName="张三";SELECT stuName,AVG(score) FROM t_grade GROUP BY stuName;
USE `db_book`;DROP TABLE IF EXISTS `t_book`;CREATE TABLE `t_book` ( `id` int(11) NOT NULL AUTO_INCREMENT, `bookName` varchar(20) DEFAULT NULL, `price` decimal(6,2) DEFAULT NULL, `author` varchar(20) DEFAULT NULL, `bookTypeId` int(11) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;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);DROP TABLE IF EXISTS `t_booktype`;CREATE TABLE `t_booktype` ( `id` int(11) NOT NULL AUTO_INCREMENT, `bookTypeName` varchar(20) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;insert into `t_booktype`(`id`,`bookTypeName`) values (1,'计算机类'),(2,'文学类'),(3,'教育类');
-- 笛卡尔积SELECT * FROM t_book,t_bookType;SELECT * FROM t_book,t_bookType WHERE t_book.bookTypeId=t_bookType.id;SELECT bookName,author,bookTypeName FROM t_book,t_bookType WHERE t_book.bookTypeId=t_bookType.id;SELECT tb.bookName,tb.author,tby.bookTypeName FROM t_book tb,t_bookType tby WHERE tb.bookTypeId=tby.id;-- 返回左表所有记录，哪怕右表为空SELECT * FROM t_book LEFT JOIN t_bookType ON t_book.bookTypeId=t_bookType.id;-- 返回右表所有记录，哪怕左表为空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 LEFT JOIN t_bookType tby ON tb.bookTypeId=tby.id;SELECT tb.bookName,tb.author,tby.bookTypeName FROM t_book tb,t_bookType tby WHERE tb.bookTypeId=tby.id AND tb.price>70;
create table `t_pricelevel` ( `id` int , `priceLevel` int , `price` float , `description` varchar (300)); insert into `t_pricelevel` (`id`, `priceLevel`, `price`, `description`) values('1','1','80.00','价格贵的书');insert into `t_pricelevel` (`id`, `priceLevel`, `price`, `description`) values('2','2','60.00','价格适中的书');insert into `t_pricelevel` (`id`, `priceLevel`, `price`, `description`) values('3','3','40.00','价格便宜的书');-- 子查询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 price>=(SELECT price FROM t_pricelevel WHERE priceLevel=1);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);
博客使用的mysql实例均来自http://www.java1234.com/
总结：多表查询分为内连接查询，外连接查询，内连接分为显示和隐式，
外连接分为左外和右外，左外就是显示交集和左表数据，右外就是显示交集和右表数据，内连接就是显示交集数据，
子查询就是嵌套查询，查询出来的表作为查询条件（也不太清楚是不是这么分类和理解，姑且这么理解吧）
联系客服
