页面“Redis 和 Memcache的区别”与“Sql 基础”之间的差异

来自linux中国网wiki
(页面间的差异)
跳到导航 跳到搜索
docker>Evan
 
(导入1个版本)
 
第1行: 第1行:
=1=
+
sql 基础
Redis支持服务器端的数据操作:Redis相比Memcached来说,拥有更多的数据结构和并支持更丰富的数据操作,通常在Memcached里,你需要将数据拿到客户端来进行类似的修改再set回去。
 
这大大增加了网络IO的次数和数据体积。在Redis中,这些复杂的操作通常和一般的GET/SET一样高效。所以,如果需要缓存能够支持更复杂的结构和操作,那么Redis会是不错的选择。
 
  
=2=
+
=== 2.2.1 SQL分类===
内存使用效率对比:使用简单的key-value存储的话,Memcached的内存利用率更高,而如果Redis采用hash结构来做key-value存储,由于其组合式的压缩,其内存利用率会高于Memcached。
+
<pre>DDL  DML  DCL
 +
DML(data manipulation language):数据操纵语言
 +
      它们是SELECT、UPDATE、INSERT、DELETE,就象它的名字一样,这4条命令是用来对数据库里的数据进行操作的语言
 +
DDL(data definition language):数据定义语言
 +
      DDL比DML要多,主要的命令有CREATE、ALTER、DROP等,DDL主要是用在定义或改变表(TABLE)的结构,数据类型,表之间的链接和约束等初始化工作上,他们大多在建立表时使用
 +
DCL(Data Control Language):数据控制语言
 +
      是数据库控制功能。是用来设置或更改数据库用户或角色权限的语句,包括(grant,deny,revoke等)语句。在默认状态下,只有sysadmin,dbcreator,db_owner或db_securityadmin等人员才有权力执行DCL</pre>
 +
=== 2.2.2  DDL ===
 +
<pre>1.创建数据库
 +
create database test1 character set utf8;
  
=3=
+
MariaDB [(none)]> use test1;
性能对比:由于Redis只使用单核,而Memcached可以使用多核,所以平均每一个核上Redis在存储小数据时比Memcached性能更高。而在100k以上的数据中,
+
Database changed
Memcached性能要高于Redis,虽然Redis最近也在存储大数据的性能上进行优化,但是比起Memcached,还是稍有逊色。
+
MariaDB [test1]> show tables;
 +
Empty set (0.001 sec)
  
=选择=
+
2. 删除数据库
对于两者的选择还是要看具体的应用场景,如果需要缓存的数据只是key-value这样简单的结构时,我在项目里还是采用memcache,它也足够的稳定可靠。如果涉及到存储,排序等一系列复杂的操作时,毫无疑问选择redis。
 
memecache 把数据全部存在内存之中,断电后会挂掉,数据不能超过内存大小
 
redis有部份存在硬盘上,这样能保证数据的持久性,支持数据的持久化
 
  
 +
MariaDB [mysql]> drop database test1;
 +
Query OK, 0 rows affected (0.001 sec)
  
[https://www.zhihu.com/question/19645807/answer/145409185 MongoDB 或者 redis 可以替代 memcached 吗?]
+
drop 语句都是显示为0 rows affected (0.001 sec)
 +
 
 +
3. 创建表
 +
create table emp(ename varchar(10), hiredate date,sal decimal(10,2),deptno int(20));
 +
 
 +
MariaDB [test1]> desc emp;
 +
+----------+---------------+------+-----+---------+-------+
 +
| Field    | Type          | Null | Key | Default | Extra |
 +
+----------+---------------+------+-----+---------+-------+
 +
| ename    | varchar(10)  | YES  |    | NULL    |      |
 +
| hiredate | date          | YES  |    | NULL    |      |
 +
| sal      | decimal(10,2) | YES  |    | NULL    |      |
 +
| deptno  | int(20)      | YES  |    | NULL    |      |
 +
+----------+---------------+------+-----+---------+-------+
 +
4 rows in set (0.004 sec)
 +
 
 +
MariaDB [test1]> show create table emp \G;
 +
*************************** 1. row ***************************
 +
      Table: emp
 +
Create Table: CREATE TABLE `emp` (
 +
  `ename` varchar(10) DEFAULT NULL,
 +
  `hiredate` date DEFAULT NULL,
 +
  `sal` decimal(10,2) DEFAULT NULL,
 +
  `deptno` int(20) DEFAULT NULL
 +
) ENGINE=InnoDB DEFAULT CHARSET=utf8
 +
1 row in set (0.000 sec)
 +
 
 +
4. 删除表
 +
drop table tablename;
 +
 
 +
5. 修改表
 +
MariaDB [test1]> desc emp;
 +
+----------+---------------+------+-----+---------+-------+
 +
| Field    | Type          | Null | Key | Default | Extra |
 +
+----------+---------------+------+-----+---------+-------+
 +
| ename    | varchar(10)  | YES  |    | NULL    |      |
 +
+----------+---------------+------+-----+---------+-------+
 +
 
 +
MariaDB [test1]> alter table  emp modify ename varchar(20);
 +
 
 +
MariaDB [test1]> desc emp;
 +
+----------+---------------+------+-----+---------+-------+
 +
| Field    | Type          | Null | Key | Default | Extra |
 +
+----------+---------------+------+-----+---------+-------+
 +
| ename    | varchar(20)  | YES  |    | NULL    |      |
 +
 
 +
 
 +
(2)增加字段 
 +
 
 +
MariaDB [test1]> alter table emp add column age int(3);
 +
 
 +
MariaDB [test1]> desc emp;
 +
+----------+---------------+------+-----+---------+-------+
 +
| Field    | Type          | Null | Key | Default | Extra |
 +
+----------+---------------+------+-----+---------+-------+
 +
| ename    | varchar(20)  | YES  |    | NULL    |      |
 +
 
 +
| age      | int(3)        | YES  |    | NULL    |      |
 +
+----------+---------------+------+-----+---------+-------+
 +
 
 +
 
 +
(3)删除字段
 +
alter table tablename drop  column col_name;
 +
 
 +
(4) 字段改名
 +
alter  table emp change age age1  int(4);
 +
 
 +
change modify 的区别p 33
 +
 
 +
(5) 修改字段排列顺序
 +
 
 +
alter table emp add birthd after ename;
 +
alter table emp modify age int(3) first;
 +
 
 +
(6) 更改表名
 +
alter table emp rename emp1;
 +
</pre>
 +
=== 2.2.3 DML ===
 +
<pre>1. 插入记录
 +
insert into emp(ename,hiredate,sal,deptno) values('evan','2018-01-01','2333',1);
 +
 
 +
MariaDB [test1]> insert into emp(ename,hiredate,sal,deptno) values('hen','2017-09-08','3333',3);
 +
Query OK, 1 row affected (0.011 sec)
 +
 
 +
MariaDB [test1]> insert into emp(ename,hiredate,sal,deptno) values('lisa','2017-07-03','3433',2);
 +
Query OK, 1 row affected (0.015 sec)
 +
 
 +
MariaDB [test1]> insert into emp(ename,hiredate,sal,deptno) values('gjg','2014-07-06','4433',1);
 +
Query OK, 1 row affected (0.005 sec)
 +
 
 +
 
 +
 
 +
 
 +
MariaDB [test1]> select * from emp;
 +
+-------+------------+---------+--------+------+
 +
| ename | hiredate  | sal    | deptno | age  |
 +
+-------+------------+---------+--------+------+
 +
| evan  | 2018-01-01 | 2333.00 |      1 | NULL |
 +
+-------+------------+---------+--------+------+
 +
 
 +
可以一次插入多条记录
 +
 
 +
2.更新记录
 +
 
 +
MariaDB [test1]> update  emp set sal=8000 where ename='evan';
 +
Query OK, 1 row affected (0.017 sec)
 +
Rows matched: 1  Changed: 1  Warnings: 0
 +
 
 +
MariaDB [test1]> select * from emp;
 +
+-------+------------+---------+--------+------+
 +
| ename | hiredate  | sal    | deptno | age  |
 +
+-------+------------+---------+--------+------+
 +
| evan  | 2018-01-01 | 8000.00 |      1 | NULL |
 +
+-------+------------+---------+--------+------+
 +
 
 +
3. 删除记录
 +
drop from emp where ename='evan';
 +
 
 +
 
 +
4. 查询记录
 +
(2) 条件查询
 +
MariaDB [test1]> select * from emp where ename='evan';
 +
+-------+------------+---------+--------+------+
 +
| ename | hiredate  | sal    | deptno | age  |
 +
+-------+------------+---------+--------+------+
 +
| evan  | 2018-01-01 | 8000.00 |      1 | NULL |
 +
 
 +
(3) 排序和限制
 +
MariaDB [test1]> select * from emp order by sal;
 +
+-------+------------+---------+--------+------+
 +
| ename | hiredate  | sal    | deptno | age  |
 +
+-------+------------+---------+--------+------+
 +
| hen  | 2017-09-08 | 3333.00 |      3 | NULL |
 +
| lisa  | 2017-07-03 | 3433.00 |      2 | NULL |
 +
| gjg  | 2014-07-06 | 4433.00 |      1 | NULL |
 +
| evan  | 2018-01-01 | 8000.00 |      1 | NULL |
 +
+-------+------------+---------+--------+------+
 +
 
 +
MariaDB [test1]> select * from emp order by sal  limit 1,3; #从第二条记录开始 ,显示屏条
 +
+-------+------------+---------+--------+------+
 +
| ename | hiredate  | sal    | deptno | age  |
 +
+-------+------------+---------+--------+------+
 +
| lisa  | 2017-07-03 | 3433.00 |      2 | NULL |
 +
| gjg  | 2014-07-06 | 4433.00 |      1 | NULL |
 +
| evan  | 2018-01-01 | 8000.00 |      1 | NULL |
 +
+-------+------------+---------+--------+------+
 +
 
 +
(4)聚合
 +
 
 +
MariaDB [test1]> select count(1) from emp ;
 +
+----------+
 +
| count(1) |
 +
+----------+
 +
|        4 |
 +
+----------+
 +
1 row in set (0.000 sec)
 +
 
 +
MariaDB [test1]> select  sum(sal),max(sal),min(sal) from emp;
 +
+----------+----------+----------+
 +
| sum(sal) | max(sal) | min(sal) |
 +
+----------+----------+----------+
 +
| 19199.00 |  8000.00 |  3333.00 |
 +
+----------+----------+----------+
 +
 
 +
统计各个部门的人数
 +
select  count(id)  from emp group by deptno;
 +
 
 +
(5) 表连接
 +
 
 +
create table dept(deptno int(8),deptname varchar(10));
 +
 
 +
insert into dept(deptno,deptname) values(1,'tech');
 +
insert into dept(deptno,deptname) values(2,'sale');
 +
insert into dept(deptno,deptname) values(3,'hr');
 +
 
 +
MariaDB [test1]> select * from dept;
 +
+--------+----------+
 +
| deptno | deptname |
 +
+--------+----------+
 +
|      1 | tech    |
 +
|      2 | sale    |
 +
|      3 | hr      |
 +
+--------+----------+
 +
所有员工的名字和所有的部门名称
 +
MariaDB [test1]> select ename,deptname from emp , dept where emp.deptno=dept.deptno;
 +
+-------+----------+
 +
| ename | deptname |
 +
+-------+----------+
 +
| evan  | tech    |
 +
| hen  | hr      |
 +
| lisa  | sale    |
 +
| gjg  | tech    |
 +
+-------+----------+
 +
 
 +
(6)子查询
 +
 
 +
(7) 记录查询
 +
</pre>
 +
 
 +
=== 2.2.4 DCL 语句 ===
 +
grant revoke
 +
 
 +
== 2.3 use help==
 +
<pre>MariaDB [test1]> ? contents
 +
You asked for help about help category: "Contents"
 +
For more information, type 'help <item>', where <item> is one of the following
 +
categories:
 +
  Account Management
 +
  Administration
 +
  Compound Statements
 +
  Data Definition
 +
  Data Manipulation
 +
 
 +
想知道int类型的相关
 +
MariaDB [test1]> ? int
 +
Name: 'INT'
 +
Description:
 +
INT[(M)] [UNSIGNED] [ZEROFILL]
 +
 
 +
A normal-size integer. The signed range is -2147483648 to 2147483647.
 +
The unsigned range is 0 to 4294967295.
 +
 
 +
URL: https://mariadb.com/kb/en/int/
 +
 
 +
 
 +
 
 +
MariaDB [test1]> ? show
 +
Name: 'SHOW'
 +
Description:
 +
SHOW has many forms that provide information about databases, tables,
 +
columns, or status information about the server. This section describes
 +
those following:
 +
 
 +
SHOW AUTHORS
 +
SHOW {BINARY | MASTER} LOGS
 +
 
 +
 
 +
MariaDB [test1]> ? create table
 +
Name: 'CREATE TABLE'
 +
Description:
 +
Syntax:
 +
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
 +
    (create_definition,...)
 +
    [table_options]
 +
    [partition_options]
 +
 
 +
Or:
 +
 
 +
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
 +
    [(create_definition,...)]
 +
    [table_options]
 +
    [partition_options]
 +
    select_statement
 +
 
 +
</pre>
  
http://www.cnblogs.com/EE-NovRain/p/3268476.html
 
 
=see also=
 
=see also=
[https://juejin.im/entry/59fbd679518825188e51472b Redis 和 Memcached 的区别在哪里]
+
[https://www.cnblogs.com/fan-yuan/p/7879353.html  DQL、DML、DDL、DCL的概念与区别]
 +
 
 +
[https://blog.csdn.net/level_level/article/details/4248685  DML、DDL、DCL区别]
 +
 
  
  
  
  
[[category:ops]]
+
[[category:mysql]]

2019年10月14日 (一) 13:52的最新版本

sql 基础

2.2.1 SQL分类

DDL  DML  DCL
DML(data manipulation language):数据操纵语言
       它们是SELECT、UPDATE、INSERT、DELETE,就象它的名字一样,这4条命令是用来对数据库里的数据进行操作的语言
DDL(data definition language):数据定义语言
       DDL比DML要多,主要的命令有CREATE、ALTER、DROP等,DDL主要是用在定义或改变表(TABLE)的结构,数据类型,表之间的链接和约束等初始化工作上,他们大多在建立表时使用
DCL(Data Control Language):数据控制语言
       是数据库控制功能。是用来设置或更改数据库用户或角色权限的语句,包括(grant,deny,revoke等)语句。在默认状态下,只有sysadmin,dbcreator,db_owner或db_securityadmin等人员才有权力执行DCL

2.2.2 DDL

1.创建数据库
 create database test1 character set utf8;

MariaDB [(none)]> use test1;
Database changed
MariaDB [test1]> show tables;
Empty set (0.001 sec)

2. 删除数据库

MariaDB [mysql]> drop database test1;
Query OK, 0 rows affected (0.001 sec)

drop 语句都是显示为0 rows affected (0.001 sec)

3. 创建表
 create table emp(ename varchar(10), hiredate date,sal decimal(10,2),deptno int(20));

MariaDB [test1]> desc emp;
+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename    | varchar(10)   | YES  |     | NULL    |       |
| hiredate | date          | YES  |     | NULL    |       |
| sal      | decimal(10,2) | YES  |     | NULL    |       |
| deptno   | int(20)       | YES  |     | NULL    |       |
+----------+---------------+------+-----+---------+-------+
4 rows in set (0.004 sec)

MariaDB [test1]> show create table emp \G;
*************************** 1. row ***************************
       Table: emp
Create Table: CREATE TABLE `emp` (
  `ename` varchar(10) DEFAULT NULL,
  `hiredate` date DEFAULT NULL,
  `sal` decimal(10,2) DEFAULT NULL,
  `deptno` int(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.000 sec)

4. 删除表
drop table tablename;

5. 修改表
MariaDB [test1]> desc emp;
+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename    | varchar(10)   | YES  |     | NULL    |       |
+----------+---------------+------+-----+---------+-------+

MariaDB [test1]> alter table  emp modify ename varchar(20);

MariaDB [test1]> desc emp;
+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename    | varchar(20)   | YES  |     | NULL    |       |


(2)增加字段  

MariaDB [test1]> alter table emp add column age int(3);

MariaDB [test1]> desc emp;
+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename    | varchar(20)   | YES  |     | NULL    |       |

| age      | int(3)        | YES  |     | NULL    |       |
+----------+---------------+------+-----+---------+-------+


(3)删除字段
alter table tablename drop  column col_name;

(4) 字段改名
alter  table emp change age age1  int(4);

change modify 的区别p 33 

(5) 修改字段排列顺序

alter table emp add birthd after ename;
 alter table emp modify age int(3) first;

(6) 更改表名 
alter table emp rename emp1;

2.2.3 DML

1. 插入记录
insert into emp(ename,hiredate,sal,deptno) values('evan','2018-01-01','2333',1);

MariaDB [test1]> insert into emp(ename,hiredate,sal,deptno) values('hen','2017-09-08','3333',3);
Query OK, 1 row affected (0.011 sec)

MariaDB [test1]> insert into emp(ename,hiredate,sal,deptno) values('lisa','2017-07-03','3433',2);
Query OK, 1 row affected (0.015 sec)

MariaDB [test1]> insert into emp(ename,hiredate,sal,deptno) values('gjg','2014-07-06','4433',1);
Query OK, 1 row affected (0.005 sec)




MariaDB [test1]> select * from emp;
+-------+------------+---------+--------+------+
| ename | hiredate   | sal     | deptno | age  |
+-------+------------+---------+--------+------+
| evan  | 2018-01-01 | 2333.00 |      1 | NULL |
+-------+------------+---------+--------+------+

可以一次插入多条记录

2.更新记录

MariaDB [test1]> update  emp set sal=8000 where ename='evan';
Query OK, 1 row affected (0.017 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [test1]> select * from emp;
+-------+------------+---------+--------+------+
| ename | hiredate   | sal     | deptno | age  |
+-------+------------+---------+--------+------+
| evan  | 2018-01-01 | 8000.00 |      1 | NULL |
+-------+------------+---------+--------+------+

3. 删除记录
drop from emp where ename='evan';


4. 查询记录
(2) 条件查询
MariaDB [test1]> select * from emp where ename='evan';
+-------+------------+---------+--------+------+
| ename | hiredate   | sal     | deptno | age  |
+-------+------------+---------+--------+------+
| evan  | 2018-01-01 | 8000.00 |      1 | NULL |

(3) 排序和限制
MariaDB [test1]> select * from emp order by sal; 
+-------+------------+---------+--------+------+
| ename | hiredate   | sal     | deptno | age  |
+-------+------------+---------+--------+------+
| hen   | 2017-09-08 | 3333.00 |      3 | NULL |
| lisa  | 2017-07-03 | 3433.00 |      2 | NULL |
| gjg   | 2014-07-06 | 4433.00 |      1 | NULL |
| evan  | 2018-01-01 | 8000.00 |      1 | NULL |
+-------+------------+---------+--------+------+

MariaDB [test1]> select * from emp order by sal  limit 1,3; #从第二条记录开始 ,显示屏条
+-------+------------+---------+--------+------+
| ename | hiredate   | sal     | deptno | age  |
+-------+------------+---------+--------+------+
| lisa  | 2017-07-03 | 3433.00 |      2 | NULL |
| gjg   | 2014-07-06 | 4433.00 |      1 | NULL |
| evan  | 2018-01-01 | 8000.00 |      1 | NULL |
+-------+------------+---------+--------+------+

(4)聚合

MariaDB [test1]> select count(1) from emp ;
+----------+
| count(1) |
+----------+
|        4 |
+----------+
1 row in set (0.000 sec)

MariaDB [test1]> select  sum(sal),max(sal),min(sal) from emp;
+----------+----------+----------+
| sum(sal) | max(sal) | min(sal) |
+----------+----------+----------+
| 19199.00 |  8000.00 |  3333.00 |
+----------+----------+----------+

统计各个部门的人数
select  count(id)  from emp group by deptno;

(5) 表连接

create table dept(deptno int(8),deptname varchar(10));

insert into dept(deptno,deptname) values(1,'tech');
insert into dept(deptno,deptname) values(2,'sale');
insert into dept(deptno,deptname) values(3,'hr');

MariaDB [test1]> select * from dept;
+--------+----------+
| deptno | deptname |
+--------+----------+
|      1 | tech     |
|      2 | sale     |
|      3 | hr       |
+--------+----------+
所有员工的名字和所有的部门名称 
MariaDB [test1]> select ename,deptname from emp , dept where emp.deptno=dept.deptno;
+-------+----------+
| ename | deptname |
+-------+----------+
| evan  | tech     |
| hen   | hr       |
| lisa  | sale     |
| gjg   | tech     |
+-------+----------+

(6)子查询

(7) 记录查询

2.2.4 DCL 语句

grant revoke 

2.3 use help

MariaDB [test1]> ? contents 
You asked for help about help category: "Contents"
For more information, type 'help <item>', where <item> is one of the following
categories:
   Account Management
   Administration
   Compound Statements
   Data Definition
   Data Manipulation

 想知道int类型的相关
MariaDB [test1]> ? int
Name: 'INT'
Description:
INT[(M)] [UNSIGNED] [ZEROFILL]

A normal-size integer. The signed range is -2147483648 to 2147483647.
The unsigned range is 0 to 4294967295.

URL: https://mariadb.com/kb/en/int/



MariaDB [test1]> ? show 
Name: 'SHOW'
Description:
SHOW has many forms that provide information about databases, tables,
columns, or status information about the server. This section describes
those following:

SHOW AUTHORS
SHOW {BINARY | MASTER} LOGS


MariaDB [test1]> ? create table
Name: 'CREATE TABLE'
Description:
Syntax:
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    (create_definition,...)
    [table_options]
    [partition_options]

Or:

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    [(create_definition,...)]
    [table_options]
    [partition_options]
    select_statement

see also

DQL、DML、DDL、DCL的概念与区别

DML、DDL、DCL区别