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.DBdatabase: 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.