DBA的学习4

#### 一、表管理

---------------------------------------------------------------------------------------------------------------

#表重命名
/*
ALTER TABLE studb.stu rename studb.stuinfo;
SHOW TABLES FROM studb;*/

#表字段删除
/*
ALTER TABLE studb.stuinfo DROP age;
#DESC studb.stuinfo;*/

#表字段添加
/*
ALTER TABLE studb.stuinfo
ADD address char(30),
ADD school char(10) AFTER name,
ADD id INT FIRST;
DESC studb.stuinfo;*/

#表字段修改
/*
ALTER TABLE studb.stuinfo MODIFY school varchar(20) AFTER class;
ALTER TABLE studb.stuinfo CHANGE name stuName char(20);
DESC studb.stuinfo;*/

#字段添加
/*
CREATE TABLE studb.t2(name char(3),address varchar(5));
INSERT INTO studb.t2 VALUES ('aaa','abcde');
INSERT INTO studb.t2 VALUES ('aaaa','abcde');
INSERT INTO studb.t2 VALUES ('宋昭伟','北京市');
SELECT * FROM studb.t2;
CREATE TABLE studb.t1(name char(10),level tinyint unsigned, money double);
INSERT INTO studb.t1 VALUES ('a',0,0.00);
INSERT INTO studb.t1 VALUES ('a',255,3.01);
INSERT INTO studb.t1 VALUES ('a',256,10.08);*/

#枚举类型，enum、set
/*
CREATE TABLE studb.t8(
    name char(20),
    gender ENUM('male','female'),
    hobby SET('eat','drink','play','hahaha')
);
INSERT INTO studb.t8 VALUES (
    'zhsan','male','eat,play'
);*/

#时间类型
/*
CREATE TABLE studb.t6(
    name char(10),
    birth_date date,
    birth_year year,
    class_time time,
    party_time datetime
);
INSERT INTO studb.t6 VALUES
('zhsan',19930708,1993,090000,20250310190000),
('lisi',curdate(),year(now()),curtime(),now());
SELECT * FROM studb.t6;*/

/*查看变量，daemon守护进程
SHOW VARIABLES LIKE "secure_file_priv";*/

#文件导入与导出
/*
CREATE DATABASE db1;
CREATE TABLE db1.user3(
    name varchar(30),
    password char(1),
    uid int,
    gid int,
    comment varchar(200),
    homedir varchar(50),
    shell varchar(30)
);

LOAD DATA INFILE "/myload/passwd"
INTO TABLE db1.user3
FIELDS TERMINATED BY ":"        #指定分隔符
LINES TERMINATED BY "\n";        #指定换行符

SELECT * FROM db1.user3;

SELECT * FROM db1.user3
INTO OUTFILE "/myload/user.txt"
FIELDS TERMINATED BY ":"
LINES TERMINATED BY "\n";
*/

#约束条件——非空约束
/*
 CREATE TABLE db1.t31(
     name char(10) NOT null,
    class char(7) DEFAULT "nsd",
    hobby SET('money','game','film','music') NOT null DEFAULT 'film,music'
 );
 DESC db1.t31;

INSERT INTO db1.t31 VALUES ('tom','nsd','game');
INSERT INTO db1.t31(name) VALUES ('jim');
INSERT INTO db1.t31 VALUES (null,null,null);
INSERT INTO db1.t31 VALUES ('null',null,null);
INSERT INTO db1.t31 VALUES ('null',null,'film');
*/

#约束条件——唯一约束
#可以非空但唯一
/*
CREATE TABLE db1.t43(
    name char(10),
    pass_id char(18) UNIQUE
);
DESC db1.t43;
INSERT INTO db1.t43 VALUES ("tom","123");
INSERT INTO db1.t43 VALUES ("jim","123");
INSERT INTO db1.t43 VALUES ("jim",null);
INSERT INTO db1.t43 VALUES ("tom",null);
SELECT * FROM db1.t43;*/