preface

Database technology is a necessary skill for Java engineers. Starting from MySQL software, this paper preliminarily introduces the concepts of database and database management system, as well as the basic application of SQL language.

1, Concept of database

1.DB

database: the "warehouse" where data is stored. It holds a series of organized data.

2.DBMS

Database Management System. A database is a container created and operated by a DBMS
DBMS is divided into two categories:
– DBMS (Access) based on shared file system
– client server based DBMS (MySQL, Oracle, SqlServer)

3.SQL

Structure Query Language: a language specially used to communicate with the database. It is not a language exclusive to a specific database vendor. Almost all DBMS support SQL

2, Database management system (DBMS) – MySQL

1. Download and install MySQL 5.5

Official website: https://downloads.mysql.com/archives/community/

MSI version is the installation version. Select the version suitable for your computer

2. Start and stop & login and logout

Start and stop of MySQL service

Method 1: Computer - right click management - Service

Mode 2 (recommended): run CMD (command prompt) as an administrator
net start service name (start service)
net stop service name (stop service)

Login and logout of MySQL service

Method 1: use the built-in client of mysql, but only the root user

Method 2: run CMD (command prompt) as an administrator
Sign in:
mysql [- h hostname - P port number] - u username - P password
[- h hostname - P port number] can be omitted
sign out:
exit or ctrl+C

3. Common MySQL commands

1. View all current databases
show databases;
2. Open the specified library
use library name
3. View all tables in the current library
show tables;
4. View all tables of other libraries
show tables from library name;
5. Create table
create table table name(
Column name, column type,
Column name, column type,
. . .
);
6. View table structure
desc table name;
7. View the version of the server
Method 1: log in to the mysql server
select version();
Method 2: no login to mysql server
mysql --version
or
mysql --V

4. Installation of graphical user interface client

See blogger space for SQLyog-10.0.0.0 installation package and cracking code

Four tables in the myemployees Library

3, SQL language

1. Classification of SQL language

DQL (data query language): Data Query Language
select

DML (data manipulation language): Data Manipulation Language
insert ,update,delete

DDL (data definition language): Data Definition Language
create,drop,alter

TCL (Transaction Control Language): Transaction Control Language
commit,rollback

2. Basic query of query statement

#Advanced 1: basic query
/*
Syntax:
select Query list from table name;


Similar to: system. Out. Println (printing things);

characteristic:

1,The query list can be fields, constant values, expressions and functions in the table
2,The result of the query is a virtual table
*/

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
 
 */
 #Method 1: use as
SELECT 100%98 AS result;
SELECT last_name AS lastname,first_name AS name FROM employees;

#Method 2: use spaces
SELECT last_name lastname,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;
	

3. Conditional query of query statement

#Advanced 2: condition query
/*

Syntax:
	select 
		Query list
	from
		Table name
	where
		Screening conditions;

Classification:
	1, Filter by conditional expression
	
	Simple conditional operator: > < =! = < > =<=
	
	2, Filter by logical expression
	Logical operators:
	Action: used to join conditional expressions
		&& || !
		and or not
		
	&&And and: both conditions are true, and the result is true; otherwise, it is false
	||Or or: as long as one condition is true, the result is true, otherwise it is false
	!Or not: if the connection condition itself is false, the result is true; otherwise, it is false
	
	Three, fuzzy query
		like
		between and
		in
		is null
	
*/
#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;


#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;
#Three, fuzzy query
/*
like

	
	
between and
in
is null|is not null

*/
#1.like
/*
characteristic:
①Generally used with wildcards
	Wildcard:
	% 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 e and the fifth character a in the employee name
select
	last_name,
	salary
FROM
	employees
WHERE
	last_name LIKE '__n_l%';



#Case 3: query the employee name whose second character is in the employee name

SELECT
	last_name
FROM
	employees
WHERE
	last_name LIKE '_$_%' ESCAPE '$';
#2.between and
/*
①Using between and can improve the brevity of statements
②Including critical value
③Do not change the order of the two 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;

#3.in
/*
Meaning: judge whether the value of a field belongs to an item in the in list
 characteristic:
	①Using in to improve statement conciseness
	②in The value types of the list must be consistent or compatible
	③in Wildcards are not supported in the list
	

*/
#Case: the work type number of the query employee is an employee name and work type number in IT_PROG, AD_VP and AD_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,is null
/*
=Or < > cannot be used to determine null values
is null Or is not null to determine the null value




*/

#Case 1: query employee name and bonus rate without bonus
SELECT
	last_name,
	commission_pct
FROM
	employees
WHERE
	commission_pct IS NULL;


#Case 1: query employee name and bonus rate with bonus
SELECT
	last_name,
	commission_pct
FROM
	employees
WHERE
	commission_pct IS NOT NULL;

#----------The following is ×
SELECT
	last_name,
	commission_pct
FROM
	employees

WHERE 
	salary IS 12000;
	
	
#Safety equals < = >


#Case 1: query employee name and bonus rate without bonus
SELECT
	last_name,
	commission_pct
FROM
	employees
WHERE
	commission_pct <=>NULL;
	
	
#Case 2: query employee information with salary of 12000
SELECT
	last_name,
	salary
FROM
	employees

WHERE 
	salary <=> 12000;
	

#is null pk <=>

IS NULL:Can only judge NULL Value, high readability, recommended
<=>    :Can judge NULL Value, and can judge ordinary values, with low readability

4. Sort query of query language

#Advanced 3: sort query
/*
Syntax:
select Query list
from Table name
[where  [filter criteria]
order by Sorted fields or expressions;


characteristic:
1,asc It represents ascending order and can be omitted
desc It represents descending order

2,order by Clause can support single field, alias, expression, function and multiple fields

3,order by Clause is at the end of the query statement, except for the limit clause

*/

#1. Sort by single field
SELECT * FROM employees ORDER BY salary DESC;

#2. Add filter criteria and then sort

#Case: query the employee information with department No. > = 90 in descending order by employee No

SELECT *
FROM employees
WHERE department_id>=90
ORDER BY employee_id DESC;


#3. Sort by expression
#Case: query employee information in descending order of annual salary


SELECT *,salary*12*(1+IFNULL(commission_pct,0))
FROM employees
ORDER BY salary*12*(1+IFNULL(commission_pct,0)) DESC;


#4. Sort by alias
#Case: query employee information in ascending order of annual salary

SELECT *,salary*12*(1+IFNULL(commission_pct,0)) Annual salary
FROM employees
ORDER BY Annual salary ASC;

#5. Sort by function
#Case: query employee names in descending order by name length

SELECT LENGTH(last_name),last_name 
FROM employees
ORDER BY LENGTH(last_name) DESC;

#6. Sort by multiple fields

#Case: when querying employee information, it is required to first press salary descending order, and then press employee_id ascending order
SELECT *
FROM employees
ORDER BY salary DESC,employee_id ASC;

summary

This is the beginning of learning database, mainly involving the application of SQL query language.