1 day SQL database entry

The author graduated from computer major, and has been doing design and front-end work since work. The background and database operation of university study have been basically returned to the teacher.

Now I am getting older, and I am prepared for danger in times of peace. In order to better career development, I decide to advance to the full stack, in order to become an architect.

After all, it’s easy to pick up now. Today spent a day of time, the SQL common syntax and command review again. Next, I’ll send you today’s notes.

Even if it is a small white, read the following command, even if you do not understand some principles, but the commonly used SQL add, delete, change and check can also be said to be so easy.

---

SQL based

• SQL statements are case insensitive. SELECT is equivalent to SELECT.
• The wildcard
  • %: Replaces one or more characters
  • _: Replaces only one character
  • [charlist]: Any single character in the character column
  • [^ charlist] or [charlist!]: Any single character not in the character column

Select queries

select * fromThe name of the table// Select allSELECT column name FROM table name/ / select columns

SELECT LastName,FirstName FROM Persons  // Select multiple columnsSELECT DISTINCT column name FROM table name//DISTINCT is used to return a unique DISTINCT value. If there are duplicate values in the column name, only one is returned

Copy the code

Conditions of the querywhere

SELECT column name FROM table name WHERE column operator (optional values are: =,< >,>,<,>=,<=,BETWEEN,LIKE)Copy the code

Use of quotation marks

SQL uses single quotes to surround text values (and most database systems accept double quotes). SELECT * FROM Persons WHERE FirstName='Bush' SELECT * FROM Persons WHERE Year>1965Copy the code

The AND AND OR operators are used to filter records based on more than one condition.

SELECT * FROM Persons WHERE FirstName='Thomas' AND LastName='Carter'

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

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

The ORDER BY statement is used to sort the result set, ascending BY default.

SELECT Company, OrderNumber FROM Orders ORDER BY Company

SELECT Company, OrderNumber FROM Orders ORDER BY Company, OrderNumber

SELECT Company, OrderNumber FROM Orders ORDER BY Company DESC

SELECT Company, OrderNumber FROM Orders ORDER BY Company DESC, OrderNumber ASC
Copy the code

INSERT INTO INSERT statement

INSERT INTO table name VALUES1And the value2,...). INSERT INTO table_name1Column,2,...). VALUES (VALUES1And the value2,...).Copy the code

The update changes

UPDATE Person SET FirstName = new'Fred' WHERE LastName = 'Wilson'  // Modify a single column of a row

UPDATE Person SET Address = 'Zhongshan 23', City = 'Nanjing' 
WHERE LastName = 'Wilson' // Modify a row with multiple columns
Copy the code

DELETE DELETE rows

DELETE FROM table name WHERE column name = value// Delete all rows without deleting the table
DELETE FROM table_name
/ / or
DELETE * FROM table_name
Copy the code

---

SQL senior

TOP specifies the number of records to return

SELECT TOP number|percent column_name(s)
FROM table_name
// equivalent to MySQL
SELECT column_name(s)
FROM table_name
LIMIT number
// Equivalent to Oracle
SELECT column_name(s)
FROM table_name
WHERE ROWNUM <= number

/ / sample
SELECT TOP 2 * FROM Persons
SELECT TOP 50 PERCENT * FROM Persons
Copy the code

LIKE: Searches for the specified pattern in the column in the WHERE clause

SELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern

/ / sample
SELECT * FROM Persons
WHERE City LIKE 'N%'  // Select the people who live in cities starting with "N"

SELECT * FROM Persons
WHERE City LIKE '%g' // Select the people who live in cities ending with "g"

SELECT * FROM Persons
WHERE City LIKE '%lon%' // Select the people who live in the city containing "lon"

SELECT * FROM Persons
WHERE City NOT LIKE '%lon%' // Select people who live in cities that do not contain "lon"

SELECT * FROM Persons
WHERE FirstName LIKE '_eorge' // Select the person whose name follows the first character with "eorge"

SELECT * FROM Persons
WHERE LastName LIKE 'C_r_er' // The last name of the selected record begins with "C", then an arbitrary character, then "r", then an arbitrary character, then "er"

SELECT * FROM Persons
WHERE City LIKE '[ALN]%' // Select people who live in cities beginning with "A" or "L" or "N"

SELECT * FROM Persons
WHERE City LIKE '[!ALN]%' // Select people who do not live in cities beginning with "A" or "L" or "N"
Copy the code

The IN operator allows us to specify multiple values IN the WHERE clause.

SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1,value2,...)

/ / sample
SELECT * FROM Persons
WHERE LastName IN ('Adams'.'Carter') // Select people whose LastName is Adams and Carter
Copy the code

The operator BETWEEN… AND takes the data range between the two values. These values can be numeric, text, or dates.

SELECT column_name(s)
FROM table_name
WHERE column_name
BETWEEN value1 AND value2

/ / sample
SELECT * FROM Persons
WHERE LastName
BETWEEN 'Adams' AND 'Carter' // Displays people between "Adams" (inclusive) and "Carter" (exclusive) in alphabetical order

SELECT * FROM Persons
WHERE LastName
NOT BETWEEN 'Adams' AND 'Carter' // Display people out of range
Copy the code

Alias: Specifies aliases for column and table names

SELECT column_name(s)
FROM table_name
AS alias_name  // SQL Alias syntax for tables

SELECT column_name AS alias_name
FROM table_name // SQL Alias syntax for columns

/ / sample
SELECT po.OrderID, p.LastName, p.FirstName
FROM Persons AS p, Product_Orders AS po
WHERE p.LastName='Adams' AND p.FirstName='John'

SELECT LastName AS Family, FirstName AS Name
FROM Persons
Copy the code

Join is used to query data from two or more tables based on the relationship between the columns in those tables.

• JOIN or INNER JOIN: Returns rows if there is at least one match in the table
• LEFT JOIN: Returns all rows from the LEFT table even if there is no match in the right table

  The LEFT JOIN keyword returns all rows from the LEFT table (table_name1), even if there are no matching rows in the right table (table_name2).
   SELECT column_name(s)
   FROM table_name1
   LEFT JOIN table_name2 
   ON table_name1.column_name=table_name2.column_name
  Copy the code

• RIGHT JOIN: Returns all rows from the RIGHT table even if there is no match in the left table

  // The RIGHT JOIN keyword returns all rows in the RIGHT table (table_name2), even if there are no matching rows in the left table (table_name1).
    SELECT column_name(s)
    FROM table_name1
    RIGHT JOIN table_name2 
    ON table_name1.column_name=table_name2.column_name
  Copy the code

• FULL JOIN: Returns rows as long as there is a match in one of the tables

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons, Orders
WHERE Persons.Id_P = Orders.Id_P 
/ / equivalent to the
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
INNER JOIN Orders
ON Persons.Id_P = Orders.Id_P
ORDER BY Persons.LastName
Copy the code

UNION is used to combine the result sets of two or more SELECT statements

Note that the SELECT statement within the UNION must have the same number of columns. Columns must also have similar data types. Also, the columns in each SELECT statement must be in the same order.

SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2
// Note: By default, the UNION operator takes a different value. If duplicate values are allowed, use UNION ALL.
SELECT column_name(s) FROM table_name1
UNION ALL
SELECT column_name(s) FROM table_name2
Copy the code

SELECT INTO selects data from one table and inserts the data INTO another table

The SELECT INTO statement is often used to create a backup copy of a table or to archive records

// Insert all columns into the new table
SELECT *
INTO new_table_name [IN externaldatabase] 
FROM old_tablename
// Insert only the desired columns into the new table
SELECT column_name(s)
INTO new_table_name [IN externaldatabase] 
FROM old_tablename

//DEMO
SELECT *
INTO Persons_backup
FROM Persons  // create a backup copy of "Persons"

SELECT LastName,FirstName
INTO Persons_backup
FROM Persons // Copy some fields

SELECT LastName,Firstname
INTO Persons_backup
FROM Persons
WHERE City='Beijing'  // Create a table with two columns named "Persons_backup" from "Persons" where Persons live in Beijing

SELECT Persons.LastName,Orders.OrderNo
INTO Persons_Order_Backup
FROM Persons
INNER JOIN Orders
ON Persons.Id_P=Orders.Id_P // create a new table named "Persons_Order_Backup" containing letters from Persons and Orders
Copy the code

Creating a Database

CREATE DATABASE database_name
Copy the code

The new table

The data type	describe
integer(size) ,int(size), smallint(size),tinyint(size)	Only integers are allowed. Specify the maximum number of digits in parentheses
decimal(size,d),numeric(size,d)	Contains numbers with decimals. Size specifies the largest number of digits. D “specifies the maximum number of digits to the right of the decimal point.
char(size)	Contains a string of fixed length (including letters, digits, and special characters). Specify the length of the string in parentheses.
varchar(size)	Contains characters of variable length (including letters, numbers, and special characters). Specify the maximum length of the string in parentheses.
date(yyyymmdd)	Date of accommodation.

The SQL constraints UNIQUE and PRIMARY KEY constraints both provide a guarantee of uniqueness for a column or collection of columns. Each table can have multiple UNIQUE constraints, but each table can have only one PRIMARY KEY constraint

The constraint	role
NOT NULL	Force columns not to accept NULL values
UNIQUE	Constraint uniquely identifies each record in a database table
PRIMARY KEY	Constraint uniquely identifies each record in a database table
FOREIGN KEY	The FOREIGN KEY in one table points to the PRIMARY KEY in the other table
CHECK	Limits the range of values in a column
DEFAULT	Insert default values into columns
AUTO INCREMENT	A unique number is generated when a new record is inserted into the table. By default, AUTO_INCREMENT starts at 1 and increments each new record by 1.

CREATE TABLE TABLE name (column name1Data type, column name2Data type, column name3Data type,....)/ / sample
CREATE TABLE Persons
(
Id_P int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255))Copy the code

CREATE INDEX Creates an INDEX in a table

Indexes make it faster for database applications to find data without reading the entire table.

CREATE INDEX index_name
ON table_name (column_name)

CREATE UNIQUE INDEX index_name
ON table_name (column_name) // CREATE UNIQUE INDEX Creates a UNIQUE INDEX on the table
Copy the code

DROP Drops indexes, tables, and databases

DROP TABLE TABLE name DROP DATABASE DATABASE nameCopy the code

ALTER TABLE Adds, modifies, or deletes columns from an existing TABLE

ALTER TABLE table_name
ADD column_name datatype / / add columns

ALTER TABLE table_name 
DROP COLUMN column_name // Drop columns from the table

ALTER TABLE table_name
ALTER COLUMN column_name datatype / / modify
Copy the code

---

SQL function

// The AVG function returns the average value of the column. NULL values are not included in the calculation.
SELECT AVG(column_name) FROM table_name

SELECT Customer FROM Orders
WHERE OrderPrice>(SELECT AVG(OrderPrice) FROM Orders)

The COUNT() function returns the number of rows matching the specified condition.
SELECT COUNT(column_name) FROM table_name  //COUNT(column_name) Returns the number of values for the specified column (NULL does not COUNT)
SELECT COUNT(*) FROM table_name //COUNT(*) returns the number of records in the table
SELECT COUNT(DISTINCT column_name) FROM table_name //COUNT(DISTINCT column_name) The function returns the number of DISTINCT values for the specified column

The FIRST() function returns the value of the FIRST record in the specified field.
SELECT FIRST(column_name) FROM table_name

The LAST() function returns the value of the LAST record in the specified field.
SELECT LAST(column_name) FROM table_name

//MIN and MAX can also be used for text columns to get the highest or lowest values in alphabetical order.
The MAX function returns the maximum value in a column. NULL values are not included in the calculation.
The MIN function returns the minimum value in a column. NULL values are not included in the calculation.
SELECT MAX(column_name) FROM table_name
SELECT MIN(column_name) FROM table_name

// The SUM function returns the total number of numeric columns.
SELECT SUM(column_name) FROM table_name

The GROUP BY statement is used in conjunction with the aggregate function to GROUP result sets BY one or more columns.
SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name

// HAVING: Added the HAVING clause in SQL because the WHERE keyword cannot be used with the aggregate function
SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value

The UCASE function converts the field value to uppercase.
SELECT UCASE(column_name) FROM table_name

The LCASE function converts the value of a field to lowercase.
SELECT LCASE(column_name) FROM table_name

The MID function is used to extract characters from text fields. Column_name: required. The field to extract characters from. ; Start: required. Specify the starting position (the starting value is 1). ; Length: optional. The number of characters to return. If omitted, the MID() function returns the rest of the text.
SELECT MID(column_name,start[,length]) FROM table_name

The LEN function returns the length of the value in the text field.
SELECT LEN(column_name) FROM table_name

The ROUND function is used to ROUND a numeric field to the specified decimal number.
SELECT ROUND(column_name,decimals) FROM table_name

The NOW function returns the current date and time.
SELECT NOW() FROM table_name

The FORMAT function is used to FORMAT the display of fields.
SELECT FORMAT(column_name,format) FROM table_name
Copy the code