sql常用操作

1.运算符
1)算数运算符
+ 加法 -添加任一侧上的操作符的值
- 减法 - 从左边减去右边操作数的操作
* 乘法 - 操作符两侧的值相乘
/ 除 - 将左边除右边的操作数（取模）
% 模量 - 将左边用右手操作，并返回操作数的余数
2)比较运算符
= 检查如果两个操作数的值是相等或不相等，如果是的话，条件为真。
!= 检查如果两个操作数的值是否相等，如果值不相等，则条件为真。
<> 检查如果两个操作数的值是否相等，如果值不相等，则条件为真。
> 检查值，如果左操作数大于右操作数的值，如果是的话，条件为真。
< 检查如果左操作数的值小于右操作数的值，如果是的话，条件为真。
>= 检查如果左操作数的值大于或等于右操作数的值，如果是的话，条件为真。
<= 检查如果左操作数的值小于或等于右操作数的值，如果是的话，条件为真。
!< 检查如果左操作数的值不小于右操作数的值，如果是的话，条件为真。
!> 检查如果左操作数的值是不大于右操作数的值，如果是的话，条件为真。
3)逻辑运算符
AND AND运算符允许多个条件的存在，在一个SQL语句中的WHERE子句。
OR 使用OR运算符结合SQL语句的WHERE子句中的多个条件。
IN IN操作符用来比较的文字值已指定一个值的列表。
LIKE LIKE运算符用于比较相似的值，使用通配符的值。
EXISTS 存在指定的表中符合特定条件的运算符用于搜索存在的一行记录。
UNIQUE 独特的操作符搜索指定的表中的每一行的唯一性（不重复）。
ALL 所有运算符用于比较的值到另一个值组中的所有值。
ANY ANY运算符用于比较的值在列表中根据任何适用的条件。
BETWEEN BETWEEN运算符用于搜索一组值的范围内的值，给定的最小值和最大值。
IS NULL NULL操作符用来比较NULL的值。
NOT NOT运算符的含义相反的逻辑运算符，它被使用如 NOT EXISTS, NOT BETWEEN, NOT IN 等，这是一个相反的运算符。


2.基本sql
选择：select * from table1 where 范围
插入：insert into table1(field1,field2) values(value1,value2)
删除：delete from table1 where 范围
更新：update table1 set field1=value1 where 范围
查找：select * from table1 where field1 like ’%value1%’
排序：select * from table1 order by field1,field2 [asc,desc]
总数：select count(field1) as totalcount from table1
求和：select sum(field1) as sumvalue from table1
平均：select avg(field1) as avgvalue from table1
最大：select max(field1) as maxvalue from table1
最小：select min(field1) as minvalue from table1


3.连表
t1 t2t3
id nameid hobbyid grade
1 张三1 篮球 1 11级
2 李四2 排球 2 12级
3 王五3 足球 3 13级
5 赵六6 水球 7 14级

1)左连表
select * from t1 a
left join t2 b on b.id=a.id
left join t3 c on c.id=a.id
where a.id in('2','3','4','5','6','7')
注：on的条件只对外加进来的表有约束限制，原本的表的约束要加在最外层的where条件里
2)内连表
select * from t1 a
join t2 b on b.id=a.id
join t3 c on c.id=a.id
where a.id in('2','3','4','5','6','7')
注：等效于select * from t1 a,t2 b,t3 c where a.id=b.id and a.id=c.id and a.id in('2','3','4','5','6','7')

4.其他
1)create database dbname//创建数据库(drop database dbname)

2)drop table if exists tabname;
3)create table tabname(col1 type1 [not null][default 1],col2 type2 [not null],..); //创建表(drop table tabname)

4)alter table tabname add column col type;//增加新列,列增加后将不能删除
 alter table tabname add primary key(col);//增加主键(alert table tabname drop primary key(col))

5)(case when A.status_flag='0' then '退回' 
when A.status_flag='1' then '新增' 
when A.status_flag='2' then '待复核' 
when A.status_flag='3' then '已复核' 
else '其他' end) status_name//case when then else end条件判断
6)mod() //取模

7)union和union all：//连接两个结果集
区别是,union会自动压缩多个结果集合中的重复结果，而union all则将所有的结果全部显示出来，不管是不是重复。

5.ORACLE和Mysql数据类型差异
ORACLE Mysql 注释
1 NUMBERint/decimal decimal就相当于NUMBER(10,2)，int就相当于NUMBER(10)。Mysql还有其他int型：tinyint，mediumint，bigint等
2 Varchar2(n)varchar(n) 都是用于可变长度的字符串存储
3 DateDATATIME Mysql日期字段分date和time两种，ORACLE日期字段只有DATE，包含年月日时分秒信息
4 INTEGERint/integer Mysql中integer等价于int
5 自增序列自增类型 Mysql插入记录的时候可以自动增长数据类型。而ORACLE需要建立一个自动增长的序列号，插入记录时要把序列号的下一个值赋于此字段。

6.Oracle和Mysql创建用户
Oracle：
1)用户创建与删除
1.sqlplus system/123456@orcl//用system连接上orcl
2.drop user mbs cascade;//删除用户test（所有图和表）
3.create user mbs identified by mbs;//创建用户test和密码
4.grant connect,resource,dba to mbs;//授予所有用户权限
注：connect 临时用户
resource 可靠和正式的用户
dba 最高权限

2)数据导出与导入
exp mbs/mbs@ORCL file=D:\xx.dmp owner=mbs
imp mbs/mbs@ORCL fromuser=mbs touser=mbs file=D:\xx.dmp

3)用户
sys/123456 //超级用户
system/123456 //系统管理员
scott/tiger //默认用户

注：修改sys和system的密码
1.sqlplus/as sysdba 2.空 3.alter user sys identified by xxx; 4.conn sys/xxx as sysdba; 5.alter user system identified by xxx;


Mysql：
1)用户创建与删除
1.mysql -u root -p//连接mysql
2.drop user 'jmm'@'localhost';//删除用户(drop user 'jmm'@'%';)
3.create user 'jmm'@'localhost' identified by 'jmm';//创建用户(create user 'jmm'@'%' identified by 'jmm';)
4.grant all on *.* to 'jmm'@'localhost';//赋予权限(grant all on *.* to 'jmm'@'%';)
注：localhost为本机，%为远程

2)select * from mysql.user;//查看用户

3)show VARIABLES LIKE 'character%';//查看字符集设置(set character_set_client=utf8;)

7.MySql表结构
(1)日期和时间
date 3字节，格式：2014-09-18 
time 3字节，格式：08:42:30 
datetime 8字节，格式：2014-09-18 08:42:30 
timestamp 4字节，自动存储记录修改的时间 
year 1字节，年份 
(2)整形
tinyint 1字节，范围（-128~127） 
smallint 2字节，范围（-32768~32767） 
mediumint 3字节，范围（-8388608~8388607） 
int 4字节，范围（-2147483648~2147483647） 
bigint 8字节，范围（+-9.22*10的18次方） 
(3)浮点型
float(m, d) 4字节，单精度浮点型，m总个数，d小数位 
double(m, d) 8字节，双精度浮点型，m总个数，d小数位 
decimal(m, d) decimal是存储为字符串的浮点数 
(4)字符串类型
char(n) 固定长度，最多255个字符 
varchar(n) 可变长度，最多65535个字符/* varchar数据类型的长度必须指定 */
tinytext 可变长度，最多255个字符 
text 可变长度，最多65535个字符 
mediumtext 可变长度，最多2的24次方-1个字符 
longtext 可变长度，最多2的32次方-1个字符 

8.Mysql一般操作
## 连接数据库
    mysql -u root -p;


    ## 删除(注:localhost为本机,%为远程)
    drop user 'jmm'@'%';


    ## 创建
    create user 'jmm'@'%' identified by 'jmm';


    ## 授予权限
    grant all on *.* to 'jmm'@'%';


    ## 查询用户信息
    SELECT A.Host,A.User FROM mysql.user A;


    ## 查看字符集设置
    show VARIABLES LIKE 'character%';



    ## 创建数据库
DROP DATABASE `jfinal`;
CREATE DATABASE `jfinal` CHARACTER SET utf8 COLLATE utf8_general_ci;



    ## 创建表
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
 `id` int(11) NOT NULL auto_increment,
 `name` varchar(200) default 'xx',
 `password` varchar(200) NOT NULL,
 PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;