本文共 6621 字，大约阅读时间需要 22 分钟。
| 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); -- 创建products表 create table products ( prod_id char(40), prod_name char(40) not null, pro_price int not null, primary key(prod_id) )engine = innodb; -- 检索单个列 select prod_name from products; -- 检索多个列 select prod_id,prod_name,prod_price from products; -- 检索所有列 select * from products; -- 返回所有指定的行 select ven_id from ven; -- 返回行中不同的值 select distinct vend_id from ven; -- 返回开始到第五行 select prod_name from products limit 5; -- 返回从第五行开始的五行 select prod_name from products limit 5,5; -- 从第3行开始取4行 select prod_name from products limit 4 offset 3; -- 假设products表在ven表里面 select products.prod_name from ven.products; -- 升序 select prod_name,pro_price,prod_id from products order by prod_name,pro_price; -- 降序 select prod_name,pro_price,prod_id from products order by pro_price desc; -- desc之前的降序之后的升序 select prod_name,pro_price,prod_id from products order by pro_price desc,prod_name; -- 检索降序并输出一行 select pro_price,prod_id from products order by pro_price desc limit 1; -- 检索两个列并判断prod_price为3的行 select prod_name from products where prod_price = 3; -- 检索两个列并判断prod_name为fuses的行,不区分字母大小写 select prod_name,pro_price from products where prod_name ='Fuses'; -- 检索两个列并判断prod_price小于10的行 select prod_name,pro_price from products where pro_price < 10; -- 检索两个列并判断prod_price小于等于10的行 select prod_name,pro_price from products where pro_price <= 10; -- 检索三列并prod_id判断不等于jp1000的行 select prod_id,prod_name,pro_price from products where prod_id <> 'jp1000'; -- 与上面相同 select prod_id,prod_name,pro_price from products where prod_id != 'jp1000'; -- 检索pro_price 5到10之间的所有产品 select prod_name,pro_price from products where pro_price between 5 and 10; -- 检索pro_price为空的所有产品 select prod_id,prod_name,pro_price from products where pro_price is null; -- 创建customers表 create table customers ( cust_id int, cust_email char(50) null, primary key(cust_id) )engine = innodb; -- 向customers表cust_id列插入数据 insert into customers(cust_id)values(1002); -- 向customers表cust_id列插入数据 insert into customers(cust_id)values(1003); -- 检索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; -- 添加vend_id列 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); |
| 深入理解： select * from student; ------------------------------------------ select t.sid+1 'tsid加了1', t.sid+5, t.score+100, t.sid+t.score as 'sid_score', t.*, 5*8 '五'from student t; ------------------------------------------ select t.sid, t.sname, -- 设置别名 from student t; ------------------------------------------ select t.sid, t.sname, -- 常量列 5, from student t; ------------------------------------------ select -- sid列数据加一，并创建一个t.sid+1列的数据 t.sid +1, t.sname, t* from student t; ------------------------------------------ select -- 两个整型数据相加，得出t.score+t.ccid列名的相加数据 t.score+t.ccid from student t; ------------------------------------------ select -- 'ni'列名 t.score 'ni'， -- 'why'列名 t.ccid 'why', -- 'you'列名 t.score+t.ccid 'you' from student t; ------------------------------------------ select -- 整型和字符类型数据相加，得出t.score+t.sname列名的t.score数据(字符类型和整型数据相加，结果也是一样，以整型为主) t.score+t.sname from student t; ------------------------------------------ select -- 改为'我'列名 t.score '我' from student t; ------------------------------------------ select -- '加'列名 t.score '加'， -- ‘王’列名 5 ‘王’ from student t; ------------------------------------------ select -- '加'列名 t.score '加'， 5 from student t -- 常数列名不能相加（还是‘加’列名数据，5列名数据） where t.score +5;
------------------------------------------ select s.score+5 'q',s.score+s.ccid,5 '我' from student s; ------------------------------------------ select -- '加'列名 t.ccid '加', -- 常数列名 5 , -- t.ccid+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 60 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); 本文转自 风雨萧条 博客，原文链接： http://blog.51cto.com/1095221645/1433344 如需转载请自行联系原作者 |