How many pieces of data exist in the mysql Statistics database _ MySQL

Mysql calculates how many data entries in the database bitsCN.com

Today I reviewed some simple MySQL statements, which can be summarized as follows:

Number of mysql statistical tables: select count (1) FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'Your database ';

Some basic MySQL statements:

Rows are record columns are fields

Create a database

Create database [if not exists] DATABASE name [parameter [parameter] [parameter]...];

Parameters:

Character set table name

For more information about COLLATE rules, see section 10.10.1.

Display Library

Show databases;

Show database creation statements

Name of the show create database;

Modify database

Alter database name [parameter [parameter] [parameter]...];

Note: you cannot change the database name.

Delete database

Drop database [if exists] DATABASE name;

Before using a table, you must determine which database to use.

USE database name

Create a table

Create table name (column name type [, column name type] [, column name type]...);

View all tables

Show tables;

View the table creation statement

Show create table name;

Display table structure

DESC table name;

Modify table name

Rename table original TABLE name TO new TABLE name;

Modify character set

Alter table name character set name;

Delete table

Drop table name;

Append column

Alter table name ADD column name type [, column name type] [, column name type]...;

Modify column type

Alter table name MODIFY column name type [, column name type] [, column name type]...;

Modify columns

Alter table name change column original COLUMN name new COLUMN name type;

Delete column

Alter table name DROP column name;

Insert syntax:

Insert into table name [(column name [, column name]...)] VALUES (value [, value]...);

Note:

The data type of the inserted value must be the same as that of the corresponding column.

Data cannot exceed the length

Insert values must be consistent with column Name order

Character and date data must be placed in single quotes

Null is used to insert null values.

If you do not specify which column to insert, all columns are inserted.

Chinese data

Because the default code table is utf8 and the code table of cmd.exe is gbk, an error is reported when Chinese data is inserted. Therefore, we need to modify the code table of the client.

First, check the system variable: show variables like 'character % ';

Modify the client code table: SET character_set_client = gbk;

Modify the output data table: SET character_set_results = gbk;

Modify syntax

UPDATE table name SET column name = value [, column name = value]... [WHERE condition statement];

Notes

The WHERE clause selects rows that meet the conditions for update. if no data is written, all rows are updated.

Delete syntax

Delete from table name [where condition statement]

Notes

If the where clause is not added, all records in the table will be deleted.

Delete can only be used to delete rows. values in a column cannot be deleted. update is required.

Pay attention to the association between tables during delete and update operations.

You can use the TRANCATE table name to delete all the data in the table. in this way, the old table is deleted and re-created.

Back up Database

Enter quit to exit mysql. in cmd.exe, enter:

Mysqldump-u user name-p password database name> file name

Restore database

After mysql.exe is used

Source file name

Operation Data (query)

DISTINCT syntax

SELECT [DISTINCT] column name [, column name]... FROM table name

Notes

* The column name can be used instead of all columns. however, to improve code readability, we do not use the column name *

DISTINCT filters duplicate records

If DISTINCT is followed by multiple columns, it filters out duplicates after merging multiple columns.

Column name expression-syntax

SELECT column name | expression [, column name | expression]... FROM table name

Notes

The expression only works when it is displayed and does not change the value in the database.

AS --

SELECT column name AS alias FROM table name

Notes

The AS can be omitted when alias is started

Does not change the value in the database

WHERE --

Syntax

SELECT column name FROM table name [WHERE condition statement]

Operators in the WHERE clause

Comparison Operators

>,<, >=, <=, =, <>

Note that it is not the same as Java, Yes <>

BETWEEN... AND...

The value in a certain range, from......

IN (list)

In the list, for example, in (1, 2, 3) Represents 1, 2, or 3.

LIKE (expression)

Fuzzy query. % indicates multiple characters. _ indicates a single character.

IS NULL

Judge whether it is NULL

Logical operators

AND &&

And, both sides are TRUE, and the result is TRUE.

OR |

Or, if one side is TRUE, the result is TRUE.

NOT!

No. reverse returns the expression result.

Order --

Syntax

SELECT column name FROM table name order by column name ASC | DESC;

Notes

The column name specified by order by can be the column name in the table or the alias after the SELECT statement.

ASC is in ascending order, and DESC is in descending order.

Order by should end with the query statement

COUNT function --

Syntax

Select count (*) | COUNT (column name) from table name [WHERE condition statement]

Notes

COUNT (column name) is used to COUNT the number of records in a specified column, excluding NULL records.

COUNT (*) is the number of data entries in the statistical table.

COUNT (DISTINCT column name) COUNT the number of records that are not repeated

If the WHERE clause is added, records that meet the conditions are counted.

BitsCN.com