MySQL入门

数据库简介：

• 用途：用于存储生活的几乎一切数据，如：身份、住房、车票、网站、…
• 概念：数据库服务器、数据库、数据表、一行数据(一条)、一列数据(字段)
• 分类：
  • 关系型数据库：mysql、oracle、SQL server、…
  • 非关系型数据库：Redis、MongoDB、…
• 发展：口述
• SQL：Structured Query Language，结构化查询语言
• SQL分类：
  • 数据定义语言：DDL
  • 数据操作语言：DML
  • 数据查询语言：DQL
  • 数据控制语言：DCL
  • 数据事务语言：DTL

MySQL(ubuntu)安装：

• 安装：sudo apt-get install mysql-server

  • 若安装出现系统服务问题，很可能是自己添加系统服务的问题，移除即可

• 安全配置：sudo mysql_secure_installation

  • 按照提示，一路进行下去
  • 设置支持远程连接，为后面做准备

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

  • -h：指定主机，localhost/127.0.0.1表示本机，本机可以省略

  • -u：指定用户，默认只有root用户

  • -p：指定密码，输入密码时要与登录分开，以防不测

  • 示例：mysql [-hlocalhost] -uroot -p，然后根据提示输入密码

    • 端口：3306
    • 退出：\h 或 help;
    • 说明：所有的命令都是以’;’结尾

    ​

数据定义语言(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;
• 删除表：drop table user;
• 查看创建方式：
  • 库：show create database test;
  • 表：show create table user;
• 修改字符集：
  • 创建时指定：在创建表的语句后指定字符集。
  • 修改配置文件：sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf，在[mysqld]后面添加
    • character-set-server=utf8
    • 重启服务：sudo service mysql restart
    • 说明：以后再创建的库中的表默认字符集就是utf8
• 修改字段：alter table user modify username char(30);，不能修改名字
• 添加字段：
  • 默认(最后)添加：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;
• 修改字段名称：alter table user change email em char(32);
• 修改位置及名称：add/change/modify
  • alter table user modify em char(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-04-16
  • time：时间，格式：15:36:30
  • datetime：日期时间，格式：2018-04-16 15:37:38
  • timestamp：时间戳
  • year：年，只占1个字节，年份范围：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，不能书写成utf-8
  • 查看支持的存储引擎：show engines;
    • 常用存储引擎：MyISAM和InnoDB

• 索引：

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

  • 分类：

    • 普通索引(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字段的普通索引

    alter table 表名add fulltext(字段);

• 很多选项都可在创建表时指定，如：

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

触发器

太多了暂时看这里

数据操作语言(DML)

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

• 准备：创建一个star表

  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,'黄晓明',200000,'山东',28,0);

    可以同时插入多条数据，每条一个小括号。

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

    insert into star(name,money,age,sex,province) values('小岳岳',4000000, 33, 0, '河南')；

    可以同时插入多条数据，每条一个小括号

  • 方式3： SQL实现将一个表的数据插入到另外一个表的代码

    #如果只希望导入指定字段，可以用这种方法：
    INSERT INTO 目标表 (字段1, 字段2, ...) SELECT 字段1, 字段2, ... FROM 来源表;
    INSERT INTO orders (user_account_id, title) SELECT m.user_id, m.title FROM meeting m where m.id=1;

  • 方式4： 向表 charger 插入一条数据，已存在就对表 charger 更新 type,update_at 字段；

    INSERT INTO `charger` (`id`,`type`,`create_at`,`update_at`) VALUES (3,2,'2017-05-18 11:06:17','2017-05-18 11:06:17') ON DUPLICATE KEY UPDATE `id`=VALUES(`id`), `type`=VALUES(`type`), `update_at`=VALUES(`update_at`);

  • 注意：什么情况下可以不传字段值

    • 自增的字段
    • 有默认值的
    • 可以为空的

  • 说明：插入数据通常使用方式2，可以根基需要进行数据的插入。

• 查询数据：

  • select * from star;

• 删除数据：

  • delete from star where id=1;
  • 警告：删除操作一定不要忘了指定条件，否则后果自负。

• 修改数据：

  • update star set age=22,money=8000000 where id=6;
  • 警告：修改操作一定不要忘了指定条件，否则后果自负。

数据查询语言(DQL)

• 基础查询：select * from star;

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

• 排除重复记录：select distinct name from star;

  • 使用distinct修饰的字段组合不能重复

• UNION:操作符用于合并两个或多个 SELECT 语句的结果集。
  SELECT E_Name FROM Employees_China UNION SELECT E_Name FROM Employees_USA

  列出所有在中国表（Employees_China）和美国（Employees_USA）的不同的雇员名

• 指定条件查询：

  • 条件：

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

  • 示例：

    select id,name,money from star where id > 4;
    select id,name from star where id between 2 and 5;
    select id,name from star where id in(2,4,6);
    select id,name from star where name like '小%';

• 结果集排序：

  • 示例：select id,name,money from star order by money desc;
  • order by ：指定排序字段
    • asc：升序，默认的
    • desc：降序
  • 也可以多字段排序，先按照前面的字段排序，再按照后面字段排序
    • 示例：select id,name,money,age from star order by money desc,age asc;

• 限制结果集：

  • 示例：

    select id,name,money from star limit 3;					# 取3条数据
    select id,name,money from star limit 2,3;				# 偏移2条，然后取3条数据
    select id,name,money from star limit 3 offset 2;		# 偏移2条，然后取3条数据

  • 用途：分页显示，假设一页显示10条数据

    第一页：limit 0, 10
    第二页：limit 10, 10
    第三页：limit 20, 10
    page：表示页数，pageSize：表示一页的大小
    查询条件：limit (page-1)*pageSize, pageSize

• 常用聚合函数

  | 函数 | 功能 |
  | —– | —- |
  | count | 统计个数 |
  | sum | 求和 |
  | max | 最大值 |
  | min | 最小值 |
  | avg | 平均值 |

  1. 使用count时指定任何字段都行
  2. 使用其他函数时必须要指定字段

  示例：

  select count(*) from star;
  select max(money) as m from star;

  as可以给字段其别名

• 分组操作

  • 示例：

    select * from star group by sex;					# 分组
    select count(*), sex from star group by sex;		# 分组统计

• 结果集过滤：

  • 示例：

    select count(*) as c,province from star group by province having c>1;

    搜索所有记录，然后按照省份分组，统计成员大于1的省份

数据控制语言(DCL)

• 创建用户：
  • 格式：create user '用户名'@'主机' identified by '密码'
  • 示例：create user 'jerry'@'%' identified by '123456'
• 授权用户：
  • 格式：grant 权限 privileges on 库.表 to '用户'@'主机' identified by '密码'
  • 示例：grant all privileges on test.* to 'jerry'@'%' identified by '123456'
• 查看授权：
  • 格式：show grants for '用户'@'主机'
  • 示例：show grants from 'jerry'@'%'
• 刷新权限：flush privileges;
• 取消授权：
  • 格式：revoke 权限 privileges on 库.表 from '用户'@'主机';
  • 示例：revoke all privileges on test.* from 'jerry'@'%';
• 删除用户：
  • 格式：drop user '用户'@'主机'
  • 示例：drop user 'jerry@'%'
• root用户远程登录：
  • sudo mysql_secure_installation，根据提示配置，允许root用户远程登录
  • 修改配置文件：sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf
    • 将bind-address=127.0.0.1，改为bind-address=0.0.0.0
  • 给root添加授权主机，%表示所有主机
    • 如：grant all privileges on *.* to 'root'@'%' identified by '123456'
    • 给root用户添加任意主机以123456作为密码登录，对所有的库下的所有表用于所有权限

多表联合查询

• 隐式内连接：没有出现join关键的连接
  • 示例：select username,name from user,goods where user.gid=goods.gid;
  • 说明：查看用户买的商品名
• 显式内连接：会出现join关键字，后面的条件使用on
  • 示例：select username,name from user [inner/cross] join goods on user.gid=goods.gid;
  • 说明：功能同上
  • join前可以加inner，也可以使用cross，也可以省略
• 外左连接：以左表为主
  • 示例：select username,name from user left [outer] join goods on user.gid=goods.gid;
  • 说明：以左表为主，显示左边所有内容，右表不匹配的选项显示NULL

• 外右连接：以右表为主

  • 说明：将left换成right即表示右连接

  多表联合操作

• 子(嵌套)查询操作

  • 说明：一条sql语句的查询结果作为另一条sql语句的条件
  • 示例：select * from user where gid in (select gid from goods);
• 联合更新(同时更新多个表的数据)
  • 示例：update user u,good g set u.gid=0,g.price=8000 where u.gid=g.gid and u.id=1

###清空数据

• 方式1：delete from aaa;
  • 删除数据，在插入数据，自增字段的值仍然接着原来的数据增加
• 方式2：truncate table aaa;
  • 删除数据，同时将auto_increment的初始值设为1

事务处理语言(DTL)

• 开启事务：禁止自动提交
  • set autocommit=0;
• 操作回滚：出现异常时使用
  • rollback;
• 提交操作：没有异常
  • commit;

外键索引(约束)

• 简介：所谓外键就是一个表的主键作为了另一个表的关联字段，然后在添加一点设置就成为了外键

• 作用：可以简化多个关联表之间的操作，如：删除一个另一个跟着删除

• 示例：一个表示用户组的表t_group，一个用户表t_user

  create table t_group(
  	id int,
  	name varchar(20),
  	primary key(id)
  );
  create table t_user(
  	id int,
  	name varchar(20),
  	groupid int,
  	primary key(id),
  	foreign key(groupid) references t_group(id) on delete restrict on update restrict
  );

• 约束行为

  • NO ACTION 或 RESTRICT：删除或修改时不做任何操作，直接报错
  • SET NULL：删除或更新时，都设置为null
  • CASCADE：删除或更新时，同时删除或更新从表的数据

    数据库的备份与恢复

• 备份：mysqldump u-uroot -p test > test.sql

• 恢复：mysql -uroot -p test < test.sql

  python操作mysql

• 安装pycharm，并配置解析器

• 安装pip3：sudo apt-get install python3-pip
• 安装pymysql：pip3 install pymysql
• 示例：见代码。

import pymysql #在 Windows 系统上，import PyMySQL 和 import pymysql 有区别。

# Open database connection
db = pymysql.connect("localhost","root","123456","test" )

# prepare a cursor object using cursor() method
cursor = db.cursor()

# execute SQL query using execute() method.
cursor.execute("SELECT VERSION()")

# Fetch a single row using fetchone() method.
data = cursor.fetchone()

print ("Database version : %s " % data)

# disconnect from server
db.close()

运行此脚本时，会产生以下结果

Database version : 5.7.14-log

COMMIT操作

提交是一种操作，它向数据库发出信号以完成更改，并且在此操作之后，不会更改任何更改。

db.commit()

回滚操作

如果您对一个或多个更改不满意，并且要完全还原这些更改，请使用rollback()方法。

db.rollback()

断开数据库连接

要断开数据库连接，请使用close()方法。

db.close()

如果用户使用close()方法关闭与数据库的连接，则任何未完成的事务都将被数据库回滚。 但是，您的应用程序不会依赖于数据级别的实现细节，而是明确地调用提交或回滚。

赏

谢谢你请我吃糖果