mysql

1. Operation on Database

create

create database NAME
character set UTF8
collate XXX;

alter

alter database NAME
character set UTF8
collate XXX;

drop

drop database NAME;

show

show databases;

2. Table operation

create

create table TABLENAME(
VARNAME_1 datatype not null ,
name varchar(10) not null,
gender tinyint(1) not null default 1,
primary key(varname_1)
)engine=innodb default charset=gbk;

Copy table:

create table employee_1 like employee;//Replication structure has no data
create table employee_1 as (select* from employee);//Copy structure and data

alter

alter table TABLENAME
[options];
alter table dbem add userid int(10)null;
alter table is used to change the structure of the table, not the data

• Add (add column)

• Direct set charset=

• change (column name redefinition)

  alter table department change departname nameafter tinyint(2);

• Modify (modify data type)

  alter table department modify COLNAME char(2);

• drop (delete column)

  alter table department
  drop COL_1,
  drop COL_2;

• Rename (rename table)

  alter table department rename to NAME_2;
  You can also modify the table name directly:
  rename table department to NAME_2;

• order by

drop

drop table department;

3. Data operation

insert

insert into TABELNAME values(10002,"Zhang San",1);(Insert new record)
insert into TABLENAME select*from TABLE2 where id=1001;(Select data from existing tables (insert)

update

update TABLENAME 
set id=1001
where name="Zhang San";(Basic usage)

update TABLENAME  set dpartid=case empid
when 1001 then 5
when 1002 then 4
end
where empid in(1001,1002,1003);

delete

delete from TABELNAME
where id=1001;

4. Relational operation (select)

Basic: Select... From... Where

select name,gender
from employee
where salary>10000;

De duplicate rows: distinct

select distinct depart,gender
from employee;

Modify display name: as

select address as address
from employee;

Aggregate sum function

COUNT finds the number of items in the group and returns an integer of type int
MAX Max
MIN minimum
SUM returns the SUM of all values in an expression
AVG average the group median
STD or STDDEV returns the standard deviation of all values in a given expression
VARIANCE returns the VARIANCE of all values in a given expression
GROUP_CONCAT returns the result bit composed of column value connections belonging to a group_ And logical or
BIT_OR logic and
BIT_XOR logical XOR

• count

  select gender ,count(gender) as num
  from employee
  group by gender;
  

• max

  select max(income-outcome),min(income-outcome)
  from salary
  where employeeid in (
  select employeeid from employee where departmentid=1);
  

• min

• sum

• avg

Query mode

• Subquery: select nested

  select *from employee
  where departmentid in(
  select departmentid 
  from department 
  where departname="Finance Department");
  

• Join query: join on

  select* from employee join salary on employee.employeeid=salary.employeeid
  where id =1001;
  

  • Inner connection

    Keyword: inner   join    on
    Statement: select * from a_table a inner join b_table b on a.a_id = b.b_id;
    Note: combine the records in two tables and return the records matching the associated fields, that is, the intersection (shadow) of the two tables.

  • External connection

    • Left outer connection

      Keywords: left join on / left outer join on

      Statements: SELECT  * FROM a left join b ON a.a_id = b.b_id;

      Note: left join is the abbreviation of left outer join. Its full name is left outer join, which is one of the outer joins. For the left (outer) connection, the records of the left table (a_table) will be displayed, while the right table (b_table) will only display the records that meet the search criteria. The insufficient records in the right table are NULL.

    • Right outer connection

      Keywords: right join on / right outer join on

      Statements: SELECT  * FROM a right outer join b on a.a_id = b.b_id;

      Note: right join is the abbreviation of right outer join. Its full name is right outer join, which is one of the outer joins. Contrary to the left (outer) connection, the right (outer) connection, the left table (a_table) will only display the records that meet the search criteria, while the right table (b_table) will display all the records. The insufficient records in the left table are NULL.

  • Full connection

    Keyword: union /union all

    Statement: (select columb1, columb2... Columbn from tableA) Union (select columb1, columb2... Columbn from tableb)

    Or (select columb1, columb2... Columbn from tableA) union all (select columb1, columb2... Columbn from tableb);

    Precautions for union statement:

    1. For SQL connected through the union, the number of columns fetched separately must be the same;

    2. It is not required that the merged table column names are the same, and the first sql table column name shall prevail;

    3. When using Union, completely equal rows will be merged. Because merging is time-consuming, union is generally not used directly for merging, but union all is usually used for merging;

    4. For the sql clauses connected by the union, there is no need to write order by in a single clause, because there will be no sorting effect. However, the final result set can be sorted;

    (select id,name from A order by id) union all (select id,name from B order by id); // No sorting effect

    (select id,name from A ) union all (select id,name from B ) order by id; // Sorting effect

    	union Duplicate columns are deleted, union all All columns are displayed, including duplicate columns
    

Grouping: group by

Sorting: order by

having

select departname,count (employee.departmentid) as num
where employee join department on employee.departmentid=department.departmentid
group by employee.departmentid
having num>2;

other

limite

5. view

1. (easy for users to use) centralize data for users and simplify users' data query and processing. Sometimes the data required by users are scattered in multiple tables. Defining views can centralize them, so as to facilitate users' data query and processing
2. (the database is easy to use) mask the complexity of the database. Users do not have to understand the table structure in a complex database, and changes to database tables do not affect users' use of the database
3. (Security) simplify the management of user rights. It only needs to grant the user permission to use the view without specifying that the user can only use specific columns of the table, which also increases security
4. Facilitate data sharing. Each user does not have to define and store the data they need, but can share the data of the database, so that the same data only needs to be stored once
5. Data can be reorganized for export to other applications

Non updatable view

If the view contains one of the following structures, the view cannot be updated (that is, the original table cannot be modified by changing the view, and the data of the original table can be changed by inserting, updating, delete and other commands)
(1) Aggregate function;
(2) (2) DISTINCT keyword;
(3) (3) GROUP BY clause;
(4) ORDER BY clause;
(5) HAVING clause;
(6) UNION operator;
(7) Sub query in the selection list;
(8) The FROM clause contains multiple tables;
(9) A non updatable view is referenced in the SELECT statement;
(10) The subquery in the WHERE clause refers to the table in the FROM clause;

Creating a view using create view v as select* from table is actually create view v as select id as id,name as name; Therefore, the view table structure cannot be modified, but it has been changed in the background.

create

create view v_dp as
select name,gender from department;

drop

6. index

InnoDB is the default engine of the system and supports reliable transaction processing.
Use the following statement to modify the default storage engine for database staging

• InnoDB supports transactions, row level locking, B-tree, full text and other indexes, but does not support Hash indexes;
• MyISAM does not support transactions, supports table level locking, supports B-tree, full text and other indexes, and does not support Hash indexes;
• Memory does not support transactions, supports table level locking, supports B-tree, Hash and other indexes, and does not support full text indexes;
• NDB supports transactions, row level locking, Hash indexes, but not B-tree, full text and other indexes;
• Archive does not support transactions, supports table level locking, and does not support B-tree, Hash, full text and other indexes;

General index

Purpose: speed up search

create index name on s1(name); #Add normal index

unique index

• Primary key index: primary key: accelerated search + constraint (not empty and unique)

• Unique index: unique: accelerated lookup + constraint (unique)

  alter table s1 add primary key(id); #Adding a residential index means adding a primary key constraint to the id field
  create unique age on s1(age);Add unique index
  

Joint index

• Primary key (id, name): the federated primary key index
• Unique (id, name): Union unique index
• Index (id, name): Union common index
• create index name on s1(id,name); # Add common union index

Full text index fulltext

It works best when used to search for a long article

create,drop

7. Referential integrity

MySQL referential integrity is generally achieved through MySQL foreign keys.
The column of the table referenced by the foreign key (only supported by innoDB) must be a primary key.
The foreign key declaration consists of three parts:
A. Which column or column combination is a foreign key
B. Specify the tables and columns that the foreign key references
C. Refer to actions [cascade, restrict, set null, no action, set default].
If the foreign key constraint specifies a reference action, the primary table record will be modified or deleted, and the columns referenced from the table will be modified accordingly, or not modified, rejected or set as the default value.
The column name of the reference table must be a primary key, and the reference relationship or the current table must be deleted when deleting the primary table.
--Many to many relationship

--Create a teacher table

CREATE TABLE teacher (
    id INT,
    NAME VARCHAR (20) NOT NULL,
    gender CHAR (2) NOT NULL,
    PRIMARY KEY (id)
);

--Create student form

CREATE TABLE student (
    id INT,
    NAME VARCHAR (20) NOT NULL,
    age INT NOT NULL
);
ALTER TABLE student ADD PRIMARY KEY (id);

--The third relationship table

CREATE TABLE tch_std (
    teacher_id INT,
    student_id INT,
    CONSTRAINT fk_teacher FOREIGN KEY (teacher_id) REFERENCES teacher (id),
    CONSTRAINT fk_student FOREIGN KEY (student_id) REFERENCES teacher (id)
);

8. Stored procedure

delimiter $$
create procedure emnum
begin
declare num int;
select count(*) into num from employee;
select num;
end $$
delimiter :

create

delimiter $$
create procedure comp(in id1 int, in id2 int)
begin
declare num1 double;
declare num2 double;
declare comp int;
select income-outcome into num1 from salary where employeeid=id1;
select income-outcome into num2 from salary where employeeid=id2;
if(num1>num2)
then set comp=0;
else set comp=0;
select comp;
end if;
end $$
delimiter ;

call

call comp(1001,1002);

drop

drop procedure comp;

9. trigger

SQL statements in MySQL triggers can be associated with any column in the table. However, you cannot directly use the name of the column to mark, which will confuse the system, because the statement that activates the trigger may have modified, deleted or added a new column name, and the old name of the column exists at the same time. Therefore, the syntax must be used to mark "NEW.column_name" or "OLD.column_name". NEW.column_name is a column used to refer to the new row, OLD.column_name is used to refer to a column of existing rows before updating or deleting it.

For INSERT statements, only NEW is legal; For DELETE statements, only OLD is legal; The UPDATE statement can be used simultaneously with NEW or OLD.

create

delimiter $$
create trigger emdelete after delete on employee for each row
begin
delete from salary where employeeid =old.employeeid;
end $$
delimiter ;

drop

drop trigger emdelete;

10. Import and backup

Import load

LOAD DATA LOCAL INFILE 'dump.txt' INTO TABLE mytbl
  -> FIELDS TERMINATED BY ':'
  -> LINES TERMINATED BY '\r\n';
use load
$ mysqlimport -u root -p --local mytbl dump.txt
password *****

use mysqlimport

Backup select into

SELECT a,b,a+b INTO OUTFILE '/tmp/result.text'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM test_table;

11. Authority management

Create user

create user 'username'@'localhost' identified by '123456';

Modify user

use mysql;
select user from mysql.user;
update user set user='user_3' where user='user_2';
alter user 'user_3'@'localhost' identified by'111111';

Authority granting

grant select,insert,update, delete on employee to user_3@localhost;
flush privileges;
show grants for user_3@localhost;
grant grant option on tablename to user_3@localhost;

Revoke permissions

revoke select on employee from user_1@localhost;

12. Concurrency control

Concurrency control problem

• Lost modification: when two transactions select the same row and update the row based on the initially selected value, because each transaction does not know the existence of other transactions, the last update will overwrite the updates made by other transactions, resulting in data loss
• Dirty reading: when a transaction is accessing data while other transactions are updating the data but have not yet committed, dirty reading will occur, that is, the data read by the first transaction is "dirty"
• Non repeatable reading: the same data is read multiple times in a transaction. When the transaction is not finished, other transactions also access and modify the data, resulting in different data read twice in the first transaction
• Phantom read: when a transaction inserts or deletes a row, because the row is within the read range of another transaction, one more row is read or one row disappears twice

quarantine

Level 4 isolation level: defines the degree of isolation and interaction between users

• Read uncommitted: you can read the uncommitted data of other transactions, and only avoid losing modifications, which will lead to a large number of data changes. Level 1 blocking protocol
• Read committed: you can see the new records added by other transactions, and the modifications made by other transactions to existing records. Once committed, you can also see the secondary blocking protocol to avoid losing modifications and dirty reads
• Repeatable read: the changes of the currently executing transaction cannot be seen. The results of select ing several times in the same transaction are the same, so as to avoid losing modification, dirty reading and non repeatable reading. It is an enhanced secondary blocking protocol
• Serializable: users execute transactions one by one to avoid all concurrency control problems, maximize isolation, and three-level blocking protocol

affair

Correct submission
begin;
insert into tablename...;
insert into tablename...;
commit;
RollBACK 
begin;
insert ...
insert...
rollback;

• Atomicity: all operations in a transaction are either completed or not completed, and will not end in an intermediate link. If an error occurs during the execution of a transaction, it will be rolled back to the state before the start of the transaction, as if the transaction had never been executed.
• Consistency: the integrity of the database is not destroyed before and after the transaction. This means that the written data must fully comply with all preset rules, including the accuracy and serialization of the data, and the subsequent database can spontaneously complete the predetermined work.
• Isolation: the ability of a database to allow multiple concurrent transactions to read, write and modify their data at the same time. Isolation can prevent data inconsistency caused by cross execution when multiple transactions are executed concurrently. Transaction isolation is divided into different levels, including Read uncommitted, read committed, repeatable read, and Serializable.
• Persistence: after the transaction is completed, the modification of data is permanent and will not be lost even if the system fails.