SQL Statement Review: insert, update, delete, select

I have been learning about SQL Server recently. I took a test yesterday. It's really not easy. In particular, some complex queries. It makes me dizzy. However, it is also because your knowledge is not solid enough. So today I reviewed the addition, deletion, modification, and query of the T-SQL statement. Many of them are indeed forgotten. Write the result. Don't forget it in the future.

-- SQL Statement Review -- 1. insert statement -- 1. Insert a single row of data into students (sname, saddress, ssex, semail) values ('name', 'address ', 'Gender ', 'email address ')

-- 2. Insert multiple rows of data and insert existing data in other tables into the database after selection -- put all the rows in the sname, saddress, and semail columns corresponding to student, -- insert the name, address, and email in the tongxulu table into the insert into students (name, address, email) Select sname, saddress, semail from students

-- 3. Insert multiple rows of data to the newly created table. The table cannot exist beforehand. -- create the tongxuelu table and create three fields sname, saddress, semail, and the ID column studentid -- and insert all the three data items in the student table into the new table. Select students. sname, students. saddress, students. semail, identity (INT, 1, 1) as studentid -- identifies the column name into tongxulu from students

-- 4: insert students (sname, sgrade, ssex) Select 'zhangke', Union select 'zhangwe', Union select 'hangzhou ', union select 'wang xiaojuan ',

-- 2. Update data update students set ssex = 1, sgrade = sgrade + 1 where ssex = 0

-- 3. delect data -- 1: Delete the specified data: delete from students where ssex = 0

-- 2. Delete all data -- the first method is delete from students -- the second method, which is faster than Delete, fewer system resources and transaction log resources are used-but cannot be used for tables with foreign key constraints (truncate table students)

-- Additional -- 1: add 5 points to the score of all male students. Update sscore set score = score + 5 where studentid (select scode from students where ssex = 1)

-- 2: only copy the table structure of students. Do not copy the data Select Identity (INT) as infoid, sname, saddress, sgrade, semail, ssex into studnetinfo from students where (1 <0)

-- 4. query -- (1) General Data Query -- 1. query all data rows and columns select * from students

-- 2. query some data rows and columns -- Query only trainees whose addresses are in Xinxiang, Henan Province, and only select scode, sname, saddress from students where saddress = 'henan Xinxiang'

-- 3. Use the column name select scode as student ID, sname as student name, saddress as student address from students where saddress <> 'henan Xinxiang 'in the query'

-- 4. The new column select firstname + '.' + lastname as 'name' from employee is obtained by merging the segments.

-- 5 another way to rename a column, use the equal sign select 'name' = firstname + '.' + lastname from employee

-- 6: Query empty rows. If the value is not null, select sname from students where semail is null.

-- 7. Use the constant column in the query -- the query output has an additional column "school name". All data in this column is "HEBEI xinlong" Select name = sname, address = saddress, 'hebei xinlong' as school name

-- 8: query the maximum number of rows returned. Use the top keyword select top 5 sname, saddress, from students where sex = 0.

-- 9, extract data by a certain percentage, use the percent keyword select top 20 percent sname, saddress from students where sex = 0

-- (2) query sorting -- 1. sort by column name ASC in ascending order. ASC can be omitted. -- reduce all scores by 10% and add 5 points, then, select studentid as student number is arranged according to the passing score (score * 0.9 + 5) as overall score from Score where (score * 0.9 + 5)> 60 order by score

-- 2. Use the order by column name DESC in descending order -- query the author table and employee table, merge all names found, and sort select au_name + 'in descending order of names '. '+ au_fname as EMP from authors Union select fname + '. '+ lname as EMP from employee order by EMP DESC

-- 3: sort by multiple fields. -- Select studentid as student ID based on the student's score, score as score from Score where score> 60 order by score, courseid

-- (3) use the function -- 1, string function -- (1) charindex in the query: used to find the starting position of a specified string in another string select charindex ('accp ', 'My ACCP Course', 1) -- Return: 4

-- (2) Len: return the string length passed to it select Len ('SQL') -- Return: 3

-- (3) Upper: converts the string passed to it into a capital select upper ('SQL Server') -- Return: SQL Server

-- (4) ltrim: clear spaces on the left -- (5) rtrim: clear spaces on the right

-- (6) Right: returns a specified number of characters from the right of the string, select right ('buyer/seller. turson', 3) -- Return: turson

-- (7) Replace: replace a string with the character select Replace ('yang K', 'k', 'lan') -- Return: Yang Lan

-- (8) stuff: In a string, delete the specified characters and insert a new string select stuff ('abcdefg', 2, 3, 'My music My World') -- Return: A my music my world EFG

-- 2, date function -- (1) getdate: Get the current date

-- (2) dateadd: add the specified value to the date after the specified date part select dateadd (mm, 4, '2017/99 ') -- returns 05/01/99 in the current date format

-- (3) datediff: the difference between two specified dates select datediff (mm, '192/99', '192/99') -- Return: 4

-- (4) datename: Select datename (DW, '2014/1/123') as a string of the specified date in the date -- Return: Saturday

-- (5) datepart: Select datepart (day, '2014/1/80') in the integer form of the specified date part in the date -- Return: 15

-- 3, mathematical function -- (1) ABS: Take the absolute value of the numerical expression select ABS (-45) -- Return: 45

-- (2) ceiling: Select ceiling (43.5), the smallest integer of the expression, taking the value greater than or equal to the specified value. -- Return: 44

-- (3) Floor: Take a value smaller than or equal to the specified value. Select floor (43.5), the maximum integer of the expression, returns: 43.

-- (4) power: Select power (5, 2) -- Return: 25

-- (5) round: Rounding to the specified precision select round (43.543, 1) -- Return: 43.5

-- (6) Sign: return 1 for positive numbers, return-1 for negative numbers, return 0 select sign (-45) for 0 -- Return:-1

-- (7) SQRT: take the square root of the floating point expression select SQRT (9) -- Return: 3

-- 4, system function -- (1) convert: convert data type select convert (varchar (5), 12345) -- Return: String 12345

-- (2) CURRENT_USER: returns the current user name select CURRENT_USER

-- (3) datalength: returns the number of bytes select datalength ('China' a consortium ') -- returns: 9

-- (4) host_name: returns the name of the computer you are logged on to. Select host_name ()

-- (5) system_user (): returns the name of the computer on which the current user logs on. Select system_user

-- (6) user_name: Return username from the given user ID select user_name (1) -- Return: Return "DBO" from any database"

-- (4) fuzzy query -- 1, like -- look for a classmate surnamed Zhang select * from students where sname like 'zhang % '-- query a card or C card that is not released in February select * from card where Id like '00 [^ 8] % [, c] %'

-- 2, between -- query the list of books not ordered from January 1, 1992 to January 1, 1993 select * from sales where ord_date not between '2017-8-1 'and '2017-8-1'

-- 3, in -- Query select * from students where saddress in ('beijing', 'shanghai', 'guangzhou ') in the enumerated values ')

-- (5) Aggregate Function -- 1, sum: Calculate the sum of values. This function is only used for select sum (ytd_sales) from titles where type = 'business' columns of the numerical type'

-- 2, AVG: calculate the average value -- query the average score of the Students above the pass level. Select AVG (score) as average score from Score where score> = 60

-- 3, Max: maximum value; min: Minimum value select AVG (score) as average score, max (score) as highest score, min (score) as lowest score from Score where score> = 60

-- 4, Count: returns a non-null count. You can use count (*) to calculate all rows without specifying a specific column. -- select count (*) as pass count from Score where score> = 60

-- (6) query by group -- 1, group by -- query the average score of the course number group: Select courseid AVG (score) as average score of the course from score group by courseid

-- Query the average score of the course number and student number grouping select studentid as student number, courseid as internal test, AVG (score) as internal test average score from scroe group by studentid, courseid

-- 2. Use the having statement to delete and select the grouping conditions -- Query "the number of employees whose salaries are no less than 2000 Department numbers, and the number of people in this department should be at least two "select Department number, count (*) from employee information table where salary> = 2000 group by Department number having count (*)> 1

-- (7) table join query -- (1) Inner join query -- 1. Specify the join condition in the WHERE clause -- SQL select students used to query the Student name and score. sname, score. courseid, score. score from students, score where students. scode = score. studentid

-- 2, Use join... In the from clause... on -- the preceding SQL statement can be implemented as follows: Select S. sname, C. courseid, C. score from students as s inner join score as C on (S. scode = C. studentid)

-- 3, three tables join select S. sname as school name, CS. coursename as course name, C. score as test score from students as s inner join score as C on (S. scode = C. studentid) Inner join course as CS on (CS. courseid = C. courseid)

-- (2) External join query: The tables involved in the join in the external join have the master-slave relationship, and match the data columns from the table with the data in each row of the master table, data that meets the join condition will be directly returned to the result set-for those columns that do not meet the condition, the null value will be filled and then returned to the result set

-- 1, left outer: Use left join or left Outer Join -- count the test information of all students, and select s should be displayed if there is no test. sname, C. courseid, C. score from students as s left Outer Join score as C on S. scode = C. studentid

-- 2, right outer join: Use Right join or right outer join -- the same as left Outer Join. It only contains all matched rows on the right.

[Reprinted from http://81146566.blog.163.com/blog/static/269554262009620115627226 /]