当DBA的快乐时光

#主键约束——两列复合主键
/*CREATE TABLE db1.t39(
    cip varchar(15),
    port SMALLINT,
    status ENUM("deny","allow"),
    PRIMARY KEY(cip,port)
);
DESC db1.t39;

INSERT INTO db1.t39 VALUES ('1.1.1.1',22,'deny');
INSERT INTO db1.t39 VALUES ('1.1.1.1',80,'allow');
INSERT INTO db1.t39 VALUES ('2.1.1.1',82,'deny');
INSERT INTO db1.t39 VALUES ('2.1.1.1',82,'allow');

ALTER TABLE db1.t39 DROP PRIMARY KEY;
ALTER TABLE db1.t39 ADD PRIMARY KEY(cip,port);
DESC db1.t39;*/

#自增约束
/*CREATE TABLE db1.t38(
    id INT PRIMARY KEY AUTO_INCREMENT,
    name char(10),
    class char(7),
    address char(10)
);
INSERT INTO db1.t38(name,class,address)
    VALUES
        ('tom','nsd','beijing'),
        ('jim','aid','shanghai'),
        ('bob','ntd','changde');
INSERT INTO db1.t38 VALUES (5,'lucy','uid','xian');
INSERT INTO db1.t38(name,class,address) VALUES ('lucy','jsd','shenzhen');
DELETE FROM db1.t38;
SELECT * FROM db1.t38;
INSERT INTO db1.t38(name,class,address)
    VALUES
        ('lily','nsd','beijing');
SELECT * FROM db1.t38;
TRUNCATE db1.t38;
INSERT INTO db1.t38(name,class,address)
    VALUES
        ('lily','nsd','beijing');
SELECT * FROM db1.t38;*/

#外键约束
/*CREATE TABLE db1.yg(
    yg_id int PRIMARY KEY AUTO_INCREMENT,
    name char(20)
);
CREATE TABLE db1.gz(
    gz_id int,
    pay float,
    FOREIGN KEY(gz_id) REFERENCES db1.yg(yg_id)
    ON UPDATE CASCADE    #级联更新
    ON DELETE CASCADE    #级联删除
);
ALTER TABLE db1.gz DROP FOREIGN KEY gz_idfk_1;

SHOW CREATE TABLE db1.gz;

ALTER TABLE db1.gz ADD FOREIGN KEY(gz_id)
REFERENCES db1.yg(yg_id)
ON UPDATE CASCADE ON DELETE CASCADE;

SHOW CREATE TABLE db1.gz;

INSERT INTO db1.yg(name) VALUES ("jerry"),("tom");    #验证外键效果
INSERT INTO db1.gz VALUES (1,10000);
INSERT INTO db1.gz VALUES (2,11000);
INSERT INTO db1.gz VALUES (3,12000);
INSERT INTO db1.yg(name) VALUES ("bob");

UPDATE db1.yg SET yg_id=9 WHERE yg_id=3;
DELETE FROM db1.yg WHERE yg_id=2;
SELECT * FROM db1.yg;*/

#索引index的添加与删除
/*CREATE DATABASE home;
CREATE TABLE home.tea4(
    id char(6),
    name varchar(6),
    age int,
    gender ENUM('boy','girl'),
    INDEX(id),INDEX(name)
);
SHOW INDEX FROM home.tea4;
DROP INDEX id ON home. tea4;
CREATE INDEX nl ON home.tea4(age);
SHOW INDEX FROM home.tea4;
DESC home.tea4;

EXPLAIN SELECT * FROM tarena.user WHERE id='sshd';
CREATE INDEX id ON tarena.user(id);
EXPLAIN SELECT * FROM tarena.user WHERE id='sshd';*/

#用户管理
/*SELECT user,host FROM mysql.user;
CREATE USER tom@'localhost' identified BY '123456';
ALTER USER tom@'localhost' identified BY '654321';
RENAME USER tom@'localhost' TO jerry@'localhost';
SELECT user,host FROM mysql.user;
DROP user jerry@'localhost';
#添加权限
CREATE user tom@'localhost' identified BY '123456';
SHOW GRANTS FOR tom@'localhost';
GRANT SELECT ON *.* TO tom@'localhost';
GRANT INSERT,UPDATE ON *.* TO tom@'localhost';
SHOW GRANTS FOR tom@'localhost';
GRANT ALL ON *.* TO tom@'localhost';
SHOW GRANTS FOR tom@'localhost';*/
#删除权限
/*REVOKE DELETE,DROP ON *.* FROM tom@'localhost';
REVOKE ALL ON *.* FROM tom@'localhost';
SHOW GRANTS FOR tom@'localhost';

GRANT SELECT ON *.* TO tom@'localhost';
GRANT INSERT ON tarena.* TO tom@'localhost';
GRANT DELETE ON tarena.user TO tom@'localhost';
GRANT UPDATE(name,shell) ON tarena.user TO tom@'localhost';*/

select * from mysql.user;