MySQL Database section simple collation

Database overview
What is Data
Symbolic records describing things are called data

What is a database
The database is the warehouse where the data is stored, except that the warehouse is on a computer storage device, and the data is stored in a certain format.
A database is a collection of data that is stored in a computer, organized, and shared over a long period of time.
The data in the database is organized, described and stored according to a certain data model, with small redundancy, high data independence and extensibility, and can be shared for various users.

What is a database management system
Tired tired, go to search it, do not mean

The relationship between database server, data management system, database, table and record
Record: 1 xxx 324245234 22 (Multiple fields of information form a record, that is, a line of content in a file)

Table: Student, Scholl, class_list (i.e. file)
Database: Oldboy_stu (that is, folder)
Database management system: such as MySQL (is a software)
Database server: One computer (high memory requirements)

Summarize:
Database server-: Running database management software
Database management software: Management-Database
Database: A folder for organizing files/tables
Table: A file that is used to hold multiple lines of content/Multiple records


MySQL Installation and basic management
What is MySQL?
MySQL is a software based on the C/s architecture of socket writing

Classification of database management software
There are two main types of:
Relationship type: As Sqllite,db2,oracle,access,sql server,mysql, NOTE: SQL statements are common
Non-relational: Mongodb,redis,memcache
Can be simply understood as:
relational databases require a table structure
The non-relational database is key-value stored, without a table structure

Download and install MySQL
Go to the official website to use the CMD operation of the proposal is strongly recommended to run CMD with the administrator, especially for small white!
I'm done. Add environment variables
Append the MySQL bin directory path to the variable value

Initialize command
Mysqld--initialize-insecure
Start the MySQL service
Mysqld #启动MySQL服务

Start the MySQL client and connect to the MySQL server (a new CMD window is opened)
Mysql-u root-p # Connect to MySQL server

Note: The absolute path of the command must be started with MySQL before--install
# Create a Windows service for MySQL and execute this command at the terminal:
"C:\mysql-5.6.40-winx64\bin\mysqld"--install
# to remove the MySQL Windows service, execute this command at the terminal:
"C:\mysql-5.7.16-winx64\bin\mysqld"--remove

After registering as a service, you only need to execute the following command when you start and close the MySQL service later:
# Start the MySQL service
net start MySQL
# Turn off MySQL service
net stop MySQL

Windows logon Settings password
Open terminal, enter MySQL

Enter the functions provided by MySQL: Select User (); # view currently logged in account

The default account for the current login is [email protected]

If you want to log in to the root account
Command
    Mysql-uroot-p

Then view the current User:
    Select User ();

Administrator for Root (with the highest privileges, Administrator account), password is empty, password-free way to login to the administrator account, is a very dangerous thing, so to set a password for the Administrator account

Set the administrator root account password to 123
C:\users\mjj>mysqladmin-uroot-p password "123"  #设置初始密码 because the original password is empty, so-P can not

PS: Don't care about it. The password is not secure at the command line interface and is exposed to the terminal.

Then login to the account, do not enter the password will appear the following results:

Perform the following actions again:

Then check the current login account:

If you want to have the original password 123, set the new password to 456
C:\users\mjj>mysqladmin-uroot-p "123" password "456"  #修改mysql密码, because there is already a password, so you must enter the original password to set a new password
Perform the following operations in turn:
    mysql-uroot-p456
    Select User ();

Password forgot--crack password
Skip the authorization method and log in directly!!
0. Open cmd as Administrator

2. Stop the MySQL server
C:\windows\system32>net stop MySQL
The MySQL service is stopping.
The MySQL service has stopped successfully.

3. Execute the following command to skip the authorization form
Skip Authorization Form
C:\windows\system32>mysqld--skip-grant-tables
2018-06-09 17:12:38 0 [Warning] Insecure configuration for--secure-file-priv:current value does no restrict location of Generated files. Consider setting it to a valid, Non-empty path.
2018-06-09 17:12:38 0 [Note] mysqld (mysqld 5.6.40) starting as Process 6052 ... 4.

4. Check Again

5. Now you can change the password arbitrarily, execute the following command
Update mysql.user Set authentication_string = password (") where user = ' root ';

6. Refresh permissions, execute commands
Flush privileges;

7. Quit MySQL. Execute command: Exit,

8. Let the user go to load permissions, enter CMD as administrator to view the current MySQL process
tasklist |findstr MySQL  #查看当前mysql的进程

9. Kill the current process and execute the following command
taskkill/f/pid 6052  # kills the current process PID

10. Perform the following actions again to restore

Unified character encoding
Go to MySQL client and execute \s

To unify character encoding, do the following:
(1) The My.ini file is a MySQL configuration file,
Create a My.ini file under the C:\mysql-5.6.40-winx64 file
(2) Save the following code copy.
#mysql5.5 or more: Modify the method to
    [Mysqld]
    Character-set-server=utf8
    Collation-server=utf8_general_ci
    [Client]
    Default-character-set=utf8
    [MySQL]
    Default-character-set=utf8

(3) Restart the service as an administrator and execute the following command
C:\windows\system32>net Stop MySQL
The MySQL service is stopping:
The MySQL service has stopped successfully.

C:\windows\system32>net Start MySQL
The MySQL service is starting.
The MySQL service has started successfully.

(4) Enter MySQL in cmd to enter MySQL environment, execute \s, display the following information, indicating success

Basic MySQL Statement
Action folder (library)
Increase
Create Database db1 charset UTF8;

Check
View the currently created database
Show CREATE Database db1;
View all databases
show databases;

Change
ALTER DATABASE DB1 CharSet GBK;

By deleting
Drop database db1;


Action file (table)
Use DB1; #切换文件夹
Select Database (); #查看当前所在文件夹

Increase
CREATE TABLE T1 (id int,name char);

Check
View the current T1 table
Show CREATE table T1;
View all the tables
Show tables;
View the details of a table
Desc T1;

Change
Modify the meaning of the modification
ALTER TABLE T1 modify name char (6);
Change name to uppercase name
ALTER TABLE T1 change name NAMA char (7);

By deleting
Delete a table
drop table T1;


Manipulate file contents (record)
Increase
Insert a piece of data that specifies Id,name data leilei
Insert T1 (Id,name) VALUES (1, "mjj01"), (2, "mjj02"), (3, "mjj03");

Check
Select ID from Db1.t1;
Select Id,name from Db1.t1;
SELECT * from Db1.t1;

Change
Update db1.t1 set name= ' Zhangsan ';
Update db1.t1 set name= ' Alex ' where id=2;

By deleting
Delete from T1;
Delete from T1 where id=2;



Database naming rules:
Can consist of letters, numbers, underscores, @, #, $
Case sensitive
Uniqueness
You cannot use keywords such as create select
Numbers cannot be used alone
Maximum 128 bits



Operation of the Library
Library additions and deletions to search
View System Libraries
show databases;

Create a database
Create database name CharSet UTF8;

Database-related Operations
View Database
show databases;
View Current Library
Show CREATE Database db1;
View the library in which it resides
Select Database ();
Select Database
Use database name
Deleting a database
DROP database name;
Modify Database
ALTER DATABASE DB1 CharSet UTF8;

There are 3 types of SQL languages:
DDL, DML, DCL statements want to know the self-Baidu



Operation of the table
The storage engine is completely self-concept Baidu
Tables in the database should also have different types, with different types of tables, and different access mechanisms for MySQL, and the table type is also known as the storage engine.

MySQL-supported storage engine
Mysql> show engines\g;# View all supported engines
Mysql> Show variables like ' storage_engine% '; # View the storage engine in use

Important engine (specifically described by Baidu itself)
InnoDB Storage Engine
MyISAM Storage Engine
Memory Storage Engine
Blackhole

Understand the engine (specifically described by Baidu itself)
NDB Storage Engine
Infobright Storage Engine
Ntse Storage Engine

Specify table type/Storage engine
CREATE TABLE t1 (id int) engine=innodb;# is not written by default is InnoDB

Create a table
Grammar:
CREATE TABLE Table name (
Field name 1 type [(width) constraint],
Field Name 2 Type [(width) constraint],
Field Name 3 Type [(width) constraint]
);

#注意:
1. Field names cannot be the same in the same table
2. Width and constraints are optional
3. Field names and types are required

1. Create a database
Create database DB2 charset UTF8;
2. Using the database
Use DB2;
3. Create a A1 table
CREATE TABLE A1 (
ID int,
Name varchar (50),
Age Int (3)
);
4. Inserting records into a table
INSERT INTO A1 values
(1, ' xxx ', 18),
(2, ' Ooo ', 28);
5. Querying the data and structure of the table
Querying stored data in a A1 table
SELECT * FROM A1;
View the structure of the A1 table
DESC A1;
View the detailed structure of a table
Show CREATE TABLE a1\g;

Copying tables
Create a new database db3
Create Database db3 charset UTF8;
Using DB3
Use DB3;
Copy the table structure and records of DB2.A1
CREATE TABLE B1 select * from Db2.a1;

View the data and table structure in DB3.B1
SELECT * from DB3.B1;
As long as the table structure, do not record
CREATE TABLE b2 select * from db2.a1 where 1>5;

To view the table structure:
Desc B2;
View data in a table structure
SELECT * from B2;

There is also a way to use like (Copy only the table structure, not copy records)
CREATE table b3 like db2.a1;
Desc B3;
SELECT * from db3.b3;

Delete a table
drop table name;



Data type
MySQL Common data type Summary: Number, string, time, enumeration, collection
1. Numbers:
Integral type: Tinyinit int bigint
Decimal:
FLOAT: Not accurate when the number of bits is relatively short
Double: not accurate when the number of bits is longer
0.000001230123123123
Deposit: 0.000001230000

Decimal: (decimal is recommended if decimal is used)
Precision
The internal principle is to deposit in a string form.

2. String:
CHAR (10): Simple rough, wasted space, fast access speed
Root saved into root000000
VARCHAR: precision, space saving, slow access speed

SQL optimization: When you create a table, the fixed-length type goes forward and gets longer.
such as the sex ratio, address or descriptive information.

>255 characters, the file path is stored in the database.
Compare slices, videos, etc. find a file server, only the path or URL is stored in the database.

3. Time Type:
Most used: datetime

4. Enumeration types and collection types



Numeric type
Integer type: TINYINT SMALLINT mediumint INT BIGINT
Function: Store age, grade, ID, various numbers, etc.

I'll give you a watch.

Fixed-point number type: Dec equals decimal


Floating point Type
Float type: float DOUBLE
Function: Store salary, height, weight, physical parameters, etc.
Grammar:
-------------------------FLOAT-------------------
float[(m,d)] [UNSIGNED] [Zerofill]
Parameter explanation: Single-precision floating-point number (non-accurate decimal value), M is full length, D is after decimal point. M maximum value is 255,d maximum of 30

There are symbols:
-3.402823466E+38 to-1.175494351e-38,
1.175494351E-38 to 3.402823466E+38

Unsigned:
1.175494351E-38 to 3.402823466E+38
Accuracy:
As the number of decimals increases, the accuracy becomes inaccurate * * *


-------------------------DOUBLE-----------------------
double[(m,d)] [UNSIGNED] [Zerofill]

Parameter explanation: Double-precision floating-point number (non-accurate decimal value), M is full length, D is after decimal point. M maximum value is 255,d maximum of 30

There are symbols:
-1.7976931348623157E+308 to-2.2250738585072014e-308
2.2250738585072014E-308 to 1.7976931348623157E+308

Unsigned:
2.2250738585072014E-308 to 1.7976931348623157E+308

Accuracy:
With the increase in decimals, accuracy is higher than float, but it can become inaccurate * * *

======================================
--------------------DECIMAL------------------------
decimal[(m[,d]) [unsigned] [Zerofill]

Parameter explanation: The exact decimal value, M is the total number of integers (minus sign), and D is the number after the decimal point. The M maximum value is 65,d maximum of 30.


Accuracy:
With the increase of decimals, accuracy is always accurate * * *
This type is required for precise numerical calculations
The reason that decaimal can store exact values is because they are stored internally as strings.


Date type
DATE Time DATETIME TIMESTAMP year
Role: Store user registration time, article release time, employee entry time, birth time, expiration time, etc.
Grammar:
Year
YYYY (1901/2155)

DATE
YYYY-MM-DD (1000-01-01/9999-12-31)

Time
HH:MM:SS (' -838:59:59 '/' 838:59:59 ')

Datetime

Yyyy-mm-dd HH:MM:SS (1000-01-01 00:00:00/9999-12-31 23:59:59 Y)

TIMESTAMP

YYYYMMDD Hhmmss (1970-01-01 00:00:00/2037)


Character type
Official website: https://dev.mysql.com/doc/refman/5.7/en/char.html
Note: The arguments in char and varchar brackets refer to the length of the character

Char type: fixed length, simple rough, wasted space, fast access speed
Character Length range: 0-255 (one Chinese is a character, is a UTF8 encoded 3 bytes)
Store:
When a value of type char is stored, a space is padded to the right to satisfy the length
For example: Specify a length of 10, save >10 characters will be error, save <10 characters will be filled with space until enough 10 characters to store

Retrieval:
When retrieving or saying a query, the results are automatically deleted trailing blanks unless we open pad_char_to_full_length SQL mode (set SQL mode: Set sql_mode = ' pad_char_to_full_length ';
default mode for querying sql: SELECT @ @sql_mode;)

VARCHAR Type: variable length, precision, space saving, slow access speed
Character Length range: 0-65535 (if greater than 21845 prompts for other types.) MySQL line maximum limit is 65535 bytes, character encoding is utf-8:https://dev.mysql.com/doc/refman/5.7/en/column-count-limit.html)
Store:
The varchar type stores the actual contents of the data, does not fill with spaces, and if ' AB ', the trailing spaces are saved.
Emphasis: The varchar type will prefix the real data with 1-2bytes, which is used to represent the bytes bytes of real data (1-2bytes maximum of 65,535 digits, exactly the maximum byte limit of MySQL to row, which is sufficient)
A 1Bytes prefix is required if the true data <255bytes (1bytes=8bit 2**8 The maximum representation of the number is 255)
A 2Bytes prefix is required if the true data >255bytes (2bytes=16bit 2**16 The maximum representation of the number is 65535)

Retrieval:
The trailing space will be saved, and the contents including the blanks will be displayed normally when the query is retrieved or queried.

The official website is explained as follows:

Enumeration types and collection types
The value of a field can be selected only in a given range, such as a radio box, a multi-marquee
The enum radio can only select a value within a given range, such as gender sex sex male male/female female
Set multiple selection You can select one or more values within a given range (hobby 1, hobby 2, hobby 3 ...) ）


Integrity constraints
Constraints are the same as the width of the data type, which are optional parameters
Role: To ensure the integrity and consistency of data
Mainly divided into:
PRIMARY KEY (PK) #标识该字段为该表的主键, can uniquely identify the record
FOREIGN KEY (FK) #标识该字段为该表的外键
Not NULL #标识该字段不能为空
UNIQUE KEY (UK) #标识该字段的值是唯一的
Auto_increment #标识该字段的值自动增长 (integer type, and primary key)
DEFAULT #为该字段设置默认值

UNSIGNED #无符号
Zerofill #使用0填充

Description
1. Allow null, default NULL, can set not NULL, field is not allowed to be empty, must be assigned
2. Whether the field has a default value, the default value is NULL, if the record is inserted without assigning a value to the field, this field uses the default value
Sex enum (' Male ', ' female ') not null default ' male '

Must be positive (unsigned) not allowed to null default is 20
Age Int. unsigned not NULL default 20
3. Whether it is a key
Primary KEY Primary Key
FOREIGN key foreign key
Index (Index,unique ...)




Not NULL vs. default
Nullable, NULL indicates NULL, non-string
Not null-non-nullable
Null-Nullable

Default value, you can specify a default value when creating a column, and automatically add a default value when inserting data if it is not actively set


Unique
Single row unique
Union unique

Primary key
In a table you can:
Single-row key
Multi-column Master key (composite primary key)
Constraint: Equivalent to NOT NULL unique, the value of the field is not null and unique
The storage Engine is (INNODB) by default: For the InnoDB storage engine, a table must have a primary key.


Auto_increment
Constraint: The field of the constraint is autogrow, and the constrained field must be constrained by key at the same time

FOREIGN key


Three types of relationships in the table
Many-to-one, many-to-many, single-


Data deletion and modification
Inserting data Insert
Updating Data Update
Deleting data Delete

One, in the MySQL management software, you can use the DML language in SQL statements to achieve the operation of the data, including

1. Inserting data by using insert
2.UPDATE implementation of data updates
3. Using Delete to implement data deletion
4. Use Select to query data and.


Inserting data Insert
1. Insert full data (sequential insertion)
Syntax One:
INSERT into table name (Field 1, Field 2, Field 3 ...) field N) VALUES (value 1, value 2, value 3 ...) Value n);

Syntax Two:
INSERT into table name values (value 1, value 2, value 3 ...) Value n);

2. Specify fields to insert data
Grammar:
INSERT into table name (Field 1, Field 2, Field 3 ...) Values (value 1, value 2, value 3 ...);

3. Inserting more than one record
Grammar:
INSERT into table name VALUES
(value 1, value 2, value 3 ...) Value N),
(value 1, value 2, value 3 ...) Value N),
(value 1, value 2, value 3 ...) Value n);

4. Insert Query Results
Grammar:
INSERT into table name (Field 1, Field 2, Field 3 ...) Field N)
SELECT (field 1, Field 2, Field 3 ...) field N) from table 2
WHERE ...;

Third, update data updates
Grammar:
UPDATE Table name SET
field 1= value 1,
field 2= value 2,
WHERE CONDITION;

Example:
UPDATE mysql.user SET Password=password (' 123 ')
where user= ' root ' and host= ' localhost ';
Iv. Deleting data Delete
Grammar:
DELETE from table name
WHERE conition;

Example:
DELETE from Mysql.user
WHERE password= ';



Single-Table Query
The syntax of a single-table query
SELECT Field 1, Field 2 ... From table name
WHERE condition
GROUP by Field
Having screening
ORDER by Field
Limit number of bars
Ii. priority of implementation of keywords (emphasis)
From
where
GROUP BY
Having
Select
Distinct
ORDER BY
Limit

1. Find the table: from
2. Take the where specified constraints, go to the File/table to remove a record
3. Group BY, if no group by, is grouped by the records taken out.
4. To filter the results of a group
5. Execute Select
6. Go to the heavy
7. Sort the results conditionally: ORDER BY
8. Limit the number of display bars of the result


where constraint
The WHERE clause can be used
1. Comparison operators:>, <, >=, <=, <>,! =
2.between and 100: values from 80 to 100
3.in (80,90,100) value is 10 or 20 or 30
4.like ' Xiaomagepattern ': pattern can be either% or _. % hours any number of characters, _ denotes one character
5. Logical operators: logical operators and or not can be used directly in multiple conditions

GROUP BY group Query
1. First make it clear: The grouping occurs after the where, that is, the grouping is based on the records obtained after the Where
2. Grouping refers to classifying all records according to one of the same fields, such as grouping positions on employee information sheets, or grouping by gender
3. Why should we group them?
The highest wage per department
Number of employees in each department
Number of males and women
Tip: The field behind the word ' every ' is the basis for our group
4, the premise:
Can be grouped by any field, but after grouping, such as group by post, can only view the post field, if you want to view the information in the group, need to use the aggregation function

Aggregation functions
Max () to find the maximum value
Min () to find the minimum value
AVG () averaging
SUM () sum
Count () to find the total number

Having filter
Having the same place as where it is
High-to-low execution priority: where > Group by > have
1. Where occurs before the group by grouping by, there can be any field in the where, but the aggregate function must never be used.
2. The having occurs after the group by, so that the having can use grouped fields, cannot be directly taken to other fields, you can use the aggregate function

Order BY Query sort
Sort by single column
SELECT * from the employee ORDER by age;
SELECT * from the employee ORDER by age ASC;
SELECT * FROM-employee ORDER by-age DESC;
Sort by multiple columns: Sort by age in ascending order, and if you are older, follow the ID descending
SELECT * FROM Employee
ORDER by age ASC,
ID DESC;


Limit limits the number of records queried:
Example:
SELECT * FROM employee ORDER by salary DESC
LIMIT 3; #默认初始位置为0
SELECT * FROM employee ORDER by salary DESC
LIMIT 0, 5; #从第0开始, that is, first query out, and then include this one, and then look back to 5 article
SELECT * FROM employee ORDER by salary DESC
LIMIT 5, 5; #从第5开始, that is, the first query out 6th, and then include this one, and then look back 5 article


Multi-Table Query
Multi-Table Connection query
Grammar:
SELECT Field List
From table 1 inner| Left| Right JOIN table 2
On table 1. field = table 2. field;

Qualifying Connection Query

Sub-query


Let's get this sorted! There are many more in the database!
Pymysql will be used in the back.

MySQL Database section simple collation