库操作
创建数据库
刚安装的数据库,自带四个系统库,是不能进行操作修改的,如果需要使用,可以自己创建数据库在进行操作
格式: CREATE database 库名 字符编码;
示例: CREATE database student charset=utf8;
查看数据库
查看权限范围内的所有数据库
命令: SHOW DATABASES;
切换数据库
在新建表的时候,需要在指定的数据库中建表,所以需要切换数据库
格式: use 数据库名;
示例: use student;
在创建表的时候,需要切换致指定的数据库才能创建表,如果没有会默认一个随机的数据库;
删除数据库
不需要的数据库可以使用删除命令将数据库删除
格式: DROP DATABASE 数据库名;
示例: DROP DATABASE student;
通过已上方式删除数据库,如果数据库不不存在,会报错,可以使用 IF EXISTS解决
格式: DROP DATABASE IF EXISTS 数据库名;
示例: DROP DATABASE IF EXISTS student;
表操作
数据类型
-
MYSQL在创建表的时候需要指定表中每列的数据类型,MySQL数据类型有很多种,我们不需要都掌握,只需要掌握常用的几个数据类型即可
关键字 | 类型 | 说明 |
int | 整型 | 存放整数类型,长度最大为11 |
double | 浮点型 | 例如double(5,2)表示最多5位,其中必须有2位小数,即最大值为999.99,存放小数 |
char | 固定长度字符类型 | 最多255个字符,存字符串使用 |
varchar | 可变长度字符串类型 | 最多65535个字符,存字符串使用 |
date | 日期类型 | 存时间年月日,表现格式为:yyyy-MM-dd |
time | 时间类型 | 存时间时分秒,表现格式为:hh:mm:ss |
datetime | 日期时间类型 | 存时间年月日 时分秒,表现格式为yyyy-MM-dd hh:mm:ss |
enum | 枚举 | 限定只能插入规定的数据项,例如:enum('男','女'),限制数据只能存男女 |
text | 文本类型 | TEXT用来保存文本类型的字符串,一般用于存在文章,存储字符串数量大 |
创建表
MySQL本身对db和table的个数没有限制,但会受限于底层文件系统的限制,InnoDB最多允许40亿个表,在同一个数据库中表名必须唯一;
格式:
CREATE TABLE 表名(
列名 列类型,
列名 列类型
……
);
示例:
CREATE TABLE stu(
sid CHAR(6),
sname VARCHAR(20),
age INT,
gender VARCHAR(10)
);
注意:多个列之间需要用英文逗号隔开,结尾需要输入分好结束!(必须是英文状态下输入)
字段约束
mysql 常见的约束:
not null:非空约束,约束的字段不能为NULL
unique:唯一约束,约束的字段不能重复
primary key:主键约束,约束的字段既不能为NULL,也不能重复,可以跟自增函数组合使用:AUTO_INCREMENT,一张表只能有一个主键和一个自增函数,主键的类型必须是int类型;
其他:
自增函数:AUTO_INCREMENT(使用自增函数必须更住建组合使用)
默认值:DEFAULT '0'
设置备注:COMMENT
查看表
查看当前数据库所有的表: SHOW TABLES;
查看指定表的创建语句: SHOW CREATE TABLE 表名;
查看表结构: DESC 表名;
修改表
添加一列:
格式: ALTER TABLE 表名 ADD(列名 列类型);
示例:给stu表中添加mobile列: ALTER TABLE stu ADD(mobile varchar(128));
修改列类型:
格式: ALTER TABLE 表名 MODIFY 列名 列类型;
示例:将stu表中mobile列的类型改为int(11): ALTER TABLE stu MODIFY mobile int(11);
修改列名:
格式: ALTER TABLE 表名 CHANGE 原列名 新列名 列类型;
示例:将stu表的mobile列名改为test: ALTER TABLE stu CHANGE mobile test int(11);
删除列:
格式: ALTER TABLE 表名 DROP 列名;
示例:删除stu表的test列: ALTER TABLE stu DROP test;
修改表名:
格式: ALTER TABLE 原表名 RENAME TO 新表名;
示例:将stu表名改为student:ALTER TABLE stu RENAME TO student;
删除表
不需要的表可以使用删除命令将表删除
格式: drop table 表名;
示例: drop table stu;
通过已上方式删除表,如果表不不存在,会报错,可以使用 IF EXISTS解决
格式: DROP table IF EXISTS 表名;
示例: DROP table IF EXISTS stu;
操作数据
新增数据
格式:insert into 表名 (列名1, 列名2, 列名3, ...) values (值1, 值2, 值3, ...);
示例:
首先创建一个stu表:
CREATE TABLE stu(
sid CHAR(6),
sname VARCHAR(20),
age INT,
gender VARCHAR(10)
);
在表中插入数据:
INSERT INTO stu (sid, sname, age, gender) VALUES (1, '张三', 18, '男');
同时插入多行:
INSERT INTO stu (sid, sname, age, gender) VALUES (2, '李四', 18, '女'), (3, '王老五', 28, '男');
这里的列名也可以不写(不建议有此习惯):
INSERT INTO stu VALUES (1, '张三', 18, '男');
注意:
这里的列名的位置可以调换,但必须对应好值!
如果字段设有默认值可以不用插入数据,使用默认的
自增住建不需要插入数据(会自增)
删除数据
格式: DELETE FROM 表名 WHERE 条件;
示例:删除stu表中id=1的数据: DELETE FROM stu WHERE id = 1;
多个条件:删除stu表中18岁的男性的数据: DELETE FROM stu WHERE age = 18 or gender = '男';
注意:不加条件会删除所有的数据!
清空表: TRUNCATE TABLE 表名; ----(谨慎执行)
修改数据
格式: UPDATE 表名 SET 列名1=值1, 列名2=值2…… WHERE 条件
示例:修改stu表中张三的性别改为女:
UPDATE stu SET gender='女' WHERE sname='张三';
修改多个值:修改stu表中id大于18,姓名=张三的数据,性别改为女,年龄改为18: UPDATE stu SET gender='女',age=18 WHERE sname='张三' and id>18;
注意:不加条件会修改所有的数据!
查询数据(重要)
语法
数据查询语言,数据库执行DQL语句不会对数据进行改变,而是让数据库发送结果集给客户端
语法:
SELECT selection_list /*要查询的列名称*/
FROM table_list /*要查询的表名称*/
WHERE condition /*行条件*/
GROUP BY grouping_columns /*对结果分组*/
HAVING condition /*分组后条件*/
ORDER BY sorting_columns /*对结果排序*/
LIMIT offset_start, row_count /*结果限定*/
导入测试数据
-- 创建学生表:
CREATE TABLE `student` (
`Sid` varchar(10) NOT NULL COMMENT '学生id',
`Sname` varchar(10) DEFAULT NULL COMMENT '学生姓名',
`Sage` varchar(10) DEFAULT NULL COMMENT '学生年龄',
`Ssex` varchar(15) DEFAULT NULL COMMENT '学生性别',
PRIMARY KEY (`Sid`)
) COMMENT '学生表';
-- 创建课程表
CREATE TABLE `course` (
`Cid` varchar(20) NOT NULL COMMENT '课程id',
`Cname` varchar(20) DEFAULT NULL COMMENT '课程名',
`Tid` varchar(10) DEFAULT NULL COMMENT '教师id',
PRIMARY KEY (`Cid`)
) COMMENT '课程表';
-- 创建教师表
CREATE TABLE `teacher` (
`Tid` varchar(10) NOT NULL COMMENT '教师id',
`Tname` varchar(20) DEFAULT NULL COMMENT '教师姓名',
PRIMARY KEY (`Tid`)
) COMMENT '教师表';
-- 创建成绩表
CREATE TABLE `sc` (
`Sid` int NOT NULL COMMENT '学生id',
`Cid` varchar(10) NOT NULL COMMENT '课程id',
`score` varchar(10) DEFAULT NULL COMMENT '成绩',
PRIMARY KEY (`Sid`,`Cid`)
) COMMENT '成绩表';
-- 向学生表中添加数据
insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
insert into Student values('02' , '钱电' , '1990-12-21' , '男');
insert into Student values('03' , '孙风' , '1990-05-20' , '男');
insert into Student values('04' , '李云' , '1990-08-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吴兰' , '1992-03-01' , '女');
insert into Student values('07' , '上官青云' , '1989-07-01' , '女');
insert into Student values('08' , '赵二牛' , '1990-01-20' , '女');
-- 向课程表中添加数据
insert into Course values('01','语文','02');
insert into Course values('02','数学','01');
insert into Course values('03','英语','03');
-- 向教师表中添加数据
insert into Teacher values('01','欧阳政宇');
insert into Teacher values('02','关羽');
insert into Teacher values('03','张一德');
-- 向课程表中添加数据
insert into SC values('01','01',80);
insert into SC values('01','02',90);
insert into SC values('01','03',99);
insert into SC values('02','01',70);
insert into SC values('02','02',60);
insert into SC values('02','03',80);
insert into SC values('03','01',80);
insert into SC values('03','02',80);
insert into SC values('03','03',80);
insert into SC values('04','01',50);
insert into SC values('04','02',30);
insert into SC values('04','03',20);
insert into SC values('05','01',76);
insert into SC values('05','02',87);
insert into SC values('06','01',31);
insert into SC values('06','03',34);
insert into SC values('07','02',89);
insert into SC values('07','03',98);
查询所有列
SELECT * FROM student;
查询指定列
SELECT Sid, Sname FROM student;
重命名列
重命名关键字:AS
SELECT Sid AS '学生id', Sname AS '学生姓名' FROM student;
AS可以默认不写,例如:
SELECT Sid '学生id', Sname '学生姓名' FROM student;
条件查询
条件查询就是在查询时给出WHERE子句,在WHERE子句中可以使用如下运算符及关键字:
=、!=、<>、<、<=、>、>=
BETWEEN … AND --- 在……之间
字段 IN(set) --- 在集合中
字段 NOT IN (set) --- 不在集合中
字段 IS NULL ———查询出为null的
字段 IS NOT NULL ———查询出不为null的
AND 和
OR 或者
练习
1.查询性别为女的所有学生:
SELECT * FROM student WHERE Ssex = '女';
2.查询性别为女并且Sid列大于5的学生:
SELECT * FROM student WHERE Ssex = '女' AND Sid > 5;
3.查询性别为男或者出生年月大于1990-05-05的学生姓名和出生年月:
SELECT Sname,Sage FROM student WHERE Ssex = '男' OR Sage > '1990-05-05';
4.查询姓名为郑竹、吴兰、王菊的学生:
SELECT * FROM student WHERE Sname in ('郑竹','吴兰','王菊');
4.查询姓名除王菊、吴兰以外的学生:
SELECT * FROM student WHERE Sname NOT in ('吴兰','王菊');
5.查询年龄为null的记录:
SELECT * FROM student WHERE Sage is null;
6.查询年龄不为null的记录:
SELECT * FROM student WHERE Sage IS NOT NULL;
7.查询id在02至07的学生信息:
SELECT * FROM student WHERE S>=2 AND S<=7;
或者
SELECT * FROM student WHERE S BETWEEN 2 and 7;
8.查询非男性的所有学生:
SELECT * FROM student WHERE Ssex != '男';
-- 或者
SELECT * FROM student WHERE Ssex <> '男';
-- 或者
SELECT * FROM student WHERE NOT Ssex = '男';
模糊查询
当想查询姓名中包含张的学生时就需要使用模糊查询了。模糊查询需要使用关键字LIKE
配合百分号和下划线使用
%:匹配所有字符,包含空
_:匹配单个字符,不包含空
查询姓名由两个字符构成的学生记录
SELECT * FROM student WHERE Sname LIKE '__';
查询姓李,并且姓名由两个字符构成的学生记录
SELECT * FROM student WHERE Sname LIKE '李_';
查询姓赵的学生记录
SELECT * FROM student WHERE Sname LIKE '赵%';
查询姓名包含“云”的学生信息:
SELECT * FROM student WHERE Sname LIKE '%云%';
字段控制查询
去重函数: DISTINCT
行或两行以上记录中系列的上的数据都相同,例如我们搜索学生表中性别有哪些的时候,执行一下语句查询:
SELECT Ssex FROM student;
通过上面的语句查询出性别有男和女,有多行,这时候我们就可以使用去重语句进行去重
SELECT DISTINCT Ssex FROM student;
IFNULL()函数,将列表中数据为空的转换为指定的数据:
IFNULL(a,b)a表示列名,b是需要指定的数据,当a列中数据为null时,会显示为b
SELECT Sname,IFNULL(Ssex,'未知') FROM student;
排序
排序函数: ORDER BY
查询成绩表,成绩列升序排序:(升序:从小到大)
SELECT * FROM sc ORDER BY score ASC;
查询成绩表,成绩列降序排序:(降序:从大到小)
SELECT * FROM sc ORDER BY score DESC;
查询成绩表,成绩列降序排序,如果成绩相同,就使用课程ID降序排序:(降序:从大到小)
SELECT * FROM sc ORDER BY score DESC,Cid DESC;
聚合函数
聚合函数是用来做纵向运算的函数:
COUNT():统计指定列不为NULL的记录行数;
MAX():计算指定列的最大值,如果指定列是字符串类型,那么使用字符串排序运算;
MIN():计算指定列的最小值,如果指定列是字符串类型,那么使用字符串排序运算;
SUM():计算指定列的数值和,如果指定列类型不是数值类型,那么计算结果为0;
AVG():计算指定列的平均值,如果指定列类型不是数值类型,那么计算结果为0;
COUNT():
查询学生表有多少个学生:
SELECT count(*) FROM student;
查询男生有多少人数:
SELECT count(*) FROM student WHERE Ssex='男';
MAX() 和 MIN():
查询学生ID为01的学生最高成绩和最低成绩
SELECT MAX(score),MIN(score) FROM sc WHERE Sid='01';
查询年纪最小的学生出生日期:
SELECT MIN(Sage) FROM student;
SUM() 和 AVG():
查询学生的总成绩和平均成绩
SELECT SUM(score),AVG(score) FROM sc WHERE Sid='01';
分组函数
分组关键字: GROUP BY (阿华警告,分组要好好理解)
例如,我们对学生表进行分组,按男女分:
SELECT Ssex FROM student GROUP BY Ssex;
1.查询男生和女生的人数:
SELECT Ssex,count(Ssex) '人数' FROM student GROUP BY Ssex;
2.查询每个学生的平均成绩和学生ID:
SELECT Sid,avg(score) FROM sc GROUP BY Sid;
分组后条件: HAVING
WHERE是对分组前的条件进行约束,而 HAVING是对分组后数据的约束
1.查询学生平均成绩大于80 的学生ID和平均成绩
SELECT Sid,avg(score) FROM sc GROUP BY Sid HAVING avg(score) > 80;
作业:
1.查询姓赵的学生有多少个
2.查询每门课程的平均成绩
3.查询学生总成绩大于200分的学生ID和总成绩
限定查询
LIMIT用来限定查询结果的起始行,以及总行数
1.查询学生表前五位同学
SELECT * FROM student LIMIT 5;
2.查询成绩排行前三的成绩
SELECT score FROM sc ORDER BY score DESC limit 3;
3.查询3条记录,从3开始
SELECT score FROM sc ORDER BY score DESC LIMIT 3,3;
连表查询
关系型数据库,同一个数据库中,表与表直接是有相互联系,相互约束的,mysql使用join来完成表链接的,JOIN 按照
功能大致分为如下三类:
INNER JOIN(内连接,或等值连接):获取两个表中字段匹配关系的记录。
LEFT JOIN(左连接):获取左表所有记录,即使右表没有对应匹配的记录。
RIGHT JOIN(右连接): 与 LEFT JOIN 相反,用于获取右表所有记录,即使左表没有对应匹配的记录
在连接之前首先要找到两张表的关联字段才能进行链接,如学生表中有Sid,和成绩表中的Sid关联,成绩表中的Cid又和课程表的Cid关联
1.将成绩表与学生表关联起来:
SELECT * FROM student join sc ON student.Sid = sc.Sid;
2.将成绩表、学生表、课程表关联起来:
SELECT * FROM student join sc ON student.Sid = sc.Sid JOIN course ON sc.Cid = course.Cid;
3.查询总成绩前三的学生姓名
SELECT student.Sname,SUM(score) FROM student join sc ON student.Sid = sc.Sid GROUP BY student.Sid ORDER BY SUM(score) DESC LIMIT 3;
4.查询数学成绩排行前三的学生姓名和数学成绩
SELECT Sname,score FROM student join sc ON student.Sid = sc.Sid JOIN course ON sc.Cid = course.Cid WHERE Cname='数学' ORDER BY score DESC LIMIT 3;
子查询
子查询:将查询结果作为下一个查询的条件
1.查询赵二牛的总成绩
SELECT sum(score) FROM sc WHERE Sid = (SELECT Sid FROM student WHERE Sname='赵雷') GROUP BY Sid;
2.查询总成绩大于200分的学生姓名
SELECT Sname FROM student WHERE Sid in (SELECT Sid FROM sc GROUP BY Sid HAVING SUM(score) > 200);
合并结果集
作用:合并结果集就是把两个select语句的查询结果合并到一起,前提:列数必须一致!
合并结果集有两种方式:
UNION:去除重复记录,例如:SELECT * FROM t1 UNION SELECT * FROM t2;
UNION ALL:不去除重复记录,例如:SELECT * FROM t1 UNION ALL SELECT * FROM t2。
查询成绩表中,学生成绩最低的和学生成绩最高的所有信息
(SELECT * FROM sc ORDER BY score DESC LIMIT 1)
UNION ALL
(SELECT * FROM sc ORDER BY score asc LIMIT 1);
常用函数
-- 获取当前时间:
SELECT NOW();
-- 统计字符串个数,str是字符串
CHAR_LENGTH(str)
-- 流程控制语句 CASE (允许套用多个WHEN)
格式:CASE WHEN 条件 THEN 如果前面条件成立就执行 ELSE 否则 END
示例:
SELECT *,(CASE WHEN s_score <= '30' THEN '超级不及格' WHEN s_score > 30 AND s_score <60 THEN '不及格' ELSE '及格' END) FROM score;
-- 字符串拼接
SELECT s_id,c_id,s_score,CONCAT(s_score,'分') FROM score
-- 字符串切片:
-- 从左边开始切1位
SELECT *,LEFT(s_name,1) FROM student;
-- 从右边边开始切2位
SELECT *,RIGHT(s_name,2) FROM student;
-- 保留两位小数
-- format(a,b) a是小数,b是保留的位数,四舍五入
SELECT sid, FORMAT(avg(score),2) FROM sc GROUP BY sid;
-- 字符串替换 REPLACE(string, from_string, new_string)
string:原始字符串,from_string:要替换的子字符串,new_string:新的替换子字符串
-- 设置变量
set @i= 90;
SELECT @i as tb_name;