The Python MySQL database

MySQL Installation and download

A: Download MySQL installer b: Install MySQL server side c: Install MySQL client d: Client Connection server E: Send commands to the servers through the client, perform additions and deletions to the database files.

　Windows Edition installation

#1. Download: MySQL Community Server 5.7.20https://dev.mysql.com/downloads/mysql/#2. UnzipDownload the extract package to the designated directory (e.g. d://mysql-5.7.20-winx64)#3. Add Environment Variables"Right-click Computer"-"Properties"-"Advanced system Settings"-"Advanced"-"Environment variable"-"in the Second content box find a row of the variable named path, double-click"-"Append the MySQL Bin directory path to the variable value, split" C:\Program Files (x86) \parallels\parallels Tools\applications;d:\mysql-5.7.16-Winx64\bin#4. InitializeMysqld--initialize-insecure#5. Start the MySQL servicemysqld#6. Connect to MySQL serviceMysql-uroot-p Enter, have password input password, do not continue to enter decompression mode installation decompression mode installation

Decompression Mode installation

PS: The production service must use the full path approach # To make a Windows service for MySQL, execute this command at the terminal: " C:\mysql-5.7.16-winx64\bin\mysqld " -install#  Remove the MySQL Windows service and execute this command at the terminal:"\ C Mysql-5.7.16-winx64\bin\mysqld" --remove'#  start MySQL service net Start MySQL (back without semicolons)#  close MySQL service net stop MySQL (no semicolon appended)

Production Services

1. Create a database

Create database name;

# Create a database named Db_name, and specify the encoding set for the current library as UTF8 CREATE DATABASE db_name charset UTF8;

2. Check the database

Use library name;

# querying all databases under the current user show databases; # View information about creating a database Show Create database db_name; # Query the name of the database in which the current operation is located select Databases ();

3. Query all tables under the current library

Show tables shows all the tables in the library

Show databases display all databases

4. Delete

drop database name;

What is SQL?

Above we introduced the database components, wherein the database management system can receive some commands, the data file to add, delete, modify, query and other operations. Then these commands are SQL.

SQL: (Structured query Language) is a Structured Query language abbreviation. is a language specialized in dealing with database management systems.

SQL language: Is the standard language of relational database, it is mainly used for accessing data, querying data, updating data and managing database system and so on.

Specifically, SQL can be divided into 4 parts:

　　　　Data Control Language (DCL): Primarily used to control the user's access rights. Where the grant statement is used to add permissions to the user, the REVOKE statement is used to reclaim the user's permissions

　　　Data Definition language (DDL): DROP, CREATE, Alter, and so on; Database definition language. Primarily used to define databases, tables, views, indexes, triggers, and so on. The CREATE statement is used primarily for creating databases, creating tables, and creating views. The ALTER statement is primarily used to modify the definition of a table and modify the definition of a view. Drop statements are primarily used to delete a database, delete a table, delete a view, and so on.

　　　　Data Manipulation Language (DML): INSERT, UPDATE, DELETE statement, database manipulation language. Mainly used to insert data, update data, delete data. The INSERT statement is used to insert data, and the UPDATE statement is used to update the data, and the DELETE statement deletes the data.

　　　　data Query Language (DQL): SELECT statement. Used primarily for querying data.

#1. Operating folderadd: Create Database db1 charset UTF8;        Check: show databases;        Change: Alter DATABASE DB1 CharSet latin1; Delete: Drop database db1;#2. Operating FilesFirst switch to folder: Use DB1: CREATE TABLE t1 (id int,name char); Check: Show tables change: ALTER TABLE t1 modify name char (3); ALTER TABLE T1 change name name1 char (2);    Delete: drop table T1; #3. Contents/Records in the action fileAdd: INSERT INTO T1 values (1,'Egon1'), (2,'Egon2'), (3,'Egon3'); Check: Select* fromT1; Change: Update T1 set name='SB'where id=2; Delete: Delete fromT1 where id=1; Empty table: Delete fromT1;#If there is a self-increment ID, the new data is still started as the last one before deletion. truncate table T1; The data volume is large, the deletion speed is faster than the previous one, and starts directly from zero, auto_increment means: self-increment primary key means: constraint (Cannot re- and cannot be empty); speed up the search ....

#1. Operating folderadd: Create Database db1 charset UTF8;        Check: show databases;        Change: Alter DATABASE DB1 CharSet latin1; Delete: Drop database db1;#2. Operating FilesFirst switch to folder: Use DB1: CREATE TABLE t1 (id int,name char); Check: Show tables change: ALTER TABLE t1 modify name char (3); ALTER TABLE T1 change name name1 char (2);    Delete: drop table T1; #3. Contents/Records in the action fileAdd: INSERT INTO T1 values (1,'Egon1'), (2,'Egon2'), (3,'Egon3'); Check: Select* fromT1; Change: Update T1 set name='SB'where id=2; Delete: Delete fromT1 where id=1; Empty table: Delete fromT1;#If there is a self-increment ID, the new data is still started as the last one before deletion. truncate table T1; The data volume is large, the deletion speed is faster than the previous one, and starts directly from zero, auto_increment means: self-increment primary key means: constraint (cannot Repeat and cannot be empty);

can be by letter, number, underscore, @, #, $ case-sensitive uniqueness cannot use keywords such as: CREATE Select cannot use the number maximum 128 bits alone

Naming Conventions

User Rights

Creating user Create users'User name'@'IP Address'Identified by'Password'Delete users drop user'User name'@'IP Address'; Modify users rename user'User name'@'IP Address'; To'New user name'@'IP Address';

User Management

#Create a new userCreate user'Alex'@'localhost'Identified by'123456';#Authorization method One: For Alex authorized db1 the query for all tables under the database. Update. Modify PermissionsGrant Select,update,delete on db1.* to'Alex'@'localhost';#Authorization Method Two: All permissions for Alex to authorize all libraries (except grant permissions)Grant all privileges on * * to'Alex'@'localhost';#Refresh user PermissionsFlush privileges;

create user and authorization examples

All privileges except grant all permissions select only Check permissions Select,insert check and insert Permissions ... u Sage NO access ALTER use ALTER TABLE ALTER routine using ALTER p Rocedure and drop procedure create use the CREATE table create routine with CREATE proc Edure Create temporary tables use create temporary tables create user using create user, Drop U                  Ser, rename user, and revoke all privileges CREATE view use the CREATE VIEW delete                    Use drop table execute with delete drop to use call and stored procedure file            Use SELECT INTO outfile and load data infile grant option using GRANT and REVOKE Index use the Insert lock tables with the index insert to use the lock tabl    E process             Use show full Processlist Select to use Show databases with the Select Show                  Databases show view uses the update reload with show view update                   Use flush shutdown with mysqladmin shutdown (turn off MySQL) super  Use change master, kill, logs, purge, master, and setGlobal. Also allows mysqladmin to debug login replication client server location access replication slave used by replication slaves

Permissions List

1 Way One: mysqladmin command2 3 4Mysqladmin-u User Name-p password Password new password5 mode two: Set user password directly6 7 8Set Password for 'User name'@'IP'= Password ('New Password');9              Ten flush Privileges; One Method Three: Modify the user table under MySQL library A  -  -5. 7 Version Change password mode: theUpdate Mysql.user Set Authentication_string=password ('New Password') Where user='User name'           -Flush privileges; --Refresh Permissions -               -5.6version +Update mysql.user Set password = password ('New Password') Where user='User name' -Flush privileges; --Refresh Permissions +Six. Forgot password

Change Password

when you forget the root password, you can:#1. First open the CMD window and turn off the MySQL servicenet stop MySQL#2. Then skip permission check, start MySQL, enter commandMysqld--skip-grant-Tables#3. Reopen a new CMD window to start the client (the permission check has been skipped, you can log in directly)MySQL#4. Directly in, change the passwordUpdate Mysql.user Set Authentication_string=password ('123456') Where user='Root'; #5. Refresh PermissionsFlush privileges;

Forgot Password

Query character encoding

' char% ';

#Modification Method:#1. Create the My.ini file and place it under the MySQL root path#2. Add the following content to the file:#3. After you add this file, you need to restart the service to ensure this file is valid------------------------------------------------------------[Client]default-character-set=Utf8[mysql]#set the MySQL client default character setdefault-character-set=Utf8[mysqld]#Set 3306 PortPort= 3306#Maximum number of connections allowedmax_connections=200#the character set used by the service side defaults to the 8-bit encoded latin1 character Setcharacter-set-server=UTF8#default storage engine that will be used when creating a new tabledefault-storage-engine=INNODB#resolve MySQL 1055 error after executing SQL statement, Sql_mode = only_full_group_by IncompatibleSql_mode='no_engine_substitution'garbled is dead

garbled is dead

The Python MySQL database