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