MySQL命令(一)

目录

• 概览

• DDL

  • CREATE

  • ALTER

  • TRUNCATE

  • DROP

• DML

  • INSERT

  • SELECT

  • UPDATE

  • DELETE

• DCL

  • GRANT

  • REVOKE

• TCL

  • COMMIT

  • ROLLBACK

概览

SQL Commands

DDL

• DDL(Data definition language): it is used to define data structures

CREATE

CREATE TABLE employees_2 (
    emp_no      INT             NOT NULL,
    birth_date  DATE            NOT NULL,
    first_name  VARCHAR(14)     NOT NULL,
    last_name   VARCHAR(16)     NOT NULL,
    gender      ENUM ('M','F')  NOT NULL,    
    hire_date   DATE            NOT NULL,
    PRIMARY KEY (emp_no)
);

DESC employees_2;

ALTER

ALTER TABLE employees_2 ADD a TINYINT NOT NULL;

ALTER TABLE employees_2 MODIFY a INT NOT NULL;

ALTER TABLE employees_2 DROP a;

TRUNCATE

TRUNCATE TABLE employees_2;

DROP

DROP TABLE employees_2;

DML

• DML(Data Manipulation Language): it is used to manipulate data itself

INSERT

INSERT INTO employees VALUES (500000,'1958-05-01','Test','Tsukuda','M','1997-11-30');

SELECT

SELECT * FROM employees ORDER BY emp_no desc LIMIT 1;
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  |
+--------+------------+------------+-----------+--------+------------+
| 500000 | 1958-05-01 | Test       | Tsukuda   | M      | 1997-11-30 |
+--------+------------+------------+-----------+--------+------------+

UPDATE

UPDATE employees SET last_name = 'Test' WHERE first_name = 'Test'

DELETE

DELETE FROM employees WHERE first_name = 'Test'

SELECT * FROM employees ORDER BY emp_no desc LIMIT 1;
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  |
+--------+------------+------------+-----------+--------+------------+
| 499999 | 1958-05-01 | Sachin     | Tsukuda   | M      | 1997-11-30 |
+--------+------------+------------+-----------+--------+------------+

DCL

• DCL(Data Control Language): it is used to control access to data stored in a database (Authorization)

GRANT

GRANT SELECT ON employees.* TO guest@localhost IDENTIFIED BY '123456';

mycli -uguest -p123456

USE employees;

SELECT * FROM employees ORDER BY emp_no desc LIMIT 1;
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  |
+--------+------------+------------+-----------+--------+------------+
| 499999 | 1958-05-01 | Sachin     | Tsukuda   | M      | 1997-11-30 |
+--------+------------+------------+-----------+--------+------------+

INSERT INTO employees VALUES (500000,'1958-05-01','Test','Tsukuda','M','1997-11-30');
(1142, "INSERT command denied to user 'guest'@'localhost' for table 'employees'")

mycli -uroot -p123456

GRANT INSERT ON employees.* TO guest@localhost IDENTIFIED BY '123456';

mycli -uguest -p123456

USE employees;

INSERT INTO employees VALUES (500000,'1958-05-01','Test','Tsukuda','M','1997-11-30');

SELECT * FROM employees ORDER BY emp_no desc LIMIT 1;
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  |
+--------+------------+------------+-----------+--------+------------+
| 500000 | 1958-05-01 | Test       | Tsukuda   | M      | 1997-11-30 |
+--------+------------+------------+-----------+--------+------------+

REVOKE

mycli -uroot -p123456

REVOKE INSERT ON employees.* FROM guest@localhost;

mycli -uguest -p123456

USE employees;

INSERT INTO employees VALUES (500000,'1958-05-01','Test','Tsukuda','M','1997-11-30');
(1142, "INSERT command denied to user 'guest'@'localhost' for table 'employees'")

TCL

• TCL(Transaction Control Language): it is used to manage transactions in the database

COMMIT

START TRANSACTION;

UPDATE employees SET last_name = 'Test' WHERE first_name = 'Test'

SELECT * FROM employees ORDER BY emp_no desc LIMIT 1;
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  |
+--------+------------+------------+-----------+--------+------------+
| 500000 | 1958-05-01 | Test       | Test      | M      | 1997-11-30 |
+--------+------------+------------+-----------+--------+------------+

UPDATE employees SET gender = 'F' WHERE first_name = 'Test'

SELECT * FROM employees ORDER BY emp_no desc LIMIT 1;
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  |
+--------+------------+------------+-----------+--------+------------+
| 500000 | 1958-05-01 | Test       | Test      | F      | 1997-11-30 |
+--------+------------+------------+-----------+--------+------------+

COMMIT

ROLLBACK

ROLLBACK

SELECT * FROM employees ORDER BY emp_no desc LIMIT 1;
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  |
+--------+------------+------------+-----------+--------+------------+
| 500000 | 1958-05-01 | Test       | Test      | F      | 1997-11-30 |
+--------+------------+------------+-----------+--------+------------+

START TRANSACTION;

UPDATE employees SET hire_date = '2000-01-01' WHERE first_name = 'Test';

SELECT * FROM employees ORDER BY emp_no desc LIMIT 1;
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  |
+--------+------------+------------+-----------+--------+------------+
| 500000 | 1958-05-01 | Test       | Test      | F      | 2000-01-01 |
+--------+------------+------------+-----------+--------+------------+

ROLLBACK

SELECT * FROM employees ORDER BY emp_no desc LIMIT 1;
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  |
+--------+------------+------------+-----------+--------+------------+
| 500000 | 1958-05-01 | Test       | Test      | F      | 1997-11-30 |
+--------+------------+------------+-----------+--------+------------+

参考

• SQL | DDL, DML, TCL and DCL

• What is DDL and DML

• MySQL5.7 添加用户、删除用户与授权