MySQL Basic operation

MySQL Basic operation

for an enterprise, the importance of data is beyond doubt. As an OPS personnel, must master the basic database operation, in order to facilitate later view, I have some simple database command to write out.

View Database

MariaDB [(None)]> show databases;+--------------------+| Database |+--------------------+| Information_schema | | MySQL | | Performance_schema | | Test |+--------------------+

Create a database

MariaDB [(None)]> CREATE Database xhk;

Enter the database and create a table

MariaDB [(None)]> use xhk;database changedmariadb [xhk]> CREATE TABLE stu (ID int auto_increment primary key,name VA Rchar () unique key);

There are 2 ways to view the structure of a table

mariadb [xhk]> desc stu;+-------+-------------+------+-----+---------+----------------+|  field | type        | null | key |  default | extra          |+-------+--------- ----+------+-----+---------+----------------+| id    | int (one)       | no   | pri | null    | auto_ increment | |  name  | varchar ( | YES  | UNI | NULL  )   |                | +-------+-------------+------+-----+---------+----------------+mariadb [xhk]> show create  table stu;+-------+-------------------------------------------------------------------------------- -----------------------------------------------------------------------------------------------------------+| table  | Create Table                                                                                                                                                                                       |+-------+---------------------- --------------------------------------------------------------------------------------------------------------- ------------------------------------------------------+| stu   | create table   ' Stu '   (   ' id '  int (one)  NOT NULL AUTO_INCREMENT,   ' name '  varchar  DEFAULT NULL,  PRIMARY KEY  (' id '),   unique key   ' name '   (' name ')  engine=innodb default charset=latin1 |+-------+--------------- --------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------+

Inserting more than one data into a table

MariaDB [xhk]> INSERT INTO Stu (id,name) value (1, ' xhk '), (2, ' xxx ');

Querying the contents of a table

MariaDB [xhk]> SELECT * FROM stu;+----+------+| ID |  Name |+----+------+| 1 |  XHK | | 2 | XXX |+----+------+

You can also query as needed

MariaDB [xhk]> select * from stu where id = 1;+----+------+| ID |  Name |+----+------+| 1 | XHK |+----+------+

Query all with the * number, but the data on the line is large, using * may cause database pressure to increase, affect performance, we recommend using specific values to query

MariaDB [xhk]> select name from stu;+------+| Name |+------+| XHK | | XXX |+------+

modifying data in a table

MariaDB [xhk]> Update xhk set name= ' QQQ ' where id=2;

Delete a row in a table

MariaDB [xhk]> Delete from Stu where id=2;

Add a table field

MariaDB [xhk]> ALTER TABLE stu add age int;

Delete a table's fields

MariaDB [xhk]> ALTER TABLE Stu drop age;

Modify the type of a field

MariaDB [xhk]> ALTER TABLE STU modify name varchar (20);

Modify the name and type of a field

MariaDB [xhk]> ALTER TABLE STU change name names varchar (10);

Modify the name of the table

MariaDB [xhk]> Rename table Stu to student;

Delete all data in a table

MariaDB [xhk]> Delete from student;

Create user and set password and specify user ownership of all libraries, and allow all hosts

MariaDB [xhk]> Grant All on * * to "xhk1" @ "%" identified by "123456";

To modify a user's password

MariaDB [xhk]> Update mysql.user set Password=password (' Redhat ') where user= "Xhk1";

To view a user's permissions

mariadb [xhk]> show grants for  "xhk1" @ "localhost"; +------------------------------- ---------------------------------------------------------------------------------------+| grants for  [email protected]                                                                                                 |+------------------------------------------------------------------ ----------------------------------------------------+| grant all privileges on *.*  TO  ' Xhk1 ' locAlhost '  IDENTIFIED BY PASSWORD  ' *6bb4837eb74329105ee4568dda7dc67ed2ca2ad9 '  |+--------  --------------------------------------------------------------------------------------------------------------+

Create users only have query permission on the student table of the XHK library, and only allow local logins

MariaDB [xhk]> Grant Select,update,delete,insert on xhk.student to "xhk1" @ "localhost" identified by "123456";

Reclaim a user's permissions

MariaDB [xhk]> revoke Update,delete,insert on xhk.student to "xhk1" @ "localhost";

Delete User

MariaDB [xhk]> Delete from Mysql.user where name= "Xhk1" and host= "localhost";

This article from "Xhk__ Yun" blog, reproduced please contact the author!

MySQL Basic operation