mysql -utesting24 -ppass99 -h172.19.200.101—登录Linux系统才有用
拷贝已经存在的字段Insert into tablename (id,name) select id,name from tablename;
DB分类：
1，关系型数据库-----面向平面 2，面向对象的数据库 3，kv数据库
UAT用户介绍测试 SIT集成测试
DBMS客户端联接的时候看的见（数据库管理系统）
Phpmyadmin ---工具
Name:testing24 Pwd: pass99
Mysql workbench---工具
Case-sensitive大小写区分 One:
SQL 查询:
SELECT ( class_id + stu_id ) *100 \"total salary\" FROM student LIMIT 0 , 30
Result
total salary 2200 2400 2400 400 2900 Two: 函数 *NVL
-NVL(stat_date,’01-JAN-95’)
-NVL(title ,’NO Title Yet’) -NVL(salary ,1000) *DISTINCT
Select distinct class_id 班级编号，stu_id 学号 from student
*big5码 台湾
*like’% s%’效率最低 *Three
SQL 查询:
SELECT * FROM `student` WHERE name LIKE '%g' LIMIT 0 , 30
Result:
name gender birthday 2010-05-15 1986-12-11 class_id stu_id 5 24 8 5 healthy good good joling liuming F W *between and *in
SQL 查询:
SELECT * FROM student WHERE birthday BETWEEN '1988-12-12' AND '1989-12-12' AND class_id IN ( 21, 22, 23, 24 ) LIMIT 0 , 30
Result:
name gender birthday 1989-04-05 1989-06-19 class_id stu_id 22 21 2 3 healthy good good
* SELECT *
join kate m w FROM student WHERE birthday
BETWEEN '1988-12-12' AND '1989-12-12' AND class_id IN (
SELECT class_id FROM student )
LIMIT 0 , 30
Result:
name gender birthday 1989-04-05 1989-06-19 class_id stu_id 22 21 2 3 healthy good good SELECT *
join kate m w SQL 查询:
FROM student WHERE birthday BETWEEN '1988-12-12' AND '1989-12-12' AND class_id IN (
SELECT class_id FROM class )
LIMIT 0 , 30
Result:
name gender birthday 1989-04-05 1989-06-19 class_id stu_id 22 21 2 3 healthy good good
自连接：
join kate m w SQL 查询: 1方法
SELECT e.name, s.salary, p.name FROM emp e, salary s, position p WHERE e.id = s.emp_id AND p.id = e.position_id AND e.position_id =1 LIMIT 0 , 30
2,方法
SELECT e.name, s.salary, p.name FROM emp e
INNER JOIN salary s ON e.id = s.emp_id INNER JOIN position p ON p.id = e.position_id AND e.position_id =1 LIMIT 0 , 30
方法3：
SELECT e.name, p.name FROM emp e
RIGHT OUTER JOIN position p ON e.position_id = p.id WHERE e.position_id =1 LIMIT 0 , 30
方法4： SQL 查询:
SELECT e.name, p.name, s.salary FROM emp e
RIGHT OUTER JOIN position p ON e.position_id = p.id RIGHT OUTER JOIN salary s ON e.id = s.emp_id WHERE e.position_id =1 LIMIT 0 , 30
Result:
name salary kongtt lium
SQL 查询:
name manager manager 1000 5000 SELECT e.name, p.name, s.salary, g.name FROM emp e
RIGHT OUTER JOIN position p ON e.position_id = p.id RIGHT OUTER JOIN salary s ON e.id = s.emp_id RIGHT OUTER JOIN gender g ON e.gender_id = g.id LIMIT 0 , 30
Result:
name xiey fanxr kongtt name manager staff manager salary name 3000 1000 1000 M W W name lium
name manager salary name 5000 W 嵌套查询： SQL 查询:
SELECT salary FROM salary WHERE emp_id = (
SELECT id FROM emp
WHERE name = 'kongtt' )
LIMIT 0 , 30
Result:
salary 1000 SQL 查询:
SELECT min( salary ) , max( salary ) , sum( salary ) , avg( salary ) , p.name FROM emp e
LEFT JOIN salary s ON s.emp_id = e.id LEFT JOIN position p ON e.position_id = p.id WHERE p.name IS NOT NULL GROUP BY p.name LIMIT 0 , 30
Result:
min(salary) max(salary) sum(salary) avg(salary) 1000 1000 EMP;
name manager staff 5000 4000 11000 5000 2750.0000 2500.0000 字段 类型 整理 属N性 ull 默认 额外 操作 字段 类型 整理 属N性 ull 默认 额外 操作 id int(8) 否 name varchar(20) varchar(100) int(8) latin1_swedish_ci latin1_swedish_ci auto_increment 否 address position_id gender_id 是 NULL 否 int(11) 否 2 Salary:
字段 类型 int(8) int(8) int(4) date 整属N理 性 ull 默认 额外 操作 id emp_id salary create_date
Position:
否 auto_increment 否 否 否
字段 id 类型 整理 属N性 ull 默认 额外 操作 int(4) 否 name varchar(20) latin1_swedish_ci auto_increment 否
单表查询
_______________________________________________________________________________ -- 创建数据表
create table student_1(
studID int not null primary key, studName varchar2(50), password varchar2(10), age int, sex char(10) );
-- 查询数据表
select * from student_1
-- 向数据表student_1中插入数据
insert into student_1 values(1,'张三','123456',20,'男') insert into student_1 values(2,'李四','123456',21,'女') insert into student_1 values(3,'王五','123456',22,'男') insert into student_1 values(4,'赵六','123456',23,'女')
-- 查询年龄为20的学员姓名
select studName from student_1 where age=20
-- 查询姓名为李四的学员信息
select * from student_1 where studName='李四'
-- 向student_1表中添加一个字段
alter table student_1 add(study varchar2(20))
-- 向student_1中添加一个日期字段 alter table student_1 add timeday date
-- 向student_1中添加日期
insert into student_1 values(6,'张三','123456',20,'男','大专',to_date('2000.1.1','yyyy.mm.dd'))
-- 过滤重复行的方式 -- 方式1
select distinct trim(studName),studID,password,age,sex,study,timeday from student_1 -- 方式2
select distinct trim(studName),studID,password,age,sex from student_1 group by trim(studName),studID,password,age,sex -- 方式3
select distinct trim(studName),studID,password,age,sex,max(rowid) from student_1 group by trim(studName),studID,password,age,sex
-- 删除重复记录
delete from emp_bak where rowid not in ( select max(rowid) from emp_bak group by empno );
++++++++++++++++++++++++++++++++++++++++ CREATE TABLE IF NOT EXISTS `vovo` ( `id` int(10) default NULL,
`name` varchar(20) default NULL, `age` varchar(20) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
id name age 5 4 4 xcxc vbvb vbvb xcxc bnbn bnbn id name age 4 4 4 2 2 2 1 1 1 1 5 5 5 4 vbvb vbvb vbvb rt rt rt df df df df xcxc xcxc xcxc vb bnbn bnbn bnbn yt yt yt gh gh gh gh xcxc xcxc xcxc vb
一种：删除重复记录Mysql
DELETE FROM bb WHERE id IN ( SELECT id FROM (
SELECT count( a.id ) , a.id id FROM bb a
GROUP BY a.id
HAVING count( a.id ) >1 )ta )
二种：
DELETE FROM bb WHERE id IN ( SELECT id FROM (
SELECT count( * ) , a.id id FROM bb a, bb b WHERE a.id = b.id GROUP BY a.id
HAVING count( * ) >1 )ta )
Result:
id name age
5 xcxc xcxc
++++++++++++++++++++++++++++++++++++++++++ -- 数字运算符(+、-、*、/) -- 1加号运算
select age+1 from student_1 -- 2减号运算
select age-1 from student_1 -- 3乘号运算
select age*2 from student_1 -- 4除号运算
select age/2 from student_1
-- 比较运算符(=、>、<、>=、<=、<>、!=) select * from student_1 where age=20 select * from student_1 where age>20 select * from student_1 where age<22 select * from student_1 where age>=22 select * from student_1 where age<=22 select * from student_1 where age<>22 select * from student_1 where age!=20 select * from student_1 where age!>22
-- 逻辑运算符(or 、and、 not)
select * from student_1 where age=21 or age=23
select * from student_1 where age=21 and studName='李四' select * from student_1 where not age=21
-- 使用BETWEEN„AND„范围(在上限和下限之间) select * from student_1 where age between 22 and 23
-- 使用IS NULL 和 IS NOT NULL 找出NULL值或非NULL 值 select * from student_1 where study is null select * from student_1 where study is not null
-- 使用like关键字查询模式匹配(或模糊查询) ,“%”号代表0个或多个字符,而”_”则表示单个字符
select studName from student_1 where studName like '张%' select studName from student_1 where studName like '张_' select studName from student_1 where studName like '%三' select studName from student_1 where studName like '_三'
-- 使用order by语句,desc是降序排列，asc是升序排列,默认为升序排列 select * from student_1 order by age desc select * from student_1 order by age asc select * from student_1 order by age
-- 使用group by语句查询
select count(studName) from student_1 group by study
-- 使用having语句
select trim(studName),count(*) from student_1 group by trim(studName) having count(*)=2; select count(*) studName from student_1 group by studName having count(*)=2;
-- 使用max函数返回列或表达式的最大值(数据最大,时间最晚) select max(age) from student_1
-- 使用min函数返回列或表达式的最小值(数据最小,时间最早) select min(age) from student_1
-- 使用avg函数返回列或表达式的平均值 select avg(age) from student_1
-- 使用sum函数返回列或表达式的总和 select sum(age) from student_1
-- 使用count函数返回非NULL行的行数
select count(age) from student_1
-- 使用VARIANCE函数返回列或表达式的方差 select variance(age) from student_1
-- 使用STDDEV函数返回列或表达式的标准偏差 select stddev(age) from student_1
-- 移除student_1表中study字段
alter table student_1 modify(study varchar2(20))
-- ROWID 用于记录数据库中记录的唯一行号 select rowid,studID,studName from student_1
-- 修改姓名为张三的学历
update student_1 set study='大专' where studName='张三'
-- 删除student_1表的张三这条记录
delete from student_1 where studName='张三'
-- 查询student_1表中有几个女生
select count(sex) from student_1 where sex='女'
-- 查询student_1表中年龄的总和 select sum(age) from student_1
-- 创建数据表 create table emps(
sid int not null primary key, deptno int not null,
deptName varchar2(20), empName varchar2(20), salary int,
job varchar2(20) )
-- 删除表
drop table emps
-- 插入数据
insert into emps values(1,1,'销售部','张三',1200,'职员'); insert into emps values(2,1,'销售部','李四',2000,'经理'); insert into emps values(3,1,'销售部','王五',3000,'总经理');
insert into emps values(4,2,'客服部','赵六',1200,'职员'); insert into emps values(5,2,'客服部','周期',2500,'经理'); insert into emps values(6,2,'客服部','李吧',4000,'总经理'); insert into emps values(7,1,'销售部','李三',1300,'职员'); insert into emps values(8,1,'销售部','周三',1400,'职员'); insert into emps values(9,2,'客服部','李六',1400,'职员'); insert into emps values(10,2,'客服部','周六',1500,'职员'); insert into emps values(11,2,'客服部','齐期',3000,'经理'); insert into emps values(12,2,'客服部','鲁期',2800,'经理'); insert into emps values(13,2,'销售部','齐九',3000,'经理'); insert into emps values(14,2,'销售部','鲁十',2800,'经理');
-- 查询表
select * from emps
-- 不包含组的查询
Select sid,salary,empname from emps Where deptno=2
-- 包含组的查询
Select avg(salary),avg(distinct salary),max(salary),min(salary),sum(salary),
count(*),count(salary),count(distinct salary),count(distinct deptname),count(deptname) from emps where deptno=2
-- 单列分组显示每个部门的平均工资和最高工资
Select deptno,avg(salary),max(salary) from emps group by deptno
-- 多列分组显示每个部门、每种岗位的平均工资和最高工资 Select deptno,job,avg(salary),max(salary) from emps group by deptno,job
-- 使用ROLLUP和CUBE限定词生成报表
-- 显示每个部门、每种岗位的平均工资和最高工资及其横向统计结果
Select deptno,job,avg(salary),max(salary) from emps group by rollup(deptno,job)
-- 显示每个部门、每种岗位的平均工资和最高工资及其纵向统计结果
Select deptno,job,avg(salary),max(salary) from emps group by cube(deptno,job)
-- 查询所有职员的平均工资值为1333.33333333333 select avg(salary) from emps where job='职员'
-- 查询所有职员工资的总和8000
select sum(salary) from emps where job='职员'
-- 查询所有经理的工资16100
select sum(salary) from emps where job='经理'
-- 查询经理的平均工资值为2683.33333333333 select avg(salary) from emps where job='经理'
-- 查询部门编号1的经理的平均工资2000
select avg(salary) from emps where job='经理' and deptno=1
-- 查询部门编号1的经理的平均工资2820
select avg(salary) from emps where job='经理' and deptno=2
-- 查询部门编号1的职员的平均工资1300
select avg(salary) from emps where job='职员' and deptno=1
-- 查询部门编号2的职员的平均工资1366.66666666667 select avg(salary) from emps where job='职员' and deptno=2
-- 显示平均工资高于2000元的部门编号、平均工资和最高工资
select deptno,avg(salary),max(salary) from emps group by deptno having avg(salary)>2000
-- MOD(m,n) 返回m 除以n 之后的余数,如果n为0,则返回m select MOD(14,5),mod(8,2.5),mod(-64,7) from dual
-- SIGN(n) 检测n的正负.如果n小于0,则返回-1;如果n 等于0,则返回0;如果n大于0,则返回1
select SIGN(-2.3),sign(0),sign(47) from dual
-- SQRT(n) 返回n的平方根,n 必须大于0 select SQRT(64) from dual
-- ABS(n) 返回n的绝对值 select ABS(-1) from dual
-- CEIL(n) 返回大于等于n的最小整数
select CEIL(9.8),ceil(-32.85),ceil(0),ceil(5) from dual
-- FLOOR(n) 返回小于等于n的最大整数
select FLOOR(9.8),floor(-32.85),floor(0),floor(5) from dual
-- ROUND(n,[m]) 执行四舍五入运算.如果省略m,则四舍五入到整数位; -- 如果m是负数,则到小数点前m位;如果m 是正数,则到小数点后m位 select ROUND(1234.5678) from dual
-- TRUNC(n,[m]) 执行截取数字.如果省略m,则将n的小数部分截取;
-- 如果m是负数,则截取到小数点前m位;如果m是正数,则截取到小数点后m位 select TRUNC(1234.5678),TRUNC(1234.5678,2),TRUNC(1234.5678,-2) from dual
-- ACOS(n) 返回n的反余弦值 select ACOS(-1) from dual
-- ASIN(n) 返回n的反正弦值 select ASIN(1) from dual
-- ATAN(n) 返回n的反正切值 select ATAN(1) from dual
-- ATAN2(n,m) 返回数字n除以m的反正切值,m不能为0 select ATAN2(2,1) from dual
-- COS(n) 返回n的余弦值
select COS(-3.14159265358979) from dual
-- COSH(n) 返回n的双曲余弦值 select COSH(1) from dual
-- SIN(n) 返回n的正弦值
select SIN(30*3.14159265359/180) \"sin(30°)\" from dual
-- SINH(n) 返回n的双曲正弦值 select SINH(1) from dual
-- TAN(n) 返回n的正切值 select TAN(1) from dual
-- TANH(n) 返回n的双曲正切值 select TANH(1) from dual
-- EXP(n) 返回e的n次幂(e=2.71828183„) select EXP(1) from dual
-- LN(n) 返回n的自然对数,n 不能为0 select LN(2.71828182845905) from dual
-- LOG(m,n) 返回以m为底的n的对数,m不能为0 select LOG(8,64),log(3,27),log(2,1024),log(2,8) from dual
-- POWER(m,n) 返回m 为底的n次幂.m和n可以为任意数字,但如果m为负数,则n必须为正数
select POWER(2,10),power(5,3),power(2,-4) from dual
-- 转换ASCII码
-- ASCII(c) 返回c的首字符在ASCII码中对应的十进制数 select ASCII('ABC') from dual
-- CHR(n) 返回十进制ASCII码n对应的字符 select CHR(65) from dual
-- 转换大小写
-- LOWER(c) 返回将c全部字符都小写的字符串 select LOWER(' i love you ') from dual;
-- UPPER(c) 返回将c全部字符串都大写的字符串 select upper(' i love you ') from dual;
-- INITCAP(c) 返回将c的每个首字符都大写,其他字符都小写的字符串. -- 单词之间以空格,控制字符和标点符号分界 select INITCAP('i love you') from dual;
-- 连接字符串
-- CONCAT(c1,c2) 返回将c2添加到c1后面而形成的字符串.如果c1是NULL,那么返回c2; -- 如果c2是NULL,那么返回c1;如果c1,c2都是NULL,那么返回NULL
select concat('ab','cd') ,concat('ab',null),concat(null,'cd'),concat(null,null) from dual; select 'ab'||'cd' from dual;
-- 字符串处理
-- LENGTH(c) 返回c的长度,包括所有的后缀空格.如果c是NULL,则返回NULL select LENGTH(' i love you ') from dual;
-- LTRIM(c1[,c2]) 去掉c1左边所包含的c2中的任何字符,
-- 当遇到不是c2中的字符时结束,然后返回剩余的字符串.c2默认为空格 select LTRIM(' i love you ') from dual;
-- RTRIM(c1 [,c2]) 去掉c1右边所包含的c2中的任何字符,
-- 当遇到不是c2中的字符时结束,然后返回剩余的字符串.c2默认为空格 select RTRIM(' i love you ') from dual;
-- TRIM([c1] c2 from c3) c1是保留字,可以取如下字符串:LEADING,TRAILING,BOTH.从c3字符串的c1处开始,删除c2字符,
-- 然后返回剩余的c3字符串.c1默认为BOTH,c2默认为空格.如果c1,c2,c3中有任何一个为NULL,则返回NULL
select TRIM(' i love you ') from dual;
-- LPAD(c1,n[,c2]) 在c1的左边填充c2,直到字符串的总长度到达n. -- c2的默认值为空格.如果c1的长度大于n,则返回c1左边的n个字符 select LPAD('abc',5,'*'),lpad('abc',2),lpad('abc',9) from dual;
-- RPAD(c1,n[,c2]) 在c1的右边填充c2,直到字符串的总长度到达n. -- c2的默认值为空格.如果c1的长度大于n,则返回c1右边的n个字符 select RPAD('abc',5,'*'),rpad('abc',2,'*'),rpad('abc',9) from dual;
-- SUBSTR(c,m[,n]) 返回c的子串,其中m是子串开始的位置,
-- n是子串的长度.如果m为0,则从c的首字符开始;如果m是负数,则从c的结尾开始 select SUBSTR('i love you',1,3) from dual;
-- INSTR(c1,c2[,n[,m]]) 在c1中从n开始搜索c2第m次出现的位置,并返回该位置数字. -- 如果n是负数,则搜索从右向左进行,但位置数字仍然从左向右计算.n和m默认都是1 select INSTR('mississippi','i',3,3) s1,INSTR('mississippi','i',1,3) s2,INSTR('mississippi','i',-2,3) s3 from dual;
-- REPLACE(c1,c2 [,c3]) 把c1中出现的c2都替换成c3,然后返回剩余的字符串.c3默认为NULL. -- 如果c3为NULL,那么所有出现c2的字符都被删除;如果c2为NULL,则将返回c1;如果c1为NULL,则将返回NULL
select REPLACE('i love you','i','I') from dual;
-- TRANSLATE(c1,c2,c3) 把所有在c2中出现的字符,逐一用对应在c3中出现的字符代替,然后返回被替代之后的c1字符串.
-- 如果c1,c2,c3中有NULL,则返回NULL;如果c3中字符少于c2,则将在c2中不匹配的字符从c1中删除;如果c2中的字符少于c3,则以c2为主。
select TRANSLATE('i love you','i','I') s1,translate('abcdefgabcd','abc','AB') s2, translate('abcdefgabcd','ab','ABC'),translate('abcdefgabcd','ab','') s4 from dual;
-- 如何计算明天的值 select sysdate+1 from dual
-- 加日期
select ADD_MONTHS(sysdate,3) from dual;
-- 取最后一天
select LAST_DAY(sysdate) from dual;
-- 取星期几
select NEXT_DAY(sysdate,'星期一'),next_day(sysdate,2) from dual;
-- 取日期年月日
select EXTRACT(year from sysdate) year,extract(month from sysdate) month,extract(day from sysdate) from dual;
-- 日期差 select MONTHS_BETWEEN(sysdate,'31-8
-98'),MONTHS_BETWEEN(sysdate,to_date('2000.1.1','yyyy.mm.dd')) from dual;
月
-- 日期四舍五入
select round(to_date('04-07-01','yy-mm-dd'),'year') y1, round(to_date('04-06-30','yy-mm-dd'),'year') y2, round(to_date('04-07-15','yy-mm-dd'),'month') m1,
round(to_date('04-07-16','yy-mm-dd'),'month') m2 from dual;
-- 日期截除
select sysdate,trunc(sysdate,'year') y,trunc(sysdate,'month'),trunc(sysdate,'day') d,
trunc(to_date('2000.11.9','yyyy.mm.dd'),'day'),trunc(to_date('2000.11.4','yyyy.mm.dd'),'day') from dual
-- 服务器（会话）时间、时区、时间戳 select sysdate, -- 服务器时间 current_date, -- 会话时间 systimestamp, -- 服务器时间戳 current_timestamp, -- 会话时间戳
localtimestamp, -- 会话时间戳不带时区 sessiontimezone, -- 会话时区 dbtimezone oracle -- 默认时区 from dual;
-- 时区偏差计算 select tz_offset(dbtimezone) chicago,tz_offset('US/Eastern') newyork,tz_offset('Europe/London') london, tz_offset('Asia/singapore') singapore from dual;
-- 转换格林威治时间
select SYS_EXTRACT_UTC(current_timestamp) from dual;
-- 我们如果当前时间加一分钟怎么办
select sysdate,sysdate+numtodsinterval(2,'hour'),sysdate+numtodsinterval(30,'minute') from dual;
select sysdate,sysdate+numtoyminterval(2,'year'),sysdate+numtoyminterval(6,'month') from dual;
select sysdate,sysdate+to_dsinterval('007 12:00:00') \"+7.5 days\00:00:00') \"+30 days\" from dual;
select sysdate,sysdate+TO_YMINTERVAL('01-02') months\
-- 如何用add_months函数实现？ -- - 也可以直接用，不用函数转换
select sysdate + interval '007 12:30:06' DAY TO SECOND from dual; select sysdate + interval '01-02' year to month from dual
-- 单行转换函数例子
select asciistr('abc*中*国*de fg?') from dual; select bin_to_num(1,1,0,1) from dual;
select cast(sysdate as varchar2(24)),cast('1' as number) from dual; select to_timestamp_tz('2003-04-5','yyyy-mm-dd') from dual;
-- -- 日期to_char的使用
select to_char(sysdate,'yyyy-mm-dd***hh-mi-ss*(*)') from dual; select to_char(sysdate,'AD') from dual; select to_char(sysdate,'AM') from dual; select to_char(sysdate,'DY') from dual; select to_char(sysdate,'DAY') from dual; select to_char(sysdate,'D') from dual; select to_char(sysdate,'DD') from dual; select to_char(sysdate,'DDD') from dual; select to_char(sysdate,'W') from dual; select to_char(sysdate,'WW') from dual; select to_char(sysdate,'MM') from dual; select to_char(sysdate,'MON') from dual; select to_char(sysdate,'MONTH') from dual; select to_char(sysdate,'YYYY') from dual; select to_char(sysdate,'YYY,YY,Y') from dual; select to_char(sysdate,'Y,YYY') from dual; select to_char(sysdate,'YEAR') from dual; select to_char(sysdate,'HH12') from dual; select to_char(sysdate,'HH24') from dual; select to_char(sysdate,'MI') from dual; select to_char(sysdate,'SS') from dual;
-- 是否可以用extract函数？两者有什么区别？ select sysdate+extract(year from sysdate) from dual;
select sysdate+cast(to_char(sysdate,'YYYY') as number) from dual; select to_char(sysdate,'SSSS') from dual;
select to_char(systimestamp,'TZD') from dual; select to_char(systimestamp,'TZH') from dual; select to_char(systimestamp,'TZM') from dual;
\"+14
select to_char(systimestamp,'TZR') from dual;
-- 单行转换函数例子 -- 数字to_char的使用
select to_char(123.456,'9.99EEEE'),TO_CHAR(123.456,'9999.90') from dual; select to_char(123.4,'9999.99') from dual; select to_char(-123.4,'9999.99') from dual; select to_char(0.3,'9999.9') from dual; select to_char(12.3,'0009.90') from dual; select to_char(1234.5,'9,999.9') from dual; select to_char(1234.5,'9G999D9') from dual; select to_char(1234.5,'9,999.9') from dual; select to_char(1234.5,'9G999D9') from dual; select to_char(123,'$999') from dual; select to_char(123,'L999') from dual; select to_char(0.1,'FM99.99') from dual; select to_char(120000,'9.9EEEE') from dual; select to_char(123,'999MI') from dual; select to_char(123,'999PR') from dual; select to_char(123,'S999') from dual; select to_char(123,'RN') from dual; select to_char(123,'rn') from dual; select to_char(22,'XX') from dual;
-- to_date函数符号的格式与to_char相同
select TO_DATE('04-5月-1957 13:14:15','DD-mon-yy hh24:mi:ss') from dual;
-- to_number函数中的格式与to_char相同 select TO_NUMBER('1234.56') from dual;
-- ?与cast函数的比较
select cast('1234.56' as number) from dual;
-- 返回字符串c对应的UNICODE字符
select unistr('\\00D6'),unistr('ca\\00D6on') from dual;
-- 任何数＋null都为null select 1+null from dual;
-- 计算emp的工资加补助
select empname,salary,comm,salary+comm from emp where deptno=2 order by empname;
-- 用nvl函数处理 nvl(expr1, expr2)，expr1为NULL，返回expr2；不为NULL，返回expr1。
注意两者的类型要一致
select empname,salary,comm,salary+nvl(comm,0) from emp where deptno=2 order by empname;
-- 用nvl2函数处理 nvl2(expr1, expr2, expr3) expr1不为NULL，返回expr2；为NULL，返回expr3。expr2和expr3类型不同的话，expr3会转换为expr2的类型。
select empname,salary,comm,nvl2(comm,salary+comm,salary) from emp where deptno=2 order by empname;
-- nullif(expr1, expr2) 相等返回NULL，不等返回expr1 select nullif(1, 1) from dual;
-- 创建成绩表 create table marks(
mid int not null primary key, studid int not null, writeEax int, maxEax int )
-- 添加测试数据
insert into marks values(1,2,85,67) insert into marks values(2,3,80,70) insert into marks values(3,4,79,89) insert into marks values(4,5,78,80) insert into marks values(5,6,83,78)
-- 当从两个或两个以上的表中选择数据，并且在where子句中没有指定连接条件时就是笛卡儿连接
Select s.studid,s.studname,s.age,s.sex,m.writeeax,m.maxeax from student_1 s,marks m
-- 相等连接,从笛卡儿集合中过滤下相等条件的集合
Select s.studid,s.studname,s.age,s.sex,m.writeeax,m.maxeax from student_1 s,marks m where s.studid=m.studid
-- 不等连接,从笛卡儿集合中过滤掉相等条件的集合
Select s.studid,s.studname,s.age,s.sex,m.writeeax,m.maxeax from student_1 s,marks m where s.studid!=m.studid
-- 自我连接,将一个表复制一个结果集进行连接
Select e.deptno,e.deptname ,m.deptname 销售部 from emps e,emps m Where m.deptno=e.deptno
-- 使用union合并查询，获取结果集的并集，并自动去除重复行，以第一列的结果进行排
序
Select deptno,deptname,empname,salary,job from emps Where deptno=2 Union
Select deptno,deptname,empname,salary,job from emps Where job='经理';
-- 使用union all合并查询，获取结果集的并集，不去除重复行，不排序 Select deptno,deptname,empname,salary from emps Where deptno=2 Union all
Select deptno,deptname,empname,salary from emps Where job='经理';
-- 使用intersect，获取结果集的交集，只会显示同时存在于结果集合中的数据，并且以第一列的结果进行排序
Select deptno,deptname,empname,salary from emps Where deptno=2 intersect
Select deptno,deptname,empname,salary from emps Where job='经理'
-- 使用minus，获取结果集的差集，只会显示在第一个结果集中存在， -- 但在第二个结果集中不存在的数据，并且以第一列的结果进行排序 Select deptno,deptname,empname,salary from emps Where deptno=2 minus
Select deptno,deptname,empname,salary from emps Where job='经理'
-- 单行子查询,显示工资最高的雇员信息
Select empname,deptno,salary from emps Where salary=(select max(salary) from emps);
-- 多行子查询,显示与部门编号为2的岗位相同的雇员信息 Select empname,deptno,salary,job from emps
Where job in (select distinct job from emps where deptno=2);
-- 显示不与部门编号为20的岗位相同的雇员信息 Select empname,deptno,salary,job from emps
Where job not in (select distinct job from emps where deptno=2)
-- 显示高于部门编号为2的所有雇员的工资的雇员信息 select empname,deptno,salary,job from emps
where salary>all(select salary from emps where deptno=2);
-- 显示高于部门编号为2的任何雇员的工资的雇员信息 select empname,deptno,salary,job from emps
where salary>any(select salary from emps where deptno=2);
-- 显示每个部门的最高工资的雇员信息
select deptno,(select max(salary) from emps b where b.deptno=a.deptno) maxsal from emps a order by deptno;
-- 增加distinct
select distinct deptno,(select max(salary) from emps b where b.deptno=a.deptno) maxsal from emps a order by deptno;
-- 内连接查询
select s.studname, s.age,s.sex,m.writeeax,m.maxeax from student_1 s inner join marks m on s.studid = m.studid
-- 左外连接查询
select s.studname, s.age,s.sex,m.writeeax,m.maxeax from student_1 s left join marks m on s.studid = m.studid
-- 右外连接查询
select s.studname, s.age,s.sex,m.writeeax,m.maxeax from student_1 s right join marks m on s.studid = m.studid
-- 完全外连接查询
select s.studname, s.age,s.sex,m.writeeax,m.maxeax from student_1 s full join marks m on s.studid = m.studid
-- 自然连接
select e.empname, p.empname from emps e inner join emps p on e.empname=p.empname
-- 设置只读事务 -- 事务四大特性： 原子性、一致性、隔离性、持久性 -- 事务设置三种状态 set transaction isolation level read committed set transaction read only set transaction read write -- 事务点、事务提交和回滚 savepoint sp1 -- 事务点 commit -- 事务提交 rollback -- 事务回滚
因篇幅问题不能全部显示，请点此查看更多更全内容