SQL基础

咬定青山不放松,立根原在破岩中！
ECRZ 记录美好事物。

SQL基础

1.SQL语句分类

• DDL 数据定义语言 CREATE DROP ALTER
• DML 数据操控语言 UPDATE DELETE ADD
• DQL 数据查询语言 SELECT
• DCL 数据控制语言 GRANT REVOKE

2.示例

DDL
CREATE TABLE/DATABASE/PROCEDURE/TRIGGER NAME
DROP TABLE/DATABASE/PROCEDURE/TRIGGER NAME
ALTER TABLE/DATABASE/PROCEDURE/TRIGGER NAME ADD/MODIFY COLUMN

DML
INSERT INTO USERS(UID,UNAME,USEX,BIRTHDAY) VALUES (1,'LUCK','男','2002-02-01');
INSERT INTO TABLENAME --插到哪里去

UPDATE USERS SET UNAME = 'MOON',USEX = '女',BIRTHDAY = '1998-06-05' WHERE UID = 2;
UPDATE TABLENAME SET  --更新哪张表的哪个字段

DELETE FROM USERS WHERE UID = 2;
DELETE FROM TABLENAME --从哪张表删除，哪个记录


DQL
SELECT * FROM USERS;
SELECT X FROM X --查询哪个字段，从哪张表

3. 创建数据库和表格

-- 创建数据库，并指定编码为UTF-8
CREATE DATABASE DB CHARACTER SET 'UTF8';

-- 创建表
CREATE TABLE IF NOT EXISTS USERS(
	UID INT NOT NULL AUTO_INCREMENT,
	UNAME VARCHAR(20) NOT NULL,
	USEX VARCHAR(1) NOT NULL,
	BIRTHDAY DATE NOT NULL DEFAULT "1997-08-13",
	PRIMARY KEY(UID),
	CHECK(UID>0)
)ENGINE = INNODB DEFAULT CHARSET=UTF8;


CREATE TABLE IF NOT EXISTS USERS_2(
	UID INT NOT NULL AUTO_INCREMENT,
	UNAME VARCHAR(20) NOT NULL,
	USEX VARCHAR(1) NOT NULL,
	BIRTHDAY DATE NOT NULL DEFAULT "1997-08-13",
	UD INT NOT NULL,
	PRIMARY KEY(UID),
	CHECK(UID>0)
)ENGINE = INNODB DEFAULT CHARSET=UTF8;

4.CRUD表

-- 增加
INSERT INTO USERS(UID,UNAME,USEX,BIRTHDAY) VALUES (1,'LUCK','男','2002-02-01');
INSERT INTO USERS VALUES (2,'LUCK','男','2002-02-01');

-- 删除
DELETE FROM USERS WHERE UID = 2;
DELETE FROM USERS;

-- 修改
UPDATE USERS SET UNAME = 'MOON',USEX = '女',BIRTHDAY = '1998-06-05' WHERE UID = 2;
UPDATE USERS SET USEX = '女';

-- 查询
SELECT * FROM USERS;
SELECT * FROM USERS WHERE USEX = '女';
SELECT UID,UNAME FROM USERS WHERE UNAME LIKE '%L%';
SELECT UID,UNAME FROM USERS WHERE UNAME LIKE '_U%';
DESC USERS;

5.修改表结构

-- 修改表结构
ALTER TABLE USERS ADD COLUMN BZ VARCHAR(20);
ALTER TABLE USERS MODIFY COLUMN BZ INT;
ALTER TABLE USERS MODIFY BZ VARCHAR(20);

-- 不能把主键再设置为外键了
ALTER TABLE USERS_2 ADD CONSTRAINT TK FOREIGN KEY (UD) REFERENCES USERS(UID);
-- 删除外键
ALTER TABLE USERS_2 DROP CONSTRAINT TK;

-- 查看表结构
DESC RUNOOB_TBL;
DESC USERS;

-- 删除表
DROP TABLE USERS_2;

6.存储

定义：SQL语句集的封装，经编译创建并保存在数据库中，用户可通过指定存储过程的名字并给定参数(需要时)来调用执行。

-- 声明变量
SET @NUM1 = 10;
SET @NUM2 = 20;
-- 创建存储
CREATE PROCEDURE FC(IN NUM1 INT,IN NUM2 INT,OUT RES VARCHAR(20))
BEGIN
	IF NUM1 > NUM2 THEN 
		SELECT "NUM1 IS BIGGEST!" INTO RES;
	ELSE
		SELECT "NUM2 IS BIGGEST!" INTO RES;
	END IF;
END

-- 删除存储
DROP PROCEDURE FC;

-- 调用存储
CALL FC(@NUM1,@NUM2,@RES);

-- 查询值
SELECT @RES;

-- 查看数据库创建的存储
-- 查看指定存储过程
SHOW CREATE PROCEDURE db_kaima.OUT_ALL_INFO;

-- 显示该数据库存储
SHOW PROCEDURE STATUS WHERE DB = 'db_kaima';

7.触发器

定义：触发器是与表有关的数据库对象，在满足定义条件时触发，并执行触发器中定义的语句集合。不建议使用！

-- 触发器
CREATE TRIGGER TGR 
AFTER INSERT
ON USERS 
FOR EACH ROW
BEGIN
	INSERT INTO TIME VALUES(NOW());
END

-- 插入测试
INSERT INTO USERS VALUES (3,'LUCK','男','2002-02-01','AAA');
INSERT INTO USERS VALUES (4,'MOON','女','1498-06-05','');

-- 测试表格
SELECT * FROM USERS;
SELECT * FROM TIME;
-- 查看触发器
SHOW TRIGGERS FROM db_kaima;
SHOW TRIGGERS;

-- 删除触发器
DROP TRIGGER TGR;

-- 查看USERS表结构
DESC USERS;

8.关键字

1. 建表测试

CREATE TABLE TB1(
	AUTHOR VARCHAR(20),
	COUNT INT(10)
);

INSERT INTO TB1 VALUES('A',1);
INSERT INTO TB1 VALUES('B',1);
INSERT INTO TB1 VALUES('C',1);
INSERT INTO TB1 VALUES('D',1);
INSERT INTO TB1 VALUES('E',1);

CREATE TABLE TB2(
	ID INT,
	AUTHOR VARCHAR(20),
	COUNT INT(10)
);
INSERT INTO TB2 VALUES(1,'A',1);
INSERT INTO TB2 VALUES(2,'B',1);
INSERT INTO TB2 VALUES(3,'C',1);


CREATE TABLE TB3(
	UNAME VARCHAR(15) NOT NULL,
	USEX VARCHAR(2),
	UPHONENUMBER VARCHAR(11),
	ULOGIN INT
);

INSERT INTO TB3 VALUES('张三','男','11111111111',1);
INSERT INTO TB3 VALUES('李四','男','11111111111',2);
INSERT INTO TB3 VALUES('王五','男','11111111111',1);
INSERT INTO TB3 VALUES('老六','男','11111111111',5);
INSERT INTO TB3 VALUES('老七','男',NULL,6);

-- 查表
SELECT * FROM TB3;
-- 删除表
DROP TABLE TB3;

2. Join连接（左连接，右连接，内连接）

SELECT TB2.ID,TB2.AUTHOR,TB1.COUNT
FROM TB1 LEFT JOIN TB2 ON TB1.AUTHOR=TB2.AUTHOR;

SELECT TB2.ID,TB2.AUTHOR,TB1.COUNT
FROM TB1 RIGHT JOIN TB2 ON TB1.AUTHOR=TB2.AUTHOR;

SELECT TB2.ID,TB2.AUTHOR,TB1.COUNT
FROM TB1 INNER JOIN TB2 ON TB1.AUTHOR=TB2.AUTHOR;

3. Having（分组条件插足）

-- HAVING 测试
SELECT COALESCE(UNAME,'总数'),SUM(ULOGIN) AS COUNT FROM TB3 GROUP BY UNAME WITH ROLLUP HAVING SUM(ULOGIN>=2);
-- SELECT COALESCE(UNAME,'总数','测试'),SUM(ULOGIN) AS COUNT FROM TB3 GROUP BY UNAME WITH ROLLUP WHERE SUM(ULOGIN>=2); 不能用there要用having

4. WITH ROLLUP(分组的统计数据的基础上再进行相同的统计)

-- WITH ROLLUP测试
SELECT COALESCE(UNAME,'总数'),SUM(ULOGIN) AS COUNT FROM TB3 GROUP BY UNAME HAVING SUM(ULOGIN>=2);
-- 有了WITH ROLLUP会在分组执行完SUM(ULOGIN)，再执行一次SUM(ULOGIN)
SELECT COALESCE(UNAME,'总数'),SUM(ULOGIN) AS COUNT FROM TB3 GROUP BY UNAME WITH ROLLUP HAVING SUM(ULOGIN>=2);

5. Group by 分组

SELECT UNAME ,COUNT(UNAME) AS COUNT FROM TB3 GROUP BY UNAME;
SELECT UNAME,SUM(ULOGIN) AS COUNT FROM TB3 GROUP BY UNAME WITH ROLLUP;
SELECT COALESCE(UNAME,'总数'),SUM(ULOGIN) AS COUNT FROM TB3 GROUP BY UNAME WITH ROLLUP;

6. COALESCE

-- COALESCE(UNAME,'COALESCE') UNAME中为null的自动填补为COALESCE
SELECT UNAME,USEX,COALESCE(UPHONENUMBER,"COALESCE") FROM TB3;
SELECT COALESCE(UNAME,'总数','测试'),SUM(ULOGIN) AS COUNT FROM TB3 GROUP BY UNAME WITH ROLLUP;
SELECT '总人数',COUNT(UNAME) AS COUNT FROM TB3;