# 第01章_Linux下MySQL的安装与使用

# 5. 字符集的相关操作

# 字符集与比较规则(了解)

#查看GBK字符集的比较规则
SHOW COLLATION LIKE 'gbk%';

#查看UTF-8字符集的比较规则
SHOW COLLATION LIKE 'utf8%';


#查看服务器的字符集和比较规则
SHOW VARIABLES LIKE '%_server';

#查看数据库的字符集和比较规则
SHOW VARIABLES LIKE '%_database';

#查看具体数据库的字符集
SHOW CREATE DATABASE dbtest1;

#修改具体数据库的字符集
ALTER DATABASE dbtest1 DEFAULT CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';


#查看表的字符集
show create table emp1;
#查看表的比较规则
show table status from dbtest1 like '%emp1';
#修改表的字符集和比较规则
ALTER TABLE emp1 DEFAULT CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';

# 6. SQL大小写规范

# Windows和Linux平台区别

SHOW VARIABLES LIKE '%lower_case_table_names%';

USE dbtest1;

USE dBtest1;

SELECT * FROM emp1;

SELECT * FROM Emp1;

# 7. sql_mode的合理设置

# 模式查看和设置

select @@session.sql_mode;

CREATE TABLE mytb12(
    id INT,
    NAME VARCHAR(16),
    age INT,
    dept INT
);

INSERT INTO mytb12 VALUES(1,'zhang3',33,101);
INSERT INTO mytb12 VALUES(2,'li4',34,101);
INSERT INTO mytb12 VALUES(3,'wang5',34,102);
INSERT INTO mytb12 VALUES(4,'zhao6',34,102);
INSERT INTO mytb12 VALUES(5,'tian7',36,102);

#查询每个部门年龄最大的人
SELECT NAME,dept,MAX(age)
FROM mytb12
GROUP BY dept;

#设置sql_mode为宽松模式
SET SESSION sql_mode = '';

INSERT INTO mytb12(id, NAME, age)
VALUES(7,'Tom','aaa');

SELECT * FROM mytb12;

SET SESSION sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'