5 days, 2W+ words to sort out the database operation instructions, with practice questions, let’s not understand database science

Posted Jun 28, 202016 min read

The reason for my friend s work is not idle during the Dragon Boat Festival. I organized the sql instructions of the database with this scourge and organized the supporting exercises for practice. I just didn t have any problems. I couldn t do anything because of the epidemic. Organize a wave, by the way, a wave of wool, hehe hehe

However, because there are really too many words, so only a part of it is displayed. If you need complete documentation and sql files, pay attention to the public number:Java Architect Alliance, add a small assistant to get it in the background

first day:

Lesson 1:Learning the basic concepts of database

  1. Data Book

In fact, it is a collection of special symbols that represent information

For example:pictures, txt, audio, video, etc.

Introduced:The development of the Internet is actually data interaction

  1. Database:Library

The thing used to store data is called a database.

  1. Database management system Application program Library management system

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

Can efficiently retrieve stored data(emphasis)

Database application system-for ordinary users, like Taobao

  1. Database system

Database administrator

database

Database management system

Database application system

user

5.

oradata:database related information

product:Information about the database management system

bin:the startup directory of the database management system software

jdbc:jar package required for database and java connection

network:the network configuration directory required by the database management system

Log directory:Oracle abnormal log information storage directory

Lesson 2:Common databases and database storage structures

  1. Database storage data structure

grid

Hierarchy

Two-dimensional table structure:

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

note:

Simply storing the data is simple, but the stored data must describe the relationship between the data clearly

It will be more difficult, so it is more reasonable to store data in a two-dimensional table structure

  1. Database management system

oracle:large database management system

mysql:small and medium

DB2:small and medium

Acess:small

Sql Server:large database

.....

  1. Historical introduction of oralce, refer to PPT

The third class:Oralce installation and Oracle directory structure introduction and uninstall

  1. The installation of oracle:

Refer to Baidu search "oracle11g installation diagram"

Global database name:The default is orcl, which is used to uniquely identify the database name

SID:the name that uniquely identifies the data in the program

  1. Oracle service:(services that need to be turned on)

orcaleServiceorcl:orcale start service

OracleOraDb11g_home1TNSListener:oracle listener service

OracleDBConsoleorcl:console service

  1. Oracle account:

sys:super administrator

system:administrator

scott:ordinary user default password:root

  1. Oracle changes password:

Use the command in the cmd command line:sqlplus/as sysdba

Then use the command:

alter user username identified by new password;

Use the command in the cmd command line:sqlplus /nolog

Then use the command:conn as sysdba

Then enter a known username and password

Then use the command:

alter user username identified by new password;

  1. New user:

Must use System account

The fourth lesson:Oracle directory and uninstall

  1. Introduction of oracle's directory:

oradata:the directory where the database stores files

db_home:

network >admin:configure network service and listener service

jdk:oracle comes with jdk

deinstall:uninstall command

jdbc:jar package that interacts with java

  1. Uninstall of Oracle:

Use the program that comes with Oracle to uninstall

Delete app directory

Delete the registry

Delete environment variables

Lesson 4:Oracle's listener and server configuration

Question 1:

My computer is equipped with oralce, how to view the data at the same table?

Client:

The software used by ordinary users is mainly used to send user requests.

server:

Accept user requests and process user requests.

Question 2:Both Zhang San and Li Si installed Oracle, and Wang Wu installed the client. Wang Wu wants to visit Zhang San. How do I access it?

ip

tcp

Database global name

The port number

Question 3:

From the perspective of the server, if 1000 requests come over, how to distinguish those that request the database

Listener

Third-party tool:plsql(not the client)

Configure local network service:(Client)

The first one uses violence to directly operate:

Edit:C:\app\Administrator\product\11.2.0\dbhome_1\NETWORK\ADMIN\tnsnames.ora

ORCL => network service name

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = orcl)

)

)

The second way:

Use graphical interface:NCA

Configure the listener:(server side)

common problem:

Configure the listener, the computer name must be in English

The main task of the first day is to install the database and configure the network service of the database, and the second day to formally learn the SQL statement

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

the next day:

The first lesson:Introduction to sql language

  1. Introduction of database tables

emp table:employee table

dept table:department table

salgrady:salary scale

Balance:

  1. Basic query statement:

Knowledge points:

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

select field name 1, field name 2, .. from table name query all values of the specified field, separated by commas between different fields

Use arithmetic expressions:select field name(operator), field name(operator)...from table name

Case:

select * from emp---Query all employee information

select ename,job,sal from emp--Query employee name, job, salary

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

select ename,job,sal*12,sal*12+3000 from emp use operator

Second class:

  1. Use aliases

as can be omitted without writing, if there are special characters to get spaces use double quotes to add an alias

select ename name, job, sal*12 seniority, sal*12+3000 annual salary from emp add an alias directly after the field

select ename "Name", job, sal*12 Seniority , sal*12+3000 annual salary from emp Use double quotes to add aliases, single quotes cannot be used

select ename as name, job "job", sal*12 seniority, sal*12+3000 annual salary from emp Use the as keyword to add an alias

select ename "Name", job, sal*12 seniority, sal*12+3000 annual salary from emp If there are spaces or other special characters in the alias, use double quotes

  1. Use the link symbol:

select ename,job,sal from emp;

select ename||job,sal from emp; Use || symbol for character link

select ename||'s job is'||job||' and the monthly salary is'||sal from emp; character link, ordinary characters use single quotes

  1. Remove duplicate distinct

select * from emp;

select job from emp--found that the work is duplicated, and the type of job needs to be removed

Question:View employee work types

select distinct job from emp use distinct keyword to remove duplicate values

Question; how to remove duplicates in multiple fields

select distinct job, sal from emp; multi-field deduplication, the entire message is repeated

  1. Sort

Knowledge points:

select field name, field name... from table name order by field name

select *from table name order by field name

explain:

--Query employee name, job, salary

select ename,job,sal from emp

--Query employee name, job, salary sorted by salary

select ename,job,sal from emp order by sal; use order by

select ename,job,sal from emp order by sal desc; Use the desc key to sort from large to small

select ename,job,sal from emp order by sal asc; Use asc key to sort from small to large, the default is to sort from small to large

select * from emp order by sal, ename When multiple fields are used as sorting conditions, priority is given to the first field, and then to other fields.

select ename,job,sal salary from emp order by salary Use alias

select ename,job,sal from emp order by sal*12 use arithmetic expressions

Lesson 3:Simple where clause

Use where condition to filter results

Syntax:select * from table name where condition; Note:The field value in the condition is case sensitive, the field name is not case sensitive, and the field value is enclosed in single quotes

  1. Use arithmetic expressions in where = ,<,> ,>= ,<= ,<>

  2. Use order by to sort the filter results, order by appears after where

View employee information with salary equal to 1250

select * from emp where sal='1250'--The filter condition is a number and single quotes can also be used

View employee information with work equal to CLERK

select * from emp where job='CLERK'--If the field value in the filter condition is a character, single quotes need to be added

select * from emp where job='clerk'--The value of the field in the SQL statement is case sensitive

select * from emp where JOB='CLERK'--The fields in SQL are not case sensitive

View the names and jobs of employees with salaries greater than 1250

select ename,job ,sal from emp where sal>'1250' order by sal--use order by to sort the filter results, order by appears after where

View employee information with a salary greater than or equal to 2000

select * from emp where sal>=2000;

View employee information with a salary less than or equal to 2000;

select * from emp where sal<=2000

View employee information with salary not equal to 1500

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

View employee information after the employment date is 81 years

select * from emp where hiredate>'December 31, 1981'

select * from emp where hiredate>'1981/12/31'

select * from emp where hiredate>'31-12Dec-1981'--Use the default format of the date to query the data that meets the requirements, day-month-year

Lesson 4:Using keywords in the where clause

Use where clause to filter results

Knowledge point:Use keywords in where query conditions

  1. and for multi-condition and screening:select * from table name where condition and condition and condition...

  2. or for multi-condition or filtering:select * from table name where condition or condition or condition....

  3. in is used for multi-condition or filtering:select * from table name where field name in(value, value, value...)

  4. Like is used for fuzzy query:select * from table name where field name like'%value%' contains

  5. Is null and is not null are used to determine whether the field is empty select * from table name where field name is null

Explain:Use keywords in the where clause(and, or, like, is null, is not null, between and,)

--Query employee information with salary between 2000-3000

select * from emp where sal>=2000 and sal<=3000--use the and keyword to perform multi-condition filtering of "AND";

select * from emp where sal between 2000 and 3000; - Use between and keyword to filter;

--Query employee information for SALESMAN, ANALYST, MANAGER

select * from emp where job='SALESMAN' or job='ANALYST' or job='MANAGER'--use the or keyword for "or" multi-criteria filtering

select * from emp where job in('SALESMAN','ANALYST','MANAGER');--Use the in keyword for "or" multi-condition filtering

select * from emp where job='ANALYST'

--Query the name that contains s, starts with s, ends with s, and the second character is A.

select * from emp where ename like'%S%';--Use like keyword, the name contains S,%represents 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'; - end with S

select * from emp where ename like'_A%'--use "_" to specify the location that contains the information of the specified character, "_" represents any character

--------------Query user information with underscore in name

select * from emp where ename like'%A_%'escape'A';--Use the escape keyword to set ordinary characters as translation characters.

--Query the employee information with allowance

select * from emp where comm is not null;

select * from emp where comm is null;--Query employee information without allowance

Lesson 5:Function learning

--Query employee information whose work is SALESMAN, MANAGER and whose salary is greater than 2500

--1, Use parentheses to increase the execution priority of where filter conditions

--2, and the priority level is higher 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 Conversion function Other functions

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

---1, character function

select * from emp;

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

select lower(ename) from emp;--lower lower case letters

select replace(ename,'S','M') from emp; --replace

--2, numerical function-Math

----Pseudo watch dual

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-Dec-2016','13-October-2016') from dual--months_between the number of months between two dates

Lesson 6:Multi-line function learning

Multi-line function:

max:max(field name) returns the maximum value of this field

min:min(field name) returns the minimum value of this field

avg:avg(field name) returns the average value

sum:sum(field name) returns the sum of the fields

count:count

--count(*), used to query how many records in the table

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

--count(distinct field name), you can remove the duplicates and then count.

note:

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

--Multi-line functions and single-line functions cannot appear directly in the query statement, unless group by

View the maximum salary of employees

select max(sal), ename from emp--multi-line functions cannot appear directly in the query statement with ordinary fields, unless group by

select max(sal), lower(ename) from emp--multi-line functions and single-line functions cannot appear directly in the query statement, unless group by

select ename from emp

View employee's minimum wage

select min(sal) from emp

View the average salary of employees

select avg(sal) from emp

View the sum of all employees' salaries

select sum(sal) from emp

Query how many employees the company has

select * from emp

select count(*) from emp--use count(*) to see how many records are in a table

Check the number of employees with allowances

select count(comm) from emp--Use count(field name) to query the number of records that have value in this field

select count(ename) from emp

Check how many job types the company has

select count(job) from emp

select count(distinct job) from emp

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

The third day:

The first lesson:Conversion function learning

Conversion function:The type of data is changed during conversion, the data content will not change, and the format can be specified.

  1. to_number:convert numeric characters to numeric values, to_number(numeric characters)

  2. to_char:convert number to character type, convert date to character type to_char(number/date)

  3. to_date:convert character date to date type:to_date(char)

------------------------------------------------- -------------------------------------

number-->char uses the default format when converting,

select to_char(123) from dual--Convert numbers to character types, use to_char(number)

select sal, to_char(sal) from emp

number-->char uses the specified format

to_char(number,'format'), format:

$Stands for the dollar sign, and 9 code digits take place. For example, L999,999,999 indicates a three-digit display mode. L stands for the renminbi symbol, and 0 stands for the number of reserved digits. If it is insufficient, use 0 to add it.

select to_char(sal),to_char(sal,'L999,999,999') from emp

select to_char(sal),to_char(sal,'L0000.00') from emp

char--->number to_number(number character)

select to_number('123') from dual

char--->date, the converted character must be a date format string, the default format is dd-mm-yyyy

Note:Because there are many character strings, when you convert characters to dates, you need to specify the format, because dates are character combinations with a certain format.

When a character is converted to a date, the specified format is the date order of the character, and no spacer is required.

yyyy:year mm:month dd day

select to_date('05-12Dec-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 data will be displayed in dd-mm-yyyy format by default if it is not the specified format.

The specified format exists as a display format in which the date is converted to a string type. E.g:

yyyy-mm-dd' 2016-12-05

yyyy/mm/dd' '2016/12/05'

yyyy"year"mm"month"dd"day"' December 05, 2016

select hiredate,to_char(hiredate) from emp--When the date is converted to a character, the default format is used without specifying the format:dd-mm-yyyy

select hiredate,to_char(hiredate,'yyyy-mm-dd') from emp;--Use the specified format to convert the date to a string type

select hiredate,to_char(hiredate,'yyyy/mm/dd') from emp;--Use the specified format to convert the date to a string type

select hiredate,to_char(hiredate,'yyyy"year"mm"month"dd"day"') from emp;--Use the specified format to convert the date to a string type

Query the employee information after the entry date is October 20, 1981

The first:automatic transformation

select * from emp where hiredate>'20-October-1981'

The second:convert the date to a string

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

The third kind:

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

Lesson 2:Other functions:

Single-line function:lower

Multi-line function: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

2, nvl2():nvl2(field name, value, value)-equivalent to If(){}else{} judgment in java

  1. decode():decode(field name, condition 1, execution content 1, condition 2, execution content 2, condition 3, execution content 3, default execution content)

Equivalent to if(){}else if(){}else if(){}...else{} in java

Query the monthly salary, name and work of all employees

select * from emp

select ename, sal basic salary, comm performance, sal+comm monthly salary from emp

select ename, sal basic salary, comm performance, sal+nvl(comm, 0) monthly salary from emp

Query the monthly salary, name and work of all employees

select ename, sal basic salary, comm performance, nvl2(comm, sal+comm, sal) monthly salary from emp

Show employee title

select ename,job,decode(job,'MANAGER','Manager','SALESMAN','Salesman','Ordinary Staff') from emp

Lesson 3:Use group by

Ordinary fields cannot be used directly in multi-line functions unless group by

Single-line functions cannot be used directly in multi-line functions, unless group by

group by learning:

---1, Use group by for data grouping select Multi-line function, group field from table name group by group field

---2. When grouping multiple fields, group according to the field order. After the first condition is grouped, continue to use other conditions to group in sequence.

---3, group by can still be used in combination with order by

---4. It can be grouped with single-line functions. Note that if single-line functions are used, they must also be used in the query statement.

Query the maximum salary and number of employees

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

Query the maximum salary of different departments

select * from emp order by deptno

select deptno,max(sal) from emp group by deptno--Use group for group query, grouped fields can appear in the query, other fields are still not allowed

Query the number of employees in different jobs

select * from emp for update

select lower(job), count(*) from emp group by lower(job)--Use a single-line function to group

Query the number of different jobs in different departments

select deptno,job ,count(*) from emp group by deptno,job--use multi-field combination for grouping

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

Query information of different jobs in different departments and the number of people is greater than 1 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 different jobs in different departments with department number greater than 10

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

Use having to filter after grouping

having learning:

--1. When data filtering is performed after group by grouping, multi-line functions cannot appear in where, so use the new keyword having to perform conditional filtering

--2, The execution order of where condition filtering:from-->where--->group -->select

--3, the execution order of having condition screening:from-->group by -->having-->select

--4, where the execution efficiency is higher than having, try not to use if you can use where

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

Use where statement to filter

Where condition statement sql execution order:from-->where--->group -->select

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

Use the having statement to filter

The execution order of having conditional statement: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

Lesson 4:Insert data learning and data backup

Single table query statement(select)

  1. Insert data(insert)

  2. Syntax specification insert into table name(field 1, field 2, field 3,...) values('value 1','value 2','value 3'...)

  3. Primary key:The field used to uniquely identify a piece of data is usually set with a primary key. The primary key is unique and cannot be repeated

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

  5. Commitment of transaction: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 we call the transaction

Keep data safe and complete

Submission of things:

Use the submit button of the third plugin

Use the commit statement

  1. After adding and deleting the modified data SQL statement is executed, it will not immediately enter the data write

Need to manually submit the data, if the data is problematic, you can roll back

select * from dept for update

A new department called LOL Academy has been created in Beijing, please insert

insert into dept(deptno,dname,loc) values('50','lol Academy','Beijing');

The primary key is the only thing that cannot be repeated

insert into dept(deptno,dname,loc) values('50','Teaching Department','Beijing');

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

insert into dept values('80','Teaching Department','Beijing');

insert into dept values('90','Teaching Department','Beijing');

insert into dept values(100','Teaching Department','Beijing');

insert into dept values('110','Teaching Department','Beijing');

  1. Create a backup of the data

(1) create table table name as query statement, the same table as the query result is created, and the same table will be backed up whenever the query result is

(2) insert into table name query statement, note:the result of the query must be the same in structure as the table into which the data is inserted, and the number of fields must be the same

(3) Note:Only the fields and data of the backup table are the same, and there is no backup constraint.

  1. Back up complete data and tables

select * from dept;

create table tdept as select * from dept;--back up dept table and data, only backup data and fields

select * from tdept

  1. Backup the complete table

create table tdept1 as select * from dept where 1>2--Backup table, no data backup.

  1. Back up part of the data and tables. create table table name as query statement, create the same table as the query result, a query will back up the same table

create table tdept2 as select dname,loc from dept

select *from tdept2

  1. Add data to the backup table insert into table name query statement, note:the result of the query must be the same in structure as the table into which the data is inserted, 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:Data update and deletion:

Update data:update

Syntax structure:update table name set field name 1='field value 1', field name 2='field value 2', ...where condition

Change the name of Department 70 to Teaching Department 2

update dept set dname='Teaching Department 2'where deptno='70'

update dept set dname='Teaching Department 2',loc='Shanghai' where deptno='70'

select * from dept

Delete data:delete

1) Delete statement:delete table name where condition

--Delete the data with department label 70

delete dept where deptno='70' --- delete the specified data

select * from dept

delete tdept---clear table data

truncate table tdept---It is recommended to use the truncate keyword to clear table data, but this keyword cannot roll back data

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

Pay attention to the public number:Alliance of Java Architects, reply mysql in the background to obtain database related information