基础篇
0、卸载与安装
安装之后:服务器软件+数据文件+mysql进程+环境变量
1、卸载:
控制面板卸载软件+删除数据文件(可选)+清除环境变量+(如果安装依然失败还需要清理注册表)
2、安装(可以安装多个版本)
Downloads下载
下载.msi格式的文件,(microsoft installer)
安装之后:服务器软件+数据文件+mysql进程+环境变量
连接数据库:
cmd下 mysql -uroot -pabc123 -hlocalhost -P3306
本机下可以省略-h和-P
推荐使用:
msql -u root -P 端口号 -h 主机名ip -p
本机省略-P和-h
5.7版本需要设置一下字符集编码;
启动mysql
启动mysql服务端程序
cmd管理员方式运行,然后net start mysql80,关闭使用net stop mysql80
启动mysql客户端程序并连接:
1、基本原理
基于客户机-服务器的数据库,服务器软件负责所有数据访问和处理,客户机软件负责和用户打交道。初次安装MySQL,要求输入管理登录(root+口令),如果使用本地服务器,使用管理登录即可。MySQL是否区分大小写可以由管理员确定,一般不区分大小写。
数据库本质上是一个文件系统,它保存一系列有组织的数据。
2、用户权限认证
mysql数据库中的3个权限表:user 、db、 host
权限表的存取过程是:
1)先从user表中的host、 user、 password这3个字段中判断连接的IP、用户名、密码是否存在表中,存在则通过身份验证;
2) 通过权限验证,进行权限分配时,按照user?db?tables_priv?columns_priv的顺序进行分配。即先检查全局权限表 user,如果user中对应的权限为Y,则此用户对所有数据库的权限都为Y,将不再检查db, tables_priv,columns_priv;如果为N,则到db表中检查此用户对应的具体数据库,并得到db中为Y的权限;如果db中为N,则检查tables_priv中此数据库对应的具体表,取得表中的权限Y,以此类推。
创建用户
3、基础操作
USE 数据库名; //使用某个数据库
SHOW DATABASES; //显示所有可用的数据库的列表,列表中可能包含MySQL内部使用的数据库
SHOW TABLES; //显示当前数据库内可用表的列表
SHOW COLUMNS FROM 表名; //对每个字段返回一行,行中包括字段名、数据类型、是否允许NULL、键信息、默认值与其他信息;
DESCRIBE 表名; //与上条语句的功能相同
SHOW GRANTS; //显示所有用户的安全权限
4、增
CREATE DATABASE 数据库名; //创建一个新的数据库
新建一张表
不写默认为允许NULL,如果不允许为空,必须指定该列的值为是NOT NULL
如果想指定默认值,可以使用NOT NULL DEFAULT XXX;
插入多条记录:多次insert()或者采用以下语法:
向另一张表中添加其他表的数据。
5、删
delete只能删除行不能删除整个表
删除整个表:
6、改
修改表名:
7、查
1、返回查询结果的前几行: LIMIT x; 返回查询结果的前x行;
2、指定检索的开始行和检索的行数:LIMIT x,y; 从第x行开始,检索y行;行的索引从0开始;
为了防止混淆,也可以是应用LIMIT x OFFSET y;
3、ORDER BY子句中将要使用的列将是为显示所选择的列,但实际上并不一定要这样,用非检索的列排序数据完全合法;ORDER BY默认升序排列;
4、LIMIT子句必须在ORDER BY之后,通过ORDER BY子句和LIMIT子句可以查找前K大或者前K小的元组。
//选择价格最高的商品
select prod_price
from products
order by prod_price desc
limit 1;
5、SQL的where子句中的AND的优先级比OR高
6、not in 不在关系内,not between ... and ...;不在之间,not exists 不存在;
7、用正则表达式进行搜索
①基本字符匹配
包含某个文本的所有元组,WHERE prod_name REGEXP 'abc'; 返回包含'abc'的元组
特殊字符 . (.代表任意字符) WHERE prod_name REGEXP '.000' 返回包含_000的字符串
OR匹配 WHERE prod_name REGEXP '1000|2000' 返回包含1000或2000的字符串
介绍[]匹配符
匹配几个字符之一 用[]来选择 WHERE prod_name REGEXP '[123] Ton',[123]在123中选择一个,1 Ton 或者 2 Ton 或者 3 Ton //先当于OR,它事实上是[1|2|3]的缩写
为了匹配除指定字符外的任何东西,可以在集合的开始除放置一个^ [^123]除了1、2、3之外的任意一个符号都可以
太长了怎么办 [0123456789] 可以写成[0-9]; [123]可以写成[1-3];[abc....z]可以写成[a-z]
对于这些特殊字符,我们怎么进行转义? 前面加\\ \\.查找. \\-查找 -
.、|、[]、\、(、)都需要转义
匹配字符类:使用预定义的字符集合
[:alnum] 任意字母和数字(同[a-zA-Z0-9]
[:alpha:] 任意字符(同[a-zA-Z]
[:blank:] 空格或制表
[:digit:] 任意数字
[:upper:] 任意大写字母
[:lower:] 任意小写字母
前面的正则表达式都试图匹配单次出现,如果是多次呢?
*0个或多个匹配;+1个或多个匹配,?0个或1个匹配
{n}指定数目的匹配,{n,}不少于指定数目的匹配;{n,m}匹配数目的范围
8、日期字段的创建以及日期记录的插入
日期类型:
datetime 8 bytes YYYY-MM-DD HH:MM:SS 1000-01-01 00:00:00 ~ 9999-12-31 23:59:59
timestamp 4 bytes YYYY-MM-DD HH:MM:SS 1970-01-01 00:00:01 ~ 2038
date 3 bytes YYYY-MM-DD 1000-01-01 ~ 9999-12-31
示例:
创建:create table orders (order_time datetime);
create table orders (order_time timestamp);
create table orders (order_time date);
插入:curdate(); 只有日期部分,yyyy-mm-dd,如果字段包括时间则 默认为00:00:00
curtime();只有时间
now();日期+时间
insert into orders values(now());
9、MySQL中的联结
①内部联结(其实就是from做笛卡尔积,然后where筛选)
from a inner join b;
相当于,作笛卡尔积,筛选相同属性相等的列。
也可以指定某些属性相等:
from a inner join b
on a.xx = b.xx;
②外部联结
联结包含了那些在相关表中没有关联行的行
OUTER JOIN
LEFT OUTER JOIN包含左边表的所有行;RIGHT OUTER JOIN包含右边表的所有行
FROM A LEFT OUTER JOIN B
ON A.XXX = B.XXX;
10、全文本搜索
MySQL原理解析
1、引擎
2、客户端与服务器端连接:
MySQL是程序,程序就是一个进程,进程之间通信是不是要用IP+端口号,默认是IP:3306(怎么修改?-P 3307),所以可以用TCP/IP协议。IP相同就是同一主机的不同进程。既然是进程间通信,那么还有哪些进程间通信方式?命名管道和共享内存(共享内存必须在同一主机,找时间总结)!!!
客户端--处理连接------查询缓存--语法解析--查询优化--(MyISAM、InnoDB、Memory...)--文件系统
3、MySQL索引
八百万条记录,查询一次4.5s,文件大小524M;
CREAT INDEX 索引名称 ON 表名(列名);
CREAT INDEX empno_index ON emp(empno);
创建索引后文件变为655M,索引本身也会占用空间,创建索引后速度大大加快。 ---0.003s
创建索引后,只对创建了索引的列有效!!!
索引的原理:
当我们没有索引,会进行全表扫描,查询速度慢
索引的类型:
主键索引:主键自动的为主索引(类型Primary key),主键一定是索引
唯一索引:(UNIQUE) 指定一列是unique,则该列是唯一的,同时也是索引
普通索引:(INDEX)
全文索引(FULLTEXT)(适用于MyISAM),实际开发中很少使用
开发中考虑:全文搜索Solr和ElasticSearch(ES)
语法:
查询某找表中的索引:
SHOW INDEXES FROM 表名;
create [UNIQUE] index index_name on tbl_name (col_name[(length)]);
添加唯一索引(如果在创建表的时候指定为UNIQUE,默认添加唯一索引):
CREATE UNIQUE INDEX id_index ON hspt25(id); //创建唯一索引,同时id转为UNIQUE类型
如果不希望id是唯一的,则选择普通索引,如果id是唯一的,优先选择唯一索引,普通索引如下:
CREATE INDEX id_index ON hspt25(id);
添加普通索引方式2:
ALTER TABLE 表名 ADD INDEX 索引名 (列名);
添加主键索引:
方式一:创建表的时候添加primar key
方式二:ALTER TABLE 表名 ADD PRIMARY KEY (列名);
删除索引:
DROP INDEX 索引名称 ON 表名;
删除主键索引:
ALTER TABLE 表名 DROP PRIMARY KEY;
修改索引:
先删除,再添加新的索引
查询索引:
SHOW INDEX FROM 表名;
SHOW INDEXES FROM 表名;
SHOW KEYS FROM 表名;
DESC 表名;
创建索引的原则:
较频繁的作为查询条件的字段应该创建索引,唯一性太差的字段不适合单独创建索引,更新非常频繁的字段不适合创建索引,不会出现在where子句中子段不该创建索引。