mysql给表授dml权,MariaDB,MySQL

CentOS安装光盘

项目官方：

https://downloads.mariadb.org/mariadb/repositories/

安装和使用MariaDB

CentOS 7直接提供

CentOS 6

MariaDB的特性

1.MariaDB的特性：

单进程，多线程

插件式存储引擎：存储管理器有多种实现版本，功能和特性可能均略有差别；用户可根据需要灵活选择存储引擎也称之为“表类型”

(1) 更多的存储的存储引擎

MyISAM ==> Aria

InnoDB ==> XtraDB

Mysql5.5.8开始innoDB引擎是MYSQL默认引擎，

InnoDB对比MyISAM的最大特点就是InnoDB支持事务

2) 诸多扩展和新特性

(3) 提供了较多测试组件

(4) 开源

(1)Centos7的MariaDB数据库包括两个包组：

Mariadb mariadb -client

(2)mariadb组包括下面包：

mariadb -server：mandatory package

mariadb -bench：optional package

mariadb -test ：optional package

(3)mariadb -client组包括下面包

Mariadb：mandatory package

MySQL -python：default package

mysql -connector-odbc：default package

libdbi-dbd-mysql：optional package

mysql-connector-java：optional package

perl-DBD-MySQL：optional package

配置文件： /etc/my.cnf和/etc/my.cnf.d/ *.cnf

2. 安装

yum groupinstall mariadb mariadb-client

systemctl start mariadb

systemctl enable mariadb

(1)提高安全性

mysql_secure_installation

 设置数据库管理员root口令

 禁止root远程登录

 删除anonymous用户帐号

 删除test数据库

 侦听3306/tcp端口可以在绑定有一个或全部接口IP上

Paste_Image.png

Paste_Image.png

Paste_Image.png

Paste_Image.png

Paste_Image.png

Paste_Image.png

(2)Vim /etc/my.cnf

[mysqld]加一行：(忽略名字反向解析，加速访问)

skip-networking=1忽略名字解析

只侦听本地客户端， 所有和服务器的交互都通过一个socket实现，socket的配置存放在/var/lib/mysql/mysql.sock) 可在/etc/my.cnf修改

 firewall-cmd --permanent --add-service=mysql

 firewall-cmd --reload

3.MairaDB配置: 二进制格式安装过程

(1) 准备用户

groupadd -r -g 306 mysql

useradd -r -g 306 -u 306 –m –d /app/data mysql -s /sbin/nologin

-m -d /app/data指定放置家目录,只是存放数据

-r制定系统用户

-g 306指定用户组及其编号

-s /sbin/login安全

(2) 准备数据目录

以/app/data为例,建议使用逻辑卷

chown mysql:mysql /app/data

(3) 准备二进制程序

 tar xf mariadb-VERSION-linux-x86_64.tar.gz -C

/usr/local

 cd /usr/local;ln -sv mariadb-VERSION mysql

 chown -R root:mysql /usr/local/mysql/

(4) 准备配置文件

配置格式：类ini格式，各程序由单个配置文件提供配[prog_name]

配置文件查找次序：后面覆盖前面的配置文件

/etc/my.cnf --> /etc/mysql/my.cnf --> --default extrafile=/PATH/TO/CONF_FILE--> ~/.my.cnf

mkdir /etc/mysql/

cp support-files/my-large.cnf /etc/mysql/my.cnf

[mysqld]中添加三个选项：

datadir = /app/data

innodb_file_per_table = on

skip_name_resolve = on 禁止主机名解析

(5)创建数据库文件

cd/usr/local/mysql/

./scripts/mysql_install_db --datadir=/app/data --user=mysql

(6)准备日志文件

touch /var/log/mysqld.log

chown mysqld /var/log/mysqld.log

(7)准备服务脚本，并启动服务

cp ./support-files/mysql.server /etc/rc.d/init.d/mysqld

chkconfig --add mysqld

service mysqld start

(8)安全初始化

/user/local/mysql/bin/mysql_secure_installation

4.mysql：CLI交互式客户端程序

服务器监听的两种socket地址：

ip socket: 监听在tcp的3306端口，支持远程通信

unix sock: 监听在sock文件上(/tmp/mysql.sock客户端,/var/lib/mysql/mysql.sock服务器端)，仅支持本机通信

server: localhost, 127.0.0.1 自动使用unix sock

5.命令行交互式客户端程序：mysql

(1)mysql选项：

-u USERNAME: 用户名；默认为root

-h HOST: 服务器主机; 默认为localhost

-p PASSWORD：用户的密码;建议使用-p,默认为空密码

(2)mysql用户账号由两部分组成：

'USERNAME'@'HOST'

HOST用于限制此用户可通过哪些远程主机连接mysql服务

(3)支持使用通配符：

% 匹配任意长度的任意字符

172.16.0.0/16 或 172.16.%.%

_ 匹配任意单个字符

6.执行命令

(1)运行mysql命令：默认空密码登录

mysql>use mysql

mysql>select user();查看当前用户

mysql>SELECT User,Host,Password FROM user;

Paste_Image.png

Paste_Image.png

Paste_Image.png

(2)安全初始化

/usr/local/mysql/bin/mysql_secure_installatoin

(3)登录系统：mysql –uroot –p

(4)客户端命令：本地执行

mysql> help

Paste_Image.png

每个命令都完整形式和简写格式

mysql> status 或 \s

Paste_Image.png

(5)服务端命令：通过mysql协议发往服务器执行并取回结果

每个命令都必须命令结束符号；默认为分号

SELECT VERSION();

7.关系型数据库的常见组件

 数据库：database

 表：table

行：row

列：column

 索引：index

 视图：view

 用户：user

 权限：privilege

 存储过程：procedure，过程无返回值

 存储函数：function，过程有返回值

 触发器：trigger

 事件调度器：event scheduler，任务计划

8.SQL语句构成

 Keyword组成clause

 多条clause组成语句

 SELECT * SELECT子句

 FROM products FROM子句

 WHERE price>400 WHERE子句

 这是一组语句，由三个子句构成，SELECT,FROM和WHERE都是关键字

9.SQL语句分类

DDL: Data Defination Language

CREATE, DROP, ALTER

DML: Data Manipulation Language

INSERT, DELETE, UPDATE

DCL：Data Control Language

GRANT, REVOKE

DQL：Data Query Language

SELECT

10.SQL语言规范

 在数据库系统中，SQL语句不区分大小写(建议用大写)

 但字符串常量区分大小写

 SQL语句可单行或多行书写，以“;”结尾

 关键词不能跨多行或简写

 用空格和缩进来提高语句的可读性

 子句通常位于独立行，便于编辑，提高可读性

11.注释

SQL标准：

/*注释内容*/ 多行注释

-- 注释内容 单行注释，注意有空格

 MySQL注释：

#

12.数据库对象的命名规则

 必须以字母开头

 可包括数字和三个特殊字符(# _ $)

 不要使用MySQL的保留字

 同一Schema下的对象不能同名

13.数据库操作

(1) 创建数据库：

CREATE DATABASE|SCHEMA [IF NOT EXISTS] 'DB_NAME';

CHARACTER SET 'character set name'

COLLATE 'collate name'

(2)删除数据库

DROP DATABASE|SCHEMA [IF EXISTS] 'DB_NAME';

(3)查看支持所有字符集：

SHOW CHARACTER SET;

(4)查看支持所有排序规则：

SHOW COLLATION;

(5)获取命令使用帮助：

mysql> HELP KEYWORD;

 查看数据库列表：

mysql> SHOW DATABASES;

Paste_Image.png

14.创建表

(1) CREATE TABLE [IF NOT EXISTS] ‘tbl_name’ (col1 type1 修饰符, col2 type2 修饰符, ...)

(2)字段信息

• col type1

• PRIMARY KEY(col1,...)

• INDEX(col1, ...)

• UNIQUE KEY(col1, ...)

(3)表选项：

• ENGINE [=] engine_name

SHOW ENGINES;查看支持的engine类型

• ROW_FORMAT [=]

{DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}

获取帮助：mysql> HELP CREATE TABLE;

(4) 表操作

 查看所有的引擎：SHOW ENGINES;

 查看表：SHOW TABLES [FROM db_name];

 查看表结构：DESC [db_name.]tb_name;

Paste_Image.png

Paste_Image.png

 删除表：DROP TABLE [IF EXISTS] tb_name；

 查看表创建命令：SHOW CREATE TABLE tbl_name;

 查看表状态：SHOW TABLE STATUS LIKE 'tbl_name'\G

15.数据类型

(1) 整型

• tinyint(m) 1个字节 范围(-128~127)

• smallint(m) 2个字节 范围(-32768~32767)

• mediumint(m) 3个字节 范围(-8388608~8388607)

• int(m) 4个字节 范围(-2147483648~2147483647)

• bigint(m) 8个字节 范围(+-9.22*10的18次方)

取值范围如果加了unsigned，则最大值翻倍，如tinyint unsigned的取值范围为(0~255)

int(m)里的m是表示SELECT查询结果集中的显示宽度，并不影响实际的取值范围

MySQL可以为整型类型指定宽度，例如Int(11)，对绝大多数应用这是没有意义的：它不会限制值的合法范围，只是规定了MySQL的一些交互工具(例如MySQL命令行客户端)用来显示字符的个数。对于存储和计算来说，Int(1)和Int(20)是相同的

(2)浮点型(float和double)，近似值

• float(m,d) 单精度浮点型 8位精度(4字节) m总个数，d小数位

• double(m,d) 双精度浮点型 16位精度(8字节) m总个数，d小数位

• 设一个字段定义为float(6,3)，如果插入一个123.45678,实际数据库里存的是123.457，但总个数还以实际为准，即6位

(3)定点数

• 在数据库中存放的是精确值,存为十进制

• decimal(m,d) 参数m<65 是总个数，d<30且 d

• MySQL5.0和更高版本将数字打包保存到一个二进制字符串中(每4个字节存9个数字)。例如，decimal(18,9)小数点两边将各存储9个数字，一共使用9个字节：小数点前的数字用4个字节，小数点后的数字用4个字节，小数点本身占1个字节

• 浮点类型在存储同样范围的值时，通常比decimal使用更少的空间。float使用4个字节存储。double占用8个字节

• 因为需要额外的空间和计算开销，所以应该尽量只在对小数进行精确计算时才使用decimal——例如存储财务数据。但在数据量比较大的时候，可以考虑使用bigint代替decimal

(4)字符串(char,varchar,_text)

• char(n) 固定长度，最多255个字符

• varchar(n)可变长度，最多65535个字符

• tinytext 可变长度，最多255个字符

• text 可变长度，最多65535个字符

• mediumtext 可变长度，最多2的24次方-1个字符

• longtext 可变长度，最多2的32次方-1个字符

• BINARY(M) 固定长度，可存二进制或字符，允许长度为0-M字节，

• VARBINARY(M) 可变长度，可存二进制或字符，允许长度为0-M字节

• 内建类型：ENUM枚举, SET集合

(5)char和varchar：

• 1.char(n) 若存入字符数小于n，则以空格补于其后，查询之时再将空格去掉。所以char类型存储的字符串末尾不能有空格，varchar不限于此。

• 2.char(n) 固定长度，char(4)不管是存入几个字符，都将占用4个字节，varchar是存入的实际字符数+1个字节(n< n>255)，所以varchar(4),存入3个字符将占用4个字节。

• 3.char类型的字符串检索速度要比varchar类型的快

(6)varchar和text：

• 1.varchar可指定n，text不能指定，内部存储varchar是存入的实际字符数+1个字节(n< n>255)，text是实际字符数+2个字节。

• 2.text类型不能有默认值

• 3.varchar可直接创建索引，text创建索引要指定前多少个字符。

varchar查询速度快于text

(7)二进制数据：BLOB

• BLOB和text存储方式不同，TEXT以文本方式存储，英文存储区分大小写，而Blob是以二进制方式存储，不分大小写

• BLOB存储的数据只能整体读出

• TEXT可以指定字符集，BLOB不用指定字符集

(8)日期时间类型

• date 日期 '2008-12-2'

• time 时间 '12:25:36'

• datetime 日期时间 '2008-12-2 22:06:44'

• timestamp 自动存储记录修改时间

• YEAR(2), YEAR(4)：年份

若定义一个字段为timestamp，这个字段里的时间数据会随其他字段修改的时候自动刷新，这个数据类型的字段可以存放这条记录最后被修改的时间

16.修饰符

(1)所有类型：

• NULL 数据列可包含NULL值

• NOT NULL 数据列不允许包含NULL值

• DEFAULT 默认值

• PRIMARY KEY 主键

• UNIQUE KEY 唯一键

• CHARACTER SET name 指定一个字符集

(2)数值型

• AUTO_INCREMENT 自动递增，适用于整数类型

• UNSIGNED 无符号

示例

CREATE TABLE students (id int UNSIGNED NOT NULL PRIMARY KEY,

name VARCHAR(20)NOT NULL,

age tinyint UNSIGNED);

 DESC students;

 CREATE TABLE students2 (id int UNSIGNED NOT NULL ,

name VARCHAR(20)NOT NULL,

age tinyint UNSIGNED,

PRIMARY KEY(id,name));

17.表操作

 DROP TABLE [IF EXISTS] 'tbl_name';

 ALTER TABLE 'tbl_name'

添加字段：add

ADD col1 data_type [FIRST|AFTER col_name]

删除字段：drop

修改字段：

alter(默认值), change(字段名), modify(字段属性)

 查看表上的索引：

SHOW INDEXES FROM [db_name.]tbl_name;

 查看帮助：

Help ALTER TABLE

修改表示例

 ALTER TABLE students RENAME s1;

 ALTER TABLE s1 ADD phone varchar(11) AFTER

name;

 ALTER TABLE s1 MODIFY phone int;

 ALTER TABLE s1 CHANGE COLUMN phone mobile

char(11);

 ALTER TABLE s1 DROP COLUMN mobile;

 Help ALTER TABLE 查看帮助

 ALTER TABLE students ADD gender ENUM('m','f')

 ALETR TABLE students CHANGE id sid int UNSIGNED

NOT NULL PRIMARY KEY;

 ALTER TABLE students ADD UNIQUE KEY(name);

 ALTER TABLE students ADD INDEX(age);

 DESC students;

 SHOW INDEXES FROM students;

 ALTER TABLE students DROP age;

18.索引

什么是索引？优缺点？

索引是特殊数据结构；定义在查找时作为查找条件的字段

 优点：提高查询速度

 缺点：占用额外空间，影响插入速度

(1)创建索引：

创建索引必须要有索引名称

CREATE INDEX index_name ON tbl_name

(index_col_name,...);

help CREATE INDEX

(2)删除索引：

DROP INDEX index_name ON tbl_name;

(3)查看索引：

SHOW INDEXES FROM [db_name.]tbl_name;

19.DML语句

 DML: INSERT, DELETE, UPDATE

• INSERT

INSERT [INTO] tbl_name [(col_name,...)] {VALUES | VALUE} (val1,...),(...),...

• UPDATE:

UPDATE tbl_name SET col1=val1, col2=val2, ... [WHERE clause] [ORDER BY 'col_name' [DESC]] [LIMIT [m,]n];

• DELETE:

DELETE FROM tbl_name [WHERE clause] [ORDER BY 'col_name' [DESC]] [LIMIT [m,]n];

可先排序再指定删除的行数

• TRUNCATE TABLE tbl_name; 清空表

20.DQL语名

(1)SELECT

• SELECT col1,col2,... FROM tbl_name [WHERE clause] [ORDER BY 'col_name' [DESC]] [LIMIT [m,]n];

Limit m,n 跳过m个，要n个

(2) 字段表示法：

*: 所有字段

as：字段别名，col1 AS alias1

(3)WHERE clause:

操作符：

>, =, <=, ==, !=

BETWEEN ... AND ...

LIKE:

%：任意长度的任意字符

_：任意单个字符；

RLIKE：正则表达式模式匹配

IS NULL ，IS NOT NULL

IN (val1,val2,…)

条件逻辑操作：

and，or，not

示例

 DESC students;

 INSERT INTO students VALUES(1,'tom'，'m'),(2,'alice','f');

 INSERT INTO students(id,name) VALUES(3,'jack'),(4,'allen');

 SELECT * FROM students WHERE id < 3;

 SELECT * FROM students WHERE gender='m';

 SELECT * FROM students WHERE gender IS NULL;

 SELECT * FROM students WHERE gender IS NOT NULL;

 SELECT * FROM students ORDER BY name DESC LIMIT 2;

 SELECT * FROM students ORDER BY name DESC LIMIT 1,2;

 SELECT * FROM students WHERE id >=2 and id <=4

 SELECT * FROM students WHERE BETWEEN 2 AND 4

 SELECT * FROM students WHERE name LIKE ‘t%’

 SELECT * FROM students WHERE name RLIKE '.*[lo].*';

 SELECT id stuid,name as stuname FROM students

21.用户账号

(1)用户账号：'user'@'host'

user: 用户名

host: 允许用户通过哪些主机远程连接mysqld服务IP、网络地址、主机名、通配符(%和_)

(2)创建用户：

CREATE USER 'username'@'host' [IDENTIFIED

BY 'password'];

(3)查看当前用户:

SELECT user();

(4)查看用户：

SELECT User,Host,Password FROM user;

(5)删除用户：DROP USER 'username'@'host';

示例：删除默认的空用户

DROP USER ''@'localhost';

(1)更改口令：

1.SET PASSWORD FOR 'user'@'host' =

PASSWORD(‘password');

2.UPDATE user SET password=PASSWORD('magedu')

WHERE User='root' ;

注意：上面修改表的命令不会马上生效，需执行FLUSH PRIVILEGES生效

/usr/local/mysql/bin/mysqladmin -u root –poldpassword

password 'newpassword‘

22.授权

(1)权限级别：管理权限、数据库、表、字段、存储例程

• Help GRANT

• GRANT priv_type,... ON [object_type] db_name.tb_name

TO 'user'@'host' [IDENTIFIED BY 'password'] [WITH

GRANT OPTION];

• priv_type: ALL [PRIVILEGES]

• db_name.tb_name:

*.*: 所有库的所表

db_name.*: 指定库的所有表

db_name.tb_name: 指定库的指定表

db_name.routine_name：指定库的存储过程和函数

 示例：

GRANT SELECT,DELETE on testdb.* TO

'testuser'@'%'IDENTIFIED BY 'testpass‘;

(2)查看指定用户获得的授权：

Help SHOW GRANTS

SHOW GRANTS FOR 'user'@'host';

SHOW GRANTS FOR CURRENT_USER[()];

(3)回收授权：

REVOKE priv_type, ... ON db_name.tb_name FROM 'user'@'host

eg:

REVOKE DELETE ON testdb.* FROM 'testuser'@'%‘

注意：MariaDB服务进程启动时会读取mysql库中所有授权表至内存

(1) GRANT或REVOKE等执行权限操作会保存于系统表中，MariaDB的服务进程通常会自动重读授权表，使之生效

(2) 对于不能够或不能及时重读授权表的命令，可手动让MariaDB的服务进程重读授权表：mysql> FLUSH PRIVILEGES;