Shulou(Shulou.com)06/01 Report--

This article mainly introduces what are the common operations of MySQL, the contents of the articles are carefully selected and edited by the author, with a certain pertinence, the reference significance for everyone is still relatively great, the following with the author to understand what common operations MySQL has.

1. View class view mysql current metrics status;show status; view character set show variables like "% character%"; show character set; view current user select user (); view collation SHOW VARIABLES WHERE Variable_name LIKE 'character\ _ set\%' OR Variable_name LIKE 'collation%';show variables like "% collation%"; show collation; view sql modeshow global variables like "sql_mode"; view storage engine show engines;show global variables like'% innodb%' # View innodb engine to open independent tablespace (restart mysql fails, you need to modify configuration file) show golbal variables like "innodb_file_per_table"; # View set global innodb_file_per_table=ON; # Open View number of mysql processes show full processlist; View show create view viewname; # View drop view viewname; # Delete Class Library Operation create database dbname # create database show databases; # View database show create database mydb; # View database creation statement drop database dbname; # delete database table operation create table mytab (name varchar (20)) # create table create table tab1 select user,host,... From mysql.user; # create tables based on queried data create table tab1 like oldtab; # create empty tables based on oldtab # View table structure show db.tables; # View all tables under db1 show table status where name= "tab1"\ G; # View the status of table tab1 select count (1) from tab1; # see how many rows insert into tab1 values ("xuel", "kaliarch") Insert data drop table mytable; for table tab1 # delete table alter table mytable rename renametable; # modify mytable table name alter table renametable add age int (20); # update table add a column alter table renametable modify age char (1); # modify column data type alter table renametable change column age age2 char (1); # rename column alter table S1 engine=myisam # modify the select column name of the storage engine of the table, column name 2 from table name where column name = 'gnome'; # query delete from course where id=2 by column # Delete table query record A record update table name set column name = new value where column = value # Update data select * from table name where condition 1 and condition 2; # where logical combination select * from table name where condition 1 or condition 2 select column_name (s) FROM table1 INNER JOIN table2 ON table1.column_name=table2.column_name; # join SELECT column_name (s) FROM table1 LEFT JOIN table2 ON table1.column_name=table2.column_name within # left join (the LEFT JOIN keyword returns all rows from the left table (table1), even if there is no match in the right table (table2). If there is no match in the right table, the result is NULL) SELECT column_name (s) FROM table1 RIGHT JOIN table2 ON table1.column_name=table2.column_name; # right join SELECT column_name (s) FROM table1 FULL OUTER JOIN table2 ON table1.column_name=table2.column_name; # full connection (combining the results of LEFT JOIN and RIGHT JOIN. ) select * from mysql.user limit 2; # take two rows of data SELECT DISTINCT column_name,column_name FROM table_name; # DISTINCT keywords to return unique different values. SELECT column_name,column_name FROM table_nameORDER BY column_name,column_name ASC | the DESC; # ORDER BY keyword sorts the records in ascending order by default, and the descending order is descSELECT column_name (s) FROM table_name WHERE 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 values within the data range between two values. SELECT column_name (s) FROM table_name AS alias_name; # aliases for table SELECT column_name AS alias_name FROM table_name; # aliases for column show indexes from tab1; # View tab1's index alter table tab1 add index (user); # add index alter table tab1 drop index user; # remove index user right create user xuel identified by "xuel@anchnet.com"; # create user rename user xuel to xuel2 # rename user select user,password,host from mysql.user; # query user grant all privileges on mydb.* to xuel2@'%'; # Authorization (Note: if the user does not have authorization, add user after identified by) revoke all privileges xuel2; # withdraw authorization select * from mysql.user where user='xuel1'\ G; # View user detailed permissions show grants for xuel1 # View xuel1's authorized set password xuel=password ("xxzx567@@") # modify the user password set password for username@host = password ('your_password') update mysql.user set password=password ("xxzx@789") where user= "xuel" and host= "%"; update the user password update mysql.user set authentication_string=password ('! 8gecco4') where user='root' and Host = 'localhost' before # 5.6 `# 5.7 Update user password

Drop user 'xuel1'@'%'; # Delete user

After reading the above about the common operations of MySQL, many readers must have some understanding. If you need more industry knowledge and information, you can continue to pay attention to our industry information column.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.