一.操作数据库(DDL)
注意:
- SQL语句结尾要加英文的分号(;)
- MySQL不严格区分大小写
连接数据库
- mysql -u用户名 -p
- enter password:密码
退出数据库
-
exit
-
quit
看已经创建的数据库
- SHOW DATABASES;
查看数据库版本
- select version();
创建数据库
-
CREATE DATABASE 数据库名;
-
CREATE DATABASE 数据库名 charset=utf8;
-
charset:指定编码为utf8
查看创建数据库的命令
- SHOW CREATE DATABASE 数据库名;
使用数据库
- use 数据库名;
查看使用的数据库
- SELECT DATABASE();
删除数据库
- DROP DATABASE 数据库名;
练习
- 创建数据库:Logic_edu
- 查看已创建的数据库
- 查看当前使用的数据库是否是Logic_edu
- 若不是,则切换使用Logic_edu数据库
- 删除Logic_edu数据库
CREATE DATABASE 'Logic_edu';
SHOW DATABASES;
SELECT DATABASE();
USE Logic_edu;
DROP DATABASE Logic_edu;
注意
- SQL语句不区分大小写
- 每行必须以英文状态下分号作为结束
二.操作数据表
查看当前数据库中的所有表
- SHOW TABLES;
创建表
- CREATE TABLE 数据表名(字段 类型 约束[, 字段 类型 约束])
练习
- 创建表名为mytable1,字段id为int并且字段name为varchar(30)
CREATE TABLE mytable1(
id INT,
NAME VARCHAR(30)
);
- 创建表名为mytable2,指定id为主键并且字段name为varchar(30)
CREATE TABLE mytable2(
id INT PRIMARY KEY,
NAME VARCHAR(30)
);
查看表的创建语句
- SHOW CREATE TABLE 数据表名;
查看表描述信息
- DESC 数据表名;
练习
- 创建 students 表(id、name、age、high、gender、cls_id)
CREATE TABLE students(
id TINYINT PRIMARY KEY NOT NULL,
NAME VARCHAR(30),
age TINYINT DEFAULT 18,
high FLOAT(5,2),
gender ENUM('男','女','保密'),
cls_id INT`students`
);
- 创建 classes 表(id、name)
CREATE TABLE classes(
id TINYINT PRIMARY KEY NOT NULL,
NAME VARCHAR(10)
);
添加表字段
- ALTER TABLE 数据表名 add 字段 类型;
ALTER TABLE NAME ADD age TINYINT DEFAULT 18;
修改表字段
- ALTER TABLE 数据表名 MODIFY 字段 类型; —不重名
ALTER TABLE NAME MODIFY age VARCHAR(5) NOT NULL;
- ALTER TABLE 数据表名 CHANGE 原字段名 新字段名 类型及约束; —将字段重命名
ALTER TABLE NAME CHANGE age NAME VARCHAR(5);
二.删除表字段
- ALTER TABLE 数据表名 DROP 字段;
ALTER TABLE NAME DROP NAME;
练习
• 添加字段
• 字段名称:birthday
• 字段类型:DATETIME
• 修改字段
• 字段类型:DATE
• 修改字段
• 字段名称:birth
• 字段类型:DATE
• 删除该字段
sALTER TABLE NAME ADD birthday DATETIME; # 添加字段
ALTER TABLE NAME MODIFY birthday DATE; # 修改字段
ALTER TABLE NAME CHANGE birthday birth DATE; # 重命名字段
ALTER TABLE NAME DROP birth; # 删除字段
三.操作数据
一.插入数据
整行插入
- INSERT INTO 数据表名 VALUES(值1,值2,值3…);
INSERT INTO students VALUES (1,'Jeff',18,1.73,'男',1);
指定列中插入数据
- INSERT INTO tabname (字段1, 字段2,…) VALUES (值1,值2,…);
INSERT INTO students (name) VALUES ('Jerry');
指定表中插入多条数据
- INSERT INTO 数据表名 (字段1, 字段2,…) VALUES (值1,值2,…),(值1,值2,…);
INSERT INTO students (NAME, gender) VALUES ('Amy','女'),('Mary','女');
练习
- Student表中添加整行数据
INSERT INTO students VALUES (1,'Jeff',18,1.73,'男',1);
- Student表中添加name,gender两个字段的数据
INSERT INTO students (NAME, gender) VALUES ('Amy','女')
- Student表中同时添加多行数据
INSERT INTO students (NAME, gender) VALUES ('Jerry','男'),('Mary','女');
二.修改数据
- UPDATE 数据表名 SET 字段1=新值,字段2=新值 [WHERE 条件];
UPDATE students SET age=20,high=1.50 WHERE id=2;
练习
- 将姓名全部修改为jack
UPDATE students SET NAME='jack';
- 将性别为女的名字修改为rose
UPDATE students SET NAME='rose' WHERE gender='女';
- 将id为3的年龄修改为22,并且性别改为男
UPDATE students SET age=22,gender='男' WHERE id=3;
三.删除数据
- DELETE FROM 数据表名 [WHERE 条件];
DELETE FROM students WHERE id=6;
练习
- 将姓名为rose的数据删除
DELETE FROM students WHERE NAME='rose';
- 清空表数据
DELETE FROM students;
四.查询数据
查询整个表数据
- SELECT * FROM 数据表名;
SELECT * FROM students;
查询指定字段的数据
- SELECT 字段1,字段2 FROM 数据表名;
SELECT NAME,age FROM students;
查询指定字段数据,并给字段起别名
- SELECT 字段1 as 别名,字段2 as 别名 FROM 数据表名;
SELECT NAME AS n,age AS a FROM students;
查询指定字段并去重
- SELECT DISTINCT 字段1 FROM 数据表名;
SELECT DISTINCT NAME FROM students;
练习
- 查询Student表所有数据
SELECT * FROM students;
- 查询Student表中name与gender字段的数据
SELECT NAME,gender FROM students;
- 查询Student表中name字段的数据并且去重
SELECT DISTINCT NAME FROM students;
where子句
where子句通常结合增删改查使用,用于做筛选的条件。
比如,查询当id=1的数据
select * from Student where id=1
不仅如此,经常结合运算符使用。
五.运算符
一.比较运算符
运算符 | 描述 |
---|---|
= | 等于 |
< | 小于 |
<= | 小于等于 |
> | 大于 |
>= | 大于等于 |
!=或<> | 不等于 |
练习
- 查询id大于3的数据
SELECT * FROM students WHERE id>3;
- 查询年龄大于18岁的信息
SELECT * FROM students WHERE age>18;
- 查询姓名不是Amy的数据
SELECT * FROM students WHERE name!='Amy';
二.逻辑运算符
运算符 | 描述 |
---|---|
a and b | 当同时满足a,b时,则为True |
a or b | 当满足a或b其中一个时,则为True |
not a | 否定 |
练习
- 查询年龄18~22之间的所有学生信息
SELECT * FROM students WHERE age>=18 AND age<=22;
- 查询id大于3的女同学
SELECT * FROM students WHERE id>3 AND gender='女'
- 查询id小于2或者id大于4的学生信息
SELECT * FROM students WHERE id<2 OR id>4;
- 查询年龄不是18的女同学
SELECT * FROM students WHERE age!=18 AND gender='女'
六.查询
一.模糊查询
like关键字用来进行模糊查询,并且结合%以及_使用。
- % 表示任意多个任意字符
- _ 表示一个任意字符
练习
- 查询名字以a开始的学生信息
SELECT * FROM students WHERE name LIKE 'a%'
- 查询名字含有a的学生信息
SELECT * FROM students WHERE name LIKE '%a%'
- 查询名字仅有2个字符的学生信息
SELECT * FROM students WHERE name LIKE '__'
- 查询名字至少有2个字符的学生信息
SELECT * FROM students WHERE name LIKE '%__%'
二.范围查询
关键字 | 描述 |
---|---|
in | 表示在一个非连续的范围内 |
between…and… | 表示在一个连续的范围内 |
练习
- 查询id是1或者4或者6的学生信息
SELECT * FROM students WHERE id IN (1,4,6);
- 查询年龄为18或者20的学生信息
SELECT * FROM students WHERE age IN (18,20);
- 查询年龄不是18,20的学生信息
SELECT * FROM students WHERE age NOT IN (18,20);
- 查询id是2至4的学生信息
SELECT * FROM students WHERE id BETWEEN 2 AND 4;
- 查询id是3-5的男同学信息
SELECT * FROM students WHERE gender='男' AND (id BETWEEN 3 AND 5);
- 查询年龄不在18至20之间的学生信息
SELECT * FROM students WHERE age NOT BETWEEN 18 AND 20;
三.空判断
关键字 | 描述 |
---|---|
is null | 判断空 |
is not null | 判断非空 |
练习
- 查询没有填写身高的学生
SELECT * FROM students WHERE high IS NULL;
四.常用聚合函数
count 总数
- 求students总人数
SELECT COUNT(*) FROM students;
- 求男性的人数
SELECT COUNT(*) FROM students WHERE gender='男';
- 女性的人数
SELECT COUNT(*) FROM students WHERE gender='女';
max() 最大值
- 查询最大的年龄
SELECT MAX(age) FROM students;
- 女性最大的id
SELECT MAX(age) FROM students WHERE gender='女';
min() 最小值
- 查询未删除的学生的最小编号
SELECT MIN(id) FROM students;
sum 求和
- 查询男生年龄和
SELECT SUM(age) FROM students WHERE gender='男';
avg 平均值
- 查询未删除女生的年龄的平均值
SELECT AVG(age) FROM students WHERE gender='女';
- 计算男性的平均年龄,保留2位小数
SELECT ROUND(AVG(age),2) FROM students WHERE gender='男';
五.分组与分组之后的筛选
分组
在数据库中,通过 group by 将查询结果按照1个或多个字段进行分组,字段值相同的为一组。
- select … from students group by 需要分组字段;
练习:
- 计算男生和女生中的人数
SELECT COUNT(*) FROM students GROUP BY gender;
- 需要显示性别对应人数
SELECT COUNT(*),gender FROM students GROUP BY gender;
- 男生最大年龄与女生最大年龄
SELECT MAX(age),gender FROM students GROUP BY gender;
group_concat(…)
- 我们通过group_concat(…)查看每组的详细信息
练习:
- 查询同种性别(分组后)的姓名
SELECT gender,GROUP_CONCAT(NAME) FROM students GROUP BY gender;
- 查询组内年龄,姓名
SELECT gender,GROUP_CONCAT(NAME,':',age) FROM students GROUP BY gender;
分组后的筛选
练习:
- 查询男生女生总数大于2
SELECT gender,COUNT(*) FROM students GROUP BY gender HAVING COUNT(*)>2;
- 查询男生女生总数大于2的姓名
SELECT gender,COUNT(*),GROUP_CONCAT(NAME) FROM students GROUP BY gender HAVING COUNT(*)>2;
- 查询男生女生平均年龄超过18岁的性别,以及姓名
SELECT gender,AVG(age),GROUP_CONCAT(NAME) FROM students GROUP BY gender HAVING AVG(age)>2;
六.排序
SQL语法 | 描述 |
---|---|
order by 字段 | 默认升序 |
order by 字段 asc | 指定升序 |
order by 字段 desc | 指定降序 |
练习
- 查询年龄在18到26之间的男同学,按照年龄从小到大排序
SELECT * FROM students WHERE (age BETWEEN 18 AND 26) AND gender='男' ORDER BY age;
- 查询年龄在18到20岁之间的女同学,id从高到低排序
SELECT * FROM students WHERE (age BETWEEN 18 AND 26) AND gender='女' ORDER BY age DESC;
- 查询年龄在18-23岁之间的女性,年龄从高到低降序,当年龄相同时,按照身高从低到高升序。
SELECT * FROM students WHERE (age BETWEEN 18 AND 23) AND gender=2 ORDER BY age DESC,high ASC;
七.限制
- limit start,count
- start 为偏移量,默认起始0
- count 为条数
SELECT * FROM students LIMIT 1,2;
注意
- limit 不能写数学公式
- limit只能写在末尾
八.表连接
当查询结果的列来源于多张表时,需要将多张表连接成一个大的数据集,再选择合适的列返回mysql。这时我们就需要使用表连接。
SQL语法 | 描述 |
---|---|
inner join | 内连接 |
left join | 左连接 |
right join | 右连接 |
分类:
- 内连接
- 外连接
- 左连接
- 右连接
内连接
内连接仅选出两张表中互相匹配的记录
- select * from 表1 inner join 表2 on 表1.列 = 表2.列;
SELECT * FROM students s INNER JOIN classes c ON s.cls_id=c.id;
练习:
- 显示学生的所有信息,但只显示班级名称
SELECT s.*,c.name FROM students s INNER JOIN classes c ON s.cls_id=c.id;
- 将班级名称显示在第一列
SELECT c.name,s.* FROM students s INNER JOIN classes c ON s.cls_id=c.id;
- 查询 有能够对应班级的学生以及班级信息,按照班级进行排序
SELECT c.name,s.* FROM students s INNER JOIN classes c ON s.cls_id=c.id ORDER BY c.id;
- 当同一个班级时,按照学生的id进行从小到大排序
SELECT c.name,s.* FROM students s INNER JOIN classes c ON s.cls_id=c.id ORDER BY id;
左连接
查询的结果为两个表匹配到的数据,左表持有的数据,对于右表中不存的数据使用null填充
- select * from 表1 left join 表2 on 表1.列 = 表2.列;
练习:
- students表左连接classes表 并查看班级为null的数据
SELECT * FROM students s LEFT JOIN classes c ON s.cls_id=c.id HAVING c.id IS NULL;
- 左连接 并且 查询 s.is_del=1 并且 s.name=“amy” 的数据
SELECT * FROM students s LEFT JOIN classes c ON s.cls_id=c.id HAVING s.name='Amy' AND s.is_del=1;
右连接
查询结果为两个表匹配到的数据,右表持有的数据,对于左表中不存在的数据使用null填充。
- select * from 表1 right join 表2 on 表1.列 = 表2.列;
自关联
可以简单的理解为自己与自己进行连接查询。
比如:
一张 areas 表里面有省市区,我们需要在通过这一张表查询某省对应的所有市。
自关联可以用左连接、右连接、内连接都可以。
SELECT * FROM areas AS p INNER JOIN areas AS c ON p.`id`=c.`pid` HAVING p.name="湖南";
九.子查询
某些情况下,当进行查询的时候,需要的条件是另外一个 select 语句的结果,这个时候,就要用到子查询。
比如:
SELECT * FROM cities WHERE provinceid=(SELECT provinceid FROM provinces WHERE province='湖南省');