Shulou(Shulou.com)06/01 Report--

Database:

Basic concepts:

Database Management system (Database Management System,DBMS):

A software system for managing (storage + operation (CRUD)) data

Relational database management system (RDBMS):

Database (Database): the disk on which data is stored, which is part of RDBMS

What are the common database management systems on the market?

ORACLE, Sql Server, Sqlite (Mobile Database), DB2, Access, mysql,sybase,h3...

SQL (Structure Query Language) structured query language

It is a standard for operating the database management system: it mainly performs CRUD operations on the database.

The operation of the database is done through SQL.

Create: creatin

Delete: deletin

Update: modifyin

Retrieve: retrieving

The mainstream databases on the market follow the SQL specification:

Note: different databases have different support for SQL.

Sql is divided into five parts according to the command function:

1.DCL (Data Control Language) data control statement: grant,revoke

2.DDL (Data Definition Language) data definition statement:

Create,alter,drop,truncate,comment on,rename to...

3.DML (Data Manipulating Language) data manipulation statement: insert,update,delete

4.DQL (Data Query Language) data query statement: select

5.DTL (Data Transaction Language) data transaction statement: commit,rollback,savepoint

Oracle www.oracle.com

... 8i,9i,10g,11g,12c

Installation Note: the directory cannot be in English: it is recommended to turn off the firewall and some 360 security software.

Oracle database consists of server and client:

Server: consists of a bunch of database background processes and listeners as well as databases

The two main services:

OracleServiceXE Oracle database service master service (database instance name, warehouse name)

OracleXETNSListener Oracle database listener, which listens for client access to the database

Client: there are many

1.oracle comes with a command line client: sqlplus

Start: run cmd to open the cmd command window and enter sqlplus

2.PL/SQL Developer third-party graphical interface

3.Navicat third-party graphical interface

Create user 2 steps:

1. Create a (normal) user:

Syntax: create user username identified by password

two。 Authorize to a user:

Roles: there are 3 roles built into oracle:

DBA: system administrative permissions

Connect: create session connection permissions

Resource: permissions to manipulate basic database objects

Grant permission, permission. To user name

Change password: alter user username identified by new password

Delete user: drop user user name; (pay attention to permission issues when deleting)

Account unlocked: alter user user name account unlock

Common commands in sqlplus:

1. Switch users:

Conn username / password

two。 Displays the user name of the current login:

Show user

3. Execute the database script:

Start or @ * .sql (you can drag the script directly to the command window)

For example: start D:/oralce_cn.sql

Database scripts: a collection of statements used to create database objects

Purpose: to create a table to store data in the database and initialize the data in the table

4. View the structure of the table: this is a command of sqlplus, not a command of sql

Name of the desc table

5. Clear the screen:

Clear screen

6. Recollection:

Arrow keys up and down

7. Edit the multiline sql statement:

Edit

Basic query:

Table: it is a two-dimensional space consisting of rows (rows) and columns (column)

It is the basic unit for storing data in a database, and our data will be stored in tables in the future.

The nature of the query:

1. What are you looking for:

two。 Where to check:

Syntax: select column name from table name

Note: if you query all columns, use * instead!

For example:

-- find out all the information about the employee

Select * from s_emp

-- query the employee's name, salary and position from the semptable

Select first_name,salary,title from s_emp

-- find out the employee's last name and first name from the s _ emptable

Select first_name,last_name from s_emp

-- find out all the titles of the company (title)

Select title from s_title

Select distinct title from s_emp

Find out the name, salary and entry date of all employees

Select first_name,salary,start_date from s_emp

Find out the names of all the customers and their phone numbers

Select name,phone from s_customer

Find out the name of the employee and his professional title

Select first_name,title from s_emp

Find out the cost, payment method and shipping date of each order

Select total,payment_type,date_shipped from s_ord

Weight: distinct

Select distinct title from s_emp

Data type of the column:

1. Number type, can do all the mathematical operations

Number

Number (4) represents an integer with a maximum storage capacity of 9999

Number (7 number 2) represents the double type integer length of 5 decimal places 2.

two。 Character type, can do splicing operation

Note: the string uses''in the database.

Varchar2 (20) represents a string with a maximum length of 20

Char (20) stands for fixed length 20

3. Date type, which can be added and subtracted

Date year, month, day, hour and second

Timestamp year, month, day, hour, minute and second, and decimal places, such as 3.2 seconds

4. Big data type

Clob character large object large character object that can store up to 4G

Blob binary large object large binary object, which can store up to 4G

Note: big data type does not support viewing results

Such as:

Sql has the ability to calculate.

Look up the employee's name and his annual salary from the employee table

Select first_name name, salary*12 annual salary from s_emp

Select first_name "last name", salary*12 "annual salary" from s_emp

Sql has string concatenation ability and can name columns

-- find out the name of the employee from the semptable

Select first_name | |''| | full name of last_name from s_emp

-- string concatenation (* is * * entry, salary is * *, professional title is * *)

Select first_name | |''| last_name | | 'Yes' | | start_date | | 'entry salary is' | | salary | |', job title is'| | title description from s_emp

Note:

In oralce database: strings are wrapped with''packages, aliases can be taken, formats can be saved, and case sensitivity can be made.

The function of "" gives the list of individual names to our query and maintains the format

Aliases do not necessarily need ""; if you want to maintain the format, you must have "".

Null permutation function (nvl)

-- query all employees' names and annual salary (no commission is considered)

Select first_name,salalry*12 from s_emp

Null operates with all values and ends up with null

Null permutation function nvl (commission_pct/100,0)

If the value of commission_pct/100 is null, the value of the expression is 0

If the value of commission_pct/100 is not null, the value of the expression is commission_pct/100

-- query the names of all employees, annual salary (consider commission)

Select first_name,salary*12* (1 + nvl (commission_pct/100,0)) from s_emp

Note: the two values in nvl () should be of the same data type

Conditional query:

The where sentence is used to filter query data. It is sensitive to literal case.

The operators that appear after where are:

1. Comparison operator:

>

< , >

=, 1500

-- find out the full name, salary and title of employees whose salary is more than 1200 yuan

Select first_name | |''| | full name of last_name, salary of salary, title of title

From s_emp where salary > 1200

two。 Logical operator:

And,or,not

-- find out the names and salaries of employees in 41 departments whose wages are higher than 1200.

Select first_name,salary from s_emp where dept_id=41 and salary > 1200

-- find out the name and salary of the employees in department 41, 50 and 42.

Select first_name,salary from s_emp where dept_id=41 or dept_id=42 or dept_id=50

3. Other operators:

In (list) list is a list with multiple values separated by commas

Not in ()

Between? And? Within the specified range, it is a fully closed space.

Is null

Is not null

Fuzzy matching: like

Wildcard characters:

_ wildcard with any single character

% with any number of characters

-- find out the name and salary of the employees in department 41, 42, and 50

Select first_name,salary from s_emp where dept_id in (41 and 42, 50)

-- find the names of employees with salaries between 1200 and 1500

Select * from s_emp where salary > = 1200 and salary1500 and commission_pct is null

Note: when querying data, whether the condition is null, we use the keyword is or is null, never use = /! =

-- find out the employee whose name starts with M

Select * from s_emp where first_name like'M%'

-- identify employees whose third letter in their name is e

Select * from s_emp where first_name like'_ e%'

If there is a _ or% in the character we want to match, then

Need to escape by keyword escape

Select * from s_emp where first_name like'/ _ A% 'escape' /'

Exercise:

-- find employees who have no commission rate.

Select * from s_emp where commission_pct is null

-- find employees who have a commission rate

Select * from s_emp where commission_pct is not null

Find out the order number and payment method for which the fee is more than 10000 yuan.

Select id,payment_type from s_ord where total > 10000

-- find out the names and titles of employees whose wages range from 950 yuan to 1200 yuan.

Select first_name,title from s_emp where salary between 950 and 1200

-- find employees whose names contain the letter a

Select * from s_emp where first_name like'% a%'

-- find the employee whose second letter in the name is a

Select * from s_emp where first_name like'_ a%'

-- find out the name, entry time and salary of the employees who joined in February.

Select first_name,start_date,salary from s_emp where

Start_date like'% February%'

-- find out the full name and salary of the employee whose title is Stock Clerk, and sort it by descending order of salary.

Select first_name | |''| | last_name,salary from s_emp where

Title='Stock Clerk' order by salary desc

Select first_name | |''| | last_name,salary from s_emp where

Title='Stock Clerk' order by 2 desc

Note: order by 2 means to sort by the second column of the query results!

Sort words:

Order by column name asc (ascending order, default can not be written) | desc (descending order)

Find out the full name, salary and title of the employees who joined in June, and sort them in descending order of salary.

Select first_name | |''| | full name of last_name, salary,title from s_emp where start_date like'% June% 'order by 2 desc

-- find out the names and salaries of employees with VP in their professional titles, and sort them in descending order.

Select first_name,salary,title from s_emp where title like'% VP%' order by 2 desc

-- find out the names and titles of employees with an annual salary of less than 25000, and sort them in the order of annual salary increase.

Select first_name,title,salary*12* (1+nvl (commission_pct/100,0))

From s_emp where salary*12* (1+nvl (commission_pct/100,0)) 5

-- find out the employee whose title is stock clerk

Select * from s_emp where title=initcap ('stock clerk')

Please divide the salary of the employees into three grades, and those with more than 2000 yuan are higher and those between 1500 and 2000 are medium.

Those below 1500 are inferior.

Select first_name name

Case

When salary2000 then 'Advanced'

Else 'ungraded'

End salary scale

From s_emp

Special note: when the column to be compared appears after when, then you must not add any more columns after case!

-- output the number and payment method of each order, which is either cash

It's either a credit card, or it's unknown.

Select id

Decode (payment_type,'CASH',' cash', 'CREDIT',' credit card', 'unknown')

From s_ord

Select id

Case payment_type

When 'CASH' then' Cash

When 'CREDIT' then' credit card

Else 'unknown'

End

From s_ord

3. Date function

Oracle default date format is: dd-mm-yy

Dd represents a two-digit day

Mm represents a two-digit month, such as: 06

Yy represents a 2-digit year

HH24/HH stands for hours

Mi stands for minutes

Ss stands for seconds

Fm means to remove the previous 0, such as fm 06, and the later value is 6.

Day says what day it is.

Date function:

Sysdate current date

The number of months between two dates of months_between (date1,date2)

Add_months (date1,n) adds a few months to date1

Next_day (what day of the week is date1,') on the basis of date1, what day is next week?

When is the last day of the month where the date of last_day (date) date is located

Modify the current session locale:

Alter session set nls_language = 'american';// changed to English

Alter session set nls_language = 'simplified chinese';// changed to simplified Chinese

Modify the format of the current session date:

Alter session set nls_date_format='yyyy-mm-dd HH24:mi:ss day'

For example:

Inquire about the current date what is the next Thursday?

Select next_day (sysdate,' Thursday') from dual

-find out the number of working months of the employee:

Select months_between (sysdate,start_date) from s_emp

-- enquire about the number of working days of employees:

Select first_name,sysdate-start_date from s_emp

-- calculate the time one year ago, now, and one year later:

Select add_months (sysdate,-12), sysdate,add_months (sysdate,12) from dual

-- the last day of six months before the current date

Select last_day (add_months (sysdate,-6)) from dual

-- shows what the date is next Friday:

Select next_day (sysdate,' Friday') from dual

4. Conversion function

To_number (char), which converts characters to numbers

To_date (char [, fmt])

Converts a string to a date and parses it in the default format (dd-mm-yy) if the fmt parameter is not passed

To_char formats a number or date as a string

1.to_char (number,fmt); formatted number

2.to_char (date, fmt); formatted date

Convert 123 strings to 123 digits

Select to_number ('123') from dual

-- convert 1986-04-13 string to date according to template yyyy-mm-dd

Select to_date ('1986-04-13) from dual

Some symbols that can be used when formatting numbers into strings:

9 represents arbitrary data

L represents the local currency symbol

$stands for US dollars

0 stands for 0

. Represent.

, representative

-formatted output employee's salary ($1500.00)

Select to_char (salary,'$999999.99') from s_emp

Exercise:

-format the entry date of the employee as year / month / day

Select to_char (start_date,'yyyy/mm/dd') from s_emp

-- identify employees who joined the workforce in May

Select * from s_emp where to_char (start_date,'fmmm') ='5'

-- find out the employees who joined the staff that month

Select * from s_emp

Where to_char (sysdate,'fmmm') = to_char (start_date,'fmmm')

-- query the employee's name, entry date, and sort by day in ascending order

Select first_name,start_date from s_emp

Order by to_number (to_char (start_date,'fmdd'))

Find out the name of each employee and its salary (e. G. $2500.00)

Select first_name,to_char (salary,'$999999.99') from s_emp

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.