SQL additions and deletions (specific)

First, Increase: there are 3 ways

1. Insert a single line of data using insert:

   insert [into] <表名> [列名] values <列值>　　insert into Strdents (name,age) values (‘atm‘,12)

2. Use the Insert,select statement to add data from an existing table to a new table

   insert into <已有的新表> <列名> select <原表列名> from <原表名>　　insert into newtable (name,class)select name,class from  tableinfo

3. Inserting data into the original table (more for test data generation)

    和另外一种方法一样，仅仅是拷贝到原表中

   insert into tableinfo (‘name‘,‘class‘)select name,class from  tableinfo

Ii. deletion: There are 3 methods

1.delete Delete

    delete from <表名> [where <删除条件>]　　      delete from tableinfo where name=‘atm‘

2.truncate table deletes data for the entire table

     truncate table <表名>　   truncate table tableinfo　  删除表的全部行。但表的结构、列、约束、索引等不会被删除；不能用于有外建约束引用的表

3. Drop Delete

    drop table <表名>    drop table tableinfo   删除表中全部行。表结构也删除了。

third, update changes

    update <表名> set <列名=更新值> [where <更新条件>]    update tableinfo set age=12 where name=‘atm1‘　　 set后面能够紧随多个数据列的更新值（非数字要引號）；

Iv. Check

1. General Enquiry

   Select < column name >  from < table name > [where < query conditional expression] [order  by < sort column name >[ASC or desc]] 1). Querying all data Select * from  tableinfo 2
      ). Query part of the row--conditional query  select name,age from Tableinfo where age= one;3). Use as to change column names in queries Select name  as name  from a where age=one;4). Query for empty rowsSelectName fromTableinfwhereClass is NULL 　　    5). Query returns the limit number of rows (keyword: top)SelectTop6Name fromTableinfo Displays the first 6 rows of the column name, in Oracle with RowNum instead (Select* fromAwhererownum<6)6). Query Sort (keywords:Order  by,ASC,descCasesSelectName fromTableinfowhereage>= One Order  by desc(implied ASC Ascending)

2. Fuzzy query

1). Use like for fuzzy queries
See also one article, SQL like four ways to use
　　　　
2). Use between to query within a range

select * from tableinfo where age between 11 and 22

　　
3). Use in to query within the enumeration (in the back is more data)

select name from tableinfo where name in (‘atm‘,‘atm1‘,‘atm2‘);

SQL additions and deletions (specific)