MySQL

一、SQL语句

SQL结构化查询语句，为数据库而建立的操作命令集。

1、SQL语句规范

• 在数据库系统中，SQL语句不区分大小写（建议用大写），但字符串的常量区分大小写，建议命令大写，表名和库名小写；
• SQL语句可单行或者多行书写，最后以英文分号;结尾；
• 单行注释：--
• 多行注释：/*...*/

2、SQL语句分类

• DDL数据定义语言，负责数据库定义，数据库对象定义，由CREATE、ALTER与DROP三个语法所组成。
• DML数据操作语言，负责对数据库对象的操作，CRUD增删改查。
• DCL数据控制语言，负责数据库权限访问控制，由GRANT和REVOKE两个指令组成。
• TCL事物控制语言，负责处理ACID事物，支持commit、rollback指令。

二、MySQL数据库

1、数据库安装

首先安装mysql，安装之前需要配置yum源（我配置的是阿里的源）：

　　yum install mysql-server

然后启动服务：

　　service mysqld start

第一次需要配置root密码：

　　mysqladmin -u root password "mysql"

进入数据库：

　　mysql -uroot -pmysql

2、数据库操作

(1)显示数据库

show databases;

(2)创建数据库

create database 数据库名称 default charset 字符集 collate 字符排序方式;

create database test1 default charset utf8 collate utf8_general_ci;

(3)删除数据库

drop database test1;

(4)使用数据库

进入数据库：

　　use test;

查看当前数据库中所有的表：

　　show tables;

查看当前正在使用的数据库：

　　select database();

(5)用户管理

查看当前已经授权的用户：

　　use mysql

　　select host,user from user;

创建一个用户：

　　create user '用户名'@'IP地址' identified by '密码';

　　create user "wj"@"%" identified by "wj";

　　%代表所有的ip都可以访问，这样做要考虑安全性的问题

删除已授权的用户：

　　drop user "wj"@"%";

修改已授权用户的用户名和Ip：

　　rename user "wj"@"%" to "wangjie"@"127.0.0.1";

修改用户密码：

　　set password for "wangjie"@"127.0.0.1" = Password("wangjie");

(6)授权管理

查看权限：

　　show grants for "wangjie"@"127.0.0.1";

授权用户权限：

　　grant select on *.* to "wangjie"@"127.0.0.1";    # 查权限，*.*表示所有数据库的所有表

　　grant all privileges on *.* to "wangjie"@"127.0.0.1";  # 所有权限，权限过大需要考虑安全性问题

取消权限：

　　revoke select on *.* from "wangjie"@"127.0.0.1";

　　revoke all privileges on *.* from "wangjie"@"127.0.0.1";

三、数据类型

1、数值型

数值型

类型	大小	用途
tinyint	1字节	小整数值
smallint	2字节	大整数值
mediumint	3字节	大整数值
int或者integer	4字节	大整数值
bigint	8字节	极大整数值
float	4字节	单精度（浮点数值）
double	8字节	双精度（浮点数值）

 

 

 

 

 

 

 

 

2、日期和时间类型

 日期和时间类型

类型	大小	用途
date	3字节	日期值
time	3字节	时间值或者持续时间
year	1字节	年份值
datetime	8字节	混合日期和时间值
timestamp	4字节	混合日期和时间值，时间戳

 

 

 

 

 

 

 

3、字符串类型

字符串类型

类型	大小	用途
char	0-255字节	定长字符串
varchar	0-65535字节	变长字符串
tinyblob	0-255字节	不超过255个字符的二进制字符串
tinytext	0-255字节	短文本字符串
blob	0-65535字节	二进制形式的长文本数据
text	0-65535字节	长文本数据
mediumblob	0-16777215字节	二进制形式的中等长度文本数据
mediumtext	0-16777215字节	中等长度文本数据
longblob	0-4294967295字节	二进制形式的极大文本数据
longtext	0-4294967295字节	极大文本数据

 

 

 

 

 

 

 

 

 

 

四、存储引擎

1、MyISAM和InnoDB的适用场景

• MyISAM适合：（1）做很多的count计算　　（2）插入不频繁，查询非常频繁　　（3）没有事务
• InnoDB适合：（1）可靠性要求比较高，或者要求事务　　（2）表更新和查询都相当频繁，并且表锁定的机会比较大的情况。

2、MyISAM和InnoDB的区别

• MyISAM不支持事务处理等高级处理，而InnoDB支持
• MyISAM不支持外键
• 在执行数据库写入操作的时候，MyISAM会锁表，InnoDB或锁行
• 在数据库有大量数据写入，更新操作，查询比较少要求数据完整性比较高的话就选择InnoDB，在数据库中主要以查询为主，写入和更新操作少，并且对于数据完整性没有那么严格的话，选择MyISAM

五、数据库表的操作

1、表的定义、删除、修改

（1）语法

• create table 表名 (字段名 字段类型)

（2）示例：创建一张表　

　　mysql> create table student(
　　-> id int not null auto_increment,
　　-> name char(32) not null,
　　-> age int not null,
　　-> register_date date,
　　-> primary key(id));

（3）表的相关操作

• 查看该数据库中已经创建的表：show tables;
• 查看表的结构信息：desc 表名;
• 查看表定义时的语法信息：show create table 表名;
• 删除表：drop table 表名;

（4）修改表的相关操作

• 增加字段：alter table 表名 add 字段名 类型;
• 删除字段：alter table 表名 drop column 字段名;
• 修改字段：alter table 表名 change 原字段名 新字段名 类型;
• 修改表名：alter table 表名 rename to 新表名;
• 添加主键：alter table 表名 add primary key(字段名);
• 删除主键：alter table 表名 modify 字段名 int,drop primary key;
• 添加外键：alter table 从表 add constraint 外键名称（形如：FK_从表_主表） foreign key 从表(外键字段) references 主表(主键字段);
• 删除外键：alter table 表名 drop foreign key 外键名称;

2、表内容的相关操作

（1）插入数据

• 语法：insert into 表名 (字段1, 字段2, ...) values (值1, 值2, ...);

（2）示例：插入内容

　　mysql> insert into students (name, age, register_date) values ('wj', 22, '2017-01-01');

（3）查看数据

• 语法：select 字段1, 字段2, ... from 表名 [where 查询条件] [offset 偏移量] [limit 返回记录数];

（4）示例：查询内容

　　mysql> select * from students limit 2 offset 1;          * 显示所有字段　　limit 2 返回两条记录　　offset 1 从第1条开始查

　　mysql> select * from students where id>1;　　查询id大于1的记录

　　mysql> select * from students where id>1 and age<22;　　查询所有id大于1并且age小于22的记录

　　mysql> select * from students where name like 'w%';　　like 模糊匹配，查询所有name字段以w开头的记录

（5）修改记录

　　mysql> update students set name='yzw' where id=1;　　将id等于1的记录的字段name的值改为yzw

（6）删除记录

　　mysql> delete from students where id>3;　　将所有id大于3的记录删除

（7）排序

　　mysql> select * from students order by id;　　按照id升序排列

　　mysql> select * from students order by id desc;　　按照id降序排列

（8）分组

　　mysql> select name, count(*) from students group by name;　　按照姓名分组，并统计姓名出现的次数

　　mysql> select name, sum(age) from students group by name;　　按照姓名分组，并将年龄进行相加

3、表的连接

（1）内连接或者等值连接inner join

• 获取两个表中字段匹配关系的记录

　　mysql> select * from A inner join B on A.a=B.b;　　交集

（2）左连接left join和右连接right join

• 获取左表所有记录，即使右表没有对应匹配的记录
• 获取右表所有记录，即使左表没有对应匹配的记录

　　mysql> select * from A left join B on A.a=B.b;　　差集

　　mysql> select * from A right join B on A.a=B.b;　　差集

　　mysql> select * from A left join B on A.a=B.b union select * from A right join B on A.a=B.b;　　并集

六、事务

关系型数据库支持事务，必须支持其四个属性（ACID）：

• 原子性：要求事务中的所有操作不可分割，不能做了一部分操作，还剩一部分操作
• 一致性：多个事务并行执行的结果，应该和事务排队执行的结果一致，如果事务的并行执行结果和多线程读写共享资源一样不可预期，就不能够保证一致性
• 隔离性：多个事务访问共同的数据，应该互不干扰
• 持久性：事务一旦提交之后，对数据库中的数据的改变就应该是永久性的

七、索引

1、普通索引

最基本的索引，没有任何限制，有以下几种创建方式：

（1）直接创建索引

• 语法： create index 索引名 on 表名(字段(长度));

　　mysql> create index test_username on test(username(10));

（2）修改表结构的方式添加索引

• 语法：alter table 表名 add 索引名(字段(长度));

　　mysql> alter table test add index test_username(username(10));

（3）创建表的时候同时创建索引

　　mysql> create table test(
　　-> id int,
　　-> username varchar(16),
　　-> city varchar(16),
　　-> age int,
　　-> index test_username (username(10))
　　-> );

2、唯一索引

索引列的值必须唯一，但允许有空值，它有以下几种创建方式：

（1）直接创建索引

• 语法：create unique index 索引名 on 表名(字段(长度));

　　mysql> create unique index test_city on test(city(10));

（2）修改表结构的方式添加索引

• 语法：alter table 表名 add unique 索引名(字段(长度(10));

　　mysql> alter table test add unique test_city(city(10));

（3）创建表的时候同时创建索引

　　mysql> CREATE TABLE test(
　　-> id INT,
　　-> username VARCHAR(16),
　　-> city VARCHAR(16),
　　-> age INT,
　　-> UNIQUE test_username (username（10）)
　　-> );

3、主键索引

是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值，一般在建表的时候就同时创建了主键索引。

4、组合索引

多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。

mysql> alter table test add index test_username_city(username, city);

5、全文索引

主要用来查找文本中的关键字，而不是直接与索引中的值相比较，它更像是一个搜索引擎，而不是简单的where语句的参数匹配。有以下几种创建方式：

（1）直接创建

• 语法：create fulltext index 索引名 on 表名 (字段);

　　mysql> create fulltext index full_username on test (username);

（2）修改表结构添加全文索引

• 语法：alter table 表名 add fulltext index 索引名(字段));

　　mysql> alter table test add fulltext index full_city (city);

（3）创建表的时候直接指定

　　mysql> create table test(
　　-> id int,
　　-> username varchar(16),
　　-> city varchar(16),
　　-> age int,
　　-> fulltext indexName (username(10))
　　-> );

6、查看索引

• 语法：show create table 表名;

7、删除索引

• 语法：drop index 索引名 on 表名;