mysql-增删改查操作

-- 查询语句

SELECT * FROM student; -- 效率略低，一般不建议使用

SELECT tid,tname FROM student;

SELECT tname AS 姓名 FROM student;

SELECT * FROM student WHERE 1=1;

SELECT tname FROM student; -- 指定查询个别列

SELECT USER,PASSWORD,HOST FROM mysql.user; -- 跨库查询mysql数据库的user表内的用户名、密码、主机

-- 创建学生信息表

CREATE TABLE stu(

sno INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,

sname VARCHAR(10) NOT NULL,

sgender ENUM(‘男‘,‘女‘) DEFAULT ‘男‘,

saddress VARCHAR(50),

sscore TINYINT UNSIGNED,

sdept VARCHAR(20) NOT NULL

)ENGINE=MYISAM AUTO_INCREMENT=2014270001 DEFAULT CHARSET=utf8;

INSERT INTO stu VALUES(NULL,‘董华‘,‘男‘,‘河南省郑州市‘,80,‘计算机网络‘);

INSERT INTO stu VALUES(NULL,‘代珂‘,‘男‘,‘河南省平顶山‘,85,‘计算机安全‘);

INSERT INTO stu VALUES(NULL,‘小江‘,‘男‘,‘河南省商丘‘,70,‘计算机网络‘);

INSERT INTO stu VALUES(NULL,‘小宾‘,‘男‘,‘河南省商丘市‘,80,‘计算机安全‘);

INSERT INTO stu VALUES(NULL,‘小丽‘,‘女‘,‘河南省安阳市‘,90,‘化工食品‘);

INSERT INTO stu VALUES(NULL,‘小华‘,‘男‘,‘北京市‘,48,‘化工食品‘);

INSERT INTO stu VALUES(NULL,‘凯利‘,‘女‘,‘上海市‘,74,‘生物工程‘);

INSERT INTO stu VALUES(NULL,‘王菲‘,‘女‘,‘上海市‘,81,‘生物工程‘);

INSERT INTO stu VALUES(NULL,‘丽萨‘,‘女‘,‘河北省天津市‘,59,‘生物工程‘);

INSERT INTO stu VALUES(NULL,‘李莉‘,‘女‘,‘河北省天津市‘,0,‘化工食品‘);

INSERT INTO stu VALUES(NULL,‘张闯‘,‘男‘,‘河北省天津市‘,NULL,‘计算机网络‘);

INSERT INTO stu VALUES(NULL,‘张秋千‘,‘男‘,‘安徽省合肥市‘,NULL,‘计算机安全‘);

INSERT INTO stu VALUES(NULL,‘张李秋‘,‘男‘,‘安徽省‘,67,‘建筑工程‘);

INSERT INTO stu VALUES(NULL,‘秋香‘,NULL,‘安徽省‘,95,‘建筑工程‘);

-- 条件查询 

-- 空条件判断 IS NULL 或 IS NOT NULL

SELECT * FROM stu WHERE sscore IS NULL;

SELECT sno,sname,sscore FROM stu WHERE sscore IS NOT NULL; 

-- IN() 在...范围内 或 NOT IN()不在...范围内

SELECT * FROM stu WHERE sdept IN(‘计算机网络‘,‘计算机安全‘);

SELECT * FROM stu WHERE sdept NOT IN(‘计算机网络‘,‘计算机安全‘); 

-- BETWEEN AND 在...之间 或 NOT BETWEEN AND 不在...之间

SELECT * FROM stu WHERE sscore NOT BETWEEN 70 AND 90;

SELECT * FROM stu WHERE sscore BETWEEN 70 AND 90; 

-- NOT 取反运算符 ADN 与运算符 OR 或运算符

SELECT * FROM stu WHERE sdept = ‘建筑工程‘ OR sdept = ‘生物工程‘;

SELECT * FROM stu WHERE sgender = ‘男‘ AND sdept = ‘计算机网络‘; 

-- LIKE ‘张%‘ 模糊查找运算符(%:代表0-n多个字符，_代表一个字符)

SELECT * FROM stu WHERE sname LIKE ‘李%‘;

SELECT * FROM stu WHERE sname LIKE ‘李_‘;

SELECT * FROM stu WHERE sname LIKE ‘%李%‘; 

SELECT * FROM stu WHERE sname NOT LIKE ‘%李%‘; 

-- 排序操作 DESC代表降序排序，ASC代表升序排序，默认升序排序

SELECT * FROM stu ORDER BY sscore DESC;

SELECT * FROM stu ORDER BY sscore ASC; 

-- LIMIT 限制结果显示行数(用法：LIMIT 5 显示5行  或 LIMIT 0,5 从0位置开始显示5行,0代表数据库内起始行数),LIMIT 在SQL语句最后面

SELECT * FROM stu LIMIT 5;

SELECT * FROM stu LIMIT 0,5;

-- IFNULL() 与 IF()两个函数的使用及区别:IFNULL(exp1,exp2) = IF(exp1 IS NULL,exp2,exp3)

SELECT sno AS 学号,sname AS 姓名,sgender AS 性别,IFNULL(sscore,‘旷考‘) AS 成绩 FROM stu;

SELECT sno AS 学号,sname AS 姓名,sgender AS 性别,IF(sscore IS NULL,‘旷考‘,sscore) AS 成绩 FROM stu;

-- 集合函数(聚集函数)

AVG() -- 求平均值 (计算平均时，不包含空值)

SUM() -- 求和

COUNT() -- 求总记录数

MAX() -- 最大值

MIN() -- 最小值

SELECT AVG(sscore) 平均分,SUM(sscore) 总分,MAX(sscore) 最高分,MIN(sscore) 最低分,COUNT(*) 总人数,COUNT(sscore) 考试人数 FROM stu;

SELECT COUNT(DISTINCT sdept) 专业 FROM stu; -- DISTINCT 去除重复

SELECT sdept,AVG(sscore) FROM stu GROUP BY sdept; -- 分组查询,一般前面是分组名称，紧跟着是集合函数(聚集函数)

-- 条件中的那个查询是子查询，显示出最高分的学生信息

SELECT * FROM stu WHERE sscore =(

SELECT MAX(sscore) FROM stu -- 子查询，当子查询查到的数据只有一个时，可以用‘=‘,多于一条时只能用‘IN‘

);

SELECT * FROM stu WHERE sscore IN(

SELECT MAX(sscore) FROM stu -- 子查询

);

-- 统计男、女生分别有几人

SELECT IFNULL(sgender,‘保密‘),COUNT(*) FROM stu GROUP BY sgender;

-- 统计各个专业的平均成绩，显示平均成绩不小于60分的专业的前两条数据

SELECT sdept,AVG(sscore) FROM stu WHERE 1=1 GROUP BY sdept HAVING AVG(sscore)>=60 ORDER BY AVG(sscore) DESC LIMIT 2;

WHERE 查询条件

GROUP BY 分组

HAVING 是分组条件（使用前提是使用了分组）

ORDER BY 排序

LIMIT 取几条记录

-- 多表连接查询  查询结果是多个表中的信息

CREATE TABLE student(

sid INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,

sname VARCHAR(10) NOT NULL,

sage TINYINT UNSIGNED,

stid SMALLINT UNSIGNED

)ENGINE=MYISAM DEFAULT CHARSET=utf8;

CREATE TABLE teacher(

tid SMALLINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,

tname VARCHAR(10) NOT NULL

)ENGINE=MYISAM DEFAULT CHARSET=utf8;

INSERT INTO teacher VALUES(NULL,‘张老师‘);

INSERT INTO teacher VALUES(NULL,‘王老师‘);

INSERT INTO teacher VALUES(NULL,‘赵老师‘);

INSERT INTO teacher VALUES(NULL,‘李老师‘);

SELECT * FROM teacher;

INSERT INTO student VALUES(NULL,‘曹操‘,20,1);

INSERT INTO student VALUES(NULL,‘张飞‘,27,4);

INSERT INTO student VALUES(NULL,‘关羽‘,29,2);

INSERT INTO student VALUES(NULL,‘刘备‘,50,2);

INSERT INTO student VALUES(NULL,‘孙权‘,40,4);

INSERT INTO student VALUES(NULL,‘诸葛亮‘,24,3);

INSERT INTO student VALUES(NULL,‘姚盖‘,25,1);

INSERT INTO student VALUES(NULL,‘大乔‘,25,5);

SELECT * FROM student;

TRUNCATE student;

-- 查询学生学号、姓名、年龄及代课老师的姓名：

-- 等值链接查询

SELECT sid,sname,sage,tname FROM student,teacher WHERE student.stid=teacher.tid;

-- 左链接查询

SELECT sid,sname,sage,IFNULL(tname,‘无‘) FROM student s LEFT JOIN teacher t ON s.stid=t.tid;

-- 右链接查询

SELECT sid,sname,sage,IFNULL(tname,‘无‘) FROM student s RIGHT JOIN teacher t ON s.stid=t.tid;

-- 表的自身链接查询

SELECT a.sid,a.sname,b.sname FROM student a,student b WHERE a.sid<b.sid;

-- INSERT 插入语句

CREATE TABLE t1(

tid INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,

tname VARCHAR(10) NOT NULL,

tage TINYINT UNSIGNED

)ENGINE=MYISAM DEFAULT CHARSET=utf8;

INSERT INTO t1 VALUES(NULL,‘jack‘,18); -- 插入一条数据

INSERT INTO t1 VALUES(NULL,‘jane‘,20),(NULL,‘lili‘,10); -- 插入多条数据

INSERT INTO t1 (tname,tage) VALUES(‘张三‘，20); -- 插入一条数据，字段对应

INSERT INTO t1 SELECT * FROM t1; -- 将查询的数据插入数据库(前提是没有主键约束)

REPLACE INTO t1 VALUES(1,‘库克‘,35); -- 如果主键相同则替换插入，不同则插入

-- DELETE 删除语句

DELETE FROM t1 WHERE tid=1; -- 删除t1表内tid为1的数据

DELETE FROM t1; -- 删除t1表内的所有数据(再次插入数据，主键接着之前的增加)

TRUNCATE t1; -- 截断表内的所有数据，清空数据，保留结构(再次插入数据的话，主键重新开始),不触发触发器

-- drop 删除语句

DROP DATABASE db;-- 删除数据库

DROP TABLE t1;-- 删除表

-- UPDATE 更新修改语句

UPDATE t1 SET tname = ‘小可‘,tage=18 WHERE tid =2;

本文出自 “夜风” 博客，请务必保留此出处http://jiangkun08.blog.51cto.com/6266992/1548533