MySQL | MySQL database system (2) – the basic operations of a SQL statement

This is the 22nd day of my participation in Gwen Challenge

---

A lifelong learner, practitioner and sharer committed to the path of technology, a busy and occasionally lazy original blogger, an occasionally boring and occasionally humorous teenager.

Welcome to dig friends wechat search “Jie Ge’s IT journey” attention!

The original link: MySQL | MySQL database system (2) – the basic operations of a SQL statement

preface

In the previous article, we were familiar with how to set up and log in to the MySQL database system. The article “How to deploy MySQL database in Linux environment!” So, next, we will learn how to use the basic operation of MySQL database system through this article, which is also what we will use in the future work.

Check which libraries are present on the current server

The show databases statement is used to check the databases in the MySQL database system.

MySQL > initialize MySQL; MySQL > initialize MySQL;

Information_schema mysql Performance_schema testCopy the code

mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | The test | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 4 rows in the set (0.01 SEC)Copy the code

See which tables are in the library you are currently using

The show tables statement is used to view the tables contained in the current library.

To do this, use the use statement to switch to the library you want to use.

If you want to know which tables are in the mysql library you are currently using, go to the mysql target library and run the show tables statement. To view a table in the mysql library.

mysql> use mysql; Database changed mysql> show tables; +---------------------------+ | Tables_in_mysql | +---------------------------+ | columns_priv | | db | | event | | func | | general_log | | help_category | | user | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + 24 rows in the set (0.00 SEC)Copy the code

The structure of the query table

Describe statement: Used to display the structure of a table and the information about the fields that make up the table.

Specify “library name, table name” as the parameter. When specifying the table name, use the use statement to switch to the desired library.

Describe mysql.user; describe mysql.user; describe mysql.user; View that the output structure is consistent.

mysql> describe user; +------------------------+-----------------------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------------------+-----------------------------------+------+-----+---------+-------+ | Host | char(60) | NO | PRI | | | | User | char(16) | NO | PRI | | | | Password | char(41) | NO | | | | | Select_priv | enum('N','Y') | NO | | N | | | Insert_priv | enum('N','Y') | NO | | N | | | max_user_connections | int(11) unsigned | NO  | | 0 | | | plugin | char(64) | YES | | | | | authentication_string | text | YES | | NULL | | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + + -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- + 42 rows in the set (0.00 SEC)Copy the code

View the current database version

select version(); SQL > query mysql database version;

mysql> select version(); + -- -- -- -- -- -- -- -- -- -- -- -- + | version () | + -- -- -- -- -- -- -- -- -- -- -- -- + | 5.5.22 - log | + -- -- -- -- -- -- -- -- -- -- -- -- + 1 row in the set (0.02 SEC)Copy the code

View which library you are currently in

select database(); Statement: used to view which library is currently in;

mysql> select database(); + -- -- -- -- -- -- -- -- -- -- -- -- + | database () | + -- -- -- -- -- -- -- -- -- -- -- -- + | mysql | + -- -- -- -- -- -- -- -- -- -- -- -- + 1 row in the set (0.00 SEC)Copy the code

Display in column format

In a MySQL database, executing a SQL statement followed by \G means printing the query result as a column.

By default, MySQL database query results are output horizontally.

The first row represents the column header and the second column represents the recordset.

mysql> select * from db\G
*************************** 1. row ***************************
                 Host: %
                   Db: test
                 User: 
          Select_priv: Y
          Insert_priv: Y
          Update_priv: Y
          Delete_priv: Y
          Create_priv: Y
            Drop_priv: Y
           Grant_priv: N
      References_priv: Y
           Index_priv: Y
           Alter_priv: Y
Create_tmp_table_priv: Y
     Lock_tables_priv: Y
     Create_view_priv: Y
       Show_view_priv: Y
  Create_routine_priv: Y
   Alter_routine_priv: N
         Execute_priv: N
           Event_priv: Y
         Trigger_priv: Y
*************************** 2. row ***************************
                 Host: %
                   Db: test\_%
                 User: 
          Select_priv: Y
          Insert_priv: Y
          Update_priv: Y
          Delete_priv: Y
          Create_priv: Y
            Drop_priv: Y
           Grant_priv: N
      References_priv: Y
           Index_priv: Y
           Alter_priv: Y
Create_tmp_table_priv: Y
     Lock_tables_priv: Y
     Create_view_priv: Y
       Show_view_priv: Y
  Create_routine_priv: Y
   Alter_routine_priv: N
         Execute_priv: N
           Event_priv: Y
         Trigger_priv: Y
2 rows in set (0.00 sec)
Copy the code

Create a library

Create DATABASE statement: to create a new database, specify the new database name as the parameter;

Auth will be the name of the new library, which does not contain any tables.

mysql> create database auth;
Query OK, 1 row affected (0.45 sec)
Copy the code

The /usr/local/mysql.data directory of the newly created library will automatically generate a folder with the same name as the newly created library.

[root@localhost ~]# cd /usr/local/mysql/data
[root@localhost data]# ls
auth     ib_logfile0  localhost.err  mysql             mysql-bin.000002  mysql-bin.000004  mysql.error.log     test
ibdata1  ib_logfile1  localhost.pid  mysql-bin.000001  mysql-bin.000003  mysql-bin.index   performance_schema
Copy the code

Create table

Create table statement: used to create a new table in the current Auth library, specify the data table name as the parameter to create the new table, and define the fields used to create the new table;

Before creating a table, specify the structure, field names, and type of the table you want to create.

The basic syntactic format for creating tables

Create table Table name (field 1 name type, field 2 name type,...... , PRIMARY KEY (PRIMARY KEY))Copy the code

Create the users table, set the default statement of the field definition part to set the default password character, the primary statement to set the primary key field name;

mysql> use auth;
Database changed
mysql> create table users (user_name char(16) not null, user_passwd char(48) default '', primary key (user_name));
Query OK, 0 rows affected (0.66 sec)
Copy the code

Delete tables and databases

Drop table statement: to drop a table from a database, specify “database name, table name” as the parameter.

To specify table name parameters, execute the “use” statement to switch to the target library.

To delete the Users table in the Auth library, do the following.

mysql> drop table auth.users;
Query OK, 0 rows affected (0.09 sec)
Copy the code

Drop DATABASE statement: used to drop the specified library, specify the name of the library as a parameter;

To delete the Auth library, do the following.

mysql> drop database auth; Query OK, 0 rows affected (0.02sec)Copy the code

Manage the data record in the table

In the previous steps, we introduced the creation of libraries and tables. Now we will manage the data in the tables based on the Auth library. Insert, query, modify and delete records in the database

Insert data

Insert into statement: Used to insert new data into the target table.

Basic statement format

Insert into values(1, 1, 2) values(1, 2, 3)Copy the code

Insert a record into auth’s user table where user_name is ‘jacktian’ and user_passwd is ‘666666’.

Note that the values inserted in VALUES must correspond to the fields specified earlier.

mysql> use auth; Database changed mysql> show tables; + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | Tables_in_auth | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | the servers | | users | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + 2 rows in the set (0.00) sec) mysql> INSERT INTO users(user_name,user_passwd) VALUES('jacktian', PASSWORD('666666')); Query OK, 1 row affected (0.00 SEC)Copy the code

Check whether the table structure is correctly established.

mysql> describe users; +-------------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+----------+------+-----+---------+-------+ | user_name | char(16) | NO | PRI | NULL | | | user_passwd | Char (48) | YES | | | | + -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + + -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- + 2 rows in the set (0.01 SEC)Copy the code

Inserts a new data record. If this record contains the values of all the fields in the table, some fields of the specified field in the inserted statement may be omitted.

mysql> insert into users values ('jake' , password('888888')); Query OK, 1 row affected (0.01sec)Copy the code

View the data

Select statement: used to view the specified table to view the data records that meet the conditions;

Basic statement format

2, 1, select the field name field name... the from name of the table where the conditional expressionCopy the code

To query all fields, use the wildcard “*” character to display all data records and omit the WHERE condition. All the data in the Users table in the Auth library can be viewed when you do the following, where the user_passwd string is encrypted, so the actual password content is not displayed.

mysql> select * from auth.users;
+-----------+-------------------------------------------+
| user_name | user_passwd                               |
+-----------+-------------------------------------------+
| jacktian  | *B2B366CA5C4697F31D4C55D61F0B17E70E5664EC |
| jake      | *DA28842831B3C40F4BC1D3C76CF9AD8CBFDAE1CB |
+-----------+-------------------------------------------+
2 rows in set (0.07 sec)
Copy the code

When you need to query data according to the specified criteria, you can use the WHERE criteria to query data. Select * from users where user_name = ‘jacktian’; select * from users where username = ‘jacktian’;

mysql> select user_name,user_passwd from auth.users where user_name='jacktian'; +-----------+-------------------------------------------+ | user_name | user_passwd | +-----------+-------------------------------------------+ | jacktian | *B2B366CA5C4697F31D4C55D61F0B17E70E5664EC | + -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 1 row in the set (0.01 SEC)Copy the code

Modify the data

Update statement: Used to modify, update data in the target table.

Basic statement format

Update table_name set table_name = table_name [, table_name = table_name] WHERE table_name = table_name [, table_name = table_nameCopy the code

To change the user name ‘Jake’ in the Users table, set the password to null, and check whether the password of the user name ‘Jake’ is null.

mysql> update auth.users set user_passwd=password ('') where user_name = 'jake'; Query OK, 1 row affected (0.05 SEC) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from auth.users; +-----------+-------------------------------------------+ | user_name | user_passwd | +-----------+-------------------------------------------+ | jacktian | *B2B366CA5C4697F31D4C55D61F0B17E70E5664EC | | Jake | | + -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 2 rows in the set (0.00 SEC)Copy the code

Typically, in a MySQL database, the various user information we use to access the database will be stored in the user table in the MySQL library, where the data can be directly modified.

mysql> use mysql; Database changed mysql> show tables; +---------------------------+ | Tables_in_mysql | +---------------------------+ | columns_priv | | db | | event | | user | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 23 rows in the set (0.06 SEC)Copy the code

MySQL -u root -p = ‘666666’; MySQL -u root -p = ‘666666’; Otherwise, you cannot log in to the MySQL database system.

It should be noted that the database user password can be set more complex to ensure the security of the data;

mysql> update mysql.user set password=password ('666666') where user= 'root'; Query OK, 3 rows affected (0.00 SEC) rows matched: 3 Changed: 3 Warnings: 0 mysql> FLUSH PRIVILEGES; // Refresh user authorization informationCopy the code

If you are running a Linux command line terminal, you can also use the mysqladmin tool to set the password.

[root@localhost data]# mysqladmin -u root -p password '666666'
Enter password: 
Copy the code

Delete the data

Delete statement: used to delete the specified data in the table;

Basic statement format

Delete from table name where conditional expressionCopy the code

To delete data in the Users table of the AUth library where user_name user name is: ‘jake’, run the following statement to verify whether the data has been deleted.

mysql> delete from auth.users where user_name = 'jake'; Query OK, 1 row affected (0.03 SEC) mysql> select * from auth.users; +-----------+-------------------------------------------+ | user_name | user_passwd | +-----------+-------------------------------------------+ | jacktian | *B2B366CA5C4697F31D4C55D61F0B17E70E5664EC | + -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 1 row in the set (0.00 SEC)Copy the code

In the MySQL database system, an empty user is added by default to access the database from the native machine. The user and password fields are null. You can delete these empty users for database security. A conditional expression after where that filters users whose user field is empty and verifies whether the user’s data has been deleted.

mysql> select user,host,password from mysql.user where user = ''; +------+-----------+----------+ | user | host | password | +------+-----------+----------+ | | localhost | | + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- + 1 row in the set (0.00 SEC) mysql > delete from mysql. The user where the user = ' '; Query OK, 1 row affected (0.04 SEC) mysql> select user,host,password from mysql. User where user = ''; The Empty set (0.00 SEC)Copy the code

Recommended reading

How to deploy MySQL database in Linux

---

Original is not easy, if you think this article is useful to you, please kindly like, comment or forward this article, because this will be my power to output more high-quality articles, thank you!

By the way, please give me some free attention! In case you get lost and don’t find me next time.

See you next time!