一. 数据库简介与安装
1 数据库介绍
从大型的BAT门户,到电商平台,分类门户等,都在使用MySQL数据库.
- MySQL的优点(了解)
- MySQL性能
- 开放源代码
- 历史悠久,社区用户活跃,遇到问题易于解决
- 体积小,安装使用简单,易于维护,安装维护成本低
2 数据库安装配置
略
二. 数据相关操作
1 数据库(database)的操作
2-1. 创建数据库
create database [if not existst] 数据库名;
创建一个名称为testdb的数据库
create database testdb;
创建一个使用gdk字符集的testdb数据库
create database testdb character set gbk;
2-2. 查看数据库
显示所有数据库
show databases;
显示某数据库创建信息:
show create database 数据库名;
显示当前数据库
select database();
2-3. 修改数据库
把已存在的数据库的字符集修改为utf8
alter database 数据库名 character set utf8;
2-4. 删除数据库
drop database testdb;
2-5. 使用数据库
use 数据库名
三 表(table)的操作
1 字段的类型
-
数值
tinyint 1字节 int 4字节 double 8字节 decimal(m,n) m个字节，n个小数 略 -
字符串
char varchar 略 -
日期类型
datetime 略 -
Null 表示为知， 两个null 不能使用=做判断。
2 字段的相关属性
- 非空约束
语法：列名 类型 not null
- 无符号
语法：列名 类型 UNSIGNED
- 自增长
语法：列名 类型 auto_increment #用于数值型，主键
- 默认值
语法：属性名 类型 default 默认值
5.零填充 ZEROFILL
1. 创建表 (field:指定列名,datatype:指定数据类型)
create table [if not exists] 表名 ( field1 字段类型1, field2 字段类型2, field3 字段类型3。。。 ) engine=表类型 character set 字符集2. 查看表
查看所有表
show tables
查看某个表结构desc 表名
查看表创建语句show create table 表名3. 修改表
修改表的名称:
rename table 表名 to 新表名;
添加一列:alter table 表名 add field datatype;
修改列名:alter table 表名 change field newfield datatype;
修改列类型alter table 表名 modify field newdatatype;
删除列:alter table 表名 drop 列名;4. 删除表
drop table 表名;
四 记录的操作 (一)
假设表
create table students (
id int auto_increment primary key,
name varchar(20),
score int(3)
);
1. 添加一条数据(insert)
一次插入一条记录
insert into students (id,name,score) values (null,'张三',90);
一次插入多条记录
insert into students values (null,'李莉',99),(null,'赵六',60);
2. 修改(update)
-
将所有的学生分数修改为100
-
将姓名为 李莉的成绩改为 90
update student set score=90 where name='李莉'; -
将没有及格的学生设为60分
update students set score=60 where score<60;
3. 删除数据(delete)
删除表中名称为’张三’的记录
delete from students where name='张三';
删除所有记录
delete from students;
使用truncate删除表中记录
truncate students;
记录的操作 (二)
假设表
create table students (
id int not null auto_increment primary key,
name varchar(20),
chinese int,
english int,
math int
);
1. 普通查询 select
查询所有的学生所有字段
select * from students;
查询所有学生对应的数学和英语
select name,math,english from students;
过滤表中的重复数据
select distinct math from students;
所有学生的分数+10分显示
select name, english+10,chinese+10,math+10 from students;
查询学生总分
select name,english+chinese+math from students;
查询使用别名(as可省略)
select name as 姓名, english+chinese+math as 总分 from students;
select name 姓名,english+chinese+math 数学 from students;
2. 条件查询
查询英语成绩大于90分的同学
select name,english from students where english>90;
查询总分大于270的同学
select name,english+chinese+math 总分 from students where english+chinese+math>270;
数学成绩在80~100之间的同学
select name, math from students where math>=80 and math<=100;
select name, math from students where math between 80 and 100;
查询数学成绩在57,58,59,60的同学
select name,math from students where math in (57,58,59,60);
查询数学成绩>90,英语<60的同学
select * from students where math>90 and english<60;
3. 模糊查询
查询名字中带有君的
select * from students where name like '%君%';
查找名字叫张x的学生
select * from students where name like '张_';
查找名字叫张xx的同学
select * from students where name like '张__';
4. 排序查询(order by column asc|desc)
按数学成绩高低排序
select * from students order by math desc;
按总分成绩由低到高
select math+chinese+english 总成绩 from students order by math+chinese+english asc;
对姓张的学生排序输出
select name 姓名, math+english+chinese 总成绩 from students where name like '张%' order by math+english+chinese;
5. 分组查询 (group by):
where 在分组之前进行过滤，不能使用聚合函数
having 在分组之后进行过滤，可以使用聚合函数,having中用到的列，必须在前面使用过才可以用
select id,name from students group by math;
7. 使用聚合函数
count :统计符合条件的记录个数
统计一个班级的共有多少学生 select count(*) from students; 统计数学及格的学生个数 select count(*) from students where math>=60;统计各班级人数
select grade, count(1) from student group by grade;
sum : 返回满足条件的,列对应数据的总和
统计一个班级的数学总成绩 select sum(math) from students; 统计班级各科总成绩: select sum(math),sum(english),sum(chinese) from students;
avg : 返回一列的平均值
求一个班级数学平均分
select avg(math) from students;
求一个班级总分的平均分
select avg(math+english+chinese) from students;
select avg(ifnull(math,0)+ifnull(english,0)+ifnull(chinese,0)) from students;
求3班总分的平均分
select
avg(ifnull(math,0)+ifnull(chinese,0)+ifnull(english,0))
from student
where grade=3;
max/min : 函数返回满足where条件的列最大值/最小值
`班级数学最高分 select max(math) from students; 班级成绩的最高分 select max(math)+max(chinese)+max(english) from students; `##多表设计
- 外键约束:明确表与表中,列的关系,从而使数据库维护这种关系,保证数据数据的正确性和完整性;
/*创建数据库*/ create database if not exists customDB; use customDB; /*创建顾客表*/ create table if not existst customer( c_id char(6) primary key, name varchar(30) not null, location varchar(30), salary decimal(8,2) ); /*创建银行表*/ create table if not exists bank( b_id char(5) primary key, bank_name char(30) not null ); /*创建存款表*/ create table if not exists deposite( d_id int(10) auto_increment primary key, c_id char(6), b_id char(5), dep_date date, amount decimal(8,2), constraint FK_c_id foreign key(c_id) references customer(c_id) ); alter table deposite add constraint FK_b_id foreign key(b_id) references bank(b_id);
###多表查询
多表查询 部门表、员工表设计
`DROP TABLE IF EXISTS dept ;
CREATE TABLE dept(
id INT(4) PRIMARY KEY,
NAME VARCHAR(10)
);
INSERT INTO dept VALUES (1001,‘产品部’),(1002,‘行政部’),(1003,‘销售部’),(1004,‘人事部’),(1005,‘技术部’);
DROP TABLE IF EXISTS emp ;
CREATE TABLE emp (
id INT(5) AUTO_INCREMENT,
NAME VARCHAR(4),
dept_id INT(5),
PRIMARY KEY(id),
FOREIGN KEY(dept_id) REFERENCES dept(id)
);
INSERT INTO emp VALUES (NULL,'张飞',1001),
(NULL,'关羽',1001),(NULL,'刘备',1002),(NULL,'曹操',1003),(NULL,'赵云',1004);
`
- 笛卡尔积查询：两张表中一条一条对应的记录，m条记录和n条记录查询，最后得到m*n条记录
select * from ta ,tb;- 内连接：查询两张表中都有的关联数据,相当于利用条件从笛卡尔积结果中筛选出了正确的结果。
select * from ta ,tb where ta.tb_id = tb.id;
select * from ta inner join tb on ta.tb_id = tb.id;- 左外连接：在内连接的基础上增加左边有右边没有的结果
select * from ta left join tb on ta.tb_id = tb.id;- 右外连接：在内连接的基础上增加右边有左边没有的结果
select * from ta right join tb on ta.tb_id = tb.id;- 全外连接：在内连接的基础上增加左边有右边没有的和右边有左边没有的结果
select * from ta full join tb on ta.tb_id = tb.id; --mysql不支持全外连接
select * from ta left join tb on ta.tb_id = tb.id
union
select * from ta right join tb on ta.tb_id = tb.id;
##数据库备份
-
备份数据库:
mysqldump -u root -p 数据库名>D:/file/test.sql
注意：1）mysqldump 不要写成mysql dump，
2）语句结束要不要加； -
恢复数据库:
mysql -u root -p 数据库名<D:/file/test.sql
使用当前数据下,source D:/file/test.sql
##以下内容请查看对应练习
子查询
在sql语句中，又嵌套了另外一个查询语句
索引
作用：
提高查询速度
确保数据的唯一性
可以加速表和表之间的连接，实现表与表之间的参照完整性
使用分组和排序子句进行数据检索时，可以显著减少分组和排序的时间
全文检索字段进行搜索优化
分类:
1. 主键索引（PRIMARY KEY）
确保数据记录的唯一性和非空，并作为其他表中外键参考的列
2. 唯一索引（UNIQUE）
避免同一个表中某数据列中的值重复
3. 常规索引（INDEX）
快速定位特定数据
4. 全文索引（FULLTEXT）：用于MyISAM类型的数据表
快速定位特定数据
注意
主键和唯一索引的区别
全文索引 用于MyISAM类型的数据表
不要对经常变动的数据加索引，影响数据的插入、删除和修改操作
小数据量的表建议不要加索引
索引一般应加在查找条件的字段
事务
事务就是将一组SQL语句放在同一批次内去执行如果一个SQL语句出错，则该批次内的所有SQL都将被取消执行
注意:MySQL事务处理只支持InnoDB和BDB数据表类型
事务的ACID原则:
1、原子性(Atomicity)：事务中的全部操作在数据库中是不可分割的，要么全部完成，要么均不执行。
2、一致性(Consistency)：几个并行执行的事务，其执行结果必须与按某一顺序串行执行的结果相一致。
3、隔离性(Isolation)：事务的执行不受其他事务的干扰，事务执行的中间结果对其他事务必须是透明的。
4、持久性(Durability)：对于任意已提交事务，系统必须保证该事务对数据库的改变不被丢失，即使数据库出现故障。
事务的语法:
`set autocommit=0;#关闭自动提交
start transation;#开启事务
#事务操作
#事务操作
commit;#rollback#提交操作或者回滚
set autocommit=1;还原自动提交`
例子：顾客A在线购买一款商品，价格为500.00元，银行转账支付,顾客A银行卡的余额为2000.00元，卖家B的账号金额10000.00元，顾客A向卖家B支付500元；
账户表：
create table account(
id int(11) auto_increment primary key,
name varchar(32),
cash decimal(9,2)
) engine=innodb;
insert into account (id,name,cash) values ('A',2000);
insert into account (id,name,cash) values (1111,'B',10000);
把A账户减少，B账户增加，作为事务操作
update account set cash=cash-500 where id=1001;
update account set cash=cash+500 where id=1111;
视图
视图是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含一系列带有名称的列和行数据。但是，视图并不在数据库中以存储的数据值集形式存在。行和列数据来自由定义视图的查询所引用的表，并且在引用视图时动态生成。
* 有灵活性的功能需求后，需要改动表的结构而导致工作量比较大。可以使用虚拟表的形式达到少修改的效果
* 视图可以隐藏一些数据，如：社会保险基金表，可以用视图只显示姓名，地址，而不显示社会保险号和工资数等
* 可使复杂的查询易于理解和使用
视图中虽然可以更新数据，但是有很多的限制。一般情况下，最好将视图作为查询数据的虚拟表，而不要通过视图更新数据。因为，使用视图更新数据时，如果没有全面考虑在视图中更新数据的限制，就可能会造成数据更新失败。
5636
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
