“This is the 15th day of my participation in the Gwen Challenge in November. Check out the details: The Last Gwen Challenge in 2021.”

Database, data table, data relationship

SQL classification:

Data Definition Language (DDL): Data Definition Language. Used to manipulate databases, tables, columns, etc. Data Manipulation Language (DML): Data Manipulation Language. Used to add, delete and change the data of the table in the database. Data Query Language (DQL): Data Query Language. Used to query records (data) of tables in a database. Data Control Language (DCL): Data Control Language. Defines database access permissions and security levels, and creates users.Copy the code

DDL queries and creates databases

Query all DATABASES SHOW DATABASES; Query the DATABASE creation statement SHOW CREATE DATABASE DATABASE name; CREATE DATABASE CREATE DATABASE name; CREATE DATABASE IF NOT EXISTS CREATE a DATABASE name. CREATE DATABASE name CHARACTER SET CHARACTER SET name;Copy the code
SHOW DATABASES;

Copy the code
SHOW CREATE DATABASE mysql; CREATE DATABASE 'mysql' /*! 40100 DEFAULT CHARACTER SET utf8 */Copy the code
CREATE DATABASE IF NOT EXISTS db2;
Copy the code
CREATE DATABASE db3 CHARACTER SET utf8; SHOW CREATE DATABASE db3; CREATE DATABASE `db3` /*! 40100 DEFAULT CHARACTER SET utf8 */Copy the code
Db4 character set GBK Create database if not exists db4 character set GBK; SHOW CREATE DATABASE db4;Copy the code

DDL modifies, deletes, and uses the database

ALTER DATABASE DATABASE name CHARACTER SET CHARACTER SET name; DROP DATABASE DROP DATABASE name; DROP DATABASE IF EXISTS Indicates the DATABASE name. USE The database name. SELECT DATABASE();Copy the code
ALTER DATABASE db4 CHARACTER SET utf8;
SHOW CREATE DATABASE db4;
DROP DATABASE db2;
DROP DATABASE IF EXISTS db2;
USE db4;
SELECT DATABASE();
Copy the code

DDL queries data tables

Show tables; Query table structure desc table name; SQL > alter table character set show table status from db like 'db ';Copy the code
USE mysql;
show tables;
desc user;
show table status from mysql like 'user';
Copy the code

DDL creates data tables

Create table create table name (column name data type constraint, column name data type constraint,....... Column name data type constraint,);Copy the code

Data type:

-- Create a product list (product number, product name, product price, product inventory, shelf time) use db3 create table product( id int, NAME varchar(20), price double, stock int, insert_time DATE ); Desc product;Copy the code

DDL modifies the data table

Alter table rename to rename a table; Alter table table name character set character set name; Alter table name add column name data type; Alter table table name modify column name New data type; Alter table table name change column name New column name New data type; Alter table drop column name;Copy the code
Alter table product rename to product2; Show table status from db3 like 'product2'; Alter table producT2 character set GBK alter table producT2 character set GBK; Show table status from db3 like 'product2'; Alter table product2 add color varchar(10); Alter table product2 modify color int; desc product2; Alter table product2 change color address varchar(200); Alter table product2 drop address;Copy the code

DDL deletes tables

DROP table DROP table name; Drop table if exists Indicates the name of the table.Copy the code
DROP table product2; DROP table Product2 if exists Product2;Copy the code