• A basis,
• Create table
• 3. Modify the table
• Four, insert,
• Five, update,
• Six, delete,
• Seven, query
• Eight, sorting,
• Nine, filtering,
• 10. Wildcards
• 11. Calculation fields
• Xii. Functions
• 13. Grouping
• Subquery
• Xv. Connection
• 16. Combination query
• Xvii. View
• Stored Procedures
• 19. Cursors
• 20. Trigger
• 21. Business management
• 22. Character Sets
• 23. Permission Management

A basis,

Schemas define information about how data is stored, what data is stored, and how the data is decomposed. Both databases and tables have schemas.

Primary key values are not allowed to be modified or reused (deleted primary key values cannot be assigned to the primary key of a new row).

Structured Query Language (SQL), standard SQL is managed by the ANSI Standards Committee and is called ANSI SQL. Each DBMS has its own implementation, such as PL/SQL, Transact-SQL, etc.

SQL statements are case insensitive, but whether database table names, column names, and values are distinct depends on the specific DBMS and configuration.

SQL supports the following three types of annotations:

# commentsSELECT *
FROM mytable; Comments -
/* Note 1 note 2 */
Copy the code

Database creation and use:

CREATE DATABASE test;
USE test;
Copy the code

Create table

CREATE TABLE mytable (
  # intType, not empty, automatically increment idINT NOT NULL AUTO_INCREMENT,
  # intThe value cannot be null. The default value is1, not null col1INT NOT NULL DEFAULT 1, # is a string of variable length45Can be empty col2VARCHAR(45) NULL, # date type, can be null col3DATE NULLSet primary key to idPRIMARY KEY (`id`));
Copy the code

3. Modify the table

Add columns

ALTER TABLE mytable
ADD col CHAR(20);
Copy the code

Delete the column

ALTER TABLE mytable
DROP COLUMN col;
Copy the code

Delete table

DROP TABLE mytable;
Copy the code

Four, insert,

INSERT INTO mytable(col1, col2)
VALUES(val1, val2);
Copy the code

INSERT INTO mytable1(col1, col2)
SELECT col1, col2
FROM mytable2;
Copy the code

CREATE TABLE newtable AS
SELECT * FROM mytable;
Copy the code

Five, update,

UPDATE mytable
SET col = val
WHERE id = 1;
Copy the code

Six, delete,

DELETE FROM mytable
WHERE id = 1;
Copy the code

TRUNCATE TABLE mytable;
Copy the code

Seven, query

DISTINCT

SELECT DISTINCT col1, col2
FROM mytable;
Copy the code

LIMIT

Limit the number of rows returned. You can take two arguments. The first argument is the start row, starting at 0; The second argument is the total number of rows returned.

Return to the first 5 lines:

SELECT *
FROM mytable
LIMIT 5;
Copy the code

SELECT *
FROM mytable
LIMIT 0.5;
Copy the code

Return lines 3 to 5:

SELECT *
FROM mytable
LIMIT 2.3;
Copy the code

Eight, sorting,

SELECT *
FROM mytable
ORDER BY col1 DESC, col2 ASC;
Copy the code

Nine, filtering,

Unfiltered data is very large, resulting in excess data being transmitted over the network, wasting network bandwidth. Therefore, try to use SQL statements to filter unnecessary data, rather than transferring all data to the client and then filtering by the client.

SELECT *
FROM mytable
WHERE col IS NULL;
Copy the code

The following table shows the operators available for the WHERE clause

Note that NULL is different from either a 0 or an empty string.

AND AND OR are used to join multiple filter conditions. Priority is given to AND. When a filter expression involves more than one AND AND OR, () can be used to determine the priority, making the priority relationship clearer.

The IN operator is used to match a set of values, which can also be followed by a SELECT clause to match a set of values from a subquery.

The NOT operator is used to negate a condition.

10. Wildcards

SELECT *
FROM mytable
WHERE col LIKE '[^AB]%'; -- Any text that does not begin with A and B
Copy the code

11. Calculation fields

Converting and formatting data on a database server is often much faster than on a client, and less data can be converted and formatted to reduce network traffic.

A computed field usually needs to be aliased using AS, otherwise the field is printed AS a computed expression.

SELECT col1 * col2 AS alias
FROM mytable;
Copy the code

CONCAT() is used to join two fields. Many databases use Spaces to fill a value to a column width, so join results in unnecessary whitespace. TRIM() removes the leading and trailing whitespace.

SELECT CONCAT(TRIM(col1), '('.TRIM(col2), ') ') AS concat_col
FROM mytable;
Copy the code

Xii. Functions

summary

AVG() ignores NULL lines.

Use DISTINCT to aggregate different values.

SELECT AVG(DISTINCT col1) AS avg_col
FROM mytable;
Copy the code

Text processing

SELECT *
FROM mytable
WHERE SOUNDEX(col1) = SOUNDEX('apple')
Copy the code

Date and time processing

• Date format: YYYY-MM-DD
• Time format: HH:MM:SS

mysql> SELECT NOW();
Copy the code

The 2018-4-14 20:25:11Copy the code

Numerical processing

13. Grouping

Put rows with the same data value in the same group.

The same group of data can be processed using a summary function, such as finding the average of the group of data.

The specified group field can be grouped and sorted automatically by the field.

SELECT col, COUNT(*) AS num
FROM mytable
GROUP BY col;
Copy the code

GROUP BY is automatically sorted BY GROUP fields, and ORDER BY can also be sorted BY summary fields.

SELECT col, COUNT(*) AS num
FROM mytable
GROUP BY col
ORDER BY num;
Copy the code

WHERE to filter rows and HAVING to filter groups. Row filtering should precede group filtering.

SELECT col, COUNT(*) AS num
FROM mytable
WHERE col > 2
GROUP BY col
HAVING num > = 2;
Copy the code

Grouping requirements:

• The GROUP BY clause appears after the WHERE clause and before the ORDER BY clause.
• Every field in the SELECT statement except the summary field must be given in the GROUP BY clause;
• NULL rows are grouped separately;
• Most SQL implementations do not support data types with variable length GROUP BY columns.

Subquery

SELECT *
FROM mytable1
WHERE col1 IN (SELECT col2
               FROM mytable2);
Copy the code

SELECT cust_name, (SELECT COUNT(*)
                   FROM Orders
                   WHERE Orders.cust_id = Customers.cust_id)
                   AS orders_num
FROM Customers
ORDER BY cust_name;
Copy the code

Xv. Connection

Joins are used to JOIN multiple tables, use the JOIN keyword, and condition statements use ON instead of WHERE.

Joins can replace subqueries and are generally more efficient than subqueries.

Column names, computed fields, and table names can be aliased with AS to simplify SQL statements and join the same tables.

In the connection

SELECT A.value, B.value
FROM tablea AS A INNER JOIN tableb AS B
ON A.key = B.key;
Copy the code

SELECT A.value, B.value
FROM tablea AS A, tableb AS B
WHERE A.key = B.key;
Copy the code

Since the connection

A self-join can be considered a type of inner join, except that the joined table is itself.

A list of employees, including the names of employees and their departments, to find the names of all employees in the same department as Jim.

Subquery version

SELECT name
FROM employee
WHERE department = (
      SELECT department
      FROM employee
      WHERE name = "Jim");
Copy the code

Self-connecting version

SELECT e1.name
FROM employee AS e1 INNER JOIN employee AS e2
ON e1.department = e2.department
      AND e2.name = "Jim";
Copy the code

A natural connection

SELECT A.value, B.value
FROM tablea AS A NATURAL JOIN tableb AS B;
Copy the code

Outer join

The outer join preserves those rows that are not associated. It is divided into left outer join, right outer join and full outer join. The left outer join is to keep the rows that are not associated with the left table.

Retrieves order information for all customers, including those who do not already have order information.

SELECT Customers.cust_id, Customer.cust_name, Orders.order_id
FROM Customers LEFT OUTER JOIN Orders
ON Customers.cust_id = Orders.cust_id;
Copy the code

Customers table:

The orders table:

Results:

16. Combination query

SELECT col
FROM mytable
WHERE col = 1
UNION
SELECT col
FROM mytable
WHERE col =2;
Copy the code

Xvii. View

A view is a virtual table that contains no data and therefore cannot be indexed.

The operation on the view is the same as the operation on the normal table.

Views have the following benefits:

• Simplify complex SQL operations, such as complex joins;
• Use only part of the data from the actual table;
• Data security is ensured by only giving users the permission to access the view.
• Change the data format and presentation.

CREATE VIEW myview AS
SELECT Concat(col1, col2) AS concat_col, col3*col4 AS compute_col
FROM mytable
WHERE col5 = val;
Copy the code

Stored Procedures

delimiter //

create procedure myprocedure( out ret int )
    begin
        declare y int;
        select sum(col1)
        from mytable
        into y;
        select y*y into ret;
    end //

delimiter ;
Copy the code

call myprocedure(@ret);
select @ret;
Copy the code

19. Cursors

Cursors can be used in stored procedures to move through a result set.

Cursors are primarily used in interactive applications where users need to browse and modify arbitrary rows in a dataset.

Four steps to using a cursor:

1. Declare a cursor, which does not actually retrieve the data;
2. Open the cursor;
3. Data retrieval;
4. Close the cursor;

delimiter //
create procedure myprocedure(out ret int)
    begin
        declare done boolean default 0;

        declare mycursor cursor for
        select col1 frommytable; # define a continue handler whensqlstate '02000'When this condition occurs, it is executedset done = 1
        declare continue handler for sqlstate '02000' set done = 1;

        open mycursor;

        repeat
            fetch mycursor into ret;
            select ret;
        until done end repeat;

        close mycursor;
    end //
 delimiter ;
Copy the code

20. Trigger

CREATE TRIGGER mytrigger AFTER INSERT ON mytable
FOR EACH ROW SELECT NEW.col into @result;

SELECT @result; -- Get results
Copy the code

21. Business management

Basic terms:

• A transaction is a group of SQL statements;
• Rollback refers to the process of revoking specified SQL statements.
• Commit refers to writing the result of an unstored SQL statement to a database table.
• Savepoints are temporary placeholders set up in a transaction to which you can publish rollback (as opposed to the entire transaction).

The SELECT statement cannot be rolled back, and there is no point in rolling back the SELECT statement. CREATE and DROP statements cannot be rolled back either.

MySQL commits transactions implicitly by default. Each statement executed is treated as a transaction and committed. When a START TRANSACTION statement occurs, implicit commit is turned off; When a COMMIT or ROLLBACK statement is executed, the transaction is automatically closed and implicit COMMIT resumes.

Setting autoCommit to 0 disables automatic commit. The AutoCOMMIT tag is per-connection, not per-server.

If no reservation point is set, ROLLBACK will ROLLBACK to the START TRANSACTION statement. If a reservation point is set and specified in ROLLBACK, it is rolled back to that reservation point.

START TRANSACTION
//.SAVEPOINT delete1
//.ROLLBACK TO delete1
//.COMMIT
Copy the code

22. Character Sets

CREATE TABLE mytable
(col VARCHAR(10) CHARACTER SET latin COLLATE latin1_general_ci )
DEFAULT CHARACTER SET hebrew COLLATE hebrew_general_ci;
Copy the code

SELECT *
FROM mytable
ORDER BY col COLLATE latin1_general_ci;
Copy the code

23. Permission Management

MySQL account information is stored in the MySQL database.

USE mysql;
SELECT user FROM user;
Copy the code

Create account

The newly created account does not have any permissions.

CREATE USER myuser IDENTIFIED BY 'mypassword';
Copy the code

Changing the Account Name

RENAME USER myuser TO newuser;
Copy the code

Delete the account

DROP USER myuser;
Copy the code

Check the permissions

SHOW GRANTS FOR myuser;
Copy the code

Grant permissions

The account is defined as username@host and username@% uses the default hostname.

GRANT SELECT.INSERT ON mydatabase.* TO myuser;
Copy the code

Remove permissions

GRANT and REVOKE control access at several levels:

• GRANT ALL and REVOKE ALL;
• For the entire database, use ON database.
• For a specific table, use ON database.table;
• Specific columns;
• Specific stored procedures.

REVOKE SELECT.INSERT ON mydatabase.* FROM myuser;
Copy the code

Change password

Encryption must be done using the Password() function.

SET PASSWROD FOR myuser = Password('new_password');
Copy the code

Turn: github.com/CyC2018/CS-…