数据库的概念：
数据库就是一个运行在系统上的软件，用于存储用户个人信息、用户的游戏资料等
数据库是多个表的集合，是存储数据的仓库，以一定的组织方式存储的相互有关的数据
数据库由多个表组成，多个数据记录组成一张表，类似Excel
数据库结构：由多个库组成，一个库包含多个表，一个表包含多条数据记录
数据库的分类：
关系数据库类型
Mysql：跨平台
Sql Server：Windows平台使用
Oracle：跨平台、安全可靠稳定
非关系数据库
Redis：将内存数据持久化保存到计算机硬盘上
Mongodb：支持关系和非关系数据库
实验图：
实验步骤：
一，数据库基础
1.安装数据库
1）挂载光盘
[root@Centos01 ~]# mount /dev/sr0 /mnt/
mount: /dev/sr0 写保护，将以只读方式挂载
2）安装依赖程序
[root@Centos01 ~]# yum -y install ncurses-devel cmake
3）创建管理mysql组
[root@centos01 ~]# groupadd mysql
4）创建管理mysql服务用户加入到mysql组
[root@centos01 ~]# useradd -M -s /sbin/nologin -g mysql mysql
5）解压源代码程序并进入源代码程序（切换到云计算光盘）云计算光盘（提取码1234）
[root@Centos01 ~]# umount /dev/sr0
[root@Centos01 ~]# mount /dev/sr0 /mnt/
mount: /dev/sr0 写保护，将以只读方式挂载
[root@Centos01 ~]# tar zxf /mnt/mysql-5.5.22.tar.gz -C /usr/src/
[root@Centos01 ~]# cd /usr/src/mysql-5.5.22/
6）配置mysql
[root@Centos01 mysql-5.5.22]# cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DSYSCONFDIR=/etc -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DWITH_EXTRA_CHARSETS=all
7）编译安装（时间有点长）
[root@Centos01 mysql-5.5.22]# make && make install
8）修改目录所有者
[root@Centos01 mysql-5.5.22]# chown -R mysql:mysql /usr/local/mysql/
9）生成mysql主配置文件
[root@Centos01 mysql-5.5.22]# cp support-files/my-medium.cnf /etc/my.cnf
cp：是否覆盖"/etc/my.cnf"？ y
10）生成服务控制文件添加执行权限
[root@Centos01 mysql-5.5.22]# cp support-files/mysql.server /etc/init.d/mysqld
[root@Centos01 mysql-5.5.22]# chmod +x /etc/init.d/mysqld
11）添加系统服务设置开机自启
[root@Centos01 mysql-5.5.22]# chkconfig --add mysqld
[root@Centos01 mysql-5.5.22]# chkconfig --level 35 mysqld on
12）优化mysql命令（敲入命令mysql补全下边内容即优化成功，优化失败需进入安全模式更改）
[root@Centos01 mysql-5.5.22]# echo "PATH=$PATH:/usr/local/mysql/bin" >> /etc/profile
[root@Centos01 mysql-5.5.22]# source /etc/profile
[root@Centos01 mysql-5.5.22]# mysql
mysql mysqld mysql_secure_installation
mysqlaccess mysqld_multi mysql_setpermission
mysqlaccess.conf mysqld_safe mysqlshow
mysqladmin mysqldump mysqlslap
mysqlbinlog mysqldumpslow mysqltest
mysqlbug mysql_find_rows mysql_tzinfo_to_sql
mysqlcheck mysql_fix_extensions mysql_upgrade
mysql_client_test mysqlhotcopy mysql_waitpid
mysql_config mysqlimport mysql_zap
mysql_convert_table_format mysql_plugin
13）初始化mysql
[root@Centos01 mysql-5.5.22]# /usr/local/mysql/scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data
Installing MySQL system tables...
OK
Filling help tables...
OK
2.启动mysql服务设置密码
1）启动mysql服务设置密码
[root@Centos01 mysql-5.5.22]# systemctl start mysqld
2）监听mysql运行端口
[root@Centos01 mysql-5.5.22]# netstat -anptu | grep mysqld
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 11922/mysqld
3）设置登陆密码
[root@Centos01 mysql-5.5.22]# mysqladmin -uroot password
4）登录mysql 账户root密码pwd@123
[root@Centos01 mysql-5.5.22]# mysql -uroot -ppwd@123
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.5.22-log Source distribution
Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
二，数据库管理
1.数据库的基本管理
1)查看数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.01 sec)
2）创建数据库名字magua
mysql> create database magua;
Query OK, 1 row affected (0.00 sec)
3）切换magua数据库
mysql> use magua
Database changed
4）删除magua数据库
mysql> drop database magua;
Query OK, 0 rows affected (0.00 sec)
2.表结构管理
1）创建表设置表结构，在magua数据创建student表
mysql> create database magua;
Query OK, 1 row affected (0.00 sec)
mysql> create table magua.student (姓名 char(4),性别 char(2),年龄 int,身份证号码 char(18),primary key( 身份证号码));
Query OK, 0 rows affected (0.00 sec)
2）插看表结构
mysql> desc magua.student;
+-----------------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+----------+------+-----+---------+-------+
| 姓名 | char(4) | YES | | NULL | |
| 性别 | char(2) | YES | | NULL | |
| 年龄 | int(11) | YES | | NULL | |
| 身份证号码 | char(18) | NO | PRI | | |
+-----------------+----------+------+-----+---------+-------+
4 rows in set (0.00 sec)
3）切换到magua数据库查看表
mysql> use magua;
mysql> show tables;
+-----------------+
| Tables_in_magua |
+-----------------+
| student |
+-----------------+
1 row in set (0.00 sec)
4）删除表student
mysql> drop table magua.student
-> ;
Query OK, 0 rows affected (0.00 sec)
3.表中记录管理
1）表插入连续列数据
mysql> create table magua.student (姓名 char(4),性别 char(2),年龄 int,身份证号码 char(18),primary key( 身份证号码));
Query OK, 0 rows affected (0.00 sec).
mysql> insert into magua.student values ('magu','男',15,'111111111111111111');
Query OK, 1 row affected, 1 warning (0.00 sec)
2）插入不连续列数据
mysql> insert into magua.student (姓名,年龄,身份证号码) values ('chon',17,'222222222222222222');
Query OK, 1 row affected, 1 warning (0.00 sec)3）查看表中所有记录
mysql> select * from magua.student;
+--------+--------+--------+--------------------+
| 姓名 | 性别 | 年龄 | 身份证号码 |
+--------+--------+--------+--------------------+
| magu | 男 | 15 | 111111111111111111 |
| chon | NULL | 17 | 222222222222222222 |
+--------+--------+--------+--------------------+
2 rows in set (0.00 sec)
4）匹配姓名为magu的记录
mysql> select * from student where 姓名='magu'
-> ;
+--------+--------+--------+--------------------+
| 姓名 | 性别 | 年龄 | 身份证号码 |
+--------+--------+--------+--------------------+
| magu | 男 | 15 | 111111111111111111 |
+--------+--------+--------+--------------------+
1 row in set (0.00 sec)
5）只查询姓名和身份证号码列
mysql> select 姓名,身份证号码 from student;
+--------+--------------------+
| 姓名 | 身份证号码 |
+--------+--------------------+
| magu | 111111111111111111 |
| chon | 222222222222222222 |
+--------+--------------------+
2 rows in set (0.00 sec)
4.修改和删除表中记录
1）修改表中激记录姓名为chon 修改性别为女 年龄为20
mysql> update student set 性别='女',年龄='20' where 姓名='chon';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from student where 姓名='chon';
+--------+--------+--------+--------------------+
| 姓名 | 性别 | 年龄 | 身份证号码 |
+--------+--------+--------+--------------------+
| chon | 女 | 20 | 222222222222222222 |
+--------+--------+--------+--------------------+
1 row in set (0.00 sec)
2）删除姓名是chon的i记录
mysql> delete from student where 姓名='chon';
Query OK, 1 row affected (0.00 sec)
mysql> select * from magua.student;
+--------+--------+--------+-------------------+
| 姓名 | 性别 | 年龄 | 身份证号码 |
+--------+--------+--------+--------------------+
| magu | 男 | 15 | 111111111111111111 |
+--------+--------+--------+--------------------+
1 row in set (0.00 sec)
3）清空表中记录
mysql> delete from student;
Query OK, 1 row affected (0.00 sec)
mysql> select * from student
-> ;
Empty set (0.00 sec)
三，数据库授权
1.授权用户访问MySQL数据库
1）授权magua通过192.168.100.20访问mysql数据拥有完全控制权限
mysql> grant all on *.* to 'magua'@'192.168.100.20'identified by 'pwd@123';
Query OK, 0 rows affected (0.00 sec)
2)查看授权
mysql> show grants for [email protected];
+----------------------------------------------------------------------------------------------------------------------------+
| Grants for [email protected] |
+----------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'magua'@'192.168.100.20' IDENTIFIED BY PASSWORD '*760F60073FD235571A5260444301DB22136ED604' |
+----------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
2.另一台Centos安装mysql客户端访问
1）安装mariadb
[root@centos02 ~]# mount /dev/sr0 /mnt/
mount: /dev/sr0 写保护，将以只读方式挂载
[root@centos02 ~]# yum -y install mariadb
2）客户端远程登陆myql服务器使用magua用户验证
[root@centos02 ~]# mysql -umagua -ppwd@123 -h 192.168.100.10
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.5.22-log Source distribution
Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.
3）验证
MySQL [(none)]> use magua
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MySQL [magua]> use magua
Database changed
MySQL [magua]>
3.授权windows客户端对数据库表有查询权限
1）授权查询权限
mysql> grant select on magua.* to 'magua'@'192.168.100.30'identified by 'pwd@123';
Query OK, 0 rows affected (0.00 sec)
2）查看授权的权限
mysql> show grants for [email protected];
+-------------------------------------------------------------------------------------------------------------------+
| Grants for [email protected] |
+-------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'magua'@'192.168.100.30' IDENTIFIED BY PASSWORD '*760F60073FD235571A5260444301DB22136ED604' |
| GRANT SELECT ON `magua`.* TO 'magua'@'192.168.100.30' |
+-------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
3）windows进行验证
