Database Frequently meet test questions (developers)

What is a stored procedure? What are the pros and cons?

What is a stored procedure? What are the pros and cons?

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

Advantages of stored procedures:

  • Being able to encapsulate code
  • Save in the database
  • Let the programming language do the calling
  • A stored procedure is a precompiled code block with high execution efficiency
  • Replacing a large number of T_SQL statements with a single stored procedure can reduce network traffic and increase the communication rate

Disadvantages of stored procedures:

  • The stored procedure syntax is almost different from database to database and is very difficult to maintain (not universal)
  • Business logic is on a database and difficult to iterate over

What are the three paradigms

What are the three paradigms

First Normal form (1NF) : The fields in a database table are single-attribute and non-divisible. This single attribute is made up of basic types, including integer, real, character, logical, date, and so on. Second normal form (2NF) : there is no partial functional dependence of non-key fields on any candidate key field in the database table (partial functional dependence refers to the presence of some fields in the combination of keywords to determine the non-key field), that is, all non-key fields are completely dependent on any set of candidate keywords. 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 dependence of non-key fields on any candidate key fields. By transfer function dependence, we mean that the transfer function C depends on A if there is A deterministic relationship “A → B → C”. Therefore, a database table that satisfies the third normal form should not have the following dependencies: key field → non-key field X → non-key field Y

It was written in a way that we certainly could not understand and did not want to read. So let me summarize:

  • First of all, it should be made clear that those who satisfy the third normal form must satisfy the second normal form, and those who satisfy the second normal form must satisfy the first normal form
  • First normal form:A field is the smallest unit that cannot be subdivided
    • Student information forms the student information form, which is composed of age, sex, student number and other information. None of these fields is divisible, so it satisfies the first normal form
  • Second normal form: satisfies the first normal form,The fields in the table must depend entirely on all primary keys, not some.
    • The other columns represent the same thing as the primary key, and the primary key is unique. They only need to depend on the primary key, and they are unique
    • Student number is 1024, name is Java3y, age is 22 years old. The name and age fields depend on the student number primary key.
  • Third normal form: satisfies the second normal form,All fields other than the primary key must be independent of each other
    • When data is stored in only one place and does not appear repeatedly in multiple tables, you can think of it as eliminating the passing dependency
    • For example, our university has many departments (Chinese department, English Department, Computer Department…) The information in the department management table consists of the following fields: Department number, Department head, Department profile, and department structure. Can we add the department number, department head, department profile and department structure fields in the student information table? No, because it would be redundant. Fields other than the primary key would form dependencies. Instead, add only one department number field to the student table.

Reference links:

  • www.zhihu.com/question/24…
  • www.cnblogs.com/CareySon/ar…

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

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

A view is a virtual table based on a data table

  • (1) A view is a virtual table
  • (2) Views are built on existing tables, which are called base tables
  • (3) The statement that provides the data content to the view is a SELECT statement, and the view can be understood as a stored SELECT statement
  • (4) Views provide users with another form of representation of base table data
  • (5) Views do not store real data, real data is still stored in the base table
  • (6) Although the programmer is operating on the view, but the final view will also be transformed into the operation base table
  • (7) A base table can have zero or more views

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

It is not reasonable to show them all the fields.

We should give them the data they want to see… On the one hand, we can let them only focus on their own data, on the other hand, we can ensure that some confidential data in the data sheet will not be leaked…

When querying data, we often need to write very long SQL statements, almost every time to write very long…. As mentioned above, a view is a query based virtual table, that is, a view can encapsulate the queried data… Then we will become very convenient when using…

It’s important to note that using views allows us to focus on the logic without improving 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) Cannot be rolled back
  • 3) No where
  • 4) Delete table content and structure
  • 5) Fast deletion speed

truncate table

  • 1) belongs to the DDL
  • 2) Cannot be rolled back
  • 3) No where
  • 4) Delete table contents
  • 5) Fast deletion speed

delete from

  • 1) belongs to the DML

  • 2) can be rolled back

  • 3) can take the where

  • 4) Table structure is in, table content depends on where execution

  • 5) The deletion speed is slow and needs to be deleted line by line

  • Drop is used when a table is no longer needed

  • When you want to delete part of a row, use delete with a WHERE clause

  • Truncate is used when all data is deleted from a reserved table

What is the index? What are the functions and advantages and disadvantages?

What is the index? What are the functions and advantages and disadvantages?

What is an Index?

  • (1) is a fast query table content mechanism, similar to the Xinhua dictionary catalog
  • (2) Apply to some fields in a table, but when stored, independent of the table

The index table makes the data ordered….

Quickly locate data files in the hard disk…

The rowid characteristics

The characteristics of the rowid

  • (1) is located in each table, but is ostensibly invisible, for example: desc EMp is invisible
  • (2) Select * from select where rowid is displayed
  • (3) It is bound to each table, the table dies, the table rowid dies, two tables rownum can be the same, but the rowid must be unique
  • (4) RoWID is an 18-bit mixture of case and number, which uniquely represents the location of this entry in the DBF file
  • (5) When rowid is used in =/like comparison, the value of rowid is enclosed in single quotation marks and case sensitive
  • (6) RoWID is the bridge between the contact table and the DBF file

The index characteristics

Characteristics of indexes

  • (1) Once an index is created,** Oracle Management automatically maintains it ** and Oracle Management decides when to use the index
  • (2) The user does not need to specify which index to use in the query statement
  • (3) When the primary key or unique constraint is defined, the system automatically creates an index on the corresponding column
  • (4) Users can also add indexes to a single field or multiple fields according to their needs

If you specify a primary key or unique constraint, you will automatically create an index on the corresponding column.

When to create indexes

  • (1) SELECT operations are often performed on tables
  • (2) The table is very large (there are too many records), and the content of records is widely distributed
  • (3) Column names often appear in WHERE clauses or join conditions

When to [not] create indexes

  • (1) Tables often perform INSERT/UPDATE/DELETE operations
  • (2) Very small table (too few records)
  • (3) Column names do not often appear as join conditions or in WHERE clauses

Index pros and cons:

  • Indexing speeds up database retrieval
  • Indexes slow down maintenance tasks such as inserts, deletes, and changes (although indexes can speed up queries, they also degrade the performance of database system updates, since most data updates require indexes to be updated at the same time).
  • The unique index can ensure the uniqueness of each row of data. By using the index, the optimization hider can be used in the query process to improve the performance of the system
  • Indexes require physical and data space

Index classification:

  • Unique index: A unique index does not allow two rows to have the same index value
  • Primary key indexes: Defining a primary key for a table automatically creates primary key indexes, which are special types of unique indexes. Primary key indexes require that each value in the primary key be unique and cannot be empty
  • Clustered indexes: The physical order of rows in a table is the same as the logical order of key values. Each table can have only one Clustered index
  • Non-clustered indexes: Clustered indexes specify the logical order of tables. The data is stored in one location, and the index is stored in another location. The index contains a pointer to the data store location. There could be more than one, less than 249

For an in-depth understanding of the index, refer to:

  • kb.cnblogs.com/page/45712/
  • www.cnblogs.com/drizzlewith…

What is a transaction?

What is a transaction?

Transactions are simple: all operations performed in a Session either succeed or fail simultaneously

ACID – The four basic elements of proper database transaction execution

  • They include: Atomicity, Consistency, Isolation, Durability.

A database system that supports transactions must have these four features, otherwise the correctness of data cannot be guaranteed in Transaction processing, and the Transaction process is likely to fall short of the Transaction.

For example :A transfers money to B. If there is A problem with the transfer process, the transaction can restore the data to the original [A’s money has not changed, B’s money has not changed].

Examples:



		/* * Let's simulate the scenario where A transfers RMB to B * * A and B both have RMB 1000, now I ask A to transfer RMB 500 to B * * */
            //JDBC closes transactions by default. What's wrong with closing transactions to operate a transfer

            //A account minus 500 yuan
            String sql = "UPDATE a SET money=money-500 ";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.executeUpdate();

            // Account B has an excess of 500 yuan
            String sql2 = "UPDATE b SET money=money+500";
            preparedStatement = connection.prepareStatement(sql2);
            preparedStatement.executeUpdate();

Copy the code

From above, we can actually see that the transfer from A to B was successful. But ** what if something goes wrong in the process of transferring money from A to B? ** Let’s simulate it


			//A account minus 500 yuan
            String sql = "UPDATE a SET money=money-500 ";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.executeUpdate();
			
			// There is a problem with the simulation
            int a = 3 / 0;


            String sql2 = "UPDATE b SET money=money+500";
            preparedStatement = connection.prepareStatement(sql2);
            preparedStatement.executeUpdate();

Copy the code

Obviously, the above code will throw an exception, so let’s query the data again. There’s 500 dollars missing in account A, and there’s no increase in account B. This is clearly unreasonable.

We can solve the above problems through transactions


			// When the transaction is started, the operation on the data does not take effect immediately.
            connection.setAutoCommit(false);
            
            //A account minus 500 yuan
            String sql = "UPDATE a SET money=money-500 ";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.executeUpdate();

            // There is a problem in the transfer process
            int a = 3 / 0;

            // Account B is 500 yuan more
            String sql2 = "UPDATE b SET money=money+500";
            preparedStatement = connection.prepareStatement(sql2);
            preparedStatement.executeUpdate();
            
            // If the program makes it this far and no exceptions are thrown, we commit the data
            connection.commit();

			// Close transaction [auto-commit]
			connection.setAutoCommit(true);
            

        } catch (SQLException e) {
            try {
                // If there is an exception, it will come in here and we will roll back the transaction.
                connection.rollback();
                
                // Close transaction [auto-commit]
                connection.setAutoCommit(true);
            } catch (SQLException e1) {
                e1.printStackTrace();
            }

Copy the code

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 is automatically rolled back, but if the program catches an exception, the transaction needs to be explicitly rolled back in the catch.

Transaction Isolation level

The database defines four isolation levels:

  1. Serializable [can avoid dirty read, cannot repeat read, virtual read]
  2. Repeatable read【 Avoid dirty read, not Repeatable read 】
  3. Read committed【 Can avoid dirty Read 】
  4. Read uncommitted

Each corresponds to the 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 hasn’t committed the transaction yet. B reads the data and finds that he has more money in his account! B says to A, I have received the money. Rollback rollback rollback rollback rollback rollback rollback rollback rollback rollback rollback rollback

Non-repeatable reads: One transaction reads data that has already been committed by another transaction, meaning that one transaction can see changes made by other transactions

Note: A queries the database to get the data, and B revises the database data, resulting in the results of A’s queries to the database are different [harm: each query result of A is affected by B, so the information queried by A is meaningless]

Virtual read: Indicates that data inserted by another transaction is read in one transaction, resulting in inconsistent reads.

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

Simple summary: dirty read is not tolerated, can not repeat read and virtual read in certain circumstances is ok [do statistics certainly not].

What are optimistic and pessimistic database locks?

What are optimistic and pessimistic database locks?

To ensure that the isolation and unity of transaction and the unity of database are not destroyed when multiple transactions access the same data in the database at the same time, optimistic lock and pessimistic lock are the main technical means of concurrency control.

  • Pessimistic locking: Assumes that concurrency conflicts will occur and shields all operations that might violate data integrity
    • The transaction is locked when the data is queried until the transaction is committed
    • Implementation: Use the locking mechanism in the database
  • Optimistic locking: Assuming no concurrency conflicts occur, only data integrity violations are checked when an operation is committed.
    • The transaction is locked when the data is modified. This is done through the version method
    • Implementation: Use version version or timestamp

Pessimistic locks:

Optimistic locking:

References:

  • www.open-open.com/lib/view/op…

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

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

  • Superkey: The set of attributes that uniquely identifies a tuple in a relationship is called the superkey of the relational schema. A single property can act as a superkey, or a combination of properties can act as a superkey. Superkeys contain both candidate and primary keys.
  • Candidate key (candidate code) : is the minimum superkey, that is, the one with no redundant elements.
  • Primary key (primary code) : A combination of data columns or attributes in a database table that uniquely and completely identifies the stored data object. A data column can have only one primary key, and the value of the primary key cannot be missing, that is, it cannot be Null.
  • Foreign key: The primary key of another table that exists in a table is called the foreign key of that table.

Candidate code and primary code:

Example: Mailing address (city name, street name, ZIP code, unit 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 property that uniquely identifies the entity, then {city name, street name} is the primary code (primary key).

What are the types of SQL constraints?

What are the types of SQL constraints?

  • NOT NULL: The content of the control field must NOT be NULL.
  • UNIQUE: Control field content cannot be repeated, a table can have more than one UNIQUE constraint.
  • PRIMARY KEY: also used for control field content cannot be repeated, but it is allowed in a table only one.
  • FOREIGN KEY: An action used to prevent breaking joins between tables. 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 value range of fields.

In which state does the database run to prevent data loss?

In which state does the database run to prevent data loss?

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

Mysql Storage Engine

Mysql has the following storage engines:

My version is 5.7.15 and I use Innodb version by default!

Common storage engines are as follows:

  • 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.
  • MyIASM, the default engine for Mysql, does not support transactions, nor does it support row-level locking and foreign keys.
  • MEMORY engine: All data is stored in MEMORY. Data processing is fast but insecure.

Tables from multiple storage engines can also be used in the same database. If a transaction requires a high level of table modification, InnoDB can be used. In this database, you can select MyISAM to store tables with high query requirements. If the database needs a temporary table for queries, the MEMORY storage engine can be selected.

References:

  • www.cnblogs.com/xiaohaillon…
  • Blog.csdn.net/ls5718/arti…
  • Blog.csdn.net/t146lla128x…

What is the index data structure used by MyIASM and Innodb?

What is the index data structure used by MyIASM and Innodb?

Answer: B+ trees!

MyIASM engine, B+ tree data structure stored in the content is actually the actual data address value. That is, its 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 non-clustered index.

Innodb’s index structure is also a B+ tree, but the data structure stores the actual data. This type of index is called clustered index.

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 > select * from table where permission is specified

Mysql > select * from table where permission is specified

The MySQL server controls users’ access to the database through the permission table, which is stored in the MySQL database and initialized by the mysql_install_DB script. These permission tables are user, DB, table_priv, columns_priv, and host. Here are the structures and contents of these tables:

  • User permission table: records the user account information that is allowed to connect to the server. The permissions in this table are global.
  • Db permission table: records the operation rights of each account on each database.
  • Table_priv Permission table: records table-level operation permissions.
  • Columns_priv Permission table: records the operation permissions at the column level.
  • Host permission table: Works 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 ways to fix data table corruption?

What are the ways to fix data table corruption?

Use Myisamchk to fix the problem.

  • 1) Stop mysql service before repair.
  • 2) Open the command line, and then go to the mysql /bin directory.
  • 3) Run myisamchk -recover database path /*.myi

With the repair table or OPTIMIZE table command, repair table table_name repair table is used to repair broken tables. The OPTIMIZE TABLE command is used to recycle idle database space. When the rows on the TABLE are deleted, the disk space is not reclaimed immediately. After the command is used, the space is reclaimed and the rows on disk are rearranged (note: on disk, not the database).

InnoDB engine row locking in MySQL is done by adding on what

InnoDB engine row locking in MySQL is done by adding on what

InnoDB does row locking based on the index

For example: select * from tab_with_index where id = 1 for update;

For update can do row locking based on the condition that the ID is a column with an index key,

If the ID is not the index key InnoDB will complete the table lock and concurrency will be eliminated

Database optimization ideas

SQL optimization

When we write SQL statements, in fact, the order of writing, the policy 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 write 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 table written last in the FROM clause being 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
  • In other words: select the table with the fewest entries and place it last

If there are more than 3 table join queries:

  • If the three tables are related, place the table with the most references at the end, and so on.
  • That is: tables referenced by other tables go last

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

The EMP table has the most references and the most records, so it is placed at the end of the form sentence


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)  		


Copy the code

② Join order in the WHERE clause

The database parses WHERE clauses 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 and is written to the far right of the WHERE sentence


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

③ Avoid using asterisks in the SELECT clause

When we learned at the time, “*” can be used to obtain all the data in the table.

  • But it’s done by querying a data dictionary, which means it takes more time
  • SQL statements written with asterisks are also not intuitive.

④ Replace DELETE with TRUNCATE

Delete all records from the table except for the table structure.

DELETE deletes one record at a time, while Truncate deletes the entire table and preserves the table structure, which is faster than DELETE

⑤ Use more internal functions to improve SQL efficiency

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

⑥ Use an alias for the table or column

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

⑦ Use commit more

Comiit will release rollback points…

⑧ Use indexes

Indexes are used to improve query data. When the number of records in the table is very large, we can use indexes.

Pet-name ruby to write SQL capitals

When we write SQL, it is officially recommended to write keywords in uppercase, because the Oracle server always converts lowercase letters to uppercase before executing

⑩ Avoid using NOT on index columns

When the Oracle server encounters a NOT, it stops working and performs a full table scan instead

① Avoid using computations on index columns

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

(1) (2) with> =alternative>

Inefficient:SELECT * FROM EMP WHERE DEPTNO > 3First, go to DEPTNO=3And the first DEPT is greater than3Record efficiently:SELECT * FROM EMP WHERE DEPTNO >= 4So let's go to the first DEPT is equal to4The record ofCopy the code

①③ IN place of OR


      select * from emp where sal = 1500 or sal = 3000 or sal = 800;
      select * from emp where sal in (1500.3000.800);

Copy the code

①④ Always use the first column of the index

If an index is built on more than one column, the optimizer will only choose to use it 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 referenced, the optimizer uses a full table scan and ignores the index


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

Database structure optimization

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

Server hardware Optimization

It costs a lot of money!

SQL exercises

The following exercises are referenced from the public account Java bosom friend:

  • Mp.weixin.qq.com/s?__biz=MzI…
  • Mp.weixin.qq.com/s?__biz=MzI…

Basic table structure:

Student (sNO,sname, Sage,ssex) Course (CNO, CName, TNO) Sc (SNO, CNo, SCORE) Teacher (TNO, TNameCopy the code

Topic:

Select * from student whose grade is higher in course 1 than in course 2select 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, query the average score is greater than60Student number and grade point averageselect a.sno as "Student id".avg(a.score) as Grade point average 
from
(select sno,score from sc) a 
group by sno having avg(a.score)>60



103To query all students' student id, name, number of courses selected, total gradeselect a.sno asStudent id, b.s nameasName,count(a.cno) asNumber of elective courses,sum(a.score) asTotal gradefrom sc a, student b
where a.sno = b.sno
group byA. no, B. name or: selectstudent.snoasStudent id, student. SnameasName,count(sc.cno) asNumber of elective courses,sum(score) asTotal gradefrom student left Outer join sc on student.sno = sc.sno
group by student.sno, sname

104Selectcount (selectcount(selectcount(distinct(tname)) from teacher where tname like Select count(distinct(tname)) as' name 'from teacher where tname like' SELECT * from teacher where tname like'Zhang %Group by tname 105, Have not learned "zhang SAN" query teacher class student. The student number, name of the select sno, student, sname from student where sno not in (select distinct from (sc) sno) sc,course,teacher where sc.cno=course.cno and teacher.tno=course.tno and teacher.tname='Zhang SAN'), 106, Select sno, sno, sno, sno, sno, sno, 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) Or:  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.no = B.no and A.no = c.no  select student.sno,student.sname from student,sc where student.sno=sc.sno and sc.cno=1 and exists( select * from sc as Sno = sno and sc_2.cno=2) 107, Select a.sno, a.sname from student A select a.sno, a.sname from student A select a.sno, a.sname from student A 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 = 'Li si') or:  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 = 'Li si') e where a.no = b.no and b.no = e.cno 108, Select * from sc where score = 1; select * from sc where score = 1; select * from sc where score = 1; (select sno, score from sc where cno = 2) c WHERE b.core > C. core and B.no = C. no and A.no = B.no 109, Select sno, sno from sc where score > 60 select sno, sno from sc where score > 60 select sno, sno from sc where score > 60 In 110, Select * from student a select * from student a select * from student a select * from student a select * from student a select * from student a select * from student a Sc b where a.no <> 1 and A.no = b.no and B.no in (select a.no from SC where a.no = 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 Update SC set score = (select avg(sc_2.score) from SC sc_2 where r1.sno= S.sno 111 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='CathySelect student id from student 2 where student id is the same as student 2 where student id is the same as student 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. name having sum(cno) = (select sum(cno) from SC WHERE sno = 2) teacher where course.cno = sc.cno and course.tno = teacher.tno and tname = 'CathyInsert some records into the sc table that meet the following conditions: Insert sc select sno, 3, sno, 3, sno, 3 (select avg(score) from sc where cno = 2) from student where sno not in (select sno from sc where cno = 3) 115. The following statistical report shows all students from high to low by average score: Select sNO as SNO, Max (case when Cno = 1) then Score end) as ENTERPRISE management, Max (case when cno = 1 2 then score end) AS Marx, Max (case when Cno = 3 then Score end) AS UML, Max (case when Cno = 4 then Score end) AS database , Max (case when Cno = 5 then score end) AS Physics,count(CNO) AS course number, AVG (Score) AS average score FROM SC GROUP by Sno ORDER by Avg (score) DESC 116, avG (score) DESC 116, avG (score) DESC 116, AVG (score) DESC Select * from SC group by cno select * from SC group by cno select * from SC group by cno select * from SC groupCourse no.'
,MAX(score) as 'The highest'
,MIN(score) as 'The lowest pointsSELECT * from SC,course where sc. Cno =course. Cno group by course. Cno 117 SELECT * from SC,course where sc. Isnull (AVG(score),0) 100 * SUM(CASE WHEN isnull(score,0)>=60 THEN 1 ELSE 0 END)/count(1) FROM SC t, Select * from course where t.cno = course. Cno GROUP BY T.cno ORDER BY select * from course where t.cno = course. Business Management (001), Marx (002), UML (003), Select avG (case when Cno = 1 then Score end) as average score 1, AVG (case when Cno = 2 then Score end) as average score 2, avG (case when Cno = 2 then Score end) as average score 2, Avg (case when CNO = 3 then score end) as average score 3, AVG (case when CNO = 4 then score end) as average score 4, 100 * sum(casewhen Cno = 1 AND score > 60 THEN 1 else 0 end)/sum(casewhen Cno = 1 THEN 1 else 0 end) Sum (casewhen Cno = 2 AND score > 60 THEN 1 else 0 end)/sum(casewhen Cno = 2 THEN 1 else 0 end) Sum (casewhen Cno = 3 AND score > 60 THEN 1 else 0 end)/sum(casewhen Cno = 3 THEN 1 else 0 end) 100 * sum(casewhen Cno = 4 AND SCORE > 60 THEN 1 else 0 end)/sum(casewhen Cno = 4 THEN 1 else 0 end) as pass rate Select * from SC a, course b select * from SC A, course b select * from SC A, course b select * from SC A, course b select * from SC A, course B select * from SC A Where a. no = c. no and B. no = C. no group by A. no order by AVERAGE score desc or: select A.name as'Teachers'',r.rname as 'course' , AVG(score) as 'average' 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, sc.cno, R.tname order by AVG(Score) desc 120, sc.cno, R.tname order by AVG(Score) desc 120, Select top 6 Max (a.no), Max (B.name), Max (B.name), Max (A.no), Max (B.name), Max (case when Cno = 1 then score end) as enterprise management, Max (case when Cno = 2 then score end) as Max, Max (case when Cno = 3 then Score end) as UML, Max (case when Cno = 4 then Score end) as database Avg (score) as average 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.snoCopy the code

Differences between Oracle and Mysql

In Mysql, you can create multiple libraries under one user:

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

  • Database instance
  • Database [understood as class]

A database instance can have multiple users, and each user has one tablespace by default.

A table space is where our database tables are stored. There can be multiple files in a table space.

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

The last

References:

  • Blog.csdn.net/xlgen157387…
  • Blog.csdn.net/beauty_1991…
  • zhuanlan.zhihu.com/p/23713529
  • Blog.csdn.net/wickedvalle…
  • Blog.csdn.net/zhugewendu/…

If the article is wrong, welcome to correct, we communicate with each other. Used to read technical articles in wechat, want to get more Java resources, students can follow the wechat public number :Java3y