Common SQL statements

--  must remember,sql  is not sensitive to the case! -- sql  uses single quotes to wrap text values (most database systems also accept double quotes). If it is a numeric value, do not use quotation marks. --  wildcard character%                Replaces one or more characters _             only one character [charlist]  Any single character of the     character columns [^charlist] or [!charlist]--  data type      description integer (size)      holds integers only. Specify the maximum number of digits within the parentheses. INT (size) SMALLINT (size) TINYINT (size) decimal (size,d)   holds numbers with decimals. "Size"   the maximum number of digits for the specified number. "D"   Specifies the maximum number of digits to the right of the decimal point. NUMERIC (size,d)  char (size)      holds a fixed-length string (can hold letters, numbers, and special characters). Specifies the length of the string in parentheses. VARCHAR (size)      accommodates variable-length strings (which can hold letters, numbers, and special characters). Specifies the maximum length of the string in parentheses. Date (YYYYMMDD)      hold dates.  create table shopnc_member_20160421 select * from shopnc_member Front is the backup table, Next is the table that needs to be backed up--  select data from the table:select  column name  FROM  table name select * from  table name select  Lastname,firstname&nBsp from personsselect * from persons --  returns a unique different value:select distinct  column name  FROM  Table name select distinct company from orders --  conditionally select data from a table   The first condition and the second condition are all set up   the first condition and the second condition as long as there is a select  column name  FROM  table name  WHERE  column   operator   Value Where and or  = <> > < <= >= between  LIKE --  sort records in ascending order     descending descselect company,ordernumber from  orders order by company desc --  Insert a new row:insert into  table name into the table  values   (value 1,  value 2,....) insert into table_name  (column 1,  column 2,...)  VALUES  (value 1,  value 2,....) insert into persons values  (' Gates ',  ' Bill ',  ' xuanwumen 10 ',  ' Beijing ' ) insert into persons  (lastname, address)  VALUES  (' Wilson ',  ' champs-elysees ' )  --  modify the data in the table: update  table name  SET  column name  =  new value  WHERE  column name  =  a value Update person set  FirstName =  ' Fred '  WHERE LastName =  ' Wilson '   --  delete rows in a table: D elete from  table name  WHERE  column name  =  value delete from person where  lastname =  ' Wilson '   --  specifies the number of records to return limit numberselect * from  persons limit 5 --  allows us to specify multiple values in the  WHERE  clause select * from persons  WHERE LastName IN  (' Adams ', ' Carter ')  --  Select a range of data between two values. These values can be numeric, text, or date    if you want to use the above example to show people outside the range, use the  NOT  operator select * from persons  where lastname between  ' Adams '  AND  ' Carter '  --  exists at least one match when Inner join   Keyword return line select column_name (s)  FROM table_name1 INNER JOIN table_name2   on table_name1.column_name=table_name2.column_namEselect persons.lastname, persons.firstname, orders.orderno from persons inner  join orders on persons.id_p=orders.id_p rder by persons.lastname --   Returns all rows from the left table   (table_name1)   There, even if there are no matching rows select persons.lastname in the right table   (table_name2)  ,  persons.firstname, orders.ordernofrom personsleft join orderson persons.id_p= orders.id_porder by persons.lastname --  from the right table   (table_name2)   where all the rows are returned, even in the left table   ( TABLE_NAME1)   does not have a matching row select persons.lastname, persons.firstname, orders.ordernofrom  personsright join orderson persons.id_p=orders.id_porder by persons.lastname --   Returns all rows from the left table   (Persons)   and right table   (Orders)   there. --  if the rows in   "Persons"   do not match in table   "orders"  , or if   "orders"   rows are in the table   "Persons" There are no matches in  , these lines will also list Select persons.lastname, persons.firstname, orders.ordernofrom personsfull join orderson persons.id_p=orders.id_porder by  Persons.LastName --  combine result sets of two or more  SELECT  statements Select e_name from employees _china union select e_name from employees_usa --  Select data from one table and then insert the data into another table; Used to create backup copies of tables or to archive records  --  "Persons"   Table backup copies:select *into persons_backupfrom  The persons-- in  clause can be used to copy a table:select *into persons in  ' Backup.mdb ' FROM  to another database persons--  if we want to copy some fields, we can list them after the  SELECT  statement: Select lastname,firstnameinto persons_ backupfrom persons--  created a two-column named   "by extracting the information from the  " Persons "  table from the person who lives in  " Beijing "  Persons_backup   's Table: select lastname,firstnameinto persons_backupfrom personswhere city = ' Beijing '--  creates a new table named   "Persons_order_backup"  , which contains the  Persons  and  Orders  Two information obtained in this table: Select persons.lastname,orders.ordernointo persons_order_backupfrom personsinner join orderson  persons.id_p=orders.id_p --  Create a database/create a table in a database create database database_namecreate table   Table name (column name 1  data type, column name 2  data type, column name 3  data type,....)  --  Delete database/delete table drop database drop table --  Add/Remove/Modify columns in a table alter table  table_name add column_name datatypealter table table_name drop column  column_namealter table table_name alter column column_name datatype --   Constraint forcing column does not accept  NULL  value create table persons (id_p int not null,lastname  VARCHAR (255)  not null,firstname varchar (255), Address varchar (255), City varchar (255))  --  constraints are used to insert default values into a column create table persons (id_p int not null,lastname  VARCHAR (255)  not null,firstname varchar (255), Address varchar (255), City varchar (255)  DEFAULT  ' Sandnes ')  --  time function function               description Now ()               Returns the current date and Time Curdate ()      returns the current date Curtime ()      returns the current time date ()               extract date or date/time expression date part extract ()       return the date/time by pressing the individual section date_add ()      Add the specified time interval to the date date_sub ()      Subtracts a specified time interval from a date DateDiff ()      returns the number of days between two dates date_format ()      Displays the date/time in a different format   

Common SQL statements