DDL, DML statements

DDL (Data Definition Language) data definition language is to create, delete and modify the internal objects of the database; DML (Data manipulation language) is a data manipulation language, which operates on the data in the table.

Create a database - CREATE

• Display the currently created database

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

• Create a database - CREATE

mysql> CREATE DATABASE test_db;
Query OK, 1 row affected (0.00 sec)

Delete the database - DROP

mysql> DROP DATABASE test_db;
Query OK, 0 rows affected (0.00 sec)

Note: Statements end with semicolons and commands are case-insensitive

Create tables

• Basic syntax for creating tables

CREATE TABLE tablename(
col_name1 col_type1 constrations, 
col_name2 col_type2 constrations,
col_name3 col_type3 constrations,

Note: Field name field type constraints

• Example:

mysql> CREATE TABLE test_tb(
    -> name varchar(10), 
    -> age int(6));
Query OK, 0 rows affected (0.24 sec)

Note: Select the database use db_name before creating the table

• Viewing Table Definitions with DESC
  DESC table-name

mysql> DESC test_tb;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(10) | YES  |     | NULL    |       |
| age   | int(6)      | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

• More comprehensive display table definition

mysql> show create table test_tb \G;
*************************** 1. row ***************************
       Table: test_tb
Create Table: CREATE TABLE `test_tb` (
  `name` varchar(10) DEFAULT NULL,
  `age` int(6) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

\ G means to allow records to be arranged vertically by field

Delete tables

• Delete table command: DROP TABLE table name;

mysql> DROP TABLE test_tb;
Query OK, 0 rows affected (0.14 sec)

Amendment table

• Modify table type
  The grammar is as follows:
  ALTER TABLE table_name MODIFY[COLUMN] column_definition[FIRST|AFTER col_name]

mysql> ALTER TABLE test_tb MODIFY age int(6);
Query OK, 0 rows affected (0.08 sec)

• Add table fields
  The grammar is as follows:
  ALTER TABLE table_name ADD[COLUMN] column_definition[FIRST|AFTER col_name]

mysql> ALTER TABLE test_tb ADD address varchar(50);
Query OK, 0 rows affected (0.39 sec)

• Delete table fields
  The grammar is as follows:
  ALTER TABLE tablename DROP[COLUMN] col_name

mysql> ALTER TABLE test_tb DROP job;
Query OK, 0 rows affected (0.41 sec)

• Field renaming
  The grammar is as follows:
  ALTER TABLE tablename CHANGE[COLUMN] old_col_name column_definition

mysql> alter table test_tb change tel telephone varchar(20);
Query OK, 0 rows affected (0.08 sec)

• Modify the field order
  There is an optional first|alter colnum_name in the addition and modification grammar of the field, which is used to modify the position of the field in the table.

mysql> alter table test_tb add sex varchar(2) after name;
Query OK, 0 rows affected (0.41 sec)

• Change table name
  The grammar is as follows:
  ALTER TABLE tablename RENAME[TO] new_tablename

mysql> alter table test_tb rename table_test;
Query OK, 0 rows affected (0.14 sec)

DML statement

DML statements are mainly table operations in the database, including insertion, update, deletion and query.

insert record

The grammar is as follows:
INSERT INTO tablename(field1, field2, field3...fieldn)VALUES(value1, value2, value3, valuen);

mysql> insert into test_tb
    -> (name, birth, age)
    -> values
    -> ('whc', '1995-03-02', 22);
Query OK, 1 row affected (0.04 sec)

Note: Contains nullable fields, non-nullable fields, but with default values and self-increasing fields, which may not appear in the list of fields after insert
In MySQL, you can insert multiple records at a time. The grammar is as follows:
INSERT INTO tablename(field1, field2, field3)
VALUES
(record1_value1, record1_value2, record1_value3),
(record2_value1, record2_value2, record2_value3);

mysql> insert into test_tb
    -> (name, birth, age)
    -> values
    -> ('csy', '1996-06-25', 21), 
    -> ('xjt', '1995-10-02', 21);

Note: Each record is separated by commas

Update records

The grammar is as follows:
UPDATE tablename SET field1=value1, field2=value2, .....[WHERE CONDITION]

mysql> update test_tb set birth='1995-11-2' where name='xjt';
Query OK, 1 row affected (0.06 sec)

Now there are two tables, one is the relationship between the unit price and the number of items, and the other is the record of customers'purchases.

mysql> create table price(
    -> num int(4), 
    -> price int(2));
Query OK, 0 rows affected (0.25 sec)

mysql> create table someonebuy(
    -> name varchar(10), 
    -> num int(4), 
    -> price int(6) default 0);
Query OK, 0 rows affected (0.26 sec)


mysql> desc price;
+-------+--------+------+-----+---------+-------+
| Field | Type   | Null | Key | Default | Extra |
+-------+--------+------+-----+---------+-------+
| num   | int(4) | YES  |     | NULL    |       |
| price | int(2) | YES  |     | NULL    |       |
+-------+--------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> desc someonebuy;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(10) | YES  |     | NULL    |       |
| num   | int(4)      | YES  |     | NULL    |       |
| price | int(6)      | YES  |     | 0       |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

Now insert three users and use the default value for total consumption

mysql> insert into price
    -> values
    -> (1, 10), 
    -> (2, 9), 
    -> (3, 7);
Query OK, 3 rows affected (0.05 sec)

mysql> insert into someonebuy
    -> (name, num)
    -> values
    -> ('whc', 1),
    -> ('csy', 2),
    -> ('xjt', 3);
Query OK, 3 rows affected (0.04 sec)

Next, update the grammar to calculate the amount of consumption

mysql> update someonebuy a, price b set a.price=a.num*b.price where a.num=b.num; 
Query OK, 3 rows affected (0.04 sec)

mysql> select * from someonebuy;
+------+------+-------+
| name | num  | price |
+------+------+-------+
| whc  |    1 |    10 |
| csy  |    2 |    18 |
| xjt  |    3 |    21 |
+------+------+-------+
3 rows in set (0.00 sec)

Delete records

The grammar is as follows:
DELETE FROM tablename[WHERE CONDITION];

mysql> delete from someonebuy where name='whc';
Query OK, 1 row affected (0.03 sec)

Delete records from multiple tables at a time

mysql> delete a, b from price a, someonebuy b where a.num=3 and b.name='csy';
Query OK, 2 rows affected (0.05 sec)

Query Record

The grammar is as follows:
SELECT * FROM tablename[WHERE CONDITION];
You can also use comma-separated fields instead.

mysql> select name, price from someonebuy;
+------+-------+
| name | price |
+------+-------+
| xjt  |    21 |
+------+-------+

Other query requirements:

• Query non-duplicate records
  Add distinct keyword after select to remove duplication and display it
• Conditional Query
  Adding where statement at the end, the conditions after where can be used as "=",">","<",">=","<=","!="; the logical operators such as "and", "or" can be used for mu lt i-condition joint query.
• Sorting and Restriction
  Using the keyword ORDER BY, the grammar is as follows:
  SELECT *FROM tablename[WHERE CONDITION] [ORDER BY field1[DESC\ASC], field2..............]
  Where field means sort field, DESC means descending order, ASC means ascending order. If you don't write this key word, default ascending order is arranged.
  Note: If the value of the first field is the same, sort it by the next field, and so on. If there is only one field, the records with the same field will not need to be arranged.
• polymerization
  In many cases, users need to do some summary work, such as counting the number of companies or the number of people in each department, when the SQL aggregation operation is used.
  The grammar is as follows:
  SELECT [field1, field2, ...fieldn] fun_name
  FROM tablename
  [WHERE CONTITION]
  [GROUP BY field1, field2, fieldn]
  [WITH ROLLUP]
  [HAVING CONTITION]
  Explain:

Create a new table

mysql> create table emp(
    -> id int primary key auto_increment, 
    -> hiredate date, 
    -> sal int, 
    -> deptno int
    -> );
Query OK, 0 rows affected (0.22 sec)

mysql> desc emp;
+----------+---------+------+-----+---------+----------------+
| Field    | Type    | Null | Key | Default | Extra          |
+----------+---------+------+-----+---------+----------------+
| id       | int(11) | NO   | PRI | NULL    | auto_increment |
| hiredate | date    | YES  |     | NULL    |                |
| sal      | int(11) | YES  |     | NULL    |                |
| deptno   | int(11) | YES  |     | NULL    |                |
+----------+---------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

Insert several records

mysql> insert into emp
    -> (hiredate, sal, deptno)
    -> values
    -> ('2001-1-11', 2000, 1), 
    -> ('2003-3-23', 1600, 2), 
    -> ('2015-1-30', 5000, 3),
    -> ('2017-8-22', 10000, 2),
    -> ('2017-2-22', 14000, 2),
    -> ('2016-7-22', 5000, 3);
Query OK, 6 rows affected (0.04 sec)

mysql> insert into emp
    -> (sal, deptno)
    -> values
    -> (20000, 3);
Query OK, 1 row affected (0.03 sec)

mysql> select * from emp;
+----+------------+-------+--------+
| id | hiredate   | sal   | deptno |
+----+------------+-------+--------+
|  1 | 2001-01-11 |  2000 |      1 |
|  2 | 2003-03-23 |  1600 |      2 |
|  3 | 2015-01-30 |  5000 |      3 |
|  4 | 2017-08-22 | 10000 |      2 |
|  5 | 2017-02-22 | 14000 |      2 |
|  6 | 2016-07-22 |  5000 |      3 |
|  7 | NULL       | 20000 |      3 |
+----+------------+-------+--------+
7 rows in set (0.00 sec)

Statistics the number of people in the whole company

mysql> select count(*) from emp;
+----------+
| count(*) |
+----------+
|        7 |
+----------+
1 row in set (0.00 sec)
Look at the number of departments

mysql> select deptno, count(*) from emp group by deptno;
+--------+----------+
| deptno | count(*) |
+--------+----------+
|      1 |        1 |
|      2 |        3 |
|      3 |        3 |
+--------+----------+
3 rows in set (0.00 sec)

We should not only make statistics for various departments, but also summarize them.

mysql> select deptno, count(*) from emp group by deptno with rollup;
+--------+----------+
| deptno | count(*) |
+--------+----------+
|      1 |        1 |
|      2 |        3 |
|      3 |        3 |
|   NULL |        7 |
+--------+----------+
4 rows in set (0.00 sec)

Refiltering Statistical Results

mysql> select deptno, count(*) from emp group by deptno having count(*)>=3;
+--------+----------+
| deptno | count(*) |
+--------+----------+
|      2 |        3 |
|      3 |        3 |
+--------+----------+

Statistical salary

mysql> select sum(sal), max(sal), min(sal) from emp;
+----------+----------+----------+
| sum(sal) | max(sal) | min(sal) |
+----------+----------+----------+
|    57600 |    20000 |     1600 |
+----------+----------+----------+

• Table connection
  Table joins are used when fields of multiple tables need to be displayed simultaneously. Table joins are divided into internal and external links. The inner join shows only the matching records in the two tables, while the outer join selects the other mismatching records.
  Create a table of department codes and department names

mysql> create table dept(
    -> deptno int, 
    -> deptname varchar(10));
Query OK, 0 rows affected (0.23 sec)

mysql> insert into dept
    -> values
    -> (1, 'tech'),
    -> (2, 'sale'), 
    -> (3, 'hr');
Query OK, 3 rows affected (0.05 sec)

Before, we could only query the relationship between employees and department names, but now we need to query the relationship between employees and department names.

mysql> select id, deptname from emp, dept where emp.deptno=dept.deptno;
+----+----------+
| id | deptname |
+----+----------+
|  1 | tech     |
|  2 | sale     |
|  3 | hr       |
|  4 | sale     |
|  5 | sale     |
|  6 | hr       |
|  7 | hr       |
+----+----------+
//Here deptno is used as a matching bridge

External connection is divided into left connection and right connection.
Left join: Contains all records in the left table or even records that do not match it in the right table
Right join: Contains all records in the right table, even those that do not match it in the left table
Note: The main difference between an external connection and an internal connection is that it can show records that are not matched, such as the presence of an employee in Department 4.

• Subqueries,
  In some cases, when querying, the condition is the result of another select statement. At this time, sub-queries are used. The main keywords of sub-queries are in, not in, exists, not exists, etc.

mysql> insert into emp
    -> (sal, deptno)
    -> values
    -> (12000, 4);
Query OK, 1 row affected (0.04 sec)

mysql> select * from emp where deptno in(select deptno from dept);
+----+------------+-------+--------+
| id | hiredate   | sal   | deptno |
+----+------------+-------+--------+
|  1 | 2001-01-11 |  2000 |      1 |
|  2 | 2003-03-23 |  1600 |      2 |
|  3 | 2015-01-30 |  5000 |      3 |
|  4 | 2017-08-22 | 10000 |      2 |
|  5 | 2017-02-22 | 14000 |      2 |
|  6 | 2016-07-22 |  5000 |      3 |
|  7 | NULL       | 20000 |      3 |
+----+------------+-------+--------+
7 rows in set (0.00 sec)

• Joint Records
  When querying two tables, it is necessary to use union and union all keywords to merge the results of the two tables.

mysql> select sal from emp
    -> union all
    -> select deptname from dept;
+-------+
| sal   |
+-------+
| 2000  |
| 1600  |
| 5000  |
| 10000 |
| 14000 |
| 5000  |
| 20000 |
| 12000 |
| tech  |
| sale  |
| hr    |
+-------+

Note: The main difference between UNION and UNION ALL is that UNION filters out duplicate data.

DCL statement

It is mainly used by database administrators for the privileges of objects in management systems, but seldom used by general developers.

Use of Help

• When you don't know what help can provide, you can use "?contents"

mysql> ? contents
You asked for help about help category: "Contents"
For more information, type 'help <item>', where <item> is one of the following
categories:
   Account Management
   Administration
   Compound Statements
   Data Definition
   Data Manipulation
   Data Types
   Functions
   Functions and Modifiers for Use with GROUP BY
   Geographic Features
   Help Metadata
   Language Structure
   Plugins
   Procedures
   Storage Engines
   Table Maintenance
   Transactions
   User-Defined Functions
   Utility

• Use "? Category Name" to further view content of interest
• If you want to quickly access a grammar, you can use "?" grammar, such as:?? create table