mysql数据库的安装和基本使用

一、数据库安装配置

1）数据库的概念

、数据库相关概念
    数据库服务器（本质就是一个台计算机，该计算机之上安装有数据库管理软件的服务端）
    数据库管理管理系统RDBMS（本质就是一个C/S架构的套接字软件）
    库（文件夹）=====》数据库
    表（文件）
    记录：抽取一个事物所有典型的特征/数据
        egon,,,male,True

        数据
        name='egon' age= height= sex="male" beutiful=True

、数据库管理系统/软件分类：
    关系型：
        有表结构，存取数据前必先定义表结构，存数据必须按照字段的类型或者约束来
        典型代表：MySQL，Oracle，DB2，SQL server
    非关系型:
        存取数据都是采用key:value的形式
        非关系型：Mongodb，redis，memcache

2）mysql数据库的下载

社区版本

下载地址: https://dev.mysql.com/downloads/mysql/5.7.html#downloads

选择版本

根据电脑需求下载

不需要注册，直接下载

3）mysql的安装使用

解压即安装，

bin目录存放于环境变量里面

管理员用户启动cmd,输入mysqld启动mysql服务器程序

mysql -h 127.0.0.1 -P 3306 -uroot -p   启动客户端程序，没有密码直接登录

简写本地登录：mysql -uroot -p

4）修改root密码操作

修改成空密码

破解root密码方法

跳过授权表启动服务端，客户端登录则无需要密码

修改root密码

5）存的库，即data下的文件夹。performance_schema内存文件，及不会存在硬盘空间里面

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

6）关闭mysql服务的方式，并制作系统服务

　　杀死进程号，关闭mysql

mysql制作成系统服务

制作：先关闭
    管理员cmd里面输入：mysqld --install
    查看：
        windows+r
        输入services.msc
制作完系统服务后就可以鼠标点击启动或关闭mysql：
    windows+r
    输入services.msc
    找到mysql，启动或关闭

7）增加配置文件，my.ini，调字符编码

[mysqld]
character-set-server=utf8
collation-server=utf8_general_ci

[client]
default-character-set=utf8

[mysql]
user="root"
password=""
default-character-set=utf8

my.ini

强调：utf8会存在字符集不兼容问题，需要将 utf8 改成 utf8mb4

重启服务   mysql> \s    查看修改结果

在Linux里面的mysql修改字符编码操作在 /etc/my.cnf 里面加上相应的内容。即可

二、MySQL数据库语法的使用

1）基本的SQL语句

、文件夹（库）
    增
        create database db1 charset utf8;
    改
        alter database db1 charset gbk;
    查
        查看所有库的库名
        show databases;
        单独查看某一个库的信息
        show create database db1;
    删
        drop database db1;

、文件（表）
    首先切换文件夹：
        use db1;
        select database(); #查看当前所在的文件夹
    增
        create table t1(id int,name char);
    改
        alter table t1 modify name char();
    查
        查看当前库下所有的表名
        show tables;
        查看t1表的详细信息
        show create table t1;
        查看表结构
        desc t1;

    删
        drop table t1;

、文件的一行内容（记录）
    增
        insert into db1.t1 values
        (,'egon'),
        (,'alex'),
        (,'lxx');
    改
        update db1.t1 set name='sb' where id > ;
    查
        select id,name from db1.t1;
    删
        delete from db1.t1 where name = "SB" ;

实际操作方法

库操作
mysql> create database db1 charset utf8;    创建库
Query OK,  row affected (0.07 sec)

mysql> show databases;                显示所有的库

mysql> show create database db1;    查看被创建库的信息
+----------+--------------------------------------------------------------+
| Database | Create Database                                              |
+----------+--------------------------------------------------------------+
| db1      | CREATE DATABASE `db1` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+--------------------------------------------------------------+

mysql> alter database db1 charset gbk;    修改库，只是修改库的字符编码，
Query OK,  row affected (0.01 sec)

mysql> show create database db1;

mysql> drop database db1;            删除库

表操作
mysql> create database db1;        修改了配置文件的字符编码，默认创建出来的就是 utf8
Query OK,  row affected (0.00 sec)

mysql> use db1;        切换到该库
Database changed
mysql> select database();   查看当前在哪个库
mysql> create table t1(id int,name char());    创建表
mysql> show create table t1;    查看被创建表的信息
mysql> desc t1;        查看表结构
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int()  | YES  |     | NULL    |       |
| name  | char() | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
mysql> alter table t1 modify name char();    修改表信息的操作
mysql> alter table t1 change name  NAME char();   字段名都能修改
mysql> desc t1;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int()  | YES  |     | NULL    |       |
| NAME  | char() | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
 rows in set (0.01 sec)
mysql> drop table t1;        删表操作

修改文件的一行内容（记录）
mysql> create table t1(id int,name char());
mysql> insert into db1.t1 values
    ->         (,'egon'),
    ->         (,'alex'),
    ->         (,'lxx');
Query OK,  rows affected (0.10 sec)
Records:   Duplicates:   Warnings:
mysql> select id,name from t1;
+------+------+
| id   | name |
+------+------+
|     | egon |
|     | alex |
|     | lxx  |
+------+------+
 rows in set (0.00 sec)
mysql> update t1 set name="sb" where id=;        单独修改某行操作
mysql> select * from t1;
+------+------+
| id   | name |
+------+------+
|     | egon |
|     | alex |
|     | sb   |
+------+------+
 rows in set (0.00 sec
mysql> delete from t1 where id >=;        删除某些行
#  delete 只能用于删除符合条件的某几条记录，不能用于清空表
#  清空表应该使用truncate,不仅删除所有记录，而且将自增字段的值归0
mysql> truncate t1;  清空表
Query OK,  rows affected (0.15 sec)

2）mysql支持的存储引擎

show engines;    查看MySQL所支持的存储引擎
create table t1(id int)engine=innodb;    （默认）常用
create table t2(id int)engine=myisam;
create table t3(id int)engine=blackhole;
create table t4(id int)engine=memory;

3）创建表的完整语法

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

[ ] 内部条件可有可无

#解释：
类型：使用限制字段必须以什么样的数据类型传值
约束条件：约束条件是在类型之外添加一种额外的限制

# 注意：
. 在同一张表中，字段名是不能相同
. 宽度和约束条件可选，字段名和类型是必须的
、最后一个字段后不加逗号

create database db37;    # 创建库
create table t1(name char);        # 语法结构：create table 表名(字段名1 类型[(宽度) 约束条件])
desc t1;    # 查看存储的类型
insert into t1 values('egon');    # 由于上面创建的char类型默认存储1个字符，只能存储一个e
create table t2(id int,name char not null);  # 约束条件

4）清空表

delete from tb1;
强调：上面的这条命令确实可以将表里的所有记录都删掉，但不会将id重置为0，
所以收该条命令根本不是用来清空表的，delete是用来删除表中某一些符合条件的记录

delete from tb1 where id > ;

如果要清空表，使用truncate tb1；
作用：将整张表重置

5）修改表

语法：
. 修改表名
      ALTER TABLE 表名
                          RENAME 新表名;
. 增加字段
      ALTER TABLE 表名
                          ADD 字段名  数据类型 [完整性约束条件…],
                          ADD 字段名  数据类型 [完整性约束条件…];
      ALTER TABLE 表名
                          ADD 字段名  数据类型 [完整性约束条件…]  FIRST;
      ALTER TABLE 表名
                          ADD 字段名  数据类型 [完整性约束条件…]  AFTER 字段名;
. 删除字段
      ALTER TABLE 表名
                          DROP 字段名;
. 修改字段
      ALTER TABLE 表名
                          MODIFY  字段名 数据类型 [完整性约束条件…];
      ALTER TABLE 表名
                          CHANGE 旧字段名 新字段名 旧数据类型 [完整性约束条件…];
      ALTER TABLE 表名
                          CHANGE 旧字段名 新字段名 新数据类型 [完整性约束条件…];

示例

. 修改存储引擎
mysql> alter table service
    -> engine=innodb;

. 添加字段
mysql> alter table student10
    -> add name varchar() not null,
    -> add age int() not null default ;

mysql> alter table student10
    -> add stu_num varchar() not null after name;                //添加name字段之后

mysql> alter table student10
    -> add sex enum('male','female') default 'male' first;          //添加到最前面

. 删除字段
mysql> alter table student10
    -> drop sex;

mysql> alter table service
    -> drop mac;

. 修改字段类型modify
mysql> alter table student10
    -> modify age int();
mysql> alter table student10
    -> modify id int() not null primary key auto_increment;    //修改为主键

. 增加约束（针对已有的主键增加auto_increment）
mysql> alter table student10 modify id int() not null primary key auto_increment;
ERROR  (): Multiple primary key defined

mysql> alter table student10 modify id int() not null auto_increment;
Query OK,  rows affected (0.01 sec)
Records:   Duplicates:   Warnings: 

. 对已经存在的表增加复合主键
mysql> alter table service2
    -> add primary key(host_ip,port);        

. 增加主键
mysql> alter table student1
    -> modify name varchar() not null primary key;

. 增加主键和自动增长
mysql> alter table student1
    -> modify id int not null primary key auto_increment;

. 删除主键
a. 删除自增约束
mysql> alter table student10 modify id int() not null; 

b. 删除主键
mysql> alter table student10
    -> drop primary key;

6）复制表操作。复制表结构＋记录 （key不会复制: 主键、外键和索引）

mysql> create table new_service select * from service;

只复制表结构
mysql> select * from service where =;        //条件为假，查不到任何记录
Empty set (0.00 sec)
mysql> create table new1_service select * from service where =;
Query OK,  rows affected (0.00 sec)
Records:   Duplicates:   Warnings: 

mysql> create table t4 like employees;

DROP TABLE 表名;  删除表

7）约束条件，约束条件与数据类型的宽度一样，都是可选参数

作用：用于保证数据的完整性和一致性

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

UNSIGNED 无符号
ZEROFILL 使用0填充

说明

. 是否允许为空，默认NULL，可设置NOT NULL，字段不允许为空，必须赋值
. 字段是否有默认值，缺省的默认值是NULL，如果插入记录时不给字段赋值，此字段使用默认值
   sex enum('male','female') not null default 'male'
   age int unsigned NOT NULL default  必须为正值（无符号） 不允许为空 默认是20
. 是否是key
   主键 primary key
   外键 foreign key
   索引 (index,unique...)

三、数字类型

1）整型类型，建议用 int

、作用：id号，各种号码，年龄，等级
、分类：
tinyint（**）
int （*****）
bigint（***）

、测试：默认整型都是有符号的
create table t1(x tinyint);
insert into t1 values(),(-);        # 超过范围，存取最大值

create table t2(x tinyint unsigned);    # unsigned 约束条件，无符号
insert into t2 values(-),();

create table t3(x int unsigned);
#
insert into t3 values();

create table t4(x int() unsigned);
insert into t4 values();

、强调：对于整型来说，数据类型后的宽度并不是存储限制，而是显示限制
    所以在创建表示，如果字段采用的是整型类型，完全无需指定显示宽度，
    默认的显示宽度，足够显示完整当初存放的数据

# 显示时，不够8位用0填充，如果超出8位则正常显示
create table t5(x int() unsigned zerofill);
insert into t5 values();
insert into t5 values();

实际操作案例

mysql> create table t2(id tinyint);
Query OK,  rows affected (1.05 sec)
mysql> insert into t2 values(-);
Query OK,  row affected (0.15 sec)
mysql> insert into t2 values();   严格模式，超过范围了
ERROR  (): Out of range value for column 'id' at row
mysql> insert into t2 values();
Query OK,  row affected (0.07 sec)
mysql> select * from t2;
+------+
| id   |
+------+
|   - |
|   |
+------+
mysql> select @@sql_mode;  查看全局模式
+--------------------------------------------+
| @@sql_mode                                 |
+--------------------------------------------+
| STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+--------------------------------------------+

mysql> create table t3(id tinyint unsigned);        # 创建无符号的整数
Query OK,  rows affected (1.06 sec)
mysql> insert into t3 values(-);    # 说明表设计是无符号的，不能插入负数
ERROR  (): Out of range value for column 'id' at row
mysql> create table t5(id int() unsigned);
Query OK,  rows affected (0.18 sec)

mysql> insert into t5 values();
Query OK,  row affected (0.06 sec)
mysql> select * from t5;    #  int() 控制的是显示宽度，默认无法查看。unsigned zerofill 可以显示出来，0填充
+------+
| id   |
+------+
|     |
+------+
 row in set (0.00 sec)
create table t5(x int() unsigned zerofill);

2）sql_mode  修改为严格模式，比如超出范围报错等

# 查看sql_mode
mysql> show variables like "%sql_mode%";
+----------------------------+---------------------+
| Variable_name              | Value               |
+----------------------------+---------------------+
| binlogging_impossible_mode | IGNORE_ERROR        |
| block_encryption_mode      | aes--ecb         |
| gtid_mode                  | OFF                 |
| innodb_autoinc_lock_mode   |                    |
| innodb_strict_mode         | OFF                 |
| pseudo_slave_mode          | OFF                 |
| slave_exec_mode            | STRICT              |
| sql_mode                   | STRICT_TRANS_TABLES |
+----------------------------+---------------------+
 rows in set (0.00 sec)

#修改sql_mode为严格模式：在该模式下，如果插入的数据超过限制，则会立即报错
mysql> set global sql_mode="strict_trans_tables";

3）小数类型，建议用float

作用：存储身高、体重、薪资
分类：
float （*****）
double （**）
decimal （**）

测试：
#相同点
#、对于三者来说，都能存放30位小数，
#不同点：
、精度的排序从低到高：float,double,decimal
、float与double类型能存放的整数位比decimal更多

create table t9(x float(,));
create table t10(x double(,));
create table t11(x decimal(,));

insert into t9 values(1.111111111111111111111111111111);
insert into t10 values(1.111111111111111111111111111111);
insert into t11 values(1.111111111111111111111111111111);

mysql> select * from t9;
+----------------------------------+
| x                                |
+----------------------------------+
| 1.111111164093017600000000000000 |
+----------------------------------+
 row in set (0.00 sec)

mysql> select * from t10;
+----------------------------------+
| x                                |
+----------------------------------+
| 1.111111111111111200000000000000 |
+----------------------------------+
 row in set (0.00 sec)

mysql> select * from t11;
+----------------------------------+
| x                                |
+----------------------------------+
| 1.111111111111111111111111111111 |
+----------------------------------+
 row in set (0.00 sec)

四、其他常用类型

1）字符类型，char 和 varchar

char 和 varchar 的作用

、作用：姓名，地址，描述类的信息

、分类：
char() 定长，最大存放5个字符，不够5个字符，那么用空格补齐
varchar() 变长，最大存放5个字符，传入几个字符，就存入几个字符

使用方法

测试:字符的宽度限制单位是字符个数
create table t12(x char()); # 超出4个字符则报错，不够4个字符则用空格补全成4个字符
create table t13(y varchar());# 超出4个字符则报错，不够4个字符那么字符有几个就存几个

insert into t12 values('hello');
insert into t13 values('hello');

insert into t12 values('a'); #'a    '
insert into t13 values('a'); #'a'

# 设置全局模式，查看字符长度
set global sql_mode="strict_trans_tables,PAD_CHAR_TO_FULL_LENGTH";
select char_length(x) from t12; #
select char_length(y) from t13; #

# 注意：
针对char类型，mysql在存储时会将数据用空格补全存放到硬盘中
但会在读出结果时自动去掉末尾的空格，因为末尾的空格在以下场景中是无用
mysql> select * from t14 where name="lxx"; # name字段明确地等于一个值，该值后填充空格是没有用

mysql> select * from t14 where name like "lxx"; # name字段模糊匹配一个值，该值后填充空格是有用的

# 对比char与varchar

name char()
# 缺点：浪费空间
# 优点：存取速度都快
egon alex lxx  wxx  yx

name varchar()
# 缺点：存取速度都慢
# 优点：节省空间
(1bytes+egon)(1bytes+alex)(1bytes+lxx)

2）时间，日期类型

时间范围

# 时间类型  time ,date, datetime,timestamp,year
YEAR
    YYYY（/）

DATE
    YYYY-MM-DD（--/--）

TIME
    HH:MM:SS（'-838:59:59'/'838:59:59'）

DATETIME

    YYYY-MM-DD HH:MM:SS（-- ::/-- ::    Y）

TIMESTAMP

    YYYYMMDD HHMMSS（-- ::/ 年某时）

create table t7(x timestamp);
= create table t7(x datetime not null default now());

使用方法，如果不考虑时间范围的话，请使用timestamp，因为和datetime相比，占用空间更小，且默认存放当前时间

、作用：时间相关

、分类：
date：--
time: ::
datetime:-- ::
year:

、测试
create table student(
    id int,
    name char(),
    born_year year,
    birth date,
    class_time time,
    reg_time datetime
);

insert into student values(,'egon','','2000-01-27','08:30:00','2013-11-11 11:11:11');

实际操作

create table student(
    id int primary key auto_increment,
    name char(),
    born_year year,
    class_time time,
    birth date,
    reg_time datetime
);
insert into student(name,born_year,class_time,birth,reg_time) value
('egon',now(),now(),now(),now());

mysql> create table student(
    -> id int primary key auto_increment,
    -> name char(),
    -> born_year year,
    -> class_time time,
    -> birth date,
    -> reg_time datetime
    -> );
Query OK,  rows affected (0.40 sec)
mysql> insert into student(name,born_year,class_time,birth,reg_time) value
    -> ('egon',now(),now(),now(),now());
Query OK,  row affected,  warning (0.18 sec)
mysql> select * from student;
+----+------+-----------+------------+------------+---------------------+
| id | name | born_year | class_time | birth      | reg_time            |
+----+------+-----------+------------+------------+---------------------+
|   | egon |       | ::   | -- | -- :: |
+----+------+-----------+------------+------------+---------------------+
mysql> insert into student(birth) values('2018-08-08');
Query OK,  row affected (0.12 sec)
mysql> select * from student;
+----+------+-----------+------------+------------+---------------------+
| id | name | born_year | class_time | birth      | reg_time            |
+----+------+-----------+------------+------------+---------------------+
|   | egon |       | ::   | -- | -- :: |
|   | NULL |      NULL | NULL       | -- | NULL                |
+----+------+-----------+------------+------------+---------------------+
 rows in set (0.00 sec)
mysql> create table t7(x timestamp);    # 不传时间，则默认传入当前时间
Query OK,  rows affected (0.18 sec)
mysql> insert into t7 values();
Query OK,  row affected (0.04 sec)
mysql> select * from t7;
+---------------------+
| x                   |
+---------------------+
| -- :: |
+---------------------+
 row in set (0.00 sec)

3）枚举与集合类型。选择类型

作用与分类：
枚举enum，多选一个
集合set，多选多

测试
create table teacher(
    id int,
    name char(),
    sex enum('male','female','others'),
    hobbies set('play','read','music','piao')
);

实际操作

# 枚举类型enum与集合类型set
# enum: 多选一
# set: 多选多
create table emp(
    id int primary key auto_increment,
    name char(),
    sex enum('male','female','others'),
    hobbies set('play','read','music','piao')
);
insert into emp(name,sex,hobbies) values
('egon','male','play,music');

mysql> create table emp(
    -> id int primary key auto_increment,
    -> name char(),
    -> sex enum('male','female','others'),
    ->     hobbies set('play','read','music','piao')
    -> );
Query OK,  rows affected (0.24 sec)
mysql> insert into emp(name,sex,hobbies) values
    -> ('egon','male','play,music');
Query OK,  row affected (0.04 sec)
mysql> select * from emp;
+----+------+------+------------+
| id | name | sex  | hobbies    |
+----+------+------+------------+
|   | egon | male | play,music |
+----+------+------+------------+

五、约束条件

1）not null + default   缺值，则使用默认值

create table t15(
    id int,
    name char() not null,
    sex enum('male','female','other') not null default "male"
);
# 性别默认选择男性

alter table t15 modify name char() not null;    # 单独修改表字段

insert into t15(id,name) values(,'alex')
insert into t15(id,name) values
(,'egon1'),
(,'egon2'),
(,'egon3');

2）限制字段的值唯一

#单列唯一
create table t16(
    id int unique,
    name char()
);

# 联合唯一
create table server(
    id int unique,
    ip char(),
    port int,
    unique(ip,port)
);

3）约束条件之主键。primary key:单单从约束角度去看，primary key就等同于not null unique

#强调（******）
、一张表中必须有，并且只能有一个主键
、一张表中都应该有一个id字段，而且应该把id字段做成主键

主键的使用

create table t17(
    id int primary key,
    name char(),
    age int,
    sex char()
)engine=innodb;

#联合主键
create table t19(
    ip char(),
    port int,
    primary key(ip,port)
);

auto_increment 与 与primary key连用，实现自增 id

# primary key auto_increment
create table t20(
    id int primary key auto_increment,
    name char()
)engine=innodb;

# primary和auto_increment注意点：
、单从约束角度去看，主键的约束效果就是not null + unique
、但其实innodb存储引擎会以主键为准创建聚集索引
、通常与primary key连用，而且通常是给id字段加
、auto_incremnt只能给被定义成key（unique key，primary key）的字段加
结论：
    如果表的类型为innodb，那么在建立表时，应该有一个id字段
    并且 id int primary key auto_increment

六、权限管理设置

1）创建授权用户

create user 'egon'@'1.1.1.1' identified by '';
create user 'egon'@'192.168.1.%' identified by '';
create user 'egon'@'%' identified by '';

2）授权

#授权：对文件夹，对文件，对文件某一字段的权限
查看帮助：help grant
常用权限有：select,update,alter,delete
all可以代表除了grant之外的所有权限

#针对所有库的授权:*.*
#只在user表中可以查到egon1用户的select权限被设置为Y
grant select on *.* to 'egon1'@'localhost' identified by '';

#针对某一数据库：db1.*
#只在db表中可以查到egon2用户的select权限被设置为Y
grant select on db1.* to 'egon2'@'%' identified by '';

#针对某一个表：db1.t1
#只在tables_priv表中可以查到egon3用户的select权限
grant select on db1.t1 to 'egon3'@'%' identified by '';

#删除权限
revoke select on db1.* from 'egon'@'%';

3）授权常用操作

服务端授权用户
grant all on *.* to 'egon'@'192.168.1.%' identified by '';
flush privileges;

客户端连接
mysql -uegon -p123 -h192.168.1.