纸绘武身（mysql）

DML

/*
添加
*/
– 添加
INSERT INTO stu (id,NAME,age) VALUES(3,“robin”,23);
INSERT INTO stu VALUES (2,“hancook”,27,95.5,“2000-01-10”,NULL);
– 删除
DELETE FROM stu WHERE age IS NULL;
TRUNCATE TABLE stu;
– 修改
UPDATE stu SET birthday=“2000-02-24”,age=20 WHERE id=1;

DQL

查询
SELECT * FROM stu;
SELECT NAME,age FROM stu;
SELECT DISTINCT NAME,age,IFNULL(id,0)+IFNULL(score,0)AS总分 FROM stu;
SELECT * FROM stu WHERE NAME LIKE “__bin%”;
SELECT * FROM stu ORDER BY id ASC, age DESC;
– 聚合函数
SELECT COUNT(id) FROM stu;-- min,max,sum,avg
– 分组
SELECT id,COUNT(id)AS 人数 FROM stu WHERE age>4 GROUP BY id HAVING COUNT(id)>1;
– 分页
SELECT * FROM stu LIMIT 0,3;
SELECT * FROM stu LIMIT 3,3;

约束

主键，自动添加，非空，唯一
CREATE TABLE stu1(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20) NOT NULL UNIQUE,
address VARCHAR (20)
);
– 外键约束
CREATE TABLE stu2(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20) NOT NULL UNIQUE,
address_id INT,
CONSTRAINT emp_ad_id FOREIGN KEY (address_id) REFERENCES stu1(id)
);
– 删除非空，唯一，主键，自动增加，外键
ALTER TABLE stu1 MODIFY NAME VARCHAR(20);
ALTER TABLE stu1 DROP INDEX NAME;
ALTER TABLE stu1 DROP PRIMARY KEY;
ALTER TABLE stu1 MODIFY id INT;
ALTER TABLE stu2 DROP FOREIGN KEY emp_ad_id;
– 添加非空唯一，主键，自动增加，外键(自动更新删除)
ALTER TABLE stu1 MODIFY NAME VARCHAR(20) NOT NULL UNIQUE;
ALTER TABLE stu1 MODIFY id INT PRIMARY KEY;
ALTER TABLE stu1 MODIFY id INT AUTO_INCREMENT;
ALTER TABLE stu2 ADD CONSTRAINT emp_ad_id FOREIGN KEY
(address_id) REFERENCES stu1(id) ON UPDATE CASCADE ON DELETE CASCADE;

多表关系

ALTER TABLE route ADD CONSTRAINT emp_ad_id FOREIGN KEY
(cid) REFERENCES category(cid);
CREATE TABLE favorite(
rid INT,
DATE DATETIME,
uid INT,
PRIMARY KEY(rid,uid),-- 联合主键
FOREIGN KEY (rid) REFERENCES route(rid),
FOREIGN KEY (uid) REFERENCES USER(uid)
);
SELECT * FROM category;
SELECT * FROM route;
SELECT * FROM USER;
SELECT * FROM favorite;

多表查询

#内连接
– 隐式内连接1
SELECT
t1.id,
t1.ename,
t1.salary,
t2.jname,
t2.description
FROM
emp t1,job t2
WHERE
t1.job_id=t2.id;
– 隐式内连接2
SELECT
t1.id,
t1.ename,
t1.salary,
t2.jname,
t2.description,
t3.dname,
t3.loc
FROM
emp t1,job t2,dept t3
WHERE
t1.job_id=t2.idAND t1.dept_id=t3.id;
– 显式内连接
SELECT * FROM emp JOIN job ON emp.job_id=job.id;

#外连接(查询不到显示为null)
SELECT
t1.ename,
t1.mgr,
t2.id,
t2.ename
FROM emp t1
LEFT JOIN emp t2
ON t1.mgr=t2.id;

#子查询(嵌套查询)
SELECT
t1.ename,
t1.salary,
t2.grade
FROM emp t1,salarygrade t2
WHERE t1.salary BETWEEN t2.losalaryAND t2.hisalary;
– 综合查询1
SELECT
t1.ename,
t1.salary,
t2.jname,
t2.description,
t3.dname,
t3.loc,
t4.grade
FROM
emp t1,job t2,dept t3,salarygrade t4
WHERE
t1.job_id=t2.id
AND t1.dept_id=t3.id
AND t1.salary BETWEEN t4.losalaryAND t4.hisalary;
– 综合查询2
SELECT
t1.id,t1.dname,t1.loc,t2.total
FROM
dept t1,
(SELECT
dept_id,COUNT(id) total
FROM emp
GROUP BY dept_id) t2
WHERE
t1.id=t2.dept_id;

事务

START TRANSACTION;
UPDATE demo SET num=num + 200 WHERE id=1;
ROLLBACK;
COMMIT;
（原子性，持久性，隔离性，一致性）

DCL

添加，删除用户，改密码
CREATE USER ‘用户名’@‘主机名’ IDENTIFIED BY ‘密码’;
DROP USER ‘用户名’@‘主机名’;
UPDATE USER SET PASSWORD = PASSWORD(‘新密码’) WHERE USER = ‘用户名’;
SET PASSWORD FOR ‘用户名’@‘主机名’ = PASSWORD(‘新密码’);

– 查询，授予，撤销权限
SHOW GRANT FOR ‘用户名’@‘主机名’;
GRANT 权限列表 ON 数据库名.表名 TO ‘用户名’@‘主机名’;
REVOKE 权限列表 ON 数据库名.表名 FROM ‘用户名’@‘主机名’;