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