Mysql进阶

典型单行函数

• 大小写操作函数
  lower（）转大写
  upper（）转小写
  initcap（）首字母大写

• 字符操作函数
  concat(‘h,’l’)字符连接
  substr(‘hello’,1,5)取字符（从1取到5）
  length(‘helloworld’)字符串长度
  instr(‘helloworld’,’w’)返回w在字符串的位置

• select round(45.926,2) trunc(45.926,2) from dual round小数点后两位四舍五入；trunc保留小数点后两位

• 日期
  select sysdate from dual;//返回系统日期
select ename,hidedate (sysdate-hidedate)/7 as weeks hidedate-1 hidedate-24/24

• 常用转换函数
  select ename,hidedate,to_char(hidedate,'yyyy-mm-dd') from user;
select ename,sal to_char(sal,'$99,999.00' from emp where sal=3000;
select to_number('000011111') from user
select to_date() from user

---

典型分组函数

• avg()
• min()
• max()
• sum()
• count()
  select avg(sal) from user where deptno=30;

• groupby
  select deptno,avg(sal) from user group by deptno;先分组再取平均

• having
  select deptno,max(sal) from user group by deptno having max(sal)>10000 order by deptno;having只能接在groupby后面，对分组的进行筛选

• 分组函数嵌套

---

多表查询

a表（上）与b表（下）

id	name
1	张3
2	李四
3	王武

id	job	parent_id
1	23	1
2	34	2
3	34	4

1） 内连接
select a.*,b.* from a inner join b on a.id=b.parent_id
结果是
1 　　张3 　 1 　23 　　 1
2 　　李四 　2 　34 　　 2

2）左连接
select a.*,b.* from a left join b on a.id=b.parent_id
结果是
1 　　 张3 　　 1 　　　 23 　　 1
2 　　李四 　　 2 　　　34 　　 2
3 　　 王武 　　 null

3） 右连接
select a.*,b.* from a right join b on a.id=b.parent_id
结果是
1 　　 张3 　　 1 　　23 　　1
2 　　李四 　　 2 　　34 　　2
　　null 　　　 3 　　34 　　 4

4） 完全连接
select a.*,b.* from a full join b on a.id=b.parent_id

结果是
1 　　 张3 　　1 　　　 23 　　1
2 　　李四 　　2 　　　34 　　2
　 null 　　 　　3 　　　34 　　 4
3 　　 王武 　　 null

---

典型子查询

单行子查询

select ename,sal from emp where sal>(select sal from emp where ename='rachel');薪水比rachel高的信息
select ename,sal from emp where sal=(select min(sal) from emp);薪水最低的员工

多行子查询（in,any,all）

select *from emp where empno in(select mgr from emp)查询所有经理信息
select empno,ename,job,sal from emp where sal<any(select sal from emp where job='salesman') and job<>'salesman';薪水低于销售部任意员工的非销售部员工

---

表结构操作语句

列和表约束

not null(非空)
unique(唯一)
primary key(主键)
foreign key(外键)
check(条件检查)
列约束包含上面五个，表约束包含后面四个
creat table emp (id varchar2(6) primary key,
　　　　　　　　　　name varchar2(20) not null,
　　　　　　　　　　hiredate date default sysdate not null,
　　　　　　　　　　salary number(7,2),
　　　　　　　　　　constraint emp_sal_min check(salary>1000)
　　　　　　　　　　);

增加和删除列

alter table emp add address varchar(20);
alter table emp drop column address;

修改表名

alter table emp rename to emps;

修改列名

alter table emp rename column name to ename;

删除表

drop table emp cascade constraints

查看表结构

desc emp

---

典型数据操作语句DML

insert

insert into emp (empno,ename) values (7902,'richal')

update

update emp set depto=60,job='trainer' where ename='ena'

delete

delete from emp where ename='ena'

---

典型事物操作语句
commit
rollback

---

sql语句简单优化

• 尽量避免使用*
• 尽量使用别名
  select e.ename,e.sal,d.deptno from emp e,dept d where d.deptno=e.deptno;
• where子句中的连接顺序
  （where从右往左运行）所以将缩小范围最大的条件放在右边
  select * from emp e,dept d where d.deptno>10 and e.deptno=30;
• 使用”>=”替代”>”
  用truncate代替delete（delete会将数据放在回滚段中，commit前可以恢复）
• 尽量多的使用commit
• 避免在索引列上使用函数

---

查询限制
where ename like‘s%’//%是通配符，表示匹配所有s开头的
where ename in (700,800,900)//选中700，800，900的