1. DML
1)创建表
create table ruoze(id int,
name varchar(20),
age int,
…
createtime timestamp,
creaateuser varchar(20),
updatetime timestamp,
updateuser varchar(20))
ENGINE=Innodb
2)增删改
insert into ruoze(id,name,age) values(1,‘doudou’,18),在这里指定了要插入值的字段,也可以不指定,但是就要将所有字段的值都明文写出来。
更新数据:update ruoze set age=20 where id = 1; 需要指定要更新的表和更新的字段
删除:delete from ruoze where id = 1;
2. Where 筛选
1)模糊查询 like
select * from zuore where name like ‘%abc%’ 查询name这个字段带有abc英文字母的记录;
select * from zuore where name like ‘%s’ 匹配name这个字段中结尾为s的记录;
select * from zuore where name like ‘_o%’ 匹配第二个字母是o的记录,_在这里是占位符。
2)IN & NOT IN
SELECT population FROM world WHERE name IN (‘Ireland’, ‘Iceland’, ‘Denmark’);
SELECT YR,SUBJECT,WINNER from nobel where subject not in (‘Chemistry’,‘Medicine’)
3)语法细节
SELECT population FROM world WHERE name != ‘Germany’;
不等于:<> 或者 != 都是表示不等于
SELECT yr,subject,winner from nobel where (yr between 1980 and 1989) ;
between用法
SELECT name, population, area FROM world where ( area>3000000 and population <= 250000000)OR (area<= 3000000 and population > 250000000)
逻辑
3.函数
1)ROUND
SELECT name,ROUND((GDP/population),-3 ) from world。。。
ROUND(7253.86, 0) -> 7254
ROUND(7253.86, 1) -> 7253.9
ROUND(7253.86,-3) -> 7000
2)length
SELECT name, capital FROM world where length(name)=length(capital)
3)any, all
Which countries have a GDP greater than every country in Europe?
select name from world where gdp >all
(select gdp from world where continent =‘Europe’ and GDP>0)
4)限制了输入行数 select * from ruoze limit 2;
5)上下合并表
union和union all 第一个操作会将两个表当中相同的记录去重,而union all则保留;
select name,age from a
union
select mingzi,nianling from b
这里的name和mingzi的数据类型最好一样,而age和nianling的数据类型也最好一样。两个合并表的字段总数也最好一样。
4. 排序 order by
排序 order by 根据某个指定的字段以某种顺序的方式进行排序,在没指定的时候为升序;
select * from ruoze order by name asc; asc是升序,降序是desc;
select * from ruoze order by name asc, age desc; 在按照name升序的同时,当name相同则对age进行降序排列
5. 子查询
1)Which country has a population that is more than Canada but less than Poland? Show the name and the population.
select name,population from world where (population >
(select population from world where name=‘canada’) and population <(select population from world where name=‘Poland’)) 注意顺序
6. 聚合
1)group by依据某个字段进行分组,这个字段的值相同的被放到同一个组里面,因为使用了group by 我们就不在group by后面使用where进行条件过滤了,我们使用having。having是对组进行条件筛选,字段是group by的字段,而where需要自己指定字段,是对记录进行筛选。
2)聚合函数是对已经分好的组使用一个函数,将一组的数据以一条记录显示出来,如:max sum count min avg;
select name,avg(age) from ruoze group by name having avg(age) > 18;