MySQL Database basics

MySQL Database basic Skills goals

• Understand the basic concepts of a database
• Mastering MySQL's compilation installation
• Will operate the MySQL database

About MySQL Database

• In the 21st century, humans bought the "era of information explosion", a large number of data, information constantly generated, it is how to safely and effectively store, check and manage them. Effective storage of data, university access, convenient sharing and security control has become an urgent problem to be solved in the information age.

The ability to store data efficiently and coherently using a database is a quicker and more convenient way to manage data. The database has the following characteristics:

• Can be structured to store a large amount of data information, convenient for users to effectively search and access
• Can effectively maintain the consistency of data information, integrity, reduce data redundancy
• To meet the sharing and security needs of your app

1: Manually compile and install MySQL database

• Install language development Packages

[[email protected] ~] yum install gcc gcc-c++ cmake ncurses ncurses-devel bison -y

• Create a MySQL Admin user

[[email protected] mysql-5.7.17] useradd -s /sbin/nologin  mysql

• Unzip mysql-5.7.17 version of package boost rename

[[email protected] ~] tar zxvf mysql-5.7.17.tar.gz -C /opt/[[email protected] ~] tar zxvf boost_1_59_0.tar.gz -C /usr/local/ #boost包是C语言里面的函数库

• The Boost_1_59_0 bag that/usr/local unzipped

[[email protected] ~] mv /usr/local/boost_1_59_0 /usr/local/boost

• Enter MYSQL-5.7.17 installation package compilation

  [[[email protected] mysql-5.7.17] cmake \ >-dcmake_install_prefix=/usr/local/mysql \                               #指定安装路径 >-dmysql_unix_addr=/usr/local/mysql/mysql.sock \ #通讯连接文件 >-DSYSCONFDIR=/ETC \                         #MySQL配置文件目录 >-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=/usr/local/boost \ #支持函授库 >-dwith_systemd=1 #生成一个便 Files managed by Systemctl [[email protected] mysql-5.7.17] make && make install #这个过程比较长需耐心等待  

• Change the number and array of working directories

[[email protected] mysql-5.7.17] chown -R mysql.mysql /usr/local/mysql/

• Modify the MySQL configuration file

[[email protected] mysql-5.7.17] vim /etc/my/cnf#在开头插入[client ]                               #客户端区域port = 3306                             #端口号3306default-character-set=utf8              #字符集socket = /usr/local/mysql/mysql.sock [mysql]port = 3306default-character-set=utf8socket = /usr/local/mysql/mysql.sock    #连接文件[mysqld]user = mysql                            #管理用户是mysqlbasedir = /usr/local/mysql              #工作目录位置datadir = /usr/local/mysql/data         #数据库文件目录port = 3306character_set_server=utf8pid-file = /usr/local/mysql/mysqld.pid  #进程号socket = /usr/local/mysql/mysql.sock    #server-id = 1                           #sererID#删除原有文件的mysqld及下列九行添加上面的mysqldsql_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  #添加sql模式支持各种模式

• View changes under/etc My.cnf of the owner and the group

[[email protected] ~] ls -l | grep /etc/my.cnf-rw-r--r--.  1 root root   728 6月      25 15:43 my.cnf[[email protected] ~] chown mysql:mysql /etc/my.cnf-rw-r--r--.  1 mysql mysql   728 6月      25 15:43 my.cnf

• Declaration Change environment variable append

  [[email protected] ~] echo ‘PATH=/usr/local/mysql/bin:/usr/local/mysql/lib:$PATH‘ >> /etc/profile[[email protected] ~] echo ‘export PATH‘ >> /etc/profile[[email protected] ~] source /etc/profile

• Initializing the database

[[email protected] ~] cd /usr/local/mysql/[[email protected] mysql] bin/mysqld --initialize-insecure \                 #生成一个初始化密码这个初始化密码是空的等会我会修改密码         --user=mysql \                          #指定管理用户啊--basedir=/usr/local/mysql --datadir=/usr/local/mysql/data

• In order to facilitate systemctl management of the files put into systenctl inside

[[email protected] ~] cd/usr/lib/systemd/system[[email protected] system] Lsmysqld.service #这是便于系统管理的摸板 [ Email protected][[email protected] system] CP mysqld.service/usr/lib/systemd/system/# Copied to this directory [[email  protected] ~] systemctl daemon-reload #刷新让系统识别 [[email protected] ~] systemctl start Mysqld #开启数据库 [[E Mail protected] ~] NETSTAT-ANPT | grep 3306 #查看端口号tcp6 0 0::: 3306:::* LISTEN 77165/mysqld [[EMAIL&NB Sp;protected] ~] systemctl stop Firewalld.service #关闭防火墙 [[email protected] ~] Setenforce 0[[email protected] ~] mysqladmin-u root-p password "abc123" #修改初始化密码 [[email protected] ~] mysql-u root-penter password:welcome to T  He MySQL Monitor. Commands End With; or \g.your MySQL connection ID is 9Server version:5.7.17 Source distributioncopyright (c) $, Oracle and/or its a Ffiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names trademarks of their respectiveowners. Type ' help ', ' or ' \h ' for help.       Type ' \c ' to clear the current input statement.mysql> show databases; #查看数据库能显示下列数据库信息就已经安装完成 +-----------------------------+| Database |+-----------------------------+| Information_schema | | MySQL | | Performance_schema | | SYS |+-----------------------------+6 rows in Set (0.00 sec)

Simple Operations Database 1: What is a database database is a record describing a thing symbol is called Data 2: Database building Structure data--table (table is a collection of data)--database table "Row" is (record: Record a description of a thing) "column" Is (property: is a field Description of the same property of a thing) basic concepts of data: DBA Database management System (tool):D MSD

• Simply create one of the following class data

Serial Number	name	Achievements	Hobbies
1	Zhangsan	78	Reading
2	Lisi	89.5	Play games
3	Wangwu	93.5	Play games

1: Create a Database First name is "Grade Two class five"

mysql> create database 五年级二班;    #创建数据库Query OK, 1 row affected (0.00 sec)mysql> show databases;+------------------------------+| Database                      |+------------------------------+| information_schema    || 五年级二班                   || mysql                             || performance_schema || sys                                 |+-------------------------------+5 rows in set (0.00 sec)

• Enter the database creation table with the table named "Class Info"

mysql> use 五年级二班;              #进入数据库Database changedmysql> create table 班级信息(序列号 varchar(10),姓名 varchar(10),成绩 decimal(5,2),爱 好 varchar(10)); #创建表Query OK, 0 rows affected (0.01 sec)mysql> desc 班级信息;+-----------+--------------+--------+--------+--------+----------+| Field     | Type              | Null     | Key | Default | Extra |+-----------+--------------+--------+--------+--------+-----------+| 序列号       | varchar(10)  | YES      |     | NULL    |       || 姓名           | varchar(10)  | YES      |     | NULL    |       || 成绩           | decimal(5,2) | YES     |     | NULL    |       || 爱好           | varchar(10)  | YES      |     | NULL    |       |+-----------+--------------+-------+--------+---------+-----------+4 rows in set (0.00 sec)

• Enter information in the table

mysql> insert into 班级信息 (序列号,姓名,成绩,爱好) values (‘1‘,‘zhangsan‘,‘78‘,‘看书‘);    #编辑表中信息Query OK, 1 row affected (0.01 sec)mysql> insert into 班级信息 (序列号,姓名,成绩,爱好) values (‘2‘,‘lisi‘,‘89.5‘,‘玩游戏‘));Query OK, 1 row affected (0.00 sec)mysql> insert into 班级信息 (序列号,姓名,成绩,爱好) values (‘2‘,‘lisi‘,‘93.5‘,‘玩游戏‘));Query OK, 1 row affected (0.00 sec)mysql> select * from 班级信息;     #查看所有来自班级信息的数据+-----------+--------------+----------+-----------+| 序列号    | 姓名         |   成绩   | 爱好        |+-----------+--------------+----------+-----------+| 1             | zhangsan |  78.00 | 看书         || 2             | lisi             |  89.50 | 玩游戏     || 3            | wangwu     |  93.50 | 玩游戏     |+-----------+--------------+--------+-----------+3 rows in set (0.00 sec)

MySQL Database basics