本文主要参考自:SQL简明数据分析教程 及 MySQL---数据库从入门走向大神系列(一)-基础入门
一 创建与检索
create database testbase character set utf8; 创建数据库
use testbase; 使用该数据库
create table mobile(
num varchar(15) not null primary key,
name varchar(15) not null,
price int ); 创建表
insert into mobile(num, name, price) values('1','p20','3500'); 插入数据
insert into mobile values('2','m20','4000');
insert into mobile values('3','mg2','4500');
insert into tableA(columnA, columnB) select columnC, columnD from tableB;
插入检索出的数据:将表B的C D列数据复制到表A的 A B列
select * from mobile; 检索
select * from mobile order by name; 按name排序
select distinct name,price from mobile order by num desc; 降序desc,升序asc
select distinct num from mobile limit 1,2;
1表示取第2行(从0起),2表示共取2行
select * from mobile where num > '1' order by price desc limit 2;
取num>1中降序排列的前2行
select * from mobile where price between 4000 and 4500;
取price大于等于4000且小于等于4500
或:select * from mobile where price>=4000 and price<=4500;
select * from mobile where price in (4000 ,4500);
取price等于4000和 4500。 not in 指不等于
select * from phone where name like '%20';
模糊检索,'%20':以20结尾的; '20%':以20开头的; '%20%'