Database Simple SQL

F Learning Website
Http://www.w3school.com.cn/sql/sql_datatypes.asp

1.DISTINCT keyword DISTINCT is used to return only different values.

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 if 1 then org_id else super_id END from
L_organization where org_id =#{orgid});

2. Query for complex conditions
Query from L_organization table, if super_id is 1 then org_id (output org_id), else output super_id
Select Case super_id If 1 then org_id else super_id END from l_organization where org_id = 3;
Department and Sub-department table
SELECT * from L_organization ORDER by super_id

The 3.parameterType parameter type, which is the parameter type of the incoming SQL statement, from where!
Resulttype returns a value type, which can be a type, such as Java.lang.long can also set a DTO to load it
<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. Fuzzy Query Example
<if test= "ShortName! = null" > and a.cust_sec_name like '% ' +#{shortname}+ '% ' </if>

5.select.. As.. Statement aliases .... Define a new name for him.
Select m.member_id as memberid,m.member_name as membername from L_member m

6.

in keyword
DELETE from Lmsp_lbpm_insstep WHERE step_id in (196792,196793)

7.between keyword in a range
SELECT * from Lmsp_lbpm_pins where pi_id between 196792 and 196799

8.like keyword fuzzy query
UPDATE t_pj_projectinfo SET project_status=3 WHERE project_name like '% fallback test 3% '

8.1 Update keyword
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 = ' Live Contractors '
) P1,
(
SELECT
*
From
T_pj_workflow
WHERE
Pjwf_node_name = ' Project declaration '
) 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 = ' signature of the recommended listing and continuous steering agreement date '
and t.pjwf_node_id = B.XCCZPJWF

9.order by sort [desc descending asc Ascending]
SELECT * from Lmsp_mom_msg ORDER by Rec_gen_time DESC

10.top keywords
SELECT TOP * from Lmsp_mom_msg ORDER by Rec_gen_time DESC

11. Database table Backup
SELECT * into lmsp_lbpm_histtask_201506180934 from Lmsp_lbpm_histtask;

12. Inserting data
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 statement function
AVG (average) count (count) max (max) min (minimum) sum (sum)

14.count and distinct combination, the calculated number may be less than the number of data bars in the table, the column inside the same two data is classified as a single piece of data
Select COUNT (distinct name) from students where name was NOT NULL

15.group by grouping
Select Store_name,sum (sales) from students group by Store_name

16.having keywords (when you need to set conditions on the value of a function, we cannot use where only the having)
Select Store_name,sum (sales) from students group by Store_name have sum (sales) >1000;

17. Table Aliases and field headings
Select S.name,sum (Sales) as sum_sales from students S GROUP by S.name

18.LEFT Join left Connection
Right Join
Full join fully connected
Inner joins inside joins

The Union keyword
Merges the results of two SQL statements (the same result will only show one column, similar to the distinct effect)
Select pi_id from Lmsp_lbpm_pins
Union
Select pi_id from Lmsp_lbpm_histins

Nuion all keyword
Union ALL displays all the qualifying data.

The Intersect keyword, combining two SQL statement results together to make the intersection union is a collection

21.minus keywords
Two SQL statements together query, the final result shows the first table data (minus) the second table in the same data as the first table

22. The table is divided into columns (column) and yours faithfully (row) Each column represents a piece of information, each of which represents a field
CREATE TABLE students (ID long,name char (), birth_date date,city char (50,country char (50)))

23.ALTER changing table structure
Mysql
A. Add a field
ALTER TABLE name add field type ALTER TABLE students add family Long
Two. Change the field name
Convert names to name ALTER TABLE students change names name Char (50)
Three. Change the field character type ALTER TABLE students modify name int
Now we want to change the data type of the "Birthday" column in the "Persons" table.
ALTER TABLE Persons
ALTER COLUMN Birthday Year
Four. Delete a field
For example, remove the name of the property ALTER TABLE students drop name
Five. Primary key setting
CREATE TABLE students (ID Long primary Key,name char (), birth_date date,city char (50,country char (50)))
Six. Change the performance has the structure to set the primary key
ALTER TABLE students add primary key (ID) (the primary key needs to be confirmed must not be empty)

SQL Server
Add a field
ALTER TABLE lmsp_lbpm_insprop_201506171109 ADD birthday DATE
Change field Character type
ALTER TABLE Persons
ALTER COLUMN Birthday Year
Delete a column in a table
ALTER TABLE lmsp_lbpm_insprop_201506171109 DROP COLUMN Birthday

24. Foreign Key Addition ·
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 )
Sql server
CREATE table order (order_id Integer primary key,order_date date,customer_id integer reference customer (ID), amount double )

Change table structure Add foreign key
ALTER TABLE Order add foreign key (CUSTOMER_ID) reference customer (ID)

25.drop Table table name Delete tables
TRUNCATE TABLE table name clears data from tables

26.insert into insertion
One.
INSERT into "table name" ("Field One", "column Two" ...). VALUES ("Value one", "Value two" ...). ）
Two.
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. Conversion between the time date and the string
Date converted to string
100 or 0 Mon dd yyyy hh:miam (or PM)
101 Mm/dd/yy
102 YY.MM.DD
103 Dd/mm/yy
104 Dd.mm.yy
Dd-mm-yy
106 dd Mon yy
107 Mon DD, YY
108 Hh:mm:ss
109 or 9 Mon dd yyyy hh:mi:ss:mmmAM (or PM)
Mm-dd-yy
111 YY/MM/DD
Yymmdd
113 or DD Mon yyyy hh:mm:ss:mmm (24h)
Hh:mi:ss:mmm (24h)
120 or Yyyy-mm-dd Hh:mi:ss (24h) 2015-06-19 09:58:24
121 or Yyyy-mm-dd hh:mi:ss.mmm (24h) 2015-06-19 09:57:48.923
126 yyyy-mm-ddthh:mm:ss.mmm (no spaces)
DD Mon yyyy hh:mi:ss:mmmAM
131 Dd/mm/yy Hh:mi:ss:mmmAM

Convert (VARCHAR), GETDATE () not written, default to 9:56AM format
CONVERT (VARCHAR), GETDATE (), 110)
CONVERT (VARCHAR (one), GETDATE (), 106)
CONVERT (VARCHAR), GETDATE (), 113)

28.date
Mysql
Now () returns the current date and time
Curdate () returns the current date
Curtime () returns the current time
Date () Extract date or date/time expression
EXTRACT () returns a separate part of the date/time Press
Date_add () Adds a specified time interval to a date
Date_sub () Subtracts a specified time interval from a date
DATEDIFF () returns the number of days between two dates
Date_format () Displays the date/time in a different format
Sql server
GETDATE () returns the current date and time
DATEPART () returns a separate part of the date/time
DATEADD () Adds or subtracts a specified time interval from a date
DATEDIFF () returns a time between two dates
CONVERT () Displays the date/time in a different format

29. Index creation in tables
Index
You can create indexes in a table to query data more quickly and efficiently. Users cannot see the index, they can only be used to speed up search/query.

Create a simple index on the table. Allowed to use duplicate values:
CREATE INDEX index_name
On table_name (COLUMN_NAME)

Creates a unique index on the table. A unique index means that two rows cannot have the same index value.
CREATE UNIQUE INDEX index_name
On table_name (COLUMN_NAME)

This session creates a simple index, named "Personindex," in the LastName column of the person table:
CREATE INDEX Personindex
On person (LastName)

If you want to index values in a column in descending order, you can add the reserved word DESC after the column name:
CREATE INDEX Personindex
On person (LastName DESC)

If you want to index more than one column, you can list the names of these columns in parentheses, separated by commas:
CREATE INDEX Personindex
On person (LastName, FirstName)

30.null if a column in the table is optional, we can insert a new record or update an existing record without adding a value to the column.
This means that the field will be saved with a NULL value.
Use the is null and is not NULL operator to compare NULL

The 31.any and all keyword//all represent all values in a subquery
Select Number,name
From Jbqk
Where Number=any (//any represents a value in a subquery
Select number
From Sle_course
Where score>=95)

32. Views View
I. Creating a View
Create View computer system as
SELECT * from Lmsp_lbpm_instask WHERE ins_id=160855

Two. Delete a view
DROP View < view name >;

33. Authorization
If the WITH GRANT OPTION clause is specified, the user who obtains some kind of permission can grant this permission to another user. If this clause is not specified, the authorized user will not be able to propagate the permissions.
Example 1: Grant the user SA the permission to query the Jbqk table.
GRANT SELECT on TABLE jbqk to SA;
Example 2: Grant the user S1 permission to update on the Sle_course table and allow him to propagate the permission
Grant UPDATE on TABLE sle_course to S1 with GRANT OPTION;
After S1 obtains this permission, he can grant this permission to S2.
GRANT UPDATE on TABLE sle_course to S2;
Example 3:DBA will grant S3 the permission to establish a base table in the database pubs.
GRANT Creattab on the DATABASE pubs to S3;
Reclaim Permissions
Note: When more than one user is involved in propagating permissions, the permission of the parent user is retracted and all subordinates are retracted.
Example 4: Revoke the permissions of the User SA query jbqk table.
REVOKE SELECT on TABLE jbqk from SA;
Example 5: The User S2 update the Sle_course table permission to recover, while the S2 update rights are also recalled.
REVOKE UPDATE on TABLE sle_course from S2;

34. Table Structure Replication
1. Copy only the table structure
CREATE table new Table select * from old table where 1=2
2. Copying
CREATE table new Table select * from old table
3. Copying
CREATE TABLE Xinbiao like Jiubiao

35. More than one data,
STUFF (character_expression, start, length, character_expression)
1. function
Deletes a character of the specified length and inserts another set of characters at the specified starting point.
2. Grammar
STUFF (character_expression, start, length, character_expression)
3. Example
The following example removes the three characters from the 2nd position (character B) in the first string abcdef, and then inserts a second string at the beginning of the deletion, creating and returning a string
SELECT STUFF (' abcdef ', 2, 3, ' IJKLMN ')
GO
Here is the result set
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

A bit more complex:
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

Review point 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

Database Simple SQL