一、添加
(1) insert into student values('0001','张三','18600001111',0);
(2)insert into student(cardId,name,sex) values('898278232','付伟伟',1);
错误:
注意:查询的数据必须与你插入的数据顺序保持一致。
二、删除
删除女同学的信息(男-1,女-0):
(1) deletefrom student where sex=0;//清空表中的数据
(2) drop from student;//删除表
三、修改
(1) UPDATE student set phone = '0001' WHERE name='小明';
(2) UPDATE student set password = '123', name = '小小鸟' WHERE userId = ' 130104197409132116'
四、查询
(1)基本查询
select*from application;查询“*”所有
select userId,appDate,status from application;查询特定列
(2)条件查询
select * from user where cardId='0001'; 一个条件查询
select * from user where cardId='0001' and sex=1; 多条件 并关系 查询
select * from user where name='小白' or cardId='0001' ;多条件 或关系 查询
例题:
查找今年6月份报名的学员
select*from application where appDate>='2017-06-01'and appDate<'2017-07-01';//范围查询
查找吉林的学员
'%a' //以a结尾的数据
'a%' //以a开头的数据
'%a%' //含有a的数据
‘_a_’ //三位且中间字母是a的
'_a' //两位且结尾字母是a的
'a_' //两位且开头字母是a的
模糊查询:
select * from student where address like '%吉' %通配符代表任意多个字符
select * from student where address like '%吉林%' _通配符代表任意一个字符
select * from student where address like '_吉%'
select*from student where address like '吉林%';
查找所有的女学员
select*from student where substr(userId,17,1)%2=0;
查找在私营企业和国有企业上班的人
select*from student where departtype='国有企业' or departtype='私营企业';
IN 与 = 的异同
- 相同点:均在WHERE中使用作为筛选条件之一、均是等于的含义
- 不同点:IN可以规定多个值,等于规定一个值
select*from student where departtype in('国有企业' ,'私营企业');
查找本科以下学历的人
select*from student where degree in('中技','初中','高中','中专','大专');
(3)排序
select * from student order by degree asc;//升序排列
select * from student order by degree,Oil ;//按照两列进行排序,前面的为主要的
select*from student order by degree desc;//降序
统计各种学历的人数
select count(userId) from student查询表中有多少条数据
select max(Price) from student 取价格的最大值
select min(Price) from student 取价格的最小值
select degree,count(userId)as num from 'student' group by degree;
(4)范围查询
例题:
统计2017年营业额
select sum(fee) from application where appDate>='2017-01-01';
统计历年营业额
select substr(appDate,1,4)as year,sum(fee)as money from application group by substr(appDate,1,4);
统计2017年学员平均收费
select avg(fee)as money from application where appDate>='2017-01-01';
(5)分组查询
select Brand from Car group by Brand having count(*)>2;//查询所有系列中数量大于2的
(6)分页查询
select* from student limit 100,10;//跳过几条数据取几条数据
(7)去重查询
Select distinct Brand from student
(8)关联查询
select name,degree,appDate,itemId from application,student where student.userId=application.userId;