MySQL Common operations

First, view the class

• View MySQL's current metrics

  status;show status;

• View character Sets

show variables like "%character%";show character set;

• View Current User

select user();

• viewing collations

  SHOW VARIABLES WHERE Variable_name LIKE ‘character\_set\_%‘ OR Variable_name LIKE ‘collation%‘;show variables like "%collation%";show collation;

• View SQL mode

show global variables like "sql_mode";

• Viewing the storage engine

show engines;show global variables like ‘%innodb%‘;    #查看innodb引擎

• Open stand-alone tablespace (restart MySQL invalidation, modify config file)

show golbal variables like "innodb_file_per_table";       #查看set global innodb_file_per_table=ON;                          #开启

• View the number of MySQL processes

show full processlist;

• View View

show create view viewname;   #查看drop view viewname;      #删除

Second, the operation of the class

• Library operations

create database dbname;    #创建数据库show databases;         #查看数据库show create database mydb;         #查看数据库创建语句drop database dbname;     #删除数据库

• Table Operations

CREATE TABLE Mytab (name varchar (20));    #创建表create table TAB1 Select User,host,... from Mysql.user;   #根据查询出来的数据创建表create table tab1 like Oldtab;     #基于oldtab Create an empty table tab1;show creating table tab1;     #查看表结构show Db.tables;      # View all tables under Library DB1 Show Table status where Name= "Tab1" \g;   #查看表tab1的状态select count (1) from TAB1;   #查看表有多少行insert into TAB1 values ("Xuel", "Kaliarch");     Insert data for Table tab1 drop table mytable;    #删除表alter table mytable Rename renametable;     #修改mytable的表名alter table renametable Add age int (20);     #更新表添加一列alter Table renametable Modify Age char (1);     #修改列的数据类型alter table renametable Change column Age Age2 char (1);    #重命名列alter table S1 Engine=myisam;    #修改表的存储引擎select column name, column name 2 from table name where column name = ' Gnome '; #按列查询delete from course where id=2; #删除表格中查询记录一条记录update table name set column name = new value where column = value; #更新数据select * from table name where bar       Piece 1 and Condition 2; #where逻辑组合select * FROM table name where Condition 1 or condition 2; SELECT column_name (s) from table1 INNER joins table2 on Table1.column_name=table2.columN_name;         #内连接SELECT column_name (s) from table1 left JOIN table2 on Table1.column_name=table2.column_name; #左连接 (the left JOIN keyword returns all rows from the table (table1), even if there is no match in the right table (table2).          If there is no match in the right table, then the result is NULL) SELECT column_name (s) from table1 R JOIN table2 on Table1.column_name=table2.column_name;          #右连接SELECT column_name (s) from table1 full OUTER joins table2 on Table1.column_name=table2.column_name; #全连接 (combined with the result of the left join and right join.)    ) SELECT * from Mysql.user limit 2;     #取两行数据SELECT DISTINCT column_name,column_name from table_name; #DISTINCT keywords are used to return only different values. SELECT column_name,column_name from Table_nameorder by Column_name,column_name asc|       DESC;                    The #ORDER by keyword sorts records by default in ascending order, descending to Descselect column_name (s) from table_name WHERE to column_name like pattern;   #WHERE column_name like pattern;                Similar to where sentence terminal pattern matching select column_name (s) from table_name WHERE column_name between value1 and value2; The #BETWEEN operator selects a value in the range of data between two values. SELECT column_name (s) from TAble_name as Alias_name;    #表的别名SELECT column_name as alias_name from table_name;      #列的别名show indexes from TAB1;         #查看tab1的索引alter Table TAB1 Add index (user);       #添加索引alter table tab1 Drop index user; #删除索引

• User Rights

create user xuel identified by "[email protected]";         #创建用户rename user xuel to xuel2;                       #重命名用户select user,password,host from mysql.user;      #查询用户grant all privileges on mydb.* to [email protected]‘%‘;        #授权（注意：如果用户未存在授权时候identified by 后新增用户）revoke all privileges xuel2;     #撤回授权select * from mysql.user where user=‘xuel1‘\G;      #查看用户详细权限show grants for xuel1;          #查看xuel1的授权set password xuel=password("[email protected]@")        #修改用户密码set password for [email protected] = password( ‘your_password‘)     update mysql.user set password=password("[email protected]") where user="xuel" and host="%";          #5.6之前更新用户密码update mysql.user set authentication_string=password(‘!8gecco4‘) where user=‘root‘ and Host = ‘localhost‘;`          #5.7更新用户密码drop user ‘xuel1‘@‘%‘;          #删除用户

MySQL Common operations