Mysql学习日志;
安装:
Linux :
yum install mysql* -y
验证Mysql是否正常安装:
mysqladmin --version
进入 Mysql,要先启动
#service mysqld start
#mysql
更换Mysql初始密码:
安装完成mysql为空密码，修改密码方式：
mysqladmin -u root password newpassword
用户已经有旧密码的更改方式：
MYSQLADMIN -u root -p'oldpassword' PASSWORD newpassword //需要注意的地方是新的密码不需要引号进行标识
完整删除mysql:
rpm -qa | grep mysql 查询
rpm -ev perl-DBD-MySQL-4.013-3.el6.x86_64 删除
find / -name mysql 查找之前老版本mysql的目录
rm -rf /var/lib/mysql 删除对应目录
关闭Mysql:
# cd /usr/bin/
# ./mysqladmin -u root -p shutdown
Enter password:
#
查询数据库用户
mysql> use mysql;
mysql> select user,password,host FROM user;
+------+-------------------------------------------+-----------+
| user | password | host |
+------+-------------------------------------------+-----------+
| root | *FD571203974BA9AFE270FE62151AE967ECA5E0AA | localhost |
| root | | rsyslog |
| root | | 127.0.0.1 |
| | | localhost |
| | | rsyslog |
+------+-------------------------------------------+-----------+
5 rows in set (0.00 sec)
插入一个用户,有查询\更新\插入功能
mysql> insert into
user(host,user,password,select_priv,update_priv,insert_priv) values('localhost','loutsx',password('111111'),'y','y','y');
Query OK, 1 row affected, 3 warnings (0.00 sec)
重新载入授权表
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> select host,password,user,select_priv from user;
+-----------+-------------------------------------------+--------+-------------+
| host | password | user | select_priv |
+-----------+-------------------------------------------+--------+-------------+
| localhost | *FD571203974BA9AFE270FE62151AE967ECA5E0AA | root | Y |
| rsyslog | | root | Y |
| 127.0.0.1 | | root | Y |
| localhost | | | N |
| rsyslog | | | N |
| localhost | *FD571203974BA9AFE270FE62151AE967ECA5E0AA | loutsx | Y |
+-----------+-------------------------------------------+--------+-------------+
6 rows in set (0.00 sec)
mysql> show tables;
mysql> desc user;-----这个简单
mysql> show columns from db;-------------查看表的字段
+-----------------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------+---------------+------+-----+---------+-------+
| Host | char(60) | NO | PRI | | |
| Db | char(64) | NO | PRI | | |
| User | char(16) | NO | PRI | | |
| Select_priv | enum('N','Y') | NO | | N | |
| Insert_priv | enum('N','Y') | NO | | N | |
| Update_priv | enum('N','Y') | NO | | N | |
| Delete_priv | enum('N','Y') | NO | | N | |
| Create_priv | enum('N','Y') | NO | | N | |
| Drop_priv | enum('N','Y') | NO | | N | |
| Grant_priv | enum('N','Y') | NO | | N | |
| References_priv | enum('N','Y') | NO | | N | |
| Index_priv | enum('N','Y') | NO | | N | |
| Alter_priv | enum('N','Y') | NO | | N | |
| Create_tmp_table_priv | enum('N','Y') | NO | | N | |
| Lock_tables_priv | enum('N','Y') | NO | | N | |
| Create_view_priv | enum('N','Y') | NO | | N | |
| Show_view_priv | enum('N','Y') | NO | | N | |
| Create_routine_priv | enum('N','Y') | NO | | N | |
| Alter_routine_priv | enum('N','Y') | NO | | N | |
| Execute_priv | enum('N','Y') | NO | | N | |
| Event_priv | enum('N','Y') | NO | | N | |
| Trigger_priv | enum('N','Y') | NO | | N | |
+-----------------------+---------------+------+-----+---------+-------+
22 rows in set (0.00 sec)
创建数据库
# mysqladmin -u root -p create RUNOOB
Enter password:******
删除数据库:
[root@host]# mysqladmin -u root -p drop RUNOOB
Enter password:******
mysql> create database aaa;
Query OK, 1 row affected (0.00 sec)
mysql> drop database aaa;
Query OK, 0 rows affected (0.01 sec)
创建和操作表
------------------------------------------------------------------------
创建表：
-----------------------------------
CREATE TABLE IF NOT EXISTE
表名 (
属性名 数据类型 [完整约束条件],
属性名 数据类型 [完整约束条件],
...
...
属性名 数据类型 [完整约束条件]
PRIMARY KEY(属性名)
)ENGINE=MYSIAM
IF NOT EXISTE 检查表是否存在，不存在则创建
mysql> create table test(
-> id int NOT NULL AUTO_INCREMENT,
-> name varchar(20) NOT NULL
-> );
NULL不等于空串，NULL是没有值，不是空串，‘‘是一个有效的值，在NOT NULL是允许的。
主键：PRIMARY KEY
必须唯一，可以创建时定义，或后续补充
AUTO_INCREMENT
自动增量，表只有允许一列用AUTO_INCREMENT，必须被索引(若让它成为主键)
使用默认值
如果插入行时没有值，可允许指定默认值，用DEFAULT关键字指定
不允许使用函数做默认值
mysql> create table test(
-> id int NOT NULL AUTO_INCREMENT,
-> name varchar(20) NOT NULL DEFAULT ‘aaaa‘
-> );
引擎类型
查看表详细结构语句SHOW CREATE TABLE
mysql> SHOW CREATE TABLE test;－－－－－要大写
修改表名
表名可以在一个数据库中唯一的确定一张表。
格式：ALTER TABLE 旧表名 RENAME 新表名;
mysql> alter table test rename test1;
Query OK, 0 rows affected (0.00 sec)
修改字段的数据类型
格式：ALTER TABLE 表名 MODIFY 属性名 数据类型;
mysql> desc test1;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> alter table test1 modify name varchar(40);
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc test1;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(40) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
修改字段名：
格式：ALTER TABLE 表名 CHANGE 旧属性名 新属性名 新数据类型;
mysql> alter table test1 change name ping varchar(30);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
增加字段
格式：ALTER TABLE 表名 ADD 属性名1 数据类型 [完整性约束条件] [FIRST | AFTER 属性名2];
其中，“属性名1”参数指需要增加的字段的名称；
“FIRST”参数是可选参数，其作用是将新增字段设置为表的第一个字段；
“AFTER”参数也是可选的参数，其作用是将新增字段添加到“属性名2”后面；“属性名2”当然就是指表中已经有的字段
mysql> alter table test1 add myname varchar(20);
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc test1;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| ping | varchar(30) | YES | | NULL | |
| myname | varchar(20) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
修改字段顺序：
LTER TABLE baike369 MODIFY stu_name VARCHAR(30) FIRST; 放在最前
ALTER TABLE baike369 MODIFY num INT(8) AFTER address; 放在address之后
5）删除字段
格式：ALTER TABLE 表名 DROP 属性名;
mysql> alter table test1 drop myname;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
字段时间：
ySQL 日期类型：日期格式、所占存储空间、日期范围 比较。
日期类型 存储空间 日期格式 日期范围
------------ --------- --------------------- -----------------------------------------
datetime 8 bytes YYYY-MM-DD HH:MM:SS 1000-01-01 00:00:00 ~ 9999-12-31 23:59:59
timestamp 4 bytes YYYY-MM-DD HH:MM:SS 1970-01-01 00:00:01 ~ 2038
date 3 bytes YYYY-MM-DD 1000-01-01 ~ 9999-12-31
year 1 bytes YYYY 1901 ~ 2155
在 MySQL 中创建表时，对照上面的表格，很容易就能选择到合适自己的数据类型。不过到底是选择 datetime 还是 timestamp，可能会有点犯难。这两个日期时间类型各有优点：datetime 的日期范围比较大；timestamp 所占存储空间比较小，只是 datetime 的一半。
另外，timestamp 类型的列还有个特性：默认情况下，在 insert, update 数据时，timestamp 列会自动以当前时间（CURRENT_TIMESTAMP）填充/更新。“自动”的意思就是，你不去管它，MySQL 会替你去处理。
删除表：
MySQL 插入数据:
INSERT INTO table_name ( field1, field2,...fieldN )
VALUES( value1, value2,...valueN );
MySQL 查询数据:
-----------------------------------------------------
SELECT column_name,column_name
FROM table_name
[WHERE Clause]
[OFFSET M ][LIMIT N]
MySQL限定唯一
DISTINCT此参数可限制后面IPaddress唯一性，不会出现多个相同的结果
SELECT DISTINCT IPaddress from ipaddr;
MySQL显示指定条目
LIMIT可限制显示的条目，如果想接这后面再显示20条，则为LIMIT 20,20
SELECT * from ipaddr LIMIT 20;
MySQL 排序:
使用 MySQL 的 ORDER BY 子句来设定你想按哪个字段哪中方式来进行排序
SELECT field1, field2,...fieldN table_name1, table_name2...
ORDER BY field1, [field2...] [ASC [DESC]]
asc:升
desc:降
select host,password,user from user order by host asc ;
MySQL 查询两个之间的数据:
BETWEEN可选择两个之间的数据
select * from buy where prod_price BETWEEN 3 AND 9;
空值检查
在创建表时，可以指定列是否可以包含空值，不包含空值时，称其为包含空值NULL
空值即无值，以字段包含0、空字符或空格不同
可通过IS NULL来检查是否具有空值的列
select * from buy where prod_price IS NULL;
AND 和 OR(可多个叠加)
可通过此两个参数来匹配
AND前后两个条件均需匹配
OR 前两两个条件只要匹配一个即可
计算次序，AND优先
mysql> select * from buy where vend_id != '1001' AND prod_price > 10;
+---------+---------+------------+--------------+
| prod_id | vend_id | prod_price | prod_name |
+---------+---------+------------+--------------+
| JP2000 | 1004 | 55 | Jetpack 2000 |
| JP1000 | 1004 | 35 | Jetpack 1000 |
+---------+---------+------------+--------------+
2 rows in set (0.00 sec)
mysql> select * from buy where vend_id != '1001' OR prod_price > 10;
+---------+---------+------------+--------------+
| prod_id | vend_id | prod_price | prod_name |
+---------+---------+------------+--------------+
| FU1 | 1002 | 3.42 | TFuses |
| SLING | 1005 | 4.49 | Sling |
| OL1 | 1004 | 8.99 | Oil |
| FB | 1003 | 10 | Bird |
| JP2000 | 1004 | 55 | Jetpack 2000 |
| JP1000 | 1004 | 35 | Jetpack 1000 |
+---------+---------+------------+--------------+
6 rows in set (0.00 sec)
IN操作符，等同于OR
使用长选项清单时，IN更直观
使用IN，次序更容易管理
IN执行比OR快
IN可包含其他SELECT语句
mysql> select * from buy where vend_id IN(1001,1002);
+---------+---------+------------+-----------+
| prod_id | vend_id | prod_price | prod_name |
+---------+---------+------------+-----------+
| FC | 1001 | 2.5 | Carrots |
| TNT1 | 1001 | 2.5 | TNT1 |
| FU1 | 1002 | 3.42 | TFuses |
+---------+---------+------------+-----------+
3 rows in set (0.00 sec)
NOT操作符---使用复杂混合操作有用
否则它之后的任何条件
mysql> select * from buy where vend_id NOT IN(1001,1002);
+---------+---------+------------+--------------+
| prod_id | vend_id | prod_price | prod_name |
+---------+---------+------------+--------------+
| SLING | 1005 | 4.49 | Sling |
| OL1 | 1004 | 8.99 | Oil |
| FB | 1003 | 10 | Bird |
| JP2000 | 1004 | 55 | Jetpack 2000 |
| JP1000 | 1004 | 35 | Jetpack 1000 |
+---------+---------+------------+--------------+
5 rows in set (0.00 sec)
SELECT field1, field2,...fieldN table_name1, table_name2...
WHERE field1 LIKE condition1 [AND [OR]] filed2 = 'somevalue'
LIKE 操作符:
可采用模糊方式查询
通配符:
% 表示任意字符出现任意次数
_ 匹配一个字符
mysql> select * from buy where prod_name like '%000%';
+---------+---------+------------+--------------+
| prod_id | vend_id | prod_price | prod_name |
+---------+---------+------------+--------------+
| JP2000 | 1004 | 55 | Jetpack 2000 |
| JP1000 | 1004 | 35 | Jetpack 1000 |
+---------+---------+------------+--------------+
2 rows in set (0.00 sec)
mysql> select * from buy where prod_name like '_il';
+---------+---------+------------+-----------+
| prod_id | vend_id | prod_price | prod_name |
+---------+---------+------------+-----------+
| OL1 | 1004 | 8.99 | Oil |
+---------+---------+------------+-----------+
1 row in set (0.01 sec)
用正则表达式进行搜索
不区别大小写，可用BINARY强制区别大小写
mysql> select * from buy where prod_name REGEXP BINARY 'jetp';
Empty set (0.00 sec)
mysql> select * from buy where prod_name REGEXP BINARY 'Jetp';
+---------+---------+------------+--------------+
| prod_id | vend_id | prod_price | prod_name |
+---------+---------+------------+--------------+
| JP2000 | 1004 | 55 | Jetpack 2000 |
| JP1000 | 1004 | 35 | Jetpack 1000 |
+---------+---------+------------+--------------+
2 rows in set (0.00 sec)
. 匹配任意一个字符
mysql> select * from buy where prod_name REGEXP '.000';
+---------+---------+------------+--------------+
| prod_id | vend_id | prod_price | prod_name |
+---------+---------+------------+--------------+
| JP2000 | 1004 | 55 | Jetpack 2000 |
| JP1000 | 1004 | 35 | Jetpack 1000 |
+---------+---------+------------+--------------+
2 rows in set (0.05 sec)
｜ 进行OR匹配
mysql> select * from buy where vend_id IN(1001,1002);
mysql> select * from buy where vend_id REGEXP '1001|1002';
+---------+---------+------------+-----------+
| prod_id | vend_id | prod_price | prod_name |
+---------+---------+------------+-----------+
| FC | 1001 | 2.5 | Carrots |
| TNT1 | 1001 | 2.5 | TNT1 |
| FU1 | 1002 | 3.42 | TFuses |
+---------+---------+------------+-----------+
3 rows in set (0.00 sec)
[]匹配几个字符,或匹配集合
mysql> select * from buy where vend_id REGEXP '[123]aaa';
mysql> select * from buy where vend_id REGEXP '[1-9]';
匹配特殊符号在符号前加\\
mysql> select * from buy where vend_id REGEXP '\\.';
\\f 换页
\\n 换行
\\r 回车
\\t 制表
\\v 纵向制表
[:alnum:] 任何字母和数字(同[]a-zA-Z0-9)
[:alpha:] 任意字符(同[]a-zA-Z)
[:blank:] 空格和制表(同[\\t])
[:cntrl:] ASCII控制字符(ASCII 0-31和127)
[:digit:] 任意数字[0-9]
[:graph:] 与[:print:]相同，但不包括空格
[:lower:] 任意小写字母[a-z]
[:print:] 任意可打印字符
[:punct:] 即不在[:alnum:]又不在[:cntrl:]中的任意字符
[:space:] 包括空格在内的任意空白字符(同[\\f\\n\\r\\t\\v])
[:upper:] 任意大字字母(同[A-Z])
[:xdigit:] 任意十六进制数字(同[a-fA-F0-9])
* 0个或多个
+ 1个或多个
? 0个或1个
{n} 指定匹配的数目
{n,} 不少于指定数目的匹配
{n,m} 匹配数目的范围(m不超255)
mysql> select * from buy where prod_name REGEXP '[0-9]';
+---------+---------+------------+--------------+
| prod_id | vend_id | prod_price | prod_name |
+---------+---------+------------+--------------+
| TNT1 | 1001 | 2.5 | TNT1 |
| JP2000 | 1004 | 55 | Jetpack 2000 |
| JP1000 | 1004 | 35 | Jetpack 1000 |
+---------+---------+------------+--------------+
3 rows in set (0.01 sec)
[[:digit:]]这里要写两个棕括号，否则结果不匹配需求
mysql> select * from buy where prod_name REGEXP '[[:digit:]]+';
+---------+---------+------------+--------------+
| prod_id | vend_id | prod_price | prod_name |
+---------+---------+------------+--------------+
| TNT1 | 1001 | 2.5 | TNT1 |
| JP2000 | 1004 | 55 | Jetpack 2000 |
| JP1000 | 1004 | 35 | Jetpack 1000 |
+---------+---------+------------+--------------+
3 rows in set (0.00 sec)
定位符
^ 文本的开始
$ 文本的结尾
[[:<:]] 词的开始
[[:>:]] 词的结尾
计算字段
-----------------------------------------------------------
拼接字段 Concat()
mysql> SELECT Concat(order_id,' ==> ',IPaddress) from ipaddr where order_id REGEXP 'MPLS+'LIMIT 10;
+-------------------------------------+
| Concat(order_id,' ==> ',IPaddress) |
+-------------------------------------+
| MPLS-2014-11-00002 ==> 10.2.13.205 |
| MPLS-2015-03-00007 ==> 10.17.41.16 |
| MPLS-2015-10-00001 ==> 10.80.142.0 |
| MPLS-2015-10-00002 ==> 10.80.142.4 |
| MPLS-2015-10-00003 ==> 10.80.142.8 |
| MPLS-2011-03-00001 ==> 10.168.175.0 |
| MPLS-2009-07-00001 ==> 114.28.64.0 |
| MPLS-2015-10-00001 ==> 114.28.64.9 |
| MPLS-2015-10-00002 ==> 114.28.64.10 |
| MPLS-2015-10-00003 ==> 114.28.64.11 |
+-------------------------------------+
10 rows in set (0.00 sec)
RTrim() 可去掉字段后的空格，等同于Python的lstrip()
SELECT Concat(RTrim(order_id),' ==> ',RTrim(IPaddress)) from ipaddr where order_id REGEXP 'MPLS+' LIMIT 10;
别名：即拼接后的内容的字段名称，用AS表示
mysql> SELECT Concat(RTrim(order_id),' ==> ',RTrim(IPaddress)) AS seta from ipaddr where order_id REGEXP 'MPLS+' LIMIT 3;
+------------------------------------+
| seta |
+------------------------------------+
| MPLS-2014-11-00002 ==> 10.2.13.205 |
| MPLS-2015-03-00007 ==> 10.17.41.16 |
| MPLS-2015-10-00001 ==> 10.80.142.0 |
+------------------------------------+
3 rows in set (0.00 sec)
算术计算( + - * / )
order_id*IPaddress AS chengji 可将两个字段进行计算
mysql> SELECT order_id,IPaddress order_id*IPaddress AS chengji from ipaddr LIMIT 3;
函数
------------------------------------------------------------------------
文本处理函数
-------------------------
RTrim() 去除列右边的空格
Upper() 大写
mysql> select prod_id,Upper(prod_name) from buy;
Left() 返回串左边的字符
Length() 返回串的长度
Locate() 找出串的一个子串
Lower() 转换为小写
LTrim() 去除列左边的空格
Soundex() 返回串的SOUNDEX值
SubSrting() 返回子串的字符
日期和时间处理函数
-------------------------------
AddDate() 增加一个日期(天，周等)
AddTime() 增加一个时间(时，分等)
CurDate() 返回当前日期
CurTime() 返回当前时间
Date() 返回日期时间的日期部分
DateDiff() 计划两个日期之差
Date_Add() 高度灵活的日期运算
Date_Format() 返回一个格式化的日期或时间串
Day() 返回一个日期的天数部分
DayOfWeek() 对于一个日期，返回星期几
Hour() 返回一个时间的小时部分
Minute() 返回一个时间的分钟部分
Month() 返回一个日期的月分部分
Now() 返回当前日期和时间
Second() 返回一个时间的时秒部分
Time() 返回一个日期时间的时间部分
Year() 返回一个日期的年份部分
数值处理函数
--------------------------------------
Abs() 返回一个绝对值
Cos() 返回一个角度的余弦
Exp() 返回一个数的指数值
Mod() 返回除操作的余数
Pi() 返回园周率
Rand() 返回一个随机整数
Sin() 返回一个角度的正弦
Sqrt() 返回一个数平方根
Tan() 返回一个角度的正切
汇总数据
---------------------------------------------------------
聚集函数
------------------
AVG() 返回列的平均值
COUNT() 返回列的行数
MAX() 返回列的最大值
MIN() 返回列的最小值
SUM() 返回列的总和
mysql> select AVG(prod_price) as prod_SUM from buy;
+-----------------+
| prod_SUM |
+-----------------+
| 15.237499952316 |
+-----------------+
1 row in set (0.00 sec)
mysql> select COUNT(prod_price) as prod_SUM from buy;------对特定列进行统计，忽略NUL行
mysql> SELECT COUNT(*) from ipaddr ;----可对所有列进行统计
+----------+
| prod_SUM |
+----------+
| 8 |
+----------+
1 row in set (0.01 sec)
mysql> select SUM(prod_price) as prod_SUM from buy;
+-----------------+
| prod_SUM |
+-----------------+
| 121.89999961853 |
+-----------------+
1 row in set (0.00 sec)
mysql> select MAX(prod_price) as prod_SUM from buy;
mysql> select MIN(prod_price) as prod_SUM from buy;
组合聚集函数
可将多个函数一起列出
mysql> SELECT COUNT(*),MAX(prod_price),MIN(prod_price),AVG(prod_price) FROM buy ;
+----------+-----------------+-----------------+-----------------+
| COUNT(*) | MAX(prod_price) | MIN(prod_price) | AVG(prod_price) |
+----------+-----------------+-----------------+-----------------+
| 8 | 55 | 2.5 | 15.237499952316 |
+----------+-----------------+-----------------+-----------------+
1 row in set (0.00 sec)
分组数据
------------------------------------------------------------------------------
创建分组
GROUP BY ;
1.GROUP BY子句可包含任意数目的列，可以对分组进行嵌套，提供更细致的数据；
2.如果在GROUP BY子句中嵌套了分组，数据将在最后规定的分组上进行汇总；
3.GROUP BY子句列出的列都必须是检索列或有效的表达式，但不能是聚集函数，如果在SELECT中使用表达式，必须在GROUP BY子句中指定相同的表达式，不能使用别名；
4.除聚集计算语句外，SELECT语句中的每个列都必须在GROUP BY子句中给出；
5.如果列中有NULL值，将作一个分组返回，列有多行NULL，将分为一组；
6.GROUP BY子句必须出现在WHERE子句之后，或ORDER子句之前；
mysql> SELECT Vendor,COUNT(*) FROM ipaddr WHERE Vendor LIKE '%电信%' GROUP BY Vendor ;
+-----------------------------+----------+
| Vendor | COUNT(*) |
+-----------------------------+----------+
| 上海电信
广州电信 | 1 |
| 上海电信 | 9 |
| 北京电信 | 1 |
| 大连电信 | 1 |
| 大连电信（代理商 大连博峰） | 1 |
| 广州电信 | 4 |
| 无锡电信 | 2 |
| 深圳电信 | 2 |
| 电信 | 14 |
+-----------------------------+----------+
9 rows in set (0.01 sec)
mysql> SELECT Vendor,COUNT(*) FROM ipaddr GROUP BY Vendor;
过滤分组：
HAVING： 等同于WHERE，
WHERE过滤行，不过HAVING过滤分组
WHERE在分组前进行过滤，HAVING在分组后过滤
mysql> SELECT Vendor FROM ipaddr WHERE Vendor LIKE '%无锡电信%' ;
mysql> SELECT Vendor FROM ipaddr HAVING Vendor LIKE '%无锡电信%' ;
+----------+
| Vendor |
+----------+
| 无锡电信 |
| 无锡电信 |
+----------+
2 rows in set (0.00 sec)
统计所有供应商的线路数，并过滤小于10条的
mysql> SELECT Vendor,COUNT(*) FROM ipaddr GROUP BY Vendor HAVING COUNT(*)>=10;
+----------+----------+
| Vendor | COUNT(*) |
+----------+----------+
| | 656 |
| 上海信网 | 11 |
| 中信 | 12 |
| 电信 | 14 |
| 联通 | 29 |
+----------+----------+
5 rows in set (0.00 sec)
将IP重复的列出来：先统计IP,再将大于2条的列出就是
mysql> SELECT IPaddress,COUNT(*) FROM ipaddr GROUP BY IPaddress HAVING COUNT(*) >1;
+----------------+----------+
| IPaddress | COUNT(*) |
+----------------+----------+
| | 7 |
| 114.28.120.216 | 2 |
| 114.28.120.40 | 2 |
先按IP分组，统计大于1的条目，并按IP排序
mysql> SELECT IPaddress,COUNT(*) FROM ipaddr GROUP BY IPaddress HAVING COUNT(*)>1 ORDER BY IPaddress;
子查询
-----------------------------------------------------------------------
Select可以嵌套
子查询用IN表示，也可用=,!=表示
先查询所有电信的记录，再列出电信线路的用户名称
mysql> select customer from ipaddr where Vendor IN(select Vendor from ipaddr where Vendor like '%电信%');
子查询可直接做为计算字段列出
在不同表中进行查询，需要完全匹配列名
ipaddr.vendor = buy.vendor，否则数据可能不正确
mysql> select Order_id,IPaddress,customer,Vendor from ipaddr where Vendor IN(select Vendor from ipaddr where ipaddr.vendor = buy.vendor);
联结表
------------------------------------------------------------------------------
两个表通过同一个相同的字段进行联结
可在FROM后直接加两个表
mysql> SELECT vend_name,prod_name,prod_price FROM vendors,products WHERE vendors.vend_id = products.vend_id ORDER BY vend_name,prod_name;
这里如果WHERE后面不加完全限定列名，可能会产生其他的结果，即所有项的乘积
mysql> SELECT vend_name,prod_name,prod_price FROM vendors INNER JOIN products ON vendors.vend_id = products.vend_id ；
同上面一样的效果，推荐下面方式
联结多个表：联结数目没有限制。费资源，因此联结时注意
SELECT子句嵌套性能并不佳，可用多表联结方式操作
以下两条命令效果相等
select Order_id,IPaddress,customer,Vendor from ipaddr where Vendor IN(select Vendor from buy where ipaddr.vendor = buy.vendor AND prod_id = 'TNT2);
select Order_id,IPaddress,customer,Vendor from ipaddr,buy where ipaddr.vendor = buy.vendor and prod_id = 'TNT2'
使用表别名可方便使用
from ipaddr as ip,buy as b
select Order_id,IPaddress,customer,Vendor from ipaddr as ip,buy as b where ip.vendor = b.vendor and prod_id = 'TNT2'
同一个表中可用自联结方式，不用Select子句，因为联结速度更快，性能更好
方法一：
mysql> select ip1.Customer from ipaddr as ip1,ipaddr as ip2 where ip1.order_id = ip2.order_id AND ip2.Vendor=
'广州电信';
+---------------------+
| Customer |
+---------------------+
| 黛安芬国际集团 |
| 黛安芬国际集团 |
| 黛安芬国际集团 |
| 黛安芬国际集团 |
| 全家－广州 |
| 全家－广州 监控地址 |
| 艾博生物广州 |
| 全家－广州 |
| 全家－广州 监控地址 |
| 艾博生物广州 |
+---------------------+
10 rows in set (0.04 sec)
方法二：更好
mysql> select ip1.Customer from ipaddr as ip1,ipaddr as ip2 where ip1.order_id = ip2.order_id AND ip2.Vendor=
'广州电信';
+---------------------+
| Customer |
+---------------------+
| 黛安芬国际集团 |
| 黛安芬国际集团 |
| 黛安芬国际集团 |
| 黛安芬国际集团 |
| 全家－广州 |
| 全家－广州 监控地址 |
| 艾博生物广州 |
| 全家－广州 |
| 全家－广州 监控地址 |
| 艾博生物广州 |
+---------------------+
10 rows in set (0.00 sec)
mysql>
外部联结
LEFT OUTER ...... ON ......
组合查询
---------------------------------------------------------------------------
SQL允许多个SELECT语句查询，并将查询结果合并返回
创建组合查询 UNION
mysql> select * from buy where vend_id != '1001' UNION select * from buy where prod_price > 10;
+---------+---------+------------+--------------+
| prod_id | vend_id | prod_price | prod_name |
+---------+---------+------------+--------------+
| FU1 | 1002 | 3.42 | TFuses |
| SLING | 1005 | 4.49 | Sling |
| OL1 | 1004 | 8.99 | Oil |
| FB | 1003 | 10 | Bird |
| JP2000 | 1004 | 55 | Jetpack 2000 |
| JP1000 | 1004 | 35 | Jetpack 1000 |
+---------+---------+------------+--------------+
UNION规则：
１.必须由两条或两条以上SELECT语句组成，用UNION分隔
２.UNION中的每个查询必须包含相同的列、表达式枵聚集函数
３.列数据类型必须兼容：类型不必相同，但必须是可以转换的类型
包含或取消重复的行，同DISTINCT取唯一
可包含，用UNION ALL
排序，可用ORDER BY，只能出现在最后一条SELECT语句后面
mysql> select * from buy where vend_id != '1002';
+---------+---------+------------+--------------+
| prod_id | vend_id | prod_price | prod_name |
+---------+---------+------------+--------------+
| FC | 1001 | 2.5 | Carrots |
| TNT1 | 1001 | 2.5 | TNT1 |
| SLING | 1005 | 4.49 | Sling |
| OL1 | 1004 | 8.99 | Oil |
| FB | 1003 | 10 | Bird |
| JP2000 | 1004 | 55 | Jetpack 2000 |
| JP1000 | 1004 | 35 | Jetpack 1000 |
+---------+---------+------------+--------------+
7 rows in set (0.00 sec)
mysql> select * from buy where prod_price > 10;
+---------+---------+------------+--------------+
| prod_id | vend_id | prod_price | prod_name |
+---------+---------+------------+--------------+
| JP2000 | 1004 | 55 | Jetpack 2000 |
| JP1000 | 1004 | 35 | Jetpack 1000 |
+---------+---------+------------+--------------+
2 rows in set (0.00 sec)
mysql> select * from buy where vend_id != '1002' UNION select * from buy where prod_price > 10;
+---------+---------+------------+--------------+
| prod_id | vend_id | prod_price | prod_name |
+---------+---------+------------+--------------+
| FC | 1001 | 2.5 | Carrots |
| TNT1 | 1001 | 2.5 | TNT1 |
| SLING | 1005 | 4.49 | Sling |
| OL1 | 1004 | 8.99 | Oil |
| FB | 1003 | 10 | Bird |
| JP2000 | 1004 | 55 | Jetpack 2000 |
| JP1000 | 1004 | 35 | Jetpack 1000 |
+---------+---------+------------+--------------+
7 rows in set (0.00 sec)
mysql> select * from buy where vend_id != '1002' UNION ALL select * from buy where prod_price > 10;
+---------+---------+------------+--------------+
| prod_id | vend_id | prod_price | prod_name |
+---------+---------+------------+--------------+
| FC | 1001 | 2.5 | Carrots |
| TNT1 | 1001 | 2.5 | TNT1 |
| SLING | 1005 | 4.49 | Sling |
| OL1 | 1004 | 8.99 | Oil |
| FB | 1003 | 10 | Bird |
| JP2000 | 1004 | 55 | Jetpack 2000 |
| JP1000 | 1004 | 35 | Jetpack 1000 |
| JP2000 | 1004 | 55 | Jetpack 2000 |
| JP1000 | 1004 | 35 | Jetpack 1000 |
+---------+---------+------------+--------------+
9 rows in set (0.00 sec)
mysql>
全文本搜索--不支持汉语和日语
---------------------------------------------------------------------
并非所有的引擎都支持全文本-------需重新学习
MYSIAM 支持
InnoDB 不支持
创建表时接受FULLTEXT子句,可指定多个列
CREATE TABLE ipaddr(
`IP_id` varchar(30) default NULL,
`Yongtu` varchar(30) default NULL,
`Order_id` varchar(30) default NULL,
`Beifeng` varchar(30) default NULL,
`IPaddress` varchar(30) default NULL,
`Customer` varchar(30) default NULL,
FULLTEXT(Customer)
)
ENGINE=MyISAM CHARSET=utf8;
使用全文本搜索需要使用两个函数：
Match()指定需要搜索的列
Against()指定要搜索的表达式
mysql> select Customer from ipaddr where Match(Customer) Against('mr01');
+-------------+
| Customer |
+-------------+
| cd-mr01 |
| hg-mr01 |
| hg-mr01 |
| cd-mr01 |
| cd-mr01 |
| sz-mr01 |
| sz-mr01 |
| dl-mr01 |
也可按字段进行全文本搜索
以下在SELECT中使用全文本搜索，这样所有的行将被返回，新列a包含全文本计算出来的值，不包含的返回0
mysql> select Customer,Match(Customer) Against('mr01') as a from ipaddr ;
插入数据
------------------------------------------------------------------------
插入完整的行
插入的值必须和表中的字段一一对应，无值的要用NULL填充，自动添加的也要用NULL填充，此种方法极不安全
INSERT INTO TABLE VALUES('x,x,x','x,x,x','x,x,x','x,x,x');
安全但烦琐的方法：
INSERT INTO TABLE(
字段1，
字段2，
字段3，
字段4，
字段5)
VALUES(
值1，
值2，
值3，
值4，
值5)；
为提高性能，可在INSETT后加LOW_PRIORITY INTO TABLE......
同样适用于UPDATE和DELETE
插入多行
可使用多条语句，或如果列相同可直接使用以下方式
单条语种多个值，可将VALUES值用一对圆括号括起来，用逗号分开
INSERT INTO TABLE(字段1，字段2，字段3，字段4，字段5)
VALUES
(值1，值2，值3，值4，值5)，
(值1，值2，值3，值4，值5)；
插入检索出的数据
将SELECT的结果插入表中
INSERT INTO TABLE1(字段1，字段2，字段3，字段4，字段5)
SELECT 字段1，字段2，字段3，字段4，字段5 from TABLE2
注意，两个列名可不相同，只对应位置即可
更新和删除数据
------------------------------------------------------------------------
更新数据
UPDATE table_name SET field1=new-value1[, field2=new-value2] [WHERE Clause]
Set后面跟要更新的新值,where 后跟需要更改的条件列
where子句必须使用，否则会更新所有行
更新表中特定行/更新表中所有行(不加Where子句)
update user set host='1.2.3.4' where user='loutsx';
update user set host='1.2.3.4',aaa='sdfsfd' where user='loutsx';
可在UPDATE中使用子查询
更新时发现错误，任务就会中断，可用IGNORE关键字，忽略错误
UPDATE IGNORE table_name ..........
删除数据
删除特定的行
删除所有行
删除表中的数据
格式：
delete from 表名 where 表达式
delete from user where host='rsyslog';
更快删除TRUNCATE TABLE语句
它可以完成相同的工作，但更快，它实际是删除一个表，并新建另一个同样表，而不是逐行删除
更新删除指导原则：
1. 更新和删除一行，一定要带WHERE子句
2. 保证每个表都有主键
3. 在更新和删除前，应用SELECT进行测试，保证过滤正确的结果
4. 使用强制实施引用完整性的数据库，这样将不允许删除具有与其他表相关联的数据行。
复制表：
1.复制表结构及数据到新表
复制代码 代码如下:
CREATE TABLE 新表 SELECT * FROM 旧表
2.只复制表结构到新表
复制代码 代码如下:
CREATE TABLE 新表 SELECT * FROM 旧表 WHERE 1=2
即:让WHERE条件不成立.
方法二:(低版本的mysql不支持，mysql4.0.25 不支持，mysql5已经支持了)
复制代码 代码如下:
CREATE TABLE 新表 LIKE 旧表
3.复制旧表的数据到新表(假设两个表结构一样)
复制代码 代码如下:
INSERT INTO 新表 SELECT * FROM 旧表
4.复制旧表的数据到新表(假设两个表结构不一样)
复制代码 代码如下:
INSERT INTO 新表(字段1,字段2,…….) SELECT 字段1,字段2,…… FROM 旧表
----------------------------------------------------------
避免导入数据有中文乱码的问题
1:将数据编码格式保存为utf-8
设置默认编码为utf8：
set names utf8;
设置数据库db_name默认为utf8:
ALTER DATABASE 库名 default character set utf8 COLLATE utf8_general_ci;
设置表tb_name默认编码为utf8:
ALTER TABLE 表名 default character set utf8 COLLATE utf8_general_ci;
2:将数据编码格式保存为ansi(即GBK或GB2312)
设置默认编码为gbk：
set names gbk;
设置数据库db_name默认编码为gbk:
ALTER DATABASE `db_name` DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci;
设置表tb_name默认编码为gbk:
ALTER TABLE `tb_name` DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci;
导入：
LOAD DATA LOCAL INFILE 'C:\\gbk.txt' INTO TABLE yjdb;
注：1.UTF8不要导入gbk，gbk不要导入UTF8;
、数据备份
1、使用mysqldump命令备份
mysqldump命令将数据库中的数据备份成一个文本文件。表的结构和表中的数据将存储在生成的文本文件中。
myldump命令的工作原理很简单。它先查出需要备份的表的结构，再在文本文件中生成一个CREATE语句。然后，将表中的所有记录转换成一条INSERT语句。然后通过这些语句，就能够创建表并插入数据。
1、备份一个数据库
mysqldump基本语法：
mysqldump -u username -p dbname table1 table2 ...-> BackupName.sql
其中：
- dbname参数表示数据库的名称；
- table1和table2参数表示需要备份的表的名称，为空则整个数据库备份；
- BackupName.sql参数表设计备份文件的名称，文件名前面可以加上一个绝对路径。通常将数据库被分成一个后缀名为sql的文件；
使用root用户备份test数据库下的person表
mysqldump -u root -p test person > D:\backup.sql
其生成的脚本如下：
文件的开头会记录MySQL的版本、备份的主机名和数据库名。
文件中以“--”开头的都是SQL语言的注释，以"/*!40101"等形式开头的是与MySQL有关的注释。40101是MySQL数据库的版本号，如果MySQL的版本比1.11高，则/*!40101和*/之间的内容就被当做SQL命令来执行，如果比4.1.1低就会被当做注释。
2、备份多个数据库
语法：
mysqldump -u username -p --databases dbname2 dbname2 > Backup.sql
加上了--databases选项，然后后面跟多个数据库
mysqldump -u root -p --databases test mysql > D:\backup.sql
3、备份所有数据库
mysqldump命令备份所有数据库的语法如下：
mysqldump -u username -p -all-databases > BackupName.sql
示例：
mysqldump -u -root -p -all-databases > D:\all.sql
2、直接复制整个数据库目录
MySQL有一种非常简单的备份方法，就是将MySQL中的数据库文件直接复制出来。这是最简单，速度最快的方法。
不过在此之前，要先将服务器停止，这样才可以保证在复制期间数据库的数据不会发生变化。如果在复制数据库的过程中还有数据写入，就会造成数据不一致。这种情况在开发环境可以，但是在生产环境中很难允许备份服务器。
注意：这种方法不适用于InnoDB存储引擎的表，而对于MyISAM存储引擎的表很方便。同时，还原时MySQL的版本最好相同。
3、使用mysqlhotcopy工具快速备份
一看名字就知道是热备份。因此,mysqlhotcopy支持不停止MySQL服务器备份。而且，mysqlhotcopy的备份方式比mysqldump快。mysqlhotcopy是一个perl脚本，主要在Linux系统下使用。其使用LOCK TABLES、FLUSH TABLES和cp来进行快速备份。
原理：先将需要备份的数据库加上一个读锁，然后用FLUSH TABLES将内存中的数据写回到硬盘上的数据库，最后，把需要备份的数据库文件复制到目标目录。
命令格式如下：
[root@localhost ~]# mysqlhotcopy [option] dbname1 dbname2 backupDir/
- dbname：数据库名称；
- backupDir：备份到哪个文件夹下；
常用选项：
- --help：查看mysqlhotcopy帮助；
- --allowold：如果备份目录下存在相同的备份文件，将旧的备份文件加上_old；
- --keepold：如果备份目录下存在相同的备份文件，不删除旧的备份文件，而是将旧的文件更名；
- --flushlog：本次辈分之后，将对数据库的更新记录到日志中；
- --noindices：只备份数据文件，不备份索引文件；
- --user=用户名：用来指定用户名，可以用-u代替；
- --password=密码：用来指定密码，可以用-p代替。使用-p时，密码与-p之间没有空格；
- --port=端口号：用来指定访问端口，可以用-P代替；
- --socket=socket文件：用来指定socket文件，可以用-S代替；
mysqlhotcopy并非mysql自带，需要安装Perl的数据库接口包；下载地址为:http://dev.mysql.com/downloads/dbi.html
目前，该工具也仅仅能够备份MyISAM类型的表。
二、数据还原
1、还原使用mysqldump命令备份的数据库的语法如下：
mysql -u root -p [dbname] < backup.sq
示例：
mysql -u root -p < C:\backup.sql
2、还原直接复制目录的备份
通过这种方式还原时，必须保证两个MySQL数据库的版本号是相同的。MyISAM类型的表有效，对于InnoDB类型的表不可用，InnoDB表的表空间不能直接复制。