New data table

create table table01 (
id int auto_increment,
name varchar(20),
age int,
date date,
primary key (id))ENGINE=InnoDB DEFAULT CHARSET=utf8;

Add data

insert into table01(name,age,date)values('Zhuguangcun',18,NOW());

Query data

select id,name,age,date from table01;

Update data

update table01 set name='chenhan',age=20;

The difference between delete, truncate and drop

1. Delete and truncate only delete table data. Drop deletes table data and table structure together. For example, delete is a single kill, truncate is a group kill, and drop is a computer crash.
2. Delete is a DML statement. After the operation, if there is no transaction that you don't want to commit, you can roll back. truncate and drop are DDL statements that take effect immediately after the operation, and you can't roll back. For example, delete is sending a wechat message to say goodbye, and regret can also be withdrawn. truncate and drop are directly slapping and rolling, and you can't go back.
3. In terms of execution speed, drop>truncate>delete. For example, drop is the Shenzhou rocket, truncate is the harmony motor car, and delete is the bicycle

like clause

like clause% represents any string,%x% represents all strings containing x

• _: Represents any single character. Match a single arbitrary character, which is often used to limit the character length statement of an expression.
• []: represents one of the characters listed in parentheses (similar to regular expressions). Specify a character, string, or range that requires the matching object to be any of them.
• [^]: indicates a single character that is not listed in parentheses. Its value is the same as [], but it requires that the matched object be any character other than the specified character. When the query content contains wildcards, due to wildcards, we query the special characters "%", "_" The statement of "[" cannot be realized normally, but you can query normally by enclosing special characters with "[]".
  select id,name,age,date from table01 where name like '%en%';

Union and union all

The UNION operator is used to connect the results of more than two SELECT statements and combine them into a result set. Multiple SELECT statements will delete duplicate data.

difference

Union will merge the same rows, while union all will not merge; And the column names of the query must be the same one-to-one
select id,name,age,date
from table01
where name like '%en%'
union all
select id,name,age,date
from table01;

order by clause

ORDER BY clause to set which field and how you want to sort

• You can use any field as the sorting condition to return the sorted query results.
• You can set multiple fields to sort.
• You can use ASC or DESC keywords to set the query results to be arranged in ascending or descending order. By default, it is arranged in ascending order.
• You can add the WHERE... LIKE clause to set the condition.

  select id,name,age,date from table01 order by age desc; # Descending order
  select id,name,age,date from table01 order by age;#(ASC) #Default ascending order
  select id,name,age,date from table01 order by convert(name using gbk);# utf8 Chinese characters need transcoding to be sorted according to Pinyin
  
  
  

grouping

SET NAMES utf8;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
--  Table structure for `employee_tbl`
-- ----------------------------
DROP TABLE IF EXISTS `employee_tbl`;
CREATE TABLE `employee_tbl` (
  `id` int(11) NOT NULL,
  `name` char(10) NOT NULL DEFAULT '',
  `date` datetime NOT NULL,
  `signin` tinyint(4) NOT NULL DEFAULT '0' COMMENT 'Login times',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
--  Records of `employee_tbl`
-- ----------------------------
BEGIN;
INSERT INTO `employee_tbl` VALUES ('1', 'Xiao Ming', '2016-04-22 15:25:33', '1'), ('2', 'Xiao Wang', '2016-04-20 15:25:47', '3'), ('3', 'Xiaoli', '2016-04-19 15:26:02', '2'), ('4', 'Xiao Wang', '2016-04-07 15:26:14', '4'), ('5', 'Xiao Ming', '2016-04-11 15:26:40', '4'), ('6', 'Xiao Ming', '2016-04-04 15:26:54', '2');
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;

SELECT * FROM employee_tbl;

group by group

select name,count(*) from employee_tbl GROUP BY name;
# with rollup can continue to count a total on the basis of statistics
select name,sum(signin) as signin_count from employee_tbl group by name with rollup; # Here, with rollup counts the value in null
# You can use the coalesce(a,b,c) function to replace null, that is, if a==null, select b, b==null, and select C
select coalesce(name,"total"),sum(signin) as signin_count from employee_tbl group by name with rollup; 

Create a table to be used as a join

create table table02 (
	id int auto_increment,
	age int,
	sex varchar(20),
	PRIMARY key (id));
	
insert into table02(age,sex) values(23,"male");

Inner join inner join query

select a.name,a.age,b.sex from table01 a inner join table02 b on a.age = b.age;
#Left join left join query
select a.name,a.age,b.sex from table01 a left join table02 b on a.age = b.age;

right join right link query

select coalesce(a.name,"N/A") as name,b.age,b.sex from table01 a right join table02 b on a.age = b.age;

Regular expression mysql uses the REGEXP operator to represent regular expression matching

Match containing substring

select id,name from table01 where name REGEXP "h";
select id,name from table01 where name REGEXP "hch";

^Indicates matching from string

select id,name from table01 where name REGEXP "^chen";

$means match from the end of string

select id,name from table01 where name REGEXP "an$";

. match any single character except "\n". To match any character including '\n', use a pattern like '[.\n]'.

select id,name from table01 where name REGEXP "^.";
select id,name from table01 where name REGEXP "^c.";

[] match any of these characters

select id,name from table01 where name REGEXP "[c,Chen,living]";

[^] match any characters that are not included

select id,name from table01 where name REGEXP "[^chenhan,Chen Han,living]";

p1|p2|p3 matches p1,p2 or P3

select id,name from table01 where name REGEXP "chen|Chen Han";

*Match the previous expression 0 or more times

select id,name from table01 where name REGEXP "Chen*";

+Match the previous expression 1 or more times

select id,name from table01 where name REGEXP "Chen+";

{n} Make sure to match n times, and N is a non negative integer. Match the determined n times. For example, 'o{2}' can't match 'o' in "Bob", but it can match two o's in "food", but it can't match two o's in blobor.

select id,name from table01 where name REGEXP "h{1}";

{n, m} m>=n large, and n>=0, match at least N times, and at most m times

select id,name from table01 where name REGEXP "Chen{1,2}";
select id,name from table01 where name REGEXP "h{1,2}";

affair

BEGIN or START TRANSACTION explicitly starts a transaction;

COMMIT WORK can also be used, but the two are equivalent. COMMIT will COMMIT the transaction and make all modifications made to the database permanent;

ROLLBACK WORK can also be used, but the two are equivalent. ROLLBACK will end the user's transaction and undo all uncommitted modifications in progress;

SAVEPOINT identifier, SAVEPOINT allows to create a SAVEPOINT in a transaction, and there can be multiple savepoints in a transaction;

RELEASE SAVEPOINT identifier deletes the savepoint of a transaction. When there is no specified savepoint, executing this statement will throw an exception;

ROLLBACK TO identifier rolls back the transaction to the marked point;

SET TRANSACTION is used to set the isolation level of transactions. The InnoDB storage engine provides transaction isolation levels such as READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, and SERIALIZABLE.

There are two main methods for MYSQL transaction processing:

1. Using BEGIN, ROLLBACK, COMMIT to achieve
BEGIN starts a transaction
ROLLBACK transaction ROLLBACK
COMMIT transaction confirmation
2. Use SET directly to change the automatic submission mode of MySQL:
SET AUTOCOMMIT=0 prohibit automatic submission
SET AUTOCOMMIT=1 enable automatic submission

ALTER command

create table table03 (
	i int,
	c char);

Show all columns

show columns FROM table03;

Add or delete the fields of the table

Delete column i

alter table table03 drop i;

Add column i

alter table table03 add i int;

Add 1 column i to the first column

alter table table03 drop i;
alter table table03 add i int first;

After adding 1 column i to c

alter table table03 drop i;
alter table table03 add i int after c;

Modify field type and name

Use modify to modify the field type

alter table table03 MODIFY c char(10);

Use change to modify, followed by the original field name, the new field name, and the new field type

alter table table03 change i j varchar(10);
alter table table03 change j j int;

alter modify whether null is allowed

alter table table03 modify j int not null DEFAULT 100;

Modify field defaults

alter table table03 alter j set default 200;

Delete field defaults

alter table table03 alter j drop default;

Modify storage engine

ALTER TABLE table03 engine = MYISAM;
show table status like 'table03';
ALTER TABLE table03 engine = INNODB;

Modify table name

alter table table13 rename to table03;

Delete foreign key constraint

alter table table03 drop foreign key keyname;# keyname indicates foreign key alias

Indexes

Create index

create index indexName on table03(j);

Display index

show index from table03;

Modify table structure (add index)

alter table table03 add unique uniqueName(j);

Delete index

drop index indexName on table03;

There are four ways to add indexes to data tables

1. ALTER TABLE tbl_name ADD PRIMARY KEY (column_list): this statement adds a primary key, which means that the index value must be unique and cannot be NULL.
2. ALTER TABLE tbl_name ADD UNIQUE index_name (column_list): the value of the index created by this statement must be unique (except for NULL, which may appear many times).
3. ALTER TABLE tbl_name ADD INDEX index_name (column_list): add a common index, and the index value can appear multiple times.
4. ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list): this statement specifies that the index is fulltext, which is used for full-text indexing

Add primary key

ALTER TABLE table03 ADD PRIMARY KEY (i);

Delete primary key

ALTER TABLE table03 drop PRIMARY KEY;

Query commands for creating tables

show create table table03;

Copy table

create table table04 like table01; # 1 include structure but not data
create table table04 select * from table01 where 1 = 2; # 2 include structure but not data
create table table04 select * from table01; # Copy new tables, including structure and data
show columns FROM table04;
drop table table04;

stored procedure

advantage
Stored procedures can be encapsulated and hide complex business logic.
Stored procedures can return values and accept parameters.
A stored procedure cannot be run using the SELECT instruction because it is a subroutine, unlike a view table, data table, or user-defined function.
Stored procedures can be used for data validation, enforcing business logic, and so on.
shortcoming
Stored procedures are often customized to a specific database, because the supported programming languages are different. When switching to the database system of other manufacturers, the original stored procedures need to be rewritten.
The performance adjustment and writing of stored procedures are limited by various database systems.
Delimiter # declaration of temporary statement terminator can be followed by $$, / / and so on, and then delimiter can be used; End the use of temporary Terminator
create procedure function_name(in @x); – Define a stored procedure
– in, out and inout represent input, output and inputable output respectively
– @x is a variable
A stored procedure is similar to a function. It can define and write the procedure body to realize the call.

Symbols for the beginning and end of stored procedures

begin...end

Variable assignment

set @x = 1;

Variable definition

declare @x int unsigned default 2000;

Create MySQL stored procedures and functions

create procedure stored procedure name (parameter)

Stored procedure body

create function store function name (parameter)

Example

mysql> delimiter $$　　#Change the ending symbol of the statement from semicolon; Temporarily changed to two $$(can be customized)
mysql> CREATE PROCEDURE delete_matches(IN p_playerno INTEGER)
    -> BEGIN
    -> 　　DELETE FROM MATCHES
    ->    WHERE playerno = p_playerno;
    -> END$$
Query OK, 0 rows affected (0.01 sec)
 
mysql> delimiter;　　#Restore the closing symbol of the statement to a semicolon


## Control statement
if-then-else-end if
case-var-when-when-else-end case
case-when-when-else-end case
while...do...end while
repeat...until...(Circulatory body)end repeat
loop...end loop

Example

mysql > DELIMITER //  
mysql > CREATE PROCEDURE proc6 ()  
     -> begin 
     -> declare v int;  
     -> set v=0;  
     -> LOOP_LABLE:loop  
     -> insert into t values(v);  
     -> set v=v+1;  
     -> if v >=5 then 
     -> leave LOOP_LABLE;  
     -> end if;  
     -> end loop;  
     -> end;  
     -> //  
mysql > DELIMITER ;

LABLES label

Labels can be used before begin repeat while or loop statements, and statement labels can only be used before legal statements. You can jump out of the loop and make the running instruction reach the last step of the compound statement.