1.
1. null 不做值的比较,相等
# 说明 field = value , field != value 都会过滤掉 field 值 为 null 的数据
select * from girls ;
# +--+----+------+---+--------+----------+-------------------+-------------+
# |id|name|boy_id|sex|boy_name|is_deleted|createdTime |num |
# +--+----+------+---+--------+----------+-------------------+-------------+
# |1 |慕容a |1 |W | |1 |2020-07-23 15:13:48|20200723_0002|
# |2 |慕容b |1 |W | |NULL |2020-07-23 14:13:48|20200723_0001|
# |3 |慕容c |1 |W | |NULL |2020-07-23 15:13:48|20200723_0003|
# |4 |慕容d |1 |W | | |2020-07-23 16:13:48|20200723_0004|
# +--+----+------+---+--------+----------+-------------------+-------------+
#
select * from girls where is_deleted= 0 ;
# +--+----+------+---+--------+----------+-------------------+-------------+
# |id|name|boy_id|sex|boy_name|is_deleted|createdTime |num |
# +--+----+------+---+--------+----------+-------------------+-------------+
# |4 |慕容d |1 |W | | |2020-07-23 16:13:48|20200723_0004|
# +--+----+------+---+--------+----------+-------------------+-------------+
select * from girls where is_deleted= '0' ;
# +--+----+------+---+--------+----------+-----------+---+
# |id|name|boy_id|sex|boy_name|is_deleted|createdTime|num|
# +--+----+------+---+--------+----------+-----------+---+
select * from girls where is_deleted= 1 ;
# +--+----+------+---+--------+----------+-------------------+-------------+
# |id|name|boy_id|sex|boy_name|is_deleted|createdTime |num |
# +--+----+------+---+--------+----------+-------------------+-------------+
# |1 |慕容a |1 |W | |1 |2020-07-23 15:13:48|20200723_0002|
# +--+----+------+---+--------+----------+-------------------+-------------+
select * from girls where is_deleted != 1 ;
# +--+----+------+---+--------+----------+-------------------+-------------+
# |id|name|boy_id|sex|boy_name|is_deleted|createdTime |num |
# +--+----+------+---+--------+----------+-------------------+-------------+
# |4 |慕容d |1 |W | | |2020-07-23 16:13:48|20200723_0004|
# +--+----+------+---+--------+----------+-------------------+-------------+
# 查的是 除 null 以外 的 数据
select * from girls where is_deleted != 2 ;
# +--+----+------+---+--------+----------+-------------------+-------------+
# |id|name|boy_id|sex|boy_name|is_deleted|createdTime |num |
# +--+----+------+---+--------+----------+-------------------+-------------+
# |1 |慕容a |1 |W | |1 |2020-07-23 15:13:48|20200723_0002|
# |4 |慕容d |1 |W | | |2020-07-23 16:13:48|20200723_0004|
# +--+----+------+---+--------+----------+-------------------+-------------+
1. 查看表字符集,字符集排序和修改表字符集字符集排序
# 查看表的字符集
SHOW CREATE TABLE mytable;
# 修改字符集 和字符集 排序
alter table mytableCONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
# 上面有语法问题的话就用下面的
alter table order_test convert to character set utf8mb4 COLLATE utf8mb4_general_ci ;
# 修改字段的字符集:
ALTER TABLE tbl_name CHANGE c_name c_name CHARACTER SET character_name [COLLATE ...];
如:ALTER TABLE logtest CHANGE title title VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_general_ci;
2. 或者建表的同时指定字符集和字符集排序
create table x_UnBusinessPaymentTime
(
*********
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='测试'
1.
修改数据库字符集
ALTER DATABASE db_name DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
把表默认的字符集和所有字符列(CHAR,VARCHAR,TEXT)改为新的字符集
ALTER TABLE tbl_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
只是修改表的默认字符集
ALTER TABLE tbl_name DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
修改字段的字符集
ALTER TABLE tbl_name CHANGE title title VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
查看数据库编码
SHOW CREATE DATABASE db_name;
查看表编码
SHOW CREATE TABLE tbl_name;
查看字段编码
SHOW FULL COLUMNS FROM tbl_name;
1. 因为utf8并不支持拼音的排序
按照汉字排序 两个方法(order by sql 中 转 成gbk ; 或者将 表字段的编码改为 gbk)
链接:mysql按照汉字拼音排序_yjclsx的博客-CSDN博客_mysql按照中文拼音排序
1. 如果用其他的 排序规则
CREATE TABLE `order_test` (
`CwztGUID` varchar(100) DEFAULT NULL,
`CwztName` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
sql 中修改编码集排序规则
SELECT order_test.Name AS Name , order_test.GUID AS GUID
FROM order_test
ORDER BY convert(order_test.Name using gbk) desc ;
or
SELECT order_test.name AS name , order_test.guid AS guid
FROM order_test
ORDER BY convert(order_test.name using gbk) COLLATE gbk_chinese_ci desc ;
修改字段的编码集
SHOW CREATE TABLE order_test;
# ALTER TABLE order_test CHANGE CwztName CwztName CHARACTER SET character_name [COLLATE ...];
# ALTER TABLE order_test CHANGE CwztName CwztName VARCHAR(100) CHARACTER SET gbk COLLATE gbk_chinese_ci ;
ALTER TABLE order_test CHANGE CwztName CwztName VARCHAR(100) CHARACTER SET gbk COLLATE gbk_chinese_ci ;
1. 日期比较(前段传个字符串,既可以和库里的 datgetime 类型比较,也可以 和 库里的 varchar2 类型的比价)
# create_time_str 是字符串类型
select create_time_str from boys where create_time_str >= '2021-05-22';
# created_time 是datetime 类型
select create_time_str from boys where created_time >= '2021-05-22';
# 脚本如下
create table boys
(
id int not null
primary key,
name varchar(20) null,
sex varchar(20) default '男' null comment '默认男',
age varchar(20) null,
address int null,
created_time datetime null,
enum int null,
create_time_str varchar(42) null
)
comment '男生表';