Database benefits

1. Data persistence is realized
2. Use a complete management system to uniformly manage data, which is easy to query

Related concepts of database

DB

database: the warehouse where data is stored. It holds some columns of organized data.

DBMS

Database Management System. A database is a container created and operated by a DBMS.
The database management systems of the scenario include MySQL, Oracle, DB2, SqlServer, etc.
DBMS classification:

• DBMS - Access based on shared file system
• Client server based DBMS - MySQL, Oracle, SqlServer

SQL

Structure Query Language: a language specifically used to communicate with databases.
Advantages of SQL:

1. Not a specific database vendor's proprietary language, almost all DBMS support SQL.
2. Easy to learn
3. Although simple, it is a powerful language. Using its language elements flexibly, it can carry out very complex and advanced database operations.

Characteristics of database storage data

1. The data is placed in the table, and the table is placed in the database.
2. A database can have multiple tables. Each table has a name to identify itself. Indicates uniqueness in the library.
3. Tables have properties that define how data is stored in the table.
4. A table consists of columns, which we call fields. All tables consist of one or more columns.
5. The data in the table is stored by row.

Features of MySQL products

1. Low cost: open source, generally free to use.
2. High performance: fast execution.
3. Simple: easy to install and use.

MySQL version

• Community Edition (free)
• Enterprise Edition (charge)

MYSQL common commands

1. Log in to mysql and issue the command

   mysql -h host -u username -P port -p #Log in to mysql,-h followed by host, - u followed by user name - p port number - p followed by password
   exit sign out
   

2. show database

   show databases
   

3. Enter database

   use databasename #Enter the specified data and specify the name of the data
   

4. Show tables in Library

   show tables;  #Displays the tables in the current library
   show tables from databasename; #Displays all tables in the specified library
   

5. View which database you are currently in

   select database();
   

6. Create a table and view the table structure

   create table stuinfo(id int, name varchar(20)); #Create table
   desc stuinfo; #Display table structure
   show create table stuinfo; #See table script for display
   select * from  stuinfo; #Data in query table
   insert into stuinfo(id, name) values(1,'john'),(2'rose'); #Insert data into table
   

7. View mysql version

   mysql version();
   mysql -V perhaps mysql --version #This command is executed outside the database without logging into the database.
   

SQL syntax specification

1. Case insensitive, but it is recommended that the keyword be capitalized, indicating that the column name is lowercase.
2. It is recommended that each command end with a semicolon.
3. Indent and wrap as needed for each command.
4. notes
   Single line note: # or –
   Multiline comment: / * starts * / ends

DQL language

Basic query

Syntax: select query list from table name;
The query list can be fields, constant values, expressions and functions in the table.
Query results: virtual tables

1. Fields, constant values, expressions, functions in query tables

   select employee_id, first_name, last_name, email, phone_number, job_id, salary, commission_pct, manager_id, department_id, hiredate from employees; #Fields in query table
   
   select * from employees; # Fields in query table
   select 100; #Query constant value
   select 'john'; #Query constant value
   select 100/90; #Query expression
   select version(); #Query function
   

2. Alias fields

   select 100/90 as result; # Mode 1
   select 100/90 result; # Mode II
   

   Benefits of aliasing:
   • Easy to understand
   • Solve the problem of duplicate names (usually occurs when tables are associated, and fields in different tables have duplicate names)
3. De duplication distinct

   select distinct department_id from employees;
   

4. The + operator in MySQL

   select 100+90; #If both operands are numeric, add
   select '123' + 90; #As long as there is one side of character type data, the view will convert character type to numeric type for operation
   select 'john' + 90; # After the conversion of character data to numerical data fails, it will be converted to 0 and then calculated
   select null + 90; # As long as there is null in the expression, the result is null;
   

5. String splicing function concat in MySQL

   select concat('a','b','c') as result;
   

6. Function ifnull in MySQL to determine whether it is NULL

   select ifnull(commission_pct, 0) as commission_pct from employees;
   

Condition query

Syntax: select query list from table name where query criteria;

1. Filter by conditional expression

   select * from employees where salary > 12000; # Employee information with salary greater than 12000
   select * from employees where department_id != 90; #Query employee information whose department id is not 90
   select * from employees where department_id <> 90;
   

2. Filter by logical expression

   select last_name, salary, commission_pct 
     from employees
    where salary >= 10000 and salary <= 20000;
   

3. Fuzzy query

   select * from employees where last_name like '%a%'; # Employee name contains character a
   select last_name from employees where last_name like '_\_%'; # The second character in the query employee name is_ Employees
   select last_name from employees where last_name like '_$_%' escape '$'; # The default escape character is \. Here, we define the escape character as$
   

4. between range query

   select * from employees where department_id between 100 and 120; # Employees with numbers between 100 and 120
   

5. in query

   select last_name, job_id from employees where job_id in('IT_PROT', 'AD_VP','AD_PRES'); # In indicates whether the queried field value belongs to an item in the list. If yes, it will be listed
   

6. is null

   select last_name, commission_pct from employees where commission_pct is null; # To judge whether the field is null, you should use is null or is not null instead of = or! =, < >
   

7. Security is equal to < = >, NULL can be judged, but the readability is poor

   select last_name, commission_pct from employees where commission_pct <=> NULL; 
   select last_name, salary from employees where salary <=> 12000;
   

Sort query

Syntax:
select query list from table name [where filter criteria] order by sort list [asc | desc]

1. Query employee information and sort salary from high to low

   select * from employees order by salary desc;
   

2. If the query department number is greater than 90, it will be sorted according to the employment date

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

3. Sort by expression

   # According to the descending order of annual salary, the annual salary is calculated by expression
   select *, salary*12*(1+IFNULL(commission_pct, 0)) as annual_salary
     from employees
    order by  salary*12*(1+IFNULL(commission_pct, 0)) desc;
   # Sort by alias
   select *, salary*12*(1+IFNULL(commission_pct, 0)) as annual_salary
     from employees
    order by  annual_salary desc;
   

4. Sort by function

   select LENGTH(last_name) as name_length, last_name, salary 
     from employees 
    order by LENGTH(last_name) desc;
   

5. Sort by multiple fields

   select * from employees order by salary desc, employee_id asc;
   

Common functions

• Single-Row Functions

  • Character function

    1. length function

       select length('john'); #Gets the number of bytes of the parameter
       select length('zhang wuji');
       

    2. concat splice string

       select concat(last_name,'_',first_name) from employees;
       

    3. uppper, lower function

       select upper('john'); #Capitalize
       select lower('JohN'); #Turn lowercase
       

    4. substr, substring intercept string

       select substr('I Love You', 7); # Intercept all subsequent characters from the seventh character
       select substr('I Love You', 7, 3); # Intercept the next three characters from the seventh character
       

    5. instr returns the first occurrence of a substring

       select instr('I Love You', 'You'); # Returns the starting position of a substring in a string
       

    6. trim clears specific characters before and after a string

       select trim('  ssss   '); # Remove spaces before and after string
       select trim('a' from 'aaaassssaaaa'); # Remove a before and after characters
       

    7. lpad left padding, padding with the specified characters to the specified length

       select lpad('Qiao Feng',10,'*');
       

    8. rpad right fill

       select lpad('Qiao Feng',10,'*');
       

    9. Replace replace

       select replace('Zhang Wuji fell in love with Zhou Zhiruo','Zhou Zhiruo','Zhao Min');
       

  • Mathematical function

    1. Round round

       select round(-1.55);
       select round(1.567, 2);
       

    2. ceil rounded up

       select ceil(1.00)
       

    3. Round down

       select floor(-9.99);
       

    4. truncate

       select truncate(1.999,1);
       

    5. mod remainder

       select mod(10, 3);
       

  • Date function

    1. now() returns the current system date

       select now();
       

    2. Current date returns the current system date

       select curdate();
       

    3. curtime returns the current system time, excluding the date

       select curtime();
       

    4. year(),month(),monthname(),day(),hour(),minute(),second()

       select year(now());
       select month(now());
       select monthname(now());
       select day(now());
       select hour(now());
       select minute(now());
       select second(now());
       

    5. str_to_date converts a string in date format to a date in the specified format

       select str_to_date('9-13-2000','%m-%d-%Y');
       

    6. date_format converts the date to characters

       select DATE_FORMAT('2019/7/7', '%Y year%m month%d day')
       

    Date format:

    Formatter	function
    %Y	Four digit year
    %y	Two digit year
    %m	Month (01,02,..., 11,12)
    %c	Month (1,2,..., 11,12)
    %d	Day (01,02,...)
    %H	Hour (24-hour system)
    %h	Hour (12 hour system)
    %i	Minutes (00,01,... 59)
    %s	Seconds (00,01,... 59)

  • Process control function

    1. if function

       select if(10<5,'large','Small');
       

    2. case

       /*
       Query employee's salary and requirements
        Department No. 30, showing 1.1 times the salary
        Department number 40, showing 1.2 times the salary
        Department number 50, showing 1.3 times the salary
        Other departments, display element salary
       */
       select  
       case department_id 
       when 30 then salary*1.1
       when 40 then salary*1.2
       when 50 then salary*1.3
       else salary
       end salary
       from employees;
       
       select 
       case when department_id = 30 then salary*1.1 
            when department_id = 40 then salary*1.2 
            when department_id = 50 then salary*1.3 
            else salary 
       end salary
       from employees;
       

  • Other functions

    1. View version number

       select version();
       

    2. view the database

       select database();
       

    3. View user

       select user();
       

• Grouping function:

  1. sum

     select sum(salary) from employees;
     

  2. avg average

     select avg(salary) from employees;
     

  3. max

     select max(salary) from employees;
     

  4. min

     select min(salary) from employees;
     

  5. Count count

     select count(salary) from employees;