Mysql Common Operations Database operations
Create DATABASE Fuzjtest
Drop Database Fuzjtest
Show databases
Use Databas 123123 # # #用户授权
Create user ' username ' @ ' IP address ' identified by ' password ';
Drop user ' username ' @ ' IP address ';
Rename user ' username ' @ ' IP address '; To ' new user name ' @ ' IP address ';;
Set password for ' username ' @ ' IP address ' = password (' New password ')
Show grants for ' user ' @ ' IP address '
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 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