MySQL command Learning (ii)

() WHERE clause operator

= equals

<> Not equal to

! = does not equal

< less than

> Greater than

>= greater than or equal to

Between between the specified two values

For example:

Find the student number from 0007 to 0009 (not including 0009)?

select* from Student_info WHERE stu_id between 1001101620007 and 1001101620009;

(14) and operator

To filter by more than one column, you can use the AND operator to append conditions to the WHERE clause

For example:

Find out if the majors are "software engineering" and gender is a student record for boys?

select* from student_info WHERE stu_sex = ' man ' and stu_major = ' Software engineering ';

(15) OR operator

Instructs MySQL to retrieve rows that match either condition

For example:

Find out which majors are all student records for "Web Engineering" and "electronic information"?

select* from student_info WHERE stu_major = ' Electronic information ' OR stu_major = ' Network engineering ';

Find the student records for both "Network Engineering" and "software Engineering" for all boys?

select* from Student_info WHERE (stu_major = ' Network engineering ' OR stu_major = ' Software engineering ') and Stu_sex = ' male ';

(16) in operator

is used to specify the condition range, each condition in the range can be matched, and in the comma-delimited list of valid values, all enclosed in parentheses.

For example:

Find out the student records for a specific three school number?

select* from Student_info WHERE stu_id in (1001101620007,1001101650018,1001101650019);

(17) Not operator

The function is to deny it after any conditions

For example:

Find a student record that is not a "network engineering" or "software engineering" two majors?

select* from Student_info WHERE stu_major not in (' Software engineering ', ' network Engineering ');

(18) Like operator

Special characters used to match part of a value

The% wildcard indicates any number of occurrences of any character

_ wildcard indicates that any character appears and only once

For example:

Find out the professional name after the two words are "engineering" all student records?

SELECT * from Student_info WHERE stu_major like '% works ';

Find a student record with the surname "King" and a two-word name?

select* from Student_info WHERE stu_name like ' king _ ';

Appendix: Statements to create an example table

CreateTable Customers (

cust_id int not NULL auto_increment,

Cust_name char () not NULL,

Cust_address char () NULL,

Cust_city char () NULL,

Cust_state char (5) NULL,

Cust_zip Char (Ten) NULL,

Cust_country char () NULL,

Cust_contact char () 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 () not NULL,

Vend_address char () NULL,

Vend_city char () NULL,

Vend_state char (5) NULL,

Vend_zip Char (Ten) NULL,

Vend_country char () NULL,

PRIMARY KEY (vend_id)

)

CreateTable OrderItems (

Order_num int not NULL,

Order_item int not NULL,

prod_id Char (Ten) is not NULL,

Quantity int not NULL,

Item_price Decimal (8,2) is not NULL,

PRIMARY KEY (Order_num,order_item)

)