Yundun practices MySQL series tutorial (2) the best database operations in history that you don't know, mysql

Yundun practices MySQL series tutorial (2) the best database operations in history that you don't know, mysql

This article is the most basic and important operation in MySQL!

Article 1: MySQL series of tutorials (I) production standard online environment installation and configuration cases and troubleshooting

Article 2: MySQL series of tutorials (II) the best database operations in history that you don't know

 

Content of this chapter:

• View \ create \ Use \ delete database
• User Management and authorization practices
• Lan remote connection method
• View \ create \ Use \ Delete \ clear \ modify Database Table (whether it can be blank, default value, primary key, auto-increment, foreign key)
• Add, delete, modify, and query table content
• Where condition, wildcard _ %, limit, sort desc \ asc, join, and union
• View table creation statements, table structures, and indexes
• Data Type
• Index!

I. Database Operations 1. view the database

Show databases; # default database: mysql-user permission-related data test-used for user test data information_schema-MySQL architecture-related data

2. Create a database

# UTF-8 encoded create database name default charset utf8 COLLATE utf8_general_ci; # gbk encoded create database name default character set gbk COLLATE gbk_chinese_ci;

3. Use a database

USE db_name; # Do not USE semicolons

4. User Management

# Create user 'username' @ 'IP address' identified by 'Password'; # delete user drop user 'username' @ 'IP address '; # modify the user rename user 'username' @ 'IP address'; to 'new username' @ 'IP address ';; # change the password set Password for 'username' @ 'IP address' = password ('new password') PS: the user permission-related data is stored in the user table of the mysql database, therefore, you can directly operate on it (not recommended)

# View the current user select user (); # view all users select host, user from mysql. user; # select distinct concat ('user: ''', User, ''' @ ''', host ,''';') AS query FROM mysql. user; # view all permissions of a user. show grants for 'Nick '@' % ';

Mysql> select distinct concat ('user: ''', User, ''' @ ''', host, '''; ') AS query FROM mysql. user; + ------------------------- + | query | + --------------------------- + | User: 'Nick '@' % '; | User: 'root' @ 'localhost '; | + ------------------------- + 2 rows in set (0.00 sec) mysql> select host, user from mysql. user; + ----------- + ------ + | host | user | + ----------- + ------ + | % | nick | localhost | root | + ----------- + ------ + 2 rows in set (0.00 sec) mysql> show grants for 'Nick '@' % '; + Grants + | grants for nick @ % | + Grants + | grant usage on *. * TO 'Nick '@' % 'identified by password' * ECE7D02DCD7D4EF7CFE8E3B249FD1D5062A821F7' | grant all privileges on 'aoshi '. * TO 'Nick '@' % '| grant all privileges on 'xxxxx '. * TO 'Nick '@' % '| grant all privileges on 'xxxxxx '. 'chouti' TO 'Nick '@' % '| + rows + 4 rows in set (0.00 sec) mysql>View Code5, authorization management

# View permissions show grants for 'user' @ 'IP address' # grant permissions to the on database. table to 'user' @ 'IP address' # revoke the revoke permission on the database. table from 'user' @ 'IP address'

Common permissions: all privileges all permissions except grant select only query permissions select, insert query and insert permissions usage no access permissions for the target database and other internal: Database Name. * names of all databases in the database. table specifies the name of a table database in the database. stored Procedure specifies the stored procedure in the database *. * for users and IP addresses of all databases: the user name @ IP Address can only access the user name @ 192.168.1 under the changed IP address. % The user can only access the IP address segment (wildcard % represents any) username @ % the user can access it from any IP address (the default IP address is %)

All privileges all permissions except grant select only query permissions select, insert query and insert permissions... usage has no access permission. alter use alter table alter routine use alter procedure and drop procedure create use create table create routine use create procedure create temporary tables use create temporary tables create user use create user, drop user rename user and revoke all privileges create view use create view delete use delete drop use drop table execute use call and stored procedure file use select into outfile and load data infile grant option use grant and revoke index use index insert USE insert lock tables use lock table process use show full processlist select use select show databases use show databases show view use show view update use update reload use flush shutdown use mysqladmin shutdown (close MySQL) super