Mysql 操作详解

修改mysql密码

ALTER USER 'root'@'localhost' IDENTIFIED BY 'newpassword'; //修改密码
FLUSH PRIVILEGES; // 更新权限

在创建数据库时，指定数据库的编码

CREATE DATABASE <数据库名> DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci; // gbk编码
CREATE DATABASE <数据库名> DEFAULT CHARSET utf8 COLLATE utf8_general_ci; //utf8编码

或者直接修改配置文件
vim /etc/mysql/my.cnf
[mysqld]
character-set-server=utf8
collation-server=utf8_general_ci

重启mysql服务
sudo service mysql stop
sudo service mysql start

通过status命令可以查看状态信息

查看数据表设置( 字段名称和类型)

DESCRIBE <表名>；

查询时，如果某个字段存在重复值，可以用DISTINCT 去重

SELECT owner FROM pet;
SELECT DISTINCT owner FROM pet;

由于字符串类型数据大小写不敏感，在排序时可用用 BINARY 强制区分大小写

SELECT name,birth FROM pet ORDER BY BINARY  species;

排序时 ASC/DESC 仅适用于它前面的字段，不影响其他列的顺序；

SELECT name,species,birth FROM pet OREDER BY species, birth DESC; 
// 升序（默认）对动物的种类进行排序，然后降序根据生日进行排序

数值类型

整型

• 如果为一个数值列指定ZEROFILL,MySql将自动为该列添加UNSIGNED属性
• SERIAL 等价于 BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE
• SERIAL DEFAULT VALUE 等价于 NOT NULL AUTO_INCREMENT

浮点数

FLOAT[(M,D)] [UNSIGNED] [ZEROFILL] 
// M: 浮点数的总位数
// D:小数点后面的位数
// 如果指定UNSIGNED，不允许负值

日期时间类型

• DATE

// MySql 以 YYYY-MM-DD 格式显示 DATE 值，但允许使用字符串或数字类型数据为 DATE 列赋值
INSERT INTO test_time (date) VALUE ('2018-04-25'); // 如果不加引号，传入的值为0000-00-00

• DATETIME

//日期和时间的组合，MySql 以 YYYY-MM-DD HH:MM:SS 格式显示 DATETIME 值，但允许使用字符串或数字类型数据为 DATE 列赋值

• TIMESTAMP

//TIMESTAMP 列用于执行 INSERT 或 UPDATE 操作是记录日期和时间
//如果你不分配一个值，自动设置为最近操作的日期和时间
//也可以通过分配一个 NULL，将 TIMESTAMP 列设置为当前的日期和时间

• TIME

//MySql 以 HH:MM:SS 格式显示 TIME 值，但允许使用字符串或数字为 TIME 列分配值

• YEAR

CREATE TABLE test_year (year_2 YEAR(2),year_4 YEAR); // 默认为4位
INSERT INTO test_year (year_2,year_4) VALUE (30,30); // 20， 2030
INSERT INTO test_year (year_2,year_4) VALUE ('30','30'); // 20， 2030
INSERT INTO test_year (year_2,year_4) VALUE (2030,2030); // 20， 2030
INSERT INTO test_year (year_2,year_4) VALUE ('2030','2030'); // 20， 2030

字符串类型

//CHARSET 等价于 CHARACTER SET

• CHAR / VARCHAR

CREATE TABLE test_string (
    str_char CHAR(4),
    str_varchar VARCHAR(4)
);
INSERT INTO test_string VALUE('ABCDE','ABCDE');  //ABCD, ABCD 超过长度的部分自动省略

• VARBINARY

//VARBINARY 类型类似于 VARCHAR 类型，但存储二进制 字节 字符串，而非普通字符串

• TEXT / LONGTEXT

//存储长文本...

• 枚举类型

ENUM('value1','value2','value3');

函数和操作符

• COALESCE

//返回参数列表中的第一个非 NULL 值，在没有非 NULL 值的情况下返回值为 NULL;
SELECT COALESCE(null,1);   // 1
SELECT COALESCE(null,null,null); // NULL

• GREATEST

// 当有两个或两个以上参数时，返回值为最大的那个参数
SELECT GREATEST(2,0); // 2
SELECT GREATEAT('b','a','c'); // c

• expr IN （value,...）

// 若IN 列表中包含 expr , 返回值为1，否则返回值为 0
// 匹配字符串时，不区分大小写
// 若expr 的值为 NULL, 无论是否匹配，返回值都为 NULL
SELECT 'yjy' IN ('mf','css','yjy') ; // 1

• INTERVAL(N,N1,N2,N3....)

//返回比 N 大的数的位置, N1<N2<N3
SELECT INTERVAL (20,1,5,19,20,21,30); //4 从0开始计数 

• 流程控制函数

SELECT CASE 3 
  WHEN 1 THEN 'one'
  WHEN 2 THEN 'two'
  ELSE  'more' 
END; 
// 3 

SELECT CASE WHEN 1>0
  THEN 'true'
  ELSE 'false'
END;
// true

SELECT CASE BINARY 'B' 
  WHEN 'b' THEN 2
END;
// NULL

• IF(expr1,expr2,expr3)

SELECT IF(1<2,'yes','no');  // 如果 expr1 的值为true，返回 expr2 否则返回 expr3
// 如果 expr2 或 expr3 中有一个为 NULL, 那么不管判断的结果如何，返回不是 NULL 的那个
SELECT IF(1<2,NULL,'yyy'); // yyy  

字符串函数

• LENGTH

// 返回字符串的长度
SELECT LENGTH('yjy');  // 3
SELECT CHAR_LENGTH('yjy');  //3

• CONCAT(str1,str2)

//返回的结果为连接参数产生的字符串
// 如果有一个参数的值为 null ,则返回值为 null
// 如果有参数为数字类型，可用cast 转换
SELECT CONCAT( CAST(int_col AS CHAR), char_col );

• ELT(N,str1,str2,str3)

SELECT ELT(1,'yy','jj','pp');   // yy
SELECT ELT(2,'yy','jj','pp'); // jj
SELECT ELT(3,'yy','jj','pp'); // pp
// 若N<1 或者 N> arg.length,  返回 NULL

• FORMAT（X,D）

// 货币转换， X 为传入的数字，D 保留的小数位， 返回结果为字符串类型
SELECT FORMAT(1156151.556,2);  // 1,156,151,56, 结果四舍五入了 

• LOAD_FILE(file_name)

// 读取文件，并将文件按照字符串的格式返回  （满足读取 条件的前提下）
UPDATE tal_name SET blob_column = LOAD_FILE('/tmp/picture') WHERE id=1;

时间日期函数

• CURDATE()

// 返回当前的日期
SELECT CURDATE(); // 2018-04-25
SELECT CURDATE() +0; // 20180425  

-CURTIME()

//返回当前的时间 // 16:37:46 

• Mysql 的时间计算

SELECT '2018-01-01 12:52:50' + INTERVAL 1 hour; // 2018-01-01 13:52:50
SELECT DATE_ADD('2018-05-04', INTERVAL 1 month)； // 2018-06-04

• 计算星期

SELECT DAYNAME('2018-04-25');  // wednesday

• LAST_DAY(date);

SELECT LAST_DAY('2018-04-25');  // 2018-04-30

• NOW();

//返回当前日期和时间值 
SELECT NOW(); 
SELECT NOW()+0;