Friends work reasons, the Dragon Boat Festival is not idle, with the scourge of sorting out the DATABASE SQL instructions, and sorting out the supporting exercises for practice, I just don’t have what, the cause of the epidemic what also can’t do, to accompany him to sort out a wave, while pulling a wave of wool, hey hey hey

However, because it is really too much text, so only show a part of the need for complete documentation and SQL files, the public number: Java architect union, add a small assistant to get it in the background

Day 1:

Lesson 1: Basic concepts of database

1. Data books

It’s a collection of special symbols that represent information

For example: picture, TXT, audio, video and so on

Introduction: The development of the Internet interaction is actually data

2. Database: Library

The things used to store data are called databases.

3, database management system application library management system

A system that stores data to a database in a specified way is called a database management system

Efficient retrieval of stored data (key points)

Database application system —- for ordinary users to use, such as Taobao

4. Database system

Database administrator

The database

Database management system

Database application system

The user

5,

Oradata: information about the database

Product: information about the database management system

Bin: startup directory of the DATABASE management system software

JDBC: JAR packages required for database and Java connections

Network: network configuration directory required by the database management system

Log directory: the directory where oracle exception logs are stored

Lesson 2: Common databases and database storage structures

1. Database storage data structure

The reticular structure

hierarchy

Table structure:

Two-dimensional table structure can clearly express the relationship between data and data

Note:

It is simple to store data, but the relationship between data must be clearly described

Therefore, it is more reasonable to use the structure of two-dimensional tables to store data

2. Database management system

Oracle: large database management system

Mysql: small to medium size

DB2: Small and medium sized

Acess: small

Sql Server: large database

.

3. Refer to PPT for history introduction of ORACLE CE

Lesson 3: Oracle installation and oracle directory structure introduction and uninstall

1. Oracle Installation:

Refer to baidu search “Oracle11G Installation Diagram”

Global database name: The default is ORCL, which uniquely identifies the database

SID: A name that uniquely identifies data in a program

2. Oracle services :(services to be enabled)

OrcaleServiceorcl: Orcale starts the service

OracleOraDb11g_home1TNSListener: Oracle listener service

OracleDBConsoleorcl: Console service

3. Oracle Account:

Sys: super administrator

System: administrator

Scott: The default password of a common user is root

Mysql > alter database password;

On the CMD cli, run the sqlplus/as sysdba command

Then use the command:

Alter user USER name identified by new password;

Use the command sqlplus /nolog from the CMD command line

Then use the command conn as sysdba

Then enter a known user name and password

Then use the command:

Alter user USER name identified by new password;

5. Create a user:

You must use the System account

Lesson 4: Oracle directory and uninstall

1, Oracle directory introduction:

Oradata: the directory where the database stores files

db_home:

Network >admin: Configure the network service and listener service

JDK: oracle comes with the JDK

Deinstall: indicates the uninstallation command

JDBC: JAR package that interacts with Java

2, Oracle uninstall:

Uninstall using the built-in oracle program

Delete app directory

Delete registry

Deleting environment variables

Lesson 4: Oracle listener and server configuration

Question 1:

My computer installed oracle CE, my deskmate how to check the data?

Client:

Software intended for ordinary users, mainly used to send user requests.

Server:

Accept and process user requests.

Question 2: Oracle has been installed on John and John, and Client has been installed on John, how does John access John?

ip

tcp

Database global name

The port number

Question 3:

From the server’s point of view, if 1000 requests come in, how do you tell which are requesting the database

The listener

Third party tools: PLSQL (not client)

Configuring local network services :(client)

The first is direct manipulation using violence:

Modification: C: \ apps \ Administrator \ product \ 11.2.0 \ dbhome_1 \ NETWORK \ ADMIN \ tnsnames ora

ORCL = > Network service name

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = server IP ADDRESS)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = orcl)

)

)

The second way:

Use graphical interface: NCA

Configuring listeners :(server side)

FAQ:

To configure listeners, the computer name must be in English

On the first day, the main task is to install the database and configure the network services of the database. On the second day, we learn SQL statements

= = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =

Day 2:

First lesson: INTRODUCTION to SQL language

1. Introduction of database tables

Emp table: Employee table

Dept table: Department table

Salgrady: Salary scale

The Balance:

2, basic query statement:

Knowledge:

Select * from table name to query all data in the table

Select * from (select * from (select * from (select * from (select * from (select * from)))) From table name Queries all the values of a specified field. Different fields are separated by commas

Use arithmetic expressions: select field name (operator), field name (operator)… From the name of the table

Case study:

Select * from emp– Query information about all employees

Select ename,job,sal from emp select ename,job,sal from emp

Query the salary of all employees for one year, and all salary of each employee after the end of 3000 years (excluding allowances)

Select ename,job,sal*12,sal*12+3000 from emp

Second lesson:

1. Use an alias

As can be omitted, and aliases can be added using double quotes if special characters get Spaces

Select ename,job,sal*12+3000 from emp select ename,job,sal*12+3000 from emp

Select ename,job,sal*12 ‘,sal*12+3000 from emp select ename,job,sal*12+3000 from emp

Select ename,sal from emp where ename,job,sal,sal,sal,sal,sal,sal,sal,sal,sal,sal,sal,sal,sal,sal,sal,sal,sal,sal,sal

Select ename,job,sal*12 years,sal*12+3000 from emp use double quotation marks if there are Spaces or other special characters in the alias

2, use the link:

select ename,job,sal from emp;

select ename||job,sal from emp; Using | | symbol characters links

Select ename | | ‘work is’ | | job | |’ and monthly salary is’ | |, sal from emp; Character links, common characters use single quotes

3, remove duplicate distinct

select * from emp;

Select job from EMP — Find that the job is duplicated and the type of job needs to be removed

Question: Check the type of work the employee does

Select distinct job from EMp Use the distinct keyword to remove duplicate values

Problem; How to remove multiple field duplicates

select distinct job,sal from emp; Multi-field removes duplication, which removes a single piece of information that is repeated

4, sorting,

Knowledge:

Select name, name… From table name order by table name

Select *from table_name order by table_name

Explanation:

— Query employee name, job, salary

select ename,job,sal from emp

Select * from employees where salary is ordered by name, job, salary

select ename,job,sal from emp order by sal; Use order by to sort

select ename,job,sal from emp order by sal desc; Sort from highest to lowest using desc key

select ename,job,sal from emp order by sal asc; Sort from smallest to largest with ASC key. The default is sort from smallest to largest

Select * from emp order by sal,ename from emp order by sal,ename from emp order by sal

Select ename,job,sal from emp order by sal

Select ename,job,sal from emp order by sal*12

Lesson 3: Simple WHERE clause

Results are filtered using where criteria

Select * from table_name where table_name = 1; Note: The field values in the condition are case sensitive, the field names are case insensitive, and the field values are enclosed in single quotes

Where =,<,>,>=,<=,<>

2, Order by to sort the filter results

View information about employees whose salaries are equal to 1250

Select * from emp where sal=’1250′ select * from emp where sal=’1250

Select * from CLERK where job = CLERK

Select * from EMp where job=’CLERK’ (select * from EMp where job=’CLERK’

Select * from EMp where job=’ CLERK ‘– select * from EMp where job=’clerk

Select * from EMp where JOB=’CLERK’ select * from EMp where JOB=’CLERK

View the names and jobs of employees whose salaries are greater than 1250

Select ename,job,sal from emp where sal>’1250′ order by sal

View information about employees whose salaries are greater than or equal to 2000

select * from emp where sal>=2000;

View the information of employees whose salary is less than or equal to 2000.

select * from emp where sal<=2000

View information about employees whose salaries are not equal to 1500

select * from emp where sal<>1500 order by sal

View information about employees whose start dates are after 81 years

Select * from emp where hiredate>’ 31 月 1981 ‘

select * from emp where hiredate>’1981/12/31′

Select * from emp where hiredate>’31-12 -1981′ select * from emp where hiredate>’31-12 -1981

Lesson 4: Where clauses use keywords

Use the WHERE clause to filter the results

Select * from ‘where’ where ‘where

Select * from table_name where table_name and table_name and table_name….

Select * from (select * from (select * from (select * from (select * from (select * from (select * from))….

Select * from table_name where table_name =….

Select * from table_name where table_name = ‘% ‘where table_name = ‘%

Select * from table_name where table_name is null

(AND, or,like, is null,is not null,between and,)

Select * from employees whose salary is between 2000 and 3000

Select * from emp where sal>=2000 and sal<=3000;

select * from emp where sal between 2000 and 3000; — Filter between and keyword;

— Query information about SALESMAN,ANALYST, and MANAGER employees

Select * from emp where job=’SALESMAN’ or job=’ANALYST’ or job=’MANAGER’ select * from EMp where job=’ANALYST’ or job=’MANAGER

select * from emp where job in(‘SALESMAN’,’ANALYST’,’MANAGER’); Use the in keyword to filter “or”

select * from emp where job=’ANALYST’

— Query names containing s, beginning with s, ending with s, and second character A.

select * from emp where ename like ‘%S%’; — Use the like keyword, the name contains S, and % stands for any number of characters

select * from emp where ename like ‘S%’; — Use the “like” keyword, starting with “S”

select * from emp where ename like ‘%S’; — “S”

Select * from emp where ename like ‘_A%’ select * from emp where ename like ‘_A%’ where ename like ‘_A%’ select * from emp where ename like ‘_A%’ where ename like ‘_A%

————– Query information about users whose names contain underscores (_)

select * from emp where ename like ‘%A_%’escape ‘A’; Use the escape keyword to set a normal character to a translation character.

— Query information about employees with benefits

select * from emp where comm is not null;

select * from emp where comm is null; — Query information about employees without benefits

Lesson 5: Function learning

SQL > select * from SALESMAN, MANAGER where salary > 2500

Use parentheses to raise the execution priority of where filters

2. And has a higher priority than OR

select * from emp

select * from emp where job=’SALESMAN’ or job=’MANAGER’ and sal>2500

select * from emp where (job=’SALESMAN’ or job=’MANAGER’) and sal>2500

———————————————————————————————————–

Use function single-line function multi-line function to convert functions to other functions

Single-line function learning: do not change the original data, only change the result

1. Character functions

select * from emp;

select INITCAP(ename) from emp; The initcap function capitalizes the first letter

select lower(ename)from emp; –lower lowercase

select replace(ename,’S’,’M’) from emp; – replace replace

–2, numerical function –Math

Dual – pseudo table

select * from dual

Select abs(-3) absolute value, ceil(3.1415926) rounded up,floor(3.1415926) rounded down,power(2,3) power,round(3.4) rounded from dual

–3. Date function

Select months_BETWEEN (‘ 13-12/2016 ‘,’ 13-10/2016 ‘) from dual–months_between Number of months between two dates

Lesson 6: Multi-line functions

Multiline functions:

Max: Max (field name) Returns the maximum value of this field

Min: min(field name) Returns the minimum value for this field

Avg: avG (field name) returns the average value

Sum: sum (field name) Returns the sum of fields

count:count

–count(*), which checks how many records there are in the table

–count (field name), used to query the number of values of a field

–count(name of a distinct field), which can remove duplicates before counting.

Note:

Multi-line functions cannot appear directly in a query statement with normal fields, unless group by

Multi-line and single-line functions cannot appear directly in a query unless group by is used

Check the employee’s highest salary

Select Max (sal),ename from emp– Multiple line functions cannot appear directly in a query with normal fields, unless group by

Select Max (sal), lower(ename) from EMp — Multi-line and single-line functions cannot appear directly in a query unless group by

select ename from emp

Check your employee’s minimum wage

select min(sal) from emp

Check the average employee salary

select avg(sal) from emp

View the sum of all employees’ salaries

select sum(sal) from emp

Find out how many employees the company has

select * from emp

Select count(*) from EMp — Use count(*) to see how many records there are in a table

Query the number of employees with allowances

Select count(comm) from emp– use count(field name) to query the number of records for which the field has a value

select count(ename) from emp

Find out how many job types the company has

select count(job) from emp

select count(distinct job) from emp

= = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =

Day 3:

First lesson: Learning transformation functions

Conversion function: During conversion, the data type is changed, the data content does not change, you can specify the format.

1. To_number: Converts numeric characters to numeric values, to_number(numeric characters)

To_char (number/date); to_char(number/date); to_char(number/date)

To_date: converts a date from a character type to a date type: to_date(char)

————————————————————————————–

Number –>char uses the default format,

Select to_char(123) from dual select to_char(123) from dual select to_char(123) from dual

select sal, to_char(sal) from emp

Number –>char uses the specified format

To_char (number,’ format ‘)

The $represents the dollar sign, and the 9 code digit is a placeholder. For example, L999,999,999 indicates the display mode of a group of three digits. L represents the SYMBOL of RMB, and 0 represents the number of reserved digits.

The select to_char (sal), to_char (sal, L999, ‘999999’) from emp

The select to_char (sal), to_char (sal, ‘L0000.00) from emp

Char –>number to_number

select to_number(‘123’) from dual

Char –>date: the converted character string must be in the date format. The default format is DD-MM-YYYY

Note: Because there are so many strings, you need to specify a format when converting a character to a date, because a date is a formatted combination of characters.

When a character is converted to a date, the format is the date order of the characters without specifying an interval character.

Yyyy: year MM: month DD: day

Select to_date(’05 — 12月 — 2016′) from dual;

Select to_date(‘2016-05-12′,’ YYYY-MM-DD ‘) from dual– convert the specified date string to a date using the specified format

select to_date(’12-05-2016′,’mm/dd/yyyy’) from dual

date—>char

Note: Because the date itself has a certain format, the default format is DD-MM-YYYY

The specified format exists as a display format for the date converted to a string type. Such as:

Mm – dd yyyy – ‘ ‘2016-12-05’

yyyy/mm/dd’ ‘2016/12/05’

Yyyy “Year” MM “month” DD “Date ‘December 05, 2016

Select hiredate,to_char(hiredate) from EMp — When converting a date to a character, the default format is DD-MM-YYYY

select hiredate,to_char(hiredate,’yyyy-mm-dd’) from emp; Converts the date to a string type using the specified format

select hiredate,to_char(hiredate,’yyyy/mm/dd’) from emp; Converts the date to a string type using the specified format

Select hiredate,to_char(hiredate,’yyyy ‘,’ mm ‘) from emp; Converts the date to a string type using the specified format

Query information about employees whose employment date is after 10/20/81

The first is automatic transformation

Select * from emp where hiredate> 1

Second: convert the date to a string

select * from emp where to_char(hiredate,’yyyy-mm-dd’)>’1981-10-20′

The third:

select * from emp where hiredate>to_date(‘1981-10-20′,’yyyy/mm/dd’)

Lesson 2: Other Functions:

Single-line function: lower

Min Max sum avg count

Conversion function :to_number to_char to_date

—————————————————————————————–

Other functions:

1. NVL (): NVL (field name, execution)– equivalent to if condition judgment in Java

Nvl2 ():nvl2(field name, value, value)– equivalent to If(){}else{} in Java

Decode ():decode(field name, condition 1, execute content 1, Condition 2, execute content 2, Condition 3, execute content 3, default execute content)

If (){}else if(){}else if(){}else if(){}… else{}

Query the monthly salary, name and job of all staff

select * from emp

Select ename,sal,comm from emp where sal+comm = 1 and sal+comm = 1

Select ename,sal,comm from emp where sal+ NVL (comm,0) = 1

Query the monthly salary, name and job of all staff

Select ename,sal from EMp where sal = comm,sal+comm,sal = comm

Display employee’s title

Select ename,job,decode(job,’MANAGER’,’ MANAGER’,’ SALESMAN’,’ employee ‘) from EMp

Lesson 3: Use group by

You cannot use normal fields directly in multi-line functions, except for group by

You cannot use single-line functions directly in multi-line functions, except group by

Group by study

Select * from table name group by select * from table name group by

–2. When grouping multiple fields, group them according to the field order. After grouping the first condition, continue to use other conditions to group them in turn.

–3, Group by can still be used together with order by

4, can be combined with the single-line function group, note that the use of single-line functions must also be used in the query statement

Query the maximum salary and number of employees

select max(sal),count(*) from emp

Query the highest salary for different departments

select * from emp order by deptno

Select deptno, Max (sal) from emp group by deptno select sal from EMp group by deptno

Query the number of employees in different jobs

select * from emp for update

Select lower(job),count(*) from emp group by lower(job

Query the number of people in different jobs in different departments

Select deptno,job,count(*) from emp group by deptno,job

select deptno,job ,count(*) from emp group by deptno,job order by deptno

T count(*) from emp where count(*)>3 group by deptno

selec

select deptno,job ,count(*) from emp where count(*)>1 group by deptno,job order by deptno

Query the number of jobs in different departments where the department number is greater than 10

select deptno,job ,count(*) from emp where deptno>10 group by deptno,job order by deptno

Group filtering using HAVING

Having to learn:

– 1, using the group by group at the time of data screening, after the where can’t appear in the multi-line function, so using the new keyword having conditional filtering

Select * from–>where– >group –>select

Select * from–>group by >having–>select

Where is more efficient than HAVING, so don’t use HAVING when you can use WHERE

Query information about different jobs in different departments and the number of people is greater than 1

Use the WHERE statement for filtering

Select from–> WHERE –>group –> SELECT

Select * from emp where count(*)>1 group by deptno, job

Use the HAVING statement to filter

Order of execution of conditional statements: FROM –>group by –>having–> SELECT

select deptno, count(*) from emp group by deptno having count(*)>5

select deptno,job ,count(*) from emp group by deptno,job having deptno>10 order by deptno

The fourth lesson: Insert data learning and data backup

Single table query statement (SELECT)

1, Insert data (insert)

Insert into table_name (select * from table_name where table 1, table 2, table 3,….) Values (‘ 1′,’ 2′,’ 3’…..)

2. Primary key: A field that uniquely identifies a piece of data is usually set to a primary key, which is unique and cannot be repeated

3. If the inserted data is full field data, the field can be omitted. For some fields, the field description and field value must be added, but the primary key cannot be empty

4, transaction submission: if an event is composed of multiple actions, as long as one action is not successfully executed, the data will be automatically rolled back to the original state, which is called transaction technology

Ensure data security and integrity

Submission of a thing:

Use the third place plug-in submit button

Use commit statements

5. Add and delete modified data. After the SQL statement is executed, data will not be written immediately

The data needs to be submitted manually and can be rolled back if there are problems with the data

select * from dept for update

There is a new department in Beijing called LOL Academy, please insert

Insert into dept(deptno,dname,loc)values(’50’,’lol ‘,’ Beijing ‘);

The primary key is the only one that cannot be repeated

Insert into dept(deptno, loc)values(’50’,’ 01 ‘,’ 01 ‘);

If the inserted data is full field data, the fields can be omitted. For some fields, the field description and field value must be added, but the primary key cannot be empty

Insert into dept values(’80’,’ 01 ‘,’ 01 ‘);

Insert into dept values(’90’,’ 01 ‘,’ 01 ‘);

Insert into dept values(100′,’ 01 ‘,’ 01 ‘);

Insert into dept values(‘110′,’ 01 ‘,’ 01 ‘);

2. Create a backup of data

SQL > create table as select * from table as select * from table as

Insert into table name query statement, note: the result of the query must be the same as that of the insert table, the number of columns must be the same

(3) Note: The backup table only has the same fields and data, and does not backup constraints.

1. Back up complete data and tables

select * from dept;

create table tdept as select * from dept; — Back up dept tables and data

select * from tdept

2. Back up the full table

Create table tdept1 as select * from dept WHERE 1>2

Create table name as query statement; create table name as query result

create table tdept2 as select dname,loc from dept

select *from tdept2

Insert into table name query statement, note: the queried result must have the same structure as the inserted table, and the number of fields must be the same

select * from tdept1

insert into tdept1 select dname,loc from dept where deptno>40

select *from tdept2

insert into tdept2 select dname,loc from dept where deptno>40

Lesson 5: Update and Delete data:

Update data :update

Update table_name set table_name 1=’ table_name 1′, table_name 2=’ table_name 2′,… Where conditions

Change the name of department 70 to Teaching Department 2

Update dept set deptno=’70’

Update dept set deptno= 1,loc= 1 where deptno= 1

select * from dept

Delete data: delete

1) delete statement: delete table name where condition

Delete data from department 70

Delete dept WHERE deptno=’70

select * from dept

Delete tDEPT — Clears table data

Truncate TABLE TDEPT – To clear table data, use the TRUNCate keyword. However, this keyword cannot roll back data

= = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =

Concern public number: Java architect union, background reply mysql to obtain database related information