Database Note 6: Retrieve, sort retrieve, filter data

Insert into products (Prod_id,prod_name,pro_price) VALUES (' Avno1 ', '. 5 ton Anvil ', 5.99); Insert into products (Prod_id,prod_name,pro_price) VALUES (' Avno2 ', ' 1 Ton Anvil ', 9.99); Insert into products (Prod_id,prod_name,pro_price) VALUES (' Avno3 ', ' 2 Ton Anvil ', 14.99); Insert into products (Prod_id,prod_name,pro_price) VALUES (' ol1 ', ' oil Can ', 8.99); Insert into products (Prod_id,prod_name,pro_price) VALUES (' fu1 ', ' fuses ', 3.42); Insert into products (Prod_id,prod_name,pro_price) VALUES (' Slite ', ' sling ', 4.49); Insert into products (Prod_id,prod_name,pro_price) VALUES (' Tnt1 ', ' TNT (1 stick) ', 2.50); Insert into products (Prod_id,prod_name,pro_price) VALUES (' Tnt2 ', ' TNT (5 stick) ', 10.00); Insert into products (Prod_id,prod_name,pro_price) VALUES (' fb ', ' bird Seed ', 10.00); Insert into products (Prod_id,prod_name,pro_price) VALUES (' fc ', ' carrots ', 2.50); Insert into products (Prod_id,prod_name,pro_price) VALUES (' safe ', ' safe ', 50.00); Insert into products (Prod_id,prod_name,pro_price) VALUES (' Dtntr ', ' detonator ', 13.00); Insert into products (Prod_id,prod_name,pro_price) VALUES (' jp1000 ', ' jetpack 1000 ', 35.00); Insert into products (Prod_id,prod_name,pro_price) VALUES (' jp2000 ', ' Jetpack 2000 ', 55.00); --Create a Products table CREATE TABLE Products ( prod_id Char (40), Prod_name char (+) NOT NULL, Pro_price int NOT NULL, Primary KEY (PROD_ID) ) engine = InnoDB; --Retrieving a single column Select Prod_name from Products; --Retrieving multiple columns Select Prod_id,prod_name,prod_price from Products; --Retrieve all columns SELECT * FROM Products; --Returns all the specified rows Select ven_id from Ven; --Returns the different values in the row Select distinct vend_id from Ven; --Return to line fifth Select Prod_name from Products limit 5; --Returns the five elements starting from line fifth Select Prod_name from Products limit 5, 5; --Take 4 rows starting from line 3rd Select Prod_name from Products limit 4 offset 3; --assuming that the Products table is inside the VEN table Select Products.prod_name from Ven.products; --Ascending Select prod_name,pro_price,prod_id from the products order by Prod_name,pro_price; --Descending Select prod_name,pro_price,prod_id from the products order by pro_price Desc; --The ascending order of Desc before descending Select prod_name,pro_price,prod_id from the products order by Pro_price Desc,prod_name; --Retrieves the descending order and outputs a row Select pro_price,prod_id from Products ORDER BY pro_price desc LIMIT 1; --Retrieve two columns and determine the row with Prod_price 3 Select Prod_name from Products where prod_price = 3; --Retrieves two columns and determines the rows prod_name to fuses, without distinguishing the letter case Select Prod_name,pro_price from products where prod_name = ' fuses '; --Retrieving two columns and judging the rows with prod_price less than 10 Select Prod_name,pro_price from Products where Pro_price < 10; --Retrieve two columns and determine prod_price less than or equal to 10 rows Select Prod_name,pro_price from Products where Pro_price <= 10; --Retrieve three columns and prod_id to determine not equal to jp1000 rows Select Prod_id,prod_name,pro_price from Products where prod_id <> ' jp1000 '; -the same as above Select Prod_id,prod_name,pro_price from Products where prod_id! = ' jp1000 '; --Retrieve all products from Pro_price 5 to 10 Select Prod_name,pro_price from Products where pro_price between 5 and 10; --Retrieve all products with empty Pro_price Select Prod_id,prod_name,pro_price from products where pro_price is null; --Create a Customers table CREATE TABLE Customers ( cust_id int, Cust_email char () NULL, Primary KEY (CUST_ID) ) engine = InnoDB; --insert data into the Customers table cust_id column INSERT into Customers (CUST_ID) values (1002); --insert data into the Customers table cust_id column INSERT into Customers (CUST_ID) values (1003); --Retrieve all products with empty Cust_email Select cust_id from Customers where cust_email is null; CREATE TABLE Ven ( ven_id int Auto_increment, Primary KEY (VEN_ID) ) engine = InnoDB; --Add vend_id column ALTER TABLE ven add vend_id int; INSERT into Ven (vend_id) values (1001); INSERT into Ven (vend_id) values (1001); INSERT into Ven (vend_id) values (1001); INSERT into Ven (vend_id) values (1002); INSERT into Ven (vend_id) values (1002); INSERT into Ven (vend_id) values (1003); INSERT into Ven (vend_id) values (1003); INSERT into Ven (vend_id) values (1003); INSERT into Ven (vend_id) values (1003); INSERT into Ven (vend_id) values (1003); INSERT into Ven (vend_id) values (1003); INSERT into Ven (vend_id) values (1003); INSERT into Ven (vend_id) values (1005); INSERT into Ven (vend_id) values (1005);

Deep Understanding: SELECT * from student; ------------------------------------------ Select T.sid+1 ' TSID plus 1 ', T.sid+5, T.SCORE+100, T.sid+t.score as ' Sid_score ', T.*, 5*8 ' five ' from student t; ------------------------------------------ Select T.sid, T.sname, --Set aliases from student t; ------------------------------------------ Select T.sid, T.sname, --Constant column 5, from student t; ------------------------------------------ Select --SID column data plus one, and create a t.sid+1 column of data T.sid +1, T.sname, t* from student t; ------------------------------------------ Select --Two integer data added, resulting in T.SCORE+T.CCID column name addition data T.score+t.ccid from student t; ------------------------------------------ Select --Integer and character type data are added, the T.score data (character type and integer data are added, and the result is the same as the integer type) for t.score+t.sname column name T.score+t.sname from student t; ------------------------------------------ Select --Change to ' I ' list name T.score ' Me ' from student t; ------------------------------------------ Select S.score+5 ' Q ', s.score+s.ccid,5 ' I ' from student s; Select T.ccid ' Plus ', 5, T.ccid + 5 from student t; ------------------------------------------ SELECT * FROM student t where t.score>70 ORDER BY t.score desc LIMIT 5; ------------------------------------------ Select s.*,1 ' A ', 2 ' B ', 3 ' C ' from student s where s.score+1=s.sid+87 and s.score between and 95 ORDER BY Sid DESC LIMIT 1; ------------------------------------------ SELECT * FROM Student t where T.score not in (60,90,85); ------------------------------------------ SELECT * FROM Student Where sname is not null; ------------------------------------------ SELECT * from student; ------------------------------------------ CREATE TABLE Student ( ID int NOT NULL auto_increment, Primary KEY (ID) ) engine = InnoDB; ------------------------------------------ ALTER TABLE student Add sname char (40); ALTER TABLE student add score int; ALTER TABLE student add CCID int; ------------------------------------------ INSERT into student (SNAME,SCORE,CCID) VALUES (' WPQ2 ', 93, 1); INSERT into student (SNAME,SCORE,CCID) VALUES (' wpq3 ', 45, 1); INSERT into student (SNAME,SCORE,CCID) VALUES (' QQ5 ', 43, 1); INSERT into student (SNAME,SCORE,CCID) VALUES (' Wp9 ', 60, 1); INSERT into student (SNAME,SCORE,CCID) VALUES (' W ', 99, 1); INSERT into student (SNAME,SCORE,CCID) VALUES (' W123 ', 91, 1);

This article is from the "Misty Life" blog, please be sure to keep this source http://1095221645.blog.51cto.com/6507311/1433344