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