Database management

Creating a database

  • Grammar:
CREATE DATABASE [IF NOT EXISTS] < database name > [[DEFAULT] CHARACTER SET< character set name >] [[DEFAULT] COLLATE< proofread rule name >];Copy the code
  • []The content is optional
  • < database name > : name of the database to be created. The MySQL data store represents the MySQL database in the form of a directory. Therefore, the database name must comply with the folder naming rules of the OPERATING system and must not start with a number.
  • IF NOT EXISTS: Determines before creating a database. An operation can only be performed IF the database does NOT currently exist. This option can be used to avoid duplicate creation of a database that already exists.
  • [DEFAULT] CHARACTER SET: Specifies the CHARACTER SET of the database. The purpose of specifying a character set is to avoid garbled characters in the data stored in the database. If you do not specify a character set when creating the database, the system’s default character set is used.
  • [DEFAULT] COLLATE: specifies the DEFAULT collation rule for the character set.
  • The CHARACTER set and COLLATION rules of MySQL are two different concepts. Character sets are used to define how MySQL stores strings, and collation rules define how strings are compared.

Modifying a Database

  • Grammar:
ALTER DATABASE[database name] {[DEFAULT ] CHARACTER SETThe < character set name > | [DEFAULT ] COLLATE< proofread rule name >}Copy the code
  • ALTER DATABASE is used to change the global characteristics of a DATABASE.
  • To use ALTER DATABASE, you need to obtain DATABASE ALTER permissions.
  • The database name can be ignored and the statement corresponds to the default database.
  • The CHARACTER SET clause is used to change the default database CHARACTER SET.

Deleting a Database

  • Grammar:
DROP DATABASE [ IF EXISTS] < database name >Copy the code
  • < database name > : Specifies the name of the database to be deleted.
  • IF EXISTS: Prevents an error IF the database does not exist.
  • DROP DATABASE: DROP all tables in the DATABASE and DROP the DATABASE simultaneously.
  • To use DROP DATABASE, you need to have the DATABASE DROP permission.

Table management

Normalization of relational databases

Good database design is manifested in the following aspects:

  • High access efficiency
  • Reduces data redundancy and saves storage space for further expansion
  • Can make application development easier

The normalization theory of relational database is: each relation in relational database must meet certain norms. According to the different conditions to meet the specification, it can be divided into six levels: first normal form (1NF), second normal form (2NF)…… Fifth normal form (5NF). NF is the abbreviation of Normal Form. In general, just standardizing the data to the third normal form standard is enough.

First Normal Form (1NF)

  • In a relationship, duplicate fields are eliminated and each field is the smallest logical unit of storage. That is, atomicity.
  • The first paradigm is the basis of the second and third paradigms and is the most basic paradigm. The first paradigm includes the following guidelines. (1) Each attribute of a data group can contain only one value. (2) Each array in the relationship must contain the same number of values. (3) Each array in the relationship must not be the same.
  • In any relational database, the first normal form is the basic requirement of relational schema, and a database that does not meet the first normal form is not a relational database.

Second normal Form (2NF)

  • The second normal form is established on the basis of the first normal form, that is to satisfy the second normal form must first satisfy the first normal form (1NF).
  • The second normal form requires that each entity in a database table (that is, each record row) must be uniquely regionable.
  • To differentiate each row, you usually need to set up a “differentiate column” for the table to store the unique identity of each entity. This unique attribute column is called the primary key or primary key.
  • The second normal form requires that the attributes of the entity completely depend on the master key, that is, there can be no attributes that only depend on part of the master key. If there are, then the attributes and the part of the master key should be separated to form a new entity, and the relationship between the new entity and the original entity is one-to-many.

Third normal Form (3NF)

  • The third normal form is established on the basis of the second normal form, namely to satisfy the third normal form must first satisfy the second normal form.
  • The third normal form requires that relational tables have no transfer function dependence of non-keyword columns on any candidate keyword columns. That is, the third normal Form requires that a relational table does not contain non-primary keyword information that is already contained in other tables.
  • Fields other than the primary key must depend on the primary key.

Table management statement

Create a table

  • Grammar:
CREATE TABLETable name (column name1Data type [(length) constraint], column name2Data type [(length) constraint], column name3Data type [(length) constraint],....)Copy the code
  • Common data types
The data type describe
tinyint(m) 1 byte range (-128 to 127)
smallint(m) 2 bytes range (-32768 to 32767)
mediumint(m) 3-byte range (-8388608 to 8388607)
int(m) 4-byte range (-2147483648 to 2147483647)
bigint(m) 8-byte range (+-9.22*10 ^ 18)
float(m,d) Single precision floating point type 8 bit precision (4 bytes) m total, D decimal
double(m,d) Double precision floating point type 16 bit precision (8 bytes) m total, D decimal
decimal(m,d) M represents the total number of decimal digits and D represents the number of digits behind the decimal point. Often used of money.
char(n) A fixed length of up to 255 characters
varchar(n) The value contains a maximum of 65535 characters
tinytext The variable length contains a maximum of 255 characters
text The value contains a maximum of 65535 characters
mediumtext Variable length, up to 2 ^ 24 -1 characters
longtext Variable length, up to 2 ^ 32 -1 characters
date The date ‘2008-12-2’
time Time ’12:25:36′
datetime Date & Time ‘2008-12-2 22:06:44’
timestamp Automatically store the modification time
Enum (Option 1, Option 2…) Radio string data type suitable for storing “radio values” in the form interface
Set (Option 1, option 2…) Multiple select string data type suitable for storing multiple select values in the form interface.
  • Example:
Create database mydb
mysql> create database mydb default charset utf8mb4;
Query OK, 1 row affected (0.00 sec)

mysql> use mydb;
Database changed

Create a department table
mysql> create table departments (
    ->   id int,
    ->   dept_name varchar(20) - >); Query OK, 0 rows affected (0.01sec)Copy the code

Modify the table

Modify the column name
  • Grammar:
ALTER TABLE 表
CHANGE [COLUMN] List data typeCopy the code
  • Example:
mysql> alter table departments
    -> change id dept_id int;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0
Copy the code
Modify the type or constraint of the column
  • Grammar:
ALTER TABLE 表
MODIFY [COLUMN] Column name typeCopy the code
  • Example:
mysql> alter table departments
    -> modify dept_name varchar(10);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
Copy the code
Add a new column
  • Grammar:
ALTER TABLE 表
ADD [COLUMN] Column name typeCopy the code
  • Example:
mysql> alter table departments
    -> add manager_id int;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
Copy the code
Delete the column
  • Grammar:
ALTER TABLE 表
DROP [COLUMN] column namesCopy the code
  • Example:
mysql> alter table departments
    -> drop manager_id;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0
Copy the code
Modify the name of the table
  • Grammar:
ALTER TABLEThe name of the tableRENAME TOThe new name of the tableCopy the code
  • Example:
mysql> alter table departments    -> rename todepts; Query OK, 0 rows affected (0.00 SEC)Copy the code

Delete table

  • Grammar:
DROP TABLE [IF EXISTS] the name of the tableCopy the code
  • Example:
mysql> drop table depts;Query OK, 0 rows affected (0.01 sec)
Copy the code

Table to copy

Only the table structure is copied
  • Grammar:
CREATE TABLEName of the table to be createdLIKEFor the name of the tableCopy the code
  • Example:
mysql> create table departments like nsd2021.departments;Query OK, 0 rows affected (0.01 sec)
Copy the code
Copy the table structure and data
  • Grammar:
CREATE TABLEName of the table to be createdSELECTFields,...FROMFor the name of the tableCopy the code
  • Example:
mysql> create table departments2    -> select * fromnsd2021.departments; Query OK, 13 rows Affected (0.01 SEC)Records: 13 Duplicates: 0 Warnings: 0Copy the code

The constraint

  • A constraint is used to restrict data in a table to ensure the accuracy and reliability of the data in the table.
  • You can add constraints when creating a table
  • You can add constraints when modifying tables

Constraint classification

  • PRIMARY KEY: The PRIMARY KEY used to ensure that the value of this field is unique and non-null.
  • NOT NULL: Indicates that the value of the field cannot be NULL.
  • DEFAULT: indicates the DEFAULT value to ensure that the field has a DEFAULT value.
  • UNIQUE: ensures that the value of this field is UNIQUE and can be null.
  • FOREIGN KEY: a FOREIGN KEY used to restrict the relationship between two tables. It is used to ensure that the value of the field must come from the value of the associated column of the primary table. It is used to add FOREIGN KEY constraints on the secondary table to reference certain values in the primary table.

Constraints can be applied at the column level or table level. All constraints in the list are supported, but the foreign key constraint has no effect. Table level constraints can support primary key, unique, and foreign key constraints.

Constraints applied

Column level application

  • Use constraints when creating tables
mysql> create table employees(    ->   employee_id int primary key.Gender enum(' male ', 'female '), -> Email varchar(20) unique, -- unique constraint -- > nation varchar(10) default 'han' -- default constraint -- >); Query OK, 0 rows affected (0.00 SEC)
Copy the code

Table level constraints

  • Use constraints when creating tables
mysql> create table employees2 (    ->   employee_id int- >name varchar(20),    ->   email varchar(20),    ->   dept_id int,    ->   primary key (employee_id),   Foreign key (dept_id) references departments(dept_id) -- >); Mysql > select * from information_schema. Table_constraints where table_name='employees2' \G
Copy the code
  • Custom constraint name
mysql> create table employees3 (    ->   employee_id int- >name varchar(20),    ->   dept_id int- >constraint pk primary key(employee_id),   # > Constraint Fk_employees3_DEPARTMENTS FOREIGN key(dept_id) references departments(dept_id) ->);
Copy the code

Remove the constraint

  • Grammar:
ALTER TABLEThe < table >DROP FOREIGN KEY< foreign key constraint name >Copy the code
  • Example:
mysql> alter table employees3    -> drop foreign key fk_employees3_departments;
Copy the code

Example: Create three tables for an employee database

Create table departments(dept_id int AUTO_INCREMENT PRIMARY KEY, dept_name VARCHAR(10) UNIQUE); Create table employees(employee_id INT auto_increment primary key, name VARCHAR(10) not null, hire_date DATE, birth_date DATE, email varchar(25) UNIQUE, phone_number varchar(11), dept_id int, FOREIGN KEY(dept_id) references departments(dept_id)); Create table salary(id int AUTO_INCREMENT PRIMARY KEY, date date, employee_id int, basic int, bonus int, FOREIGN KEY(employee_id) references employees(employee_id))
Copy the code