mysql学习日志

首页 > 代码库 > mysql学习日志

2024-09-17 18:32:51 218人阅读

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> 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> 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)

mysql> alter table test1 modify name varchar(40);
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0

修改字段名：
　　格式：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

修改字段顺序：
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在分组后过滤

统计所有供应商的线路数，并过滤小于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>

外部联结
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()指定要搜索的表达式

也可按字段进行全文本搜索
以下在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;

mysql学习日志

声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉：投诉/举报工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。

联系
我们

首页 > 代码库 > mysql学习日志

mysql学习日志

看完仍有疑问？有类似问题直接问程序猿