MySQL怎么给表简明_科学网—mySQL简明入门 - 张金龙的博文

详情请参考http://downloads.mysql.com/docs/mysql-tutorial-excerpt-5.1-en.pdfmysql-u root-p回车密码12345Ctrl+D退出查看版本SELECTVERSION(),CURRENT_DATE;查看数据库SHOW DATABASES;创建数据库CREATEDATABASE menagerie;使用数据库USE menagerie创建表格CREATE TABLEpet(nameVARCHAR(20),ownerVARCHAR(20),speciesVARCHAR(20),sexCHAR(1),birthDATE,deathDATE);显示数据库中的表格的名称SHOW TABLES;显示表格各列的属性DESCRIBEpet;从本地读取文件LOADDATA LOCALINFILE'/path/pet.txt'INTO TABLEpet;注意,如果是Windows,要使用LINES TERMINATEDBY'rn';在表格中插入一行INSERT INTOpetVALUES('Puffball','Diane','hamster','f','1999-03-30',NULL);INSERT INTOpetVALUES('Fluffy',' Harold ','cat ','f ','1993-02-04',NULL);INSERT INTOpetVALUES('Claws ','Gwen ','cat ','m ','1994-03-17',NULL);INSERT INTOpetVALUES('Buffy ','Harold ','dog ','f ','1989-05-13',NULL);INSERT INTOpetVALUES('Fang ','Benny',' dog ','m ','1990-08-27',NULL);INSERT INTOpetVALUES('Bowser ','Diane ','dog ','m ','1979-08-31','1995-07-29');INSERT INTOpetVALUES('Chirpy',' Gwen',' bird',' f',' 1998-09-11',NULL)INSERT INTOpetVALUES('Whistler',' Gwen',' bird',' NULL','1997-12-09',NULL)INSERT INTOpetVALUES('Slim',' Benny',' snake',' m ','1996-04-29',NULL)从数据表中获取数据SELECT的基本语句格式:SELECTwhat_to_selectFROMwhich_tableWHEREconditions_to_satisfy;### 查询全部数据SELECT*FROMpet;### 修改部分数据UPDATEpetSETbirth='1989-08-31'WHEREname='Bowser';### 查询部分数据SELECT*FROMpetWHEREname='Bowser';### 比较运算SELECT*FROMpetWHEREbirth>='1998-1-1';### 逻辑运算ANDSELECT*FROMpetWHEREspecies='dog'ANDsex='f';### 逻辑运算ORSELECT*FROMpetWHEREspecies='snake'ORspecies='bird';### 运算的优先级SELECT*FROMpetWHERE(species='cat'ANDsex='m')OR(species='dog'ANDsex='f');### 选择特定的列SELECTname,birthFROMpet;SELECTownerFROMpet;### 去重复SELECT DISTINCTownerFROMpet;###SELECT的基本组合SELECTname,species,birthFROMpetWHEREspecies='dog'ORspecies='cat';### 排序,默认是 由小到大排序SELECTname,birthFROMpetORDER BYbirth;### 由大到小排序SELECTname,birthFROMpetORDER BYbirthDESC;### 多个列排序SELECTname,species,birthFROMpetORDER BYspecies,birthDESC;### 当前日期CURDATE()### 两个日期相隔时间TIMESTAMPDIFF()### 形成新的一列ASage(见后面的例子)### 日期计算SELECTname,birth,CURDATE(),TIMESTAMPDIFF(YEAR,birth,CURDATE())ASageFROMpet;### 加入新一列的表格再排序,按照nameSELECTname,birth,CURDATE(),TIMESTAMPDIFF(YEAR,birth,CURDATE())ASageFROMpetORDER BYname;### 加入新一列的表格再排序,按照ageSELECTname,birth,CURDATE(),TIMESTAMPDIFF(YEAR,birth,CURDATE())ASageFROMpetORDER BYage;### 判断is not nullSELECTname,birth,death,TIMESTAMPDIFF(YEAR,birth,death)ASageFROMpetWHEREdeathIS NOT NULL ORDER BYage;### 时间计算YEAR(),MONTH(),DAYOFMONTH()### 哪个月份出生SELECTname,birth,MONTH(birth)FROMpet;### 按照运算出的结果查找SELECTname,birthFROMpetWHERE MONTH(birth) =5;### 十二月份等的处理SELECTname,birthFROMpetWHERE MONTH(birth) =MOD(MONTH(CURDATE()),12) +1;### 对NULL的判断SELECT1IS NULL,1IS NOT NULL;#### 匹配与正则表达式,寻找name中以b开头的SELECT*FROMpetWHEREnameLIKE'b%';#### 寻找name中以fy结尾的SELECT*FROMpetWHEREnameLIKE'%fy';#### 寻找名字中含有w的SELECT*FROMpetWHEREnameLIKE'%w%';#### 寻找名字只包含五个字母的,用五个_SELECT*FROMpetWHEREnameLIKE'_____';#### 正则表达式的关键字REGEXP and NOT REGEXPoperators(orRLIKEand NOTRLIKE)#### 正则表达式.任意单个字符[a-z]a到z的任意字符[0-9]0到9的任意字符“*” 之前,放任何字符,表示的是任意重复次数的该字符.*表示任意重复次数的任意字符^开始$ 结束### 正则表达式举例### 以b开始的人名(不区分大小写)SELECT*FROMpetWHEREnameREGEXP'^b';### 匹配大小写BINARY,名字以小写的b开头的SELECT*FROMpetWHEREnameREGEXP BINARY'^b';### 名字以fy结尾的SELECT*FROMpetWHEREnameREGEXP'fy$';### 名字中含有w的SELECT*FROMpetWHEREnameREGEXP'w';### 名字仅由五个字母组成SELECT*FROMpetWHEREnameREGEXP'^.....$';或者SELECT*FROMpetWHEREnameREGEXP'^.{5}$';### 统计表格的行数SELECTCOUNT(*)FROMpet;###GROUP BY,针对每一个水平进行计算SELECTowner,COUNT(*)FROMpetGROUP BYowner;SELECTspecies,COUNT(*)FROMpetGROUP BYspecies;SELECTsex,COUNT(*)FROMpetGROUP BYsex;### 分组可以同时考虑几个因素SELECTspecies,sex,COUNT(*)FROMpetGROUP BYspecies,sex;### 进一步筛选,只要dog或者cat的部分记录SELECTspecies,sex,COUNT(*)FROMpetWHEREspecies='dog'ORspecies='cat'GROUP BYspecies,sex;### 只保留已知性别的,按照species和sex统计SELECTspecies,sex,COUNT(*)FROMpetWHEREsexIS NOT NULL GROUP BYspecies,sex;注意:select后面的列,如果使用了count()函数,则后面的group by后面,必须跟上相同的列.否则mysql会报错.####### 使用多个表格#### 创建一个新的表CREATE TABLEevent(nameVARCHAR(20),date DATE,type VARCHAR(15),remarkVARCHAR(255));#### 载入数据LOADDATAINFILE'/home/jinlong/programming/mysql/event.txt'INTO TABLEevent;#### 同时查询两个表格,注意应该先将两个表格用INNER JOIN合并.ON表格合并的凭借 对于pet表格来说,是name,所以是pet.name对于event表格来说,也是name,所以是event.name而筛选的条件,是event表格中的type为'litter'SELECTpet.name,(YEAR(date)-YEAR(birth)) - (RIGHT(date,5)SELECTp1.name,p1.sex,p2.name,p2.sex,p1.speciesFROMpetASp1INNER JOINpetASp2ONp1.species=p2.speciesANDp1.sex='f'ANDp2.sex='m';#### 查询数据库的名称SELECTDATABASE();#### 显示表名SHOW TABLES;#### 获取表格的结构DESCRIBEpet;####MYSQL的批处理 从terminal运行UNIX平台mysqlmysql.out#### 从terminal运行 在批处理模式下显示输出mysql-t#### 从terminal运行 在批处理模式下,保存输入的命令mysql-vvv#### 在mySQL中运行脚本文件source filename;或.filename;####################################################################### 创建一个商品价目表,并基于该表做各种查询show databases;createdatabase test;use test;CREATE TABLEshop(articleINT(4)UNSIGNED ZEROFILLDEFAULT'0000'NOT NULL,dealerCHAR(20)DEFAULT''NOT NULL,priceDOUBLE(16,2)DEFAULT'0.00'NOT NULL,PRIMARY KEY(article,dealer));INSERT INTOshopVALUES(1,'A',3.45),(1,'B',3.99),(2,'A',10.99),(3,'B',1.45),(3,'C',1.69),(3,'D',1.25),(4,'D',19.95);SELECT*FROMshop;#### 查找最贵商品SELECTMAX(article)ASarticleFROMshop;#### 查找最高价对应的货品,供应商SELECTarticle,dealer,priceFROMshopWHEREprice=(SELECTMAX(price)FROMshop);#### 每一组的最贵商品SELECTarticle,MAX(price)ASpriceFROMshopGROUP BYarticle;#### 每组最贵商品所在的行SELECTarticle,dealer,priceFROMshop s1WHEREprice=(SELECTMAX(s2.price)FROMshop s2WHEREs1.article=s2.article);#### 临时变量SELECT@min_price:=MIN(price),@max_price:=MAX(price)FROMshop;SELECT*FROMshopWHEREprice=@min_priceORprice=@max_price;##########################################KEYSCREATE TABLEperson(idSMALLINTUNSIGNEDNOT NULLAUTO_INCREMENT,nameCHAR(60)NOT NULL,PRIMARY KEY(id));CREATE TABLEshirt(idSMALLINTUNSIGNEDNOT NULLAUTO_INCREMENT,style ENUM('t-shirt','polo','dress')NOT NULL,color ENUM('red','blue','orange','white','black')NOT NULL,ownerSMALLINTUNSIGNEDNOT NULL REFERENCESperson(id),PRIMARY KEY(id));INSERT INTOpersonVALUES(NULL,'Antonio Paz');SELECT@last:=LAST_INSERT_ID();INSERT INTOshirtVALUES(NULL,'polo','blue',@last),(NULL,'dress','white',@last),(NULL,'t-shirt','blue',@last);INSERT INTOpersonVALUES(NULL,'Lilliana Angelovska');SELECT@last:=LAST_INSERT_ID();INSERT INTOshirtVALUES(NULL,'dress','orange',@last),(NULL,'polo','red',@last),(NULL,'dress','blue',@last),(NULL,'t-shirt','white',@last);SELECT*FROMperson;###### 查询keysSELECTfield1_index,field2_indexFROMtest_tableWHEREfield1_index='1'ORfield2_index='1'### 查询每个月的访问量CREATE TABLEt1(year YEAR(4),month INT(2)UNSIGNED ZEROFILL,day INT(2)UNSIGNED ZEROFILL);INSERT INTOt1VALUES(2000,1,1),(2000,1,20),(2000,1,30),(2000,2,2),(2000,2,23),(2000,2,23);SELECT year,month,BIT_COUNT(BIT_OR(1<