MySql
1) Data Definition Language (**DDL * *) such as database and table building
2) Data Manipulation Language(**DML * *), such as adding, deleting and modifying records in a table
3) Data Query Language(**DQL * *), such as query operations in tables
4) Data Control Language(**DCL * *), such as setting user permissions
Addition, deletion, query and modification of DDL database
increase
Create Library
create database Library name;
Judge to create Library
-- Determine whether this database name exists,This database was not created create database if no exists Library name;
Delete
Delete Library
drop database Library name;
Judge to delete Library
-- Determine whether this database name exists,Delete this database if necessary drop database if exists Library name;
check
Query all library names
show databases;
Query the default character set for creating libraries
show create database Library name;
Query all character set formats with character sets in the database
show variables like '%character%'; /* If Chinese garbled code appears in the operation table (DML statement) on the dos console Change character_set_client -- become utf8 character_set_results -- Become utf8 */
change
Modify the default character set of the library
-- Change the library name character set to GBK alter database Library name character set gbk ;
Addition, deletion, query and modification of DDL table
Before creating a table, first select the library and enter the library
Field type
--Integer:
tinylnt Microinteger (8-bit binary)
smallint small integer (16 bit binary)
Integer in mediumint (24 bit binary)
int(integer) integer (32-bit binary)
--Decimal:
float single precision floating point number (4 bytes)
Double double precision floating point number (8 bytes) -- double(3,2): the value of this field is 3 digits, and 2 digits are reserved after the decimal. Example: 3.56
character string:
char(m) fixed length string. No matter how many characters are used, it is full. M is an integer between 0 and 255
varchar(m) variable length string. A few characters take up a few. M is an integer between 0 and 65535
--Date:
Time indicates the time type
Date indicates the date type
datetime represents both date and time types
timestamp (instant time) when you insert a specified time into this field, the current date + time
--Large binary:
tinyblob allowed length 0 ~ 255 bytes
The allowed length of Big Large Object is 0 ~ 255 bytes
blob allowed length ~ 65535 bytes
The allowable length of mediumblob is 0 ~ 167772150 bytes
The allowable length of longblob is 0 ~ 4294967295 bytes
--Large text:
tinytext allows a length of 0 ~ 255 bytes
Textallowable length 0 ~ 65535 bytes
The allowable length of mediumtext is 0 ~ 167772150 bytes
longtext Allowable length: 0 ~ 4294967295 bytes
Warehousing
-- Enter this library use Library name;
increase
Create table
create table Table name( Field name 1 field type 1, Field name 2 field type 2, .... Field name n Field type n );
Add new column
alter table Table name add New field name field type;
Copying a new table has the same structure as the old table
create table New table name like Old table name
Delete
Delete table
drop table Table name;
check
Query all tables in the library
show tables;
Query table structure
desc Table name;
change
Modify the field name of the table
alter table Table name change Old field name new field name data type;
Modify field type
alter table Table name modify Field name new field type;
Delete a column
alter table Table name drop Field name;
Modify table name
alter table Old table name rename to New table name;
Addition, deletion, query and modification of DML table
increase
insert into sentence; -- Grammar one (Insert all data at once) insert into Table name values(Value for field name 1,Value for field name 2,...); -- Grammar II (Insert partial data) insert into Table name(Field name 1,Field name 2..)values(Value 1,Value 2..); -- Grammar III (Multiple records can be inserted at one time, which is applicable to all table fields and some fields) insert into Table name(field value) values(Value 1,Value 2...),(Value 1,Value 2..)..;
be careful
When inserting a field value, it must correspond to the sequence of the current field
Delete
-- Grammar one (Delete the columns that meet the criteria) delete from Table name where Conditional deletion(Field name = value); -- Grammar II (Delete full table data,The table structure will not be deleted,Constraint fields for self growth are not affected,For example, the previous table pair id Set self growth,Previous table id End with 5,The new table starts at 6) delete from Table name; -- Grammar III (Delete full table data,The table structure will not be deleted,But recreate as like as two peas.,For example, the previous table pair id Set self growth,Previous table id End with 5,The new table starts with 1) truncate table Table name;
Multi table query and transaction operation DQL
Query all field values
-- Grammar one (Under development,Can't write*,All field names need to be written above) select * from Table name; -- Grammar II select Field name 1, Field name 2, .. from Table name;
Specify an alias when querying a specified field
select (as Can be omitted) Field name 1 as 'Alias 1', Field name 2 as 'Alias 2', .. from Table name;
Alias table
select -- Multi table query Table alias.Field name 1, Table alias.Field name 2, from Table name table alias; -- Alias table (as Can be omitted)
Query one or more fields
select Field name 1, Field name 2, ... from Table name;
De duplication when querying fields
select distinct(Field name) from Table name;
Query the sum of two or more fields
select (Field name 1+Field name 2) from Table name;
be careful:
The data types of these fields should be consistent. If the value of one field is null, the result is null. You can use * * ifnull * * (field name, expected value);
select (Possible values are null Field name for,When the value is null The default value is 1) Field name 1+ifnull(Field name,Value 1) from Table name;
Condition query
select Field name from Table name where condition;
Comparison operator
> < <= >= = <> !=(mysql)
<> It is not equal to in sql. It can also be used in mysql=
between value 1 and value 2
Between values 1 and 2, the header and footer
In (set)
A collection represents multiple values, separated by commas
like 'Zhang%'
Fuzzy query
is null
Query the null value of a column, cannot write = null
is not null
Query the value of a column that is not null
Logical operator
--The former is recommended in SQL and the latter is not common
and &&
--Or the pre and post conditions meet one of them
or ||
--Not
not !
in keyword
-- in Every data in it will be used as a condition,As long as the conditions are met, it will be displayed select Field name from Table name where field in(Data 1,Data 2..); -- Display if conditions are not met select Field name from Table name where field not in(Data 1,Data 2..);
Mysql wildcard like keyword
-- Match any number of strings % -- Query students surnamed Su select * from student where name like 'Soviet%'; -- Query students whose names contain Su select * from student where name like '%Soviet%'; -- Match a string _(Lower horizontal bar) -- Query surname Su,And a student with a three word name select * from student where name like 'Soviet__';
sort
order by
asc (ascending)
desc (descending)
select field list from table name order by Field name sorting method;
SELECT * FROM student ORDER BY grade ASC;
Aggregate function
Count count
Count (field name: generally used non business field). If the value is null, this value will not be counted
max
min
sum
avg average
Paging query
--select field list from table name limit the number of starting rows (starting index) and the number of entries displayed on each page;
--Number of starting lines (starting index): calculated from 0 = (current page number - 1) * number of entries displayed per page
The starting value of limit starts from 0 and the length is;
SELECT * FROM student ORDER BY grade ASC -- limit 0,2 LIMIT 4,2 ;
Grouping query
group by Grouping column having Filter condition; group by Not behind where sentence,where Must be group by before; SELECT class, COUNT(id), AVG(grade) FROM student GROUP BY class;
Constraint keyword
-- Foreign key constraint basis,Cascading operation is possible(Cascade modification and cascade deletion)CASCADE -- Primary key (Non empty and unique) primary key -- only (After setting,The field value cannot be repeated) Unique -- Non empty (After setting,The field value cannot be assigned null) not null -- default Default -- Self growth needs to be used with primary keys primary key auto_increment(It starts from the field row by default,Increment 1 from 0,Move straight ahead) auto_increment
Foreign key
-- Associate the values of the master table with the values of the slave table foreign key
Format
CONSTRAINT name -- statement Foreign key name following(Can be omitted) FOREIGN KEY (The associated field name is required from the table) -- Act on foreign keys REFERENCES Main table name(Field name associated with the main table) -- Primary key associated with the primary table id
Delete foreign key constraint
If two tables are associated, you cannot directly add or change the slave table
alter table Table name drop foreign key The name of the foreign key
cascade operation
When the user operates the master table, the data of the slave table related to the master table will also change;
If not used: for modification or deletion, you should first execute the slave table and then the master table;
on delete cascade -- delete on update cascade -- modify
CONSTRAINT name -- statement Foreign key name following(Can be omitted) FOREIGN KEY (The associated field name is required from the table) -- Act on foreign keys REFERENCES Main table name(Field name associated with the main table) -- Primary key associated with the primary table id ON DELETE CASCADE ON UPDATE CASCADE ;
change
-- Grammar one (take id In the field with value 2, the value in the field name is changed to value 2) update Table name set Field name = Value 1 where Other fields = value(In the later stage id = Value 2); -- Grammar II (Modify multiple fields)(Modify the names of other fields that meet the values of other fields to the value 1,Value 2) update Table name set Field name 1 = Value 1,Field name 2 = Value 2,..where Conditional statement(Other fields = value); -- Grammar III (Batch modification Not recommended)(Change all values under the field name in the table to the value 1) update Table name set Field name = Value 1;
Three paradigms of database
First paradigm
Each column in the database table is a non separable atomic data item (an independent column)
Second paradigm
On the basis of meeting the first normal form, the non primary key field (column) of each table must be completely dependent on the primary key field, and cannot be partially dependent!
Third paradigm
On the basis of meeting the second normal form (the non primary key field must completely depend on the primary key field), the non primary key field cannot generate transfer dependency,
Foreign key constraints: eliminate transitive dependencies / and reduce field redundancy
multi-table query
Internal connection query
Implicit inner connection
Explicit inner join
External connection query
Left outer connection
Right outer connection
Subquery
Single row and single column
Multi row and multi column
Complex query
Inner connection
Implicit inner connection (recommended)
where statement as a condition
select Field list 1)Which fields to query from Table name 1,Table name 2 2)Which tables are queried where Connection conditions; 3)Connection conditions
Explicit inner join
select Field category from Table name 1 inner join Table name 2 on Connection conditions;
External connection
When the value in the table is null, an outer join is required
Left outer connection (recommended)
select Field list from Table name 1 left outer(outer Can be omitted) join Table name 2 on Connection conditions;
Right outer connection
select Field list from Table name 1 right outer join Table name 2 on Connection conditions;
Subquery
Single case
Select statement nested select statement implementation
Multi row and multi column
Using or or in statements
Complex query
View (virtual table)
When modifying or adding / deleting, the basic data may be changed,
But not all execution can be operated!
Create view
create view View name as select Field name from Basic table (where Conditional statement);
Delete view
drop view View name ;
Query table type
SHOW FULL TABLES ;
Modify view
alter view View name(Virtual table name) as select sentence Condition query with check option ;
In some cases, the view cannot be modified:
1. When aggregate functions are basically used
2. System or user variables cannot be used in the view
1. mysql8.0 query isolation level
2. selecct @@tx_isolation
3. View cannot be created in temporary table
Create temporary table
create temporary table Temporary table name(Field name field type);
affair
concept
During the execution of a business function, when the business function executes multiple sql statements at the same time, the execution process of multiple sql statements is regarded as a whole, either successful or failed at the same time!
Open transaction
start transaction ;
Rollback transaction
-- When the transaction result fails,Roll back rollback ;
Commit transaction
-- Result success,Submit commit ;
Transaction characteristics
ACID:
Traditional transaction
A atomicity:
A transaction is an inseparable whole!
C consistency:
If the total amount of transactions remains unchanged before and after the operation of business data, the total amount is the same before and after the transfer
I isolation:
Transactions and transactions - that is, a business and a business are independent and cannot affect each other
D persistence:
Once the commit is submitted for data modification, the data operation is permanent. Even if the server is shut down and / or down, the next boot still exists!
Isolation level of transaction
Query isolation level
SELECT @@transaction_isolation ;
Set isolation level
set global transaction isolation level Name of the level;
Five isolation levels
read uncommitted;
Read uncommitted has the worst security and high execution efficiency. Dirty reads will occur (the most dangerous and serious)
read committed ;
Compared with the first security, read committed security prevents dirty reads, but non repeatable reads occur
repeatable read;
The default level of mysql that can be read repeatedly can prevent dirty reading and cannot be read repeatedly
serializable ;
Serial voice has the highest security
Unreal reading