MySQL数据库 sql语句及其含义

本文主要记录常用sql语句及对应意义，有一定基础可直接查看使用，需要学习者，可转向另一篇博文

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

 1、查询语句 

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组数据（返回全部字段）

select name,math,english,math+IFNULL(english,0) from student;  若english为null，则将其替换为0.
SELECT DISTINCT gradeName FROM t_student;            查询该表中所有的年级并去掉重复部分（返回年级)

2、聚合函数查询

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;            根据学生姓名进行分组，返回每个姓名对应的最低分

3、连接查询

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;            多表联查

4、子查询

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、增删改查

    （1）增

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);            向表中插入多条数据

    （2）删

DELETE FROM t_book WHERE id=5;

DELETE FROM t_book WHERE bookName='我';

TRUNCATE TABLE 表名;//删除表，在创建一张一模一样的表，相当于删除全部数据

    （3）改

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为‘我爱我家’的书籍名称为‘我’   

   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;

8、视图 

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;            查看视图的详细信息

 9、修改视图

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