1.连接 表连接 内连接和外连接 自连接
(1)表连接 from后加多表,在where后加连接条件
例:
-- 查询人力资源部的所有员工,显示: 人名 职位 部门名称 城市
select * from employee,department where deptno=id;
select ename,ejob,dname,city from employee a,department b where deptno=b.id and dname='人力资源部';
select a.username,b.name ,c.title
-> from bbs_user a,bbs_category b,bbs_forum c
-> where b.cid = c.cid and c.uid = a.uid;
(2)内连接
select 字段名,... from 表1 inner join 表2 on `表1`.xxx = 表2.xxx where 条件
例:查询工作地点在北京的员工姓名,职位,奖金,底薪,部门名
select A.id, ename,ejob,ebonus,ebsalary,dname from employee A inner join department B on A.deptno=B.id where dname='人力资源部'; #给表起一个别名A、B,方便书写
(3)右连接和左连接
1)-- 左外连接: left JOIN ....on 左侧的表为主表,主表的记录会全部显示,从表有匹配的显示,没有匹配的则显示null,右侧的表为从表
2)-- 右连接: 主表 从表 right JOIN ....on
-- 右侧的表为主表,主表的记录会全部显示,从表有匹配的显示,没有匹配的则显示null,左侧的表为从表
例:-- 部门号错误的员工
select * from department right JOIN employee on employee.deptno=department.id where department.id is null
select * from employee LEFT JOIN department on employee.deptno=department.id where department.id is null
例:注:如下案例,employee
select * from employee LEFT JOIN department on employee.deptno=department.id
select * from department LEFT JOIN employee on employee.deptno=department.id
(4)--全连接:左右连接综合 sql server oracle 有, mysql无】
-- full join ... on
select * from department full join employee on employee.deptno=department.id
(5)-- 自连接 表中有相关字段有联系
-- 查询部门员工以及主管名字
select b.*,a.ename as 主管名 from employee a,employee b where a.id=b.manager
2.函数
(1)内部函数
(2)日期函数
(3)数学函数
(4)其他函数
应用:
insert into phone2 values(3,'iphone','12 plus','黑色','6999',CURDATE())
insert into phone2 values(4,'iphone','12','黑色','5999',NOW())
select * from employee where MONTH(ehiredate)=10
3.事务
-- 多个数据库操作看成是一个整体,整体是不可分。 原子性
-- 事务的语句:
-- 1.开启事务: begin | start TRANSACTION
-- 2. 提交事务: COMMIT
-- 3. 回滚事务: ROLLBACK 还可以结合保存点使用
-- 4. 设置保存点: SAVEPOINT 名字; (可设置多个)
-- 5. 回滚到指定的保存点: rollback to 保存点名字
4.视图
-- 视图: VIEW 使用视图简化查询。视图就是固化的sql语句
create view myview as # 创建视图 create view 视图名 as
select dname from department LEFT JOIN employee on employee.deptno=department.id where employee.id is null;
-- 使用视图
select * from myview;
-- 删除视图
drop view myview;
5.索引
索引就像图书的目录,可以加快查询速度
### 3.1 索引的优点
- 可以大大加快数据的检索速度
- 唯一索引可以保证数据的唯一性
- 可以降低分组、排序的时间
- 可以使用查询优化器提高系统性能
### 3.2 索引的缺点
- 建立索引会建立对应索引文件,占用大量空间
- 建立索引会降低增、删、改的效率
### 3.3 不建立索引
- 频繁更新的字段不要建立索引
- 没出现在where、having,不要建立索引
- 数据量少的表没有必要建立索引
- 唯一性比较差的字段不要建立索引
### 3.4 索引分类
#### 普通索引
create index 索引名 on 表名(字段 asc/desc) 默认asc升序
#### 唯一索引
在唯一索引所在列不能有重复值,增加和修改会受影响。
~~~
create unique index 索引名 on 表名(字段 asc/desc) 默认asc升序
~~~
#### 主键索引
创建表,主键索引会自动添加,要求在主键上不能有重复值,不能有空值
#### 复合索引(联合索引) 索引了多个列
- 使用联合索引,必须包含左前缀。 (a,b,c)
- a
- a,b
- a,b,c
#### 全文索引(了解)
一般会用全文索引服务器(sphinx),不会直接创建全文索引
create FULLTEXT index 索引名 on 表名(字段 asc/desc)
#### 删除索引drop index 索引名 on 表
#### 查看索引 show index from 表 \G
#查看sql性能
explain select sno,sname from student where class='1812'\G;
mysql> explain select sno,sname from student where sclass='1812' ;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | student | NULL | ALL | NULL | NULL | NULL | NULL | 10 | 10.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
type: ALL 全表扫描
index 使用索引
range 在指定范围内使用索引
const、system 常量查询
~~~
#### 其它创建索引的方式
~~~
alter table 表 add index(字段1,字段2,...)
alter table 表 add primary key(字段1,字段2,...)
alter table 表 add unique(字段1,字段2,...)
alter table 表 add fulltext(字段1,字段2,...)
~~~
### 3.5 不使用索引的情况
- 查询时的联合索引没有左前缀,不使用索引
- or条件里,如果一方字段没有索引,则不使用索引
- 类型不对应的不会使用索引
- like '%tom' ,如果左边是通配符,不会使用索引
- 使用!=、<>、not in操作,不使用索引