mysql 提高篇

1、数据类型
MySQL中定义数据字段的类型对你数据库的优化是非常重要的。
MySQL支持多种类型,大致可以分为三类:数值、日期/时间和字符串(字符)0类型。
 数值类型分类

整型
tinyint,占1字节,有符号:-138~137,无符号位:0~255
smallint,占2字节,有符号:-32768~32767,无符号位:0~65535
mediumint,占3字节,有符号:-8388608~8388607,无符号位:0~16777215
int,占4字节,有符号:-2147483648~2147483647,无符号位:0~4284967295
bigint,占8字节
bool 等价于tinyint(1) 布尔型

浮点型
float([m[,d]]) 占4字节,1.17E-38~3.4E+38
double([m[,d]]) 占8字节
decimal([m[,d]]) 以字符串形式表示的浮点数

字符型
char([m]):固定长度的字符,占用m字节
varchar[(m)]:可变长度的字符,占用m+1字节,大于255个字符:占用m+2
tinytext,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个成员

2、常用select命令

--查看系统信息
mysql> show variables; #显示变量
mysql> show global variables; #显示全局变量
mysql> show global variables like '%version%';
mysql> show variables like '%storage_engine%'; #默认的存储引擎
like和”_”模糊搜索还可用户where字句。
MySQL提供两个通配符,用于与LIKE运算符一起使用,它们分别是:百分比符号 %和下划线 _。
百分比(%)表示通配符允许匹配任何字符串的零个或多个字符。
下划线(_)表示通配符允许匹配任何单个字符
mysql> select * from students where stname like'_1';

select * from students where stname not like'_1';

mysql> show engines; #查看支持哪些存储引擎


查看系统运行状态信息:
mysql> show status; #查看运行状态
mysql> show global status like 'Thread%'; #显示全局状态

3  导出、导入数据库

1)、
导入数据库前必须创建一个空数据库
[root@xuegod63 ~]# mysql -e 'create database book' -u root -p123456
或者登陆 MySQL
mysql> create database book;

mysql -uroot -p123456 book < book.sql


2)、mysql> create database book;
mysql> use book;
mysql> source /tmp/book.sql #sql脚本的路径
mysql> show tables;

3) 导出数据库
导出数据库:mysqldump -u 用户名 -p 数据库名 > 导出的文件名
# mysqldump -u root -p123456 book > /tmp/book2.sql


扩展知识
mysqldump - u root -p123456 -B 库名 >文件.sql
-B : 导出整个库包含建库语句
-A:导出全部数据库

 

如何把一个select的结果导出到文本,其实就是备份数据库
mysql> use book;
mysql> select * into outfile '/tmp/133.txt' from books; 此处有个文件访问权限问题,mysql用户是可以访问/tmp路径的,所以这里放到tmp下
或:
mysql> select * from books into outfile '/tmp/456.txt';
注意:mysql 默认对导出的目录有权限限制,也就是说使用命令行进行导出的时候,需要指定目录进行操作,否则会出现报错如下。
mysql> select * into outfile '/tmp/133.txt' from books;
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

扩展:
解决 5.7版本导出报错,可以设置my.cnf 加上secure-file-priv="/ "

 

4、 SQL查询语句进阶

mysql> select * from category;
mysql> select * from books\G

 查看字段类型
desc 表名


 逻辑运算符
and or not
and 且
or 或
not 非

选择出书籍价格为(30,40,50,60)的记录,只显示书籍名称,出版社,价格
select bName,publishing,price from books where price=30 or price=40 or price=50 or price=60;

 

算术运算符
= 等于
<> 不等于 !=
> 大于
< 小于
>= 大于等于
<= 小于等于


in 运算符
IN 运算符用于 WHERE 表达式中,以列表项的形式支持多个选择,语法如下:
WHERE column IN (value1,value2,...)
WHERE column NOT IN (value1,value2,...)

Not in 与in相反

mysql> select bName,price from books where price in (50,60,70); #找出价格是60,50,70的记录。

排序
升序:order by “排序的字段” asc 默认
降序:oredr by “排序的字段” desc
mysql> select bName,price from books where price in (50,60,70) order by price asc;

 

 范围运算
[not]between ....and....
Between and 可以使用大于小于的方式来代替,并且使用大于小于意义表述更明确
查找价格不在30到60之间的书名和价格
mysql> select bName,price from books where price not between 30 and 60 order by price desc;

 

模糊匹配查询
字段名 [not]like '通配符' ----》% 表示任意多个字符

查找书名中包括"程序"字样记录
mysql> select bName from books where bName like '%程序%';

 

mysql> select bName from books where bName not like '%程序%';

 

MySQL子查询:
概念:在select 的where条件中又出现了select,查询中嵌套着查询
选择 类型名为“网络技术”的图书:
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开始,偏移量为1
mysql> select * from category limit 1,5;

我们对所有记录排序以升序排列,取出前面3个来
mysql> select bName,price from books order by price asc limit 0,3;

select bName,price from books where publishing="电子工业出版社" order by price asc limit 0,1;


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.字段
内连接:根据表中的共同字段进行匹配
测试
mysql> select a.bname,a.price,b.btypename from books a inner join category b on a.btypeid=b.btypeid;

实际使用中inner可省略掉,跟WHERE 子句结果一样
mysql> 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 连接条件
解释:优先显示左表全部记录,此时左表主表,右表为从表
主表内容全都有,从表内没有的显示null。

mysql> 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 条件
解释:优先显示右表全部记录,此时右表主表,左表为从表
主表内容全都有,从表内没有的显示null。


select a.bname,b.* from books a right join category b on a.btypeid=b.btypeid;

 

聚合函数
函数:执行特定功能的代码块。
1. 算数运算函数
(1) Sum()求和
显示所有图书单价的总合
mysql> select sum(price) from books;
或:
select sum(price) as 图书总价 from books;


求书籍Id小于3的所有书籍的平均价格
mysql> select avg(price) from books where bId<=3;


我们来查一下最贵的图书是哪本?
Select bname,price from books order by price asc limit 0,3;
可见最贵书是Javascript与Jscript从入门到精通,而不是网站制作直通车
Select bName,price from books where price=(select max(price) from books);

 

 min()最小值
求所有图书中价格便宜的书籍?
mysql> select bName,price from books where price=(select min(price) from books);

2. count()统计记录数
统计价格大于40的书籍数量
mysql> select count(*) from books where price>40;
mysql> select count(distinct price) from books where price>40;

3. 算数运算
+ - * /
给所有价格小于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;

 

4. 字符串函数
substr(string ,start,len) 截取:从start开始,截取len长.start 从1开始算起。
mysql> select substr(bTypeName,1,7) from category where bTypeId=10;

5. 大小写转换
upper()大写 : 转为大写输出
mysql> select upper(bname) from books where bId=9;

 

 

6、查看所有用户分别拥有什么权限
mysql> SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM mysql.user;

查看数据库中具体某个用户的权限
mysql> show grants for 'cactiuser'@'%';

posted @ 2019-08-29 16:56  科子  阅读(420)  评论(0编辑  收藏  举报