Basic SQL Server statements (1): Basic SQL Server statements
1. Insert a row of data:
Insert into Table Name (column name) VALUES (corresponding value );
2. Insert multiple data records at a time
Create a table first:
Insert into new table name (list) SELECT original table column name FROM original table;
Create a table during execution:
SELECT Table name. Column name INTO new table name FROM original table;
Create an ID column:
SELECT Grade. GradeldName, IDENTITY (int, 1, 1) as GradeId into AddressList5 From Grade;
Update a row of data:
UPDATE Grade SET GradeldName = '000000' WHERE GradeldName = 'oop ';
Update all data:
UPDATE Grade SET GradeldName = '20140901 ';
Delete data:
Delete from Grade WHERE GradeId = '1 ';
Delete from Grade WHERE GradeId = '2 ';
Delete a table (not recommended, data cannot be restored ):
Truncate table name;
Truncate table AddressList;
Query statement:
SELECT GradeId FROM AddressList1 WHERE GradeldName = '201312 ';
Query all data except '123:
SELECT GradeldName FROM AddressList1 WHERE GradeldName <> '123 ';
Merge columns:
Select name = GradeldName + '.' + GradeId FROM AddressList5;
Query null value:
Select name from table where email is null;
SELECT GradeId FROM AddressList1 WHERE GradeldName is null;
Query and add a constant row. Default Value: school name:
Select gradename = '201312', 'obtk 'as schoolname;
SELECT GradeldName = 'oop ', 't153' as schoolname;
Sort:
How many rows are sorted:
For example, if the ID is smaller than 3;
SELECT GradeldName AS 'Year' FROM AddressList1 WHERE GradeId <3 order by GradeId ;;
1. sort by Column
SELECT GradeldName AS 'Year' FROM AddressList1 order by GradeId;
SELECT GradeldName AS 'Year', 't153 'AS 'school name' FROM AddressList1 order by GradeldName;
2. Column Ascending Order:
SELECT GradeldName AS 'Year' FROM AddressList1 order by GradeId ASC;
SELECT GradeldName AS 'Year', 't153 'AS 'school name' FROM AddressList1 order by GradeldName ASC;
3. Column descending order:
SELECT GradeldName AS 'Year' FROM AddressList1 order by GradeId DESC;
SELECT GradeldName AS 'Year', 't153 'AS 'school name' FROM AddressList1 order by GradeldName DESC;