1.【sql语句实操】一篇搞定，带你认识sql语句

DDL语句：

Data Definition Language数据库定义语句，create、alter、drop、rename、truncate    
 1、delete、drop、truncate的区别？delete:删除某条记录； drop：删除表或者某列；truncate清空表的内容。
delete：会发起事务，可以有where过滤条件(即删除指定的数据，列名不变),属于DML语句，删除效率比truncate低
drop：属于DDL语句，删除表，删除列
truncate：不会发起事务，不能有where过滤条件(即默认删除表中所有的数据，列名不变),属于DDL语句,删除效率比delete高，但有危险性 

#创建表
create table t_student(
    id number,
    name varchar2(50),
    sex char(2),
    birthday date,
    score(3,1)--长度为3，保留一位小数

);
#查询刚建好的表格
select * from t_student;
#创建两个有主外键联系的表--班级表---学生表
create table class0728(
    cid number(8) primary key,
    cname varchar2(20)not null

);
create table student(
    sid number(8) primary key,
    sname varchar2(12) not null,
    cardid char(18) unique,
    sex char(3) check(sex='男'or sex='女'),
    age number(3)default 18,
    cid number(8)reference class0728(cid)
);
select * from class0728
select * from student

数据库DDL语句--表的增删改
create table XX();
drop table XX;
rename 旧表名 to 新表名

#表中列名的增删改
增加列：
alter table 表名
add(字段名 1 数据类型[not null|null],字段名 2 数据类型[not null |null]);
举例:
alter table t_student
add(stu_phone number,stu_addr varchar(20));

删除列：
alter table 表名 drop column 列名；
例：
alter table t_student
drop column stu_phone;

修改列：1.修改数据类型，约束等等----
alter table 表名
modify(列名 数据类型[default 默认值][not null|null]);
修改列名：2.修改列名
alter table 表名 rename column 列名 to 新列名;
例：把 t_student的sex字段名修改为ssex
alter table t_student
rename column sex to ssex;

Truncate的语法很简单，后面直接跟表名即可，例如： truncate table tbl_name 或者 truncate tbl_name

数据库DML语句
DML语句：Data Manipulation Language，数据操作语句，常见的增、删、改、查(最重要)

无条件查询（全表查询）
语法：
select 列1,列2,列3...列N 或 * from 表名;
--查询指定列名
select empno,ename,job,mgr,hiredate,sal,comm,deptno from emp;
--查询查询所有列，用 * 表示所有列名
select * from emp;

条件查询
select 列1,列2,列3...列N 或 *
from 表名
where 过滤条件;
按照指定的条件进行过滤，如“单值比较运算符”：>、<、=、!=或<>（不等于）、<=、>=，比较运算符两边的数据类型要保持一致。

例：
#如果系统中存在emp（员工信息表）和dept（部门表）两个表
#员工表内信息                  部门表内信息
--empno  员工工号           --deptno   部门编号
--ename  员工姓名           --dname   部门名
--job    工种                -- loc     部门所在地
--mgr    上司工号
--hiredate 入职日期
--sal      工资
--comm     奖金/提成
--deptno   部门编号

例：查询表中20号部门员工的姓名，工资以及部门号

select ename,sal,deptno
from emp
where deptno=20;

--练习1：查找出奖金超过其工资的雇员的姓名、工资、奖金和工种的信息。
select sname,sal,comn,job
from emp
where comm>sal;
--练习2：查找员工JONES的员工编号、工资
select empnp,sal,deptno
from emp
where ename=JONES;

对重复值进行处理 ：distinct 字段名

--例：显示出公司所有的工种信息
select distinct job
from emp;

多条件过滤之逻辑运算符：or、and、not

逻辑运算符的作用：在where后面连接多个条件，优先级：()> and >or
例：查找出全部经理 和 第10号部门秘书的工种，姓名，工资，部门号

select *from emp where job='manager' or (job='clerk' and deptno=10); 

--练习1：查找出第10号部门经理 和 秘书的有关信息。

select * from emp where (job='manager' and deptno=10) or( job='clerk' and deptno=10)
--练习2：查找出不是30号部门中的所有经理的所有信息。

select * from emp where job ='manager' and deptno<>10;

多值比较运算符

--字段名  between 值1 and 值2       表示在值1和值2之间

--字段名 not between 值1 and 值2    表示在值1和值2之外

--字段名 in(值1,值2...)                 表示字段等于多个值

--字段名 not in(值1,值2...)             表示字段不等于多个值

--练习3：查找出工资在2000到3000之间的职工姓名、工种和工资

select ename,job,sal from emp where sal between 2000 and 3000;

--练习4：查找出工资在2500到3500之外的职工姓名、工种和工资

select ename,job,sal from emp where sal not between 2000 and 3000;

--练习5：查询出全部秘书、分析员或推销员的姓名、工种、工资和所在部门号

select sname,job,sal,sno from emp where job = 'CLERK' OR job = 'ANALYST' OR job = 'SALESMAN';

--练习6：查询出工资分别是1500，2500，3000的分析员或推销员的姓名、工种、工资和所在部门号

select sname ,job,depno from emp where (sal=1500 or sal=2500 or sal=3000)and (job='ANALYST'  or job='SALESMAN'  );

ANY运算符表示其中之一的意思，逻辑上等价于OR关系。

上面练习5中，条件表达式中的运算符 "in" 可以用 "=any" 来代替。

select e.ename,e.job,e.sal,e.deptno from emp e

where e.job =any('CLERK','ANALYST','SALESMAN');

思考如下语句是否成立？

select * from emp  where empno=12345 or 1=1;

模糊查询：like

用法：字段名 like 值

匹配符：%  和  下划线_

% 表示匹配任意长度的字符串（包括空字符串） 

%test% 表示匹配包含test的任意长度的字符串

%test   表示匹配以test结尾的任意长度的字符串

test%   表示匹配以test开头的任意长度的字符串

--例：查找商品名称中包含诺基亚的商品的所有信息

SELECT *

FROM ecs_goods

WHERE goods_name LIKE '%诺基亚%';

_ 表示仅能匹配一个字符

o_a 表示匹配长度为3，以字母o开头，且以字母a结尾的字符串，中间一个字符为任意字符。

_oa 表示匹配长度为3，以任意字符开头，且以oa结尾的字符串

oa_ 表示匹配长度为3，以oa开头，结尾为任意一个字符的字符串

练习1：查询出名字以"MA"开头的全部职工的姓名、工种、工资和部门

select * from emp where ename LIKE 'MA%';

练习2：查询出名字以"SCO"开头的，长度为5位的全部职工的姓名、工种、工资和部门号

select sname ,job,sal,deptno from emp where sname LIKE'SCO__'; 

练习3：查找出所有工种以'MANAG'开头的职工姓名、工种和工资

select sname ,job,sal from emp where job LIKE 'MANAG%';

练习4：查找出所有姓名以'ALLE'开头，且长度为5的职工姓名、工种和工资

select sname ,job,sal from emp where sname LIKE 'ALLE%';

空值查询：is null 或 is not null

例：查找出emp表中上级编号(MGR)为空的职工信息。

select *

from emp

where mgr is null;

PS : (不能用" =NULL "或" !=NULL "表达式)

练习1：查找出没有奖金的员工的信息。

select *from emp where sal is null

排序显示查询结果

通常情况下，查询结果是以按数据在数据库表中存储的顺序显示的。若要使查询结果按某种特定顺序显示，就要使用“order by”子句。

格式：

select 列1,列2,列3...列N 或 *

from 表名

where 过滤条件

order by 字段名 asc/desc ;

--ascend升序、descend降序

字段名，指出查询出来的结果按该字段排序；选项asc表示按升序排序，desc表示按降序排序，ASC可以省略，默认按升序排列。

order by子句可以对查询结果按一个字段或多个字段进行排序。

order by 字段名1，字段名2...  按多个字段排序时，先按前面的字段排序，当字段相同时，再按下一个字段排序，以此类推，字段之间用逗号(,)隔开。

例：计算每个销售人员的年度总报酬，并按总报酬由高到低顺序显示

select (sal+comm)*12 年度总报酬

from emp

where job='SALESMAN'

order by (sal+comm)*12 desc;   --降序

向表中插入数据

--新增一个表：

create table student(

  stid number(8) primary key,

  sname varchar2(12),

   age number(3),

   birthday date

);

向表中新增数据格式：

insert into 表名 values(值1,值2，....);

insert into 表名(列1,列2...列N) values(值1,值2...值N);

insert into 表名(列1,列2，列3) values(值1,值2,值3);

commit | rollback;

--注意事项：

1，字段名的个数和值的个数保持一致

2，值的数据类型和字段名的数据类型要保持一致

3，没有写出列名，默认是全部列名

事务：通俗理解就是做一件事情的过程，事务封装了一条dml、或者多条dml语句。这个过程有两种结果：要么全部成功、要么全部失败。（select 默认不会发起事务）

     结束事务的两种方式：

      a1) commit，提交事务，所有的操作都生效，操作的结果直会永久保存在数据库中。

      a2) rollback，回滚事务，所有的操作都不会生效，操作的结果直接扔掉，不会保存到数据库。

事务的四个特点：原子性，一致性，持久性，隔离性。

例：

insert into student(stid,sname,age)

values(1,'小美',18);

commit;            ---提交

rollback;          ---撤销

--插入日期数据

insert into student

values(2,'小白',20,sysdate);

Commit;

sysdate：获取系统当前时间，格式是：年月日时分秒，2011/7/25 23:55:23。

to_date()函数：将具有日期格式的字符串转换成date类型

用法：to_date('具有日期格式的字符串','yyyy-mm-dd')

insert into student

values(2,'小帅',20,to_date('2000-01-01','yyyy-mm-dd'));

Commit;

---特殊插入数据（两张表字段名的个数以及数据类型需要一一匹配）

复制表：create table emp1 as select * from emp;

insert into 表名1 (select * from 表名2);--表2查出来的内容会追加到表1

insert into emp1 (select * from emp); 

--练习1：在emp表中，查找1981-05-01之前入职的员工信息。

修改表的数据

update 表名

set 列1 = 值1,列2 = 值2...列N = 值N

where过滤条件;

commit | rollback;

PS : 注意此处若不加where子句，则会修改此列所有数据

例：修改上表中1号同学的年龄为20岁。

update student

set age=20

where stid=1;

commit;

练习1：将1号同学的生日2001-01-02填进表格中

练习2：将姓高的同学的年龄修改为23岁

练习3：在emp1中，将职工ALLEN提升为经理，工资增加至$8000，奖金增加40%

练习4：在emp1中，对20号部门中的经理和销售人员提高15%的工

修改表的数据

update 表名

set 列1 = 值1,列2 = 值2...列N = 值N

where过滤条件;

commit | rollback;

PS : 注意此处若不加where子句，则会修改此列所有数据

例：修改上表中1号同学的年龄为20岁。

update student

set age=20

where stid=1;

commit;

练习1：将1号同学的生日2001-01-02填进表格中

练习2：将姓高的同学的年龄修改为23岁

练习3：在emp1中，将职工ALLEN提升为经理，工资增加至$8000，奖金增加40%

练习4：在emp1中，对20号部门中的经理和销售人员提高15%的工资

删除表的数据

delete from 表名  where 过滤条件;

commit | rollback;

Ps: 此处若不接where语句表示删除表中所有数据

例：删除student表中学号为1的学员信息

delete from student

where stid=1;

commit;

练习1：删除student表中姓高的同学的信息

练习2：删除emp1表中工资低于2000元的所有员工的信息

--删除数据的方式：

truncate table 表名;  --截断表truncate，即清空表中所有的数据

drop table 表名；   --删除表

三者的区别：

delete from：会发起事务，可以有where过滤条件(即删除指定的数据，列名不变),属于dml语句，删除效率比truncate低。

truncate table：不会发起事务，不能有where过滤条件(即默认删除表中所有的数据，列名不变),属于ddl语句,删除效率比delete高，但有危险性。

drop table: 删除整张表及所有数据，属于ddl语句。

truncate table student;   --删除student表中所有数据，不能回滚

drop table student;  --将student整张表及数据删除掉