MySQL Series 4 SQL Syntax

Tutorial Series

MySQL series: Basic concepts of MySQL relational database
MariaDB-server installation of MySQL series
MySQL Series II Multi-Instance Configuration
MySQL Series 3 Basics
MySQL series five views, stored functions, stored procedures, triggers
MySQL series 6 users and authorization
MySQL Series 7 MySQL Storage Engine
MySQL Series 8 MySQL Server Variables
MySQL series 9 MySQL query cache and index
MySQL Series 10 MySQL Transaction Isolation to Implement Concurrency Control
MySQL Series 11 Logging
MySQL Series 12 Backup and Recovery
MySQL Series 13 MySQL Replication
MySQL Series 14 MySQL High Availability Implementation
MySQL series 15 MySQL common configuration and performance stress test

1. Introduction and specification of SQL language

​ is a special-purpose programming language used for managing relational database management systems (RDBMS) or for stream processing in relational stream data management systems (RDSMS).

• In the 1970s, IBM developed SQL for DB2
• In 1981, IBM launched the SQL/DS database
• Industry standards: T-SQL from Microsoft and Sybase, PL/SQL from Oracle
• SQL, the standard language used by relational databases, was originally approved in 1986 based on IBM's implementation. In 1987, the International Organization for Standardization (ISO) adopted ANSI (American National Standards Institute) SQL as an international standard.
• SQL: ANSI SQL - SQL-86, SQL-89, SQL-92, SQL-99, SQL-03
  

SQL language specification

1. In the database system, SQL statements are not case sensitive (uppercase is recommended)
2. But string constants are case sensitive
3. SQL statements can be written in one line or multiple lines and end with ";"
4. Keywords cannot span multiple lines or be abbreviated.
5. Use spaces and indentation to improve readability
6. Clauses are usually placed on separate lines to facilitate editing and improve readability.
7. Notes:

• SQL Standard:
  • /*Comment content*/ Multi-line comment
  • -- Single-line comment, note the spaces
• MySQL Notes:#
  

Naming conventions for database objects

- Must start with a letter
- Can include numbers and three special characters (# _ $)
- Do not use MySQL reserved words
- Objects in the same database (Schema) cannot have the same name

Classification of SQL statements

DDL: Data Defination Language

- CREATE, DROP, ALTER

DML: Data Manipulation Language

- INSERT, DELETE, UPDATE

DCL: Data Control Language

- GRANT, REVOKE

DQL: Data Query Language

-SELECT

2. Database Operation

1. Create a library

CREATE DATABASE [IF NOT EXISTS] db_name; Create a database

CHARACTER SET 'character set name' Set the character set type

COLLATE 'collate name' sets the collation

View all supported character sets: SHOW CHARACTER SET;

View all supported sorting rules: SHOW COLLATION;

MariaDB [(none)]> CREATE DATABASE IF NOT EXISTS testdb;

2. Delete the library

I don't

3. View the database list

SHOW DATABASES;

3. Table Operation

1. Create a table

Method 1: Create directly
CREATE TABLE [IF NOT EXISTS] tbl_name (create_definition,...)

MariaDB [testdb]> CREATE TABLE IF NOT EXISTS students (id TINYINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,name VARCHAR(30) NOT NULL,phone CHAR(11),gender ENUM('M','F')); 

Method 2: Create by querying an existing table; the new table will be directly inserted with the data from the query

CREATE TABLE [IF NOT EXISTS] tbl_name select_statement

MariaDB [testdb]> CREATE TABLE user SELECT user,host,password FROM mysql.user;

If we just want to imitate the query of the old table and create a table without records, we can add the condition WHERE 0=1;

MariaDB [testdb]> CREATE TABLE user2 SELECT user,host,password FROM mysql.user WHERE 0=1;

Method 3: Create a table by copying the table structure of an existing table, but not copying the data

CREATE TABLE [IF NOT EXISTS] tbl_name LIKE old_tbl_name

MariaDB [testdb]> CREATE TABLE user3 LIKE mysql.user;

2. Modify the table

ALTER TABLE tbl_name [alter_specification [, alter_specification] ...]

Add attribute ADD

MariaDB [testdb]> ALTER TABLE students ADD age TINYINT AFTER name;

DROP

MariaDB [testdb]> ALTER TABLE students DROP phone;

CHANGE, MODIFY

MariaDB [testdb]> ALTER TABLE students CHANGE age ages TINYINT(2) NOT NULL;

MariaDB [testdb]> ALTER TABLE students MODIFY gender ENUM('M','F');

3. Delete table

MariaDB [testdb]> DROP TABLE user3;

4. View table

SHOW TABLES; List all tables in the library

DESC [db_name.]tb_name; View the table structure

SHOW CREATE TABLE tbl_name; View the command to create the table

SHOW TABLE STATUS LIKE 'tbl_name'; View table status

SHOW TABLE STATUS FROM db_name; View the status of all tables in the specified database

SHOW ENGINES; View all storage engines

4. DML: Data Manipulation Language

MariaDB [testdb]> DESC students; #Sample table+--------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+----------------+
| id | tinyint(3) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(30) | NO | | NULL | |
| ages | tinyint(2) | NO | | NULL | |
| gender | enum('M','F') | YES | | NULL | |
+--------+---------------------+------+-----+---------+----------------+

1. INSERT inserts data

Insert a single record

INSERT INTO tb_name (col1,col2,...) VALUES (val1,val2,...);

MariaDB [testdb]> INSERT students(id,name,ages,gender) VALUES (1,'tom',26,'M');
MariaDB [testdb]> INSERT students(name,ages,gender) VALUES ('jerry',19,'M'); 
MariaDB [testdb]> INSERT students(name,ages,gender) VALUES ('maria',19,'M');
MariaDB [testdb]> INSERT students SET name='ouyangfeng',ages=56,gender='M';

Insert multiple records

INSERT INTO tb_name (col1,col2,...) VALUES (val1,val2,...)[,(val1,val2,...),...];

MariaDB [testdb]> INSERT students(name,ages,gender) VALUES ('xiaolongnv',18,'F'),('dongfangbubai',28,'F');

MariaDB [testdb]> SELECT * FROM students;
+----+---------------+------+--------+
| id | name | ages | gender |
+----+---------------+------+--------+
| 1 | tom | 26 | M |
| 2 | jerry | 19 | M |
| 3 | maria | 19 | M |
| 4 | xiaolongnv | 18 | F |
| 5 | dongfangbubai | 28 | F |
| 6 | ouyangfeng | 56 | M |
+----+---------------+------+--------+

Query data from other tables and save it to this table

MariaDB [testdb]> ALTER TABLE students ADD address TEXT; #Add a field for testing

MariaDB [testdb]> INSERT students(name,address) SELECT user,host FROM mysql.user;

MariaDB [testdb]> SELECT * FROM students;
+----+---------------+------+--------+-----------+
| id | name | ages | gender | address |
+----+---------------+------+--------+-----------+
| 1 | tom | 26 | M | NULL |
| 2 | jerry | 19 | M | NULL |
| 3 | maria | 19 | M | NULL |
| 4 | xiaolongnv | 18 | F | NULL |
| 5 | dongfangbubai | 28 | F | NULL |
| 6 | ouyangfeng | 56 | M | NULL |
| 7 | root | 0 | NULL | 127.0.0.1 |
| 8 | root | 0 | NULL | ::1 |
| 9 | | 0 | NULL | centos7 |
| 10 | root | 0 | NULL | centos7 |
| 11 | | 0 | NULL | localhost |
| 12 | root | 0 | NULL | localhost |
+----+---------------+------+--------+-----------+

2. UPDATE to modify data

UPDATE tbl_name SET col1=value1,col2=value2,... WHERE col=value;

MariaDB [testdb]> UPDATE students SET gender='F' WHERE id=3;

3. DELETE

MariaDB [testdb]> DELETE FROM students WHERE name=''; #Delete records with empty names MariaDB [testdb]> TRUNCATE TABLE user; #Situation table records

Note: There must be a restriction (WHERE | LIMIT), otherwise the specified fields of all rows will be modified

5. SELECT: Data Query

• AS: Alias
• WHERE: specifies the filter conditions to achieve the "select" function
  • +, -, *, /, %: Arithmetic operators
  • =, !=, <>, >, <, >=, <=: comparison operators
  • BETWEEN min_num AND max_num: between min_num and max_mun
  • IN (element1,element2,...): in element...
  • IS NULL: empty
  • IS NOT NULL: Not empty
  • LIKE: to match, like. . .
    %: any character of any length
    _: A single arbitrary character
  • RLIKE: Regular expression, not recommended
  • REGEXP: Same as above
  • NOT, AND, OR, XOR: Logical operators
• GROUP BY: Group the query results according to the specified conditions for "aggregation" operations
  • AVG() Average
  • MAX() Maximum number
  • MIN() Minimum number
  • COUNT() Statistics
  • SUM()
  • HAVING: Specifies filtering conditions for the results of group aggregation operations. Similar to WHERE, but can only be used in groups
• ORDER BY: Sorting
  • ASC: positive sequence, default
  • DESC: Reverse order
  • -KEYWORD: Adding - before the keyword during sorting can avoid putting NULL at the front.
• LIMIT [[offset,]row_count]: Limit the number of rows output for query results
  

1. Selection

MariaDB [testdb]> SELECT * FROM students WHERE name='maria'; #Query maria's informationMariaDB [testdb]> SELECT * FROM students WHERE id BETWEEN 2 AND 5; #Query the information of students 2 to 5MariaDB [testdb]> SELECT * FROM students WHERE name IN ('jerry','xiaolongnv'); #Query the information of jerry and xiaolongnvMariaDB [testdb]> SELECT * FROM students WHERE gender IS NOT NULL; #Query the information where age is not emptyMariaDB [testdb]> SELECT * FROM students WHERE name LIKE '%o%'; #Query the information where the name contains'o'

2. Projection

MariaDB [testdb]> SELECT user AS user, host AS host, password AS password FROM mysql.user;

3. Grouping

MariaDB [testdb]> SELECT gender,AVG(ages) FROM students GROUP BY gender; #Query the average age of boys and girlsMariaDB [testdb]> SELECT gender,AVG(ages) FROM students GROUP BY gender HAVING gender='M'; #Only display the average age information of boys

4. Sorting

MariaDB [testdb]> SELECT * FROM students ORDER BY ages DESC; #Sort by age, display in reverse orderMariaDB [testdb]> SELECT * FROM students WHERE ages > 0 ORDER BY ages LIMIT 3; #Sort by age, filter those with age greater than 0, sort in ascending order, and take the first three records

6. Multi-table query

For the sake of practice, we will expand the table

MariaDB [testdb]> DELETE FROM students WHERE id BETWEEN 7 AND 12;
MariaDB [testdb]> CREATE TABLE score (id TINYINT(2) UNSIGNED AUTO_INCREMENT PRIMARY KEY, score TINYINT(3));
MariaDB [testdb]> ALTER TABLE students ADD sid TINYINT(2); 
MariaDB [testdb]> UPDATE students SET sid=6 WHERE id=6;
MariaDB [testdb]> INSERT score SET score=87;
MariaDB [testdb]> SELECT * FROM students;
+----+---------------+------+--------+---------+------+
| id | name | ages | gender | address | sid |
+----+---------------+------+--------+---------+------+
| 1 | tom | 26 | M | NULL | 1 |
| 2 | jerry | 19 | M | NULL | 2 |
| 3 | maria | 19 | F | NULL | 3 |
| 4 | xiaolongnv | 18 | F | NULL | 4 |
| 5 | dongfangbubai | 28 | F | NULL | 5 |
| 6 | ouyangfeng | 56 | M | NULL | 6 |
+----+---------------+------+--------+---------+------+
MariaDB [testdb]> SELECT * FROM score;   
+----+-------+
| id | score |
+----+-------+
| 1 | 99 |
| 2 | 98 |
| 3 | 88 |
| 4 | 68 |
| 5 | 78 |
| 6 | 87 |
+----+-------+

JOIN ON: Cross Join

INNER JOIN ON: Inner join

LEFT OUTER JOIN ON: Left outer join

RIGHT OUTER JOIN ON: Right outer join

UNION ON: Full outer join

MariaDB [testdb]> SELECT * FROM students AS s,score AS o WHERE s.sid=o.id; #Intersection of two tables

1. Cross connection

MariaDB [testdb]> SELECT * FROM students JOIN score;

2. Inner Join

MariaDB [testdb]> SELECT t.name,s.score FROM students AS t INNER JOIN score AS s ON t.sid=s.id;
+---------------+-------+
| name | score |
+---------------+-------+
| tom | 99 |
| jerry | 98 |
| maria | 88 |
| xiaolongnv | 68 |
| dongfangbubai | 78 |
| ouyangfeng | 87 |
+---------------+-------+

3. Outer Join

MariaDB [testdb]> SELECT t.name,s.score FROM students AS t LEFT JOIN score AS s ON t.sid=s.id; #Left outer join +---------------+-------+
| name | score |
+---------------+-------+
| tom | 99 |
| jerry | 98 |
| maria | 88 |
| xiaolongnv | 68 |
| dongfangbubai | 78 |
| ouyangfeng | 87 |
+---------------+-------+

MariaDB [testdb]> SELECT * FROM students AS t RIGHT JOIN score AS s ON t.sid=s.id; #Right outer join

4. Full outer join

MariaDB [testdb]> SELECT name,address FROM students
    -> UNION
    -> SELECT user,host FROM mysql.user;
+---------------+-----------+
| name | address |
+---------------+-----------+
| tom | NULL |
| jerry | NULL |
| maria | NULL |
| xiaolongnv | NULL |
| dongfangbubai | NULL |
| ouyangfeng | NULL |
| root | 127.0.0.1 |
| root | ::1 |
| | centos7 |
| root | centos7 |
| | localhost |
| root | localhost |
+---------------+-----------+

5. Self-connection

MariaDB [testdb]> ALTER TABLE students ADD tid TINYINT(2); #Add another tid fieldMariaDB [testdb]> SELECT * FROM students;
+----+---------------+------+--------+---------+------+------+
| id | name | ages | gender | address | sid | tid |
+----+---------------+------+--------+---------+------+------+
| 1 | tom | 26 | M | NULL | 1 | 2 |
| 2 | jerry | 19 | M | NULL | 2 | 1 |
| 3 | maria | 19 | F | NULL | 3 | 4 |
| 4 | xiaolongnv | 18 | F | NULL | 4 | 5 |
| 5 | dongfangbubai | 28 | F | NULL | 5 | 4 |
| 6 | ouyangfeng | 56 | M | NULL | 6 | 4 |
+----+---------------+------+--------+---------+------+------+

MariaDB [testdb]> SELECT s1.name AS studentname,s2.name AS teachername FROM students AS s1 INNER JOIN students AS s2 ON s1.id=s2.tid;
+---------------+---------------+
| studentname | teachername |
+---------------+---------------+
| jerry | tom |
| tom | jerry |
| xiaolongnv | maria |
| dongfangbubai | xiaolongnv |
| xiaolongnv | dongfangbubai |
| xiaolongnv | ouyangfeng |
+---------------+---------------+

7. Subquery

Subquery: A query statement is nested in a query statement. It has poor performance and is a query based on the query result of a certain statement.

1. Subqueries used in the WHERE clause

Used for subqueries in comparison expressions; subqueries can only return a single value

MariaDB [testdb]> SELECT name, ages FROM students WHERE ages > (SELECT AVG(ages) FROM students); #Query students older than the average age

Subqueries used in IN: The subquery should be a single key query and return one or more values ​​from the list of components

2. Subqueries in the FROM clause

SELECT tb_alias.col1,... FROM (SELECT clause) AS tb_alias WHERE Clause;

8. Data Types

Choosing the right data type is critical to achieving high performance. Three principles apply:

• Smaller is usually better; try to use the smallest data type that can correctly store the data.
• Keep it simple. Operations on simple data types usually require fewer CPU cycles.
• Try to avoid NULL. Columns containing NULL are more difficult to optimize for MySQL.
  

1. Numerical type

Exact value

• INT
  • TINYINT Micro Plastic Surgery 1
  • SMALLINT Small integer 2
  • MEDIUMINT Medium integer 3
  • INT integer 4
  • BIGINT Large integer 8
• DECIMAL exact point type

Approximate values

• FLOAT Single precision floating point type4
• DOUBLE Double precision floating point type8
• REAL
• BIT

2. Character type

Fixed length

- CHAR (case insensitive) 255

- BINARY (case sensitive)

Lengthening

• VARCHAR (case insensitive) 65,535
• VARBINNARY (case-sensitive)

TEXT (case insensitive)

• TINYTEXT 255
• TEXT 65,535
• MEDIUMTEXT 16,777,215
• LONGTEXT 4,294,967,295

BLOB (case sensitive)

• TINYBLOB micro binary large object 255
• BLOB Binary Large Object 64K
• MEDIUMBLOB Medium binary large object 16M
• LONGBLOB Long binary large object 4G

ENUM enumeration 65535 variations

SET sets 1-64 strings, which can be combined at will

3. Date and time type

• DATE 3
• TIME 3
• DATETIME 8
• TIMESTAMP 4
• YEAR{2|4} 1

4. Boolean

• BOOL, BOOLEAN: Boolean type, a synonym for TINYINT(1). A value of zero is considered false. Non-zero values ​​are considered true.
  

Refer to the official documentation: https://dev.mysql.com/doc/refman/5.5/en/data-types.html

Summarize

This is the end of this article about SQL syntax. For more relevant SQL syntax content, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!