ACCP Ma Tianpeng 2017/10/20 14:33:07
Manipulate the data with SQL statements.
The composition of SQL:
(1) DML (data manipiation, Language) used to insert, modify and delete data in the database, such as: Insert,update,delete, etc.
(2) DDL (data definition Language) is used to create databases, database pairs and define their columns, most of which are commands that start with create, such as Create TABLE, create view and drop table.
(3) DQL (data query Language) is used to query the data in the database, such as: Sellect, etc.
(4) DCL (Data Control Language) is used to control the access permission of database components, access permissions, etc., such as: Grant,revoke.
Operators in sql:
1:
Arithmetic operators: + (plus),-(minus), * (multiply),/(except),% (modulo, to divide the remainder of two numbers),
2:
Assignment operator: = (assigns one number or variable to another)
3:
Comparison operator: = (equals),> (greater than),< (less than),<> (not Equal), >= (greater than or equal), <= (less than equals),! = (not equal to, non-SQL-92 standard)
4:
Logical operators: and (returns True if and only if two Boolean expressions are true), OR (returns False if and only if two Boolean expressions are false),
Not (negates the value of the Boolean expression, with the highest precedence).
Insert data using T-SQL.
Inserting the data one line at a line using the INSERT statement is the most common method:
Syntax: INSERT into table name (table column name)
Values (Value list)
Example: INSERT into Studentes (sname,sadress,sgrade,semali,sec)
VALUES (' Zhang Qing ', ' Shanghai ', 6, ' [email protected] ', 0)
Insert more than one row of data at a time.
There are three ways to insert multiple rows of data at one time:
(1) Adding data through the Insert SELECT statement Phase table
INSERT into AddressList (name, address, email)
SELECT Sname,saddress,semail
From Student
Note: 1, the number of data to be queried, the order, the data type, etc., must be consistent with the inserted item. 2,addresslist tables must be pre-created and have names, addresses, e-mail boxes of these three categories.
(2) Add existing data to the table by using the SELECT INTO statement
Example: SELECT Students.sname,students.saddress,students.semali
Into AddressList
From Students
When inserting data into a new table, there is a new question, how do i insert an identity column?
Syntax: SELECT identity (data type, identity seed, identity growth) as column name
into new table
From original table
(3) Insert by Union Key Sub-merge data
Example: INSERT Students (Sname,sgrade,ssex)
SELECT ' Zhang Ke ', 7,1union
SELECT ' Zhang Ke ', 7,1union
SELECT ' Zhang Ke ', 7,1
Updating data with T-SQL
Syntax: Update table name set column name = update value WHERE update condition
Delete data using T-SQL
Statement: Delete from table name WHERE Delete condition
Delete data using TRUNCATE TABLE
Truncae TABLE studnts
Deletes all data from the table.
The database uses SQL statements to manipulate data
