5.常见约束&TCL事务控制语言

USE students;
DROP TABLE stuinfo;
CREATE TABLE stuinfo(
    id INT PRIMARY KEY,#主键
    stuName VARCHAR(20) NOT NULL UNIQUE,#非空
    gender CHAR(1) CHECK(gender='男' OR gender ='女'),#检查
    seat INT UNIQUE,#唯一
    age INT DEFAULT  18,#默认约束
    majorId INT REFERENCES major(id)#外键
);

CREATE TABLE major(
    id INT PRIMARY KEY,
    majorName VARCHAR(20)
);

SHOW INDEX FROM stuinfo;

DROP TABLE IF EXISTS stuinfo;
CREATE TABLE stuinfo(
	id INT,
	stuname VARCHAR(20),
	gender CHAR(1),
	seat INT,
	age INT,
	majorid INT,
	CONSTRAINT pk PRIMARY KEY(id),#主键
	CONSTRAINT uq UNIQUE(seat),#唯一键
	CONSTRAINT ck CHECK(gender ='男' OR gender  = '女'),#检查
	CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)#外键
);
SHOW INDEX FROM stuinfo;

CREATE TABLE IF NOT EXISTS stuinfo(
    id INT PRIMARY KEY,
    stuname VARCHAR(20),
    sex CHAR(1),
    age INT DEFAULT 18,
    seat INT UNIQUE,
    majorid INT,
    CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)
);

DROP TABLE IF EXISTS stuinfo;
CREATE TABLE stuinfo(
    id INT,
    stuname VARCHAR(20),
    gender CHAR(1),
    seat INT,
    age INT,
    majorid INT
)
DESC stuinfo;

ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20)  NOT NULL;

ALTER TABLE stuinfo MODIFY COLUMN age INT DEFAULT 18;

ALTER TABLE stuinfo MODIFY COLUMN id INT PRIMARY KEY;

ALTER TABLE stuinfo ADD PRIMARY KEY(id);

ALTER TABLE stuinfo MODIFY COLUMN seat INT UNIQUE;

ALTER TABLE stuinfo ADD UNIQUE(seat);

ALTER TABLE stuinfo ADD CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id);

ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NULL;

ALTER TABLE stuinfo MODIFY COLUMN age INT ;

ALTER TABLE stuinfo DROP PRIMARY KEY;

ALTER TABLE stuinfo DROP INDEX seat;

ALTER TABLE stuinfo DROP FOREIGN KEY fk_stuinfo_major;
SHOW INDEX FROM stuinfo;

DROP TABLE IF EXISTS tab_identity;
CREATE TABLE tab_identity(
	id INT  primary key auto_increment,
	NAME varchar(20)
);
TRUNCATE TABLE tab_identity;
INSERT INTO tab_identity(id,NAME) VALUES(null,'john');
INSERT INTO tab_identity(NAME) VALUES('lucy');
SELECT * FROM tab_identity;

TRUNCATE TABLE tab_identity;
INSERT INTO tab_identity(id,NAME) VALUES(NULL,'john');
INSERT INTO tab_identity(NAME) VALUES('lucy');
SELECT * FROM tab_identity;

SHOW VARIABLES LIKE '%auto_increment%';

SET auto_increment_increment=3;

set autocommit=0;

SET autocommit=0;
START TRANSACTION;

UPDATE account SET balance = 1000 WHERE username='张无忌';
UPDATE account SET balance = 1000 WHERE username='赵敏';

ROLLBACK;
#commit;

SELECT * FROM account;

SET autocommit=0;
START TRANSACTION;
DELETE FROM account;
ROLLBACK;

SET autocommit=0;
START TRANSACTION;
DELETE FROM account WHERE id=25;
SAVEPOINT a;#设置保存点
DELETE FROM account WHERE id=28;
ROLLBACK TO a;#回滚到保存点
SELECT * FROM account;

SELECT stuname,majorname
FROM stuinfo s
INNER JOIN major m ON s.`majorid`= m.`id`
WHERE s.`stuname` LIKE '张%';

CREATE VIEW v1
AS
SELECT stuname,majorname
FROM stuinfo s
INNER JOIN major m ON s.`majorid`= m.`id`;

SELECT * FROM v1 WHERE stuname LIKE '张%';

CREATE VIEW myv1
AS
SELECT last_name,department_name,job_title
FROM employees e
JOIN departments d ON e.department_id  = d.department_id
JOIN jobs j ON j.job_id  = e.job_id;

SELECT * FROM myv1 WHERE last_name LIKE '%a%';

CREATE VIEW myv2
AS
SELECT AVG(salary) ag,department_id
FROM employees
GROUP BY department_id;

SELECT myv2.`ag`,g.grade_level
FROM myv2
JOIN job_grades g
ON myv2.`ag` BETWEEN g.`lowest_sal` AND g.`highest_sal`;

SELECT * FROM myv2 ORDER BY ag LIMIT 1;

CREATE VIEW myv3
AS
SELECT * FROM myv2 ORDER BY ag LIMIT 1;

SELECT d.*,m.ag
FROM myv3 m
JOIN departments d
ON m.`department_id`=d.`department_id`;

SELECT * FROM myv3;

CREATE OR REPLACE VIEW myv3
AS
SELECT AVG(salary),job_id
FROM employees
GROUP BY job_id;

ALTER VIEW myv3
AS
SELECT * FROM employees;

DROP VIEW emp_v1,emp_v2,myv3;

CREATE OR REPLACE VIEW emp_v1
AS
SELECT last_name,salary,email
FROM employees
WHERE phone_number LIKE '011%';

CREATE OR REPLACE VIEW emp_v2
AS
SELECT MAX(salary) mx_dep,department_id
FROM employees
GROUP BY department_id
HAVING MAX(salary)>12000;

SELECT d.*,m.mx_dep
FROM departments d
JOIN emp_v2 m
ON m.department_id = d.`department_id`;

CREATE OR REPLACE VIEW myv1
AS
SELECT last_name,email,salary*12*(1+IFNULL(commission_pct,0)) "annual salary"
FROM employees;

CREATE OR REPLACE VIEW myv1
AS
SELECT last_name,email
FROM employees;

SELECT * FROM myv1;
SELECT * FROM employees;

INSERT INTO myv1 VALUES('张飞','zf@qq.com');

UPDATE myv1 SET last_name = '张无忌' WHERE last_name='张飞';

DELETE FROM myv1 WHERE last_name = '张无忌';

CREATE OR REPLACE VIEW myv1
AS
SELECT MAX(salary) m,department_id
FROM employees
GROUP BY department_id;
SELECT * FROM myv1;

UPDATE myv1 SET m=9000 WHERE department_id=10;

CREATE OR REPLACE VIEW myv2
AS
SELECT 'john' NAME;

SELECT * FROM myv2;

UPDATE myv2 SET NAME='lucy';

CREATE OR REPLACE VIEW myv3
AS
SELECT (SELECT MAX(salary) FROM employees) 最高工资;

CREATE OR REPLACE VIEW myv4
AS
SELECT last_name,department_name
FROM employees e
JOIN departments d
ON e.department_id  = d.department_id;

SELECT * FROM myv4;
UPDATE myv4 SET last_name  = '张飞' WHERE last_name='Whalen';
INSERT INTO myv4 VALUES('陈真','xxxx');

CREATE OR REPLACE VIEW myv5
AS
SELECT * FROM myv3;

SELECT * FROM myv5;
UPDATE myv5 SET 最高工资=10000 WHERE department_id=60;

CREATE OR REPLACE VIEW myv6
AS
SELECT last_name,email,salary
FROM employees
WHERE employee_id IN(
    SELECT  manager_id
    FROM employees
    WHERE manager_id IS NOT NULL
);

SELECT * FROM myv6;
UPDATE myv6 SET salary=10000 WHERE last_name = 'k_ing';

CREATE TABLE book(
	bid INT PRIMARY KEY,
	bname VARCHAR(20)UNIQUE NOT NULL
	price FLOAT DEFAULT 10,
	btypedId INT,
	FOREIGN KEY(btypeid) REFERENCES bookType(id)
);

set autocommit=0；
insert into book(bid,bname,price,btypeId)
values(1,'小李飞刀'，100,1);
rollback;

create view myv1
as 
select bname,name
from book b
join bookType t on b.typeid=t.id
where price>100;

create or replace view myv1
as
select bname,price
from book
where price between 90 and 120;

drop view myv1；