MySQL基本语法

1、创建表

create table task_project_info（
	   id                   bigint auto_increment comment '主键'
        primary key,
    project_name         varchar(255)                not null comment '项目名称',
    project_no           varchar(100)                not null comment '项目编号',
    project_state        varchar(32) default '01'     not null comment '项目状态 01 未启动
02已启动
03结束',
    audit_type           varchar(32)                 not null comment '审计类型',
    project_unit         varchar(32)                 not null comment '立项单位',
    implement_start_date date                        not null comment '实施开始时间',
    implement_end_date   date                        not null comment '实施结束时间',
    project_year         varchar(4)                  not null comment '所属年度',
    organizational_form  varchar(32)                 not null comment '组织形式',
    external_agencies_id bigint                      not null comment '外聘机构',
    project_content      text                        not null comment '项目内容',
    operator             varchar(32)                 null comment '操作人账号',
    crt_time             timestamp   default (now()) null comment '创建时间',
    update_time          timestamp                   null comment '修改时间'
)commit '作业项目信息'

2、创建索引

create index task_project_info_project_name_index
    on task_project_info (project_name);

3、创建存储过程

DELIMITER //  
CREATE PROCEDURE InsertTaskProjectInfo(  
    IN p_project_name VARCHAR(255),  
    IN p_project_no VARCHAR(100),  
    OUT p_new_id BIGINT  
)  
BEGIN  
    INSERT INTO task_project_info (project_name, project_no, /* ... 其他列 ... */)  
    VALUES (p_project_name, p_project_no, /* ... 对应的值 ... */);  
      
    -- 获取新插入的记录的ID（假设id是自增的）  
    SET p_new_id = LAST_INSERT_ID();  
END //  
DELIMITER ;

4、修改表

ALTER TABLE task_project_info  
ADD COLUMN project_budget DECIMAL(10, 2) COMMENT '项目预算';

5、创建视图

CREATE VIEW active_projects AS  
SELECT project_id, project_name, project_status, start_date, end_date  
FROM task_project_info  
WHERE start_date <= CURDATE() AND (end_date IS NULL OR end_date >= CURDATE());

6、删除

DELETE FROM task_project_info  
WHERE project_id = 123;

7、插入语句

INSERT INTO task_project_info (project_id, project_name, project_status, start_date, end_date)  
VALUES (123, 'ID123项目', '已完成', '2022-07-01', '2022-09-30');

8、输出表、视图、索引

输出表：SHOW COLUMNS FROM task_project_info;
索引：SHOW INDEXES FROM task_project_info;
视图：SHOW CREATE VIEW view_name;

9、更新数据

UPDATE task_project_info  
SET project_name = 'Updated Project Name'  
WHERE project_id = 123;