Linux系统下Mysql的使用
Mysql简介
概念
实现流行的开源、免费的关系型数据库
特点
- 免费、开源数据库
- 小巧、功能齐全
- 使用便捷
- 可运行于Windows或Linux操作系统
- 可适用于中小型甚至大型网站应用
Mysql运行机制
结构化查询语句SQL
结构化查询语句分类
| 名称 | 解释 | 命令 |
|---|---|---|
| DLL（数据定义语言） | 定义和管理数据对象，如数据库、数据表等 | create、drop、alter |
| DML（数据操作语言） | 用于操作数据库对象中所包含的数据 | insert、update、delete |
| DQL（数据查询语言） | 用于查询数据库数据 | select |
| DCL（数据控制语言） | 用来管理数据库的语言，包括管理权限及数据更改 | grant、commit、rollback |
命令行操作数据库
创建数据库
create database [IF NOT EXISTS] 数据库名;
删除数据库
drop database [IF EXISTS] 数据库名;
查看数据库
show databases;
使用数据库
use 数据库名;
创建数据表
CREATE TABLE [IF NOT EXISTS] tablename(
`字段1` 列类型 [属性] [索引] [注释],
`字段2` 列类型 [属性] [索引] [注释],
......
`字段n` 列类型 [属性] [索引] [注释],
)[表类型][表字符集][注释];
数据值和列类型
列类型
规定数据库中该列存放的数据类型
分为：
- 数值类型
- 字符串类型
- 时期和时间型数值类型
- NULL值
数值类型
字符串类型
日期
日期和时间型数值类型
数据字段属性
- UNSIGNED:无符号的，声明该数据列不允许负数
- ZEROFILL:0填充的，不足位数的用0来填充，如int(3),5则为005
- AUTO_INCREMENT:自动增长的，每添加一条数据，自动在上一个记录数上加1，通常用于设置主键，且为整数类型，可定义起始值和步长
- NULL和NOT NULL:默认为NULL，即没有插入该列的数值，如果设置为NOT NULL ，则该列必须有值
- DEFAULT:默认的，用于设置默认值
修改数据表
- 修改表名
ALTER TABLE 旧表名 RENAME TO 新表名
- 添加字段
ALTER TABLE 表名 ADD 字段名 列类型[属性]
- 修改字段
ALTER TABLE 表名 MODIFY 字段名 列类型[属性]
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 列类型[属性]
- 删除字段
ALTER TABLE 表名 DROP 字段名
删除数据表
DROP TABLE [IF EXISTS] 表名
IF EXISTS为可选，判断是否存在该数据表
如删除不存在的数据表会抛出错误
外键（foreign key）
一、基本概念
1、MySQL中“键”和“索引”的定义相同，所以外键和主键一样也是索引的一种。不同的是MySQL会自动为所有表的主键进行索引，但是外键字段必须由用户进行明确的索引。用于外键关系的字段必须在所有的参照表中进行明确地索引，InnoDB不能自动地创建索引。
2、外键可以是一对一的，一个表的记录只能与另一个表的一条记录连接，或者是一对多的，一个表的记录与另一个表的多条记录连接。
3、如果需要更好的性能，并且不需要完整性检查，可以选择使用MyISAM表类型，如果想要在MySQL中根据参照完整性来建立表并且希望在此基础上保持良好的性能，最好选择表结构为innoDB类型。
4、外键的使用条件
① 两个表必须是InnoDB表，MyISAM表暂时不支持外键
② 外键列必须建立了索引，MySQL 4.1.2以后的版本在建立外键时会自动创建索引，但如果在较早的版本则需要显式建立；
③ 外键关系的两个表的列必须是数据类型相似，也就是可以相互转换类型的列，比如int和tinyint可以，而int和char则不可以；
5、外键的好处：可以使得两张表关联，保证数据的一致性和实现一些级联操作。
二、使用方法
1、创建外键的语法：
外键的定义语法：
[CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, …)
REFERENCES tbl_name (index_col_name, …)
[ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]
[ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]
该语法可以在 CREATE TABLE 和 ALTER TABLE 时使用，如果不指定CONSTRAINT symbol，MYSQL会自动生成一个名字。
ON DELETE、ON UPDATE表示事件触发限制，可设参数：
① RESTRICT（限制外表中的外键改动，默认值）
② CASCADE（跟随外键改动）
③ SET NULL（设空值）
④ SET DEFAULT（设默认值）
⑤ NO ACTION（无动作，默认的）
DML语言
INSERT
INSERT INTO 表名 [(字段1,字段2,字段3,...)] VALUES('值1','值2','值3',...);
字段或值之间用英文难逗号隔开
可同时插入多条数据，values后用英文逗号隔开
UPDATE
UPDATE 表名 SET column_name1=value1[,column_name2=value2,...] [WHERE conditon];
column_name为要修改的数据列
value为修改后的数据，可以为变量，具体值，表达式或者嵌套的select结果
condition为筛选条件，如不指定则修改该表的所有列数据
DELETE
DELETE FROM 表名 [WHERE condition]
condition为筛选条件，如不指定则删除该表的所有列数据
TRUNCATE
TRUNCATE [table] table_name;
用于完全清空表数据，但表结构、索引、约束等不变
区别于delete
相同:都能删除数据，不删除表结构，但truncate速度更快
不同:使用truncate重新设置auto_increment计数器
使用truncate不会对事务有影响
DQL(数据查询语言)
查询表数据:
SELECT 列名1, 列名2, ... FROM 表名 WHERE 条件;
查询所有字段用* 不带WHERE条件 把表的所有记录查出来
过滤掉重复的值:
SELECT DISTINCT 列名1 FROM 表名;
该列重复的值将不会显示出来
连接:
SELECT CONCAT(列名1, 列名2, ) FROM 表名;
使查询结果为多个列值连在一起
OR
SELECT CONCAT_WS('==', 列名1, 列名2, ) FROM 表名;
使查询结果为多个列值连在一起
并增加列值连接符
OR
SELECT CONCAT_WS('==', 列名1, 列名2, ) AS 重命名 FROM 表名;
使查询结果为多个列值连在一起
并增加列值连接符
并修改查询结果列名
AS为重命名
模糊查询:
SELECT 列名 FROM 表名 WHERE 表名 LIKE '%关键字%';
这是全模糊,还有左模糊右模糊
LIKE效率并不高, 数据量庞大的情况下建议用Sphinx(斯芬克斯)
Sphinx是一个基于SQL的全文检索引擎
排序查询:
SELECT 列名1,列名2 ... FROM 表名 ORDER BY 被排序的列名 ASC;
ASC为正序, 将ASC换成DESC时为倒序 不写时默认为正序
聚合函数:
SELECT COUNT(*) FROM 表名; 查询表的记录数
SELECT SUM(列名) FROM 表名; 查询此列的和
SELECT AVG(列名) FROM 表名; 查询此列的平均值
SELECT MAX(列名) FROM 表名; 查询此列的最大值
SELECT MIN(列名) FROM 表名; 查询此列的最小值
分组查询:
SELECT COUNT(*) FROM 表名 GROUP BY 性别列 按照性别分组
一、内连接（INNER JOIN）
1、等值连接
概述：指使用等号"="比较两个表的连接列的值，相当于两表执行笛卡尔后，取两表连结列值相等的记录。
语法：
[sql] view plain copy
SELECT 列
FROM 表1 INNER JOIN 表2
ON 表1.列 = 表2.列
示例 ：
[sql] view plain copy
SELECT A.*, B.*
FROM student_info A inner join student_score B
ON A.student_id = B.student_id ;
2、非等值连接
概述：指使用大于号">“或小于号”<"比较两个表的连接列的值，相当于两表执行笛卡尔后，取一个表大于或小于另一个表的连结列值的记录。
语法：
[sql] view plain copy
SELECT 列
FROM 表1 INNER JOIN 表2
ON 表1.列 <> 表2.列
示例：
[sql] view plain copy
SELECT A.*, B.*
FROM student_info A inner join student_score B
ON A.student_id > B.student_id
二、外连接
1、左外连接（LEFT OUTER JOIN）
概述：指将左表的所有记录与右表符合条件的记录，返回的结果除内连接的结果，还有左表不符合条件的记录，并在右表相应列中填NULL。
示例：
[sql] view plain copy
SELECT A.*, B.*
FROM student_info A left join student_score B
ON A.student_id = B.student_id
说明1：左外连接是以左边的表 student_info A 为主表，student_score 为从表。在查询结果中全部展示主表的信息。
2、右外连接（RIGHT OUTER JOIN）
概述：与左外连接相反，指将右表的所有记录与左表符合条件的记录，返回的结果除内连接的结果，还有右表不符合条件的记录，并在左表相应列中填NULL。
示例：
[sql] view plain copy
SELECT A.*, B.*
FROM student_info A right join student_score B
ON A.student_id = B.student_id
说明2：右外连接是以右边的表 student_score B为主表，student_info A为从表。在查询结果中全部展示主表的信息。
3、全外连接（FULL JOIN）——MySQL不支持
概述：指将左表所有记录与右表所有记录进行连接，返回的结果除内连接的结果，还有左表与右表不符合条件的记录，并在左表与右表相应列中填NULL。
[sql] view plain copy
SELECT A.*, B.*
FROM student_info Afull join student_score B
ON A.student_id = B.student_id
(此语句针对一般数据库)
三、自然连接（NATURAL JOIN）
概述：指自动将表中相同名称的列进行记录匹配。
示例：
[sql] view plain copy
SELECT A.*, B.*
FROM student_info A natural join student_score B ;
四、自连接
概述：指用表的别名实现表自身的连接。
示例：
[sql] view plain copy
SELECT B.*
FROM student_score A, student_score B
WHERE A.student_id = B.student_id
AND B.student_score > 80
常用函数
数学函数
（1）ABS(x)
返回x的绝对值
（2）PI()
返回圆周率π，默认显示6位小数
（3）SQRT(x)
返回非负数的x的二次方根
（4）MOD(x,y)
返回x被y除后的余数
（5）CEIL(x)、CEILING(x)
返回不小于x的最小整数
（6）FLOOR(x)
返回不大于x的最大整数
（7）ROUND(x)、ROUND(x,y)
前者返回最接近于x的整数，即对x进行四舍五入；后者返回最接近x的数，其值保留到小数点后面y位，若y为负值，则将保留到x到小数点左边y位
（8）SIGN(x)
返回参数x的符号，-1表示负数，0表示0，1表示正数
（9）POW(x,y)和、POWER(x,y)
返回x的y次乘方的值
（10）EXP(x)
返回e的x乘方后的值
（11）LOG(x)
返回x的自然对数，x相对于基数e的对数
（12）LOG10(x)
返回x的基数为10的对数
（13）RADIANS(x)
返回x由角度转化为弧度的值
（14）DEGREES(x)
返回x由弧度转化为角度的值
（15）SIN(x)、ASIN(x)
前者返回x的正弦，其中x为给定的弧度值；后者返回x的反正弦值，x为正弦
（16）COS(x)、ACOS(x)
前者返回x的余弦，其中x为给定的弧度值；后者返回x的反余弦值，x为余弦
（17）TAN(x)、ATAN(x)
前者返回x的正切，其中x为给定的弧度值；后者返回x的反正切值，x为正切
（18）COT(x)
返回给定弧度值x的余切
字符串函数
（1）CHAR_LENGTH(str)
计算字符串字符个数
（2）CONCAT(s1,s2，...)
返回连接参数产生的字符串，一个或多个待拼接的内容，任意一个为NULL则返回值为NULL
（3）CONCAT_WS(x,s1,s2,...)
返回多个字符串拼接之后的字符串，每个字符串之间有一个x
（4）INSERT(s1,x,len,s2)
返回字符串s1，其子字符串起始于位置x，被字符串s2取代len个字符
（5）LOWER(str)和LCASE(str)、UPPER(str)和UCASE(str)
前两者将str中的字母全部转换成小写，后两者将字符串中的字母全部转换成大写
（6）LEFT(s,n)、RIGHT(s,n)
前者返回字符串s从最左边开始的n个字符，后者返回字符串s从最右边开始的n个字符
（7）LPAD(s1,len,s2)、RPAD(s1,len,s2)
前者返回s1，其左边由字符串s2填补到len字符长度，假如s1的长度大于len，则返回值被缩短至len字符；前者返回s1，其右边由字符串s2填补到len字符长度，假如s1的长度大于len，则返回值被缩短至len字符
（8）LTRIM(s)、RTRIM(s)
前者返回字符串s，其左边所有空格被删除；后者返回字符串s，其右边所有空格被删除
（9）TRIM(s)
返回字符串s删除了两边空格之后的字符串
（10）TRIM(s1 FROM s)
删除字符串s两端所有子字符串s1，未指定s1的情况下则默认删除空格
（11）REPEAT(s,n)
返回一个由重复字符串s组成的字符串，字符串s的数目等于n
（12）SPACE(n)
返回一个由n个空格组成的字符串
（13）REPLACE(s,s1,s2)
返回一个字符串，用字符串s2替代字符串s中所有的字符串s1
（14）STRCMP(s1,s2)
若s1和s2中所有的字符串都相同，则返回0；根据当前分类次序，第一个参数小于第二个则返回-1，其他情况返回1
（15）SUBSTRING(s,n,len)、MID(s,n,len)
两个函数作用相同，从字符串s中返回一个第n个字符开始、长度为len的字符串
（16）LOCATE(str1,str)、POSITION(str1 IN str)、INSTR(str,str1)
三个函数作用相同，返回子字符串str1在字符串str中的开始位置（从第几个字符开始）
（17）REVERSE(s)
将字符串s反转
（18）ELT(N,str1,str2,str3,str4,...)
返回第N个字符串
时间和日期函数
（1）CURDATE()、CURRENT_DATE()
将当前日期按照"YYYY-MM-DD"或者"YYYYMMDD"格式的值返回，具体格式根据函数用在字符串或是数字语境中而定
（2）CURRENT_TIMESTAMP()、LOCALTIME()、NOW()、SYSDATE()
这四个函数作用相同，返回当前日期和时间值，格式为"YYYY_MM-DD HH:MM:SS"或"YYYYMMDDHHMMSS"，具体格式根据函数用在字符串或数字语境中而定
（3）UNIX_TIMESTAMP()、UNIX_TIMESTAMP(date)
前者返回一个格林尼治标准时间1970-01-01 00:00:00到现在的秒数，后者返回一个格林尼治标准时间1970-01-01 00:00:00到指定时间的秒数
（4）FROM_UNIXTIME(date)
和UNIX_TIMESTAMP互为反函数，把UNIX时间戳转换为普通格式的时间
（5）UTC_DATE()和UTC_TIME()
前者返回当前UTC（世界标准时间）日期值，其格式为"YYYY-MM-DD"或"YYYYMMDD"，后者返回当前UTC时间值，其格式为"YYYY-MM-DD"或"YYYYMMDD"。具体使用哪种取决于函数用在字符串还是数字语境中
（6）MONTH(date)和MONTHNAME(date)
前者返回指定日期中的月份，后者返回指定日期中的月份的名称
（7）DAYNAME(d)、DAYOFWEEK(d)、WEEKDAY(d)
DAYNAME(d)返回d对应的工作日的英文名称，如Sunday、Monday等；DAYOFWEEK(d)返回的对应一周中的索引，1表示周日、2表示周一；WEEKDAY(d)表示d对应的工作日索引，0表示周一，1表示周二
（8）WEEK(d)、WEEKOFYEAD(d)
前者计算日期d是一年中的第几周，后者计算某一天位于一年中的第几周
（9）DAYOFYEAR(d)、DAYOFMONTH(d)
前者返回d是一年中的第几天，后者返回d是一月中的第几天
（10）YEAR(date)、QUARTER(date)、MINUTE(time)、SECOND(time)
YEAR(date)返回指定日期对应的年份，范围是1970~2069；QUARTER(date)返回date对应一年中的季度，范围是1~4；MINUTE(time)返回time对应的分钟数，范围是0~59；SECOND(time)返回制定时间的秒值
（11）EXTRACE(type FROM date)
从日期中提取一部分，type可以是YEAR、YEAR_MONTH、DAY_HOUR、DAY_MICROSECOND、DAY_MINUTE、DAY_SECOND
（12）TIME_TO_SEC(time)
返回以转换为秒的time参数，转换公式为"3600*小时 + 60*分钟 + 秒"
（13）SEC_TO_TIME()
和TIME_TO_SEC(time)互为反函数，将秒值转换为时间格式
（14）DATE_ADD(date,INTERVAL expr type)、ADD_DATE(date,INTERVAL expr type)
返回将起始时间加上expr type之后的时间，比如DATE_ADD('2010-12-31 23:59:59', INTERVAL 1 SECOND)表示的就是把第一个时间加1秒
（15）DATE_SUB(date,INTERVAL expr type)、SUBDATE(date,INTERVAL expr type)
返回将起始时间减去expr type之后的时间
（16）ADDTIME(date,expr)、SUBTIME(date,expr)
前者进行date的时间加操作，后者进行date的时间减操作
条件判断函数
1）IF(expr,v1,v2)
如果expr是TRUE则返回v1，否则返回v2
（2）IFNULL(v1,v2)
如果v1不为NULL，则返回v1，否则返回v2
（3）CASE expr WHEN v1 THEN r1 [WHEN v2 THEN v2] [ELSE rn] END
如果expr等于某个vn，则返回对应位置THEN后面的结果，如果与所有值都不想等，则返回ELSE后面的rn
系统信息函数
（1）VERSION()
查看MySQL版本号
（2）CONNECTION_ID()
查看当前用户的连接数
（3）USER()、CURRENT_USER()、SYSTEM_USER()、SESSION_USER()
查看当前被MySQL服务器验证的用户名和主机的组合，一般这几个函数的返回值是相同的
（4）CHARSET(str)
查看字符串str使用的字符集
（5）COLLATION()
查看字符串排列方式
在这里插入代码片
加密函数
（1）PASSWORD(str)
从原明文密码str计算并返回加密后的字符串密码，注意这个函数的加密是单向的（不可逆），因此不应将它应用在个人的应用程序中而应该只在MySQL服务器的鉴定系统中使用
（2）MD5(str)
为字符串算出一个MD5 128比特校验和，改值以32位十六进制数字的二进制字符串形式返回
（3）ENCODE(str, pswd_str)
使用pswd_str作为密码，加密str
（4）DECODE(crypt_str,pswd_str)
使用pswd_str作为密码，解密加密字符串crypt_str，crypt_str是由ENCODE函数返回的字符串
事务
概述
事务就是将一组SQL语句放在同一批次内去执行，如果一个SQL 语句出错，则该批次内的所有SQL都将被取消执行。
MySQL事务处理只支持InnoDB和BDB数据表类型。
事务特性
| 名称 | 解释 |
|---|---|
| 原子性 | 同一事务要么执行成功，要么执行失败。 |
| 隔离性 | 多个事务之间互不影响 |
| 持久性 | 事务提交或回滚后数据回永久保存 |
| 一致性 | 事务提交前后数据一致，在事务未提交或回滚时，数据会不一致，提交后，或者提交前才会一致 |
事务实现方法
SET AUTOCOMMIT
使用SET语句来改变自动提交模式
SET AUTOCOMMIT = 0; # 关闭自动提交模式
SET AUTOCOMMIT = 1; # 开启自动提交模式
MySQL中默认是自动提交
使用事务时应先关闭自动提交
START TRANSACTION
开始一个事务,标记事务的起始点
若想改变则ROLLBACK，将事务回滚，数据回到本次事务的初始状态
若想提交则COMMIT，提交一个事务给数据库
提交后不可改变
SET AUTOCOMMIT = 1;
开启MySQL数据库的自动提交
索引
作用
提高查询速度
确保数据的唯一性
可以加速表和表之间的连接，实现表与表之间的参照完整性
使用分组和排序子句进行数据检索时，可以显著减少分组和排序的时间
全文检索字段进行搜索优化
分类
主键索引（PRIMARY KEY）
唯一索引（UNIQUE）
常规索引（INDEX）
全文索引（FULLTEXT）
主键索引（PRIMARY KEY）
某一个属性或属性的组合能唯一标识一条记录,如：学生表（学号，姓名，班级，性别等） ，学号就是唯一标识的，可作为主键
特点:
最常见的索引类型
确保数据记录的唯一性
确定特定数据记录在数据库中的位置
唯一索引（UNIQUE）
作用
避免同一个表中某数据列中的值重复
与主键索引的区别
主键索引只能有一个
唯一索引可有多个
常规索引（INDEX）
作用：快速定位特定数据
注意
index和key关键字都可设置常规索引
应加在查找条件的字段
不宜添加太多常规索引，影响数据的插入、删除和修改操作
全文索引（FULLTEXT）
作用：快速定位特定数据
注意
只能用于MyISAM类型的数据表
只能用于 CHAR 、 VARCHAR、TEXT数据列类型
适合大型数据集
数据恢复和备份
数据库备份mysqldump
mysqlsump -h 主机名 -u 用户名 -p [options] 数据库名 > path/filename.sql
mysqldump常用选项
数据库恢复
方法一:
在mysql命令行里执行 source path/db_name.sql;
方法二:
用mysql客户端
mysql -u root -p 数据库名 < /path/db_name.sql
599
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
