MySql and mysql

MySql and mysql

MySql exam

------ I just made a Mysql exam two days ago and shared it with you ------------------

Part 1: Single-choice questions (40 points in total)

1. The unique index keyword in mysql is (C)

A. fulltext index B. only index C. unique index D. index

2. One of the following errors in the index description is (C)

A. indexing improves data query speed B. indexing reduces data insertion speed

C. innodb Storage engine supports full-text index D. The command for deleting indexes is drop index.

3. The storage engine for indexes and transactions is (B)

A. myisam B. INNODB C. MEMORY D. CHARACTER

4. Which of the following statements about transactions is false? (C)

A. transactions are atomic. B. transactions are isolated.

C. Transaction rollback use the commit command D. Transaction Reliability

5. In mysql, the command for backing up the database is ()

A. mysqldump B. mysql C. backup D. copy

6. The command for batch data import is ()

A. mysqldump B. mysql C. backup D. return

7. The command for creating a user is (D)

A. join user B. create user C. create root D. mysql user

8. The command for changing the password of your mysql server is (C)

A. mysql B. grant C. set password D. change password

9. An important step to retrieve the root password of the mysql server is to skip the permission table check and start mysql. The command is (D)

A. mysql-u root-proot B. mysqladmin-uroot-proot

C. net start mysql D. mysqld-nt -- skip-grant-tables

10. The keyword used for the joint query is (B)

A. union B. JOIN C. ALL D. FULL

11. There is an order table orders, which contains userid and product information productid. Can the following (D) Statements return at least two productid orders?

A. select productid from orders where count (productid)> 1

B. select productid from orders where max (productid)> 1

C. select productid from orders where having count (productid)> 1 group by productid

D. select productid from orders group by productid having count (productid)> 1

12. The subquery can use the operator ANY, which indicates (B)

A. all conditions are met B. At least one condition is met

C. None of them meet D. At least five conditions are met

13. The command for rollback in a transaction is (C)

A. transaction B. COMMIT C. ROLLBACK D. SAVEPOINT

14. In mysql, the command for restoring the database is (B)

A. mysqldump B. mysql C. backup D. return

15. The table in mysql that stores the global permissions of users is (D)

A. table_priv B. procs_priv C. columns_priv D. user

16. The command for deleting A user is ()

A. drop user B. delete user C. drop root D. truncate user

17. The statement assigned to a user named zhangsan to query and insert data in the stuinfo table in the database studb is (B)

A. grant select, insert on studb. stuinfo for 'hangsan' @ 'localhost'

B. grant select, insert on studb. stuinfo to 'hangsan' @ 'localhost'

C. grant 'hangsan' @ 'localhost' to select, insert for studb. stuinfo

D. grant 'hangsan' @ 'localhost' to studb. stuinfo on select, insert

18. The following options (A) are not supported by mysql replication technology.

A. SQL statement-based replication method B. Row-based replication method

C. Data File-based replication method D. Combination of SQL statements and rows

19. Which of the following functions cannot process the date and time? (B)

A round B WeekDay C Curdate D DayofMonth

20. Constraints in mysql do not include ()

A. Check constraints B. Default constraints C. Non-empty constraints D. unique constraints

Part 2: Short answer (18 points in total)

1. Which of the following storage engines are commonly used in mysql? What are the characteristics of each engine? (6 points)

1. InnoDB: InnoDB provides mysql tables with the capabilities of transactions, rollback, crash repair, and multi-version sick staff to control transaction security.

2. MyISAM: it occupies a small amount of space and can process quickly. Transaction integrity and concurrency are not supported.

3. MEMORY: MEMORY uses the content stored in the MEMORY to create a table, and all data is stored in the MEMORY.

 

 

 

2. What should I do if I forget the password of the MySQL administrator root? Write steps and instructions (6 points)

1. skip the permission mysql -- skip-grant-tables

2. Modify the database name mysql user table update mysql. user set Password = PASSWORD (root) where User = 'root' AND host = 'localhost'

3. Refresh the permission table FULSH PRIIVILEGES

 

 

 

3. Briefly describe the principle of mysql master-slave replication and the modification content and precautions of the my. cnf file on the master and slave servers (6 points)

 

The two servers implement data insertion in the database on the master server and read data from the database on the slave server. This can cope with high concurrency, high traffic, and reduce database pressure, improve user experience. The implementation principle is based on the powerful mysql configuration. Note that during configuration

 

 

 

 

 

Part 3: programming questions (42 points in total)

There is a database empinfo about company employee information. The Department table and employee basic information table are two of them. The table information is as follows:

Department table depts (dept_id, dept_name, description)

Description: dept_id Department No. dept_name Department Name description Department description

 

Employee table employees (id, name, gender, dept_id, join_time, salary, address, age, des)

Note: id employee id name gender dept_id Department join_time add time salary wage address age des description

1) analyze the relationship between tables (primary and foreign key reference relationships) and write the table creation Statement (6 points)

 

Create table depts (dept_id int primary key, dept_name nvachar (50), description nvarchar (100 ));

Create table employees (id int primary, name nvarchar (50), gender char (10), dept_id int, join_time datetime, salary decemical (2, 9), address nvarchar (50 ), age int, des nvarchar (100), constraint e_fk foreign key (dept_id) references depts (dept_id ));

 

 

 

 

 

 

 

 

 

2) Add a field image for the employee table employees to record the employee profile picture (2 points)

Alter table employees add image nvarchar (100) not null;

 

 

3) query records of employees other than zhangsan (2 points)

Select * from employees where name not int ('zhang san ')

 

 

4) query employee records with salaries between and (2 points)

Select * from employees where salary> 2000 and salary <5000;

 

 

5) query the records of employees whose department numbers are 1, 3, and 5 (2 points)

Select * from employees where dept_id int (1, 3, 5 );

 

 

6) query records of employees whose names start with "Zhang" (2 points)

Select * from employees where name like 'sheet % ';

 

 

7) query the records of employees whose gender is "M" and who joined the company in January 1, 2008 (2 points)

Select * from employees where gender = 'M' AND join_time = '2017-1-1 ';

 

 

8) Find the Department ID and name with the highest average monthly salary (3 points)

Select dept_id, name where group by dept_id having out max (avg (salary ));

 

 

9) Create an index for the name column of the employees table (2 points)

Create index index_employees on employees (name );

 

 

10) create a view, EMP_V_10, including Department 10 information and all staff information (3 points)

Create view EMP_V_10 as select employees. *, depts. * from employees, depts where dept_id = 10;

 

 

11) create a user test1 so that he can only query the employees table (2 points)

Grante select on empinfo. employees to 'test1 @ localhost' identity by 'test1 ';

 

 

12) authorize common DBA users (systop) to manage the empinfo database (2 points)

Grante * on empinfo. * to 'systop @ localhost' identity by 'systop ';

 

 

 

13) grant the user test3 the permission to change the name column of the employees table (3 points)

Grante update on empinfo. employees. name to 'test3 @ localhost' identity by 'test3 ';

 

 

14) Procedure for enabling the transaction function for the employees table (4 points)

Begin transaction

Execute the transaction body statement {...}

If @ error> 0

Begin

Rollback transaction

End

Else

Begin

Commit transaction

End

 

 

End

 

 

 

15) write out the methods and steps for backing up the empinfo database to the remote server 182.21.32.129 (5 points)

Backup-send-Import

Mysqldump-u root-p -- all-database> empinfo. SQL

Scp empinfo. SQL 182.21.32.129/tmp

Mysql-u root-p </tmp/empinfo. SQL