史上最全SQL语句诠释.docx
《史上最全SQL语句诠释.docx》由会员分享，可在线阅读，更多相关《史上最全SQL语句诠释.docx（16页珍藏版）》请在冰豆网上搜索。
史上最全SQL语句诠释
创建用户：
createuserzxhidentifiedby'czs';
createuser zxh@localhost identifiedby'czs';
授权:
grantallprivilegeson*.*to'zxh';
*.*是代表任何数据库的任何表
数据库名.* 某某数据的任何操作
grantinsert,selectonmysql.*tousername;
flushprivileges;//刷新权限表
用户登录与切换：
一定要在dos的run中打开
找到mysql.exe的路径
C:
\wamp\mysql\bin\mysql-hlocalhost-uczs-p #-h前面为打开mysql，localhost可以换为ip
删除用户：
dropuserusername1,username2;
删除用户权限：
mysql>revokeallprivilegeson*.*fromczs;
修改用户密码：
GRANTUSAGEON*.*TOusername@"%"IDENTIFIEDBY'password';
mysql注释符有三种：
...代表任意内容 1，3比较常用
1、#...
2、"-- ..."
3、/*...*/
创建数据库：
CREATE DATABASE `sql_study`;
CREATE DATABASE `databaseName` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
进入数据库:
use数据库名;
显示所有表:
showtables;
打印表列名:
desc表名;
创建表：
方式一：
CREATE TABLE `collect`（
`cId`bigint（20） NOT NULL auto_increment,
`jId`bigint（20） NOT NULL,
`uId` int（11） NOT NULL,
`cTime` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY （`cId`）,
KEY `jId`（`jId`）
）ENGINE=InnoDB DEFAULT CHARSET=utf8AUTO_INCREMENT=1 ;
还包含自增由1开始
方式二：
（标准）
create table two
（
name char（20） not null,
id int（11） not null auto_increment,
primary key （id）
）;
方式三：
CREATE TABLE `newwe1e`.`tableName`（
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`name` VARCHAR（ 20 ） NOT NULL DEFAULT '默认',
`time` TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
）ENGINE = INNODB
通过上面上种创建，可以发现列的notnull,primarykey,onupdatecurrent_timestamp等的次序是可以随意改变的
另外primary可以马上说明，也可列出所有列之后在指明。
列名可以不用添加单引号
不可以在主键为多个列的情况下，让某列自增。
插入数据：
方式一：
insert into two（name,id）vaules（'name', null）; #可以去掉id和null，
方式二：
insert into two values（'name', null）;#必须一个个对应，必须列出所有值，不予赋值置null
insert into seven values（null,now（））;#使用时间函数
删除：
删除表中所有元组：
delete from r;#表还在
删除表：
drop table r; /*表不存在*/
删除列，增加列：
ALTER TABLE `two` ADD `num1` VARCHAR（ 11 ） NOT NULL ,
ADD `num2` INT NOT NULL ; #增加列
alter table two drop num1, drop num2; #删除列
（由下面可知，可以删除一个列就要一个drop，增加一个列就要一个add，同时也说明可以嵌套，同时删除，同时增加）
alter table two drop city, add num1 int（11）;
添加属性：
ALTER TABLE `tablename` ADD UNIQUE （`time`） /*增加唯一属性*/
ALTER TABLE `tablename` ADD INDEX （`time`） /*增加索引属性*/
ALTER TABLE `tablename`CHANGE`time``time` VARCHAR（ 20 ） NOT NULL /*改变某一列的属性*/
上面这些修改，都是可以修改嵌套的，增加后面紧跟改变等
外键:
ALTER TABLE `tablenamer` ADD FOREIGN KEY （`本地id`） REFERENCES `one`.`tablename`（`外部id`）;
更新:
UPDATE `imu`.`user` SET `qq` = 'QQ',
`uTime` = NOW（）,
`resume` = '简历',
`mood` = '签名' WHERE `user`.`uId` =16 LIMIT 1 ;
查询:
不重复:
结果元组集中不出现相同元组distinct
selectdistinctnum,shufromeight; //distinct只能一个，作用于所有列,distinct还用于count,sum等函数
范围查询：
between11and200
select*fromeightwhereshubetween11and200; 等价于select*fromeightwhereshu<=200andshu>=11;
between...and包含了等号，还有notbetween...and
字符串匹配查询：
_代表任意的一个字符， %代表任意子串，长度无限
like'ab\%c\\d%'; //转义字符\，让字符串可以匹配%， 匹配\
排序查询：
放在select语句最后，且最后作用，orderby列名desc,列名asc， 默认asc为升序，desc为降序
结果集运算：
select idfromeightunion（selectidfromseven）; //并运算
intersect//交运算
except//差运算
参加运算的关系，对应的列数据类型要相容，n个select语句就n-1个运算关键字
函数查询：
avg,min,max,sum,count
selectname,avg（distinctbalance）fromaccountgroupbynamehavingavg（distinctbalance）>1200;
//先分组，然后查询，之后用having过滤
空值查询：
如果算术运算的输入有一个是空，则该算术表达式（+-*/）的结果是空，如果空值参与比较运算，运算结果看成unknown（既不是isnull,也不是isnotnull）
whereamountisnull/isnotnull/isunknown/isnot unknown
嵌套子查询：
in,notin
select*fromeightwherenamein（selectnamefromeight）;
集合的比较：
select*fromeightwhereshu>=some（selectshufromeight）; //shu只要大于或等于子查询结果中的一个就为真
select*fromeightwhereshu>= all（selectshufromeight）; //shu要大于或等于子查询结果中的全部才为真
select*fromeightwhereshu>=any（selectshufromeight）;//any同some
=some,=some,<>some等，all也类推
测试关系是否为空：
select*fromeightwhereshuexists（selectshufromeight）; //这种只有两种结果，相当于where true或者wherefalse; 子查询为空则为false
select*fromeightwhereshunotexists（selectshufromeight）;
测试关系是否存在重复元组：
select*fromeightwhereshu unique（selectshufromeight）;
select*fromeightwhereshunotunique（selectshufromeight）;
别名as的另一种用法：
（selectname,avg（balance）fromaccountgroupbyname）asbranch_avg（branch_name, avg_balance）; //对整个查询结果进行命名
selectmax（tot_balance）from（selectname,sum（balance）fromaccountgroupbybranch_name）asbranch_total（branch_name,tot_balance）;
//对整个查询结果进行命名，让后马上投入max计算。
临时视图：
with
deletefromaccountwherebalance<（selectavg（balance）fromaccount）; //<后面搜寻结果是一个数字
插入
insertintoaccountwhere...
更新
updateaccountsetbalance=balance*4wherebalance>=1000;
case结构:
更新顺序很重要，eight表名，shu<2，加5，不然加10
updateeightsetshu=
case
whenshu<2thenshu+ 5
elseshu+10
end;
创建数据类型：
createtypenewTypeasint（11）final;
断言：
createassertion<断言名>check<谓词>;
1-orderby
-1- 单列
-2- 多列 orderbycolumn1,column2,column3
-3-位置select语句中的最后一条子句
-4-orderby的列，不一定要选择哪些被检索，被显示的列，哪些没有出现的列也是可以的。
-5-支持用位置序号代替（必须是select清单中，所以不支持未检索的）列名orderby1,3;#列名不清楚，如果数据库表修改也会出乱问题
-6-指定排序方向desc（降序），asc（升序，默认）多列是要分别说明
orderbycolumn1desc,column2,column3desc; #column1降序z-a，column3降序，默认升序,desc必须放在后面。
-7-a与A是否排序先后，要根据不同数据库系统，可以手动修改数据库系统设置
selectnamefromeightorderbyid;//最后，产生查询结果后，在排序。
任意列。
2-where
操作符
= 等于
<>不等于
!
= 不等于 （有的不支持，access,access只支持<>）
<
<=
!
<
>
>=
!
> 不大于
between 值1and值2 #在指定的两个值之间
isnull 为空值
wherecolumn1<>10;
wherecolumn1 <>‘10’; #字符串的情况要加单引号
wherecolumn1between1and10;
wherecolumn1isnull;
组合where子语句
and（优先级高于or先执行）
or
wherecolumn1='1'orcolumn2='2'andcolumn3>=10; 小于10的也会出现，次序问题
where（column1='1'orcolumn2='2'）andcolumn3>=10; 这样才没有小于10的
in指定条件范围（效率快，而且可以在括号中嵌套select语句）
wherecolumn1in（'1','2'）; = wherecolumn1='1'orcolumn1='2';
notin的相反（mysql不支持，NOT EXISTS 代替）
where（id,num）>（2,3） 等价于whereid>2ornum>3;
3-通配符
%代表任何字符出现任意次数（access用*）
_代表任何字符出现一次，匹配单个字符
select*fromonewherenumlike '%3%';
select*fromonewherenumlike'_3';
select*fromonewherenumlike 12;
虽然num是int类型，同样可以用like，可以用字符串去匹配，但是要用上通配符，就一定是否字符串，一定要加上单引号，像%12是会报错的。
放过来char类型也可以直接like12;数据库会自动将12转换成字符串之后再进行比较的。
[]集合 （微软的才支持）
like'[js]'; //意思是字符串里包含有j或s的，只能匹配一个
like'[js]%'; //意思是字符串里以j或s开头的字符串。
[^js]是取相反的意思，[!
js]有些用!
;
4-拼接字符串
selectnum+':
（'+x+','+y+'）' aspointfromone; //多少数据库用+表示连接，如将点的序号加上坐标形成新的列， 2:
（232,332）
//有些数据库系统使用||,而不是+;
MySQL对上面的都不支持。
MySql使用concat函数
selectconcat（vend_name,'（',vend_country,'）'）fromvendors; //参数任意长。
5-别名
列别名
表别名：
from语句表名as别名，别名可以用于select，where，groupby等， or
注意：
oracle不支持as关键字，也可以说省略了as。
别名可以缩短sql语句，允许单条中多次使用
6-计算*/+-
selectid,quantity*priceasexpanded_pricefromitems; //这种不是交叉相乘，而是每一行中两个元素的相乘，结果行数不会发生变化
selectid,quantity*2.3asquantityfromitems; //as可以是表中某列名，也可以是原来的，不过命名，为没有名的列,或者列名为quantity*2.3
7-数据库函数：
不同数据库系统支持的函数可能不同
不区分大小写
字符串：
length（name）
upper
lower
rtrim（name）//去掉右边空格
ltrim（name）
left（name,length） 返回name左边的length个长度，length为阿拉伯数字
left（name,列名），长度可变，可以是对应元组的某一属性值。
列必须为数字列，长度过大会将其全部输出
right同上
日期和时间处理函数：
DATEPART（yy,列名） //取出年，mysql不支持
year（列名）, mysql支持的，day,month,hour等
to_char（）,to_number（） //mysql不支持
数值计算函数：
abs（列名）
cos（列名）
exp（列名）
pi（） 圆周
sin（）
sqrt（）
tan等
汇总函数:
avg（列名） //求平均，去除重复avg（distinct列名）
count（列） //忽略列名中的null行，count（*）整个表有多少记录，不忽略null，也可用distinct去除重复
max
min
sum等 去除重复sum（distinct列名）
过滤分组：
selectcount（*）asnum_count,numfromeightgroupbynum;//不忽略null，num相同的被分到同一组，让后对各个组进行count操作。
，count（num）,null不像上面的，而是会被算出，null作为一个分组。
selectcount（*）asnum_count,numfromeightgroupbynum,shu; //num和shu都相同才会被分到一起
groupby也可按选择的列数编号，groupby1,2
对分组进行过滤：
having
having支持所有where后面的操作符
where在分组前进行过滤，having在分组后进行过滤，where过滤行，where排除的行不会出现在分组中
orderby排序。
groupby很多是排序，可是不一定的。
orderby与groupby
orderby排序产出，所有过滤操作后，才计算，可以是任意列，即使列没有出现
groupby把相同的归类到一组，不能是任意列，列一定要出现
selectname,shu,count（*）ascount fromeightgroupbyshu;
11-子查询
情况一：
in的使用
selectafromeightwhereid=2;
a
1
2
selectbfromeightwhereain（1,2）;
b
3
4
组合上面的查找
selectbfromeightwhereain（selectafromeightwhereid=2）; //重内向外进行计算
注意：
作为子查询的select语句只能查询单列，多列将会报错
情况二：
填充计算列
selecta,（selectcount（*）fromsevenwhereeigth.id=seven.id）ascountfromeight;
一个a在seven中有多个记录，计算它的数目
12-联结表
关系表的设计就是要保证把信息分解成多个表，比如由多个供应商生产的多种产品，如果一个表表示，供应商会出现很多次，而如果把供应商和产品分开，用两个表，用id关联，会避免重复字符串的重复。
联结查询，不同于其他就是，from语句中为多个表，而且列名出现二义性时，要完全限定列名，通过（表名.列名）表示
联结查询，不能少了正确的where语句，没有where语句，返回的将是表1的每一行与表2的每一行配对，结果也就是笛卡尔积。
表的联结个数是有限制的，对于不同的DBMS系统有不同的个数限制
selecta,bfromt1,t2wheret1.id=t2.id; 等价于selecta,bfromt1innerjoint2ont1.id=t2.id; //on后面和where一样
自联结：
selectnamefromcustomerswherename=（selectnamefromcustomerswherecontact='Jim'） //用=类似于in，子查询慢
selectc1.namefromcustomersasc1,customersasc2wherec1.name=c2.nameandc2.contact='Jim' //同一个表多个命名，自联结快
自然联结：
selectC1.*,C2.name,C3.contactfromcustomersasC1,citysasC2,catasC3fromwhereC1.id=C2.idandC2.id=C3.id; //避免多列出现C1.*
外部联结：
在联结中包含哪些相关表中没有关联行的行
如：
列出所有产品及订购数量，及哪些没有人订购的产品
c表id,name d表id,count
上面的可以用
selectname,countfromc,dwherec.id*=d.id; //左外联，=*右外联， oracle使用（+）代替*
selectname,countfromcfullouterjoin donc.id=d.id;//全外连接，部分dbms支持
使用聚集函数的联结：
13-组合查询
将多个select语句的结果并在一起
在单个查询中从不同的表类似返回数据结构
对单个表执行多个查询，按单个查询返回数据
就是并运算，参加元素的元素要相容（类型可以不同）才行，多条select语句，n条select语句，n-1个union
union //自动取出重复的
unionall
以下mysql不支持
交 intersect（all）
差except
14-插入数据
插入完整的一行：
可以不用列出列名，直接insertinto表名values（值）;//值一定要全部一一对应，如果有自增的不妥
插入行的部分数据：
插入到哪里，就要指定对应的列名，一一对应
insertintoeight（name,id）values（'wew',12
升级会员 