菜粥

2020-03-28 19:20 已编辑 SHEIN_资深Java开发工程师

关注

MySql学习总结-基础命令

2018/7/21

net start mysql 启动数据库服务
net stop mysql 关闭数据库服务
mysql -u root -p 登陆数据库
quit 退出登录
exit 退出登录
\q 退出登录
mysql -V 查看mysql版本信息
prompt 提示符修改提示符默认为mysql>
SELECT VERSION(); 显示服务器版本
SELECT NOW(); 显示当前日期时间
SELECT USER(); 显示当前用户
CREATE DATABASE 数据库name; 创建数据库
CREATE DATABASE IF NOT EXISTS 数据库name; 创建数据库可以重名
SHOW DATABASES; 显示所有的数据库
SHOW WARNINGS; 查看警告信息
SHOW CREATE DATABASE 数据库名; 显示数据库创建时的信息
CREATE DATABASE 数据库名 CHARACTER SET gbk/utf8; 创建编码类型为gbk/utf8的数据库
ALTER DATABASE 数据库名 CHARACTER SET gbk/utf8; 修改数据库编码类型
DROP DATABASE 数据库名; 删除数据库

2018/7/22

MySQL基本数据类型：

整型

数据类型	存储范围	字节
TINYINT	有符号值：-128 到 127（-2727到2727）无符号值：0到255（0到2828）	1
SMALLINT	有符号值：-32768 到 32767（-215到215215到215）无符号值：0到65535（0到216216-1）	2
MEDIUMINT	有符号值：-8388608 到 8388607（-223到223223到223）无符号值：0到16777215（0到224224）	3
INT	有符号值：-231到231231到231 无符号值：0到232到232-1	4
BIGINT	有符号值：-263到263263到263 无符号值：0到264到264-1	8

浮点型

数据类型	精度
FLOAT[(M,D)]	小数点后6-7位（常用）
DOUBLE[(M,D)]	小数点后14-15位

日期时间类型

日期类型	存储空间（字节）	日期格式	范围
datetime	8	YYYY-MM-DD HH:MM:SS	1000-01-01 00:00:00 ~ 9999-12-31 23:59:59
timestamp	4	YYYY-MM-DD HH:MM:SS	1970-01-01 00:00:01 ~ 2038
date	3	YYYY-MM-DD	1000-01-01 ~ 9999-12-31
year	1	YYYY	1901~2155
time	2	HH:MM:SS	-838:59:59 ~ 838:59:59

字符型

列类型	说明
CHAR(M)	M个字节，0<=M<=255，固定长度
VARCHAR(M)	L+1个字节，其中L<M且0<M<65535，可以小于定于长度M
TINYTEXT	L+1个字节，其中L<2828
TEXT	L+2个字节，其中L<216216
MEDIUMTEXT	L+3个字节，其中L<224224
LONGTEXT	L+4个字节，其中L<232232
ENUM('value1','value2')	枚举，1或2个字节，取决于枚举的个数（最多216216-1个值）
SET('value1','value2')	集合，1,2,3,4,8个字节，取决于成员个数

USE 数据库名; 使用该数据库
SELECT DATABASE(); 显示当前使用的数据库
CREATE TABLE 表名
(
......
); 创建数据表
SHOW TABLES; 查看当前数据库的数据表
SHOW TABLES FROM 数据库名; 查看某个数据库中的数据表
SHOW COLUMNS FROM 数据表名; 查看数据表结构

2018/7/23
________________________________________________________________

INSERT 数据表名(id,name) VALUES(201717,'ZGH'); 在表中插入记录
INSERT 数据表名 VALUES(201717,'ZGH'); 在表中插入记录如果省略参数则后面的值需要全都写上
SELECT * FROM 数据表名; 查看数据表所有的记录
CREATE TABLE 表名
(
name VARCHAR(20) NOT NULL,//字段值禁止为空
age TINYINT UNSIGNED NULL//字段值可以为空
);
CREATE TABLE 表名
(
id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,//将id设置为递增主键
//AUTO_INCREMENT 自动编号必须定义为主键
name VARCHAR(20) NOT NULL UNIQUE KEY //唯一约束
sex ENUM('男','女','保密') DEFAULT '保密' //则性别的默认值为保密
);
ALTER TABLE 数据表名 ADD 列名 VARCHAR(32) NOT NULL;
//增加数据表的列最后面不加参数则添加到所有列的后面
ALTER TABLE stu0 ADD 列名 VARCHAR(32) NOT NULL FIRST;
//增加数据表的列添加到所有列的前面
ALTER TABLE stu0 ADD password2 VARCHAR(32) NOT NULL AFTER password1;
//在password1后面添加列password2
ALTER TABLE STU0 ADD 列1 TINYINT(5) ,ADD 列2 TINYINT ;
//添加多列不能指定位置只能添加到最后
ALTER TABLE 数据表名 DROP 列1,DROP 列2;//删除多列
ALTER TABLE 数据表名 ADD PRIMARY KEY (列名); //添加主键
ALTER TABLE 数据表名 ADD UNIQUE KEY (列名);//添加唯一约束
ALTER TABLE 数据表名 ALTER 列名 SET DEFAULT 默认值;//修改列的默认值
ALTER TABLE 数据表名 ALTER 列名 DROP DEFAULT;//删除列的默认值
ALTER TABLE 数据表名 DROP PRIMARY KEY;//删除主键
ALTER TABLE 数据表名 DROP INDEX 标识符;//删除唯一约束
ALTER TABLE 数据表名 MODIFY 列名 TINYINT(4) NOT NULL AFTER 列名;//修改列的位置到最后或者最前
ALTER TABLE 数据表名 CHANGE 列名新列名 tinyint(5) NOT NULL;//修改列的名称及类型修改类型应注意数据丢失
ALTER TABLE 旧数据表名 RENAME 新数据表名;//修改数据表名
RENAME TABLE 旧数据表名 TO 新数据表名;//修改数据表名
INSERT 表名 VALUES('ZGH',123),('LEO',111);//在表中插入多条记录有默认值的可以使用default代替
INSERT 表名 SET name='qqq' ,id=110;//只能在表中插入一条记录
UPDATE 表名 set id=id-100;//更改所有记录中id为id-100
UPDATE 表名 SET id=0 WHERE name='qqq';//修改name为qqq的记录的id为0
DELETE FROM 表名 WHERE name='qqq';//删除表中名字为qqq 的这条记录
SELECT 列名1，列名2 FROM 表名;//查询表中指定的列
SELECT 表名1.列名1 ,表名2.列名2 FROM 表名1,表名2;//查询不同表中的不同列
SELECT 列名 AS 别名 FROM 表名;//在表中查询该列用别名显示
SELECT 列名 FROM 表名 GROUP BY 列名;//分组查询
SELECT 列名 FROM 表名 GROUP BY 列名 HAVING 列名='ZGH';//限制条件查询
SELECT 列名 FROM 表名 GROUP BY 列名 HAVING count(id)>=12;
SELECT * FROM 表名 ORDER BY 列名 ACS/DESC;//根据列名进行升序/降序排列查询结果
SELECT * FROM 表名 ORDER BY 列名1 ACS/DESC,列名2 ACS/DESC; 设置多个排序方式
select * from table limit m,n;
其中m是指记录开始的index，从0开始，表示第一条记录
n是指从第m+1条开始，取n条。
select * from tablename limit 2,4
即取出第3条至第6条，4条记录
select * from table limit m;//从第一条开始返回m条记录
INSERT 表名1(name) SELECT name FROM 表名2 WHERE 限制条件;//将一个表的列记录添加到另外一个表的列中
SET NAMES gbk/utf8;//设置编码为gbk/utf8
set password for 'root'@'localhost'='root'; //修改root密码

2018/7/25

MySQL字符函数：https://www.cnblogs.com/geaozhang/p/6739303.html

MySQL数值运算符和函数：https://blog.csdn.net/qq_28602957/article/details/52472357

MySQL比较运算符和函数：https://www.cnblogs.com/alaya/p/4389720.html

MySQL日期时间函数：https://www.cnblogs.com/geaozhang/p/6740457.html

MySQL系统信息函数：
1、VERSION() 返回数据库的版本号
SELECT VERSION() -- 5.0.67-community-nt
2、CONNECTION_ID() 返回服务器的连接数
SELECT CONNECTION_ID() -- 3
3、DATABASE()、SCHEMA 返回当前数据库名
4、USER()、SYSTEM_USER()、SESSION_USER()、CURRENT_USER()、CURRENT_USER 返回当前用户
SELECT USER() -- root@localhost
5、CHARSET(str) 返回字符串str的字符集
SELECT CHARSET('abc'); -- utf8
6、COLLATION(str) 返回字符串str的字符排列方式
SELECT COLLATION('abc'); -- utf8_general_ci
7、LAST_INSERT_ID() 返回最近生成的AUTO_INCREMENT值
SELECT LAST_INSERT_ID(); -- 0

MySQL聚合函数：https://www.cnblogs.com/geaozhang/p/6745147.html

MySQL加密函数：

本节介绍了加密和加密值。若你想要储存一些由可能包含任意字节值的加密函数返回的结果，使用BLOB列而不是 CHAR 或VARCHAR 列，从而避免由于结尾空格的删除而改变一些数据值的潜在问题。

AES_ENCRYPT(str,key_str) , AES_DECRYPT(crypt_str,key_str)

这些函数允许使用官方AES进行加密和数据加密 (高级加密标准 ) 算法, 即以前人们所熟知的 “Rijndael”。保密关键字的长度为128比特，不过你可以通过改变源而将其延长到 256 比特。我们选择了 128比特的原因是它的速度要快得多，且对于大多数用途而言这个保密程度已经够用。

输入参数可以为任何长度。若任何一个参数为NULL,则函数的结果也是NULL。

因为 AES 是块级算法，使用填充将不均衡长度字符串编码，这样结果字符串的长度的算法为 16 * (trunc(string_length / 16) + 1)。

若 AES_DECRYPT()检测到无效数据或不正确填充，它会返回 NULL。然而，若输入的资料或密码无效时， AES_DECRYPT()有可能返回一个非 NULL 值 (可能为无用信息 )。

你可以通过修改你的问询，从而使用AES函数以加密形式来存储数据：

INSERT INTO t VALUES (1,AES_ENCRYPT('text','password'));

AES_ENCRYPT()和AES_DECRYPT() 可以被看作MySQL中普遍通用的密码最安全的加密函数。

DECODE(crypt_str,pass_str)

使用 pass_str 作为密码，解密加密字符串 crypt_str， crypt_str 应该是由ENCODE()返回的字符串。

ENCODE(str,pass_str)

使用pass_str 作为密码，解密 str 。使用DECODE()解密结果。

结果是一个和str长度相同的二进制字符串。若你想要将其保留在一个列中，可使用 BLOB 列类型。

DES_DECRYPT(crypt_str[,key_str])

使用DES_ENCRYPT()加密一个字符串。若出现错误，这个函数会返回 NULL。

注意，这个函数只有当MySQL在SSL 的支持下配置完毕时才会运作。请参见5.8.7节，“使用安全连接”.

假如没有给定 key_str 参数， DES_DECRYPT() 会首先检查加密字符串的第一个字节，从而确定用来加密原始字符串的DES密码关键字数字，之后从DES关键字文件中读取关键字从而解密信息。为使其运行，用户必须享有 SUPER 特权。可以选择--des-key-file服务器指定关键字文件。

假如你向这个函数传递一个key_str 参数，该字符串被用作解密信息的关键字。

若 crypt_str 参数看起来不是一个加密字符串， MySQL 会返回给定的 crypt_str。

DES_ENCRYPT(str[,(key_num|key_str)])

用Triple-DES 算法给出的关键字加密字符串。若出现错误，这个函数会返回NULL。

注意，这个函数只有当MySQL 在SSL的支持下配置完毕后才会运行。请参见5.8.7节，“使用安全连接”.

使用的加密关键字的选择基于第二个到 DES_ENCRYPT()的参数，假如给定：

参数	说明
无参数	使用来自DES关键字文件的第一个关键字。
key_num	使用DES 关键字文件给出的关键字数字(0-9)。
key_str	使用给出的关键字字符串为 str 加密。

选择--des-key-file服务器指定关键字文件。

返回字符串是一个二进制字符串，其中第一个字符为 CHAR(128 | key_num)。

加上 128使得识别加密关键字更加容易。若你使用一个字符串关键字，则 key_num 为127。

结果的字符串长度为 new_len = orig_len + (8-(orig_len % 8))+1。

DES关键字文件中的每一行都具有如下格式：

key_num des_key_str

每个key_num 必须是一个从0到0范围内的数字。文件中行的排列顺序是任意的。 des_key_str 是用来加密信息的字符串。在数字和关键字之间应该至少有一个空格。若你未指定任何到DES_ENCRYPT()的关键字参数，则第一个关键字为默认的使用关键字。

使用FLUSH DES_KEY_FILE语句，你可以让 MySQL从关键字文件读取新的关键字值。这要求你享有 RELOAD特权。

拥有一套默认关键字的一个好处就是它向应用程序提供了一个检验加密列值的方式，而无须向最终用户提供解密这些值的权力。

mysql> SELECT customer_address FROM customer_table

> WHERE crypted_credit_card = DES_ENCRYPT('credit_card_number');

ENCRYPT(str[,salt])

使用Unix crypt() 系统调用加密 str。 salt 参数应为一个至少包含2个字符的字符串。若没有给出 salt 参数，则使用任意值。

mysql> SELECT ENCRYPT('hello');

-> 'VxuFAJXVARROc'

至少在一些系统中，ENCRYPT()除了str的前八位字符之外会忽略所有内容。这个行为由下划线的crypt() 系统调用的执行所决定。

假如crypt()在你的系统中不可用(正如在 Windows系统), ENCRYPT() 则会始终返回NULL。鉴于这个原因，我们向你推荐使用 MD5() 或SHA1() 来代替,因为这两个函数适合所有的平台。

MD5(str)

为字符串算出一个 MD5 128比特检查和。该值以32位十六进制数字的二进制字符串的形式返回, 若参数为 NULL 则会返回 NULL。例如，返回值可被用作散列关键字。

mysql> SELECT MD5('testing');

-> 'ae2b1fca515949e5d54fb22b8ed95575'

这是"RSA Data Security, Inc. MD5 Message-Digest Algorithm."

假如你想要将这个值转化为大写字母，参见12.8节，“Cast函数和操作符”中BINARY操作符项中给出的二进制字符串转换。

OLD_PASSWORD(str)

当PASSWORD()的执行变为改善安全性时，OLD_PASSWORD()会被添加到 MySQL。OLD_PASSWORD()返回从前的PASSWORD()执行值( 4.1之前)，同时允许你为任何4.1 之前的需要连接到你的5.1 版本MySQL服务器前客户端设置密码，从而不至于将它们切断。请参见5.7.9节，“MySQL 4.1中的密码哈希处理”。