A detailed explanation of Oracle Bone Inscriptions's theory study and how to solve various simple operation examples.

The development process of 1. database

Hierarchical model->; Grid model->; Relational model->; Object relation model

2. About the concept of database

DB: Database (warehouse for storing information)

DBMS: database management system (tool for managing databases)

RDBMS: Relational Database Management System

ORDBMS: an object-relational database management system

3. The main characteristics of 3.Oracle database

1) supports multi-user and large transaction processing.

2) database security and integrity control

3) Support distributed data processing

4) portability

4. What are the common questions about 4? Oracle Bone Inscriptions?

1) If I only have one table, why should I create a database?

The SQL language requires all tables to be placed in the database. This design certainly has its good reasons. SQL can control the behavior of users accessing tables at the same time. Ability to grant or revoke access to the entire database. This is sometimes much simpler than controlling the permissions of each table.

2) The letters of the command to create the library are all capitalized. Is this necessary?

Some systems do require certain keywords to be capitalized. But SQL itself is case-insensitive. In other words, it is ok to write commands in lowercase, but it is a good SQL programming habit to write commands in uppercase.

3) Do you have any ideas about naming databases, tables and columns?

Creating descriptive names is usually effective. Sometimes more than one word is needed to name it. All names cannot contain spaces, so you can create more descriptive names by using underscores. It is best to avoid capitalization when naming, because SQL is case-insensitive. It is very likely that the database will go wrong.

4) Why can't BLOB be directly used as the type of all text values?

Because it's a waste of space. VARCHAR or CHAR will only occupy a specific space. No more than 256 characters. But BLOB need a lot of storage space. With the growth of the database, occupying storage space risks running out of hard disk space. In addition, some important string operations cannot be operated on BLOB data. Can only be used for VARCHAR or CHAR.

5) Why do you need numerical types such as INT and DEC?

Saving database storage space is related to efficiency. Choosing the most suitable data type for the columns in the table can make the table slim and make the data operation faster.

5.Oracle relational database foundation

1) Primary key: one or more columns in the table, whose values can uniquely identify each row in the table.

Any column in the table can be used as the primary key, but the following conditions must be met:

No two rows have the same primary key value.

Each row must have a primary key value (null values are not allowed in primary key columns).

Modifying or updating the value in the primary key column is not allowed.

The primary key value cannot be reused (if a row is deleted from the table, its primary key value cannot be assigned to new rows in the future).

Generally, the name of the primary key is id or uuid.

2) What is a foreign key?

In one relationship (reference table), it is the primary key, and another relationship references this key. Then this key is a foreign key in another relationship.

3) What can external buildings do?

To associate two relationships (tables), a foreign key can only refer to the primary key in the reference table. Maintain data consistency and integrity.

As shown in the figure:

4) How to represent them one by one in the database?

5) How to represent one-to-many in the database?

6) What are the integrity constraints of the relational model?

It is to constrain the relationship through some constraint conditions of the relationship. That is to say, when the value of the relationship changes with time, some constraints should be met. If the age cannot exceed 1000, the sex must be male or female.

7) What are the integrity constraints of the relational model?

Entity integrity, referential integrity, user-defined integrity.

Entity integrity: For the basic relationship, that is, the two-dimensional table, the primary key cannot be empty.

Referential integrity: when there is a relationship between tables, there will naturally be references (foreign keys) to the relationship. The relationship between tables is realized by foreign keys, which can be empty or refer to the primary key of the table.

Custom Integrity: Define your own integrity constraints according to different requirements. For example, non-numeric characters are not allowed in the student number, and the gender must be male or female.

6. Oracle Bone Inscriptions's self-study notes

1) Database and table names do not need to be capitalized.

2) A column is a piece of data stored in a table, and a row is a set of columns that can describe something. Columns and rows make up a table.

3) Create an oracle database. Create a library using oracle's own database configuration assistant.

4) Create a tablespace as DBA. The specific sql is as follows:

Create tablespace pzw data file' C:/oracle/pzw.dbf' size 1024M Reuse and automatically expand the detachment zone to manage the space of this lot.

Manage cars;

5) Delete user command

Discard the user pzw cascade;

6) Delete Tablespace command

Delete table space pzw, including content and data files;

7) View Tablespace command.

Link Address: Two Ways to View oracle Tablespaces

8) Create a user.

Create a user pzw identified by pzw;

9) Allocate package space to users.

Change the default tablespace pzw of user pzw;

10) to authorize users.

Grant create session, create table and unlimited tablespace to pzw.

1 1) to create a table.

Create table doughnut _ list

(

Donuts _name VARCHAR( 10),

Donuts _type VARCHAR(6),

Donuts _ birthday date

);

12) Delete table

Turn over the table

Donuts _ list;

13) adds a column to the table.

Change table EMP to add sales number;

Insert a piece of data into the database.

Inserted into DEPT_EMP_TABLE (DEPT_EMP_NO, EMP_NO, Dept_No, Joined_Date) value (009,' 00002', 1, to _ date ('201-)

Submit;

15) database to modify a piece of data.

Update emp set emp_name=' May', age =20, gender =' female', occupation =' pop singer', where emp _ no =' 00002.

Submit;

16) Delete a piece of data in the database.

Delete emp, where EMP _ NO =' 000013';

Submit;

17) to query all data.

select * from emp

18) to create a view

Create the view adress _ view as select * from pzw.adress

The following are oracle demonstration data operations and exercises (for beginners and. Log in with scott. Default password tiger)

1. View the demo data sheet.

select*fromtab

Or select table _ name from user _ tables;

2. Checklist structure (plsql operation is invalid. You can use naming hints to do this)

Desc department;

Check the employee's name

Select ename； from emp;

4. Query the employee's number and asterisk (sql statement is case-insensitive)

select empno，enamefrom emp

5. Query all fields

select * fromemp

It is generally recommended not to use the * sign, which is unclear. It is suggested to write the relevant fields after the select statement, which is inefficient.

6. List the number, name and annual salary of employees.

Select empno, ename, sal *12 from emp;

Operators can be used in the select statement, but there are some problems above. The field name of annual salary is not clear.

7. Display the fields of the query in Chinese.

Select empNOAS employee number from EMP, with ename as employee name and sal* 12 as annual salary;

You can use as to name the alias, or you can omit as.

For example, as can be used to name aliases, and as can be omitted.

8. Query employees with salary equal to 5000.

Select empno, ename, sal from emp where sal = 5000.

When comparing data of character type, it is case-sensitive.

9. Query employees whose salary is not equal to 5000.

select empno，ename，sal from emp where sal & lt& gt5000;

10. Query employees whose positions are not equal to managers.

select empno，ename，sal，job from emp where job & lt& gt

Manager';

In sql statements, if a string is enclosed in single quotation marks, it is different from the double quotation marks used in Java. If it is a number, it can also be enclosed in quotation marks, but the digital data is regarded as a string.

1 1. Query employees whose salary is 1600 to 3000 (the first method is >; = sum

Select empno, ename, sal from EMP where sal> =1600 and sal & lt=3000;

Query employees whose salary is 1600 to 3000 (the first way is between ... and ...).

Select employee number, name, sal and job from employees.

1600 and 3000;

Between ... and ... inclusive.

Between ... and ... apply not only to numerical data, but also to character data.

Between ... ... and ..., there are restrictions on the setting of two parameters, with the small number before and the big number after.

12. Query employees with blank allowance.

select * from emp where commis null

13. Query employees whose allowance is not empty.

select * from EMP where commis not null；

14. Employees who are managers and earn more than 2500 pounds.

select empno，ename，sal from EMP where job = ' MANAGER ' and sal & gt； 2500;

It means that all conditions must be met.

15. Find out which employees are managers and which are salesmen.

Select * from emp where job='MANAGER' or job =' SALESMAN.

Or, as long as the conditions are met, it is equivalent to or

16. Check the salary is greater than 1800, and the department number is twenty or thirty.

Select * from EMP where sal>1800 and (deptno = 20 or deptno = 30);

17. Find out which employees are managers and which are salesmen.

select * from EMP where jobin(' MANAGER '，' SALESMAN ')；

18. The query position is not equal to the manager, and the employee of the salesman cannot be associated (the first writing method).

Select * from EMP where job < > "manager" and job < & gt‘' salesman';

19. The query position is not equal to the manager and cannot be associated with the employee of the salesperson (the second writing method).

select * from EMP where job not in(' MANAGER '，' SALESMAN

20. Query all employees starting with m..

select * from EMP where name like ' M % '；

2 1. Query all employees ending in t.

select * from EMP where name like“% T”；

22. query all employees ending in o.

select * from EMP where name like“% O %”；

23. Query all employees whose first character is A in their names.

select * from EMP where ename like ' _ A % '；

Like can realize fuzzy query, and like supports% and underscore matching.

What's the difference between% and underscore in Like?

% matches any character any time.

The underscore matches only one occurrence of any character.

Like statements can be applied to numerical data, but% and underscore cannot be used without quotation marks. Similar to the operation of equal sign, if quotation marks are used, numerical data can be converted into character data with% and underscore, and then processed.

24. Sort by salary from small to large.

Select * from emporder by sal;

If there is a where clause, the order by must be placed after the where statement.

25. Manually specify the salary from small to large.

select * from emp order by saldesc

26. Sort by salary and name

Select * from employee orders by desc, Inamededesc;

If multiple fields are used for sorting, if the sorting based on the first field is repeated, the sorting will be based on the second field.

select * from emp order by sal asc

26. Manually assigned salaries should be sorted in descending order.

Sal ·desc's "Selected from emporder"

27. Sort by salary in ascending order (sorted by field position)

select * from EMP order by 6；

This method is not recommended because the meaning of numbers is not clear and the program is not robust.

28. Query employees. Convert all employee names to lowercase.

Select lower(ename) from emp;

29. Query the employee whose position is manager.

select * from EMP where job = upper(' manager ')；

30. Query all employees whose names begin with m..

select * from EMP where substr(ename， 1， 1)= ' M '；

The second parameter of the method indicates the position of the query character, 0, 1 all indicate the first character, the negative number indicates the position from the end, and the third parameter indicates the length of the intercepted string.

3 1. Get the length of the employee's name.

Select the length (ename) from emp;

32. Get all employees whose positions are managers.

select * from EMP where job = trim(' MANAGER ')；

Trim will delete leading and trailing spaces, but not middle spaces.

33. Query 1986-02-20 employees (the first method matches the format of the database).

select * from EMP where hired = ' 20-Fe b-8 1 '；

Query 1982-02-20 employees (the second method is to convert the string into date type).

Select * from emp where hiredate = to _ date ('1981-02-20 00: 00: 00',' yyyy-mm-ddhh24: mi: ss');

To_date can convert a string into a date, and the specific format is to_date (string, matching format).

34. Query the employees who joined after 198 1- 02-30, and the entry date format is YY-MM-DD HH: MM: SS.

select empno，ename，to_char(hiredate，' yyyy-mm-DD hh24:mi:ss ')from EMP where hiredate & gt； to _ date(' 198 1-02-2000:00:00 '，' YYYY-MM-DD HH24:MI:SS ')；

35. Inquire about employees' salaries before joining.

select empno，ename，to_char(sal，' $999，999 ')from EMP；

36. Check salary, add one thousandth, and keep two decimal places.

select empno，ename，to_char(sal，' $999，999.00 ')from EMP；

Convert numbers to strings, format

Functional character

explain

nine

Represents a number.

There are not enough numbers to fill in zeros.

dollar mark

Local currency symbol

Display decimal number

Show one thousandth

37. Convert a string into a numeric value

select * from emp where sal & gtto_number(' 1，500 '，' 999，999 ')；

38. Get the full salary of employees, salary+allowance.

select empno，ename，sal，comm，sal+nvl(comm，0)from EMP；

39. If the job is a manager, the salary will be increased by 10%, and if the job is a salesman, the salary will be increased by 50% (case … ends when … then …).

Select empno, ename, job, sal, (casejobwhen' manager' then sal *1.1when' sales' then sal *1.5 end) from emp as newsal.

40. If the job is a manager, the salary will increase by 10%, and if the job is a salesman, the salary will increase by 50% (decoding).

Select empno, ename, job, sal, decode(job,' MANAGER', SAL* 1. 1,' SALESMAN', sal* 1.5) from emp as newsal.

4 1. Rounding

Select round (1234567.4567,2) from dual;

Dual is provided by oracle, mainly for convenience, because you need to use from when choosing.

42. Aggregation function

count

Get the number of records

total

sue for peace

average

Average

maximum

Take the largest number

department

Take the smallest number

43. Get the number of all employees

select count(*)from EMP；

Count(*) means that all records are obtained, and null values will be obtained if null is ignored.

44. Number of employees whose allowance is not empty

Select the count (comm) from emp;

If you use count (field name), you will not get an empty record.

45. Get the number of jobs

select count(distinct job)from EMP；

Distinct can delete duplicate records.

46. Total wages earned

Select sum(sal) from emp;

47 total salary (sales+commission)

select sum(sal+nvl(comm，0))from EMP；

48. Get an average wage

Select avg (sal) from emp;

49. Get the highest salary

select max(to_char(hiredate，' yyyy-mm-DD '))from EMP；

50. Get the minimum wage

Select min(sal) from emp;

5 1. Get the earliest employees.

Select min(hiredate) from emp;

52. These aggregate functions can be used together in select.

Select count(*), sum(sal), avg(sal), max(sal) and min (sal) from emp;

53. To get the total salary of each position, you need to display the position name and total salary.

select job，sum(sal)from EMP group by job；

When using group by, fields used by non-aggregate functions must participate in grouping.

Aggregate functions cannot be used in Group by.

If order by is used, it must be placed after group by.

54。 Get an average salary of more than 2000 per post.

Select a job from the employee group, and the average value (sal) is based on the job with average value (sal) > 2000;

Execution order of grouping function:

1. Query data according to conditions.

Step 2 group

3. Using having filtering to get the correct data.

55. Display the information of each employee and the name of the department to which he belongs.

Select ename, dname from emp a, dept b where a.dept no = b.dept no;

The above query is also called "inner join", which refers to querying equal data.

56. Get the names of employees and their managers.

Select a.ename, b.ename from emp a, emp b where a.mgr = b.empno;

This is called "self-join", and there is only one table join. Specific query method

57. (Internal link) Displays the information of employees whose salary is more than 2,000, and displays the name of their department.

SQL99 syntax:

select ename，sal，dname from EMP a join dept b on a . deptno = b . deptno where sal & gt； 2000;

SQL92 syntax

Select ename, sal, dname from emp a, dept b, where a.deptno = b.deptno and sal >; 2000;

The difference between Sql92 syntax and sql99 syntax: 99 syntax can separate the connection between tables and query conditions, especially when multiple tables are connected, which will be clearer than sql92.

58. (External link) Display the information of employees whose salary is above 2,000, and display the name of their department. If a department has no employees. Then the department must also be displayed.

select dname，ename from EMP a right join dept b on a . deptno = b . deptno；

59. Query the employee's name and experience name. If there is no superior manager, you should also find out.

Select e.ename，m.ename mname from emp e，EMP m where m . empno(+)= e . mgr；

60. Inquire about employee information and who is the manager. Employee number and employee name are required.

select ename，ename from EMP where empno in(select distinct mgr from EMP where mgr not null)；

6 1. To find out whose salary is higher than the average salary of employees, you need to display the employee number, employee name and salary.

Select empno, ename, sal from emp where sal> (select AVG (sal) from emp);

Analysis idea: First, find out the dependence conditions according to the text description and analyze them one by one.

62. To query the grade of average salary of each department, you need to display the department number, average salary and grade number.

Select a.deptno, a.avg_sal, b.grade from (select deptno, avg (sal) avg _ sal from EMP group by deptno) a, salgrade b where a.avg_sal is between b.losal and b.hisal;

Important: treat the subquery as a table.

63. Query employee information and department name

Select e.empno, e.ename, e.deptno, (select dname from dept, where deptno = e.deptno) as dname in emp e e.

Union can merge sets (add).

Select * from emp where job='MANAGER'

alliance

select * from EMP where job = ' SALESMAN '

65. The minus sign can be moved out of the set (subtraction).

Query the department numbers of 10 and 20, and take out those with salaries greater than 2000.

select * from EMP where deptno in( 10，20)

negative

select * from emp where sal & gt2000