select

数据表三连

• 查看表结构

desc table_name;复制代码

• 查看建表语句

show create table table_name;复制代码

• 查看表里的数据

select * from table_name;复制代码

单表查询

简单查询

• 查询所有行和列

  select * from 表名称;

  select * from book;复制代码

• 查询部分列

  select 字段名称1, 字段名称2 from 表名称;

  select `bookid`,`bookname` from book;复制代码

• 查询计算列

  select 字段名称1, 字段名称2,字段名称3 * 字段名称4 from 表名称;

  select `bookid`,`bookname`,bookprice * borrowsum from book;复制代码

• 使用别名

  select 字段名称1 as 自定义名称1, 字段名称2 as 自定义名称2, 字段名称3 * 字段名称4 as 自定义名称3 from 表名;

  select `bookid` as 图书ID,`bookname` as 图书名称,bookprice * borrowsum as 图书总价 from book;复制代码

• 消除重复行

  select distinct 字段名称 from 表名称;

  select distinct `borrowsum` from book;复制代码

条件查询

• 简单条件查询

  select * from 表名称 where 条件;

  select * from book where borrowsum > 10;复制代码

• 复合条件查询

  select * from book where 条件1 and 条件2;

  select * from book where borrowsum >= 10 and typeid =3;复制代码

• 指定范围查询

  select * from 表名 where 条件1 and 条件2;

  select * from book where borrowsum >= 10 and borrowsum <= 30;复制代码

  select * from 表名 where 条件1 or 条件2;

  select * from book where borrowsum < 10 or borrowsum > 30;复制代码

  select * from 表名 where 字段名 between 值1 and 值2;

  select * from book where borrowsum between 10 and 30;复制代码

  select * from 表名 where 字段 not between 值1 and 值2;

  select * from book where borrowsum not between 10 and 30;复制代码

• 指定集合查询

  select * from 表名 where 字段 in (值1,值2);

  select * from book where bookname in ('离散数学','Java程序设计');复制代码

  select * from 表名 where 字段 not in (值1,值2);

  select * from book where bookname not in ('离散数学','Java程序设计');复制代码

• 查询值为空的行

  select * from 表名 where 字段名 is null;

  select * from bookborrow where fine is null;复制代码

• 模糊查询

  select * from 表名 where 字段名 like '字段值_';

  select * from book where bookname like 'java_';复制代码

  select * from 表名 where 字段名 like '%字段值';

  select * from book where bookname like '%java';复制代码

  select * from 表名 where 字段名 like '%字段值%';

  select * from book where bookname like '%java%';复制代码

  select * from 表名 where 字段名 like '字段值%';

  select * from book where bookname like 'java%';复制代码

寻找高富帅...

建表语句

drop table if exists `user`;CREATE TABLE `user` (  `user_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户主键id',  `user_name` varchar(25) NOT NULL COMMENT '用户姓名',  `user_sex` tinyint(1) NOT NULL COMMENT '用户性别 男生1 女生2',  `user_height` int(11) NOT NULL COMMENT '用户身高(cm)',  `user_weight` int(11) NOT NULL COMMENT '用户体重(kg)',  `user_account` double(20,0) NOT NULL COMMENT '用户账户(精确到分)',  `user_appearance` tinyint(2) NOT NULL COMMENT '颜值, 1-10, 分数越高, 颜值越高',  PRIMARY KEY (`user_id`)) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;复制代码

插入数据

INSERT INTO `user`(`user_name`, `user_sex`, `user_height`, `user_weight`, `user_account`, `user_appearance`) VALUES ('王思聪', 1, 180, 88, 999900000.00, 6);INSERT INTO `user`(`user_name`, `user_sex`, `user_height`, `user_weight`, `user_account`, `user_appearance`) VALUES ('陈伟霆', 1, 177, 66, 88880000.00, 9);INSERT INTO `user`(`user_name`, `user_sex`, `user_height`, `user_weight`, `user_account`, `user_appearance`) VALUES ('鹿晗', 1, 170, 64, 77770000.00, 10);INSERT INTO `user`(`user_name`, `user_sex`, `user_height`, `user_weight`, `user_account`, `user_appearance`) VALUES ('刘诗诗', 2, 166, 52, 66660000.00, 9);INSERT INTO `user`(`user_name`, `user_sex`, `user_height`, `user_weight`, `user_account`, `user_appearance`) VALUES ('刘德华', 1, 165, 72,999960000.00, 9);INSERT INTO `user`(`user_name`, `user_sex`, `user_height`, `user_weight`, `user_account`, `user_appearance`) VALUES ('张艺兴', 1, 166, 70,55550000.00, 8);INSERT INTO `user`(`user_name`, `user_sex`, `user_height`, `user_weight`, `user_account`, `user_appearance`) VALUES ('张嘉倪', 2, 155, 56,44440000.00, 6);INSERT INTO `user`(`user_name`, `user_sex`, `user_height`, `user_weight`, `user_account`, `user_appearance`) VALUES ('黄子韬', 1, 177, 66,44443333.00, 5);INSERT INTO `user`(`user_name`, `user_sex`, `user_height`, `user_weight`, `user_account`, `user_appearance`) VALUES ('周笔畅', 2, 160, 54,5550000.00, 4);INSERT INTO `user`(`user_name`, `user_sex`, `user_height`, `user_weight`, `user_account`, `user_appearance`) VALUES ('陈小纭', 2, 159, 66,67890000.00, 6);INSERT INTO `user`(`user_name`, `user_sex`, `user_height`, `user_weight`, `user_account`, `user_appearance`) VALUES ('乔振宇', 1, 188,66, 67890000.00, 5);INSERT INTO `user`(`user_name`, `user_sex`, `user_height`, `user_weight`, `user_account`, `user_appearance`) VALUES ('赵丽颖', 2, 171,50, 77890000.00, 7);INSERT INTO `user`(`user_name`, `user_sex`, `user_height`, `user_weight`, `user_account`, `user_appearance`) VALUES ('王鸥', 1,  199,55, 3990000.00, 8);INSERT INTO `user`(`user_name`, `user_sex`, `user_height`, `user_weight`, `user_account`, `user_appearance`) VALUES ('蔡徐坤', 1, 187, 45, 59080000.00, 8);INSERT INTO `user`(`user_name`, `user_sex`, `user_height`, `user_weight`, `user_account`, `user_appearance`) VALUES ('Angelababy', 2, 177, 55, 45890000.00, 8);INSERT INTO `user`(`user_name`, `user_sex`, `user_height`, `user_weight`, `user_account`, `user_appearance`) VALUES ('张云雷', 1, 183,69, 45670000.00, 8);INSERT INTO `user`(`user_name`, `user_sex`, `user_height`, `user_weight`, `user_account`, `user_appearance`) VALUES ('迪丽热巴', 2, 155, 55, 458910000.00, 8);INSERT INTO `user`(`user_name`, `user_sex`, `user_height`, `user_weight`, `user_account`, `user_appearance`) VALUES ('朱一龙', 1, 180, 70, 78950000.00, 6);INSERT INTO `user`(`user_name`, `user_sex`, `user_height`, `user_weight`, `user_account`, `user_appearance`) VALUES ('郑爽', 2, 162, 56, 78540000.00, 8);INSERT INTO `user`(`user_name`, `user_sex`, `user_height`, `user_weight`, `user_account`, `user_appearance`) VALUES ('沈腾', 1, 175, 70, 3890000.00, 7);INSERT INTO `user`(`user_name`, `user_sex`, `user_height`, `user_weight`, `user_account`, `user_appearance`) VALUES ('雷杰', 1, 179, 70, 34560000.00, 8);INSERT INTO `user`(`user_name`, `user_sex`, `user_height`, `user_weight`, `user_account`, `user_appearance`) VALUES ('关晓彤', 2, 165, 55, 43440000.00, 6);INSERT INTO `user`(`user_name`, `user_sex`, `user_height`, `user_weight`, `user_account`, `user_appearance`) VALUES ('许凯', 1, 192,66, 34560000.55, 7);INSERT INTO `user`(`user_name`, `user_sex`, `user_height`, `user_weight`, `user_account`, `user_appearance`) VALUES ('焦俊艳', 2, 155, 56, 67890000.00, 5);INSERT INTO `user`(`user_name`, `user_sex`, `user_height`, `user_weight`, `user_account`, `user_appearance`) VALUES ('马丽', 2, 161, 55, 34560000.00, 7);复制代码

排序

• desc

  select * from 表名 order by 字段值 desc;

  select * from book order by bookprice desc;复制代码

• asc

  select *from 表名 order by 字段值 asc;

  select *from book order by bookprice asc;复制代码

• 多字段排序

  select * from 表名 order by 字段名1 asc, 字段名2 desc;

  select * from book order by borrowsum asc ,typeid desc;复制代码

  select * from 表名 order by 字段名1, 字段名2 desc;

  select * from book order by borrowsum, typeid desc;复制代码

限制条数

select * from 表名 limit 查询个数;

select * from book limit 3;复制代码

select * from 表名 limit 开始位置, 查询个数;

select * from book limit 0, 3;复制代码

select * from book limit 1, 3;复制代码

聚合函数

• 最大值(max)

  select max(字段名) as '自定义名称' from 表名;

  select max(bookprice) as '最贵的书' from book;复制代码

• 最小值(min)

  select min(字段名) as '自定义名称...' from 表名;

  select min(borrowsum) as '最受嫌弃的书...' from book;复制代码

• 数量(count)

  select count(字段名) from 表名;

  select count(bookid) from book;复制代码

• 总和(sum)

  select sum(字段名) from 表名;

  select sum(borrowsum) from book;复制代码

• 平均值(avg)

  select avg(字段名) from 表名;

  select avg(bookprice) from book;复制代码

分组查询

• 简单分组

  select 字段名1, count(字段名2) from 表名 group by 字段名1;

  select borrowsum, count(bookid) from book group by borrowsum;复制代码

• 筛选分组结果

  select 字段1, count(字段2) from 表名 group by 字段1 having count(字段2) = 1;

  select borrowsum, count(bookid) from book group by borrowsum having count(bookid) = 1;复制代码

• 分组排序

  select 字段1, count(字段2) from 表名 group by 字段1 order by count(字段2) desc;

  select borrowsum, count(bookid) from book group by borrowsum order by count(bookid) desc;复制代码

• 统计功能分组查询

  select 字段1,group_concat(字段2) from 表名 group by 字段1 order by 字段1 desc;

  select borrowsum,group_concat(bookname) from book group by borrowsum order by borrowsum desc;复制代码

• 多个分组查询

  select 字段1, 字段2, 聚合函数 from 表名称 group by 字段1, 字段2;

  建表语句以及插入数据

  -- ------------------------------ Table structure for choose_course-- ----------------------------DROP TABLE IF EXISTS `choose_course`;CREATE TABLE `choose_course` (  `course_name` char(10) DEFAULT NULL,  `semester_number` int(11) DEFAULT NULL,  `student_name` char(20) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;-- ------------------------------ Records of choose_course-- ----------------------------INSERT INTO `choose_course` VALUES ('语文', '1', '李雷');INSERT INTO `choose_course` VALUES ('语文', '1', '韩梅梅');INSERT INTO `choose_course` VALUES ('语文', '1', '露西');INSERT INTO `choose_course` VALUES ('语文', '2', '莉莉');INSERT INTO `choose_course` VALUES ('语文', '2', '格林');INSERT INTO `choose_course` VALUES ('数学', '1', '李雷');INSERT INTO `choose_course` VALUES ('数学', '1', '名字真难起...');复制代码

  查询语句(查询每门课, 每个学期, 都被多少同学选择)

  SELECT	course_name,	semester_number,	count('hello')FROM	choose_courseGROUP BY	course_name,	semester_number;复制代码

多表查询

内连接

• 使用from子句

  SELECT * FROM 表名1, 表名2;

  SELECT * FROM book, reader;复制代码

  select 表1.字段1, 表1.字段2, 表2.字段1, 表2.字段2 from 表1,表2;

  select book.bookid,book.bookname,reader.readerid,reader.readername from book,reader;复制代码

• 在where中指定连接条件

  SELECT * FROM 表1, 表2 WHERE 表2.字段 = 表1.字段;

  SELECT * FROM readertype, reader WHERE reader.retypeid = readertype.retypeid;复制代码

• 使用join关键字实现连接

  select * from 表1 join 表2 on 表2.字段 = 表1.字段;

  select * from readertype join reader on reader.retypeid = readertype.retypeid;复制代码

• 为数据表使用别名

  select 表1.字段1, 表1.字段2, 表2.字段1 from 表1 join 表2 on 表2.字段 = 表1.字段;

  select readertype.retypeid,readertype.borrowquantity,reader.readerstatus from readertype join reader on reader.retypeid = readertype.retypeid;复制代码

  select 别名1.字段1, 别名1.字段2, 别名2.字段 from 表1 as 别名1 join 表2 as 别名2 on 别名2.字段 = 别名1.字段;

  select a.retypeid,a.borrowquantity,b.readerstatus from readertype as a join reader as b on b.retypeid = a.retypeid;复制代码

• 三个表连接查询

  select 表1.字段,表2.字段,表3.字段 from 表1 join 表2 on 表1.字段 = 表2.字段 join 表3 on 表3.字段 = 表1.字段;

  select bookborrow.borrowid,bookstorage.bookstatus,reader.readername from bookborrow join bookstorage on bookborrow.bookbarcode = bookstorage.bookbarcode join reader on reader.readerid = bookborrow.readerid;复制代码

  select 表1.字段, 表2.字段, 表3.字段 from 表1 join 表2 on 表2.字段 = 表1.字段 join 表3 on 表3.字段 = 表2.字段;

  select bookstorage.bookintime,book.bookname,booktype.typename from bookstorage join book on book.bookid = bookstorage.bookid join booktype on booktype.typeid = book.typeid;复制代码

  select 表1.字段, 表2.字段, 表3.字段 from 表1 inner join 表2 on 表2.字段 = 表1.字段 inner join 表3 on 表3.字段 = 表2.字段;

  select bookstorage.bookintime,book.bookname,booktype.typename from bookstorage inner join book on book.bookid = bookstorage.bookid inner join booktype on booktype.typeid = book.typeid;复制代码

外连接

set foreign_key_checks=0;drop table if exists `user`;drop table if exists `user_detail`;create table user(	user_id int(11) primary key auto_increment not null,	user_name varchar(20) not null ,	user_sex tinyint(1) not null default 1 comment '1 for male 2 for female');create table user_detail(	user_detail_id int(11) not null primary key auto_increment,	user_detail_address varchar(255) default '河南平顶山' not null,	user_detail_phone char(11) not null unique,	user_detail_uid int(11) not null,	foreign key(user_detail_uid) REFERENCES user(user_id));insert into user(`user_name`) values('张三');insert into user(`user_name`) values('李四');insert into user(`user_name`) values('王五');insert into user(`user_name`) values('赵六');insert into user(`user_name`) values('钱七');insert into user(`user_name`) values('孙八');insert into user(`user_name`) values('周老九');insert into user(`user_name`) values('吴老十');INSERT INTO `user_detail` VALUES (1, '河南平顶山', '15639279531', 1);INSERT INTO `user_detail` VALUES (2, '河南平顶山', '15639279532', 2);INSERT INTO `user_detail` VALUES (3, '河南平顶山', '15639279533', 3);INSERT INTO `user_detail` VALUES (4, '河南平顶山', '15639279534', 4);INSERT INTO `user_detail` VALUES (11, '河南平顶山', '15639279521', 11);INSERT INTO `user_detail` VALUES (12, '河南平顶山', '15639279522', 12);INSERT INTO `user_detail` VALUES (13, '河南平顶山', '15639279523', 13);INSERT INTO `user_detail` VALUES (14, '河南平顶山', '15639279524', 14);复制代码

• 左外连接

  select * from 表1 left join 表2 on 表1.字段 = 表2.字段;

  select * from user left join user_detail on user.user_id = user_detail.user_detail_uid;复制代码

  select * from 表2 right join 表1 on 表1.字段 = 表2.字段;

  select * from user_detail right join user on user.user_id = user_detail.user_detail_uid;复制代码

• 右外连接

  select * from 表1 right join 表2 on 表1.字段 = 表2.字段;

  select * from user right join user_detail on user.user_id = user_detail.user_detail_uid;复制代码

• 内连接

  select * from 表1 inner join 表2 on 表2.字段 = 表1.字段;

  select * from user inner join user_detail on user_detail.user_detail_uid = user.user_id;复制代码

  select * from 表1 join 表2 on 表2.字段 = 表1.字段;

  select * from user join user_detail on user_detail.user_detail_uid = user.user_id;复制代码

交叉连接

以下两句, 效果一样

select * from 表1 cross join 表2 ;

select * from user cross join user_detail ;复制代码

select * from 表1,表2;

select * from user,user_detail;复制代码

以下两句, 效果一样

select * from 表1 cross join 表2 on 表2.字段 = 表1.字段;

select * from user cross join user_detail on user_detail.user_detail_uid = user.user_id;复制代码

select * from 表1,表2 where 表1.字段 = 表2.字段;

select * from user,user_detail where user.user_id = user_detail.user_detail_uid;复制代码

自连接

以下两句, 效果一样

select 表别名1.字段1, 表别名1.字段2 from 表名 as 表别名1,表名 as 表别名2 where 表别名1.字段2 > 表别名2.字段2 and 表别名2.字段1 = '字段值' order by 表别名1.`字段2 desc,表别名1.字段1

SELECT	b2.bookname,	b2.borrowsum FROM	book AS b2,	book AS b1 WHERE	b2.borrowsum > b1.borrowsum 	AND b1.bookname = '中医的故事' ORDER BY	b2.borrowsum DESC,	b2.bookname;复制代码

SELECT	b2.bookname,	b2.borrowsum FROM	book AS b2 join	book AS b1 on	b2.borrowsum > b1.borrowsum 	where b1.bookname = '中医的故事' ORDER BY	b2.borrowsum DESC,	b2.bookname;复制代码

select 字段1,字段2 from 表名 where 字段3 > (select 字段3 from 表名 where 字段1='字段值') order by 字段2 desc, 字段1;

select bookname,borrowsum from book where bookprice > (select bookprice from book where bookname='中医的故事') order by borrowsum desc, bookname;复制代码

联合查询

select * from 表1 left join 表2 on 表1.字段 = 表2.字段 union select * from 表1 right join 表2 on 表1.字段 = 表2.字段;

select * from user left join user_detail on user.user_id = user_detail.user_detail_uid union select * from user right join user_detail on user.user_id = user_detail.user_detail_uid;复制代码

子查询

使用比较运算符的子查询

• 查询价格高于机械设计手册的书籍的书籍号, 书籍名称, 书籍单价, 价格从高到低排序

SELECT	bookid,	bookname,	bookpriceFROM	book WHERE	bookprice > ( SELECT bookprice FROM book WHERE bookname = '机械设计手册' ) ORDER BY	bookprice DESC;复制代码

• 查询类别是学生的读者信息, 包括读者编号, 读者姓名, 发证日期

SELECT	readerid,	readername,	readerdate FROM	reader WHERE	retypeid = ( SELECT retypeid FROM readertype WHERE typename = '学生' );复制代码

也可以使用连表查询......

SELECT	readerid,	readername,	readerdate FROM	reader	JOIN readertype ON readertype.retypeid = reader.retypeid 	AND typename = '学生';复制代码

[not] in 子查询

• 查询已经借出的书籍id, 书籍名称

SELECT	bookid,	bookname FROM	book WHERE	bookid IN ( SELECT bookid FROM bookstorage WHERE bookstatus = '借出' );复制代码

• 查询没有借出(在馆)的书籍id, 书籍名称

SELECT	`bookid`,	`bookname` FROM	`book` WHERE	`bookid` NOT IN ( SELECT `bookid` FROM `bookstorage` WHERE `bookstatus` = '借出' );复制代码

SELECT	`bookid`,	`bookname` FROM	`book` WHERE	`bookid` IN ( SELECT `bookid` FROM `bookstorage` WHERE `bookstatus` != '借出' );复制代码

any 子查询

> any 大于最小的 < any 小于最大的 = any 相当于in();

• 选择book表中, 价格大于机械工业出版社最便宜价格的图书(图书ID, 图书名称, 出版社, 价格)

SELECT	`bookid`,	`bookname`,	`bookpublisher`,	`bookprice` FROM	`book` WHERE	`bookprice` > ANY ( SELECT bookprice FROM book WHERE bookpublisher = '机械工业出版社' );复制代码

all 子查询

> all 大于最大的 < all 小于最小的

• 选择book表中, 价格大于机械工业出版社最贵价格的图书(图书ID, 图书名称, 出版社, 价格)

SELECT	`bookid`,	`bookname`,	`bookpublisher`,	`bookprice` FROM	`book` WHERE	`bookprice` > all ( SELECT bookprice FROM book WHERE bookpublisher = '机械工业出版社' );复制代码

[not] exists子查询

• 查看图书类别表中没有图书的类别id和类别名称

SELECT	typeid,	typename FROM	booktype WHERE	NOT EXISTS ( SELECT * FROM book WHERE booktype.typeid = book.typeid );复制代码

• 查看图书类别表中有图书的类别id和类别名称

SELECT	typeid,	typename FROM	booktype WHERE	EXISTS ( SELECT * FROM book WHERE booktype.typeid = book.typeid );复制代码

快速跳转