mysql语句[宝典].docVIP

# 启动和停用数据库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 d:/site/s.txtEnter password: *** #或\. d:/site/s.txt\. d:\\site\\s.txt #where 语句select * from pet where name=Bowser; #找出1998年1月1日以后出生的PETselect * from pet where birth 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 pet.name regexp ^bunionselect * from pet where binary pet.name 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; #多项