A very comprehensive Mysql database, database tables, data base operation notes (including code) _mysql

MySQL database, database tables, data base operation notes to share with you for your reference, the specific content as follows

First, the database operation

1. Create a database
Create Database DB name[databases option];
Tip: statement requires a statement terminator ";" To end the service.
Identifiers (database name) naming rules:
Capitalization depends on the current operating system.
It is recommended to use the Underline method when you know the name.
Characters for identifiers:
Use any character, number, or even Chinese. However, some special combinations, such as pure numeric combinations, special symbols, etc., need to be wrapped with identifier qualifiers.
Qualifier: Inverted Quote '
Chinese can: but require client code

In the MySQL data directory, form a directory where the directory name is the database name.
In the directory, there is a file that holds the option information for the database. Db.opt
2. Database Query
To view a database that currently exists:
show databases;
To view the creation statement for a database:
Show CREATE Database db_name;

3. Database deletion
Drop database db_name;

4. Modify Database Information
ALTER DATABASE db_name[Modify instruction]

Change Name:
Simple to modify the directory name directly.
Export the database content, create a new database, import the content, and delete the old database.
Create a new database that will move (rename) the tables in the database to the new database and delete the old database.

Second, the database table operation

A database is a container for a table, which must belong to a database by using the. Statement to specify the owning database;
When you perform a table operation, you specify the current default database
Use db_name;

1. Create a table
Create table tbl_name {column Structure}[table options]
First analyze the entity data that needs to be saved, and what attributes it has. How these properties should be saved.
For example: class information
Class number, class date

Column definition:
Data type for column names [Column properties (constraints)]

Simple string type varchar;
Dates Type Date
The first method does not have to specify the database beforehand, use the. Statement

The second method first specifies the default database

2. View
1> have those watches.
Show tables[like ' pattern '];
Like ' pattern ' indicates the name of a table that obtains some kind of rule

The above like also applies to show Databases like[' pattern '];

2> create information for a table
Show CREATE TABLE tal_name;
Tip: You can use \g as a statement terminator to show clarity.
Contrast

3> view table structure (description table structure)
Describe Tbl_name;
Desc Tbl_name;

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

3. Delete Table
Drop table tbl_name;

Does not exist and does not complain:

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

Support multiple names to modify together

Support for renaming across databases, moving a table under one database to another database, you can use this feature for database renaming.

modifying 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 table structure, above is subcommand, superior is, ALTER TABLE tbl_name [Add|drop|change|modify]

Modify Table Options
Alter table tbl_name New Table options

Third, data operation

1. Create data (insert data)
Insert into table name (field list) VALUES (Value list)

If you set a value for all fields when inserting, you can omit the field list. Requirements are worth the order and should correspond to the order of the fields in the table

2. Access to data (query data)
Select field list from table name query criteria

Tip: Query conditions can omit the equivalent of where 1
Conditional query

3. Delete data
Delete from table name condition

Tip: Delete needs to be logically strict to the condition, otherwise it is easy to cause data loss.
4. Modify Data
Update table name Set field = new Value,.... Conditions.

The above is about the MySQL database operation of the entire content, I hope to help you learn.