The Way to MySQL Learning: SQL Scripting Language

Original Link: https://my.oschina.net/u/1772925/blog/600871

Using the MySQL database, you first install the MySQL database, and all the SQL scripts in this article are tested and executed on MySQL.

Install Mysql server;
Install the Mysql workbench client to manage mysql with a graphical interface.
Install the phpMyadmin client to graphically manage Mysql via bs.

1. Mysql server operations:

Take windows for example, win+r enters the cmd interface

Start server: net start mysql
 Stop server: net stop mysql
 Connect to server: mysql-h localhost-u root-p password

2. Basic Database operations

create database db_bookstore; //Create a database:
use database db_bookstore; //Use database: 
drop database db_bookstore; //Delete database:

3. DDL table operations

(1) Create tables

creat table table_name(id int not null auto_increment primary key,
name char(50)
) if not exists;

(2) View table structure

show columns from tb_name from db_name;
desc table_name;

(3) Delete tables:

drop table tb_name if exists;

(4) Modify table: alter tb_name alter/modify/change [column]...
ALTER COLUMN: Set or delete column defaults (very fast)
Example:

alter table film alter column rental_duration set default 5; 
alter table film alter column rental_duration drop default;

CHANGE COLUMN: Rename of columns, change of column types, and move of column positions
Example:

ALTER TABLE MyTable CHANGE COLUMN foo bar VARCHAR(32) NOT NULL FIRST; 
ALTER TABLE MyTable CHANGE COLUMN foo bar VARCHAR(32) NOT NULL AFTER baz;

MODIFY COLUMN: Besides not being able to rename columns, he does the same work as CHANGE COLUMN
Example:

ALTER TABLE MyTable MODIFY COLUMN foo VARCHAR(32) NOT NULL AFTER baz;

(5) Renaming tables

ALTER tb_name RENAME [AS] new_tb_name;
RENAME TABLE tb_name TO new_tb_name;

Note: [] is optional, including change, modify, COLLUMN full after alter

4. DML Language: Data Manipulation Language

(1) Insert

INSERT INTO tb_name(field1,...) VALUES(value1,...);

(2) Modification

UPDATE tb_name SET f1=v1,f2=v2...;

(3) Delete

DELETE FROM tb_name WHERE condition

5.DQL: Data Query, SELECT Theme

SELECT f1,f2,... FROM tb_name
WHERE primary_constraint
GROUP BY grouping_columns
ORDER BY column_name DESC/ASC
HAVING secdonary_constraint
LIMIT count;

Key Syntax:

SELECT *
SELECT DISTINCT *
SELECT TOP count *

(1) Form query

SELECT * FROM tb_name; // All Fields
SELECT id,name FROM tb_name; // Specify Fields
SELECT * FROM tb_name WHERE id<5 // Query qualified fields

(2) IN, NOT IN, BETWEEN AND (this indicates the scope of the field, followed by an IN for subqueries)

SELECT * FROM tb_name WHERE id IN(1,2,3);
SELECT * FROM tb_name WHERE id NOT IN(1,2,3);
SELECT * FROM tb_name WHERE id BETWEEN 1 AND 5;

(3) Use LIKE + wildcards

%: match one or more characters
_: Matches any single character

SELECT * FROM tb_name WHERE user LIKE 'm%r_';// with m Start with a few characters in the middle, r End of last character

(4) Null value query IS NULL,IS NOT NULL

SELECT * FROM tb_name WHERE row IS NULL;
SELECT * FROM tb_name WHERE row IS NOT NULL;

(5) Multi-Conditional Query AND, OR

SELECT * FROM tb_name WHERE id=1 AND name='Jack';
SELECT * FROM tb_name WHERE id=1 OR name='Jack';

(6) Remove duplicate lines

SELECT DISTINCT * FROM tb_name;

(7) Using ORDER BY [ASC/DESC]

SELECT * FROM tb_name ORDER BY id DESC
SELECT * FROM tb_name ORDER BY id ASC

(8) GROUP BY clause
Note: Fields that do not appear in the GROUP BY clause cannot appear in the SELECT clause, except aggregate functions
If a field does not appear in GROUP BY in the SELECT clause, the result shows only one record for each group.
The general form is:

SELECT field,func_name(field) FROM tb_name GROUP BY field;

(9) Multi-field grouping: Cartesian product of two fields

SELECT field1,field2 FROM tb_name GROUP BY field1,field2;

(10) LIMIT Limited Result Set

SELECT * FROM db_name WHERE id<5 LIMIT 3; // 3 lines showing results
SELECT * FROM db_name WHERE id<5 LIMIT 5,10; // 10 lines starting at line 5

(11) Aggregation function: MIN(),AGV,MAX,COUNT
The feature of aggregate function is to calculate a value based on a set of data. Aggregate function can only calculate non-NULL values, and NULL values are ignored.

(12) Join query:

Understanding: There are at least two tables, and the result of the query is a splicing of two tables that meet some criteria;
The Cartesian product of two tables without query criteria;

(1) Inner join queries, the most common being equal join

SELECT * FROM tb_left,tb_right WHERE tb_left.id=tb_right.id;
SELECT * FROM tb_left INNER JOIN tb_right WHERE tb_left.id=tb_right.id;

(2) External join query:
Left Outer Join: Inner Join result, also contains left table data that does not meet the criteria, right table columns plus NULL

SELECT * FROM tb_left LEFT [OUTER] JOIN tb_right WHERE tb_left.id=tb_right.id;

Right Outer Connection:

SELECT * FROM tb_left RIGHT [OUTER] JOIN tb_right WHERE tb_left.id=tb_right.id;

External connection:

SELECT * FROM tb_left FULL OUTER JOIN tb_right WHERE tb_left.id=tb_right.id;

(13) Subquery:

①IN

SELECT * FROM tb_name WHERE id IN(SELECT id FROM tb_2);

②ANY

SELECT * FROM tb_name WHERE id<ANY(SELECT id FROM tb_2);

③ALL

SELECT * FROM tb_name WHERE id<ALL(SELECT id FROM tb_2);

(4) EXISTS subquery returns a bool value

SELECT * FROM tb_1 WHERE row>=90 AND EXISTS(SELECT * FROM tb_2 WHERE score='excellent');
// If there is score For good records, query row>=90 Records

Relational operations, subqueries return unique values

SELECT * FROM tb_name WHERE id >(SELECT id FROM tb_2 WHERE id=1);

Note: Subqueries are generally a single-column list, SELECT list FROM tb_name; list is a single-column list
Except when using EXISTS;
Use relational operators to return unique values from subqueries;

(14) Merge query results

UNION: Merge query results, remove same rows
UNION ALL: Merge query results, simply mix elements from two sets

SELECT id,name UNION SELECT id,author; // Correct
SELECT id,name UNION SELECT id; // Error, different number of fields, can't UNION

Note: The precondition is that the number of fields in the left and right field lists must be the same

6. Mysql function

6.1 Mathematical Functions

(1) ABS(X) takes absolute value

SELECT ABS(-1); // Result 1

(2) Integer function

CEIL(); // ceil
FLOOR(); // Rounding Down

(3) Random number generator

RAND(); // 0-1 random number
RAND(X); // Generate 0-1 Random number, x When the same, the random numbers are the same

(4) Symbol Detector

SIGN(X); // Return-1,0,1 Symbols as parameters

(5) Circumference

SIGN(X); // Return-1,0,1 Symbols as parameters

(6) Decimal truncation function

TRUNCATE(X,Y) // Retain x after y Bit decimal

(7) Round (near nearest integer)

ROUND(X) // Return away X Recent Integer
ROUND(X,Y) // Return x，Keep behind y Bit decimal, rounded when truncated
                 // for example ROUND(3.1415，3)=3.142

(8) Square and prescription

POW(X,Y) // x Of y Power
SQRT(X); // Yes x Square
MOD(X,Y) // Mod

(9) Exponential logarithm

EXP(X); // e Of x Power
LOG(X);
LOG10(X);

(10) Radian angle conversion

DEGREES(radians) // radian---angle
RADIANS(degree) // Degrees---radian

(11) Trigonometric functions

SIN(X); // x Is radian
ASIN(X);
COS(X);
ACOS(X);
TAN(X);
ATAN(X);
COT(X);

6.2 String Functions

(1) Length

CHAR_LENGTH(str);

(2) Convert case

UPPER(str);UCASE(str);
LOWER(str);LCASE(str);

(3) Trim function (for leading and ending spaces)

LTRIM(s);RTRIM(s);TRIM(s);//Remove leading spaces; trailing spaces; leading and trailing spaces
TRIM(s1 from s_src); //delete s2 At the beginning and end s1

(4) substitution, substring, inversion

REPLACE(s,s1,s2); //use s2 replace s In s1
SUBSTRING(s,n,len); //s place n Start length is len String of
LEFT(s,n),RIGHT(s,n) //Return left or right length is n String of
REVERSE(s); //String Reverse Order

For more information: PHP Learning Roadmap MySQL Quick Start

6.3 Date and Time Function---------DATE
　　　　　　　　|----------TIME

(1) Date and time

CURRENT_DATE(); // 2014-08-07
CURRENT_TIME(); // 10:30:30

(2) Date and time (timestamp)

NOW(); // 2014-08-07 10:30:30
CURRENT_TIMESTAMP();// 2014-08-07 10:30:30

LOCALTIME(); // 2014-08-07 10:30:30
LOCALTIMESTAMP(); // 2014-08-07 10:30:30
SYSDATE(); // 2014-08-07 10:30:30

(3) Time stamps in seconds

UNIX_TIMESTAMP(); // current UNIX time stamp
UNIX_TIMESTAMP(d); 
FROM_UNIXTIME(d);

(4) Year, month, day, hour and second

YEAR(d);
MONTH(d);
DAY(d);
HOUR(t);
MINUTE(t);
SECOND(t);

(5) Tool functions

DATEDIFF(d1,d2) // d1-d2 Days, d1 Is the latest date, d2 Is an earlier date
ADDDATE(d,n);
SUBDATE(d,n);

For more information: PHP Learning Roadmap MySQL Quick Start

6.4 Conditional Judgment Function

6.5 System Information Functions

(1) Version number, number of connections

VERSION(); // Get Version
CONNECTION_ID(); // Get the number of connections

(2) Current database name

DATABASE();SCHEMA(); // Current database

(3) Current user

USER();SYSTEM_USER();SESSON_USER(); // Current User
CURRENT_USER();

(4) Character set

CHARSET();

(5) Last inserted ID

LAST_INSERT_ID();

6.6 Encryption Function

MD5(str); // General Data Encryption
PASSWORD(s); // Irreversible encryption,Encrypt user name

ENCODE(str_dst,pass_str); // use pass_str encryption str_dst,Returns a binary number, using blob Type Storage
DECODE(crypt_str,psss_str);// use pass_str Decrypt crpyt_str,Encrypted and decrypted pass_str identical

Reprinted at: https://my.oschina.net/u/1772925/blog/600871

Posted by MishaPappa on Sat, 14 Sep 2019 23:13:11 -0700

Programmer Group