1, Short answer
1. Briefly describe the MySQL version used by your company, and specify the specific minor version and GA time?
5.6.38 5.7.20 2017-9-13
2. Please introduce the type of database you are familiar with and the representative product name?
NoSQL:Redis Mongodb memcache RDBMS:Oracle MySQL MSSQL PG
3. Please briefly describe the key steps of MySQL binary installation?
1.download,upload,decompression 2. Create user 3. Create relevant directories and authorize 4. Setting environment variables 5. Initialization data 6. configuration file 7. Configure startup script
4. How to confirm that the database is started successfully?
ps -ef |grep mysqld netstat -lnp|grep 3306 ss -lnp|grep grep 3306 mysql Login test
5. Briefly describe the MySQL branch version you know?
Oracle MySQL MariaDB Percona
6. Please briefly describe the program structure of mysqld (the execution process of one SQL statement)
adjoining course:
Provide connection protocol,verification,Dedicated connection thread
SQL layer
grammar,semantics,jurisdiction,analysis,optimization,implement,Query cache,Logging
Storage engine tier
amount to Linux file system,for example:InnoDB Provides transactions,CSR,Hot standby,MVCC,Row level lock, etc
7. Please briefly describe the startup mode of MySQL you know
sys-v:mysql.server--->mysqld_safe---->mysqld systemd: mysqld --defaults-file mysqld_safe mysqld
8. Briefly describe the default reading order of MySQL configuration file
mysqld --help --verbose|grep "my.cnf"
9.mysqld_ Safe -- default files = / opt / my. CNF & what is the function?
Custom profile,Background start mysql
10. Forget the password processing process of administrator root, please describe the parameters in detail
--skip-grant-tables Turn off the verification function of the connection layer --skip-netwoking close TCPIP agreement
11. Please list the common types of SQL statements
DDL DML DCL DQL
12. Please explain the difference between clustered index and secondary index
Secondary index,Leaf nodes store only,All values of an ordered column Clustered index,The whole row of data is stored Secondary indexes are generally used in conjunction with clustered indexes,Find the primary key value through the auxiliary,Then find the data row through the clustered index,Reduce the random caused by back table query IO
13. Please briefly describe the possible problems of the following statement execution plan
Explain the possible problems of the following statements and put forward reasonable solutions
explain select * from city where countrycode='CHN' order by population;
1. countrycode No index,There may be no index,Or the index fails 2. There it is filesort File sorting, orderby The conditions didn't go either proposal: 1. If there is no index,Create federated index(countrycode,population) 2. If the index fails,Delete index rebuild
Briefly describe the possible causes of the following results
Doubt is telnum Column is a string type,An implicit conversion may have occurred,The data type needs to be further determined.
14. Please briefly describe the factors affecting the height of the index tree?
Data magnitude:Distributed by table and database Index key value too long:Prefix index data type:char varchar choice,enum,Select a reasonable data type
15. Please explain the handling idea of database startup failure?
There are logs,Look at the log,No log,use mysqld Direct test start
16. What are the types of MySQL indexes?
B tree HASH R tree
17. What kinds of MySQL storage engines do you know?
InnoDB,MyIAM,CSV
18. Core features of InnoDB storage engine
affair,CSR,MVCC,Row level lock,Hot standby,Foreign key
2, Operation problem
1. Create an administrator user: oldboy can log in and manage MySQL through any address in the 10.0.0.0/24 network segment
grant all on *.* to oldboy@'10.0.0.%' identified by '123';
2. Create application user: wordpress can log in and operate all tables under wordpress library through any address of 172.16.1.0/24 network segment
grant select,update,delete,insert on wordpress.* to wordpress@'172.16.1.%' identified by '123';
3. Please write out the basic configuration information of / etc/my.cnf
[mysqld] user=mysql basedir=/usr/local/mysql datadir=/data/mysql socket=/tmp/mysql.sock server_id=1 port=3306 [mysql] socket=/tmp/mysql.sock
4. Please write out the specific statement of using oldboy user to log in to MySQL remotely
mysql -uoldboy -p -h 10.0.0.51 -P3306
5. View the character set of the current database
show charset;
6. Create the database oldboy of GBK character set and view the complete statements of the built database
create database oldboy charset gbk; show create database oldboy;
7. Please introduce not null default auto respectively_ Role of increase
Not NULL :Non empty default: Default value auto_increament:Automatic growth
8. Create the user oldboy so that it can manage the database oldboy
grant all on oldboy.* to oldboy@'10.0.0.%' identified by '123';
9. Withdraw the drop permission of oldboy user
revoke drop on oldboy.* from oldboy@'10.0.0.%';
10. View the permissions of the created user oldboy
show grants for oldboy@'10.0.0.%'
11. View the table structure and the SQL statement for creating the table
desc stu; show create table stu;
12. Insert a piece of data "1,oldboy"
insert into t1 values(1,'oldboy');
13. Insert 2 rows of data "2, old boy" and "3,oldboyedu" in batch
14. Query the record named oldboy
select * from t1 where name='oldboy'
15. View the types of all engines in the database
show engines;
16. View the database log parameter configuration
show variables like '%log%';
17. View handler_read_key current status information
show status like 'handler_read_key';
18. Difference between delete and truncate
delete :Delete data rows one by one logically trucate:Physically delete all data pages in the table segment
19. In the test table, there are id, name and shouji columns. Set the id column as the primary key and create a common index on the name field
create table test ( id int not null primary key comment 'Student number', name varchar(64) not null comment 'full name', telnum char(11) not null comment 'cell-phone number' )engine InnoDB Charset Utf8 comment 'Student list'; alter table test add index idx_name(name);
20. Create a common index for the first 8 characters on the mobile phone field
alter table add index idx_tel(telnum(8));
21. View the created index and index type
desc test; show index from test
22. Delete the index of the Name and shouji columns
alter table test drop index idx_name ; alter table test drop index idx_tel;
23. Establish a joint index for the first 6 characters of the Name column and the first 8 characters of the mobile phone column
alter table test add index idx_n_t(name(6),telnum(8));
24. Replace the shouji column index with a unique key
alter table test add unique index idx_tel(telnum);
25. How to view the duplicate values of the population column in the city table in the world database
select population,count(id) from world.city group by population having count(id)>1 order by count(id) desc ;
26. Please list multiple type s in the explain command
ALL,INDEX,RANGE,REF,EQ_REF,SYSTEM(const),NULL
27.Select query statement strengthening exercise
Count the total population of each country in the world
select countrycode,sum(population) from city group by countrycode;
Count the total population of each province in China
select district,sum(population) from city where countrycode='CHN' group by district;
Count the number of cities in each country in the world
select countrycode,count(name) from city group by countrycode;
Count the total population of each province in China, and sort the total population less than 100w from high to low
select district,sum(population) from city where countrycode='CHN' group by district having sum(population)<1000000 order by sum(ppopulation);
28. Generate separate backup statements for all tables under the entire database
SELECT
CONCAT("mysqldump -uroot -p123 ",table_schema," ",table_name
," >/bak/",table_schema,"_",table_name,".sql")
FROM information_schema.tables;
29. SQL comprehensive exercise
1. Query the student number and average score of students whose average score is greater than 60;
select student.sname,sc.sno,AVG(sc.score) as gg from student join sc on sc.sno=student.sno group by sc.sno having gg>60;
2. Query the student number, name, number of courses selected and total score of all students;
select student.sno,student.sname,count(sc.cno),sum(sc.score) from student join sc on sc.sno=student.sno group by sc.sno;
3. Query the highest and lowest scores of each subject: displayed in the following form: course ID, highest score and lowest score
select sc.cno ,max(sc.score),min(sc.score) from student join sc on sc.sno=student.sno group by sc.cno;
4. Count the passing rate (case) of the courses taught by each teacher
count(case when isfull(socre,0)>=60 then 1 end)/count(*) group by subject select teacher.tname,count(case when ifnull(sc.score,0)>=60 then 1 end)/count(*)*100 as 'pass rate%' from teacher join course on course.tno=teacher.tno join sc on sc.cno=course.cno group by teacher.tno;
5. Query the number of students selected for each course
select course.cname,count(sc.cno) from teacher join course on course.tno=teacher.tno join sc on sc.cno=course.cno group by sc.cno;
6. Find out the student numbers and names of all students who have taken only one course
select sc.sno,student.sname,count(sc.cno) from teacher join course on course.tno=teacher.tno join sc on sc.cno=course.cno join student on student.sno=sc.sno group by sc.sno having count(sc.cno)<2;
7. Query the information of students with more than one elective course
select sc.sno,student.sname,count(sc.cno) from teacher join course on course.tno=teacher.tno join sc on sc.cno=course.cno join student on student.sno=sc.sno group by sc.sno having count(sc.cno)>1;
8. Statistics of each course: excellent (above 85 points), good (70-85), average (60-70), failed (less than 60) student list (case)
,group_concat(case when isfull(score ,0) >=85 then sname end) as 'excellent' ,group_concat(case when isfull(score ,0) between 70 and 85 then sname end ) as 'good' select course.cname, group_concat(case when ifnull(sc.score,0)>=85 then student.sname end) as 'excellent', group_concat(case when ifnull(sc.score,0) between 70 and 85 then student.sname end ) as 'good', group_concat(case when ifnull(sc.score,0) between 60 and 70 then student.sname end) as 'commonly', group_concat(case when ifnull(sc.score,0)< 60 then student.sname end) as 'fail,' from student join sc on sc.sno=student.sno join course on course.cno=sc.cno group by course.cno;
9. Query the student number, name and average score of all students whose average score is greater than 85
select student.sname,sc.sno,AVG(sc.score) as gg from student join sc on sc.sno=student.sno group by sc.sno having gg>85;