Build table statements

set names utf8mb4;
use f_algo_platform;
CREATE TABLE `result_table` (
   `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Increment primary key ID',
    `prec`  VARCHAR(100) NOT NULL DEFAULT ' ' COMMENT 'Accuracy',
    `acc` DOUBLE NOT NULL DEFAULT 0 COMMENT 'Precision', ` model_path `VARCHAR(100) NOT NULL DEFAULT ' ' COMMENT 'Model storage address'.PRIMARY KEY (`id`),
    UNIQUE KEY `uniq_task_id` (`task_id`)
  ) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COMMENT = 'Algorithm Result table';
Copy the code

DQL query statement

show databases ;
show tables ;

use myemployees;
select * from employees;
select * from departments;
select * from locations;
select * from jobs;
show columns from employees;
desc employees;
Copy the code

Step 1: Basic query

/* select * from table name; Can query the fields, constant values, expressions, functions in the table
#1.Query a single field in the tableselect last_name from employees;
#2.Query multiple fields in the tableselect last_name,email from employees;
select
    employee_id,
    first_name,
    last_name,
    phone_number
from employees;
# 3.Use emphasis numbersselect
    `employee_id`,
    `first_name`,
    `last_name`,
    `phone_number`
from employees;
#4.Query all fields in the tableselect * from employees;
#5.Query constant valuesselect 100 from employees;
select 'john';
#6.Query expressionselect 100%98;
#7.Query functionselect version();
#8.Alias the field # method oneselect 100%98 asResults;select last_name as 姓 fromemployees; Method # 2selectLast_name surnamefromemployees; SQL > select * from salary where salary is displayed as outputselect salary as 'out put' from employees;
#9.duplicate removalselect department_id from employees;
select distinct department_id from employees;
#10.+The effect of no.-Example: Query the name of an employee and link it into a field that is merged to display as the nameselect last_name+first_name 'name'fromemployees; # No resultselect 100+90;
select '100'+88; # converts a string to a numeric value, and continues with addition,select 'ha ha'+98; If the conversion fails, the character is converted to0.select null+98; # If there is anull. The result isnull
#11.Concatenation of stringsselect concat(last_name,first_name) 'name' from employees;
#12.Ifnull functionselect ifnull(commission_pct,0) 'Bonus rate' from employees;
Copy the code

Advanced 2 Conditional query

/* select * from (select * from (select * from (select * from)); Filter conditional operators by conditional expression: <,>,=,! = (< >) is small and big, < =, > = 2, according to the logical expression filter logic expressions: and, or, not three, fuzzy query like, between and, in, is null * /
#1.Filter by conditional expressionselect * from employees where salary>12000;
select last_name,department_id from employees where department_id! =90;
select last_name,department_id from employees where department_id<>90;
#2.Filter by logical expression to link multiple conditions togetherselect last_name,salary,commission_pct from employees where salary>10000 and salary<20000;
select * from employees where department_id<90 or department_id>110 or salary>15000;
select * from employees where not(department_id> =90 and department_id< =110) or salary>15000;
#3.likeFuzzy query/* Generally used with wildcards: % Any character containing 0 characters _ Any single character */
select * from employees where last_name like '%a%';
select last_name,salary from employees where last_name like '__n_l%';
select first_name,last_name,salary from employees where last_name like '_ \ _ %'; # Use escape charactersselect first_name,last_name,salary from employees where last_name like '_a_%' escape 'a'; # specifies a as the escape character #4.between and
/* Between and is a closed interval */
select first_name,last_name,department_id from employees where department_id between 0 and 100;
#5.in
The value types in the IN class table must be consistent or compatible. The wildcard */ is not supported
select * from employees where job_id in ('IT_PROG'.'AD_VP'.'AD_PRES');
#4.is null
/* = or <,> Cannot determine null */
select first_name,last_name,commission_pct from employees where commission_pct is null ;
select first_name,last_name,commission_pct from employees where commission_pct is not null ;
#5.Security is equal to the/* <=> Can be used to check for NULL, and can also be used to check for common values. Is NULL can only check for NULL. <=> Can check for common values
select first_name,last_name,commission_pct from employees where commission_pct < = > null ;
select first_name,last_name,commission_pct from employees where commission_pct < = > 0.2 ;
Copy the code

Advanced 3 Sort the query

/ * select * from table name where the order query condition by sorting list (asc | desc) features: Order by, order by, order by, order by, order by, order by, order by
select * from employees order by salary;
select * from employees order by salary desc;
select * from employees order by salary asc;

select * from employees where department_id > = 90 order by hiredate asc;

select salary*12*(1+ifnull(commission_pct,0) -- earningfrom employees order byAnnual salarydesc;

select last_name,salary from employees order by length(last_name) desc,salary;

select last_name,salary,employee_id from employees order by salary,employee_id desc;

select last_name,department_id,salary*12*(1+(ifnull(commission_pct,0)))from employees order byAnnual salarydesc,last_name;
select last_name,salary from employees where not (salary between 8000 and 17000) order by salary desc ;
select * from employees where email like '%e%' order by length(email) desc,department_id asc;
Copy the code

Advanced 4 Common functions

/* Function: similar to Java methods, encapsulate a set of logical statements in a method body, exposing the method name benefits: 1. Call: select function name (argument list) from table name; Single line function: processing function (character function) concat, length, IFNULL Grouping function: statistics use, (statistics function, aggregate function) */# character function #1.Length () Gets the number of bytes for the parameter valueselect length('join');
select length('Zhang SAN Feng');
#2.Concat () concates a stringselect concat(last_name,The '-', first_name) namefrom employees;
#3.upper,lower
select upper(last_name) from employees;
select lower(first_name) from employees;
select concat(upper(last_name),'_'.lower(first_name)) from employees;
#4.Substr, subString Intercepts a character stringselect substr('Qunar Holiday Department'.5.2);
select substr(last_name,1.2) ,last_name from employees;
select concat(upper(substr(last_name,1.1)),lower(substr(last_name,2))) from employees;
#5.Instr returns the index of the string in the stringselect instr(Qunar.'net') as output;
select instr(Qunar.'xie') as output;
#6.Trim removes whitespace, removing only the leading and trailing whitespaceselect trim(' aa bb ') as output;
select trim('a' from 'hi aaaaaaaaaaaaaaa aaaaa) as output;
#7.Lpad, rpad fills the string with the specified character to the specified lengthselect lpad('Uncle In'.10.The '*') as output;
# 8.The replace replaceselect replace('Zhang Wuji falls in love with Zhou Zhiruo'.'Zhouzhi Ruo'.'zhao') asoutput; # Mathematical function #1.Round Is rounded to an integerselect round(1.45);
select round(1.5678.2); Take two decimal places2.Ceil rounded upselect ceil(1.002);
#3,floor is rounded downselect floor(1.999);
#truncatetruncationselect truncate(1.9999.1); Take more than # modselect mod(10.3);
select mod(10.- 3); # date function #1.Now () returns the current system timeselect now();
#2.Curdate () Returns the current system date, excluding timeselect curdate();
#3,curtime() Returns the current system time, excluding the dateselect curtime();
#4Gets the specified hour, minute, and secondselect YEAR(2020- 11- 11);
#5,str_to_date
select str_to_date('9-13-1999'.'%m-%d-%Y');
select date_format('1999-6-12'.'%Y-%m-%d'); # other functionsselect version();
select database();
select user(a); # flow control function #1.The if function s implements ifelseThe effect ofselect if(10>5.'big'.'small');
select last_name,if(commission_pct is null.'you'.'wu') from employees;
#2.caseUse of the function # use one/* switch (constant or expression){case constant 1: statement 1; break; Case constant 2: statement 2; break; } select * from mysqld; select * from mysqld; select * from mysqld; When constant 2 then statement to display; . Else The statement to display end */
selectSalary original salary,department_id,case department_id
           when 50 then salary*2
           when 60 then salary*1.2
           when 70 then salary*1.3
           else salary
           endThe new salaryfromemployees; # using two/* case when conditional 1 then value 1 when conditional 2 then value 2 when conditional 3 then value 3 else End */
select salary,
       case
           when salary > 10000 then 'A'
           end
from employees;

select now();
select employee_id,last_name,salary,salary*1.2 from employees;
select length(last_name),substr(last_name,1.1),last_namefrom employees order byThe first character;select concat(last_name,'earns',salary,'monthly but wants',salary*3 ) from employees;
select job_id,
       case job_id
           when 'AD_PRES' then 'A'
           when 'AD_VP' then 'B'
           when 'IT_PROG' then 'C'
           end
fromemployees; # Grouping functionSum avG average value Max Maximum value min Minimum value count Number of computations */
#1, easy to useselect sumThe sum of salaryfrom employees;
select round(avg(salary)minThe minimum value,maxThe maximum value,countThe total number of (salary)from employees;
#2Parameter types are supported/* sum, null does not participate in the operation avG, null does not participate in the operation Max, character, null does not participate in the operation min, null does not participate in the operation count is not null, null does not count */
select sum(last_name),avg(last_name) from employees;
select max(hiredate),min(hiredate)from employees;
#3May,distinctWith use, to heavyselect sum(distinct(salary)) from employees;
select count(distinct salary) from employees;
#4, a detailed introduction to the count function/* Innodb count(*) is as efficient as count(1). Myisam count(*) is as efficient as count(1)
select count(salary) from employees;
select count(*) from employees;
select count(1) fromemployees; # is equivalent to adding a list of constants to the table #5, and the group function together with the query field is limited, the general requirements of the same group query field isgroup bySubsequent fieldsselect avg(salary),employee_id fromemployees; # errorselect datediff(now(),'1995-09-15');
select count(*) from employees where department_id=90;
Copy the code

Advanced 5 Group query

Select * from table where (select * from table where (select * from table where (select * from table where (select * from table where (select * from table where))) 1. The filtering conditions in group query are divided into two types: pre-group filtering: post-group filtering of original table: group function of the result set after group to make conditions, put in the having function */
select department_id,avg(salary) from employees group by department_id;
select max(salary),job_id from employees group by job_id;
select count(*),location_id from departments group bylocation_id; Add filter criteria, filter before groupingselect avg(salary) ,department_id from employees where email like '%a%' group by department_id;
select max(salary),manager_id from employees where commission_pct is not null group bymanager_id; Add complex filtersselect department_id,count(*) 'Number of employees' from where 'Number of employees'>2 employees group bydepartment_id; Query group again according to the query result/* Use having to filter after the result of the query
select count(*) ,department_id from employees group by department_id having count(*)>2;
select job_id,max(salary) from employees where commission_pct is not null group by job_id having max(salary)>200;
select minSalary Minimum salary,manager_idfrom employees where manager_id>102 group by manager_id havingThe minimum wage>5000 order bymanager_id; #gruopby groups by expressionselect count(*),length(last_name) from employees group by length(last_name) having count(*) >5 order bylength(last_name); Group by multiple fieldsselect department_id,job_id from employees  group by department_id, job_id order by department_id  ;
select department_id,job_id,avg(salary) from employees group by department_id,job_id;

select max(salary)-min(salary) difference from employees;
select min(salary),manager_id from employees where manager_id is not null group by manager_id  having min(salary)>6000;
select department_id,count(*),avg(salary)from employees group by department_id order by avg(salary);
Copy the code

Advanced 6 link query

Select * from mXn where table 1 has m rows and table 2 has n rows Sql92 standard SQL99 standard according to function to classify internal links - equivalent links - non-equivalent links - from link external links - left external links - right external links - full external links cross links */# Cartesian product occursselect name,boyName from beauty,boys;
select name ,boyName from beauty,boys where beauty.boyfriend_id=boys.id;
Copy the code

1. Equivalent links

select boyName ,name from boys,beauty where beauty.boyfriend_id=boys.id;
use myemployees;
select * fromdepartments; Select * from 'department' where 'department' = 'department'select last_name ,department_name from employees e,departments d where e.department_id=d.department_id; # query employee id, job id, job name #select * from jobs;
select e.last_name,e.job_id,j.job_title from employees e,jobs j where e.job_id=j.job_id; Filter can be addedselect last_name,department_name,commission_pct from employees e,departments d where e.department_id=d.department_id and e.commission_pct is not null; Select * from department where the second character of the city name is oselect * from departments;
select d.department_name,l.city from departments d,locations l where d.location_id=l.location_id and l.city like '_o%'; Query the number of departments in each cityselect count(*) Number of departments, Cityfrom departments d,locations l where d.location_id=l.location_id group byl.city; Select * from department where bonuses are available; select * from department where bonuses are available; select * from department where bonuses are availableselect
    d.department_name,min(salary)
from departments d,employees e
where
        d.department_id=e.department_id and e.commission_pct is not null
group byd.department_name; # add sortingselect job_title,count(*) from jobs j,employees e where j.job_id=e.job_id group by job_title order by count(*) desc; # three table linksselect last_name,department_name,city from employees e,departments d,locations l where e.department_id=d.department_id and d.location_id=l.location_id;
Copy the code

2. Non-equivalent links

select last_name,salary,grade_level from employees e,job_grades j where e.salary>j.lowest_sal and e.salary <j.highest_sal; # Self-link, self-linkselect e1.last_name,e2.last_name from employees e1,employees e2 where e1.manager_id=e2.employee_id;
Copy the code

3. Internal connection and external connection

/* inner link inner link -- left outer left[outer] -- right outer right[outer] -- all outer full[outer] cross link syntax select query list from table 1 join table 2 Alias ON link condition WHERE filter condition */

#1.In the link/* select table 1 from inner join table 2 alias */
select last_name,department_name from employees e inner join departments d on e.department_id=d.department_id;
select last_name,job_title from employees e inner join jobs j on e.job_id = j.job_id where last_name like '%e%';
select city,count(*) from departments d inner join locations l on d.location_id = l.location_id group by d.location_id having count(*)>3;
select department_name,count(*) employeesfrom departments d inner join employees e on d.department_id = e.department_id group by e.department_id having count(*)>3 order by count(*)desc;
select last_name,department_name,job_title from employees e inner join departments d on e.department_id = d.department_id inner join jobs j on e.job_id = j.job_id order by  department_name desc; # unequal linksselect last_name,salary,grade_level from employees e inner join job_grades j on e.salary>j.lowest_sal and e.salary<j.highest_sal;
select last_name,salary,grade_level from employees e inner join job_grades j on e.salary between j.lowest_sal and j.highest_sal;
select e.last_name,e.employee_id,m.last_name,m.employee_id from employees e inner join employees m on e.manager_id=m.employee_id;

#2.Use girls;select * from beauty;
select be.*,bo.* from beauty be left outer join boys bo on be.boyfriend_id=bo.id;
/* left join */ left join */
use myemployees;
select d.*,e.* from departments d left join employees e on d.department_id = e.department_id where employee_id is null; Use girls; # cross link=Cartesian productselect b.*,bo.* from boys b cross join beauty bo;

select be.*,b.*from beauty be left join boys b on be.boyfriend_id=b.id where be.id>3;
use myemployees;


select city,d.* from departments d right join locations l on d.location_id = l.location_id where d.department_id is null;

select last_name,department_name from departments d left join employees e on d.department_id = e.department_id where d.department_name='SAL' or d.department_name='IT' and last_name ! = null;
Copy the code

Advanced 7 sub-query

Select * from (select * from); select * from (select * from); -----select, -----from, -----where or having, -----, -----exist, -----exist, ----- standard quantum query (result set has only one row and one column) ----- column subquery (result set has only one row and many columns) ----- row subquery (result set has only one row and many columns) ----- table subquery (result set has many rows and many columns) */# a.where.havingbehind/ * features: In /any/some/all subqueries take precedence over the main query, which uses the results of the subquery */

#1.Scalar quantum queryselect
    last_name,salary
from
    employees
where
        salary> =(select salary from employees where last_name='Abel')
order by
    last_name;

select
    last_name,job_id,salary
from employees
where job_id=(
    select job_id
    from employees
    where employee_id=141)
  and salary> (
    select salary
    from employees
    where employee_id=143
);

select last_name,job_id,salary
from employees
where salary = (
    select min(salary)
    from employees
);

select department_id,min(salary)
from employees
group by department_id
having min(salary)>(
    select min(salary)
    from employees
    where department_id=50
);

#2.Column subquery (multi-row subquery)Use the multi-line comparator IN/NOT IN equal to ANY/SOME of the list and compare ALL with ALL of the values returned by the subquery */

select last_name,department_id from employees where department_id in (select distinct department_id from departments where location_id in (1400.1700));

select last_name,employee_id,job_id,salary from employees where salary <any(select distinct salary from employees where job_id='IT_PROG') and job_id! ='IT_PROG';


#3.Muck queryselect * from employees where (employee_id,salary)=(select min(employee_id),max(salary) fromemployees); # 2, put itselectbehindselect d.*, (select count(*) from employees e where e.department_id=D.d epartment_id) numberfrom departments d;

select (select department_name from departments d where d.department_id=e.department_id)from employees e where employee_id=102; # 3, put it onfrombehindselect j.grade_level,avgsalary.* from (select avgAverage salary,department_idfrom employees group by department_id) avgsalary,job_grades j whereAvgsalary>j.lowest_sal andAvgsalary<j.highest_sal; # 4, put the use of # after exist to judge whether the query has a valueselect exists(select employee_id from employees);
select department_name from departments d where exists(select * from employees e where e.department_id=d.department_id);
Copy the code

Step 8, paging query

/* When you want to display data on a page, you need to submit a page-in SQL request syntax: Join type join table 2 on WHERE group by having order by limit offset, size; Offset: start index of the entries to be displayed Size: number of entries to be displayed Feature: Number of pages to be displayed Size Page: (pag-1) * size size: size */
select * from employees limit 0.5;

select * from employees limit 10.15;

select * from employees where commission_pct is not null order by salary desc limit 10;
Copy the code

Step 9 Joint query union Joint query

/* union, combine multiple query statements into one result syntax: query statement 1 union query statement 2; Usage scenario: Precautions: The number of result columns in multiple tables must be the same. The column types of the query must be the same. The results will be automatically deduplicated# query department id>90Or the email contains the information of a's employeesselect * from employees where email like '%a%'
union
select * from employees where department_id>90;
Copy the code

DML language

/* Insert: modify: delete: */Copy the code

Advanced 1 insert statement

/* syntax: insert into table name (column name,...) Values (value); Features: Insert values of the same type or compatible with the column type */

#1.Insert beauty use girls;insert into beauty (id,name,sex,borndate,phone,photo,boyfriend_id) values (13.'Tang Yixin'.'woman'.'1990-4-23'.'18988888888'.null.2);
select * from beauty;
delete from beauty where id=16;

#2.Can I do fornullHow are values inserted into columns ofnullThe column must have the value #. Method two, can be empty column, can not writeinsert into beauty (id,name,sex,borndate,phone,boyfriend_id) values (14.'Venus'.'woman'.'1990-4-23'.'13822222222'.9);

#3.Whether the order of columns can be reversedinsert into beauty (name,sex,id,phone) values ('the xin'.'woman'.16.'1345678903');

#4.The number of columns must match the number of values #5.You can omit column names, default to all columns, and the columns are in the same order as the columns in the tableinsert into beauty values (18.'zhang fei'.'male'.null.'12345678901'.null.null); Way # 2/* insert into table_name set table_name = table_name, table_name = table_name... * /
insert into beauty set id=19,name='liu tao',phone='34567654839';

/* The difference between the two methods 1. Method 1 supports multiple rows at a time. Method 2 does not support 2. Method 1 supports subquery. Method 2 does not support */
insert into beauty (id,name,sex,borndate,phone,photo,boyfriend_id)
values (20.'Tang Yixin'.'woman'.'1990-4-23'.'18988888888'.null.2),
       (21.'Tang Yixin'.'woman'.'1990-4-23'.'18988888888'.null.2),
       (22.'Tang Yixin'.'woman'.'1990-4-23'.'18988888888'.null.2),
       (23.'Tang Yixin'.'woman'.'1990-4-23'.'18988888888'.null.2);
insert into beauty (id,name,phone) select 26.'Victoria song'.'12345678901';
Copy the code

Advanced 2 modify statement

Update indicates set column = new value, column = new value where filter criteria */
#1.Update a single tableset phone='13899888899' where name like Don '%';
select * from beauty;
#2.Modify boys update boysset boyName='zhang fei',userCP=20 where id=2;

#2.Modify records of multiple tables/* sql92 syntax: Update table 1, alias, SQL > select * from inner, left, right JOIN where SQL > select * from inner, left, right JOIN where SQL > select * from inner, left, right JOIN where SQL > select * from left

#2.Update boys Binner join beauty be
    on b.id=be.boyfriend_id
set be.phone=11111111111
where b.boyName='Zhang Wuji';
select * from beauty;

update beauty be left join boys b on be.boyfriend_id=b.id set be.boyfriend_id=2 where b.id is null; Delete statementTruancate TRUNCate TABLE name */Delete a single tabledelete from beauty where phone like '% 9';
select * frombeauty; Delete multiple tablesdelete  be from beauty be inner join  boys b on be.boyfriend_id=b.id where b.boyName='Zhang Wuji';

delete be,b from beauty be inner join boys b on be.boyfriend_id=b.id where be.name='Angelababy'; Way # 2truncateEmpty datatruncate table boys;
#delete 与truncateThe difference betweenTruncate cannot be truncate, which is more efficient. If the data in the delete table has self-growing columns, if the delete is used, the newly inserted data grows from the breakpoint. If TRUNCATE is used, from 1 onwards, no value is returned when TRUNCate is deleted. A return value is returned when DELETE is deleted
use myemployees;

CREATE TABLE my_employees(
                             Id INT(10),
                             First_name VARCHAR(10),
                             Last_name VARCHAR(10),
                             Userid VARCHAR(10),
                             Salary DOUBLE(10.2));CREATE TABLE users(
                      id INT,
                      userid VARCHAR(10),
                      department_id INT

);
#2.Shows the structure of the table my_employeesDESCmy_employees; # Method 1:INSERT INTO my_employees
VALUES(1.'patel'.'Ralph'.'Rpatel'.895),
      (2.'Dancs'.'Betty'.'Bdancs'.860),
      (3.'Biri'.'Ben'.'Bbiri'.1100),
      (4.'Newman'.'Chad'.'Cnewman'.750),
      (5.'Ropeburn'.'Audrey'.'Aropebur'.1550);

INSERT INTO users
VALUES(4.'Rpatel'.10),
      (5.'Bdancs'.10);

update my_employees set Last_name='drelxer' where id=3;
update my_employees set Salary =1000 where Salary<900;
delete my,u from my_employees my inner join  users u on my.Userid = u.userid where u.userid='Bbiri';
select * from my_employees;
Copy the code

DDL language

Alter alter alter drop drop */# 1. Library managementCreate database create database name; * /
#if not exists
create database if not exists books;
showdatabases ; # Library modificationsAlter database books charachter set GBK; alter database books charachter set GBK; * /
alter database books character setgbk; # delete librarydrop database if existsbooks; # Table management/* Table creation */
#1.The creation of a table/* create table name (column name type (length) constraint, column name type (length) constraint, column name type (length) constraint... Column name Column type (length) constraint, */
create database if not exists books;
create table book(
                     id int, # bNamevarchar(20), #s title pricedouble,
                     authorId int, publishDate datetime #);desc book;
create table author(
                       id int,
                       authorName varchar(20),
                       nation varchar(10));desc author;

#2.The modification of table/ * modifies the column column type and constraints, add delete row change column names the alter table table name drop | add | the modify | change column column type constraints; * /Change the column namealter table book change column  publishDate pubDate DATETIME;
descbook; Change the type of the columnalter table book modify column pubDate timestamp;
descbook; Add a new columnalter table author add column annual double; # delete columnsalter table author drop columnannual; Alter table namealter table author rename to bookAuthor;
alter table bookAuthor rename to author;

#3The deletion of the tabledrop table if exists book;
showtables ; # Universaldrop database if existsOld library name;drop table if existsThe old name of the table; Copy the tableinsert into author
(id,authorName,nation)
values
(1.'Haruki Murakami'.'Japan'),
(2.'mo yan'.'China'),
(3.'talk to the wind'.'China'),
(4.'jin yong'.'China');
#1.Just copy the structure of the tablecreate table cpoyAuthor like author;
select * from copyAuthor2;
#2To replicate the table's constructor datacreate table copyAuthor2 select * from author;
#3.Only part of the data is copiedcreate table capyAuthor3 select id,authorName from author where nation='China';
#4.Only some fields are copiedcreate table copyAuthor4 select id,authorName from author where 1=2;

create database if not exists test;
use test;
create table dept1(
                      id int(7),
                      name varchar(20)); #2Inserts data from table DEPARTMENTS into DEPT1create table dept2 select department_id,department_name from myemployees.departments;

create table emp5(
                     id int(7),
                     first_name varchar(20),
                     last_name varchar(20),
                     dep_id int(7));alter table emp5 modify column last_name varchar(50);

create table employees2 like myemployees.my_employees;

drop table if exists emp5;

alter table employees2 rename to emp5;

alter table emp5 add column test_column varchar(20);

alter table test.emp5 drop column  test_column;

select * from emp5;

/* Table type: Smallint 2 bytes -32768 to 32767 0 to 65503 MediumInt 3 bytes int, -- Tinyint 1 byte -128 to 127 0 to 255 Different stored ranges Smallint 2 bytes -32768 to 32767 0 to 65503 MediumInt 3 bytes int, Integer 4 bytes bigint 8 bytes, floating-point fixed-point number -- -- -- -- -- -- -- -- -- -- -- -- -- -- floating point Numbers - character -- -- -- -- -- -- -- short text char varchar -- -- -- -- -- -- -- a long text text blob - date * /
#1. Integer, how to set unsigned and unsigned/ * characteristics; If the length is not set, or if there is a default length, the default length only affects the display. If you want to fill with 0, use zerofill */
create table tabInt(
                       t1 int(9) zerofill,
                       t2 int(9) unsigned
);
select * from tabInt ;
insert into tabInt (t1) values (12);
insert into tabInt (t1) values (- 12);
insert into tabInt (t2) values (- 1);
insert into tabInt (t2) values (1);
drop table if exists tabInt;
insert into tabInt values (3563333333.333333567);

#3.The decimal/* Float (m,d) 4 bytes double(m,d) 8 bytes Fixed-point byte decimal(m,d) dec(m,d) M, d can be omitted, decimal defaults to (10,0) m, the total number of digits d, the length of the integer part after the decimal point =m-d m, an error is reported if the number of digits exceeds the total number of digits d is intercepted if the number exceeds the decimal point. Precedence over double as simple as possible, as small as possible */

create table tab_float(
                          f1 float(5.2),
                          f2 double(5.2),
                          f3 decimal(5.2));select * from tab_float;
insert into tab_float values(123.45.123.56.123.67);
insert into tab_float values(1234.456.123.56.123.67);
insert into tab_float values(123.456.123.567.123.678);
insert into tab_float values(123.4.123.56.123.67); # 3, character type/* Short text char, varchar each character is 3 bytes long text blob(large binary) cahr(M) varchar(M) M holds the most characters Varchar Unfixed length char The default value is 1. The m in varchar cannot omit */

Enum type / * * /
create table tab_char(
    c1 enum('a'.'b'.'c'.'d'));/* Uppercase is automatically changed to lowercase */
select * from tab_char;
insert into tab_char  (c1) values ('a');
insert into tab_char  (c1) values ('e');
insert into tab_char  (c1) values ('A');

/* Set can select more than one insert out of range will report an error */
create table tab_set(
    c1 set('a'.'b'.'c'));insert into tab_set (c1) values ('a');
insert into tab_set (c1) values ('a,b');
insert into tab_set (c1) values ('a,e');

/* Minimum value of date type Date 4 bytes 1000-01-01 datetime 8 bytes 1000-01-01 00:00: 00 TIMESTAMP 4 bytes time 3 bytes Year 1 byte 1901-2155 Differences between DateTime and timestamp Datatime Not affected by time zone timestamp Affected by time zone */

create table tab_date(
                         t1 datetime,
                         t2 timestamp
);

insert into tab_date values (now(),now());
insert into tab_date values ('2020-02-04'.'2020-02-04');

select * from tab_date;

show variables like 'time_zone';
set time_zone ='+ 9'
Copy the code

Constraint, primary and foreign keys

/* Common constraint meaning: a restriction used to restrict the data in a table. There are six restrictions to ensure the accuracy and reliability of the data in a table: -- not null -- not null -- not null -- not null -- not null -- not null -- not null -- not null -- not null -- not null -- not null -- not null -- not null -- not null -- not null -- not null -- not null Mysql > select * from table where id = 1; mysql > select * from table where id = 1; mysql > select * from table where id = 1; Table name (column name) Type (column level) constraint (column level) Create TABLE name (column name) Not null,default), table level constraint (all but non-null,default); * /
#1Create table to add more constraint #1.Add column-level constraintscreate database if not exists students;
use students;
drop table if exists stuinfo;

create table stuinfo(
                        id int primaryKey, # stuNmaevarchar(20) not null,
                        gender char(1),
                        searnum int unique ,
                        age int default 18
);
desc stuinfo;
select * fromstuinfo; Select * from primary key; select * from primary keyshow index from stuinfo;

drop table if exists major;
create table major(
                      id int primary key ,
                      majorNmae varchar(20))default character setutf8; Add table level constraints/* Syntax: At the bottom of each column, the constraint name constraint type (column name) can be used instead of using the generic way of writing the constraint name section: */
drop table if exists stuinfo;
create table stuinfo(
                        id int,
                        stuName varchar(20),
                        gender char(1),
                        seat int,
                        age int,
                        majorid int.constraint pk primary# key (id), the primary keyconstraint uq unique(seat), # unique keyconstraint fk_stuinfo_major foreign key(majorid) references major(id)
);
show index from stuinfo;
create table stuinfo(
                        id int,
                        stuName varchar(20),
                        gender char(1),
                        seat int,
                        age int,
                        majorid int.primary# key (id), the primary keyunique(seat), # unique keyforeign key(majorid) references major(id)
);

create table if not exists stuinfo(
                                      id int primary key ,
                                      stuName varchar(20) not null ,
                                      gender char(1),
                                      seat int unique ,
                                      age int default 18,
                                      majorid int.constraint fk_stuinfo_major foreign key(majorid) references major(id)
) default character set utf8;

/* Comparison between primary keys and unique ensures uniqueness whether a table can be empty how many tables can be in a table Whether combinations are allowed Primary keys Yes No One or none Yes Unique Yes Multiple or none */

select * from stuinfo;
insert into stuinfo (id,stuName,seat) values (1.'tom'.null),
                                             (2.'john'.null);
/* join a primary key */
create table if not exists stuinfo(
                                      id int,
                                      stuName varchar(20) not null unique default 'Joe',
                                      gender char(1),
                                      seat int unique ,
                                      age int default 18,
                                      majorid int.constraint pk primary key (id,stuName),
                                      constraint fk_stuinfo_major foreign key(majorid) references major(id)
);

insert into stuinfo (id,stuName,seat) values (1.'tom'.null),
                                             (2.'tom'.null);

SQL > alter table table_name */; SQL > alter table table_name */; SQL > alter table table_name */Add constraint when modifying tableAlter table table name modify column type new constraint; Alter table name add constraint constraint */

drop table if exists stuinfo;
desc stuinfo;
create table if not exists stuinfo(
                                      id int,
                                      stuName varchar(20),
                                      gender char(1),
                                      seat int ,
                                      age int ,
                                      majorid int
);
use students;
#1.Add a non-null constraintalter table stuinfo modify column stuName varchar(20) not null ;
#2.Delete a non-null constraintalter table stuinfo modify column stuName varchar(20) null;
#3.Adding default Constraintsalter table stuinfo modify column age int default 18;
#4.Add primary key # column level constraintalter table stuinfo modify column id int primarykey ; Table level constraintalter table stuinfo add constraint primary key (id);
#5.Add unique use students;alter table stuinfo modify column seat int unique ;
alter table students.stuinfo add constraint  unique (seat);
#6.Add the healthalter table students.stuinfo add foreign key (majorid) references major(id);
alter table students.stuinfo add constraint fk foreign key (majorid) referencesmajor(id); Alter table drop constraint #1.Delete a non-null constraintalter table stuinfo modify column stuName varchar(20) null;
#2.Delete default Constraintsalter table stuinfo modify column age int ;
#3.Remove the primary keyalter table stuinfo drop primary key ;
#4.Delete unique constraintsalter table stuinfo drop index fk;
#5.Delete external health constraintsalter table stuinfo drop foreign key fk;
show index from stuinfo;
descstuinfo; # identity column/* Instead of manually inserting values, the system provides a default sequence value feature: must identity pages be paired with primary keys? Not necessarily, but must be matched with keys. How many identification columns can there be in a tableSet the identity column when creating the tabledrop table if exists tab_identity;
create table tab_identity(
    id int ,
    name varchar(20));select * from tab_identity;
insert into tab_identity values (1.'john');
insert into tab_identity values (2.'tom');
insert into tab_identity (name) values ('wede');
insert into tab_identity values (20.'james');
#1.Not from the beginningshow VARIABLES like '%auto_increment%'; Alter table set identity columnalter table tab_identity modify column id int primary key auto_increment;
Copy the code

things

Set autocommit=0; /* Set autocommit=0; /* set autocommit=0; SQL statement 1; SQL statements 2; The statement to submit the thing */
show variables like 'autocommit';
/* step 1: start things set autocommit=0; start transcation; Optional step 2: write a statement in the transaction Step 3: End the transaction commit; Rollback; Rollback things */# Demonstrations of thingsdrop table if exists account;
create table account(
    id       bigint unsigned comment 'id',
    username varchar(20) comment 'User name',
    balance  decimal(40.20) comment 'savings'
)character set utf8;
alter table account default character set utf8;
alter table account modify column id bigint unsigned primary key auto_increment;
insert into account (username,balance) values ('aa'.1000), ('zhao'.1000);
select * fromaccount; # Turn things onset autocommit =0;


update account set balance=300 where username='Zhang Wuji';
start transaction ;
update account set balance=500 where username='Zhang Wuji';
update account set balance=1500 where username='zhao';
#commit;
rollback; # Things are not segregated/* Dirty reads: for two things, t1,t2. T1 reads a field that has been updated by T2, but has not been committed. If T2 rolls back, T1 reads an invalid non-repeatable read: For two things, T1, T2. t1 reads a field, then T2 updates that field, t1 reads the same field again, and the value is different: for two things, T1, T2. t1 reads some fields, and t2 inserts some new data into the table. Mysql isolation level read uncommitted Read Committed */# Check the isolation levelselect @@tx_isolation;
setsession transaction isolation level read uncommitted ; Can't avoid dirty, phantom, and unrepeatable readssetsession transaction isolation level read committed ; # Can avoid dirty read, can not avoid magic read, do not repeat readsetsession transaction isolation level repeatable read; Can avoid dirty read, can not repeat read, can not avoid magic readsetsession transaction isolation level serializable; Can avoid dirty read, do not repeat read, magic read #savepointNode name set rollback point use students;select * from account;
set autocommit =0;
start transaction ;
delete from account where id=5;
savepoint a;
delete from account where id =6;
rollback to a;
Copy the code

Views, virtual tables

/* As with regular tables, mysql5.1 is a new feature that dynamically generates data from tables */
desc students.stuinfo;
select * from students.stuinfo;
select * from major;
insert into
    students.stuinfo
values
    (1.'zhang fei'.'male'.111.18.1),
    (2.'liu bei'.'male'.112.18.2),
    (3.'guan yu'.'male'.113.18.1),
    (4.'zhaoyun'.'male'.114.18.2),
    (5.'黄盖'.'male'.115.18.1);
set character set utf8;
show variables like '%char%';
set character_set_database=utf8;
set character_set_server =utf8;
set character_set_connection =utf8;
set character_set_filesystem =utf8;
insert into major (id,majorNmae) values (1.'Chinese'), (2.'mathematics');
select s.stuName,majorNmae from stuinfo s inner join major m on s.majorid=m.id where s.stuName like 'a %';

create view v1 as select s.stuName,majorNmae from stuinfo s inner join major m on s.majorid=m.id where s.stuName like 'a %';
select * from v1 where stuName='zhang fei';
Copy the code

First, create a view

/* Syntax: create view as complex query statement */
use myemployees;
create view
    myv1
as select
    e.last_name,department_name,job_title
from employees e
    inner join departments d on e.department_id=d.department_id
    inner join jobs j on e.job_id = j.job_id;
Copy the code

Two, use views

select * from myv1;
select last_name,department_name,job_title from myv1 where last_name like '%a%';
drop view avgsalary;
create view avgsalary as select avg(salary) avs,department_id from employees group by department_id;
select  avs, j.grade_level from avgsalary a inner join job_grades j on a.avs>j.lowest_sal and a.avs<j.highest_sal ;

select avs,department_id from avgsalary order by avs desc limit 1;
create view myv3 as select avs,department_id from avgsalary order by avs desc limit 1;

select avs,department_name from avgsalary a inner join departments d on a.department_id = d.department_id order by a.avs desc limit 1;
select avs,department_name from myv3 m inner join  departments d on m.department_id = d.department_id;
Copy the code

Third, view modification

/* Method 1: create or replace View view name query statement Method 2: ALTER VIEW view name as query statement */

select * from myv3;
create or replace view myv3 as select avg(salary) avs,job_id from employees group byjob_id; Delete the view/* drop view 1, 2 */# 5, view the viewdesc myv3;
show create view myemployees.myv3;
drop view emp_v1;
create view emp_v1 as select last_name,salary,email from employees where phone_number like '011%';
select * from emp_v1;

create or replace view emp_v2
    as select max(salary),department_name
    from employees e inner join departments d on e.department_id = d.department_id
group by e.department_id having max(salary) >12000;
select * fromemp_v2; # 6. Update the viewcreate or replace view myv1 as select last_name,email,salary*12*(1+ifnull(commission_pct,0)) asalary from employees;
create or replace view myv1 as select last_name,email from employees;
#1.Insert data/* Insert data into the view, inserts data into the original table */
select * from myv1;
insert into myv1 values ('zhang fei'.'[email protected]');

#2.Modify the update myv1set last_name='Zhang Wuji' where last_name='zhang fei';

#3.deletedelete from myv1 where last_name='Zhang Wuji'; You can add read-only permission to a view/* Views with the following keywords are not allowed to update distinct,group by, having,union or union all; * /
create or replace view myv1 as select max(salary) m,department_id from employees group by department_id;
update myv1 set m=9000 where department_id=10; # constant viewcreate or replace view myv2 as select 'john' name;
update myv2 set name='jack';

#selectContains subqueriescreate or replace view myv3 as select (select max(salary) from employees) 'Maximum salary';
update myv3 setThe highest salary=100000;

#joinStatement cannot update #fromThe last is a view that cannot be updated/* Create table does not occupy physical space; create view does not occupy physical space; Generally, only the difference between DELETE and TRUNCATE is performed. Delete can be rolled back, while TRUNCate cannot be rolled back */# demodelete

create table if not exists bookType(
    id int auto_increment,
    type varchar(20) not null ,
    primary key (id)
);

use students;
create table book(
    bid int primary key ,
    bname varchar(20) unique  not null ,
    price float default 10,
    btype_id int ,
    constraint foreign key fk_book_booktype(btype_id) references bookType(id)
);
insert into bookType values(1.'aaaa');
set autocommit =0;
insert into book (bid, bname,price, btype_id) values (1.'it'.12.34.1);

create view bookview as select bname,type from book b inner join bookType bT on b.btype_id = bT.id where b.price>1000;

#create or replace view bookview as
#alter view bookview as selectCascading update or cascading deletedrop table if exists major;
create table major(
                      id int primary key ,
                      majorName varchar(20))default character set utf8;
show index from major;
insert into major values (1.'java'), (2.'h5'), (3.'data');

drop table if exists stuinfo;
desc stuinfo;
create table if not exists stuinfo(
                                      id int,
                                      stuName varchar(20),
                                      gender char(1),
                                      seat int ,
                                      age int ,
                                      majorid int
)default character set =utf8;
show index fromstuinfo; # Add external healthalter table stuinfo add constraint fk_stu_major foreign key(majorid) references major(id);
insert into
    stuinfo
values
    (1.'john1'.'woman'.null.null.1),
    (2.'john2'.'woman'.null.null.2),
    (3.'john3'.'woman'.null.null.1),
    (4.'john4'.'woman'.null.null.2),
    (5.'john5'.'woman'.null.null.3),
    (6.'john6'.'woman'.null.null.1); # delete from professional table3Professional no./* Cannot delete */ due to external memoryuse students; Mysql > delete primary table; delete primary tableshow create table students.stuinfo;
alter table stuinfo drop foreign key fk_stu_major;
alter table stuinfo add constraint fk_stu_major foreign key(majorid) references students.major(id) on delete cascade ;

select * from students.major;
select * from students.stuinfo;
delete from students.major where id=4; # Mode 2: cascade emptyalter table stuinfo drop foreign key fk_stu_major;
alter table stuinfo add constraint fk_stu_major foreign key(majorid) references students.major(id) on delete set null;

select * from students.major;
select * from students.stuinfo;
delete from major where id =2;
Copy the code

variable

/* System variables: show global variables; Check out the global variable show session variables; See the session variable 2. Check the meet the conditions of a part of the system variables show global | session variables like '% char %'; A system set up 3. See the value of the variable select @ @ global | session. System variable name 4. As a system variable assignment method one: the set global | session system variable name = value way 2: set global | session. System variable name = value; 2>> Session variables scope: only for the current link 1. Show session variables like '%char%' 3 Select @@TX_ISOLATION; 4. Set @@TX_ISOLATION ='read-uncommitted' for a session variable; Set session TX_ISOLATION = 'read-COMMITTED' Valid for the current session (link), same as session variable scope #1, declare and initialize = or := set @user variable name = value; Set @ user variable name := value; Select @username := value; Set @user variable name = value; set @user variable name = value; Set @ user variable name := value; Select @username := value; Select variable name from table; Select @username from username; Declare variable name type DECLARE variable name type default; declare variable name type default; #2, assignment method 1: set or select set local variable name = value Set local variable name := value; Select @ local variable name := value; Select * from table where select * from table where select * from #3. Use the select local variable name; User variable Current session The @ sign must be added to any position in the session. The type is not set. Begin End The local variable must be in BEGIN End only
set @name=100;
select count(*) into @count from students.major;
select @count ;

set @m=1;
set @n=2;
set @sum = @m+@n;
select @sum;


declare m int default 1;
declare n int default 2;
declare sum int;
set sum =n+m;
select sum;

Copy the code

Stored procedures and functions

/* Stored procedures and functions, similar to methods in Java. Improved code reuse 2. Simplified operation 3. Reduced compile time and reduced the number of links to the database server, improved efficiency Create PROCEDURE Name of the stored procedure begin Body of the stored procedure end Note, 1. The parameter list contains three parts: Parameter mode Parameter Name Parameter Type IN Stuname vARCHar (20) Parameter mode: IN This parameter can be used as input, that is, a value is passed IN by the call method. Out this parameter can be used as output, and this parameter can be returned as inout 2. If the stored procedure body contains only one sentence, you can omit the end of each SQL statement in the stored procedure body. You must add a semicolon to the end of the stored procedure. You can reset the syntax by using delimiter. Delimiter $2, call procedure Call stored procedure name (argument list); * /

#1.Use girls;drop table admin;
create table admin(
    id bigint unsigned auto_increment,
    username varchar(20)  not null,
    password varchar(20) not null.primary key (id)
)default character set = utf8;

insert into
    admin (id,username,password)
values
    (1.'join'.8888),
    (2.'lyt'.6666);
select * from admin;

delimiter $
create procedure myp1()
begin
    insert into admin(username,password) values ('john2'.0000), ('jack'.0000), ('tom1'.0000);
end $

call myp1()$

#inStored procedures for the parameters of the schemacreate procedure myp2(in name varchar(20))
begin
    select bo.* from boys bo right join beauty b on b.boyfriend_id=bo.id  where b.name=name;
end $

call myp2('Ada') $

create procedure myp3(in username varchar(20),in password varchar(20))
begin
    declare result varchar(20) default ' ';
    select count(*) into result from admin where admin.username=username and admin.password=password;
    select result;

end $

create procedure myp5(in username varchar(20),in password varchar(20))
begin
    declare result int;
    select count(*) into result from admin where admin.username=username and admin.password=password;
    select if(result>0.'success'.'failure');
end $

call myp5('zhang fei'.0000$# create tapeoutSchema stored procedurescreate procedure myp7(in girlname varchar(20),out boyname varchar(20))
begin
    select bo.boyName into boyname from boys bo inner join beauty b on bo.id=b.boyfriend_id where b.name=girlname;
end $
select * from beauty;
select * from boys;
set @name=' ' $
call myp7('Ada'.@name) $
select @name;

call myp7('Ada'.@name1) $
select @name1;

#4.withinoutThe storage mode of the schemacreate procedure myp8(inout a int.inout b int)
begin
    set a=a*2;
    set b=b*2;
end $


set @a=10 $
set @b=20 $
call myp8(@a.@b) $
select @a.@b;


create procedure test1(in user varchar(20),in psas varchar(20))
begin
    insert into admin (admin.username,admin.password) values (username,password);
end $
call test1('aaa'.'0000');
select * from admin;

create procedure test2(in id int.out name varchar(20),out phone varchar(20))
begin
    select b.name,b.phone into name,phone from beauty b where b.id=id;
end$# Delete the stored proceduredrop proceduretest1; View the stored procedure informationshow create procedure myp2;

create procedure test3(in mydate datetime,out strdate varchar(50))
begin
    select date_format(mydate,'%Y年%m月%d日') into strdate;
end $

call test3(now(),@str);
select @str;
Copy the code

function

/* The difference with stored procedures: stored procedures can have zero returns, or multiple return functions must have one return after processing data, return a result */# 1, function creation syntax/* create function Function name (parameter list) returns Return type begin Function body; End note: 1. The body of the function must have a return statement. If it does not have a return statement, it will not report an error. 2. Set the end tag */ using the DELIMiter statement# call syntax/* select function name (parameter list) */# no return use myemployees;create function myf1() returns int
begin
    declare c int default 0;
    select count(*) into c from employees;
    return c;
end $
selectMyf1 ()$# returnscreate function myf2(empNmae varchar(20)) returns double
begin
    declare s double default 0;
    set @sal=0;
    select salary into s from employees where last_name=empNmae;
    return s;
end $
select * from employees;
select myf2('Kochhar');

create function myf3(deptname varchar(20)) returns double
begin
    declare sal double default 0;
    select avg(salary) into sal from employees e inner join departments d on e.department_id=d.department_id where department_name=deptname;
    return sal;
end $
select * from departments;
select myf3('IT'); # 3, look at the functionshow create functionmyf3; # 4. Delete the functiondrop function myf3;

create function myf4(num1 float,num2 float) returns float
begin
   declare sum float;
   set sum= num1+num2;
   return sum;
end $

select myf4(1.3.1.4); # Process control structure/* Sequential structure: a program executes a branch structure from top to bottom: a program executes a loop structure by choosing one of two or more: a program executes a piece of code repeatedly under certain conditions #1, branch structure #1. If (expression 1, expression 2, expression 3) if(expression 1, expression 2, expression 3) if(expression 1, expression 2, expression 3) if(expression 1, expression 2, expression 3) Case variable expression | | field the when to determine the value of the then returns the value of 1 or statements; When The value to be judged then returns the value 2 or statement; When The value to be judged then returns the value 3 or statement; . Else The value to return; end case; Case when return value 1 when return value 2 when return value 3 case when return value 2 when return value 3 Else The value to return end can be used as an expression. Nested within other statements and can be placed anywhere */

create procedure myf5(in score int)
begin
   case
   when  score> =90 and score< =100 then  select 'A';
   when  score> =80 and score< =90 then  select 'b';
   when  score> =70 and score< =80 then  select 'c';
   when  score> =60 and score< =70 then  select 'd';
   else select 'E';
   end case ;
end $

call myf5(77);

/* if construct syntax: if (condition 1) then statement 1; Elseif condition 2 then statement 2; Elseif condition 2 then statement 2; end if; Used in begin end statements */
create function myf6 (score int) returns char
begin
    if score> =90 and score< =100 then return 'a';
    elseif score> =80 and score< =90 then return 'b';
    elseif score> =70 and score< =80 then return 'c';
        end if;
end $
select myf6(77); # loop structure/* Classify while,loop,repeat control: iterate similar to continue leave similar to break 1. Syntax of while While loop condition do loop body; 3. Repeat syntax: tag :repeat; End repeat[tag] */
use girls;
create procedure pro_while1(in insertCount int)
begin
    declare i int default 1;
    while (i< =insertCount) do
        insert into admin(username,password) values ('rose'.'6666');
        set i=i+1;
    end while ;
end $
drop procedure pro_while1;
call pro_while1(10) $select * from admin;

create procedure pro_while2(in insertCount int)
begin
    declare i int default 1;
    a:while (i< =insertCount) do
            insert into admin(username,password) values (concat('rose',i),'5555');
            if i> =20 then leave a;
            end if;
            set i=i+1;
    end while a;
end $

call pro_while2(111);

Copy the code