表数据
一些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语句(2) 6.
在所有学生分数上加10分特长分显示。
统计每个学生的总分。
使用别名表示学生总分。
练习
查询姓名为XXX的学生成绩
查询英语成绩大于90分的同学
查询总分大于200分的所有同学
使用where子句,进行过滤查询。练习:
Select语句(3) 7.
在where子句中经常使用的运算符
Select语句(4) 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 语句 ( 4 ) 练习
使用order by 子句排序查询结果。
SELECT column1, column2. column3…
FROM table_name
order by column_name asc|desc
Order by 指定排序的列,排序的列既可是表中的列名,也可以是select 语句后指定的列名。
Asc 升序(默认)、Desc 降序
ORDER BY 子句应位于SELECT语句的结尾。
Select语句(5) 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 |
±-----±-------±-----±-----±--------+