MySQL/MariaDB基础知识

cheun

　　MySQL/MariaDB基础知识
　　数据库：即数据存储的仓库或集合
　　数据库的组成：
　　1.  数据库
　　2.  数据表
　　3.  记录
　　4.  字段
　　5.  索引
　　6.  查询
　　7.  视图
　　8.  过滤器
　　1.  数据库 ：  由一个或多个表组成以文件的形式存储在磁盘上的物理文件；
　　2.  数据表  :   一组按行排列的相关数据，由一组数据记录组成；
　　3.  记录     ： 表中的一行称为一个记录（记录即行），由若干个字段组成；
　　4.  字段    ： 表中的一列称为一个字段，也叫域，每个字段都有相应的描述信息，如数据类型，大小，宽度等；
　　5.  索引    ： 将一个或多个字段抽取出来的一种特殊的数据结构，为了提高数据检索的效率；
　　6.  查询     ： 从一个或多个表中获取需要的记录的SQL语言；
　　7.  视图     ：  即虚表，包含已知表中的部分列；
　　8.  过滤器  ： 根据给出的条件输出指定的数据；
　　如何安装MySQL/MariaDB?
　　1.基于包管理器格式的程序包直接安装，如：rpm包，deb包等；
　　CentOS 6中：
　　]# yum install  mysqld-server
　　CentOS 7中：
　　]# yum install  mariadb-server
　　2.官方提供的通用二进制程序包；直接解压缩程序包即可使用；
　　3.源代码包编译安装；
　　MySQL/MariaDB的程序环境：
　　1. 服务器端程序：mysql-server, mariadb-server
　　1） 服务启动脚本：
　　/etc/rc.d/init.d/mysqld
　　2）常用的二进制文件：
　　/usr/bin/mysql_install_db
　　/usr/bin/mysql_secure_installation
　　/usr/bin/mysqld_multi
　　/usr/bin/mysqld_safe
　　3） 数据目录：
　　/var/lib/mysql
　　4）主配置文件：
　　/etc/my.cnf
　　2. 客户端程序：mysql, mariadb
　　常用的二进制文件：
　　/usr/bin/mysql
　　/usr/bin/mysqladmin
　　/usr/bin/mysqlbinlog
　　/usr/bin/mysqldump
　　如何启动数据库服务：
　　CentOS 6中：
　　]# service mysqld[-service] start            "[]"可省略；
　　CentOS 7中：
　　]# systemctl start mariadb[-service]    "[]"可省略；
　　mysql相关基本命令：
　　1.客户端命令
　　1）mysql命令 - 基本的交互式命令行客户端工具；
　　格式：mysql [options] [db_name]
　　常用选项：
　　--database=db_name, -D db_name
　　指定登录数据库后使用哪个数据库；不写则为null;
　　例：]# mysql -D ytc
　　......
　　MariaDB [ytc]>   //登录后直接进入了名为ytc的数据库；
　　--execute=statement, -e statement
　　连接至mysql服务器并将后面的SQL语句交给服务器运行之后返回执行结果并直接退出交互式登录模式，返回bash命令提示符；
　　--host=host_name, -h host_name
　　用于指定此次mysql程序所连接的mysql服务器的主机名称或主机IP地址；默认是localhost(127.0.0.1)；
　　--user=user_name, -u user_name
　　用于指定连接mysql服务器时使用的用户名；默认的用户名为：'root'@'localhost'
　　mysql的用户名：USERNAME@HOSTNAME
　　USERNAME表示登录的用户名称；
　　HOSTNAME表示用户通过哪个特定的客户端主机连接到mysql服务器；
　　如：'root'@'172.16.0.1'
　　表示只有172.16.0.1主机可以通过root用户连接至myslq服务器；
　　HOSTNAME中可以使用通配符：
　　_：任意单个字符；
　　%：任意长度的任意字符；
　　如：'root'@'172.16.%.%'
　　表示172.16.0.0/16网段中的所有主机都可以使用root用户连接至mysql服务器；
　　如：'root'@'172.16._.%'
　　表示172.16.0.0~172.16.9.255地址的主机都可以使用root用户连接至mysql服务器；
　　--password[=password], -p[password]
　　用于指定连接mysql服务器时，为对应用户指定身份验证所需密码；如果密码部分省略不写，执行命令后需要在新行中给出密码，这种方式相对更安全；
　　例：
　　]# mysql -utest -h172.16.75.1 -p 123456   //表示以test用户的身份登录数据库服务器172.16.75.1,密码为123456
　　注意：默认情况下，所有用户均为空密码，并且还有匿名用户存在，因此，为了增强安全性，可以使用mysql_secure_installation命令完成账户安全初始化；
　　mysql服务器仅能让root用户在本地登录，并且移除匿名用户和test数据库；
　　客户端命令：mysql程序自身完成或执行的命令，在客户端所在地运行；
　　clear     (\c)
　　停止发送当前正在键入的命令或语句并直接返回提示符；
　　delimiter (\d)
　　设置语句结束标记；默认的语句结束标记为";"；
　　ego       (\G)
　　将其前面的语句送往mysql服务器运行，并将返回的结果纵线显示；
　　go        (\g)
　　无需输入语句结束标记，直接将其前面的语句送往服务器执行；
　　exit,quit (\q)
　　退出mysql程序的交互式模式；
　　status    (\s)
　　获取mysql服务器的状态信息；
　　use       (\u)
　　将指定的数据库作为当前正在使用的数据库；
　　source    (\.)
　　可以在mysql交互式模式中执行SQL脚本文件；
　　?,help    (\?)
　　获取客户端命令的帮助信息；
　　服务器端命令：mysql客户端程序通过mysql协议向mysql服务器端发送的SQL语句；
　　注意：在书写服务器端命令时，每个SQL语句都必须以指定的结束标记结尾，默认是";"
　　2） mysqladmin命令：
　　mysqladmin - client for administering a MySQL server
　　常用选项：
　　-u, -h, -p, -P, -D, -S
　　各选项功能同mysql命令的选项功能；
　　常用子命令：
　　create db_name：创建数据库；
　　drop db_name：删除数据库；
　　flush-hosts：清除服务器缓存中所有信息；
　　flush-logs：清除日志，让日志滚动；
　　flush-privileges：刷新授权表；
　　flush-tables：为表加锁；
　　password new-password：为指定的用户设置新密码；
　　start-slave：在msyql的集群服务中的从节点启动用于实施复制功能的线程；
　　stop-slave：在msyql的集群服务中的从节点关闭用于实施复制功能的线程；
　　shutdown：停止服务；
　　例：
　　~]# mysqladmin -uytc password 123456   给名为ytc的用户设置密码
　　~]# mysqladmin -uytc -p password '111111'
　　Enter password: [输入原来的密码]         给名为ytc的用户更改密码
　　~]# mysqladmin create students                创建名为students的数据库
　　~]# mysqladmin drop students                           删除名为students的数据库
　　。。。。。。
　　2.服务器端命令
　　1）mysqld_safe命令：
　　用于启动mysql服务，定义mysql的工作特性；
　　工作特性：
　　]# mysql
　　mysql> show global variables;   查看特性
　　格式：/usr/bin/mysqld_safe [OPTIONS]
　　注意：所有给出的 OPTION(--option)都是一次性生效；如果期望配置参数永久有效，需要将此类配置参数及参数值直接定义在配置文件中(/etc/my.cnf)即可；
　　2） mysqld_multi命令
　　mysqld_multi设计用于管理多个sqmyld进程，这些进程监听不同Unix套接字上的连接文件和TCP / IP端口。它可以启动或停止服务器，或报告其当前状态。
　　3）DDL：数据定义语言
　　1. create:
　　格式：
　　创建数据库：
　　CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name [DEFAULT] [CHARACTER SET [=] charset_name  | [DEFAULT] COLLATE [=] collation_name
　　CHARACTER：字符集
　　mysql> show character set;              查看字符集
　　Collate：          核对
　　mysql> show collation;                            查看核对排序类型
　　例：
　　[root@localhost ~]# mysql -p
　　Enter password:
　　。。。。。。。
　　mysql> create database students character set = 'utf8';
　　Query OK, 1 row affected (0.04 sec)        创建名为students的数据库，默认字符集为‘utf8’;
　　创建数据表：
　　CREATE TABLE [IF NOT EXISTS] tbl_name (create_definition,...) [table_options]
　　create_definition：
　　col_name  data_type [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY] [COMMENT 'string']
　　table_options：
　　ENGINE [=] engine_name | AUTO_INCREMENT [=] value
　　col_name:                                              字段名
　　data_type:                                              数据类型
　　[NOT NULL | NULL]                               该字段的值是否为空
　　[AUTO_INCREMENT]                             整型数据的递增或递减
　　[UNIQUE [KEY] | [PRIMARY] KEY]          定义唯一键和主键
　　COMMENT 'string'                                评论‘字符串’
　　例：
　　MariaDB [(none)]> use ytc;
　　MariaDB [ytc]> create table stu_info (SID tinyint unsigned not null auto_increment primary key,SName varchar(50) not null,SAge tinyint unsigned,Gender enum('F','M'));
　　Query OK, 0 rows affected (0.00 sec)     创建stu_info表
　　MariaDB [ytc]> select * from stu_info;    查看表内容
　　MariaDB [ytc]> desc stu_info;                查看表结构
　　2. drop：删除数据库：
　　格式：
　　DROP {DATABASE | SCHEMA} [IF EXISTS] db_name
　　例：
　　mysql> drop  database students;
　　Query OK, 0 rows affected (0.02 sec)     删除名为students的数据库
　　3.alter:
　　格式：

　　>　　例：

　　mysql>>　　Query OK, 1 row affected (0.01 sec)        修改students数据库的字符集为‘latin7’
　　mysql> show character set;              查看字符集
　　4. show:
　　格式：
　　SHOW {DATABASES | SCHEMAS}[LIKE 'pattern' | WHERE expr]
　　例：
　　mysql>  show databases like 'ytc';
　　+----------------+
　　| Database (ytc) |
　　+----------------+
　　| ytc            |
　　+----------------+                               查看名字ytc的数据库
　　例：
　　mysql>  show databases like 'y%';
　　+---------------+
　　| Database (y%) |
　　+---------------+
　　| ytc           |
　　| ytc123        |
　　+---------------+
　　2 rows in set (0.00 sec)                       查看名字中以“y”开头的数据库
　　4）DML：数据管理语言
　　1.select  查看表内容
　　格式：
　　SELECT select_expr [, select_expr ...] [FROM table_references] [WHERE where_condition]
　　1）显示整张表：
　　SELECT * FROM tbl_name;
　　例：
　　MariaDB [hellodb]> select * from students;                          显示students整张表
　　2）投影：显示指定的或符合要求的列；
　　SELECT col_name AS con_alias[,col_name AS con_alias,...] FROM tbl_name;
　　例：
　　MariaDB [hellodb]> select StuID,Name from students;                 显示表中StuID,Name两列
　　3）选择：显示符合要求或匹配条件的列并将列改用别名显示；
　　SELECT col_name AS con_alias[,col_name AS con_alias,...] FROM tbl_name WHERE condition;
　　例：
　　MariaDB [hellodb]> select StuID as a,Name as b from students;    将StuID,Name分别以别名a,b显示
　　WHERE子句用于指明选择时依据的条件：
　　col_name 操作符 value
　　操作符：
　　1) 比较操作符：>, =,  select * from students where Age>20;
　　//查看年龄大于20的学生信息
　　MariaDB [hellodb]> select * from students where SAge >= 20 and SAge  select * from students where Age between 20 and 30;
　　//查看年龄大于20小于30的学生信息
　　MariaDB [hellodb]> select * from students where Gender = "M";
　　//查看性别为男的学生信息
　　MariaDB [hellodb]> select * from students where Name like 'H%';
　　//查看名字以H开头的学生信息
　　MariaDB [hellodb]> select * from students where Name like '%g';
　　//查看名字以g结尾的学生信息
　　MariaDB [hellodb]> select * from students where Name rlike '^S';
//查看名字以S开头的学生信息　　MariaDB [hellodb]> select * from students where Name rlike 'n$';
　　//查看名字以n结尾的学生信息

　　MariaDB [hellodb]> select * from students where>　　//查看ClassID不为空的学生信息
　　4)排序：
　　SELECT col_name AS con_alias[,col_name AS con_alias,...] FROM tbl_name WHERE condition [ORDER BY {col_name | expr | position} [ASC | DESC], ...]
　　例：
　　MariaDB [hellodb]> select * from students order by Age ;
　　MariaDB [hellodb]> select * from students order by Age asc ;
　　MariaDB [hellodb]> select * from students order by Age ASC;
　　//以年龄大小升序排序
　　例：
　　MariaDB [hellodb]> select * from students order by Age desc ;
　　MariaDB [hellodb]> select * from students order by Age DESC;
　　//以年龄从大小降序排序
　　2.修改表数据内容：
　　UPDATE [LOW_PRIORITY] [IGNORE] table_reference SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...
　　[WHERE where_condition] [ORDER BY ...] [LIMIT row_count]
　　例：
　　MariaDB [hellodb]> update students set Name='Obama' where StuID=5;
　　MariaDB [hellodb]> update students set Age=SAge+1;
　　MariaDB [hellodb]> update students set Age=Age+100 where StuID in (1,3,5,7);
　　注意：修改数据时，一定要考虑清楚是否要添加where条件子句；如果不写where子句，默认修改全表；
　　3. insert/replace插入内容
　　INSERT [INTO] tbl_name [(col_name,...)] {VALUES | VALUE} ({expr | DEFAULT},...),(...),...
　　Or:
　　INSERT [INTO] tbl_name SET col_name={expr | DEFAULT}, ...
　　例：
　　INSERT [INTO] tbl_name [(col_name,...)] {VALUES | VALUE} ({expr | DEFAULT},...),(...),...
　　Or:
　　INSERT [INTO] tbl_name SET col_name={expr | DEFAULT}, ...
　　例：
　　MariaDB [hellodb]> insert into students values (1,'tom','M',25),(2,'jerry','F',28);
　　MariaDB [hellodb]> insert into students (SName) values ('Alice'),('James');
　　MariaDB [hellodb]> insert into students set SName='zhangsan',Gender='M',SAge=20;
　　注意：
　　1.如果向表中插入的value是字符串，则value必须使用引号引用；
　　2.如果向表中插入的value是数字，则value一定不能用引号引用；
　　4. delete删除表内容
　　DELETE FROM tbl_name  [WHERE where_condition] [ORDER BY ...] [LIMIT row_count]
　　例：
　　MariaDB [hellodb]> delete from students where StuID > 3;
　　5） DCL：数据控制语言，主要用于管理数据访问的授权；
　　1.grant
　　格式：
　　GRANT priv_type [(column_list)][, priv_type [(column_list)]] ... ON [object_type] priv_level TO user_specification [, user_specification] ...
　　例：

　　MariaDB [hellodb]> grant select,insert,update on *.* to 'myuser'@'172.16.%.%'>　　2.revoke：授权回收：
　　格式：
　　REVOKE priv_type [(column_list)][, priv_type [(column_list)]] ... ON [object_type] priv_level FROM user [, user] ...
　　例：
　　MariaDB [hellodb]> revoke insert on *.* from 'myuser'@'172.16.%.%';