Introduction to MySQL

Keywords: MySQL

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


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


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

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

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:

      %YFour digit year
      %yTwo digit year
      %mMonth (01,02,..., 11,12)
      %cMonth (1,2,..., 11,12)
      %dDay (01,02,...)
      %HHour (24-hour system)
      %hHour (12 hour system)
      %iMinutes (00,01,... 59)
      %sSeconds (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
        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;
        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;

Posted by TheDefender on Sat, 18 Sep 2021 23:36:51 -0700