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

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.