MySQL——数据库基础操作（实战篇）

案例：建立学生成绩表

一，编译安装MySQL

1，安装环境组件

[root@localhost ~]# yum install -y \     ##安装环境组件> ncurses \> ncurese-devel \    ##控制终端屏幕显示的库> cmake \                ##cmake工具> bison                    ##语法分析[root@localhost ~]# useradd -s /sbin/nologin mysql     ##创建程序用户

2，解压源码包到/opt下

[root@localhost ~]# cd /mnt[root@localhost mnt]# tar zxvf mysql-boost-5.7.20.tar.gz -C /opt   ##解压源码包到/opt[root@localhost mnt]# cd /opt[root@localhost opt]# lsmysql-5.7.20  nginx-1.12.2  rh

3，cmake配置

[root@localhost opt]# cd mysql-5.7.20/       ##切换到MySQL目录下[root@localhost mysql-5.7.20]# cmake \     ##cmake配置-DCMAKE_INSTALL_PREFIX=/usr/localmysql \            ##安装路径-DMYSQL_UNIX_ADDR=/usr/local/mysql/mysql.sock \ ##定义sock文件连接数据库文件-DSYSCONFDIR=/etc \                                                   ##配置文件目录-DSYSTEMD_PID_DIR=/usr/local/mysql \                      ##PID文件目录-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=boost \                                               ##底层运行库-DWITH_SYSTEMD=1                                                   ##主从参数》

4，编译及安装

[root@localhost mysql-5.7.20]# make          ##编译[root@localhost mysql-5.7.20]# make install    ##安装

5，配置mysql，调整配置文件

[root@localhost mysql-5.7.20]# chown -R mysql:mysql /usr/local/mysql/   ##数据库目录进行权限调整[root@localhost mysql-5.7.20]# vim /etc/my.cnf     ##调整配置文件[client]                            ##客户端port = 3306default-character-set=utf8socket = /usr/local/mysql/mysql.sock[mysql]                           ##客户端     port = 3306default-character-set=utf8socket = /usr/local/mysql/mysql.sock[mysqld]                         ##服务器        user = mysql                  ##用户basedir = /usr/local/mysql      ##设置mysql的安装目录datadir = /usr/local/mysql/data    ##设置mysql数据库的数据的存放目录port = 3306                    ##设置3306端口character_set_server=utf8            ##中文字符集pid-file = /usr/local/mysql/mysqld.pid     ##pid文件路径socket = /usr/local/mysql/mysql.sock     ##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##支持模块>[root@localhost mysql-5.7.20]# chown mysql:mysql /etc/my.cnf    ##给配置文件mysql属主属组[root@localhost mysql-5.7.20]# echo 'PATH=/usr/local/mysql/bin:/usr/local/mysql/lib:$PATH' >> /etc/profile##将MySQL写到本地环境配置中[root@localhost mysql-5.7.20]# echo 'export PATH' >> /etc/profile    ##设置全局环境配置[root@localhost mysql-5.7.20]# source /etc/profile    ##重启配置文件

6，初始化数据库

[root@localhost mysql-5.7.20]# cd /usr/local/mysql/[root@localhost mysql]# bin/mysqld \> --initialize-insecure \        ##初始化> --user=mysql \                 ##用户> --basedir=/usr/local/mysql \      ##安装目录> --datadir=/usr/local/mysql/data   ##数据库数据文件目录

7，将MySQL服务配置文件复制到/usr/lib/systemd/system/下便于使用systemctl管理

[root@localhost mysql]# cp usr/lib/systemd/system/mysqld.service /usr/lib/systemd/system/ ##便于systemctl管理[root@localhost mysql]# systemctl enable mysqld   ##开机自启动[root@localhost mysql]# systemctl start mysqld.service     ##开启数据库[root@localhost mysql]# netstat -ntap | grep 3306              ##查看MySQL端口号开启情况tcp6  0  0 :::3306    :::*       LISTEN   59464/mysqld   

8，设置MySQL密码

[root@localhost mysql]# mysqladmin -u root -p passwordEnter password:               ##空格New password:                ##新密码Confirm new password:   ##确认密码

二，进入数据库创建

1，创建数据库及数据库中的表

mysql> grant all privileges on *.* to 'root'@'%' identified by '123123';   ##提权用户Query OK, 0 rows affected, 1 warning (0.00 sec)mysql> create database test;     ##创建数据库testQuery OK, 1 row affected (0.00 sec)mysql> show databases;   ##查看数据库+--------------------+| Database           |+--------------------+| information_schema || mysql              || performance_schema || sys                || test               |+--------------------+5 rows in set (0.01 sec)mysql> use test;     ##使用数据库testDatabase changedmysql> create table info(      ##创建表        -> id int(3) not null,        -> name varchar(4) not null,        -> score decimal default 0,        -> primary key (id));mysql> desc info;    ##查看表结构+-------+---------------+------+-----+---------+-------+| Field | Type          | Null | Key | Default | Extra |+-------+---------------+------+-----+---------+-------+| id    | int(3)        | NO   | PRI | NULL    |       || name  | varchar(4)    | NO   |     | NULL    |       || score | decimal(10,0) | YES  |     | 0       |       |+-------+---------------+------+-----+---------+-------+3 rows in set (0.01 sec)

2，插入数据到info表中

mysql> insert into info values (1,'周几轮',90);       ##往表中插入数据Query OK, 1 row affected (0.01 sec)mysql> insert into info values (2,'王峰',default);Query OK, 1 row affected (0.00 sec)mysql> insert into info values (3,'娜英',60);Query OK, 1 row affected (0.00 sec)mysql> select * from info;     ##查看表中的数据+----+-----------+-------+| id | name      | score |+----+-----------+-------+|  1 | 周几轮    |    90 ||  2 | 王峰      |     0 ||  3 | 娜英      |    60 |+----+-----------+-------+3 rows in set (0.00 sec)

3，修改表中的数据

mysql> update info set score=88 where id=2;    ##修改表中的数据Query OK, 1 row affected (0.00 sec)Rows matched: 1  Changed: 1  Warnings: 0mysql> select * from info;+----+-----------+-------+| id | name      | score |+----+-----------+-------+|  1 | 周几轮    |    90 ||  2 | 王峰      |    88 ||  3 | 娜英      |    60 |+----+-----------+-------+3 rows in set (0.00 sec)

三，使用Navicat连接数据库查看表

谢谢阅读！