mysql数据库随笔


number(p,s):数值型,包括小数点前后的位数
Integer:整数
vachar2:字符串
nvachar2:国际化使用字符串
char:
data:日期
timestamp:时间戳
BLOB:放大数据

事物:一组数据库操作,只能全部成功,或全部不成功

关系模型:数据存储用表,数据运算也是基于表

关系数据库:使用关系模型
表里应该有主键,并且其他字段和主键有联系
订单表:订单号 金额 日期 客户主键
客户表: id, 联系方式,名称
订单商品表:订单号 商品 数量

sql语句 数据库操作语言
标准Sql:所有数据都支持的sql
pl/sql:oracle特定的sql
TSQL
select*from
DL.ADMIN t
select 查询
from 表示所要查询的表
t 标的别名
where:设置查询条件

* 查询表下的所有字段
select t.*, t.rowid from
DL.ADMIN t where

select t.*, t.rowid from DL.客户表 t where t.id=123
表示查找客户id 是123的客户信息
from DL.客户表 t where t.tele is not null
表示查找电话不为空的客户

权限表;权限角色关系

|| //连接字符串

比较运算符:=,>,<,<=,>=,<>

like:模糊比较,%表示匹配任何字符
select t.name from DL.customer t where t.name like 'c%' //以c开头的字符串

in: //表示包含
select t.name from DL.customer t where t.name in('a','b')//有a或 b名字就可查出来

is null,is not null;

关系运算符:
and:表示与运算 or: 表示或运算
select * from DL.订单表 t where t.cust_id=1 and t.amount>100 //两个条件同时满足的查询

选择运算符(关键字)
rownum:查询记录的范围
select t.* form DL.ADMIN t where rownum>=1 and rownum<=10 //查询前十条记录

distinct:去除重复记录
select distinct t.password form DL.ADMIN t //查出来后只显示重复记录的一条

别名:能改列名

条件判断: case when..(条件).then.(返回值)..end
select t.name, case when t.account<=100 then '数量不冲突' when t.account>100 then 'from数量充足' end isEnough from DL.GOOD t

排序: order by (加desc 从大到小)(不加desc从小到大)
select * from DL.Good t order by t.price,t.account desc //先按价格排序,再按数量排序

关联查询:
select t2.name type(重新给的列名),
t1.name from DL.DOOD t1,
DL.GOODTYPE t2 where t1.type_id=t2.id

内连接:关联的两张表只有满足关联条件
(表1)inner (表2)join on(条件)
select t2.name type(重新给的列名),
t1.name from DL.GOOD t1 inner join
DL.GOOD_TYPE t2 on t1.type_id=t2.id (内连接和关联查询效果一样)

左外链接:左边的表记录可以全部查出来,右边的只有满足条件的才能差出来,如果两表关联不上,左边可以出来,右边字段字段置为空
select t1.name type,
t2.name from DL.GOOD_TYPE t1 left join
DL.GOOD t2 on t2.type_id=t1.id

右外连接
select t1.name type,
t2.name from DL.GOOD_TYPE t1 right join
DL.GOOD t2 on t2.type_id=t1.id

全外连接 full join

substr: 字符串截取 参数 1要截取的字符串 2从第几个字符开始截取 3截取几个字符
dual: 虚表 只计算表达式 不查找
select substr('hello word',2,4) from dual

trim:过滤字符串两端的空格 ltrim :过滤左边的空格 rtrim:过滤右边的空格
select trim(' test ') from dual

length:求字符串长度
select length('test') from dual

replace: 字符串替换 参数 1 要替换的字符串 2 被替换的字符 3 替换后的字符
select replace('abc','b','d') from dual

转换函数: to_char:转换字符串类型
参数1 系统时间 2 b把系统时间转换为固定格式
select to_char(sysdate,'yyyy-mm-dd hh:mi:ss') from dual

to_date:字符串转换时间
select to_date('2016-09-07','yyyy-mm-dd') from dual

其他
nvl:转换空值
select t.name,nvl(t.realname,'未知') from DL.ADMIN t

汇总和分组汇总

1 统计个数: count()
select count(t.name) from DL.ADMIN t where t.realname is not null//统计用户名不为空的个数

2 统计和:sum
select sum(t.account) from DL.GOOD t where t.type=1//统计类型为1 的手机数量

3最大值:max
select max(t.price) from DL.GOOD t//最高价格

4 最小值:min

5 平均数:avg
select avg(t.account) from DL.GOOD t //统计每个商品类型数量的平均数

6 分组汇总:group by 分总的字段
select t.type_id count(t.id) from DL.GOOD t group by t.type_id

select t.type_id,sum(t.account) from DL.GOOD t group by t.type_id

select t1.id, t1.name, count(t.id) from
DL.GOOD t,DL.GOOD_TYPE t1 where t.type_id= t1.id
group by t1.id,t1.name

7 对汇总的结果进行排序
select t.goods_id , sum(t.amount)
from DL.订单商品表 t
group by t.good_id
order by t.good_id //先统计商品购买数量 然后在根据 id 排序显示出来

8 对汇总结果筛选:having
select t.goods_id , sum(t.amount)
from DL.订单商品表 t
group by t.good_id
having sum(t.amount)>1000 //筛选出每种商品购买数量总和大于1000的

9 子查询:

from里面使用
select t1.name ,t.name
from(select * from DL.GOOD where price >100) t,
DL.GOOD_TYPE t1, where t.type_id=t1.id ///查询出价格大于100的商品名字和类型

where 里使用
select * from DL.ROLE t
where t.naem in
(select role_name from DL.REL_ROLE_AUTH where auth_id=1)//显示出有权限1的角色的信息

exists: 判断子查询结果集是否有记录
select * from
DL.ROLE t where exists
(select t1.role_name from DL.REL_ROLE_AUTH t1 where t1.role_name= t.name and t1.auth_id=1)

select 里使用
select t.name,(select max(t1.price)from DL.GOOD t1 where t1.type_id=t.id)
from DL.GOOD_TYPE t ///显示出商品类型中最贵的商品价格 给商品类型中显示出最贵的商品价格

10 分页:
select t.* from (select t.*,rownum r from (select * from DL.GOOD t order by t.id) t) where r>=2 and r<=3

11 新增记录

insert into(表名)(列) values (值)
insert into DL.GOOD(id, NAME ,TYPE_ID,PRICE, AMOUNT) values(5,'键盘',0,50,40)

//把select查出来的结果写入一张类型一样的表里面
insert into DL.GOOD1(id, NAME ,TYPE_ID,PRICE, AMOUNT) seclet * from DL.GOOD

修改记录:update(表名)set(设置值 列如:t.price=20) where(条件)
update DL.GOOD set price=60,amount=40 where id=3//同时可修改多个字段
update DL.GOOD set amount=40 where amount>100// 把库存大于100 的商品数量都改为40

删除:1 delete from(表名)where(条件)
delete from DL.GOOD where id= 4 //商品id号是4 的商品都删掉
delete from DL.GOOD //好像全删了

2 truncate: truncate table (表名) 删除速度快点吧


创建表:create table 表名(字段 字段类型)
create table DL.ADMIN
(
name VARCHAR2(16) not null,
password VARCHAR2(32) not null,
username VARCHAR2(8),
tlel VARCHAR2(16)
)

添加主键:
alter table DL.ADMIN //alter table 修改表 表名
add constraint PK_PRACTICE_ADMIN //主键的名称
primary key (字段) //设置主键字段

放在一起进行操作
create table DL.EMP
(
name VARCHAR2(16) not null,
tlel VARCHAR2(16),
job VARCHAR2(32),
constraint PK_DL_EMP
primary key (name)
)

删除主键: alter table (表名)drop constraint(主键名)

删除表:drop table(表名)//把表格完全删除


建表:
1 表中的字段不可分割
2 表中要有主键,并且其他字段和主键是有联系
3 表中的字段和主键是有直接联系的,避免数据的冗余


添加列:
alter table dl.emp add
(salary number(8,2),performance number(8,2))

修改列:
1 alter table (表名) modify(列名 类型)
alter table DL.EMP modify (tele number)

重命名列名:
2 alter table (表名) rname column (列名) to(新列名)
alter table DL.EMP rname column tele to phone

删除列:
alter table (表名) drop column (列名)

修改表名
alter table (表名) rname to (新表名)

约束:对标记录内容的限制
非空:alter table(表名) modify (列名)not null
唯一:alter table(表名)add constraint(约束名)(列名)
举例: alter table DL.EMP add constraint UK_DL_EMP_PHONE unique (phone)

外键:
alter table(表名) add constraint (约束名) foreign key(列名) references 表名(列名)
列子:

check:
alter table (表名) add constraint(约束名) check (约束条件)
列子 :alter table (表名) add constraint CK_DL_EMP_AGE check (age>=18 and age <=60)

删除约束:
alter table (表名) drop constraint (约束名)

序列:数值型 ,自增对象 可以在sql 语句里面使用,经常在主键中使用
create sequence (序列名称)increment by//每次自增的数值 (自增的数值)
start with// 从哪个数值开始(开始数值)maxvalues (到最大值) minvalues 最小值
cycle //循环 nocycle //不循环

例子:create sequence SEQ_DL_GOOD increment by 1 start with 1 maxvalue 99999999 minvalue 1 nocycle
获取序列下一个值
SEQ_DL_GOOD.nextval //例子:select SEQ_DL_GOOD.nextval 1 from dual
获取序列当前值
SEQ_DL_GOOD.currval
根据主键 id 对应序列 往表里加东西
insert into DL.GOOD(id,NAME ,type_id,price,数量) values (SEQ_DL_GOOD.nextval,'耳机',0, 80, 50)

删除序列;drop sequence (序列名)

修改序列:alter sequence(序列名)(序列字段)(值)

视图:是定义一个查询语句,可以通过视图语句直接调用查询语句
crate or replace view (视图名称)as (查询语句)
create or replace view V_订单表_数量(no,amount)

删除视图:drop view(视图名称)

存储过程:已编程的方式(一段程序)操作数据库
create or replace procedure (名称)(
参数 in 参数类型,。。。。//添加参数类型时 要用in)is
变量 类型;....
begin
。。。。。(程序体)
end ;
例子1://含参构造语句
create or replace procedure proc_helloworld
(para in varchar) is
v_var varchar2(16);
begin
v_var := 'world';
dbms_output.put_line
(para || '' || v_var);
end;
例子2://无参构造语句
create or replace procedure proc_printtest
is
v_price number;
begin
select max(price) into v_price from good;
dbms_output.put_line(v_price);
end;
执行存储过程

call (名称)(参数)

语句块:
declare(定义变量)
begin (程序体)

declare(定义变量)
begin (程序体)
end; // 嵌套语句块
end;

类型:
(表字段)%type:字段类型
列子: v_emp_id.id%type select id into v_emp_id from emp where phone ='333345'

(表名)%rowtype: v_emp emp%rowtype; v_emp.id; v_emp.name;

变量赋值;
:= ,select (字段) into (变量)。。。

流程控制:
条件选择:
case when(条件) then (返回值)。。。else。。。end;// 可以有多个else语句
select * into v_emp from emp where id= 1;
v_var := case when v_emp.salary<1500
then '工资小于1500'
else
'工资大于1500'
end;
dbms_output.put_line(v_var);

IF(条件)then (语句) elsif(条件) then(语句) else(语句) end if;
if v_emp.salary<1500 then
update emp set salary = 1500 where id=1;
end if;
commit;

循环:
loop(循环体) exit when(退出循环的条件) end loop;
例子: v_i:=1;
loop
insert into role(name,comments) values
('role' || v_i, null);// ||表示连接字符的
v_i:= v_i+1;
exit when v_i= 5;
end loop;
commit;

while(条件) loop(循环体)end loop;

游标:用来获取sql语句结果记录
定义:
cursor(游标名称) is (查询语句) 列子: cursor c_emp is select * from emp;
打开;
open(游标名称);
提取:提取一条记录
fetch(游标名称) into (变量1,变量2.。。)
fetch(游标名称) into (rowtype 变量)
循环提取条件:
(游标名称)%notfound
关闭:
close(游标名称)
例子;
open c_emp;
loop
fetch c_emp into v_emp;

隐式游标:没有定义,打开和关闭,常用于增删改操作。
sql%notfound:判断修改语句没有修改记录。
sql%found:判断修改语句有修改记录。
sql%rowfound:返回修改语句修改的行数。

异常处理:
declare
begin
(程序体)
exception
when(异常名称)then (处理程序);
when other then (处理程序);
end;
sql异常:
1 no_date_found 2 self_is_null;

自定义函数:
create or replace function(函数名)(参数)//有参数的写在后面 return(数据类型) is
定义变量(语句块)
begin
(函数程序)
return(变量);
end;
例子;
create or replace function to_date8(p_d in date) return varchar2 is
v_str varchar2(8);
begin
v_str := to_char (p_d,'yyyymmdd');
return v_str;
end;

触发器:在一定条件下自动执行的程序,条件值,对表的增删改
create or replace trigger(名称)(before|after)(insert or update or delete) on (表名)
(for each row|for each statement)//
begin
触发器要执行的程序
end;

事务:一组数据库操作,要么一起成功,要么一起失败。
原子性,一致性,隔离性,持久性

commit:
rollback:
when other then rollback;//恢复到开始的样子
end;


mysql-:
数据库:每一个数据库都有一套独立的对象,比如表 视图。。。
表; 按数据库区分
用户;独立于数据库,属于服务器
超级用户:root

创建用户:
create user 'practice'@'127.0.0.1' // 'practice'@'%'
identified by '123456';

授权;
grant all on practice.* to 'practice '@'127.0.0.1'; flush privileges

修改密码;
set password for 'root'@'localhost'password'1234567';

删除用户:drop user 'practice'@'127.0.0.1';

撤销权限: revoke privilege on practice .* from 'practice'@'127.0.0.1';

数据类型:
数值型;
整数:tinyint ,smallint, int
浮点数: float double
decimal: decimal(m,d)//参数 1 小数点前面的最大位数, 2 小数点后面的最大位数
时间:
date: 日期
time; 时间
datetime:日期和时间
字符串:
char:
varchar:变长字符串
text:大文本

常用函数:
数字函数:
round(x,y):四舍五入函数//参数 1 数字 2 保留几位小数
round(3.1415926,2)

聚合函数:
count sum max min avg

字符串函数:
substring,left,right :字符串截取
substring(str,p,1) left(str,1)
时间函数;
now():获取系统时间
类型转换: date-format(now(),'%y-%m-%d');日期转换字符串

str_to_date('2016-09-08','%y-%m-%d');字符串转换日期

其他;
ifnull(p1,p2);空值转换
分页:
limit 1,10:获取第一个到第十个的记录
select * from users limit 1,10;

原文地址:https://www.cnblogs.com/sunny-miss/p/14984143.html