Create and manage tables

1, Basic knowledge

1.1. A data storage procedure

the first step in processing data when storing data. Only by storing the data correctly can we process and analyze it effectively. In mysql, a complete data storage process has four steps: creating a database, confirming fields, creating a data table, and inserting data. 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.

1.2 naming rules of identifiers

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
Object names such as database name, table name and field name cannot contain spaces
In the same MySQL software, the database name 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 field names do not conflict with keywords and reserved words, database systems, or common methods
- If you insist on using it, please use the emphasis sign (`) in the SQL statement
Keep field names and types consistent:
- Be sure to ensure consistency when naming fields and specifying data types for them

1.4 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. Create database

-- Method 1: create database
CREATE DATABASE Database name; 
-- Method 2: create database and specify character set
CREATE DATABASE Database name CHARACTER SET character set;
-- Method 3: judge whether the database already exists. If it does not exist, create the database(`recommend`)，If it exists, it is not created
CREATE DATABASE IF NOT EXISTS Database name;

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.

example:

CREATE DATABASE IF NOT EXISTS template CHARACTER SET utf8;

2.2. Using database

-- View all current databases
SHOW DATABASES;
-- View the database currently in use
SELECT DATABASE(); 
-- View all tables under the specified library
SHOW TABLES FROM Database name;
-- View database creation information
SHOW CREATE DATABASE Database name;
-- use/Switch database
USE Database name;
-- Change database character set
ALTER DATABASE Database name CHARACTER SET character set;
-- Method 1: delete the specified database
DROP DATABASE [IF EXISTS] 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.

example:

-- View all current databases
SHOW DATABASES;
-- View the database currently in use
SELECT DATABASE(); 
-- View all tables under the specified library
SHOW TABLES FROM template;
-- View database creation information
SHOW CREATE DATABASE template;
-- use/Switch database
USE template;
-- Change database character set
ALTER DATABASE template CHARACTER SET utf8;
-- Deletes the specified database
DROP DATABASE IF EXISTS template;

3, Create table

3.1. How to create a table

Mode 1

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.

example:

CREATE TABLE IF NOT EXISTS emp(
    id INT,
    emp_name VARCHAR(15),
    hire_date DATE
);

Method 2: create a new table based on the existing table

CREATE TABLE Table name 2
AS
SELECT Field name FROM Table name 1;

example:

CREATE TABLE emp1
AS 
SELECT id,emp_name,hire_date
FROM emp;

3.2. View data table structure

-- View data table structure
DESC/DESCRIBE Table name;
SHOW CREATE TABLE Table name;

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.

example:

DESC emp;
SHOW CREATE TABLE emp;

4, Modify 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
- You can modify the data type of the column, such as length, default value, position, constraint, etc
- Changes to the default value only affect future changes to the table
Delete columns from an existing table
Rename a column in an existing table

-- Append a column: it is added to the position of the last field in the table by default
ALTER TABLE Table name ADD [COLUMN] Field name field type [FIRST|AFTER Field name];
-- Modify a column
ALTER TABLE Table name MODIFY [COLUMN] Field name 1 field type [DEFAULT Default value][FIRST|AFTER Field name 2];
-- Rename a column
ALTER TABLE Table name CHANGE [column] Column name new column name new data type;
-- Delete a column
ALTER TABLE Table name DROP [COLUMN]Field name;
-- rename table 
RENAME TABLE Old table name TO New table name;
ALTER table Old table name RENAME [TO] New table name;

example

-- Append a field: it is added to the position of the last field in the table by default
ALTER TABLE emp ADD salary DOUBLE(10,2);
ALTER TABLE emp ADD phone_number VARCHAR(20) FIRST;
ALTER TABLE emp ADD email VARCHAR(45) AFTER emp_name;

-- Modify a field
ALTER TABLE emp MODIFY emp_name VARCHAR(25);
ALTER TABLE emp MODIFY emp_name VARCHAR(35) DEFAULT 'sakura';

-- Rename a field
ALTER TABLE emp CHANGE salary montyly_salary DOUBLE(10,2);
ALTER TABLE emp CHANGE email my_email VARCHAR(50);

-- Delete a field
ALTER TABLE emp DROP COLUMN my_email;

-- Duplicate name list
RENAME TABLE emp TO emp2;
ALTER TABLE emp2 RENAME TO myemp;

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
DROP TABLE statement cannot be rolled back

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.

***sql
--Delete table
DROP TABLE IF EXISTS emp1;

7, Empty table

TRUNCATE TABLE statement:
- Delete all data in the table
- Free up storage space for tables
TRUNCATE statement cannot be rolled back, but DELETE statement can be used to DELETE data

TRUNCATE TABLE Table name;

example

-- Copy table
CREATE TABLE employee_copy 
AS 
SELECT * FROM atguigudb.employees;

SELECT * FROM employee_copy;

-- Empty table
TRUNCATE TABLE employee_copy;

SELECT * FROM employee_copy;

7.1 COMMIT and ROLLBACK in DCL

COMMIT: submit data
- Once COMMIT is executed, the data will be 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 to the latest COMMIT;

7.2 compare TRUNCATE TABLE and DELETE FROM

Similarities:
- All data in the table can be deleted while preserving the structure of the table.
difference:
- TRUNCATE TABLE:
  - Once this operation is performed, all table data is cleared. At the same time, data cannot be rolled back.
- DELETE FROM:
  - Once this operation is performed, all table data can be cleared (without where).
  - At the same time, the data can be rolled back.

7.3 description of DDL and DML

Once a DDL operation is executed, it cannot be rolled back;
- After the DDL operation, a COMMIT must be executed. This COMMIT operation is not affected by SET autocommit = FALSE;
By default, DML operations cannot be rolled back once executed;
- If SET autocommit = FALSE is executed before executing DML, the executed DML operation can be rolled back;

-- Copy table
CREATE TABLE employees_copy
AS
SELECT * FROM atguigudb.employees;

-- First
COMMIT;
SELECT * FROM employees_copy;
-- Turn off auto submit
SET autocommit = FALSE;
-- Delete table data
DELETE FROM employees_copy;
SELECT * FROM employees_copy;
-- Undo Data 
ROLLBACK;
SELECT * FROM employees_copy;

-- First
COMMIT;
-- Turn off auto submit
SET autocommit = FALSE;
-- Delete table data
TRUNCATE TABLE employees_copy;
SELECT * FROM employees_copy;
-- Undo Data 
ROLLBACK;
SELECT * FROM employees_copy;

8, Exercises

-- 1. Create database test01_office,Indicates that the character set is utf8. And perform the following operations under this database 
CREATE DATABASE IF NOT EXISTS test01_office DEFAULT CHARACTER SET 'utf8';

-- 2. Create table dept01 
/*
	Field type 
	id INT(7) 
	NAME VARCHAR(25) 
*/
CREATE TABLE IF NOT EXISTS dept01(
	id INT(7),
	NAME VARCHAR(25)
);

-- 3. Will table departments Insert new table with data from dept02 in 
CREATE TABLE IF NOT EXISTS dept02
AS 
SELECT * FROM atguigudb.departments;

-- 4. Create table emp01 
/*
	Field 		 type 
	id 		INT(7)
	first_name 	VARCHAR (25) 
	last_name 	VARCHAR(25) 
	dept_id 	INT(7) 
*/
CREATE TABLE IF NOT EXISTS emp01 (
	id INT(7),
	first_name VARCHAR(25),
	last_name VARCHAR(25),
	dept_id INT(7)
);

-- 5. Column last_name The length of the is increased to 50 
ALTER TABLE emp01 MODIFY last_name VARCHAR(50);

-- 6. According to table employees establish emp02 
CREATE TABLE IF NOT EXISTS emp02
AS
SELECT * FROM atguigudb.employees;

-- 7. Delete table emp01 
DROP TABLE emp01;

-- 8. Will table emp02 Rename to emp01 
-- alter table emp02 rename to emp01;
RENAME TABLE emp02 TO emp01;

-- 9.In table dept02 and emp01 Add a new column to the test_column，And check the operation 
ALTER TABLE dept02 ADD COLUMN test_column VARCHAR(10);
ALTER TABLE emp01 ADD test_column VARCHAR(10);

-- 10.Delete table directly emp01 Columns in department_id
ALTER TABLE emp01 DROP COLUMN department_id;

-- 11,Create database test02_market 
CREATE DATABASE IF NOT EXISTS test02_market CHARACTER SET 'utf8';

-- 12,Create data table customers
/*
	Field name 		 data type
	c_num		int 
	c_name		varchar(50) 
	c_contact	varchar(50) 
	c_city 		varchar(50) 
	c_birth 	date
*/
CREATE TABLE IF NOT EXISTS customers (
	c_num INT,
	c_name VARCHAR(50),
	c_contact VARCHAR(50),
	c_city VARCHAR(50),
	c_birth DATE
);

-- 13,take c_contact Move field to c_birth After field 
ALTER TABLE customers MODIFY c_contact VARCHAR(50) AFTER c_birth;

-- 14,take c_name Field data type changed to varchar(70) 
ALTER TABLE customers MODIFY c_name VARCHAR(70);

-- 15,take c_contact Rename the field to c_phone 
ALTER TABLE customers CHANGE c_contact c_phone VARCHAR(50);

-- 16,increase c_gender Field to c_name Later, the data type is char(1) 
ALTER TABLE customers ADD c_gender CHAR(1) AFTER c_name;

-- 17,Change table name to customers_info
RENAME TABLE customers TO customers_info;

-- 18,Delete field c_city
ALTER TABLE customers_info DROP COLUMN c_city;

-- 19,Create database test03_company 
CREATE DATABASE IF NOT EXISTS test03_company CHARACTER SET 'utf8';

-- 20,Create table offices
/*
	Field name 		 data type
	officeCode	int 
	city 		varchar(30) 
	address 	varchar(50) 
	country 	varchar(50) 
	postalCode 	varchar(25)
*/
CREATE TABLE IF NOT EXISTS offices (
	officeCode INT,
	city VARCHAR(30),
	address VARCHAR(50),
	country VARCHAR(50),
	postalCode VARCHAR(25)
);

-- 21,Create table employees
/*
	Field name 		 data type
	empNum		int 
	lastName	varchar(50) 
	firstName 	varchar(50) 
	mobile 		varchar(25) 
	code 		int 
	jobTitle 	varchar(50) 
	birth 		date 
	note 		varchar(255) 
	sex 		varchar(5)
*/
CREATE TABLE IF NOT EXISTS employees (
	empNum INT,
	lastName VARCHAR(50),
	first_name VARCHAR(50),
	mobile VARCHAR(25),
	CODE INT,
	jobTitle VARCHAR(50),
	birth DATE,
	note VARCHAR(255),
	sex VARCHAR(5)
);

-- 22,Will table employees of mobile Field modified to code After field 
ALTER TABLE employees MODIFY mobile VARCHAR(25) AFTER CODE;

-- 23,Will table employees of birth Rename the field to birthday 
ALTER TABLE employees CHANGE birth birthday DATE;

-- 24,modify sex Field, data type is char(1) 
ALTER TABLE employees MODIFY sex CHAR(1);

-- 25,Delete field note 
ALTER TABLE employees DROP COLUMN note;

-- 26,Add field name favoriate_activity，Data type is varchar(100) 
ALTER TABLE employees ADD favoriate_activity VARCHAR(100);

-- 27,Will table employees The name of the is changed to employees_info
RENAME TABLE employees TO employees_info;

Keywords: MySQL SQL

Added by jwaqa1 on Tue, 25 Jan 2022 06:22:49 +0200

Programming VIP

Create and manage tables

1, Basic knowledge

1.1. A data storage procedure

1.2 naming rules of identifiers

1.4 data types in MySQL

2, Create and manage databases

2.1. Create database

2.2. Using database

3, Create table

3.1. How to create a table

3.2. View data table structure

4, Modify table

6, Delete table

7, Empty table

7.1 COMMIT and ROLLBACK in DCL

7.2 compare TRUNCATE TABLE and DELETE FROM

7.3 description of DDL and DML

8, Exercises

Popular Keywords