MySQL数据库总结

一、建表

create table table_name(
id number(7) primary key,
name varchar2(20),
age number(7));
commit;

varchar2\varchar\char区别

char类型：浪费内存，但检索效率高

varchar/varchar2：节省内存，但检索效率低

varchar和varchar2的区别

varchar2把所有字符都占两字节处理(一般情况下)，varchar只对汉字和全角等字符占两字节，数字，英文字符等都是一个字节；

varchar2把空串等同于null处理，而varchar仍按照空串处理；

varchar2字符要用几个字节存储，要看数据库使用的字符集，

大部分情况下建议使用varchar2类型，可以保证更好的兼容性。

案例

实体：学生

表名：s_student

属性：id,name,age,gender,dob,phone

要求名字默认为"新用户"，性别默认为男性，年龄默认为20，

生日默认为2000年1月1日

create table s_student(
id number(7)
name varchar2(20) default '新用户'
age number(7) default 20
gender varcher2(20) default '男'
dob date default ('2000-01-01','yyyy-mm-dd')
phone char (11)
);

二、删除表

drop table table_name;

三、表数据的增删改查

增：insert  

insert into table(id,last_name)

values (999,'kangkang');

commit;

删：delete 

delete from table_name
where dept_id  = 41;

改：update    

update table_name set name = "hang"

where id in(1,2,3);

查：select

select ***  

from table_name；

四、八大关键字

select 字段1，字段2，...
from 表名
where 单值限定条件
group by 分组
having 多值限定条件
order by 排序方式 （id ASC/DESC）

范围判断（> >= < <=）

案例1：查询id为1的员工last_name、salary？

select last_name,salary
from table
where id=1;

案例2：查询所有不拿工资的员工id、last_name、salary？

select id,last_name,salary
from table
where salary is null;（对于空值的等值和不等值判断不能用等号，而要用"is null"和"is not null"）

案例3：查询所有工资高于1200元的员工id、salary？按照工资的降序以及id的升序排列？

select id,salary
from table
where salary >= 1200
order by salary desc,id asc;

案例4：查询所有工资不低于1200元的员工id、salary？

select id,salary
from table
where salary >=1200;

条件并列（and—— 与并列，使用and并列的所有条件必须同时满足才会被查询出来；or—— 或并列，使用or并列的所有条件只需满足其中之一就会被查询出来）

案例1：查询41号部门中工资高于1400元的员工id、salary？

select id,salary
from table
where dept_id = 41 and salary > 1400;

案例2：查询id为1、3、5的员工last_name、salary？

select last_name,salary
from table
where id in(1,3,5);

案例3：查询41、42两个部门的全部员工id、salary？

select id,salary
from table
where dept_id in(41,42);

案例4：查询出41或42部门中工资高于1400元的员工id、salary？

select id,salary
from table
where (dept_id =41 or dept_id =42 ) and salary > 1400;

案例5：找出部门41中所有经理、部门43中所有办事员，以及既不是经理又不是办事员但其薪金>=2000的所有雇员的详细资料(职称：Title 经理：Warehouse Manager 办事员：Stock Clerk)

select *
from table
where (dept_id = 41 and title ='Warehouse Manager') or (dept_id=43 and title ='Stock Clerk' ) or (title !='Warehouse Manager' and title !='Stock Clerk' and salary >=2000);

逻辑比较符（between 小 and 大；in(1,2,3)）

案例1：查询工资在1000-1500之间的员工id,salary？

select id,salary
from table
where salary between 1000 and 1500;

案例2：查询工资不在1000-1500之间的员工id,salary？

select id,salary
from table
where salary not between 1000 and 1500;

案例3：查询id为1、3、5、7、9的五位员工last_name、salary？

select last_name,salary
from table
where id in(1,3,5,7,9);

案例4：查询除了41、42部门之外的其他部门的员工id、last_name？

select id,last_name
from table
where dept_id not in(41,42);

模糊匹配（like '_占位符，%通配符'）

案例1：查询last_name中包含's'的员工信息？

select *
from table
where last_name like '%s%';

案例2：查询last_name中不包含's'以及'S'的员工信息？

select *
from table
where last_name not like '%s%' and last_name not like '%S%';

案例3：查询last_name中第二个字母是'g'的员工信息？

select *
from table
where last_name like '_g%';

案例4：查询last_name以'o'结尾的员工id、last_name？

select id,last_name
from table
where last_name like '%o';

案例5：向表中插入数据

insert into table(id,name)
values (999,'_briup');
commit;

在模糊匹配的过程中，某些具有特殊含义的符号，需要进行转义。

转义的步骤：

1）给被转义的字符前面加上一个标识字符

标识字符可以是任意字符。

where last_name like 'a_%';

2）使用escape短语指定哪个字符作为标识字符

where last_name like 'a_%' escape 'a';

案例：查询所有last_name以"_"开头的员工信息？

select *
from table
where last_name like 'a_%' escape 'a';

五、单值函数

处理字符串的单值函数

1、lower（全部小写显示）

select lower(name)
from table;

2、upper （全部大写显示）

select upper(name)
from table;

3、initcap(首字母大写)

select initcap(name)
from table

4、concat (字符串1,字符串2)（字符串的拼接）

5、substr(字符串,起始位置,截取长度)（从某个字符串中截取子字符串）

案例：查询所有员工last_name的第3到5个字符？

select substr(last_name,3,3)
from table;

6、length(字符串)（求字符串的长度）

案例1：查询所有员工的last_name长度？

select length(last_name)
from table;

案例2：查询所有last_name长度为6的员工信息？

select *
from table
where length (last_name) = 6;

7、nvl(可能出现空值的字段,默认值) （空值处理）

处理数值类型的单值函数

round(number1,number2)（四舍五入）

trunc(number1,number2) （只舍不取）

mod(number1,number) （取余运算

mod(number1,number) （取余运算）

处理日期格式的单值函数

a、months_between(sysdate,start_date) （计算两个日期之间相隔的月份）

案例1：查询所有入职超过10年的员工信息？

select *
from table
where months_between(sysdate,start_date) >120;

b、add_months(start_date,所加月份) （计算某个日期加上n个月之后的日期）

案例1：假设每个员工签合同40年，那么请计算每个员工的合同到期时间？

select add_months(start_date,480)
from table;

c、next_day(sysdate,1) （计算某个日期的下一个星期几是几号）

案例1：计算当前时间的下一个星期日是几号？

select next_day(sysdate,1)
from table;

d、last_day(start_date) （计算某个日期所在月的最后一天是几月几号）

案例1：查找所有在某个月最后一天入职的员工信息？

select *
from table
where start_date =last_day(start_date);

e、round(日期,'year') （日期四舍五入）

转换函数

1、tochar(数字/日期,'fmt') （把数字或日期转换为一个字符串）

fmt格式中的特殊字符：

9 指定数值长度，如果长度超出，不会强制补零

0 指定数值长度，长度超出会强制补零

$ 在数字前面加上美元符号

L 使用当前所在地区的货币符号

. 小数点

, 用逗号进行分隔

❤ 查询所有的员工id、salary？要求按照美元格式输出工资？前面有$符号，每3位一个逗号，小数点后保留两位

select tochar(salary,'L9,999.00'),id

from table;

2、to_date(日期,'fmt') （把日期转换成字符串）

fmt格式

YYYY YY制四位数的年份数字

MM 阿拉伯数字两位数的月份

D 在一周中的第几天

DD 在一个月中的第几天（俗称几号）

DDD 在一年中的第几天

YEAR 返回年份的拼写格式

MONTH 返回月份的全称

ddsp sp=spell 返回日期的拼写形式

ddspth 基数词变序数词

DAY 返回星期的全称

DY DAY的简称（星期名称的前三个字母）

HH 小时

HH24 24小时制的小时数

MI 分钟

SS 秒钟

AM/PM 输出上午还是下午（写哪个都可以，结果一样）

❤ 查询当前时间，以下面的格式进行输出：2000-01-01 10:20:30 AM

select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS AM')

from dual;

3、to_number （把字符串转换成数字）

4、to_date(字符串,'fmt') （把字符串转换成日期）

❤把"2019-01-01 13:24:52"转换成日期类型。

select to_date('2019-01-01 13:24:52','YYYY-MM-DD HH24:MI:SS')

from table;

                 5、rownum 显示查询结果的行号

select id,salary,rownum

from s_emp;

四、多表查询

select 别名.字段1,别名.字段2,.....

from 表1 别名1,表2 别名2;

案例

查询所有学生的姓名以及考试成绩？

学生表：            学生成绩表：

ID NAME           ID SCORE

1 Tom                 1 90

2 Jack                  2 85

3 Lucy                 3 65

select s1.name,s2.score

from 学生表 s1,学生成绩表 s2;

1、等值与不等值连接

❤ 查询所有的员工id以及对应的部门名称？

select s1.id,s2.name

from emp s1,dept s2

where s.dept_id = s2.id;

2、范围判断（>,>=,<,<=,between,in）

❤ 查询所有员工的工资以及工资所处的等级名称？emp,gender

select e.salary,g.name

from emp e,gender g

where e.salary between g.minSal and g.maxSal;

五、多值函数（group by,having）

案例

1、计算每个部门的平均工资？

select dept_id,avg(salary)

from emp

group by dept_id;

2、计算所有员工的平均工资？

select id,avg(salary)

from emp;

3、查询每个部门中工资高于1200的员工数量？

select dept_id,count(*)

from emp

where salary > 1200

group by dept_id;

4、查询每个部门中员工的最高工资？

select dept_id,max(salary)

from emp

group by dept_id;

5、查询每种不同职位薪资最高的人的salary？

select title,max(salary)

from emp

group by title;

6、查询公司三年内需要为所有员工共支付多少薪金（不计佣金，不考虑涨薪）？

select sum(salary*3*12)

from emp;

六、子嵌套（嵌套查询）

案例

1、查询和Ngao在同一部门的员工id、last_name？

select id,last_name

from emp

where dept_id = (

select dept_id

from emp

where last_name = 'Ngao');

2、查看工资大于Chang员工工资的所有员工的id和名字。

select id,last_name

from emp

where salary > (

select salary

from emp

where last_name = 'Chang');

3、查询所在区域号为2的员工的信息？【多表查询】

select e.id,e.last_name

from emp e,dept d

where e.dept_id = d.id and d.region_id=2;

4、查询工作在2号区域的部门ID？

select id

from dept

where region_id = 2;

5、查询员工的id以及对应的区域id？

select e.id as emp_id,d.region_id

from s_emp e,s_dept d

where e.dept_id = d.id;

6、查询部门平均工资高于1200的部门中的所有员工id、last_name？

select id,last_name

from emp

where dept_id in(

group by dept_id

having avg(salary)>1200);

7、查看员工工资小于平均工资的所有员工的id和名字?

select id,name

from emp

where salary < (

select avg(salary)

from emp);

8、查看部门平均工资大于32号部门平均工资的部门id?

select dept_id

from emp e

where (

select avg(salary)

from emp

where dept_id = e.dept_id)>(

select avg(salary)

from emp

where dept_id = 32);

九、创建序列

创建序列并设置属性

create sequence seq_test

increment by 2

start with 45

maxvalue 60

cycle

nocache;

十、删除序列

drop sequence seq_test;

十一、创建视图

create or replace view 视图名

as

SQL语句

案例

create or replace view v_test

as

select *

from t_user

where id > 10;

十二、查看视图内容

select * from v_test;

十三、删除视图

drop view 视图名字;

十四、创建索引

create index 索引名

on 表名/列名;

案列

create index emp_index

on s_emp(last_name);

十五、删除索引

drop index 索引名;

案列

drop index emp_index;