Mysql database Quick Start (1), mysql database Quick Start

Source: Internet
Author: User

Mysql database Quick Start (1), mysql database Quick Start
1. Database Operations

1.1 connect to the mysql server

Mysql-u root (User Name)-p

1.2 exit the mysql Command Prompt window.

Exit

1.3 view version

Select version ();

1.4 list databases

Show databases;

1.5 create a database

Create database if not exists [database] default charset UTF8;

1.6 Select the database to operate

USE [database];

1.7 delete a database

Grop database [database];

2. Table operations (created on the selected database)

2.1 create a table

Create table if not exists ['tablename'] ('columnname' columnType primary key/...) CHARSET UTF8;

Primary key: Set the field as the primary key;

AUTO_INCREMENT: auto-increment;

Not nul: The field data is not null; otherwise, an error is returned;

CHARSET: sets the encoding format.

2.2 list tables

Show tables;

2.3. query data table Field Information

Show columns from [tableName];

2.4. query detailed index information of a data table

Show index from [tableName];

2.5. query the performance and statistical information of the Management System

Show table status from [database] LIKE '** %' \ G;

** %: Start with something;

\ G: displays by column.

2.6 delete a data table

Drop table [tableName];

2.7. For details about table modification, refer to 4.9.

3. Data Operations (add, delete, modify, and query)

3.1 insert data

Insert into [tableName] (key1, key2) VALUES (val1, val2 );

  

3.2 delete data

Delete from [tableName] condition;

Condition: WHERE id = 1 /...

3.3 modify and update data

UPDATE [tableName] SET key1 = val1, key2 = val2 condition;

  

3.4. query data

SELECT */(key1, key2) FROM [tableName];

*: Check all;

Key1, key2: The field to be queried.

4. Clause (combined with addition, deletion, modification, and query operations)

4.1. WHERE

AND: AND conditions

Eg: SELECT */(key1, key2) FROM [tableName] WHERE id = 1 AND name = 'newname ';

  

OR: OR condition

Eg: SELECT */(key1, key2) FROM [tableName] WHERE id = 2 OR name = 'newname ';

  

4.2. LIKE % -- fuzzy query allowed

The table contains the following data:

  

None %: exact query:

,

% ** Before: ended **: SELECT * FROM [tableName] WHERE key LIKE '% val ';

  

** % After: started with **: SELECT * FROM [tableName] WHERE key LIKE 'val % ';

  

% ** %: Include **: SELECT * FROM [tableName] WHERE key LIKE '% val % ';

  

4.3. union all/DISTINCT -- get a set of more than two select statements.

There are two tables:

  

ALL: Not heavy

  

DISTINCT: deduplication

  

4.4. order by key ASC/DESC -- asc ascending and desc descending

4.5. group by -- GROUP statistics

  

SELECT name COUNT (*)/SUM (key)/AVG (key) AS newName FROM [tableName] group by name with rollup; (the number of group statistics names is displayed in the newName field)

  

4.6 JOIN -- JOIN

There are two tables:

  

Inner join (get matching relationship records of two tables): SELECT a. name, B. count FROM [tableName1] a inner join [tableName2] B ON a. name = B. name;

  

Equivalent statement: SELECT a. name, B. count FROM [tableName1] a, [tableName2] B WHERE a. name = B. name;

  

LEFT JOIN)

  

Right join (right join to obtain the records of the RIGHT table, even if the left table does not exist)

4.7, NULL

Is null: The value is null;

Is not null: The value is not null.

4.8. REGEXP -- Regular

SELECT * FROM [tableName] WHERE name REGEXP 'regexp ';

4.9. ALTER -- add, delete, and modify Fields

Original table structure:

  

ADD a column: alter table [tableName] ADD newKey INT /... not null default 'val 'first/AFTER key; Add a new field newKey. The DEFAULT value is val AFTER the FIRST column or key column.

FIRST: add the field to the FIRST column;

AFTER: add the field to the ** field;

DEFAULT: Set the DEFAULT value of the field.

  

Delete a column: alter table [tableName] DROP newKey;

  

Alter table [tableName] CHANGE key newKey INT /...;

  

Alter table: alter table [tableName] rename to [newTableName];

  

------- If you have any questions, please point out. For more information, please describe the source.

 

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.