MySQL数据库基础知识

最新推荐文章于 2022-02-18 11:05:44 发布

badw01f

最新推荐文章于 2022-02-18 11:05:44 发布

阅读量220

点赞数

分类专栏：数据库文章标签： MySQL 数据库

数据库专栏收录该内容

1 篇文章 0 订阅

订阅专栏

MySQL–关系型数据库

什么是数据库？

按照数据结构来组织、存储和管理数据的仓库

关系型数据库概念：

建立在关系模型基础上的数据库，借助集合代数等数学概念和方法来处理数据库中的数据

RDBMS的特点：

1.数据以表格形式出现

2.每行为各种记录名称

3.每列为记录名称所对应的数据域

4.许多行和列组成一张表单

5.若干的表单组成database

RDBMS术语：

冗余：存储两倍的数据，冗余降低了性能，提高了数据的安全性

主键：是唯一的。一个数据表中只能包含一个主键

外键：用于关联两个表

复合键：将多个列作为一个索引健，一般用于复合索引

索引：使用索引可以快速访问数据库表中的特定信息。索引是对数据库表中一列或者多列的值进行排序的一种结构。类似于书籍的目录

MySQL数据库

开源、支持大型数据库、使用标准SQL数据语言、支持多种编程语言、采用GPL协议

MyQL基本操作

初始化：mysql --initialize

启动MySQL：systemctl start mysqld

查看MySQL运行状态：systemctl status mysqld 　检查服务器是否启动：ps -ef | grep mysqld

验证MySQL安装：mysqladmin --version

注意：甲骨文公司收购MySQL后，有将MySQL闭源的潜在风险，因此社区采用分支的方式避开这个风险，所以有MariaDB出现，它是MySQL的一个分支，由开源社区维护。

进入MySQL：sudo mysql

显示数据库：SHOW DATABASES;

给指定数据库(mysql)添加用户：CREATE USER xxx@localhost IDENTIFIED BY 'password';

给添加的用户赋予权限：GRANT ALL PRIVILEGES ON *.* TO xxx@localhost WITH GRANT OPTION;
刷新：FLUSH PRIVILEGES;

管理MySQL的命令

列出RDBMS中的数据库列表 SHOW DATABASES;

选择数据库 USE mysql;

显示指定数据库mysql的所有表：SHOW TABLES;
显示指定表user中的所有列：SHOW COLUMNS FROM user;

显示数据库表user的详细索引信息，包括PRIMARY KEY（主键）：SHOW INDEX FROM user;

输出MySQL的性能及统计信息：SHOW TABLE STATUS FROM mysql;

使用PHP脚本连接数据库

PHP提供了mysqli_connect()函数来连接函数库

语法：mysqli_conncet(hostname, username, password, dbname, port, socket);

实例：

<?php 

$dbhost = 'localhost:3306';

$dbuser = 'root';

$dbpass = '123456';

$conn = mysqli_connect($dbhost, $dbuser, $dbpass);

if( ! $conn )

{

    die('Could not conncet: ' .mysql_error()) ;

}

echo '数据库连接成功！';

mysqli_close($conn);

?>

MySQL创建数据库

创建数据库test：CREATE DATABASE test;

使用mysqladmin创建数据库：mysqladmin -u root -p create test

使用PHP脚本创建数据库：mysqli_query(connection,query,resultmode);

实例:

<?php
$dbhost = 'localhost:3306';
$dbuser = 'root';
$dbpass = '123456';
$conn = mysqli_connect($dbhost,$dbuser,$dbpass);
if ( ! $conn )
{
    die('连接错误: ' . mysqli_error($conn));
}
echo '连接成功<br />';
$sql = 'CREATE DATABASE test';
$retval = mysqli_query($conn,$sql);
if ( ! $retval )
{
    die('创建数据库失败: ' . mysqli_error($conn));
}
echo "数据库test创建成功\n";
mysqli_close($conn);
?>

MySQL删除数据库

删除数据库test：drop database test;
使用mysqladmin删除数据库：mysqladmin -u root -p drop test

使用PHP脚本删除数据库：mysqli_query(connection,query,resultmode);

<?php
$dbhost = 'localhost:3306';
$dbuser = 'root';
$dbpass = '123456';
$conn = mysqli_query($dbhost,$dbuser,$dbpass);
if ( ! $conn )
{
    die('连接失败： ' . mysqli_error($conn));
}
echo '连接成功<br />';
$sql = 'DROP DATABASE test';
$retval = mysql_query($conn,$sql);
if ( ! $retval )
{
    die('删除数据库失败： ' . mysqli_error_($conn));
}
echo "数据库test删除成功!\n";
?>

选择数据库

选择数据库：use test;

使用PHP脚本选择数据库：myslqi_select_db(connection,dbname);

<?php
$dbhost = 'localhost:3306';
$dbuser = 'root';
$dbpass = '123456';
$conn = mysqli_connect($dbhost,$dbuser,$dbpass);
if ( ! $conn )
{
    die('连接失败：' . mysqli_error($conn));
}
echo '连接成功<br />';
mysqli_select_db($conn, 'test');
mysqli_close($conn);
?>

MySQL创建数据表

表名、表字段名、定义每个表字段

语法：CREATE TABLE table_name(column_name column_type);

CREATE TABLE IF NOT EXISTS 'test_table'(
    test_id INT UNSIGNED AUTO_INCREMENT；//定义列为自增属性，一般用于主键，自动１
    test_title VARCHAR(100) NOT NULL,
    test_author VARCHAR(40) NOT NULL,
    submission_date DATE,
    PRIMARY KEY('test_id')  //用于定义主键
    )ENGINE=InnoDB DEFAULT CHARSET=utf8;  //ENGINE设置存储引擎，CHARSET设置编码

使用PHP脚本创建数据表: mysqli_query(connection,query,resultmode);

<?php
$dbhost = 'localhost:3306';
$dbuser = 'root';
$dbpass = '123456';
$conn = mysqli_conncet($dbhost,$dbuser,$dbpass);
if ( ! $conn )
{
    die('连接失败: ' . mysqli_error($conn));
}
echo '连接成功！<br />';
$sql = "CREATE TABLE test(".
       "test1 INT NOT NULL AUTO_INCREMENT,".
       "testCreate VARCHAR(100) NOT NULL,".
       "test_author VARCHAR(40) NOT NULL,".
       "submission_date DATE,".
       "PRIMARY KEY(test1))ENGINE=InnoDB DEFAULT CHARSET=utf8;";
mysqli_select_db($conn, 'test');
$retval = mysqli_query($conn, $sql);
if ( ! $revtal )
{
    die('数据表创建失败: ' . mysqli_error($conn));
}
echo "数据表创建成功\n";
mysqli_close($conn);
?>

MySQL删除数据表

语法：DROP TABLE test;

使用PHP删除：mysqli_query(conncetion,query,resultmode);

<?php
$dbhost = 'localhost:3306';
$dbuser = 'root';
$dbpass = '123456';
$conn = mysqli_connect($dbhost,$dbuser,$dbpass);
if ( ! $conn )
{
    die('连接失败: ' . mysqli_error($conn));
}
echo '连接成功<br />';
$sql = "DROP TABLE test";
mysqli_select_db($conn, 'test');
$ retval = mysqli_query($conn, $sql);
if ( ! $retval )
{
    die('删除数据表失败: ' . mysqli_error($conn));
}
echo "删除数据表成功\n";
mysqli_close($conn);
?>

MySQL插入数据

语法: INSERT INTO test (key1, key2, ... keyN) Values (value1, value2, ...valueN);

使用PHP插入数据：mysqli_query(connection,query,resultmode);

<?php
$dbhost = 'localhost:3306';
$dbuser = 'root';
$dbpass = '123456';
$conn = mysqli_connect($dbhost,$dbuser,$dbpass);
if ( ! $conn )
{
    die('连接失败: ' . mysqli_error($conn));
}
mysqli_query($conn, "set names utf8");

$sql = 'SELECT test_id, test_title, test_author, submission_date FROM test';
mysql_select_db($conn,"test");
$retval = mysqli_query($conn,$sql);
if ( ! $retval )
{
    die('无法读取数据：' . mysqli_error($conn));
}
echo '<table border="1"><tr><td>教程 ID</td><td>标题</td><td>作者</td><td>日期</td></tr>';
while ($row = mysqli_fetch_array($retval, MYSQLI_ASSOC))
{
    echo "<tr><td>{$row['test_id']}</td>".
        "<td>{$row['test_title']}</td>".
        "<td>{$row['test_author']}</td>".
        "<td>{$row['submission_date']}</td>".
        "</tr>";
}
echo '</table>';
mysqli_close($conn);
?>

WHERE子句

BINARY关键字：SELECT * FROM test WHERE BINARY test_author='testMe';

BINARY关键字来设定WHERE子句的字符串比较是区分大小写的

UPDATE查询

UPDATE命令修改SQL数据表数据 UPDATE table_name SET test1=new-value1, test2=new-value2

DELETE删除

DELETE命令从数据表中删除数据

如果没有指定WHERE子句，则将表中所有数据删除

DELETE FROM test WHERE test_id = 1;

LIKE子句

使用LIKE来模糊匹配，会与%和_结合使用

SELECT * FROM test WHERE test_id LIKE 'test1%'

‘%a’	以a结尾的数据
‘a%’	以a开头的数据
‘%a%’	含有a的数据
‘_a_’	三位且a在中间
‘_a’	两位且a结尾
‘a_’	两位且a开头

UNION语句

UNION操作符用于连接两个以上的SELECT语句的结果组合到一个结果集合中

SELECT country FROM websites UNION SELECT country FROM apps ORDER BY country;

UNINON ALL 会选取所有值（含重复）

MySQL排序

使用ORDER BY子句将查询数据排序后再返回

SELECT * FROM test ORDER BY submission_date ASC;

GROUP BY语句

GROUP BY语句根据一个或多个列对结果集进行分组

SELECT name, COUNT(*) FROM empolyee_tbl GROUP BY name;

WITH ROLLUP可以实现分组统计数据基础上再进行相同的统计(SUM,AVG,COUNT….)

JOIN连接的使用

JOIN按功能分为三类：

INNER JOIN ：获取两个表中字段匹配关系的记录
LEFT JOIN ：获取左表所有记录，即使右表没有对应匹配的记录
RIGHT JOIN ：与2相反

MySQL 正则表达

模式	描述
^	匹配输入字符串的开始位置
$	匹配输入字符串的结束位置
.	匹配除”\n”之外的任何单个字符
[…]	字符合集，匹配所包含的任意一个字符
[^…]	负值字符集合，匹配未包含的任意字符
p1\|p2\|p3	匹配p1或p2或p3
*	匹配前面的子表达式零次或多次
+	匹配前面的子表达式一次或多次
{n}	n是一个非负整数，匹配确定的n次
{n,m}	m和n均为非负整数，其中n<=m。最少匹配n次最多匹配m次

MySQL事务

主要处理操作量大，复杂度高的数据

在MySQL中只有使用了Innodb数据库引擎的数据库或表才支持事务
事务处理可以用来维护数据库的完整性，保证成批的SQL语句要么全部执行，要么全不执行
事务用来管理INSERT,UPDATE,DELETE语句

事务必需满足4个条件（ACID）：

原子性 Atomicity（不可分割性）：要么全部完成，要么全部不完成
一致性 Consistency：开始与结束事务后，数据库的完整性没有被破坏
隔离性 Isolation （独立性）：允许多个并发事务同时对数据读写，隔离性防止交叉执行导致的数据不一致
持久性 Durability：对数据的修改是永久的，系统故障也不丢失

事务控制语句：

BEGIN或START TRANSACTION 显式地开启一个事务
COMMIT 提交事务，并使修改成为永久性
ROLLBACK 回滚会结束用户的事务，并撤销正在进行的所有未提交的修改
SAVEPOINT identifier 允许事务中创建一或多个保存点
RELEASE SAVEPOINT 删除一个事务的保存点，若无指定，则抛出异常
ROLLBACK TO identifier 事务回滚到标记点
SET TRANSACTION 用来设置事务的隔离级别

MySQL索引

索引大大提高检索速度

分类：

单列索引：只包含单个列，一个表可以有多个单列索引
组合索引：一个索引包含多个列

实际上，索引就是一张表，该表保存了主键与索引字段，并指向实体表的记录

缺点：降低更新表的速度，占用磁盘控件的索引文件

最基本的索引：CREATE INDEX indexName ON mytable(username(length));

删除索引：DROP INDE [indexName] ON mytable;

唯一索引：索引列的值必须唯一，但允许有空值。

使用ALTER命令添加和删除索引

ALTER TABLE tbl_name ADD PRIMARY KEY(column_list)：添加一个主键，意味着索引值必须唯一且不为空

ALTER TABLE tbl_name ADD UNIQUE index_name(column_list)：创建索引的值必须是唯一

ALTER TABLE tbl_name ADD INDEX index_name(column_list)：添加普通索引，索引值可以出现多次

ALTER TABLE tbl_name ADD FULLTEXT index_name(column_list)：指定索引为FULLTEXT，用于全文索引

使用ALTER命令添加和删除主键

主键只能作用于一个列上，添加主键索引时，要确保主键默认不为空

ALTER TABLE test_tbl MODIFY i INT NOT NULL;

ALTER TABLE test_tbl ADD PRIMARY KEY (i);

也可以用ALTER命令删除主键：

ALTER TABLE test_tbl DROP PRIMARY KEY;

MySQL临时表

在我们需要保存一些临时数据时非常有用，只在当前连接可见

CREATE TEMPORARY TABLE tem_tbl;

删除临时表：

DROP TABLE tem_tbl;

MySQL复制表

复制数据表包括结构、索引、默认值等，步骤：

1.用 SHOW CREATE TABLE获取创建数据表的语句，该语句包含了原数据表的结构、索引等

2.复制回显的语句，修改数据表名，并执行语句

另一种方法：

CREATE TABLE target_tbl LIKE source_tbl;

INSERT INTO target_tbl SELECT * FROM source_tbl;

MySQL处理重复数据

防止表中出现重复数据

设置指定字段为PRIMARY KEY 或者 UNIQUE索引来保证数据唯一性

INSERT IGNORE INTO test_tbl会忽略已经存在的数据

统计重复数据

SELECT COUNT(*) as repetitions, one_name, another_name FROM test_tbl GROUP BY one_name, another name HAVING repetitions > 1;

以上查询语句返回表中重复的记录数

过滤重复数据

DISTINCT关键字来过滤重复数据

SELECT DISTINCT one_name, another_name FROM test_tbl;

也可以用 GROUP BY来读取不重复数据：

SELECT one_name,another_name FROM test_tbl GROUP BY (one_name, another_name);

删除重复数据

CREATE TABLE tmp SELECT last_name, first_name, sex FROM test_tbl GROUP BY (last_name,firstname,sex);

或者在数据表中添加INDEX和PRIMARY KEY 来删除重复记录：

ALTER IGNORE TABLE test_tbl ADD PRIMARY KEY (last_name, first_name);

SQL导出数据

将数据表test_tbl导出到/tmp/test.txt文件中：

SELECT * FROM test_tbl INTO OUTFILE '/tmp/test.txt';

也可以导出CSV格式：

SELECT * FROM passwd INTO OUTFILE '/tmp/test.txt' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n';

mysqldump是mysql用于转存储数据库的是实用程序，实用mysqldump导出数据需要用–tab 选项指定导出文件指定的目录，该目标必须是可写的

mysqldump -u root -p --no-create-info --tab=/tmp TEST test_tbl;

导出SQL格式的数据：

mysqldump -u root -p TEST test_tbl > dump.txt

备份数据库：

mysqldump -u root -p --all-databases > database_dump.txt

将主机数据库拷贝到本地：

mysqldump -h other-host.com -P port -u root -p database_name > dump.txt

MySQL导入数据

1.mysql命令导入：mysql -u username -p < test.sql
2.source命令导入：

CREATE DATABASE abc;
USE abc;
SET names utf8;
source /home/abc/abc.sql

3.使用LOAD DATA导入：

LOAD DATA LOCAL INFILE 'dump.txt' INTO TABLE test_tbl;

4.使用mysqlimport导入数据：

mysqlimport -u root -p --local database_name dump.txt

badw01f

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL数据库基础知识

MySQL–关系型数据库什么是数据库？按照数据结构来组织、存储和管理数据的仓库关系型数据库概念：建立在关系模型基础上的数据库，借助集合代数等数学概念和方法来处理数据库中的数据RDBMS的特点：1.数据以表格形式出现2.每行为各种记录名称3.每列为记录名称所对应的数据域4.许多行和列组成一张表单5.若干的表单组成databaseRDBMS术语：冗余：...
复制链接

扫一扫

专栏目录