Chapter 10_ Create and manage tables

Lecturer: Shang Silicon Valley - Song Hongkang (Jianghu person: Master Kang)

Official website: http://www.atguigu.com

1. Basic knowledge

1.1 a data storage process

Storing data is the first step in processing data. Only by storing the data correctly can we process and analyze it effectively. Otherwise, it can only be a mess and there is no way to start.

So, how can we store all kinds of complicated data related to users' business in an orderly and efficient manner? In MySQL, a complete data storage process has four steps: creating a database, confirming fields, creating a data table, and inserting data.

Should we first create a database instead of directly creating a data table?

From the perspective of system architecture, MySQL database system is followed by database server, database, data table, rows and columns of data table from large to small.

MySQL database server has been installed before. So let's start by creating a database.

1.2 identifier naming rules

• The database name and table name shall not exceed 30 characters, and the variable name shall be limited to 29 characters
• Must contain only A – Z, a – z, 0 – 9_ 63 characters in total
• Do not include spaces between object names such as database name, table name and field name
• In the same MySQL software, the database cannot have the same name; In the same library, tables cannot have the same name; Fields in the same table cannot have the same name
• You must ensure that your fields do not conflict with reserved words, database systems, or common methods. If you insist on using it, please use ` (emphasis sign) in the SQL statement
• Keep the consistency of field name and type: when naming a field and specifying its data type, be sure to ensure consistency. If the data type is an integer in one table, don't become a character in another table

1.3 data types in MySQL

type	Type example
Integer type	TINYINT, SMALLINT, MEDIUMINT, int (or INTEGER), BIGINT
Floating point type	FLOAT,DOUBLE
Fixed point number type	DECIMAL
Bit type	BIT
Date time type	YEAR,TIME,DATE,DATETIME,TIMESTAMP
Text string type	CHAR,VARCHAR,TINYTEXT,TEXT,MEDIUMTEXT,LONGTEXT
Enumeration type	ENUM
Collection type	SET
Binary string type	BINARY,VARBINARY,TINYBLOB,BLOB,MEDIUMBLOB,LONGBLOB
JSON type	JSON object, JSON array
Spatial data type	Single value: GEOMETRY, POINT, LINESTRING, POLYGON; Sets: MULTIPOINT, MULTILINESTRING, MULTIPOLYGON, GEOMETRYCOLLECTION

The common types are described as follows:

data type	describe
INT	Integer data from - 231 to 231-1. The storage size is 4 bytes
CHAR(size)	Fixed length character data. If not specified, the default is 1 character and the maximum length is 255
VARCHAR(size)	Variable length character data is saved according to the actual length of the string, and the length must be specified
FLOAT(M,D)	Single precision, occupying 4 bytes, M = integer bits + decimal places, d = decimal places. D < = m < = 255,0 < = d < = 30, default m + d < = 6
DOUBLE(M,D)	Double precision, 8 bytes, d < = m < = 255,0 < = d < = 30, default m + d < = 15
DECIMAL(M,D)	High precision decimal, occupying M+2 bytes, d < = m < = 65, 0 < = d < = 30, and the maximum value range is the same as DOUBLE.
DATE	Date data, format 'YYYY-MM-DD'
BLOB	Long text data in binary form, up to 4G
TEXT	Long text data, up to 4G

2. Create and manage databases

2.1 creating database

• Method 1: create database

CREATE DATABASE Database name; 
Example:
CREATE DATABASE mytest1;  # This database was created using the default character set mysql8 0 defaults to utf8, mysql5 7 if it is not modified, it defaults to Latin

• Method 2: create database and specify character set

CREATE DATABASE Database name CHARACTER SET character set;
#Explicitly specifies the character set of the database to be created
CREATE DATABASE mytest2 CHARACTER SET 'gbk';

• Method 3: judge whether the database already exists. If it does not exist, create the database (recommended)

CREATE DATABASE IF NOT EXISTS Database name; 
#(recommended): if the database to be created already exists, the creation is unsuccessful, but no error will be reported.
CREATE DATABASE IF NOT EXISTS mytest2 CHARACTER SET 'utf8';

#If the database to be created does not exist, the creation is successful
CREATE DATABASE IF NOT EXISTS mytest3 CHARACTER SET 'utf8';

If a related database already exists in MySQL, the create statement will be ignored and the database will not be created.

Note: DATABASE cannot be renamed. Some visualization tools can be renamed. It is completed by building a new library, copying all tables to the new library, and then deleting the old library.

2.2 using database

• View all current databases

SHOW DATABASES; #There is an S that represents multiple databases

• View the database currently in use

SELECT DATABASE();  #Use a global function in mysql
 Equivalent to:
SELECT DATABASE() FROM DUAL;#View the database currently in use

• View all tables under the specified library

SHOW TABLES;#View the data table saved in the current database. If it is in the current database, it can be omitted after from.

SHOW TABLES FROM Database name;#View the data table saved under the specified database

• View database creation information

SHOW CREATE DATABASE Database name;
Or:
SHOW CREATE DATABASE Database name\G

• Use / switch database

USE Database name;

Note: before operating tables and data, you must specify which database you are operating on, otherwise you must add "database name." to all objects.

2.3 modifying the database

• Change database character set

ALTER DATABASE Database name CHARACTER SET character set;  #For example: gbk, utf8, etc

2.4 delete database

• Method 1: delete the specified database

DROP DATABASE Database name;#If the database to be deleted exists, the deletion is successful. If it does not exist, an error is reported

• Method 2: delete the specified database (recommended)

DROP DATABASE IF EXISTS Database name;#If the database to be deleted exists, the deletion is successful. If it does not exist, it will end silently and no error will be reported.

3. Create table

3.1 creation method 1: "start from scratch"

• Must have:
  • CREATE TABLE permissions
  • storage space
• Syntax format:

CREATE TABLE [IF NOT EXISTS] Table name(
	Field 1, data type [constraint condition] [Default value],
	Field 2, data type [constraint condition] [Default value],
	Field 3, data type [constraint condition] [Default value],
	......
	[Table constraints]
);

IF NOT EXISTS keyword is added, it means: if there is no data table to be created in the current database, the data table will be created; If the data table to be created already exists in the current database, the table creation statement will be ignored and the data table will not be created.

• You must specify:

  • Table name
  • Column name (or field name), data type, length

• Optional designation:

  • constraint condition
  • Default value

• Create table example 1:

  #Method 1: "start from scratch"
  CREATE TABLE IF NOT EXISTS myemp1(   #Users are required to have permission to create tables.
  id INT,
  emp_name VARCHAR(15), #When using VARCHAR to define a string, its length must be indicated when using VARCHAR.
  hire_date DATE
  );
  
  #View table structure
  DESC myemp1;
  
  #View the structure of the statement that created the table
  SHOW CREATE TABLE myemp1; #If the character set used is not specified when creating the table, the character set of the database where the table is located is used by default,
                                                       If the database does not indicate, look at the character set of the database configuration file.
  #View table data
  SELECT * FROM myemp1;
  

• Create table example 2:

-- Create table
CREATE TABLE emp (
  -- int type
  emp_id INT,
  -- Save up to 20 Chinese and English characters
  emp_name VARCHAR(20),
  -- The total number of digits shall not exceed 15
  salary DOUBLE,
  -- Date type
  birthday DATE
);

DESC emp;#View table structure

When MySQL executes the table creation statement, set the type of the id field to int(11). Here, 11 is actually the display width specified by the int type. The default display width is 11. You can also specify the display width of data when creating a data table.

• Create table example 3:

CREATE TABLE dept(
    -- int Type, self increasing
	deptno INT(2) AUTO_INCREMENT,
	dname VARCHAR(14),
	loc VARCHAR(13),
    -- Primary key
    PRIMARY KEY (deptno)
);

DESCRIBE dept;

In MySQL 8 In the X version, specifying the display length for INT type is no longer recommended, and such syntax may be removed in future versions.

3.2 creation method 2: create on an existing table

• Use the AS subquery option to combine creating tables and inserting data

• The specified column should correspond to the column in the subquery one by one

• Define columns by column names and default values

CREATE TABLE emp1 
AS SELECT * FROM employees;

CREATE TABLE emp2
AS SELECT * FROM employees WHERE 1=2; -- Created emp2 Is an empty table

CREATE TABLE dept80
AS 
SELECT  employee_id, last_name, salary*12 ANNSAL, hire_date
FROM    employees
WHERE   department_id = 80;

DESCRIBE dept80;

#Method 2: import data at the same time based on existing tables
CREATE TABLE myemp2
AS
SELECT employee_id,last_name,salary
FROM employees;

DESC myemp2;
DESC employees;

SELECT *
FROM myemp2;

#Note 1: the alias of the field in the query statement can be used as the name of the field of the newly created table.
#Note 2: at this time, the query statements can have rich structures and use the various SELECT statements described in the previous chapter
CREATE TABLE myemp3
AS
SELECT e.employee_id emp_id,e.last_name lname,d.department_name
FROM employees e JOIN departments d
ON e.department_id = d.department_id;

SELECT *
FROM myemp3;

DESC myemp3;

#Exercise 1: create a table employees_copy to copy the employees table, including table data
CREATE TABLE employees_copy
AS
SELECT *
FROM employees;

SELECT * FROM employees_copy;

#Exercise 2: create a table employees_blank to copy the employees table, excluding the table data
CREATE TABLE employees_blank
AS
SELECT *
FROM employees
#where department_id > 10000;
WHERE 1 = 2; #The mountain has no mausoleum, the heaven and the earth are united, but I dare to break with you. 1 cannot be equal to 2, so it is impossible to query the result, that is, there is no data.


SELECT * FROM employees_blank;

3.3 view data table structure

After creating a data table in mysql, you can view the structure of the data table. MySQL supports using DESCRIBE/DESC statements to view the data table structure, and also supports using SHOW CREATE TABLE statements to view the data table structure.

The syntax format is as follows:

SHOW CREATE TABLE Table name\G

Use the SHOW CREATE TABLE statement to view not only the detailed statements when the table is created, but also the storage engine and character encoding.

4. Modification table

Modifying a table refers to modifying the structure of an existing data table in the database.

Use the ALTER TABLE statement to:

• Add columns to an existing table

• Modify columns in an existing table

• Delete columns from an existing table

• Rename a column in an existing table

4.1 add a column

The syntax format is as follows:

ALTER TABLE Table name ADD [COLUMN] Field name field type[ FIRST|AFTER [field name];

give an example:

ALTER TABLE dept80 
ADD job_id varchar(15);

ALTER TABLE myemp1
ADD salary DOUBLE(10,2); #The location of the last field added to the table by default

ALTER TABLE myemp1
ADD phone_number VARCHAR(20) FIRST;#The location of the first field added to the table

ALTER TABLE myemp1
ADD email VARCHAR(45) AFTER emp_name;#EMP added to table_ Position after name field

4.2 modifying a column

• You can modify the data type, length, default value and position of the column

• Modify the syntax format of field data type, length, default value and position as follows:

ALTER TABLE Table name MODIFY [COLUMN] Field name 1 field type[ DEFAULT [default][ FIRST|AFTER [field name 2];

• give an example:

ALTER TABLE	dept80
MODIFY last_name VARCHAR(30);

ALTER TABLE	dept80
MODIFY salary double(9,2) default 1000;

# Modify a field: data type, length and default value (omitted)
#Generally, the field length is modified, and the type is generally correct.
ALTER TABLE myemp1
MODIFY emp_name VARCHAR(25) ; #Modify length

ALTER TABLE myemp1
MODIFY emp_name VARCHAR(35) DEFAULT 'aaa'; #Modify default values

• Changes to the default value only affect future changes to the table
• In addition, you can modify column constraints in this way. Not here for the time being.

4.3 rename a column

Use CHANGE old_column new_column dataType clause renames a column. The syntax format is as follows:

ALTER TABLE Table name CHANGE [column] Column name new column name new data type;

give an example:

ALTER TABLE  dept80
CHANGE department_name dept_name varchar(15); 


ALTER TABLE myemp1
MODIFY emp_name VARCHAR(35) DEFAULT 'aaa';

#  Rename a field
ALTER TABLE myemp1
CHANGE salary monthly_salary DOUBLE(10,2);

ALTER TABLE myemp1
CHANGE email my_email VARCHAR(50);#Modify the scope while renaming

4.4 delete a column

The syntax format of deleting a field in the table is as follows:

ALTER TABLE Table name DROP [COLUMN]Field name

give an example:

ALTER TABLE  dept80
DROP COLUMN  job_id; 

# Delete a field
ALTER TABLE myemp1
DROP COLUMN my_email;

5. Rename table

• Method 1: use RENAME

RENAME TABLE emp
TO myemp;   #The original table name is preceded by the new table name

• Mode 2:

ALTER table dept #The original table name is preceded by the new table name
RENAME [TO] detail_dept;  -- [TO]Can be omitted

• Must be the owner of the object

6. Delete table

• In MySQL, when a data table is not associated with any other data table, the current data table can be deleted directly.

• Data and structures are deleted

• All running related transactions are committed

• All related indexes are deleted

• Syntax format:

DROP TABLE [IF EXISTS] Data sheet 1 [, Data sheet 2, ..., data sheet n];

IF EXISTS means that if there is a corresponding data table in the current database, the data table will be deleted; If the corresponding data table does not exist in the current database, the delete statement is ignored and the operation of deleting the data table is no longer performed.

• give an example:

DROP TABLE dept80;

# Delete table
#Not only the table structure is deleted, but also the data in the table is deleted to free up the table space
DROP TABLE IF EXISTS myemp2;

DROP TABLE IF EXISTS myemp12;

• DROP TABLE statement cannot be rolled back

7. Empty the table

• TRUNCATE TABLE statement:

  • Delete all data in the table
  • Free up storage space for tables

• give an example:

TRUNCATE TABLE detail_dept;

#Clearing the table means that all data in the table is cleared, but the table structure remains.

SELECT * FROM employees_copy;

TRUNCATE TABLE employees_copy;

SELECT * FROM employees_copy;

DESC employees_copy;

• TRUNCATE statement cannot be rolled back, but DELETE statement can be used to DELETE data

• contrast:

#7. COMMIT and ROLLBACK in DCL
# COMMIT: submit data. Once COMMIT is executed, the data is permanently saved in the database, which means that the data cannot be rolled back.
# ROLLBACK: ROLLBACK data. Once ROLLBACK is executed, the data can be rolled back. ROLLBACK to the latest COMMIT.

#8. Compare TRUNCATE TABLE and DELETE FROM 
# Same point: you can delete all data in the table while preserving the table structure.
# difference:
#	TRUNCATE TABLE: once this operation is performed, all table data will be cleared. At the same time, data cannot be rolled back.
#	DELETE FROM: once this operation is performed, all the table data can be cleared (without WHERE). At the same time, the data can be rolled back.

/*
9. DDL Description of and DML
  ① DDL Once the operation is executed, it cannot be rolled back. The instruction SET autocommit = FALSE is invalid for DDL operation. (because after DDL execution
    After the operation, a COMMIT must be performed. This COMMIT operation is not affected by SET autocommit = FALSE.)
  
  ② DML By default, once executed, the operation cannot be rolled back. However, if the DML is executed before the DML is executed 
    SET autocommit = FALSE，Then the executed DML operation can be rolled back.

*/
# Demo: DELETE FROM 
#1)
COMMIT;
#2)
SELECT *
FROM myemp3;
#3)
SET autocommit = FALSE;
#4)
DELETE FROM myemp3;
#5)
SELECT *
FROM myemp3;
#6)
ROLLBACK;
#7)
SELECT *
FROM myemp3;

# Presentation: TRUNCATE TABLE
#1)
COMMIT;
#2)
SELECT *
FROM myemp3;
#3)
SET autocommit = FALSE;
#4)
TRUNCATE TABLE myemp3;
#5)
SELECT *
FROM myemp3;
#6)
ROLLBACK;
#7)
SELECT *
FROM myemp3;


/*
DDL:Data definition language. CREATE \ ALTER \ DROP \ RENAME \ TRUNCATE


DML:Data manipulation language. INSERT \ DELETE \ UPDATE \ SELECT (Top priority)


DCL:Data control language. COMMIT \ ROLLBACK \ SAVEPOINT \ GRANT \ REVOKE

Alibaba development specification:

[reference] TRUNCATE TABLE is faster than DELETE and uses less system and transaction log resources. However, TRUNCATE has no transactions and does not TRIGGER trigger, which may cause accidents. Therefore, it is not recommended to use this statement in the development code.

Note: TRUNCATE TABLE is functionally the same as a DELETE statement without a WHERE clause.

8. Content expansion

Extension 1: MySQL field naming in Alibaba's Java Development Manual

• [mandatory] table names and field names must use lowercase letters or numbers. It is forbidden to start with numbers, and only numbers are allowed between two underscores. The modification of database field names is expensive, because pre publishing cannot be carried out, so field names need to be carefully considered.

  • Positive example: aliyun_admin，rdc_config，level3_name
  • Counterexample: AliyunAdmin, rdcConfig, level_3_name

• [mandatory] disable reserved words, such as desc, range, match, delayed, etc. Please refer to the official reserved words of MySQL.

• The [mandatory] table must have three fields: id, gmt_create, gmt_modified.

  • Note: the id must be the primary key, the type must be BIGINT UNSIGNED, auto increment in single table, and the step size must be 1. gmt_ create, gmt_ The types of modified are DATETIME. The former indicates active creation in the present tense, and the latter indicates passive update in the past participle

• [recommended] the naming of the table should preferably follow the "function of business name table".

  • Positive example: alipay_task , force_project, trade_config

• [recommended] the database name shall be consistent with the application name as far as possible.

• [reference] appropriate character storage length not only saves database table space and index storage, but also improves retrieval speed.

  • Positive example: unsigned values can avoid false storage of negative numbers and expand the representation range.

Expansion 2: how to understand that operations such as clearing tables and deleting tables need to be cautious?!

The table deletion operation will delete the table definition and the data in the table together, and MySQL will not be prompted with any confirmation information when performing the deletion operation. Therefore, you should be careful when performing the deletion operation. Before deleting a table, it is best to back up the data in the table, so that the data can be recovered in case of operation errors, so as not to cause irreparable consequences.

Similarly, when using ALTER TABLE for basic table modification, you should also ensure that the data is backed up completely before the operation process, because the database change cannot be undone. If an unnecessary field is added, it can be deleted; Similarly, if a required column is deleted, all data under the column will be lost.

Expansion 3: new feature of MySQL 8 - atomization of DDL

In MySQL version 8.0, the DDL of InnoDB table supports transaction integrity, that is, the DDL operation is either successful or rolled back. The DDL operation rollback log is written to the data dictionary table mysql innodb_ ddl_ Log (the table is hidden and cannot be seen through show tables), which is used to rollback operations. By setting parameters, the DDL operation log can be printed and output to the MySQL error log.

Create databases and data tables in MySQL version 5.7 and MySQL version 8.0 respectively. The results are as follows:

CREATE DATABASE mytest;

USE mytest;

CREATE TABLE book1(
book_id INT ,
book_name VARCHAR(255)
);

SHOW TABLES;

(1) In MySQL 5.7, the test steps are as follows:
Delete data table book1 and data table book2, and the results are as follows:

mysql> DROP TABLE book1,book2;
ERROR 1051 (42S02): Unknown table 'mytest.book2'

Query the data table name in the database again, and the results are as follows:

mysql> SHOW TABLES;
Empty set (0.00 sec)

It can be seen from the results that although the deletion operation is wrong, the data table book1 is still deleted.

(2) In MySQL version 8.0, the test steps are as follows:
Delete data table book1 and data table book2, and the results are as follows:

mysql> DROP TABLE book1,book2;
ERROR 1051 (42S02): Unknown table 'mytest.book2'

Query the data table name in the database again, and the results are as follows:

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

As can be seen from the results, the data table book1 has not been deleted.