左右连接内连接
select * from aa,cc where id=s_id; #两个表进行拼接
| 1001 | zhangsan || 1001 | 99 |
select * from aa,cc where id=s_id and name='zhangsan';
| 1001 | zhangsan || 1001 | 99 |
select score from aa,cc where id=s_id and name='zhangsan';
| 99 |
临时表写法:
select * from aa,cc where id=s_id;
| 1001 | zhangsan || 1001 | 99 |
select * from (select * from aa,cc where id=s_id)a;
| 1001 | zhangsan || 1001 | 99 |
select * from (select * from aa,cc where id=s_id)a where a.name='zhangsan';
select score from (select * from aa,cc where id=s_id)a where a.name='zhangsan';
select * from (select * from aa,cc where id=s_id)a where name='zhangsan';
举例:
select * from aa order by score desc group by class;如果出现抛异常语法不能这样
select * from(select * from aa order by score desc)a group by class;参考一下临时表的方法
select max(score),name,clss from aa;
select name,class from(select max(score),name,clss from aa)a;
嵌套:
select * from aa where name='zhangsan';张三的所有信息
| 1001 | zhangsan |
select id from aa where name='zhangsan';
| 1001 |
select * from cc;
| 1001 | 99 |
select * from cc where s_id in(select id from aa where name='zhangsan');
| 1001 | 99 |
select score from cc where s_id in(select id from aa where name='zhangsan');
| 99 |
select * from aa,cc where id=s_id and name='zhangsan';
求出谁没参加考试?
+------+----------+
| id | name | 姓名表 aa
+------+----------+
| 1001 | zhangsan |
| 1002 | lisi |
+------+----------+
+------+-------+ 成绩表cc
| s_id | score |
+------+-------+
| 1001 | 99 |
+------+-------+
| id | name |s_id score
+------+----------+
| 1001 | zhangsan || 1001 | 99 |
+------+-------+
| 1002 | lisi | NULL NULL
+------+----------+
select * from aa,cc where id=s_id;
| 1001 | zhangsan || 1001 | 99 |
select id from aa,cc where id=s_id; 1001
select * from aa where id not in(select id from aa,cc where id=s_id)
| 1002 | lisi
select name from aa where id not in(select id from aa,cc where id=s_id)
lisi
select * from aa left join cc on id=s_id;
| 1001 | zhangsan || 1001 | 99 |
+------+-------+ +------+-------+
| 1002 | lisi | NULL NULL
select * from aa left join cc on id=s_id where score is null;
| 1002 | lisi | NULL NULL
select name from aa left join cc on id=s_id where score is null;
lisi
select * from cc right join aa on id=s_id;
| 1001 | zhangsan || 1001 | 99 |
+------+-------+ +------+-------+
| 1002 | lisi | NULL NULL
select * from cc right join aa on id=s_id where score is null;
| 1002 | lisi | NULL NULL
select name from cc right join aa on id=s_id where score is null;
lisi
select * from aa left join cc on id=s_id;
| 1001 | zhangsan || 1001 | 99 |
+------+-------+ +------+-------+
| 1002 | lisi | NULL NULL
select * from(select * from aa left join cc on id=s_id)a where score is null;
| 1002 | lisi | NULL NULL
select * from(select name from aa left join cc on id=s_id)a where score is null;
lisi
索引的作用? (记住)
1.快速读取数据
2.保证数据记录的唯一性
3.实现表与表之间的参照完整性
4.在使用order by ,group by子句进行检索时,索引可以减少排序和分组的时间。
优点:
1.大大加快数据的检索速度
2.创建唯一性索引,保证数据库中的每一行数据的唯一性。
3.加速表与表之间的连接
4.在使用分组和排序进行检索时,可以显著的减少查询的时间。
缺点:
1.索引占物理空间,
2.当对表中的数据进行增加,删除和修改的时候,索引也要动态的维护,降低了数据的维护速度。
3.1 普通索引 ---创建后不会对字段有任何约束,可以为空,不为空,相同
index==索引的意思
创建普通索引:index
create index a on aa(id); 给aa表中的id字段创建了一个普通索引别名:a
查询索引:
show index from aa; 查询aa表中有哪些索引
删除索引:
alter table aa drop index a; 删除aa表中的索引:a
3.2 唯一索引--值不能相同,可以为空
创建普通索引:unique index
create unique index a on aa(id);
查询索引:
show index from aa; 查询aa表中有哪些索引
删除索引:
alter table aa drop index a; 删除aa表中的索引:a
3.3 主键索引--不能为空,不能相同(常见)
创建表的时候增加主键:
create table aa(id int(4)primary key);
表已创建OK增加主键:
alter table aa change id id int(4)primary key;
alter table aa add primary key(id);
查询索引:
show index from aa; 查询aa表中有哪些索引
删除索引:
alter table aa drop primary key;
普通索引,唯一索引,主键索引的区别?
1、普通唯一索引在表中是可以有多个的存在(但是一般不会这样做)
2、主键索引在表中只能有1个
3、普通没有任何约束,唯一值不能相同但是可以为空,
主键索引--不能为空,不能相同
|
|