web基础--mysqlDay05

表数据

一些mysql的基础的了解与练习

server是服务器安装时指定的默认字符集设定。 •
show variables like’character%’;
set names gbk;临时修改当前CMD窗口和mysql的通信编码字符集
system是数据库系统使用的字符集设定。（utf-8不可修改） •
请到mysql安装目录下面找到 my.ini文件
修改default-character-set=utf8 为 default-character-set=gbk
有两个地方都要改
修改文件前，先停止mysql服务 ，等修改后再重新启动
使用dos命令 ：net stop mysql 来停止服务 net start mysql 来启动
通过修改my.ini 修改字符集编码 •
使用 update语句修改表中数据。
UPDATE tbl_name
SET col_name1=expr1 [, col_name2=expr2 …]
[WHERE where_definition]
UPDATE语法可以用新值更新原有表行中的各列。
SET子句指示要修改哪些列和要给予哪些值。
WHERE子句指定应更新哪些行。如没有WHERE子句，则更新所有的行。
要求:
将所有员工薪水修改为5000元。
将姓名为’zs’的员工薪水修改为3000元。
将姓名为’lisi’的员工薪水修改为4000元,job改为ccc。
将’wu’的薪水在原有基础上增加1000元。
练习：在上面创建的employee表中修改表中的纪录。 
Update语句练习

Update语句 3.
使用 delete语句删除表中数据。
delete from tbl_name [WHERE where_definition]
如果不使用where子句，将删除表中所有数据。 ○
update table_name set 字段名=’’; 
Delete语句不能删除某一列的值（可使用update） ○
drop table table_name; 
使用delete语句仅删除记录，不删除表本身。如要删除表，使用drop table语句。 ○
外键约束
同insert和update一样，从一个表中删除记录将引起其它表的参照完整性问题，在修改数据库数据
时，头脑中应该始终不要忘记这个潜在的问题。
○
删除表中数据也可使用TRUNCATE TABLE 语句，它和delete有所不同，参看mysql文档。 ○
删除表中名称为’zs’的记录。
删除表中所有记录。
Delete 语句练习 a.

Delete语句 4.
斜体样式
删除表中所有记录。
使用truncate删除表中记录。
基本select语句
Select语句(1) 5.
select 指定查询哪些列的数据。
column指定列名。
号代表查询所有列。
from指定查询哪张表。
DISTINCT可选，指显示结果时，是否剔除重复数据
SELECT [DISTINCT] |{column1, column2. column3…}FROM table_name;
查询表中所有学生的信息。
查询表中所有学生的姓名和对应的英语成绩。
过滤表中重复数据。distinct去重
练习：
在select语句中可使用表达式对查询的列进行运算 •
SELECT * |{column1｜expression, column2｜expression，…}
FROM table;
SELECT column as 别名 from 表名;
在select语句中可使用as语句 •
Select语句(２) 6.
在所有学生分数上加10分特长分显示。
统计每个学生的总分。
使用别名表示学生总分。
练习
查询姓名为XXX的学生成绩
查询英语成绩大于90分的同学
查询总分大于200分的所有同学
使用where子句，进行过滤查询。练习：
Select语句(3) 7.
在where子句中经常使用的运算符
Select语句(４) 8.
比较运算符
**> < <= >= = <>
大于、小于、大于(小于)等于、不等于
between …and… 显示在某一区间的值
in(set)
显示在in列表中的值，例：in(100,200)
like ‘张pattern’ 模糊查询%_ select * from user where name like “%张%”
分区 day04-mysql 的第 9 页
like ‘张pattern’ 模糊查询%_ select * from user where name like “%张%”
is null
判断是否为空
select * from user where id is null
ifnull(原值,替代值) 如果原值为null，则使用代替值
select ifnull(score,0) from exam;
逻辑运算符
and
多个条件同时成立
or
多个条件任一成立
not
不成立，例：where not(salary>100);
Like语句中，% 代表零个或多个任意字符，_ 代表一个字符，例first_name like ‘_a%’;
查询英语分数在 80－100之间的同学。
查询数学分数为75,76,77的同学。
查询所有姓张的学生成绩。
查询数学分>70，语文分>80的同学。
Select 语句 ( ４ ) 练习
使用order by 子句排序查询结果。
SELECT column1, column2. column3…
FROM table_name
order by column_name asc|desc
Order by 指定排序的列，排序的列既可是表中的列名，也可以是select 语句后指定的列名。
Asc 升序（默认）、Desc 降序
ORDER BY 子句应位于SELECT语句的结尾。
Select语句(５) 9.
对语文成绩排序后输出。
对总分排序按从高到低的顺序输出
对姓李的学生成绩排序输出
练习：
count(列名)返回某一列，行的总数
Select count()|count(列名) from tablename [WHERE where_definition]
聚集函数－count 10.
统计一个班级共有多少学生？
统计数学成绩大于90的学生有多少个？
统计总分大于250的人数有多少？
练习：
Sum函数返回满足where条件的行的和
Select sum(列名)｛,sum(列名)…｝ from tablename[WHERE where_definition]
练习：
聚集函数－SUM 11.
分区 day04-mysql 的第 10 页
练习：
统计一个班级数学总成绩？
统计一个班级语文、英语、数学各科的总成绩
统计一个班级语文、英语、数学的成绩总和
统计一个班级语文成绩平均分
注意：sum仅对数值起作用，否则会报错。
注意：对多列求和，“，”号不能少。
AVG函数返回满足where条件的一列的平均值
Select avg(列名)｛,avg(列名)…｝ from tablename [WHERE where_definition]
练习：
求一个班级数学平均分？
求一个班级总分平均分？
聚集函数－AVG 12.
Max/min函数返回满足where条件的一列的最大/最小值
Select max(列名)from tablename [WHERE where_definition]
聚集函数－MAX/MIN 13.
求班级最高分和最低分（数值范围在统计中特别有用）
练习：
Select语句(6)分组操作 14.
使用group by 子句对列进行分组
SELECT column1, column2. column3…
FROM table_name
group by column_name having …
使用having 子句 对分组结果进行过滤 •
练习：对订单表中商品归类后，显示每一类商品的总价
where和having区别：where在分组前进行条件过滤，having在分组后进行条件过滤。使用
where的地方都可以用having替换。但是having可以使用分组函数，而where后不可以使用。
•
练习：查询购买了几类商品，并且每类总价大于100的商品
分区 day04-mysql 的第 11 页
备份数据库表中的数据 1.
cmd> mysqldump -u 用户名 -p 数据库名 > 文件名.sql
mysqldump -uroot -p db_name > d:/1.sql
（注意：如果数据库已经删除，先创建数据库再恢复数据。）
mysql –u 用户名 -p 数据库名 < 文件名.sql
mysql -uroot -p db_name < d:/1.sql
mysql -uroot -p mydb3 < d:/1.sql
方式一: 在cmd中:
source 文件名.sql
source d:/1.sql
方式二: 在mysql客户端中
恢复数据库 2.
备份test库中的数据，并恢复
练习 3.
备份和恢复数据
2019年8月5日 15:33
分区 day04-mysql 的第 12 页
1对1 1对多 多对多
多表关系 1.
多表关系
2019年8月5日 15:45
分区 day04-mysql 的第 13 页
使用数据库维护表与表之间的关系。可以通过外键来实现，外键本身还是一个约
束，所以称之为外键约束。
外键可以保证数据的一致性和正确性。
foreign key(ordersid) references orders(id)
定义外键约束 ○
外键概念 1.
新建部门表dept(id,name)
create tabledept(
id int primary key auto_increment,
name varchar(20)
);
通过外键约束建立与员工表emp关系
dept 和emp表数据操作：
insert into dept values(null, ‘财务部’);
insert into dept values(null, ‘人事部’);
insert into dept values(null, ‘科技部’);
insert into dept values(null, ‘销售部’);
create table emp(
id int primary key auto_increment,
name varchar(20),
dept_id int,
foreign key(dept_id) references dept(id)
);
insert into emp values(null, ‘张三’, 1);
insert into emp values(null, ‘李四’, 2);
insert into emp values(null, ‘老王’, 3);
insert into emp values(null, ‘赵四’, 4);
insert into emp values(null, ‘刘能’, 4);
在面对高吞吐的场景下，外键使用的并不多，如果外键存在，会影响数据
的入库和出库操作。这在高并发场景下是不被允许的。应该优先保证数据
入库和出库。
至于数据的正确性，可以通过其他技术手段解决。
在面对低吞吐量，低并发量的场景下可以使用外键来保证数据的正确性。
外键的使用情景：
案例演示： 2.
外键约束
2019年8月5日 16:21
分区 day04-mysql 的第 14 页
数据库中经常涉及到多张表一起查询的情景，可以使用多表查询的方式解决。
select * from dept,emp;
左边表的行数右边表的行数 = 查询结果数量
笛卡尔积查询结果： i.
±—±-------±—±-----±--------+
| id | name | id | name | dept_id |
±—±-------±—±-----±--------+
| 1 | 财务部 | 1 | 张飞 | 1 |
| 2 | 人事部 | 1 | 张飞 | 1 |
| 3 | 科技部 | 1 | 张飞 | 1 |
| 4 | 销售部 | 1 | 张飞 | 1 |
| 1 | 财务部 | 2 | 关羽 | 2 |
| 2 | 人事部 | 2 | 关羽 | 2 |
| 3 | 科技部 | 2 | 关羽 | 2 |
| 4 | 销售部 | 2 | 关羽 | 2 |
| 1 | 财务部 | 3 | 刘备 | 3 |
| 2 | 人事部 | 3 | 刘备 | 3 |
| 3 | 科技部 | 3 | 刘备 | 3 |
| 4 | 销售部 | 3 | 刘备 | 3 |
| 1 | 财务部 | 4 | 赵云 | 4 |
| 2 | 人事部 | 4 | 赵云 | 4 |
| 3 | 科技部 | 4 | 赵云 | 4 |
| 4 | 销售部 | 4 | 赵云 | 4 |
±—±-------±—±-----±--------+
select * from dept,emp where dept.id = emp.dept_id;
笛卡尔积查询： a.
在笛卡尔积的查询基础之上，获取左边表有且右边表也有的数据，这种查询
方式称之为内连接查询。
select * from dept,emp where dept.id = emp.dept_id;
inner join … on
inner join前后书写两张表， on后添加两张表连接时的条件。
select * from dept inner join emp on dept.id = emp.dept_id;
±—±-------±—±-----±--------+
| id | name | id | name | dept_id |
±—±-------±—±-----±--------+
| 1 | 财务部 | 1 | 张飞 | 1 |
内连接查询 b.
多表查询 1.
多表查询
2019年8月5日 16:41
分区 day04-mysql 的第 15 页
| 1 | 财务部 | 1 | 张飞 | 1 |
| 2 | 人事部 | 2 | 关羽 | 2 |
| 3 | 科技部 | 3 | 刘备 | 3 |
| 4 | 销售部 | 4 | 赵云 | 4 |
±—±-------±—±-----±--------+
在内连接的查询基础之上，获取左边表有但右边表没有的数据，这种查询
方式称之为左外连接查询。
select * from dept left join emp on dept.id = emp.dept_id;
使用别名查询：
±—±-------±-----±-----±--------+
| id | name | id | name | dept_id |
±—±-------±-----±-----±--------+
| 1 | 财务部 | 1 | 张飞 | 1 |
| 2 | 人事部 | 2 | 关羽 | 2 |
| 3 | 科技部 | 3 | 刘备 | 3 |
| 4 | 销售部 | 4 | 赵云 | 4 |
| 5 | 保安部 | NULL | NULL | NULL |
±—±-------±-----±-----±--------+
select * from dept d left join emp e on d.id = e.dept_id;
左外链接查询 i.
在内连接的查询基础之上，获取左边表没有但右边表右的数据，这种查询
方式称之为右外连接查询。
±-----±-------±—±-----±--------+
| id | name | id | name | dept_id |
±-----±-------±—±-----±--------+
| 1 | 财务部 | 1 | 张飞 | 1 |
| 2 | 人事部 | 2 | 关羽 | 2 |
| 3 | 科技部 | 3 | 刘备 | 3 |
| 4 | 销售部 | 4 | 赵云 | 4 |
| NULL | NULL | 5 | 背景 | 6 |
±-----±-------±—±-----±--------+
select * from dept right join emp on dept.id = emp.dept_id;
右外连接查询 ii.
在内连接的查询基础之上，获取左边表有但右边表没有的数据和左边表没
有但右边表右的数据，这种查询方式叫做全外连接查询。
select * from dept full join emp on dept.id = emp.dept_id;#mysql中没有
fulljoin
可以通过union关键字实现全外连接查询的结果。
select * from dept left join emp on dept.id = emp.dept_id
union
select * from dept right join emp on dept.id = emp.dept_id;
±-----±-------±-----±-----±--------+
全外连接查询 iii.
外连接查询 c.

±-----±-------±-----±-----±--------+
| id | name | id | name | dept_id |
±-----±-------±-----±-----±--------+
| 1 | 财务部 | 1 | 张飞 | 1 |
| 2 | 人事部 | 2 | 关羽 | 2 |
| 3 | 科技部 | 3 | 刘备 | 3 |
| 4 | 销售部 | 4 | 赵云 | 4 |
| 5 | 保安部 | NULL | NULL | NULL |
| NULL | NULL | 5 | 背景 | 6 |
±-----±-------±-----±-----±--------+