Linux Operations (iv)

20 days did not organize the system, previously for the interview for the study, the previous period of time want to change a way to improve the technical strength to learn, but the efficiency is too low. In fact, I lost a lot of things, the peak of the past, want to quickly pick back is not easy. Time Day by day past, is let me feel the only happy thing, do not want to be a student, boring. Also have a feeling of being defeated by reality, really do not know how to do, anyway, I try to think about it, do not degenerate on the line.

1. What is the difference between inner connection and outer connection?

A: (1) Internal connection: Return two tables with the same join condition that corresponds to the same column value row

(2) Outer connection: divided into left outer connection, right outer connection, full outer connection. A table is a base table that matches another base table, if the column values match, if the column values do not match, the row of the first base table is preserved, and the column value that contains the other base table is set to NULL.

(3) The inner connection is to ensure that all rows in two tables meet the join condition, while in an outer join, some columns that do not meet the criteria are displayed, that is, only the rows of one table are restricted, not the rows of the other table.

2, the role of triggers?

A: (1) Definition: A trigger is a special stored procedure that is defined as a statement that executes automatically when a Insert/update/delete trigger event is emitted in a particular table or view.

(2) Role: Enforce constraints, maintain data integrity and consistency, and track operations within the database so that unauthorized updates and changes are not allowed.

# # The integrity of the database is to ensure that changes made to the database by authorized users do not affect the loss of data consistency.

(3) Type: Insteaf-of trigger, after trigger. Represents the meaning before and after a DML statement.

3. What is a stored procedure? Using what to invoke?

A: (1) Definition: A set of pre-compiled SQL statements that perform efficiently and allow for modular design.

(2) Called by exec in SQL Server, using call in MySQL to invoke

4. What is the difference between memory overflow and memory leak?

Answer: (1) memory overflow out of memory: refers to the program in the application of RAM, there is not enough memory space for its use, appear out of the memory; For example, to apply an integer,

But to save it a long to save the number, that is memory overflow.

(2) Memory leak memories leak: Refers to the program after the application of memory, can not free up the requested memory space, a memory leakage hazard may be ignored,

But memory leaks accumulate consequences that are serious, regardless of how much memory, sooner or later, will be occupied by light.

5. What happens when time_wait waits for a timeout? What is the meaning of waiting for 2MSL?

A: There will be a large number of sockets in the Timewait state. If the concurrency of the client continues to be high, some clients will not be connected at this point.

Meaning: (1) The end of the last ACK message segment can reach the passive shutdown of the party (2) so that all the packets in the network disappear, avoid interfering with the next connection

6. How to optimize the SQL statement for writing? (note is written)

A: (1) Select uses a definite field to replace the * number

(2) Use aliases for tables

(3) reasonably add index

(4) Use inner connection instead of subquery

(5) Less use of cursors and temporary tables

7, primary key index and unique index difference? (The primary key index seems to be the primary KEY constraint)

A: (1) There can be only one primary key index for a table, and a unique index may be built multiple.

(2) The primary key cannot be null, and a unique index can have one column null.

(3) A PRIMARY KEY constraint automatically creates a clustered index, and a unique constraint automatically creates a nonclustered index

8. What is a transaction? What is a lock?

A: (1) A transaction is defined as a series of operations that conform to the so-called acid properties performed as a unit.

A transaction is a single unit of work that can contain more than one action statement in a transaction, and if a commit is performed on a transaction, all operations in that thing are committed, and if the transaction encounters an error and is canceled or rolled back, all operations of the transaction are purged and the data reverts to the state before the transaction was executed.

(2) A lock is a mechanism used to prevent other users from modifying resources in a transaction that is not currently completed. The types of locks are: exclusive, shared, update, intent, and so on.

9, what is the view, what is the difference with the table? What is a cursor?

A: The definition of a view: storing a predefined query as an object in a database is called a view.

The difference between a view and a table: (1) The view is a virtual table, it does not hold data, it only stores the definition of the query, and the table holds the actual data

(2) You can modify the data through the view, but there are constraints (where statements), and the table can be modified directly.

(3) The index can be indexed on the view, but there are constraints, and the table can be indexed directly.

Cursor definition: To facilitate access to a separate data row in a result set that defines the collection of data rows returned by the SELECT statement. Cursors include the cursor result set and the cursor position two parts.

10. What are the database objects and what does null mean?

Answer: Tables, views, user-defined functions, stored procedures, triggers. Null represents the unknown unknown, not the so-called (empty string).

11. What can you use to ensure that the fields in the table only accept values in a specific range? What are the completeness?

A: Check constraints, as well as triggers (as reflected by the action of the trigger). Integrity: Entity integrity, domain integrity, custom integrity, referential integrity.

12. What are related subqueries? How do I use these queries?

A: A correlated subquery refers to a query that is re-queried, usually with one query as a condition for another query to use, and the subquery contained in the query will actually request the value of the external query. Embed in, exists statements use

13, why use index can improve query performance 0? Will using index queries improve the performance of queries?

A: The reason for improving query performance: The sequencing of the original records, the equivalent of a multi-level directory tree structure.

Not necessarily. Cause: (1) A full table scan may be faster than using index queries.

(2) For those columns that are defined as text, the image and bit data types are useless using indexes. This is because the amount of data in these columns is either quite large or has very little value.

(3) When the performance of the modification is far greater than the retrieval performance, because of contradiction, using the index is useless.

(4) For columns that have very few data values, using the index is useless.

14. What are the categories of transactions? The overall structure of the database can be divided into?

A: Transaction type: Displays transactions, autocommit transactions, implicit transactions, batch scope transactions.

Structure of the database: network, hierarchy, relational

15. Is there a relationship between several entities in a relational database? What are the types of stored procedures?

A: 1 pairs of more, many to more.

1), the system stored procedure: starts with the sp_, uses in the system the various settings. Obtain information. Related management work, 2), extended stored procedures start with xp_, used to invoke the functionality provided by the operating system 3), user-defined stored procedures, which we refer to as stored procedures

16, there is a personnel system database, personnel in the entry time, to the personnel table inserted data, now requires personnel to enter the position at the same time, said that the personnel registration to the attendance table and payroll, can not be achieved, with what technology to achieve!

A: Yes, using triggers, after people data is inserted into the people table, use the trigger technology to update to the time sheet and payroll.

17. What is a e-r model? What are the main components of the E-R model?

A: The Entity Contact Diagram (E-R) is a well-known method used to describe a conceptual model in the real world. The E-R model is composed of primary entity sets, attributes, and associated methods.

18. SQL Server's character system data types mainly include

Answer: char, varchar, text, nchar, etc.

19. What is the difference between a cookie and a session? (This will be more detailed.)

A: (1) The state information of the session object is saved in the Web server's cache, and the state information of the cookie object is saved on the client's hard disk.

(2) sessions are related to the session, the entire lifetime of the conversation exists, will not be actively discarded, and the cookie is related to the user, for a certain period of time to persist storage, can share data across the browser

(3) The session will not be serialized, the server and the client data transfer, the cookie will be serialized, the server and the client data transmission, such as some websites using cookies to remember my function.

(4) The session has a high level of security, and the cookie has security and state expiration issues, hackers can take advantage of cookie spoofing

20, what is the security of the database? How to ensure the security of the database. (This is not a good answer)

A: Database security refers to the protection of the database to prevent illegal use of the data caused by the disclosure, alteration or destruction.

(1) Reasonable authorization management for the user

(2) database encryption, log audit

(3) Hardware firewall, and intrusion detection system ids/ips

21. What are some common constraints? What do you mean by each other? How do I use it?

Answer: PRIMARY KEY constraints, FOREIGN KEY constraints, check constraints, UNIQUE constraints, DEFAULT constraints

Constraint: Constraint on CREATE TABLE or ALTER TABLE to ensure data integrity and consistency

(1) Primary key: PRIMARY key (primary key field) guarantees entity integrity, a table has only one primary key, but one primary key can have multiple fields, primary key field cannot be empty

(2) Unique: Unique (constraint field) guarantees entity integrity, a table has more than one unique constraint, and a unique constraint can contain multiple fields

(3) FOREIGN key: FOREIGN Key (Foreign key field) REFERENCES primary key table (primary key field) guarantees referential integrity, a table can have multiple foreign keys

(4) Checking: check (check expression) guarantees domain integrity, a table can have multiple check constraints

(5) Default value: default (expression or null for defaults), guaranteed domain integrity, one table can have more than one default value constraint, but one field has only one default value

22. What is a stored procedure and what are his advantages?

A: A set of pre-compiled SQL code stored on the server, which is a way to encapsulate repetitive tasks.

Advantages of stored procedures:

(1) Improve performance: Applications do not have to recompile this process

(2) Reduce network congestion: Client calls directly, do not need to execute a large number of SQL statements

(3) Consistency is better: results are only affected by the transfer parameters

(4) Improved security mechanism: You can authorize users to execute stored procedures.

23. Please list the related components of 4 mail server, and what are their functions?

A: (1) MUA: Mail user agent, which is the client application that forwards mail to the mail server (i.e. MTA).

(2) MTA: Message transfer agents, such as SendMail and postfix, use the SMTP protocol to forward messages between the sending mail server and the receiving mail server.

(3) Mda:delivery mail delivery Agent, that is, the incoming mail server mail, placed in the local account under the Mail box (Mailbox)

(4) Mra:retrieve Mail retrieval agent, using POP3 or IMAP protocol, removed from the mail box to return to the user display.

24, please say how the Mail server works? (The steps are too lengthy to draw the best.)

A: Based on the C/S mode, SMTP 25 port, the POP3 protocol 110 port.

(1) When user A finishes editing an email, The user agent MUA uses the SMTP protocol to send the message to the mail server mail.a.org that manages the local domain, which is resolved by DNS, finds the MX record mail.b.org, and then finds the IP address about mail.b.org according to the a record.

(2) The MTA then uses its own local SMTP protocol for mail forwarding to the destination mail server, the destination mail server discovery is sent to the domain, and then to the MDA delivery Agent to the corresponding user's cylinder, and then the P0P3 server uses the POP3 protocol to read the mailbox mail, Use the MRA Mail Retrieval protocol to give the destination User B.

25, Xiao Ming access to the Internet gateway is 192.168.1.1, access LAN segment 192.168.2.0 gateway is 192.168.1.254, how to operate so that he can access the Internet and access to the company intranet?

Answer: Route add-net 192.168.2.0 netmask 255.255.255.0 GW 192.168.1.254 >>/etc/rc.local

Route add default GW 192.168.1.1 >>/etc/rc.local

26. What do you know about syslog.conf? If loglevel=err, what error logs are found in the log file Error_log?

A: (1) Linux uses syslogd as the log monitoring process, and the configuration file is/etc/rsyslog.conf, where the format definition rule is device. Priority processing Scenarios

(2) The device defines the range of log messages, such as Local0-local7,kern (kernel information), LPR (print Service), mark (Generate timestamp), * represents all functions except mark.

(3) Priority defines the urgency of the message, such as Emerg (not available), alert (warning needs to be modified immediately), Crit, warning, info, notice (important), err

(4) The format of the processing scheme can represent the path of the log file or terminal equipment such as/dev/console, etc.

Error log: Alert warning appears in error log file and Crit emergency if loglevel=err.

27, introduce the ICMP protocol in the computer network? What kinds of messages (this is a big controversy, the book says it says IP protocol, and Shanhui says it is a transport layer protocol)

A: In fact, I have previously felt that the network layer protocol, and then I think Shanhui teacher so authoritative, should be correct is the transport layer protocol. On the other hand, it acts as a gateway to the source host error protocol, because the network layer is not reliable, so the ICMP protocol needs, although aware of possible transmission errors, but can not solve the real problem. The ICMP message is encapsulated in an IP packet that has the type, the code (that is, the type value), and the data part.

Type: (1) Endpoint unreachable (2) Time Exceeded (3) parameter Problem (4) Source point suppression (5) redirection (6) Echo request and Echuo reply

28. Please describe briefly the IPV4 format of the data report. And talk about your understanding of the IPV6 (ah, so memorized things incredibly carker, and then the sensitive mind do not review it! ）

A: The IP header is 20 bytes without padding option, the message format has version number, header length, priority (i.e. service type), entire datagram length, protocol field, source IP address, destination IP address, and some Shard flags, TTL and header checksum.

IPV6: The first fixed 128-bit, greatly solves the problem of address scarcity, there is no ARP protocol, because there is no broadcast, integrated mobility, global accessibility and flexibility, to achieve stateless automatic configuration, users can access the Internet on a row, simple baotou reduce processing overhead caused by the delay, There is also no checksum, for some dynamic routing protocols are also developed, such as RIPng, OSPFv3, DHCPv6

29, the difference between drop,delete and truncate?

A: (1) Drop directly delete the table structure; truncate clears the data in the table, and when it is inserted again, the ID starts from 1, and the delete deletes the data in the table, and it can add the WHERE clause

Specifies to delete some rows;

(2) Truncate is equivalent to delete without where, but truncate can only delete the table, and delete can delete both the view and the table;

(3) from the efficiency of execution,drop>truncate>delete;

(4) When the table is truncate, the space occupied by the table and index reverts to the initial size, and the delete operation does not reduce the space occupied by the table or index. The drop statement frees all the space occupied by the table.

30, SQL statement optimization. (It's a bit different from the previous issue)

A: (1) Try to avoid using the non-equal (! = or <>) operator in the WHERE clause, or discard the engine for full table scanning using the index.

(2) You should try to avoid null values for the field in the WHERE clause, otherwise it will cause the engine to abandon using the index for a full table scan, such as:

The select ID from the where num is null can set the default value of 0 on NUM, make sure that the NUM column in the table does not have a null value, and then query: Select ID from t where num=0

(3) It is a good choice to use exists instead of in.

(4) Replace the HAVING clause with a WHERE clause because the having will only filter the result set after retrieving all records

Linux Operations (iv)