Mysql的基础学习（下）-阿里云开发者社区

1对1

已知如下表所示，商品类别信息表（具有两层类别关系，通过pid表示，0表示一级类别）
mysql> select * from type;
+----+-----------+------+
| id | name      | pid  |
+----+-----------+------+
|  1 | 服装      |    0 |
|  2 | 数码      |    0 |
|  3 | 男装      |    1 |
|  4 | 手机      |    2 |
|  5 | 相机      |    2 |
|  6 | 电脑      |    2 |
|  7 | 女装      |    1 |
|  8 | 童装      |    1 |
|  9 | 食品      |    0 |
| 10 | 零食      |    9 |
| 11 | 特产      |    9 |
| 12 | 休闲装    |    1 |
+----+-----------+------+
-- 查询二级类别信息，并关联出他们的父类别名称
mysql> select  t1.id,t1.name,t2.name  from type t1,type t2 where t1.pid!=0 and t1.pid=t2.id;
+----+-----------+--------+
| id | name      | name   |
+----+-----------+--------+
|  3 | 男装      | 服装   |
|  4 | 手机      | 数码   |
|  5 | 相机      | 数码   |
|  6 | 电脑      | 数码   |
|  7 | 女装      | 服装   |
|  8 | 童装      | 服装   |
| 10 | 零食      | 食品   |
| 11 | 特产      | 食品   |
| 12 | 休闲装    | 服装   |
+----+-----------+--------+
9 rows in set (0.01 sec)
--统计每个一级类别下都有多少个子类别。
mysql> select t1.id,t1.name,count(t2.id) from type t1,type t2 where t1.pid=0 and t1.id=t2.pid group by t1.id;
+----+--------+--------------+
| id | name   | count(t2.id) |
+----+--------+--------------+
|  1 | 服装   |            4 |
|  2 | 数码   |            3 |
|  9 | 食品   |            2 |
+----+--------+--------------+
3 rows in set (0.00 sec)

数据库的备份和导入

-- 数据库mydb备份
[root@localhost mnt]# mysqldump -u root -p mydb > mydb.sql
password：
-- 备份 mydb库中的stu表
[root@localhost mnt]# mysqldump -u root -p mydb stu > stu.sql
password：
-- 恢复数据（导入）注：mydb库要存在
[root@localhost mnt]# mysql -u root -p mydb < mydb.sql
[root@localhost mnt]# mysql -u root -p mydb < stu.sql

数据库日志的备份和导入

mysql日志
    vim /etc/my.cnf       产看下面这句话56行
    开启日志： 在mysql配置文件中开启：log-bin=mysql-bin
    查看bin-log日志:
    mysql>show binary logs;
    查看最后一个bin-log日志:
    mysql>show master status;
    此时就会多一个最新的bin-log日志
    mysql>flush logs;
    查看最后一个bin日志.
    mysql>show master status;
    mysql>reset master;
    清空所有的bin-log日志
    执行查看bin-log日志
    备份数据:
    mysqldump -u 用户名 -p 库名 -l -F 库名> 库名.sql
    其中：-F即flush logs，可以重新生成新的日志文件，当然包括log-bin日志 
    // Linux关闭MySQL的命令
    $mysql_dir/bin/mysqladmin -uroot -p shutdown
    // linux启动MySQL的命令
    $mysql_dir/bin/mysqld_safe &

MySql的其他操作

1 表复制           2表的索引           3视图
4 内置函数         5 事务处理           6 触发器
7 日志            8 有关慢查询操作      9 数据库的恢复

1. MySQL的表复制
    复制表结构
    mysql> create table 目标表名 like 原表名;
    复制表数据
    mysql> insert into 目标表名 select * from 原表名; 
create table stu2 like stu;
复制stu 名为stu2 ;  只复制结构不复制内容
insert into stu2 select * from stu limit 5;
赋值stu数据前五条添加到 stu2;

2. 数据表的索引
    创建索引
    CREATE INDEX index_name ON table_name (column_list)
    CREATE UNIQUE INDEX index_name ON table_name (column_list)
    删除索引
    DROP INDEX index_name ON talbe_name

3. mysql视图
    创建视图:
    mysql> create view v_t1 as select * from t1 where id>4 and id<11;
    Query OK, 0 rows affected (0.00 sec)
    view视图的帮助信息:
    mysql> ? view
    ALTER VIEW
    CREATE VIEW
    DROP VIEW
    查看视图:
    mysql> show tables;
    删除视图v_t1:
    mysql> drop view v_t1;

4. MySQL的内置函数
    字符串处理函数
    ---------------------------------------------
    *concat(s1,s2,…Sn) 连接s1,s2..Sn为一个字符串
    insert(str,x,y,instr)将字符串str从第xx位置开始，y字符串的子字符串替换为字符串str
    lower(str)将所有的字符串变为小写
    upper(str)将所有的字符串变为大写
    left(str,x)返回字符串中最左边的x个字符
    rigth(str,y)返回字符串中最右边的x个字符
    lpad(str,n,pad)用字符串pad对str最左边进行填充，直到长度为n个字符串长度
    rpad(str,n,pad)用字符串pad对str最右边进行填充，直到长度为n个字符串长度
    trim(str)  去掉左右两边的空格
    ltrim(str) 去掉字符串str左侧的空格
    rtrim(str) 去掉字符串str右侧的空格
    repeat(str,x)   返回字符串str重复x次
    replace(str,a,b)将字符串的的a替换成b
    strcmp(s1,s2)   比较字符串s1和s2
    substring(s,x,y)返回字符串指定的长度
    *length(str)  返回值为字符串str 的长度    
    数值函数
    -----------------------------------------------------
    *abs(x)    返回x的绝对值
    ceil(x)   返回大于x的最小整数值
    floor(x)  返回小于x的最大整数值
    mod(x,y)  返回x/y的取余结果
    rand()    返回0~1之间的随机数
    *round(x,y)返回参数x的四舍五入的有y位小数的值
    truncate(x,y) 返回x截断为y位小数的结果
    日期和时间函数
    ---------------------------------------------------
    curdate()  返回当前日期,按照’YYYY-MM-DD’格式
    curtime()  返回当前时间,当前时间以'HH:MM:SS' 
    *now()      返回当前日期和时间,
    *unix_timestamp(date) 返回date时间的unix时间戳
    from_unixtime(unix_timestamp[,format])  返回unix时间的时间
    week(date)      返回日期是一年中的第几周
    year(date)      返回日期的年份
    hour(time)      返回time的小时值
    minute(time)    返回日time的分钟值
    monthname(date) 返回date的月份
    *date_fomat(date,fmt) 返回按字符串fmt格式化日期date值
    date_add(date,INTERVAL,expr type) 返回一个日期或者时间值加上一个时间间隔的时间值
    *datediff(expr,expr2)   返回起始时间和结束时间的间隔天数
    //统计时间戳647583423距离当前时间相差天数（生日天数（不考虑年份））
    mysql> select datediff(date_format(from_unixtime(647583423),"2017-%m-%d %h:%i:%s"),now());
    其他常用函数
    ------------------------------------------------------
    *database() 返回当前数据库名
    version()   返回当前服务器版本
    user()      返回当前登陆用户名
    inet_aton   返回当前IP地址的数字表示 inet_aton("192.168.80.250");
    inet_ntoa(num) 返回当前数字表示的ip   inet_ntoa(3232256250);
    *password(str)  返回当前str的加密版本
    *md5(str)      返回字符串str的md5值

5. MySQL的事务处理
    关闭自动提交功能（开启手动事务）
    mysql> set autocommit=0;
    从表t1中删除了一条记录
    mysql> delete from t1 where id=11;
    此时做一个p1还原点:
    mysql> savepoint p1;
    再次从表t1中删除一条记录:
    mysql> delete from t1 where id=10;
    再次做一个p2还原点:
    mysql> savepoint p2;
    此时恢复到p1还原点，当然后面的p2这些还原点自动会失效: 
    mysql> rollback to p1;
    退回到最原始的还原点:
    mysql> rollback;
    回滚
    开启自动事务提交（关闭手动事务）
    mysql> set autocommit=1;

6. MySQL的触发器
    格式：1、触发器的定义：
      CREATE TRIGGER trigger_name trigger_time trigger_event
        ON tbl_name FOR EACH ROW trigger_stmt
      说明：
      # trigger_name：触发器名称
      # trigger_time:触发时间，可取值：BEFORE或AFTER
      # trigger_event：触发事件，可取值：INSERT、UPDATE或DELETE。
      # tb1_name：指定在哪个表上
      # trigger_stmt：触发处理SQL语句。
    示例：
        mysql> delimiter $$
    mysql> create trigger del_stu before delete on stu for each row
      -> begin
      ->  insert into stu_bak values(old.id,old.name,old.sex,old.age,old.addtime);
      -> end;
      -> $$
        Query OK, 0 rows affected (0.05 sec)
        mysql> delimiter ;

7. mysql日志
    进入 /usr/my.cnf
    开启日志： 在mysql配置文件中开启：log-bin=mysql-bin
    查看bin-log日志:
    mysql>show binary logs;
    查看最后一个bin-log日志:
    mysql>show master status;
    此时就会多一个最新的bin-log日志
    mysql>flush logs;
    查看最后一个bin日志.
    mysql>show master status;
    mysql>reset master;
    清空所有的bin-log日志
    执行查看bin-log日志
    备份数据:
    mysqldump -uroot -pwei test -l -F '/tmp/test.sql'
    其中：-F即flush logs，可以重新生成新的日志文件，当然包括log-bin日志 
    // Linux关闭MySQL的命令
    $mysql_dir/bin/mysqladmin -uroot -p shutdown
    // linux启动MySQL的命令
    $mysql_dir/bin/mysqld_safe &

8、有关慢查询操作：
    开户和设置慢查询时间:
    vi /etc/my.cnf
    log_slow_queries=slow.log
    long_query_time=5
    查看设置后是否生效
    mysql> show variables like "%quer%"; 
    慢查询次数:
    mysql> show global status like "%quer%";

9 数据库的恢复
    1. 首先恢复最后一次的备份完整数据
    [root@localhost mnt]# mysql -u root -p mydemo<mydemo_2017-7-26.sql 
    Enter password: 
    2. 查看bin-log日志
    [root@localhost data]# mysqlbinlog --no-defaults mysql-bin.000009;
      查找到恢复的节点
    3. 执行bin-log日志文件，恢复最后一块的增量数据。 
    [root@localhost data]# mysqlbinlog --no-defaults --stop-position="802" mysql-bin.000009|mysql -u root -p123456 mydemo;

Python3 Mysql数据库连接

    Python3 MySQL 数据库连接
======================================================
1. 什么是 PyMySQL？
    PyMySQL 是在 Python3.x 版本中用于连接 MySQL 服务器的一个库，Python2中则使用mysqldb。
    PyMySQL 遵循 Python 数据库 API v2.0 规范，并包含了 pure-Python MySQL 客户端库。
2. PyMySQL安装
    PyMySQL下载地址：https://github.com/PyMySQL/PyMySQL。
    2.1 使用pip命令进行安装：
    $ pip install PyMySQL
    2.2 使用 git 命令下载安装包安装(你也可以手动下载)：
    $ git clone https://github.com/PyMySQL/PyMySQL
    $ cd PyMySQL/
    $ python3 setup.py install
3. 数据库连接
   通过如下代码测试数据库连接
----------------------------------------------------------------------
    #!/usr/bin/python3
    import pymysql
    # 打开数据库连接
    db = pymysql.connect("localhost","root","123456","mydb" )
    # 使用 cursor() 方法创建一个游标对象 cursor
    cursor = db.cursor()
    # 使用 execute()  方法执行 SQL 查询 
    cursor.execute("SELECT VERSION()")
    # 使用 fetchone() 方法获取单条数据.
    data = cursor.fetchone()
    print ("Database version : %s " % data)
    # 关闭数据库连接
    db.close()
4. 执行数据查询：
-------------------------------------------------------------------
    #!/usr/bin/python3
    import pymysql
    # 打开数据库连接
    db = pymysql.connect("localhost","root","","mydemo" )
    # 使用 cursor() 方法创建一个游标对象 cursor
    cursor = db.cursor()
    # SQL 查询语句
    sql = "select * from stu limit %d" % (3)
    #sql = "select * from stu"
    try:
       # 执行SQL语句
       cursor.execute(sql)
       # 获取所有记录列表
       results = cursor.fetchall()
       for row in results:
          id = row[0]
          name = row[1]
          sex = row[2]
          age = row[3]
          classid = row[4]
           # 打印结果
          print ("id=%d,name=%s,sex=%s,age=%d,classid=%s" % (id,name,sex,age,classid))
    except:
       print ("Error: unable to fetch data")
    # 关闭数据库连接
    db.close()
5. 执行数据添加
---------------------------------------------------------------------
#!/usr/bin/python3
import pymysql
# 打开数据库连接
db = pymysql.connect("localhost","root","","mydemo" )
# 使用 cursor() 方法创建一个游标对象 cursor
cursor = db.cursor()
# SQL 插入语句
sql = "INSERT INTO stu(name,sex,age,classid) values('%s','%c','%d','%s')" % ('uu142','m',22,'lamp180') 
try:
   # 执行sql语句
   cursor.execute(sql)
   # 执行sql语句
   db.commit()
   print("ok: %d " % (cursor.rowcount))
except:
   # 发生错误时回滚
   db.rollback()
# 关闭数据库连接
db.close() 
6. 执行删除操作
---------------------------------------------------------------------
    #!/usr/bin/python3
    import pymysql
    # 打开数据库连接
    db = pymysql.connect("localhost","root","","mydemo" )
    # 使用 cursor() 方法创建一个游标对象 cursor
    cursor = db.cursor()
    # SQL 删除语句
    sql = "delete from stu where id = '%d'" % (13)
    try:
       # 执行SQL语句
       cursor.execute(sql)
       # 提交修改
       db.commit()
    except:
       # 发生错误时回滚
       db.rollback()
    # 关闭数据库连接
    db.close()
数据库查询操作：  
    Python查询Mysql使用 fetchone() 方法获取单条数据, 使用fetchall() 方法获取多条数据。
        fetchone(): 该方法获取下一个查询结果集。结果集是一个对象
        fetchall(): 接收全部的返回结果行.
        rowcount: 这是一个只读属性，并返回执行execute()方法后影响的行数。
pip命令
------------------------------------------------------------
列出已安装的包：
    $ pip list
    $ pip freeze     # 查看自己安装的
安装软件（安装特定版本的package，通过使用==, &gt;=, &lt;=, &gt;, &lt;来指定一个版本号）**
    $ pip install SomePackage
    $ pip install 'Markdown<2.0'
    $ pip install 'Markdown>2.0,<2.0.3'
卸载软件pip uninstall SomePackage
    $ pip uninstall SomePackage
下载所需的软件包：
    $ pip download SomePackage -d directory 
    例如下载PyMySQL软件包
    $ pip download PyMySQL -d D:/pypackage
安装下载好的软件包文件
    $ pip install 目录/软件包文件名
    如安装PyMySQL软件包
    $ pip install D:/pypackage/PyMySQL-0.7.11-py2.py3-none-any.whl

修改完结符

\d  $$     把最后的完结符号由;改为$$ 用于写代码时用

python3 Mysql数据库连接

    Python3 MySQL 数据库连接
======================================================
1. 什么是 PyMySQL？
    PyMySQL 是在 Python3.x 版本中用于连接 MySQL 服务器的一个库，Python2中则使用mysqldb。
    PyMySQL 遵循 Python 数据库 API v2.0 规范，并包含了 pure-Python MySQL 客户端库。
2. PyMySQL安装
    PyMySQL下载地址：https://github.com/PyMySQL/PyMySQL。
    2.1 使用pip命令进行安装：
    $ pip install PyMySQL
    2.2 使用 git 命令下载安装包安装(你也可以手动下载)：
    $ git clone https://github.com/PyMySQL/PyMySQL
    $ cd PyMySQL/
    $ python3 setup.py install
3. 数据库连接
   通过如下代码测试数据库连接
----------------------------------------------------------------------
    #!/usr/bin/python3
    import pymysql
    # 打开数据库连接
    db = pymysql.connect("localhost","root","123456","mydb" )
    # 使用 cursor() 方法创建一个游标对象 cursor
    cursor = db.cursor()
    # 使用 execute()  方法执行 SQL 查询 
    cursor.execute("SELECT VERSION()")
    # 使用 fetchone() 方法获取单条数据.
    data = cursor.fetchone()
    print ("Database version : %s " % data)
    # 关闭数据库连接
    db.close()
4. 执行数据查询：
-------------------------------------------------------------------
    #!/usr/bin/python3
    import pymysql
    # 打开数据库连接
    db = pymysql.connect("localhost","root","","mydemo" )
    # 使用 cursor() 方法创建一个游标对象 cursor
    cursor = db.cursor()
    # SQL 查询语句
    sql = "select * from stu limit %d" % (3)
    #sql = "select * from stu"
    try:
       # 执行SQL语句
       cursor.execute(sql)
       # 获取所有记录列表
       results = cursor.fetchall()
       for row in results:
          id = row[0]
          name = row[1]
          sex = row[2]
          age = row[3]
          classid = row[4]
           # 打印结果
          print ("id=%d,name=%s,sex=%s,age=%d,classid=%s" % (id,name,sex,age,classid))
    except:
       print ("Error: unable to fetch data")
    # 关闭数据库连接
    db.close()
5. 执行数据添加
---------------------------------------------------------------------
#!/usr/bin/python3
import pymysql
# 打开数据库连接
db = pymysql.connect("localhost","root","","mydemo" )
# 使用 cursor() 方法创建一个游标对象 cursor
cursor = db.cursor()
# SQL 插入语句
sql = "INSERT INTO stu(name,sex,age,classid) values('%s','%c','%d','%s')" % ('uu142','m',22,'lamp180') 
try:
   # 执行sql语句
   cursor.execute(sql)
   # 执行sql语句
   db.commit()
   print("ok: %d " % (cursor.rowcount))
except:
   # 发生错误时回滚
   db.rollback()
# 关闭数据库连接
db.close() 
6. 执行删除操作
---------------------------------------------------------------------
    #!/usr/bin/python3
    import pymysql
    # 打开数据库连接
    db = pymysql.connect("localhost","root","","mydemo" )
    # 使用 cursor() 方法创建一个游标对象 cursor
    cursor = db.cursor()
    # SQL 删除语句
    sql = "delete from stu where id = '%d'" % (13)
    try:
       # 执行SQL语句
       cursor.execute(sql)
       # 提交修改
       db.commit()
    except:
       # 发生错误时回滚
       db.rollback()
    # 关闭数据库连接
    db.close()
数据库查询操作：  
    Python查询Mysql使用 fetchone() 方法获取单条数据, 使用fetchall() 方法获取多条数据。
        fetchone(): 该方法获取下一个查询结果集。结果集是一个对象
        fetchall(): 接收全部的返回结果行.
        rowcount: 这是一个只读属性，并返回执行execute()方法后影响的行数。

pip

pip命令
------------------------------------------------------------
列出已安装的包：
    $ pip list
    $ pip freeze     # 查看自己安装的
安装软件（安装特定版本的package，通过使用==, &gt;=, &lt;=, &gt;, &lt;来指定一个版本号）**
    $ pip install SomePackage
    $ pip install 'Markdown<2.0'
    $ pip install 'Markdown>2.0,<2.0.3'
卸载软件pip uninstall SomePackage
    $ pip uninstall SomePackage
下载所需的软件包：
    $ pip download SomePackage -d directory 
    例如下载PyMySQL软件包
    $ pip download PyMySQL -d D:/pypackage
安装下载好的软件包文件
    $ pip install 目录/软件包文件名
    如安装PyMySQL软件包
    $ pip install D:/pypackage/PyMySQL-0.7.11-py2.py3-none-any.whl

Mysql的基础学习（下）

1对1

数据库的备份和导入

数据库日志的备份和导入

MySql的其他操作

Python3 Mysql数据库连接

修改完结符

python3 Mysql数据库连接

pip

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像

热门

活动广场

任务中心

开发者评测

高校计划

乘风者计划

训练营

阿里云MVP

话题

直播

下载

镜像站

技术资料

插件

Mysql的基础学习（下）

1对1

数据库的备份和导入

数据库日志的备份和导入

MySql的其他操作

Python3 Mysql数据库连接

修改完结符

python3 Mysql数据库连接

pip

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像