MySQL common operation and python operation MySQL Common class

Mysql Common Operations Database operations

• Create a database

Create DATABASE Fuzjtest

• Deleting a database

Drop Database Fuzjtest

• Querying the database

Show databases

• Switch database

Use Databas 123123 # # #用户授权

• Create user

Create user ' username ' @ ' IP address ' identified by ' password ';

• Delete User

Drop user ' username ' @ ' IP address ';

• Modify User

Rename user ' username ' @ ' IP address '; To ' new user name ' @ ' IP address ';;

• Change Password

Set password for ' username ' @ ' IP address ' = password (' New password ')

• View Permissions

Show grants for ' user ' @ ' IP address '

• Authorized

Grant permissions on the database. Table to ' user ' @ ' IP address '

• Cancel Permissions
  Revoke permissions on the database. Table from ' user ' @ ' IP address '

  PS: User rights related data is stored in the user table in the MySQL database, so it can also be manipulated directly (not recommended)
  Authorization database

 All Privilegesall permissions except GrantSelectCheck Permissions onlySelect,Insertcheck and Insert Permissions ... usage no access rightsAlterUse alterTableAlterRoutine using ALTER procedure and dropprocedureCreateUsing the CreateTableCreateRoutine using CreateprocedureCreate TemporaryTables using CreateTemporaryTablesCreate UserUsing the CreateUser、Drop User, rename user, and revoke All PrivilegesCreate ViewUsing the CreateViewDeleteusing the DeleteDropUse DropTableExecuteusing call and stored proceduresfileUsing Select intoOutFile andLoadData infileGrant optionUsing Grant andRevokeIndexUse indexInsertUse insertlock tables with lockTableprocess using show FullprocesslistSelectUse selectshow databases with show DatabasesshowViewUsing showViewUpdateUsing flush with updatereloadshutdownUsing Mysqladminshutdown(close MySQL) Super???? Use change master,Kill, logs, purge, master, and set global. also allows mysqladmin???????? Commissioning LoginReplicationaccess to client server locationsReplicationslave related permissions by copy dependent

Detailed permission Interpretation instructions

for the target database and other internal:  database names. *            All database names in the database  . Table          Specifies a table database name in the database  . The stored procedure     specifies the stored procedure  in the database *.  *                all databases

Authorization for the database system

User name @IP address user          can only access user name @192 under IP change .   168.1. %   Users can only be accessed under the IP segment (wildcard % means any) user name @%             user may be accessed under any IP (default IP address is %)

Authorization for users and IP

Grant  All Privileges  onDb1.tb1 to 'User name'@'IP'Grant Select  onDB1.*  to 'User name'@'IP'Grant Select,Insert  on *.*  to 'User name'@'IP'Revoke Select  onDb1.tb1 from 'User name'@'IP'

Common examples

Table Operations

• Create a table

  • Grammar

Create Table Table name (    whether the column name  type  can be empty,    whether the column name  type can be  empty)

• • Creating parameters for a table

1whether it is nullable, NULL means NULL, non-string not NULL    -not emptyNULL        -can be empty2. 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 setCreate Tabletb1 (Nidint  not NULLDefalut2, Numint  not NULL          )3. Self-increment, if you set the self-increment column for a column, you do not have to set this column when inserting data, and the default will be self-increment (only one self-increment in the table)Create Tabletb1 (Nidint  not NULLAuto_incrementPrimary Key, Numint NULL) orCreate Tabletb1 (Nidint  not NULLauto_increment, Numint NULL,              Index(NID)) Note:1, for self-increment columns, must be an index (with a primary key). 2, for self-increment, you can set the step and start values show session variables like 'auto_inc%'; SetSession Auto_increment_increment=2; SetSession Auto_increment_offset=Ten; SHWO Global Variables like 'auto_inc%'; SetGlobal Auto_increment_increment=2; SetGlobal Auto_increment_offset=Ten; 4. Primary key, a special unique index that does not allow null values, if the primary key uses a single column, its value must be unique, and if it is multiple columns, its combination must be unique. Create Tabletb1 (Nidint  not NULLAuto_incrementPrimary Key, Numint NULL) orCreate Tabletb1 (Nidint  not NULL, Numint  not NULL,              Primary Key(nid,num))5. Foreign keys, a special index, can only be specified content creatTableColor (Nidint  not NULL Primary Key, nameChar( -) not NULL          )          Create TableFruit (Nidint  not NULL Primary Key, SMTChar( +)NULL, color_idint  not NULL,              constraintFk_ccForeign Key(COLOR_ID)Referencescolor (NID))

Creating Table Parameters

MySQL common operation and python operation MySQL Common class