初识MySQL数据库

1、初识MySQL数据库

1.1 连接数据库

• 命令行连接

mysql -u root -p795200  -- 连接数据库

• 修改数据库

update mysql.user set anthentication_string=password('795200') where user='root'and Host = 'localhost';   -- 修改用户密码

flush privileges;    -- 刷新权限

show databases;  -- 查询所有的数据库

show tables; -- 查看数据库中所有的表

describe student; -- 查看数据库表中所有的信息 describe {表名}

use school; -- 切换数据库 use {数据库名}  Database changed

create database westos; -- 创建一个数据库 create database {数据库名}

exit; -- 退出mysql连接


-- MySQL数据库注释
-- 单行注释

/*
多行注释
sfad
dsadas
dsadas
*/

数据库语言（CRUD增删查改）

• DDL 定义
• DML 操作
• DQL 查询
• DCL 控制

---

2、操作数据库

操作数据库>操作数据库中的表>操作数据库表的数据

mysql关键字不区分大小写

2.1 操作数据库（了解）

• 创建数据库

CREATE DATABASE [IF NOT EXISTS] singer;  -- 创建数据库singer

• 删除数据库

DROP DATABASE [IF EXISTS] singer  -- 删除一个数据库singer

• 使用数据库

USE `singer`  -- 使用数据库
-- tab键上面，如果表名或字段名是一个特殊符号，就需要带 ` `

• 查看数据库

SHOW DATABASES  -- 查看所有的数据库

• 对照sqlyog可视化的历史记录查看sql命令！
• 固定的语法或关键字必须要牢记！

2.2 数据库的列类型（重要）

数值

• tinyint 十分小的数据 1个字节

• smallint 较小的数据 2个字节

• mediumint 中等大小的数据 3个字节

• int 标准的整数 4个字节 常用的

• bigint 较大的数据 8个字节

• float 单精度浮点数 4个字节

• double 浮点数 8个字节（精度问题）

• decimal 字符串形式的浮点数 （金融计算的时候一般使用这个类型）

字符串

• char 固定大小的字符串 0-255
• varchar 可变字符串 0-65535 对应java中的String类型
• tinytext 微型文本 2^8-1 博客之类
• text 文本串 2^16-1 保持大文本

日期

• data YYYY-MM-DD 日期格式
• time HH: MM: SS 时间格式
• datatime YYY-MM-DD HH: mm: ss 最常用的时间格式
• timestamp 时间戳 1970.1.1到现在的毫秒数！ 也较为常用

Null

• 没有值，未知
• 注意，不要使用NULL进行运算，运算结果为NULL

2.3 数据库的字段属性（重点）

Unsigned

• 无符号的整数
• 声明强调该列不能声明为负数

zerofill

• 0填充
• 不足的位数，使用0来填充 例： int(3) 5 005

自增

• 自动在上一条记录的基础上+1（默认）
• 通常用来设计唯一的主键~index,且必须是整数类型
• 可以自定义设计主键自增的起始值和步长

非空

• 假设设置为not null ,如果不给它赋值，会报错！
• NULL，如果不填写值，默认就是null.

默认

• 设置默认的值
• sex，默认值为 男，如果不指定该列的值，则会有默认值

2.4 创建数据库表（重点）

SQL创表示例

-- 目标：创建一个school数据库
-- 创建学生表（列，字段）使用SQL语句创建
-- 学号 int
-- 登陆密码 varchar(20)
-- 姓名 
-- 性别 varchar(2)
-- 出生日期 datatime
-- 家庭住址
-- email
-- 注意点：使用英文(),表的名称和字段尽量用``括起来
CREATE TABLE IF NOT EXISTS `student`(
	`student_id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
	`password` VARCHAR(30) NOT NULL COMMENT '密码',
	`name` VARCHAR(20) NOT NULL DEFAULT '匿名' COMMENT '姓名',
	`sex` VARCHAR(2) NOT NULL DEFAULT '男' COMMENT '性别',
	`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
	`address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
	`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
	PRIMARY KEY (`student_id`)
)ENGINE = INNODB DEFAULT CHARSET = UTF8

示例语句解释

NOT NULL	-- 非空
AUTO_INCREMENT	-- 自增
DEFAULT '男'		-- 初始化值
DEFAULT NULL	-- 初始化值为空
COMMENT		-- 注释
PRIMARY KEY (`student_id`)	-- 主键
ENGINE = INNODB DEFAULT CHARSET = UTF8	-- 
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
创建 表 如果 不 存在 `表名`(
	`列名` 数据类型(长度) 不 为空 自增 注释'学号',
	`列名` 数据类型(长度) 不 为空 默认值 '匿名' 注释 '姓名',
	...
	...
	`列名` 数据类型(长度) 默认 为空 注释 '出生日期',
	...
	...
	主 键(`列名`)
)引擎=INNODB 默认 编码=utf8mb4

格式

create table [if exists] `表名`(
	`字段名` 列类型 [属性][索引][注释],
	`字段名` 列类型 [属性][索引][注释],
	...
	...
	`字段名` 列类型 [属性][索引][注释]
	[PRIMARY KEY(`字段名`)]
)[表类型][字符集设置][注释]

常用命令

SHOW CREATE DATABASE `数据库名` -- 查看创建数据库的语句
SHOW CREATE TABLE `表名`	-- 查看创建表的语句
DESC `表名` -- 查看表的结构

2.5 数据表的类型

-- 关于数据库引擎
/*
INNODB -- MySQL5.7/8.0默认使用
MYISAM -- 早些年使用的
*/

MyISAM InnoDB的区别和总结：

MyISAM更适合读密集的表，而InnoDB更适合写密集的的表。

	MyISAM	InnoDB
事务	不支持	支持
外键	不支持	支持
锁	表锁	行锁
速度	插入速度快
全文索引	支持	不支持

数据库字符集编码

CHARSET = UTF8

2.6 修改或删除表

修改表

-- 修改表名：ALTER TABLE 旧表名 RENAME AS 新表名
ALTER TABLE teacher RENAME AS teacher1

-- 增加表的字段：ALTER TABLE 表名 ADD 字段名 列属性
ALTER TABLE tacher1 ADD age INT(11)

-- 修改表的字段（重命名，修改约束！）
-- ALTER TABLE 表名 MODIFY 字段名 列属性[]
ALTER TABLE teacher1 MODIFY age VARCHAR(11) -- 修改约束
-- ALTER TABLE 表名 CHANGE 旧名字 新名字 列属性[] -- 字段重命名
ALTER TABLE teacher1 CHANGE age age1 INT(1)

删除

-- 删除表：OROP [TABLE IF EXISTS] `表名`
OROP TABLE IF EXISTS teacher1 --

注意点

• `` 字段名，使用这个符号包裹
• 注释 – /**/
• sql官架子大小写不敏感，建议大家写小写
• 所有的符号必须全部用英文！

---

3、DML数据库操纵语句

实现增删查改,需要重点记忆

实现增删查改,需要重点记忆

实现增删查改,需要重点记忆

3.1 添加插入语句

1、插入语句语法

insert into `表名`(列字段1,列字段2,列字段3) values(列字段对应值1,列字段对应值1，列字段对应值1)

语法要求：
1、数值类型直接填写，字符串或字符类型使用" "(引号)括起来；

2、插入值与列约束一致，插入的列字段的个数、数据类型和值的个数、数据类型必须一致；

3、使用英文符号，不能使用中文字符

2、SQL插入数据实例：

INSERT INTO student(id,student_id,password,name,sex,age) VALUES(NULL,20171649,'assam','zx','男',23);
-- 分号表示语句的结束
INSERT INTO student(id,student_id,password,name,sex,age) VALUES(NULL,20171649,'assam','zx','男',23);
INSERT INTO student(id,student_id,password,name,sex,age) VALUES(NULL,20171649,'assam','zx','男',23);

• 注：使用以下这种语句时，主键设置自动递增，列字段可以不写，但是值列表中的主键要用Null填充

INSERT INTO student VALUES(NULL,20171649,'assam','zx','男',23); -- 分号表示语句的结束
INSERT INTO student VALUES(NULL,20171649,'assam','zx','男',23);
INSERT INTO student VALUES(NULL,20171649,'assam','zx','男',23);

3.2 删除语句

1、删除语句语法

delete from `表名`; -- 删除表中所有数据
delete from `表名` where 条件(指定的记录); -- 删除表中指定的数据

2、SQL删除语句实例

delete from `singerw`; 	-- 删除表中所有数据,但自动递增不会清零
delete from `singerw`  where student_id = 20171649;  	-- 删除表中学号为20171649的这条数据
delete from `singerw`  where name = `张三`; 	 -- 删除表中姓名为张三的这条数据

• 删除表中所有数据，且把自动递增清零：

truncate table `singerw`;

3.3 更新修改语句

1、更新语句语法

update `表名` set 列名 = 新的值  -- 修改表中所有数据
update `表名` set 列名 = 新的值 where 更新条件； -- 修改表中指定的数据

2、SQL更新修改语句实例

update `student` set student_id = 20171549,sag = 25 where student_id = 20171649;
update `student` set student_id = 20171549,sag = 80 where student_id = 20171649 and name = ‘张欣’; --where添加用 and 连接即可。

3.4 查询语句

1、查询语句语法

select 查询的字段1,查询的字段2,......form `表名` where 查询条件
where 可以省略，如果省略，查询的是表中所有的数据

2、SQL查询语句实例

select id,student_id,name from `student`;

-- 查询指定的行记录
select student_id from `student` where = 20171649;
-- *代表所有的列字段，不建议用
select * from `student`;

• AS -显示类的别名

-- 显示类的别名 AS
select student_id as 学号,name as 学生姓名,age as 年龄,sex as 性别 from student;
-- 显示类的别名 AS可以省略
select id 编号,student_id 学号,name 姓名,sex 性别,age 年龄 from student;

• 单条件查询

-- 单个条件查询 使用比较运算符 > < >= <= !=(不等于) <>(不等于)
select student_id,name,age from student where age>23;  --筛选出年龄大于23岁的
select student_id,name,age from student where age!=23; --筛选出年龄不等于23岁的

• 多条件查询 -LIKE 模糊查询

  查询关键字有：

  %

  _

  or

  in

  not in

  between and

  is null

  is not null

• %

-- % 匹配多个字符
select sid,sname,sage from student where sname like '%欣%'  -- 名字中带有欣的都可以查询出来
select sid,sname,sage from student where sname like '张%'  -- 名字中带有张的都可以查询出来

• _

-- _ 匹配一个字符（下划线）
select sid,sname,sage from student where sname like '_欣'  -- 查询出X欣，名字只能有两个字符
select sid,sname,sage from student where sname like '张_'  -- 查询出张X，名字只能有两个字符
select sid,sname,sage from student where sname like '张__'  -- 查询出张XX，名字只能有三个字符

• or

-- or 或者  输出编号1或者编号20的学生，如果没有20，就不会显示
select sid,sname,sage from student where 

• in

-- in 可以用来批量删除、修改、查询
select sid,sname,sage from student where sid in(1,3,5,7,8,10);

• not in

-- not in  可以用来批量删除、修改、查询。排除专用
select sid,sname,sage from student where sid not in(1,3,5,7,8,10);

• between and

-- between and 一个范围
select sid,sname,sage from student where sage between 18 and 25;   18-25岁的人

• is null

-- is null
select sid,sname,sage from student where sage is null;  -- 查询出年龄为空的

• is not null

-- is not null
select sid,sname,sage from student where sage is not null;  --查询出年龄不为空的

3.5 基础查询小练习

• 创建数据库 T
  创建表：t_class、t_score、t_student、t_teacher

-- ----------------------------
-- Table structure for t_class
-- ----------------------------
DROP TABLE IF EXISTS `t_class`;
CREATE TABLE `t_class`  (
  `cid` int NOT NULL AUTO_INCREMENT COMMENT '班级编号',
  `cname` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '班级名称',
  `teacherid` int NOT NULL COMMENT '教师编号',
  PRIMARY KEY (`cid`)
) 


-- ----------------------------
-- Table structure for t_score
-- ----------------------------
DROP TABLE IF EXISTS `t_score`;
CREATE TABLE `t_score`  (
  `scid` int NOT NULL COMMENT '考试编号',
  `sid` int NULL DEFAULT NULL COMMENT '考试编号',
  `subject` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '考试科目',
  `score` float(4, 1) NULL DEFAULT NULL COMMENT '考试成绩',
  PRIMARY KEY (`scid`) USING BTREE
) 


-- ----------------------------
-- Table structure for t_student
-- ----------------------------
DROP TABLE IF EXISTS `t_student`;
CREATE TABLE `t_student`  (
  `sid` int NOT NULL AUTO_INCREMENT COMMENT '学生编号',
  `sname` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '学生姓名',
  `gender` varchar(2) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '性别',
  `age` int NULL DEFAULT NULL COMMENT '年龄',
  `classid` int NULL DEFAULT NULL COMMENT '班级编号',
  PRIMARY KEY (`sid`) USING BTREE
) 

-- ----------------------------
-- Table structure for t_teacher
-- ----------------------------
DROP TABLE IF EXISTS `t_teacher`;
CREATE TABLE `t_teacher`  (
  `tid` int NOT NULL AUTO_INCREMENT COMMENT '教师编号',
  `tname` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '教师姓名',
  `gender` varchar(2) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '性别',
  PRIMARY KEY (`tid`) USING BTREE
) 

• 添加数据

-- 添加语法：insert into 表名（字段列表用逗号隔开） values （对应的值，用逗号隔开，字符串用单引号包含）
-- ------------------------------------------------------------------------------------
-- eg:插入编号3，姓名王五，性别男，班级1
INSERT INTO `t_student`(sid,sname,gender,age,classid) VALUES (3,'王五','男',NULL,'1');
INSERT INTO `t_student` VALUES (1,'张欣','男',NULL,'1'),(4,'大大','男',NULL,'2'),(2,'朱帅','男',NULL,'3');

• 修改数据

-- 修改语法：update 表名 set 列名=值 where 列名=值
-- ------------------------------------------------------------------------------------
-- eg:修改表中所有学生的班级号为2
UPDATE t_student SET classid = 2;
-- eg:修改表中王五的班级号为1
UPDATE t_student SET classid = 1 WHERE sname='王五';

• 查询语法：select 列名 from 表名 where 条件 order by 排序的列名【asc/desc】 group by 分组的列名 having 分组后的条件
  简单查询语句

  select 查询的字段1,查询的字段2,…form 表名 where 查询条件
  where 可以省略，如果省略，查询的是表中所有的数据

-- eg:查询学生表所有信息
SELECT sid,sname,gender,age,classid FROM `t_student`;
-- eg:查询所有学生的姓名，性别
SELECT sname,gender FROM `t_student`;
-- eg:查询姓名为王五的学生姓名，性别
SELECT sname,gender FROM `t_student` WHERE sname = '王五';

• 查询语法：select 列名 from 表名 where 条件 order by 排序的列名【asc/desc】 group by 分组的列名 having 分组后的条件
  模糊查询语句:like, in, not in, between...and, is null, is not null

-- eg:查询所有姓带张的学生信息:where 列名 like 'X%'
SELECT sid,sname,gender,age,classid FROM t_student WHERE sname LIKE '张%';

-- eg:查询所有名字中带三的学生信息：where  列名  like '%x%'
SELECT sid,sname,gender,age,classid FROM t_student WHERE sname LIKE '%三%';

-- eg:查询所有老师编号是1或者2所在班级信息:可以使用or,也可以使用where 列名 in(1,2)
SELECT cid,cname,teacherid FROM t_class WHERE teacherid = 1 or teacherid = 2;
SELECT cid,cname,teacherid FROM t_class WHERE teacherid in(1,2);

-- 如果只知道老师的名字是老张，老林查询他们的班级信息
-- 先通过教师表查询Tid
-- 在班级表通过Tid查询班级信息
-- 然后串表嵌套查询
SELECT cid,cname,teacherid FROM t_class WHERE teacherid in(SELECT tid FROM t_teacher WHERE tname = '老张' or tname = '老林');

-- 查询老师编号不是1的班级信息，where 列名 not in(1)
SELECT cid,cname,teacherid FROM t_class WHERE teacherid NOT IN(1); --where
SELECT cid,cname,teacherid FROM t_class WHERE teacherid !=1; --not in

-- 查询学生年龄在20-23岁之间的学生信息，可以使用and也可以采用where age between 20 and 25
SELECT sid,sname,gender,age,classid FROM t_student WHERE age BETWEEN 20 AND 25;
SELECT sid,sname,gender,age,classid FROM t_student WHERE age>=20 AND age<=25;

-- 查询班级名称不为空的数据 where cname is not null
SELECT cid,cname,teacherid FROM t_class WHERE cname is not null;

3.6 排序查询与聚合函数

order by 列名 – 对某一列进行排序
asc – 升序
desc – 降序

1、排序查询语法

select 列字段 from 表名 where 查询条件 order by 列名 desc/asc;

2、排序查询实例

-- 获取及格的学生的排名
select scid,sid,subject,score, from student where score>=60 order by desc;

3、聚合函数

语法：

select 函数名(列字段) from 表名;

常见的统计函数：

• count() 统计个数
• sum() 计算某一数值列的总和
• avg() 计算某一数值列的平均值
• max() 计算某一数值列的最大值
• mix() 计算某一数值列的最小值

实例：

SELECT count(*) 记录数 FROM student;
SELECT sum(score) 总成绩 FROM student;
SELECT max(score) 最高分 FROM student;
SELECT min(score) 最低分 FROM student;
SELECT avg(score) 平均分 FROM student;

3.7 排序与聚合函数小练习

-- 聚合函数（count,sum,avg,max,min）
-- 按学生的年龄降序
SELECT sid,sname,gender,age,classid FROM t_student ORDER BY age DESC;

-- 查询班级号为1的学生数select count(*，或列) from 表名 where 列名=条件
SELECT COUNT(classid) AS 班级号为1的人数 FROM t_student WHERE classid = 1;

-- 查询编号为1的学生总分sum(score)
SELECT SUM(score) AS 学生总分 FROM t_score WHERE sid = 1;

-- 查询编号为2的学生平均分
SELECT AVG(score) AS 学生平均分 FROM t_score WHERE sid = 2;

-- 查询成绩表中最高成绩
SELECT MAX(score) FROM t_score;

-- 查询成绩表中最低成绩
SELECT MIN(score) FROM t_score;

-- 查询最高成绩的两名学生信息,先按成绩排序，然后查询条件为where ？  limit ？ 
SELECT a.sid,sname,gender,age,classid,`subject`,score
FROM t_student AS a
INNER JOIN t_score AS b
WHERE a.sid = b.sid AND score ORDER BY score DESC LIMIT 2;

-- 按学生的成绩降序
SELECT score FROM t_score ORDER BY score DESC;

-- 查询选修java的学生,按成绩降序
SELECT a.sid,sname,gender,age,classid,`subject`,score
FROM t_student AS a
INNER JOIN t_score AS b
WHERE a.sid = b.sid AND`subject`= 'java'AND b.score ORDER BY score DESC;

SELECT sid,sname,gender,age,classid FROM t_student WHERE `subject`= 'java' ORDER BY score DESC;


-- 查询班级号为1的学生人数
SELECT COUNT(classid) FROM t_student WHERE classid = 1;

-- 查询学生的总成绩
SELECT SUM(score) FROM t_score WHERE score;


-- 查询年龄最小的学生的信息
SELECT MIN(age) FROM t_student WHERE age;


-- 查询年龄最小的学生信息
SELECT sid,sname,gender,MIN(age),classid FROM t_student WHERE age;

3.8 分组查询

group by 列名

1、语法

select 列名1,count(*) from 表名 group by 列名1;
select 列名1,列名2 count(*) from 表名 group by 列名1,列名2;

2、分组查询实例

-- 分组查询
-- select 列名1,count(*) from 表名 group by 列名1;
-- 求出几种课程
SELECT COUNT(`subject`) FROM t_score;

-- 对课程去重 DISTINCT
SELECT COUNT(DISTINCT `subject`) FROM t_score;

--查出每门课程的人数
SELECT subject 科目,count(*) 人数 from t_score group by subject;

-- 查出课程的人数>30的班级，并且按人数降序排序
SELECT `subject` 课程,COUNT(*) 人数 FROM t_score
GROUP BY `subject` HAVING 人数>30 ORDER BY 人数 DESC;

注：一条sql语句中出现：where group by odder by having的使用顺序

select -> where -> group by  -> having  -> order by

3、分组查询练习

-- 分组查询练习
-- 查询每个班级的人数

-- 以成绩升序，年龄降序显示学生表中的所有记录（排序）

-- 查询每门课程的平均分

-- 班级人数大于1的班级号

-- 分数大于平均分的学生姓名和分数

3.9 关联查询

1、语法

-- 内连接 inner join 语法
select 列字段....from 表1 inner join 表2 表与表直接的关联关系 where......

-- 实例
select * from t_student inner join  t_score on  t_student.sid=t_score.sid; 

-- 左连接 left join 语法
select 列字段... from 表1 left join  表2 on  表与表之间的关联关系  where....;

-- 实例
select * from t_student left join  t_score on  t_student.sid=t_score.sid;

-- 右连接 ringht join 语法
select 列字段... from 表1 right join  表2 on  表与表之间的关联关系  where....;

-- 实例
select * from t_student right join  t_score on  t_student.sid=t_score.sid;

• 内连接 inner join：输出的是2张表的关联(公共)的部分，得到的是两张以上表的公共数据
• 左连接 left join：输出的是左表的所有数据，得到的是左表的所有数据,如果右表没有对应的数据,则显示null
• 右连接 ringht join：输出的是右表的所有数据，得到的是右表的所有数据,如果左表没有对应的数据,则显示null

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-WMv8Cj6g-1622046277357)(https://singerw-com-1300001977.cos.ap-beijing.myqcloud.com/blog_image/sql_inner%20join.png)]