mysql语句.doc

# 启动和停用数据库net start mysqlnet stop mysql# 登陆和退出mysql -hlocalhost -uroot -p123;quit;# 注释#/* */脚本中可以使用 -# 查看版本select version();# 查看日期select current_date();# 查看当前时间select now();# 查看用户select user();# 取消未完成的输入c#显示有哪些数据库show databases;#显示当前数据库select database();#创建数据库。如：创建menageriecreate database menagerie;#切换到menagerieuse menagerie;#通过登陆直接进入menagerie数据库mysql -hlocalhost -uroot -p menagerie*#删除数据库drop database menagerie#显示当前数据库中的tableshow tables;#新建一个tablecreate table pet(name varchar(20),owner varchar(20),species varchar(20),sex char(1),birth date, death date);#describe显示table的结构describe pet;#显示表是如何创建的show create table pet;#删除tabledrop table pet;#insert 插入记录insert into pet values(Puffball,Diane,hamster,f,1999-03-30,NULL);#select 语句select * from pet;#delete 删除记录delete from pet where name=Puffball;#update 将Bowse的birth 改成1989-08-31update pet set birth=1989-08-31 where name=Bowser;#清空tabledelet from pet;#3.5批处理mysql -hlocalhost -uroot -p123d:/site/s.txt#或mysql -hlocalhost -uroot -p 1998-1-1;#找出母狗select * from pet where species = dog and sex=f;#找出蛇以及鸟select * from pet where species = bird or species=snake;#找出公猫以及母狗select * from pet where (species=cat and sex=m) or (species=dog and sex=f);#显示pet的Name 和 birthselect name, birth from pet;# UNIONselect * from pet where binary regexp bunionselect * from pet where binary regexp py$;#找出宠物主人select owner from pet;#DISTINCT 只出现一次select distinct owner from pet;#选出猫狗的name,species,birthselect name, species, birth from pet wherespecies=dog or species =cat;#选出name,birth 按 birth 排序select name, birth from pet order by birth;#选出name,birth 按 birth 倒序排序select name, birth from petorder by birth desc;#强制区分大小写功能select name, birth from petorder by binary name desc;#多项排序select * from pet order by species,birth desc;#计算日期select name, birth, curdate(),(year(curdate()-year(birth)-(right(curdate(),5)right(birth,5)As agefrom pet;#计算年龄并按年龄排序select name, birth, curdate(),(year(curdate()-year(birth)-(right(curdate(),5)right(birth,5)as agefrom petorder by age;#选出已死庞物的寿命，并按寿命排序select name,birth,death,(year(death)-year(birth)-(right(death,5)right(birth,5)as DAGEfrom pet where death is not nullorder by DAGE;#选出庞物的生日select name, birth, month(birth) from pet;#选出下月过生日的庞物#方法一：select name, birth from petwhere month(birth)=month(date_add(curdate(),interval 1 month);#方法二，利用求余法：select name,birth from petwhere month(birth)=mod(month(curdate()+1,12);#模式匹配#_匹配单个字符，%匹配任意数目字符#SQL默认不区分大小写#以b开头select * from pet where name like b%;#以fy结尾select * from pet where name like%fy;#找出包含w的名字select * from pet where name like %w%;#找出正好五个字母的名字select * from pet where name like _;#SQL支持扩展的正则表达式#找出以b开头的名字，使用匹配名字的开始#加上 binary 区分大小写select * from pet where name regexp b;#找出以fy结尾的名字select * from pet where name regexp binary fy$;#找出包含w的名字select * from pet where name regexp w;#找出正好五个字母的名字select * from pet where name regexp .5$;#计算行select count(*) from pet;#计算出每组的行数select owner, count(*) from pet group by owner;#每种动物的数量select species, count(*) from pet group by species;#每种性别动物的数量，性别不为空select sex, count(*) as amount from pet where sex is not null group by sex;#按种类和性别组合动物select species, sex, count(*) from pet group by species, sex;#选出猫狗。种类、性别及对应的行数select species, sex, count(*) from petwhere species=dog or species=catgroup by species,sex;#种类、性别及对应的行数select species, sex, count(*) from petwhere sex is not nullgroup by species, sex;#列的最大值#选出最大物品号select max(birth) as article from pet;#拥有某个最大值的行select * from pet where birth =(select max(birth)from pet);#排序后的第一行select * from petorder by birth desclimit 0,1;#按组取出列的最大值 （非一一对应）select name, max(birth) as bdfrom petgroup by species;#拥有某个字段的组间最大值的行#上例一一对应select * from pet p1where p1.birth=(select max(p2.birth)from pet p2where p2.species=p1.species);#使用auto_increment#create table animals(id mediumint not null auto_increment,name char(30) not null,primary key(id);#insert into animals (name) values(dog),(cat),(penguin),(lax),(whale),(ostric