1. User permission settings
1. How are users defined in MySQL?
username@hostdomain
- 10.0.0.51
- 10.0.0.%
- %
- 10.0.0.0/255.255.255.0
- Db01
- Localhost
- 127.0.0.1
2. User creation
- create user xinjing@'%' identified by '123'
3. User deletion
- drop user username；
- username is the username to be deleted:
- For example, drop user root@'%'
- drop use''@'127.0.0.1'Delete if the username is empty
4. User Query
use mysal
select user,host from user;
5. Introduction to permission levels
- Global--You can manage the entire MySOL
- Library--can manage the specified database
- Table - can manage the specified table of the specified database
- Field--You can manage the specified fields of the specified table in the specified database
- Permissions are stored in the mysql database user, db, tables priv, columns_priv, procs_priv
- These system tables are loaded into memory after the MySQL instance is started.
6. Check the user permissions granted
- show grants for xinjing@'%'
7. User Authorization
- grant all privileges on stu.* to 'xinjing'@'%' identified by'123' withoption;
- revoke all privileges on stu.* from 'xinjing'@'%'
2. Metadata Query
- Select version(); Server version information
- Select database(); Current database name
- Select user(); Current user name
- Show status; Server status
- Show variables; Server configuration variables
- Show global variables like'%datadir%: See where the data files are stored
- show global variables like '%datadir%':
- select @@datadir; query the database path
- select @@basedir to query the installation path of mysql
3. Detailed explanation of union query
- A union query combines the query results of two or more SQL statements into one result set.
- sql1: N rows, sql2: M rows, sqll union sql2 ---> N+M rows
- The number of columns in the result set must be the same (such as 2 columns or N columns)
- The final column name taken out by union is based on the first sql statement.
- Union query will remove duplicates by default. If you don't want to remove duplicates, use union all
4. Group query display
GROUP_CONCAT()
- select sid,group_concat(cid),group_concat(cj order by cj desc SEPARATOR ' ') from score
group by sid
5. String functions
6. MySQL database import and export (outside MySQL)
--dump table utemp1
mysqldump -uroot -p student utemp1>utemp1.sql
--dump out of library student
mysqldump -uroot -p student>student.sql
--dump all databases and database tables
mysqldump -uroot -p --all -databases>mysqldatabases.sql;
- mysql database import "<"
--Create a new database
create database dumpdemo1
--Return to mysql interface
mysql -u root -p654321 dumpdemo1<student.sql
--Enter mysql verification
use dumpdemo1
show tables;
7. MySQL database import and export (inside MySQL)
- Importing mysql database into "source"
mysal -u root -pcreate database source dumpdemo;
source /var/lib/mysal-files/student.sql
