Index, view, Oracle of database

1. Index index

1. Definition

Indexing is a fast, ordered data structure that helps databases efficiently retrieve data. In addition to data, the database system maintains data structures (additional storage space) that satisfy specific lookup algorithms, which point to the data in some way so that efficient lookup algorithms can be implemented on these data structures. This data structure is called an index.

Generally speaking, the index itself is too large to store all in memory, so it is often stored on disk as an index file. Currently, most indexes are built using BTree trees.

2. Classification

IndexesMeaning
Single Value IndexAn index contains only one column, and a table can have more than one column
unique indexIndexed columns must have unique values, but null values are allowed; Primary keys automatically create unique indexes
Composite IndexAn index contains multiple columns at the same time

3. Grammar

3.1 Create index: create index name on table name (field name)

3.2 View Index: show index from table name

3.3 Create a unique index: alter table table table name add unique (field name)

3.4 Create a composite index: alter table table table name add index index name (field name, field name)

3.5 Delete index: alter table table table name drop index name

4. Testing

#Create a normal index:
create index name_index on teachers(tname)
#Query Index
show index from teachers
#Use Index
explain
select * from teachers where tname='Yitian'
#Create Unique Index
ALTER TABLE teachers ADD UNIQUE (tbirthday)
#Use Index
explain 
#Explain the execution of SQL mainly to see if an index is useful (see if the key has a value)
select * from teachers 
where tbirthday='1958-12-02 00:00:00'
#Create a composite index--Use with the leftmost attribute: prefer the leftmost
ALTER TABLE teachers ADD INDEX fuhe_index
(tname,tsex,tbirthday)

5. Leftmost Feature

When we create a joint index (composite index), such as (k1,k2,k3), we create three indexes (k1), (k1,k2) and (k1,k2,k3). This is the leftmost matching principle, also known as the leftmost attribute.

EXPLAIN: Explains the execution of SQL, mainly to see if an index is used and if the key has a value

EXPLAIN
SELECT * FROM empext WHERE phone='13572801415'

EXPLAIN
SELECT * FROM empext WHERE cardno='610113'

EXPLAIN
SELECT * FROM empext WHERE phone='13572801415' AND cardno='610113'

EXPLAIN
SELECT * FROM empext WHERE cardno='610113' AND phone='13572801415'

6. Summary

6.1 Advantages:
6.1.1 index is database optimization;

The primary key of the 6.1.2 table automatically creates the index by default;

6.1.3 substantially reduces the cost of IO disks in the database and greatly improves the retrieval speed.

The 6.1.4 index prioritizes the data to reduce the cost of sorting query data and CPU consumption.

6.2 Disadvantages:
The 6.2.1 index itself is a table that holds the primary key and index fields and points to the records of the entity table, so the index columns also take up space.

6.2.2 The contents of index tables are found in business tables, data is duplicated, and space is wasted.

6.2.3 Although the index greatly improves the speed of query, it negatively affects the efficiency of add, delete and change operations. If the data in the table changes, the index content will be incorrect and the information of the index table needs to be updated. If the amount of data is very large, the time to recreate the index will be greatly increased.

6.2.4 As business changes, previously built indexes may not meet query needs and will take us time to update them.

2. Viewview

1. Concepts

A view is essentially a query, unlike our own queries, which have a cache after execution and will be used directly the next time the query is executed. However, it is also not optimized because it is cached beforehand and is prohibited in large projects.

2. Grammar

2.1 Create View: create view view name as SQL statement

2.2 Use views: select * from view name

3. Testing

#Create View
create view stu_view 
as
select * from students limit 3

#Use Views
select * from stu_view

4. Summary

4.1 Benefits: Optimized SQL, complex SQL read-write once, results will be cached in the view, next time you can directly query the view

4.2 Disadvantage: Data is duplicated, occupies memory, and SQL cannot be optimized

3. SQL interview questions

1. Query all records

select * from students;	Low efficiency

2. Query only specified columns

select sno,sname,class from students;

3. Query only records with id 101

select * from students where sno=101;

4. Fuzzy Query Records

select * from students where sname like 'king%';

5. Query all records between specified ranges

select * from students where sno between 100 and 105;

6. in The following two sentences are equivalent

select * from students where sno in (101,105);
select * from students where sno=101 or sno=105;

7. Query records that satisfy two criteria

select * from students where sname like 'king%' and class=95033;

8. Query Jack's address (joint search)

select address from tb_user_address,tb_user where tb_user.id=tb_user_address.user_id and tb_user.name='jack';
select address from tb_user_address where user_id=(select id from tb_user where NAME='jack');

9. Calculate the age of each trainee

select sname,(year(now())-year(sbirthday)) Age from students;

10. Query records in ascending order by age

select sname,(year(now())-year(sbirthday)) age from students order by age;

11. Total Records

select count(1) from students;

12. Query the oldest and youngest of records

select max(age),min(age) from user;

13. Query the oldest user information

select * from user order by age desc limit 1;

14. Queries older than average age

select * from user where age>(select avg(age) from user);

15. Query Department maximum pay

select deptno,max(sal) from emp group by deptno;

16. Query the average salary for each department

select deptno,avg(sal) from emp group by deptno;

17. Query employee information for Department

select * from emp where deptno is not null;

18. Query employee information for unassigned departments

select * from emp where deptno is null;

19. Query employee information with the same name

Group by name first,Reoutput records with names greater than 1
select * from emp where name=(select name from emp group by name having count(name)>1);

20. Query the top three students

select sno,sum(degree) Total score from scores group by sno order by Total score desc limit 3; 
The numbers found were: 103,107,105
select * from students where sno in (103,107,105);

21. Query course is the student information in the top 3 points of "Introduction to Computer"

select sno from scores where cno=(select cno from courses where cname='Introduction to Computer Science') order by degree desc limit 3;
The numbers found were: 103,107,105
select * from students where sno in (103,107,105);

22. Last three students of course number 3-105

select sno from scores where cno='3-105' order by degree limit 3;
The numbers found were: 101,109,108
select * from students where sno in (101,109,108);

23. Query the information of the female teacher

select * from teachers where tsex='female';

4. Oracle Database

1. Installation

Be sure to "run as administrator". Oracle is system-level and requires privileges to install services. The firewall is usually closed during installation, otherwise it is easy to fail installation.






2. Oracle10g EX


Install the OracleXEUniv simplified version. exe, the EX service will be created automatically. If you install the installation version, you need to create the local service manually.
After installation, Listener and ServiceXE start automatically


When installing, it is best not to install to the c disk, choose the d disk. Installation requires entering the system password, which is important to remember that the oracle password cannot be cracked. Forgot that you can only reinstall. Remember.

Be careful:

1) The default oracle opens the 8080 web service, which conflicts with the Tomcat default port 8080. oracle 8080 can be turned off, or tomcat's default port can be changed.

2) After the Oracle installation is complete, you cannot use sql-plus to test whether the installation was successful because it has very large privileges. Can communicate directly with oracle. You can use the plsql tool, if you can connect, then the written program can be accessed normally.

3) If the installation fails, first stop the listening Service, then stop the Service, then run the installation package to uninstall.

3. Configure local services

Remote Access to Oracle Server

Before remote access, install Oracle server-side on the server, Oracle client on the client, and use PL/SQL to access Oracle server-side through Oracle client.

Modify tnsnames. The ora file configures the address for remote access:
D:\oraclexe\app\oracle\product\10.2.0\server\NETWORK\ADMIN\tnsnames.ora

XEremote =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.105)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = XE)
    )
  )

4. Connection error

After a long period of inaccessibility, Oracle will automatically disconnect. In this case, you can login again.

5. Create a database

5.1 Notes:

1). Unlike MySQL, Oracle's database concept is to create an account under which all tables and so on are placed. So the account in Oracle is the same as the database in MySQL.

2). Do not give users direct access to the system. The permissions are too large, causing security risks, data disclosure, or even deleting someone else's account (database). Illegal persons must not be given the opportunity to run away from their stores. As a result, accounts are usually created separately for each business and privileges are assigned separately.

5.2 Password Security

The system account cannot be modified. It is a super administrator. The password must be secure or otherwise dangerous.

Enterprise settings in the actual development business:

14yHl9t-hjcmt		Nine days suspected of Milky Way falling-Go home and eat steamed bread

5.3 Create Users
Re-login with the new account ht:

6. PL/SQL Client Operations

6.1 Common Errors

If it takes too long to access Oracle,Oracle automatically closes the connection. Then close it and reopen it.

6.2 Choose your own content
6.3 Common Objects

6.4 SQL Window

Enter SQL statement execution, you can enter more than one SQL, execute through the selection, not all execution.

7. Use

7.1 MySQL: Database-Table-Record

7.2 Oracle: User-Table-Record

7.3 Create User: New - User - Enter User Name and Password - Set Permissions dba/resource/connect - OK

7.4 New User Login: Key in upper left corner - Enter username and password - OK

7.5 Create Table: Select Tables - Right-click - New - Enter Table Name, Field, Primary Key - OK

7.6 Create records: Select table - Right-click - Edit data - Fill in new data -

8. Preparing data





9. Testing

select t.* from student t;

oracle default date format: day-month-year

insert into student values('king','1','10-8 month-1990','php',3);

Modify date format:

alter session set nls_date_format = 'yyyy-mm-dd' ;

Insert data in new format

insert into student values('Liu','0','2000-1-1','java',4);

Decde judges that if 0 is found, women and 1 is male

select ssex, decode(ssex,0,'female',1,'male') sex  from student;

casewhen judges that if 0 is found, women and men are shown.

select ssex, 
		case ssex when '0' then 'female' else 'male' end   sex
from student

Oracle Paging rownum

select t.* from student t  limit 2  mysql;
select t.* from student t where rownum <= 2;
Oracle Special Grammar

Keywords: Database MySQL Oracle SQL

Added by Majes on Mon, 31 Jan 2022 22:32:37 +0200

Popular Keywords

©2024 Programming VIP