PHP programmer interview questions (classic summary, mainly mysql)

mysql_query("BEGIN");
 mysql_query("INSERT INTO customerinfo (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, and through some calculations, use an UPDATE statement to update the new values ​​into the table. A LOCK TABLE statement containing the WRITE keyword ensures that the UNLOCK TABLES command is There will be no other access to insert, update, or delete the inventory

mysql_query("LOCK TABLE customerinfo READ, orderinfo WRITE"); mysql_query("SELECT customerid FROM `customerinfo` where id=".$id); mysql_query("UPDATE `orderinfo` SET ordertitle='$title' where customerid=".$id); mysql_query("UNLOCK TABLES");

6), use foreign keys to optimize lock tables a. Map the customerid in customerinfo to the customerid in orderinfo, Any record without a valid customerid 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', this parameter ensures that when a record in the customerinfo table is deleted, the order will also be deleted. All records of this user in the table, please note that when using foreign keys, you must define the transaction security type as INNODB;

7), create index: a.Format: (Normal index)-> Create:CREATE INDEX ON tablename (index field) Modify: ALTER TABLE tablename ADD INDEX [index name] (index field) Create a specified index for a table: CREATE TABLE tablename([...],INDEX[index name](index field)) (unique index)-> Create:CREATE UNIQUE ON tablename (index field) Modify: ALTER TABLE tablename ADD UNIQUE [index name] (index field) Create a table and specify an index: CREATE TABLE tablename([...],UNIQUE[index name](index field)) (primary key)-> It is a unique index, usually created when creating a table. The format is: CREATA TABLE tablename ([...],PRIMARY KEY[index field])

8), optimize query statements a. It is best to perform comparison operations on the same fields and minimize function operations on established index fields. Example 1:

SELECT * FROM order WHERE YEAR(orderDate) Example 2: SELECT * FROM order WHERE addtime/7 Example 3: SELECT * FROM order WHERE title like "%good%"; SELECT * FROM order WHERE title>="good" and name6. What is the function of MYSQL to obtain the current time?, and the function of formatting date is (2 points) Answer:now(),dateformat()

This PHP interview question focuses on mysql performance optimization. It looks like recruiting a DBA of mysql database who is familiar with PHP programming, ha~~