Fork me on GitHub

sql语句大全

数据库/表结构操作

创建/删除数据库

1
2
3
create database dbname;//创建数据库
drop database dbname;//删除数据库
drop database if exists dbname;//删除数据库如果存在

例如(mysql):

1
2
3
create database test;//创建数据库
drop database test;//删除数据库
drop database if exists test;//如果存在这个数据库,就删除它

创建/删除表

1
2
3
//创建表
create table tabname(column_name1 type1 [not null] [primary key],column_name2 type2 [not null],..);
drop table tabname;//删除表

例如(mysql):

1
2
3
//创建表
create table emp(id float(7,3) not null primary key,name varchar(30),salary float(8,2))charset utf8;
drop table emp;//删除表

根据已有的表创建新表:

1
2
3
4
5
create table tab_new like tab_old; //只复制表结构到新表
//复制部分表结构及数据到新表(完整的用select *)
create table tab_new select column_name1,column_name2 from tab_old;
//复制部分表结构到新表
create table tab_new select column_name1,column_name2 from tab_old where 1=2;

例如(mysql):

1
2
3
create table emp1 like emp;//只复制表结构到新表
create table emp2 select id,name from emp;//复制部分表结构及数据到新表(完整的用select *)
create table emp2 select id,name from emp where 1=2;//复制部分表结构到新表

列出表的创建命令

1
show create table tabname;

例如(mysql):

1
show create table emp;

查表结构

1
desc tabname;

例如(mysql):

1
desc emp;

修改表

1
2
3
4
5
alter table tabname add column_name type [not null];//新增字段
alter table tabname modify column_name type;//修改字段属性
//修改字段名;此时一定要重新指定该字段类型
alter table tabname change column_nameold column_namenew type;
alter table tabname drop column column_name;//删除字段

例如(mysql):

1
2
3
4
alter table emp add sex char(1) not null;//新增字段
alter table emp modify sex char(10);//修改字段属性
alter table emp change sex sex2 char(2);//修改字段名;此时一定要重新指定该字段类型
alter table emp drop column sex2;//删除字段

重命名表

1
rename table tabname to tabname-new,tabname2 to tabname2-new,...;//重命名表(可多个)

例如(mysql):

1
rename table emp to emp1;

添加/删除主键

1
2
3
4
alter table tabname add primary key(column_name);//添加主键
//修改主键为自增长主键
alter table tabname modify column_name type auto_increment primary key;
alter table tabname drop primary key;//删除主键

例如(mysql):

1
2
3
4
5
6
7
alter table emp add primary key(id);//添加主键
//修改主键为自增长主键
alter table emp1 modify id int auto_increment primary key;
alter table emp1 drop primary key;//删除主键
//删除自增长主键:分2步
alter table emp1 modify id int;//1.改数据类型
alter table emp1 drop primary key;//2.删除主键

创建/删除索引

1
2
3
4
5
6
7
8
create [unique] index idxname on tabname(column_name...);//对表增加索引
alter table table_name add index index_name(column_list);//创建普通索引
//创建unique索引;索引名字不加默认字段名
alter table table_name add unique [index_name](column_list);
alter table table_name add primary key(column_list);//创建primary key索引
drop index index_name on talbe_name;//删除索引
alter table table_name drop index index_name;//删除索引
alter table table_name drop primary key;删除主键(索引)

例如(mysql):

1
2
3
4
5
6
7
create unique index ididx on emp1(id);//对表增加索引
alter table emp1 add index sidx (salary);//创建普通索引
alter table emp1 add unique [sidx](id);//创建unique索引;索引名字不加默认字段名
alter table emp1 add primary key(id);//创建primary key索引
drop index idxname on emp1;//删除索引
alter table emp1 drop index idxname;//删除索引
alter table emp1 drop primary key;删除主键(索引)

注:索引是不可更改的,想更改必须删除重新建。
不能用create index语句创建primary key索引。

创建/删除视图

1
2
create view viewname as select column_name1,column_name2... from tabname;//创建视图
drop view viewname;//删除视图

例如(mysql):

1
2
create view empview as select * from emp1;//创建视图
drop view empview;//删除视图

数据库中表与视图的联系与区别:
联系:视图(view)是在基本表之上建立的表,它的结构(即所定义的列)和内容(即所有数据行)都来自基本表,它依据基本表存在而存在。一个视图可以对应一个基本表,也可以对应多个基本表。视图是基本表的抽象和在逻辑意义上建立的新关系
区别:1、视图是已经编译好的sql语句。而表不是
2、视图没有实际的物理记录。而表有。
3、表是内容,视图是窗口
4、表只用物理空间而视图不占用物理空间,视图只是逻辑概念的存在,表可以及时四对它进行修改,但视图只能有创建的语句来修改
5、表是内模式,视图是外模式
6、视图是查看数据表的一种方法,可以查询数据表中某些字段构成的数据,只是一些SQL语句的集合。从安全的角度说,视图可以不给用户接触数据表,从而不知道表结构。
7、表属于全局模式中的表,是实表;视图属于局部模式的表,是虚表。
8、视图的建立和删除只影响视图本身,不影响对应的基本表。

sql语句操作

简单的基本sql语句(包括增删改查)

1
2
3
4
5
6
7
8
9
10
11
select * from tabname where ...;//查询数据
insert into tabname(column_name1,column_name2) values(value1,value2);//插入数据
delete from tabname where ...;//删除数据
update tabname set column_name1=value1,column_name2=value2... where ...;//更新数据
select * from tabname where column_name1 like ’%value1%’;//模糊查询数据
select * from tabname order by column_name1,column_name12 [desc/asc];//排序(降序desc/升序asc)
select count as totalcount from table1;//总数;as totalcount是给它一个字段名
select sum(column_name) as sumvalue from tabname;//求和
select avg(column_name) as avgvalue from tabname;//平均值
select max(column_name) as maxv from tabname;//最大值
select min(column_name) as minv from tabname;//最小值

例如(mysql):

1
2
3
4
5
6
7
8
9
10
11
12
13
select * from emp where id=1;//查询数据
insert into emp1(id,name,salary) values(4,'王五',8000);//插入数据
delete from emp1 where id=4;//删除数据
update emp1 set name='阿黄',salary=5000 where id=3;//更新数据
select * from emp1 where salary like '%0%';//模糊查询数据
select * from emp1 where name like '_A%';//%表示0到多个字符; _:标识单个字符
select * from tabname order by column_name1,column_name12 [desc/asc];//排序
select count(*) as totalcount from emp1;//行数目总数;as totalcount是给它一个字段名
select count(remarks) as totalcount from emp1;//count(column_name)是对列中不为空的行进行计数
select sum(salary) as sumvalue from emp1;//列中数据求和(非数字为0)
select avg(salary) as avgvalue from emp1;//平均值
select max(salary) as maxv from emp1;//最大值
select min(salary) as minv from emp1;//最小值

mysql查询的五种子句

where(条件查询)、having(筛选)、group by(分组)、order by(排序)、limit(限制结果数)

where(条件查询)

where常用运算符

比较运算符

, < , = , !=(< >), >= , <= , in(v1,v2..vn) , between v1 and v2 在v1至v2之间(包含v1,v2)
逻辑运算符
not(!) 逻辑非 , or(||) 逻辑或 , and(&&) 逻辑与

1
2
3
4
// 取500-1000或者3000-5000的值
where price>=3000 and price <= 5000 or price >=500 and price <=1000;
//不在30005000之间的值
where price not between 3000 and 5000;

模糊查询

like 像
通配符: % 任意字符
_ 单个字符

1
2
where goods_name like '诺基亚%';
where goods_name like '诺基亚N__';

group by 分组

一般情况下group需与统计函数(聚合函数)一起使用才有意义

mysql中的五种统计函数:

max(求最大值),min(求最小值),sum(求总数和),avg(求平均值),count(求总行数);

1
2
//查询每个栏目下面积压的货款
select cat_id,sum(goods_price * goods_number) as hk from goods group by cat_id

having 与where 的异同点

having与where类似,可以筛选数据,where后的表达式怎么写,having后就怎么写
where针对表中的列发挥作用,查询数据
having对查询结果中的列发挥作用,筛选数据

1
2
3
4
5
//查询本店商品价格比市场价低多少钱,输出低200元以上的商品
select goods_id,good_name,market_price - shop_price as s from goods having s>200 ;
//这里不能用where因为s是查询结果,而where只能对表中的字段名筛选
//如果用where的话则是:
select goods_id,goods_name from goods where market_price - shop_price > 200;

同时使用where与having

1
2
3
4
5
6
7
8
9
10
11
12
13
14
select cat_id,goods_name,market_price - shop_price as s from goods where cat_id = 3 having s > 200;
//查询积压货款超过2万元的栏目,以及该栏目积压的货款
select cat_id,sum(shop_price * goods_number) as t from goods group by cat_id having s > 20000;
//查询两门及两门以上科目不及格的学生的平均分
//思路:
//先计算所有学生的平均分
select name,avg(score) as pj from stu group by name;
//查出所有学生的挂科情况
select name,score<60 from stu;
//这里score<60是判断语句,所以结果为真或假,mysql中真为1假为0
//查出两门及两门以上不及格的学生
select name,sum(score<60) as gk from stu group by name having gk > 1;
//综合结果
select name,sum(score<60) as gk,avg(score) as pj from stu group by name having gk >1;

order by

1
2
3
4
5
6
order by price; //默认升序排列
order by price desc; //降序排列
order by price asc; //升序排列,与默认一样
order by rand(); //随机排列,效率不高
//按栏目号升序排列,每个栏目下的商品价格降序排列
select * from goods where cat_id !=2 order by cat_id,price desc;

limit

limit [offset,] N
offset 偏移量,可选,不写则相当于limit 0,N
N 取出条目

1
2
//取价格第4-6高的商品
select good_id,goods_name,goods_price from goods order by good_price desc limit 3,3;

例:查询每个栏目下最贵的商品

order by、group by、子查询同时使用

1
2
3
4
5
6
7
8
//思路:
//先对每个栏目下的商品价格排序
select cat_id,goods_id,goods_name,shop_price from goods order by cat_id,shop_price desc;
//上面的查询结果中每个栏目的第一行的商品就是最贵的商品
//把上面的查询结果理解为一个临时表[存在于内存中]【子查询】
//再从临时表中选出每个栏目最贵的商品
select * from (select goods_id,goods_name,cat_id,shop_price from goods order by cat_id,shop_price desc) as t group by cat_id;
//这里使用group by cat_id是因为临时表中每个栏目的第一个商品就是最贵的商品,而group by前面没有使用聚合函数,所以默认就取每个分组的第一行数据,这里以cat_id分组

良好的理解模型

  • where后面的表达式,把表达式放在每一行中,看是否成立
  • 字段(列),理解为变量,可以进行运算(算术运算和逻辑运算)
  • 取出结果可以理解成一张临时表

mysql子查询

where型子查询

把内层查询结果当作外层查询的比较条件

1
2
3
4
//不用order by 来查询最新的商品
select goods_id,goods_name from goods where goods_id = (select max(goods_id) from goods);
//取出每个栏目下最新的产品(goods_id唯一)
select cat_id,goods_id,goods_name from goods where goods_id in(select max(goods_id) from goods group by cat_id);

from型子查询

把内层的查询结果供外层再次查询

1
2
3
4
5
6
7
8
//用子查询查出挂科两门及以上的同学的平均成绩
//思路:
//先查出哪些同学挂科两门以上
select name,count(*) as gk from stu where score < 60 having gk >=2;
//以上查询结果,我们只要名字就可以了,所以再取一次名字
select name from (select name,count(*) as gk from stu having gk >=2) as t;
//找出这些同学了,那么再计算他们的平均分
select name,avg(score) from stu where name in (select name from (select name,count(*) as gk from stu having gk >=2) as t) group by name;

exists型子查询

把外层查询结果拿到内层,看内层的查询是否成立

1
2
3
//如果department表中存在d_id取值为1003的记录,则查询employee表的记录。
//SELECT语句的代码如下
select * from employee where exists (select d_name from department where d_id=1003);

上述中代码执行的结果显示,查询出了employee表中的所有记录。

因为department表中存在d_id值为1003的记录,内层查询语句返回一个true,外层查询语句接收true后,开始查询employee表中的记录。因为没有设置查询employee表的查询条件,所以查询出了employee表的所有记录。

1
2
//如果department表中存在d_id取值为1003的记录,则查询employee表中age大于24岁的记录。
select * from employee where age>24 and exists (select d_name from department where d_id=1003);

当内层查询语句从department表中查询到记录,返回一个true,外层查询语句开始进行查询,根据查询条件,从employee表中查询出age大于24岁的两条记录。

1
2
//如果department表中不存在d_id字段取值为1003的记录,则查询employee表的记录。
select * from employee where not exists (select d_name from department where d_id=1003);

1
2
//查询哪些栏目下有商品,栏目表category,商品表goods
select cat_id,cat_name from category where exists(select * from goods where goods.cat_id = category.cat_id);

分页查询

Oracle分页查询:

1
select * from (select t.*, rownum rn from (select * from users order by id) t where rownum<=pageSize*pageNow) where rn>=(pageSize*(pageNow-1)+1));

Mysql分页查询:

1
select * from users order by id limit pageSize*(pageNow-1), pageSize;

关联查询

关联查询必须要写链接条件,N张表进行链接查询,至少要有N-1个链接条件(消除笛卡尔积)

1
select a.name,b.name from emp1 a,emp2 b where a.id = b.id;

distinct关键字

例如(mysql):

1
2
3
4
5
6
//从emp中查询得到name不重复的所有数据的name字段,使用distinct去掉多余的重复记录。
select distinct name from emp;
//从emp中查询得到name不重复的所有数据
//可以使用group by,这里group by前面没有用聚合函数(count(*)之类)的话,
//默认取每个分组的第一条数据;order by 需要放在最后面
select * from emp group by name order by name desc;

时间和日期

mysql中Timestamp,time,datetime 区别?

TIMESTAMP(用来自动插入日期时间)
时间戳。范围是’1970-01-01 00:00:00’到2037年。
TIMESTAMP列用于INSERT或UPDATE操作时记录日期和时间。
如果你不分配一个值,表中的第一个TIMESTAMP列自动设置为最近操作的日期和时间。
也可以通过分配一个NULL值,将TIMESTAMP列设置为当前的日期和时间。
TIMESTAMP值返回后显示为’YYYY-MM-DD HH:MM:SS’格式的字符串,
显示宽度固定为19个字符。如果想要获得数字值,应在TIMESTAMP 列添加+0。

1
2
3
4
5
6
select curdate();//2016-02-20
select curdate() + 0;//20160220
select curtime();//12:20:24
select curtime() + 0;//121946
select now();//2016-02-20 12:20:44.0
select now() + 0;//20160220122114

TIME
时间。范围是’-838:59:59’到’838:59:59’。
MySQL以’HH:MM:SS’格式显示TIME值,但允许使用字符串或数字为TIME列分配值。

DATETIME
日期和时间的组合。
支持的范围是’1000-01-01 00:00:00’到’9999-12-31 23:59:59’。
MySQL以’YYYY-MM-DD HH:MM:SS’格式显示DATETIME值,但允许使用字符串或数字为DATETIME列分配值。

mysql 创建表时,设置时间字段自动插入当前时间

1
create table content(id char(20) not null,time1 timestamp not null default current_timestamp,primary key (id)) engine=InnoDB default charset=utf8;

mysql中TIMESTAMP设置默认值的灵活运用:
CURRENT_TIMESTAMP :当我更新这条记录的时候,这条记录的这个字段不会改变
CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP :当我更新这条记录的时候,这条记录的这个字段将会改变。即时间变为了更新时候的时间。

1
2
//这里如果update数据时,相应的行的对应time1字段时间会更新
create table content1(id char(20) not null,time1 timestamp not null default current_timestamp on update current_timestamp,primary key (id)) engine=InnoDB default charset=utf8;

MySQL默认表的第一个timestamp字段为NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP属性,必须显式定义改变这种行为。

1
2
3
//第一个timestamp字段为not null default current_timestamp on update current_timestamp属性
//自动更新时间;注意是第一个timestamp字段(如果有多个)
create table content2(id char(20) not null,time1 timestamp ,primary key (id)) engine=InnoDB default charset=utf8;

mysql插入时间/日期+时间

仅仅插入时间

1
2
3
4
create table emp(id char(20),createtime time)charset=utf8;//创建表
insert into emp values(1,'12:12:12');//插入时间
insert into emp values(2,curtime());//插入当前时间(23:33:48)
insert into emp values(2,now());//插入当前时间(now()也可以23:33:48)

插入日期和时间

1
2
3
4
create table emp(id char(20),createtime datetime)charset=utf8;//创建表
insert into emp values(1,'1999-9-9 12:12:12');//插入时间
insert into emp values(2,curtime());//插入当前时间(2016-02-19 23:38:41)
insert into emp values(2,now());//插入当前时间(now()也可以2016-02-19 23:38:41)

mysql中now,curdate,curtime,sysdate区别:
我们一般在执行语句的时候,都是用now(),因为sysdate()获取当时实时的时间(查看的系统时间,时时的系统时间),这有可能导致主库和从库执行时返回值不一样,导致主从数据库不一致。
区别:除了本身定义所返回的区别以为,另外一个区别是:now(),curtime(),curdate()都是返回基于语句的开始执行时间,而sysdate()返回time的值。

查询30(N)天内的数据(TO_DAYS)

1
2
//这里createtime是时间的字段名
select * from emp4 where to_days(now())-to_days(createtime) <= 30;

返回 date 的星期/月/年索引

这里的date可以用时间、日期+时间、now()
DAYOFWEEK(date):
返回 date 的星期索引(1 = Sunday, 2 = Monday, … 7 = Saturday)。索引值符合 ODBC 的标准。

1
2
select dayofweek('1998-02-03');//具体时间的星期索引
select dayofweek(now());//now的星期索引

WEEKDAY(date):
返回 date 的星期索引(0 = Monday, 1 = Tuesday, … 6 = Sunday)。

1
2
3
select weekday('1997-11-05');//日期
select weekday('1998-02-03 22:23:00');//日期+时间
select weekday(now());//now()

DAYOFMONTH(date)
DAYOFYEAR(date)
MONTH(date)
YEAR(date)
HOUR(time)
MINUTE(time)
SECOND(time)

1
2
3
4
5
6
7
select dayofmonth('1998-02-03');//返回 date 是一月中的第几天,范围为 1 到 31
select dayofyear('1998-02-03');//返回 date 是一年中的第几天,范围为 1 到 366
select month('1998-02-03');//返回 date 中的月份,范围为 1 到 12
select year('1998-02-03');//返回 date 的年份,范围为 1000 到 9999
select hour('10:05:03');//返回 time 的小时值
select minute('10:05:03');//返回 time 的分钟值
select second('10:05:03');//返回 time 的秒值

DAYNAME(date)
MONTHNAME(date)
QUARTER(date)

1
2
3
select dayname('1998-02-03');//返回 date 的星期名
select monthname('1998-02-03');//返回date的月份名
select quarter('1998-02-03');//返回 date 在一年中的季度,范围为 1 到 4

WEEK(date)
WEEK(date,first)
对于星期日是一周中的第一天的场合,如果函数只有一个参数调用,返回 date 为一年的第几周,返回值范围为 0 到 53 (是的,可能有第 53 周的开始)。两个参数形式的 WEEK() 允许你指定一周是否以星期日或星期一开始,以及返回值为 0-53 还是 1-52。这里的一个表显示第二个参数是如何工作的: 值 含义
0 一周以星期日开始,返回值范围为 0-53
1 一周以星期一开始,返回值范围为 0-53
2 一周以星期日开始,返回值范围为 1-53
3 一周以星期一开始,返回值范围为 1-53

1
2
select week('1998-02-03');//返回今天是一年中的第几周,默认一周以星期日开始(0)
select week('1998-02-03',1);//返回今天是一年中的第几周,以周一开始

mysql格式化日期函数

DATE_FORMAT(date,format)
依照 format 字符串格式化 date 值。下面的修饰符可被用于 format 字符串中: 修饰符 含义
%M 月的名字 (January..December)
%W 星期的名字 (Sunday..Saturday)
%D 有英文后缀的某月的第几天 (0th, 1st, 2nd, 3rd, etc.)
%Y 年份,数字的,4 位
%y 年份,数字的,2 位
%X 周值的年份,星期日是一个星期的第一天,数字的,4 位,与 ‘%V’ 一同使用
%x 周值的年份,星期一是一个星期的第一天,数字的,4 位,与 ‘%v’ 一同使用
%a 缩写的星期名 (Sun..Sat)
%d 月份中的天数,数字的 (00..31)
%e 月份中的天数,数字的 (0..31)
%m 月,数字的 (00..12)
%c 月,数字的 (0..12)
%b 缩写的月份名 (Jan..Dec)
%j 一年中的天数 (001..366)
%H 小时 (00..23)
%k 小时 (0..23)
%h 小时 (01..12)
%I 小时 (01..12)
%l 小时 (1..12)
%i 分钟,数字的 (00..59)
%r 时间,12 小时 (hh:mm:ss [AP]M)
%T 时间,24 小时 (hh:mm:ss)
%S 秒 (00..59)
%s 秒 (00..59)
%p AM 或 PM
%w 一周中的天数 (0=Sunday..6=Saturday)
%U 星期 (00..53),星期日是一个星期的第一天
%u 星期 (00..53),星期一是一个星期的第一天
%V 星期 (01..53),星期日是一个星期的第一天。与 ‘%X’ 一起使用
%v 星期 (01..53),星期一是一个星期的第一天。与 ‘%x’ 一起使用
%% 一个字母 “%”

1
2
3
//注意这里的%W %M %Y里面带不带空格决定取得的内容带不带空格
select date_format('1997-10-04 22:23:00','%W %M %Y');//Saturday October 1997
select date_format('1997-10-04 22:23:00','%H:%i:%s');//22:23:00

TIME_FORMAT(time,format)
它的使用方法与上面的 DATE_FORMAT() 函数相似,但是 format 字符串只包含处理小时、分和秒的那些格式修饰符。使用其它的修饰符会产生一个 NULL 值或 0。

其他语法

高级查询运算词

UNION运算符

UNION运算符通过组合其他两(多)个结果表(例如 TABLE1 和 TABLE2)并消去表中任何重复行而派生出一个结果表。当 ALL 随 UNION 一起使用时(即 UNION ALL),不消除重复行(并集)。两种情况下,派生表的每一行不是来自 TABLE1 就是来自 TABLE2。
例如(mysql):

1
2
3
select name from emp union select name from emp1;
//按照名字降序取出前3个,组合两个表的name字段,不消除重复行
select name from emp union all select name from emp1 order by name desc limit 3;

前提:每个SELECT的内容(表项)必须是相同的结构。详细的说,链接的表的列数必须互相相同,同时相对的列属性也必须相同。而列名可以不同(结构相同)

EXCEPT运算符

EXCEPT运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。当 ALL 随 EXCEPT 一起使用时 (EXCEPT ALL),不消除重复行。

1
select column_name from tabname except select column_name from tabname;

例如(mysql):

1
2
//mysql不支持except(SQL Server支持),但是可以用LEFT JOIN来代替
select emp1.name from emp1 left join emp on emp1.name = emp.name where emp.name is null;

左连接:以左表为准,去右表找数据,如果没有匹配的数据,则以null补空位;
所以左表有,右表没有的数据,就是这里以null补空位的行中的左表数据。

INTERSECT运算符

INTERSECT运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一个结果表(交集)。当 ALL 随 INTERSECT 一起使用时 (INTERSECT ALL),不消除重复行。
注:使用运算词的几个查询结果行必须是一致的。
例如(mysql):

1
2
//mysql不支持intersect(SQL Server支持),但是可以用LEFT JOIN来代替
select emp1.name from emp1 left join emp on emp1.name = emp.name where emp.name is not null;

外连接

左连接(左外连接)

以左表为准,去右表找数据,如果没有匹配的数据,则以null补空位,所以输出结果数>=左表原数据数
例如(mysql):

1
select * from emp1 left join emp on emp1.name = emp.name;

左表的第一条数据到右表找,依据是on后面的条件。这里是emp1.name = emp.name;如果这条数据的name在右表找到,后面拼接右表数据;如果没找到,后面拼接右表字段数据都为null;如果on后面的条件是1=1,返回右表数据,并且所有数据条数为笛卡尔积。如果on后面的条件是1=2,返回右表字段数据为null,并且所有数据条数为左表数据数。

右连接(右外连接)

a left join b 等价于 b right join a
推荐使用左连接代替右连接

内连接

查询结果是左右连接的交集,即左右连接的结果去除null项后的并集(去除了重复项)
例如(mysql):

1
select * from emp1 inner join emp on emp1.name=emp.name;

全外连接

full join:不仅包括符号连接表的匹配行,还包括两个连接表中的所有记录。
mysql目前还不支持 外连接(即左右连接结果的并集,不去除null项)

其他查询条件的使用(mysql)

null的使用

将一个null值改为0:

1
update emp set name=0 where name is null;

将一个0值改为null:

1
update emp set name=null where name = 0;

问题与提升

mysql问题

mysql相比于其他数据库有哪些特点?

mysql是一个小型关系型数据库管理系统,开发者为瑞典MySQL AB公司,现在已经被Sun公司(Sun公司被Oracle收购)收购,

  • 可以处理拥有上千万条记录的大型数据
  • 支持常见的SQL语句规范
  • 可移植行高,安装简单小巧
  • 良好的运行效率,有丰富信息的网络支持
  • 调试、管理,优化简单(相对其他大型数据库)

    解决mysql数据库中文乱码问题

  • 在数据库安的时候指定字符集
    如果在安完了以后可以更改以下文件:
    C:\Program Files\MySQL\MySQL Server 5.0\my.ini里的所有的 default-character-set=utf8
    C:\Program Files\MySQL\MySQL Server 5.0\data\depot_development\db.opt
    default-character-set=utf8
    default-collation=utf8_general_ci
  • 建立数据库时候:指定字符集类型

    1
    2
    3
    4
    //gbk
    create database test default character set gbk collate gbk_chinese_ci;
    //utf8
    create database test default character set utf8 collate utf8_general_ci;
  • 建表的时候 也指定字符集

    1
    create table emp(id char(20),createtime time default curtime())charset=utf8;
  • 设置URL的时候

    1
    jdbc:mysql://localhost:3306/database?useUnicode=true&characterEncoding=UTF-8

数据库的优化

数据库的设计
尽量把数据库设计的更小的占磁盘空间

  • 尽可能使用更小的整数类型.(mediumint就比int更合适)。
  • 尽可能的定义字段为not null,除非这个字段需要null。
  • 如果没有用到变长字段的话比如varchar,那就采用固定大小的纪录格式比如char。
  • 表的主索引应该尽可能的短.这样的话每条纪录都有名字标志且更高效。
  • 只创建确实需要的索引。索引有利于检索记录,但是不利于快速保存记录。如果总是要在表的组合字段上做搜索,那么就在这些字段上创建索引。索引的第一部分必须是最常使用的字段。如果总是需要用到很多字段,首先就应该多复制这些字段,使索引更好的压缩。
  • 所有数据都得在保存到数据库前进行处理。
  • 所有字段都得有默认值。
  • 在某些情况下,把一个频繁扫描的表分成两个速度会快好多。在对动态格式表扫描以取得相关记录时,它可能使用更小的静态格式表的情况下更是如此。
    系统的用途
  • 尽量使用长连接。
  • explain 复杂的SQL语句。
  • 如果两个关联表要做比较话,做比较的字段必须类型和长度都一致。
  • LIMIT语句尽量要跟order by或者 distinct.这样可以避免做一次full table scan。
  • 如果想要清空表的所有纪录,建议用truncate table tablename而不是delete from tablename。
  • 能使用STORE PROCEDURE 或者 USER FUNCTION的时候。
  • 在一条insert语句中采用多重纪录插入格式.而且使用load data infile来导入大量数据,这比单纯的indert快好多。
  • 经常OPTIMIZE TABLE 来整理碎片。
  • 还有就是date 类型的数据如果频繁要做比较的话尽量保存在unsigned int 类型比较快。

如何写出高质量、高性能的sql查询

下面就某些SQL语句的where子句编写中需要注意的问题作详细介绍。在这些where子句中,即使某些列存在索引,但是由于编写了劣质的SQL,系统在运行该SQL语句时也不能使用该索引,而同样使用全表扫描,这就造成了响应速度的极大降低。
IS NULL 与 IS NOT NULL
不能用null作索引,任何包含null值的列都将不会被包含在索引中。即使索引有多列这样的情况下,只要这些列中有一列含有null,该列就会从索引中排除。也就是说如果某列存在空值,即使对该列建索引也不会提高性能。
任何在where子句中使用is null或is not null的语句优化器是不允许使用索引的。
联接列
对于有联接的列,即使最后的联接值为一个静态值,优化器是不会使用索引的。我们一起 来看一个例子,假定有一个职工表(employee),对于一个职工的姓和名分成两列存放(FIRST_NAME和LAST_NAME),现在要查询一个 叫比尔.克林顿(Bill Cliton)的职工。
下面是一个采用联接查询的SQL语句,

1
select * from employss where first_name||''||last_name ='Beill Cliton';

上面这条语句完全可以查询出是否有Bill Cliton这个员工,但是这里需要注意,系统优化器对基于last_name创建的索引没有使用。
当采用下面这种SQL语句的编写,Oracle系统就可以采用基于last_name创建的索引。

1
Select * from employee where first_name ='Beill' and last_name ='Cliton'

遇到下面这种情况又如何处理呢?如果一个变量(name)中存放着Bill Cliton这个员工的姓名,对于这种情况我们又如何避免全程遍历,使用索引呢?可以使用一个函数,将变量name中的姓和名分开就可以了,但是有一点需 要注意,这个函数是不能作用在索引列上。下面是SQL查询脚本:

1
select * from employee where first_name = SUBSTR('&&name',1,INSTR('&&name',' ')-1) and last_name = SUBSTR('&&name',INSTR('&&name’,' ')+1);

带通配符(%)的like语句
同样以上面的例子来看这种情况。目前的需求是这样的,要求在职工表中查询名字中包含cliton的人。可以采用如下的查询SQL语句:

1
select * from employee where last_name like '%cliton%';

这里由于通配符(%)在搜寻词首出现,所以Oracle系统不使用last_name的索引。在很多情况下可能无法避免这种情况,但是一定要心中有底,通配符如此使用会降低查询速度。然而当通配符出现在字符串其他位置时,优化器就能利用索引。 在下面的查询中索引得到了使用:

1
select * from employee where last_name like 'c%';

Order by语句
ORDER BY语句决定了Oracle如何将返回的查询结果排序。Order by语句对要排序的列没有什么特别的限制,也可以将函数加入列中(象联接或者附加等)。任何在Order by语句的非索引项或者有计算表达式都将降低查询速度。
仔细检查order by语句以找出非索引项或者表达式,它们会降低性能。解决这个问题的办法就是重写order by语句以使用索引,也可以为所使用的列建立另外一个索引,同时应绝对避免在order by子句中使用表达式。

  1. NOT
    我们在查询时经常在where子句使用一些逻辑表达式,如大于、小于、等于以及不等于等等,也可以使用and(与)、or(或)以及not(非)。NOT可用来对任何逻辑运算符号取反。下面是一个NOT子句的例子:
    1
    where not (status =’VALID’)

如果要使用NOT,则应在取反的短语前面加上括号,并在短语前面加上NOT运算符。NOT运算符包含在另外一个逻辑运算符中,这就是不等于(<>)运算符。换句话说,即使不在查询where子句中显式地加入NOT词,NOT仍在运算符中,见下例:

1
where status <>’INVALID’

再看下面这个例子:

1
select * from employee where salary<>3000;

对这个查询,可以改写为不使用NOT:

1
select * from employee where salary<3000 or salary>3000;

虽然这两种查询的结果一样,但是第二种查询方案会比第一种查询方案更快些第二种查询允许Oracle对salary列使用索引,而第一种查询则不能使用索引。

  1. IN和EXISTS
    有时候会将一列和一系列值相比较。最简单的办法就是在where子句中使用子查询。在where子句中可以使用两种格式的子查询。
    第一种格式是使用IN操作符:
    … where column in(select from … where …);
    第二种格式是使用EXIST操作符:
    … where exists (select ‘X’ from …where …);
    我相信绝大多数人会使用第一种格式,因为它比较容易编写,而实际上第二种格式要远比第一种格式的效率高。在Oracle中可以几乎将所有的IN操作符子查询改写为使用EXISTS的子查询。
    第二种格式中,子查询以‘select ‘X’开始。运用EXISTS子句不管子查询从表中抽取什么数据它只查看where子句。这样优化器就不必遍历整个表而仅根据索引就可完成工作(这里假定在where语句中使用的列存在索引)。相对于IN子句来说,EXISTS使用相连子查询,构造起来要比IN子查询困难一些。
    通过使用EXIST,Oracle系统会首先检查主查询,然后运行子查询直到它找到第一个匹配项,这就节省了时间。Oracle系统在执行IN子查询时,首先执行子查询,并将获得的结果列表存放在在一个加了索引的临时表中。在执行子查询之前,系统先将主查询挂起,待子查询执行完毕,存放在临时表中以后再执行主查询。这也就是使用EXISTS比使用IN通常查询速度快的原因。
    同时应尽可能使用NOT EXISTS来代替NOT IN,尽管二者都使用了NOT(不能使用索引而降低速度),*NOT EXISTS要比NOT IN查询效率更高
「真诚赞赏,手留余香」