MySQL learning to create and operate databases and table DDL for beginners

1. Operate the database

1.1 Create a database

create + database + database name

Of course, if we don't know whether the database exists, we can use the following statement to create it if it does not exist.

create + database + if not exists + database name

We can also specify the character set when creating a database.

create + dabase + if not exists + database name + character set + character set name

1.2 Query the database

Query all existing databases

show databases

Query the creation statement of a database and the character set used

show create database + database name

1.3 Modify the database

Modify the database character set

alter database + database name + character set + character set name

1.4 Delete the database

drop database + database name

Similarly, you can also add if not exists

drop database + if exists + database name

1.5 Using the Database

Using Database

use + database name

Query the name of the database currently in use

select database()

2. Operation table

2.1MySQL Data Types

Question 1: Characteristics and differences between varchar and char

Characteristics of char:

1. char represents a fixed-length string, and the length is fixed (for example, char(5) is stored as 5 characters even if it is less than 5 characters)
characters will also be stored as 5 characters);
2. If the length of the inserted data is less than the fixed length of char, it is filled with spaces;
3. Because of the fixed length, the access speed is much faster than varchar, even 50% faster. However, because of its fixed length, it will take up extra space, which is a space-for-time approach.
4. For char, the maximum number of characters that can be stored is 255, regardless of the encoding

Characteristics of varchar:

1. varchar represents a variable-length string, and the length is variable (for example, if varchar(5) is less than 5 characters, for example, 3 characters, then it is stored as three characters);
2. The inserted data is stored according to its length;
3.Varchar is the opposite of char in terms of access. It is slow to access because its length is not fixed. However, because of this, it does not occupy extra space and is a method of trading time for space.
4. For varchar, the maximum number of characters that can be stored is 65532

The difference between the two:

Combining the performance perspective (char is faster) and the disk space saving perspective (varchar is smaller)

There is also a point that both store characters

Zhang San: represents two characters
zhangsan: represents eight characters

Question 2: What is the meaning of 20 in int(20)?

Refers to the length of displayed characters. 20 means the maximum display width is 20, but it still occupies 4 bytes of storage, and the storage range remains unchanged;

It does not affect internal storage, but only affects how many zeros are added to the front of an int with zerofill definition, which is convenient for report display.

Question 3: What is the difference between float and double?

1. Float type data can store up to 8 decimal digits and occupies 4 bytes in memory.
2. Double type data can store up to 18 decimal digits and occupies 8 bytes in memory.

Question 4: What is the difference between datetime and timestamp?

For a timestamp field, if you do not assign a value to this field or assign a null value to this field, the current system time will be used by default.

2.2 Create Table

create table + table name (
Column data type,
Column data type,
.........
Column Data Types
)

Sometimes we think Copy the structure of a table:

create table + the name of the new table to be copied + like + the table to be copied

Sometimes we think Copy all the contents of a table:

create table + the name of the new table to be copied + select * from the name of the table to be copied. Note that there is no like

Sometimes we Copy only part of the content:

create table + copied new table name + select statement

Sometimes we Copy only part of the table's field structure

create table + the name of the new table to be copied + select the fields to be copied + from the table to be copied + where + a condition that is always false (such as 0=1)

2.3 Query Table

Query the names of all tables in the database

show tables

Query table structure

desc + table name

2.4 Delete Table

drop table + if exists + table name

2.5 Modify the table

Modify table name

alter table + table name + rename to + new table name

Modify the character set of a table

alter table + table name + character set + character set name

Add a column

alter table + table name + add + (column) + column data type

Modify column name

alter table + table name + change + (column) + column name new column name data type

Modify column type

alter table table name modify + (column) + column name new data type

Deleting a column

alter table + table name + drop column name

The above is the detailed content of the beginner's edition of MySQL learning on creating and manipulating databases and table DDL. For more information about MySQL database, please pay attention to other related articles on 123WORDPRESS.COM!