MySQL Database basics

SQL Structured Query Language.
MySQL database management System.
SQL Server Microsoft's database management system.
Oracle (Oracle) data management system.

show databases; Display all databases in the database management system
Create database name; Create a database.
drop database database name;
Use the database name, using the specified database.

You can specify the encoding language and proofing rules during the database creation process.
ISO 8859-1
GBK gb2312 are all Simplified Chinese codes.
Big5 traditional encoding set.
UTF8 a more comprehensive set of encodings. Most encoding formats are supported.

Understand the meaning of if exists and if not exists clauses.
Understand the meaning of the encoding set and the proofing rules.
Learn how to create a database using Navicat.

March 14

Know:
Alter modifies the only code set and proofing alert warnings that we have modified for the database
Timestamp time stamp
Master:
Show tables;
YYYY-MM-DD Hh:mm:ss
YYYY year mm month DD date
HH hours mm min ss sec

March 19

Classroom objectives:
Complete the creation of a simple table and insert processing of the data.

Creating Table: Create table table name (column name type,......);
The simplest syntax for creating a table.

DESC Table Name
View the structure of the table.

SELECT * from table name;
Query all the contents of the table.

In any of the data tables,
Each valid row describes a complete object.

Insert into table name values (value ...);

Select name from Stuinfo;
Select age from Stuinfo;
Select Name,age from Stuinfo;
SELECT * from Stuinfo;

April 2
1, Query XS_KC table in the results of more than 75 students of the student number and results?

Results >75 conditions where scores >75
The query is not the number and grades to meet my requirements of the conditions.

Select study number, score from XS_KC where results >75

2, I want to find the highest grade students of the school number?

Select study number from XS_KC where score = max (score);
The error in this statement is because the usage syntax of the MAX function is incorrect

The correct wording is:
Select study number from XS_KC where results =
(select MAX (Score) from XS_KC)
Exercise: Query to the students who have the lowest score of the student number?

3, I would like to inquire about the average score of students in the school number and score?

Condition: score is above average.
The results of the query are: study number, achievement.

Aggregate function: Max () min () Avg () count () (this function is required for all management system login processes!) ）

Select study number, score from XS_KC where score >
(select AVG (score) from XS_KC)

4, I would like to inquire about the course number 101 of the score is greater than the average number of students and grades

5. Between and
I would like to inquire about the number of grades and grades greater than 60 less than 70.
Select study number, score from XS_KC where results >60 and score <70

Select study number, score from XS_KC where results between and 70

6. The law of writing SQL query statements.
Results:
Conditions:
7. I want to check the name and score of the students with the highest scores?

Results: Score in the XS table in name XS_KC
Interim result: Student number of the highest achievers
Condition: Highest Score

What are the results of the query from several tables?
Currently there are two, XS. Name XS_KC. Score

8. Cartesian product N*m

9. The name of the student who inquires the highest grade, the result

Select Name, max (XS_KC ' score ') from XS,XS_KC where XS. Study number =
(
SELECT study number from XS_KC where ' score ' =
(select MAX (Score) from XS_KC)
)
10, simple introduction of the use of the Count function
COUNT (primary key name) if there is no primary key then use COUNT (*) to query

Check the number for the 081102 students named Wang Lin is there
Select COUNT (study number) from XS WHERE
Study number = ' 081102 ' and name = ' Wang Lin '

April 4, 2018

1. Inquire the name, professional name and total credits of each student in the XS table.

2. Inquire about the student's number, name and total credits of the computer department in the XS table, and the headings of each column in the results are designated as
Number, name, Mark.

Select name as name, as number, total credits as Mark from XS
where professional name = ' computer '

Conditional query and display of column names

3, query the XS table in the computer department students of the school number, name and total credit, the total credit to the following rules to replace,
If total credit is empty replace with not selected course
Total credits less than 50, replaced by unqualified
The total credits between 50 and 52 are replaced by qualified
Greater than 52 is replaced by excellent.
The column headings are replaced with a rank.

Select study number, name,
Case
When total credits is NULL then ' not selected course '
When total credits <50 then ' unqualified '
When total credits >=50 and total credits <=52 then ' qualified '
When total credits >52 then ' excellent '
END as Rating
From XS
Where ' professional name ' = ' computer '

4, according to 120 points calculation results, showing XS_KC table secondary School Number 081101 students course information

Results: Course Information
Condition: Study number is 081101
SELECT study number, course number, floor (' Score ' *1.2)
As score, credits from XS_KC
Where study number = ' 081101 '

5. Query the professional name and total credits in the XS table and eliminate duplicate lines
The DISTINCT keyword is used to eliminate duplicate records in the result set.
6, the total number of students to ask?
Results: Number of people
7. The number of students who do not have an empty statistical note.
Result: Number
Condition: note is not empty
Select COUNT (study number) as total number from XS
Where note is not NULL

8. The number of students with total credits of 50 or more.
Select COUNT (study number) as total number from XS
Where total credits >50

9. Check the highest and lowest points of the students taking the 101 courses.
Min (), Max ()
10. The total number of students studying in the course of 081101
Select SUM (Score) as total from XS_KC where study number = ' 081101 '
11. Average results for students taking elective 101 courses
Select AVG (Score) from XS_KC WHERE Course number =101
12. Check the status of the students of the XS school number 081101
SELECT * FROM xs where study number = ' 081101 '

13. Check the status of the total credits in the XS table greater than 50

SELECT * from XS where total credits >50

14. The situation of students who have empty comments in the enquiry form
SELECT * FROM XS where note is null

15, the inquiry form of professional computer, sex for women (0) The situation of students
SELECT * from xs where professional name = ' computer ' and gender =0
16. Check the records of students who are more than 80 points in the 102 and 206 courses in the XS_KC table.
SELECT * from XS_KC where ' score ' >80 and course number in (102,206)
17. Inquire about the student's number, name and gender of the surname "Wang" in the XS table
The keywords used are "like" and "like"
Wildcard characters used in conjunction are "%" (any length) "_" (one length)
Select study number, name, gender from XS where name like ' King% ';
18. Inquire about the student's number, name and gender of the "King" in the XS table with the name of 3 characters
Select study number, name, gender from XS where name like ' King __ ';
19. Check the name and professional name of the student with the second-to-last digit of the XS Secondary school number 0.
Select study number, name, professional name from XS where study no. like '%0_ '
20. Inquire about the student number and name of the name in the XS table with the underlined names?

Select study number, name from XS where name like ' \_ '

This is tomorrow.
21. Check the status of students who are not born in 1989 in the XS table

Select study number, name, professional name, birth time from XS
where birth time > ' 1989-12-31 ' or birth time < ' 1989-01-01 '
Data of the date type can be compared in size.

Select study number, name, professional name, birth time from XS
Where birth time not like ' 1989% '

The LIKE keyword can be applied to columns of the date type.

22, the inquiry form of the professional name "Computer", "Communication engineering" the situation of students

SELECT * from xs where professional name = ' computer ' or professional name = ' Communication Engineering '
SELECT * from XS where professional name in (' Computer ', ' Communication engineering ')
23. Inquire about the students who are still unsure of the XS secondary school

SELECT * FROM XS where
Remark like '% to be retake% '

24. Check the course name and course number selected by all students.
Results: Course Name and course number
Condition: Selected.

Select KC. Course name, KC. Course number from KC where KC. Course number in
(select DISTINCT xs_kc. Course number from XS_KC)

Select DISTINCT KC. Course name, KC. Course number from

Kc,xs_kc
where KC. ' Course number ' =XS_KC. ' Course Number '

25. Find the students who have enrolled in the basic course of computer science and have scored more than 80 points in the Student name course (computer Basic course).
Select xs ' learning number ', XS ' name ', xs_kc ' results ', KC. ' Course name '
From XS INNER JOIN XS_KC on XS ' study number ' =XS_KC ' School number '
INNER JOIN KC on XS_KC. ' Course number ' =KC. ' Course number ' and KC. ' Course name ' = ' Computer Basics '
and XS_KC. ' Grades ' >80

26. Find the students who have enrolled in the basic course of computer science and have a score of 80 or above, the name of the course (all courses), and the results.

Select xs ' learning number ', XS ' name ', KC. ' Course name ', XS_KC ' results ' from
XS INNER JOIN XS_KC on xs ' learning number ' in
(select DISTINCT xs_kc. ' Learning number ' from XS_KC inner JOIN, KC on XS_KC. ' Score ' >80 and
KC. ' Course name ' = ' Computer Basics '
INNER JOIN KC on XS_KC. ' Course number ' =KC. ' Course Number '

27. Find the student's number and course number as well as the result of the course.
Query three field (s) in the same table.
Condition: The result is the same.
If there is a primary foreign key relationship between the two tables, you can A.field = B.field
If we look at it from a single table, we must consider this table as two tables.

Select DISTINCT A. Course number, a. Score from XS_KC A,XS_KC b
Where a. ' Course number '!=b. ' Course number ' and a. ' Score ' =b. ' Score '

28. Find out the status of all students and the number of courses they take, including the circumstances of the students if they have not enrolled in any courses.

29. Find the course name of all the classes selected by the students in the KC table
How do you know if you've been chosen?
XS_KC The course number that appears in the table to prove that you have chosen

Select DISTINCT KC. ' Course name ' from KC, XS_KC where
KC. ' Course number ' =XS_KC. ' Course Number '

SELECT DISTINCT KC. ' Course name ' from KC INNER JOIN XS_KC
On KC. ' Course number ' =XS_KC. ' Course Number '

30. Search for elective courses and all courses offered

What are the elective cases?
How many candidates are there? Course

SELECT COUNT (XS_KC) as number of people, XS_KC. ' Course number ', KC. ' Course name '

From XS_KC right JOIN in KC on XS_KC. ' Course number ' =KC. ' Course Number '

GROUP by KC. ' Course Number '

31. Find the names, school numbers and professional names of students who have not been enrolled in discrete mathematics
SELECT xs ' learning number ', XS ' name ', xs ' professional name ' from XS
where XS. ' Learning no ' in
(
Select XS_KC. ' Learning number ' from XS_KC WHERE xs_kc. ' Course Number ' =
(SELECT KC. ' Course number ' from KC where KC. ' Course name ' = ' Discrete math ')
)

32. Find the student number that has been enrolled in discrete mathematics
Select XS_KC. ' Learning number ' from XS_KC WHERE xs_kc. ' Course Number ' =
(SELECT KC. ' Course number ' from KC where KC. ' Course name ' = ' Discrete math ')

33. Find the student's number, name, professional name, and date of birth in the XS table that are older than all computer departments
SELECT xs. ' Birth time ' from XS where XS. ' Birth time ' <
(
Select min (Birth time) from XS where XS. ' Professional name ' = ' computer '
)

SELECT xs. ' Birth time ' from XS where XS. ' Birth time ' <
All
(
Select birth time from XS where XS. ' Professional name ' = ' computer '
)

34. Find the student number of the course number 206 in the XS_KC table not less than the lowest grade of course number 101

Select XS_KC. ' Learning number ' from XS_KC WHERE xs_kc. ' Score ' >=
Any
(
SELECT score from XS_KC where XS_KC. ' Course Number ' =101
)
and XS_KC. ' Course Number ' =206

35. Find the name of the student taking course No. No. 206
Select XS. ' Name ' from XS INNER JOIN XS_KC
On XS ' study number ' =XS_KC. ' Study Number '
where XS_KC. ' Course Number ' =206

36. Export the professional names in XS

37. Find the number of majors in XS

38. Average scores of courses taken and number of elective courses

Select XS_KC. ' Course number ', KC. ' Course name ', COUNT (academic number), AVG (score) from XS_KC
INNER JOIN KC on KC. ' Course number ' =XS_KC. ' Course Number '
GROUP by ' Course Number '

39. Produce a result set on the XSCJ database, including the number of males per profession, number of females, total number of students and total number of pupils
SELECT COUNT (student number) as number of females, professional name from XS
where gender =0 GROUP by professional name;

SELECT COUNT (number) as the number of males, professional name from XS
where gender =1 group by professional name;

SELECT COUNT (number) as the total number of professionals, professional name from the XS group by professional name;

SELECT COUNT (study number) as total number from XS;

40. Find the names, school numbers and average scores of students with average scores above 85 points
SELECT XS_KC ' study number ', AVG (XS_KC ' score '), Xs. ' Name '
From XS_KC
INNER JOIN xs on XS_KC. ' School Number ' =xs. ' School Number '
GROUP by XS_KC. ' School Number ' having AVG (xs_kc. ' Score ') >85

41. Find the number of students with more than 2 elective courses with a score of 80 or above, name
SELECT XS_KC. ' Study number ', XS ' name '
From XS_KC
INNER JOIN XS
On XS ' study number ' =XS_KC. ' Study Number '
GROUP by XS_KC. ' School Number '
Having MIN (score) >80
and COUNT (XS_KC. ' Course number ') >2

42. Prioritize communications engineering students by date of birth
SELECT * from xs where professional name = ' Communication Engineering '
ORDER by ' Birth Time ' ASC

43. The computer Science students ' computer Fundamentals ' grade, name and number are sorted in descending order.
SELECT xs_kc. ' Score ', xs ' name ', XS ' study number ' from XS_KC INNER JOIN XS
On XS ' study number ' =XS_KC. ' Study Number '
INNER JOIN KC on KC. ' Course number ' =XS_KC. ' Course Number '
where KC. ' Course name ' = ' Computer Foundation ' and
XS. ' Professional name ' = ' computer '
ORDER by XS_KC. ' Grades ' DESC

44. Rank computer majors by their average scores
SELECT AVG (Score) from XS_KC INNER JOIN
XS on XS ' study number ' =XS_KC. ' School Number '
where XS. ' Professional name ' = ' computer ' GROUP by XS_KC. ' School Number '
ORDER by AVG (score)

45. Find information on the top 5 students of the XS table secondary School number
SELECT xs.* from XS
ORDER by study number LIMIT 0, 5;

46. Find information about 5 students starting from the 4th student in the XS table

Query database Select Column name from table name where condition
Insert a database insert into table name values values
Delete database Delete from table name where condition
Modify database Update table name set number where value

View
CREATE VIEW V_xs
As SELECT XS ' study number ', XS ' name ', Xs. ' Birth time ' from XS
Create a view with CHECK option

Drop View name Delete views

Create or Replace view V_xs
As SELECT XS ' study number ', XS ' name ', Xs. ' Birth time ' from XS
Modify View with CHECK option

Show full tables display views and tables

Alter VIEW V_gameinfo AS
Select Gameid,gamename from gameinfo change view

A view is a virtual table
A view is a query that is stored

What is an index?
Understanding the index is actually like a directory
Indexes are all about improving the efficiency of queries.

What is a constraint?
Rule index
The primary key PRIMARY KEY constraint cannot be duplicated and cannot be null, indicating the difference between the current row and the other rows.
Unique uniqueness Constraint
Index
Fulltext
Set autocommit=0
Start transaction;
SELECT * FROM Gameinfo
Delete Frim Gameinfo
SELECT * FROM Gameinfo
Rollback
Set Autocommit=1

Show index from class

CREATE index INDEX_XS on XS (name (2) desc); Create an index
The index index is the function of improving the efficiency of the query.

Show index from XS display indexes

--Create INDEX INDEX_XS on XS (name (2) desc);
The index index is the function of improving the efficiency of the query.
--Show index from XS
Create unique index UNIQUE_XS on XS (name);
--Ensure that the content in the current column is different from the content in the other rows
INSERT INTO XS (XS ' study number ', XS ' name ', XS.)

ALTER TABLE XS
Add unique index UNIQUE_XS (name)
Delete is using the Drop keyword.

Columns that are appropriate for creating indexes typically appear in a condition statement.
Use of short indexes.
About the concept of things
For example: Go to a bank and get a bank card from the door. For the bank, you started the business.
The entire process of processing the business, is called the value of "things."

atomicity (non-delimited)
Consistency (Consistent results)
Isolation (for the user, the business is handled separately)
Persistence (the duration of the action is a long period of time)
A set of statements is called a thing
There are two ways to commit a thing, one is to show that a commit is an implicit commit
Turn off auto-commit instructions
Set autocommit=0
Start transaction

SavePoint P

Rollback to savepoint P
--rolls back to the specified save point.
Rollback
--Rollback (I'm back on it)
Commit
--Submit (I'm sure this is the case)
--Set Autocommit=1

Database programming

1. What do we mean by the data we operate on?
Information
Information or information that represents the data must be loaded with a container.
The container that loads the data is a constant or variable.
2, MYSQQL kinds of constants are divided into numerical type, character type, date type, Boolean type.
3. Assignment operation format (global variable) in MySQL species:
1) SET @ variable name = ' xxxx '
2) SELECT @ Variable name: = ' xxxx '
4, the assignment of variables in local variables can use the SELECT INTO statement.
Notice how the save process is created. You can create a global variable in the previous article @name

Set @name = "Wang Lin";

Select @name;

SELECT * from XS where XS. ' Name ' [email protected];

Select @pai: = (@pai: =3.14) +2

Create PROCEDURE P01 ()
BEGIN
DECLARE myname varchar (8) DEFAULT ' Long Letter student '
--Declare a local variable myname default value for long letter students
Set @name = MyName;
End

Call P01 ();

5, the way of invoking system variables, some system variables can be used without "@@"

Conditional control Statements

If statement
Create PROCEDURE P02 ()
BEGIN
Set @a=80;
If @a>60 then select ' Pass ';
Else select ' failed ';

End If;
END

Drop PROCEDURE P02
Call P02 ();

Select @a

===============================================================
Create PROCEDURE P02 ()
BEGIN
Set @a=80;
Set @b= ' score ';
If @a>60 then set @b= ' Pass ';
else set @b= ' fail ';
End If;
END

Drop PROCEDURE P02
Call P02 ();

Select @b

==================================================================
Create PROCEDURE P02 ()
BEGIN
Set @a=70;
Set @b= ' score ';
If @a>80 then set @b= ' excellent ';
ElseIf @a>60 Then set @b= ' Pass ';
else set @b= ' fail ';
End If;
END

Drop PROCEDURE P02
Call P02 ();

Select @b
===================================================================
Create PROCEDURE P02 ()
BEGIN
Case
When @a>59 then select ' Pass ';
Else select ' failed ';
End case;
END

Drop PROCEDURE P02
Call P02 ();

====================================================================
drop procedure P01

CREATE PROCEDURE P01 ()
BEGIN

DECLARE sum int default 0;
declare i int default 1;
While i<10 do
Set sum=sum+i;
Set i=i+1;
End while;
Select sum;
END

Call P01 ();
Select @a
===================================================================
drop procedure P01

CREATE PROCEDURE P01 ()
BEGIN
DECLARE sum int default 0;
declare i int default 1;
Add_sum:while true Do
Set sum=sum+i;
Set i=i+1;
if (i=101) then
Leave Add_sum;
End If;
End While Add_sum;
Select sum;
END

Call P01 ();
==================================================================
drop procedure P01

CREATE PROCEDURE P01 ()
BEGIN
DECLARE sum int default 0;
declare i int default 1;
Add_sum:while true Do
Set i=i+1;
if (i=101) then
Leave Add_sum;
End If;
if (i%2!=0) then
Iterate add_sum;
End If;
Set sum=sum+i;
End While Add_sum;
Select sum;
END

Call P01 ();

MySQL Database basics