MySQL Common Commands

MySQL Common commands

1. Create Database data Tables (student, lesson, score)

creating databases and Database tables

2. Inserting data records
Insert one or more table data to student, lesson, score

3. Updating of table data
Update No. 2015112401 student name changed to Xiao Ming, class changed to 15 department

4. Query statements

4.1 Single-Table query
Query all fields, wildcard characters * =like

Selective field condition Query/Condition determination to query table records about conditions

4.2 Multi-Table query

Multi-table query with inner JOIN query

The equivalent connection (INNER join on) connects all two tables of data.
Equivalent connection of student and score table, the condition is the number equivalent, the return result of the connection is 5 inside
select* from student INNER joins score on student. Name =score. Name LIMIT 3;

Natural join (NATURAL join) Cartesian product connection, removing duplicate fields
The student and score tables are naturally connected, and the return result of the connection is 5 in
select* from student NATURAL JOIN score LIMIT 5;

Left JOIN connection (left join)
The student, score left connection contains records with index records in the left table and no match on the right, and the different fields displayed are displayed on a single table
SELECT student, student. Name, student class, score. Course number, score. Course name from student left JOIN score on student. Study number =score. LIMIT 5;

5. Index (four types)
5.1 General Index
Create a new table Suoyin that contains the ID field of type int, the name field of the varchar (20) type, and the XB field of type int. Create a normal index above the first 10 characters of the Name field in the table

5.2 Unique Indexes
Create a new table Suoyin, set up a unique index named Id_index on the ID field of the table, in descending order

5.3 Full-Text indexing
Create the table newTable2 and specify the char (20) field Type field info for the full-text index

5.4 Multi-column index

Create a multicolumn index on the Name field of type char (20) and the Age field of type int on the NewTable3 table

6. View
6.1 Creating a View
Create a simple view for the score table, the view name Score_view1, the view contains the number, the name, the score field
CREATE VIEW Score_view1 (school number, name, score) as SELECT study number, name, score from score;

6.2 View Queries
Use view to query the relevant table record information for student number 2015112401 students
SELECT * FROM Score_view1 WHERE study number = ' 2015112401 ';

7. Stored Procedure + Trigger
7.1 Triggers
1. Create a table TB with only one column A. Creates a trigger on the table, adding 1 to the value of the user variable count each time the insert operation

2. Insert a row of data into the table TB:

3. Insert a row of data into the TB

7.2 Defining a stored procedure
CREATE database caoyin;//define a DB
Use caoyin;//using this database
CREATE PROCEDURE my () SELECT CONCAT (@str1, @str2);//define a stored procedure library and assign null values
Call my ();//Return to Empty

SET @str1 = ' Cao Pin ', @str2 = ' 2015112401 '; Assign value
Call my ();

MySQL Common Commands