第05课查询数据

作者: 猫哥的技术博客 | 来源:发表于2019-03-25 11:40 被阅读0次

第四章-思维导图.png

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 表名称;
msyql 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;

多表查询

908177-20160329123323113-1922028008.jpg

内连接

使用from子句

SELECT * FROM 表名1, 表名2;
```
SELECT * FROM book, reader;
```
select 表1.字段1, 表1.字段2, 表2.字段1, 表2.字段2 from 表1,表2;
myql 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 );

第05课查询数据

select

数据表`三连`

单表查询

简单查询

条件查询

寻找`高富帅`...

排序

限制条数

聚合函数

分组查询

多表查询

内连接

外连接

交叉连接

自连接

联合查询

子查询

使用比较运算符的子查询

[not] in 子查询

any 子查询

all 子查询

[not] exists子查询

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读

第05课 查询数据

select

数据表三连

单表查询

简单查询

条件查询

寻找高富帅...

排序

限制条数

聚合函数

分组查询

多表查询

内连接

外连接

交叉连接

自连接

联合查询

子查询

使用比较运算符的子查询

[not] in 子查询

any 子查询

all 子查询

[not] exists子查询

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读

第05课查询数据

数据表`三连`

寻找`高富帅`...