Sql语句内功心法

CREATE SCHEMA <模式名> AUTHORIZATION <用户名>

定义模式实际上定义了一个命名空间，在这个空间可以进一步定义该模式包含的数据库对象，例如基本表，视图，索引

DROP SCHEMA <模式名>  <CASCADE|RESTRICT>

选择了CASCADE 表示删除模式的同时把该模式下的数据库对象全部一起删除；

选择了RESTRICT表示如果在该模式下定义了数据库对象，则拒绝该删除语句的执行；

Mysql，sql语句赏析

DROP TABLE IF EXISTS dl_user;

CREATE TABLE dl_user(
id int(12) NOT NULL auto_increment,
email varchar(50) NOT NULL,
nick_name varchar(30) default NULL,
password varchar(50) NOT NULL,
user_integral int(12) not null default '',
is_email_verify char(3),
email_verify_code varchar(50) default NULL,
last_login_time bigint default NULL,
last_login_ip varchar(15),
PRIMARY KEY (id),
UNIQUE KEY email(email)
)ENGINE=InnoDB DEFAULT CHARSET=UTF8;

insert into dl_user(email,nick_name,password,
user_integral,is_email_verify,email_verify_code,
last_login_time,last_login_ip)
values("[e-mail]","jdbc","1234",10,"Y","ABSDFWE",345123421345,"192.168.3.1");

select * from dl_user;
insert into dl_user(email,nick_name,password,user_integral,is_email_verify,email_verify_code,last_login_time,last_login_ip)
values("[e-mail]","jdbc","1234",10,"Y","ABSDFWE",345123421345,"192.168.3.1")

注意：

1

当我们要想数据库中插入数据时，

行列一定要对应；

数字不必加单引号，字符串必须加单引号

insert into user(name,num,password) values('sufeng',7,'')

2

更新操作(包括删除)，在选定表，选定行列的基础上再去修改，否则会酿成错误，

update dl_user set email= '[e-mail]' where id=7

３　查询操作

列－》变量

where是判断条件

常用的查询语句

# 查询主键为32的商品
select * from goods where good_id=32；

# 选择不属于第三栏目的商品
select good_id,cat_id,good_name from goods where  cat_id != 3；

# 不高于三千元的商品
select good_id,good_name form goods where shop_price<=3000；

# 取出第七栏或者第十栏的商品
select good_id,good_name,cat_id  from goods where cat_id=7 or cat_id=10；
select good_id,good_name,cat_id  from goods where cat_id in <7,11> ；

#查询价格在100到500之间的所有商品
select good_id,good_name.shop_price from goods where shop_price>=100 and shop_price <= 500;
select good_id,good_name,shop_price from goods where shop_price between 100 and 500;  

#取出不属于第三栏目且不属于第四栏目的商品
select good_id,good_name,cat_id from goods where cat_id not in <4,5>;

#取出第三个栏目下边价格在1000到3000，并且点击率大于5的系列商品
select good_id,good_name,cat_id,shop_price,click_cout from where cat_id=3 and (shop_price<1000 or shop_price>3000) and click_count>5;

# 模糊查询 %通配任意字符
select good_id ,cat_id,good_name from goods where goods_name like 'windows%';

#模糊查询 "_"通配一个字符
select good_id,cat_id,good_name,from goods where goods_name like 'window__'

# 将10到20内数字记为10，将20到30内数字记为20
update userinfo set age=floor(age/10)*10;

# 替换字符串
select goods_id,concat('htc',substring(good_name,4)) from goods where good_name like '诺基亚%';

# 统计函数 平均值函数
 select avg(good_price) from goods;

# 统计函数 统计个数
select count(*) from goods;

# 按照商品类别分组进行统计
select cat_id,avg(good_price) from goods group by cat_id;

# where是对数据本身的判断，having 是对结果集的判断，两者虽然都能起筛选的作用，但有着本质的区别
select good_id,(mark_price-show_price) as pro from goods where 1 having pro >200;

#....
select name,sum(score<60) as gk,avg(score) from grades  group by name having gk>=2;

#....order by排序 asc(默认)升序 desc降序  limit 开始位置 条目数

select good_id,good_name.cat_id,show_price from goods order by cat_id asc,show_price desc limit 2,3;

#5种 子句 顺序 where , group by, having ,order by, limit

# where 型子查询
 select good_id,good_name,show price,cat_id from goods where good_id=(select max(good_id) from goods);

# where 型子查询
 select good_id,good_name,show price,cat_id from goods where good_id in (select max(good_id) from goods group by cat_id);

# from型 子查询
select * from (select good_id,good_name,cat_id,show_price from goods order by cat_id asc,good_id_desc) group by cat_id;

#exist 子查询
select * from category where exists(select * from goods where good.cat_id=category.cat_id);

# 内连接查询
select xx from
table1 inner join table2 on table1.xx =table2.xx;

#左连接 查询 左表为主，右表为辅
select xx from
table1 left join table2 on table1.xx =table2.xx;

# 比赛详情示例1
select m.*,t1.tname as hteam,t2.tname as gteam
from
m inner join t as t1 on m.hid=t1.tid inner join as t2 on m.git =t2.tid ;

# 比赛详情示例2
select mid,t1.tname as hteam,t2.tname as gteam
from
m inner join t as t1 on m.hid=t1.tid inner join t as t2 on m.gid=t2.tid
where matme between  '2006-06-01' and '2006-07-01';

# union操作
select  uid,name from user
union
select id,name from tmp;

union的语句必须满足一个条件，各语句取出的列数相同，列名称未必要一致，列名称会使用第一条sql语句的列。

# union 操作
select id,sum(num) from
(select * from a
union all
select * from b) as tmp
group by id;

使用union时 ，完全相等的行将会被合并；因此，这里直接采取union all

create  table 表名（

列1    列类型   【列属性】

列2   列类型    【列属性】

）；

engine=存储引擎

charset=字符集

整型列

bigint         int           mediumint       smallint       tinyint

# 增加无符号列
alert table t2 add unum tinyint unsigned;

#0填充，固定长度4
alert table t2 add sn tinyint(4) zerofill;

# 查看表结构
desc t2;

浮点型

float/double 有精度损失

（M，D） M时总位数，D是精确度

decimal定点型 更精确

字符型

char  定常

varchar 变长

text

blog

enum

#enum操作
create table t7(
gender enum('男','女')
)；

时间日期类型

#创建年月日时间表
create table t8(
ya year,
dt date,
tm time,
dttm datetime);

#创建年
insert into t8 (ya) values(1996);

#创建日期
insert into t8(dt) values('1990-12-23');

#创建时间
insert into t8 (tm) values('18:23:45');

#详细时间
insert into t8 (dttm) values('1996-01-07 23:34:56');

timestamp时间戳类型

定义该属性 该列自动填充系统时间

#建表 不允许空 带默认值
create table t10(
id int not null default 0,
name char(10) not null default ' '
);

#声明表的主键
create table t11(
id int primary key,
name char(2)
);

create table t12(
id int,
name char(2),
primary key (id)
);

primary key此列不重复 可以做到区分元组

数值型primary key 一般与auto_increament联用

建表时注意定常与变长，常用与不常用的结合，时刻考虑查询速度及空间浪费。

改表名字

rename table regist3 to  reg3;

增加列

alter table reg3 add height tinyint unsigned not null default 0;

删除列

alter table reg3 drop column height;

在指定列后添加

alter table reg3 add height tinyint unsigned after weight;

修改列

按照商品类别分组 计算每种类别售价均值

create view v3 as select  cat_id,avg(show_price) from goods group by cat_id;

视图的引入

视图又被叫做虚拟表，时sql语句的查询结果，

保证了数据表的安全，权限的控制（体现在对部分列的开放和关闭，），以及简化可sql语句

create view v3 as select cat_id,avg(shop_price) as pj  from goods group by cat_id;
select * from v3 order by pj limit 0,3;

视图的更新 删除，分情况，

如果视图的每一行是与物理表一一对应的，则可以，

view的行是由物理表多行计算得到的结果，则不可以。

视图的algorthm

对于简单查询形成的view，再对view查询，如where，order 等等，可以把建视图的语句和查视图的语句

合并成差五里的语句，这种视图的算法叫merge算法；

对于复杂查询生成的视图，结果集形成临时表temp 表，再对临时表进行操作；

create algorithm=merge view v7 as select goods_id,goods_name from goods;

表管理语句

#选择数据库
use test;

#显示表
show tables;
(#视图也会被显示)

#显示表结构
desc t12;

#删除表
drop table tmp;
#删除视图
drop view vuser;

#查看表详细信息
show table status  \G
#(\G竖着显示)
show table status where name='goods' \G;

#改表名
rename table oldName to Newname;

#清空表数据
truncate  t12;

frm存储表结构

myd数据信息

myi索引

存储引擎 Myisam InnoDB Memory

客户端提交的字符集  character_set_client = gbk;

客户端返回的字符集　character_set_results = gbk   (/utf8);

服务器端的字符集 character_set_connection = gbk;

简写 为 set names gbk;

校对集 排序规则

索引是数据的目录，能快速定位数据的位置，

提高了查询速度，增加了增删改的麻烦，

一般在查询频率较高，重复度较低的列上加；

primary key();

key key1( key11)

unique key key2(key22)

可以设定索引的长度，多列索引以及索引修饰

create table t18(
id int,
name char(10),
email char(20),
primary key(id),
key name(name),
unique key  email(email(10))
key xx(name,email)
);

show index from t19 /G;

explain  select * from t19  where xing='朱' and ming='院长'  \G；

#删除索引
alter table t20 drop index m;

create table t16(
email char(30);
)

insert into t16 values('abc@163.c0m','[e-mail]');

select left (email,3) from t16;

select position ('@' in email ) from t16;

select left (email,position('@' in email)-1) from t16;

时间日期

select date_formate(now(),'%Y%m');

事务

update account set money=money+500;

start transaction;

commit;

rollback;

模糊查询   % 一个或多个字符

select  *  from  studentinfo  where sname  like  '张%'；

any

select sname as 补考学生
from studentinfo
where sno=any(select sno from elective where score<60);

in

select sname as 考试不及格的学生
 from studentinfo
where  sno in (select sno from elective where score < 60) ;

not exist

select tname ,tpro
from teacher
where not exists  (select * from teacher
where tpro='教授');

存储过程

#结束符定义
delimter //

#创建存储过程
create procedure p_jiaoshi()

begin

select *  from teacher where tedu='硕士研究生'；

end

//

# 调用存储过程
call p_jiaoshi();

存储过程与存储函数都哦是由sql语句和过程式所组成的代码片段，并且可以被应用程序和其他的sql语句调用，他们之间的区别在于

（1）存储函数不能有输出参数，因为存储函数本身就是输出参数；而存储过程可以拥有输出参数。

（2）可以直接对存储函数进行调用，而不需要使用call与语句：对存储过程的调用，需要使用call语句。

（3） 存储函数中必须包含一条return语句，而这条特殊的sql语句不允许包含于存储过程中。

# 创建过程函数
create function addTwoNumber(x small  unsigned, y smallint unsigned)
return smallint unsigned
begin
declare a,b smallint usigned default 10:
set a=x, b=y;
return a+b;
end

# 函数调用
set @num1=10;
set @num2=20;
set result =  addTwoNumber(@num1,@num2);
select @ result;

事务，原子性， 一致性， 隔离性， 持久性

# 开启事务
start transation;
insert into teacher values('t006'，'张君瑞','男')；
insert into teacher values('t007','赵楠','女')；

# 在事务处理过程中，为了使执行的修改操作保存在数据库中，事务处理结束必须由用户提交
#  确定，使用commit语句
commit;
select * from teacher;

在进行事务处理过程中，如果事务尚未提交时发现某些操作不合理，可以通过事务的回滚来取消当前事务，把数据库恢复到事务处理之前的状态。

rollback;