——Advanced database (practical operation)

Import batch file *. sql

  • Download emp.zip, extract the emp.sql file, and put the file in the root directory of a disk, such as disk F (the contents of the file are in)

  • Execute the source path in the terminal; You can import the database / table / data in emp.sql into your own mysql

  • Test:

    show databases; // One more empdb will be displayed

    use empdb; // Use database

    show tables; // There are two tables emp and dept

data type

  1. Integers: int(m) and bigint bigint are equivalent to long in Java. M represents the display length. m=10 saves 18. 0000000018 is displayed when querying. It needs to be used in combination with the zerofill keyword

    give an example:

    create table t1(age int(10) zerofill);
    
    insert into t1 values(18);
    
    select * from t1; (Test in terminal)

  2. Floating point number: double(m,d) m represents the total length, D represents the decimal length, 35.342 m=5 d=3, ultra-high precision floating point number decimal(m,d), which is used when ultra-high precision operation is involved  

    create table t2(money double(5,3));
    
    insert into t2 values(55.234); //normal
    
    insert into t2 values(55.2345678); //rounding
    
    insert into t2 values(555.235); //Rule mismatch exception

  3. character string

    • char(m): fixed length, m=10. The length occupied by abc is 10. The execution efficiency is slightly higher than that of variable length. It is used when the length in the demand is a fixed value. The maximum length is 255
    • varchar(m): variable length, m=10. The length of abc is 3, which saves more space. The maximum length is 65535. It is recommended to use text if the data stored within 255 is more than 255
    • text(m): variable length, maximum length 65535
  4. time

    • date: only the year, month and day can be saved
    • time: only hours, minutes and seconds can be saved
    • datetime: save year, day, hour, minute and second. The default value is null, and the maximum value is 9999-12-31
    • Timestamp (number of milliseconds from the timestamp to January 1, 1970): save the year, month, day, hour, minute and second. The default value is the current system time, and the maximum value is 2038-1-19  

    give an example:

    create table t_date(t1 date,t2 time,t3 datetime,t4 timestamp);
    
    insert into t_date values("2021-9-29",null,null,null);
    
    insert into t_date values(null,"14:48:22","1985-11-22 10:20:30",null);
    
    select * from t_date;

Primary key constraint primary key

create table t(id int primary key,name varchar(20));

insert into t values(1,"Zhang San");

insert into t values(2,"Li Si");

insert into t values(1,"Wang Wu"); //report errors

ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY' primary key value duplicate error

insert into t values(null,"Zhao Liu"); //report errors

ERROR 1048 (23000): Column 'id' cannot be null

Primary key constraint + auto increment_ increment

create table t3(id int primary key auto_increment,name varchar(20));

insert into t3 values(null,"aaa");

insert into t3 values(null,"bbb");

insert into t3 values(10,"ccc");

insert into t3 values(null,"ddd");

delete from t3 where id>=10;

insert into t3 values(null,"eee");

Comparison operator > < > = < == And < >

  1. Query employee information with salary less than or equal to 3000

    select * from emp where sal<=3000;

  2. Query the name and work of the employee in department 1

    select name,job from emp where dept_id=1;

  3. Query the name, salary and work of employees who are not programmers (two ways)

    select name,sal,job from emp where job!="programmer";
    
    select name,sal,job from emp where job<>"programmer";

  4. Query the name and bonus of employees with bonus  

    select name,comm from emp where comm>0;

and or not not

  1. Query the employee information of department 1 whose salary is greater than 2000

    select * from emp where dept_id=1 and sal>2000;

  2. Query is the information of programmers or employees whose salary is equal to 5000

    select * from emp where job="programmer" or sal=5000;

  3. Find out the names of CEO and project manager

    select name from emp where job="CEO" or job="project manager";

  4. Query sales information with a bonus of 500  

    select * from emp where comm=500 and job="sale"

between x and y

  • The query contains x and y between the two
  1. Query employee information with salary between 2000 and 3000

    select * from emp where sal>=2000 and sal<=3000;
    
    select * from emp where sal between 2000 and 3000;

  2. Query employee information with salary beyond 2000 to 3000

    select * from emp where sal not between 2000 and 3000;

in

  • Used when an automatic query value is multiple values
  1. Query employee information with salary equal to 1500, 3000 and 5000

    select * from emp where sal=1500 or sal=3000 or sal=5000;
    
    select * from emp where sal in(1500,3000,5000);
    
    select * from emp where sal not in(1500,3000,5000);

Fuzzy query like

  • _: Represents 1 unknown character
  • %: represents 0 or more unknown characters
  • give an example:
    • x% starting with x
    • End with x% x
    • Contains% X% X%
    • The second is X_ x%
    • Starting with X, the penultimate is y x%y__
  1. Query employee information surnamed sun

    select * from emp where name like "Sun%";

  2. The second word in the query is employee information

    select * from emp where job like "_sell%";

  3. Query the employee name ending with fine in the name

    select name from emp where name like "%essence";

  4. Query the information of employees whose names contain monks and whose wages are higher than 2000

    select * from emp where name like "%monk%" and sal>2000;

  5. Query the employee information starting with city in departments 1 and 2

    select * from emp where dept_id in(1,2) and job like "city%";

  6. Query the name of the manager among the employees with leaders

    select name from emp where manager is not null and job like "%manager%";

Sort order by

  • Format: order by sort field name ASC (default ascending) / desc (descending)
  1. Query the name and salary of each employee and sort them in ascending order

    select name,sal from emp order by sal;
    
    select name,sal from emp order by sal desc; Descending order

  2. Query the name and salary of department 1 and sort them in descending order

    select name,sal from emp where dept_id=1 order by sal desc;

  3. Query the name, salary and department number of employees whose salary is higher than 1000, and sort by department number in ascending order. If the Department numbers are the same, sort by salary in descending order (multi field sorting)

    select name,sal,dept_id from emp where sal>1000 order by dept_id,sal desc;

Paging query limit

  • Format: limit number of skipped pages (number of skipped pages * number of pages per page), number of requested pages (number of pages per page)
  • give an example:  
    • limit 0,5 request 5 pieces of data on page 1
    • limit 5,5 request 5 pieces of data on page 2
    • limit 70,10 request 10 data on page 8
    • limit 40,8 request 8 pieces of data on page 6
  1. Query employee name and salary, sort by salary ascending order, and query 5 data on the first page

    select name,sal from emp order by sal limit 0,5;

  2. Query the name and salary of employees, sort them in ascending order, and query the 5 data on page 2

    select name,sal from emp order by sal limit 5,5;

  3. Query the name and salary of employees, sort them in ascending order, and query the three data on page 3

    select name,sal from emp order by sal limit 6,3;

  4. Query the name and salary of the highest paid employee in department 1

    select name,sal from emp where dept_id=1 order by sal desc limit 0,1;

alias

select name as "name" from emp;

select name "name" from emp;

select name from emp;

Numerical calculation + - * /%

  1. Query the name, salary and year-end bonus of each employee (salary * 5)

    select name,sal,sal*5 annual bonus from emp;

  2. Raise the salary of employees in department 1 by 5 yuan  

    update emp set sal=sal+5 where dept_id=1;

Aggregate function

  • The aggregate function can perform statistical query on multiple pieces of data queried
  • Statistical method: average, maximum, minimum, sum, count  
  1. AVG (field name)

    • Query the average salary of department 1

      select avg(sal) from emp where dept_id=1;

  2. Max (field name)

    • Query the maximum salary of department 1

      select max(sal) from emp where dept_id=1;

  3. Min Min (field name)

    • Inquire about the minimum wage of department 1

      select min(sal) from emp where dept_id=1;

  4. Sum (field name)

    • Query the total salary of department 1

      select sum(sal) from emp where dept_id=1;

Tags: Database MySQL Big Data

Posted on Thu, 30 Sep 2021 00:13:35 -0400 by rulian