Basic knowledge in MySQL

Clear screen: CLS

MySQL Basics

Start MySQL net start MySQL

Shut down MySQL net stop MySQL

Login Mysql-uroot-p

Exit mysql>exit;

mysql>quit;

mysql>\p;

Displays the current server version of SELECT version ();

Show Current Time SELECT now ();

Displays the current user SELECT user ();

The default port number for MySQL is: 3306

MySQL Super User is: root

Creating databases: Create Database

modifying databases: ALTER database

Delete database: Drop db

Data type

Integral type: TINYINT SMALLINT mediumint INT BIGINT

float: float[(m,d)] double[(m,d)] M is the total number of digits, and D is the number of digits after the decimal point

Character type: Verchar (M)

Data Sheet

See what database: Show DATABASES;

Open database: Use database name

View Current database: SELECT DATABASES;

Create data table: Creation Table[if not Exists]table_name (

Agetinyint unsgined (unsigned bit),

......

)

View a list of data tables: Show Tables[form db_name]; Use form to view tables in other databases

View data table structure: Show COLUMNS form Tbl_name;

Insert record; Insert [into] tbl_name[(Coi_name,...)] VALUES (VAL,...);

Find records: SELECT expr,... FORM tbl_name (WHERE .....) ）;

Null and non-empty

CREATE Table[if not Exists]table_name (

Age1 TINYINT unsgined (unsigned bit) not NULL,

Age2 TINYINT unsgined (unsigned bit) null,//default is can be empty

......

)

Auto Number Auto_increment

1 is automatically numbered and must be combined with the primary key

2 By default, the starting value is 1 and the increment is 1

Primary KEY Primary Key

1 only one primary key per table

The primary key guarantees the uniqueness of the record

Primary key is not NULL automatically

CREATE Table[if not Exists]table_name (

Age1 TINYINT unsgined (unsigned bit) PRIMARY KEY,

......

)

Uniquely constrained unique KEY

Default constraint: Defaults

Update Record Updates

Syntax: UPDATE tb_name SET age=age+10 WHERE name= "Chaihuo";

Deleting Records Felete

Syntax: DELETE FORM tb_name WHERE name= "Chaihuo";

Query result GROUP BY

Syntax: SELECT sex FORM users by sex;

hcaing Grouping conditions

Syntax: SELECT sex,age FORM users by sex have age>35;

Main: There are only two cases at this time 1) aggregate function 2) after select

Sort the query results order BY

Syntax: SELECT * FORM users ORDER by ID DESC;

Operators and functions

Character operators

CONCAT () Character connection

CONCAT_WS () using the specified delimiter for character connections

Format () number formatting

LOWER () UPPER () Convert to small/uppercase

Left () Right () gets the character

Length () Get string lengths

SUBSTRING () string intercept

[NOT] Like pattern matching

Replace () string substitution

numeric operators and functions

Ceil () into a rounding

DIV Integer Division

Floor () Rounding and rounding

MOD take remainder

Power () Power operation

ROUND () rounding

comparison operators and functions

[NOT] Between ... And ... [NOT] In () Is[not]null

Information functions

CONNECTION_ID () connection ID

Datebase () Current database

LAST_INSERT_ID () The ID number of the last inserted record

User () Current users

Verson () version information

Aggregation functions

AVG () Average

Count () counts

MAX () MIN () SUM ()

Cryptographic functions

MD5 () Information Digest algorithm

PASSWORD () cipher algorithm

Sub-queries and connections

Writing query results to a data table

Insert[into] tbl_name[(col_name,...)] SELECT ...

Example: INSERT tdb_goods (cate_name) SELECT good_cake FORM table GROUP by Good_cake;

Multiple table Updates

UPDATE table1 INNER JOIN table2 on Table1_name=table2_name SET Table1_id=table2_di;

CREATE ... SELECT

Creating a data table and writing query results to the datasheet

CREATE TABLE table_name [(create_definine)] Select_statement

Example: CREATE TABLE table1 (

ID SMALLINT UNSIGNED PRIMARY KEY auto_increment,

Name VARCHAR () not NULL)

Select Select Good_cake FORM table GROUP by Good_cake;

)

Storage Engine

By modifying the MySQL configuration file implementation

1)-ddfault-storage-engine=engine

2) by creating a data Table command to implement

CREATE Table[if not Exists]table_name (

Age1 TINYINT unsgined (unsigned bit) not NULL,

Age2 TINYINT unsgined (unsigned bit) null,//default is can be empty

......

) Engine=engine;

Implemented by modifying the data Table command

-alter TABLE table_name engine[=]engine_name;

Storage Engine

MyISAM: Storage now up to 256TB, supports indexing. Table-level locking, data compression

InnoDB: Storage limit is 64TB, transactions and indexes are supported. Lock grain as row lock