sql语句

sql语句基本用法

向学生表中插入5行数据，用以模拟完成学生注册时向数据库中写入的数据

每个学生向项目库中提交两个项目（一个立项，一个未立项）

-- 创建学生表

CREATE TABLE student(id INT PRIMARY KEY COMMENT '主键',

NAME VARCHAR(10) NOT NULL COMMENT '姓名'

)

 

-- 创建项目表

CREATE TABLE project(pid INT PRIMARY KEY COMMENT '项目id主键',

pname VARCHAR(20) NOT NULL COMMENT '项目名称',

pdesc VARCHAR(100) NULL,

tijiaoren INT )

 

-- 添加表之间的关系

ALTER TABLE project ADD FOREIGN KEY fk_project_student(tijiaoren) REFERENCES student(id)

插入

INSERT INTO student(id,NAME) VALUES(1,'学生1');

INSERT INTO student(id,NAME) VALUES(2,'学生2');

INSERT INTO student(id,NAME) VALUES(3,'学生3');

INSERT INTO student(id,NAME) VALUES(4,'学生4');

INSERT INTO student(id,NAME) VALUES(5,'学生5');

 

INSERT INTO project(pid,pname,tijiaoren) VALUES(1,'项目11',1);

INSERT INTO project(pid,pname,tijiaoren) VALUES(2,'项目12',1);

 

INSERT INTO project(pid,pname,tijiaoren) VALUES(3,'项目21',2);

INSERT INTO project(pid,pname,tijiaoren) VALUES(4,'项目22',2);

INSERT INTO project(pid,pname,tijiaoren) VALUES(5,'项目31',3);

INSERT INTO project(pid,pname,tijiaoren) VALUES(6,'项目32',3);

INSERT INTO project(pid,pname,tijiaoren) VALUES(7,'项目41',4);

INSERT INTO project(pid,pname,tijiaoren) VALUES(8,'项目42',4);

INSERT INTO project(pid,pname,tijiaoren) VALUES(9,'项目51',5);

INSERT INTO project(pid,pname,tijiaoren) VALUES(10,'项目52',5);

INSERT INTO project(pid,pname,tijiaoren) VALUES(11,'项目52aa',5);

ALTER TABLE project ADD COLUMN lixiang VARCHAR(10) DEFAULT '否'

 

-- 一次插入多个值

INSERT INTO project(pid,pname,tijiaoren) VALUES(12,'项目53',5),(13,'项目55',5);

SHOW TABLES

查询

SELECT 1+2 FROM DUAL

 

-- 查询项目表中的所有字段

SELECT pname,pid,tijiaoren,lixiang FROM project

-- 使用通配符*代表表中的所有字段

SELECT * FROM project

-- 查询学生3 提交的项目

SELECT pname,pid,tijiaoren,lixiang FROM project

WHERE tijiaoren=3

 

-- 统计一下每个学生提交了几个项目

SELECT tijiaoren,COUNT(*) FROM project

GROUP BY tijiaoren

 

-- 找出项目提交数量不足3个的学生id

 

SELECT tijiaoren,COUNT(*) FROM project

GROUP BY tijiaoren

HAVING COUNT(*)<3

 

-- 按照学生编号从大到小的顺序显示其提交的项目

SELECT * FROM project

ORDER BY tijiaoren DESC

 

-- 按照学生编号从小到大的顺序显示项目，一次最多显示3行

SELECT * FROM project

ORDER BY tijiaoren ASC LIMIT 3

 

 

 

插入示例

SHOW TABLES

 

DESC hero_class

 

INSERT INTO hero_class(id,classname) VALUES(1,'分类1')

INSERT INTO hero_class(id,classname) VALUES(2,'分类2')

INSERT INTO hero_class(id,classname) VALUES(3,'分类3')

 

DESC hero_table71

INSERT INTO hero_table71(id,NAME,birth) VALUES(1,'宋江','1992-08-05')

INSERT INTO hero_table71(id,NAME) VALUES(2,'卢俊义')

 

DESC hero_table5

INSERT INTO hero_table5(NAME) VALUES('宋江')

INSERT INTO hero_table5(NAME) VALUES('卢俊义')

INSERT INTO hero_table5(id,NAME) VALUES(10,'吴用')

INSERT INTO hero_table5(id,NAME) VALUES(3,'吴用11')

INSERT INTO hero_table5(NAME) VALUES('武松')

INSERT INTO hero_table5(NAME) VALUES('武松22')

 

查询

 

-- 内连接

SELECT * FROM project , student WHERE project.tijiaoren=student.id;

SELECT * FROM project INNER JOIN student ON project.tijiaoren=student.id

 

SELECT t.id AS tid,t.name AS tname,student.id AS sid,student.name AS sname FROM t INNER JOIN student ON t.id=student.id

 

-- 表的别名

SELECT * FROM project a,student b WHERE a.tijiaoren=b.id;

 

CREATE TABLE tba(a1 INT,a2 INT)

CREATE TABLE tbb(b1 INT,b2 INT)

 

INSERT INTO tba(a1,a2) VALUES(1,2),(3,4)

INSERT INTO tbb(b1,b2) VALUES(1,5),(8,10)

-- 笛卡尔积

SELECT * FROM tba,tbb

-- 内连接

SELECT * FROM tba INNER JOIN tbb ON tba.a1=tbb.b1;

-- 左外连接

SELECT * FROM tba LEFT JOIN tbb ON tba.a1=tbb.b1

-- 右外连接

SELECT * FROM tba RIGHT JOIN tbb ON tba.a1=tbb.b1

-- 全外连接

SELECT * FROM tba LEFT JOIN tbb ON tba.a1=tbb.b1

UNION

SELECT * FROM tba RIGHT JOIN tbb ON tba.a1=tbb.b1

 

--  根据学生名字查询该学生提交的项目

SELECT * FROM project , student WHERE project.tijiaoren=student.id AND student.name LIKE '%3%';

-- 使用子查询

SELECT * FROM project WHERE tijiaoren IN (SELECT id FROM student WHERE NAME LIKE '%3%');

-- 子查询作为操作数

SELECT (SELECT id FROM student WHERE id=1) FROM DUAL

-- 子查询作为条件

SELECT * FROM project WHERE tijiaoren>(SELECT 2 FROM DUAL)

 

SELECT * FROM project WHERE tijiaoren> ANY (SELECT id FROM student WHERE id>3)

SELECT * FROM project WHERE tijiaoren> ALL (SELECT id FROM student WHERE id>3)

-- 多列比较

SELECT (3,2)<(3,4) FROM DUAL

 

-- exists 和not exists

--  根据学生名字查询该学生提交的项目

SELECT * FROM project WHERE EXISTS ( SELECT id FROM student WHERE id=project.tijiaoren AND NAME LIKE '%3%')

 

-- 子查询作为数据表使用

SELECT * FROM (SELECT * FROM project , student WHERE project.tijiaoren=student.id)  a WHERE a.name LIKE '%3%'

 

 

SELECT * FROM project

修改

-- 修改数据

-- 将所有偶数项目id的状态变更为是

UPDATE project SET lixiang='是' WHERE MOD(pid,2)=0

 

-- 将项目序号5以内的项目介绍更新为项目名称+项目id

SELECT CONCAT(pname,CAST(pid AS CHAR)) FROM project

UPDATE project SET pdesc= CONCAT(pname,CAST(pid AS CHAR)) WHERE pid<5

SELECT * FROM project

-- update可以更新多列

UPDATE project SET pdesc='123',pname=CONCAT(pname,'123') WHERE pid BETWEEN 5 AND 8

删除

 

-- 删除表中项目id大于11的数据

DELETE FROM project WHERE pid>11

 

-- 删除或更新时的约束

UPDATE project tijiaoren=6 WHERE pid=11

 

DELETE FROM student WHERE id=5

 

DELETE FROM project WHERE tijiaoren=5

DELETE FROM student WHERE id=5

 

SELECT * FROM student

 

SELECT MOD(3,2),MOD(4,2) FROM DUAL

 

触发器

 

CREATE TABLE dingdan(dingdanid INT PRIMARY KEY,zhuangtai VARCHAR(20));

CREATE TABLE mingxi(mingxiid INT PRIMARY KEY,shangpinid INT,lingyong INT,dingdanid INT);

CREATE TABLE shangpin(shangpinid INT NOT NULL PRIMARY KEY,shuliang INT);

INSERT INTO shangpin (shangpinid,shuliang)  VALUES(1,100);

INSERT INTO shangpin (shangpinid,shuliang)  VALUES(2,100);

INSERT INTO shangpin (shangpinid,shuliang)  VALUES(3,100);

INSERT INTO dingdan(dingdanid,zhuangtai) VALUES(1,'no')

INSERT INTO mingxi(mingxiid,shangpinid,lingyong,dingdanid) VALUES(1,1,5,1);

INSERT INTO mingxi(mingxiid,shangpinid,lingyong,dingdanid) VALUES(2,2,14,1);

SELECT * FROM dingdan

DELIMITER $$

CREATE TRIGGER trg01 AFTER UPDATE ON dingdan

FOR EACH ROW BEGIN

    IF new.zhuangtai='yes' AND old.zhuangtai!='yes' THEN

       UPDATE shangpin a SET shuliang=shuliang-IFNULL((SELECT lingyong FROM mingxi b WHERE b.dingdanid=new.dingdanid AND b.shangpinid=a.shangpinid ),0);

    END IF;

END$$

DELIMITER ;

UPDATE dingdan SET zhuangtai='yes'

SELECT * FROM shangpin