本文详细介绍MySQL数据库的基础知识，包括数据库概念、特点、SQL语句分类，以及数据库的创建、连接方法。此外，还提供了MySQL常用命令的实例，如创建、删除数据库和表，数据的增删改查，用户权限管理等。
数据库MySQL基础入门及表索引
一、MySQL认识
【1】数据库定义
（1）广义定义
数据库(Database)是按照 数据结构来组织、 存储和管理数据的建立在计算机存储设备上的仓库。简单来说是本身可视为 电子化的 文件柜——存储电子 文件的处所，用户可以对文件中的数据进行新增、截取、更新、删除等操作。
（2）狭义定义
严格来说，数据库是长期储存在计算机内、有组织的、可共享的数据集合。数据库中的数据指的是以一定的数据模型组织、描述和储存在一起、具有尽可能小的 冗余度、较高的数据独立性和易扩展性的特点并可在一定范围内为多个用户共享。这种数据集合具有如下特点：尽可能不重复，以最优方式为某个特定组织的多种应用服务，其数据结构独立于使用它的应用程序，对数据的增、删、改、查由统一软件进行管理和控制。从发展的历史看，数据库是数据管理的高级阶段，它是由文件管理系统发展起来的。
【2】MySQL数据库定义
MySQL是一个关系型数据库管理系统，原本是一个开放源码的关系数据库管理系统，原开发者为瑞典的MySQL AB公司，该公司于2008年被昇阳微系统（Sun Microsystems）收购。2009年，甲骨文公司（Oracle）收购昇阳微系统公司，MySQL成为Oracle旗下产品。MySQL在过去由于性能高、成本低、可靠性好，已经成为最流行的开源数据库，因此被广泛地应用在Internet上的中小型网站中。随着MySQL的不断成熟，它也逐渐用于更多大规模网站和应用。非常流行的开源软件组合LAMP或者LNMP中的“M”指的就是MySQL。
【3】数据库特点
（1）实现数据共享
数据共享包含所有用户可同时存取数据库中的数据，也包括用户可以用各种方式通过接口使用数据，并提供数据享。
（2）减少数据的冗余度
同文件系统相比，由于数据库实现了数据共享，从而避免了用户各自建立应用文件。减少了大量重复数据，减少了数据冗余，维护了数据的一致性。
（3）数据的独立性
数据的独立性包括逻辑独立性（数据库中数据库的 逻辑结构和 应用程序相互独立）和物理独立性（数据物理结构的变化不影响数据的逻辑结构）。
（4）数据实现集中控制
文件管理方式中，数据处于一种分散的状态，不同的用户或同一用户在不同处理中其文件之间毫无关系。利用数据库可对数据进行集中控制和管理，并通过 数据模型表示各种数据的组织以及数据间的联系。
（5）数据一致性 和可维护性，以确保数据的安全性和可靠性
主要包括：①安全性控制：以防止数据丢失、错误更新和越权使用；② 完整性控制：保证数据的正确性、有效性和相容性；③ 并发控制：使在同一时间 周期内，允许对数据实现多路存取，又能防止用户之间的不正常交互作用。
（6）故障恢复
由 数据库管理系统提供一套方法，可及时发现故障和修复故障，从而防止数据被破坏。 数据库系统能尽快恢复数据库系统运行时出现的故障，可能是物理上或是逻辑上的错误。比如对系统的误操作造成的数据错误等。
【4】数据库SQL语句的分类
DDL: 数据定义语言—create(创建数据库,表)、drop(删除数据库,表)
DML: 数据操纵语言—insert(添加数据)、update(更新数据)、delete(删除数据,可单独删除一条数据)
DQL: 数据查询语言—select(查看)
DCL: 数据控制语言—grant(加权)、revoke(撤权)
二、数据库的创建及连接
【1】MySQL5.7数据库的创建
//安装编译依赖环境
yum install -y gcc gcc-c++ ncurses-devel ncurses bison cmake
//创建数据库管理用户
useradd -s /sbin/nologin mysql
//上传数据库压缩包
cd /opt
tar zxvf mysql-boost-5.7.20.tar.gz
tar zxvf boot_1_59_0.tar.gz
//部署数据库boost存储路径
cp -r /opt/boost_1_59_0 /usr/local/
cd /usr/local
mv boost_1_59_0/ boost
//编译安装
cd mysql-5.7.20/
cmake \
-DCMAKE_INSTALL_PREFIX=/usr/local/mysql \
-DMYSQL_UNIX_ADDR=/usr/local/mysql/mysql.sock \
-DSYSCONFDIR=/etc \
-DSYSTEMD_PID_DIR=/usr/local/mysql \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_ARCHIVE_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITH_PERFSCHEMA_STORAGE_ENGINE=1 \
-DMYSQL_DATADIR=/usr/local/mysql/data \
-DWITH_BOOST=/usr/local/boost \
-DWITH_SYSTEMD=1
make && make install
//修改数据库文件属主属组
chown -R mysql:mysql /usr/local/mysql/
//修改数据库配置文件（删除所有，复制下面）
vim /etc/my.cnf
[client]
port=3306
default-character-set=utf8
socket=/usr/local/mysql/mysql.sock
[mysql]
port=3306
default-character-set=utf8
socket=/usr/local/mysql/mysql.sock
[mysqld]
user=mysql
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
port=3306
character_set_server=utf8
pid-file=/usr/local/mysql/mysql.pid
socket=/usr/local/mysql/mysql.sock
server-id=1
#下面内容请一行写入配置文件
sql_mode=NO_ENGINE_SUBSTITUTION,
STRICT_TRANS_TABLES,
NO_AUTO_CREATE_USER,
NO_AUTO_VALUE_ON_ZERO,
NO_ZERO_IN_DATE,
NO_ZERO_DATE,
ERROR_FOR_DIVISION_BY_ZERO,
PIPES_AS_CONCAT,
ANSI_QUOTES
//设置数据库全局环境变量
echo "PATH=/usr/local/mysql/bin:/usr/local/mysql/lib:$PATH" >> /etc/profile
echo "export PATH" >> /etc/profile
source /etc/profile
———————————————初始化数据库————————
/usr/local/mysql/bin/mysqld \
--initialize-insecure \
--user=mysql \
--basedir=/usr/local/mysql \
--datadir=/usr/local/mysql/data
——————————————————————————————
//优化数据库启动脚本
cp /usr/local/mysql/usr/lib/systemd/system/mysqld.service /lib/systemd/system/
systemctl start mysqld.service
netstat -natp |grep 3306
//创建数据库登录用户及密码
mysqladmin -u root -p password
Enter password: 空
New password: 123123
Confirm new password:123123
//连接数据库
mysql -uroot -p
password：123123
【2】MySQL5.6数据库的创建
//安装依赖环境
yum install -y ncurses-devel gcc-c++ perl-Module-Install
//安装gmake编译软件
tar zxvf cmake-2.8.6.tar.gz
cd cmake-2.8.6
./configure
gmake && gmake install
//安装MySQL数据库
tar zxvf mysql-5.6.36.tar.gz
cd mysql-5.6.36
cmake \
-DCMAKE_INSTALL_PREFIX=/usr/local/mysql \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
-DEXTRA_CHARSETS=all \
-DSYSCONFIDIR=/etc
make && make install
//优化数据库配置文件和开机自启动
cp support-files/my-default.cnf /etc/my.cnf
cp support-files/mysql.server /etc/rc.d/init.d/mysqld
chmod +x /etc/rc.d/init.d/mysqld
chkconfig --add mysqld
//设置数据库软件全局变量
echo "PATH=$PATH:/usr/local/mysql/bin" >> /etc/profile
source /etc/profile
//创建数据库管理用户
groupadd mysql
useradd -M -s /sbin/nologin mysql -g mysql
//修改数据库文件属主属组
chown -R mysql.mysql /usr/local/mysql
mkdir -p /data/mysql
//初始化数据库
/usr/local/mysql/scripts/mysql_install_db \
--user=mysql \
--datadir=/usr/local/mysql/data \
--basedir=/usr/local/mysql
//优化数据库命令脚本文件
ln -s /usr/local/mysql/bin/mysql /usr/sbin
ln -s /usr/local/mysql/bin/mysqlbinlog /usr/sbin
/usr/local/mysql/bin/mysqld_safe --user=mysql & //启动mysql服务
//连接数据库
mysql -uroot -p
password：空
【3】远程堡垒机连接数据库
注意防火墙策略要允许通过端口3306或者mysql服务
mysql -u root -h 192.168.60.60 -P 3306 -p
password：
【4】数据库修改登录密码
[root@localhost ~]# vim /etc/my.cnf
[mysqld]
......
skip-grant-tables //跳过密码认证
[root@localhost ~]#systemctl restart mysqld
mysql -uroot -p //进入mysql数据库
mysql> use mysql;
mysql> update mysql.user set
authentication_string=password('123456') where user='tom';
//修改好之后记得将之前在配置文件添加的代码删除
[root@localhost ~]# vim /etc/my.cnf
[mysqld]
......
#skip-grant-tables
[root@localhost ~]#systemctl restart mysqld
三、MySQL基础命令
【1】查看数据库信息
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
【2】查看表信息
mysql> use mysql;
Database changed
mysql> show tables;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| engine_cost |
| event |
| time_zone_transition_type |
| user |
+---------------------------+
31 rows in set (0.00 sec)
【3】查看表的结构
mysql> describe user;
【4】查看表的数据信息
mysql> select * from user;
【5】创建数据库
mysql> create database school;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| school |
| sys |
+--------------------+
5 rows in set (0.00 sec)
【6】创建表
格式：create table 表名（字段1 字段类型 字段约束）存储引擎，字符集
字段类型：整数型int（）；浮点型double（双精度8字节）、float（单精度4字节）；decimal（5,2）有效数字5位，小数2位；字符char；字符串char（固定长度）、varchar（可变长度）。
字段约束：非空not null；默认default ‘’；主键primary key；自增auto increment。
存储引擎：myisam；innodb。
字符集：utf8。
mysql> create table student (
id int(9) primary key not null,
name char(10) not null,
socre decimal(5,2) default '0');
Query OK, 0 rows affected (0.01 sec)
mysql> desc student;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id | int(9) | NO | PRI | NULL | |
| name | char(10) | NO | | NULL | |
| socre | decimal(5,2) | YES | | 0.00 | |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
【7】删除数据库和表
删除表
mysql> drop table comany.boss;
Query OK, 0 rows affected (0.01 sec)
删除库
mysql> drop database comany;
Query OK, 0 rows affected (0.00 sec)
【8】往表中插入数据
方法一（在构建表结构时候加入数据）
mysql> insert into student(id,name,socre)values(170420101,'zhangqing',91);
方法二（在已有表中加入数据）
mysql> insert into student values(170420102,'yangwu',88);
方法三（在创建表的时候加入数据）
mysql> create table student (
id int(9) primary key not null,
name char(10) not null,
socre decimal(5,2) default '0'
)values(170420103,'zhangsan',60);
查看添加信息
mysql> select * from student;
+-----------+-----------+-------+
| id | name | socre |
+-----------+-----------+-------+
| 170420101 | zhangqing | 91.00 |
| 170420102 | yangwu | 88.00 |
+-----------+-----------+-------+
2 rows in set (0.00 sec)
【9】提取数据库信息到另一个数据库
mysql> create table teacher as select * from student where name='zhangqing';
Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| student |
| teacher |
+------------------+
2 rows in set (0.00 sec)
mysql> select * from teacher;
+-----------+-----------+-------+
| id | name | socre |
+-----------+-----------+-------+
| 170420101 | zhangqing | 91.00 |
+-----------+-----------+-------+
1 row in set (0.00 sec)
【10】更新数据库表中的数据
mysql> update teacher set socre=100 where id=170420101;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from teacher;
+-----------+-----------+--------+
| id | name | socre |
+-----------+-----------+--------+
| 170420101 | zhangqing | 100.00 |
+-----------+-----------+--------+
1 row in set (0.00 sec)
【11】删除表中的数据
方法一（可以清空，可以逐个删除）
mysql> delete from student where name='zhangqing';
Query OK, 1 row affected (0.00 sec)
mysql> select * from student;
+-----------+--------+-------+
| id | name | socre |
+-----------+--------+-------+
| 170420102 | yangwu | 88.00 |
+-----------+--------+-------+
1 row in set (0.00 sec)
mysql> delete from school.teacher;
Query OK, 1 row affected (0.00 sec)
mysql> select * from school.teacher;
Empty set (0.00 sec)
mysql> desc school.teacher;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id | int(9) | NO | | NULL | |
| name | char(10) | NO | | NULL | |
| socre | decimal(5,2) | YES | | 0.00 | |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
方法二（清空表数据）
mysql> truncate table student;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from school.student;
Empty set (0.00 sec)
mysql> desc school.student;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id | int(9) | NO | PRI | NULL | |
| name | char(10) | NO | | NULL | |
| socre | decimal(5,2) | YES | | 0.00 | |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
【12】创建临时表
用于存放临时的数据，不会长时间的存在，断开连接就会被删除，数据存放在内存之中，不在磁盘。
mysql> create temporary table tmp(
id int (5) not null primary key auto_increment,
name char(10) not null,
socre double(5) default '0'
)engine=innodb default charset=utf8;
mysql> desc tmp;
+-------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+----------------+
| id | int(5) | NO | PRI | NULL | auto_increment |
| name | char(10) | NO | | NULL | |
| socre | decimal(5,2) | YES | | 0.00 | |
+-------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
【13】克隆表
方法一（分部克隆）
首先克隆表结构
mysql> create table test like student;
Query OK, 0 rows affected (0.01 sec)
mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| student |
| teacher |
| test |
+------------------+
然后克隆表中的数据
mysql> insert into test select * from student;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select * from test;
Empty set (0.00 sec)
方法二（一次性克隆）
mysql> create table test1 as select * from teacher;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| student |
| teacher |
| test |
| test1 |
+------------------+
4 rows in set (0.00 sec)
mysql> desc test1;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id | int(9) | NO | | NULL | |
| name | char(10) | NO | | NULL | |
| socre | decimal(5,2) | YES | | 0.00 | |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> select * from test1;
Empty set (0.00 sec)
【14】数据库用户授权（如果数据库用户不存在则先创建用户再加权限）
格式：grant 权限 on 数据库名.表名 to 用户名@来源地址 [identified by '密码'];
权限：查询select；增加insert；更新update；删除数据delete；执行execute；all所有。
数据库名和表名可以使用*表示所有库或者表。
mysql> grant select on *.* to 'tom'@'localhost' identified by 'admin123';
Query OK, 0 rows affected, 1 warning (0.00 sec)
【15】撤销用户授权（撤销不会删除用户）
格式：revoke 权限 on 数据库名.表名 from 用户名@来源地址;
权限：查询select；增加insert；更新update；删除数据delete；执行execute；all所有。
//查询权限
mysql> show grants;
+---------------------------------------------------------------------+
| Grants for root@localhost |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)
//撤销权限
mysql> revoke select on school.test from 'tom'@'localhost';
Query OK, 0 rows affected (0.00 sec)
【16】新建表并提取另一表中的字段到新表中（相当于给某表增加字段）
方法一：建新表，粘贴其他表的字段
mysql> create table student1(
id int(5) not null primary key auto_increment
) as select name,socre from student;
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| student |
| student1 |
| teacher |
+------------------+
3 rows in set (0.00 sec)
mysql> desc student1;
+-------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+----------------+
| id | int(5) | NO | PRI | NULL | auto_increment |
| name | char(10) | NO | | NULL | |
| socre | decimal(5,2) | YES | | 0.00 | |
+-------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql> select * from student1;
+----+----------+-------+
| id | name | socre |
+----+----------+-------+
| 1 | lisi | 99.00 |
| 2 | zhangsan | 88.00 |
| 3 | tianqi | 77.00 |
| 4 | qianba | 78.00 |
+----+----------+-------+
4 rows in set (0.00 sec)
方法二：在旧表的基础上增加字段
mysql> alter table class add teacher char;（默认char字符是1）
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
//修改字段类型
mysql> alter table class change teacher teacher char(10);
Query OK, 3 rows affected (0.13 sec)
Records: 3 Duplicates: 0 Warnings: 0
方法三：增加字段的时候可以添加约束
mysql> alter table student1 add column course int(5) not null;
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc student1;
+--------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+----------------+
| id | int(5) | NO | PRI | NULL | auto_increment |
| name | char(10) | NO | | NULL | |
| socre | decimal(5,2) | YES | | 0.00 | |
| clas | char(50) | NO | | NULL | |
| course | int(5) | NO | | NULL | |
+--------+--------------+------+-----+---------+----------------+
5 rows in set (0.01 sec)
【17】删除表中的某一个字段
mysql> alter table student1 drop clas;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select * from student1;
+----+----------+-------+--------+
| id | name | socre | course |
+----+----------+-------+--------+
| 1 | lisi | 99.00 | 0 |
| 2 | zhangsan | 88.00 | 0 |
| 3 | tianqi | 77.00 | 0 |
| 4 | qianba | 78.00 | 0 |
+----+----------+-------+--------+
4 rows in set (0.00 sec)
【18】修改表的名称
mysql> alter table class rename to course;
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| course |
| student1 |
+------------------+
2 rows in set (0.00 sec)
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
