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

数据类型：

数值型
INT, TINYINT, SMALL INT, MEDIUM INT, BIG INT 
INT UNSIGNED

无符号整型 
DECIMAL

精确数字 
FLOAT
单精度浮点型
DOUBLE
双精度浮点型



字符型

CHAR(5)
2
定长字符

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 

# 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
# systemctl enable mariadb.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: 

导出数据库和表：mysqldump
mysqldump -u root -p 库名 表名 > 存放位置和文件名

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] 

> 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;



多表查询/连接查询


内连接

外连接

左连接

右连接

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


内连接

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


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);

> 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;