Fork me on GitHub

探寻数据库

Posted on | Post modified: | In
Words count in article: 475 | Reading time ≈ 2

mysql命令行操作小结

感觉这些知识又快忘记了

  • 登陆 mysql -u root -p
  • 创建数据库 create database databasename
  • 删除数据库 drop database databasename
  • 从外部sql文件导入，如果sql文件中没有创建数据库需要先指定一个数据库然后source path/*.sql就可以导入了，再也不用受烦人的phpmyadmin影响了
  • 删除数据表drop table table_name
  • 导出表mysqldump -u root -p RUNOOB runoob_tbl > dump.txt
  • 导出数据库mysqldump -u root -p RUNOOB > database_dump.txt
  • 备份所有数据库mysqldump -u root -p --all-databases > database_dump.txt

一般sql文件的写法：

1
2
3
4
5
6
7
8
9
drop database if exists DBName;
creat database DBName;
use DBName;

#开始创建表
create ......

#开始插入数据
insert....

使用pymysql连接数据库

1
2
3
4
5
6
7
8
9
10
11
import pymsql
con = pymysql.connect('localhost', 'root', '', 'mpsa')
try:
    with con.cursor() as cursor:
        sql = 'drop database runoob'
        cursor.execute(sql)

    con.commit()

finally:
    con.close()

那个cursor函数我也不是很明白为什么，不过一般的用法就是这样啦

php连接数据库

使用php连接数据库,创建数据库，然后删除

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
$conn = mysqli_connect($dbhost, $dbuser, $dbpass);
if(!$conn){
    die("error!!");
}
echo "连接成功";
//查询数据
$sql = "create database runoob";
$retval = mysqli_query($conn, $sql);
if(!$retval){
    die("查询数据失败！");
}
echo "创建成功";
$sql1 = "drop database runoob";
$retval = mysqli_query($conn, $sql);
mysqli_close($conn);

遇到了问题：

php6

查了一下stackoverflow
看到了这个回答：

The mysqli->connect_error property only works properly as of PHP versions 5.2.9 and 5.3.0. Use the mysqli_connect_error() function if compatibility with earlier PHP versions is required.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
$mysqli = new mysqli('localhost', 'my_user', 'my_password', 'my_db');

/*
 * This is the "official" OO way to do it,
 * BUT $connect_error was broken until PHP 5.2.9 and 5.3.0.
 */
if ($mysqli->connect_error) {
    die('Connect Error (' . $mysqli->connect_errno . ') '
            . $mysqli->connect_error);
}

/*
 * Use this instead of $connect_error if you need to ensure
 * compatibility with PHP versions prior to 5.2.9 and 5.3.0.
 */
if (mysqli_connect_error()) {
    die('Connect Error (' . mysqli_connect_errno() . ') '
            . mysqli_connect_error());
}

原来是建立过程中的不稳定，怪不得我新建的数据库没有被删掉.

参考

stackoverflow的回答
pymysql文档，写的很简单