SQL基础练习

SQL基础练习

我们新建了一张user表，存放的是用户信息，下面我家们进行sql基础语句的练习（基于mysql数据库）：
user表

1、查询表中指定列

SELECT 列名 FROM 表名 WHERE 条件;

示例：

select * from user where id=41;     // * 表示所有列

2、更新表中指定的列

update 表名 set (列1=‘值1’，列2=‘值2’,...) where 条件

示例：

update user set username='小明',sex='女',address='杭州' where id=65

更新后的user表

3、插入表中指定的列的数据

INSERT INTO 表名 (列1, 列2,...) VALUES (值1, 值2,....),(值1, 值2,....);

示例：

insert into user (id,username,sex,address,birthday) values (66,'小馁','女','内蒙','2015-03-04 12:04:06');

插入后的user表

4、分组和排序

//group by 结合聚合函数用于对某一列进行分组   
//order by 降序排列desc  升序排列asc  默认是升序排列  
select sex,COUNT(u.id) as num from user u GROUP BY u.sex ORDER BY num desc;

5、in和like

//返回username为’小东‘或者’小妮‘的行  
select * from user u where u.username in ('小东','小妮');

//模糊匹配username中含有’东‘的列  
select * from user u where u.username like '%东%'

我们另外新建一张role表，一张user_info表，role表存放的是角色信息，user_role存放的用户和角色的关联信息，同一个用户可能有多个角色，同一个角色可能对应多个用户，通过这三张表来进行多表操作。

role表

user_role表

4、多表查询

4.1、查询所有用户及用户对应的角色信息

select u.*,r.ROLE_NAME,r.ROLE_DESC from user u left join user_role ur on u.id=ur.uid left join role r on r.id=ur.rid;

这里主要的使用的 lefte join语法，示例图如下，（不过上述示例我们使用了两次left join，通过user_info将user表与role表关联起来）：

left join产生表1的全集，表2中匹配到的有值，没有匹配到的填入null

与此相类似的还有 inner join 、join、right join，full join其中join和inner join功能一样，示例图如下：

inner join和join有一样的功能，返回表1和表2都存在的部分

right join产生表2的全集，表1匹配到的有值，没有匹配到的为null

full join产生表1和表2的全集，没有的填充null（mysql不支持，尴尬。。。）

4.2、查询id为45的用户信息及其角色信息：

select u.*,r.ROLE_NAME,r.ROLE_DESC from user u left join user_role ur on u.id=ur.uid left join role r on ur.rid=r.id where u.id=45;

以下是关于on和where条件需要注意的地方：

内连接 join、inner join可以和on以及where连用，外连接left join、right join只能和on连用；

以使用left jion为例，on和where条件的区别如下：
1、on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录。
2、where条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有left join的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉。

right join同样如此，但join和inner join返回的是两表的交集，所以条件在on或者where之后结果都是一样的。 

对比如下：