Mysql

 

数据库简介

• 用途：用于存储生活的一切数据，如：身份、住房、车票、网站、...

• 概念：数据库服务器、数据库、数据表、一行数据(一条)、一列数据(字段)

• 分类：

  • 关系型数据库：MySQL、oracle、SQL Server、...

  • 非关系数据库：Redis、MongoDB、...

• SQL：Structured Query Language，结构化查询语言

• 历史：口述

• 命令：

  • 数据定义语言(DDL)：创建、删除、修改库或表

  • 数据操作语言(DML)：增、删、改

  • 数据查询语言(DQL)：查

  • 数据控制语言(DCL)：授权、取消授权

  • 数据事务语言(DTL)：开启事务、提交事务、操作回滚

MySQL安装

• 安装过程

  • 双击MySQL安装程序：mysql-installer-community-5.7.20.0.msi

  • 选择自定义安装：custom

  • 选择安装的版本：MySQL Server5.7.20 -X64

  • 点击Excute执行安装

  • 设置root密码

  • 点击Excute执行配置

  • 点击Finish完成安装

• 成功测试

  • 查看系统服务：services.msc

  • 连接测试：mysql -h host -u root -p

    • -h：指定主机

    • -u：指定用户

    • -p：指定密码，建议输入密码时分两步，这样可以隐藏密码

    • 示例：mysql [-h127.0.0.1] -uroot -p，敲回车，然后根据提示输入密码即可

      • 端口：3306

      • 退出：exit

      • 帮助：help 或 \h

      • 说明：几乎所有命令都是以';'结束

• 敲mysql命令无反应

  • 说明：那是因为没有将该命令所在目录添加到系统环境变量Path下

  • 添加：

    • 打开系统环境变量设置页面

    • 将mysql命令所在的目录添加到系统环境变量Path下

    • 保存即可

    • 新添加的环境变量在已经启动的终端是无效，重启一个新的终端即可

数据定义语言(DDL)

• 查看库：show databases;，会显示当前服务器上所有的数据库

• 创建库：create database test;，创建数据库test

• 删除库：drop database test;，删除数据库test

• 选择库：use test;，选择数据库test

  • 查看当前使用的数据库

    • show tables; 查看所有数据库

    • select database(); 查看当前所用数据库

• 查看表：show tables;，查看当前数据库下的所有数据表

• 创建表：create table user(username varchar(20), password char(32));

• 查看表结构：desc user;

• 查看创建方式：

  • 查看库：show create database test;

  • 查看表：show create table user;

• 删除表：drop table user;

• 指定字符集：

  • 创建时指定：create table xxx()engine=Innodb default chatrset=utf8;

  • 修改配置文件：

    • win：C:\ProgramData\MySQL\MySQL Server 5.7\my.ini

    • linux：/etc/mysql/mysql.conf.d/mysqld.conf

    • 字符集：character-set-server=utf8

      “[mysqld]下添加

    • 存储引擎：default-storage-engine=INNODB

    • 远程连接：bind-address = 127.0.0.1改成0.0.0.0

  • 重启服务配置才能生效

• 修改字段：

  • alter table user modify username varchar(30);，不能修改字段名

  • alter table user change email em varchar(32);，可以修改字段名

• 添加字段：

  • 默认在最后添加：alter table user add age int(3);

  • 在指定字段后添加：alter table user add email varchar(60) after password;

  • 在开头添加字段：alter table user add id int(11) first;

• 删除字段：alter table user drop age;

• 修改字段位置及名称：add/modify/change

  • alter table user modify em varchar(32) first;

• 修改表名：alter table user rename new_user;

数据类型

• 整型：tinyint(1个字节)、smallint(2个字节)、mediumint(3个字节)、int(4个字节)、bigint(8个字节)

• 浮点型：

  • float(m, d)：单精度浮点型，4个字节，m表示总位数，d表示小数位数

  • double(m, d)：双精度浮点型，8个字节，m表示总位数，d表示小数位数

  • decimal(m, d)：以字符串的形式存储浮点数，多用于金融相关应用

• 字符串：

  • char：定长字符串，0~255个字节

  • varchar：变长字符串，0~65535个字节

• 时间日期：

  • date：日期，格式：2018-11-05

  • time：时间，格式：15:52:52

  • datetime：日期时间，格式：2018-11-05 15:52:52

  • timestamp：时间戳

  • year：年，只占用一个字节，年份：1901~2155

• 符合类型：

  • set：集合类型，格式：set(s1,s2,...,s63)，最多63种可能

  • enum：枚举类型，格式：enum(e1,e2,...,e65535)，最多65535种可能

• 字段修饰：

  • unsigned：无符号数

  • zerofill：高位0填充，防止出现负数

  • auto_increment：自动增加(1)，用于整数，经常与主键结合使用

  • default：默认值

  • not null：不能为空

• 字符集：

  • 查看系统支持字符集：show character set;，通常我们只使用utf8

• 存储引擎：

  • 查看系统支持存储引擎：show engines;

  • 常用存储引擎：InnoDB和MyISAM

• 索引

  • 说明：简单理解就是一本书最前面的目录，可以提高读取效率，但也不是说越多越好。

  • 分类：

    • 普通索引(index)：最基本的索引

    • 唯一索引(unique)：修改的字段不能重复

    • 主键索引(primary key)：是一种特殊的唯一索引，一张表中最多只能有一个字段设置

    • 全文索引(fulltext)：对全局数据进行添加索引

  • 示例：

    alter table user add index(em);             # 给em字段添加普通索引
    alter table user add unique(username);      # 给username字段添加唯一索引
    alter table user add primary key(id);       # 给id字段添加主键索引
    ​
    alter table user drop index em;             # 删除em字段的普通索引

  • 创建表示直接指定：

    create table user(
        id int auto_increment,
        name varchar(20),
        primary key(id),
        unique(name)
    )engine=innodb default charset=utf8;

数据操作语言(DML)

• 说明：在大多数的操作中，使用的都是增删改查操作(CURD)。

• 准备：一张用于测试的表

  mysql> create table star(
      -> id int auto_increment,
      -> name varchar(20) not null,
      -> money float not null,
      -> province varchar(20) default null,
      -> age tinyint unsigned not null,
      -> sex tinyint not null,
      -> primary key(id)
      -> )engine=innodb default charset=utf8;

• 插入数据：

  • 方式1：不指定字段，添加数据时需要写完整所有的字段

  insert into star values(1, '刘亦菲', 20000000, '武汉', 28, 1),(数据),（数据）;

  可以一次性插入多条数据，一条数据需要使用一个()包括起来。

  • 方式2：指定字段，只需要传递指定字段的值

  insert into star(name, money, age, sex, province) values('赵丽颖', 8000000, 31, 1, '河北'),(数据),(数据);
  数据中的字段值如果为空就写Null :('东源',823484,31,2,Null)

  插入数据的顺序与前面指定的字段名要一致。

  • 注意：插入数据时可以不传值的字段

    • 自增的字段

    • 有默认值的

    • 可以为空的

  • 说明：插入数据时，第二种方式使用的比较多。

• 查询数据：select * from star;

• 修改数据：update star set age=31, money=10000000 where id=1;

  • 警告：修改操作一定不要忘了指定条件，否则后果自负。

• 删除数据：delete from star where id=2;

  • 警告：删除操作一定不要忘了指定条件，否则后果自负。

修改数据库字符集：

fdy@fdy: /etc/mysql/mysql.conf.d$ sudo cp mysqld.cnf mysqld.cnf.bak1

fdy@fdy:/etc/mysql/mysql.conf.d$ sudo vim mysqld.cnf

39行：插入
character_set_server = utf8

fdy@fdy:/etc/mysql/mysql.conf.d$ sudo service mysql restart

进入mysql

显示mysql字符集：show variables like 'character%';

+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
​

数据查询语言(DQL)

• 基础查询：select * from star;

• 指定字段查询：select name, money, province from star;

• 删除重复记录：select distinct province, sex from star;

  • 使用distinct指定的字段不能重复，指定多个字段时，字段组合不能重复

• 指定条件查询：

  • 条件

  条件	说明
  >	大于
  >=	大于等于
  <	小于
  <=	小于等于
  =	等于
  != 或 <>	不等于
  and	并且
  or	或者
  [not] between m and n	[不]在区间[m, n]区间
  [not] in ()	[不]在指定集合中
  [not] like 条件	模糊匹配，%表示任意字符

  • 示例：

  select * from star where age > 30;
  select * from star where age > 30 and sex=1;
  select * from star where age > 30 or province='河南';
  select * from star where age [not] between 30 and 40;
  select * from star where id [not] in(1,3,5);
  select * from star where province like '%北%';

• 结果集排序：

  • 示例：

   select * from star order by age asc;   
  select * from star order by money desc;

  • order by：指定排序字段

    • asc：升序，默认方式

    • desc：降序

  • 多字段排序：当前面字段值一样时，按照后面指定的字段进行排序

  select * from star order by sex asc, age desc;

• 限制结果集：

  • limit：用于限制结果集的数量

  • 示例：

  select * from star limit 5;                     # 取前5条数据
  select * from star limit 5 offset 2;            # 偏移2条，取5条数据
  select * from star limit 2, 5;                  # 功能同上

  • 分页查询：

  每页10条数据,用pageSize，page是当前页面，请写出对于页码的查询条件
  第一页：limit 10
  第二页：limit 10, 10
  第三页：limit 20, 10
  page页：limit (page-1) * pageSize, pageSize

• 常用聚合函数：

  函数	说明
  count	统计个数
  sum	求和
  avg	平均值
  max	最大值
  min	最小值

  • 示例：

  select count(*) as c from star;
  select max(money) as max_money from star;

  as可以给查询结构字段起别名

• 分组及过滤：

  • 示例：

  select * from star group by sex;            # 只分组，结果集中每个组中只有一条数据
  select count(*) as c, sex from star group by sex;   # 分组并统计信息
  select count(*) as c, province from star group by province having c>1; 
  # 分组后条件不能使用where，只能使用having

多表联合查询

• 隐式内连接：没有出现join关键字

  • 示例：select username, name from user, goods where user.gid=goods.gid;

  • 说明：查看哪个用户购买了哪个商品

• 显式内连接：SQL语句中出现了join关键字

  • 示例：select username,name from user join goods on user.gid=goods.gid;

  • 说明：功能同上

  • join前可以添加inner或cross关键字，可以省略

• 左外连接：以左表为主，left [outer] #table1 left join table2 以左边为主

  • 示例：select username, name from user left [outer] join goods on user.gid=goods.gid;

  • 说明：显示左表所有数据，右表有对应就显示数据，没有对应显示NULL

• 右外连接：以有表位住，right [outer]

  • 示例：select username, name from user right[outer] join goods on user.gid=goods.gid;

  • 说明：显示有表所有数据，左表有对应就显示数据，没有对应显示NULL

• 子(嵌套)查询

  select * from user where gid in (1,3,5);
  select * from user where gid in (select gid from goods);

• 记录联合

  • 语法：select 语句1 union select 语句2

  • 说明：

    • union all：将两边查询结果直接拼在一起

    • union：是union all的去重结果

• 联合更新数据

  • 示例：update user u, goods g set u.gid=0, g.price=g.price-200 where u.gid=g.gid and u.id=7;

  • 说明：可以给操作的表起别名，还可以对字段的原有只进行运算

事务处理语言(DTL)

• 说明：测试的表的存储引擎要支持事务(InnoDB)

• 开启事务：禁止自动提交

  • set autocommit=0;

• 操作回滚：通常在出现操作异常时使用

  • rollback;

• 提交操作：整个事务过程中没有问题

  • commit;

数据控制语言(DCL)

• 查看授权：

  • 格式：show grants [for 'user'@'host'];

  • 示例：show grants for 'root'@'localhost';

  • 说明：查看当前登录用户授权时可以不指定用户及主机

• 创建用户：

  • 格式：create user 'user'@'host' identified by 'password';

  • 示例：create user 'test'@'10.8.156.%' identified by '123456';

  • 说明：%表示通配符，任意的

• 用户授权：

  • 格式：grant 权限 privileges on 库名.表名 to 'user'@'host' identified by 'password';

  • 示例：grant all privileges on *.* to 'test'@'10.8.156.%' identified by '123456';

  • 说明：权限可以是select、delete、insert、update等，all表示所有权限；*表示所有

• 刷新权限：flush privileges;

• 取消授权：

  • 格式：revoke 权限 privileges on 库名.表名 from 'user'@'host';

  • 示例：revoke delete privileges on test.* from 'test'@'10.8.156.%';

  • 说明：收回当前局域网内的test用户在test库下所有的表的删除权限

• 删除用户：

  • 格式：drop user 'user'@'host';

  • 示例：drop user 'test'@'10.8.156.%';

• linux下远程登录：

  • bind-address=127.0.0.1 改为：bind-address=0.0.0.0

  • 添加指定用户在指定主机的操作权限：

  grant all privileges on *.* to 'root'@'%' identified by '123456';

备份与恢复

• 备份：

  • 说明：就是将数据库中的数据(SQL语句)保存到一个文件中

  • 示例：mysqldump -uroot -p test > test.sql

• 恢复：

  • 说明：将保存SQL语句的文件解析，挨个执行其中的SQL语句

  • 示例：mysql -uroot -p test < test.sql