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_course GROUP 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,
bookprice
FROM
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 );
复制代码