MySQL First day

DATABASE Day1:
First, build the database server
Second, the basic use of database services
Third, MySQL data type
Iv. Management Records
V. User authorization and revocation of rights
Vi. Data Backup and recovery
Vii. MySQL master/slave synchronization
Viii. data read and write separation
Nine, MySQL optimization
X. MySQL Cluster
#######################
First, build the database server
The database server is used to store data
1, purchase the hardware configuration of the server: storage CPU memory
2. Install the operating system: Linux UNIX Windows
3. Install the package that provides the database service:
3.1 What types of software are available: MySQL mariadb sql-server DB2
3.2来 Source: Download the system installation CD-ROM from the website
3.3 Type: RPM (non-modifiable) source package (customizable)
3.4 Whether the package's open source is cross-platform:

Pre-Installation Preparation
Basic Needs
-1. Build MySQL server with RHEL7.2 system
-2. Turn off firewall systemctl stop FIREWALLD
-3. Turn off selinux sed ' s/=enforcing/=permissive/g '/etc/selinux/config
-4. Software mysql-5.7.17-1
Preparatory work
Stop MARIADB Service
deleting files

1. Install the Package
[Email protected] 09.mysql]# RM-RF mysql-community-server-minimal-5.7.17-1.el7.x86_64.rpm

[[email protected] 09.mysql]# ls mysql-*.rpm

[[email protected] 09.mysql]# RPM-UVH mysql-community-*.rpm installation, if there is an older version of the upgrade, you can also see which dependent packages are required
Warning: mysql-community-client-5.7.17-1.el7.x86_64.rpm: Head V3 dsa/sha1 Signature, key ID 5072e1f5:nokey
Error: Dependency detection failed:
Perl (Data::D umper) is mysql-community-test-5.7.17-1.el7.x86_64 required
Perl (JSON) is mysql-community-test-5.7.17-1.el7.x86_64 required

[email protected] 09.mysql]# Yum list |grep "Perl-data-dumper"
perl-data-dumper.x86_64 2.145-3.el7 192.168.4.254_rhel7
[email protected] 09.mysql]# Yum list |grep "Perl-json"
Perl-json.noarch 2.59-2.el7 192.168.4.254_rhel7
Perl-json-pp.noarch 2.27202-2.el7 192.168.4.254_rhel7
[Email protected] 09.mysql]# yum-y install perl-data-dumper.x86_64 perl-json.noarch perl-json-pp.noarch
[Email protected] 09.mysql]# RPM-UVH mysql-community-*.rpm

2. Start the service
Systemctl Start mysqld
Systemctl Enable Mysqld

3. Information related to MySQL database service
Process name Mysqld
Process owner and owning group MySQL MySQL
Default port number 3306
Transport Protocol TCP
Configuration file/etc/my.cnf
Database Directory/var/lib/mysql
Log file/var/log/mysqld.log (store initial password)

Connect to the database service using the database administrator root user initial password
[Email protected] 09.mysql]# grep-i Password/var/log/mysqld.log
2018-02-22T03:21:32.096060Z 1 [Note] A temporary password is generated for [email protected]:; Go-_qk (2Jon
[[email protected] 09.mysql]# which MySQL
[Email protected] 09.mysql]# mysql-uroot-p "; Go-_qk (2Jon "
Mysql>
Mysql> quit//Disconnect

Set the database administrator root user native login password
mysql> set global validate_password_policy=0;
Query OK, 0 rows Affected (0.00 sec)

mysql> set global validate_password_length=6;
Query OK, 0 rows Affected (0.00 sec)

mysql> alter user [email protected] identified by "123456";
Query OK, 0 rows Affected (0.00 sec)

Mysql> quit

VIM/ETC/MY.CNF #永久生效密码
[Mysqld]
Validate_password_policy=0
Validate_password_length=6

[Email protected] 09.mysql]# Systemctl stop Mysqld.service
[Email protected] 09.mysql]# systemctl start Mysqld.service

[Email protected] 09.mysql]# mysql-uroot-p123456
mysql> show databases;

Ii. Basic use of database services
to store data on the database server procedure
1. Connect to the database server
allow the database administrator root user to connect to the database natively in the database service
MySQL- hlocalhost-uroot-p123456
2. Create library (folder)
mysql> create database name; # #等于在/var/lib/mysql created a directory
Mysql> System Ls/var/lib/mysql # #可以用linux命令 without semicolons
mysql> Select Database (); # #类似linux中的pwd
mysql> Use library name; # #进入库
mysql> drop database name; # #删除库
mysql> Drop database name \c # #终止继续打命令
3. Build table (System file)
table must be saved in the library.
mysql> use MySQL; # #进入库
mysql> show tables, # #查看库里面所有表
mysql> select * from user; # #查看表中所有列的内容
Mysq L> select User,host from user; View the contents of the User,host column in the table
mysql> desc user; # #查看表中有什么列, what type
mysql> select User,host,select_priv from user; View the contents of the Select_priv,user,host column in a table
############################################################## #3
Build a Table command
Row (record)
column (field name)
Save student information in the Gamedb library
Name Age
Tom +
Jim

Mysql> CREATE TABLE Gamedb.stu (name char (ten), age int (2)); # #创建表中的列, type, column name,age, type Char,int.
Query OK, 0 rows affected (0.19 sec)

mysql> use Gamedb; # #进入库

Mysql> Show tables; # #查看表库中所有表
+------------------+
| Tables_in_gamedb |
+------------------+
| Stu |
+------------------+
1 row in Set (0.00 sec)

Mysql> desc Stu;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| name | char (10) | YES | | NULL | |
| Age | Int (2) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+

Mysql> select * from Stu;
Empty Set (0.00 sec)

mysql> INSERT INTO Stu values ("Jim", 21); # #插入表中列内的内容, name Jim,age is 21
mysql> INSERT INTO Stu values ("Tom", 19);

Mysql> select * from Stu; # #查看stu表中所有列的内容 (value)
+------+------+
| name | Age |
+------+------+
| Jim | 21 |
| Tom | 19 |
+------+------+
2 rows in Set (0.00 sec)

Mysql> select name from Stu; # #查看stu表中name列的内容 (value)
+------+
| name |
+------+
| Jim |
| Tom |
+------+
2 rows in Set (0.00 sec)

mysql> Delete from Stu; # #删除表中所有列内的内容 (value)
Query OK, 2 rows affected (0.06 sec)

Mysql> select * from Stu; # #查看表在所有列中的内容 (value)
Empty Set (0.00 sec)

mysql> drop table Stu; # #删除表
Query OK, 0 rows affected (0.12 sec)

mysql> drop Database gamedb; # #删除库
Query OK, 0 rows Affected (0.00 sec)

Manage SQL commands used by the database service;
SQL command usage rules?
SQL command Type

Third, MySQL data type
1. Integer type
When the value is not sufficient to specify the width, the left space complements
Width only displays width, the size of the stored value is determined by the type
Fill in 0 instead of space when using keyword Zerofill
Type size range (signed) range (unsigned) use
tinyint 1 byte -128~127 255 minute integer
SmallInt 2 bytes -32768~32767 0~65535 Small integer
Mediumint 3 bytes -2^23~2^23-1 0~2^24-1 in integers
int 4 byte -2^31~2^31-1 0~2^32-1 large integer
bigint 8 byte -2^63~2^63-1 0~2^64-1 maximum integer
Float 4-byte single-precision floating-point number
Double 8-byte dual-precision floating-point number

mysql> CREATE DATABASE db1;
Query OK, 1 row Affected (0.00 sec)

mysql> use DB1;
Database changed

Mysql> CREATE TABLE Db1.t2 (name char (4), age tinyint); # #无符号表示 Age tinyint unsigned
Query OK, 0 rows affected (0.41 sec)

mysql> desc T2;
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| name | CHAR (4) | YES | | NULL | |
| Age | Tinyint (4) | YES | | NULL | |
+-------+------------+------+-----+---------+-------+
2 rows in Set (0.00 sec)

mysql> INSERT INTO T2 values ("Tom", 127);
Query OK, 1 row affected (0.04 sec)

Mysql> select * from T2;
+------+------+
| name | Age |
+------+------+
| Tom | 127 |
+------+------+
1 row in Set (0.00 sec)

2. Floating point number
Float (n,m) n total width, m decimal number
Mysql> CREATE TABLE db1.t3 (tinyint unsigned,rust float (5,2), pay float (7,2));
Query OK, 0 rows affected (0.40 sec)

mysql> desc T3;
+-------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| Age | tinyint (3) unsigned | YES | | NULL | |
| Rust | Float (5,2) | YES | | NULL | |
| Pay | Float (7,2) | YES | | NULL | |
+-------+---------------------+------+-----+---------+-------+
3 Rows in Set (0.00 sec)

mysql> INSERT into T3 values (21,59.9,18000.23);
Query OK, 1 row affected (0.08 sec)

Mysql> select * from T3;
+------+-------+----------+
| age| Rust| Pay |
+------+-------+----------+
| 21 | 59.90 | 18000.23 |
+------+-------+----------+
1 row in Set (0.00 sec)

3. Character type 1 bytes one character
Fixed length: char (number of characters) maximum length 255 characters # #不够指定字符长度在右边用空格补齐 (wasted space, common)
Variable length: varchar (number of characters) Max 65,532 characters # #按实际大小分配空间 (System helps calculate space, but affects processing speed)
Large text type: Text/blob characters greater than 65535 are used when storing
Mysql> CREATE TABLE Db1.t8 (name char (5), Age Tinyint,rust Float (5,2), pay float (7,2));
Query OK, 0 rows affected (0.39 sec)
Mysql> DESC T8;
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| name | CHAR (5) | YES | | NULL | |
| Age | Tinyint (4) | YES | | NULL | |
| Rust | Float (5,2) | YES | | NULL | |
| Pay | Float (7,2) | YES | | NULL | |
+-------+------------+------+-----+---------+-------+
4 rows in Set (0.00 sec)

Mysql> INSERT INTO T8 values ("Tom", 21,159.92,18000.23);
Query OK, 1 row affected (0.04 sec)

Mysql> SELECT * from T8;
+------+------+--------+----------+
| name | Age | Rust | Pay |
+------+------+--------+----------+
| Tom | 21 | 159.92 | 18000.23 |
+------+------+--------+----------+
1 row in Set (0.00 sec)

MySQL First day