mysql数据库（一）

1.初识数据库

1.1 数据库（DataBase,简称DB）

从字面意思上讲就是用来存储数据的仓库。
数据库中的数据按一定的数据模型组织、描述和储存，具有较小的冗余度、较高的数据独立性和易扩展性，并可为各种 用户共享。

1.2 数据库管理软件（DataBase management system,简称DBMS)

科学地组织和存储数据，高效获取和维护数据
主要具有以下功能：
（1）数据存取的物理构建
（2）数据的操纵
（3）数据的定义
（4）数据库的运行管理
（5）数据库的建立和维护

1.3 数据库常用对象

（1）表 ：包含数据库中所有数据的数据库对象，由行和列组成，用于组织和存储过程
（2）字段 ：表中的每一列称为字段，字段有自己独立的属性，如字段属性、字段大小等。其中字段类型决定着字段存储为哪种数据类型
SQL规范支持的5种基本字段类型：字符型、文本型、数值型、逻辑型、和日期时间型
（3）索引 ：是一个单独的、物理的数据库结构。依赖于表的建立，在数据库中索引使数据库程序无需对整个表进行扫描，就可以找到所需数据
（4）视图 ：是从一张或多张表中导出的表（虚拟表），是用户查看数据的一种方式
（5）存储过程 ：是一组为了完成特定功能的SQL语句集合，经过编译后以名称的形式存储在SQL Server服务端的数据库中。

1.4 SQL语言

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

2.库的操作

2.1系统数据库

information_schema： 虚拟库，不占用磁盘空间，存储的是数据库启动后的一些参数，如用户表信息、列信息、权限信息、字符信息等
performance_schema： 主要用于收集数据库服务器性能参数，记录处理查询请求时发生的各种事件、锁等现象
mysql： 授权库，主要存储系统用户的权限信息

2.2 创建数据库

2.2.1 语法

CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
[create_specification] ...

create_specification:
[DEFAULT] CHARACTER SET [=] charset_name
| [DEFAULT] COLLATE [=] collation_name

2.2.2 数据库命名规

（1）可以由字母、数字、下划线、＠、＃、＄
（2）区分大小写
（3）唯一性
（4）不能使用关键字如 create select
（5）不能单独使用数字
（6）最长128位

2.2.3 数据库的相关操作

show databases; 查看数据库，并返回当前所有库
show create database db1; 查看具体数据库信息
select database(); 查看当前所选的数据库
USE 数据库名; 选择数据库
DROP DATABASE 数据库名; 删除数据库
alter database db1 charset utf8; 修改数据库

3.表的操作

3.1存储引擎

表有不同的类型，表的类型不同，会对应mysql不同的存取机制，表类型又称为存储引擎
show enginesG #查看所有支持的存储引擎
show variables like 'storage_engine%'; #查看正在使用的存储引擎

3.1.2 使用存储引擎

（1）建表时指定 create table t1(id int) engine=innodb;
（2）在配置文件中指定默认的存储引擎

3.2 增删改查

3.2.1创建表

create table 表名(
字段名1 类型[(宽度) 约束条件],
字段名2 类型[(宽度) 约束条件],
字段名3 类型[(宽度) 约束条件]
);

#注意：
1. 在同一张表中，字段名是不能相同
2. 宽度和约束条件可选
3. 字段名和类型是必须的

3.2.2查看表的结构

desc 表名 查看表结构
show create table 表名G; 查看详细结构

3.2.3 修改表结构

语法：
（1）修改表名
ALTER TABLE 表名
RENAME 新表名;

（2）增加字段
ALTER TABLE 表名
ADD 字段名 数据类型 [完整性约束条件…],
ADD 字段名 数据类型 [完整性约束条件…];
ALTER TABLE 表名
ADD 字段名 数据类型 [完整性约束条件…] FIRST;
ALTER TABLE 表名
ADD 字段名 数据类型 [完整性约束条件…] AFTER 字段名;

（3）删除字段
ALTER TABLE 表名
DROP 字段名;

（4）修改字段
ALTER TABLE 表名
MODIFY 字段名 数据类型 [完整性约束条件…];
ALTER TABLE 表名
CHANGE 旧字段名 新字段名 旧数据类型 [完整性约束条件…];
ALTER TABLE 表名
CHANGE 旧字段名 新字段名 新数据类型 [完整性约束条件…];

3.2.4 复制表

（1）复制表结构和数据
create table 新表名 select * from 表名；
（2）只复制表结构
create table 新表名 select * from 表名 where false;
create table 新表名 like 表名;

3.2.5 删除表

drop table 表名

3.3 数值类型

3.3.1 整数类型

整数类型：TINYINT SMALLINT MEDIUMINT INT BIGINT

tinyint[(m)] [unsigned] [zerofill]

小整数，数据类型用于保存一些范围的整数数值范围：
            有符号：
                -128 ～ 127
            无符号：
                0 ～ 255

int[(m)][unsigned][zerofill]

整数，数据类型用于保存一些范围的整数数值范围：
            有符号：
                 -2147483648 ～ 2147483647
            无符号：
                 0 ～ 4294967295

bigint[(m)][unsigned][zerofill]
大整数，数据类型用于保存一些范围的整数数值范围：
            有符号：
                 -9223372036854775808 ～ 9223372036854775807
            无符号：
                0  ～  18446744073709551615

FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]

单精度浮点数（非准确小数值），m是数字总个数，d是小数点后个数。m最大值为255，d最大值为30

DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]

双精度浮点数（非准确小数值），m是数字总个数，d是小数点后个数。m最大值为255，d最大值为30

decimal[(m[,d])] [unsigned] [zerofill]

准确的小数值，m是数字总个数（负号不算），d是小数点后个数。 m最大值为65，d最大值为30，decaimal能够存储精确值的原因在于其内部按照字符串存储。

3.3.2 日期类型

YEAR      YYYY（1901/2155）
DATE      YYYY-MM-DD（1000-01-01/9999-12-31） 
TIME      HH:MM:SS（'-838:59:59'/'838:59:59'）
DATETIME  YYYY-MM-DD HH:MM:SS（1000-01-01 00:00:00/9999-12-31 23:59:59    Y）
TIMESTAMP YYYYMMDD HHMMSS（1970-01-01 00:00:00/2037 年某时）

3.3.3 字符类型

char类型：定长，浪费空间，存取速度快，字符长度范围：0-255（一个中文是一个字符，是utf8编码的3个字节）

varchar类型：变长，精准，节省空间，存取速度慢，字符长度范围：0-65535

3.3.4 枚举、集合类型

enum 单选 只能在给定的范围内选一个值，如 sex enum('male','female')

set 多选 在给定的范围内可以选择一个或一个以上的值，如 hobby set('play','music','read','study')

3.4 完整性约束

约束条件与数据类型的宽度一样，都是可选参数,用于保证数据的完整性和一致性

主要分为：

PRIMARY KEY (PK)    标识该字段为该表的主键，可以唯一的标识记录
FOREIGN KEY (FK)    标识该字段为该表的外键
NOT NULL    标识该字段不能为空
UNIQUE KEY (UK)    标识该字段的值是唯一的
AUTO_INCREMENT    标识该字段的值自动增长（整数类型，而且为主键）
DEFAULT    为该字段设置默认值
UNSIGNED 无符号
ZEROFILL 使用0填充

4.数据的操作

4.1 数据的增删改

4.1.1 插入数据 insert

 (1). 插入完整数据（顺序插入）

    语法一：
    INSERT INTO 表名(字段1,字段2,字段3…字段n) VALUES(值1,值2,值3…值n);

    语法二：
    INSERT INTO 表名 VALUES (值1,值2,值3…值n);

(2). 指定字段插入数据
    语法：
    INSERT INTO 表名(字段1,字段2,字段3…) VALUES (值1,值2,值3…);

(3). 插入多条记录
    语法：
    INSERT INTO 表名 VALUES
        (值1,值2,值3…值n),
        (值1,值2,值3…值n),
        (值1,值2,值3…值n);

(4). 插入查询结果
    语法：
    INSERT INTO 表名(字段1,字段2,字段3…字段n) 
                    SELECT (字段1,字段2,字段3…字段n) FROM 表2
                    WHERE …;

 4.1.2 更新数据 update

语法：
    UPDATE 表名 SET
        字段1=值1,
        字段2=值2,
        WHERE CONDITION;
示例：
    UPDATE mysql.user SET password=password(‘123’) 
        where user=’root’ and host=’localhost’;　　

4.1.3 删除数据 delete

语法：
    DELETE FROM 表名 
        WHERE CONITION;
示例：
    DELETE FROM mysql.user 
        WHERE password=’’;
练习：
    更新MySQL root用户密码为mysql123
    删除除从本地登录的root用户以外的所有用户