2-12-mysql-sql语句进阶-精品文档整理.docx
回顾前面的基础命令语句修改数据表添加字段：alter table 表名 add 字段名 列类型 not null|nullprimary keyuniqueauto_incrementdefault 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的区别是后者可以带有子查询。update - 单表update 表名 set 字段名称=值,. where 条件如果省略WHERE条件将更新全部记录。删除记录 - 单表delete from 数据表名称 where 条件如果省略where条件，将删除全部记录selectselect 字段列表 from 数据表 as 别名 where 条件别名的用法： Select * from 数据表 as 别名字段名称 as别名Select product_offer_instance_object_id as ID, product_offer_instance_object_name name,coumn33 金额From tableselect btypeid as '图书类别ID',btypename as '图书类型' from category; select语句返回零条或多条记录；属于记录读操作insert、update、delete只返回此次操作影响的记录数；属于写操作数据类型MySQL中定义数据字段的类型对你数据库的优化是非常重要的。MySQL支持多种类型，大致可以分为三类：数值、日期/时间和字符串(字符)类型。数值类型日期和时间类型字符串类型整型tinyint,占1字节,有符号：-128127,无符号位:0255smallint,占2字节,有符号：-3276832767,无符号位:065535mediumint,占3字节,有符号：-83886088388607,无符号位:016777215int,占4字节,有符号：-21474836482147483647,无符号位:04284967295bigint,占8字节bool 等价于tinyint(1) 布尔型浮点型float(m,d) 占4字节,1.17E-383.4E+38 double(m,d) 占8字节decimal(m,d) 以字符串形式表示的浮点数字符型char(m):固定长度的字符,占用m字节varchar(m):可变长度的字符,占用m+1字节，大于255个字符：占用m+2tinytext,255个字符(2的8次方)text,65535个字符(2的16次方)mediumtext,16777215字符(2的24次方)longtext,(2的32次方)enum(value,value,.)占1/2个字节 最多可以有65535个成员set(value,value,.)占1/2/3/4/8个字节，最多可以有64个成员常用SELECT命令使用select命令查看mysql数据库系统信息：- 打印当前的日期和时间select now();- 打印当前的日期select curdate();- 打印当前的时间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 '%l%1%2%3%'除了like 还有not likeshow engines;查看支持哪些存储引擎-查看系统运行状态信息show status;show global status like 'Thread%'多使用help导出，导入数据库导入数据库导入数据库前必须创建一个空数据库mysql -e 'create database book' -uroot -p123456或者登陆 mysql create database book;导入（方法一）mysql -uroot -p123456 book < book.sqlmysql> use book;mysql> show tables;+-+| Tables_in_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 -p123456 book>book2.sql扩展知识Mysqldump uroot p123456 B 库名>文件.sql-B : 导出整个库包含建库语句-A：导出全部数据库如何把一个select的结果导出到文本select * into outfile '/tmp/123.txt' from books; 此处有个文件访问权限问题，mysql用户是可以访问/tmp路径的,所以这里放到tmp下select * from books into outfile '/tmp/456.txt'其实就是备份数据库扩展： 5.7版本导出报错，可以设置f 加上secure-file-priv="/ "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 bName,publishing,price from books where price=30 or price=40 or price=50 or price=60;+-+-+-+| bName | publishing | price |+-+-+-+| Illustrator 10完全手册| 科学出版社| 50 | FreeHand 10基础教程| 北京希望电子出版| 50 | 网站设计全程教程| 科学出版社| 50 | ASP数据库系统开发实例导航| 人民邮电出版社| 60 | Delphi 5程序设计与控件参考| 电子工业出版社| 60 | ASP数据库系统开发实例导航| 人民邮电出版社| 60 |+-+-+-+算术运算符：=等于<>不等于 !=>大于<小于>=大于等于<=小于等于in运算符IN 运算符用于 WHERE 表达式中，以列表项的形式支持多个选择，语法如下：WHERE column IN (value1,value2,.)WHERE column NOT IN (value1,value2,.)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);排序：升序：order by “排序的字段” asc 默认降序：oredr by “排序的字段” desc mysql> select bName,price from books where price in (50,60,70) order by price asc;+-+-+| bName | price |+-+-+| Illustrator 10完全手册| 50 | FreeHand 10基础教程| 50 | 网站设计全程教程| 50 | ASP数据库系统开发实例导航| 60 | Delphi 5程序设计与控件参考 | 60 | ASP数据库系统开发实例导航 | 60 |mysql> select bName,price from books where price in (50,60,70) order by price desc;+-+-+| bName | price |+-+-+| ASP数据库系统开发实例导航 | 60 | Delphi 5程序设计与控件参考 | 60 | ASP数据库系统开发实例导航 | 60 | Illustrator 10完全手册 | 50 | FreeHand 10基础教程 | 50 | 网站设计全程教程 | 50 |多个字段排序select bName,price from books where price in (50,60,70) order by price desc,bName desc;范围运算：notbetween .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 <6030,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 bTypeId from category where bTypeName='网络技术');选择类型名称为“黑客”的图书；mysql> select bName,bTypeId from books where bTypeId=(select bTypeId 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;+-+-+| bTypeId | bTypeName |+-+-+| 2 | 网站 | 3 | 3D动画 | 4 | linux学习 | 5 | Delphi学习 | 6 | 黑客 |+-+-+查看所有书籍中价格中最低的三条记录我们对所有记录排序以升序排列，取出前面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.bname,a.price,b.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表 on 条件 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 | 3 | 王二麻子 | 2 | 1245 | 3 | 4 | HA | 3 | 1231 | 4 | 5 | Tom | 4 | 1234 | 5 |+-+-+-+-+-+聚合函数函数：执行特定功能的代码块。算数运算函数：Sum()求和显示所有图书单价的总合mysql> select sum(price) from books; 或select sum(price) as 图书总价 from books;+-+| sum(price) |+-+| 10048 |+-+avg()平均值：求书籍Id小于3的所有书籍的平均价格mysql> select avg(price) from books where bId<=3;+-+| avg(price) |+-+| 39.3333 |+-+max() 最大值：求所有图书中价格最贵的书籍mysql> select bName,max(price) from books; 这种方法是错误的我们来查一下最贵的图书是哪本？select bname,price from books order by desc price limit 0,3;可见最贵书是Javascript与Jscript从入门到精通，而不是网站制作直通车select bName,price 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(*) |+-+| 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 ,start,len) 截取：从start开始，截取len长.start 从1开始算起。mysql> select substr(bTypeName,1,7) from category where bTypeId=10;+-+| substr(bTypeName,1,7) |+-+| AutoCAD | 本来是AutoCAD技术+-+select substr(bTypeName,8,2)from category where bTypeId=10;+-+| substr(bTypeName,8,2) |+-+| 技术 | 只截取汉字+-+1 row in set (0.00 sec)concat(str1,str2,str3.) 拼接。 把多个字段拼成一个字段输出mysql> select concat(bName,publishing) from books;mysql> select concat(bName,"-",publishing) from books;大小写转换upper()大写 : 转为大写输出mysql> select upper(bname) from books where bId=9;+-+| upper(bname) |+-+| DREAMWEAVER 4µ¶ |+-+这样转换中文会出现乱码lower()小写 ：转为小写输出mysql> select lower(bName) from books where bId=10;+-+| lower(bName) |+-+| 3d max 3.0 创作效果百例 |+-+
