SQL - Structured Query Language

1. Introduction to SQL


SQL is a structured query language. It is a set of operation commands specially established for database. It is a database language.

The interactive operation of the database is inseparable from SQL, which provides great convenience for us to manage the database.

1.1 database use case construction
Note: all of this article SQL The procedures are based on the self built database at the end of the text. You can download and obtain them yourself.
  • Download create. Net at the end of the article txt,populate.txt file
  • Run "CREATE DATABASE sample;" in the MySQL command line client, Create sample database
  • Run "USE sample;" in the MySQL command line client, Go to the sample database for subsequent operations
  • Run create.com in the My SQL command line client Txt to create a data table
  • Run populate. Com in the My SQL command line client Txt to import the data into the sample database
  • Run "SHOW TABLES;" in the MySQL command line client, View data table creation
  • Run "select * from customers; select * from orders; select * from orders; select * from products; select * from vendors;" in the MySQL command-line client, View data import

2.SQL overview


The explanation of SQL language can be carried out from seven aspects: definition, syntax rules, data type, SQL syntax, function, advanced query and view. However, we need to know that the core and difficulty of SQL language is to solve the problem of database data query. Data query is also our most commonly used service, so we should focus on mastering this core technology!

3.SQL explanation

3.1 data type


SQL can process more than a dozen data types. These data can be stored in the database in the form of tables, but the breadth of the database is unmatched by EXCEL, which is an important reason why the company uses the database for data storage.

3.2 grammar rules


Syntax rules are hard rules that must be followed when writing SQL statements. If they are not followed, errors will occur; Therefore, our check of SQL program starts from whether it meets the syntax rules.

3.3 function


Functions are mostly used to process the queried data and are used in combination with SQL statements, so SQL language also has a certain ability of data analysis.

--Find the mean
SELECT AVG(Listing) FROM Table name;
--Number of records
SELECT COUNT(Listing) FROM Table name;
--Get the first query record
SELECT FIRST(Listing) FROM Table name;
--Get the last record queried
SELECT LAST(Listing) FROM Table name;
--Get the maximum records queried
SELECT MAX(Listing) FROM Table name;
--Get the minimum record queried
SELECT MIN(Listing) FROM Table name;
--Get field sum
SELECT SUM(Listing) FORM Table name;

--Grouping calculation
SELECT Listing,aggregate_function(Listing)
FROM Table name
WHERE condition
GROUP BY Listing;

--Group, calculate, and filter records for operators and conditions
SELECT Listing,aggregate_function(Listing)
FROM Table name
WHERE condition
GROUP BY Listing
HAVING aggergate_function(Listing) Operator value;

--Capitalize field values
SELECT UCASE(Listing) FROM Table name;
--Lower case field values
SELECT LCASE(Listing) FROM Table name;
--Extract field values
SELECT MID(Listing,Start bit,length) FROM Table name;
--Gets the length of the field value
SELECT LEN(Listing) FROM Table name;
--Round field values
SELECT ROUND(Listing,Decimal places) FROM Table name;
--Get current time
SELECT NOW() FROM Table name;
--Displays fields in the specified format
SELECT FORMAT(Listing,format) FROM Table name;
3.4 SQL syntax



SQL syntax explains the reserved keywords in SQL language, and it is also a direct tool for database operation.

SQL syntax includes data definition language (DDL), which is mainly used for creating, modifying, updating and deleting databases and data tables; And the creation and deletion of indexes, primary keys and foreign keys; It is not a direct operation on data.

The data operation language (DML) in SQL syntax is a direct operation of data, including data addition, deletion, modification and query, in which "query" is the core problem.

Clauses and attribute words are reserved keywords serving data manipulation language (DML) for more complex data operations.

Wildcards are used in conjunction with the LIKE keyword for Fuzzy Lookup.

DELETE statement

The DELETE statement is used to DELETE rows in a table

DELTETE FROM Table name WHERE [condition];

--delete orders All records in the table
DELETE FROM orders;
DELETE * FROM orders;

--delete orders In the table order_num Record for 20005
DELETE FROM orders WHERE order_num = 20005;

UPDATE statement

The UPDATE statement is used to modify data in a table

UPDATE Table name SET Listing=New value WHERE [condition];

--Update a column in a row
UPDATE customers SET cust_country = 'CHAIN' WHERE cust_name = Fun4All;

-- Update several columns in a row
UPDATE customers SET cust_country = 'CHAIN',cust_state = 'MI' WHERE cust_name = 'Kids Place';

-- Updating data from one table with data from another
UPDATE customers SET A.cust_zip = B.order_date FROM customers A,orders B WHERE A.cust_id  = B.cust_id;

INSERT INTO statement

The INSERT INTO statement is used to insert a new row into a table

--Insert data for all columns
INSERT INTO Table name VALUES(Value 1, value 2,...);

--Insert data for several columns
INSERT INTO Table name (Column 1, column 2,...) VALUES(Value 1, value 2,...);

--Insert data for all columns
INSERT INTO orderitems VALUES(20005,1,'BRO3',254,11.99);

--Insert data for several columns
INSERT INTO orderitems (order_item,quantity) VALUES(3,199);

SELECT statement

The SELECT statement is used to SELECT data from a table

--Get some fields
SELECT Listing FROM Table name;
--Get all records
SELECT * FROM Table name;

--De duplication of acquired fields
SELECT DISTINCT Listing FROM Table name;

--Query data table by criteria
SELECT Listing FROM Table name WHERE Column name operator value;
SELECT Listing FROM Table name WHERE Condition 1 AND Condition 2;
SELECT Listing FROM Table name WHERE Condition 1 OR Condition 2;
SELECT Listing FROM Table name WHERE Listing IS NULL;
SELECT Listing FROM Table name WHERE Listing IS NOT NULL;

--Grouping the queried data
SELECT Listing FROM Table name ORDER BY Listing;
SELECT Listing FROM Table name ORDER BY Listing DESC;
SELECT Listing FROM Table name ORDER BY Column name 1 DESC,Column name 2 ASC;
SELECT Listing FROM Table name WHERE Listing LIKE pattern;
SELECT Listing FROM Table name WHERE Listing NOT LIKE pattern;
SELECT Listing FROM Table name WHERE Listing IN (value1,value2,...);
SELECT Listing FROM Table name WHERE Listing BETWEEN value1 AND value2;

--Before acquisition N Data records
SELECT TOP Number column name FROM Table name;

--rename table 
SELECT Listing FROM Table name AS New table name;
--Rename field
SELECT Listing AS New column name FROM Table name;

--Two table query
SELECT Listing FROM Table name 1,Table name 2 WHERE condition;

--Data table replication
SELECT Listing INTO New table name [IN externaldatabase] FROM Old table name;

SELECT Fields (query fields)
FROM Table name (query table name)
WHERE Criteria (filter criteria)
GROUP BY Fields (group results)
ORDER BY Fields (sorted by field)
HAVING Condition (filter grouping condition)
LIMIT NUM (Limit function);

CREATE statement

The CREATE statement can be used to CREATE databases and data tables

--Create database
CREATE DATABASE Database name;

--Create data table
CREATE TABLE Table name 1
(
Column name 1 data type(size),
Column name 2 data type(size),
Column name 3 data type(size) NOT NULL,
Column name 4 data type(size) DEFAULT value,
Column name 5 data type(size) AUTO_INCREMENT,
CHECK (Column name>0),
UNIQUE(Column name 3),
PRIMARY KEY (Column name 4),
FOREIGN KEY (Column name 4) REFERENCES Table name 2(Column name 4),
...
);

DROP statement

DROP statements can DROP indexes, tables, and databases

--Delete index
DROP INDEX index_name ON table_name;
DROP INDEX table_name.index_name;
DROP INDEX index_name;
ALTER TABLE table_name DROP INDEX index_name;
 
--Delete data table
DROP TABLE Table name;

--Delete database
DROP DATABASE Database name;

ALTER statement

The ALTER TABLE statement is used to add, modify, or delete rows from an existing table

--Add field
ALTER TABLE table_name ADD column_name datatype;

--Delete field
ALTER TABLE table_name DROP COLUMN column_name;

--Modify field data type
ALTER TABLE table_name ALTER COLUMN column_name datatype;
3.5 advanced query


Advanced query includes combined query, sub query and join table; It is used to realize data query with more complex logic.

--Combined search
SELECT Column name 1 FROM Table name 1 UNION SELECT Column name 2 FROM Table name 2;
SELECT Column name 1 FROM Table name 1 UNION ALL SELECT Column name 2 FROM Table name 2;

--Sub search
SELECT Column name 1 FROM Table name 1 WHERE Column name 2 = (SELECT Column name 3 FROM Table name 2 WHERE condition);

--Join tables: multi table lookup
SELECT Listing FROM Table name 1 INNER JOIN Table name 2 ON condition;
SELECT Listing FROM Table name 1 LEFT JOIN Table name 2 ON condition;
SELECT Listing FROM Table name 1 RIGHT JOIN Table name 2 ON condition;
SELECT Listing FROM Table name 1 FULL JOIN Table name 2 ON condition;
3.6 view


The view is equivalent to the visualization of query data.
The view contains rows and columns, just like a real table, but the design and structure of the database are not affected by the functions, WHERE or JOIN statements in the view.

CREATE VIEW statement

The CREATE VIEW statement is used to create a view

CREATE VIEW view_name AS SELECT Listing FROM Table name WHERE condition;

DROP VIEW view_name;

UPDATE view_name SET Listing = value WHERE condition;

INSERT INTO view_name VALUES(value1,value2,...);

DELETE FROM view_name WHERE condition;

4. Services

Several SQL statements are packaged together to perform a complete task, which is a transaction, which is equivalent to a collection of multiple SQL statements.

  • In MySQL, only databases or tables that use the Innodb database engine support transactions
  • Transactions are used to maintain the integrity of the database and ensure that batch SQL statements are either executed or not executed
  • Transactions are used to manage INSERT, UPDATE and DELETE statements

Transactions have the following characteristics:

  • Atomicity: a transaction is either executed or not executed at all
  • Consistency: the integrity of the database is not destroyed before and after the transaction
  • Isolation: the ability of a database to allow multiple transactions to read, write, and modify its data at the same time
  • Persistence: after the transaction is completed, the modification of data is permanent.

Transaction control statement:

  • BEGIN: start a transaction
  • ROLLBACK: ROLLBACK transaction
  • COMMIT: COMMIT transaction
  • SET AUTOCOMMIT = 0: automatic submission is prohibited
  • SET AUTOCOMMIT = 1: start autocommit

5. Attachment Download

SQL operation case

Keywords: Database MySQL SQL

Added by wmac on Sun, 19 Dec 2021 05:43:26 +0200

Popular Keywords

©2024 Programming VIP