数据库简易sql

最新推荐文章于 2021-09-27 19:35:19 发布

dmz_2888

最新推荐文章于 2021-09-27 19:35:19 发布

阅读量140

点赞数

文章标签：数据库 java

原文链接：http://www.cnblogs.com/zhoupinghua/p/5033448.html

版权

F学习网站
http://www.w3school.com.cn/sql/sql_datatypes.asp

1.DISTINCT 关键词 DISTINCT 用于返回唯一不同的值。

SELECT DISTINCT
T.MEMBER_ID
FROM

(SELECT
L_MEMBER_ORGANIZATION_REL.ORG_ID,L_MEMBER.MEMBER_ID
from
L_MEMBER ,
L_MEMBER_ORGANIZATION_REL
WHERE
L_MEMBER.MEMBER_ID IN (SELECT MEMBER_ID
FROM L_MEMBER_ROLE_REL where ROLE_ID = 503)
AND
L_MEMBER_ORGANIZATION_REL.MEMBER_ID = L_MEMBER.MEMBER_ID ) T
where
T.ORG_ID = (select case super_id when 1 then org_id else super_id END from
L_ORGANIZATION where org_id =#{orgId} );

2.复杂条件查询
// 从L_ORGANIZATION表里面查询，如果super_id为1那么then org_id（输出org_id），else 输出super_id
select case super_id when 1 then org_id else super_id END from L_ORGANIZATION where org_id = 3 ;
//部门和子部门表
select * from L_ORGANIZATION order by super_id

3.parameterType 参数类型，即传入sql语句的参数类型，从哪里来！
resultType 返回值类型，可以是一种类型，比如java.lang.long也可设置一个dto装载它
<select id="getDocId" parameterType="com.linkstec.ib.project.dto.PR100427InDto"
resultType="java.lang.Long">
SELECT doc.DOC_ID FROM T_DOC doc
LEFT JOIN T_DOC_RELATION doc_relation
ON
doc.DOC_ID = doc_relation.DOC_ID
WHERE 1=1
<if test="projectId != null">
and doc_relation.OBJ_ID = #{projectId}
</if>
<if test="nodeId!=null">
AND doc_relation.PJWF_NODE_ID=#{nodeId}
</if>
<if test="taskId!=null">
AND doc.DOC_SUBMODEL1_ID=#{taskId}
</if>
and doc_relation.RELATION_TYPE=114
and doc.ins_id is null
</select>

4.模糊查询例子
<if test="shortName != null"> and a.CUST_SEC_NAME LIKE '%'+#{shortName}+'%' </if>

5.select 。。 as。。语句别名....给他定义一个新的名称
select m.member_id as memberId,m.member_name as memberName from L_MEMBER m

in关键字
DELETE FROM LMSP_LBPM_INSSTEP WHERE STEP_ID IN (196792,196793)

7.between关键字一个范围内
select * from lmsp_lbpm_pins where pi_id between 196792 and 196799

8.like关键字模糊查询
UPDATE T_PJ_PROJECTINFO SET project_status=3 WHERE project_name LIKE '%回退测试3%'

8.1 update 关键字
UPDATE t
SET t.PJWF_NODE_ID = b.xmsbpjwf
FROM
T_PJ_FORMTEMPLATE t,
(
SELECT
p1.pjwf_node_id AS xcczpjwf,
P2.PJWF_NODE_ID AS xmsbpjwf
FROM
(
SELECT
*
FROM
T_PJ_WORKFLOW
WHERE
pjwf_node_name = '现场承做'
) p1,
(
SELECT
*
FROM
T_PJ_WORKFLOW
WHERE
pjwf_node_name = '项目申报'
) p2
WHERE
p1.pj_id = p2.pj_id
AND p1.pj_id IN (
SELECT
project_id
FROM
T_PJ_PROJECTINFO
WHERE
project_type_id = 314
)
) b
WHERE
t.FIELD_LABLE = '签署推荐挂牌并持续督导协议日期'
AND t.PJWF_NODE_ID = b.xcczpjwf

9.order by 排序 [desc 降序 asc 升序]
SELECT * from LMSP_MOM_MSG ORDER BY rec_gen_time DESC

10.top关键字
SELECT TOP 10 * from LMSP_MOM_MSG ORDER BY rec_gen_time DESC

11.数据库表备份
SELECT * INTO LMSP_LBPM_HISTTASK_201506180934 FROM LMSP_LBPM_HISTTASK;

12.插入数据
INSERT INTO LMSP_LBPM_INSPROP (REL_ID,REL_TYPE,PROP_KEY,PROP_VALUE,VALUE_TYPE,INS_ID,REC_GEN_TIME,REC_UPD_TIME)
SELECT REL_ID,REL_TYPE,PROP_KEY,PROP_VALUE,VALUE_TYPE,INS_ID,REC_GEN_TIME,REC_UPD_TIME
FROM LMSP_LBPM_HISTINSPROP WHERE INS_ID=160855

13.sql语句函数
avg（平均） count（计数） max（最大值） min（最小值） sum（总和）

14.count和distinct组合使用，计算出来的数字可能小于表内数据条数，列里面相同两条数据被归为一条数据
select count（distinct name）from students where name is not null

15.group by 分组
select store_name，sum（sales）from students group by store_name

16.having关键字（当需要对函数的值设定条件时，我们不能使用where只能用having）
select store_name，sum（sales）from students group by store_name having sum(sales)>1000;

17.表格别名和栏位标题
select S.name,sum(sales) as sum_sales from students S group by S.name

18.LEFT JOIN 左连接
RIGHT JOIN 右连接
full join 全连接
inner join 内连接

19 union 关键字
将两个sql语句的结果合并起来，（相同的结果只会显示一栏，和distinct效果类似）
select pi_id from lmsp_lbpm_pins
union
select pi_id from LMSP_LBPM_HISTINS

nuion all关键字
union all 会将每一笔符合条件的资料全部显示出来

20 intersect关键字，将两个sql语句结果组合起来做交集 union是合集

21.minus关键字
两个sql语句结合起来查询，最后结果显示第一张表数据（减去）第二张表内与第一张表相同的数据

22.表格分为栏位（column）和列位（row）每一列代表一笔资料，每一栏代表一个字段
create table students(id Long,name char(50),birth_date date,city char(50,country char(50)))

23.ALTER 改变表结构
mysql
一.加一个栏位
alter table 表名 add 栏位名栏位类型 alter table students add family Long
二.改变栏位名称
把names改成name alter table students change names name char(50)
三.改变栏位字符类型 alter table students modify name int
现在我们希望改变 "Persons" 表中 "Birthday" 列的数据类型。
ALTER TABLE Persons
ALTER COLUMN Birthday year
四.删除一个栏位
比如删除name这个属性 alter table students drop name
五.主键设定
create table students(id Long primary key,name char(50),birth_date date,city char(50,country char(50)))
六.改变表现有结构来设定主键
alter table students add primary key(id)（主键需确认一定不能为空）

sql server
增加一个栏位
ALTER TABLE LMSP_LBPM_INSPROP_201506171109 ADD birthday DATE
改变栏位字符类型
ALTER TABLE Persons
ALTER COLUMN Birthday year
删除表中栏位
ALTER TABLE LMSP_LBPM_INSPROP_201506171109 DROP COLUMN birthday

24.外键添加·
mysql
create table order(order_id integer,order_date date,customer_id integer,amount double,primary key(order_id),foreign key(customer_id) reference customer(id))
oracle
create table order(order_id integer primary key,order_date date,customer_id integer reference customer(id),amount double)
sqlserver
create table order(order_id integer primary key,order_date date,customer_id integer reference customer(id),amount double)

改变表结构添加外键
alter table order add foreign key (customer_id) reference customer(id)

25.drop table 表名删除表
truncate table 表名清除表中资料

26.insert into 插入
一.
insert into "表格名"（"栏位一"， "栏位二".....）values （"值一" ，"值二".....）
二.
INSERT INTO LMSP_LBPM_INSPROP (REL_ID,REL_TYPE,PROP_KEY,PROP_VALUE,VALUE_TYPE,INS_ID,REC_GEN_TIME,REC_UPD_TIME)
SELECT REL_ID,REL_TYPE,PROP_KEY,PROP_VALUE,VALUE_TYPE,INS_ID,REC_GEN_TIME,REC_UPD_TIME
FROM LMSP_LBPM_HISTINSPROP WHERE INS_ID=160855

27.时间日期和字符串之间的转换
日期转换为字符串
100 或者 0 mon dd yyyy hh:miAM （或者 PM）
101 mm/dd/yy
102 yy.mm.dd
103 dd/mm/yy
104 dd.mm.yy
105 dd-mm-yy
106 dd mon yy
107 Mon dd, yy
108 hh:mm:ss
109 或者 9 mon dd yyyy hh:mi:ss:mmmAM（或者 PM）
110 mm-dd-yy
111 yy/mm/dd
112 yymmdd
113 或者 13 dd mon yyyy hh:mm:ss:mmm(24h)
114 hh:mi:ss:mmm(24h)
120 或者 20 yyyy-mm-dd hh:mi:ss(24h) 2015-06-19 09:58:24
121 或者 21 yyyy-mm-dd hh:mi:ss.mmm(24h) 2015-06-19 09:57:48.923
126 yyyy-mm-ddThh:mm:ss.mmm（没有空格）
130 dd mon yyyy hh:mi:ss:mmmAM
131 dd/mm/yy hh:mi:ss:mmmAM

CONVERT(VARCHAR(19),GETDATE()) 不写，默认为 06 19 2015 9:56AM 格式
CONVERT(VARCHAR(10),GETDATE(),110)
CONVERT(VARCHAR(11),GETDATE(),106)
CONVERT(VARCHAR(24),GETDATE(),113)

28.date
mysql
NOW() 返回当前的日期和时间
CURDATE() 返回当前的日期
CURTIME() 返回当前的时间
DATE() 提取日期或日期/时间表达式的日期部分
EXTRACT() 返回日期/时间按的单独部分
DATE_ADD() 给日期添加指定的时间间隔
DATE_SUB() 从日期减去指定的时间间隔
DATEDIFF() 返回两个日期之间的天数
DATE_FORMAT() 用不同的格式显示日期/时间
sqlserver
GETDATE() 返回当前日期和时间
DATEPART() 返回日期/时间的单独部分
DATEADD() 在日期中添加或减去指定的时间间隔
DATEDIFF() 返回两个日期之间的时间
CONVERT() 用不同的格式显示日期/时间

29.表中索引创建
索引
您可以在表中创建索引，以便更加快速高效地查询数据。用户无法看到索引，它们只能被用来加速搜索/查询。

在表上创建一个简单的索引。允许使用重复的值：
CREATE INDEX index_name
ON table_name (column_name)

在表上创建一个唯一的索引。唯一的索引意味着两个行不能拥有相同的索引值。
CREATE UNIQUE INDEX index_name
ON table_name (column_name)

本例会创建一个简单的索引，名为 "PersonIndex"，在 Person 表的 LastName 列：
CREATE INDEX PersonIndex
ON Person (LastName)

如果您希望以降序索引某个列中的值，您可以在列名称之后添加保留字 DESC：
CREATE INDEX PersonIndex
ON Person (LastName DESC)

假如您希望索引不止一个列，您可以在括号中列出这些列的名称，用逗号隔开：
CREATE INDEX PersonIndex
ON Person (LastName, FirstName)

30.null 如果表中的某个列是可选的，那么我们可以在不向该列添加值的情况下插入新记录或更新已有的记录。
这意味着该字段将以 NULL 值保存。
使用 IS NULL 和 IS NOT NULL 操作符来对null进行比较

31.any和all关键字 //all表示子查询中的所有值
Select number,name
from jbqk
where number=any( //any表示子查询中的某个值
select number
from sle_course
Where score>=95)

32.视图 view
一.创建视图
Create view 计算机系 as
SELECT * FROM LMSP_LBPM_INSTASK WHERE ins_id=160855

二.删除视图
DROP VIEW <视图名>;

33.授权
如果指定WITH GRANT OPTION子句，则获得某种权限的用户可以把这种权限在授予其他用户。如没有指定该子句，获得授权的用户将不能传播权限。
例1：将查询jbqk表的权限授予用户sa。
GRANT SELECT ON TABLE jbqk TO sa;
例2：将在sle_course表上进行UPDATE的权限授予用户s1，并允许他传播该权限
GRANT UPDATE ON TABLE sle_course TO s1 WITH GRANT OPTION;
S1获得该权限后，他可以在将此权限授予s2。
GRANT UPDATE ON TABLE sle_course TO s2;
例3：DBA将在数据库pubs中建立基本表的权限授予s3。
GRANT CREATTAB ON DATABASE pubs TO s3;
回收权限
说明：当涉及多个用户传播权限时，收回上级用户某权限的同时也收回所有下级的该权限。
例4：将用户sa查询jbqk表的权限收回。
REVOKE SELECT ON TABLE jbqk FROM sa;
例5：将用户s2更新sle_course表的权限收回，同时s2的更新权也被收回。
REVOKE UPDATE ON TABLE sle_course FROM s2;

34.表结构复制
1.只复制表结构
create table 新表 select * from 旧表 where 1=2
2.复制
create table 新表 select * from 旧表
3.复制
create table xinbiao like jiubiao

35.多条数据，
STUFF(character_expression, start, length, character_expression)
1、作用
删除指定长度的字符，并在指定的起点处插入另一组字符。
2、语法
STUFF ( character_expression , start , length ,character_expression )
3、示例
以下示例在第一个字符串 abcdef 中删除从第 2 个位置（字符 b）开始的三个字符，然后在删除的起始位置插入第二个字符串，从而创建并返回一个字符串
SELECT STUFF('abcdef', 2, 3, 'ijklmn')
GO
下面是结果集
aijklmnef

SELECT
contract_id,
STUFF((
SELECT
',' + CONVERT (
VARCHAR,
tr2.contract_to_pay_time,111
)
FROM
T_CONTRACT_RECORD tr2
WHERE
tr1.contract_id = tr2.contract_id FOR xml path('')
), 1, 1, '')allAmount
FROM
T_CONTRACT_RECORD tr1 GROUP BY contract_id

复杂一点的：
SELECT
contract_id,
STUFF(
(
SELECT
',' + CONVERT (VARCHAR, tr2.dea)
FROM
(
SELECT
a.contract_id,
(
CONVERT (
VARCHAR,
a.contract_to_pay_amount
) + '(' + CONVERT (
VARCHAR,
a.contract_to_pay_time,
111
) + ')'
) dea
FROM
T_CONTRACT_RECORD a
) tr2
WHERE
tr1.contract_id = tr2.contract_id FOR xml path ('')
),
1,
1,
''
) allAmount
FROM
T_CONTRACT_RECORD tr1
GROUP BY
contract_id

复查一点2：
SELECT
a.id AS id,
a.project_id AS projectId,
a.cxdd_state AS cxddState,
b.project_num AS projectNum,
c.org_name AS orgName,
d.pj_type_name AS typeName,
allmem.ww AS allmem
FROM
T_CXDD_TASK a
LEFT JOIN T_PJ_PROJECTINFO b ON a.PROJECT_ID = b.PROJECT_ID
LEFT JOIN L_ORGANIZATION c ON b.PROJECT_ORG_ID = c.org_id
LEFT JOIN T_PJ_TYPE d ON b.project_type_id = d.pj_type_id
LEFT JOIN T_CXDD_TASK_MEMBER e ON e.CXDD_TASK_ID = a.ID
LEFT JOIN (
SELECT
i.id,
STUFF(
(
SELECT
',' + CONVERT (VARCHAR, h.member_name)
FROM
T_CXDD_TASK f,
T_CXDD_TASK_MEMBER g,
L_MEMBER h
WHERE
g.CXDD_TASK_ID = f.ID
AND g.member_id = h.member_id
AND i.id = f.id FOR xml path ('')
),
1,
1,
''
) ww
FROM
T_CXDD_TASK i
GROUP BY
i.id
) allmem ON a.id = allmem.id

转载于:https://www.cnblogs.com/zhoupinghua/p/5033448.html

dmz_2888

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
数据库简易sql

F学习网站http://www.w3school.com.cn/sql/sql_datatypes.asp1.DISTINCT 关键词 DISTINCT 用于返回唯一不同的值。SELECT DISTINCT T.MEMBER_IDFROM (SELECT L_MEMBER_ORGANIZATION_REL.ORG_ID,L_MEMBER.MEMBER_ID from ...
复制链接

扫一扫