preface
Database management is very important. This paper is based on mysql8.0
text
Account Management
-
Create a new account
create user 'root'@'localhost' identified by '123456'; create user 'root'@'%' identified by '123456';
"localhost" means that the user can only log in locally, and the remote login is "%"
-
Set a new password for the account
alter user 'root'@'localhost' identified by 'Set new password for';
-
View account information
use mysql select user,host from user;
-
Delete the account drop user 'account name';
-
User authorization
#grammar GRANT privileges ON databasename.tablename TO 'username'@'host'; #privileges: the user's operation permissions, such as SELECT, INSERT, UPDATE, etc. if you want to grant the required permissions, use ALL #databasename: database name #tablename: table name. If you want to grant the user corresponding operation permissions on all databases and tables, it can be represented by *, such as ** #Grants the user all permissions on the specified table of the specified database grant all privileges on Database name.Table name to 'user name'@'host IP'; #Grant all permissions to all databases and tables in the user server grant all privileges on *.* to 'user name'@'host IP';
- After MySQL newly sets the user or changes the password, flush privileges is required to refresh the system permission related table of MySQL, otherwise access will be denied
Database management
- show databases; Display database list
- create database 'database name'; set up a database
- drop database 'database name'; Delete database
- use 'database name'; The next operation is to modify the library
data sheet
-
show tables; View all table names in the database
-
create table [table name] (field name, data type, -- –, --–); create table
mysql> create table account( -> id int, -> name varchar(255) -> );
-
The retrieved data can also be used as the data source when creating a table
-
Data type (some must be marked with size)
-
drop table 'table name'; Delete table
-
alter table 'old table name' rename 'new table name'; Modify table name
-
describe 'table name' or desc 'table name'; Displays the structure of the data table without retrieving data
-
alter table 'table name' Add 'field name', 'data type' [not null] '' [default] '(the latter two items are unnecessary) add a column
alter table account add weixin varchar(255);
-
alter table 'table name' drop 'field name'; Delete a column
-
alter table 'table name' change 'old field name', 'new field name', 'data type'; Modify the information of the field name
alter table account change weixin qq varchar(255);
data row
-
insert into [table name] values (value 1, value 2,...); Insert data into the table (the number of values is the same as the number of fields)
mysql> desc account; +-------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+-------+ | id | int | YES | | NULL | | | name | varchar(255) | YES | | NULL | | | qq | varchar(255) | YES | | NULL | | +-------+--------------+------+-----+---------+-------+ mysql> insert into account(id, name, qq) values(1, 'yuwenlong', '1589292300');
It is better to specify the column and give the column. Even if the table structure changes, it can continue to work. In addition, the same record cannot be inserted twice
-
insert into [table name] (column 1, column 2...) values; insert data into only a few columns in the table
mysql> select * from account; +------+-----------+------------+ | id | name | qq | +------+-----------+------------+ | 1 | yuwenlong | 1589292300 | +------+-----------+------------+ mysql> insert into account (id,name) values(2,'yyy'); mysql> select * from account; +------+-----------+------------+ | id | name | qq | +------+-----------+------------+ | 1 | yuwenlong | 1589292300 | | 2 | yyy | NULL | +------+-----------+------------+
-
update [table name] set [field name] = xxx [where clause]; modifying the data information of a single column depends on where
-
update [table name] set [field name 1] = xxx, [field name 2] =xxx... [where clause]; modify the data information of multiple columns
mysql> alter table account add mm int ; mysql> select * from account; +------+-------------+-------------+--------+------+ | id | name | qq | gender | mm | +------+-------------+-------------+--------+------+ | 1 | yuwenlong | 1589292300 | 1 | NULL | | 2 | wuchengxiao | 12345678 | 1 | NULL | | 3 | genziyue | 987654321 | 0 | NULL | | 3 | zhl | 123213123 | 0 | NULL | | 4 | zhouyang | 37427837847 | 1 | NULL | | 5 | ssss | 36261736 | 0 | NULL | | 6 | erw | 762377278 | 0 | NULL | | 7 | hsdhf | 3728338 | 1 | NULL | | 8 | sdfsd | 32477278 | 1 | NULL | | 9 | jsjdsj | 378282832 | 0 | NULL | | 10 | sdfs3we | 273878278 | 0 | NULL | | 11 | ehjrh32 | 636467 | 0 | NULL | | 12 | 3jjsjd | 37274728372 | 1 | NULL | | 13 | eyrwyu | 2367476237 | 1 | NULL | +------+-------------+-------------+--------+------+ mysql> update account set mm = 1 where gender=0; mysql> update account set mm =737 where id>=6; mysql> select * from account ; +------+-------------+-------------+--------+------+ | id | name | qq | gender | mm | +------+-------------+-------------+--------+------+ | 1 | yuwenlong | 1589292300 | 1 | NULL | | 2 | wuchengxiao | 12345678 | 1 | NULL | | 3 | genziyue | 987654321 | 0 | 1 | | 3 | zhl | 123213123 | 0 | 1 | | 4 | zhouyang | 37427837847 | 1 | NULL | | 5 | ssss | 36261736 | 0 | 1 | | 6 | erw | 762377278 | 0 | 737 | | 7 | hsdhf | 3728338 | 1 | 737 | | 8 | sdfsd | 32477278 | 1 | 737 | | 9 | jsjdsj | 378282832 | 0 | 737 | | 10 | sdfs3we | 273878278 | 0 | 737 | | 11 | ehjrh32 | 636467 | 0 | 737 | | 12 | 3jjsjd | 37274728372 | 1 | 737 | | 13 | eyrwyu | 2367476237 | 1 | 737 | +------+-------------+-------------+--------+------+ mysql> update account set mm =33 where mm is NULL; mysql> select * from account; +------+-------------+-------------+--------+------+ | id | name | qq | gender | mm | +------+-------------+-------------+--------+------+ | 1 | yuwenlong | 1589292300 | 1 | 33 | | 2 | wuchengxiao | 12345678 | 1 | 33 | | 3 | genziyue | 987654321 | 0 | 1 | | 3 | zhl | 123213123 | 0 | 1 | | 4 | zhouyang | 37427837847 | 1 | 33 | | 5 | ssss | 36261736 | 0 | 1 | | 6 | erw | 762377278 | 0 | 737 | | 7 | hsdhf | 3728338 | 1 | 737 | | 8 | sdfsd | 32477278 | 1 | 737 | | 9 | jsjdsj | 378282832 | 0 | 737 | | 10 | sdfs3we | 273878278 | 0 | 737 | | 11 | ehjrh32 | 636467 | 0 | 737 | | 12 | 3jjsjd | 37274728372 | 1 | 737 | | 13 | eyrwyu | 2367476237 | 1 | 737 | +------+-------------+-------------+--------+------+
-
Insert and update can use the retrieved data as the data source (sub query)
-
delete from [table name] [where statement] deletes data from MySQL data table
mysql> select * from book2; +------+---------+---------+-------+ | id | title | content | pages | +------+---------+---------+-------+ | 1 | dfsd | fdsdf | 23 | | 2 | sdfdsz | dhfhjsd | 12 | | 3 | sjhdfj | shjdfhj | 29 | | 4 | hgdfh | hwsdh | 45 | | NULL | fdsdf | NULL | NULL | | NULL | dhfhjsd | NULL | NULL | | NULL | shjdfhj | NULL | NULL | | NULL | hwsdh | NULL | NULL | +------+---------+---------+-------+ mysql> delete from book2 where id is NULL; Query OK, 4 rows affected (0.02 sec) mysql> select * from book2; +------+--------+---------+-------+ | id | title | content | pages | +------+--------+---------+-------+ | 1 | dfsd | fdsdf | 23 | | 2 | sdfdsz | dhfhjsd | 12 | | 3 | sjhdfj | shjdfhj | 29 | | 4 | hgdfh | hwsdh | 45 | +------+--------+---------+-------+ mysql> delete from book2 ; Query OK, 4 rows affected (0.01 sec) mysql> select * from book2; Empty set (0.00 sec)
Last words
This article is for personal reference later