数据库之基本查询示例（一）

#创建数据表
mysql> create table fruits ( f_id char(10) not null, s_id int not null, f_name char(255) not null, f_price decimal(8,2) not null, primary key(f_id) );
#插入数据
mysql> insert into fruits(f_id,s_id,f_name,f_price)
    -> values('a1',101,'apple','5.2'),
    -> ('b1',101,'blackberry','10.2'),
    -> ('bs1',102,'orange','11.2'),
    -> ('bs2',105,'melon','8.2'),
    -> ('t1',102,'banana','10.3'),
    -> ('t2',102,'grape','5.3'),
    -> ('o2',103,'coconut','9.2'),
    -> ('c0',101,'cherry','3.2'),
    -> ('a2',103,'apricot','2.2'),
    -> ('l2',104,'lemon','6.4'),
    -> ('b2',104,'berry','7.6'),
    -> ('m1',106,'mango','15.7'),
    -> ('m2',105,'xbabay','2.6'),
    -> ('t4',107,'xbababa','2.6'),
    -> ('m3',105,'xxtt','11.6'),
    -> ('b5',107,'xxxx','3.6');
#创建第二个表
mysql> create table customers(
    -> c_id int not null auto_increment,
    -> c_name char(50) not null,
    -> c_address char(50) null,
    -> c_city char(50) null,
    -> c_zip char(50) null,
    -> c_contact char(50) null,
    -> c_email char(50) null,
    -> primary key(c_id)
    -> );
#第二张表插入数据
mysql> insert into customers(c_id,c_name,c_address,c_city,c_zip,c_contact,c_email)
    -> values(10001,'RedHook','200 Street','Tianjin','300000','LiMing','LMing@163.com'),
    -> (1002,'Stars','333 Fromage Lane','Dalian','116000','Zhangbo','Jerry@hotnail.com'),
    -> (10003,'Netbhood','1 Sunny Place','Qingdao','266000','LuoCong',NULL),
    -> (1004,'JOTO','829 Riverside Drive','Haikou','570000','YangShan','sam@hotmail.com');

mysql> select f_name from fruits;

mysql> select f_name,f_price from fruits;

mysql> select f_name,f_price from fruits where f_price=5.2;

mysql> select f_name,f_price from fruits where f_price >= 10;

mysql> select f_name,f_price from fruits where f_price between 2 and 8;

mysql> select f_name,s_id from fruits where s_id = 101 or s_id = 103;

mysql> select f_name,s_id from fruits where s_id in(101,103);

mysql> select f_name,s_id from fruits where s_id != 101 and s_id != 103;

mysql> select f_name,s_id from fruits where s_id not in(101,103);

#查询fruits表中的f_name列，并且值以“b”开头
mysql> select f_name from fruits where f_name like 'b%';

#查询fruits表中的f_name列，并且值以“b”开头，以“y”结尾
mysql> select f_name from fruits where f_name like 'b%y';

#查询fruits表中的f_name列，值以“b”开头，以“y”结尾，并且b和y之间有三个字符
mysql> select f_name from fruits where f_name like 'b___y';

mysql> select * from fruits where s_id = 101 and f_price > '2.0';

mysql> select * from fruits where s_id in(101,103) and f_price > 5;

mysql> select distinct s_id from fruits;

mysql> select s_id,f_name from fruits order by s_id;

mysql> select f_name,f_price from fruits order by f_name,f_price;

#默认是asc升序排序，可以通过关键字DESC更改为降序
mysql> select f_price from fruits order by f_price desc;

#调用count(*)函数统计次数，并通过as来对其设置别名，group by来进行分组
mysql> select s_id,count(*) as total from fruits group by s_id;

mysql> select s_id,group_concat(f_name) as name from fruits group by s_id having count(f_name) > 1;

mysql> select * from customers where c_email is null;