MySQL After class study questions

1. Please describe the relationship between database, table and database server.

Knowledge points Database storage Structure

A database server can manage multiple databases, typically a developer creates a database for each app, and to save data for entities in the app, multiple tables are created in the database (the logical structure for storing and describing the data), and each table records information about the entity.

2. Describe the two ways to modify MySQL configuration?

Knowledge points reconfigure MySQL

1. Reconfigure MySQL with a DOS command, such as

Set = GBK

2. Reconfigure MySQL through the My.ini file, such as modifying the properties of the file My.ini

default - character - Set =GBK

3, briefly describe the function of the primary key and its characteristics, create a data table student stu_id and course_id two fields together as the primary key SQL statement?

Knowledge points PRIMARY KEY constraint

The primary key is defined by the primary key, which uniquely identifies the record in the table. Its main features are as follows:

1. Uniqueness: cannot have duplicate values

2. Non-nullability: cannot be a null value

SQL statements:

INT INT PRIMARY KEY (stu_id, course_id));

4. Describe what is the index and the classification of the index, and write out the SQL statement that establishes the full-text index for the name field?

Knowledge points Classification of Indexes

The index of the database is like the sequencer table of the Xinhua dictionary, which is a structure that sorts the values of one or more columns in a database table, and its function is to improve the query speed of the data in the table.

The index is categorized as follows:

1. General Index

2. Uniqueness Index

3. Full-Text Indexing

4. Single-Column indexing

5. Multi-column index

6. Spatial index

To establish a full-text index for the name field:

INDEX fulltext_name (name)

5.1. Create a student table in the database to store student information, where field information includes ID integer field, name string type and non-empty, grade floating-point type, adding 3 new records to the table based on the field type?

Knowledge points Add Data

CREATE TABLE Student (   INT(4),   VARCHAR(  NULL,   FLOAT);

--Inserting data

INSERT  into Student (Id,name,grade) VALUES (1,'zhangsan',98.5);

5.2, update the value of the Grade field in the student table, so that the value of the Grade field will increase 10 points on the original basis, but not exceed the upper limit of 100 points, grade value beyond 100 is modified to 100?

Knowledge points Update Data

--Update data

UPDATE SET Grade=grade+ten; UPDATE SET Grade=where grade>;

6. Please write the full syntax format of the SELECT query statement.

Knowledge points Simple Query

 select  [ distinct  ]  *|  {field name 1, field name 2, field name 3,......}  from   [ where conditional expression 1  ]  [ group by field name [having conditional expression 2   ]  [ order by field name [asc| DESC  ]   "  [ limit [OFFSET  ]  record number]  

7, existing a student table, the table field has students _id, _id, gender _id.

1) Statistics on the number of male and female students in each department.

2) The number of persons in the Department of more than 10 people.

Knowledge points Aggregation Functions

1) SELECT COUNT (*) as Renshu from student GROUP by gender _id, department _id;

2)

SELECT COUNT (*  as Renshu, department _id from student GROUP by department _id  having  COUNT (*ten;

8.1, according to the following conditions to write SQL statements: Query the existence of older than 21 years old staff corresponding to the department information?

Knowledge points subquery with exists keyword

SELECT *  from WHERE EXISTS (Selectfromwhere>);

8.2, according to the following conditions to write SQL statements: The use of self-connected query with Wang Hong in the same department of employees?

Knowledge points Internal Connection

SELECT p1. *  from JOIN  on P1.did=WHERE p2.name='  Wang Hong ';

If you are in a connection query, the two tables involved are the same table, a query called a self-join query

9. Use the Process Control statement to write the following SQL statement:

1) Implement the traversal of numbers between 1-10

2) When the number is greater than 10 exits the traversal process

When the number is between 1-10, the number is traversed and output

Knowledge points use of Process Control in stored procedures

 declare  ID  INT  default  0  Span style= "COLOR: #000000" >;add_loop:loop  set  ID =  id+  1  ;  if  id>=  10  then   LEAVE Add_loop;  end  if     ;  select   ID;  end  LOOP Add_loop;  

10. What are the isolation levels for MySQL transactions?

Knowledge points isolation level of a transaction

1.READ uncommitted is the lowest level in a transaction, also known as dirty Read

2.READ committed can only read content that has been committed by other transactions to avoid dirty reads

3.REPEATABLE Read is the default transaction isolation level for MySQL, which avoids dirty reads and non-repeatable read issues

4.SERIALIZABLE is the highest level of isolation for transactions, which forces transactions to be sorted so that they do not conflict, thus resolving dirty reads, Phantom reads, and repeated reads.

11, known to have a sales table, the table has the first half of sales first_half and the second half of the sales latter_half. Please create a view on the sales table to find out the total sales for one year?

Knowledge points Create a view on a single table

CREATE VIEW  as SELECT first_half+ latter_half from   sales;

12, briefly describe the two ways to modify the view, and write out its basic syntax?

Knowledge points Modify a View

1. Modify the view using the Create OR REPLACE view statement

In MySQL, using the Create OR REPLACE view statement to modify the view, the basic syntax format is as follows:

CREATE [][algorithm = {UNDEFINED | MERGE | TempTable}]VIEW[(column_list)] as select_ Statement[with[cascaded | LOCAL]CHECKOPTION]

2. Modify the view with the ALTER statement

The ALTER statement is another method that MySQL provides to modify the view, and the basic syntax for modifying the view using the ALTER statement is as follows:

ALTER [algorithm = {UNDEFINED | MERGE | TempTable}]VIEW[(column_list)] as select_ Statement[with[cascaded | LOCAL]CHECKOPTION]

13. Please write out, use the mysqldump command to back up the SQL statement of the CHAPTER08 database?

Knowledge points Backup of Data

- -pitcast chapter08>D:/Chapter08.sql

14, please briefly describe how to solve the root user password loss problem?

Knowledge points Modify User Password

1. Stop the MySQL server

2.mysqld--skip-grant-tables start MySQL service

3.mysql-u root command to log back in

4. Reset the password via update:

UPDATE MySQL. User SET Password=Password ('itcast'WHEREUser='  root' and Host='localhost';

5.FLUSH privileges; Reload Permission table

MySQL After class study questions