MYSQL基础

1. MySQL 简介

Mysql是最流行的RDBMS(Relational Database Management System：关系数据库管理系统)，特别是在WEB应用方面。所谓的关系型数据库，是建立在关系模型基础上的数据库，借助于集合代数等数学概念和方法来处理数据库中的数据。‘
数据库（Database）是按照数据结构来组织、存储和管理数据的仓库，每个数据库都有一个或多个不同的API用于创建，访问，管理，搜索和复制所保存的数据。

RDBMS，关系数据库管理系统的特点：

数据以表格的形式出现
每行为各种记录名称
每列为记录名称所对应的数据域
许多的行和列组成一张表单
若干的表单组成database

一些术语：

数据库: 数据库是一些关联表的集合。
数据表: 表是数据的矩阵。在一个数据库中的表看起来像一个简单的电子表格。
列: 一列(数据元素) 包含了相同的数据, 例如邮政编码的数据。
行：一行（=元组，或记录）是一组相关的数据，例如一条用户订阅的数据。
冗余：存储两倍数据，冗余降低了性能，但提高了数据的安全性。
主键：主键是唯一的。一个数据表中只能包含一个主键。你可以使用主键来查询数据。
外键：外键用于关联两个表。
复合键：复合键（组合键）将多个列作为一个索引键，一般用于复合索引。
索引：使用索引可快速访问数据库表中的特定信息。索引是对数据库表中一列或多列的值进行排序的一种结构。类似于书籍的目录。
参照完整性: 参照的完整性要求关系中不允许引用不存在的实体。与实体完整性是关系模型必须满足的完整性约束条件，目的是保证数据的一致性。

SQL

SQL: 结构化查询语言(Structured Query Language)简称SQL,是最重要的关系数据库操作语言。有上百种数据库产品都支持SQL，如：MySQL、DB2、ORACLE、INGRES、SYBASE、SQLSERVER…

SQL包含6个部分：

数据查询语言（DQL：data Query Language）：SELECT
数据操作语言（DML：Manipulation）：INSERT, UPDATE, DELETE
事务处理语言（TPL）：BEGIN TRANSACTION，COMMIT和ROLLBACK
数据控制语言（DCL）：GRANT（授权）或REVOKE（回收权限）
数据定义语言（DDL）：CREATE、ALTER和DROP
指针控制语言（CCL）：DECLARE CURSOR，FETCH INTO和UPDATE WHERE CURRENT用于对一个或多个表单独行的操作

安装，连接

2. SQL基本操作

2.1 数据库操作

mysql> show databases;                              --查看当前用户下的所有数据库
mysql> create database [if not exists] 数据库名;  --创建数据库
mysql> use test;                                 --选择进入test数据库
mysql> show create database 数据库名\G               --查看建数据库语句 
mysql> select database();                           --查看当前所在的数据库位置 
mysql> drop database [if exists] 数据库名;           --删除一个数据库

2.2 数据表操作

mysql> show tables;             --查看当前库下的所有表格
mysql> desc tb1;                  --查看tb1的表结构。
mysql> show create table 表名\G  --查看表的建表语句。
mysql> create table demo(        --创建demo表格
    -> name varchar(16) not null,
    -> age int,
    -> sex enum('w','m') not null default 'm');
Query OK, 0 rows affected (0.05 sec)

mysql> show columns from demo;  --查看表结构
mysql> desc demo;                  --查看表结构
+-------+---------------+------+-----+---------+-------+
| Field | Type          | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| name  | varchar(16)   | NO   |     | NULL    |       |
| age   | int(11)       | YES  |     | NULL    |       |
| sex   | enum('w','m') | NO   |     | m       |       |
+-------+---------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql>drop table if exists mytab;  -- 尝试删除mytab表格

3.3 数据操作

--添加一条数据
mysql> insert into demo(name,age,sex) values('zhangsan',20,'w');
Query OK, 1 row affected (0.00 sec)

--不指定字段名来添加数据
mysql> insert into demovalues('lisi',22,'m'); 
Query OK, 1 row affected (0.00 sec)

--指定部分字段名来添加数据
mysql> insert into demo(name,age) values('wangwu',23); 
Query OK, 1 row affected (0.00 sec)

--批量添加数据
mysql> insert into demo(name,age,sex) values('aaa',21,'w'),("bbb",22,'m');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from demo; --查询数据

mysql> update demo set age=24 where name='aaa';  --修改
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> delete from demo where name='bbb';  --删除
Query OK, 1 row affected (0.00 sec)

3. MySQL数据结构类型及操作

数据类型分为三个类：数值类型、字串类型、日期类型。还有一个特殊的值：NULL 。

3.1 数据类型

数值类型

*tinyint(1字节) 0~255  -128~127
smallint(2字节)
mediumint(3字节)
*int(4字节)
bigint(8字节)
*float(4字节)   float(6,2)
*double(8字节)  
decimal(自定义)字串形数值

字串类型

普通字串
*char    定长字串        char(8)  
*varchar 可变字串 varchar(8)

二进制类型
tinyblob
blob
mediumblob
longblob

文本类型
tinytext
*text      常用于<textarea></textarea>
mediumtext
longtext

*enum枚举
set集合

时间和日期

date  年月日
time  时分秒
*datetime 年月日时分秒
timestamp 时间戳
year 年

NULL

1
2
3

NULL意味着没有值或未知值，可以测试某个值是否为NULL，但不能对其进行算数计算
计算结果还是NULL
0和NULL代表假，其余值为真

3.2 数据库操作

进入Mysql交互操作界面：
1
> mysql -u root -p
退出交互：
1
> quit;

3.2 数据表操作

运算符

1 2	算数运算符比较运算符、逻辑运算符（and or not）特有的：in，not in, is null,is not null,like, between and

表的字段约束

unsigned 无符号(正数)
zerofill 前导零填充
auto_increment 自增
default    默认值
not null  非空
PRIMARY KEY 主键 （非null并不重复）
unique 唯一性   （可以为null但不重复）
index 常规索引

建表语句格式

create table 表名(
	字段名 类型 [字段约束],
	字段名 类型 [字段约束],
	...
);

mysql> create table stu(
    -> id int unsigned not null auto_increment primary key,
    -> name varchar(8) not null unique,
    -> age tinyint unsigned,
    -> sex enum('m','w') not null default 'm',
    -> classid char(6)
    -> );

修改表的结构

1	alter table 表名 action（更改选项）;

更改选项：

1. 添加字段：alter table 表名 add 字段名信息
        例如：
            -- 在user表的最后追加一个num字段 设置为int not null
            mysql> alter table user add num int not null;

            -- 在user表的email字段后添加一个age字段，设置int not null default 20；
            mysql> alter table user add age int not null default 20 after email;

            -- 在user表的最前面添加一个aa字段设置为int类型
            mysql> alter table user add aa int first;


2. 删除字段：alter table 表名 drop 被删除的字段名
        例如：-- 删除user表的aa字段
             mysql> alter table user drop aa;

3. 修改字段：alter table 表名 change/modify 被修改后的字段信息
        其中：change可以修改字段名， modify 不修改
        例如：
        -- 修改user表中age字段信息（类型），（使用modify关键字的目的不修改字段名）
        mysql> alter table user modify age tinyint unsigned not null default 20;
        -- 修改user表的num字段改为mm字段并添加了默认值（使用change可以改字段名）
        mysql> alter table user change num mm int not null default 10;

4. 添加和删除索引
        -- 为user表中的name字段添加唯一性索引，索引名为uni_name;
        mysql> alter table user add unique uni_name(name);
        -- 为user表中的email字段添加普通索引，索引名为index_eamil
        mysql> alter table user add index index_email(email);
        -- 将user表中index_email的索引删除
        mysql> alter table user drop index index_email;

-
5. 更改表名称：
        ALTER TABLE 旧表名 RENAME AS 新表名

6. 更改AUTO_INCREMENT初始值:
        ALTER TABLE 表名称 AUTO_INCREMENT=1

7. 更改表类型：
        ALTER TABLE 表名称 ENGINE="InnoDB"

字符编码（支持中文）

1，创建table的时候就使用utf8编码

1 create table entries2 (
2         id     int auto_increment, 
3         title  text,
4         content  text,
5         posted_on  datetime,
6         primary key (id)   
7 ) character set = utf8;

2. 修改已有table的编码（只能保证新插入的数据正常显示）

alter table table_name convert to character set utf8;

MySQL数据库中的表类型一般常用两种：MyISAM和InnoDB

MyISAM类型的数据文件有三个frm(结构)、MYD（数据）、MYI（索引）
MyISAM类型中的表数据增删改速度快，不支持事务，没有InnoDB安全。
InnoDB类型的数据文件只有一个 .frm
InnoDB类型的表数据增删改速度没有MyISAM的快，但支持事务，相对安全。

3.3 数据操作

1. 添加数据

1	insert into 表名[(字段列表)] values(值列表...);

--标准添加（指定所有字段，给定所有的值）
   mysql> insert into stu(id,name,age,sex,classid) values(1,'zhangsan',20,'m','lamp138');
   Query OK, 1 row affected (0.13 sec)

   --指定部分字段添加值
   mysql> insert into stu(name,classid) value('lisi','lamp138');
   Query OK, 1 row affected (0.11 sec)

   -- 不指定字段添加值
   mysql> insert into stu value(null,'wangwu',21,'w','lamp138');
   Query OK, 1 row affected (0.22 sec)

   -- 批量添加值
   mysql> insert into stu values
       -> (null,'zhaoliu',25,'w','lamp94'),
       -> (null,'uu01',26,'m','lamp94'),
       -> (null,'uu02',28,'w','lamp92'),
       -> (null,'qq02',24,'m','lamp92'),
       -> (null,'uu03',32,'m','lamp138'),
       -> (null,'qq03',23,'w','lamp94'),
       -> (null,'aa',19,'m','lamp138');
   Query OK, 7 rows affected (0.27 sec)
   Records: 7  Duplicates: 0  Warnings: 0

2. 修改数据

1	update 表名 set 字段1=值1,字段2=值2,字段n=值n... where 条件

-- 将id为11的age改为35，sex改为m值
mysql> update stu set age=35,sex='m' where id=11;

-- 将id值为12和14的数据值sex改为m，classid改为lamp92
mysql> update stu set sex='m',classid='lamp92' where id=12 or id=14 --等价于下面
mysql> update stu set sex='m',classid='lamp92' where id in(12,14);

3. 删除数据

1	delete from 表名 [where 条件]

   -- 删除stu表中id值为100的数据
   mysql> delete from stu where id=100;

   -- 删除stu表中id值为20到30的数据
   mysql> delete from stu where id>=20 and id<=30;
mysql> delete from stu where id between 20 and 30;

4. 数据查询

格式：

select [字段列表]|* from 表名
    [where 搜索条件]
    [group by 分组字段 [having 子条件]]
    [order by 排序 asc|desc]
    [limit 分页参数]

-- 显示整张表
mysql> select * from stu;
-- 查询lamp138期的男生信息
mysql> select * from stu where classid='lamp138' and sex='m';
-- 查询id号在10以上的学生信息
mysql> select * from Stu where id>10
-- 查询年龄不在20至25岁的学生信息
mysql> select * from stu where age<20 or age>25;
-- 查询id值为1,8,4,10,14的学生信息
mysql> select * from stu where id in(1,8,4,10,14);
mysql> select * from stu where classid in('lamp138','lamp94') and sex='w';
--查询name的值为2~4位的小写字母
mysql> select * from stu where name regexp '^[a-z]{2,4}$';

3.4 数据库授权、备份、恢复

1. 授权

1	grant 允许操作 on 库名.表名 to 账号@来源 identified by '密码';