select * from Task
* 代表整个表
单独查询*改成列名称 用半角,隔开
select * from Task where id=19
显示id为9的数据
select * from Task where taskStatus=1
查询taskStatus列的值为1的数据
select * from Task where taskName='导火线'
数据库里面字符是用单引号
select * from Task where id=35 and taskName= '四儿' and userID=1
多个条件用and 或 or 连接
select id*2 from Task
所有的id都乘2
select * from Task where id>9
查询id>9的所有数据(= > < ! = ! < !> <>) <>为不等于
select * from Task where id between 19 and 24
包括19和24
select * from Task where id in(3,2,4,32,3,5)
--select * from Task where id in(3,2,4,32,3,5) --是备注
In(里面是字符串的话要加‘’)
select * from Task where id is NULL 就这个NULL有这种用法
select * from Task where taskName like '一%'
模糊查询,只查第一个字为一的,%表示后面的N个字...
select * from Task where taskName like '一______'
查第一个字为一,后面还有几个字就加几个下划线
select * from Task where taskName like '%s_'
倒数第二个是s的数据
select * from Task order by id (asc)
默认为升序
select * from Task order by id desc
降序
select * from Task where userID=1 order by id asc --组合
select COUNT(*) from Task 查询一共有多少刚数据
select sum(id) from Task 求和
select sum(id) as 总数 from Task 得出的数取个名字叫 总数
MAX() MIN() AVG() 用法同上 数据库不区分大小写
select * from Task where userID=(select userID from UserInfo where userName ='admin')
查询Task里面userID为UserInfo中userID的数据
insert into Task(taskName ,createTime,taskStatus,userID )
values('sda','2009-08-09',1,2)
插入 要一一对应
delete (from) Task where taskName ='sda'
删除
update Task set userID=100 where id=32
修改
select * from Reader where reader_ID in(select reader_ID from Borrow group by reader_ID having COUNT(book_ID)>2)
select * from Reader where reader_ID
in(select reader_id from Borrow group by reader_ID having COUNT(book_ID)>2)
and sex='女'
--错误,select book_ID from Borrow group by reader_ID 中book_ID的地方只能是reader_ID或者集合函数,
--只能返回一个值.
select * from Book where book_ID
in(select book_ID from Borrow group by reader_ID having reader_ID =
(select reader_ID from Reader where name='施秋乐'))
--查找每个出版社介个最高的图书
select publish,name from Book a where price in
(select max(price) from Book b where a.publish=b.publish)
--distinct去掉相同的reader_ID
select * from Reader where reader_ID not in(select distinct reader_ID from Borrow)
--some[] 一部分值
--all[] 全部值
select * from Borrow join Reader on Borrow.reader_ID=Reader.reader_ID where name='施秋乐'
--表的连接
select Reader.name as 读者姓名,Book.name as 图书名称,Borrow.borrowdate as 借阅时间 from Reader
join Borrow on Reader.reader_ID=Borrow.reader_ID
join Book on Book.book_ID=Borrow.book_ID
with t(图书编号,借阅人数)
as
(select book_id as 图书编号,count(*) as 借阅人数
from borrow group by book_id)
select 图书编号 from t where 借阅人数=(select max(借阅人数) from t)
--with可以构造临时表....
Update 表名 set 列名=值,..... where 条件
update reader set name='hh' where id=1212