当前位置:Gxlcms > 数据库问题 > mysql学习日志

mysql学习日志

时间:2021-07-01 10:21:17 帮助过:7人阅读


安装:
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规则:
1.必须由两条或两条以上SELECT语句组成,用UNION分隔
2.UNION中的每个查询必须包含相同的列、表达式枵聚集函数
3.列数据类型必须兼容:类型不必相同,但必须是可以转换的类型

包含或取消重复的行,同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 fr

人气教程排行