MYSQL Pen Question

MYSQL Pen Question

------Two days ago just made a set of MySQL pen questions, share to everyone------------------

first part, single choice (total 40 points)

1, the unique index in MySQL keyword 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. Indexes can increase the speed of data queries B. Indexes can reduce data insertion speed

C.INNODB Storage Engine supports full-text indexing D. The command to delete an index is drop-index

3. The storage engine supporting the primary foreign key, index and transaction is (B)

A.myisam B.innodb c.memory D.character

4, the description of the transaction is incorrect (C)

A. Transactions have atomicity B. Transactions are isolated

C. Transaction rollback using the commit command D. Transaction reliability

5, MySQL, the command to back up the database is (A)

A.mysqldump B.mysql c.backup d.copy

6 . The command to implement bulk data import is (a)

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

7 . The command to create a user is (D)

A. Join user b.create user c.create root D.mysql user

8. The command to modify your mysql server password is (C)

A.mysql b.grant c.set Password d.change password

9. A very important step in retrieving the root password of MySQL server is to skip the check of the permission table to 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 key word used in the Federated query is (B)

A.union B.join C.all D.full

11, there is Order form orders, including user information UserID, product information ProductID, the following (D ) statement can return at least two back to the order of ProductID?

A. Select ProductID from Orders where count (ProductID) >1

B. Select ProductID from Orders where Max (ProductID) >1

C. Select ProductID from Orders where have count (ProductID) >1 GROUP BY ProductID

D. Select ProductID from Orders GROUP by ProductID have Count (ProductID) >1

12. You can use the operator any in a subquery, which means (B)

A. Meet all conditions B. Meet at least one condition

C. None of them satisfies D. Satisfies at least 5 conditions

13. The command that can be rolled back in a transaction is (C)

A.transaction B.commit C.rollback D.savepoint

14. In MySQL, the command to restore the database is (B)

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

15. The table in MySQL that stores the user's global permissions is (D)

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

16. The command to delete a user is (a)

A. Drop user B.delete user c.drop root d.truncate user

17, give the name is Zhangsan the user assigns to the database studb in the Stuinfo table the query and the INSERT Data permission statement is (B)

A.grant Select,insert on Studb.stuinfo for ' zhangsan ' @ ' localhost '

B.grant Select,insert on Studb.stuinfo to ' zhangsan ' @ ' localhost '

C.grant ' zhangsan ' @ ' localhost ' to Select,insert for Studb.stuinfo

D.grant ' zhangsan ' @ ' localhost ' to studb.stuinfo on Select,insert

18. The following option (A ) is a replication type not supported by MySQL replication technology

A. SQL statement-based replication Method b. Row-based replication

C. Data file-based replication method D.sql statements and rows are combined

19. The function in the following function that cannot handle the date and time is (B )

A round B WeekDay C curdate D dayofmonth

20. The constraints in MySQL do not include (A)

A. Checking constraints B. Default constraints C. non-null constraints D. UNIQUE constraints

Part II, short answer (total 18 points)

1, what kinds of common storage engine in MySQL (minimum three kinds), each engine features a difference? (6 points)

1.innodb:innodb gives MySQL tables the ability to handle transactions, rollback, crash repair, and multiple versions of the sick man's security.

2.MyISAM: Small footprint, fast processing speed. The integrity and concurrency of transactions are not supported.

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

2. How to resolve the password of MySQL administrator root? Write Steps and instructions (6 points)

1. Skip Permissions MySQL--skip-grant-tables

2. Modify the database named MySQL user table update mysql.user set Password=password (root) where user= ' root ' and host= ' localhost '

3. Refresh the Load permissions table Fulsh priivileges

3, briefly describes the principle of MySQL master-slave replication and the main, from the server my.cnf file modification content and considerations (6 points)

two server implementation of the database on the primary server to achieve data insertion, in the database from the server to achieve data read, so as to deal with high concurrency, large traffic, reduce database pressure, improve the user experience. The implementation principle is based on a powerful MySQL configuration that can be implemented. Note that the time of the configuration is two

Part III, programming questions ( 42 points total)

there is a database Empinfo about company employee Information . The departmental and employee basic information tables are among the two tables, with the following information in the table:

Department Table depts (dept_id, dept_name, description)

Description:dept_id Department number dept_name Department name Description Department description

Employee Table Employees (ID, name, gender, dept_id, join_time, salary, address, age, DES)

Description:ID Employee number name Employee Name Gender Gender DEPT_ID Department Join_time join time salary salary address age des description

1) analyze the relationship between tables (primary foreign key reference relationship), write out the statement (6 points)

CREATE TABLE depts (dept_id int primary key, Dept_name Nvachar (+), description nvarchar (100));

CREATE TABLE employees (ID int primary,name nvarchar (+), gender char (TEN), dept_id int,join_time datetime,salary decemical (2,9), address nvarchar (a), age int,des nvarchar (+), constraint E_FK foreign key (dept_id) references depts (dept_id));

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

Alter table Employees add image nvarchar (+) not null;

3) Check the records of employees other than those named Zhang San (2 points)

Select *from employees WHERE name not int (' Zhang San ')

4) Check the employee record (2 points) between salary and 2000-5000

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

5) The Enquiry department number is 1,3,5 of the employee's records (2 points)

Select *from Employees where dept_id Int (1,3,5);

6) Check the records of employees whose names begin with "Zhang" (2 points)

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

7) The record of the employee who is gender "M" and has entered the companyat the age of 1 months and 1 days (2 points)

Select *from employees where gender= ' M ' and join_time= ' 2018-1-1 ';

8) Find out the number and name of the department with the highest average monthly salary (3 points)

Select dept_id,name where GROUP by dept_id has 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 views, emp_v_10, including information for department 10th 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 only has permission to query the Employees table (2 points)

Grante Select on Empinfo.employees to ' [e-mail protected] ' identity by ' test1 ';

12) grant the normal DBA user (systop) permission toadminister the Empinfo database (2 points)

Grante * on Empinfo.*to ' [e-mail protected] ' identity by ' systop ';

13) Grant the user test3 The Change permission for theEmployees table Name column (3 points)

Grante update on Empinfo.employees.name to ' [e-mail protected] ' identity by ' test3 ';

14) How to turn on the transaction function for the Employees table (4 points)

Begin Transaction

executing a transaction body statement {...}

[Email protected]>0

Begin

Rollback Transaction

End

Else

Begin

Commit Transaction

End

End

A) write out the methods and steps for backing up the Empinfo database to a 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

MYSQL Pen Question