# database understanding
MySQL
DB
Database, container for storing data
DEMS
Database management system, also known as database software or database products, is used to create or manage DB
SQL
Structured query language, the language used to communicate with the database, is not unique to a database software, but a common language for almost all mainstream database software
Advantages of MySQL
1. Open source, free and low cost
2. High performance and portability
3. Small size, easy to install
cmd commands after MySQL installation
- Login
mysql -h local -P 3306 -u root -p1020 mysql -u root -p1020
- Show all databases
show databases;
- Create database
create database Database name;
- Use database
use Database name
- View all tables in the current library
show tables;
- Create table
create table Student( -> Sno int not null auto_increment primary key, -> Sname varchar(10) not null, -> Sex char(1) not null, -> Sage tinyint(100) not null, -> Sdept char(4) not null)comment = 'Student list';
- Delete table
drop table Table name;
- Use other libraries
Use directly under the current library use Table name;
DQL language learning
Basic query
grammar: select Query list from Table name; be similar to:System.out.Println(Printed things)
characteristic:
1. The query list can be: fields, constant values, expressions and functions in the table
2. The query result is a virtual table
Query 1
#1. Query the current field in the table SELECT last_name FROM employees; #2. Query multiple fields in the table SELECT last_name,salary,email FROM employees; #3. Query all fields in the table #Mode 1: SELECT `employee_id`,`first_name`,`last_name`,`email`,`phone_number`,`job_id`,`salary`,`commission_pct`,`manager_id`,`department_id`,`hiredate` FROM employees ; #Mode 2: SELECT * FROM employees
be careful
Before using which library,Should first: USE Library name ''It distinguishes between fields and keywords Select command,have access to f12 Code formatting
query
# Query constant value SELECT 100; SELECT 'john'; # Query expression SELECT 100%98; # Query function SELECT VERSION();
Alias
#1. Alias /* Easy to understand If the fields queried have duplicate names, aliases can be used to distinguish them AS Can be omitted */ #Mode 1 SELECT 100%98 AS result; SELECT last_name AS surname,first_name AS name FROM employees; #Mode 2: SELECT last_name surname,first_name name FROM employees; #Query the slot, and the result is out put (if the alias has spaces, double quotation marks are recommended) SELECT salary AS "out put" FROM employees;
duplicate removal
#De duplication DISTINCT #Query all department numbers in the employee table SELECT DISTINCT department_id FROM employees; #+Function of No /* mysql The + in has only one function: operator select 100+90:If both operations are numeric, add select '123'+90;One of them is character type, and the character type value is converted to numeric type Conversion succeeded. Continue adding select 'jphn'+90; If the conversion fails, convert the character value to 0 select null+10; As long as one of them is null, the result must be null */ Query employee name and last name linked into a field,And display as name SELECT last_name+first_name AS full name FROM employees; SELECT NULL+10;
Functions: connect CONCAT()
#Connect the CONCAT function (field name to be connected) SELECT CONCAT(first_name,last_name) AS full name FROM employees;
Display table structure
DESC departments;
Function: IFNULL()
#IFNULL(expr1,expr2) SELECT IFNULL(commission_pct,0) AS Bonus rate,commission_pct FROM employees;
Function: ISNULL()
Determine whether a field or expression is null,If yes, it returns 1, otherwise it returns 0
Condition query
grammar: select Query list from Table name where Screening conditions; 1 2 3 Execution sequence 2-->3--->1 Classification of filter criteria: 1.Filter by conditional expression: > < = !=(<>) >= <= 2.Filter by logical expression: && || ! and:If both conditions are true,Result nine tails true,Otherwise false or:As long as one condition is true,The result is true,Otherwise false not 3.Fuzzy query: like between and in is null
be careful
-
like:
Generally used with wildcards
%Any number of characters, including 0 characters
_ placeholder -
Escape character
(\), but (custom escape character) 'is recommended_ KaTeX parse error: Expected group after '_' at position 1: _ ̲%' ESCAPE '’;
-
between and
1. Using between and can improve the simplicity of the code
2. The critical values at both ends include
3. Do not change the order of the two critical values -
in
Meaning: judge whether the value of a field belongs to an item in the in list
characteristic:
1. Using in can improve simplicity
The values of the 2.in list must be uniform or compatible (implicit conversion) -
is null
=Or < > cannot judge null value
is null -
Safety equals < = >
is null can only judge the null value, with high readability
< = > you can judge both values and null s, but the readability is not high
case
#case #1. Query salary > 12000 SELECT * FROM employees WHERE salary>12000; #2. Query the employee name and department number whose department number is not equal to 90 SELECT last_name,department_id FROM employees WHERE department_id<>90; #Logical expression filtering #3. Query employee name, salary and bonus with salary between 10000-20000 SELECT last_name,salary,commission_pct FROM employees WHERE salary>=10000 AND salary<=30000; #4. Query the information of employees whose department number is not between 90-110 and whose living salary is higher than 15000 SELECT * FROM employees WHERE NOT(department_id>=90 && department_id<=110) || salary>=15000; #Fuzzy query /* like: 1.Generally used with wildcards % Any number of characters, including 0 characters _ placeholder */ #1.like employee information with character a in employee name SELECT * FROM employees WHERE last_name LIKE '%a%'; #2. Query the employee name and salary with the third character n and the fifth character l in the employee name SELECT last_name, salary FROM employees WHERE last_name LIKE '__n_l%'; #3. Escape character (\), but it is recommended to use (user-defined escape character) '$%' ESCAPE '$'; #The second character in the query employee name is_ Employee name SELECT last_name FROM employees WHERE last_name LIKE '_$_%' ESCAPE '$'; #between and /* 1.Using between and can improve the simplicity of the code 2.The critical values at both ends include 3.Two critical values, do not change the order */ #Case query employee information with employee table between 100-200 SELECT * FROM employees WHERE employee_id BETWEEN 100 AND 120; #in /* Meaning: judge whether the value of a field belongs to an item in the in list characteristic: 1.Using in can improve simplicity 2.in The values of the list must be uniform or compatible (implicit conversion) */ #1. The type of work name of the query employee is ad_ VP IT_ PROG AD_ An employee name and job number in Pres SELECT last_name, job_id FROM employees WHERE job_id IN ('AD_VP','IT_PROG','AD_PRES'); # is null /* =Or < > cannot judge null value is null */ # Query employee name and bonus rate without bonus SELECT last_name, commission_pct FROM employees WHERE commission_pct IS NULL; #Safety equals < = > /* is null Only the null value can be judged, and the readability is high <=> Both numeric and null values can be judged, but the readability is not high */ SELECT last_name, commission_pct FROM employees WHERE commission_pct <=>NULL; #The name, department number and annual salary of the employee with employee No. 176 are queried in the case SELECT last_name, department_id, salary*12*(1+IFNULL(commission_pct,0)) AS Annual salary FROM employees WHERE job_id = 176;
Sort query
-
Syntax:
select Query list from surface [where Screening conditions] order by Sort list [asc(Ascending order)|desc(Descending order)]
-
characteristic:
1.asc indicates ascending and desc indicates descending. If not written, it defaults to ascending
2. Alias, single field, multiple fields, expressions and functions are supported after order by -
case
#Case: query employee information and require salary to be sorted from high to low SELECT * FROM employees ORDER BY salary DESC; #Case: query the employee information with department number > = 90 and arrange it in chronological order SELECT * FROM employees WHERE department_id>=90 ORDER BY hiredate ASC; #The case displays employee information and annual salary according to the annual salary [sort by alias] SELECT *,salary*12*(1+IFNULL(commission_pct,0)) AS Annual salary FROM employees ORDER BY Annual salary DESC; #Cases are sorted by the length of employee names [sorted by function] SELECT LENGTH(last_name) AS Byte length,last_name,salary FROM employees ORDER BY LENGTH(last_name) DESC; #Case query employee information, first in ascending order by salary, then in descending order by employee number [multiple fields] SELECT * FROM employees ORDER BY salary ASC,employee_id DESC;
Introduction to common functions
Character function
-
length gets the number of bytes of the string
-
concat splice string
-
Upper and lower case conversion
-
substr intercept string
-
instr returns the index of the substring since the last occurrence
-
trim removes spaces before and after
-
lpad implements left padding with the specified characters to the specified length
-
RPAD implements right padding with the specified characters to the specified length
-
Replace replace
-
code
#length gets the number of bytes of the parameter value SELECT LENGTH('john'); #utf8 one Chinese character takes up 3 bytes SELECT LENGTH('Hello hahaha'); #CONCAT splice string SELECT CONCAT(last_name,'_',first_name) full name FROM employees; #Upper and lower case conversion #Change the last name to uppercase, the first name to lowercase, and then splice SELECT CONCAT(UPPER(last_name),'_',LOWER(first_name)) full name FROM employees; #substr intercept string #Note: the index in the sql statement starts from 1 SELECT SUBSTR('Who are you',2) SELECT SUBSTR('Who are you',1,3) #Case, capitalize the first character of the Chinese name and lowercase other characters, and use_ Splicing SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)),'_',LOWER(SUBSTR(last_name,2))) AS output FROM employees; #instr returns the index of the first occurrence of the substring. If it is not found, it returns 0 SELECT INSTR('You are a good person.','good person') AS output; #trim before and after spaces SELECT LENGTH(TRIM(' good person ')) AS output; SELECT TRIM('a' FROM 'aaaaa good person aaaaa ah aaaaa') AS output; #LPAD implements left padding with the specified characters to the specified length SELECT LPAD('NI',10,'*') AS OUTPUT; #RPAD implements right padding with the specified characters to the specified length SELECT RPAD('NI',10,'*') AS OUTPUT; #Replace replace SELECT REPLACE('It's a fine day today','today','Friday') AS pm;
Mathematical function
#rounding SELECT ROUND(1.45);#Default rounding SELECT ROUND(1.567,3);#Keep 3 digits after the decimal point #ceil rounding up returns > = the smallest integer of the parameter SELECT CEIL(1.002); #floor rounded down and returns < = the maximum integer of the parameter SELECT FLOOR(-9.9); #truncate SELECT TRUNCATE(1.6999999,2); #mod remainder /* mod(a,b) a-a/b*b Related to divisor */ SELECT MOD(10,3);
Date function
[external chain picture transfer failed. The source station may have anti-theft chain mechanism. It is recommended to save the picture and upload it directly (img-96butt8d-1637852683310)( file:///C: \Users \ Yongxing \ AppData\Roaming\feiq\RichOle577038.bmp)]
#now returns the current system date + time SELECT NOW(); #Current date returns the current date, excluding time SELECT CURDATE(); #curtime returns the current time, excluding the date SELECT CURTIME(); #You can get the specified part, month, day, hour SELECT YEAR(NOW()) year; SELECT MONTH(NOW()) month; SELECT MONTHNAME(NOW()) month; #str_to_date converts characters to a date in the specified format SELECT STR_TO_DATE('2021-2-12','%Y-%m-%d') AS date; #Query employee information whose employment date is 1992 -- 4-3 SELECT * FROM employees WHERE hiredate = '1992-4-3'; #DATE_FORMAT converts dates to characters SELECT DATE_FORMAT(NOW(),'%y year%m month%d day') AS op; #Query the employee name and employment date of employees with bonus SELECT last_name,DATE_FORMAT(hiredate,'%m month-%d day %y year') AS Entry date FROM employees WHERE commission_pct IS NOT NULL;
Other functions
SELECT VERSION(); SELECT DATABASE(); SELECT USER();
Process control function
#1.if function and if else effect SELECT IF(10>5,'large','Small'); SELECT last_name,commission_pct,IF(commission_pct IS NULL,'No bonus','Bonus') remarks FROM employees; #2.case function: switch case effect /* case Field or expression to judge when Constant 1 then the value 1 or statement 1 to be displayed; when Constant 2 then the value 2 or statement 2 to be displayed; ... else The value n or statement n to display; end */