Database (mysql) learning - (updated irregularly)

# 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

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

*/

Tags: Database MySQL SQL

Posted on Thu, 25 Nov 2021 13:15:57 -0500 by ataria

Programmer Help

Database (mysql) learning - (updated irregularly)

MySQL

DB

DEMS

SQL

Advantages of MySQL

cmd commands after MySQL installation

DQL language learning

Basic query

Query 1

be careful

query

Alias

duplicate removal

Functions: connect CONCAT()

Display table structure

Function: IFNULL()

Function: ISNULL()

Condition query

be careful

case

Sort query

Introduction to common functions

Character function

Mathematical function

Other functions

Process control function

Hot Tags