1. Mysql基础

SQL的分类：

1、DDL—数据定义语言（Data Define Language）：create（创建），alter（修改），drop（删除），TRUNCATE（截断），RENAME（重命名）；
2、DML—数据操纵语言（Data Manipulation Language）：select（查询），delete（删除），update（更新），insert（新增）；
3、DCL—数据控制语言（Data Control Language）：grant（添加权限），revoke（回收权限）；

DDL操作：
注意：使用DDL语言时，必须在动词后跟上数据库对象名词（例如：TABLE、VIEW、INDEX、SCHEMA、TRIGGER等）。
数据库的链接

# mysql -h 127.0.0.1 -u root -p
  • 常用的参数解释：
-A 不预读数据库信息，提高连接和切换数据库速度,使用--disable-auto-rehash代替
--default-character-set  使用的默认字符集
-e 执行命令并退出
-h 主机地址
-p 连接到服务器时使用的密码
-P 连接的端口号
  • 创建数据库：
create database test1 ;
  • 查看数据库：
show databases;
  • 选择数据库：
use mysql;
  • 删除数据库：
drop database test1;
  • 创建表：
CREATE  [TEMPORARY]  TABLE  [IF NOT EXISTS] [database_name.] <table_name>
(
  <column_name>  <data_type>  [[not] null],…
)
  • 注：TEMPORARY：指明创建临时表

    　　
    查看定义：

　　IF NOT EXISTS：如果要创建的表已经存在，强制不显示错误消息
　　database_name：数据库名
　　table_name：表名
　　column_name：列名
　　data_type：数据类型

desc emp;
  • 查看创建的表：
show create table emp ;
  • 更新表名：
 alter table emp rename users;
  • 删除表：
drop table emp;

 

  • 修改表字段：
alter table emp modify ename varchar(30);
  • 增加表字段：
alter table emp add column age int(3);
  • 修改表字段：
alter table emp change age age int(4);
  • 删除表字段：
alter table emp drop column age;
  • change和modify:
前者可以修改列名称,后者不能. 
change需要些两次列名称.
  • 字段增加修改 add/change/modify/ 添加顺序:
add 增加在表尾.
change/modify 不该表字段位置.
修改字段可以带上以下参数进行位置调整(frist/after column_name);

alter table emp change age age int(2) after ename;
alter table emp change age age int(3) first;

DML语句:
插入记录:

//指定字段,
//自增,默认值等字段可以不用列出来,没有默认值的为自动设置为NULL
insert into emp (ename,hiredate,sal,deptno) values ('jack','2000-01-01','2000',1);

//可以不指定字段,但要一一对应
insert into emp values ('lisa','2010-01-01','8000',2);

批量记录:

insert into emp values ('jack chen','2011-01-01','18000',2),('andy lao','2013-01-01','18000',2);
  • 更新记录:
update emp set sal="7000.00" where ename="jack";

update emp e,dept d set e.sal="10000",d.deptname=e.ename where e.deptno=d.deptno and e.ename="lisa";
  • 删除记录:
//请仔细检查where条件,慎重
delete from emp where ename='jack';
  • 查看记录:
//查看所有字段
select * from emp;

//查询不重复记录
select distinct(deptno) from emp ;
select distinct(deptno),emp.* from emp ;

//条件查询
//比较运算符: > < >= <= <> != ...
//逻辑运算符: and or ...
select * from emp where sal="18000" and deptno=2;
//查询表中记录总数
select count(*) from 表名;

排序

//desc降序,asc 升序(默认)
select * from emp order by deptno ;
select * from emp order by deptno asc;
select * from emp order by deptno desc,sal desc;

根据id获取最后一条记录(同理第一条用asc)

select * from 表名 order by id DESC limit 1;

 

  • 限制记录数:
select * from emp limit 1;
select * from emp limit 100,10;
select * from emp order by deptno desc,sal desc limit 1;
  • 聚合:函数:count():记录数 / sum(总和); / max():最大值 / min():最小值
select count(id) from emp ;
select sum(sal) from emp ;
select max(sal) from emp ;
select min(sal) from emp ;
  • group by分组：
//分组统计
select count(deptno) as count from emp group by deptno;
select count(deptno) as count,deptno from emp group by deptno;
select count(deptno) as count,deptno,emp.* from emp group by deptno;
  • having 对分组结果二次过滤：
select count(deptno) as count,deptno from emp group by deptno having count > 2;
  • with rollup 对分组结果二次汇总：
select count(sal),emp.*  from emp group by sal, deptno with rollup ;
  • 表连接：
  • left join :左连接，返回左表中所有的记录以及右表中连接字段相等的记录；
  • right join :右连接，返回右表中所有的记录以及左表中连接字段相等的记录；
  • inner join: 内连接，又叫等值连接，只返回两个表中连接字段相等的行；
  • full join:外连接，返回两个表中的行：left join + right join；
  • cross join:结果是笛卡尔积，就是第一个表的行数乘以第二个表的行数。

内连接:只返回两个表中连接字段相等的行

select * from emp as e,dept as d where e.deptno=d.deptno;
select * from emp as e inner join dept as d on e.deptno=d.deptno;
  • 左外连接:包含左表中所有的记录以及右表中连接字段相等的记录
select * from emp as e left join dept as d on e.deptno=d.deptno;
  • 右外连接:包含右表中所有的记录以及左表中连接字段相等的记录
select * from emp as e right join dept as d on e.deptno=d.deptno;
  • 子查询：
//=, !=
select * from emp where deptno = (select deptno from dept where deptname="技术部");
select * from emp where deptno != (select deptno from dept where deptname="技术部");

//in, not in 
//当需要使用里面的结果集的时候必须用in(); 
select * from emp where deptno in (select deptno from dept where deptname="技术部");
select * from emp where deptno not in (select deptno from dept where deptname="技术部");

//exists , not exists
//当需要判断后面的查询结果是否存在时使用exists();
select * from emp where exists (select deptno from dept where deptno > 5);
select * from emp where not exists (select deptno from dept where deptno > 5);
  • 记录联合:
union:返回去重之后的结果
select ename from emp union select ename from emp;

union all:返回所有结果
select ename from emp union all select ename from emp;
  • DCL语句:

添加权限:

grant select,insert on test.* to 'db_user_1'@'localhost'  identified by '123456';
flush privileges;
  • 回收权限：
revoke insert on test.* from 'db_user_1'@'localhost';

批量替换replace

update 表名
set 字段名=REPLACE (字段名,'原来的值','要修改的值')

例如把\替换成/,因为\为mysql转义符,所以用两个// 

update file set path=REPLACE(path, "\\", "/");

​​​​​​

2. Mysql数据类型

整数类型：

指定宽度:
指定显示的宽度为5,不影响实际数据

create table t1 (id int , id2 int(5));

zerofill:
采用零填充,不足5位采用0填充，配合数据宽度

create table t2 (id int , id2 int(5) zerofill);
  • unsigned:
create table t3 (id int , id2 int(5) unsigned);
  • auto_increment:

只用于整数类型
产生唯一标识
值从1开始,逐行增加
一个表中最多只能存在一个自增列
自增列应该定义为not null
自增列应该这只为 primary key 或者 unique

id int not null auto_increment primary key

浮点数类型

定点数类型

float , double , decimal 特点:
1.(m,d)表示方式:m指的是整数位,d指的是小数位(又称作精度和标度)
2.float/double四舍五入丢失精度,decimal会截断数据并输出warning
3.如果不指定精度,float/double采用操作系统默认,decimal则是(10,0)
位类型

1 存放位字段值
2 指定存放多位二进制的长度,默认为1(范围:1~64)
3 读取需要bin()/hex(),普通的select读取结果为null
4 插入的值会转化为二进制码,如果长度运行则正常处理,否则插入失败

create table t6 (id bit(1));
select bin(id) from t6;
  • 日期和时间类型


当前系统日期

timestamp:返回yyyy-mm-dd hh:mm:ss 宽度19

timestamp:不适合存放久远日期,超出范围则会采用零值填充

//不同格式的显示零值格式
d date, t time,dt datetime
+------------+----------+---------------------+
| d          | t        | dt                  |
+------------+----------+---------------------+
| 2016-11-25 | 14:52:44 | 2016-11-25 14:52:44 |
+------------+----------+---------------------+

//默认值的体现
id1 timestamp
+---------------------+
| id1                 |
+---------------------+
| 2016-11-25 14:55:45 |
+---------------------+

//timestamp字段只能有一个"CURRENT_TIMESTAMP"
+-------+-----------+------+-----+---------------------+-----------------------------+
| Field | Type      | Null | Key | Default             | Extra                       |
+-------+-----------+------+-----+---------------------+-----------------------------+
| id1   | timestamp | NO   |     | CURRENT_TIMESTAMP   | on update CURRENT_TIMESTAMP |
| id2   | timestamp | NO   |     | 0000-00-00 00:00:00 |                             |
+-------+-----------+------+-----+---------------------+-----------------------------+

//timestamp和时区相关:SYSTEM 指的是和主机时区保持一致
show variables like "%_zone";
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | CST    |
| time_zone        | SYSTEM |
+------------------+--------+

//修改时区
set time_zone="+9:00"/在 [mysqld] 之下加 default-time-zone=timezone

年份

year:默认为4位格式.1901~2155和0000. 2位的已经不推荐,高版本已经不支持了.
  • timestamp和datetime区别:

1、timestamp支持范围小(1970-01-01 08:00:01到2038年某个点)
2、表中第一个timestamp字段,会默认采用当前系统时间.如果更新其他字段,该字段没有赋值的话,则该字段会自动更新.如果指定字段不满足规格,则采用零值填充
3、timestamp查询和插入都会受到当地时区影响

datetime支持范围宽度大(1000-01-01 00:00:00到9999-12-31 23:23:59)

字符串类型

char和varchar的区别:

  • char定长，效率高,在创建字段的时候就已经指定,一般用于固定长度的表单提交数据存储
  • char在检索的时候回去掉尾部的空格
  • varchar是动态长度
  • varchar在检索的时候回保留尾部的空格
  • varchar将实际内容单独存储在聚簇索引之外，内容开头用1到2个字节表示实际长度(长度>255时需要2个字节)
  • 字符类型若为gbk，每个字符最多占2个字节，字符类型若为utf8，每个字符最多占3个字节

枚举类型:

  • 枚举在集合中取值,只能取一个
  • 如果值不存在则采用第一个值为默认
  • 如果插入NULL不会出错,会将NULL写入表
  • 成员个数在1255占用1个字节,在25565535占用2个字节,节约资源
create table `t8` (
    `gender` enum('m','f') default null
) engine=innodb default charset=utf8
  • 集合类型
  • 集合类型和枚举很像,但是支持多值选择
  • 最多可以保存64个成员,每8个成员占1个字节
  • 重复值只会插入一次,如果查出集合范围则插入为null
create table t9 (col set ('a','b','c','d'));

​​​​

3. Mysql运算符

本章节我们主要介绍 MySQL 的运算符及运算符的优先级。 MySQL 主要有以下几种运算符：

  • 算术运算符
  • 比较运算符
  • 逻辑运算符
  • 位运算符

算术运算符

MySQL 支持的算术运算符包括:

运算符 作用
+ 加法
- 减法
* 乘法
/ 或 DIV 除法
% 或 MOD 取余

在除法运算和模运算中，如果除数为0，将是非法除数，返回结果为NULL。

1、加

mysql> select 1+2;
+-----+
| 1+2 |
+-----+
|   3 |
+-----+

2、减

mysql> select 1-2;
+-----+
| 1-2 |
+-----+
|  -1 |
+-----+

3、乘

mysql> select 2*3;
+-----+
| 2*3 |
+-----+
|   6 |
+-----+

4、除

mysql> select 2/3;
+--------+
| 2/3    |
+--------+
| 0.6667 |
+--------+

5、商

mysql> select 10 DIV 4;
+----------+
| 10 DIV 4 |
+----------+
|        2 |
+----------+

6、取余

mysql> select 10 MOD 4;
+----------+
| 10 MOD 4 |
+----------+
|        2 |
+----------+

比较运算符

SELECT 语句中的条件语句经常要使用比较运算符。通过这些比较运算符，可以判断表中的哪些记录是符合条件的。比较结果为真，则返回 1，为假则返回 0，比较结果不确定则返回 NULL。

符号 描述 备注
= 等于  
<>, != 不等于  
> 大于  
< 小于  
<= 小于等于  
>= 大于等于  
BETWEEN 在两值之间 >=min&&<=max
NOT BETWEEN 不在两值之间  
IN 在集合中  
NOT IN 不在集合中  
<=> 严格比较两个NULL值是否相等 两个操作码均为NULL时，其所得值为1；而当一个操作码为NULL时，其所得值为0
LIKE 模糊匹配  
REGEXP 或 RLIKE 正则式匹配  
IS NULL 为空  
IS NOT NULL 不为空  

1、等于

mysql> select 2=3;
+-----+
| 2=3 |
+-----+
|   0 |
+-----+


mysql> select NULL = NULL;
+-------------+
| NULL = NULL |
+-------------+
|        NULL |
+-------------+

2、不等于

mysql> select 2<>3;
+------+
| 2<>3 |
+------+
|    1 |
+------+

3、安全等于

与 = 的区别在于当两个操作码均为 NULL 时，其所得值为 1 而不为 NULL，而当一个操作码为 NULL 时，其所得值为 0而不为 NULL。

mysql> select 2<=>3;
+-------+
| 2<=>3 |
+-------+
|     0 |
+-------+


mysql> select null=null;
+-----------+
| null=null |
+-----------+
|      NULL |
+-----------+

        
mysql> select null<=>null;
+-------------+
| null<=>null |
+-------------+
|           1 |
+-------------+

4、小于

mysql> select 2<3;
+-----+
| 2<3 |
+-----+
|   1 |
+-----+

5、小于等于

mysql> select 2<=3;
+------+
| 2<=3 |
+------+
|    1 |
+------+

6、大于

mysql> select 2>3;
+-----+
| 2>3 |
+-----+
|   0 |
+-----+

7、大于等于

mysql> select 2>=3;
+------+
| 2>=3 |
+------+
|    0 |
+------+

8、BETWEEN

mysql> select 5 between 1 and 10;
+--------------------+
| 5 between 1 and 10 |
+--------------------+
|                  1 |
+--------------------+

9、IN

mysql> select 5 in (1,2,3,4,5);
+------------------+
| 5 in (1,2,3,4,5) |
+------------------+
|                1 |
+------------------+

10、NOT IN

mysql> select 5 not in (1,2,3,4,5);
+----------------------+
| 5 not in (1,2,3,4,5) |
+----------------------+
|                    0 |
+----------------------+

11、IS NULL

mysql> select null is NULL;
+--------------+
| null is NULL |
+--------------+
|            1 |
+--------------+

mysql> select 'a' is NULL;
+-------------+
| 'a' is NULL |
+-------------+
|           0 |
+-------------+

12、IS NOT NULL

mysql> select null IS NOT NULL;
+------------------+
| null IS NOT NULL |
+------------------+
|                0 |
+------------------+

        
mysql> select 'a' IS NOT NULL;
+-----------------+
| 'a' IS NOT NULL |
+-----------------+
|               1 |
+-----------------+

13、LIKE

mysql> select '12345' like '12%';
+--------------------+
| '12345' like '12%' |
+--------------------+
|                  1 |
+--------------------+

mysql> select '12345' like '12_';
+--------------------+
| '12345' like '12_' |
+--------------------+
|                  0 |
+--------------------+

14、REGEXP

mysql> select 'beijing' REGEXP 'jing';
+-------------------------+
| 'beijing' REGEXP 'jing' |
+-------------------------+
|                       1 |
+-------------------------+

mysql> select 'beijing' REGEXP 'xi';
+-----------------------+
| 'beijing' REGEXP 'xi' |
+-----------------------+
|                     0 |
+-----------------------+

逻辑运算符

逻辑运算符用来判断表达式的真假。如果表达式是真，结果返回 1。如果表达式是假，结果返回 0。

运算符号 作用
NOT 或 ! 逻辑非
AND 逻辑与
OR 逻辑或
XOR 逻辑异或

1、与

mysql> select 2 and 0;
+---------+
| 2 and 0 |
+---------+
|       0 |
+---------+

        
mysql> select 2 and 1;   
+---------+     
| 2 and 1 |      
+---------+      
|       1 |      
+---------+

2、或

mysql> select 2 or 0;
+--------+
| 2 or 0 |
+--------+
|      1 |
+--------+

mysql> select 2 or 1;
+--------+
| 2 or 1 |
+--------+
|      1 |
+--------+

mysql> select 0 or 0;
+--------+
| 0 or 0 |
+--------+
|      0 |
+--------+

mysql> select 1 || 0;
+--------+
| 1 || 0 |
+--------+
|      1 |
+--------+

3、非

mysql> select not 1;
+-------+
| not 1 |
+-------+
|     0 |
+-------+

mysql> select !0;
+----+
| !0 |
+----+
|  1 |
+----+

4、异或

mysql> select 1 xor 1;
+---------+
| 1 xor 1 |
+---------+
|       0 |
+---------+

mysql> select 0 xor 0;
+---------+
| 0 xor 0 |
+---------+
|       0 |
+---------+

mysql> select 1 xor 0;
+---------+
| 1 xor 0 |
+---------+
|       1 |
+---------+

mysql> select null or 1;
+-----------+
| null or 1 |
+-----------+
|         1 |
+-----------+

mysql> select 1 ^ 0;
+-------+
| 1 ^ 0 |
+-------+
|     1 |
+-------+

位运算符

位运算符是在二进制数上进行计算的运算符。位运算会先将操作数变成二进制数，进行位运算。然后再将计算结果从二进制数变回十进制数。

运算符号 作用
& 按位与
| 按位或
^ 按位异或
! 取反
<< 左移
>> 右移

1、按位与

mysql> select 3&5;
+-----+
| 3&5 |
+-----+
|   1 |
+-----+

2、按位或

mysql> select 3|5;
+-----+
| 3|5 |
+-----+
|   7 |
+-----+

3、按位异或

mysql> select 3^5;
+-----+
| 3^5 |
+-----+
|   6 |
+-----+

4、按位取反

mysql> select ~18446744073709551612;
+-----------------------+
| ~18446744073709551612 |
+-----------------------+
|                     3 |
+-----------------------+

5、按位右移

mysql> select 3>>1;
+------+
| 3>>1 |
+------+
|    1 |
+------+

6、按位左移

mysql> select 3<<1;
+------+
| 3<<1 |
+------+
|    6 |
+------+

运算符优先级

最低优先级为： :=。

优先级顺序 运算符
1  :=
2 ll,OR,XOR
3 &&,AND
4 NOT
5 BETWEEN,CASE，WHEN,THEN,ELSE
6 =，<=>,>=,>,<=,<,<>,!=,IS,LIKE,REGEXP,IN
7 |
8 &
9 <<,>>
10 －，＋
11 *,/,DIV,%,MOD
12 ^
13 -(一元减号),～(一元比特反转)
14 !

最高优先级为： !、BINARY、 COLLATE。

四、Mysql常用的函数

五、MySQL管理工具和应用程序

六、Mysql存储引擎

七、常用数据类型的选择

八、Mysql 字符集

九、Mysql索引设计和使用

十、Mysql中的视图

十一、事务控制和锁定

十二、其它
1、mysql–修改自增主键的初始值

alter table  m_money_process  AUTO_INCREMENT =10;
  • 参考：

在此非常感谢以下技术博文
来源：简书 战神悟空-Mysql 基础