mysql curd语句_MySQL常用SQL语句（CURD，建表，加字段，查改参数）

查询：

FROM_UNIXTIME()：时间戳转日期

sum：合计

SELECT

*,

FROM_UNIXTIME(a.add_time) AS add_time2,

SUM(b.goods_number) AS goods_number2

FROM

ecs_order_info AS a

LEFT JOIN ecs_order_goods AS b

ON

a.order_id = b.order_id

WHERE

a.pay_name = '支付宝' AND b.goods_name LIKE '%口罩%' AND a.add_time BETWEEN 1584623511 AND 1589893911

GROUP BY

a.order_status

HAVING

a.order_status > 1

ORDER BY

a.order_id

DESC

LIMIT 0, 5

增加：

INSERT INTO ecs_test(username, PASSWORD, certNo)

VALUES('BABA', '123456', '7894'),('CACA', '123456', '9546')

更新：

UPDATE

`ecs_test`

SET

`username` = 'DaDa',

`password` = '111111'

WHERE

id = 1

删除：

DELETE

FROM

ecs_test

WHERE

id = 5

建表：

CREATE TABLE `jifen_xianhua_cn`.`ecs_test`(

`id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',

`username` VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL COMMENT '用户名',

`password` CHAR(32) NULL DEFAULT NULL COMMENT '密码',

`status` TINYINT(1) UNSIGNED NULL DEFAULT '0' COMMENT '是否封号 1停用 0启用',

`create_time` DATE NULL COMMENT '创建日期',

`certNo` VARCHAR(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '身份证号',

`pay_self` DECIMAL(10, 2) NOT NULL COMMENT '卖身价',

PRIMARY KEY(`id`),

INDEX `idx_username`(`username`),

UNIQUE `uqu_certNo`(`certNo`(64))

) ENGINE = INNODB CHARSET = utf8 COLLATE utf8_general_ci COMMENT = '用户表';

加字段：

AFTER：加到之后

FIRST：加到表头

ALTER TABLE

`ecs_test` ADD `height` INT(4) UNSIGNED NULL DEFAULT '180' COMMENT '身高(cm)' AFTER `status`,

ADD INDEX `idx_height`(`height`);

查参数：

SHOW VARIABLES LIKE '%iso%'

改参数值：

[]：代表可选

|：或

persist：永久有效

global：重启失效

session：当前连接有效

SET [persist | global | session] TRANSACTION ISOLATION LEVEL READ COMMITTED