DQL(数据查询)
WITH
WITH子句用于指定临时命名的公用表达式,在单条语句(SELECT、INSERT、UPDATE、DELETE)的语句执行范围内定义
SELECT … FROM …
查询信息并且可以为指定列新起列名(列名 as 新列名,列名 = 新列名,列名 新列名)
INTO
将查询到的结果into到一个新表中
WHERE
逻辑运算符(NOT, AND, OR)
NOT:取反;AND:逻辑与;OR:逻辑或。
比较运算符
(=, <>, !=, >, >=, !=,
LIKE关键字
用于模糊查询,通配符包括%、_、[]、[^]%:后面可以跟零个或多个字符
_:匹配任意单个字符
[]:查询一定范围内单个字符,包括两端数据
[^]:表示不在一定范围内的单个字符,包括两端数据
BETWEEN关键字
is(not) null 关键字
在where子句中,需要用is (not) null 判断空值,不能使用=判断空值
in关键字
用来指定列表搜索的条件
ALL SOME ANY关键字
三者都作用于比较运算符和子查询之间,一般和嵌套查询一起用,Some和any等效。ALL是大于最大者,ANY是小于最小者
exists关键字
用于子查询,指定行是否存在
Group by子句
用于对表进行分组
Having
用于指定组或聚合的搜索条件,只能与select一起使用
ORDER BY
在select返回的结果中排序;只有在指定了Top情况下,才能在视图、内联函数、派生表和子查询中起作用
COMPUTE
生成合计,出现在结果列
DISTINCT关键字
从select结果中去掉重复记录
TOP关键字
用于指定显示的行数
DDL(数据定义)
数据表操作
创建表
create table student([列级完整性约束条件],…);
添加字段
alter table student add (stu_age number(2));
删除字段
alter table student drop column stu_age;
修改字段名
alter table student rename column stu_age to age;
修改字段的数据类型
alter table 表名称 change 字段名称 字段名称 字段类型 [是否允许非空];
alter table student modify 字段名称 字段类型 [是否允许为空];
重命名表
rename student to stu;
删除表
drop table stu;
回收站操作
还原表格
flashback table student to before drop;
还原表格并且重命名
flashback table student to before drop rename to stu2;
删除回收站
purge table s;
清空回收站
purge recyclebin;
删除不经过回收站,直接删除
drop table stu purge;
DML(数据操纵)
在数据库中所有的字符串类型,必须是单引,不能使用双引
插入数据
INTERT INTO 表名(列名1,列名2, …) VALUES(列值1, 列值2, …);
删除数据
DELETE FROM 表名 [WHERE 条件]
TRUNCATE TABLE 表名(先删除该表,再create该表,而且无法回滚)
修改数据
UPDATE 表名 SET 列名1=列值1, 列名2=列值2, … [WHERE 条件]
创建索引
ALTER TABLE 表名 ADD 索引类型 (unique,primary key,fulltext,index)[索引名](字段名)
CREATE INDEX(或者UNIQUE INDEX,只能是这两种) [index_name] ON ]table_name([字段名])];(可用于对表增加普通索引或UNIQUE索引)
删除索引
drop index [index_name] on [table_name] ;
alter table [table_name] drop index [index_name] ;
alter table [table_name] drop primary key ;
DCL(数据控制)
系统管理功能
创建用户
CREATE USER [email protected] IDENTIFIED BY ‘密码’; 用户只能在指定的IP地址上登录
CREATE USER [email protected]%’ IDENTIFIED BY ‘密码’; 用户可以在任意IP地址上登录
赋予权限
grant connect,resource to superdrew;
grant dba to superdrew;
GRANT 权限1, … , 权限n ON 数据库.* TO [email protected]
撤销权限
revoke connect,resource from superdrew;
REVOKE 权限1, … , 权限n ON 数据库.* FROM [email protected];
查看权限
修改密码
alter user superdrew identified by 123456;
删除用户
drop user superdrew;
常用示例
基础创建数据库CREATE DATABASE
删除数据库drop database
创建新表create table (col1 type1 [not null] [primary key],col2 type2 [not null],..)
增加一个列Alter table add column
添加主键Alter table add primary key(col)
创建索引create [unique] index on (col….)
创建视图create view as select
删除视图drop view
选择: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%’ —like的语法很精妙,查资料!
排序:select * from table1 order by field1,field2 [desc]
总数:select count 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
子查询(表名1:a 表名2:b):select a,b,c from a where a IN (select d from b ) 或者: select a,b,c from a where a IN (1,2,3)
显示文章、提交人和最后回复时间:select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b
外连接查询(表名1:a 表名2:b):select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c
在线视图查询(表名1:a ):select * from (SELECT a,b,c FROM a) T where t.a > 1;
between的用法,between限制查询数据范围时包括了边界值,not between不包括:select * from table1 where time between time1 and time2;select a,b,c, from table1 where a not between 数值1 and 数值2
in 的使用方法:select * from table1 where a [not] in (‘值1’,’值2’,’值4’,’值6’)
两张关联表,删除主表中已经在副表中没有的信息:delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 )
四表联查问题:select * from a left inner join b on a.a=b.b right inner join c on a.a=c.c inner join d on a.a=d.d where
日程安排提前五分钟提醒:select * from 日程安排 where datediff(‘minute’,f开始时间,getdate())>5
一条sql 语句搞定数据库分页:select top 10 b.* from (select top 20 主键字段,排序字段 from 表名 order by 排序字段 desc) a,表名 b where b.主键字段 = a.主键字段 order by a.排序字段
前10条记录:select top 10 * form table1 where 范围
选择在每一组b值相同的数据中对应的a最大的记录的所有信息:select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b)
包括所有在 TableA 中但不在 TableB和TableC 中的行并消除所有重复行而派生出一个结果表:(select a from tableA ) except (select a from tableB) except (select a from tableC)
随机取出10条数据:select top 10 * from tablename order by newid()
随机选择记录:select newid()
删除重复记录:delete from tablename where id not in (select max(id) from tablename group by col1,col2,…);
select distinct * into temp from tablename
delete from tablename
insert into tablename select * from temp
列出数据库里所有的表名:select name from sysobjects where type=’U’ // U代表用户
列出表里的所有的列名:select name from syscolumns where id=object_id(‘TableName’)
列示type、vender、pcs字段,以type字段排列,case可以方便地实现多重选择,类似select 中的case。:select type,sum(case vender when ‘A’ then pcs else 0 end),sum(case vender when ‘C’ then pcs else 0 end),sum(case vender when ‘B’ then pcs else 0 end) FROM tablename group by type显示结果:
初始化表table1:TRUNCATE TABLE table1
选择从10到15的记录:select top 5 * from (select top 15 * from table order by id asc) table_别名 order by id desc
提升
定义模式:mysql的数据库有三种sql_mode模式,分别为ANSI,TRADITIONAL,STRICT_TRANS_TABLES模式,通过SET @@sql_mode=ANSI;设置ANSI:宽松模式,对插入数据进行校验,如果不符合定义类型或长度,对数据类型调整或截断保存,报warning警告。
TRADITIONAL:严格模式,当向mysql数据库插入数据时,进行数据的严格校验,保证错误数据不能插入,报error错误。用于事务时,会进行事务的回滚。
STRICT_TRANS_TABLES:严格模式,进行数据的严格校验,错误数据不能插入,报error错误。1
2
3
4
5
6
7
8
9
10SET @@sql_mode=ANSI;
CREATE TABLE testtable(
NAME VARCHAR(2),
PASSWORD VARCHAR(2)
);
INSERT INTO testtable VALUES('11111111','222222222222');
#会报警告,截取字符但不会影响插入