【收藏】常用SQL语句
//MySQL 5.1参考手册 CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20), species VARCHAR(20), sex CHAR(1), birth DATE, death DATE); //创建表 mysql> show tables; //查看数据库中的表 +----------------+ | Tables_in_test | +----------------+ | pet | +----------------+ 1 row in set (0.00 sec) mysql> describe pet; //查看表的结构 +---------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+-------+ | name | varchar(20) | YES | | NULL | | | owner | varchar(20) | YES | | NULL | | | species | varchar(20) | YES | | NULL | | | sex | char(1) | YES | | NULL | | | birth | date | YES | | NULL | | | death | date | YES | | NULL | | +---------+-------------+------+-----+---------+-------+ 6 rows in set (0.00 sec) //导入txt文件中的数据 LOAD DATA LOCAL INFILE 'E:\\pet.txt' INTO TABLE pet; //导入txt文件中的数据 load data local infile 'E:\\data.txt' into table pet; mysql> INSERT INTO pet -> VALUES ('Puffball','Diane','hamster','f','1999-03-30',NULL); //一次导入一条数据 Query OK, 1 row affected (0.40 sec) mysql> select * from pet where name='bowser'; //查询name=XxxxX的数据 +--------+-------+---------+------+------------+------------+ | name | owner | species | sex | birth | death | +--------+-------+---------+------+------------+------------+ | bowser | diane | dog | m | 1979-08-31 | 1995-07-29 | +--------+-------+---------+------+------------+------------+ 1 row in set (0.00 sec) mysql> select * from pet where birth>'1998-1-1'; //查询birth在1998-1-1之后的数据 +----------+-------+---------+------+------------+-------+ | name | owner | species | sex | birth | death | +----------+-------+---------+------+------------+-------+ | chirpy | gwen | bird | f | 1998-09-11 | NULL | | Puffball | Diane | hamster | f | 1999-03-30 | NULL | +----------+-------+---------+------+------------+-------+ 2 rows in set (0.00 sec) mysql> select * from pet where species='dog' and sex='f'; //组合条件的使用 +-------+--------+---------+------+------------+-------+ | name | owner | species | sex | birth | death | +-------+--------+---------+------+------------+-------+ | buffy | harold | dog | f | 1989-05-13 | NULL | +-------+--------+---------+------+------------+-------+ 1 row in set (0.00 sec) mysql> select * from pet where species='dog' or species='bird'; //or操作符，满足一个条件即可 +----------+--------+---------+------+------------+------------+ | name | owner | species | sex | birth | death | +----------+--------+---------+------+------------+------------+ | buffy | harold | dog | f | 1989-05-13 | NULL | | fang | benny | dog | m | 1990-08-27 | NULL | | bowser | diane | dog | m | 1979-08-31 | 1995-07-29 | | chirpy | gwen | bird | f | 1998-09-11 | NULL | | whistler | gwen | bird | | 1997-12-09 | NULL | +----------+--------+---------+------+------------+------------+ 5 rows in set (0.00 sec) mysql> select * from pet where (species='cat' and sex='m') //and、or混用 -> or (species='dog' and sex='f'); +-------+--------+---------+------+------------+-------+ | name | owner | species | sex | birth | death | +-------+--------+---------+------+------------+-------+ | claws | gwen | cat | m | 1994-03-17 | NULL | | buffy | harold | dog | f | 1989-05-13 | NULL | +-------+--------+---------+------+------------+-------+ 2 rows in set (0.00 sec) mysql> select name,birth from pet; //输出制定的字段内容 +----------+------------+ | name | birth | +----------+------------+ | fluff | 1993-02-04 | | claws | 1994-03-17 | | buffy | 1989-05-13 | | fang | 1990-08-27 | | bowser | 1979-08-31 | | chirpy | 1998-09-11 | | whistler | 1997-12-09 | | slim | 1996-04-29 | | Puffball | 1999-03-30 | +----------+------------+ 9 rows in set (0.00 sec) mysql> select distinct owner from pet; //对输出的字段做去重处理 +--------+ | owner | +--------+ | harold | | gwen | | benny | | diane | +--------+ 4 rows in set (0.00 sec) mysql> select name,birth from pet order by birth; //对birth排序，默认升序 +----------+------------+ | name | birth | +----------+------------+ | bowser | 1979-08-31 | | buffy | 1989-05-13 | | fang | 1990-08-27 | | fluff | 1993-02-04 | | claws | 1994-03-17 | | slim | 1996-04-29 | | whistler | 1997-12-09 | | chirpy | 1998-09-11 | | Puffball | 1999-03-30 | +----------+------------+ 9 rows in set (0.00 sec) mysql> select name,birth from pet order by birth desc; //降序排列，最小值放第一个位置 +----------+------------+ | name | birth | +----------+------------+ | Puffball | 1999-03-30 | | chirpy | 1998-09-11 | | whistler | 1997-12-09 | | slim | 1996-04-29 | | claws | 1994-03-17 | | fluff | 1993-02-04 | | fang | 1990-08-27 | | buffy | 1989-05-13 | | bowser | 1979-08-31 | +----------+------------+ 9 rows in set (0.00 sec) mysql> select name,species,birth from pet order by species,birth desc; //对多列排序，一个升序一个降序 +----------+---------+------------+ | name | species | birth | +----------+---------+------------+ | chirpy | bird | 1998-09-11 | | whistler | bird | 1997-12-09 | | claws | cat | 1994-03-17 | | fluff | cat | 1993-02-04 | | fang | dog | 1990-08-27 | | buffy | dog | 1989-05-13 | | bowser | dog | 1979-08-31 | | Puffball | hamster | 1999-03-30 | | slim | snake | 1996-04-29 | +----------+---------+------------+ 9 rows in set (0.00 sec) //计算年龄 mysql> select name,birth,curdate(), //当前日期 -> (year(curdate())-year(birth))-(right(curdate(),5)<right(birth,5)) //这里还是有一些不理解，后面慢慢研究 -> as age //字段重命名为age -> from pet; +----------+------------+------------+------+ | name | birth | curdate() | age | +----------+------------+------------+------+ | fluff | 1993-02-04 | 2016-10-30 | 23 | | claws | 1994-03-17 | 2016-10-30 | 22 | | buffy | 1989-05-13 | 2016-10-30 | 27 | | fang | 1990-08-27 | 2016-10-30 | 26 | | bowser | 1979-08-31 | 2016-10-30 | 37 | | chirpy | 1998-09-11 | 2016-10-30 | 18 | | whistler | 1997-12-09 | 2016-10-30 | 18 | | slim | 1996-04-29 | 2016-10-30 | 20 | | Puffball | 1999-03-30 | 2016-10-30 | 17 | +----------+------------+------------+------+ 9 rows in set (0.00 sec) //针对rigth做的解释案例 mysql> select curdate(); //当前日期，格式为2016-10-30 +------------+ | curdate() | +------------+ | 2016-10-30 | +------------+ mysql> select right(curdate(),5); //取后边5个字符，如此可以让年龄计算更加的精确 +--------------------+ | right(curdate(),5) | +--------------------+ | 10-30 | +--------------------+ mysql> select name,birth,curdate(), -> (year(curdate())-year(birth))-(right(curdate(),5)<right(birth,5)) -> as age -> from pet -> order by age; //按照年龄排序，age是字段的别称 +----------+------------+------------+------+ | name | birth | curdate() | age | +----------+------------+------------+------+ | Puffball | 1999-03-30 | 2016-10-30 | 17 | | chirpy | 1998-09-11 | 2016-10-30 | 18 | | whistler | 1997-12-09 | 2016-10-30 | 18 | | slim | 1996-04-29 | 2016-10-30 | 20 | | claws | 1994-03-17 | 2016-10-30 | 22 | | fluff | 1993-02-04 | 2016-10-30 | 23 | | fang | 1990-08-27 | 2016-10-30 | 26 | | buffy | 1989-05-13 | 2016-10-30 | 27 | | bowser | 1979-08-31 | 2016-10-30 | 37 | +----------+------------+------------+------+ 9 rows in set (0.00 sec) mysql> select name,birth,death, -> (year(death)-year(birth))-(right(death,5)< right(birth,5)) //计算已死亡年数 -> as age -> from pet where death is not null order by age; 抓取不为空的数据 +--------+------------+------------+------+ | name | birth | death | age | +--------+------------+------------+------+ | bowser | 1979-08-31 | 1995-07-29 | 15 | +--------+------------+------------+------+ 1 row in set (0.00 sec) mysql> select name,birth,month(birth) from pet; /出生月份 +----------+------------+--------------+ | name | birth | month(birth) | +----------+------------+--------------+ | fluff | 1993-02-04 | 2 | | claws | 1994-03-17 | 3 | | buffy | 1989-05-13 | 5 | | fang | 1990-08-27 | 8 | | bowser | 1979-08-31 | 8 | | chirpy | 1998-09-11 | 9 | | whistler | 1997-12-09 | 12 | | slim | 1996-04-29 | 4 | | Puffball | 1999-03-30 | 3 | +----------+------------+--------------+ 9 rows in set (0.01 sec) mysql> select name,birth from pet -> where month(birth)=month(date_add(curdate(),interval 2 month)); //在当前月份的基础上加2个月，得到月份是当前月份+2的数据 +----------+------------+ | name | birth | +----------+------------+ | whistler | 1997-12-09 | +----------+------------+ 1 row in set (0.00 sec) “_”匹配任何单个字符， “%”匹配任意数目字符(包括零字符) mysql> select * from pet where name like 'b%'; //查找name以字母b开头的数据 +--------+--------+---------+------+------------+------------+ | name | owner | species | sex | birth | death | +--------+--------+---------+------+------------+------------+ | buffy | harold | dog | f | 1989-05-13 | NULL | | bowser | diane | dog | m | 1979-08-31 | 1995-07-29 | +--------+--------+---------+------+------------+------------+ 2 rows in set (0.00 sec) mysql> select * from pet where name like '%fy'; //查找以fy结尾的数据 +-------+--------+---------+------+------------+-------+ | name | owner | species | sex | birth | death | +-------+--------+---------+------+------------+-------+ | buffy | harold | dog | f | 1989-05-13 | NULL | +-------+--------+---------+------+------------+-------+ 1 row in set (0.02 sec) mysql> select * from pet where name like '%w%'; //查找name中包含了w字母的数据 +----------+-------+---------+------+------------+------------+ | name | owner | species | sex | birth | death | +----------+-------+---------+------+------------+------------+ | claws | gwen | cat | m | 1994-03-17 | NULL | | bowser | diane | dog | m | 1979-08-31 | 1995-07-29 | | whistler | gwen | bird | | 1997-12-09 | NULL | +----------+-------+---------+------+------------+------------+ 3 rows in set (0.00 sec) mysql> select * from pet where name like '_____'; //查找name正好包含5个字母的数据 +-------+--------+---------+------+------------+-------+ | name | owner | species | sex | birth | death | +-------+--------+---------+------+------------+-------+ | fluff | harold | cat | f | 1993-02-04 | NULL | | claws | gwen | cat | m | 1994-03-17 | NULL | | buffy | harold | dog | f | 1989-05-13 | NULL | +-------+--------+---------+------+------------+-------+ 3 rows in set (0.00 sec) ‘.’匹配任何单个的字符。 [...]：匹配在方括号内的任何字符。 例如，“[abc]”匹配“a”、“b”或“c”。为了命名字符的范围，使用一个“-”。 [a-z]：匹配任何字母 [0-9]：匹配任何数字 “ * ”匹配零个或多个在它前面的字符。 例如，“x*”匹配任何数量的“x”字符，“[0-9]*”匹配任何数量的数字，而“.*”匹配任何数量的任何字符。 “^”：开头 “$”：结尾 mysql> select * from pet where name regexp '^b'; //查找以模式字母b开始的数据 +--------+--------+---------+------+------------+------------+ | name | owner | species | sex | birth | death | +--------+--------+---------+------+------------+------------+ | buffy | harold | dog | f | 1989-05-13 | NULL | | bowser | diane | dog | m | 1979-08-31 | 1995-07-29 | +--------+--------+---------+------+------------+------------+ 2 rows in set (0.00 sec) mysql> select * from pet where name regexp 'fy$'; //查找以模式字母fy结尾的数据 +-------+--------+---------+------+------------+-------+ | name | owner | species | sex | birth | death | +-------+--------+---------+------+------------+-------+ | buffy | harold | dog | f | 1989-05-13 | NULL | +-------+--------+---------+------+------------+-------+ 1 row in set (0.00 sec) mysql> select * from pet where name regexp 'w'; //正则查找包含字母w的数据 +----------+-------+---------+------+------------+------------+ | name | owner | species | sex | birth | death | +----------+-------+---------+------+------------+------------+ | claws | gwen | cat | m | 1994-03-17 | NULL | | bowser | diane | dog | m | 1979-08-31 | 1995-07-29 | | whistler | gwen | bird | | 1997-12-09 | NULL | +----------+-------+---------+------+------------+------------+ 3 rows in set (0.00 sec) mysql> select * from pet where name regexp '^.{5}$'; //查找name正好包含5个字母的数据 //正则的方式 +-------+--------+---------+------+------------+-------+ | name | owner | species | sex | birth | death | +-------+--------+---------+------+------------+-------+ | fluff | harold | cat | f | 1993-02-04 | NULL | | claws | gwen | cat | m | 1994-03-17 | NULL | | buffy | harold | dog | f | 1989-05-13 | NULL | +-------+--------+---------+------+------------+-------+ 3 rows in set (0.00 sec) mysql> select count(*) from pet; //计数行 +----------+ | count(*) | +----------+ | 9 | +----------+ 1 row in set (0.00 sec) mysql> select owner,count(*) from pet group by owner; //分组计数 +--------+----------+ | owner | count(*) | +--------+----------+ | benny | 2 | | diane | 2 | | gwen | 3 | | harold | 2 | +--------+----------+ 4 rows in set (0.00 sec) mysql> select species,sex,count(*) from pet group by species,sex; //按照species和sex2个条件进行分组 +---------+------+----------+ | species | sex | count(*) | +---------+------+----------+ | bird | | 1 | | bird | f | 1 | | cat | f | 1 | | cat | m | 1 | | dog | f | 1 | | dog | m | 2 | | hamster | f | 1 | | snake | m | 1 | +---------+------+----------+ 8 rows in set (0.00 sec) mysql> create table event (name varchar(20),date date, //创建event表，是pet的附表 -> type varchar(15),remark varchar(255)); Query OK, 0 rows affected (0.60 sec) //event.txt文件数据： fluffy 1995/5/15 litter 4 kittens, 3 female, 1 male buffy 1993/6/23 litter 5 puppies, 2 female, 3 male buffy 1994/6/19 litter 3 puppies, 3 female chirpy 1999/3/21 vet needed beak straightened slim 1997/8/3 vet broken rib bowser 1991/10/12 kennel fang 1991/10/12 kennel fang 1998/8/28 birthday Gave him a new chew toy claws 1998/3/17 birthday Gave him a new flea collar whistler 1998/12/9 birthday First birthday mysql> LOAD DATA LOCAL INFILE 'E:\\event.txt' INTO TABLE event; //转载数据 Query OK, 10 rows affected (0.20 sec) Records: 10 Deleted: 0 Skipped: 0 Warnings: 0 // mysql> select pet.name, -> (year(date)-year(birth))-(right(date,5)<right(birth,5)) as age, -> remark -> from pet,event -> where pet.name=event.name and event.type='litter'; +-------+------+------------------------------+ | name | age | remark | +-------+------+------------------------------+ |buffy | 4 | 5 puppies, 2 female, 3 male | 5 | 3 puppies, 3 female +-------+------+------------------------------+ 2 rows in set (0.00 sec) mysql> select pet.name, -> remark -> from pet,event -> where pet.name=event.name and event.type='litter'; //联合查找 type='litter'的数据 +-------+------------------------------+ | name | remark | +-------+------------------------------+ |buffy | 5 puppies, 2 female, 3 male |3 puppies, 3 female +-------+------------------------------+ 2 rows in set (0.00 sec) mysql> select database(); //查找当前所有数据库，这里特指正在使用的数据库 +------------+ | database() | +------------+ | test | +------------+ 1 row in set (0.00 sec) mysql> show tables; //查看所有表 +----------------+ | Tables_in_test | +----------------+ | event | | pet | +----------------+ 2 rows in set (0.00 sec) mysql> describe pet; //查找表的结构 +---------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+-------+ | name | varchar(20) | YES | | NULL | | | owner | varchar(20) | YES | | NULL | | | species | varchar(20) | YES | | NULL | | | sex | char(1) | YES | | NULL | | | birth | date | YES | | NULL | | | death | date | YES | | NULL | | +---------+-------------+------+-----+---------+-------+ 6 rows in set (0.00 sec) mysql> create table shop( //创建shop数据库 -> article int(4) unsigned zerofill default '0000' not null, //指定数字位数，位数不足自动用0补齐 -> dealer char(20) default '' not null, -> price double(16,2) default '0.00' not null, //price保留2个小数点，16个字节，默认值0.00 -> primary key(article,dealer)); Query OK, 0 rows affected (0.62 sec) mysql> insert into shop values //插入数据，一种新的插入数据的形式 -> (1,'a',3.45), -> (1,'b',3.99), -> (2,'a',10.99), -> (3,'b',1045), -> (3,'c',1.69), -> (3,'d',1.25), -> (4,'d',19.95); Query OK, 7 rows affected (0.40 sec) Records: 7 Duplicates: 0 Warnings: 0 mysql> select * from shop; //查看全部数据结果 +---------+--------+---------+ | article | dealer | price | +---------+--------+---------+ | 0001 | a | 3.45 | | 0001 | b | 3.99 | | 0002 | a | 10.99 | | 0003 | b | 1045.00 | | 0003 | c | 1.69 | | 0003 | d | 1.25 | | 0004 | d | 19.95 | +---------+--------+---------+ 7 rows in set (0.00 sec) mysql> select max(article) as '物品号最大的商品' from shop; //max +------------------+ | 物品号最大的商品 | +------------------+ | 4 | +------------------+ 1 row in set (0.02 sec) mysql> select article,dealer,price //查找最贵商品的编号、销售商和价格等数据 -> from shop -> where price= -> (select max(price) from shop); //从子查询语句来实现 +---------+--------+---------+ | article | dealer | price | +---------+--------+---------+ | 0003 | b | 1045.00 | +---------+--------+---------+ 1 row in set (0.00 sec) mysql> select article,dealer,price //查找最贵商品的编号、销售商和价格等数据 -> from shop //排序（降序）和limit方式综合实现 -> order by price desc -> limit 1; +---------+--------+---------+ | article | dealer | price | +---------+--------+---------+ | 0003 | b | 1045.00 | +---------+--------+---------+ 1 row in set (0.00 sec) mysql> select article,max(price) as price //分组求每种商品的最大值 -> from shop -> group by article; +---------+---------+ | article | price | +---------+---------+ | 0001 | 3.99 | | 0002 | 10.99 | | 0003 | 1045.00 | | 0004 | 19.95 | +---------+---------+ 4 rows in set (0.35 sec) mysql> select article,dealer,price //定义要查找的字段 -> from shop s1 //数据来源 -> where price= //定义数据需要满足的条件 -> (select max(s2.price) from shop s2 where s1.article = s2.article); +---------+--------+---------+ | article | dealer | price | +---------+--------+---------+ | 0001 | b | 3.99 | | 0002 | a | 10.99 | | 0003 | b | 1045.00 | | 0004 | d | 19.95 | +---------+--------+---------+ 4 rows in set (0.00 sec) //论坛用户访问时间数据 mysql> create table t1 (year year(4),month int(2) unsigned zerofill, //创建t1表 -> day int(2) unsigned zerofill); Query OK, 0 rows affected (0.65 sec) mysql> insert into t1 values //插入数据 -> (2000,1,1), -> (2000,2,20), -> (2000,1,30), -> (2000,2,2), -> (2000,2,23), -> (2000,2,23); Query OK, 6 rows affected (0.40 sec) Records: 6 Duplicates: 0 Warnings: 0 ysql> select year,month,bit_count(bit_or(1<<day)) as days //查找同年同月访问量 -> from t1 -> group by year,month; +------+-------+------+ | year | month | days | +------+-------+------+ | 2000 | 01 | 2 | | 2000 | 02 | 3 | //本来是4次，为什么是3次呢，做了去重处理 +------+-------+------+ 2 rows in set (0.36 sec) mysql> create table animals( //通过AUTO_INCREMENT属性为新的行产生唯一的标识 -> id mediumint not null auto_increment, -> name char(20) not null, -> primary key(id) -> ); Query OK, 0 rows affected (0.26 sec) mysql> insert into animals (name) values -> ('dog'), -> ('cat'), -> ('penguin'), -> ('lax'), -> ('whale'), -> ('ostrich'); mysql> select * from animals; //通过查看结果可以看出表对name自动加了编号 +----+---------+ | id | name | +----+---------+ | 1 | dog | | 2 | cat | | 3 | penguin | | 4 | lax | | 5 | whale | | 6 | ostrich | +----+---------+ 6 rows in set (0.00 sec) mysql> alter table animals auto_increment=100; //定义数据的编号从100开始编号 Query OK, 0 rows affected (0.37 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> insert into animals (name) values //插入一条新的数据 -> ('cat1'); Query OK, 1 row affected (0.40 sec) mysql> select * from animals; //查看发现最新插入的数据编号为100，而不是编号7 +-----+---------+ | id | name | +-----+---------+ | 1 | dog | | 2 | cat | | 3 | penguin | | 4 | lax | | 5 | whale | | 6 | ostrich | | 100 | cat1 | +-----+---------+ 7 rows in set (0.00 sec)
无语言基础，自学python所做的各种笔记，欢迎大牛指点.
