mysql 经验_mysql经验

--UUID 获取方法!

replace(uuid(),'-','') ---->将uuid()中的‘-’，去掉，即替换成空串；

此外

upper(replace(uuid(),'-',''))用于将字符转换为大写

--去除重复 DISTINCT

SELECT DISTINCT PWD FROM t_core_resident_user WHERE EMPI='8A41B722524411E6B006000C29946D7C' //去重

--排序

order by ID desc/asc --排序 倒序/

--查询非空字段(空字段)

IFNULL(字段名,'')<>'' //非空

IFNULL(字段名,'')='' //空值

字段名 IS NULL //空值

字段名 IS NOT NULL //非空

FIND_IN_SET(字段名, '') //空

--查询别名 不为 空显示别名, 为空显示名称(SHORT_NAME2)

SELECT *,(CASE WHEN IFNULL(SHORT_NAME,'')='' THEN ORG_NAME ELSE SHORT_NAME END) AS SHORT_NAME2 FROM med_core.t_core_organization

--创建表

CREATE TABLE person(

id INT NOT NULL AUTO_INCREMENT,

NAME VARCHAR(40) NOT NULL,

city VARCHAR(20),

salary INT,

age INT,

PRIMARY KEY(id)

)ENGINE=INNODB CHARSET=gb2312;

--导入txt文件

LOAD DATA LOCAL INFILE 'C:/Users/Administrator/Desktop/person3.txt' INTO TABLE person CHARACTER SET 'gb2312'(NAME,age,city,salary);

LOAD DATA LOCAL INFILE 'C:/Users/Administrator/Desktop/data3.txt' INTO TABLE t0 CHARACTER SET 'utf8'(NAME,age,description); --导入txt文件

--倒出txt文件

SELECT NAME,age,city,salary INTO OUTFILE 'C:/Users/Administrator/Desktop/data_out.txt' LINES TERMINATED BY '\r\n' FROM person;

--批量插入数据

insert into `person`(`id`,`name`,`city`,`salary`,`age`) values (57,'张三','北京',3000,31),(58,'李四','杭州',4000,25);

--Count() 函数

select count(1) from 表名 --求总个数

select count(列名) from 表名 --求总个数不包含空