2-12-mysql-sql语句进阶-文档.docx

回顾前面的基础命令语句修改数据表添加字段：alter table表名add字段名列类型key unique auto Jncrement default valuealter table 表名 add 字段定义 after ar_id;删除字段：alter table表名drop字段名修改字段：alter table表名modify字段名字段新类型完整修改字段：alter table表名change旧字段名称新字段定义修改表名称alter table 表名 rename 新名字删除表drop table if (not) exists!表名;表中行的操作insertinsert into数据表名称(字段列表)values|value俵达式|null|default,.),俵达式 |null|default,.»)insert into数据表名称set字段名称=值,.insert与insert.set的区别是后者可以带有子查询。Delphi 5程序设计与控件参考|电子工业出版社60I ASP数据库系统开发实例导航I人民邮电出版社I 60 |+-+ -+算术运算符：= 等于<>不等于!=> 大于< 小于>=大于等于<=小于等于in 运算符IN运算符用于WHERE表达式中，以列表项的形式支持多个选择，语法如下：WHERE column IN (valuel,value2,.)WHERE column NOT IN (valuelrvalue2r.)Not in与in相反当IN前面加上NOT运算符时，表示与IN相反的意思，即不在这些列表项内选择。找出价格大于60的记录mysql> select bName,price from books where price>60;找出价格为60的mysql> select bName,price from books where price=60;找出价格不等于60的mysql> select bName,price from books where price<>60;找出价格是60,50,70的记录mysql> select bName,price from books where price in (50,60,70);找出价格不是60,50,70的记录mysql> select bName,price from books where price not in (50,60,70);排序：升序：。rderby "排序的字段 asc默认降序：oredrby 排序的字段 descmysql> select bName,price from books where price in (50,60,70) order by price asc;+-+- +I Price | bName+| Illustrator 10 完全手册|50| FreeHand 10 基础教程|50|网站设计全程教程|50 |I ASP数据库系统开发实例导航|60| Delphi 5程序设计与控件参考|60|I ASP数据库系统开发实例导航|60| mysql> select bName,price from books where price in (50,60,70) order by price desc;+| price | price | bName+I ASP数据库系统开发实例导航|60|I Delphi 5程序设计与控件参考|60|I ASP数据库系统开发实例导航|60| Illustrator 10 完全手册| Illustrator 10 完全手册50I FreeHand 10基础教程I FreeHand 10基础教程50 |I网站设计全程教程多个字段排序select bName,price from books where price in (50,60,70) order by price desc,bName desc;范围运算：not between .and.Between and可以使用大于小于的方式来代替,并且使用大于小于意义表述更明确查找价格不在30到60之间的书名和价格mysql> select bName,price from books where price not between 30 and 60 order by price desc;注： 这里的查询条件有三种：between。and , or和in (30,60) >30 and <60 30,60 > = 30 and <=60模糊匹配查询：字段名notlike，通配符'%任意多个字符查找书名中包括“程序”字样记录mysql> select bName from books where bName like '%程序'不含有mysql> select bName from books where bName not like '%程序'MYSQL子查询：概念：在select的where条件中又出现了 select查询中嵌套着查询选择类型名为网络技术”的图书：mysql> select bName,bTypeId from books where bTypeId = (select bTypeld from category where bTypeName='网络技术')；选择类型名称为黑客的图书；mysql> select bName,bTypeId from books where bTypeld = (select bTypeld from category where bTypeName='黑客')；LIMIT限定显示的条目：SELECT * FROM table LIMIT offset, rows偏移量行数LIMIT子句可以被用于强制SELECT语句返回指定的记录数。LIMIT接受一个或两个数字参数。参 数必须是一个整数常量。如果给定两个参数，第一个参数指定第一个返回记录行的偏移量，第二个参数指 定返回记录行的最大数目。初始记录行的偏移量是0(而不是1)：比如 select * from table limit m,n 语句表示其中m是指记录开始的index，从0开始，表示第一条记录n是指从第m + 1条开始，取n条。查出category表中第2条到第6行的记录。首先2到6行有2 , 3,4,5,6总共有5个数字，从2开始，偏移量为1mysql> select * from category limit 1,5;+| bTypeld | bTypeName |网站I3 | 3D动画|4|linux 学习|5 | Delphi 学习 |I6|黑客|+查看所有书籍中价格中最低的三条记录我们对所有记录排序以升序排列，取出前面3个来mysql> select bName,price from books order by price asc limit 0,3; +| bName| price |+|网站制作直通车| 34 |黑客与网络安全| 41 |网络程序与设计-asp |43|我们将子查询和限制条目，算术运算结合起来查询显示字段bName ,price ;条件：找出价格比电子工业出版社出版的书中最便宜还要便宜的书。针对这种查询，我们一步步的来，先找出电子工业出版社出版中最便宜的书mysql> select bName,price from books where publishing = ”电子工业出版社“order by price asc limit 0,1;mysql> select bName,price from books where price<（select price from books where publishing二"电子工业出版社"order by price asc limit 0,1）;或者 多行子直询：all表示小于子查询中返回全部值中的最小值mysql> select bName,price from books where price<all（select price from books where publishing'电子工业出版社）连接查询：以一个共同的字段，求两张表当中符合条件的并集。通过共同字段把这两张表连接起来。常用的连接：内连接：根据表中的共同字段进行匹配外连接分两种：左外连接、右外链接。内连接语法：select字段from表1 inner join表2 on表1.字段=表2.字段内连接：根据表中的共同字段进行匹配测试Select a.bname,a.price,b.btypename from books a inner join category b on a.btypeid = b.btypeid;实际使用中inner可省略掉跟WHERE子句结果一样select a.bnamera.pricerb.btypename from books a, category b where a.btypeid=b.btypeid;外连接（分为左外连接；右外连接）1.左连接：select字段from a表left join b表on连接条件a表是主表，都显示。b表从表主表内容全都有，从表内没有的显示null。Select a.bname,a.price,b.btypename from books a left join category b on a.btypeid = b.btypeid;2,右连接：select字段from a表right join b表。n条件a表是从表，b表主表，都显示。Select a.bname,b.* from books a right join category b on a.btypeid=b.btypeid; 右连接，可以多表连接mysql> select * from student as s left join grade as g on s.sid=g.sid; | sid | name | id | score | sid | |1 | 张三|NULL | NULL | NULL|2 | 李四|1 | 1567|2 |I3|王二麻子|2 |1245 |3|4 |HA|3 | 1231|4|5 |Tom|41 1234|5| 聚合函数函数：执行特定功能的代码块。算数运算函数：Sum()求和显示所有图书单价的总合mysql> select sum(price) from books;或 select sum(price) as 图书总价 from books;I sum(price) | +10048 |+avg()平均值：求书籍Id小于3的所有书籍的平均价格 mysql> select avg(price) from books where bld< = 3; +I avg (price) | +|39.3333 |+max()最大值：求所有图书中价格最贵的书籍mysql> select bNamefmax(price) from books;这种方法是错误的 我们来直一下最贵的图书是哪本？select bname,price from books order by desc price limit 0,3； 可见最贵书是Javascript与Jscript从入门到精通，而不是网站制作直通车select bNamerprice from books where price=(select max(price) from books); +| bName| price |+| Javascript 与 Jscript 从入门到精通 | 7500 |+ -+min()最小值:求所有图书中价格便宜的书籍mysql> select bName,price from books where price=(select min(price) from books); +| bName| price |+-+|网站制作直通车|34 |+count。统计记录数：统计价格大于40的书籍数量mysql> select count(*) from books where price>40; +| count(*) | +I 43 | +Count ()中还可以熠加你需要的内容，比如增加distinct来配合使用select count(distinct price) from books where price>40; 算数运算：+ -*/给所有价格小于40元的书籍，涨价5元mysql> update books set price=price+5 where price<40;给所有价格高于70元的书籍打8折mysql> update books set price=price*0.8 where price>70;字符串函数：substr(string fstart,len)截取：从 start 开始，截取 len 长.start 从 1 开始算起。mysql> select substr(bTypeNameflf7) from category where bTypeId = 10;+| substr(bTypeNameffl,7) |+| AutoCAD| 本来是 AutoCAD 技术+select substr(bTypeNamef8,2)from category where bTypeId = 10;+| substr(bTypeName,8f2) |+|技术|只截取汉字+1 row in set (0.00 sec)concat(strl,str2fstr3.)拼接。把多个字段拼成一个字段输出mysql> select concat(bName,publishing) from books;mysql> select concat(bNamer"'publishing) from books;大小写转换upper。大写：转为大写输出mysql> select upper(bname) from books where bld=9;+| upper(bname)update -单表 update表名set字段名称=值，where条件如果省略WHERE条件将更新全部记录。删除记录-单表delete from数据表名称where条件如果省略where条件，将删除全部记录select字段列表from数据表as别名where条件别名的用法：Select * from 数据表as别名字段名称as别名Select product_offer_instance_object_id as ID, product_offer_instance_object_name namercoumn33 '金额'From table+|图书类型+|图书类型+select btypeid as '图书类别 ID',btypename as '图书类型from category;+|图书类别工D+12345678910windows 应用 网站_3D动画 linux学习 Delphi学习 黑客 网络技术 安全 平面AutoCAD 技术+select语句返回零条或多条记录；属于记录读操作 insert, update, delete只返回此次操作影响的记录数；属于写操作+| DREAMWEAVER 4aegpn1IL |+-+这样转换中文会出现乱码lower。小写：转为小写输出mysql> select lower(bName) from books where bld = 10;| lower(bName)+I 3d max 3.0创作效果百例|+数据类型MySQL中定义数据字段的类型对你数据库的优化是非常重要的。MySQL支持多种类型，大致可以分为三类：数值、日期/时间和字符串（字符）类型。数值类型类型大小范围（有符号）范Bl （无符号）用途TINYINT1字节(-128 , 127)(0,255)小整数值SMALLINT2字节(-32 768,32 767)(0,65 535)大整数值MEDIUMINT3字节(-8 388 608,8 388 607)(0 , 16 777 215)大整数值INT或INTEGER4字节(-2 147 483 648 f 2 147 483 647)(0,4 294 967 295)大整数值BIGINT8字节(-9 233 372 036 854 775 808,9 223 372 036 854 775 807)(0 , 18 446 744 073 709 551 615)极大整数值FLOAT4字节(-3.402 823 466 E+38 r -1.175 494351 E-38), 0,(1.175 494 351 E-38,3.402 823 466 351 E+38)0 , (1.175 494 351 E-38,3.402823 466 E+38)单精度浮点数值DOUBLE8字节(-1.797 693 134 862 315 7 E+308 , -2.225 073 858 507 201 4 E-308), 0 , (2.225 073 858 507 201 4 E-308 , 1.797 693 134 862 315 7 E+308)0 , (2.225 073 858 507 201 4 E- 308,1.797 693 134 862 315 7 E+308)双精度浮点数值DECIMAL对DECIMAL(M.D) r如果M>D f为M+2否则为D+2依赖于M和D的值依赖于M和D的值小数值日期和时间类型类型大小 （却SS*用途DATE31000-01-01/9999-12-31YYYY-MM-DD日期值TIME3'-838:59:597838:59:59,HH:MM:SS时间值或持续时间YEAR11901/2155YYYY年份值DATETIME81000-01-01 00:00:00/9999-12-31 23:59:59YYYY-MM-DDHH:MM:SS混合日期和时间值TIMESTAMP41970-01-01 00:00:00/2037 年某时YYYYMMDDHHMMSS混合日期和时间值，时间戳字符串类型类型大小用途CHAR3255字节定长字符串VARCHAR0-65535 字节变长字符串TINYBLOBd25涪节不超过255个字符的二进制字符串TINYTEXT3255字节短文本字符串BLOB0-65 534节二进制形式的长文本数据TEXT比5 534节长文本数据MEDIUMBLOB0>16 777 21 涪节二进制形式的中等长度文本数据MEDIUMTEXT0-16 777 21555中等长度文本数据LONGBLOB0-4 294 967 295字节二进制形式的极大文本数据LONGTEXT0-4 294 967 295字节极大义本数据整型tinyint,占1字节,有符号：128127,无符号位:0255smallint,占2字节,有符号：3276832767,无符号位:065535 mediumint,占 3 字节,有符号：-83886088388607,无符号位:016777215int,占 4 字节,有符号：-21474836482147483647,无符号位:04284967295bigint,占8字节bool等价于tinyint(l)布尔型浮点型float(m,d)占 4 字节,L17E383.4E+38double(mfd)占 8 字节decimal(mfd)以字符串形式表示的浮点数字符型char(m):固定长度的字符，占用m字节varchar(m):可变长度的字符，占用m+1字节，大于255个字符：占用m+2tinytextf255个字符(2的8次方)text,65535个字符(2的16次方)mediumtextf16777215 字符(2 的 24 次方)longtextf(2 的 32 次方)enum(vaEe,value,.)占1/2个字节最多可以有65535个成员set(value,value,.)占1/2/3/4/8个字节，最多可以有64个成员http:/www. runoob. com/mysql/mysqI-seIect-query. html常用SELECT命令使用select命令查看mysql数据库系统信息:一打印当前的日期和时间select now();一打印当前的日期select curdateQ;一打印当前的时间select curtime();打印当前数据库select database(); 打印MySQL版本select version(); 一打印当前用户select user(); 查看系统信息show variables;show global variables;show global variables like '%version%,;show variables like ,%storage_engine%'默认的存储引擎like模糊搜索还可用户where字句，例如select * from students where stname like ,%I%1%2%3%,;除了 like 还有 not likeshow engines;查看支持哪些存储引擎查看系统运行状态信息show status;show global status like 'Thread%1;多使用help导出，导入数据库导入数据库导入数据库前必须创建一个空数据库mysql -e 'create database book' -uroot -pl23456或者登陆mysqlcreate database book;导入（方法一）mysql -uroot -pl23456 book < book.sqlmysql> use book;mysql > show tables;| Tablesjn_book | +-+| books| catego+导入（方法二）create database book;mysql> use book;mysql> source /root/book.sql #sql 脚本的路径mysql> show tables;+| Tables_in_book |+| books| category|+导出数据库导出数据库：mysqldump -u用户名p数据库名 > 导出的文件名mysqldump -u system -pl23456 book>book2.sql扩展知识Mysqldump -uroot -pl23456 -B 库名文件.sql-B ：导出整个库包含建库语句-A:导出全部数据库如何把一个select的结果导出到文本select * into outfile 7tmp/123.txt' from books;此处有个文件访问权限问题，mysql用户是可以访 问/tmp路径的，所以这里放到tmp下select * from books into outfile 7tmp/456.txt,;其实就是备份数据库扩展：5.7版本导出报错,可以设置f加上secure-file-priv=7 "SQL查询语句进阶在我们刚导入的book数据库进行测试查看表的内容：mysql> select * from category;mysql> select * from books;mysql> select * from booksG查看字段类型：desc表名 mysql> desc books;逻辑运算符：and or notand且or或not非选择出书籍价格为(30,40,50,60 )的记录，只显示书籍名称，出版社，价格mysql> select bNamerpublishing,price from books where price=30 or price=40 or price=50 or price=60;+| bName| publishing| price |+ -+I Illustrator 10完全手册|科学出版社|50| Freehand 10基础教程|北京希望电子出版|50|网站设计全程教程|科学出版社|50|I ASP数据库系统开发实例导航|人民邮电出版社|60|