Analysis of SQL data operation statements and SQL statements

Analysis of SQL data operation statements and SQL statements

Operators in SQL

1 Arithmetic Operator:

+: Calculate the sum of two numbers or expressions.
-: Subtraction operation. Calculate the subtraction between two numbers or expressions.
* Multiplication operation: calculates the product of the multiplication of two numbers or expressions.
/: Calculate the division operation to find the operator for division of two numbers or expressions
%: Modulo operation. Calculate the remainder of the division of two numbers or expressions.

2. Value assignment

=: Assign a number or expression to another scalar.

3. Comparison Operators

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

4. logical operators

AND: returns true if AND only if the two boolean expressions are true.
OR: if and only if both boolean expressions are false, false is returned.
NOT returns the value of a Boolean expression with the highest priority.

Insert data with T-SQL

1. Switch the database. Take Myschool as an example.

Use myschool

2. query the data in the table (* indicates all columns in the table)

select * from student

Add data to the student table

01 if you want to add all columns, you can leave the column name empty after the table name, but you must provide the values of all columns.

02 if you only want to add some columns to a table, keep up with the column name after the table name, and ensure that all columns except the column value you have given can be empty.

Add data to student. student is followed by the column name. If the column name contains an auto-incrementing column, delete the auto-incrementing column.

The value behind the values brackets is the value corresponding to each column.

Note: Each column corresponds to a value.

Insert into student (StudenttNo, LoginPwd, StudentName, Gender, Gradeld, Phone, Address, Birthday, Email) values (23214,5634, 'Tear slogs', 5434, 'beijing ', '2017-10-31 09:29:59 ', 'lsfjkl ')

When a column in the student table has a default value, it must be added to the default value.

Eg:

If studentName has a default value, the value corresponding to studentName is default.

Insert into student (StudenttNo, LoginPwd, StudentName, Gender, Gradeld, Phone, Address, Birthday, Email) values (23214,5634, default, 5434, 'beijing ', '2017-10-31 09:29:59 ', 'lsfjkl ')

Insert multiple data entries into a table at a time (three solutions are available)

Solution 1: (studentbak) This is a non-existent table. solution 1 backs up a studentbak from a table (student must exist ).

select * into studentbakfrom student

Solution 2: student (target table) studentbak (existing table) is equivalent to attaching data in studentbak to student table
-- * Indicates all columns. If the target table has an auto-incrementing column, an error is returned when you append the column. You must change the * number to a specific column in The studentbak table and delete the auto-incrementing column.

Eg:

insert into studentselect * from studentbak

Solution 3: If you want to add all columns, you can leave the column name after the table name, but provide the values of all columns.

If you only want to add some columns to a table, keep up with the column name after the table name, and ensure that all columns except the value of the column you give are allowed to be empty.

Eg:

Insert into studentselect 'He'

Modify Table Data

Update. When you see update, you must add the where condition (the condition after where cannot be compared with = and null. It must be "is null)
Update is followed by the table name, set is followed by the column name, if multiple column names are separated by commas
Where is a condition. Only the studentNo and studentName columns of the row whose id is 192ABC are modified.

Eg:

Update student set studentNo = 1, StudentName = 'Tear start' where ID = '192abc'

Delete: delete the data in the table (logs are recorded when the data is deleted, and the id number does not start from 1)

When you see delete, you must add the where condition (the condition after where cannot be compared with = and null, and is null must be used)
Delete followed by table name
Where is followed by a condition. Only the row with the id of 192ABC is deleted.

Eg:

delete studentwhere ID='192ABC'

Truncate: Delete the data in the table (the log is not deleted completely when the data is deleted, and the id number starts from 1 again)

After truncate, the where condition is not required.

Next, let's take a moment to introduce how to use SQL statements to operate databases in Android.

Increase in Data

1. Create a SQLite data help class

SQLiteDatabase db = helper. getWritableDatabase ();

2. Execute SQL statements to increase data

Db.exe cSQL ("insert into person (name, number) values (?,?) ", New Object [] {name, number });

3. Shut down the database

Db. close ();

Data deletion

1. Create a SQLite data help class

SQLiteDatabase db = helper. getWritableDatabase ();

2. Execute SQL statements to modify data

Db.exe cSQL ("delete from person where name =? ", New Object [] {name });

3. Shut down the database

Db. close ();

Data Modification

1. Create a SQLite data help class

SQLiteDatabase db = helper. getWritableDatabase ();

2. Execute SQL statements to modify data

Db.exe cSQL ("update person set number =? Where name =? ", New Object [] {newnumber, name });

3. Shut down the database

Db. close ();

Data Query

1. Create a SQLite data help class

SQLiteDatabase db = helper. getReadableDatabase ();

2. Call the rawQuery method in the SQLite Database Help class to query data

Cursor cursor = db. rawQuery ("select * from person where name =? ", New String [] {name });

3. query all data in the database

Boolean result = cursor. moveToNext ();

4. Close the cursor Project

Cursor. close ();

5. Shut down the database

Db. close ();

6. Check whether the query results exist in the database.

Return result;