Catalog

1. SQL Order classification

2. Database operation

3. MySQL data type  

3.1 value type

3.2 String type

3.3 ⽇ Period type

 4. Table operations

4.1 Table structure operations

4.1.1 Create table

4.1.2 Delete table

 4.1.3 View table structure

 4.1.4 Delete table

 4.1.5 Modify table structure

---

1. SQL Order classification

• DDL【Data Definition Language】 Data definition language ⾔,⽤ To maintain the structure of stored data, representing instructions : create,drop, alter;
• DML【Data Manipulation Language】 Data manipulation language ⾔,⽤ To enter the data ⾏ Operation represents instruction ：insert,delete,update,DML in ⼜ Separately ⼀ individual DQL, Data query language ⾔, For instructions ：select;
• DCL【Data Control Language】 Data control language ⾔, Mainly responsible for authority management and affairs representative instructions ：grant,revoke,commit.

DDL It can be seen as the structure of operation library or table ,⽽ DML yes ⽤ To manipulate data ,⽽ DCL Is to operate other , Such as permissions and transactions .

2. Database operation

• Create database ：create database [ Database name ];
• Switch database ：use [ Database name ];
• Delete database ：drop database [ Database name ];
• Query all databases ：show databases;
• Query the current database ：select database();

3. MySQL data type  

3.1 value type

It can be divided into integer type and floating point type ：

data type	⼤⼩	explain	Corresponding java type	Corresponding C type
BIT[ (M) ]	M Specify the number of digits , Silent Think 1	⼆ Hexadecimal number ,M Fan Wai Cong 1 To 64, save The stored values range from 0 To 2^M-1	often ⽤Boolean Yes Should be BIT, At this time, silence Yes 1 position , It's just Able to survive 0 and 1	char[]
TINYINT	1 byte	Byte	signed char
SMALLINT	2 byte	Short	short int
INT	4 byte	Integer	int
BIGINT	8 byte	Long	long long int
FLOAT(M, D)	4 byte	Single precision ,M Appoint ⻓ degree ,D Appoint ⼩ Number of digits . Will send ⽣ Precision loss	Float	float
DOUBLE(M, D)	8 byte	Double	double
DECIMAL(M, D)	M/D most ⼤ value +2	Double precision ,M Appoint ⻓ degree ,D Express ⼩ Count the number of points . accurate The number	BigDecimal	char[]
NUMERIC(M, D)	M/D most ⼤ value +2	and DECIMAL⼀ sample	BigDecimal	char[]

3.2 String type

data type	⼤⼩	explain	Corresponding java type	Corresponding C type
VARCHAR (SIZE)	0-65,535 byte	variable ⻓ Degree string	String	char[]
TEXT	0-65,535 byte	⻓⽂ This data	String	char[]
MEDIUMTEXT	0-16 777 215 word section	secondary ⻓ degree ⽂ Number of books According to the	String	char[]
LONGTEXT	0-4 294 967 295 byte	extremely ⼤⽂ This data	String	char[]
BLOB	0-65,535 byte	⼆ In decimal form ⻓ ⽂ This data	byte[]	char[]

3.3 ⽇ Period type

data type	⼤⼩	explain	Corresponding java type	Corresponding C type
DATETIME	8 byte	Range from 1000 To 9999 year , I won't go in ⾏ Time zone retrieval and transformation .	java.util.Date、 java.sql.Timest amp	MYSQL_TIME
TIMESTAMP	4 byte	Range from 1970 To 2038 year ,⾃ Dynamic inspection Search the current time zone and enter ⾏ transformation .	java.util.Date、 java.sql.Timest amp	MYSQL_TIME

 4. Table operations

        MySQL ⾮ Linux The system installation defaults to ⼤⼩ Write insensitive , That is to ignore ⼤⼩ Write match , But when creating databases and tables ⼀ Be sure to pay attention to , The default is all lowercase To operate , prevent ⽌ Compiling sql no ⽤. Table names are underlined ,⽐ Like learning ⽣ League tables , Can make ⽤ student_score.

4.1 Table structure operations

4.1.1 Create table

grammar ：

CREATE TABLE table_name (
    field1 datatype,
    field2 datatype,
    field3 datatype
);

You can make ⽤comment Add field description . for example ：

create table student (
    id int,
    name varchar(20) comment ' full name ',
    age int,
    sex varchar(1)
);

4.1.2 Delete table

grammar ：

DROP [TEMPORARY] TABLE [IF EXISTS] tbl_name [, tbl_name] ...

Brackets can be omitted , You can delete more than one table at a time , Table names are used between ' , ' Separate .

for example ：

--  Delete  student  surface 
drop table student;
--  If there is  student  surface , Delete  student  surface 
drop table if exists student;

 4.1.3 View table structure

grammar ：

desc  Table name ;

for example ：

desc student;

desc Unable to view the description information , You can make ⽤ another ⼀ A query table structure ⽅ Law ：

show create table student;

  Or make ⽤“show full columns from Table name ” Inquire about ⼀ All field information of the table , The effect is as follows ：

View all tables ：

show tables;

 4.1.4 Delete table

grammar ：

drop table [if exists]  Table name ;

 4.1.5 Modify table structure

（1） Add fields ：

grammar ：

alter table  Table name  add column  Name   Column type  [comment  Notes ];

for example ：

 （2） Delete field

grammar ：

alter table  Table name  drop column  Name ;

for example ：

（3） Modify fields

grammar ：

alter table  Table name  change  Original field name   new field name   type  [ constraint ];

  for example ：

（4） Modify the name of the table

grammar ：

alter table  The old name of the table  rename  The new name of the table ;

  for example ：

（5） Change the encoding format of the table

grammar ：

alter table  Table name  convert to character set utf8mb4;

  for example ：