MySQL

1, Connected data volume

mysql -u username -p

Enter after entering the command , display Enter password: Just enter the password .

username User name used when logging in

2, Create database

create database databaseName

databaseName: Database name to be created .

3, Delete database

drop database databaseName

databaseName: Database name to be deleted .

4, Select database

use databaseName

databaseName: Database name you want to use .

5, Create data table

create table tableName(column_name column_type);

tableName: Name of data table to be created

column_name: Create column names in the table

column_type: Create column data types in the table

example :

create table if not exists Device_Type(

Device_id int auto_increment,

Device_name varchar(64) not null,

Device_typenum varchar(24) not null,

Device_date datetime,

primary key(Device_id)

) default charset=utf8;

create table if not exists Consumables_Type(

Consumable_id int auto_increment,

Consumable_name varchar(64) not null,

Consumable_typenum varchar(24) not null,

Consumable_date datetime,

primary key(Consumable_id)

) default charset=utf8;

create table if not exists Types_Num(

Type_id int auto_increment,

Type_Num varchar(24) not null,

Type_date datetime,

primary key(Type_id)

)default charset=utf8;

auto_increment Define column auto increment attribute , Generally used for primary keys , The value will increase automatically 1

primary key Used to define this column as the primary key , You can use multiple columns to define a primary key , Columns are separated by commas

charset Set encoding

If you don't want the field to be null, Set the field property to not null, When operating the database, if this field is null, An error will be reported .

6, Delete data table

drop table tableName

tableName: Name of data table to be deleted

7, insert data

insert into tableName(field1, field2,...fieldN) values(value1,
value2,...valueN )

tableName: The table name that needs to insert data

filed1...fieldN: Table column name to insert data

value1...valueN: Data to be inserted .

example :

insert into Device_type

(Device_name, Device_typenum, Device_date)

values

("Genie", "001", NOW());

insert into Types_Num

(Type_Num, Type_date)

values("001", NOW()),("002", NOW()), ("003", NOW());

NOW() Function returns the date and time of the current system .

8, Query data

select column_name1, column_name2, ...column_nameN from tableName

[where condition]

[limit N][offset M]

where Statement to include filter criteria

limit Property to set the number of records returned

offset appoint select Data offset at the beginning of the statement query , By default, the offset is 0

use * Substitute field , The values of all fields will be returned .

example

select * from Device_Type where Device_typenum = '0001' limit 5 offset 1;

Only unique different values are returned .

select distinct column_name1, column_name2, ...column_nameN from tableName

[where condition]

[limit N][offset M]

9,where clause

select field1, field2, ...fieldN from tableName1, tableName2

[where condition1 [and[or]] condition2];

One or more tables can be queried at a time in a query statement , Between table names (,) separate .

Can be used in query statements where To set query conditions

have access to and( And ) or or( or ) To connect multiple query criteria

where Clauses can also be used for delete,update Wait for command statement

example :

select * from Device_Type where Device_typenum = '005' or Device_id < 2;

10,update to update

update tableName set field1 = 'newvalue1', field2 = 'newvalue2'

[where condition]

You can update the value of one or more fields at a time

example :

update Device_Type set Device_typenum = '0002'

where Device_id > 6 and Device_typenum = '005';

11,delete sentence

delete from tableName [where condition];

without where clause , All data in the table will be deleted .

example :

delete from Device_Type where Device_id > 8;

12,like sentence

select field1, field2,...fieldN from tableName

where like condition;

Can be in where Used in Clause like clause

have access to like Clause instead of equal sign =

like Usually with % Use together , Search similar to a metacharacter

example :

update Device_Type set Device_name = 'SuperGenie' where Device_name like 'DP%';

supplement :

13,union Operator

MySQL union Operators are used to connect two or more select sentence , Combine results into a result set .

select field1, field2,... fieldN from tableName1

[where condition]

union [all | distinct]

select field1, field2,... fieldN from tableName2

[where condition]

distinct: Optional , Delete duplicate data in the result set . By default union Duplicate data has been deleted , All distinct No impact on the results

all: Optional , Return all data in the result set , Include duplicate data .

example :

select Device_typenum from Device_Type

union all

select Consumable_typenum from Consumables_Type;

14,MySQL sort

select field1, field2, ... fieldN from tableName

order by fieldX ASC | DESC;

ASC Ascending order

DESC Descending order

example

select * from Device_Type order by Device_id desc;

15, grouping

select field, function(field) from tableName

[where condition]

group by field;

function(): SQL function

example :

select Consumable_name, COUNT(*) from Consumables_Type group by
Consumable_name;

select Consumable_name, COUNT(*) as count from Consumables_Type group by
Consumable_name;

as Alias

use with rollup: The same statistics can be carried out on the basis of grouping statistics

select Consumable_name, COUNT(*) as count from Consumables_Type group by
Consumable_name with rollup;

Return to in NULL Is the sum of statistics .

select coalesce(Consumable_name, "total") as name, COUNT(*) as count from
Consumables_Type group by Consumable_name with rollup;

16, Use of connections

1,inner join( Inner connection or equivalent connection )

Get the record of field matching relationship in two tables

select tableName1.field1, tableName1.field2, tableName2.field1 from tableName1

inner join tableName2

on tableName1.filed = tableName2.filed;

example :

select Consumables_Type.Consumable_id, Consumables_Type.Consumable_date,
Types_Num.Type_Num from Consumables_Type

inner join Types_Num

on Consumables_Type.Consumable_typenum = Types_Num.Type_Num;

2,left join( Left connection )

Get all records in the left table , Even if there are no matching records in the right table

example :

select Consumables_Type.Consumable_id, Consumables_Type.Consumable_date,
Types_Num.Type_Num from Consumables_Type

left join Types_Num

on Consumables_Type.Consumable_typenum = Types_Num.Type_Num;

3,right join( Right connection )

Get all records in the right table , Even if there are no matching records in the left table

example :

select Consumables_Type.Consumable_id, Consumables_Type.Consumable_date,
Types_Num.Type_Num from Consumables_Type

right join Types_Num

on Consumables_Type.Consumable_typenum = Types_Num.Type_Num;

17,NULL Value processing

is null: When the value of the column is NULL, This operator returns true

is not null: When the value of the column is not NULL Hour , Operator return true

example :

insert into Types_Num (Type_Num) values("004"), ("007"), ("008");

Use the following statement to query the table Types_Num in Type_date Field is null Data of .

select * from Types_Num where Type_date = NULL;

Executing the above statement will return “empty set”.

The correct query method is as follows :

select * from Types_Num where Type_date is null;

18, affair

MySQL
Transactions are mainly used for processing large amount of operations , Highly complex data . For example, to delete the information of a product , The information of this product may exist in multiple tables , This requires deleting the product related information in each table , If the information in a table is not deleted successfully , This may cause errors in the entire product information base , Therefore, the information of this product can be completely deleted , Or none of them will be deleted .

stay MySQL Only used in Innodb Only the database or table of the database engine supports transactions .

Transaction processing can be used to maintain the integrity of the database , Guaranteed in batches SQL Statements are either executed in full , Or not at all .

Transactions are used to manage insert,update,delete sentence

Transaction control statement :

begin or start transaction Displayed to open a transaction .

commit or commit work Commit transaction , Make changes to the database permanent .

rollback or rollback work Rollback will end the user's transaction , And undo all uncommitted modifications in progress

savepoint identifier,savepoint Allow to create a savepoint in a transaction , There can be more than one transaction savepoint

release savepoint identifier Delete the savepoint of a transaction , When the savepoint does not exist , Executing this statement will throw an exception

rollback to identifier Rollback transaction to savepoint , When the savepoint does not exist , Executing this statement will throw an exception

set transaction Used to set the isolation level of transactions

There are two main methods of transaction processing

1, use BEGIN, ROLLBACK, COMMIT To achieve

begin Start a transaction

rollback Transaction rollback

commit Transaction confirmation

2, Direct use SET To change MySQL Auto submit mode for :

set autocommit=0 Prohibit automatic submission

set autocommit=1 Turn on auto submit .

stay MySQL By default on the command line , Transactions are automatically committed , Immediate execution SQL Statement will be executed immediately COMMIT operation . Therefore, to explicitly start a transaction, you must use the command
BEGIN or START TRANSACTION, Or execute the order SET AUTOCOMMIT=0, Used to disable the automatic submission of the current session

19,alter command

alter table Statement to add to an existing table , Delete or modify columns .

Add field

alter table tableName add fieldName datatype

Under the name tableName In the table of , Add a new one named fieldName Field of , Data type is datatype.

example :

alter table Types_Num add Type_msg varchar(64);

Delete field

alter table tableName drop fieldName

Under the name tableName In the table of , Delete named fieldName Field of .

example :

alter table Types_Num drop Type_msg;

Modify field

If you need to modify the field type and name , Can be in alter Used in command modify or change clause .

alter table tableName modify fieldName newdatatype;

Under the name tableName In the table of , Will be named fieldName The data type of the field of is modified to newdatatype.

example :

alter table Types_Num modify Type_msg char(128);

use change clause , stay change After keyword , Next is the field name you want to modify , Then specify the new field name and type

alter table tableName change oldfield newfield datatype

Under the name tableName In the table of , Will be named oldfield The field of is modified to be named newfield, Data type is datatype Field of .

example :

alter table Types_Num change Type_msg Type_message varchar(128);

Add new fields and set default values

example :

alter table Types_Num add Type_msg varchar(64) not null default "unknow"

Modify table name

alter table oldTablName rename to newTableName

example :

alter table Types_Num rename to Types_Serial;

20, Indexes

1, General index

Create index

create index indexName on tableName(fieldName(length));

indexName: Index name

tableName: Table name

fieldName: Column name

If it is CHAR,VARCHAR type ,length Can be less than the actual length of the field ; If it is BLOB and TEXT type , Must specify length.

Modify table structure and add index

alter table tableName add index indexName(fieldName);

Create index when creating table

create table tableName(

ID int not null,

fieldName varchar(24) not null,

index[indexName](fieldName(length))

);

Delete index

drop index [indexName] on tableName

2, unique index

Create index

create unique index indexName on tableName(fieldName(length));

Modify table structure and add index

alter table tableName add unique index indexName(fieldName);

Create index when creating table

create table tableName(

ID int not null,

fieldName varchar(24) not null,

unique[indexName](fieldName(length))

);

3, Display index information

show index from tableName;

21, cursor

MySQL The temporary table is only valid under the current connection , The temporary table will be deleted automatically after the connection is disconnected . When using scripting language or client connection MySQL When creating a temporary table , End of script or client shutdown , Then the temporary table is deleted .

create temporary table tableName(

);

use

show tables;

Temporary table information will not be displayed .

22, Copy table

1, method 1

create table newTableName();

insert into newTableName (field1, field2, ...fiedlN)

select field1, field2, ...fiedlN from oldTableName

2, method 2

create table newTableName like oldTableName; # Copy table structure only

insert into newTableName select * from oldTableName; # Copy data in table

Technology