MySQL数据库安装与语句总结

一、MySQL语句总结

创建 --create

创建 <create>
create database  数据库名 [charset=utf8];
create table  数据表名 (
    (字段 类型 约束[, 字段 类型 约束]) | 
    -- 级联删除/级联更新 on delete/update cascade
    [[constraint 外键名] foreign key(字段) references 表名(字段)] [on delete cascade]| 
    --终端执行OK,导入sql文件报错,原因未知
    [constraint  主键约束的名字  primary key(列名1,列名2)] | 
    -- 联合主键
    [primary key(列名1,列名2)] | 
    -- 设置字段单列唯一与联合唯一
    [unique(字段)] | 
    [unique(字段1, 字段2)]
);
create view 视图名 as select语句;
create index 索引名称 on 表名(字段名(长度))
* 如果指定字段是字符串，需要指定长度，建议长度与定义字段时的长度一致
* 字段类型如果不是字符串，可以不填写长度部分 

创建数据库用户

create user songbai identified by 'songbai'; 
grant all on songbai_mall.* to 'songbai'@'%'; 
flush privileges;
 
第一句：创建用户账号 songbai, 密码 songbai (由identified by 指明)
第二句：授权songbai_mall数据库下的所有表（songbai_mall.*）的所有权限（all）给用户songbai在以任何ip访问数据库的时候（'songbai'@'%'）
第三句：刷新生效用户权限
 
MySQL SERVER 8.0以上版本 添加用户方式
insert into user(user, host, authentication_string, ssl_cipher, x509_issuer, x509_subject) 
values('songbai','%', concat('*',upper(sha1(unhex(sha1('songbai'))))),'','','');

删除 --drop、delete

drop table 表名;
drop view 视图名;
drop index 索引名 on 表名;
drop database 数据库名;
delete from 表名 [where 条件]; # 物理删除
逻辑删除实际上是在表中添加一个字段(类型一般为bit)用于标记是否删除
-- 清空表数据并将AUTO_INCREMENT置为1
truncate 表名; 

修改 --alter、update

1. 修改表结构<alter table>
alter table 表名 add 字段名 类型;
alter table 表名 add primary key(设置为主键的字段);
alter table 表名 add primary key(字段1,字段2); # 添加联合主键
alter table 表名 add constraint 主键约束的名字 primary key(列名1,列名2); # 添加联合主键并命名
alter table 表名 add unique key(添加唯一约束的字段) # 为指定字段添加唯一约束
alter table 表名1 add foreign key (设置为外键的字段1) references 表名2(与字段1关联的主键字段);
alter table 旧表名 rename 新表名;
alter table 表名 modify 字段名 类型及约束;
alter table 表名 change 原字段名 新字段名 类型及约束;
alter table 表名 drop 列名1 [,drop 列名2];
alter table 表名 drop primary key; 删除主键约束但保留字段
alter table 表名 drop foreign key 外键名称;
* 外键名称使用show create table 表名;查看外键名称
* mysql中没有多主键，只有联合主键。也就是说不能够同时存在多个独立的primary key,但可以有多个unique
 
2. 修改编码模式 <(alter database set) | (alter table set)>
alter database 数据库名 character set utf8;
alter database 数据库名 charset=utf8
alter table 表名 character set utf8;
alter table 表名 modify 字段名 character set utf8;   #修改字段编码
 
3.修改表引擎
alter table 表名 ENGINE=MyISAM;
*** 需要注意的是，更改表引擎时可能会出现一些问题，比如由于当前引擎的限制导致无法更改。因此，在更改表引擎之前，应该确保当前引擎能够支持更改，并进行充分地备份数据。
 
4. 修改表记录 <update>
update 表名 set 字段1=值1,字段2=值2... [where 条件];

插入 --insert into

insert into 表名 values(...);
insert into 表名 values(...),(...);
insert into 表名(字段1,...字段n) values (值1,...,值n);
insert into 表名(字段1,...字段n) values (值1,...,值n),(值1,...,值n);
insert into 表名 分组结果;
eg:
    insert into goods_cates (name) select cate_name from goods group by cate_name;
* 将分组的结果select cate_name from goods group by cate_name 插入到表goods_cates的name字段中

查询 --select、show、desc、where、having . . .

1. 简单查询
select version();
select now();
select database();
select user();
select * from 表名|视图名 [where 条件];
select * from 数据库名.表名|数据库名.视图名 [where 条件];
select * from 表名1,表名2 ... where 条件; 
说明： 
1. 内连接查询是 显式的内连接,多表查询是 隐式的内连接
2. 多表查询是在笛卡尔积的基础之上的
3.相对而言，隐式连接好理解好书写，语法简单，担心的点较少。但是显式连接可以减少字段的扫描，有更快的执行速度。这种速度优势在3张或更多表连接时比较明显
4.显示内连接和隐示内连接获得的查询结果是一样的
select 字段1,字段2,... from 表名 [where 条件];
select distinct 字段1,字段2,... from 表名;  # 根据指定字段(可以为多个)去重
 
show databases;
show tables;                # 包含视图
show index from 表名;       # 查看索引
show create table 表名;
show create database 数据库名;
show engines; 查看支持的引擎
desc 数据表名;
 
2. 复杂查询
Ⅰ. 语法结构
    select 字段,(聚合函数,表达式)
    from  表名1,表名2,...
    where 条件
    order by  字段 asc| desc
    group by 字段名
    limit start,count
 
   执行顺序
    from 表名
    where ....
    group by ...
    select distinct *
    having ...
    order by ...
    limit start,count
 
Ⅱ. 条件查询
select * from 表名 where 字段名 like '黄%' | '黄_' | ; 
# "%"  任意零个或多个字符 "_"  一个任意字符
select * from 表名 where name rlike "正则表达式";
# rlike正则查找
select * from 表名 where 字段名 in 非连续范围;
select * from 表名 where 字段名 between n and m;   # n到m
select * from 表名 where 字段名 is null;
Ⅲ. 排序查询
select * from 表名 order by 字段名1 asc|desc [,字段名2 asc|desc,...]
 # asc 升序,desc 降序;按字段1、字段2、...逐次排序
Ⅳ. 聚合函数查询
select count(*) from 表名;
select max(字段名)[,min(字段名),sum(字段名),avg(字段名)] from 表名;
* round(计算结果 , 小数位数)  --四舍五入
* char_length(字段名) -- 字符长度
Ⅴ. 分组查询
select 字段名1,group_concat(字段名2,[,字段3])[,group_concat(字段名4,[,字段5]),...] from 表名 group by 字段名1;
*select后面可以跟group_concat(字段名1,[,字段名2...])和聚合函数(max、min、avg、sum)、分组的字段名(只能更分组的字段名,其他字段名不行)
eg:
  select name,group_concat(id,cate_id),group_concat(cate_id,price) from goods group by name;
 
select 字段名1,count(*) from 表名 group by 字段名1 having count(*)>2;
select 字段名1,count(*) from 表名 group by 字段名1 with rollup;
*with rollup的作用是：在最后新增一行，来记录当前列里所有记录的总和
Ⅵ. 连接查询
select * from 表1 inner|left|right join 表2 on 条件 where 条件

取别名 --as

select 字段 as 别名,字段 as 别名,... from 数据表 as 别名 [where 条件];
select b.字段1 as 字段1别名, b.字段2 as 字段2别名,... from 表名 as b;

数据表导入导出

数据表导入(终端环境->进入数据库)
激活要导入的数据库
一. 使用终端命令
  方式1 source 数据表存储路径;
  方式2 mysql [-h数据库ip地址， --port 端口号] -u数据库用户名 -p 数据库名 < areas.sql
  # mysql -h10.211.55.5 -usongbai -p songbai_mall < areas.sql
二. 使用脚本
   1.建立"文件名.sh"文件
   2.文件内容:
     #!/bin/bash
     mysql -h10.211.55.5 -usongbai -p songbai_mall < areas.sql
   3.修改文件的执行权限
	chmod +x 文件名.sh
   4.执行如下命令导入数据
	文件路径/文件名.sh
 
导出所有数据库
mysqldump --column-statistics=0 -uroot -p --all-databases --lock-all-tables > dump.sql
导出数据库中所有的数据表(终端环境)
mysqldump -u root -p 数据库名 > dump.sql
数据表导出(终端环境)
mysqldump -u root -p 数据库名 数据表名 > dump.sql
 
*说明：--column-statistics=0 解决客户端mysqldump的版本大于8，而MySQL数据库是比较老的版本问题

其他指令

1. mysql客户端登录
mysql -uroot -p
mysql -uroot -p密码
 
2. 退出mysql客户端登录
exit/quit/ctrl+d
 
3. 使用数据库
use python04new;
 
4. 数据比较多时使用\G进行分组显示(一行一行显示):  
查询结果集\G
 
5. 复制数据表-复制表数据
create table 表名 查询语句
eg: create table t1 select * from t2
 
6. 复制数据表-不复制表数据
eg: 
create table t1 like t2
create table t1 select * from t2 where 1=2
 
修改数据库名: https://www.if98.com/justtest2020/database/110016.html

其他说明

对于innodb 存储引擎来说，一张表必须有一个主键; 如果建表时没有指定主键,MySQL会检索约束为 not null unique 的第一个字段作为主键，如果没有则创建一个影藏的主键。

二、MySQL安装与配置

1. 服务端安装

• 安装服务器端：在终端中输入如下命令，回车后，然后按照提示输入

  sudo apt-get install mysql-server

• 启动服务

  sudo service mysql start

• 查看进程中是否存在mysql服务

  ps ajx|grep mysql

• 停止服务

  sudo service mysql stop

• 重启服务

  sudo service mysql restart

2. 客户端安装

• 在终端运行如下命令，按提示填写信息

  sudo apt-get install mysql-client

• 连接数据库服务器

  mysql -u用户名 -p用户密码

• 退出

  quit 或者 exit

3. 参数配置

• 配置文件目录

  /etc/mysql/mysql.cnf

• 主要配置

  # 表示服务器绑定的ip，默认为127.0.0.1
  bind-address
  # 表示端口，默认为3306
  port
  # 表示数据库目录，默认为/var/lib/mysql
  datadir
  # 表示普通日志，默认为/var/log/mysql/mysql.log
  general_log_file
  # 表示错误日志，默认为/var/log/mysql/error.log
  log_error

三、Mysql与Python交互

1. Python 中操作MysqlSQL流程图

2. 代码演练

#  *_* coding:utf8 *_*
from pymysql import *
 
class JDQuery(object):
 
    def __init__(self):
        self.conn = connect(
            host="localhost", 
            port=3306, user="root", 
            password="", 
            database="jingdong", 
            charset="utf8")
        self.cs = self.conn.cursor()
        # 查询结果以字典形式输出
        self.cs = self.conn.cursor(cursors.DictCursor)
 
    def __del__(self):
        self.cs.close()
        self.conn.close()
        print("welcome use jing-dong query system again !")
 
    def execute_sql(self, sql):
        self.cs.execute(sql)
        for temp in self.cs.fetchall():
            print(temp)
 
    @staticmethod
    def print_menu():
        print("----  welcome use jing-dong mini goods query system !  ----")
        print("----1.query all goods infomations !----")
        print("----2.query all goods category !----")
        print("----3.query all goods brands !----")
        print("----4.add all goods brands !----")
        print("----0.exit query system !----")
 
    def show_all_goods(self):
        sql = "select * from goods"
        self.execute_sql(sql)
 
    def show_goods_cate(self):
        sql = "select * from goods_cates"
        self.execute_sql(sql)
 
    def show_goods_brand(self):
        sql = "select * from goods_brands"
        self.execute_sql(sql)
 
    def add_goods_brand(self):
        brand_name = input("请输入新增品牌: ")
 
        sql = "insert into goods_brands(name) values(%s)"
        self.cs.execute(sql, [brand_name])
        self.conn.commit()
        sql = "select * from goods_brands where name=%s"
        self.cs.execute(sql, [brand_name])
        print(self.cs.fetchone())
 
    def run(self):
        while True:
            self.print_menu()
            command = input("请按提示输入操作指令: ")
            if command == "1":
                self.show_all_goods()
            elif command == "2":
                self.show_goods_cate()
            elif command == "3":
                self.show_goods_brand()
            elif command == "4":
                self.add_goods_brand()
            elif command == "0":
                exit()
            else:
                print("输入指令错误! ")
 
 
def main():
    query = JDQuery()
    query.run()
 
 
if __name__ == '__main__':
    main()

四、事务

• 定义：

  所谓事务,它是一个操作序列，这些操作要么都执行，要么都不执行，它是一个不可分割的工作单位。

• 事务四大特性（ACID）

  • 原子性（atomicity）

    一个事务必须被视为一个不可分割的最小工作单元，整个事务中的所有操作要么全部提交成功，要么全部失败回滚，对于一个事务来说，不可能只执行其中的一部分操作，这就是事务的原子性

  • 一致性(Consistency)

    数据库总是从一个一致性的状态转换到另一个一致性的状态。（在前面的例子中，一致性确保了，即使在执行第三、四条语句之间时系统崩溃，支票账户中也不会损失200美元，因为事务最终没有提交，所以事务中所做的修改也不会保存到数据库中。）

  • 隔离性(Isolation)

    通常来说，一个事务所做的修改在最终提交以前，对其他事务是不可见的。（在前面的例子中，当执行完第三条语句、第四条语句还未开始时，此时有另外的一个账户汇总程序开始运行，则其看到支票帐户的余额并没有被减去200美元。）

  • 持久性(Durability)

    一旦事务提交，则其所做的修改会永久保存到数据库。（此时即使系统崩溃，修改的数据也不会丢失。）

• 开启事务

  # 开启事务后执行修改命令，变更会维护到本地缓存中，而不维护到物理表中
  begin;
  # 或者
  start transaction;

• 提交事务

  # 将缓存中的数据变更维护到物理表中
  commit;

• 回滚事务

  # 放弃缓存中变更的数据
  rollback;

五、账户管理

1. 查看所有用户

• 所有用户及权限信息存储在mysql数据库的user表中
• 主要字段说明：
  • Host表示允许访问的主机
  • User表示用户名
  • authentication_string表示密码，为加密后的值

2. 创建账户、授权

• 需要使用实例级账户登录后操作，以root为例
• 常用权限主要包括：create、alter、drop、insert、update、delete、select
• 如果分配所有权限，可以使用all privileges
• 创建账户&授权

创建格式:	
	grant 权限列表 on 数据库 to '用户名'@'访问主机' identified by '密码';
示例1:
	创建一个laowang的账号，密码为123456，只能通过本地访问, 并且只能对jing_dong数据库中的所有表进行读操作
	1. mysql -uroot -p
	2. grant select on jing_dong.* to 'laowang'@'localhost' identified by '123456';
示例2:
	创建一个laoli的账号，密码为12345678，可以任意电脑进行链接访问, 并且对jing_dong数据库中的所有表拥有所有权限
	2. grant all privileges on jing_dong.* to "laoli"@"%" identified by "12345678"
说明:
- 可以操作python数据库的所有表，方式为:jing_dong.*
- 访问主机通常使用 百分号% 表示此账户可以使用任何ip的主机登录访问此数据库
- 访问主机可以设置成 localhost或具体的ip，表示只允许本机或特定主机访问
- 查看用户有哪些权限
	show grants for laowang@localhost;
	

3. 账户操作

• 修改权限

  修改格式:	
  	grant 权限名称 on 数据库 to 账户@主机 with grant option;
   
  示例:
  	1. mysql -uroot -p
  	2. grant select,insert on jingdong.* to 'laowang'@'localhost' with grant option;
  	3. flush privileges

• 修改密码

  • 使用root登录，修改mysql数据库的user表

    -- 使用password()函数进行密码加密
    -- update user set authentication_string=password('新密码') where user='用户名';
     
    -- eg:
    update user set authentication_string=password('123') where user='laowang';
     
    -- 注意 ubuntu mysql5.7版本需增加一条指令
    update user set plugin="mysql_native_password";

  • 修改完成后需要刷新权限

    flush privileges

• 远程登录 (谨慎使用)

如果向在一个Ubuntu中使用msyql命令远程连接另外一台mysql服务器的话，通过以下方式即可完成，但是此方法仅仅了解就好了，不要在实际生产环境中使用
 
- 修改 /etc/mysql/mysql.conf.d/mysqld.cnf 文件
- service mysql restart
 
连接不上说明
1. 网络不通
2. 查看数据库是否配置了bind_address参数， 如果设置了bind_address=127.0.0.1 那么只能本地登录
3. 查看数据库是否设置了skip_networking参数， 如果设置了该参数，那么只能本地登录mysql数据库
4. 端口指定是否正确

• 删除账户

  • 说明：

    说明: 推荐使用语法1删除用户, 如果使用语法1删除失败，采用语法2方式
    忘记 root 账户密码: http://blog.csdn.net/lxpbs8851/article/details/10895085

  • 方式1:

    -- drop user '用户名'@'主机';
    -- eg:
    drop user 'laowang'@'%';

  • 方式2:

    -- delete from user where user='用户名';
    -- eg:
    delete from user where user='laowang';

  • 操作结束之后需要刷新权限

    flush privileges

六、MySQL主从同步

MySQL主从同步配置示意图

• 主从同步定义:

  	主从同步使得数据可以从一个数据库服务器复制到其他服务器上，在复制数据时，一个服务器充当主服务器（master），其余的服务器充当从服务器（slave）。因为复制是异步进行的，所以从服务器不需要一直连接着主服务器，从服务器甚至可以通过拨号断断续续地连接主服务器。通过配置文件，可以指定复制所有的数据库，某个数据库，甚至是某个数据库上的某个表
  

• 使用主从同步的好处：

  • 数据备份
  • 读写分离
  • 负载均衡

• 主从同步机制

  	Mysql服务器之间的主从同步是基于二进制日志机制，主服务器使用二进制日志来记录数据库的变动情况，从服务器通过读取和执行该日志文件来保持和主服务器的数据一致。
  	在使用二进制日志时，主服务器的所有操作都会被记录下来，然后从服务器会接收到该日志的一个副本。从服务器可以指定执行该日志中的哪一类事件（譬如只插入数据或者只更新数据），默认会执行日志中的所有语句。
  	每一个从服务器会记录关于二进制日志的信息：文件名和已经处理过的语句，这样意味着不同的从服务器可以分别执行同一个二进制日志的不同部分，并且从服务器可以随时连接或者中断和服务器的连接。
  	主服务器和每一个从服务器都必须配置一个唯一的ID号（在my.cnf文件的[mysqld]模块下有一个server-id配置项），另外，每一个从服务器还需要通过CHANGE MASTER TO语句来配置它要连接的主服务器的ip地址，日志文件名称和该日志里面的位置（这些信息存储在主服务器的数据库里）
  

• 基本步骤

  1. 在主服务器上，开启 二进制日志机制 和 配置一个独立的ID
  2. 在每一个从服务器上，配置一个唯一的ID，创建一个用来专门复制主服务器数据的账号
  3. 在开始复制进程前，在主服务器上记录二进制文件的位置信息
  4. 如果在开始复制之前，数据库中已经有数据，就必须先创建一个数据快照（可以使用mysqldump导出数据库，或者直接复制数据文件）
  5. 配置从服务器要连接的主服务器的IP地址和登陆授权，二进制日志文件名和位置

• 详细配置步骤

  主和从的身份可以自己指定，我们将虚拟机Ubuntu中MySQL作为主服务器，将Windows中的MySQL作为从服务器。 在主从设置前，要保证Ubuntu与Windows间的网络连通。

  • 1 - 在主服务器Ubuntu上进行备份（将所有数据库导出），执行命令：

    mysqldump -uroot -pmysql --all-databases --lock-all-tables > ~/master_db.sql

  • 2 - 将主服务器数据库导入从服务器 （保持主从数据库数据一致）

    mysql –uroot –pmysql < master_db.sql

  • 3 - 在主从服务器中设置mysqld的配置文件，主服务器开启log_bin和server-id；从服务器开启server-id（server-id必须唯一） 为防止主从服务器server-id重复 一般设置为ip地址最后一个字节

    • Linux系统:

    sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf

     
    - Windows系统：
     
    ```tex
    1. 找到Windows中MySQL的配置文件（一般为my.ini）
    2. 编辑my.ini文件，修改server-id，并保存退出。 
    -- server-id 建议放在配置第一行，否者可能出现无法启动数据库

  • 4 - 重启mysql服务(主从服务器)

    • linux 系统重启方式

      sudo service mysql restart

    • Windows系统重启方式

      方式一
        - 以管理员身份打开dos
        - net stop mysql
        - net start mysql
        
      方式二
        - 可以在开始菜单中输入services.msc找到并运行
        - 在打开的服务管理中找到MySQL，并重启该服务

  • 5 - 登入主服务器 Linux 中的mysql，为从服务器创建一个账号同步数据使用

    GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' identified by 'slave';
    FLUSH PRIVILEGES;

  • 6 - 获取主服务器的二进制日志信息（File与position）

    SHOW MASTER STATUS;
    -- File 为使用的日志文件名字;  从服务器连接到master主服务器时，master_log_file的值
    -- Position 为使用的文件位置;  从服务器连接到master主服务器时，master_log_pos的值

  • 7 - 登入从服务器windows/linux中的mysql，设置连接到master主服务器

    change master to master_host='10.211.55.5', master_user='slave', master_password='slave',master_log_file='mysql-bin.000006', master_log_pos=590;
     
    -- master_host：主服务器Ubuntu的ip地址
    -- master_log_file: 前面查询到的主服务器日志文件名
    -- master_log_pos: 前面查询到的主服务器日志文件位置

  • 8 - 登入从服务器windows/linux中的mysql，开启同步，查看同步状态

    start slave
    show slave status \G

    • 出现以下字段说明同步正常运行

      # 如果同步异常可以尝试重启电脑
      Slave_IO_Running: Yes
      Slave_SQL_Running: Yes

  • 9 - 测试主从同步

    - 在Ubuntu的MySQL中（主服务器）创建一个数据库
    - 在Windows的MySQL中（从服务器）查看新建的数据库是否存在