Mysql基础知识整

web项目部署

Java项目使用的web服务器：Tomcat、weblogic、webshare、jetty

Php、python使用的web服务器：nginx、apache

搭建环境过程：　　　　　　　　　　　　　　　　　　　　　　部署、发版过程

1.安装依赖软件　　　　　　　　　　　　　　　　　　　　　　1.更新源代码，打包

2.获取代码、编译　　　　　　　　　　　　　　　　　　　　　2.修改配置文件

3.导入基础数据　　　　　　　　　　　　　　　　　　　　　　3.替换成最新的代码

4.修改配置文件(数据库地址、redis地址、mq地址)　　　　　　4.重启web服务器

5.启动web服务器

 

Mysql是现在公司使用比较多的一款数据库，Oracle虽然功能强大，但是费用成本高，而Mysql是一款开源的数据库，现在已经被越来越多的公司推行使用，今天就把对于工作中经常使用的sql语句进行整理，方便以后查询使用。

sql语句分为三个部分：

　　数据定义语言（Data Definition Language，简称为DDL）：CREATE、ALTER、DROP

　　数据操作语言（Data Manipulation Language，简称为DML）：update、insert、delete、select

　　数据控制语言（Data Control Language，简称为DCL）：用来设置或更改数据库用户或角色权限的语句，包括grant,deny,revoke等）语句

一、Mysql的常用数据类型

　（1）整数类型：其中int为最常用的数据类型，标准SQL中支持INTEGER和SMALLINT这两类整数类型。MySQL数据库除了支持这两种类型以外，还扩展支持了TINYINT、MEDIUMINT和BIGINT。

　　

（2）浮点数类型和定点数类型：浮点数类型包括单精度浮点数（FLOAT型）和双精度浮点数（DOUBLE型）。定点数类型就是DECIMAL型。

　　

（3）日期类型和时间类型：

date表示日期，默认格式为‘YYYY-MM-DD’；
time表示时间，格式为‘HH:ii:ss’；
year表示年份；
datetime与timestamp是日期和时间的混合类型，格式为'YYYY-MM-DD HH:ii:ss'，区别在于：

　　1、datetime的取值范围大于timestamp的取值范围

　　2、将NULL插入timestamp字段后，该字段的值实际上是MySQL服务器当前的日期和时间。

　　3、同一个timestamp类型的日期或时间，不同的时区，显示结果不同。

Timestamp是时间戳，通常是一个字符序列，唯一地标识某一刻的时间，它的范围是1970-2037年

（4）字符串类型

　　字符串类型包括CHAR、VARCHAR、BLOB、TEXT、ENUM和SET。

　　char()和varchar()的区别是，varchar(255)表示可以存储255个汉字，而每个汉字占用两个字节的存储空间。假如这个字符串没有那么多汉字，例如仅仅包含一个‘中’字，那么varchar(255)仅仅占用1个字符（两个字节）的储存空间；而char(255)则必须占用255个字符长度的存储空间，哪怕里面只存储一个汉字。

（5）二进制类型

　　二进制类型的字段主要用于存储由‘0’和‘1’组成的字符串，因此从某种意义上将，二进制类型的数据是一种特殊格式的字符串。　　

二进制类型与字符串类型的区别在于：字符串类型的数据按字符为单位进行存储，因此存在多种字符集、多种字符序；而二进制类型的数据按字节为单位进行存储，仅存在二进制字符集binary。

二、Mysql常用命令

（1）DDL语句

　　显示所有数据库：show databases;

　　选定默认数据库：use dbname;

　　显示默认数据库中所有表：show tables;

　　创建数据库：create database 数据库名 (CHARSET=utf8);  CHARSET=utf8表示创建机字符集为utf-8，如果不添加此设置在数据库中添加表的数据，会导致中文显示乱码的问题；

　　显示数据库结构：show create database 数据库名;

　　删除数据库：drop database 数据库名; 删除数据库会删除数据库中所有的表，数据，索引等信息。所以要确认清楚后操作；

　　创建表：

create table 表名（
列名1 列类型 [<列的完整性约束>],
列名2 列类型 [<列的完整性约束>],
 ... ... );

主键：
定义：唯一标识一条记录，不能有重复的，不允许为空，主键只能有一个。
作用：用来保证数据完整性。
外键：
定义：表的外键是另一表的主键, 外键可以有重复的, 可以是空值，一个表可以有多个外键。
作用：用来和其他表建立联系用的。

常见的唯一性约束条件

PRIMARY KEY 主码约束(主键)
UNIQUE 唯一性约束
NOT NULL 非空值约束
AUTO_INCREMENT 用于整数列默认自增1
DEFAULT default_value 默认值约束

查看表结构

　　desc 表名;

　　show create table 表名;

修改表名

　　ALTER TABLE 旧表名 RENAME 新表名;

修改字段的数据类型

　　ALTER TABLE 表名 MODIFY 属性名 数据类型 ;

　　ALTER TABLE 表名 CHANGE 旧属性名 新属性名 新数据类型 ;

　　Modify和change的区别就是，modify在修改表结构的时候不需指定新的字段名，直接在后面跟上需要修改的属性即可，change在修改表结构和字段名称的时候需要指定旧字段名和新字段名和新属性。

增加字段

　　ALTER TABLE 表名 ADD 属性名1 数据类型 [完整性约束条件] [FIRST | AFTER 属性名2] ;

删除字段

　　ALTER TABLE 表名 DROP 字段名 ;

清空表

　　Truncate 表名;

　　delete from 表名;

　　清空表是指把表中现有数据全部清空，使用TRUNCATE关键字，truncate之后，自增长列的id也从建表时设置的起始id开始，和delete的区别是delete之后数据会清空，但是自增长列的id不会从起始id开始，truncat之后不能回滚，delete之后，可以回滚。

删除表

　　DROP TABLE 表名;

（2）DEL语句

　　插入数据

　　　　为表的所有字段插入数据

　　　　INSERT INTO 表名 VALUES('列一','列二','列三',.......);

　　　　为表的指定字段插入数据

　　　　INSERT INTO 表名(属性1, 属性2, … , 属性m)VALUES(值1,值2, …, 值m);

　更新数据　

UPDATE 表名
SET 属性名1=取值1, 属性名2=取值2,
…,
属性名n=取值n
WHERE 条件表达式;

删除数据

　　DELETE FROM 表名 [ WHERE 条件表达式 ] ;

查询数据　　

SELECT 属性列表
FROM 表名和视图列表
[ WHERE 条件表达式1 ]
[ GROUP BY 属性名1 [ HAVING 条件表达式2 ] ]
[ ORDER BY 属性名2 [ ASC | DESC ] ]

查询所有字段

　　select * from 表名;

查询指定字段

　　SELECT 列一, 列二, 列三,... FROM 表名;

多表查询　　

　　多表连接可以通过join关键字来连接，也可以直接用关联表中相同的id来进行关联；

select a.name 学生姓名,b.score 学生成绩 from students a,score b where a.id=b.student_id;

Left join：左连接， 连接两张表，以左边表的数据匹配右边表中的数据，如果左边表中的数据在右边表中没有，会显示左边表中的数据。

select a.name 学生姓名,b.score 学生成绩 from students a left join score b on a.id=b.student_id;

Right join：右连接，连接两张表，以右边表的数据匹配左边表中的数据，如果左边表中的数据在左边边表中没有，会显示右边表中的数据。

select a.name 学生姓名,b.score 学生成绩 from students a right join score b on a.id=b.student_id;

Inner join：内连接，连接两张表，匹配两张表中的数据，和前面两个不同的是只会显示匹配的数据。

　　select a.name 学生姓名,b.score 学生成绩 from students a INNER join score b on a.id=b.student_id;
IN关键字查询

　　IN关键字可以判断某个字段的值是否在指定的集合中。如果字段的值在集合中，则满足查询条件，该纪录将被查询出来。如果不在集合中，则不满足查询条件。

　　select * from 表名 where id in (元素1 ,元素2,....);

OR多条件查询

　　使用OR关键字时，只要满足这几个查询条件的其中一个，这样的记录将会被查询出来。如果不满足这些查询条件中的任何一个，这样的记录将被排除掉。

　　select * from 表名 where 条件1 or 条件2......;

AND多条件查询

　　使用AND关键字时，只有同时满足所有查询条件的记录会被查询出来。如果不满足这些查询条件的其中一个，这样的记录将被排除掉。

　　Select * from 表名 where 条件1 and 条件2....;

排除重复结果

　　distinct

　　如果表中的某些字段上没有惟一性约束，这些字段可能存在着重复的值，可以使用distinct排除重复数据。

限制查询条件

　　limit

　　Select * from 表名 limit 10,20;　　　显示第10行到第20行数据

　　Select * from 表名 limit 5;　　　　　显示前5条数据

BETWEEN AND的范围查询

　　BETWEEN AND关键字可以判读某个字段的值是否在指定的范围内。如果字段的值在指定范围内，则满足查询条件，该纪录将被查询出来。如果不在指定范围内，则不满足查询条件。

　　Select * from 表名 where score [not] between 60 and 100;

 LIKE的字符匹配查询

　　LIKE关键字可以匹配字符串是否相等。如果字段的值与指定的字符串相匹配，则满足查询条件，该纪录将被查询出来。如果与指定的字符串不匹配，则不满足查询条件。

　　select * from student where name [not] like '张%';

　　% 包含零个或多个字符组成的任意字符串;

　　_ 任意一个字符

查询空值

　　IS NULL关键字可以用来判断字段的值是否为空值（NULL）。

　　Select * from users where sex is [not] null;

对查询结果排序 order by

从表中查询出来的数据可能是无序的，或者其排列顺序不是用户所期望的顺序。为了使查询结果的顺序满足用户的要求，可以使用ORDER BY关键字对记录进行排序排序的方向可以是升序（asc）或降序（desc），使用order by字段名后不加参数的话，默认是升序，asc，其语法规则如下：

ORDER BY 属性名 [ ASC | DESC ]

GROUP BY子句

GROUP BY关键字可以将查询结果按某个字段或多个字段进行分组。字段中值相等的为一组。其语法规则如下：

GROUP BY 属性名 [ HAVING 条件表达式 ]

1．单独使用GROUP BY关键字来分组

2．GROUP BY关键字与集合函数一起使用

3．GROUP BY关键与HAVING一起使用

4．按多个字段进行分组

多个字段分组就是group by后面跟多个字段，比如说要统计每个班的男女生人数，就要用班级名称和性别进行分组。

聚合函数

　　COUNT()用来统计记录的条数；

SUM()用来计算字段的值的总和；

AVG()用来计算字段的值的平均值；

MAX()用来查询字段的最大值；

MIN()用来查询字段的最小值。

合并结果集

union 与 union all 的区别：

当使用union时，MySQL 会筛选掉select结果集中重复的记录（在结果集合并后会对新产生的结果集进行排序运算，效率稍低）。而使用union all时，MySQL会直接合并两个结果集，效率高于union。如果可以确定合并前的两个结果集中不包含重复的记录，建议使用union all。

子查询

如果一个select语句能够返回单个值或者一列值，且该select语句嵌套在另一个SQL语句中（例如select语句、insert语句、update语句或者delete语句）中，那么该select语句称为子查询（也叫内层查询），包含子查询的SQL语句称为主查询（也叫外层查询）。为了标记子查询与主查询之间的关系，通常将子查询写在小括号内。子查询一般用在主查询的where子句或having子句中，与比较运算符或者逻辑运算符一起构成where筛选条件或having筛选条件。这些比较运算符包括=、!=、>、>=、<、<=、<>等。其中，<>与!=是等价的。比较运算符在子查询时使用的非常广泛

（3）DCL语句

Grant关键字为用户设置权限

比如说增加一个超级用户，拥有所有的权限，只允许本地登录

Grant all on *.* to 'andashu'@'localhost' IDENTIFIED BY '123456‘with grant option;

增加一个普通用户，只有对bugfree数据库查询的修改的权限，允许远程登录

Grant select, insert,update on bugfree.* to 'tester'@'%' IDENTIFIED BY '123456';

增加一个超级用户，限制登录ip为192.168.1.101

Grant all privileges on *.* to dba@‘192.168.1.101' IDENTIFIED BY '123456';

增加一个只有查询bugfree的bug表中的bugtitle的用户

Grant select(title) on bugfree.bf_bug_info to 'bugfree'@'%' IDENTIFIED BY '123456';

修改完成后执行 flush privileges; 生效

为数据库用户设置密码

使用set password

set password for tester= password("123456")

使用update语句

Update user set password=password('123456') where user='tester';

删除用户

Delet from user where user=tester;

数据库的备份和恢复

三、索引、视图、存储过程

　　（1）索引

索引由数据库表中一列或多列组合而成，其作用是提高对表中数据的查询速度。索引是创建在表上的，是对数据库表中一列或多列的值进行排序的一种结构。索引可以提高查询的速度。

普通索引：create index 索引名称 on 表名(列)

　　　　　alter table 表名 add index 索引名称 (列)

唯一索引：create unique index 索引名称 on 表名(列名）

　　　　　alter table 表名 add unique index 索引名称 (列)

删除索引：DROP INDEX 索引名 ON 表名 ;

 （2）视图

　　视图是从一个或多个表中导出来的表，是一种虚拟存在的表。视图可以使用户的操作更方便，而且可以保障数据库系统的安全性。视图中的数据是依赖于原来的表中的数据的。一旦表中的数据发生改变，显示在视图中的数据也会发生改变。

　　Create view 视图名称（视图列1，视图列2） as select 语句

（3）存储过程　　

delimiter $$;

CREATE PROCEDURE 名称(参数列表)

BEGIN

SQL语句块

End

$$;

delimiter;

注：由括号包围的参数列必须总是存在。如果没有参数，也该使用一个空参数列()。每个参数默认都是一个IN参数。要指定为其它参数，可在参数名之前使用关键词 OUT

或INOUT

变量定义：

　　declare 变量名 数据类型 default 默认值
参数类型：
　　in参数
　　　　表示该参数的值必须在调用存储过程之前指定，在存储过程中修改的值不能被返回，也就是调用的时候就得指定，默认传入的就是in参数
　　Out参数
　　　　该值可在存储过程内部改变，并可以返回.往往是用于获取存储过程里的参数值。
　　Inout参数
　　　　该值可以在调用时指定，并可修改和返回。
传参 :
　　CREATE PROCEDURE test_p(in 参数名 参数类型,out 参数名 参数类型)
调用：
　　Call 存储过程名