SQL Collation 4

--==================== simple to delete and change ===========
--View all data for student tables
SELECT * FROM Studio
--Inserting a new student information
INSERT into Studio (St_name,st_sex,st_age,st_add,st_tel) VALUES ("Huanglan", 0, 36, ' Nanchong ', ' 13943943334 ')
--View all class data
SELECT * FROM class
--Add two data to the class table
INSERT into Class (Cl_class,cl_coding,cl_o_time,cl_remark) VALUES (' New electric Training class ', ' gxa-ncs-001 ', ' 2008-03-11 ', ' are very good friends ')
INSERT into Class (Cl_class,cl_coding,cl_o_time) values (' Aba teachers ' training class ', ' gxa-absz-001 ', ' 2008-03-11 ')

--The importance of updating the data conditions of a piece
Update class set cl_remark= ' is really good ' where cl_id=5
--importance of deleting a data condition
Delete from class where cl_id=7

--Modify column headings
Select cl_id as ' class primary key ', Cl_class as ' class name ' from class

Select name =st_name from Studio

--Using a text string
Select ' Name is: ', st_name from studio


The--============= condition is slightly complicated and the ============ is changed.
--mainly related to or and not between on like > < =!>!<! = <> () <= >= is Null was NOT NULL

--Query cl_id for all information greater than 1
SELECT * FROM class where cl_id>1

--Using or
SELECT * from class where cl_id<>10 or cl_class= ' hundred classes '

--Using and
SELECT * from class where cl_id<>10 and cl_class= ' hundred Classes '

--using like and%
SELECT * FROM class where cl_class like '% '
SELECT * FROM class where cl_remark like '% morning% '

--Using between
SELECT * from class where cl_id between 3 and 5

--use between mates on not
SELECT * from class where cl_id not between 3 and 5

--using is not NULL
SELECT * from class where cl_remark are NOT null

--using in
SELECT * from class where cl_class in (' Thousand Star class ', ' Hundred Second class ')


--================= using mathematical operators =====================
--mainly related to + = * \
--How many weeks are required for Java-related courses to be calculated according to 6 lessons per day, 5 days a week
Select ' Results ' =CO_NUM/5/6 from course where co_name in (' Java Basics ', ' Java Project Primer ')

--================== Use the summary function =====================
--related to count SUM AVG MAX MIN

--the number of courses with less than 50 hours of inquiry
Select COUNT (*) from course where co_num<50

--Find out how many classes are in all courses
Select SUM (co_num) from course

--Calculate the full lesson fee, assuming 50 dollars per lesson
Select SUM (co_num) *50 from course

--Check the course with the fewest hours
Select min (co_num) from course

--The most frequently-queried courses
Select Max (co_num) from course

--query average number of lessons per course
Select AVG (co_num) from course

--================= using Mathematical Functions ===========================
--including the ABS function of the absolute function, the PI function to find the positive value sin () function, the exponential function exp () and so on.

--Query the sine value of each course
Select sin (co_num) from course

--Check the absolute value of each course
Select ABS (Co_num) from course

--Query the number of lessons per class multiplied by pi, what is the specific use I do not know, anyway, it seems to be absolutely 8.5 poles can not be hit
Select Pi () *co_num from course

--Query the index of each course
Select exp (co_num) from course

--randomly returns 5 randomly generated numbers (returns the random float value between 0~1)
DECLARE @i tinyint
Set @i=1
While @i<=5
Begin

Select rand (@i) as ' randomly generated number ', @i as ' current value '

Set @[email protected]+1
End

--Returns a numeric expression and rounded to the specified length or precision-ROUND
Select Round (345.456,-1) as ' parameter is-1 '
, round (345.456,-2,1) as ' parameter is-2 '
, round (345.456,0) as ' parameter is 0 '
, round (345.456,1) as ' parameter is 1 '
, round (345.456,2) as ' parameter is 2 '


--================ using the Date function ====================
--day (), Month (), year ()--Returns the number of days, months, and years of the specified date;
Select Day (cl_s_time) as ' date ' from class--return days
Select ' Month ' =month (cl_s_time) from class--Return month
Select ' Year ' =year (Cl_s_time) from class--return year

--dateadd (datepart,number,date)--Increase the number of the given date type on the date;
Select DATEADD (yyyy,4,cl_s_time) as ' increased after 4 years ' from class--datepart-year
YY, yyyy
Select DATEADD (q,2,cl_s_time) as ' added after 2 quarter ' from class
--datepart-Quarterly
QQ, Q
Select DATEADD (mm,3,cl_s_time) as ' increased after March ' from class
--datepart-month
MM, M
--datepart-One day of the year
Dy, y
--datepart-Date
DD, D
--datepart-week
WK, WW
--datepart-Hour
hh
--datepart-min
MI, n
--datepart-Sec
SS, S
--datepart-MS
Ms

--datediff (DATEPART,DATE1,DATE2)--Gets the number difference of a given date type between two dates (the entire function result is date2-date1);
Select DateDiff (mm,cl_s_time,cl_o_time) as ' Total Month ' from class



--datepart (Datepart,date)-Returns the value (integer) of the specified date type, based on the given date;
In fact, this is equivalent to day, MONTH, and year functions
Select DATEPART (dd,cl_s_time) as ' date ' from class

--getdate ()--Returns the current date and time. When we design a database, it's often possible to use it as a default value.
Update class set Cl_s_time=getdate () where cl_id=6

SELECT * FROM class


Select CONVERT (varchar), GETDATE (), 0): 2006 10:57am
Select CONVERT (varchar), GETDATE (), 1): 05/16/06
Select CONVERT (varchar), GETDATE (), 2): 06.05.16
Select CONVERT (varchar), GETDATE (), 3): 16/05/06
Select CONVERT (varchar), GETDATE (), 4): 16.05.06
Select CONVERT (varchar), GETDATE (), 5): 16-05-06
Select CONVERT (varchar), GETDATE (), 6): 16 05 06
Select CONVERT (varchar), GETDATE (), 7): 05 16, 06
Select CONVERT (varchar), GETDATE (), 8): 10:57:46
Select CONVERT (varchar), GETDATE (), 9): 2006 10:57:46:827am
Select CONVERT (varchar), GETDATE (), 10): 05-16-06
Select CONVERT (varchar), GETDATE (), 11): 06/05/16
Select CONVERT (varchar), GETDATE (), 12): 060516
Select CONVERT (varchar), GETDATE (), 13): 16 05 2006 10:57:46:937
Select CONVERT (varchar), GETDATE (), 14): 10:57:46:967
Select CONVERT (varchar), GETDATE (), 20): 2006-05-16 10:57:47
Select CONVERT (varchar), GETDATE (), 21): 2006-05-16 10:57:47.157
Select CONVERT (varchar), GETDATE (): 05/16/06 10:57:47 AM
Select CONVERT (varchar), GETDATE (), 23): 2006-05-16
Select CONVERT (varchar), GETDATE (), 24): 10:57:47
Select CONVERT (varchar), GETDATE (), 25): 2006-05-16 10:57:47.250
Select CONVERT (varchar), GETDATE (), (+): 2006 10:57am
Select CONVERT (varchar), GETDATE (), 101): 05/16/2006
Select CONVERT (varchar), GETDATE (), 102): 2006.05.16
Select CONVERT (varchar), GETDATE (), 103): 16/05/2006
Select CONVERT (varchar), GETDATE (), 104): 16.05.2006
Select CONVERT (varchar), GETDATE (), 105): 16-05-2006
Select CONVERT (varchar), GETDATE (), 106): 16 05 2006
Select CONVERT (varchar), GETDATE (), 107): 05 16, 2006
Select CONVERT (varchar), GETDATE (), 108): 10:57:49
Select CONVERT (varchar), GETDATE (), 109): 2006 10:57:49:437am
Select CONVERT (varchar), GETDATE (), 110): 05-16-2006
Select CONVERT (varchar), GETDATE (), 111): 2006/05/16
Select CONVERT (varchar), GETDATE (), 112): 20060516
Select CONVERT (varchar), GETDATE (), 113): 16 05 2006 10:57:49:513
Select CONVERT (varchar), GETDATE (), 114): 10:57:49:547
Select CONVERT (varchar), GETDATE (), 120): 2006-05-16 10:57:49
Select CONVERT (varchar), GETDATE (), 121): 2006-05-16 10:57:49.700
Select CONVERT (varchar), GETDATE (), 126): 2006-05-16t10:57:49.827
Select CONVERT (varchar), GETDATE (), ():???? ?????? 1427 10:57:49:907am
Select CONVERT (varchar), GETDATE (), 131): 18/04/1427 10:57:49:920am

--============= using String Functions ====================
--String link operator
Select ' Results show ' = ' class name is: ' + Cl_class + ', class number is: ' + cl_coding from class
--Using the SUBSTRING function to intercept a string
Select substring (cl_class,1,4) from class

--Returns 3 characters from the left of a string
Select Left (cl_class,3) from class
--Similarly, return to the right
Select Right (cl_class,3) from class

--the number of characters in the return value
Select Len (cl_class) from class

--Replacement
Select Replace (cl_class, ' training ', ' hardening ') from class

--============== using System functions ==================
Select HOST_ID ()
--Return workstation identification number
Select HOST_NAME ()
--Returns the name of the computer to which the workstation is running

Select DB_ID ()
Select Db_name ()
Select object_id (' Stu_course_add ')
--Get the server ID of this server object by name
Select object_name (151671588)
--ditto opposite

--======= use other clauses =========
--First order by function-sort
SELECT * FROM Studio ORDER by St_name
--Multi-sorting conditions
SELECT * FROM Studio ORDER by St_name Desc,st_age Desc,st_sex DESC
--conditional, mostly looking at the position of conditions and clauses
SELECT * FROM studio where cl_id=1 order by st_name



--group BY clause function-Group statistics
Select cl_id as ' class number ', COUNT (*) as ' number ' from studio GROUP by cl_id
--according to the average age of dormitory statistics
Select ho_id as ' Dorm number ', AVG (st_age) as ' average age ' from studio GROUP by ho_id
--Multi-group
Select ho_id as ' Dorm number ', cl_id as ' class number ', Avg (st_age) as ' average age ' from studio GROUP by ho_id,cl_id
--conditional, mostly looking at the position of conditions and clauses
Select ho_id as ' Dorm number ', AVG (st_age) as ' average age ' from studio where cl_id=1 GROUP by ho_id


--Use the HAVING clause function-Specify the group or aggregated search criteria, usually used with the GROUP BY clause, and then progress the filter after the group query is complete
Select ho_id as ' Dorm number ', AVG (st_age) as ' average age ' from studio GROUP by HO_ID have avg (st_age) >35
--Multi-criteria
Select ho_id as ' Dorm number ', AVG (st_age) as ' average age ' from studio GROUP by HO_ID have avg (st_age) >35 and Ho_id>2


--=========== Joint Query =============
--a query that uses a UNION clause is called a union query: combines more than two query result sets into a single result set that includes all the row data in all sets
--Let's try to unite multiple queries.
SELECT * FROM studio where cl_id=1

Union
SELECT * FROM studio where ho_id=1


Union
SELECT * FROM studio where st_age>=30

--Below we continue to use the above examples to increase the effect of all to see

SELECT * FROM studio where cl_id=1

UNION ALL
SELECT * FROM studio where ho_id=1


UNION ALL
SELECT * FROM studio where st_age>=30

--and then continue to use, to add a sort to him

SELECT * FROM studio where cl_id=1

UNION ALL
SELECT * FROM studio where ho_id=1


UNION ALL
SELECT * FROM studio where st_age>=30

ORDER BY st_id

--=========== Connection Query ==================
--Connection query, function--Query the data in multiple tables to put together
--Inner connection: using comparison operator =>< The comparison of some databases between tables, and lists the data rows in those tables that match the join criteria
The equivalent connection, of course, is the equal sign, the problem, which also asked
SELECT * FROM studio inner JOIN class on studio.cl_id = class.cl_id
--Indicate the columns to be queried (also known as natural connections), and sort
Select st_id as ' number ', st_name as ' student name ', Cl_class as ' class name ' from studio inner JOIN class on studio.cl_id = class.cl_id Order by st_id
--Using Table aliases
Select St.st_name as ' student name ', st.cl_id as ' class number ', Cl.cl_class as ' class name ' from studio as St inner join class as CL on st.cl_id = cl.cl_id

--unequal connection, the problem is very funny, since the use of equal value of the connection, then not equal to you say should be non-equals?
--Let's connect to the third table and see how it drops.
Select St.st_name as ' student name ', st.cl_id as ' class number ', Cl.cl_class as ' class name ', ho.ho_coding as ' dorm number '
From studio as St inner join class as Cl

On st.cl_id = cl.cl_id


Inner JOIN hostel as Ho

On st.ho_id=ho.ho_id
--Let's give him another condition to look at.
--where st.cl_id>2
--give him another sort.
--order by st.st_id


--External connection:
--Unlike an inner join, a row is returned when the inner join has at least one row in the same two table that matches the join condition, and the outer join returns rows that meet any criteria
-his watch has a master-slave, he uses each row in the main table to match from the table, unlike the inline, he does not discard rows without matching, but fills null to the result set

--Left outer connection
Select st.st_id as ' student number ', st.st_name as ' student name ', cl.cl_id as ' class number ', Cl_class as ' class name '
From studio as St left outer join class as CL

On st.cl_id=cl.cl_id
where cl.cl_id>2

--Multi-table
Select tka.te_co_id as ' Course schedule number '

, cl.cl_id as ' class number ', Cl.cl_class as ' class name '

, co.co_id as ' Course ID ', co.co_name as ' course name ', Co.co_num as ' class number '

, te.te_name as ' teacher name '
From Te_kc_ap as Tka left outer join class as CL

On tka.cl_id=cl.cl_id
Left OUTER JOIN

Course as Co

On tka.co_id=co.co_id
Left OUTER JOIN

Teacher as Te

On tka.te_id=te.te_id

--==================== right outer link ======================
Select st.st_id as ' student number ', st.st_name as ' student name ', cl.cl_id as ' class number ', Cl_class as ' class name '
From studio as St right outer join class as CL

On st.cl_id=cl.cl_id
where cl.cl_id>2

--Multi-table
Select tka.te_co_id as ' Course schedule number '

, cl.cl_id as ' class number ', Cl.cl_class as ' class name '

, co.co_id as ' Course ID ', co.co_name as ' course name ', Co.co_num as ' class number '

, te.te_name as ' teacher name '
From Te_kc_ap as Tka
Right outer join class as CL

On
tka.cl_id=cl.cl_id
Right outer join teacher Te

On
tka.te_id=te.te_id
Right OUTER JOIN course CO

On
tka.co_id=co.co_id


--======== Fully connected ==============
Select st.st_id as ' student number ', st.st_name as ' student name ', cl.cl_id as ' class number ', Cl_class as ' class name '
From studio as St full outer join class as CL

On st.cl_id=cl.cl_id
ORDER BY st.st_id

--Multi-table
Select tka.te_co_id as ' Course schedule number '

, cl.cl_id as ' class number ', Cl.cl_class as ' class name '

, co.co_id as ' Course ID ', co.co_name as ' course name ', Co.co_num as ' class number '

, te.te_name as ' teacher name '
From Te_kc_ap as Tka
Full outer JOIN class as CL

On
tka.cl_id=cl.cl_id
Full outer join teacher Te

On
tka.te_id=te.te_id
Full OUTER JOIN Course CO

On
tka.co_id=co.co_id


--========== Cross Connection ================
--This method returns the Cartesian product of all data rows in two tables without a WHERE clause (rows in the first table multiplied by rows in the second table)
--cross-check with students and class tables
Select St_name,cl_class from Studio Cross Join class
Select St_name,cl_class from Studio,class

Select St_name,cl_class from Studio Cross Join class

--========= self-Connection = = = Learn Cloud network-days to wear-[url]www.ixueyun.com[/url]
-----------------Create a table on a temporary basis-------------
CREATE TABLE Zone (

ID int primary key identity (.) NOT NULL,

Z_zone varchar (30),

z_id int references zone (ID))
--Let's see if we can give you a default value.



SELECT * FROM Zone

Insert into zone (Z_zone) VALUES (' Beijing ')
Insert into zone (z_zone,z_id) VALUES (' Beijing ', 4)
Insert into zone (Z_zone) VALUES (' Sichuan ')
Insert into zone (z_zone,z_id) VALUES (' Chengdu ', 6)
Insert into zone (z_zone,z_id) VALUES (' Mianyang ', 6)
Insert into zone (Z_zone) VALUES (' Jiangsu ')
Insert into zone (z_zone,z_id) VALUES (' Nanjing ', 10)
Insert into zone (z_zone,z_id) VALUES (' Suzhou ', 10)
Insert into zone (z_zone,z_id) VALUES (' Wuxi ', 10)
Insert into zone (z_zone,z_id) VALUES (' Changzhou ', 10)

----------------------------------------------
--look at the general usefulness of the self-connection
Select A.z_zone,b.z_zone from zone as a inner join zone as B on a.z_id=b.id
--Extended application
Select B.z_zone,count (a.z_zone) as ' jurisdictions ' from zone as a inner join zone as B on A.z_id=b.id GROUP by B.z_zone


--Simply connect yourself, in other words, connect to the same table.
Select A.st_name,a.st_add,b.st_name,b.st_add from studio as a inner join studio as B on A.st_add=b.st_add
--we find that someone equals ourselves, so add a condition
Select A.st_name,a.st_add,b.st_name,b.st_add from studio as a inner join studio as B on A.st_add=b.st_add and a.st_name!=b . st_name


--====== Sub-query ============
---Insert another SQL statement into a SQL statement to teach the nesting query, and the SQL statement that is embedded in it is queried by the lake and the Hu person. is an additional way to handle multiple table operations
--subquery is also called internal query, and the SELECT statement containing the subquery is the external query, the subquery itself can include one or more sub-queries, can also set any number of subqueries

--Subquery using in
SELECT * FROM Studio where cl_id in (select cl_id from class where cl_id>2)
--Using Not in
SELECT * FROM studio where cl_id not in (select cl_id from class where cl_id>2)


--Subquery using the comparison operator--any means that any value in the subquery represents each value in the subquery
--Use any
SELECT * FROM class where Cl_id>any (select cl_id from studio where st_age>30)
--Use all
SELECT * FROM class where Cl_id>all (select cl_id from studio where st_age>30)


--============ a paged SQL statement ========
Select Top 3 * from Studio
where St_id>all (select Top 3 st_id from Studio ORDER by st_id)
ORDER BY st_id




--using exists, when the keyword introduces a subquery is basically a test of whether the data is present once
--We query those who are in the class is numbered 1
SELECT * FROM studio where exists (select cl_id from class where studio.cl_id=class.cl_id and Class.cl_id=1)
--Using NOT EXISTS
SELECT * from studio where NOT EXISTS (SELECT * from class where studio.cl_id=class.cl_id and class.cl_id=1) Order by st_id


--Generate a new table based on a query
Select St_name into Class_3 from studio where cl_id=3

--BULK INSERT data into a table
Insert INTO Class_3 select St_name from studio where cl_id=4



-----------------------SQL Programming--------------
declare @max int;
--Declare a variable @max
Set @max = 1;
--Assigning values to variable @max

While @max <10
--if @max is less than 10, enter the loop
Begin

Set @[email protected]+1;--to @max plus 1 per cycle

Print @max;
--Print the value of the current @max

End
print ' finally has finished circulating ';

SQL Collation 4