Common SQL Example database development needs knowledge _MYSQL

--View all the data for the student table
SELECT * FROM Studio
--Insert 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 piece of 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 ', ' All 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 a data condition
Update class set cl_remark= ' really nice ' where cl_id=5
--The importance of deleting a single data condition
Delete from class where cl_id=7
--Modifying column headings
Select cl_id as ' class primary key ', Cl_class as ' class name ' from class
Select name =st_name from Studio
--Using text strings
Select ' Name is: ', st_name from studio
--============= condition slightly complex point of check and delete change ==============
--mainly related to or not between in like > < =!>!<!= <> () <= >= is null.
--Query for all information cl_id greater than 1
SELECT * FROM class where cl_id>1
--Use or
SELECT * from class where cl_id<>10 or cl_class= ' Hundred Jie class '
--Use and
SELECT * from class where cl_id<>10 and Cl_class= ' Hundred Jie class '
--use like and%
SELECT * FROM class where cl_class like ' percent Jay '
SELECT * FROM class where cl_remark like '% a.m. '
--Using between
SELECT * from class where cl_id between 3 and 5
--using between with not
SELECT * from class where cl_id not between 3 and 5
--use is not NULL
SELECT * from class where Cl_remark was NOT null
--Use in
SELECT * from class where cl_class in (' Thousand Star class ', ' Hundred Second class ')
--================= using mathematical operators ======================
--mainly related to + = * \
--Query Java related courses How many weeks to go according to 5 days a week, 6 classes a day to calculate
Select ' Results ' =CO_NUM/5/6 from course where co_name in (' Java Basics ', ' Java Project Primer ')
--================== Using summary functions ========================
--involving Count SUM AVG MAX MIN
--How many doors are there in a course with less than 50 hours of inquiry?
Select COUNT (*) from course where co_num<50
--How many hours are there for all courses?
Select SUM (co_num) from course
--Calculate the whole class fee, suppose 50 dollars per lesson
Select SUM (co_num) *50 from course
--The minimum course of inquiry
Select min (co_num) from course
--most of the courses in the inquiry class
Select Max (co_num) from course
--average number of hours per course
Select AVG (co_num) from course
--================= Use Mathematical Functions =============================
Including the ABS function of the absolute value function, the Pi of Pi (), the positive-Xuan value sin () function, the exponential function exp () and so on.
--inquire about the sine of each course
Select sin (co_num) from course
--Search the absolute value of each course
Select ABS (Co_num) from course
--Query the number of hours per class multiplied by pi, what is the use of what I do not know, anyway it seems to be absolutely 8.5 poles can not hit
Select Pi () *co_num from course
--Check the index for 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 @i=@i+1
End
--Returns a numeric expression 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 '
--================ Use date functions ======================
--day (), MONTH (), year ()--Returns the number of days, months, years of the specified date;
Select Day (cl_s_time) as ' days ' from class--return to 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)--increases the number of a given date type on a date;
Select DATEADD (yyyy,4,cl_s_time) as ' increased 4 years later ' from class--datepart-year
YY, yyyy
Select DATEADD (q,2,cl_s_time) as ' Add 2 quarter after ' from class
--datepart-Quarterly
QQ, Q
Select DATEADD (mm,3,cl_s_time) as ' increase 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-Millisecond
Ms
--datediff (DATEPART,DATE1,DATE2)-Gets the difference in the number of date types given between two dates (the entire function results in date2-date1);
Select DateDiff (mm,cl_s_time,cl_o_time) as ' total continuous month ' from class
--datepart (datepart,date)--Returns the value of the specified date type (integer) on a 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 usually possible to use it as a default.
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 (MB), 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 (), 130):???? ?????? 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 strings
Select substring (cl_class,1,4) from class
--Returns 3 characters from the left side of the string
Select Left (cl_class,3) from class
--Similarly, return to the right
Select Right (cl_class,3) from class
--Returns the number of characters for the value
Select Len (cl_class) from class
--Replace
Select Replace (cl_class, ' training ', ' hardening ') from class
--============== using System functions ====================
Select HOST_ID ()
--Return to workstation identification number
Select HOST_NAME ()
--Returns the name of the computer on 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 the opposite
--======= Learn Cloud Net-days to wear-[url]www.ixueyun.co m[/url]=== use other clauses = = Learn cloud Net-day blow wear-[url]www.ixueyun.com[/url]=========
--First order by function-sort
SELECT * FROM Studio ORDER by St_name
--Multiple sorting criteria
SELECT * FROM Studio ORDER by St_name Desc,st_age Desc,st_sex DESC
--conditional, mainly to look at the position of conditions and clauses
SELECT * FROM studio where cl_id=1 order by st_name
--group BY clause function-grouping statistics
Select cl_id as ' class number ', COUNT (*) as ' number ' from studio GROUP by cl_id
--average age according to 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 ' dormitory number ', cl_id as ' class number ', Avg (st_age) as ' average age ' from studio GROUP by ho_id,cl_id
--conditional, mainly to look 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 search criteria for a group or aggregation, usually used with the GROUP BY clause, and then progress the filter after the grouped query is completed
Select ho_id as ' Dorm number ', AVG (st_age) as ' average age ' from studio GROUP by HO_ID have avg (st_age) >35
--Multiple conditions
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 Inquiry ======= Cloud Network-days to wear-[url]www.ixueyun.com[/url]======
--a query using the Union clause is called a federated query, which combines more than two query result sets into a single result set that includes all row data in all sets
--Here we try to combine 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 example, to increase the all look at the effect
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
--Continue to use it, and 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 ==================
--Join query, function-query the data from multiple tables together
--INNER JOIN: Using comparison operator =>&lt ..... For comparison of some databases between tables, and lists rows of data in these tables that match the join condition
The equivalent connection, of course, is to use the equal sign, the problem, which also have to ask
SELECT * FROM studio inner JOIN class on studio.cl_id = class.cl_id
--Indicate the column to query (also known as natural connection), 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, this question is very funny, since the use of equal equals is the equivalent of the connection, then do not equal to the equivalent of you should not mean it?
--Let's connect to the third table and see what's going on here.
Select St.st_name as ' student name ', st.cl_id as ' class number ', Cl.cl_class as ' class name ', ho.ho_coding as ' dormitory 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
We'll give him one more condition to look at.
--where st.cl_id>2
--and give him a sort.
--order by st.st_id
--Outer Joins:
--Unlike an inner join, an inner join returns rows when at least one row with the two tables conforms to 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, and in contrast to the inner, he will not discard rows that do not match, but instead populate null to the result set
--left OUTER join
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
--Multiple tables
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
--Multiple tables
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
--Multiple tables
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 Connect ================
-This method returns the Cartesian product of all data rows in the two tables without the WHERE clause (the rows in the first table multiplied by the 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 = = =
-----------------Create a table temporarily-------------
CREATE TABLE Zone (
ID int primary key identity (1,1) not NULL,
Z_zone varchar (30),
z_id int references zone (ID))
--Let's try it, can we give you a default value here?
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
--The extended application
Select B.z_zone,count (A.z_zone) as ' jurisdiction number ' 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 someone equal to ourselves, then 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
--==== Cloud Network--[url]www.ixueyun.com[/url]==-sky-piercing inquiry ============
--Insert another SQL statement in one SQL statement to teach the set query, and the embedded SQL statement is called the Lake of inquiry. is an additional way to handle multiple table operations
--subquery also called internal query, and the SELECT statement containing the subquery is prudential for external query, the subquery itself can include one or more subqueries, you can set up any number of subqueries
--Subqueries using in
SELECT * FROM studio where cl_id into (select cl_id from class where cl_id>2)
--Use NOT in
SELECT * FROM studio where cl_id isn't in (select cl_id from class where cl_id>2)
--a subquery using the comparison operator--any means that any value in the subquery all 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 paging 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, it basically tests the existence of the data once
--we're looking for the classes that are numbered 1.
SELECT * FROM studio where exists (select cl_id from class where studio.cl_id=class.cl_id and Class.cl_id=1)
--Use 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
--insert data into a table in bulk
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;
--Assign a value to a variable @max
While @max <10
--If the @max is less than 10, enter the loop
Begin
Set @max = @max +1;--@max plus 1 per loop
Print @max;
--Print the current @max value
End
print ' finally cycle finished ';