MySQL（五）数据库操作

一、初识SQL语言

SQL -----Structured Query Language 即结构化查询语言；
在关系型数据库上执行数据操作、数据检索以及数据维护的标准语言。

SQL语言主要用于存取数据、查询数据、更新数据和管理关系数据库系统，SQL语言由IBM开发。

1、SQL语言分为3种类型：
DDL语句 ---数据库定义语言： 数据库、表、视图、索引、存储过程，例如CREATE DROP ALTER
DML语句 ---数据库操纵语言： 插入数据INSERT、删除数据DELETE、更新数据UPDATE、查询数据SELECT
DCL语句 ---数据库控制语言： 例如控制用户的访问权限GRANT、REVOKE

2、SQL语句的书写规范：
在数据库系统中，SQL语句不区分大小写(建议用大写) 。但字符串常量区分大小写；
SQL语句可单行或多行书写，以“;”结尾；
关键词不能跨多行或简写；
用空格和缩进来提高语句的可读性；
子句通常位于独立行，便于编辑，提高可读性；
SELECT * FROM tb_table
注释：
SQL标准：/**/。
多行注释 “--”
单行注释MySQL注释：“#”

二、忘记MySQL密码：

1、修改配置文件

vim /etc/my.cnf
[mysqld]下添加：
skip-grant-table

2、重启

# service mysqld restart

3、登录回车跳过密码

# mysql -uroot -p

4、改密

mysql> use mysql

mysql> update userset authentication_string=password('123456') where user='root'and Host = 'localhost'; ------新版本
mysql> exit

mysql> update mysql.user set password=password("456") where user="root" and host="localhost";
mysql> flush privileges;
mysql> \q ------老版本

5、修改配置文件
# vim /etc/my.cnf
[mysqld]下加注释：
#skip-grant-table

6、重启

# systemctl  restart mysqld

7、登录输入新密码即可；

三、创建业务数据库

1、 语法
CREATE DATABASE 数据库名;
数据库命名规则：
区分大小写
唯一性
不能使用关键字如 create、select、database等
不能单独使用数字

CREATE DATABASE [IF NOT EXISTS]数据库名;

功能：用给定的名字创建一个数据库，如果数据库已经存在，发生一个错误。

查看创建数据库：

SHOW CREATE DATABASE <数据库名>；

MySQL字符集包括字符集（CHARACTER）和校对规则（COLLATION）两个概念：
latin1支持西欧字符、希腊字符等
gbk支持中文简体字符
big5支持中文繁体字符
utf8几乎支持世界所有国家的字符。

使用MySQL命令 SHOW VARIABLES like 'character%';
使用MySQL命令“SHOW COLLATION;”即可查看当前MySQL服务实例支持的字符序。

MySQL字符序命名规则是：以字符序对应的字符集名称开头，以国家名居中（或以general居中），以ci、cs或bin结尾。ci表示大小写不敏感，cs表示大小写敏感，bin表示按二进制编码值比较。

2、默认为latin1字符集，如何更改为utf8：

方法1：修改my.cnf配置文件，可修改MySQL默认的字符集，修改完毕重启MySQL
1.在[mysqld]下添加 default-character-set=utf8

#适合5.1及以前版本 （mysql 5.5及以后版本添加character-set-server=utf8）
init_connect = 'SET NAMES utf8'
2.在[client]下添加 default-character-set=utf8

方法2：MySQL提供下列MySQL命令可以“临时地”修改MySQL“当前会话的”字符集以及字符序。
set character_set_client = utf8;
set character_set_connection = utf8;
set character_set_database = utf8;
set character_set_results = utf8;
set character_set_server = utf8;
set collation_connection = utf8_general_ci ;
set collation_database = utf8_general_ci ;
set collation_server = utf8_general_ci ;

方法3：使用MySQL命令 “set names utf8;”可以“临时一次性地”设置character_set_client、character_set_connection以及character_set_results的字符集为utf8 。

方法4：连接MySQL服务器时指定字符集 mysql --default-character-set=字符集 -h 服务器IP地址 -u 账户名 –p密码

查看数据库
SHOW DATABASES;

选择数据库
SELECT database();
USE 数据库名

删除数据库
DROP DATABASE 数据库名;

查看数据库信息
查看当前连接的数据库 SELECT DATABASE();
查看数据库版本 SELECT VERSION();
查看当前用户 SELECT USER();
查看所有用户 SELECT User,Host,Password FROM mysql.user;
执行Linux命令： SYSTEM <命令>

四、MySQL数据类型

1、 MySQL常见的数据类型

在MySQL数据库管理系统中，可以通过存储引擎来决定表的类型。同时，MySQL数据库管理系统也提供了数据类型决定表存储数据的类型。

MySQL数据库管理系统提供的数据类型：

数值类型：
整数类型  TINYINT SMALLINT MEDIUMINT INT BIGINT
浮点数类型   FLOAT DOUBLE
定点数类型   DEC
位类型  BIT

浮点类型：

字符串类型：
CHAR系列 -----CHAR VARCHAR
TEXT系列 ------TINYTEXT TEXT MEDIUMTEXT LONGTEXT
BLOB 系列 -----TINYBLOB BLOB MEDIUMBLOB LONGBLOB
BINARY系列 ---BINARY VARBINARY

枚举类型： ENUM（男女）

集合类型： SET

时间和日期类型： DATE TIME DATETIME TIMESTAMP YEAR

2、 数据类型测试

===整数类型测试：tinyint,int
作用：用于存储用户的年龄、游戏的Level、经验值等。

unsigned -----约束条件unsigned限定只能存正值（无符号）

===浮点数类型测试：
作用：用于存储用户的身高、体重、薪水等。

浮点数和定点数都可以用类型名称后加(M,D)的方式来表示，(M,D)表示一共显示M位数字（整数位+小数位），其中D位于小数点后面，M和D又称为精度和标度。

float和double在不指定精度时，默认会按照实际的精度（由实际的硬件和操作系统决定）来显示，
而decimal在不指定精度时，默认的整数位为10，默认的小数位为0；

eg： mysql> create table test4(float_testfloat(5,2)); //一共5位，小数占2位
Query OK, 0 rows affected (0.00 sec)

定点数在MySQL内部以字符串形式存储，比浮点数更精确，适合用来表示货币等精度高的数据。

===位类型测试：BIT
BIT(M)可以用来存放多位二进制数，M范围从1~64，如果不写默认为1位；
对于位字段可以使用函数读取：
bin()显示为二进制
hex()显示为十六进制

===时间和日期类型测试：year、date、time、datetime、timestamp
作用：用于存储用户的注册时间，文章的发布时间，文章的更新时间，员工的入职时间等

注意事项：
其它的时间，按要求插入
==插入年份时，尽量使用4位值
==插入两位年份时：

<=69，以20开头，比如65, 结果2065
>=70，以19 开头，比如82，结果1982

＝＝＝字符串类型测试：CHAR、VARCHAR
作用：用于存储用户的姓名、爱好、发布的文章等
CHAR 列的长度固定为创建表时声明的长度： 0 ~ 255
VARCHAR 列中的值为可变长字符串，长度： 0 ~ 65535

注：在检索的时候，CHAR列删除了尾部的空格，而VARCHAR则保留这些空格

＝＝＝字符串类型
＝＝＝ENUM类型即枚举类型、集合类型SET测试
字段的值只能在给定范围中选择
常见的是单选按钮和复选框
enum 单选 只能在给定的范围内选一个值，如性别 sex 男male/女female
set 多选 在给定的范围内可以选择一个或一个以上的值（爱好1,爱好2,爱好3...）

五、存储引擎

1. 查看存储引擎
SHOW ENGINES\G
SHOW VARIABLES LIKE 'storage_engine%'

2. 选择存储引擎

方法1.
mysql> create table innodb1(
-> id int
-> )engine=innodb;
mysql> show create table innodb1;

方法2.
/etc/my.cnf
[mysqld]
default-storage-engine=INNODB

3、 MySQL常用的存储引擎

(1).MyISAM存储引擎

   由于该存储引擎不支持事务、也不支持外键，所以访问速度较快。因此当对事务完整性没有要求并以访问为主的应用适合使用该存储引擎。

(2).InnoDB存储引擎

   由于该存储引擎在事务上具有优势，即支持具有提交、回滚及崩溃恢复能力等事务特性，所以比MyISAM存储引擎占用更多的磁盘空间。因此当需要频繁的更新、删除操作，同时还对事务的完整性要求较高，需要实现并发控制，建议选择。

(3).MEMORY

   MEMORY存储引擎存储数据的位置是内存，因此访问速度最快，但是安全上没有保障。适合于需要快速的访问或临时表。

(4).BLACKHOLE

   黑洞存储引擎，可以应用于主备复制中的分发主库。