1.更新数据
update xx set name='ll' where id = 1;
2.增加列
alter table xx add column age int;
3.设置用户变量“@变量名”
使用set时可以用“=”或“:=”两种赋值符号赋值,使用select时必须用“:=”赋值符号赋值
set @age=5;
update xx set age =(@age:=@age+1);
4.=和:=的区别
- = 只有在set和update时才是和:=一样,赋值的作用,其它都是等于的作用。鉴于此,用变量实现行号时,必须用:=
- := 不只在set和update时时赋值的作用,在select也是赋值的作用。
5.<=>安全等于
与null作比较时,返回0,1;
与is null差不多。
6.where
where是对查询的结果集进行过滤
a)
select * from user where Id+0>100;
select * from user 会产生一个结果集,对这条语句来说就是user表的所有记录(所有的行所有的列)。
加了where 条件过后, where Id+0>100;那么它只会把accountId+0>100的记录保留为结果集。
b)
select * from user u join address a ON u.userId=a.userId where u.Id>100;
7.子查询
常用在where语句中
1)in/not in:表示某一列的值是否出现的一个 列表里面
a)
select * from user where userId IN(10,20,30); #列表里面的值是固定的(给定的);
# 等价于
select * from user where userId=10 or userId=20 or userId=30;
b)
select * from user where userId IN(select userId from address where userId is not null);
#列表里的值是现场查询出来的
注意 此时 user表和 address表没有表关联。
执行流程时,先取出user表第一行的userId(比如userId=100),然后执行后面的子查询,此时子查询会产生一个结果集,然后判断100是否出现在那个结果集里面。
2)exits/not exists
select * from user u where exists (select userId from address a where a.userId=u.userId);
3)any/some
SELECT s1 FROM t1 WHERE s1 > ANY (SELECT s1 FROM t2);
#等价于
SELECT s1 FROM t1 WHERE s1 > (SELECT max(s1) FROM t2);
4) =
后面如果是一个子查询,那么该子查询只能有一个返回值
select * from user where userId=(select max(userId+0) from address where userId is not null);
8.表关联查询
join 是 留下两个表都满足条件的行。两个表的地位是平等的。
select * from user u join account_custom_data acd ON u.accountId=acd.account;
left join 左表的地位要高些,无论怎样左表中所有的行都会保留下来。(右表所有的列均显示为NULL,拼接在左表的右面)
select * from user u join account_custom_data acd ON u.accountId=acd.account;
#比较经典的一个示例是用来改写子查询
select * from account_custom_data where accountId NOT IN(select id from account);
select * from account_custom_data acd left join account a ON acd.accountId=a.id
where a.id is NULL;
示例
select * from user u left join address a ON u.userId=a.userId where a.primaryAddress=1;
#等价于
select * from user u join address a ON u.userId=a.userId where a.primaryAddress=1;
#等价于
select * from user u join address a ON u.userId=a.userId and a.primaryAddress=1;
###不等于
select * from user u left join address a ON u.userId=a.userId and a.primaryAddress=1;
9.联合查询
union:会去重最后的结果集(去重条件是所有的列的值一样)
union all:不会去重
select phone1Number,userId from address where phone1Number is not null
union
select phone2Number,userId from address where phone1Number is not null;
10.分组查询
group by [having ...]:是对结果集进行分组(group by ), having 是对分组后的每个组的数组进行过滤(where是对整个结果集进行过滤)
注意:先过滤后分组,先 where后分组(group by )
select * from address group by userId having count(*)>1;
having后使用聚合函数: count()、max()、min()、avg()、sum()、count(distinct ..)
##查询有多条非primaryAddress的人(primaryAddress=0)
select * from address group by userId having count(primaryAddress=0)>1; #错误
#第一步是满足primaryAddress=0
#第二步才是看是否有多个满足条件的
select * from address where primaryAddress=0 group by userId having count(*)>1;
##查询有多条primaryAddress的人(primaryAddress=1)
select * from address where primaryAddress=1 group by userId having count(*)>1;
#等价于
select * from address group by userId having sum(primaryAddress)>1;