MySQL学习(一)

MySQL语句的规范

• 关键子与函数名称全部大写
• 数据库名称、表名称、字段名称全部小写
• SQL语句必须以分号结尾

数据类型

整形

• BIT
• TINYINT
• BOOL、BOOLEAN
• SMALLINT
• MEDIUMINT
• INT
• INTEGER
• BIGINT

浮点型

• FLOAT
• DOUBLE

日期和时间类型

• YEAR
  

  MySQL以YYYY 格式显示YEAR值，但允许使用字符串或数字为YEAR列分配值

• TIME
  MySQL以’HH:MM:SS’格式显示TIME值，但允许使用字符串或数字为TIME列分配值
• TIMESTAMP
  TIMESTAMP值返回后显示为’YYYY-MM-DD HH:MM:SS’格式的字符串，显示宽度固定为19个字符
• DATETIME
  MySQL以’YYYY-MM-DD HH:MM:SS’格式显示DATETIME值，但允许使用字符串或数字为DATETIME列分配值。
• DATE
  MySQL以’YYYY-MM-DD’格式显示DATE值，但允许使用字符串或数字为DATE列分配值。

字符串类型

• CHAR
  

  固定长度字符串，当保存时在右侧填充空格以达到指定的长度。

• VARCHAR
  变长字符串
• TINYTEXT
• TEXT
• MEDIUMTEXT
• LONGTEXT
• ENUM(‘value1’,’value2’,…)
  枚举类型。只能有一个值的字符串，从值列’value1’，’value2’，…，NULL中或特殊 ”错误值中选出。ENUM值在内部用整数表示.
• SET(‘value1’,’value2’,…)
  一个设置。字符串对象可以有零个或多个值，每个值必须来自列值’value1’，’value2’，…SET列最多可以有64个成员。SET值在内部用整数表示。

约束条件

• NULL/NOT NULL 该字段可以/不可以为空
• AUTO_INCREMENT 自动编号(该字段必须为主键约束,编号的数字为数值型,若为浮点数则小数位数为0)
• PRIMARY KEY 主键
  
  • 每张数据表只能有存在一个主键
  • 主键保证了记录的唯一性
  • 主键自动为NOT NULL
  • 主键约束自动创建索引
• DEFAULT 默认值
  

  当插入记录时,如果没有明确为字段赋值,则自动赋予默认值.

• UNIQUE KEY 唯一约束(每张数据表可以有多个字段唯一约束).

• FOREIGN KEY 外键约束

  保证数据的一致性和完整性,实现一对一或一对多的关系.
  要求:

  1. 父表和子表必须使用相同的存储引擎,而且禁止使用临时表.
  2. 数据表的存储引擎只能为InnoDB.(mysql配置文件中default-storage-engine=INNODB).
  3. 外键列和参照列必须具有相似的数据类型.其中数字的长度或是否有符号位必须相同;而字符的长度则可以不同.()
  4. 外键列和参照列必须创建索引.如果外键列不存在索引,Mysql将自动创建索引.

  [CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, ...)
REFERENCES tbl_name (index_col_name, ...)
[ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION}]
[ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION}]
//1.CASCADE: 从父表删除或更新且自动删除或更新子表中匹配的行
//2.SET NULL: 从父表删除或更新行，并设置子表中的外键列为NULL,(子表中该列没有NOT NULL 约束)
//3. NO ACTION:NO ACTION意味这不采取动作，就是如果有一个相关的外键值在被参考的表里，删除或更新主要键值的企图不被允许进行.
//4.RESTRICT: 拒绝对父表的删除或更新操作.

修改数据表

• 添加列

  Alter Table tbl_name Add[column](col_name column_definition,...)

• 删除列

  Alter Table tbl_name Drop[column](col_name)

• 添加约束

  Alter Table tbl_name Add[Constrant[symbol]]PRIMARY KEY[index_type](index_col_name)//添加主键约束

• 删除约束

  Alter Table tbl_name Drop {Index|Key}(index_name)//删除唯一约束
ALter Table tbl_name Drop Foreign Key fk_symbol//删除外键约束

• 修改列定义

  Alter Table tbl_name Modify [Column] column_definition [FIRST|AFTER col_name]
Alter Table tbl_name Change [Column] old_col_name column_definition[FIRST|AFTER col_name]

插入记录

//第一种方式
Insert [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
[Into] tbl_name [(col_name,...)]
values ({expr | DEFAULT},...),(...),...
[ ON DUPLICATE KEY UPDATE col_name=expr, ... ]
//第二种方式
Insert [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
[Into] tbl_name 
Set col_name={expr|Default},...
[ ON DUPLICATE KEY UPDATE col_name=expr, ... ]
//第三种方式
Insert [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
[Into] tbl_name [(col_name,...)]
Select ...
[ ON DUPLICATE KEY UPDATE col_name=expr, ... ]

更新记录

1. 单表更新:

   Update [LOW_PRIORITY] [IGNORE] tbl_name
   Set col_name1=expr1 [, col_name2=expr2 …]
   [Where where_definition]
   [ORDER BY …]
   [LIMIT row_count]

2. 多表更新:

   UPDATE [LOW_PRIORITY] [IGNORE] table_references
   SET col_name1=expr1 [, col_name2=expr2 …]
   [WHERE where_definition]

删除记录

1. 单表语法:

   //删除自增长字段时删除了8,新增加的数据依旧从9开始.
   DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name
   [WHERE where_definition]
   [ORDER BY …]
   [LIMIT row_count]

2. 多表语法:

   //第一种
   DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
   tbl_name[.] [, tbl_name[.] …]
   FROM table_references
   [WHERE where_definition]
   //第二种
   DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
   FROM tbl_name[.] [, tbl_name[.] …]
   USING table_references
   [WHERE where_definition]

查询记录

SELECT
[ALL | DISTINCT | DISTINCTROW ]
  [HIGH_PRIORITY]
  [STRAIGHT_JOIN]
  [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
  [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
//查询表达式, 使用AS alias_name可以为select_expr给定一个别名。
//此别名用作表达式的列名，可以用于GROUP BY、ORDER BY或HAVING子句。
//不可以使用在WHERE中.
select_expr, ...
[INTO OUTFILE 'file_name' export_options
  | INTO DUMPFILE 'file_name']
[FROM table_references//数据表的名字
//查询条件
[WHERE where_definition]
//输出结果集根据GROUP BY列进行分组
[GROUP BY {col_name | expr | position}
  [ASC | DESC], ... [WITH ROLLUP]]
//设置分组条件.(后面的语法必须为一个聚合函数或者条件字段是在Select的查找字段)
[HAVING where_definition]
//对查询结果进行排序(可以按多个字段排序)
[ORDER BY {col_name | expr | position}
  [ASC | DESC] , ...]
//限制查询结果集返回的数量
//(1)offset:从哪里开始查询,下标从0开始
//(2)row_count:查询数量
[LIMIT {[offset,] row_count | row_count OFFSET offset}]
[PROCEDURE procedure_name(argument_list)]
[FOR UPDATE | LOCK IN SHARE MODE]]

多表连接

//table_reference为表名
//join_condition为连接条件
table_reference [INNER | CROSS] JOIN table_factor [join_condition]//内连接:INNER JOIN与ON子句同时使用，CROSS JOIN以其它方式使用
| table_reference LEFT [OUTER] JOIN table_reference join_condition//左外连接
| table_reference RIGHT [OUTER] JOIN table_reference join_condition//右外连接

表的自身连接

表的自身连接时必须给表起别名.JOIN两边都是该表本身.