数据库无限进化（一）--MYSQL安装开发教程

发表于   |   分类于 ，

数据库安装

1
2
yum install mysql
yum install mysql-server

mysql建账号

1
2
3
4
5
6
CREATE DATABASE cookbook;

CREATE USER 'cbuser'@'localhost' IDENTIFIED BY 'Cbpass1!';
GRANT ALL ON cookbook.* TO 'cbuser'@'localhost' WITH GRANT OPTION;
CREATE USER 'cbuser'@'%' IDENTIFIED BY 'Cbpass1!';
GRANT ALL ON cookbook.* TO 'cbuser'@'%' WITH GRANT OPTION;

mysql 建库表

建库

1
2
3
mysql -h localhost -u cbuser -p

USE cookbook;

建表并插入数据

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE TABLE limbs (things VARCHAR(20), legs INT, arms INT);
INSERT INTO limbs (things, legs, arms) VALUES ('human', 2, 2);
INSERT INTO limbs (things, legs, arms) VALUES ('insect', 6, 0);
INSERT INTO limbs (things, legs, arms) VALUES ('squid', 0, 10);
INSERT INTO limbs (things, legs, arms) VALUES ('octopus', 0, 8);
INSERT INTO limbs (things, legs, arms) VALUES ('fish', 0, 0);
INSERT INTO limbs (things, legs, arms) VALUES ('centipede', 100, 0);
INSERT INTO limbs (things, legs, arms) VALUES ('table', 4, 0);
INSERT INTO limbs (things, legs, arms) VALUES ('armchair', 4, 2);
INSERT INTO limbs (things, legs, arms) VALUES ('photograph', 0, 1);
INSERT INTO limbs (things, legs, arms) VALUES ('tripod', 3, 0);
INSERT INTO limbs (things, legs, arms) VALUES ('Peg Leg Pete', 1, 2);
INSERT INTO limbs (things, legs, arms) VALUES ('space alien', NULL, NULL);

利用文件插入数据

两种模式

1
2
3
4
mysql -u cbuser -p cookbook < mysql.sql

// 登录mysql后
mysql> SOURCE mysql.sql;

mysql应用

分页

1
2
3
4
mysql --pager=/usr/bin/less
mysql> \P
mysql> \P /usr/bin/less
mysql> \n

mysql导入导出

1
2
3
4
5
6
7
% mysql -u cbuser -pCbpass1! cookbook < input > output

% mysql -u cbuser -pCbpass1! cookbook < input | sed -e "s/\t/,/g" > output

% mysql -u cbuser -pCbpass1! cookbook < input | sed -e 's/"/""/g' -e 's/\t/","/g' -e 's/^/"/' -e 's/$/"/' > output

% mysql -u cbuser -pCbpass1! cookbook -e "select * from limbs"

mysql的繁冗级别

1
2
3
% echo " select now()" | mysql -u cbuser -pCbpass1! cookbook -v
% echo " select now()" | mysql -u cbuser -pCbpass1! cookbook -vv
% echo " select now()" | mysql -u cbuser -pCbpass1! cookbook -vvv

交互式mysql会话

1
% mysql -u cbuser -pCbpass1! cookbook --tee=tmp.out

mysql使用变量

1
2
3
4
5
6
7
8
9
10
11
12
mysql> select @max := max(things) from limbs;
mysql> select * from limbs where things = @max;
mysql> select @max;
mysql> set @max=1;select @max;
Query OK, 0 rows affected (0.35 sec)

+------+
| @max |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

查询输出行计数

1
% mysql -u cbuser -pCbpass1! cookbook -e "select * from limbs" --skip-column-names | cat -n

全局状态

1
% mysql -u cbuser -pCbpass1! cookbook -e "show global status like 'Uptime';" --skip-column-names

JAVA环境变量

1
2
export JAVA_HOME=~/jdk1.8.0_141/
export PATH=$PATH:$JAVA_HOME/bin

mybatis账号与建表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
grant all on mybatisdb.* to 'mbuser'@'%' identified by 'Mbpasswd1!';

CREATE TABLE user (
    id INT NOT NULL AUTO_INCREMENT,
    username VARCHAR(32),
    birthday DATE,
    sex CHAR(1),
    address VARCHAR(256),
    PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=27 DEFAULT CHARSET=utf8;

CREATE TABLE orders (
	id INT NOT NULL AUTO_INCREMENT,
	userid INT,
	number VARCHAR(11),
	createtime DATE,
	note VARCHAR(32),
	PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=10000 DEFAULT CHARSET=utf8;

PERL执行SQL语句

示例：

数据文件 websites_perl.sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
/*
 Navicat MySQL Data Transfer

 Source Server         : 127.0.0.1
 Source Server Version : 50621
 Source Host           : localhost
 Source Database       : RUNOOB

 Target Server Version : 50621
 File Encoding         : utf-8

 Date: 06/17/2016 13:41:50 PM
*/

SET NAMES utf8;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
--  Table structure for `websites`
-- ----------------------------
DROP TABLE IF EXISTS `websites`;
CREATE TABLE `websites` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` char(20) NOT NULL DEFAULT '' COMMENT '站点名称',
  `url` varchar(255) NOT NULL DEFAULT '',
  `alexa` int(11) NOT NULL DEFAULT '0' COMMENT 'Alexa 排名',
  `country` char(10) NOT NULL DEFAULT '' COMMENT '国家',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

-- ----------------------------
--  Records of `websites`
-- ----------------------------
BEGIN;
INSERT INTO `websites` VALUES ('1', 'Google', 'https://www.google.cm/', '1', 'USA'), ('2', '淘宝', 'https://www.taobao.com/', '13', 'CN'), ('3', '菜鸟教程', 'http://www.runoob.com/', '5000', 'USA'), ('4', '微博', 'http://weibo.com/', '20', 'CN'), ('5', 'Facebook', 'https://www.facebook.com/', '3', 'USA'), ('7', 'stackoverflow', 'http://stackoverflow.com/', '0', 'IND');
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;

perl连接mysql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
#!/usr/bin/perl -w
 
use strict;
use DBI;
 
my $host = "localhost";         # 主机地址
my $driver = "mysql";           # 接口类型 默认为 localhost
my $database = "RUNOOB";        # 数据库
# 驱动程序对象的句柄
my $dsn = "DBI:$driver:database=$database:$host";  
my $userid = "root";            # 数据库用户名
my $password = "123456";        # 数据库密码
 
# 连接数据库
my $dbh = DBI->connect($dsn, $userid, $password ) or die $DBI::errstr;
my $sth = $dbh->prepare("SELECT * FROM Websites");   # 预处理 SQL  语句
$sth->execute();    # 执行 SQL 操作
 
# 注释这部分使用的是绑定值操作
# $alexa = 20;
# my $sth = $dbh->prepare("SELECT name, url
#                        FROM Websites
#                        WHERE alexa > ?");
# $sth->execute( $alexa ) or die $DBI::errstr;
 
# 循环输出所有数据
while ( my @row = $sth->fetchrow_array() )
{
       print join('\t', @row)."\n";
}
 
$sth->finish();
$dbh->disconnect();

mysql插入操作

1
2
3
4
5
6
7
my $sth = $dbh->prepare("INSERT INTO Websites
                       (name, url, alexa, conutry )
                        values
                       ('Twitter', 'https://twitter.com/', 10, 'USA')");
$sth->execute() or die $DBI::errstr;
$sth->finish();
$dbh->commit or die $DBI::errstr;

使用占位符

1
2
3
4
5
6
7
8
9
10
11
12
my $name = "Twitter";
my $url = "https://twitter.com/";
my $alexa = 10;
my $conutry = "USA";
my $sth = $dbh->prepare("INSERT INTO Websites
                       (name, url, alexa, conutry )
                        values
                       (?,?,?,?)");
$sth->execute($name,$url,$alexa, $conutry) 
          or die $DBI::errstr;
$sth->finish();
$dbh->commit or die $DBI::errstr;

RUBY执行SQL语句

待续。

PHP执行SQL语句

连接mysql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
<?php
$servername = "localhost";
$username = "root";
$password = "";
 
// 创建连接
$conn = mysqli_connect($servername, $username, $password);
 
// 检测连接
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}
echo "连接成功";

mysqli_close($conn);
?>

插入数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";
 
// 创建连接
$conn = mysqli_connect($servername, $username, $password, $dbname);
// 检测连接
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}
 
$sql = "INSERT INTO MyGuests (firstname, lastname, email)
VALUES ('John', 'Doe', 'john@example.com')";
 
if (mysqli_query($conn, $sql)) {
    echo "新记录插入成功";
} else {
    echo "Error: " . $sql . "<br>" . mysqli_error($conn);
}
 
mysqli_close($conn);
?>

插入多笔数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";
 
// 创建链接
$conn = mysqli_connect($servername, $username, $password, $dbname);
// 检查链接
if (!$conn) {
    die("连接失败: " . mysqli_connect_error());
}
 
$sql = "INSERT INTO MyGuests (firstname, lastname, email)
VALUES ('John', 'Doe', 'john@example.com');";
$sql .= "INSERT INTO MyGuests (firstname, lastname, email)
VALUES ('Mary', 'Moe', 'mary@example.com');";
$sql .= "INSERT INTO MyGuests (firstname, lastname, email)
VALUES ('Julie', 'Dooley', 'julie@example.com')";
 
if (mysqli_multi_query($conn, $sql)) {
    echo "新记录插入成功";
} else {
    echo "Error: " . $sql . "<br>" . mysqli_error($conn);
}
 
mysqli_close($conn);
?>

预处理语句

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";
 
// 创建连接
$conn = new mysqli($servername, $username, $password, $dbname);
 
// 检测连接
if ($conn->connect_error) {
    die("连接失败: " . $conn->connect_error);
}
 
// 预处理及绑定
$stmt = $conn->prepare("INSERT INTO MyGuests (firstname, lastname, email) VALUES (?, ?, ?)");
$stmt->bind_param("sss", $firstname, $lastname, $email);
 
// 设置参数并执行
$firstname = "John";
$lastname = "Doe";
$email = "john@example.com";
$stmt->execute();
 
$firstname = "Mary";
$lastname = "Moe";
$email = "mary@example.com";
$stmt->execute();
 
$firstname = "Julie";
$lastname = "Dooley";
$email = "julie@example.com";
$stmt->execute();
 
echo "新记录插入成功";
 
$stmt->close();
$conn->close();
?>

读取数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";
 
// 创建连接
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) {
    die("连接失败: " . mysqli_connect_error());
}
 
$sql = "SELECT id, firstname, lastname FROM MyGuests";
$result = mysqli_query($conn, $sql);
 
if (mysqli_num_rows($result) > 0) {
    // 输出数据
    while($row = mysqli_fetch_assoc($result)) {
        echo "id: " . $row["id"]. " - Name: " . $row["firstname"]. " " . $row["lastname"]. "<br>";
    }
} else {
    echo "0 结果";
}
 
mysqli_close($conn);
?>

update

1
2
3
4
5
6
7
8
9
10
11
12
13
<?php
$con=mysqli_connect("localhost","username","password","database");
// 检测连接
if (mysqli_connect_errno())
{
    echo "连接失败: " . mysqli_connect_error();
}

mysqli_query($con,"UPDATE Persons SET Age=36
WHERE FirstName='Peter' AND LastName='Griffin'");

mysqli_close($con);
?>

delete

1
2
3
4
5
6
7
8
9
10
11
12
<?php
$con=mysqli_connect("localhost","username","password","database");
// 检测连接
if (mysqli_connect_errno())
{
    echo "连接失败: " . mysqli_connect_error();
}

mysqli_query($con,"DELETE FROM Persons WHERE LastName='Griffin'");

mysqli_close($con);
?>

PYTHON执行SQL语句

待续。

查询mysql版本，数据库

1
select version(),database();

PHP MySQL 简介

通过 PHP，您可以连接和操作数据库。

MySQL 是跟 PHP 配套使用的最流行的开源数据库系统。

PHP 与 MySQL 结合是跨平台的。（您可以在 Windows 上开发，在 Unix 平台上应用。）

MySQLi 与 PDO?

MySQLi 和 PDO 有它们自己的优势：

PDO 应用在 12 种不同数据库中， MySQLi 只针对 MySQL 数据库。

所以，如果你的项目需要在多种数据库中切换，建议使用 PDO ，这样你只需要修改连接字符串和部分查询语句即可。 使用 MySQLi, 如果不同数据库，你需要重新编写所有代码，包括查询。

两者都是面向对象, 但 MySQLi 还提供了 API 接口。

两者都支持预处理语句。 预处理语句可以防止 SQL 注入，对于 web 项目的安全性是非常重要的。

Wampserver安装后，默认用户是root，密码为空

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
<?php
$servername = "localhost";
$username = "root";
$password = "";
 
// 创建连接
$conn = mysqli_connect($servername, $username, $password);
 
// 检测连接
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}
echo "连接成功";

mysqli_close($conn);
?>

PDO连接

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
<?php
$servername = "localhost";
$username = "username";
$password = "password";
 
try {
    $conn = new PDO("mysql:host=$servername;", $username, $password);
    echo "连接成功"; 
}
catch(PDOException $e)
{
    echo $e->getMessage();
}

$conn = null;
?>

mysql语句

创建数据库

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
<?php
$servername = "localhost";
$username = "username";
$password = "password";
 
// 创建连接
$conn = mysqli_connect($servername, $username, $password);
// 检测连接
if (!$conn) {
    die("连接失败: " . mysqli_connect_error());
}
 
// 创建数据库
$sql = "CREATE DATABASE myDB";
if (mysqli_query($conn, $sql)) {
    echo "数据库创建成功";
} else {
    echo "Error creating database: " . mysqli_error($conn);
}
 
mysqli_close($conn);
?>

创建数据表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";
 
// 创建连接
$conn = mysqli_connect($servername, $username, $password, $dbname);
// 检测连接
if (!$conn) {
    die("连接失败: " . mysqli_connect_error());
}
 
// 使用 sql 创建数据表
$sql = "CREATE TABLE MyGuests (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY, 
firstname VARCHAR(30) NOT NULL,
lastname VARCHAR(30) NOT NULL,
email VARCHAR(50),
reg_date TIMESTAMP
)";
 
if (mysqli_query($conn, $sql)) {
    echo "数据表 MyGuests 创建成功";
} else {
    echo "创建数据表错误: " . mysqli_error($conn);
}
 
mysqli_close($conn);
?>

插入数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";
 
// 创建连接
$conn = mysqli_connect($servername, $username, $password, $dbname);
// 检测连接
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}
 
$sql = "INSERT INTO MyGuests (firstname, lastname, email)
VALUES ('John', 'Doe', 'john@example.com')";
 
if (mysqli_query($conn, $sql)) {
    echo "新记录插入成功";
} else {
    echo "Error: " . $sql . "<br>" . mysqli_error($conn);
}
 
mysqli_close($conn);
?>

插入多笔数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";
 
// 创建链接
$conn = mysqli_connect($servername, $username, $password, $dbname);
// 检查链接
if (!$conn) {
    die("连接失败: " . mysqli_connect_error());
}
 
$sql = "INSERT INTO MyGuests (firstname, lastname, email)
VALUES ('John', 'Doe', 'john@example.com');";
$sql .= "INSERT INTO MyGuests (firstname, lastname, email)
VALUES ('Mary', 'Moe', 'mary@example.com');";
$sql .= "INSERT INTO MyGuests (firstname, lastname, email)
VALUES ('Julie', 'Dooley', 'julie@example.com')";
 
if (mysqli_multi_query($conn, $sql)) {
    echo "新记录插入成功";
} else {
    echo "Error: " . $sql . "<br>" . mysqli_error($conn);
}
 
mysqli_close($conn);
?>

预处理语句

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";
 
// 创建连接
$conn = new mysqli($servername, $username, $password, $dbname);
 
// 检测连接
if ($conn->connect_error) {
    die("连接失败: " . $conn->connect_error);
}
 
// 预处理及绑定
$stmt = $conn->prepare("INSERT INTO MyGuests (firstname, lastname, email) VALUES (?, ?, ?)");
$stmt->bind_param("sss", $firstname, $lastname, $email);
 
// 设置参数并执行
$firstname = "John";
$lastname = "Doe";
$email = "john@example.com";
$stmt->execute();
 
$firstname = "Mary";
$lastname = "Moe";
$email = "mary@example.com";
$stmt->execute();
 
$firstname = "Julie";
$lastname = "Dooley";
$email = "julie@example.com";
$stmt->execute();
 
echo "新记录插入成功";
 
$stmt->close();
$conn->close();
?>

读取数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";
 
// 创建连接
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) {
    die("连接失败: " . mysqli_connect_error());
}
 
$sql = "SELECT id, firstname, lastname FROM MyGuests";
$result = mysqli_query($conn, $sql);
 
if (mysqli_num_rows($result) > 0) {
    // 输出数据
    while($row = mysqli_fetch_assoc($result)) {
        echo "id: " . $row["id"]. " - Name: " . $row["firstname"]. " " . $row["lastname"]. "<br>";
    }
} else {
    echo "0 结果";
}
 
mysqli_close($conn);
?>

update

1
2
3
4
5
6
7
8
9
10
11
12
13
<?php
$con=mysqli_connect("localhost","username","password","database");
// 检测连接
if (mysqli_connect_errno())
{
    echo "连接失败: " . mysqli_connect_error();
}

mysqli_query($con,"UPDATE Persons SET Age=36
WHERE FirstName='Peter' AND LastName='Griffin'");

mysqli_close($con);
?>

delete

1
2
3
4
5
6
7
8
9
10
11
12
<?php
$con=mysqli_connect("localhost","username","password","database");
// 检测连接
if (mysqli_connect_errno())
{
    echo "连接失败: " . mysqli_connect_error();
}

mysqli_query($con,"DELETE FROM Persons WHERE LastName='Griffin'");

mysqli_close($con);
?>

ODBC

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
<html>
<body>

<?php
$conn=odbc_connect('northwind','','');
if (!$conn)
{
    exit("连接失败: " . $conn);
}

$sql="SELECT * FROM customers";
$rs=odbc_exec($conn,$sql);

if (!$rs)
{
    exit("SQL 语句错误");
}
echo "<table><tr>";
echo "<th>Companyname</th>";
echo "<th>Contactname</th></tr>";

while (odbc_fetch_row($rs))
{
    $compname=odbc_result($rs,"CompanyName");
    $conname=odbc_result($rs,"ContactName");
    echo "<tr><td>$compname</td>";
    echo "<td>$conname</td></tr>";
}
odbc_close($conn);
echo "</table>";
?>

</body>
</html>
-->