MySQL基本操作

MySQL From Ubuntu

Preface *Jack Lee*

首先MySQL是一种数据库(Database)管理软件.

所以MySQL是用来管理若干数据库, 数据库中存放的是若干数据表(Table),

数据表以一种类似于Execl表格的形式来存放数据. 数据表中的每一条数据都是一行.

一张数据表中每一列可以存放一种类型的数据, 那么数据表就能够存放各种类型的数据, 如存放用户信息, 用户的名字, 用户的年龄, 用户的联系方式等.

而数据表中的每一列称为字段(Column), 我们可以在创建表的时候设置上对应的字段名和字段类型来应对需要存入的各种数据的类型.

• 语句结束符: 每个语句都以 ; 或 \G 结束.

• 大小写: MySQL不严格区分大小写.

• 类型: 强制数据类型, 任何数据都有自己的类型.

• 逗号: 最后一列不需要逗号, 换行继续输入可以使用逗号.

Install & Deploy

# 获取更新.
sudo apt-get update
# get MySQL
sudo apt-get instal mysql-server
# 配置信息, 配置密码, 禁止默认登陆.
sudo mysql_secure_installation
# 查看服务状态.
sudo /etc/init.d/mysql status

Getting Start

# 进入MySQL
mysql -uusername -pppassword
# 进入远程MySQL
mysql -hip -Pport -uusername -ppassword
# 退出MySQL or quit;
exit;
# 创建用户, %表示任意远程链接.
CREATE USER 'username'@'%' IDENTIFIED BY 'password';
# 设置用户权限, 将几乎所有库表权限都给username.
GRANT ALL ON *.* TO 'username'@'%';
# 查看MySQL用户.
SELECT host, user FROM mysql.user;
# 查看当前登陆用户.
SELECT USER();
# 刷新.
FLUSH PRIVILEGES:
# 查看当前的时间.
SELECT NOW();

Library Operation

# 查看MySQL的库.
SHOW DATABASES;
# 创建库.
CREATE DATABASE base_name;
# 查看库的创建信息.
SHOW CREATE DATABASE base_name;
# 创建库时指定编码.
CREATE DATABASE base_name CHARSET utf8;
# 删除库(当然一般不会这么做).
DROP DATABASE base_name;
# 先判断库是否存在再删除.
DROP DATABASE IF EXISTS base_name;
# 在任意位置切换到指定库中.
USE base_name;
# 查看当前所在库. 如果是null, 表示为根.
SELECT DATABASE();

Table Operation

# 查看数据库中的表.
SHOW TABLES;
# 创建表.
CREATE TABLE tb_name(column_name1 INT, ...);
# 查看表创建时的结构.
SHOW CREATE TABLE tb_name;
# 删除表(当然一般也不会这么做).
DROP TABLE tb_name;

字段常用类型:

• INT: 整型.
• DOUBLE(4, 2): 双精度浮点型. 4表总长, 2表小数位.
• DATETIME: 时间日期类型.
• VARCHAR(20): 不定长字符类型. 20表接收数据的最长长度.
• CHAR(4): 定长字符类型. 4表接收数据的最长长度.
• TEXT: 不定长文本类型.
• DATE: 日期类型.
• BOOL: bool类型.

DATA CRUD

• C(Create): 插入数据.

# 多次插入数据不如一次插入快.
# 指定字段名插入单条数据.
INSERT INTO tb_name(col_name1, ...) VALUE (col_value1, ...);
# 不指定字段名插入单条数据即为表中的每个字段插入数据.
INSERT INTO tb_name VALUE (col_value1, ...);
# 插入多条数据.
INSERT INTO tb_name VALUES (col_value1, ...), (col_value1, ...), ...;
# 细致插入单条数据.
INSERT INTO tb_name SET col_name1=col_value1, ...;

• R(Read): 查询数据.

# 查看指定表全部数据.
SELECT * FROM tb_name;
# 查看指定表的指定字段的数据.
SELECT col_name1, ... FROM tb_name;
# 对数据进行筛选. condition: col_name1=value1
SELECT col_name1, ... FROM tb_name WHERE condition1;

• U(Update): 更新数据.

# 更新数据, 更细的时候需要有最基本的筛选条件, 不然会更新全表指定字段数据.
UPDATE tb_name SET col_name1=col_value1 WHERE condition1;

• D(Delete): 删除数据.

# 删除数据, 当然正常情况我们宁愿写明废弃数据也不会删除.
DELETE FROM tb_name WHERE condition1;

条件(condition): 如col_name1=‘hah’

条件符	含义	实例
=	等于	name=‘Stephen’
<=, >=	小于等于, 大于等于	id<=20
<, >	小于, 大于	age<18
!=, <>	不等于	gender<>‘man’
IS NULL	判断为空	name IS NULL
IS NOT NULL	判断为非空	name IS NOT NULL
AND, OR, NOT	逻辑与或非	name=‘Stephan’ OR name=‘Jack Lee’

Subquery & JOIN Query

# 我们可把查询返回的结果当成另一张表(虽然只是数据, 但是可以这样去使用).
# 子查询是把一条SQL语句返回的结果来当作条件或者表使用.
# MySQL中表和表之间是可以有关系.
# 如果我们通过表关系来查询一些信息出来可以使用子查询.
SELECT * FROM tb_name1 WHERE col1=(SELECT col2 FROM tb_name2 WHERE condition1);
# 查询出col1等于col2的tb_name1的数据.

JOIN:

# 连接查询是把一个表里的每一条数据和另一个表里的每一条数据进行匹对.
# 所以如果A, B表有10条数据, 那么整合后就是100条数据.
# 连接查询.
SELECT * FROM tb_name1 JOIN tb_name2;
# 连接查询加筛选.
SELECT * FROM tb_name1 JOIN tb_name2 ON tb_name1.name1=tb_name2.name2;
# 嵌套连接查询.
SELECT * FROM (tb_name1 JOIN tb_name2 ON tb_name1.name1=tb_name2.name2) JOIN tb_name3;

筛选条件优先级:

• WHERE: 最高
• ON: 其次
• HAVING: 最后

Column & Constraint

• DEFAULT value: 设置字段默认值

CREATE TABLE tb_name(id INT DEFAULT 10);

• NOT NULL: 默认非空

CREATE TABLE tb_name(name VARCHAR(20) NOT NULL);

• UNIQUE KEY: 唯一键, 不重复.

CREATE TABLE tb_name(
    age CHAR(4),
    UNIQUE KEY (age)
);

• PRIMARY KEY: 主键, 非空且唯一.

CREATE TABLE tb_name(
	id INT,
    PRIMARY KEY (id)
);

• AUTO_INCREMENT: 自增长, 配合主键使用.

CREATE TABLE tb_name(
	auto_id INT AUTO_INCREMENT,
    PRIMARY KEY (auto_id)
);

• 外键(FOREIGN KEY (col1) REFERENCES tb_name(col2)): 将两个表的字段进行联系. 外国的(foreign), 参考(references).

CREATE TABLE tb_c(
	c_col INT,
    PRIMARY KEY (c_col)
);
CREATE TABLE tb_d(
	d_col INT,
    FOREIGN KEY (d_col) REFERENCES tb_c(c_col)
);

• 表结构查看
  • DESC tb_name;: 查看表中的字段及其类型.
  • SHOW KEYS FROM tb_name;: 查看表中设置的键.

DATA Filter

• 筛选: BETWEEN和IN

# 获取数值17到20之间的数据. 等同16<=col_name1<=20
SELECT * FROM td_name WHERE col_name1 BETWEEN 17 AND 20;
# 第二种形式.
SELECT * FROM td_name WHERE col_name1 in (17, 20);

• LIKE: 模糊匹配
  • %: 匹配任意多个字符.
  • _: 匹配一个字符.

# 如果name字段中有'杰克.李'这样的一条数据.
# 匹配这个名字.
SELECT * FROM tb_name WHERE name LIKE '杰克%';
# 或者这样
SELECT * FROM tb_name WHERE name LIKE '杰克__';

• ORDER BY: 排序.
  • ASC: 正序(默认).
  • DESC: 倒序.

# 更具某个字段进行倒序.
SELECT * FROM tb_name ORDER BY col1 DESC;

• DISTINCT: 去重.

# 以每条数据为标准, 去除重复的数据.
SELECT DISTINCT col1, col2 FROM tb_name;

• 聚合
  • COUNT(*): 计算数据总量.
  • MAX(column): 数据中最大值.
  • MIN(column): 数据中最小值.
  • SUM(column): 数据数值和.
  • AVG(column): 数据数值平均值.

SELECT COUNT(*) FROM tb_name;

• GROUP BY: 分组

# 将每表中的某字段中的数据进行分组即数据相同的分在一起.
SELECT col1, AVG(col2) FROM tb_name GROUP BY col1;
# 上面这条语句是把col1字段的数据进行分组, 然后把分组了的数据对应的col2字段的数据进行求平均值.
# 给分组加上条件.
SELECT col1, COUNT(*) FROM tb_name GROUP BY col1 HAVING COUNT(*)>4;
# 上面这个语句表示将col1字段中的数据进行分组, 然后计算分组的数据总数, 并把总数大于4的分组返回回来.

DATA LIMIT

# 可以使用限制来进行分页等功能.
# 返回前3条数据.
SELECT * FROM tb_name LIMIT 3;
# 从位置为3(前)的位置开始取3(后)条数据. 第一条数据的位置为0.
SELECT * FROM tb_name LIMIT 3, 3;

DATA AS

• AS: 别名, 有各种用途.

# 将查询出来的字段进行别名.
SELECT col1 AS 字段1 FROM tb_name;
# 能不能将子查询的结果再进行查询返回呢?
# SELECT * FROM (SELECT * FROM tb_name WHERE condition1);
# 然后会报错, 需要我们给别名, 因为我们想要查询的子查询的结果并没有名字, 所以需要我们给.
SELECT * FROM (SELECT * FROM tb_name WHERE condition1) AS new_name;

DATA VIEW

• VIEW: 视图类似于镜子, 将表的数据映射出来, 表发生变化那么视图就会发生变化, 视图不是真的表.

# 将条件查询得到的结果映射到视图, 需要给视图名字.
# 简单来说就是视图会映射一条SQL语句的结果.
# 通常我们也不会给视图添加数据或者修改.
CREATE VIEW view_name AS SELECT * FROM tb_name;
# 查看视图.
SELECT * FROM view_name;

TABLE MODIFY

• ALTER TBALE: 正常情况下, 我们创建一个表的时候就应该定义好结构, 而不是在后面大量的整改.

# 添加一列(字段)到表结构中.
ALTER TABLE tb_name ADD COLUMN new_col INT;
# 将新的字段添加到开头.
ALTER TABLE tb_name ADD COLUMN new_col BOOL NOT NULL DEFAULT TRUE FIRST;
# 将新的字段添加到指定字段的后面.
ALTER TABLE tb_name ADD COLUMN new_col VARCHAR(20) AFTER col1_name;
# 删除指定字段(当然我们一般不会删除).
ALTER TABLE tb_name DROP COLUMN old_col;
# 修改字段的类型或者约束, 甚至是位置.
ALTER TABLE tb_name MODIFY COLUMN old_col INT NOT NULL AFTER col1_name;
# 修改字段的名字, 当然在修改的时候需要指定其类型或约束.
ALTER TABLE tb_name CHANGE COLUMN old_col_name new_col_name CHAR(4) NOT NULL;

• 添加或者删除约束键.

# 给字段添加主键.
ALTER TABLE tb_name ADD PRIMARY KEY (col1_name);
# 给字段添加唯一键.
ALTER TABLE tb_name ADD UNIQUE KEY (col1_name);
# 删除主键.
ALTER TABLE tb_name DROP PRIMARY KEY;
# 删除唯一键. 这里的key_name是键名, 通过查看表结构可以知晓.
ALTER TABLE tb_name DROP KEY key_name;

• 无功能键(索引): 给字段添加索引, 在查询的时候会加快速度, 但是添加索引会消耗资源.

# 给字段添加索引
ALTER TABLE tb_name ADD KEY (col_name);
# 删除字段索引, 这里的key_name是索引的名字, 可以用过表结构知晓
ALTER TABLE tb_name DROP KEY key_name;

Python & MySQL >>> PyMySQL

import pymysql


db_config = {
    'host': '127.0.0.1', 	# 需要连接ip
    'port': 3306			# 默认端口3306
    'user': 'username',		# 用户名.
    'password': 'password',	# 用户密码
    'db': 'db_name',		# 进入的数据库名.
    'charset': 'utf8'		# 编码方式.
}
							# 解包.
conn = pymysql.connect(**db_config)
cur = conn.cursor()			# create cursor.
try:
	sql = 'SELECT * FROM students'	
	rv = cur.execute(sql)	# execute, rv接收行数.
	resp = cur.fetchall()	# 接收展示内容.
	a = [print(entry) for entry in resp]
except Exception as e:
	print(e)
	conn.rollback()			# 相当于撤销, 如果失败就回滚. 
finally:
	conn.commit()			# 提交修改的数据.
	cur.close()				# 关闭游标. 
	conn.close()			# 关闭conn.

Ubuntu To Remote MySQL

# 网络互通, mysql服务正常.
# 但是win的mysql无法远程到ubuntu.
# 创建好mysql的用户和权限.
# 然后修改一个文件.
sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf
# 重启mysql服务.
sudo /etc/init.d/mysql restart

mysqld.cnf修改内容:

• bind-address修改为0.0.0.0: 表示为任何IP访问.
• bind-address注释掉: 简单粗暴.
• bind-address修改为指定IP: 表示指定的IP是可以访问的.

# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address		= 127.0.0.1

Ubuntu MySQL ENCODE

# 在MySQL中查看默认编码.
SHOW VARIABLES LIKE '%character%';
# 来把MySQL的编码修改为UTF-8.
# 修改好了后, 重启mysql服务.
# sudo /etc/init.d/mysql restart

• 通过查看/etc/mysql/my.cnf可以看到两个目录.
  • (1) 修改/etc/mysql/mysql.conf.d/mysqld.cnf文件
  • (2) 修改/etc/mysql/conf.d/mysql.cnf文件

# (1)
[mysqld]
skip-external-locking
# 添加下面这行配置.
character-set-server = utf8

# (2)
[mysql]
# 添加下面这行配置.
default-character-set = utf8

---