数据库基本语句

查询数据：
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 https://www.360docs.net/doc/f37754895.html, as 读者姓名,https://www.360docs.net/doc/f37754895.html, 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