Comprehensive Mysql database, database table, basic data operation notes (including code), mysql database

Comprehensive Mysql database, database table, basic data operation notes (including code), mysql database

Mysql database, database table, and basic data operation notes are shared with you for your reference. The specific content is as follows:

I. Database Operations

1. Create a database
Create database db name [database options];
Tip: The statement must end the service with the statement Terminator.
Naming rules for Identifiers (Database names:
The case sensitivity depends on the current operating system.
We recommend that you use the underline method.
Characters of the identifier:
Use any character, number, or even Chinese. However, some special combinations, such as combination of numbers only and special symbols, must be enclosed by an identifier qualifier.
Qualifier: backticks''
Chinese: but the client encoding is required.

Create a directory in the mysql data directory. The directory name is the database name.
A file exists in the directory to save the options of the database. Db. opt
2. Database Query
View the existing database:
Show databases;
View the database creation statement:
Show create database db_name;

3. delete a database
Drop database db_name;

4. modify Database Information
Alter database db_name [modify command]

Modify Name:
You can directly modify the directory name.
Export the database content, create a new database, import the content, and delete the old database.
When you create a new database, all the tables in the database are moved (renamed) to the new database to delete the old database.

Ii. Database Table operations

A database is a table container. A table must belong to a database and be specified by a. Statement;
During table operations, the current default database is specified.
Use db_name;

1. Create a table
Create table tbl_name {Column Structure} [table options]
First, analyze the object data to be saved and what attributes it has. How to save these attributes.
For example, class information
Class number, class start date

Column definition:
Data Type of column names [column attributes (constraints)]

Simple string type varchar;
Date
The first method is to use a. statement instead of specifying a database in advance.

Method 2: Specify the default database

 

2. View
1> tables
Show tables [like 'pattern'];
Like 'pattern' indicates the name of the table to obtain a rule.

The above like applies to show databases like ['pattern'];

2> Create a table
Show create table tal_name;
Tip: \ G can be used as the statement Terminator for clear display.
Comparison

3> View the table structure (describe the table structure)
Describe tbl_name;
Desc tbl_name;

 

Table name prefix
To differentiate different applications with the same logical table name, add a prefix to the logical table name to form a logical table name.

3. delete a table
Drop table tbl_name;

If this parameter does not exist, no error is returned:

The same applies to databases:
Drop database if exists db_name;
4. modify a table
Modify Table Name
Rename table old tbl_name to tbl_name;

Multiple names can be modified together.

Supports cross-database rename. You can use this feature to rename a database by moving tables in one database to another.

Modify column Definitions
Add a new column
Add

Delete a column
Drop

Modify the definition of a column
Modify column Definition

Modify a column
Change old_column new column Definition

Modify the table structure. The preceding command is a sub-command and the upper-level command is alter table tbl_name [add | drop | change | modify].

Modify Table options
Alter table tbl_name new table options

Iii. Data Operations

1. Create data (insert data)
Insert into Table Name (Field List) values (Value List)

If you set values for all fields during insertion, You can omit the field list. The requirements are worthy of order and should be consistent with the order of fields in the table

 

2. obtain data (query data)
Select field list from table name query Condition

Tip: the query condition can be omitted, which is equivalent to where 1.
Conditional Query

3. delete data
Delete from table name Condition

Tip: the deletion must be logically strict with the conditions. Otherwise, data may be lost.
4. modify data
Update table name set field = new value,... condition.

The above is all about Mysql database operations, and I hope it will help you learn.