MySQL Database Summary

Introduction to Database

  Database (DB) is a warehouse that organizes, stores and manages data in accordance with the data structure.

  Typical characteristics: data structure, data sharing, reduction of data redundancy, and data independence.

   Relational database: use relational model to organize data into data tables (table). The real world can be described by data.

  Mainstream relational database products: Oracle (Oracle), DB2 (IBM), SQL Server (MS), MySQL (Oracle).

  Data table: The data table is the basic storage structure of a relational database. A two-dimensional data table consists of rows (Row) and columns (Column), also called records (rows) and fields (columns).

Sql statement

(1) Introduction to Sql statement

SQL (Structured Query Language): Structured Query Language.


SQL is a standard language used to perform data operations, retrieval and maintenance on relational databases. It can be used to query data, manipulate data, define data, and control data.

SQL can be divided into:

Data Definition Language (DDL): Data Definition Language


Data Manipulation Language (DML): Data Manipulation Language


Transaction Control Language (TCL): Transaction Control Language


Data Query Language (DQL): Data Query Language


Data Control Language (DCL): Data Control Language



(2) Data definition language DDLcreate, alter, drop 

--Data definition language DDL (create, alter, drop)

- 1. Database related DDL

- 1. Create a database
CREATE DATABASE mybase;
- 2. Create a database and specify the character set
CREATE DATABASE mybase1 CHARACTER SET UTF8;
- 3. View all databases
SHOW DATABASES;
- 4. View the currently used database
SELECT DATABASE();
- 5. Modify the database
ALTER DATABASE mybase CHARACTER SET UTF8;
- 6.Delete the database
DROP DATABASE mybase1;
- Switch database
USE mybase;

Table related DDL

- 1. Create a table
create table exam(
  id INT(11) PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(20),
  English INT,
  Chinese INT,
  Math int
);
- 2. View all tables in the database
show TABLES;
- 3. View table structure
desc exam;

- 4. Table deletion
drop table exam;

- 5. Table modification (based on exam table)

- 5.1 add column
ALTER TABLE exam ADD History INT NOT NULL;
- 5.2 Modify column type, length, constraint
ALTER TABLE exam MODIFY History DOUBLE(7,2);
- 5.3 Modify the column name of the table
ALTER TABLE exam CHANGE History Physics INT NOT NULL;
- 5.4 Modify the table name
RENAME TABLE exam TO score;
- 5.5 Modify the character set of the table
ALTER TABLE score CHARACTER SET GBK;
- 5.6 Delete column
ALTER TABLE score DROP Physics;
- 5.7

ALTER TABLE

- Three, exercise: create emp table

CREATE TABLE emp(
  empno INT PRIMARY KEY AUTO_INCREMENT,
  ename VARCHAR(20),
  job VARCHAR(20),
  mgr int,
  hiredate DATE,
  sal DOUBLE(7,2),
  commit double(5,2),
  deptno INT NOT NULL - non-null constraint
);
  

(3) Data manipulation language DMLupdate, insert, delete

- Data manipulation language DML (update, insert, delete)


- 1. Insert some columns
INSERT INTO score(id,NAME,English,Chinese,Math) VALUE(1,'Hudie',90,90,90);
INSERT INTO score(id,NAME,English,Chinese) VALUE(NULL,'diedie',91,91);
- 2. Insert all columns
INSERT INTO score VALUES(3,'Shu',80,80,80);
- 3. Modification records
UPDATE score set Chinese=99; --Full table modification
UPDATE score SET Math=100 WHERE id='1';
- 4. Delete records
DELETE FROM score WHERE id='2';
DELETE FROM score;

- The difference between delete and truncate √
--TRUNCATE TABLE delete table records: delete the entire table and re-create a new table, which belongs to DDL.
--DELETE FROM delete table records: delete one by one, DELETE.
INSERT INTO score VALUES(3,'Shu',80,80,80);
DELETE FROM score;
INSERT INTO score VALUES(NULL,'Libai',10,10,10); - will not clear the AUTO_INCREMENT value
TRUNCATE TABLE score;
INSERT INTO score VALUES(NULL,'Libai',10,10,10); --clear the value of AUTO_INCREMENT

- Transaction management: It can only be used on DML statements. If you use delete to delete all records in a transaction, you can retrieve them.
- After using delete, you can use COMMIT and ROLLBACK to retrieve the data, but after using truncate, you can't retrieve it.

- The difference between delete, truncate and drop √
delete, truncate, just delete the records of the table, and drop will delete the table directly.


(4) Data control language DCLgrant, revoke 

- Data control language DCL (grant, revoke)
- Mainly grant and revoke permissions for users

- 1. Create a user: CREATE USER username@ip IDENTIFIED BY password;
create user Fox@localhost identified by '123456';

- 2. Authorize the user: grrank authority 1, authority 2,..., authority n ON database name.* TO username@IP;
grant select,drop on mysql.* to Fox@localhost;
 
- 3. Revocation of authority: REVOKE authority 1, authority 2,..., authority n ON database name.* FROM user name@IP;
revoke select on mysql.* from Fox@localhost;

- 4. View user permissions: SHOW GRANTS FOR username@IPl
show grants for Fox@localhost;
- 5.Delete user: DROP USER username@IP;
drop user Fox@localhost;
- 6.Log in: mysql -u username -p password;
mysql -u root -p
- 7. Log out: exit;
exit;