我在日常的使用和学习中,发现刚接触人对于 SQL语言的使用有一定的压力,我整理了一些常用的语句和语法,如下:
DDL语句
-- 1.查看所有数据库 SHOW DATEBASES;
-- 2.创建db1数据库 CREATE DATABASE mydatebase;
-- 3.创建db2数据库,如果不存在的话就创建 CREATE DATABASE IF NOT EXISTS db2;
-- 4.删除db2数据库 DROP DATABASE db2; -- 5.删除db2数据库,如果存在的话就删除 DROP DATABASE IF EXISTS mydatebase; -- 6.使用db1数据库 USE db1; -- 7.查看当前使用的数据库 SELECT DATABASE();
-- 1.查询当前数据库下的所有表 SHOW TABLES; -- 2.查询表结构 DESCRIBE stu; /* CREATE TABLE 表名 ( 字段名1 数据类型1, 字段名2 数据类型2, … 字段名n 数据类型n );
3.需求:创建tb_user表 id int, username varchar(20), password varchar(20)
/ CREATE TABLE tb_user( id INT, username VARCHAR(20), password VARCHAR(20) ); / 4.需求:设计一张学生表(student),请注重数据类型、长度的合理性 编号(id) 姓名(name),姓名最长不超过10个汉字 性别(gender),因为取值只有两种可能,因此最多一个汉字 生日(birthday),取值为年月日 入学成绩(score),小数点后保留两位 邮件地址(email),最大长度不超过 64 家庭联系电话(tel),不一定是手机号码,可能会出现 - 等字符 学生状态(status)(用数字表示,正常、休学、毕业...)
*/
-- 5.删除tb_user表 DROP TABLE tb_user; -- 6.删除tb_user表,判断表是否存在。 DROP TABLE IF EXISTS tb_user; -- 7.修改student表名称为stu RENAME TABLE stu TO student; -- 8.给stu表添加一列address,类型为varchar类型 ALTER TABLE student ADD address VARCHAR(36); -- 9.修改address列的类型为char类型 ALTER TABLE student MODIFY COLUMN address CHAR(42); -- 10.修改address列为addr并且类为为varchar类型 ALTER TABLE student CHANGE address addr VARCHAR(32); -- 11.删除addr列 ALTER TABLE student DROP COLUMN addr; ALTER TABLE student DROP COLUMN address;
===============================================
DML语句
-- 1.给指定列添加数据 INSERT INTO 表名(列名1,列名2,…) VALUES(值1,值2,…); INSERT INTO student(id,name,sex,birthday) VALUES (1,'张三','男','1999-08-02'); SELECT * FROM stu; -- 2.给所有列添加数据,列名的列表可以省略的 RENAME TABLE student TO stu; INSERT INTO stu VALUES(2,'李四','女','2000-05-06',88.88,'lfy@163.com','13539412345',6); -- 3.批量添加数据 INSERT INTO stu (id,name,sex) VALUES (3,'李四','男'), (4,'王五','女'), (5,'赵六','男');
-- 修改数据 UPDATE 表名 SET 列名1=值1,列名2=值2,… [WHERE 条件] ; SELECT * FROM stu; -- 4.将张三的性别改为女 UPDATE stu SET sex ='女'WHERE id =1; -- 5、将张三的生日改为 1999-12-12 分数改为99.99 UPDATE stu SET birthday ='1999-12-12',score ='99.99'WHERE name ='张三'; -- 6、将王五的分数在原有的基础上-10分 UPDATE stu SET score = 80.00 WHERE name ='王五'; UPDATE stu SET score = score-10 WHERE name ='王五';
-- 删除 DELETE FROM 表名 [WHERE 条件] ; -- 7.删除张三记录 DELETE FROM stu WHERE name ='张三';
====================================================== DQL基础查询
-- 删除stu表 drop table if exists stu;
-- 创建stu表 CREATE TABLE stu ( id int, -- 编号 name varchar(20), -- 姓名 age int, -- 年龄 sex varchar(5), -- 性别 address varchar(100), -- 地址 math double(5,2), -- 数学成绩 english double(5,2), -- 英语成绩 hire_date date -- 入学时间 );
-- 添加数据 INSERT INTO stu(id,NAME,age,sex,address,math,english,hire_date) VALUES (1,'马运',55,'男','杭州',66,78,'1995-09-01'), (2,'马花疼',45,'女','深圳',98,87,'1998-09-01'), (3,'马斯克',55,'男','香港',56,77,'1999-09-02'), (4,'柳白',20,'女','湖南',76,65,'1997-09-05'), (5,'柳青',20,'男','湖南',86,NULL,'1998-09-01'), (6,'刘德花',57,'男','香港',99,99,'1998-09-01'), (7,'张学右',22,'女','香港',99,99,'1998-09-01'), (8,'德玛西亚',18,'男','南京',56,65,'1994-09-02');
-- 1.查询name、age两列 select name,age from stu; -- 2.查询所有列的数据,列名的列表可以使用*替代 SELECT * FROM stu; -- 3.查询地址信息,去除重复记录
SELECT DISTINCT address FROM stu ;
#查找表中多余的重复记录,重复记录是根据单个字段(address)来判断 select * from stu where address in (select address from stu group by address having count(address) > 1); #删除表中多余的重复记录,重复记录是根据单个字段(address)来判断,只留有rowid最小的记录 delete from stu where address in (select address from stu group by address having count(address) > 1) and rowid not in (select min(rowid) from stu group by address having count(address )>1); #查找表中多余的重复记录(多个字段) select * from stu where (stu.name,stu.address) in (select name,address from stu group by name,address having count(*) > 1) #删除表中多余的重复记录(多个字段),只留有rowid最小的记录 delete from stu where (stu.name,stu.address) in (select name,address from stu group by name,address having count() > 1) and rowid not in (select min(rowid) from stu group by name,address having count()>1); #查找表中多余的重复记录(多个字段),不包含rowid最小的记录 select * from stu where (stu.name,stu.address) in (select name,address from stu group by name,address having count() > 1) and rowid not in (select min(rowid) from stu group by name,address having count()>1);
-- 4.查询姓名、数学成绩、英语成绩,要求查询结果列名展示对应的中文名称 SELECT name AS 名字 FROM stu; SELECT math AS 数学成绩 FROM stu; SELECT math FROM stu; SELECT english AS 英语成绩 FROM stu;
DQL条件查询
SELECT * FROM stu; -- 条件查询 ===================== -- 1.查询年龄大于20岁的学员信息 SELECT * FROM stu WHERE age >20; -- 2.查询年龄大于等于20岁的学员信息 SELECT * FROM stu WHERE age >=20; -- 3.查询年龄大于等于20岁 并且 年龄 小于等于 30岁 的学员信息 SELECT * FROM stu WHERE age >=20 AND age <30; -- 4.查询入学日期在'1998-09-01' 到 '1999-09-01' 之间的学员信息 SELECT * FROM stu WHERE hire_date BETWEEN '1998-09-01' AND '1999-09-01'; -- 5.查询年龄等于18岁的学员信息 SELECT * FROM stu WHERE age =18; -- 6.查询年龄不等于18岁的学员信息 SELECT * FROM stu WHERE age >=18; -- 7.查询年龄等于18岁 或者 年龄等于20岁 或者 年龄等于22岁的学员信息 SELECT * FROM stu WHERE age =18 OR age =20 OR age=22; -- 8.查询英语成绩为 null的学员信息 SELECT * FROM stu WHERE english is NULL; -- 使用is null或者is not NULL SELECT * FROM stu WHERE english IS NOT NULL;
-- 模糊查询 like ===================== /* 占位符: (1):代表单个任意字符 (2)%:代表任意个数字符 */ select * from stu; -- 1. 查询姓'马'的学员信息 SELECT * FROM stu WHERE NAME LIKE'马%'; -- 2. 查询第二个字是'花'的学员信息 SELECT * FROM stu WHERE name LIKE'花%'; -- 3. 查询名字中包含 '德' 的学员信息 SELECT * FROM stu WHERE name LIKE'%德%'; -- 4. 查询年龄大于50并且姓'马'的学员信息 SELECT * FROM stu WHERE NAME LIKE'马%'AND age >50; -- 5 查询姓名是三个字符的学生信息 SELECT * FROM stu WHERE NAME LIKE'_ _ _';
SELECT * FROM stu; /* count(列名):统计数量(一般选用不为null的列) max(列名):最大值 min(列名):最小值 sum(列名):求和 avg(列名):平均值 */ SELECT COUNT(id) FROM stu;
SELECT MAX(id) FROM stu;
SELECT MIN(age) FROM stu;
SELECT SUM(id) FROM stu;
SELECT AVG(id) FROM stu;
/* 分组函数 SELECT 字段列表 FROM 表名 [WHERE 分组前条件限定] GROUP BY 分组字段名 [HAVING 分组后条件过滤]…; */ select * from stu; -- 1. 查询男同学和女同学各自的数学平均分 SELECT AVG(math) FROM stu GROUP BY sex; SELECT sex ,AVG(math) FROM stu GROUP BY sex;
-- 2. 查询男同学和女同学各自的数学平均分,以及各自人数 SELECT sex,AVG(math),COUNT() FROM stu GROUP BY sex; -- 3. 查询男同学和女同学各自的数学平均分,以及各自人数,要求:分数低于70分的不参与分组 select sex,avg(math),COUNT() from stu where math >70 group by sex ; -- 4. 查询男同学和女同学各自的数学平均分,以及各自人数,要求:分数低于70分的不参与分组,分组之后人数大于2个的。 select sex,avg(math),COUNT()from stu where math >70 group by sex having count() >2;
/* 排序查询: * 语法:SELECT 字段列表 FROM 表名 ORDER BY 排序字段名1 [排序方式1],排序字段名2 [排序方式2] …; * 排序方式: * ASC:升序排列(默认值) * DESC:降序排列 */
select * from stu; -- 1.查询学生信息,按照年龄升序排列 select * from stu order by age asc; -- asc可以省略不写,默认升序 select * from stu order by age; -- 2.查询学生信息,按照数学成绩降序排列 select * from stu order by math desc; -- 3.查询学生信息,按照数学成绩降序排列,如果数学成绩一样,再按照英语成绩升序排列 select * from stu order by math desc,english asc;
/* 分页查询:
SELECT 字段列表 FROM 表名 LIMIT 起始索引 , 查询条目数 * 起始索引:从0开始
*/ select * from stu; -- 1. 每页显示3条数据,查询第1页数据 select * from stu limit 0,3; -- 2. 每页显示3条数据,查询第2页数据 select * from stu limit 3,3; -- 3. 每页显示3条数据,查询第3页数据 select * from stu limit 6,3;
-- 起始索引=(当前页码-1)*每页条数