MYSQL手册

时间:2023-03-09 01:19:57
MYSQL手册

 原文出处:http://www.cnblogs.com/gaofei-1/p/7152875.html

MySQL配置文件

 MySQL软件使用的配置文件名为my.ini,在安装目录下。

  MySQL常用配置参数:

    1.default-character-set:客户端默认字符集。

    2.character-set-server:服务器端默认字符集。

    3.port:客户端和服务器端的端口号。

    4.default-storage-engine:MySQL默认存储引擎。

MySQL附带系统数据库

  1.information_schema:主要存储系统中的一些数据库对象信息,如用户表信息、字段信息、权限信息、字符集信息和分区信息等。

  2.performance_schema:主要存储数据库服务器性能参数。

  3.mysql:主要存储系统的用户权限信息。

  4.test:MySQL数据库管理系统自动创建的测试数据库,任何用户都可以使用。

结构化查询语言(概念)

  1.DML(数据操作语言):用来插入、修改和删除表中的数据,如insert、update、delete语句。

  2.DDL(数据定义语言):在数据库中创建或删除数据库对象等操作,如create、drop、alter等语句。

  3.DQL(数据查询语言):用来对数据库中的数据进行查询,如select语句。

  4.DCL(数据控制语言):用来控住数据库组件的存取许可、存取权限等,如grant、revoke等。

MySQL常用数据类型

常用数值类型
数据类型 字节数
tinyint[(M)] 1字节
smallint[(M)] 2字节
mediumint[(M)] 3字节
int[(M)] 4字节
float[(M,D)] 4字节
double[(M,D)] 8字节
decimal[(M,d)] M+2字节
字符串类型
数据类型 字节 说明
char[(M)] M字节

固定长度字符串

M为0~255的整数

varchar[(M)] 可变长度

可变长度

M为0~65535的整数

tinytext 0~255 微型文本串
text 0~65535 文本串
日期类型
数据类型 格式 取值范围
date yyyy-mm-dd 1000-01-01~9999-12-31
datetime yy-mm-dd hh:mm:ss 1000-01-01 00:00:00~9999-12-31 23:59:59
time hh:mm:ss -835:59:59~838:59:59
timestamp yyyymmddhhmmss 1970年某时刻至2038年某时刻,精度为1秒
year yyyy格式的年份 1901-2155

MySQL字段常用属性约束

常用的属性约束
字段属性、约束名 关键字 说明
非空约束 not null 字段非空
默认约束 default 设置默认值
唯一约束 unique key 设置字段值唯一,允许但只能有一个空值
主键约束 primary key 设置主键
外键约束 foreign key 设置外键
自动增长 auto_increment 设置字段为自增长,可以设定初始值和步长

MySQL存储引擎

  MySQL支持的存储引擎有InnoDB、MylSAM、Memory、MRG_MylSAM、Acrchive、Federated、CSV、BLACKHOLE等九种,可以使用

  show engines语句查看系统所支持的引擎类型。

  以下列举两个常用的引擎

InnoDB和MylSAM存储引擎比较
功能 InnoDB MylSAM
支持事物 支持 不支持
支持全文索引 不支持 支持
外键约束 支持 不支持
表空间大小 较大 较小
数据行锁定 支持 不支持

  

  InnoDB存储引擎主要在事务处理上由优势,如果需要频繁的更新、删除操作,同时还对事务的完整性要求比较高,需要实现并发控制,则适合

  使用该引擎。

  MylSAM存储引擎主要是访问速度比较快,适合以访问为主的应用。

  查看当前默认的存储引擎

      show variables like 'storage_engine%';

MySQL数据文件

  1.存储位置

      默认在data文件夹下。

  2.MylSAM类型的表文件

      .frm文件:表结构定义文件。主要存放表的元数据,宝库哟表结构定义信息等。该文件与存储引擎无关,任何存储类型的表都会有这个文件。

      .MYI文件:索引文件。主要存放MylSAM类型表的索引信息,每个MylSAM类型的表会有一个.MYI文件,存放的位置与.frm文件相同。

      .MYD文件:数据文件。存放表中数据的文件。

  3.InnoDB类型的表文件

      .frm文件:表结构定义文件。主要存放表的元数据,宝库哟表结构定义信息等。该文件与存储引擎无关,任何存储类型的表都会有这个文件。

      ibd文件:数据文件。保存所有InnoDB类型表的数据。这个文件的保存位置可以通过my.ini文件中的参数查询或修改。例:

          innodb_data_home_dir:"文件存放路径";

命令行登录MySQL数据库

  mysql -u root -proot

  或

  mysql -u root -p

  enter password:root

MySQL操作数据库语法

  1.创建数据库

      create database [if not exists] 数据库名;

  2.删除数据库

      drop database [if exists] 数据库名;

  3.切换数据库

      use 数据库名;

  4.查看所有数据库

      show databases;

MySQL操作表语法

  1.创建表

      create table [if not exists] 表名 (

        字段1 数据类型 [字段属性|约束] [索引] [注释],

        字段2 数据类型 [字段属性|约束] [索引] [注释],

        ......

        字段n 数据类型 [字段属性|约束] [索引] [注释]

      ) [表类型] [表字符集] [注释];

  2.删除表

      drop table [if exists] 表名;

  3.查看当前数据库所有表

      show tables;

  4.查看表定义

      describe 表名;

      或

      desc 表名;

  5.在dos窗口设置MySQL默认字符集编码

      set names gbk;

      或

      set character_set_client=gbk;

      set character_set_results=gbk;

      set character_set_connection=gbk;

  6.修改表名

      alter table 旧表名 rename [to] 新表名;

  7.添加字段

      alter table 表名 add 字段名 数据类型 [属性];

  8.修改字段

      alter table 表名 change 原字段名 新字段名 数据类型 [属性];

  9.删除字段

      alter table 表名 drop 字段名;

  10.添加主键

      alter table 表名 add constraint 主键名 primary key 表名(主键字段);

      如果在创建表时添加则只需primary key

  11.添加外键

      alter table 表名 add constraint 外键名 foreign key(外键字段) references 关联表名(关联字段);

      如果在创建表时添加则只需references 关联表名(关联字段)

MySQL数据操作

  1.添加数据

      和SQL一样,只是可以一条语句添加多条记录。insert into 表名 values(),values(),values(),...;

  2.将查询结果添加到新表

      insert into 新表(字段) select 字段 from 原表;         (注:新表需按插入字段的类型、顺序、个数提前创建好)

      或

      create table 新表名(select 字段 from 原表);          (注:新表无需提前创建)

  3.删除数据

      delete from 表名;    (注:不会删除自增列信息)

      或

      truncate table 表名;     (注:会删除自增列信息,执行速度比delete块)

  4.分页查询

      select * from 表名 limit 起始行,查询行数;    (注:起始行从0开始)

      或

      select * from 表名 limit 查询行数;

MySQL常用函数

  

常用聚合函数
函数名 作用
  • AVG([DISTINCT] expr)

返回expr 的平均值。 DISTINCT 选项可用于返回 expr的不同值的平均值。

若找不到匹配的行,则AVG()返回 NULL 。

  • COUNT(expr)

返回SELECT语句检索到的行中非NULL值的数目。

若找不到匹配的行,则COUNT() 返回 0 。

  • MIN([DISTINCT] expr), MAX([DISTINCT] expr)
返回expr 的最小值和最大值。 MIN() 和 MAX() 的取值可以是一个字符串参数;在这些情况下, 它们返回最小或最大字符串值。DISTINCT关键词可以被用来查找expr 的不同值的最小或最大值,然而,这产生的结果与省略DISTINCT 的结果相同。

若找不到匹配的行,MIN()和MAX()返回 NULL 。

  • SUM([DISTINCT] expr)

返回expr 的总数。 若返回集合中无任何行,则 SUM() 返回NULL。DISTINCT 关键词可用于 MySQL 5.1 中,求得expr 不同值的总和。

若找不到匹配的行,则SUM()返回 NULL。

      

常用字符串函数
函数名 作用
  • CONCAT(str1,str2,...)

返回结果为连接参数产生的字符串。如有任何一个参数为NULL ,则返回值为 NULL。或许有一个或多个参数。 如果所有参数均为非二进制字符串,则结果为非二进制字符串。 如果自变量中含有任一二进制字符串,则结果为一个二进制字符串。一个数字参数被转化为与之相等的二进制字符串格式;若要避免这种情况,可使用显式类型 cast, 例如: SELECT CONCAT(CAST(int_col AS CHAR), char_col)

  • INSERT(str,pos,len,newstr)

返回字符串 str, 其子字符串起始于 pos 位置和长期被字符串 newstr取代的len 字符。  如果pos 超过字符串长度,则返回值为原始字符串。 假如len的长度大于其它字符串的长度,则从位置pos开始替换。若任何一个参数为null,则返回值为NULL。

  • LOWER(str)

返回字符串 str 以及所有根据最新的字符集映射表变为小写字母的字符 (默认为  cp1252 Latin1)。

  • UPPER(str)

返回字符串str, 以及根据最新字符集映射转化为大写字母的字符 (默认为cp1252 Latin1).

  • SUBSTRING(str,pos) , SUBSTRING(str FROM pos) SUBSTRING(str,pos,len) , SUBSTRING(str FROM posFOR len)

不带有len 参数的格式从字符串str返回一个子字符串,起始于位置 pos。带有len参数的格式从字符串str返回一个长度同len字符相同的子字符串,起始于位置 pos。 使用 FROM的格式为标准 SQL 语法。也可能对pos使用一个负值。假若这样,则子字符串的位置起始于字符串结尾的pos 字符,而不是字符串的开头位置。在以下格式的函数中可以对pos 使用一个负值。

常用日期函数
函数名 作用
  • CURDATE()
将当前日期按照'YYYY-MM-DD' 或YYYYMMDD 格式的值返回,具体格式根据函数用在字符串或是数字语境中而定。
  • CURTIME()

将当前时间以'HH:MM:SS'或 HHMMSS 的格式返回, 具体格式根据函数用在字符串或是数字语境中而定。

  • NOW()

返回当前日期和时间值,其格式为 'YYYY-MM-DD HH:MM:SS' 或YYYYMMDDHHMMSS , 具体格式取决于该函数是否用在字符串中或数字语境中。

  • WEEK(date[,mode])
该函数返回date 对应的星期数。WEEK() 的双参数形式允许你指定该星期是否起始于周日或周一, 以及返回值的范围是否为从0 到53 或从1 到53。若 mode参数被省略,则使用default_week_format系统自变量的值。
  • YEAR(date)

返回date 对应的年份,范围是从1000到9999。

  • HOUR(time)

返回time 对应的小时数。对于日时值的返回值范围是从 0 到 23

  • MINUTE(time)

返回 time 对应的分钟数,范围是从 0 到 59。

  • DATEDIFF(expr,expr2)

DATEDIFF() 返回起始时间 expr和结束时间expr2之间的天数。Exprexpr2 为日期或 date-and-time 表达式。计算中只用到这些值的日期部分。

  • ADDDATE(expr,days)
计算date日期+days天后的日期
常用数学函数
函数名 作用
ceil(x) 返回大于等于x的最小整数
floor(x) 返回小于等于x的最大整数
rand() 返回0~1之间的随机数

MySQL事务、视图、索引、备份和恢复

  事务

      begin;(开始)

      commit;(提交)

      rollback;(回滚)

      set autocommit=0;(关闭自动提交)

      set autocommit=1;(开启自动提交)

  视图

      创建

        create view 视图名

        as

        select * from 表

      删除

        drop view 视图名

      注意:对视图中的数据进行操作将直接引用表中的数据,但如果视图来自多个表则不允许操作数据。

  索引

      创建

        create index 索引名 on 表名(字段)

      删除

        drop index 表名.索引名

      查看

        show index from 表名

  备份数据库

      mysqldump -u root -proot 数据库名 > 保存路径

      或

      mysqldump -u root -p 数据库名 > 保存路径

      enter password root

  恢复数据库

      mysql -u root -p 数据库名 < 路径

      或

      source filename(需登录MySQL服务器)

  导出表数据

      select * from 表 into outfile '路径'      (注:此操作路径需和my.ini配置文件中的secure-file-priv路径一致)

  导入表数据

      load data infile 路径 into table 表名

欢迎提出意见或建议!!!