T-SQL basics SQL statement summary (1/6)

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

Home 1 2 3 4 5 6 Last page