day02_DDL (operation database and table structure) DML (operation table data)

DDL (Data Definition Language) is used to define database objects: databases, tables, columns, etc. Keywords: create, drop, alter and so on . Both the structure of the database can be manipulated and the structure of the tables in the database can be manipulated, let's learn about them separately

DDL operation database

C (Create): Several ways to create a database

Create a database directly

  • CREATE DATABASE database name;
-- 直接创建wrgdb数据库CREATE DATABASE wrgdb; 

Determine whether the database already exists, create a database if it does not exist

  • CREATE DATABASE IF NOT EXISTS database name;
-- 判断wrgdb数据库是否已经存在,不存在则创建wrgdb数据库CREATE DATABASE IF NOT EXISTS wrgdb;

Determine whether the database already exists, create the database and specify the character set if it does not exist

  • CREATE DATABASE IF NOT EXISTS database name CHARACTER SET character set;
-- 判断wrgdb数据库是否已经存在,不存在则就创建wrgdb数据库并指定字符集为gbkCREATE DATABASE IF NOT EXISTS wrgdb  CHARACTER SET gbk;

R (Retrieve): Several ways to query the database

Query the names of all databases:

  • show databases;
-- 查询所有数据库的名称:SHOW DATABASES;

Query the creation statement of a database (check the creation statement to know the character set of the database)

  • show create database database name;
-- 查询wrgdb数据库的创建语句(查看创建语句就知道数据库的字符集,)SHOW CREATE DATABASE wrgdb;

U(Update): Modify the default character set of the database

  • ALTER DATABASE database name DEFAULT CHARACTER SET character set;
-- 修改wrgdb数据库默认的字符集为gbkALTER DATABASE wrgdb DEFAULT CHARACTER SET gbk;

D (Delete): delete the database

Delete directly

  • drop database database name;
-- 直接删除wrgdb1数据库DROP DATABASE wrgdb1;

Determine if the database exists, delete it if it exists

  • drop database if exists database name;
-- 判断wrgdb1数据库存在,存在再删除wrgdb1数据库DROP DATABASE IF EXISTS wrgdb1;

Use database

Query the name of the database currently in use

  • select database();
-- 查询当前正在使用的数据库名称SELECT DATABASE();

Enter a specific database

  • use database name;
-- 进入wrgdb数据库USE wrgdb;

DDL operation table structure

Prerequisite: use a database first

C(Create) : Create table format:

Note: There is no need to add a comma in the last column,

Common data types

  • int: integer type, for example: age int, the default signed range (-2147483648 ~ 2147483647), unsigned range (0 ~ 4294967295).
  • double: decimal type For example: score double(5,2) specifies that the score consists of at most 5 digits, and at most 2 digits are reserved after the decimal point
  • date: date, only contains year, month and day, yyyy-MM-dd
  • datetime: date, including year, month, day, hour, minute and second yyyy-MM-dd HH:mm:ss
  • timestamp: The type of time rubbing includes year, month, day, hour, minute, and second yyyy-MM-dd HH:mm:ss If you do not assign a value to this field in the future, or assign a value to null, the current system time will be used by default to automatically assign the value
  • varchar: string For example: name varchar(20): The name can have a maximum of 20 characters, zhangsan 8 characters and 2 characters, one Chinese or one letter occupies one character.

The detailed data types are as follows

-- 创建学生表,由字段id,name,age,score,birthday,insert_time组成CREATE TABLE student(	id        INT,	`name`    VARCHAR(32),	age       INT,	birthday   DATE,	insert_time TIMESTAMP)

Quickly create a table with the same table structure

  • CREATE TABLE new table name LIKE old table name;
-- 快速创建一个新表student1结构与旧表student完全相同 CREATE TABLE student1 LIKE student;

R (Retrieve): query table

Query the names of all tables in the database

  • show tables;
-- 查询所在数据库中所有的表名称SHOW TABLES;

Query table structure

  • desc table name;
-- 查询student表结构DESC student;

U(Update): modify the table

Modify table name

  • alter table table name rename to new table name;
-- 修改student1表名为student2ALTER TABLE student1 RENAME TO student2;

Modify the character set of the table

  • alter table table name character set character set name;
-- 修改student2表的字符集为gbkALTER TABLE student2 CHARACTER SET gbk;

Add a column

  • alter table table name add column name data type;
-- 给student2添加一列goodALTER TABLE student2 ADD good VARCHAR(32);

Modify column name type

  • alter table table name change column name new column type new data type;
-- 修改student2 表 good 列名称 类型:ALTER TABLE student2 CHANGE good good INT;

Delete column

  • alter table table name drop column name;
-- 删除student2的good列ALTER TABLE student2 DROP good;

D(Delete): delete

Directly delete the table

  • drop table table name;
-- 直接删除student2表DROP TABLE student2;

First judge whether the table exists otherwise, delete it if it exists

  • drop table if exists table name;
-- 先判断student2表是否则存在,存在就删除DROP TABLE IF EXISTS student2 ;

Data in DML operation table

adding data:

To add data to any field, write the name of the field. Fields that do not add data will use NULL, except for the timestamp

  • insert into table name (column name 1, column name 2, ... column name n) values ​​(value 1, value 2, ... value n);

note:

  • The column name and value should correspond one to one.
  • If the column name is not defined after the table name, values ​​will be added to all columns by default. Syntax: insert into table name values ​​(value 1, value 2, ... value n);
  • In addition to the number type, other types need to use quotation marks (single and double)

delete data:

  • delete from table name [where condition]

note:

  • If no conditions are added, all records in the table are deleted.
  • If you want to delete all records, there are two ways
  • Method 1: delete from table name; - Not recommended. How many delete operations will be performed as many records as there are
  • Method 2: TRUNCATE TABLE table name; - Recommended, more efficient, delete the table first, and then create the same table.

change the data:

  • update table name set column name 1 = value 1, column name 2 = value 2,... [where condition];

note:

  • If no conditions are added, all records in the table will be modified.