Day04 about Mysql-android's small white study notes

MySQL Getting Started 1, Database Basics (Understanding) 1.1, Database Introduction 1.1.1, what is a database? What is the role of the database?

The database is the warehouse that stores the data, its essence is a file system, the data is stored in the specific format, the user can add, modify, delete and query the data in the database.

To store and read data:

No database:

1, create a new folder to store files;

2, the corresponding data in the form of a character stream into the corresponding file;

3. Put the file into the folder in a certain way;

4, in order to query the corresponding data, you need to data warehouse all files in the form of character stream to get and traverse, in order to obtain some of the corresponding data identification to obtain data;

There are databases:

1, the data in the database language to the database;

2. The database is stored in the corresponding data table after the data is obtained;

3, in order to query the corresponding data, only need to use the database language to get the corresponding data.

As software application data in development. What are the 1.1.2 and common databases? (Java ee)

Oracle large fees are applied more in Java

L SQL Server large charge in Microsoft

L Sybase is basically powerdesinger

Mysql Small and medium-sized free open source database (6 version before free)

L DB2 IBM Large charge IBM

Brief description of each database:

Oracle Database: Oracle is the first word in the English translation of Oracle bone inscriptions, unearthed by Yin Ruins (Yīn Xu), which is "Oracle" in English, founded by Larry Ellison and two other programmers in 1977 , they have developed their own products, a large number of sales in the market, in 1979, Oracle introduced the first commercial SQL relational database management system. Oracle is one of the first vendors to develop a relational database with products that support the widest range of operating system platforms. Oracle relational database products are at the forefront of market share.

L SQL Server database: In 1987, Microsoft and IBM collaborated to develop the OS/2,IBM OS/2 extendededition system in which it was sold, and the Microsoft product line lacked a database product. To this end, Microsoft has looked to Sybase, signed a cooperation agreement with Sybase, using Sybase technology to develop a relational database based on the OS/2 platform. In 1989, Microsoft released SQL Server version 1.0.

L Sybase Database: Sybase was founded in 1984, and the company name "Sybase" is taken from the meaning of "system" and "database". Bob Epstein, one of the founders of Sybase, is the main designer of the Ingres University Edition (relational database model product with SYSTEM/R). The company's first relational database product was the Sybase SQLServer1.0 launched in May 1987. Sybase first put forward the idea of Client/server database architecture, and was the first to implement it in Sybase SQL Server.

MySQL data: MySQL is a small relational database management system, the developer of the Swedish MySQL AB company. was acquired by Sun Company on January 16, 2008. In 2009, Sun was also acquired by Oracle Corporation. MySQL is now widely used in small and medium-sized websites on the internet. Because of its small size, fast speed, low total cost of ownership, especially the open source, many small and medium-sized web sites in order to reduce the total cost of ownership of the site chose MySQL as the site database

L DB2 Database: As a pioneer and pilot in the field of relational databases, IBM completed the prototype of System R systems in 1997, and in 1980 began providing integrated database server--SYSTEM/38, followed by Sql/dsforvse and VMS, Its initial version is closely related to the SYSTEMR research prototype. DB2 ForMVSV1 was launched in 1983. The goal of this release is to provide the simplicity that this new scenario promises, data uncorrelated and user productivity. 1988 DB2 for MVS provides robust online transaction processing (OLTP) support, and distributed database support in 1989 and 1993, respectively, with remote units of work and distributed work units. The recently launched DB2 Universal Database 6.1 is a model of the general-purpose databases and is the first multimedia relational database management system with online capabilities to support a range of platforms including Linux. 1.1.3, Database type

The database development process is as follows:

? No database, use disk file to store data;

? Hierarchical model database;

? Network structure model database;

? Relational structure model database, using a two-dimensional table to store data;

? Relational-object model database; (NOSQL)

L relational Database (SQL)

relational database, which is based on the relational model, is used to deal with the data in the database by means of mathematical concepts and methods such as set algebra.

The MySQL we're going to learn today is the relational Structure model database management system, referred to as relational database management system

L Non-relational database (NOSQL) 1.1.4, DBMS 1.1.4.1, what is a DBMS?

The DBMS (Database Management System) is referred to as the data management systems.

Windows Computer Management System

is a large software for manipulating and managing databases that are used to establish, use, and maintain databases. It has unified management and control of the database to ensure the security and integrity of the database.

The role of 1.1.4.2 and DBMS

L Data definition (establish database structure and constraints)

L Data operation (user implementation of the data to append, delete, update, query and other operations)

L Database operation and Management

• Data organization, storage and management.

L Database Protection and maintenance

L Communication

Equivalent to the warehouse Butler or Windows operating system (without assembly language) 1.1.4.3, data storage method

We usually refer to the database management system as a database, when we install the database (database server), you can create the database in the database server, each database can also contain more than one table

Through the above diagram we know the relationship between the database and the table, then how our data is stored in the database.

A table in a database is a multi-row, multi-column table. When you create a table, you specify the number of columns in the table, as well as information such as column names, column types, and so on. Without specifying the number of rows in the table, there is no limit to the number of rows.

The columns in the table we call fields, the rows in the table we call records.

Then using our familiar Java program to compare with relational data, we will find the following correspondence.

Class-------Table

fields in the-------table for properties in a class

Object--------Record. 1.2.4.2, exiting the MySQL service

1. Enter exit

2. Enter quit

3. Direct window x number off

or direct CTRL + C 1.3, MySQL database root password reset

1) Stop MySQL server running input services.msc stop MySQL service

2) Enter mysqld--skip-grant-tables under CMD to start the server cursor does not move (do not close the window)

3) New open cmd input mysql-u root-p no password required

Use MySQL;

Update user set Password=password (' 1234 ') WHERE user= ' root ';

4) Close two cmd windows in Task Manager end MYSQLD Process

5) Restart the MySQL service on the Service Management page

Password modification complete 1, Database Operations (key) 2.1, SQL Introduction 2.1.1, what is SQL? What is the role?

Structured Query Language (structured query Language) is a database query and programming language for accessing data and querying, updating, and managing relational database systems.

The SQL language is the language of the command to the DBMS 2.1.2, the classification of SQL

SQL is divided into four classes, as follows:

? Data definition language: Language, a Database object: Database, table, column, etc., such as create, delete, modify: Database, table structure, database or data table structure operation

? Data manipulation language: referred to as DML (manipulation Language), used to update the records of tables in the database, such as: Add, delete, change table records, table data additions and deletions

? Data Control Language: The Language, which defines the access and security level of the database and creates the user;

? Data Query Language: Short DQL (Language), which is used to query the records of tables in the database. Querying table Data

You might be less clear about DDL and DML, DDL is manipulating the structure of a database or table, and DML is manipulating the table's records (add, delete, change). The above four categories, our most commonly used is DDL, DML, DQL, of which, DQL is difficult. Syntax for 2.1.3, SQL

The syntax for SQL has the following requirements:

The SQL statement can be written one or more lines, and must end with a semicolon;

L can use space and indentation to enhance the readability of the statement;

The SQL statement for the MySQL database is not size-sensitive (default), and it is generally recommended to use uppercase, for example: SELECT * from user. (Because uppercase readability is poor, the general developer will use lowercase tests, formally put in uppercase on the code) 2.2, the database's library structure operation (DDL) 2.2.1, create the database

1. Using the default character set and proofreading

Create database name;

2. You can specify database characters

Create database name CHARACTER set GBK;

Practice:

Create a database called MYDB1.

Create Database mydb1;

Create a MYDB2 database that uses the UTF8 character set.

Create database mydb2 character set UTF8; 2.2.2, viewing the database

View all databases under the current database server

show databases;

To view the statement for the database character set

Show create database name;

Practice:

View all databases in the current database server

show databases;

View the definition information for the MYDB2 database that you created earlier

Show CREATE Database mydb2; 2.2.3, modifying the database

Modifying database operations We seldom use them, so we can understand the syntax.

Modifying the encoding set of a database

ALTER DATABASE name Character set encoding

Practice:

View the database in the server and modify the character set of one of the libraries to UTF8;

Show CREATE Database mydb2;

ALTER DATABASE MYDB2 character set UTF8 collate utf8_general_ci; 2.2.4, deleting a database

Deleting a database

Drop database name

Practice:

Delete the previously created MYDB1 data 2.2.5, other operations about the library

1. Switching the database

Use database name

2. View the current database of things you are using

Select Database (); 2.3. database table structure Operations (DDL) 2.3.1, field types in data tables

MySQL, like Java, has data types, and the data types in MySQL are primarily applied to columns. When we define a table, we define the column name and column type for each column. This column type is the type of data we are going to talk about today.

Data types that are common in Java

Basic type:

Integer byte short int long

Floating-point float double

Boolean Boolean

Character Char

Reference type

Strings string

Date 2.3.1.1, Java---mysql integer comparison:

Java

Mysql

Byte

TINYINT

Short

SMALLINT

Int

Int

Long

BIGINT 2.3.1.2, Java---mysql floating-point comparison:

Java

Mysql

Float

Float

Double

Double

BigDecimal

Decimal

Decimal, which is also a floating-point type, typically used for currencies such as: decimal (5,2) length is 5 and two digits after the decimal point.

Boolean----BOOL Boolean is generally described directly using numeric types, directly using int or string 2.3.1.3, Java---mysql boolean comparison:

Java

Mysql

Boolean

bool

There is no Boolean type in MySQL. There's no real sense of true and false

Official: In bool, 0 is considered false. Non-0 is considered true.

In the future, a full Boolean type of processing will be introduced based on standard SQL.

Development:

Normally use a string to save true false

INT 0false 1---"trues 2.3.1.4, Java---mysql character and string comparison: (emphasis)

Java

Mysql

Char

Char/varchar

String

Char/varchar

In MySQL, characters are the same as strings, and can be stored with char and varchar.

The difference between char and varchar:

Char

Varchar

Char can specify a length when created, or it can be unspecified, default is 1

varchar must specify length when creating, otherwise error

Char is a fixed-length string that, after a specified length,

If the string is less than the specified length, the trailing space is padded at the time of storage to reach the specified length before saving. (When retrieved, the retrieved char value removes trailing spaces).

cannot be stored beyond the specified length

VARCHAR is a variable-length string that, after a specified length,

If the string is less than the specified length, storage is stored normally and the trailing space is not filled.

cannot be stored beyond the specified length

Char retrieval is highly efficient, but occupies a large space.

The varchar retrieval efficiency is very low, but because space is not filled, the footprint is small.

According to the actual situation, biased query efficiency with char, biased to occupy small space with varchar 2.3.1.5, Java---mysql date comparison:

This class is available in the JDK API.

Java.util.Date

Java

Mysql

Java.sql.Date

Date

Only save Date 2015-07-07

Default is NULL

If you forcibly save the date and time, seconds and seconds will not be saved.

For example: Save 2015-07-07 15:49:55

Date type will only be credited to 2015-07-07

Java.sql.Time

Time

Can only be saved when minutes minutes minutes 15:49:55

Default is NULL

If the day of the month is forcibly saved, the date will not be saved.

For example: Save 2015-07-07 15:49:55

The time type is only credited to 15:49:55

Java.sql.TimeStamp

Timestamp

It represents a time stamp.

2015-07-07 15:49:55.

If you do not set a value or add null directly, the default is the current system time.

(Timestamp valid date 1970-01-01 00:00:00 ~ 2037-12-31 23:59:59)

Datetime

The save format is 2015-07-07 15:49:55.

The default is null.

Timestamp and datetime differences:

1, timestamp default value is the current system date, the datetime default value is NULL

2, timestamp valid date is 1970-01-01 00:00:00 ~ 2037-12-31 23:59:59, beyond this category cannot save; datetime unlimited 2.3.1.6, MySQL Big data type:

In SQL syntax:

L BLOB-----indicates binary

Tinyblob (255) blob (65536) Mediumblob (16m) Longblob (4g)

L Text----Large text type in Oracle it's called CLOB.

Tinytext (255) text (65536) mediumtext (16m) Longtext (4g) 2.3.1.7, common data types in MySQL:

Type

Describe

Int

Integral type, as in Java, the type of int;

Double

A floating-point type, such as double (5,2), indicates that the column holds up to 5 bits of data, and that it must have 2 decimal places, the maximum value of which is: 999.99;

Decimal

Floating-point type, which holds values that are important for accurate precision, such as money-related data.

Char

Fixed length string type, if the length is not specified, the default length is 1, if the stored characters do not reach the specified length, MySQL will be followed by a space to the specified length;

varchar

Variable length string type, its length can be specified by ourselves, it can hold the maximum value of the length of the data is 65535, if the stored characters do not reach the specified length, will not be filled to the specified length;

Tinytext

A variable-length string type whose storage range is 28-1b;

Text

is also a variable-length string type, whose storage range is 216-1b;

Mediumtext

is also a variable-length string type, whose storage range is 224-1b;

Longtext

is also a variable-length string type, whose storage range is 232-1b;

Tinyblob

is a variable-length binary type whose storage range is 28-1b;

Blob

is a variable-length binary type whose storage range is 216-1b;

Mediumblob

is also a variable-length binary type, its storage range is 224-1b;

Longblob

is also a variable-length binary type, its storage range is 232-1b;

Date

Date type, format YYYY-MM-DD, only month day, no time seconds;

Time

The time type, the format is HH:MM:SS, only then seconds, no month day;

Timestamp

Timestamp type in the format: YYYY-MM:DD hh:mm:ss, Month day, time and seconds. 2.3.1.8, Practice

Create a user table

ID integral type

Username String length 20

Password string length 20

Gender string Length 10

Email string Length 50

Telephone string length 20

Introduce string length 100

Activecode String Length 50

State integral type

Role String Length 10

Registtime time Stamp

Implementation code:

CREATE TABLE User (

ID int,

Username varchar (20),

Password varchar (20),

Gender varchar (10),

Email varchar (50),

Telephone varchar (20),

Introduce varchar (100),

Activecode varchar (50),

State int,

Role varchar (10),

Registtime Timestamp

); 2.3.2, creating data tables

1. Create a table---Use the default character set and proofing

CREATE TABLE Table name (

Field 1 type,

Field 2 Type,

.....

);

2. Creating tables-Specifying character sets and proofing

CREATE TABLE Table name (

Field 1 type,

Field 2 Type,

.....

) Character set character set;

The content after the table name needs to be enclosed in "()", the contents of which are the table structure, consisting of the column name and column type, separated by a space between the column name and the column type, separated by commas between each column, and the last column needs to be added ";" After the end of the statement. 2.3.3, creating Data table constraints (important)

It is used to qualify the fields in the table.

We'll cover 5 constraints in the MySQL course (note: There are six constraints in the database, while MySQL supports only five) constraints supported in SQL:

L PRIMARY KEY constraint

L Non-null constraint

L UNIQUE Constraint

L DEFAULT Constraints

L CHECK constraint (MySQL not supported) 2.3.3.1, PRIMARY KEY constraint

The primary key is the only field used to identify the current record.

It is characterized by non-null, unique.

In development, the primary key does not have any meaning, but is used to identify the current record.

To create a primary key:

1. When creating the table, add primary key after the field.

To create a single primary key:

CREATE TABLE TableName (

ID int PRIMARY KEY,

.......

)

2. You can also specify the primary key at the end of the table creation

Creating a federated Primary key: CREATE TABLE TableName (

ID int,

Name varchar (20),

Primary KEY (Id,name)

)

Delete PRIMARY key: ALTER TABLE TableName drop PRIMARY key;

One table with up to one primary key 2.3.3.2, UNIQUE constraint

The value at the top of the column is not duplicated. Add unique behind

Example: Username varchar (a) unique

Note: null values do not participate in uniqueness judgments

Delete: ALTER TABLE name DROP INDEX field name 2.3.3.3, non-null constraint

The value at the top of the column cannot be empty. After plus not null

Example: Username varchar () NOT NULL 2.3.3.4, default value constraint

Sets the default value for the current column. followed by the default value

Example: Utype varchar (default "VIP")

Ignoring the current column will trigger the default value constraint 2.3.3.5, the primary key auto-grow

Sets the auto-grow policy for unique primary key values.

Add auto_increment at the back.

Attention:

L must be an integral type and must be unique. (primary KEY constraint or UNIQUE constraint)

When the primary key value is manually added successfully, the automatic value is set to the manual value if the manually added value is greater than the automatic value, and the automatic value does not change if the manual value is less than the automatic value.

Primary key auto-growth policy is triggered by ignoring the primary key or by actively setting a null value

Extended:

Mysql:auto_increment Automatic growth

Sqlserver:identity (a)

Oracle:sequence sequence 2.3.3.6, exercise

2.3.4, viewing Data sheets

L View Table structure

DESC table name;

L View all tables under the current database

Show tables;

L. Viewing the character encoding set of a table

Show create table table name; 2.3.5, modifying data tables (structure)

L Modify the structure of a table

Syntax format: ALTER TABLE table name keyword ...;

The following keywords are available:

1.add----ADD column operations ALTER TABLE name add column name type;

2.modify--Modify the type of the column ALTER TABLE name modify column name type;

3.drop--Delete column ALTER TABLE name drop column name.

4.change-changing column names and types ALTER TABLE name change old column name new column name type;

L Modify the name of the table

Rename table name to new table name;

L Modify the character encoding set of a table

ALTER TABLE name character set character set;

Practice

An image column is basically added to the employee table above.

ALTER TABLE user add image blob;

Modify the job column so that it has a length of 60.

ALTER TABLE user Modify job varchar (60);

Delete the gender column.

ALTER TABLE user drop gender;

The table name is changed to Employe.

Rename table employee to Employe;

Modify the table's character set to UTF8

ALTER TABLE employee character set UTF8;

Column Name name modified to username

ALTER TABLE employee Change Column name username varchar (20);

ALTER TABLE employee change name username varchar (20); 2.3.6, deleting data tables

drop table name; 2.4. Database table Data Operations (DML) 2.4.1, insert table data (insert) 2.4.1.1, format

L Specify Column Name:

Insert into table name (column name 1, column name 2) VALUES (column value 1, column value 2);

Ignoring some fields does not write.

You can customize the field insertion Order

The table name is followed by some field names in the current table

L do not specify a column name:

Insert into table name values (column value 1, column value 2, ...);

L do not give the column to be inserted, then all columns are inserted sequentially;

The number of L values must be the number of columns in the table one by one corresponding to 2.4.1.2, insert operation considerations

The data inserted should be the same as the data type of the field

L The size of the data should be within the range of the column length

The field names listed in values must correspond to the field value one by one

L. In addition to numeric types, the values of the other field types must be enclosed in single quotation marks.

L If you want to insert a null value, you can omit the write or insert null.

Note: For auto-growing columns, insert null values directly in the operation.

For the timestamp type, insert the null value directly to insert the current system time. 2.4.2.1, format

General format:

Update table name set field 1 name = value, field 2 name = value,...;

Note: The above will modify the field values in all records. Generally not used in development. and the actual development of the modification operation is conditional modification

Conditional modification:

Update table name set field 1 name = value, field 2 name = value,... where condition. 2.4.2.2, Practice

Exercise: Modify the information in the user table

1. The password of everyone is changed to "456"

2. Change the name of the user named Tom to [email protected]

3. Change Tom's gender to "female" and introduce change to "Good girl"

Implementation code:

1.

Update user set password= ' 456 ';

2.

Update user set email= ' [email protected] ' where username= ' Tom ';

3.

Update user set gender= ' female ', introduce= ' good girl ' where username= ' Tom '; 2.4.3, delete table data (delete) 2.4.3.1, format

General format:

Delete from table name.

Note: All records above will be deleted. Generally conditional removal in development

Conditional Delete:

Delete from table name where condition. 2.4.3.2, delete operation considerations

Thinking:

The difference between the following delete operations:

DROP table Name

Delete from table name

TRUNCATE TABLE name

The drop table table name--------Delete the tables structure.

Delete from table name----------Remove table record (article by article)

TRUNCATE TABLE name---------Delete table structure and recreate table structure

Written questions:

The difference between delete and truncate:

1, delete is deleted, the entry is more inefficient

Truncate is re-created after the table structure has been deleted, which is more efficient for more entries.

2, delete is a DML statement, the data can be retrieved. (Rollback by transaction)

Truncate and drop are DDL statements that cannot be retrieved after data is deleted. (Unable to rollback TRANSACTION)

3. The DELETE statement does not initialize auto_increment.

Truncate will initialize the auto_increment. 2.4.4, coding problems

Here we insert a record into the user table

Insert into User (Username,password) VALUES (' Zhang San ', ' 123 ');

When executing this SQL, an error will be

ERROR 1366 (HY000): Incorrect string value: ' \xd5\xc5\xc8\xfd ' for column ' username ' at row 1

Reason:

Because the problem with MySQL's client code is UTF8, and the system's CMD window code is GBK

Solution (Temporary solution):

Modify the MySQL client code to do so.

Show variables like ' character% '; View all MySQL codes

The encoding settings related to the client in the diagram:

Client connetion result is related to clients,

Database server system and server-side related

Modify the client code to GBK.

Set CHARACTER_SET_RESULTS=GBK; /set names GBK;

The above operation, only for the current window has effect, if the server is closed, invalid.

If you want to permanently modify it, use the following method:

There are my.ini files in the MySQL installation directory

DEFAULT-CHARACTER-SET=GBK Client encoding Settings

Character-set-server=utf8 server-side encoding settings

Note: Modify complete configuration file, restart Service 2.4.5, query table data (SELECT) 2.4.5.1, format 2.4.5.1.1, query all field data

SELECT * from table name;

Note: Use "*" in the practice, the learning process can be used, in actual development, is not recommended to use. Can seriously consume performance and read inconvenient 2.4.5.1.2, query the specified field

Select field 1, Field 2,... from table name; 2.4.5.1.3, remove duplicate records

Select distinct field from table name;

Distinct its role is to remove repetition. 2.4.5.1.4, using aliases

Field name as Alias

Use the As alias to set aliases for the fields in the table.

The structure in the table is not modified.

As can omit 2.4.5.1.5, column operations in queries

Field + arithmetic symbol + operand

Example: sal+100

No effect on the data itself in the table 2.4.5.1.6, ifnull function (filter null function)

When doing operations on a column of numeric types, if the value of the column being evaluated is NULL, the result of the operation is null, and the Ifnull function can be used to solve the problem.

Ifnull (the field name that needs to be filtered, which needs to be replaced by null)

The filter empty function does not affect the table data, only the display effect is manipulated

For example: Ifnull (sal,0) means that if a field Sal is a record that is null, it returns 2.4.5.2 as 0, where clause

In development, we use select operations, which are generally conditional queries, so let's introduce the use of the WHERE clause 2.4.5.2.1, format

Select field from table name where condition; 2.4.5.2.2, where clause condition category

1. Comparison operators

>

>=

<

<=

=

! = can be replaced with <>

2. Logical operators

and conditional intersection

or conditional and set

The not condition takes the inverse

Not very low efficiency

3. Between...and ...

Interval value

Equivalent to >= and <=

Note: Between small value and large value

SELECT * from pro where id>=2 and id<=9;

SELECT * from pro where ID between 2 and 9;

The upper and lower sentences are exactly equivalent

4, in

You can compare multiple values

equal to or OR or ...

SELECT * from pro where id=1 or id=3 or id=5 or id=7 or id=9;

SELECT * from pro where ID in (1,3,5,7,9);

The upper and lower sentence effect is completely equivalent, the efficiency in the higher

5. Null value operation

is null; Judged to be empty

is not null; Judge not Null (the Not field is null)

The null value in the database cannot be compared using the = sign

6. Like

Fuzzy query

Wildcard use:

1.% matches All (0~ multiple arbitrary characters)

2._ matches an arbitrary character 2.4.5.2.3, exercise

1. Check all Computer Products information

2. Check the product information for goods with a price greater than 90

3. Check the product information of the product price greater than 10000

4. Check the product information of the price between 100-200

5. Check the commodity price is 65,100 or 190 of the product information

6. The name of the product to be queried contains the Java product information.

7. Find out the title is two words of the product information

8. Check the product price is not NULL product information

Code implementation:

1, select * from the products WHERE category= "Computer";

2. SELECT * FROM Products WHERE price>90;

3. SELECT * FROM Products WHERE (price*pnum) >10000

4. SELECT * FROM Products WHERE price>=100 and price<=200;

SELECT * FROM Products WHERE price between and 200;

5, SELECT * from the products WHERE price=65 or price=100 or price=190;

SELECT * FROM Products WHERE price in (65,100,190);

6, SELECT * from the products WHERE the NAME like "%java%";

7, SELECT * from the products WHERE the NAME like "__";

8, SELECT * from the products WHERE the price was not NULL;

SELECT * from the products WHERE isn't price is NULL; 2.4.5.3, order by sort 2.4.5.3.1, format

In development, the data we query from the database often needs to be sorted according to some fields, using the order by keyword, followed by the column to be sorted

Format:

Order BY field name ASC Ascending (default)

Order BY field name desc Descending

Sort multiple fields:

Order BY field name 1 desc, field name 2 ASC.

In descending order of field 1, if field 1 values are equal, the equivalent data is sorted in ascending order by field 2

Note: The ORDER BY clause is the last clause of SELECT. 2.4.5.3.2, Practice

1. Check out all items and sort them in ascending order according to Price

2. Check out all goods, sorted in ascending order according to quantity, if the quantity is the same, according to the price descending order

Code implementation:

1. SELECT * FROM Products ORDER by Price ASC;

2. SELECT * from the products ORDER by Pnum ASC, Price DESC; 2.4.5.4, grouping functions (aggregate functions)

grouping function aka aggregation function, aggregation function

The queries we made before are all horizontal queries, which are judged by a row of criteria, and the aggregate function query is a vertical query that evaluates the values of a column and returns a single value, and the aggregate function ignores null values.

Today we study the following five aggregate functions:

When using grouping functions, NULL does not participate in grouping function operations and must be used in conjunction with the Ifnull function

L Count: Counts the number of record rows that specify columns that are NOT null;

L Sum: Calculates the value of the specified column and, if the specified column type is not a numeric type, evaluates to 0, or null if it is omitted;

L Max: Calculates the maximum value of the specified column, and if the specified column is a string type, the string sort operation (string ordering is meaningless) and, if NULL, is omitted;

L min: Calculates the minimum value of the specified column and, if the specified column is a string type, uses the string sort operation (the string sort is meaningless);

L AVG: Calculates the average of the specified column, if the specified column type is not a numeric type, evaluates to 0, or if the value is null, then ignores;

Sum,max,min,avg are used to operate on numeric types, and no such operations are performed on non-numeric types in development

Think Exercise:

1. Number of records in the Statistics commodity table

SELECT COUNT (*) from the products;

2. Statistics on the number of items in the commodity table with prices greater than 50

SELECT COUNT (*) from the products WHERE price>50;

3. Statistics on the total number of all commodities

SELECT SUM (Pnum) from the products;

4. Statistics on the total value of all commodities

SELECT SUM (Pnum*price) from the products;

SELECT SUM (Pnum*ifnull (price,0)) from products

5. Statistics on the average price of all commodities

SELECT SUM (Pnum*price)/sum (Pnum) from the products;

6. Calculate the average of the price in the record

SELECT AVG (price) from the products;

7. Calculate the price maximum and minimum value in the commodity table

SELECT MAX (Price), MIN (price) from the products;

SELECT MAX (Price), MIN (Ifnull (price,0)) from products; 2.4.5.5, group query

A group query is a grouping of data records based on a field at query time, with the same field values grouped together. 2.4.5.5.1, format

Group By field name

The records that are queried are grouped by the specified fields, and the same field values are divided into a group. 2.4.5.5.2, HAVING clause

Filter filtered data after grouping, similar to where

Group BY field name having condition;

Day04 about Mysql-android's small white study notes