MySQL 快速入门（上篇）

1.什么是数据库

数据库(Database)是按照数据结构来组织、存储和管理数据的仓库

1.1 常见数据库

Oracle、DB2、SQL Server、Postgre SQL、MySQL

1.2 相关术语

数据库系统(Database System):DBS
数据库(Database)；数据库管理系统(Database Management System):DBMS；应用开发工具；管理员及用户
SQL语言：Structured Query Language：结构化查询语言

• DDL：数据定义语言
• DML：数据操作语言
• DQL：数据查询语言
• DCL：数据控制语言

2.MySQL相关操作

配置文件：my.cnf是MySQL的配置文件
登陆/退出MySQL：

• 登陆
  1. mysql -uroot -p
  2. mysql -uroot -proot
  3. 得到版本号：mysql -V ； mysql --version
  4. 登陆的同时打开指定数据库：mysql -uroot -p -D db_name
• 退出
  1. exit
  2. quit
• 登录信息中需要掌握的
  1. 命令行结束符默认使用;或者\g来结束
  2. 可以通过help或者\h或者?加上相关关键字来查看手册
  3. \c可以取消当前命令的执行
• SQL语句语法规范
  1. 常用MySQL的关键字我们需要大写，库名、表名、字段名称等使用小写
  2. SQL语句支持折行操作，拆分的时候不能把完整单词拆开
  3. 数据库名称、表名称、字段名称不要使用MySQL的保留字，如果必须要使用，需要用反引号``将其括起来
• 常用SQL语句
  1. SELECT USER()：得到登陆的用户
  2. SELECT VERSION()：得到MySQL的版本信息
  3. SELECT NOW()：得到当前的日期时间
  4. SELECT DATABASE()：得到当前打开的数据库

3.数据库相关操作

• 创建数据库：

  1. CREATE {DATABASE|SCHEMA} db_name;
  2. 检测数据库名称是否存在，不存在则创建：CREATE DATABASE [IF NOT EXISTS] db_name;
  3. 在创建数据库的同时指定编码方式：CREATE DATABASE [IF NOT EXISTS] db_name [DEFAULT] CHARACTER SET [=] charset;
  4. 注意：数据库名称最好有意义；名称不要包含特殊字符或者是MySQL关键字

• 查看当前服务器下全部数据库：SHOW DATABASES|SCHEMAS;

• 查看指定数据库的详细信息：SHOW CREATE DATABASE db_name;

• 修改指定数据库的编码方式：ALTER DATABASE db_name [DEFAULT] CHARACTER SET [=] charset;

• 打开指定数据库：USE db_name;

• 得到当前打开的数据库：SELECT DATABASE()|SCHEMA();

• 删除指定的数据库：DROP DATABASE db_name ; 如果数据库存在则删除：DROP DATABASE [IF EXISTS] db_name;

4.数据表相关操作

• 数据表：

  1. 是数据库最重要的组成部分之一，数据是保存在数据表中
  2. 数据表由行(row)和列(column)来组成
  3. 每个数据表中至少有一列，行可以有零行一行或者多行组成
  4. 表名要求唯一，不要包含特殊字符，最好含义明确

• 创建表：

  CREATE TABLE [IF NOT EXISTS] tbl_name(
  字段名称 字段类型 [完整性约束条件],
  字段名称 字段类型 [完整性约束条件],
  …
  )ENGINE=存储引擎 CHARSET=编码方式;

  1. UNSIGNED：无符号，没有负数，从0开始
  2. ZEROFILL：零填充，当数据的显示长度不够的时候可以使用前补0的效果填充至指定长度,字段会自动添加UNSIGNED
  3. NOT NULL：非空约束，也就是插入值的时候这个字段必须要给值,值不能为空
  4. DEFAULT：默认值，如果插入记录的时候没有给字段赋值，则使用默认值
  5. PRIMARY KEY：主键，标识记录的唯一性，值不能重复，一个表只能有一个主键，自动禁止为空
  6. AUTO_INCREMENT：自动增长，只能用于数值列，而且配合索引使用,默认起始值从1开始，每次增长1
  7. UNIQUE KEY：唯一性，一个表中可以有多个字段是唯一索引，同样的值不能重复，但是NULL值除外
  8. FOREIGN KEY：外键约束
     举例：

  --创建数据库
  CREATE DATABASE IF NOT EXISTS king DEFAULT CHARACTER SET 'UTF8';
  USE king;
  --用户表imooc_user
  CREATE TABLE IF NOT EXISTS imooc_user(
  	id INT UNSIGNED AUTO_INCREMENT KEY COMMENT '用户编号',
  	username VARCHAR(20) NOT NULL UNIQUE COMMENT '用户名',
  	password CHAR(32) NOT NULL COMMENT '密码',
  	email VARCHAR(50) NOT NULL UNIQUE COMMENT '邮箱',
  	age TINYINT UNSIGNED NOT NULL DEFAULT 18 COMMENT '年龄',
  	sex ENUM('男','女','保密') NOT NULL DEFAULT '保密' COMMENT '性别',
  	tel CHAR(11) NOT NULL UNIQUE COMMENT '电话',
  	addr VARCHAR(50) NOT NULL DEFAULT '北京' COMMENT '地址',
  	card CHAR(18) NOT NULL UNIQUE COMMENT '身份证',
  	married TINYINT(1) NOT NULL DEFAULT 0 COMMENT '0代表未结婚，1代表已结婚',
  	salary FLOAT(8,2) NOT NULL DEFAULT 0 COMMENT '薪水'
  )ENGINE=INNODB DEFAULT CHARSET=UTF8;
  

• 查看当前数据库下已有数据表
  SHOW TABLES;
SHOW [FULL] TABLES [{FROM | IN} db_name] [LIKE 'pattern' | WHERE expr];

• 查看指定数据表的详细信息：SHOW CREATE TABLE tbl_name;

• 查看表结构：DESC tbl_name; DESCRIBE tbl_name; SHOW COLUMNS FROM tbl_name;

• 删除指定的数据表:DROP TABLE [IF EXISTS] tbl_name;

• 表结构相关操作:

  1. 添加字段：ALTER TABLE tbl_name ADD 字段名称 字段属性 [完整性约束条件] [FIRST|AFTER 字段名称]
  2. 删除字段：ALTER TABLE tbl_name DROP 字段名称
  3. 添加默认值：ALTER TABLE tbl_name ALTER 字段名称 SET DEFAULT 默认值;
  4. 删除默认值：ALTER TABLE tbl_name ALTER 字段名称 DROP DEFAULT
  5. 修改字段类型、字段属性：ALTER TABLE tbl_name MODIFY 字段名称 字段类型 [字段属性] [FIRST | AFTER 字段名称]
  6. 修改字段名称、字段类型、字段属性：ALTER TABLE tbl_name CHANGE 原字段名称 新字段名称 字段类型 字段属性 [FIRST | AFTER 字段名称]
  7. 添加主键：ALTER TABLE tbl_name ADD PRIMARY KEY(字段名称)
  8. 删除主键：ALTER TABLE tbl_name DROP PRIMARY KEY;
  9. 添加唯一：ALTER TABLE tbl_name ADD UNIQUE KEY|INDEX [index_name] (字段名称)
  10. 删除唯一：ALTER TABLE tbl_name DROP index_name;
  11. 修改数据表名称：ALTER TABLE tbl_name RENAME [TO|AS] new_tbl_name 或 RENAME TABLE tbl_name TO new_tbl_name;
  12. 修改AUTO_INCREMENT的值：ALTER TABLE tbl_name AUTO_INCREMENT=值

举例：

--删除唯一索引 username 和 email
ALTER TABLE user6
DROP INDEX username;

--添加唯一索引
ALTER TABLE user6
ADD UNIQUE INDEX username;

ALTER TABLE user6
ADD UNIQUE INDEX uni_email(email); // 添加自定义索引值	

5.MySQL中的数据类型

• 数值型:

  1. 整数型
     

  2. 浮点型
     

  3. 字符串类型
     CHAR效率高于VARCHAR,CHAR相当于拿空间换时间，VARCHAR拿时间换空间
     CHAR默认存储数据的时候，后面会用空格填充到指定长度；而在检索的时候会去掉后面空格；VARCHAR在保存的时候不进行填充，尾部的空格会留下
     TEXT列不能有默认值,检索的时候不存在大小写转换

  4. 日期时间类型
     

6.MySQL存储引擎

• MyISAM存储引擎

  1. 默认MyISAM的表会在磁盘中产生3个文件
     .frm：表结构文件
     .MYD：数据文件
     .MYI：索引文件
  2. 可以在创建的时候指定数据文件和索引文件的存储位置，只有MyISAM表支持
     DATA DIRECORY [=] 数据保存的绝对路径
     INDEX DIRECTORY [=] 索引文件保存的绝对路径
  3. MyISAM单表最大支持的数据量2的64次方条记录
  4. 每个表最多可以建立64个索引
  5. 如果是复合索引，每个复合索引最多包含16个列，索引值最大长度是1000B
  6. MyISAM引擎的存储格式
     定长（FIXED 静态）：是指字段中不包含VARCHAR/TEXT/BLOB
     动态（DYNAMIC）：只要字段中包含了VARCHAR/TEXT/BLOB
     压缩（COMPRESSED）：myisam pack创建

• InnoDB存储引擎

  1. 设计遵循ACID模型，支持事务，具有从服务崩溃中恢复的能力，能够最大限度保护用户的数据
  2. 支持行级锁，可以提升多用户并发时的读写性能
  3. 支持外键，保证数据的一致性和完整性
  4. InnoDB拥有自己独立的缓冲池，常用的数据和索引都在缓存中
  5. 对于INSERT、UPDATE、DELETE操作，InnoDB会使用一种change buffering的机制来自动优化，还可以提供一致性的读，并且还能够缓存变更的数据，减少磁盘I/O，提高性能
  6. 创建InnoDB表之后会产生两个文件
     .frm表结构文件
     .ibd,数据和索引存储表空间中
  7. 所有的表都需要创建主键，最好是配合上AUTO_INCREMENT,也可以放到经常查询的列作为主键