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

Source: Internet
Author: User

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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.