当前位置:网站首页>SQL basic syntax, very complete recommended collection (Oriental Dream)

SQL basic syntax, very complete recommended collection (Oriental Dream)

2020-11-15 10:12:30 osc_ vuza8uho

sql Basic course

sql course

What is? sql?
  • SQL Structured query language
  • SQL Give us the ability to access databases
  • SQL It's a kind of ANSI Standard computer language
sql What can be done
  • SQL Database oriented query execution
  • SQL Data can be retrieved from the database
  • SQL New records can be inserted into the database
  • SQL Data in the database can be updated
  • SQL Records can be deleted from the database
  • SQL You can create a new database
  • SQL New tables can be created in the database
  • SQL You can create stored procedures in the database
  • SQL You can create views in the database
  • SQL You can set up tables 、 Permissions for stored procedures and views
SQL It's a standard - however …
  • SQL It's a door ANSI Standard computer language , To access and operate the database system .SQL Statement is used to retrieve and update data in the database .SQL Can work with database program , such as MS Access、DB2、Informix、MS SQL Server、Oracle、Sybase And other database systems .

    Unfortunately , There are many different versions SQL Language , But for the sake of ANSI Standards are compatible , They have to work together in a similar way to support some of the main keywords ( such as SELECT、UPDATE、DELETE、INSERT、WHERE wait ).

    ** notes :** except SQL Out of standard , Most of the SQL Database programs have their own private extensions !

Use... In your website sql
  • RDBMS Database program ( such as MS Access, SQL Server, MySQL)
  • Server side scripting language ( such as PHP or ASP)
  • SQL
  • HTML / CSS
RDBMS
  • RDBMS A relational database management system .

    RDBMS yes SQL The basis of , It's also the foundation of all modern database systems , such as MS SQL Server, IBM DB2, Oracle, MySQL as well as Microsoft Access.

    RDBMS The data in is stored in what is called a table (tables) In the database object of .

    A table is a collection of related data items , It consists of columns and rows .

sql grammar

Database table
  • A database usually contains one or more tables . Each table is identified by a name ( for example “ Customer ” perhaps “ Order ”). Table contains records with data ( That's ok ).

    Here's an example called “Persons” Table of :

    Id LastName FirstName Address City
    1 Adams John Oxford Street London
    2 Bush George Fifth Avenue New York
    3 Carter Thomas Changan Street Beijing
    • The table above contains three records ( Each one corresponds to one person ) And five columns (Id、 surname 、 name 、 Address and city ).
sql sentence
  • Most of the work you need to perform on the database is done by SQL Sentence completion .

    The following statement selects from the table LastName Columns of data :

    SELECT LastName FROM Persons
    
  • The result set looks like this :

    LastName
    Adams
    Bush
    Carter
SQL DML and DDL
  • You can put SQL In two parts : Data operation language (DML) and Data definition language (DDL).

    SQL ( Structured query language ) Is the syntax used to execute queries . however SQL The language also includes updates for 、 Syntax for inserting and deleting records .

    Query and update instructions make up SQL Of DML part :

    • SELECT - Getting data from a database table
    • UPDATE - Update the data in the database table
    • DELETE - Delete data from the database table
    • INSERT INTO - Insert data into a database table
  • SQL Data definition language of (DDL) Part of it gives us the ability to create or delete tables . We can also define indexes ( key ), Specify links between tables , And impose constraints between tables .

    SQL The most important DDL sentence :

    • CREATE DATABASE - Create a new database
    • ALTER DATABASE - modify the database
    • CREATE TABLE - Create new table
    • ALTER TABLE - change ( change ) Database table
    • DROP TABLE - Delete table
    • CREATE INDEX - Create index ( The search button )
    • DROP INDEX - Delete index

sql select

sql select sentence
  • SELECT Statement to select data from a table .

    The results are stored in a result table ( Called result set ).

  • sql select grammar

    SELECT  Column name  FROM  The name of the table 
    
    SELECT * FROM  The name of the table 
    
    • Case insensitive ,SELECT Equivalent to select.
sql select example
  • To be named “LastName” and “FirstName” The contents of the column ( From the name “Persons” Database table of ), Use something like this SELECT sentence :

    SELECT LastName,FirstName FROM Persons
    
  • “Persons” surface

    Id LastName FirstName Address City
    1 Adams John Oxford Street London
    2 Bush George Fifth Avenue New York
    3 Carter Thomas Changan Street Beijing

    result

    LastName FirstName
    Adams John
    Bush George
    Carter Thomas
sql select * example
  • Now we want to start from “Persons” Select all the columns in the table .

    Please use the symbol * Replace the name of the column , Just like this. :

    SELECT * FROM Persons
    
    • Tips : asterisk (*) Is a shortcut to select all columns .

    result

    Id LastName FirstName Address City
    1 Adams John Oxford Street London
    2 Bush George Fifth Avenue New York
    3 Carter Thomas Changan Street Beijing

sql select distinct

sql select distinct sentence
  • In the table , May contain duplicate values . It's not a problem , however , Sometimes you may want to just list the differences (distinct) Value .

    key word DISTINCT Used to return a unique different value .

  • grammar

    SELECT DISTINCT  Column name  FROM  The name of the table 
    
Use distinct key word
  • If you want to from “Company” Select all the values in the column , We need to use SELECT sentence :

    SELECT Company FROM Orders
    
    • order surface

      Company OrderNumber
      IBM 3532
      W3School 2356
      Apple 4698
      W3School 6953

      result

      Company
      IBM
      W3School
      Apple
      W3School
  • Please note that , In the result set ,W3School It was listed twice .

    From Company" Select only the only different value in the column , We need to use SELECT DISTINCT sentence :

    SELECT DISTINCT Company FROM Orders 
    

    result

    Company
    IBM
    W3School
    Apple

sql where sentence

where sentence
  • grammar

    SELECT  Column name  FROM  The name of the table  WHERE  Column   Operator   value 
    
  • The following operators can be found in WHERE Used in clauses :

    The operator describe
    = be equal to
    <> It's not equal to
    > Greater than
    < Less than
    >= Greater than or equal to
    <= Less than or equal to
    BETWEEN In a certain range
    LIKE Search for a pattern
    • notes : In some versions of SQL in , The operator <> Can be written as !=.
Use where sentence
  • If you just want to choose to live in a city “Beijing” People in , We need to SELECT Add WHERE Clause :

    SELECT * FROM Persons WHERE City='Beijing'
    
  • persons surface

    LastName FirstName Address City Year
    Adams John Oxford Street London 1970
    Bush George Fifth Avenue New York 1975
    Carter Thomas Changan Street Beijing 1980
    Gates Bill Xuanwumen 10 Beijing 1985

    result

    LastName FirstName Address City Year
    Carter Thomas Changan Street Beijing 1980
    Gates Bill Xuanwumen 10 Beijing 1985
Use of quotation marks
  • Text value

     That's right :
    SELECT * FROM Persons WHERE FirstName='Bush'
    
     This is wrong :
    SELECT * FROM Persons WHERE FirstName=Bush
    
  • The number

     That's right :
    SELECT * FROM Persons WHERE Year>1965
    
     This is wrong :
    SELECT * FROM Persons WHERE Year>'1965'
    

sql and & or Operator

  • AND and OR Filter the record based on more than one condition .
and and or Operator
  • AND and OR Can be found in WHERE The combination of two or more conditions in a sub statement .

    If the first condition and the second condition hold , be AND Operator to display a record .

    If only one of the first and second conditions holds , be OR Operator to display a record .

  • Original table ( In the example ):

    LastName FirstName Address City
    Adams John Oxford Street London
    Bush George Fifth Avenue New York
    Carter Thomas Changan Street Beijing
    Carter William Xuanwumen 10 Beijing
and Operator instance
  • Use AND To show all surnames as “Carter” And it's called “Thomas” People who :
SELECT * FROM Persons WHERE FirstName='Thomas' AND LastName='Carter'

result

LastName FirstName Address City
Carter Thomas Changan Street Beijing
or Operator instance
  • Use OR To show all surnames as “Carter” Or it's called “Thomas” People who :

    SELECT * FROM Persons WHERE firstname='Thomas' OR lastname='Carter'
    

    result

    LastName FirstName Address City
    Carter Thomas Changan Street Beijing
    Carter William Xuanwumen 10 Beijing
combination and and or Operator
  • We can also put AND and OR Combine ( Use parentheses to make complex expressions ):

    SELECT * FROM Persons WHERE (FirstName='Thomas' OR FirstName='William')
    AND LastName='Carter'
    

    result

    LastName FirstName Address City
    Carter Thomas Changan Street Beijing
    Carter William Xuanwumen 10 Beijing

sql order by sentence

  • order by Statement is used to sort the rows of the result set
order by sentence
  • ORDER BY Statement to sort the result set according to the specified column .

    ORDER BY By default, statements sort records in ascending order .

    If you want to sort records in descending order , have access to DESC keyword .

Original table ( Used in the example )
  • order surface :

    Company OrderNumber
    IBM 3532
    W3School 2356
    Apple 4698
    W3School 6953
example 1
  • Show company names in alphabetical order :

    SELECT Company, OrderNumber FROM Orders ORDER BY Company
    

    result

    Company OrderNumber
    Apple 4698
    IBM 3532
    W3School 6953
    W3School 2356
example 2
  • Show company names in alphabetical order (Company), And display the sequence number in numerical order (OrderNumber):

    SELECT Company, OrderNumber FROM Orders ORDER BY Company, OrderNumber
    

    result

    Company OrderNumber
    Apple 4698
    IBM 3532
    W3School 2356
    W3School 6953
example 3
  • Show company names in reverse alphabetical order :

    SELECT Company, OrderNumber FROM Orders ORDER BY Company DESC
    

    result

    Company OrderNumber
    W3School 6953
    W3School 2356
    IBM 3532
    Apple 4698
example 4
  • Show company names in reverse alphabetical order , And display the sequence number in numerical order :

    SELECT Company, OrderNumber FROM Orders ORDER BY Company DESC, OrderNumber ASC
    

    result

    Company OrderNumber
    W3School 2356
    W3School 6953
    IBM 3532
    Apple 4698

sql insert sentence

insertinto Statement is used to insert new rows into a table
  • grammar

    INSERT INTO  The name of the table  VALUES ( value 1,  value 2,....)
    
  • We can also specify which columns to insert data into :

    INSERT INTO table_name ( Column 1,  Column 2,...) VALUES ( value 1,  value 2,....)
    
Insert a new line
  • persons surface

    LastName FirstName Address City
    Carter Thomas Changan Street Beijing
  • sql sentence

    INSERT INTO Persons VALUES ('Gates', 'Bill', 'Xuanwumen 10', 'Beijing')
    

    result

    LastName FirstName Address City
    Carter Thomas Changan Street Beijing
    Gates Bill Xuanwumen 10 Beijing
Inserts data into the specified column
  • persons surface

    LastName FirstName Address City
    Carter Thomas Changan Street Beijing
    Gates Bill Xuanwumen 10 Beijing
  • sql sentence

    INSERT INTO Persons (LastName, Address) VALUES ('Wilson', 'Champs-Elysees')
    

    result

    LastName FirstName Address City
    Carter Thomas Changan Street Beijing
    Gates Bill Xuanwumen 10 Beijing
    Wilson Champs-Elysees

sql update sentence

update sentence
  • Update Statement is used to modify the data in the table .
grammar
UPDATE  The name of the table  SET  Column name  =  The new value  WHERE  Column name  =  Certain value 
persons surface
LastName FirstName Address City
Gates Bill Xuanwumen 10 Beijing
Wilson Champs-Elysees
Update a column in a row
  • We are lastname yes “Wilson” People add firstname:

    UPDATE Person SET FirstName = 'Fred' WHERE LastName = 'Wilson' 
    

    result

    LastName FirstName Address City
    Gates Bill Xuanwumen 10 Beijing
    Wilson Fred Champs-Elysees
Update several columns in a row
  • We'll change the address (address), And add the city name (city):

    UPDATE Person SET Address = 'Zhongshan 23', City = 'Nanjing'
    WHERE LastName = 'Wilson'
    

    result

    LastName FirstName Address City
    Gates Bill Xuanwumen 10 Beijing
    Wilson Fred Zhongshan 23 Nanjing

sql delete sentence

delete sentence
  • DELETE Statement to delete rows in a table .
grammar
DELETE FROM  The name of the table  WHERE  Column name  =  value 
person surface :
LastName FirstName Address City
Gates Bill Xuanwumen 10 Beijing
Wilson Fred Zhongshan 23 Nanjing
Delete a line
  • fred willson Will be deleted

    DELETE FROM Person WHERE LastName = 'Wilson' 
    

    result

    LastName FirstName Address City
    Gates Bill Xuanwumen 10 Beijing
Delete all rows
  • You can delete all rows without deleting the table . This means the structure of the watch 、 Properties and indexes are complete :

    DELETE FROM table_name
    

    perhaps

    DELETE * FROM table_name
    

版权声明
本文为[osc_ vuza8uho]所创,转载请带上原文链接,感谢

随机推荐