小白学习之Mysql-day02

• 觉得通俗易懂的点个赞吧。谢谢了

一、数据库表操作(接昨天)

1、删除表

-- 删除表
DROP TABLE 表名;
-- 删除列
ALTER TABLE 表名 DROP 列名;

2、修改表

语法：

– 修改现有列类型、长度和约束
ALTER TABLE 表名 MODIFY 列名 类型(长度) 约束;
– 修改现有列名称
ALTER TABLE 表名 CHANGE 旧列名 新列名 类型(长度) 约束;
– 修改表名
RENAME TABLE 旧表名 TO 新表名;
– 修改表的字符集
ALTER TABLE 表名 CHARACTER SET 编码表名;
– 删除现有列
ALTER TABLE 表名 DROP 列名;
– 增加列
ALTER TABLE 表名 ADD 列名;

-- 1、增加列
ALTER TABLE student ADD sno VARCHAR(20);

-- 2、修改现有列类型、长度和约束
ALTER TABLE student MODIFY sno CHAR(10) NOT NULL;
DESC student;

-- 3、修改现有列名称
ALTER TABLE student CHANGE sno snum INT;
DESC student;

-- 4、修改表名
RENAME TABLE sudent TO student;

-- 5、修改表的字符集
SHOW CREATE TABLE student;
ALTER TABLE student CHARACTER SET GBK;

-- 6、删除现有列
ALTER TABLE student DROP snum;

3、查看数据表结构

-- 查看表结构
desc 表名; 
-- 查看当前库内所有表名
show tables;
-- 查看建表语句和字符集 
show create table 表名; 

二、表中的数据操作

1、创建数据表

create table student(
	id int primary key auto_increment,
	sage int ,
	sname varchar(32) not null,
	gender varchar(10) not null,
	score double not null,
	birthday date
);

在创建表结构的时候，如果主键是整数类型，那么可以借助mysql的主键自增长策略，在插入的时候可以省略主键的数据。

2、插入表数据

语法：insert into 表名 (列名，列名，列名…) values (值，值，值…);

上述的语法是给表中每列插入数据，需要注意以下问题：

1、插入值类型必须和列类型匹配。

2、值长度不能超过列定义长度。

3、值的顺序和列顺序对应。

4、字符串和日期型值必须写单引号。

5、插入空值可以写null。

• 如果表的主键是自增的，这时插入的时候，主键可以写null，不是将null存放进主键中，而是让数据库为主键自动增长。
• 如果列的类型是varchar、char、date、datetime类型需要插入时，使用单引号引用。

-- 插入数据
INSERT INTO student
            (id,
             sage,
             sname,
             gender,
             score,
             birthday)
VALUES (001,12,'李四','男',91.00,1999-04-24);

• 如果某些列不需要插入数据时，可以在插入的时候省略这些列

INSERT INTO student(sname,gender,score) VALUES('李四','男',91.00);

• 如果是给表中所有的列添加数据，这时可以省略列名

INSERT INTO student VALUES(NULL,11,'张三','女',79.22,'2011-09-02');

• 如果使用dos窗口插入数据时，可能会有中文乱码的问题：
  

告诉数据库当前提交的数据，采用的是GBK编码。

为什么会乱码，因为mysql这个mydb2库使用的是utf8编码，而dos窗口使用的是GBK编码，故会错乱。使用set names GBK;将该表的数据库编码临时改为GBK编码，结束窗口时编码自动变回初始的utf8。

通过 show variables like ‘character%’; 插入mysql支持的客户端和服务器的编码。

3、修改表数据

语法：update 表名 set 列名=值,列名=值… where条件语句;

如果没有where条件语句，默认修改所有行数据

-- 修改表数据
-- 不添加条件修改
UPDATE student SET sage=20;

-- 添加条件修改
UPDATE student SET gender = '女',score = 100 WHERE id = 5;

4、删除表数据

语法：delete from 表名 where条件语句 ;

自增主键，表中删除的主键(例如id)不会再被使用。

索引类似于图书馆中找书，具有索引如同拥有了寻找的路线，能够直接去找到该id的值，不用搜索，大大减少时间。

-- 删除表中数据
DELETE FROM student WHERE id = 13;

-- 删除表
DELETE FROM student;

使用delete删除表中所有记录和使用truncate table删除表中所有记录有何不同 ？

truncate 删除数据，过程先将整个表删除，再重新创建 。（使用drop将表删除，再用create重新建一个表）

delete 删除数据，逐行删除记录

truncate 效率要好于 delete

truncate 属于DDL ，delete 属于DML

事务管理只能对DML 有效 ，被事务管理SQL语句可以回滚到SQL执行前状态

5、查询表数据

5.1、基础查询

语法：select [distinct] * | 列名,列名… from 表名;

​ select * from 表名; 查询该表中所有列信息

​ select 列名,列名… from 表名; 查询表中指定列的信息

-- 查询操作

-- 查询表中所有数据
SELECT * FROM student;

-- 添加条件查询
SELECT * FROM student WHERE id = 1;

-- 根据姓名查询
SELECT * FROM student WHERE sname = '唐四';

-- 查询姓名和成绩
SELECT sname,score FROM student;

• 使用distinct去重

-- 使用distinct去重
SELECT DISTINCT score FROM student;
-- 某一行没有重复数据
SELECT DISTINCT * FROM student;

• 查询操作对结果做运算

-- 查询的成绩加10分
SELECT DISTINCT score + 10 FROM student;
ALTER TABLE student MODIFY score NUMERIC(10,2);
SELECT DISTINCT score + 10 FROM student;

这里仅仅是在查询的成绩上加了10分，而数据表中的成绩是没有变化的。

可能会出现小数精度问题（计算中所有小数都是近似值）。

如果数据库中需要小数精确值，列的类型可以使用dcimal(M,D)，numeric(M,D)：M表示数字的个数，D表示小数位数。

• 查询的别名使用

  别名中，as可以省略。

-- 给查询列或表使用别名
SELECT score AS 成绩 FROM student;

-- 别名的面试题
-- 查询sage和score两列的数据
SELECT sage,score FROM student;
-- 查询sage的数据，并将sage的别名取为score
SELECT sage score FROM student;

5.2、and、or、null使用

-- 查询成绩在80~100的学生
SELECT * FROM student WHERE score>=80 AND score<=100;
SELECT * FROM student WHERE score>=80 && score<=100;
SELECT * FROM student WHERE score BETWEEN 80 AND 100;

-- 查询性别为男，或者成绩大于80的学生
SELECT * FROM student WHERE gender = '男' || score > 80; 

-- 查询年龄为null的同学
SELECT * FROM student WHERE sage IS NULL;
SELECT * FROM student WHERE sage IS NOT NULL;

5.3、列表in的使用

in(值,值,值) 在指定值中任取一个 in(70,80,90) 值可以是70、80或者90

-- in的用法
-- 查询年龄为23和25的学生
SELECT * FROM stu2 WHERE sage = 23 OR sage = 25;
SELECT * FROM stu2 WHERE sage IN(23,25)

5.4、模糊条件的使用

语法：select 列名，列名，列名，列名…from 表名 where 列名 like 值

模糊查询的like关键字后面的值书写有两种情况：

• %：匹配任意个字符
• _：匹配一个位置

-- 模糊查询
-- 查询所有姓张的学生数据
SELECT * FROM student WHERE sname LIKE '张%';
-- 查询名字是两个的学生数据
SELECT * FROM student WHERE sname LIKE '__';
-- 查询姓名中包含张的学生数据
SELECT * FROM student WHERE sname LIKE '%张%'

5.5、排序查询

语法：select * from 表名 order by 列名 asc|desc ;

asc是升序排列，desc是降序排列

-- 排序查询
-- 按年龄升序查询
SELECT * FROM student ORDER BY sage ASC;
-- 查询所有的学生数据，按照年龄升序，年龄相同，成绩降序
SELECT * FROM student ORDER BY sage ASC,score DESC;
-- 查询年龄大于15小于25的升序，先执行条件过滤，再执行排序
SELECT * FROM student WHERE sage>15 AND sage<25 ORDER BY sage ASC;

6、分组

分组：是对数据表中的数据按照一定规律进行划分档次，同一档次的为一组。

聚集函数：也成为分组函数，主要是指SQL语句中的内置函数。用于分组统计。

6.1、count函数

语法：select count(*)|count(列名) from 表名;

count：统计符合条件的数据个数。

-- 统计学生的个数
SELECT COUNT(*) FROM student;
SELECT COUNT(id) FROM student;

count函数在使用的时候，传递参数不同：

count(*)：在统计的时候，如果这列值为null，找本列后面的一列，如果当前一行都是null，不统计。

count(主键)：只能统计主键这列，由于主键唯一并不为null，统计整个表中的记录数。

count(列名)：统计指定的那列的数据个数，如果有null，不统计。

6.2、sum函数

统计某一列数据的和

语法：select sum(列名) from 表名;

-- 统计成绩的总和
SELECT SUM(score) FROM student;
-- 分别统计年龄和成绩的和
SELECT SUM(sage),SUM(score) FROM student;
-- 统计年龄和成绩和值(正确写法)
SELECT SUM(sage)+SUM(score) FROM student;

在mysql中所有的null与其他数据运算的结果还是一个null，导致有同学的成绩没有加到总和中。

在mysql中有ifnull函数可以判断某列是否为null，当为null时，可以给这里设置一个值。

上述的ifnull(sage,0) 意思就是当某个学生的sage为null时，给其设置值为零。

-- 统计年龄和成绩和值(错误写法)
SELECT SUM(sage+score) FROM student;

SELECT SUM(IFNULL(sage,0)+IFNULL(score,0)) FROM student;

6.3、max、min函数

-- 求最大、最小值
SELECT MAX(sage),MIN(score) FROM student;

6.4、avg函数

-- 求平均值
SELECT AVG(score) AS 平均值 FROM student;
SELECT SUM(score)/COUNT(id) AS 平均值 FROM student;

6.5、group by分组函数

-- 查询购买了几类商品，并且每类总价大于100的商品
-- 聚集（分组）函数即sum，count等函数，不能使用在where的条件中，必须使用having关键字代替where
-- select count(product) from orders GROUP BY product where sum(price)> 100;
SELECT product,COUNT(product),SUM(price) FROM orders GROUP BY product HAVING SUM(price)>100;
-- 分组，过滤，排序
SELECT product,COUNT(product) FROM orders GROUP BY product HAVING SUM(price)> 100 ORDER BY product DESC;

• having关键字和where关键字作用是一致的，都可以对查询的数据进行过滤，区别在于having关键字后面的过滤条件中可以使用聚集（分组）函数即sum，count等函数。

• where 和 having 条件语句的区别 ？

​ where 是在分组前进行条件过滤，having 是在分组后进行条件过滤 ，使用where地方都可以用 having替换 ， 但是having后可以使用分组函数，而where后不可以用分组函数 。

三、数据库备份和恢复

1、备份数据

在mysql的安装目录的bin目录下有mysqldump命令，可以完成对数据库的备份。
语法：mysqldump -u 用户名 -p 数据库名 > 磁盘SQL文件路径 由于mysqldump命令不是sql命令，需要在dos窗口下使用。

演示：
1、重新开启一个新的dos窗口。

2、导出数据。

2、恢复

通过上面的命令备份的数据，导出库中的所有数据，但是没有创建库的语句。因此在恢复的时候，需要 手动创建库。

恢复数据库：