一、基础查询
Select * from Student; --学生信息
Select * from Author; -- 作者信息
Select * from Press; --出版社信息
Select * from Book; --图书信息
Select * from BookType; --图书类别
Select * from BorrowBook; -- 借书信息
Select -- 查询什么,列筛选,其中*表示所有列
From -- 在哪个表查询,主要连接要查询的表名称
Where -- 满足什么条件,行筛选
- 一次性插入多行数据
insert into Student(SNO,SName)
select 95001,'Alice'
union select 95002,'Bob'
insert into Student(SNO,SName)
values(95003,'Michael')
values(95004,'Tony')
示例
- 查询处姓名为“陈鹏”的学号、手机号码和邮箱地址
Select SNO As '学号', MobileNO As '手机号码',StuEMail As '邮箱地址'
from Student
--Where SName = '陈鹏';
where SName in ('陈鹏');
- 查询处姓名不是“陈鹏”的学生的所有信息
Select SNO,Sname, Sage ,Sex, MobileNo,StuEmail
from Student
--where SName != '陈鹏';
--Where SName <> '陈鹏';
Where SName Not in ('陈鹏');
- 查询出学生年龄介于20到30间的学生学号和姓名
Select SNO As '学号', SName As '姓名'
from Student
--Where ( Sage >=20 ) And ( Sage <= 30);
where Sage Between 20 And 30; -- Between and -- 某一个范围
- 查询哪些学生没有填写“年龄”信息
Select SNo,Sname
from Student
where Sage is null; -- Null不能用算术运算符连接,用is ,not is
- 查询出“陈鹏”、”Alice”、”Bob”的学号,年龄
Select SNo As '学号', Sage As '年龄'
from Student
--where SName ='陈鹏' OR SName = 'Alice' OR SName = 'Bob';
Where SName in ('陈鹏','Alice','Bob');
- 查询出哪些图书被借过
Select distinct Bookid from BorrowBook;
Select COUNT(distinct bookId) As '被借图书量' from BorrowBook;
- 查询出小于20岁或者大于25的女生
Select *
from Student
where (Sage<20 OR Sage>30) And Sex ='女';
- 查询出列表中在“陈鹏”和“王进”中间的学生信息
Select * from Student Where SName between '陈鹏' and '王进';
Select * from Student order by SName;
- 查询出手机号码133或者134开头,倒数第2位为不是2也不是4的学生学号和姓名
Select *
from Student
where MobileNO Like '[1][3][34]%[^24]_';
-- 模糊查询:条件不明确: Like
-- 两个重要的通配符: % -->(匹配0-n多个任意字符), _ --> (匹配任意1个字符)
- 对Student表按照年龄升序排序,如果年龄一样,女生排在男生前面
Select *
from Student
Order by Sage ASC, Sex DESC;
- 查询出年龄最大的学生的学号和姓名
-- 错误写法01:
Select * from Student where Sage = MAX(Sage);