Knowledge required for database development _ MySQL

Common SQL example: knowledge required for database development bitsCN.com -- View all data in 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 ("Huang Lanqi", 13943943334, 'nanchong ', '123 ')
-- View all data of class
Select * from class
-- Add two pieces 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 ', '2017-03-11', 'are good friends ')
Insert into class (cl_class, cl_coding, cl_o_time) values ('Aba normal training class', 'gxa-ABSZ-001 ', '2017-03-11 ')
-- Importance of updating a data condition
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 the column title
Select cl_id as 'class primary key', cl_class as 'class name' from class
Select name = st_name from studio
-- Use text strings
Select 'name: ', st_name from studio
-- ================== Query, add, delete, modify, ======================
-- Mainly involves or and not between in like ><=!> ! <! = <> () <=> = Is null is not null
-- Query all information with cl_id greater than 1
Select * from class where cl_id> 1
-- Use or
Select * from class where cl_id <> 10 or cl_class = 'baijie first class'
-- Use and
Select * from class where cl_id <> 10 and cl_class = 'baijie first class'
-- Use like and %
Select * from class where cl_class like 'baijie %'
Select * from class where cl_remark like '% am %'
-- Use
Select * from class where cl_id between 3 and 5
-- Use between with not
Select * from class where cl_id not between 3 and 5
-- Use is not null
Select * from class where cl_remark is not null
-- Use in
Select * from class where cl_class in ('first class of Qixing ', second class of 'baijie ')
-- ========================== Use mathematical operators ======================= ===
-- Mainly involves + = */
-- Query the number of weeks required for Java-related courses, which is calculated based on the number of 5 days per week and 6 courses per day.
Select 'Result '= co_num/5/6 from course where co_name in ('Java basics', 'Java project start ')
-- ============================== Use the aggregate function ========================= ======
-- COUNT SUM AVG MAX MIN
-- Query the total number of courses with less than 50 course hours
Select count (*) from course where co_num <50
-- Query the total number of lessons in all courses
Select sum (co_num) from course
-- Calculate the total course time fee. assume 50 RMB for each course.
Select sum (co_num) * 50 from course
-- Query the courses with the least class hours
Select min (co_num) from course
-- Query the courses with the most class hours
Select max (co_num) from course
-- Query the average number of lessons per course
Select avg (co_num) from course
-- ========================== Use mathematical functions ======================= ============
-- Includes the ABS function, PI () function, SIN () function, and EXP () function.
-- Query the sine of each course
Select sin (co_num) from course
-- Query the absolute value of each course
Select abs (co_num) from course
-- Query the number of hours of each course multiplied by the circumference rate. I don't know anything about it. it seems that it cannot be reached by 8.5.
Select pi () * co_num from course
-- Query the index of each course
Select exp (co_num) from course
-- Returns 5 randomly generated numbers (0 ~ Random float value between 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,-) 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 the date function ============================= =
-- DAY (), MONTH (), YEAR ()-returns the number of days, months, and years of the specified date;
Select day (cl_s_time) as 'day' from class -- return day
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 given date types on the date;
Select dateadd (yyyy, 4, cl_s_time) as 'add 4 years later 'from class -- datepart-year
Yy, yyyy
Select dateadd (q, 2, cl_s_time) as 'add 2 quarters later 'from class
-- Datepart-quarter
Qq and q
Select dateadd (mm, 3, cl_s_time) as 'adds the 'From class after January 1, March
-- Datepart-month
Mm, m
-- Datepart-one day of each year
Dy, y
-- Datepart-date
Dd, d
-- Datepart-week
Wk, ww
-- Datepart-Hour
Hh
-- Datepart-minutes
Mi, n
-- Datepart-seconds
Ss, s
-- Datepart-millisecond
MS
-- DATEDIFF (datepart, date1, date2) -- gets the number difference of the given date type between two dates (the entire function result is a date2-date1 );
Select datediff (mm, cl_s_time, cl_o_time) as 'total duration' from class
-- Datepart (datepart, date)-returns a value of the specified date type (integer) based on the given date );
-- In fact, this is equivalent to the DAY, MONTH, and YEAR functions.
Select datepart (dd, cl_s_time) as 'date' from class
-- GETDATE () -- returns the current date and time. When designing a database, we usually use it as the default value.
Update class set cl_s_time = getdate () where cl_id = 6
Select * from class
Select CONVERT (varchar (100), GETDATE (), 0): 05 16 2006 AM
Select CONVERT (varchar (100), GETDATE (), 1): 05/16/06
Select CONVERT (varchar (100), GETDATE (), 2): 06.05.16
Select CONVERT (varchar (100), GETDATE (), 3): 16/05/06
Select CONVERT (varchar (100), GETDATE (), 4): 16.05.06
Select CONVERT (varchar (100), GETDATE (), 5): 16-05-06
Select CONVERT (varchar (100), GETDATE (), 6): 16 05 06
Select CONVERT (varchar (100), GETDATE (), 7): 05 16, 06
Select CONVERT (varchar (100), GETDATE (), 8): 10:57:46
Select CONVERT (varchar (100), GETDATE (), 9): 05 16 2006 10: 57: 46: 827AM
Select CONVERT (varchar (100), GETDATE (), 10): 05-16-06
Select CONVERT (varchar (100), GETDATE (), 11): 06/05/16
Select CONVERT (varchar (100), GETDATE (), 12): 060516
Select CONVERT (varchar (100), GETDATE (), 13): 16 05 2006 10: 57: 46: 937
Select CONVERT (varchar (100), GETDATE (), 14): 10: 57: 46: 967
Select CONVERT (varchar (100), GETDATE (), 20): 10:57:47
Select CONVERT (varchar (100), GETDATE (), 21): 10:57:47. 157
Select CONVERT (varchar (100), GETDATE (), 22): 05/16/06 10:57:47 AM
Select CONVERT (varchar (100), GETDATE (), 23 ):
Select CONVERT (varchar (100), GETDATE (), 24): 10:57:47
Select CONVERT (varchar (100), GETDATE (), 25): 10:57:47. 250
Select CONVERT (varchar (100), GETDATE (), 100): 05 16 2006 AM
Select CONVERT (varchar (100), GETDATE (), 101): 05/16/2006
Select CONVERT (varchar (100), GETDATE (), 102): 2006.05.16
Select CONVERT (varchar (100), GETDATE (), 103): 16/05/2006
Select CONVERT (varchar (100), GETDATE (), 104): 16.05.2006
Select CONVERT (varchar (100), GETDATE (), 105): 16-05-2006
Select CONVERT (varchar (100), GETDATE (), 106): 16 05 2006
Select CONVERT (varchar (100), GETDATE (), 107): 05 16,200 6
Select CONVERT (varchar (100), GETDATE (), 108): 10:57:49
Select CONVERT (varchar (100), GETDATE (), 109): 05 16 2006 10: 57: 49: 437AM
Select CONVERT (varchar (100), GETDATE (), 110): 05-16-2006
Select CONVERT (varchar (100), GETDATE (), 111): 2006/05/16
Select CONVERT (varchar (100), GETDATE (), 112): 20060516
Select CONVERT (varchar (100), GETDATE (), 113): 16 05 2006 10: 57: 49: 513
Select CONVERT (varchar (100), GETDATE (), 114): 10: 57: 49: 547
Select CONVERT (varchar (100), GETDATE (), 120): 10:57:49
Select CONVERT (varchar (100), GETDATE (), 121): 10:57:49. 700
Select CONVERT (varchar (100), GETDATE (), 126): 2006-05-16T10: 57: 49.827
Select CONVERT (varchar (100), GETDATE (), 130): 18 ???? ?????? 1427 10: 57: 49: 907AM
Select CONVERT (varchar (100), GETDATE (), 131): 18/04/1427 10: 57: 49: 920AM
-- ================ Use a string function =====================================
-- String link operator
The result of the select statement is '='. the class name is '+ cl_class +', and the class number is '+ cl_coding from class.
-- Use the SUBSTRING function to intercept a string
Select substring (cl_class, 1, 4) from class
-- Returns 3 characters from the left of the string.
Select left (cl_class, 3) from class
-- Similarly, return
Select right (cl_class, 3) from class
-- Number of characters returned
Select len (cl_class) from class
-- Replace
Select replace (cl_class, 'training', 'harden ') from class
-- =====================Use system functions ===========================
Select host_id ()
-- Return workstation ID
Select host_name ()
-- Return the name of the computer on which the workstation runs
Select db_id ()
Select db_name ()
Select object_id ('Stu _ course_add ')
-- Obtain the server ID of the server object by name
Select object_name (151671588)
-- The opposite is true.
-- ======= Xueyun.com-tianfengheng-[url] www. ixueyun. co m [/url] ==== use other clauses ==== xueyun.com-Tianbang-[url] www.ixueyun.com [/url] ======
-- Order by function-sort
Select * from studio order by st_name
-- Multiple sorting conditions
Select * from studio order by st_name DESC, st_age DESC, st_sex DESC
-- Conditional mainly refers to the position of the condition and clause.
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 'students 'from studio group by cl_id
-- Average age statistics by dormitory
Select ho_id as 'dormitory 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 refers to the position of the condition and clause.
Select ho_id as 'dormitory number', avg (st_age) as 'average age' from studio where cl_id = 1 group by ho_id
-- Use the having clause function-specify search conditions for a group or aggregation, which is usually used with the group by clause. After grouping query is completed, filter progress.
Select ho_id as 'dormitory number', avg (st_age) as 'average age' from studio group by ho_id having avg (st_age)> 35
-- Multiple conditions
Select ho_id as 'dormitory number', avg (st_age) as 'average age' from studio group by ho_id having avg (st_age)> 35 and ho_id> 2
-- ============= Joint query ======= xueyun.com-Tianbang-[url] www.ixueyun.com [/url] ==== ===
-- A query using the union clause is called a joint query. function: combines two or more query result sets into a single result set, which includes all row data in all sets.
-- Next 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
-- Next we will continue to use the example above to add All to see 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 sort it.
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 data from multiple tables together
-- Inner join: use the comparison operator ><... to compare some databases between tables and list the data rows matching the connection conditions in these tables.
-- Equi join, of course, is to use the equal sign. if it is wrong, you should also ask
Select * from studio inner join class on studio. cl_id = class. cl_id
-- Specify and sort the columns to be queried (also called natural connections)
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
-- Use table alias
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 connections. this is a funny question. Since equal signs are used for equivalent connections, shouldn't they be non-equals?
-- Next we will connect to the third table to see how it works.
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
-- Let's add another condition for him.
-- Where st. cl_id> 2
-- Sort him again
-- Order by st. st_id
-- External connection:
-- Different from the internal connection, the internal connection must have at least one row in two tables that meet the connection conditions. The outer join will return rows that meet any conditions.
-- His table has the master-slave relationship. he uses each row in the master table to match the rows in the table. Unlike the internal connection, he does not discard the rows that do not match, instead, it is filled with null to the slave 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
-- Multi-table
Select tka. te_co_id as 'course schedule no'
, 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 'number of Lessons'
, Te. te_name as 'Instructor 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 no'
, 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 'number of Lessons'
, Te. te_name as 'Instructor 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
-- ========= Full 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 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 no'
, 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 'number of Lessons'
, Te. te_name as 'Instructor 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 the where clause (the rows in the first table are multiplied by the rows in the second table)
-- Use the student and class tables for cross-query
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 at the beginning -------------
Create table zone (
Id int primary key identity (1, 1) not null,
Z_zone varchar (30 ),
Z_id int references zone (id ))
-- Can you give 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)
----------------------------------------------
-- Take a look at the general use of 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 'number of jurisdictions 'from zone as a inner join zone as B on a. z_id = B. id group by B. z_zone
-- Simply put, you can connect yourself 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
-- If we find that someone is equal to ourselves, 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
-- === Xueyun.com-Tianbang-[url] www.ixueyun.com [/url] === subquery ========
-- Insert another SQL statement into an SQL statement to teach you how to set up a query. the entered SQL statement is called a subquery. Is an additional method for processing multi-table operations
-- A subquery is also called an internal query, while a Select statement containing a subquery is considered as an external query. a subquery can include one or more subqueries, or include any number of subqueries.
-- Subquery using in
Select * from studio where cl_id in (select cl_id from class where cl_id> 2)
-- Use 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 indicates any value in the subquery. all indicates 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

-- Use exists. when this keyword is used to introduce a subquery, it basically tests whether the data exists.
-- We can query the classes where the persons are located. they 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 the query
Select st_name into class_3 from studio where cl_id = 3
-- Batch 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;
-- Assign values to the variable @ max
While @ max <10
-- If @ max is less than 10, it enters the loop.
Begin
Set @ max = @ max + 1; -- add 1 to @ max every cycle
Print @ max;
-- Print the current @ max value
End
Print 'finally finished looping '; bitsCN.com