Analysis of SQL data operation statement _mssql

Operators in SQL

1 Arithmetic operators:

+: Add operation, find two numbers or expressions want to add and
-: minus operation, the difference of two number or expression subtraction
*, multiply by multiplication of two numbers or expressions
/: In addition to operations, find two number or expression division of the quotient
%: modulo operation, finding the remainder of two numbers or expressions

2. Assignment operation

=: Assign a number or expression to another scalar.

3. Comparison operators

=: equal to > Greater than < less than <> not equal to >= greater than equal <= less than or equal to!= not equal to

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 a Boolean expression with the highest precedence

inserting data using T-SQL

1 switching database, taking MySchool as an example

Use MySchool

2. The data in the query table (* Number represents all columns in the table)

SELECT * FROM Student

add data to the student table

01 If you want to add all columns, the table name can not write the column name, but to provide the value of all columns

02 If you want to add only a partial column to a table, follow the list name after the table name, and make sure that all columns are allowed to be empty except for the value of the column you give.

Add data to student This table student parentheses are followed by the column name if there is a self added column in the column name, be sure to delete the self added column.

The values bracket is followed by the value of each column

Note: Each column corresponds to a value

INSERT into student (Studenttno, Loginpwd, Studentname, Gender, Gradeld, Phone, address, birthday, Email)
values (2321 4,5634, ' Tears sprinkled stars ', 0,2,5434, ' Beijing ', ' 2015-10-31 09:29:59 ', ' LSFJKL '

When a column in the student table has a default value, you must join default in values

eg

If Studentname has a default value, the corresponding value in Studentname is default

INSERT into student (Studenttno, Loginpwd, Studentname, Gender, Gradeld, Phone, address, birthday, Email)
values (2321 4,5634,default,0,2,5434, ' Beijing ', ' 2015-10-31 09:29:59 ', ' LSFJKL '

inserts multiple data into one table at a time (three options)

Scenario One: (Studentbak) This is a non-existent table, and scenario one is equivalent to backing up a table (student must exist) Studentbak

SELECT * Into Studentbak from
student

Scenario Two: Student (target table) Studentbak (table already exists) is equivalent to attaching data from the Studentbak table to the student table
--* on behalf of all columns if there is a self added column in the target table, you will be able to add an error, you must studentbak the table to the specific column, delete the self-added column

eg

INSERT INTO student
select * from Studentbak

Scenario Three: If you want to add all the columns, the table name can not write the column name, but to provide the values of all columns

If you want to add only a partial column to a table, follow the list name after the table name, and make sure that other columns are allowed to be empty except for the value of the column you give.

eg

INSERT INTO student

modify data in a table

Update, see update must add a where condition (where the qualification cannot be compared with = and NULL, must use is null)
Update followed by the table name, set followed by the column name, if multiple column names are separated by commas
Where is the Studentno studentname two column that modifies only the id=192abc row of data for the qualifying condition

eg

Update student set studentno=1,studentname= ' Tears sprinkled stars '

Delete deletes data from table (log is logged when data is deleted, ID number does not start at 1)

See delete must add a where condition (where the qualification cannot be compared with = and NULL, you must use is null)
Delete followed by the table name
Where followed by a qualification, delete only the line with ID 192ABC

eg

Delete student
where id= ' 192ABC '

truncate delete data in a table (no log is deleted when data is deleted, ID number will be restarted from 1)

Truncate does not need to follow the Where condition

Let's take a moment to introduce you to Android. Using SQL statements to manipulate databases

Increase in data

1, create a sqlite data of the Help class

Sqlitedatabase db = Helper.getwritabledatabase ();

2, execute the SQL statement, realize the increase of the data

Db.execsql ("INSERT into person (name,number) VALUES (?,?)", new object[] {name, number});

3. Close the database

Db.close ();

Deletion of data

1, create a sqlite data of the Help class

Sqlitedatabase db = Helper.getwritabledatabase ();

2, execute the SQL statement, realize the data modification

Db.execsql ("Delete from person where name=?", new object[] {name});

3. Close the database

Db.close ();

Modification of data

1, create a sqlite data of the Help class

Sqlitedatabase db = Helper.getwritabledatabase ();

2, execute the SQL statement, realize the data modification

Db.execsql ("Update person set number=?") Where Name=? ", new object[] {newnumber, name});

3. Close the database

Db.close ();

Query for Data

1, create a sqlite data of the Help class

Sqlitedatabase db = Helper.getreadabledatabase ();

2, call the SQLite database in the Help class of the Rawquery method query data

Cursor Cursor = Db.rawquery ("select * from person where name=?", new string[] {name});

3, query the database of all the data

Boolean result = Cursor.movetonext ();

4. Close the cursor project

Cursor.close ();

5. Close the database

Db.close ();

6, return to the database whether there is a need to query results

return result;