MySQL Command Learning (2)

(13) where Operator

= Equal

<> Not equal

! = Not equal

<Less

> Greater

> = Greater than or equal

BETWEEN is BETWEEN two specified values.

For example:

Find the Student Records Between 0007 and 0009 (excluding 0009 )?

SELECT * FROM student_info WHERE stu_id BETWEEN 1001101620007 AND 1001101620009;

(14)AndOperator

To filter by more than one column, you can use the and operator to add conditions to where statements.

For example:

Find out the student records of male students whose major is "Software Engineering" and whose gender is gender?

SELECT * FROM student_info WHERE stu_sex = 'male' AND stu_major = 'Software project ';

(15)OROperator

Indicates MySQL to retrieve rows matching any condition

For example:

Find out all student records whose major is "network engineering" and "electronic information?

SELECT * FROM student_info WHERE stu_major = 'electronic information' OR stu_major = 'network project ';

Find the student records of all boys in "network engineering" and "Software Engineering?

SELECT * FROM student_info WHERE (stu_major = 'network project' OR stu_major = 'Software Project') AND stu_sex = 'male ';

(16)INOperator

Is used to specify the condition range. Each condition IN the range can be matched. The list of valid values of IN is separated by commas (,), and all values are enclosed IN parentheses.

For example:

Find the student records for the specified three student IDs?

SELECT * FROM student_info WHERE stu_id IN (1001101620007,1001101650018, 1001101650019 );

(17) NOTOperator

The function is to deny any conditions followed by it.

For example:

Find out the student records that are not "network engineering" or "Software Engineering?

SELECT * FROM student_info WHERE stu_major not in ('Software Project', 'network Project ');

(18)LIKEOperator

Special characters used to match a part of a value

% Wildcard indicates the number of times any character appears

_ Wildcard indicates that any character appears only once

For example:

Find out all student records with the last two words "engineering" in the professional name?

SELECT * FROM student_info WHERE stu_major LIKE '% project ';

Find the student record with the surname "Wang" and the name is two words?

SELECT * FROM student_info WHERE stu_name LIKE 'wang _';

Appendix: statements used to create an example table

CREATETABLE MERs (

Cust_id int not null AUTO_INCREMENT,

Cust_name char (50) not null,

Cust_address char (50) NULL,

Cust_city char (50) NULL,

Cust_state char (5) NULL,

Cust_zip char (10) NULL,

Cust_country char (50) NULL,

Cust_contact char (50) NULL,

Cust_email char (255) NULL,

Primary key (cust_id)

)

CREATETABLE orders (

Order_num int not null AUTO_INCREMENT,

Order_date datetime not null,

Cust_id int not null,

Primary key (order_num)

)

CREATETABLE vendors (

Vend_id int not null AUTO_INCREMENT,

Vend_name char (50) not null,

Vend_address char (50) NULL,

Vend_city char (50) NULL,

Vend_state char (5) NULL,

Vend_zip char (10) NULL,

Vend_country char (50) NULL,

Primary key (vend_id)

)

CREATETABLE orderitems (

Order_num int not null,

Order_item int not null,

Prod_id char (10) not null,

Quantity int not null,

Item_price decimal (8, 2) not null,

Primary key (order_num, order_item)

)