Mysql Management Operations

Database management operations

• View database structure
• Create and delete libraries and tables
• Manage records for tables

Basic Operational Commands

• View database list information
  • SHOW DATABASES

[root@localhost ~]# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.17 Source distribution

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

• View table information in the database
  • USE database name
  • SHOW TABLES

mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| engine_cost               |
| event                     |
| .....                     |
| user                      |
+---------------------------+
31 rows in set (0.00 sec)

• Display the structure of the data table (fields)
  • DESCRIBE [database name.] table name

mysql> describe db;
+-----------------------+---------------+------+-----+---------+-------+
| Field                 | Type          | Null | Key | Default | Extra |
+-----------------------+---------------+------+-----+---------+-------+
| Host                  | char(60)      | NO   | PRI |         |       |
| Db                    | char(64)      | NO   | PRI |         |       |
| User                  | cha(32)      | NO   | PRI |         |       |
| Select_priv           | enum('N','Y') | NO   |     | N       |       |
| Execute_priv          | enum('N','Y') | NO   |     | N       |       |
| ...                                                                  |
| Trigger_priv          | enum('N','Y') | NO   |     | N       |       |
+-----------------------+---------------+------+-----+---------+-------+
22 rows in set (0.00 sec)

Overview of SQL statements

SQL Language

• Is the abbreviation of Structured Query Language, Structured Query Language
• Is the standard language for relational databases
• Used to maintain and manage databases, such as data query, data update, access control, object management, etc.

SQL Classification

• DDL: Data Definition Language
• DML: Data Manipulation Language
• DQL: Data Query Language
• DCL: Data Control Language

DDL Statement Action

• DDL statements are used to create database objects, such as libraries, tables, indexes, and so on
• Use DDL statements to create new libraries and tables
  • Create database: CREATE DATABASE database name
  • Create Datasheet: CREATE TABLE Table Name (Field Definition...)

mysql> create database school;
Query OK, 1 row affected (0.00 sec)

mysql> use school;
Database changed
mysql> create table info (
    -> id int(4) ,
    -> name char(10) not null,
    -> address varchar(50) default 'nanjing',
    -> primary key (id));
Query OK, 0 rows affected (0.01 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school             |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| info             |
+------------------+
1 row in set (0.00 sec)

DDL Statement Action

• Delete libraries, tables using DDL statements
  • Delete the specified table: DROP TABLE [database name.]table name
  • Delete the specified database: DROP DATABASE database name

mysql> drop table info;
Query OK, 0 rows affected (0.00 sec)

mysql> show tables;
Empty set (0.00 sec)

mysql> drop database school;
Query OK, 0 rows affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

DML Statement Operation

• DML statements are used to manage data in tables
• Include the following actions
  • INSERT: Insert new data
  • UPDATE: Update original data
  • DELETE: Delete unnecessary data

mysql> create database school;
Query OK, 1 row affected (0.01 sec)

mysql> use school;
Database changed
mysql> create table info ( 
    -> id int(4) not null,
    -> name char(10) not null,
    -> address varchar(50) default 'nanjing',
    -> primary key (id));
Query OK, 0 rows affected (0.00 sec)

mysql> insert into info (id,name,address) values (1,'zhangsan','beijing');
Query OK, 1 row affected (0.01 sec)

mysql> select * from info;       //View all contents of the table
+----+----------+---------+
| id | name     | address |
+----+----------+---------+
|  1 | zhangsan | beijing |
+----+----------+---------+
1 row in set (0.00 sec)

mysql> update info set address='shanghai' where id=1;    //Change address with id 1 in info table to shanghai
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from info;              //View Table Contents
+----+----------+----------+
| id | name     | address  |
+----+----------+----------+
|  1 | zhangsan | shanghai |
+----+----------+----------+
1 row in set (0.00 sec)

mysql> delete from info where id=1;     //Conditionally delete data with id 1 in the info table and all data in the table without where condition
Query OK, 1 row affected (0.00 sec)

mysql> select * from info;
Empty set (0.00 sec)

DQL Statement Operation

• DQL is a data query statement with only one: SELECT
• Used to find qualified data records from a data table
• No criteria can be specified when querying
  • SELECT field name 1, field name 2.... FROM table name

mysql> select * from info;       //View all contents of the table
+----+----------+---------+
| id | name     | address |
+----+----------+---------+
|  1 | zhangsan | beijing |
+----+----------+---------+
1 row in set (0.00 sec)

mysql> select name from info where id=1;      //Conditional View Table Contents
+----------+
| name     |
+----------+
| zhangsan |
+----------+
1 row in set (0.00 sec)

DCL Statement Operation

• Set user permissions (when the user does not exist).New user

  • GRANT Permission List ON Database Name.Table Name TO User Name@Source Address [IDENTIFIED BY'Password']

• View user's permissions

  • SHOW GRANT FOR User Name@Source Address
• Revoke user's rights
  • REVOKE Permission List ON Database Name.Table Name FROM User Name@Source Address