Basic operation of mysql database

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

Tags: Database SQL

Posted on Mon, 08 Nov 2021 17:18:47 -0500 by codygoodman