Second stage MySQL function library

A beginner of PHP's accompanying notes, record their growth!

I. Databases (database DB)
1. Database: Warehouse where data is stored
2. Database classification:
(1) Relational database: two-dimensional table storage data (MYSQL,SQLSERVER,ORCALE,DB2, etc.)
(2) Non-relational database (Nosql): MONGDB, etc.
3. mysql Installation
(1) window: Configuration file My.ini
Go to command line start-run--cmd
(2) Linux: Configuration file:/etc/my.cnf
Terminal
Mysql-uroot-p

Description: The location where the Mysql database is stored
/var/lib/mysql
4.Mysql Login
Mysql-u User name-p password [-p3306]
[-D database name]
5.Mysql exit
Quit
Exit
\q

6.Mysql comments
--Notes
# Notes

7. Modify the prompt (Prompt)

(1) command to enter MySQL
Prompt prompt Name
(2) login MySQL to write commands
Mysql-uroot-p--prompt= Prompt Name
Description
A. Recovery prompt prompt mysql>
B. Special symbols
\h Host Name
\d System time and date
\d Choosing a database name

8. SQL (structured query Language) Structured query statement
(1) DDL (data Creation language): Create a database, create a table
Create views, etc.
Create, Drop (delete), alter (modify)
(2) DML (Data Manipulation language): Increased data on the database,
Delete, change
INSERT (add), delete (delete), UPDATE (change)
(3) DQL (data Query Language): Data query to Database
SELECT (query)
(4) DCL (Database Control Language): Assign (GRANT) User rights
and authority (REVOKE) revocation
9.SQL Command Line specification
A. System command uppercase, name lowercase
B.sql command line ends with a semicolon or \g
Description: End delimiter can be used with delimiter symbol name
The C.sql command line supports wrapping, but not in functions, names, quotes
Cannot fold line
The name in the D.sql command cannot be used with keywords and reserved words,
If use must be enclosed in anti-quotes, such as ' user '
10. Creating a Database (DDL)
(1) View database
SHOW DATABASES;
(2) Create a database
CREATE DATABASE [IF not EXISTS]
database name [[DEFAULT] CHARACTER
SET [=] encode];
(3) View CREATE DATABASE command
SHOW CREATE database name;
(4) Deleting a database
DROP database [IF EXISTS] name;
(5) Modify the encoding of the database
ALTER database name
[DEFAULT] CHARACTER SET [=] encoding;
(6) Select database
Use database name;
(7) View the currently selected database
SELECT DATABASE ();

11. Create a table in the database
(1) View table
SHOW TABLES;
(2) Creating a table structure
The CREATE table [IF not EXISTS] Table name (
field (field) name | column name data type [field Attribute | constraint condition],
field (field) name | column name data type [field Attribute | constraint condition]
...
) [Engine=innodb DEFAULT Charset=utf8];
Description: The smallest unit of the database is a table, must be the first table structure
Add Data again
(3) View table structure
DESC table name;
DESCRIBE table name;
SHOW COLUMNS from table name;
(4) View CREATE TABLE Structure command
SHOW CREATE table table name;

12. Data manipulation (DML DQL)
(1) Inserting data
INSERT Table name (Field 1, Field 2 ...)
Value[s]
(value, Value ...),
(value, Value ...) ...;
(2) Query data:
SELECT * from table name;
SELECT Field 1, Field 2,... from table name;

13.MySQL Data types
(1) Integral type
TINYINT ([M]) [unsigned| Zerofill]:
Range: Size 1 bytes (byte 2^8)
Signed Bit -128~127
unsigned 0~255
SMALLINT ([M]) [unsigned| Zerofill]:
Range: Size 2 bytes (byte 2^16)
Signed Bit -32768~32767
unsigned 0~65535
Mediumint ([M]) [unsigned| Zerofill]:
Range: Size 3 bytes (byte 2^24)
Signed Bit -8388608~8388607
unsigned 0~16777215
INT ([M]) [unsigned| Zerofill]:
Range: Size 4 bytes (byte 2^32)
Signed Bit -2147483648~2147483647
unsigned 0~4294967295
BIGINT ([M]) [unsigned| Zerofill]:
Range: Size 8 bytes (byte 2^64)
Description
Field Properties:
UNSIGNED: Unsigned bit (positive number)
Zerofill: When the displayed length is less than the specified length
Filled with 0, will automatically add unsigned

(2) floating point
A. Single-precision floating point
FLOAT ([m,d]) [unsigned| Zerofill]
Range:
-3.40e38 ~3.40e38
B. Double-precision floating point
DOUBLE ([m,d]) [unsigned| Zerofill]
-1.79e308 ~1.79e308
C. Fixed-length floating point
DECIMAL ([m,d]) [unsigned| Zerofill]

Description
When you specify the D-dot number, float,double exceeds
The specified number of digits is rounded to the approximate value,
Decimal exceeds the specified number of digits, packet warning error
Decimal Deposit Exact Value

(3) Character type
A.char ([M]): Fixed length character (0-255 bytes one letter in one byte)
B.varchar ([M]): variable length character (0-65535 bytes)
VarChar storage length is affected by the following conditions:
(a) A field maximum of 65535 bytes
(b) Coding
GBK: Up to 2 bytes
UTF8: Up to 3 bytes of 65532/3 =21844
(c) The total length of multiple character fields cannot exceed
65535 (21844)
c.tinytext:0-255 bytes
d.text:0-65535 bytes
e.mediumtext:0-16777215 bytes
f.longtext:0-4294967295 bytes

Description: Char is highly efficient but does not save space, VARCHAR
Low efficiency But saves space, text efficiency is lowest

(4) Boolean type
BOOLEAN
TINYINT (1)

(5) Enum type
ENUM: Range: 0-65535 bytes
For example:
Sex ENUM (' Man ', ' woman ', ' secret ');

14. Constraints (1)

Not NULL: is not empty
Default: Defaults
[PRIMARY] Key: Primary key, only one table can have
A primary key, and the field that is the primary key must be
Automatically add not NULL and no Duplicates (UNIQUE)
UNIQUE [KEY]: Do not repeat, a table can have more than one
Do not repeat (for example: User name is not duplicated, mailbox is not duplicated)
Auto_increment: Auto Grow, field if number is added
When a record (line) is recorded, the number is self-increasing and
The Auto_increment field must be a primary key

Second stage MySQL function library