数据分析中,通常需要使用数据库来存储数据,包括一些关系型和非关系型数据库,关系型数据库中mysql使用很广泛,因为它免费,支持强大的功能。
mysql是使用最广泛的结构化数据库之一,需要一些基本语法的时候,我们通常会去百度搜,各种查。有时候很快就能查出来需要的命令,粘贴过来执行成功后就不管了,下次要使用同样的命令,可能还要查一下,如此循环;并且有些命令可能就不是一下子就能查出来了。这害处可见一斑——浪费了时间,大佬与菜鸟的区别,可能就是随手记(心记,笔记)这么一点点拉开的。
其实,每次查一个命令可以稍加记忆,然后记下来,做成笔记。一些常用的命令,我们可以把它总结下来,简介的命令甚至可以在脑海里记下来,下次使用的时候,先去大脑中查,然后从笔记中查,最后才去百度,工作、学习效率都会提高。(推荐有道云笔记,小编用着感觉挺方便,其它还有印象笔记啥的都不错?)
小编总结了自己平时常用的命令,算是比较全面了,笔记这种东西就是不断总结完善的。
mysql文档地址
官方文档:https://dev.mysql.com/doc/refman/5.7/en/
W3Cschool文档:https://www.w3cschool.cn/mysql/
学习一门新的语言,新的东西,很多人首先会去找一些教程,官方文档不错,英语不错的同学可以看看,作为程序员,英语最好不要太差。然后,就是国内一些好的教程类网站,像W3Cschool的文档就不错,菜鸟教程也不错,我大学时就比较喜欢菜鸟教程。
顺便贴个mysql下载地址:
https://dev.mysql.com/downloads/windows/installer/5.7.html
下载页面有一句话:
Note: MySQL Installer is 32 bit, but will install both 32 bit and 64 bit binaries.
意思是,安装包名字是32bit,不要怕安装时候会装上64bit的。
就这一点需要注意的了,然后按照自己的需要选择5.5,5.6,5.7的版本即可。
安装时候,按照安装向导,win10系统的话,基本无障碍,一直点击"下一步",几分钟就能安装完成,还是很方便的。win7系统可能就需要安装一个.NET 框架4.5.2啥的,去微软官方下载后,安装即可。
mysql常用命令
登陆msyql数据库
如果是windows装了mysql client,点击client,然后输入密码即可。
我windows装的mysql5.7,好像没有添加到环境变量,需要把:C:\Program Files\MySQL\MySQL Server 5.7\bin 这个目录暴露到环境变量里面;linux通常在make & install时候已经配置到系统环境中了。
如果只安装了服务端,或者是在linux上安装,需要通过命令来登陆,登陆mysql通常需要提供ip,port(端口),userName(用户名),password(密码):
mysql -h 192.168.1.213 -P 3306 -u root -p 123456
注意
-p(小写):后面跟密码
-P(大写):后面跟端口
常用命令
SQL全称呼为Structure Query Language(结构化查询语言)。主要分为3类。
DDL(Data Definition Languages)语句:数据定义语言,这些语句定义了不同的数据段、 数据库、表、列、索引等数据库对象的定义。常用的语句关键字主要包括 create、drop、alter 等。
DML(Data Manipulation Language)语句:数据操纵语句,用于添加、删除、更新和查询数据库记录,并检查数据完整性,常用的语句关键字主要包括 insert、delete、update 和 select 等。
DCL(Data Control Language)语句:数据控制语句,用于控制不同数据段直接的许可和 访问级别的语句。这些语句定义了数据库、表、字段、用户的访问权限和安全级别。主要的语句关键字包括 grant、revoke 等。
显示命令
-- 创建数据库
create database wx_test;
-- 显示数据库
show databases;
-- 指定操作的数据库
use wx_test;
-- 显示表
show tables;
-- 建表命令
create table if not exists user(
id bigint(10) not null auto_increment primary key,
user_name varchar(50) not null default 'null',
age int not null default 0
)engine=Innodb charset=utf8mb4;
-- 显示建表语句
show create table user;
/*
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| user | CREATE TABLE `user` (
`id` bigint(10) NOT NULL AUTO_INCREMENT,
`user_name` varchar(50) NOT NULL DEFAULT 'null',
`age` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
*/
-- 查看表的字段信息
desc user;
/*
+-----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+----------------+
| id | bigint(10) | NO | PRI | NULL | auto_increment |
| user_name | varchar(50) | NO | | null | |
| age | int(11) | NO | | 0 | |
+-----------+-------------+------+-----+---------+----------------+
*/
sql脚本的注释
-- =====注释=============
mysql存储引擎
MyISAM:这个是默认类型,它是基于传统的ISAM类型,ISAM是Indexed Sequential Access Method (有索引的顺序访问方法) 的缩写,它是存储记录和文件的标准方法.与其他存储引擎比较,MyISAM具有检查和修复表格的大多数工具. MyISAM表格可以被压缩,而且它们支持全文搜索.它们不是事务安全的,而且也不支持外键。如果事物回滚将造成不完全回滚,不具有原子性。如果执行大量的SELECT,MyISAM是更好的选择。
InnoDB:这种类型是事务安全的.它与BDB类型具有相同的特性,它们还支持外键.InnoDB表格速度很快.具有比BDB还丰富的特性,因此如果需要一个事务安全的存储引擎,建议使用它.如果你的数据执行大量的INSERT或UPDATE,出于性能方面的考虑,应该使用InnoDB表
mysql的事务支持与存储引擎有关,MyISAM不支持事务,INNODB支持事务,更新时采用的是行级锁。行级锁并不是直接锁记录,而是锁索引,如果一条SQL语句用到了主键索引,mysql会锁住主键索引;如果一条语句操作了非主键索引,mysql会先锁住非主键索引,再锁定主键索引。
update语句会执行以下步骤:
1、由于用到了非主键索引,首先需要获取非主键列上的行级锁
2、紧接着根据主键进行更新,所以需要获取主键上的行级锁;
3、更新完毕后,提交,并释放所有锁。
常用mysql状态命令
/*
常用mysql状态命令
*/
-- 显示最大连接数
show global status like 'Max_used_connections';
+----------------------+-------+
| Variable_name | Value |
+----------------------+-------+
| Max_used_connections | 2 |
+----------------------+-------+
-- 查看进程的连接
show full processlist;
/*
+----+------+-----------------+---------+---------+------+----------+-----------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------------+---------+---------+------+----------+-----------------------+
| 8 | root | localhost:49608 | wx_test | Query | 0 | starting | show full processlist |
+----+------+-----------------+---------+---------+------+----------+-----------------------+
*/
-- SQL执行计划
explain select * from user;
/*
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | user | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
*/
-- 查看数据库字符集
show create database wx_test;
/*
+----------+------------------------------------------------------------------+
| Database | Create Database |
+----------+------------------------------------------------------------------+
| wx_test | CREATE DATABASE `wx_test` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+------------------------------------------------------------------+
1 row in set (0.00 sec)
*/
-- 修改数据库字符集
alter database test default character set utf8mb4;
-- 查看表的字符集,显示建表语句
show create table user;
/*
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| user | CREATE TABLE `user` (
`id` bigint(10) NOT NULL AUTO_INCREMENT,
`user_name` varchar(50) NOT NULL DEFAULT 'null',
`age` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
*/
-- 修改表的字符集
alter table user default character set utf8mb4;
-- 查看mysql版本
status;
/*
--------------
mysql Ver 14.14 Distrib 5.7.17, for Win64 (x86_64)
Connection id: 8
Current database: wx_test
Current user: root@localhost
SSL: Not in use
Using delimiter: ;
Server version: 5.7.17-log MySQL Community Server (GPL)
Protocol version: 10
Connection: localhost via TCP/IP
Server characterset: utf8
Db characterset: utf8
Client characterset: gbk
Conn. characterset: gbk
TCP port: 3306
Uptime: 5 days 23 hours 41 min 4 sec
Threads: 1 Questions: 85 Slow queries: 0 Opens: 114 Flush tables: 1 Open tables: 107 Queries per second avg: 0.000
*/
-- 启动mysql服务命令
/etc/init.d/mysqld start
-- 停止mysql服务命令
/etc/inint.d/mysqld stop
-- 重启mysql服务命令
/etc/init.d/mysqld restart
增删改查
-- 插入数据
insert into user(user_name, age) values
('ikeguang.com', 23),
('cj318.cn', 24),
('数据分析挖掘与算法', 36);
-- 查询数据
select * from user;
/*
+----+--------------------+-----+
| id | user_name | age |
+----+--------------------+-----+
| 1 | ikeguang.com | 23 |
| 2 | cj318.cn | 24 |
| 3 | 数据分析挖掘与算法 | 36 |
+----+--------------------+-----+
3 rows in set (0.14 sec)
*/
-- 更新数据
update user set age = 25 where id = 3;
-- 删除数据
delete from user where age = 24;
select * from user;
/*
+----+--------------------+-----+
| id | user_name | age |
+----+--------------------+-----+
| 1 | ikeguang.com | 23 |
| 3 | 数据分析挖掘与算法 | 25 |
+----+--------------------+-----+
2 rows in set (0.00 sec)
*/
将一个.txt文件导入mysql中,文本文件user.txt内容:
4 学习使我快乐 18
使用load data local命令导入到user表中:
-- 将一个文本文件导入mysql表中
load data local infile 'D:/我的文件/公众号文章材料/数据分析挖掘与算法/mysql知识/user.txt' into table user fields terminated by '\t';
/*
Query OK, 1 row affected (0.07 sec)
Records: 1 Deleted: 0 Skipped: 0 Warnings: 0
*/
select * from user;
/*
+----+--------------------+-----+
| id | user_name | age |
+----+--------------------+-----+
| 1 | ikeguang.com | 23 |
| 3 | 数据分析挖掘与算法 | 25 |
| 4 | 学习使我快乐 | 18 |
+----+--------------------+-----+
*/
`fields terminated by '\t'` 指定user.txt文本文件中的内容以\t格式分开,\t格式分开可以防止字段中有分隔符。
显示当前用户和正在操作的数据库
-- 显示当前用户和正在操作的数据库
select database();
/*
+------------+
| database() |
+------------+
| wx_test |
+------------+
*/
select user();
/*
+----------------+
| user() |
+----------------+
| root@localhost |
+----------------+
*/
*/
常用运算符
算术运算符
比较运算符
这里有几个运算符,需要注意:
NULL:判断NULL需要用is null,判断空值需要用 = '';
field_a not in (select field_b from tb),如果field_b中有NULL值,那么这个查询不会返回任何数据,这时候需要用left join来解决;
like 后跟通配模式,rlike或者regexp是可以写正则表达式的。
-- 正则匹配 user_name 以k开头,k出现0次或者多次
select * from user where user_name rlike '^k*';
/*
+----+--------------------+-----+
| id | user_name | age |
+----+--------------------+-----+
| 1 | ikeguang.com | 23 |
| 3 | 数据分析挖掘与算法 | 25 |
| 4 | 学习使我快乐 | 18 |
+----+--------------------+-----+
3 rows in set (0.00 sec)
*/
-- 正则匹配 user_name 以k开头,k出现一次以上
select * from user where user_name rlike '^k+';
-- Empty set (0.00 sec)
-- 正则匹配 user_name 以i开头,k出现一次及以上
select * from user where user_name rlike '^ik+';
/*
+----+--------------+-----+
| id | user_name | age |
+----+--------------+-----+
| 1 | ikeguang.com | 23 |
+----+--------------+-----+
1 row in set (0.00 sec)
*/
正则表达式语法,可以学习一些常用的。
复制表
-- 复制表as 与 like
CREATE TABLE IF NOT EXISTS `test` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`label_id` varchar(180) NOT NULL DEFAULT 'null',
`cou` bigint(20) NOT NULL DEFAULT '0',
`date` date NOT NULL,
`hour` int not null DEFAULT '0',
UNIQUE KEY `date_hour_label_id`(`date`, `hour`, `label_id`),
PRIMARY KEY (`id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
insert into test(`label_id`,cou, `date`, hour) values('1', 1314, '2019-07-16', 22);
create table test2 as select * from test;
create table test3 like test;
select * from test2;
/*
+----+----------+------+------------+------+
| id | label_id | cou | date | hour |
+----+----------+------+------------+------+
| 1 | 1 | 1314 | 2019-07-16 | 22 |
+----+----------+------+------------+------+
*/
show create table test2;
/*
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test2 | CREATE TABLE `test2` (
`id` bigint(20) unsigned NOT NULL DEFAULT '0',
`label_id` varchar(180) CHARACTER SET utf8mb4 NOT NULL DEFAULT 'null',
`cou` bigint(20) NOT NULL DEFAULT '0',
`date` date NOT NULL,
`hour` int(11) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
*/
show create table test3;
/*
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test3 | CREATE TABLE `test3` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`label_id` varchar(180) NOT NULL DEFAULT 'null',
`cou` bigint(20) NOT NULL DEFAULT '0',
`date` date NOT NULL,
`hour` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
UNIQUE KEY `date_hour_label_id` (`date`,`hour`,`label_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
*/
select * from test3;
/*
Empty set (0.00 sec)
*/
区别很明显:
as用来创建相同表结构并复制源表数据
like用来创建完整表结构和全部索引
as复制的表,表结构相同,索引信息没了,包括主键(主键也是一种索引,并且不能为空),有数据;like复制的表,没有数据,表结构相同,有索引信息。
mysql用法命令很多,足够记一本很厚的书了,平时用到慢慢总结,主要是提高工作、学习效率。
sql文件笔记已经同步到Github,后期可以慢慢补充。Github地址:
https://github.com/ddxygq/PyCode/blob/master/db/mysql.sql
猜你可能喜欢
分类与聚类的本质区别
Python操作MySQL的几种方法
MySQL 面试之必会知识点
数据分析必学知识之MySQL 使用方法简单教程
交了3年的女朋友不理我了,她说我连SVM都不会?