Java Learning Summary (15)--mysql database (top)-Add, delete, change and partial query statements

A MySQL introduction and operation in DOS MySQL
1.MySQL is a medium-sized relational database management system (Dbms,database Management systems).
Note: A relational database is a way to store data as a table
Operating MySQL in a 2.DOS environment
(1) Enter the MySQL environment:


(2) Show all databases:

(3) Enter a database:


(4) Show All tables:


(5) To modify MySQL password steps:
• Stop MySQL service, run input services.msc stop MySQL service or cmd--net stop MySQL
• Enter Mysqld--skip-grant-tables under CMD to start the server cursor does not move (do not close the window)
· New open cmd input mysql-u root-p no password required
Use MySQL;
Update user set Password=password (' abc ') WHERE user= ' root ';
• Close two cmd windows in Task Manager end MYSQLD Process
• Restart the MySQL service on the Service Management page
Two Basic SQL statements
1.SQL: (structured query Language) Structured Query Language, specialized language for manipulating relational databases
2. Can differentiate into:
(1) DDL (data definition Language): Database schema definition language, used to define database objects: libraries, tables, columns, etc.;
(2) DML (data manipulation Language): Manipulation language, used to define database records (increase, delete, change);
(3) DQL (data query Language): The language for querying data;
(4) DCL (Data Control Language): The language used to define access rights and security levels;
3. Create a database:


Note: (1) [Default character set ' UTF8 '] can choose not to write, function to set the database encoding
(2) The data in the relational database is stored in the table, and the table is stored in the database.
4. Create a table:

Note: Be sure to enter the specified database before building the table
5. View the table structure:


6. Insert a record into the table:
(1) syntax one (specify field names to insert records):

(2) syntax Two (do not specify a field to insert the record, the inserted value corresponds to each field in the table):

(3) syntax three (insert multiple records):

7. Delete data:

Note: (1) Delete from table name (delete entire table data: Delete data from table)
(2) TRUNCATE table name (delete all records at once, create empty table)
8. Modify the table data:

9. Query data:

• Check the names and grades of stuid>=4 students in the student table

• Query students with scores from 90 to 95 in the student table

Three Sort queries, fuzzy queries, limit queries
1. Sort queries:

• Query the information of all students and display the results in ascending order of results

• Query the information of all students, display the results in descending order, and then show the results in ascending age if the scores are equal

2. Fuzzy query
Wildcard characters:
% represents any number of characters
_ Represents an arbitrary character
• Check all student information for the surname Zhang

• Search for student information that contains ' none ' in all names

• The query name has four characters, and the last word is ' Wang ' student information

3. Restricting queries
(1) Limit the number of rows to query:

• Find information on the top 3 students from 90 to 100

(2) Query the specified location based on the offset (starting from 0, representing the first row) and the number of queries

• Check the student records from 3rd to 8th

Four Paging Query

• Actual operation refer to the previous query case
Five Aggregation functions
1.max (field name): Calculates the maximum value in a field
• Check the highest score in the student

2.min (field name): Calculates the minimum value in a field
• Check the minimum age of the student

3.avg (field name): Calculates the average in a field
• Check the student's average score

4.sum (field name): Calculates the value of a field and
• Check student Scores

5.Count (field name): Calculates the total number of records that are not NULL in a field value
• Query the number of records for which age is not NULL in students

6.Count (*): Calculates the total number of records, whether or not NULL
• Query the total number of records in student tables

Add:
1. Delete: Drop table name
2. Modification:
(1) Modify the added column:
• Add classname columns to the student table:

(2) Modified column type:
• Modify the Name column type of the student table to char (15):

(3) Modification of modified column name:
• Modify the Stuid column name of the student table as ID:

(4) Modify the deletion column:
• Delete the classname column of the Stu table:

(5) Modify the name of the modified table:
• Modify the Stu table name to student:

"This summary is complete"

Java Learning Summary (15)--mysql database (top)-Add, delete, change and partial query statements