ch2 data management

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

Tags: Database MySQL SQL

Posted on Mon, 22 Nov 2021 10:51:25 -0500 by synical21

Programmer Help