搭建MySQL数据库服务RHCE-Day10

MySQL数据库存储数据结构：
库 Database
表 Table
字段 Colmun
记录 Record



数据类型：
数值型
INT, TINY INT, SMALL INT, MEDIUM INT, BIG INT 
INT UNSIGNED 无符号整型 
DECIMAL 精确数字 
FLOAT 单精度浮点型
DOUBLE 双精度浮点型

字符型
CHAR(5) 定长字符
VARCHAR(20) 变长字符
TEXT
ENUM('M','F') 枚举 
SET('M','F') 集合

日期/时间型
DATE YYYY-MM-DD 
TIME HH:MM:SS 
DATETIME YYYY-MM-DD HH:MM:SS 
YEAR YYYY 
TIMESTAMP 时间戳   YYYY-MM-DD HH:MM:SS 


特殊属性：
主键   PRIMARY KEY
自动增长 AUTO_INCREMENT （主键+NOT NULL）
NOT NULL     不允许为空
DEFAULT "value"设置默认值


数据库管理软件：
MySQL, Oracle, Redis, postgreSQL
SQL Server


MariaDB www.percona.com 

# yum install -y mariadb-server 
# yum install -y mariadb-*


# rpm -qa | grep mariadb
mariadb-devel-5.5.35-3.el7.x86_64
mariadb-libs-5.5.35-3.el7.x86_64
mariadb-bench-5.5.35-3.el7.x86_64
mariadb-server-5.5.35-3.el7.x86_64
mariadb-5.5.35-3.el7.x86_64
mariadb-test-5.5.35-3.el7.x86_64

# systemctl start mariadb
开机启动mariaba
# systemctl enable mariadb.service 
查看开机启动项：
# systemctl list-unit-files  —-type service 
# netstat -antp | grep :3306
tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN      2243/mysqld    


1、登录数据库


# mysql -u <user>
# mysql -u <user> -p 
# mysql -u <user> -p -h <Host> 




2、设置root用户密码
# mysqladmin -u root -p password "redhat"
Enter password: 




库、表管理


数据库管理 


1、查看数据库
MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.01 sec)


2、创建数据库
MariaDB [(none)]> CREATE DATABASE mydb;
Query OK, 1 row affected (0.01 sec)


3、切换数据库
MariaDB [(none)]> USE mydb
Database changed


4、删除数据库
MariaDB [mysql]> DROP DATABASE test ;
Query OK, 0 rows affected (0.01 sec)


5、导入数据库


# mysql -u root -p < /root/jiaowu.sql 
Enter password: 


MariaDB [(none)]> SOURCE /root/jiaowu.sql;




数据表管理 


1、查看数据表
MariaDB [jiaowu]> SHOW TABLES;
+------------------+
| Tables_in_jiaowu |
+------------------+
| courses          |
| scores           |
| students         |
| tutors           |
+------------------+
4 rows in set (0.01 sec)


2、查看表结构 


MariaDB [jiaowu]> DESC tutors\G;


3、创建表
CREATE TABLE tb_name(col1 DataType [property], col2 DataType [property],....)


MariaDB [mydb]> CREATE TABLE mytb(
    -> id INT UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT,
    -> name CHAR(10) NOT NULL,
    -> gender ENUM("M","F") NOT NULL,
    -> age TINYINT);



4、删除表 
MariaDB [jiaowu]> DROP TABLE scores;
Query OK, 0 rows affected (0.01 sec)




5、导入数据表 
# mysql -u root -p mydb < /root/a.sql 
Enter password: 




6、修改表结构-------ALTER TABLE 


1)修改表名称 


MariaDB [mydb]> ALTER TABLE mytb RENAME game;
Query OK, 0 rows affected (0.01 sec)


2)删除字段
MariaDB [mydb]> ALTER TABLE game DROP age;


3)添加字段
MariaDB [mydb]> ALTER TABLE game ADD age TINYINT NOT NULL;


MariaDB [mydb]> ALTER TABLE game ADD city VARCHAR(20) FIRST;


MariaDB [mydb]> ALTER TABLE game ADD address VARCHAR(500) AFTER gender;


4)修改字段名称及数据类型
MariaDB [mydb]> ALTER TABLE game CHANGE age NianLing TINYINT;


5)修改字段的数据类型
MariaDB [mydb]> ALTER TABLE game MODIFY address VARCHAR(50);







数据管理


1、添加数据 


INSERT INTO tb_name(col1,col2,col3,...) VALUES(value1,value2,value3,....)


MariaDB [mydb]> INSERT INTO game(id,name) VALUES(1,"tom");
MariaDB [mydb]> INSERT INTO game(id,name) VALUES(2,"jerry"),(3,"Mike");


MariaDB [mydb]> INSERT INTO game VALUES(6,"userC","F");




2、更新数据 


UPDATE tb_name SET col=new_value WHERE condition


MariaDB [mydb]> UPDATE game SET gender="F" WHERE name="Mike";




3、删除数据 


DELETE FROM tb_name WHERE condition


MariaDB [mydb]> DELETE FROM game WHERE gender="M";
Query OK, 4 rows affected (0.03 sec)




数据查询
单表查询
多表查询/连接查询
嵌套查询




单表查询


SELECT col1,col2,col3,... FROM tb_name [select_statement]

MariaDB [jiaowu]> SELECT * FROM tutors;
MariaDB [jiaowu]> SELECT Tname,Age FROM tutors;


设置字段别名
MariaDB [jiaowu]> SELECT Tname AS 姓名, Age AS 年龄 FROM tutors;




子句1：
WHERE condition 

算术运算符： =, >, >=, <, <=, != 
逻辑运算符： AND, OR, NOT 


MariaDB [jiaowu]> SELECT * FROM tutors WHERE Age > 80;


MariaDB [jiaowu]> SELECT * FROM tutors WHERE Age >= 70 AND Age <= 90;


BETWEEN n AND m 
MariaDB [jiaowu]> SELECT * FROM tutors WHERE Age BETWEEN 70 AND 90;






> SELECT Tname,Age,Gender  FROM tutors WHERE Tname="YiDeng" OR Tname="HuYiDAO";


IN(value1,value2)


> SELECT Tname,Age,Gender FROM tutors WHERE Tname IN("YiDeng","HuYiDao");






IS NULL/IS NOT NULL 


MariaDB [mydb]> SELECT * FROM game WHERE age IS NULL;
MariaDB [mydb]> SELECT * FROM game WHERE age IS NOT NULL;




模糊查询


LIKE "通配符"

通配符： 
%：任意长度字符
_：任意单个字符

MariaDB [jiaowu]> SELECT * FROM tutors WHERE Tname LIKE "%ang%";


RLIKE "正则表达式"


MariaDB [jiaowu]> SELECT * FROM tutors WHERE Tname RLIKE "^[HY]";






子句2：
ORDER BY col [ ASC | DESC ]排序 
ASC：升序，默认
DESC：降序 

MariaDB [jiaowu]> SELECT * FROM tutors ORDER BY Age;
MariaDB [jiaowu]> SELECT * FROM tutors ORDER BY Age DESC;




子句3：
LIMIT [n,] m 
LIMIT m：仅显示前m行 
LIMIT n,m：忽略前n行，共显示m行



MariaDB [jiaowu]> SELECT * FROM tutors LIMIT 3;


MariaDB [jiaowu]> SELECT * FROM tutors LIMIT 4,1;






聚合函数：
SUM(col)
AVG(col)
MAX(col)
MIN(col)
COUNT(col)


MariaDB [jiaowu]> SELECT AVG(Age) AS 平均年龄 FROM tutors;

子句4：
GROUP BY col  [HAVING condition] 作用是按col分组输出

> SELECT AVG(Age) AS 平均年龄,Gender AS 性别 FROM tutors GROUP BY Gender;

> SELECT AVG(Age) AS 平均年龄,Gender AS 性别 FROM tutors GROUP BY Gender HAVING 平均年龄>65 ;



DISTINCT：重复的值只显示一次

> SELECT DISTINCT Gender FROM tutors;

LIMIT OFFSET：显示行数，从第几行开始显示

LIMIT 1 表示只显示1行

OFFSET 3：表示从第4行开始显示

DECLARE：声明一个变量关键字

DECLARE M INT；声明了一个int变量M

多表查询/连接查询
包括：内连接、外连接、左连接、右连接

前提：多表间存在相关联字段

内连接
相关联字段具有相同的值时才会生成结果 

SELECT tb1.col,tb2.col,... FROM tb1 INNER JOIN tb2 ON tb1.col=tb2.col 


> SELECT students.name,students.age,courses.Cname FROM students INNER JOIN courses ON students.CID1=courses.CID;




左外连接：
以左表为主，显示左表中所有数据，右表没有相关联数据时，显示为NULL 


SELECT tb1.col,tb2.col,... FROM tb1 LEFT JOIN tb2 ON tb1.col=tb2.col 


> SELECT students.name,students.age,courses.Cname FROM students LEFT JOIN courses ON students.CID1=courses.CID;




右外连接
以右表为主，显示右表中所有数据，左表没有相关联数据时，显示为NULL 


SELECT tb1.col,tb2.col,... FROM tb1 RIGHT JOIN tb2 ON tb1.col=tb2.col 


> SELECT students.name,students.age,courses.Cname FROM courses RIGHT JOIN students ON students.CID1=courses.CID;




嵌套查询/子查询
将一个查询的结果作为另外一个查询数据源、条件使用

> SELECT * FROM tutors WHERE Age > (SELECT AVG(Age) FROM tutors);


查找students表中和tutors中年龄相同的记录
> SELECT name,age FROM students WHERE Age IN(SELECT Age FROM tutors);


和上面的功能相类似，使用的是非嵌套查询的方式, 因为是两张表，但是还需要多表查询。
SELECT a.name,a.age FROM a,b WHERE a.age=b.age ;








用户、权限管理 


用户名：
user@host 

存放用户的表：
mysql.user 
MariaDB [(none)]> SELECT user,host,password FROM mysql.user;
+------+---------------+-------------------------------------------+
| user | host          | password                                  |
+------+---------------+-------------------------------------------+
| root | localhost     | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 |
| root | server.bj.com |                                           |
| root | 127.0.0.1     |                                           |
| root | ::1           |                                           |
|      | localhost     |                                           |
|      | server.bj.com |                                           |
| tom  | 127.0.0.1     | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 |
+------+---------------+-------------------------------------------+

创建用户
CREATE USER user@host IDENTIFIED BY "password"


> CREATE USER 'admin'@'192.168.1.1' IDENTIFIED BY "redhat";
> FLUSH PRIVILEGES;


192.168.1.1
192.168.1.% 
%


删除用户
DROP USER user@host
MariaDB [(none)]> DROP USER ''@'localhost';
MariaDB [(none)]> DROP USER ''@'server.bj.com';
MariaDB [(none)]> FLUSH PRIVILEGES;




使用UPDATE语句修改用户密码
> UPDATE mysql.user SET password=Password("redhat") WHERE user="root";
> FLUSH PRIVILEGES;


使用SET PASSWORD修改密码 
MariaDB [(none)]> SET PASSWORD FOR 'admin'@'192.168.1.1'=Password("123456");
MariaDB [(none)]> FLUSH PRIVILEGES;




权限： 


1、授权语句


GRANT priv ON db_name.tb_name TO user@host [IDENTIFIED BY password] [WITH GRANT OPTION]


priv权限：
create, drop, alter 
select, update, insert, delete 
all 

db_name.tb_name
jiaowu.tutors 
jiaowu.* 
*.* 


WITH GRANT OPTION：
将权限授予其他用户



> GRANT select,insert ON jiaowu.* TO 'admin'@'10.1.1.22' IDENTIFIED BY "redhat";
> FLUSH PRIVILEGES;



2、查看用户权限 


SHOW GRANTS FOR user@host


> SHOW GRANTS FOR 'admin'@'10.1.1.22';
+--------------------------------------------------------------------------------------------------------------+
| Grants for admin@10.1.1.22                                                                                   |
+--------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'admin'@'10.1.1.22' IDENTIFIED BY PASSWORD '*84BB5DF4823DA319BBF86C99624479A198E6EEE9' |
| GRANT SELECT, INSERT ON `jiaowu`.* TO 'admin'@'10.1.1.22'                                                    |
+--------------------------------------------------------------------------------------------------------------+


3、撤销权限


REVOKE priv ON db_name.tb_name FROM user@host


> REVOKE insert ON jiaowu.* FROM 'admin'@'10.1.1.22';
> FLUSH PRIVILEGES;