MySQL basic learning notes - Day01

Posted by Hurklefish on Sat, 18 Dec 2021 04:19:36 +0100

1. Concept of database

DB:
database: a "warehouse" for storing data. It holds a series of organized data;
DBMS:
Database Management System. Database is a container created and operated by DBMS;
SQL:
Structure Query Language: a language specially used to communicate with the database;
Relationship diagram of the three:

2. Common commands

2.1 start service

net start mysql

2.2 cessation of service

net stop mysql

2.3 login to mysql server

mysql -h localhost -P 3306 -u root -p
#-h refers to host
#-P refers to Port
#-u refers to the user name
#-p refers to the user password

Note: - pxxxx can be followed by the password, but there can be no spaces

2.4 exit MySQL server

Just enter the exit command directly

2.5 basic database operation commands

2.5. 1. View all current databases show databases;

2.5. 2 open the specified library use xxx xxx refers to the library name

2.5. 3. View all tables of the current library show tables;

2.5. 4. View all tables of other libraries show tables from xxxx;

2.5. 5. View the table structure desc xxxx table name;

2.5. 6 view the version of the server
Method 1: log in to mysql server select version();

Method 2: do not log in to mysql server mysql --version
(the premise is that the windows system has been configured with mysql environment variables)

3. Basic query

Framework diagram of DQL(Data Query Language) learning:

3.1 import myemployees SQL file, do the following
Summary:

The emphasis sign can be used to distinguish between fields and keywords
Note the usage rules of SQL +

USE myemployees;
#1. Single field in query 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`,
    `phone_number`,
    `last_name`,
    `job_id`,
    `phone_number`,
    `job_id`,
    `salary`,
    `commission_pct`,
    `manager_id`,
    `department_id`,
    `hiredate` 
FROM
    employees ;
#Mode 2:  
 SELECT * FROM employees;
 
 #4. Query constant value
 SELECT 100;
 SELECT 'john';
 
 #5. Query expression
 SELECT 100%98;
 
 #6. Query function
 SELECT VERSION();
 
 #7. Alias
 /*
 ①Easy to understand
 ②If the fields to be queried have duplicate names, aliases can be used to distinguish them
 ③The emphasis sign can be used to distinguish between fields and keywords
 */
 #Method 1: use as
SELECT 100%98 AS result;
SELECT last_name AS surname,first_name AS name FROM employees;

#Method 2: use spaces
SELECT last_name surname,first_name name FROM employees;

#Case: query salary, and the display result is out put
SELECT salary AS "out put" FROM employees;

#8. Weight removal
#Case: query all department numbers involved in the employee table
SELECT DISTINCT department_id FROM employees;

#9. + function
/*
java + sign in:
①Operator, both operands are numeric
②Connector, as long as one operand is a string
mysql + sign in:
There is only one function: operator
select 100+90; If both operands are numeric, add
select '123'+90;As long as one of them is character type, try to convert character type numerical value to numerical value
			If the conversion is successful, continue the addition operation
select 'john'+90;If the conversion fails, the character value is converted to 0
select null+10; As long as one of them is null, the result must be null
*/

#Case: query the employee's first name and last name, connect them into a field, and display them as names
SELECT CONCAT('a','b','c') AS result;
SELECT 
	CONCAT(last_name,first_name) AS full name
FROM
	employees;

4. Condition query

4.1 filter by conditional expression

Case 1: query employee information with salary > 12000
```
SELECT *
FROM employees
WHERE salary>12000;
```

Case 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;
	#department_id !=90; #Same effect

4.2 filter by logical expression

Case 1: query employee name, salary and bonus with salary z between 10000 and 20000

SELECT
	last_name,
	salary,
	commission_pct
FROM
	employees
WHERE
	salary>=10000 AND salary<=20000;

#Case 2: query the information of employees whose department number is not between 90 and 110 or whose salary is higher than 15000
```
SELECT
	*
FROM
	employees
WHERE
	NOT(department_id>=90 AND  department_id<=110) OR salary>15000;
```

4.3 fuzzy query

4.3.1 LIKE

characteristic:
Generally used in combination with wildcards, wildcards:
%Any number of characters, including 0 characters
_ Any single character
Case 1: query employee information with character a in employee name
```
select 
	*
from
	employees
where
	last_name like '%a%';#abc
```
Case 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%';
```

Case 3: the second character in the employee name is_ Employee name

SELECT
	last_name
FROM
	employees
WHERE
	last_name LIKE '_$_%' ESCAPE '$';
	#last_name LIKE '_\__%'; #Same effect

4.3.2 BETWEEN AND
Note: using between and can improve the brevity of statements, including critical values, and do not change the order of critical values

Case 1: query employee information with employee number between 100 and 120

SELECT
	*
FROM
	employees
WHERE
	employee_id >= 120 AND employee_id<=100;
#----------------------
SELECT
	*
FROM
	employees
WHERE
	employee_id BETWEEN 120 AND 100;```

4.3.3 IN
Note: judge whether the value of a field belongs to an item in the in list; ① Use in to improve the conciseness of sentences; ② The value types of in list must be consistent or compatible; ③ Wildcards are not supported in the in list

Case: the job number of the employee queried is IT_PROG,AD_VP,AD_ An employee name and job number in Pres

SELECT
	last_name,
	job_id
FROM
	employees
WHERE
	job_id = 'IT_PROT' OR job_id = 'AD_VP' OR JOB_ID ='AD_PRES';
#------------------
SELECT
	last_name,
	job_id
FROM
	employees
WHERE
	job_id IN( 'IT_PROT' ,'AD_VP','AD_PRES');

4.3.4 IS NULL is used to judge whether a field is null

Case 1: query employee name and bonus rate without bonus

SELECT
	last_name,
	commission_pct
FROM
	employees
WHERE
	commission_pct IS NULL;

Case 2: query employee name and bonus rate with bonus

SELECT
	last_name,
	commission_pct
FROM
	employees
WHERE
	commission_pct IS NOT NULL;```

4.3. 5 < = > safety equals
IS NULL: only the NULL value can be judged, which is highly readable and recommended;
< = >: it can judge both NULL value and ordinary value, with low readability;