MySQL基础_select count(0) from-CSDN博客

本文链接：https://blog.csdn.net/guanmao4322/article/details/83348769

本文主要解释一下sql语句的基础，有一定基础者可转向另一篇直接查询使用

链接为：https://blog.csdn.net/guanmao4322/article/details/83348661

数据库基础

2.对表单的操作

首先使用（use 数据库名）进入数据库，之后进行以后步骤

3.查询表单

实例（均以t_student表为例）

SELECT id,stuName,age,sex,gradeName FROM t_student ; 查询表中全部数据（返回全部字段）

SELECT stuName,id,age,sex,gradeName FROM t_student ; 查询表中指定数据（返回指定字段）

SELECT * FROM t_student; 查询表中全部数据（同一）

SELECT stuName,gradeName FROM t_student; 查询表中指定数据（返回指定字段）

SELECT * FROM t_student WHERE id=1; 查询表中id=1的数据（返回id=1的全部字段）

SELECT * FROM t_student WHERE age>22; 查询表中年龄大于22岁的数据（返回全部字段）

SELECT * FROM t_student WHERE age IN (21,23); 查询表中年龄为21或23的数据（返回全部字段）

SELECT * FROM t_student WHERE age NOT IN (21,23); 查询表中年龄不是21或23的数据（返回全部字段）

SELECT * FROM t_student WHERE age BETWEEN 21 AND 24; 查询年龄在21-24之间的数据（返回全部字段）

SELECT * FROM t_student WHERE age NOT BETWEEN 21 AND 24; 查询年龄不在21-24之间的数据（返回全部字段）

SELECT * FROM t_student WHERE stuName LIKE '张三'; 查询名字与张三相同的数据（返回全部字段）

SELECT * FROM t_student WHERE stuName LIKE '张三%'; 查询名字中以张三开头的数据（返回全部字段）（见上图）

SELECT * FROM t_student WHERE stuName LIKE '张三__'; 查询名字中含有张三但是是三个字的名字的数据（返回全部字段）

SELECT * FROM t_student WHERE stuName LIKE '%张三%'; 查询名字中含有张三的全部数据（返回全部字段）

SELECT * FROM t_student WHERE sex IS NULL; 查询性别为空的数据（返回全部字段）

SELECT * FROM t_student WHERE sex IS NOT NULL; 查询性别不为空的数据（返回全部字段）

SELECT * FROM t_student WHERE gradeName='一年级' AND age=23 查询年级为‘一年级’并且年龄为‘23’的数据（返回全部字段）

SELECT * FROM t_student WHERE gradeName='一年级' OR age=23 查询年级为‘一年级’或年龄为‘23’的学生数据（返回全部字段）

SELECT DISTINCT gradeName FROM t_student; 查询该表中所有的年级并去掉重复部分（返回年级)

SELECT * FROM t_student ORDER BY age ASC; 查询该表中全部数据，并以年龄进行升序排序（返回全部字段）ASC表示升序排列

SELECT * FROM t_student ORDER BY age DESC; 查询该表中全部数据，并以年龄进行升序排序（返回全部字段）ASC表示升序排列

SELECT * FROM t_student GROUP BY gradeName; 查询根据年级来分组的结果（返回全部字段）无现实意义（见上图）

SELECT gradeName,GROUP_CONCAT(stuName) FROM t_student GROUP BY gradeName; 查询该表中根据年级分组的结果，返回年级和该年级中所有的学生姓名（见上图）

SELECT gradeName,COUNT(stuName) FROM t_student GROUP BY gradeName; 查询该表中根据年级分组的结果，返回年级和该年级中所有的学生数量（见上图）

SELECT gradeName,COUNT(stuName) FROM t_student GROUP BY gradeName HAVING COUNT(stuName)>3; 查询该表中根据年级分组并且该年级人数要大于3的结果，返回年级和该年级中所有的学生数量（见上图）

SELECT gradeName,COUNT(stuName) FROM t_student GROUP BY gradeName WITH ROLLUP; 查询该表中根据年级分组的结果，返回年级和该年级中所有的学生数量及学生总人数（见上图）

SELECT gradeName,GROUP_CONCAT(stuName) FROM t_student GROUP BY gradeName WITH ROLLUP; 查询该表中根据年级分组的结果，返回年级和该年级中所有的学生姓名及全部学生的姓名（见上图）

SELECT * FROM t_student LIMIT 0,5; 在该表中查询从0开始，取5组数据（返回全部字段）

SELECT * FROM t_student LIMIT 5,5; 在该表中查询从5开始，取5组数据（返回全部字段）

SELECT * FROM t_student LIMIT 10,5; 在该表中查询从10开始，取5组数据（返回全部字段）

4.使用聚合函数查询

实例（以t_grade为例）

SELECT COUNT(*) FROM t_grade; 查询该表中数据的条数

SELECT COUNT(*) AS total FROM t_grade; 查询该表中数据的条数并命名为total

SELECT stuName,COUNT(*) FROM t_grade GROUP BY stuName; 根据stuName分组，并返回学生姓名和该姓名对应的数量

SELECT stuName,SUM(score) FROM t_grade WHERE stuName="张三"; 查询张三的分数总和

SELECT stuName,SUM(score) FROM t_grade GROUP BY stuName; 根据学生的姓名分组，返回每个姓名对应的分数总和

SELECT stuName,AVG(score) FROM t_grade WHERE stuName="张三"; 查询张三的分数的平均值

SELECT stuName,AVG(score) FROM t_grade GROUP BY stuName; 根据学生姓名进行分组，返回每个姓名对应的分数的均值

SELECT stuName,course,MAX(score) FROM t_grade WHERE stuName="张三"; 查询张三的分数的最大值，返回姓名，课程，课程分数

SELECT stuName,MAX(score) FROM t_grade GROUP BY stuName; 根据学生姓名进行分组，返回每个姓名对应的最高分

SELECT stuName,course,MIN(score) FROM t_grade WHERE stuName="张三"; 查询张三的分数的最小值，返回姓名，课程，课程分数

SELECT stuName,MIN(score) FROM t_grade GROUP BY stuName; 根据学生姓名进行分组，返回每个姓名对应的最低分

5.连接查询

实例所用的表（t_book,t_bookType）

SELECT * FROM t_book,t_bookType; 笛卡尔乘积

SELECT * FROM t_book,t_bookType WHERE t_book.bookTypeId=t_bookType.id; 内连接查询，查询条件为t_book表中bookTypeId与表t_bookType中的id相等

SELECT bookName,author,bookTypeName FROM t_book,t_bookType WHERE t_book.bookTypeId=t_bookType.id; 内连接查询，查询条件为t_book表中bookTypeId与表t_bookType中的id相等

SELECT tb.bookName,tb.author,tby.bookTypeName FROM t_book tb,t_bookType tby WHERE tb.bookTypeId=tby.id; 内连接查询，查询条件为t_book表中bookTypeId与表t_bookType中的id相等（使用别名）

SELECT * FROM t_book LEFT JOIN t_bookType ON t_book.bookTypeId=t_bookType.id; 左连接查询

SELECT tb.bookName,tb.author,tby.bookTypeName FROM t_book tb LEFT JOIN t_bookType tby ON tb.bookTypeId=tby.id; 左连接查询（使用别名）

SELECT * FROM t_book RIGHT JOIN t_bookType ON t_book.bookTypeId=t_bookType.id; 右连接查询

SELECT tb.bookName,tb.author,tby.bookTypeName FROM t_book tb RIGHT JOIN t_bookType tby ON tb.bookTypeId=tby.id; 右连接查询（使用别名）

SELECT tb.bookName,tb.author,tby.bookTypeName FROM t_book tb,t_bookType tby WHERE tb.bookTypeId=tby.id AND tb.price>70; 多表联查

实例（以上述三个表为例，查询时，先内查询后外查询）

SELECT * FROM t_book WHERE booktypeId IN (SELECT id FROM t_booktype);（1先在t_booktype表中查询所有id）之后在t_book表中查询booktypeID与id相等的数据

SELECT * FROM t_book WHERE booktypeId NOT IN (SELECT id FROM t_booktype);

SELECT * FROM t_book WHERE price>=(SELECT price FROM t_pricelevel WHERE priceLevel=1);（先内查询，在t_pricelevel表中查询priceLevel=1数据条中的price）之后外查询，在t_book表中查询price>=内查询对的price的数据

SELECT * FROM t_book WHERE EXISTS (SELECT * FROM t_booktype);（先内查询，若表t_booktype中有数据则返回TRUE，执行外查询，否则结束）之后进行外查询

SELECT * FROM t_book WHERE NOT EXISTS (SELECT * FROM t_booktype);

SELECT * FROM t_book WHERE price>= ANY (SELECT price FROM t_pricelevel);（先内查询，在t_pricelevel表中查询所有的price）之后外查询，要求price大于任意一个内查询的price

SELECT * FROM t_book WHERE price>= ALL (SELECT price FROM t_pricelevel);（先内查询，在t_pricelevel表中查询所有的price）之后外查询，要求price大于所有的内查询price

5.合并查询结果

实例

SELECT id FROM t_book; 准备材料

SELECT id FROM t_booktype; 准备材料

SELECT id FROM t_book UNION SELECT id FROM t_booktype; 将两个表中的id合并（去除相同数据）

SELECT id FROM t_book UNION ALL SELECT id FROM t_booktype; 将两张表中的id合并（保留相同数据）

SELECT * FROM t_book WHERE id=1; 查询id=1的全部数据

SELECT * FROM t_book t WHERE t.id=1; 使用表别名的方式查询id=1的全部数据

SELECT t.bookName FROM t_book t WHERE t.id=1; 使用表别名的方式查询表t_book中id=1的数据条中bookName的数据

SELECT t.bookName bName FROM t_book t WHERE t.id=1; 使用字段别名

SELECT t.bookName AS bName FROM t_book t WHERE t.id=1; 使用字段别名

6.增删改查

实例（以表t_book为例）

INSERT INTO t_book VALUES(NULL,'我爱我家',20,'张三',1); 按照字段名称插入数据

INSERT INTO t_book(id,bookName,price,author,bookTypeId) VALUES(NULL,'我爱我家',20,'张三',1); 按照字段名称插入数据，与上一条效果相同

INSERT INTO t_book(bookName,author) VALUES('我爱我家','张三'); 向指定字段插入数据

INSERT INTO t_book(id,bookName,price,author,bookTypeId) VALUES (NULL,'我爱我家2',20,'张三',1),(NULL,'我爱我家3',20,'张三',1); 向表中插入多条数据

UPDATE t_book SET bookName='Java编程思想',price=120 WHERE id=1; 更新表t_book中，id=1中bookName与price的数据

UPDATE t_book SET bookName='我' WHERE bookName LIKE '%我爱我家%'; 更新表t_book中，bookName为‘我爱我家’的书籍名称为‘我’

DELETE FROM t_book WHERE id=5;

DELETE FROM t_book WHERE bookName='我';

7.索引

创建索引

实例

CREATE TABLE t_user1(id INT ,

userName VARCHAR(20),

PASSWORD VARCHAR(20),

INDEX (userName) 在创建表的时候创建索引

);

CREATE TABLE t_user2(id INT ,

userName VARCHAR(20),

PASSWORD VARCHAR(20),

UNIQUE INDEX index_userName(userName)

);

CREATE TABLE t_user3(id INT ,

userName VARCHAR(20),

PASSWORD VARCHAR(20),

INDEX index_userName_password(userName,PASSWORD)

);

CREATE INDEX index_userName ON t_user4(userName);

CREATE UNIQUE INDEX index_userName ON t_user4(userName);

CREATE INDEX index_userName_password ON t_user4(userName,PASSWORD);

ALTER TABLE t_user5 ADD INDEX index_userName(userName);

ALTER TABLE t_user5 ADD UNIQUE INDEX index_userName(userName);

ALTER TABLE t_user5 ADD INDEX index_userName_password(userName,PASSWORD);

DROP INDEX index_userName ON t_user5;

DROP INDEX index_userName_password ON t_user5;

视图

CREATE VIEW v1 AS SELECT * FROM t_book; 创建视图：选择表t_book中的全部信息，从而创建视图v1；（单表）

CREATE VIEW v2 AS SELECT bookName,price FROM t_book; 选择表t_book中的bookName，price的信息，创建视图v2

CREATE VIEW v3(b,p) AS SELECT bookName,price FROM t_book; 选择表t_book中的bookName，price的信息，创建视图v3,并将这两个字段命名为b,p

SELECT * FROM v1; 查看视图v1

SELECT * FROM v2; 查看视图v2

SELECT * FROM v3; 查看视图v3

CREATE VIEW v4 AS SELECT bookName,bookTypeName FROM t_book,t_booktype WHERE t_book.bookTypeId=t_booktype.id; 多表创建视图：选择表t_book,t_booktype，中bookTypeId=id行中的

bookName和bookTypeName字段创建视图v4

CREATE VIEW v5 AS SELECT tb.bookName,tby.bookTypeName FROM t_book tb,t_booktype tby WHERE tb.bookTypeId=tby.id; 多表创建视图：（同上）表名使用别名

SELECT * FROM v4; 查看视图v4

SELECT * FROM v5; 查看视图v5

DESC v5; 查看视图基本信息

SHOW TABLE STATUS LIKE 'v5'; 查看视图基本信息

SHOW TABLE STATUS LIKE 't_book'; 查看表的基本信息

SHOW CREATE VIEW v5; 查看视图的详细信息

SELECT * FROM v1; 查看视图v1

CREATE OR REPLACE VIEW v1(bookName,price) AS SELECT bookName,price FROM t_book; 创建或更改视图v1，更改为从表t_book中选择字段的形式

ALTER VIEW v1 AS SELECT * FROM t_book; 修改视图v1为从表中选择的全部字段

INSERT INTO v1 VALUES(NULL,'java good',120,'feng',1); 向视图v1中插入对应的数据（向视图插入相当于向表中插入）

UPDATE v1 SET bookName='java very good',price=200 WHERE id=5; 更新视图id=5的数据

DELETE FROM v1 WHERE id=5; 删除视图中id=5的行数据

DROP VIEW IF EXISTS v4; 删除已存在的视图v4

触发器（15讲）

作用：假如在a表中添加一个数据，相应的也需要在b表中添加相同的数据，此时就可以使用触发器；当a表中增加数据时，激活触发器执行相应的操作。

创建触发器（只有一个执行语句的触发器）

CREATE TRIGGER trig_book AFTER INSERT #创建一个触发器在insert一条数据之后触发

ON t_book FOR EACH ROW #在表t_book中insert一条数据之后

UPDATE t_bookType SET bookNum=bookNum+1 WHERE new.bookTypeId=t_booktype.id; #在insert一条数据之后，激活触发器，执行该update语句

创建有多个执行语句的触发器

举例：INSERT INTO t_book VALUES(NULL,'java好',100,'ke',1);

DELIMITER | #加分隔符，防止程序执行到“；”时自动结束

CREATE TRIGGER trig_book2 AFTER DELETE

ON t_book FOR EACH ROW

BEGIN

UPDATE t_bookType SET bookNum=bookNum-1 WHERE old.bookTypeId=t_booktype.id;

INSERT INTO t_log VALUES(NULL,NOW(),'在book表里删除了一条数据');

DELETE FROM t_test WHERE old.bookTypeId=t_test.id;

END

DELIMITER ;

举例：DELETE FROM t_book WHERE id=5;

SHOW TRIGGERS; 查看触发器

DROP TRIGGER trig_book2 ; 删除触发器

SELECT CURDATE(),CURTIME(),MONTH(birthday) AS m FROM t_t; 在表t_t中获取当前日期，当前时间，以及字段birthday的月数，并给月数别名m

SELECT userName,CHAR_LENGTH(userName),UPPER(userName),LOWER(userName) FROM t_t; 在表中获取userName的字符串个数以及将其转换为大写和小写

SELECT num,ABS(num) FROM t_t; 在表中选取num字段，并取其绝对值

SELECT SQRT(4),MOD(9,4) FROM t_t; 求平方根和余数

INSERT INTO t_t VALUES(NULL,'2013-1-1','a',1,PASSWORD('123456')); 使用password加密密码，不可逆

INSERT INTO t_t VALUES(NULL,'2013-1-1','a',1,MD5('123456')); 使用MD5加密密码，不可逆

INSERT INTO t_t VALUES(NULL,'2013-1-1','a',1,MD5('123456'),ENCODE('abcd','aa')); 使用encode加密，其中abcd为密码，aa为加密的秘钥，加密字段必须为blob的存储格式

SELECT DECODE(pp,'aa') FROM t_t WHERE id=5; 使用decode解密，pp为加密后数字，aa为加密秘钥

举例："select username,decode(password,'admin') as password from user where username = '" + username+ "'";

存储过程和函数

DELIMITER && 定义SQL语句执行结束标志

CREATE PROCEDURE pro_book ( IN bT INT,OUT count_num INT) 创建一个存储过程名称为pro_book输入为bT（int型），输出为count_num，输入与输出的执行关系在begin与end之间

READS SQL DATA

BEGIN

SELECT COUNT(*) FROM t_book WHERE bookTypeId=bT; 执行关系

END

&& 结束标志

DELIMITER ;

CALL pro_book(1,@total);

DELIMITER &&

CREATE FUNCTION func_book (bookId INT) 创建一个存储函数名称为func_book输入为bookId，输入与输出的执行关系在begin与end之间

RETURNS VARCHAR(20)

BEGIN

RETURN ( SELECT bookName FROM t_book WHERE id=bookId );

END

DELIMITER ;

SELECT func_book(2);

DELIMITER && 定义结束标志

CREATE PROCEDURE pro_user() 创建一个存储过程，名称为pro_user

BEGIN

DECLARE a,b VARCHAR(20) ; 定义变量：变量名为a,b

INSERT INTO t_user VALUES(NULL,a,b); 向表中插入数据

END

DELIMITER ;

DELIMITER &&

CREATE PROCEDURE pro_user2()

BEGIN

DECLARE a,b VARCHAR(20) ;

SET a='java1234',b='123456'; 给变量幅值

INSERT INTO t_user VALUES(NULL,a,b);

END

DELIMITER ;

DELIMITER &&

CREATE PROCEDURE pro_user3()

BEGIN

DECLARE a,b VARCHAR(20) ;

SELECT userName2,password2 INTO a,b FROM t_user2 WHERE id2=1; 在表中查询userName2，password2字段，并将其字段的值赋予a,b

INSERT INTO t_user VALUES(NULL,a,b);

END

DELIMITER ;

DELIMITER &&

CREATE PROCEDURE pro_user4()

BEGIN

DECLARE a,b VARCHAR(20) ;

DECLARE cur_t_user2 CURSOR FOR SELECT userName2,password2 FROM t_user2; 声名一个游标，用于逐条读取userName2，和password2字段

OPEN cur_t_user2; 打开游标

FETCH cur_t_user2 INTO a,b; 使用游标，将游标中的值逐条赋值给变量a,b

INSERT INTO t_user VALUES(NULL,a,b);

CLOSE cur_t_user2; 关闭游标

END

DELIMITER ;

DELIMITER &&

CREATE PROCEDURE pro_user5(IN bookId INT) 创建一个存储过程，使bookId作为传入参数

BEGIN

SELECT COUNT(*) INTO @num FROM t_user WHERE id=bookId; 在t_user表中查询id=bookId的数据条数，并赋值给全局变量num

IF @num>0 THEN UPDATE t_user SET userName='java12345' WHERE id=bookId; 如果num>0则更新该数据

ELSE

INSERT INTO t_user VALUES(NULL,'2312312','2321312'); 否则重新插入一条数据

END IF ;

END

DELIMITER ;

DELIMITER &&

CREATE PROCEDURE pro_user6(IN bookId INT)

BEGIN

SELECT COUNT(*) INTO @num FROM t_user WHERE id=bookId;

CASE @num case语句

WHEN 1 THEN UPDATE t_user SET userName='java12345' WHERE id=bookId;

WHEN 2 THEN INSERT INTO t_user VALUES(NULL,'2312312','2321312');

ELSE INSERT INTO t_user VALUES(NULL,'231231221321312','2321312321312');

END CASE ;

END

DELIMITER ;

DELIMITER &&

CREATE PROCEDURE pro_user7(IN totalNum INT)

BEGIN

aaa:LOOP loop产生一个无限循环，标志为aaa

SET totalNum=totalNum-1;

IF totalNum=0 THEN LEAVE aaa ; 如果totalNum=0，则跳出循环

ELSE INSERT INTO t_user VALUES(totalNum,'2312312','2321312'); 否则插入一条语句

END IF ;

END LOOP aaa ; 结束循环

END

DELIMITER ;

DELIMITER &&

CREATE PROCEDURE pro_user8(IN totalNum INT)

BEGIN

aaa:LOOP

SET totalNum=totalNum-1;

IF totalNum=0 THEN LEAVE aaa ; 跳出循环，不在执行之后的语句

ELSEIF totalNum=3 THEN ITERATE aaa ; 跳出本轮循环，还会继续执行之后的操作

END IF ;

INSERT INTO t_user VALUES(totalNum,'2312312','2321312');

END LOOP aaa ;

END

DELIMITER ;

DELIMITER &&

CREATE PROCEDURE pro_user9(IN totalNum INT)

BEGIN

REPEAT 有条件的跳出循环

SET totalNum=totalNum-1;

INSERT INTO t_user VALUES(totalNum,'2312312','2321312');

UNTIL totalNum=1 条件在这里

END REPEAT;

END

DELIMITER ;

DELIMITER &&

CREATE PROCEDURE pro_user10(IN totalNum INT)

BEGIN

WHILE totalNum>0 DO while语句

INSERT INTO t_user VALUES(totalNum,'2312312','2321312');

SET totalNum=totalNum-1;

END WHILE ;

END

DELIMITER ;

CALL pro_user(); 调用该存储过程

CALL pro_user2();

CALL pro_user3();

CALL pro_user4();

CALL pro_user5(5);

CALL pro_user6(6);

CALL pro_user7(11);

CALL pro_user8(11);

CALL pro_user9(11);

CALL pro_user10(10);

DELETE FROM t_user;