Php interview questions-Database Technical Questions

Database Technical Questions


In a content management system, the table message has the following fields:
Id Article id
Title article title
Content
Category_id document category id
Hits clicks
Create the table above and write MySQL statements
No answer


2. Similarly, the preceding Content Management System: The table comment records the user's reply content. The fields are as follows:
Comment_id reply id
Id Document id, associated with the id in the message table
Comment_content reply content
To query the database, you need to obtain a list of article titles in the following format and sort them by the number of replies.
Article id: number of replies in the document title
Use an SQL statement to complete the above query. If the article does not reply, the number of replies is displayed as 0.
No answer


3. In the content management system described above, the table category stores the category information. The fields are as follows:
Category_id int (4) not null auto_increment;
Categroy_name varchar (40) not null;
When you enter an article, select an article category from the drop-down menu.
Write down how to implement this drop-down menu
No answer


Use the following table to write the SQL statement for the top 10 posts:
Members (id, username, posts, pass, email)


What is the difference between mysql_fetch_row () and mysql_fetch_array?
The former extracts an array from the result set as an enumeration;
The latter extracts an array from the result set as an associated array or number array.


What is the function used to retrieve the total number of query result sets?
Mysql_num_rows ($ result );


The storage engine of Mysql, the difference between myisam and innodb.
InnoDB and MyISAM are the two most common table types used by many people when using MySQL. The two table types have their own advantages and disadvantages, depending on the specific application.
The basic difference is:
The MyISAM type does not support advanced processing such as transaction processing, whereas the InnoDB type does.
MyISAM tables emphasize performance, and the execution speed is faster than that of InnoDB, but transactions are not supported. InnoDB provides advanced database functions such as external keys for transactions.
Details: http://www.php100.com/html/webkaifa/database/Mysql/2011/0326/7789.html


Write the names of more than three MYSQL database storage engines (Note: Case Insensitive)
MyISAM, InnoDB, DBD (Berkeley DB), Merge, Memory (Heap), Example, Federated, Archive, CSV, Blackhole, MaxDB, etc.


Name the three or more open source databases you know (Note: Think about the popular open source databases in foreign countries)
MySQL, SQLite, BDB (Berkeley DB), PostgreSQL, Firebird


What are the main differences between the field types varchar and char in the MYSQL database? Which field is highly efficient to search? Why?
Varchar is variable-length, saving storage space; char is fixed-length. Fast query efficiency, char type,
Because varchar is not a fixed length, you must first search for the length and then extract the data. This is an additional step than the char fixed length type.
The value saved by varchar is a variable length, and char is a fixed length. If the saved value is less than the defined length, the SQL statement will append spaces to the end until the field length you define.


The two major differences between MySQL 4.1 and MySQL are described. If you have used MYSQL5, tell me the main differences between MYSQL5 and 4.
4.1-4.0 mainly supports subqueries and character encoding;
5. More enhanced functions than 4, including stored procedure view and transactions.


What are the three basic optimization rules for MYSQL databases? (Tip: service configuration, application, and development considerations)
(1) System Service optimization: Increase the capacity of Mysql's key_buffer and cache_buffer | "query_cache;
(2) Add an appropriate index to all frequently queried fields;
(3) Optimize SQL statements to reduce operations on ditinct, group, join, and other statements.


Write the SQL statement for the top 10 people with the most posts. Use the following table: members (id, username, posts, pass, email) (2 points)
A: SELECT * FROM 'members 'order by posts DESC limit;


Write the code in php as required.
Table Name user
ID name tel content date
1 Zhang San 13333663366 graduated from college-
3 Zhang Si 020-5566556 graduated from technical secondary school 2006-10-15
4 Wang Wu 13521212125 graduated from college-12-25
2 ......
6 ......
Assume that the database connection is as follows:
$ Mysql_db = mysql_connect ('local', 'root', 'pass ');
@ Mysql_select_db ('db', $ mysql_db );
(1) query and output all records whose names are equal to 'zhang san;
<? Php
$ SQL = 'select * from user where name = "John "';
$ Result = mysql_query ($ SQL );
While ($ row = mysql_fetch_array ($ result, MYSQL_ASSOC )){
Echo $ row ['name'];
}
?>


(2) Querying by ID in ascending order means returning the first three records sorted and outputting them.
<? Php
$ SQL = 'select * from user order by ID asc limit 0, 3 ';
$ Result = mysql_query ($ SQL );
While ($ row = mysql_fetch_array ($ result, MYSQL_ASSOC )){
Print_r ($ row );
}
?>


What is the transaction in the database?
A transaction is a group of sequential database operations as a unit. If all operations in the group are successful, the transaction is considered successful. Even if only one operation fails, the transaction fails.
If all operations are completed, the transaction is committed, and the modifications are applied to all other database processes. If an operation fails, the transaction will be rolled back and all operations affected by the transaction will be canceled.


How to optimize the MYSQL database. (4 points, more writes)
A:
1. select the most suitable field attribute, minimize the length of the defined field, and set the field not null as much as possible, such as 'province, gender'. It is best to set it to ENUM.
2. Use JOIN instead of subquery:
A. DELETE no order Customer: delete from customerinfo WHERE customerid NOT in (SELECT customerid FROM orderinfo)
B. Extract all customers without orders: SELECT FROM customerinfo WHERE customerid NOT in (SELECT customerid FROM orderinfo)
C. Speed up B optimization: SELECT FROM customerinfo LEFT JOIN orderid customerinfo. customerid = orderinfo. customerid
WHERE orderinfo. customerid IS NULL
3. Use UNION instead of creating a temporary table manually
A. Create a temporary table: SELECT name FROM 'nametest 'union select username FROM 'nametest2'
4. Transaction processing:
A. ensure data integrity. For example, if both the ADD and modify operations are performed, both operations fail.
Mysql_query ("BEGIN ");
Mysql_query ("insert into mermerinfo (name) VALUES ('$ name1 ')";
Mysql_query ("SELECT * FROM 'orderinfo' where customerid =". $ id ");
Mysql_query ("COMMIT ");
5. Lock the table and optimize transaction processing:
A. We use a SELECT statement to retrieve the initial data. Through some calculations, we use the UPDATE statement to UPDATE the new value to the table.
The lock table statement containing the WRITE keyword can ensure that before the unlock tables command is executed,
There will be no other access to insert, update, or delete inventory
Mysql_query ("lock table customerinfo READ, orderinfo WRITE ");
Mysql_query ("SELECT customerid FROM 'mermerinfo' where id =". $ id );
Mysql_query ("UPDATE 'orderinfo' SET ordertitle = '$ title' where mermerid =". $ id );
Mysql_query ("unlock tables ");
6. Use foreign keys to optimize table locking
A. Map customerid in customerinfo to customerid in orderinfo,
Any record without a valid mermerid will not be written to orderinfo
Create table customerinfo
(
Customerid int not null,
Primary key (customerid)
) TYPE = INNODB;
Create table orderinfo
(
Orderid int not null,
Customerid int not null,
Primary key (customerid, orderid ),
Foreign key (customerid) REFERENCES customerinfo
(Customerid) ON DELETE CASCADE
) TYPE = INNODB;
Note: 'On delete cascade 'ensures that order is also deleted when a record in the customerinfo table is deleted.
All records of the user in the Table. Note that the transaction security type should be defined as INNODB using the foreign key;
7. Create an index:
A. format:
(Common Index)->
CREATE: create index <INDEX Name> ON tablename (INDEX field)
Modify: alter table tablename add index [INDEX name] (INDEX field)
Create table specified INDEX: create table tablename ([...], INDEX [INDEX name] (INDEX field ))
(Unique index)->
CREATE: create unique <index Name> ON tablename (index field)
Modify: alter table tablename add unique [index name] (index field)
Create table specified index: create table tablename ([...], UNIQUE [index name] (index field ))
(Primary Key)->
It is a unique index. Generally, a table is created in the format:
Creata table tablename ([...], primary key [index field])
8. Optimize Query statements
A. It is best to compare the same fields and minimize the number of function operations on the created index fields.
Example 1:
SELECT * FROM order where year (orderDate) <2008; (slow)
SELECT * FROM order WHERE orderDate <"2008-01-01"; (FAST)
Example 2:
SELECT * FROM order WHERE addtime/7 <24; (slow)
SELECT * FROM order WHERE addtime <24*7; (FAST)
Example 3:
SELECT * FROM order WHERE title like "% good % ";
SELECT * FROM order WHERE title> = "good" and name <"good ";