SQL整理

SQL整理
/* mysql */
#对库的操作

											-- 创建数据库

create database databa character set utf8 collate utf8_general_ci;

show databases; – 查看所有数据库
show create database databa; – 查看指定数据库
alter database databa character set gbk; – 改变数据库编码
drop database databa ; – 删除数据库
use databa ; – 切换数据库
select database(); – 查看当前所在的数据库

---

#对表的操作

#创建一个张表user,该表具有以下字段: – 创建表
create table user(

主键 自动增长

id int primary key auto_increment,

字符串 长度20 非空

username varchar(20) not null, 

字符串 长度20 非空 转译符

`password` varchar(20) not null,

字符串 长度10

gender varchar(10), 

整型 默认25岁

age int default 25,

字符串 长度50 唯一 非空

email varchar(50) unique not null,

浮点型 最大8位其中两位小数

salary double(8,2),

整型 默认0

state int default 0,

字符串 长度10 默认值 “VIP”

role varchar(10) default 'VIP',

时间戳

registtime timestamp

);
show tables; – 查看库下所有表
show create table user; – 查看指定表的创建
desc user; – 查看表结构
drop table user; – 删除表
rename table user to users; – 修改表名
alter table users character set gbk; – 改表的编码
desc users;

---

#表内数据处理

alter table users add idcard varchar(18) unique;-- 新增字段
alter table users drop idcard; – 删除字段
alter table users change gender sex varchar(2); – 修改字段
alter table users modify role varchar(30); – 修改数据类型

					-- 添加值,同时添加多个

insert into users(id,username,password,sex,age,email,salary,state,role,registtime)
values(null,‘zhangsan’,‘123’,‘1’,25,‘zs@itcast.cn’,100,0,‘teacher’,null),
(null,‘lisi’,‘321’,‘0’,35,‘ls@itcast.cn’,200,0,‘teacher’,null);

insert into users(id,username,password,sex,age,email,salary,state,role,registtime)
values(null,‘wangwu’,‘123’,‘1’,25,‘ww@itcast.cn’,300,0,‘teacher’,null);
– 不指定字段添加
insert into users values(null,‘tianqi’,‘123’,‘1’,25,‘tq@itcast.cn’,400,0,‘teacher’,null);
– 修改值
update users set password=‘zhangsan123’,age=45 where id=1;
– 删除指定数据
delete from users where email =‘ww@itcast.cn’;

truncate table users; – 摧毁性的删除方式,直接把表删除建一个新表
#排序

selectfrom emp;
selectfrom emp order by salary desc; – order by asc 升 desc 降
selectfrom emp order by salary desc limit 2,5; – limit(n,m) 跳过n,取
(selectfrom emp limit (2,5) order by salary desc ;

#查
selectfrom users; – 查看表中所有数据
select sex 性别, role 职务 from users; – 指定列查询,带别名显示
select distinct sex from users; – 去重查询
select username ,salary100 工资 from users; – 带运算查询 null运算还是空
#聚合函数 统计函数
select count(salary) 计数 from users; – 计数, null不计入
select sum(salary) 和 from users; – 求和, null看成0
select max(salary)最大值 from users; – 求最大值
select min(salary) 最小值 from users; – 求最小值
select avg(salary)平均值 from users; – 求平均值,算出的是double类型
#条件查询
select*from users where age = 25 and salary>200;-- 给定条件查询,and与,or或,not非
#and的优先级大于or

					-- 范围查询包含两端点

selectfrom users where salary between 250 and 400;
selectfrom users where salary in (200,400); – 指定数值查询
selectfrom users where id in (13,15);
selectfrom users where username like ‘___%a%’; – 模糊查询 %代替任意个字符 _代替一个字符
#分组查询
select id,username 名字,salary 工资 from users where salary>=200 and id > 10
group by age having age=25 or age=35 order by username asc ;

#执行优先级
from > where > group by > having > select >order by

– (7) SELECT
– (8) DISTINCT <select_list>
– (1) FROM <left_table>
– (3) <join_type> JOIN <right_table>
– (2) ON <join_condition>
– (4) WHERE <where_condition>
– (5) GROUP BY <group_by_list>
– (6) HAVING <having_condition>
– (9) ORDER BY <order_by_condition>
– (10) LIMIT <limit_number>

		/* oracle */

–创建表空间需要dba的权限：切换到 system 管理员
create tablespace itheima87 – 表空间名字
datafile ‘c:\itheima87.dbf’ – 文件地址
size 500m – 内存大小
autoextend on – 自动扩容
next 300m; – 扩容大小

– ====数据类型
char : 定长
varchar2 : 变长
long : 存储2G字符串
– ======数字
number(v1,p1): v1 : 是整个数字的总长度； p1:小数位数，省略p1,就是整数
–小数
number(3,2) : 最大值：9.99
–整数
number(3) : 最大值：999
– ======日期
date : 精确到时分秒，类似mysql ：datatime
timestamp: 精确到秒的后9位 ，用于秒杀场景
– ======大数据类型
blob : 存储4G的二进制文件
clob : 存储4G的字符串

– 创建表
create table person(
id number(10) primary key, – 主键
name varchar2(200) not null unique, – 唯一非空
gender number(1) default 1 check(gender in(1,2)) – 给一默认值 限定值为(1,2)
);

– 事务回滚
insert into person (id,name,gender) values (1,‘樱木’,1);
savepoint s1; – 定义回滚点
insert into person (id,name,gender) values (2,‘三井寿’,2);
rollback to s1; – 回滚到s1点 三井寿添加不成功
commit;

– 创建序列
create sequence seq_test;
– currval ： 查看序列的当前值，刚创建完的序列，不能马上执行查询当前值，必须要先执行一次nextval
– nextval : 查询序列的下一个值，每执行一次都会加一，从1开始
– 创建试图
create view v_dept_avg_sal as
select deptno,round(avg(sal),2) as davg from emp group by deptno;

– 添加索引
create index index_person_name on person(name);

– 同义词 表格限权
create synonym semp for scott.emp;
select * from semp;

–1、在服务器端【oracle服务所在的机器】操作： 在命令提示符中去操作,最后不能有分号，效率最高，推荐用这种
导出数据：
导出所有数据：
exp scott/123456 file=‘c:\scott.dmp’
导出具体的表的数据：
exp scott/123456 file=‘c:\scott_emp_dept.dmp’ tables=emp,dept

  导入数据：
     导入所有数据：
                imp feifei/feifei123 file='c:\scott.dmp' full=y
                imp feifei/feifei123 file='c:\scott.dmp' fromuser=scott touser=feifei
     导入具体的表的数据：
                imp feifei/feifei123 file='c:\scott_emp_dept.dmp' fromuser=scott touser=feifei tables=emp,dept

SELECT concat(concat(‘a’, ‘b’), ‘c’) FROM dual; --拼接字符串

SELECT sal * 12 + nvl(comm, 0) AS 年收入 FROM emp; --判空

SELECT * FROM emp ORDER BY comm DESC NULLS LAST; --排序的时候处理空值： nulls first | nulls last

/*
多表操作 scott用户
*/

– 显示薪资级别
SELECT decode(s.GRADE,1,‘一级’,2,‘二级’,3,‘三级’,‘四级’) salarygrade – 判断s.grade的值 如果为1就显示一级,2就显示二级,3就三级,其他就显示四级
FROM EMP e ,SALGRADE s
WHERE e.SAL BETWEEN s.LOSAL AND s.HISAL; – 筛选出 e.sal 值在 s.losal ~s.hisal 之间的

– 查询工资大于 7654,并且跟 7788 同一个职位的员工
– 子查询 把查出的元素当条件来用
SELECT e.*
FROM EMP e
WHERE e.SAL > (
SELECT SAL FROM EMP WHERE EMPNO = 7654 – 查询7654的工资
) AND e.JOB = (
SELECT JOB FROM EMP WHERE EMPNO = 7788 – 查询7788的职务
);

– 查询是领导的员工
SELECT *
FROM EMP
WHERE EMPNO IN (SELECT DISTINCT MGR FROM EMP); – in匹配多个元素 会去空

– 排序后分页
– 第一页
SELECT *
FROM (SELECT *
FROM EMP
ORDER BY SAL DESC) e
WHERE ROWNUM <= 3;

– 第二页
SELECT *
FROM (SELECT
ROWNUM r,
e.*
FROM (SELECT *
FROM EMP
ORDER BY SAL DESC) e
WHERE ROWNUM <= 6) e2
WHERE r > 3;