What is a stored procedure? What are the advantages and disadvantages?

What is a stored procedure? What are the advantages and disadvantages?

Stored procedures, like functions in our programming language, encapsulate our code (PLSQL, T-SQL).

Advantages of stored procedures:

  • The ability to encapsulate code
  • Save it in the database
  • Let the programming language do the calling
  • A stored procedure is a precompiled block of code that is more efficient to execute
  • Replacing a large number of T_SQL statements with a single stored procedure can reduce network traffic and increase communication rates

Disadvantages of stored procedures:

  • The stored procedure syntax is nearly different from database to database and is difficult to maintain (not universal)
  • The business logic sits on the database and is difficult to iterate

As a reading benefit Xiaobian sorted out the database related interview questions + core knowledge points, as neededClick here to

What are the three paradigms

What are the three paradigms

First normal form (1NF) : The fields in the database table are all single attributes and cannot be subdivided. This single attribute is made up of primitive types, including integer, real, character, logical, date, and so on. Second Normal Form (2NF) : There is no partial functional dependency of non-critical fields on any candidate key fields in the database table (partial functional dependency refers to the situation that some fields in a combined key determine the non-critical fields), that is, all non-critical fields are completely dependent on any set of candidate keys. Third Normal Form (3NF) : On the basis of the second normal form, the data table conforms to the third normal form if there is no transfer function dependency of non-critical fields to any candidate key fields. The so-called transfer function dependence refers to that if there is A decisive relation of “A → B → C”, then the C transfer function depends on A. Therefore, a database table that satisfies the third normal form should not have the following dependencies: key field → non-critical field X → non-critical field Y

The above words we certainly can not understand, and do not want to read. Here’s what I’d like to summarize:

  • The first point should be made clear: if the third paradigm is satisfied, the second paradigm must be satisfied, and if the second paradigm is satisfied, the first paradigm must be satisfied

  • First normal form: fields are the smallest units that cannot be subdivided

    • The student information table consists of age, gender, student number and other information. None of these fields are subdivided, so it satisfies the first normal form

  • Second normal form: To satisfy the first normal form, the fields in the table must be completely dependent on all primary keys and not just some primary keys.

    • This row of other fields represents the same thing as the primary key, and the primary key is unique, so they only need to depend on the primary key, so they’re unique
    • Student number 1024, name is Java3y, age is 22. The name and age fields depend on the student number primary key.

  • Third normal form: To satisfy the second normal form, all fields that are not primary keys must be independent of each other

    • The data is stored in only one place and does not appear repeatedly in multiple tables, which can be considered to eliminate the transfer dependency
    • For example, our university is divided into many departments (Chinese Department, English Department, Computer Department…) , the department management form information consists of the following fields: department number, department head, department description, and department structure. Can we add the department number, dean, department profile, and department structure fields to the student information table? No, because this will be redundant, the fields outside the primary key form a dependency (dependent on the student information table)! The correct approach is to add only one department number field to the student table.

What is a view? And what are the use scenarios for the view?

What is a view? And what are the use scenarios for the view?

A view is a virtual table based on a data table

  • (1) A view is a virtual table
  • (2) The view is built on the basis of existing tables. These tables are called base tables
  • (3) The statement that provides data content to the view is a SELECT statement, which can be understood as a stored SELECT statement
  • (4) The view provides the user with another representation of the base table data
  • (5) The view does not store real data, and the real data is still stored in the base table
  • (6) Although the programmer manipulates the view, the view will eventually be converted into the operation base table
  • (7) A base table can have zero or more views

Sometimes, we may only relate to certain fields of a data table, while others only relate to certain fields of the same data table…

It doesn’t make sense to show them all the fields.

We should give them what data they want to see… On the one hand, we can keep them focused on their own data. On the other hand, we can make sure that some of the confidential data in the data sheet will not leak out…

When we query data, we often need to write very long SQL statements, almost every time to write very long…. As mentioned above, a view is a virtual table based on a query. That is, a view can encapsulate the query data. Then it will be very convenient for us to use…

It is important to note that using views allows us to focus on logic, but does not improve query efficiency

In what scenarios are DROP, DELETE, and TRUNCATE used?

In what scenarios are DROP, DELETE, and TRUNCATE used?

Let’s compare the differences:

drop table

  • 1) belongs to the DDL
  • 2) Do not roll back
  • 3) Do not use “where”
  • 4) Delete the contents and structure of the table
  • 5) Fast deletion speed

truncate table

  • 1) belongs to the DDL
  • 2) Do not roll back
  • 3) Do not use “where”
  • 4) delete the contents of the table
  • 5) Fast deletion speed

delete from

  • 1) belongs to the DML
  • 2) can be rolled back
  • 3) can take the where
  • 4) The contents of the table depend on the execution of WHERE
  • 5) Delete speed is slow, need to delete line by line
  • Use DROP when a table is no longer needed
  • When you want to delete part of a row, use DELETE with a WHERE clause
  • Use truncate when keeping a table and deleting all data

What is an index? What effect and advantage and disadvantage do they have?

What is an index? What effect and advantage and disadvantage do they have?

What is an Index?

  • (1) It is a mechanism for quickly querying the contents of the table, similar to the directory of Xinhua Dictionary
  • (2) Apply to some fields in the table, but when stored, independent of the table

The index table makes the data orderly….

Quickly locate data files on your hard drive…

The rowid characteristics

The characteristics of the rowid

  • (1) is located in each table, but is not visible on the surface, for example: desc emp is not visible
  • (2) Only in SELECT, write ROWID, can be seen
  • (3) it is bound to each table, table death, the ROWID death, two tables can be the same, but ROWID must be unique
  • (4) ROWID is an 18-bit mixture of case and numbers, and the only table represents the location of the entry recorded in the DBF file
  • (5) When rowid participates in a =/like comparison, enclose the value of rowid in single quotes and be case-sensitive
  • (6) ROWID is the bridge between the table and the DBF file

The index characteristics

Index features

  • (1) Once an index is established, Oracle Management System maintains it automatically, and Oracle Management System decides when to use the index
  • (2) The user does not need to specify which index to use in the query statement
  • (3) The system will automatically create an index on the corresponding column after defining a PRIMARY KEY or UNIQUE constraint
  • (4) Users can also add indexes to a single field or multiple fields according to their own needs

If you specify a PRIMARY KEY or UNIQUE constraint, you will automatically create an index on the corresponding column. If you specify a PRIMARY KEY or UNIQUE constraint, you will automatically create an index on that column.

When is the index created

  • (1) The table often performs SELECT operations
  • (2) The table is very large (too many records), and the contents of records are distributed in a wide range
  • (3) Column names often appear in WHERE clauses or join conditions

When do you [don’t] create indexes

  • INSERT/UPDATE/DELETE operations are frequently performed on tables
  • (2) table is very small (record is very few)
  • (3) Column names are not often used as join conditions or appear in WHERE clauses

Index pros and cons:

  • Indexes speed up database retrieval
  • Indexes slow down maintenance tasks such as inserts, deletes, and modifications (although indexes can speed up queries, they can also slow down the performance of database systems updating data, since most data updates require indexes to be updated at the same time)
  • Unique indexes can ensure the uniqueness of each row of data. By using indexes, you can use optimization hiders in the process of querying and improve the performance of the system
  • Indexes need to take up physical and data space

Index Classification:

  • Unique index: A unique index does not allow two rows to have the same index value
  • Primary key index: Defining a primary key for a table automatically creates a primary key index, which is a special type of unique index. A primary key index requires that each value in the primary key be unique and cannot be null
  • Aggregated indexes (Clustered) : The physical order of the rows in the table is the same as the logical (index) order of the key values, and there can be only one per table
  • Non-clustered indexes: A non-clustered index specifies the logical order of tables. The data is stored in one location, the index is stored in another location, and the index contains Pointers to the location where the data is stored. You can have multiple, less than 249

What is a transaction?

What is a transaction?

A transaction is simply put: all operations performed in a Session either succeed or fail simultaneously

ACID – Four basic elements for the correct execution of database transactions

  • These include: Atomicity, Consistency, Isolation, and Durability.

A database system that supports transactions must have these four characteristics, otherwise the correctness of the data cannot be guaranteed during the Transaction processing process, and it is highly likely that the Transaction will not be reached.

For example :A transfers money to B. If there is A problem in the transfer process, the transaction can restore the data to the original [account A has the same money, and account B has the same money].

Example description:

/* * Let's simulate the scene where A transfers money to B account * A and B account each have RMB 1000, now I want A account to transfer RMB 500 to B account * * */ / By default, JDBC closes transactions. SQL = "UPDATE A SET money=money-500 "; preparedStatement = connection.prepareStatement(sql); preparedStatement.executeUpdate(); String sql2 = "UPDATE B SET money=money+500"; preparedStatement = connection.prepareStatement(sql2); preparedStatement.executeUpdate();

Viewed from above, we can indeed find that A transfers to B, which is successful. But what if something goes wrong in the transfer process from A to B? So let’s simulate that

// SQL = "UPDATE A SET money=money-500 "; preparedStatement = connection.prepareStatement(sql); preparedStatement.executeUpdate(); Int a = 3/0; int a = 3/0; String sql2 = "UPDATE b SET money=money+500"; preparedStatement = connection.prepareStatement(sql2); preparedStatement.executeUpdate();

Obviously, the above code will throw an exception, so let’s query the data again. Account A is missing $500, but account B has not increased. This is clearly unreasonable.

We can solve the above problems through transactions

// When the transaction is turned on, the operation on the data does not take effect immediately. connection.setAutoCommit(false); // SQL = "UPDATE A SET money=money-500 "; preparedStatement = connection.prepareStatement(sql); preparedStatement.executeUpdate(); Int a = 3/0; int a = 3/0; String sql2 = "UPDATE B SET money=money+500"; preparedStatement = connection.prepareStatement(sql2); preparedStatement.executeUpdate(); // If the program gets to this point and doesn't throw an exception, we submit the data connection.mit (); // Close the transaction Connection.setAutoCommit (true); } catch (SQLException e) {try {// If there is an exception in the transaction, we will rollback the connection. // Close the transaction Connection.setAutoCommit (true); } catch (SQLException e1) { e1.printStackTrace(); }

The program above also throws an exception. The money in account A does not decrease and the money in account B does not increase.

Note: When a Connection encounters an unhandled SQlexception, the system exits abnormally and the transaction rolls back automatically. However, if the exception is caught, it is necessary to explicitly roll back the transaction in a catch.

Transaction isolation level

The database defines four isolation levels:

  1. Serializable: Avoid dirty reads, unrepeatable reads, and fake reads.
  2. Repeatable read Repeatable read Repeatable read
  3. Read committed to avoid dirty Read
  4. You can’t Read uncommitted.

Each corresponds to four constants in the Connection class

  1. TRANSACTION_READ_UNCOMMITTED
  2. TRANSACTION_READ_COMMITTED
  3. TRANSACTION_REPEATABLE_READ
  4. TRANSACTION_SERIALIZABLE
    • *

Dirty read: One transaction reads uncommitted data from another transaction

Example: A transfers money to B, A executes the transfer statement, but A has not committed the transaction, B reads the data, and finds that his account has more money! B says to A, I’ve already received the money. A rolls back the transaction, and when B checks the money in the account again, he finds that there is not much money.

Non-repeatable reads: A transaction reads data already committed by another transaction, which means that a transaction can see changes made by other transactions

Note: A queries the database to get the data, and B modifies the data in the database, so that A queries the database several times with different results [harm: The results of each query by A are affected by B, so the information queried by A is meaningless]

Phantom read: This is when data inserted by another transaction is read in one transaction, resulting in inconsistent reads.

Note: Similar to non-repeatable reads, virtual reads (phantom reads) can read data inserted by other transactions, resulting in inconsistent reads

Summary: Dirty reading is not tolerated. Unrepeatable reading and virtual reading are OK under certain circumstances.

What are the optimistic and pessimistic locks of a database?

What are the optimistic and pessimistic locks of a database?

Optimistic lock and pessimistic lock are the main technical means of concurrency control to ensure that the isolation and unity of transactions and the unity of database will not be destroyed when multiple transactions access the same data in the database simultaneously.

  • Pessimistic locking: Assume concurrency conflicts and mask any operations that might violate data integrity

    • When the data is queried, the transaction is locked until the transaction is committed
    • How to do it: Use the locking mechanism in the database

  • Optimistic locking: Assume that no concurrency conflicts occur and only check for data integrity violations when the operation is committed.

    • The transaction is locked while the data is being modified, using version to lock it
    • How to do it: Use version or timestamp

Pessimistic locks:

Optimistic locking:

What are superkeys, candidate keys, primary keys, and foreign keys?

What are superkeys, candidate keys, primary keys, and foreign keys?

  • Superkey: The set of attributes that uniquely identify a tuple in a relationship is called the relational schema’s superkey. A single property can be used as a superkey, or a combination of properties can be used as a superkey. A superkey contains a candidate key and a primary key.
  • Candidate key (candidate code) : is the smallest hyperkey, that is, there are no redundant elements of the hyperkey.
  • Primary key: A combination of data columns or attributes in a database table that uniquely and fully identifies a stored data object. A column can have only one primary key, and the primary key value cannot be missing, that is, Null.
  • Foreign key: The primary key of another table that exists in a table is called the foreign key of that table.

Candidate and primary codes:

Example: Mailing address (city name, street name, zip code, company name, recipient)

  • It has two candidate keys :{city name, street name} and {street name, zip code}
  • If I select {city name, street name} as the attribute that uniquely identifies the entity, then {city name, street name} is the primary code (primary key)

What types of SQL constraints are there?

What types of SQL constraints are there?

  • NOT NULL: The contents of the control field must NOT be NULL.
  • Unique: Control field contents cannot be duplicated, and a table can have multiple Unique constraints.
  • Primary KEY: also used to control the contents of the field cannot be repeated, but it is allowed to appear in a table only one.
  • Foreign KEY: An action used to prevent a connection between tables from being broken. It also prevents illegal data from being inserted into a FOREIGN KEY column, because it must be one of the values in the table to which it points.
  • Check: Used to control the range of values for the field.

What state does the database run in to prevent data loss?

What state does the database run in to prevent data loss?

In archivelog mode, you can effectively prevent data loss as long as the archived log files are not lost.

MySQL Storage Engine

MySQL has the following storage engines:

Mine is version 5.7.15, default is InnoDB version!

Common storage engines include the following:

  • InnoDB engine, InnoDB engine provides support for ACID transactions in the database. Row level locking and foreign key constraints are also provided. It is designed to deal with large data capacity of the database system.
  • The MyIASM engine (the original MySQL default engine) does not support transactions, row level locking and foreign keys.
  • Memory Engine: All data is in MEMORY, which is fast but not secure.

The same database can also use tables from multiple storage engines. If a table modification requires high transaction processing, you can choose InnoDB. In this database, you can select MyISAM to store tables that are more query-demanding. If the database requires a temporary table for querying, you can choose the MEMORY storage engine.

What is the data structure of the index used by both the MyIASM and InnoDB engines?

What is the data structure of the index used by both the MyIASM and InnoDB engines?

Answer: both B+ trees!

MyIASM engine, the contents stored in the data structure of B+ tree are actually the address values of the actual data. That is, the index is separate from the actual data, but the index is used to point to the actual data. This pattern of indexing is called a nonclustered index.

The index structure of the InnoDB engine is also B+ tree, but the data structure is stored in the actual data, such indexes are called clustered indexes.

The difference between VARCHAR and CHAR

The difference between VARCHAR and CHAR

CHAR is a fixed-length type, and VARCHAR is a variable-length type

MySQL about the permissions of the table have which a few

MySQL about the permissions of the table have which a few

The MySQL server controls user access to the database through the permission table. The permission table is stored in the MySQL database and initialized by the mysql_install_db script. These access tables are user, db, table_priv, columns_priv, and host. The structure and contents of these tables are described below:

  • User permission table: records the user account information that is allowed to connect to the server. The permissions in the table are global.
  • DB permission table: records the operation permissions of each account in each database.
  • Table_priv permission table: Recording operation permissions at the data table level.
  • COLUMNS_PRIV permission table: Recording operation permissions at the data column level.
  • Host permission table: Cooperate with DB permission table for more detailed control of database level operation permissions on a given host. This permission table is not affected by GRANT and REVOKE statements.

What are the repair methods for data table corruption?

What are the repair methods for data table corruption?

Use MyIsamchk to repair. The steps are as follows:

  • 1) Stop MySQL service before repair.
  • Mysql > mysql /bin > mysql /bin > mysql /bin
  • 3) Execute myisamchk — recover database path /*.myi

OPTIMIZE TABLE TABLE_NAME REPAIR TABLE is used to repair broken tables. Optimize TABLE is used to retrieve unused database space. When you remove rows from a TABLE, the space is not immediately recovered. When you use the OPTIMIZE TABLE command, the space is recovered and the rows are rearranged on the disk (note: on the disk, not the database)

On what is InnoDB engine’s row locking done in MySQL

On what is InnoDB engine’s row locking done in MySQL

InnoDB does row locking based on indexes

Select * from tab_with_index where id = 1 for update;

For update can perform row locking based on conditions, and if the id is a column with an index key,

If id is not an index key then InnoDB will complete the table lock and concurrency will be out of the question

Database optimization ideas

SQL optimization

When we write SQL statements, in fact, the order of writing, the strategy will affect the performance of SQL, although the implementation of the same function, but their performance will be slightly different.

So, here’s how to do it when writing SQL.

① Select the most efficient table name order

The database parser processes the table names in the FROM clause in right-to-left order, with the last table written in the FROM clause processed first

In the case of multiple tables in the FROM clause:

  • If the three tables are completely unrelated, write the table with the fewest records and column names last, and so on
  • That is, select the table with the fewest entries and put it last

If there are more than 3 table join queries:

  • If the three tables are related, put the most referenced table last, and so on.
  • That is, tables referenced by other tables are placed last

For example: query employee number, name, salary, salary level, department name

The EMP table is the most referenced and has the most records, so it is placed at the end of the FORM clause

select emp.empno,emp.ename,emp.sal,salgrade.grade,dept.dname from salgrade,dept,emp where (emp.deptno = dept.deptno) and  (emp.sal between salgrade.losal and salgrade.hisal)

② Join order in the WHERE clause

The database parses the WHERE clause in a right-to-left order. According to this principle, joins between tables must be written to the left of other WHERE conditions, and conditions that filter out the maximum number of records must be written to the right of the WHERE clause.

Emp. Sal can filter multiple records, written at the right of the WHERE clause


      select emp.empno,emp.ename,emp.sal,dept.dname
      from dept,emp
      where (emp.deptno = dept.deptno) and (emp.sal > 1500)

③ Avoid using the * in the SELECT clause

When we were learning, the “*” sign could get all the field data in the table.

  • But it is done by querying the data dictionary, which means it will take more time
  • SQL statements written with the * sign are also less intuitive.
    • *

④ Use TRUNCATE instead of DELETE

This is simply: delete all records from the table except for the table structure.

DELETE deletes records one by one, while TRUNCATE deletes the entire table, preserving the table structure, which is faster than DELETE

⑤ Use more internal functions to improve the efficiency of SQL

For example, using mysql concat () function is better than using | | for joining together quickly, because the concat () function has been mysql optimization.

⑥ Use aliases for tables or columns

If the table or column names are too long, using a few short aliases can also improve SQL performance slightly. After all, there are fewer characters to scan…

⑦ Use Commit more often

COMIIT releases rollback points…

⑧ Make good use of indexes

The purpose of an index is to improve the query data. When the number of records in a table is very large, we can use an index.

Pet-name ruby to write SQL capitals

When writing SQL, the official recommendation is to write keywords in uppercase because Oracle servers always convert lowercase letters to uppercase before executing them

Attending to avoid using NOT on indexed columns

When the Oracle server encounters a NOT, it will stop working and perform a full table scan instead

①① Avoid using calculations on indexed columns

In the WHERE clause, if the index column is part of the function, the optimizer will use a full table scan instead of the index, which will be slower

(1) (2) with> =alternative>

SELECT * FROM EMP WHERE DEPTNO=3 SELECT * FROM EMP WHERE DEPTNO=3 SELECT * FROM EMP WHERE DEPTNO=3 SELECT * FROM EMP WHERE DEPTNO >= 4 SELECT * FROM EMP WHERE DEPTNO >= 4

①③ IN place of OR

select * from emp where sal = 1500 or sal = 3000 or sal = 800; Select sal from emp where sal in (1500, 300, 800);

①④ Always use the first column of an index

If the index is built on multiple columns, the optimizer will choose to use the index only if its first column is referenced by the WHERE clause. When only the second column of the index is referenced, but the first column of the index is not, the optimizer uses a full table scan and ignores the index

create index emp_sal_job_idex on emp(sal,job); ---------------------------------- select * from emp where job ! = 'SALES'; I'm not going to use the index up here.

Database structure optimization

  • 1) Paradigm optimization: such as eliminating redundancy (save space..)
  • 2) Anti-normal optimization: such as appropriately adding redundancy (reducing join)
  • 3) Split table: vertical split and horizontal split

Server hardware optimization

This cost a lot of money!

SQL exercises

Table structure:

Student (SNO, SNAME, SSEX) Course (CNO, CNAME, TNO) Course (SNO, CNO, SCORE

Topic:


101，查询课程1的成绩比课程2的成绩高的所有学生的学号
select a.sno from
(select sno,score from sc where cno=1) a,
(select sno,score from sc where cno=2) b
where a.score>b.score and a.sno=b.sno

102，查询平均成绩大于60分的同学的学号和平均成绩
select a.sno as "学号", avg(a.score) as "平均成绩" 
from
(select sno,score from sc) a 
group by sno having avg(a.score)>60

103，查询所有同学的学号、姓名、选课数、总成绩
select a.sno as 学号, b.sname as 姓名,
count(a.cno) as 选课数, sum(a.score) as 总成绩
from sc a, student b
where a.sno = b.sno
group by a.sno, b.sname

或者：

selectstudent.sno as 学号, student.sname as 姓名,
 count(sc.cno) as 选课数, sum(score) as 总成绩
from student left Outer join sc on student.sno = sc.sno
group by student.sno, sname

104，查询姓“张”的老师的个数

selectcount(distinct(tname)) from teacher where tname like '张%‘
或者：
select tname as "姓名", count(distinct(tname)) as "人数" 
from teacher 
where tname like'张%'
group by tname

105，查询没学过“张三”老师课的同学的学号、姓名
select student.sno,student.sname from student
where sno not in (select distinct(sc.sno) from sc,course,teacher
where sc.cno=course.cno and teacher.tno=course.tno and teacher.tname='张三')

106，查询同时学过课程1和课程2的同学的学号、姓名
select sno, sname from student
where sno in (select sno from sc where sc.cno = 1)
and sno in (select sno from sc where sc.cno = 2)
或者：

selectc.sno, c.sname from
(select sno from sc where sc.cno = 1) a,
(select sno from sc where sc.cno = 2) b,
student c
where a.sno = b.sno and a.sno = c.sno
或者：

select student.sno,student.sname from student,sc where student.sno=sc.sno and sc.cno=1
and exists( select * from sc as sc_2 where sc_2.sno=sc.sno and sc_2.cno=2)

107，查询学过“李四”老师所教所有课程的所有同学的学号、姓名
select a.sno, a.sname from student a, sc b
where a.sno = b.sno and b.cno in
(select c.cno from course c, teacher d where c.tno = d.tno and d.tname = '李四')

或者：

select a.sno, a.sname from student a, sc b,
(select c.cno from course c, teacher d where c.tno = d.tno and d.tname = '李四') e
where a.sno = b.sno and b.cno = e.cno

108，查询课程编号1的成绩比课程编号2的成绩高的所有同学的学号、姓名
select a.sno, a.sname from student a,
(select sno, score from sc where cno = 1) b,
(select sno, score from sc where cno = 2) c
where b.score > c.score and b.sno = c.sno and a.sno = b.sno

109，查询所有课程成绩小于60分的同学的学号、姓名
select sno,sname from student
where sno not in (select distinct sno from sc where score > 60)

110，查询至少有一门课程与学号为1的同学所学课程相同的同学的学号和姓名
select distinct a.sno, a.sname
from student a, sc b
where a.sno <> 1 and a.sno=b.sno and
b.cno in (select cno from sc where sno = 1)

或者：

select s.sno,s.sname 
from student s,
(select sc.sno 
from sc
where sc.cno in (select sc1.cno from sc sc1 where sc1.sno=1)and sc.sno<>1
group by sc.sno)r1
where r1.sno=s.sno

111、把“sc”表中“王五”所教课的成绩都更改为此课程的平均成绩
update sc set score = (select avg(sc_2.score) from sc sc_2 wheresc_2.cno=sc.cno)
from course,teacher where course.cno=sc.cno and course.tno=teacher.tno andteacher.tname='王五'

112、查询和编号为2的同学学习的课程完全相同的其他同学学号和姓名
这一题分两步查：

1，

select sno
from sc
where sno <> 2
group by sno
having sum(cno) = (select sum(cno) from sc where sno = 2)

2，
select b.sno, b.sname
from sc a, student b
where b.sno <> 2 and a.sno = b.sno
group by b.sno, b.sname
having sum(cno) = (select sum(cno) from sc where sno = 2)

113、删除学习“王五”老师课的sc表记录
delete sc from course, teacher
where course.cno = sc.cno and course.tno = teacher.tno and tname = '王五'

114、向sc表中插入一些记录，这些记录要求符合以下条件：
将没有课程3成绩同学的该成绩补齐, 其成绩取所有学生的课程2的平均成绩
insert sc select sno, 3, (select avg(score) from sc where cno = 2)
from student
where sno not in (select sno from sc where cno = 3)

115、按平平均分从高到低显示所有学生的如下统计报表：
-- 学号,企业管理,马克思,UML,数据库,物理,课程数,平均分
select sno as 学号
,max(case when cno = 1 then score end) AS 企业管理
,max(case when cno = 2 then score end) AS 马克思
,max(case when cno = 3 then score end) AS UML
,max(case when cno = 4 then score end) AS 数据库
,max(case when cno = 5 then score end) AS 物理
,count(cno) AS 课程数
,avg(score) AS 平均分
FROM sc
GROUP by sno
ORDER by avg(score) DESC

116、查询各科成绩最高分和最低分：

以如下形式显示：课程号，最高分，最低分
select cno as 课程号, max(score) as 最高分, min(score) 最低分
from sc group by cno

select  course.cno as '课程号'
,MAX(score) as '最高分'
,MIN(score) as '最低分'
from sc,course
where sc.cno=course.cno
group by course.cno

117、按各科平均成绩从低到高和及格率的百分数从高到低顺序
SELECT t.cno AS 课程号,
max(course.cname)AS 课程名,
isnull(AVG(score),0) AS 平均成绩,
100 * SUM(CASE WHEN isnull(score,0)>=60 THEN 1 ELSE 0 END)/count(1) AS 及格率
FROM sc t, course
where t.cno = course.cno
GROUP BY t.cno
ORDER BY 及格率 desc

118、查询如下课程平均成绩和及格率的百分数(用"1行"显示): 

企业管理（001），马克思（002），UML （003），数据库（004） 
select 
avg(case when cno = 1 then score end) as 平均分1,
avg(case when cno = 2 then score end) as 平均分2,
avg(case when cno = 3 then score end) as 平均分3,
avg(case when cno = 4 then score end) as 平均分4,
100 * sum(case when cno = 1 and score > 60 then 1 else 0 end) / sum(casewhen cno = 1 then 1 else 0 end) as 及格率1,
100 * sum(case when cno = 2 and score > 60 then 1 else 0 end) / sum(casewhen cno = 2 then 1 else 0 end) as 及格率2,
100 * sum(case when cno = 3 and score > 60 then 1 else 0 end) / sum(casewhen cno = 3 then 1 else 0 end) as 及格率3,
100 * sum(case when cno = 4 and score > 60 then 1 else 0 end) / sum(casewhen cno = 4 then 1 else 0 end) as 及格率4
from sc

119、查询不同老师所教不同课程平均分, 从高到低显示
select max(c.tname) as 教师, max(b.cname) 课程, avg(a.score) 平均分
from sc a, course b, teacher c
where a.cno = b.cno and b.tno = c.tno
group by a.cno
order by 平均分 desc
或者：
select r.tname as '教师',r.rname as '课程' , AVG(score) as '平均分'
from sc,
(select  t.tname,c.cno as rcso,c.cname as rname
from teacher t ,course c
where t.tno=c.tno)r
where sc.cno=r.rcso
group by sc.cno,r.tname,r.rname 
order by AVG(score) desc

120、查询如下课程成绩均在第3名到第6名之间的学生的成绩：
-- [学生ID],[学生姓名],企业管理,马克思,UML,数据库,平均成绩
select top 6 max(a.sno) 学号, max(b.sname) 姓名,
max(case when cno = 1 then score end) as 企业管理,
max(case when cno = 2 then score end) as 马克思,
max(case when cno = 3 then score end) as UML,
max(case when cno = 4 then score end) as 数据库,
avg(score) as 平均分
from sc a, student b
where a.sno not in 

(select top 2 sno from sc where cno = 1 order by score desc)
  and a.sno not in (select top 2 sno from sc where cno = 2 order by scoredesc)
  and a.sno not in (select top 2 sno from sc where cno = 3 order by scoredesc)
  and a.sno not in (select top 2 sno from sc where cno = 4 order by scoredesc)
  and a.sno = b.sno
group by a.sno

The difference between Oracle and MySQL

In MySQL, one user can create multiple libraries:

In Oracle, the Oracle server is made up of two parts

  • Database Instance (Object)
  • The database is a class that can be seen.

A database instance can have more than one user, and a user has one tablespace by default.

The table space is where our database tables are stored, and there can be multiple files in the table space.

When we use Oracle as our database, we need to specify the user and tablespace to store the data we need!

The last

Now that you’ve read this far, give it a thumbs up! As a reading benefit Xiaobian collated database related interview questions + core knowledge points, the need to click here