mysql知识总结

一. 数据库简介与安装

1 数据库介绍

从大型的BAT门户,到电商平台,分类门户等,都在使用MySQL数据库.

  1. 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 字段的相关属性

  1. 非空约束
语法:列名 类型 not null
  1. 无符号
语法:列名 类型 UNSIGNED
  1. 自增长
语法:列名 类型 auto_increment #用于数值型,主键
  1. 默认值
语法:属性名 类型 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);
`
  1. 笛卡尔积查询:两张表中一条一条对应的记录,m条记录和n条记录查询,最后得到m*n条记录
    select * from ta ,tb;
  2. 内连接:查询两张表中都有的关联数据,相当于利用条件从笛卡尔积结果中筛选出了正确的结果。
    select * from ta ,tb where ta.tb_id = tb.id;
    select * from ta inner join tb on ta.tb_id = tb.id;
  3. 左外连接:在内连接的基础上增加左边有右边没有的结果
    select * from ta left join tb on ta.tb_id = tb.id;
  4. 右外连接:在内连接的基础上增加右边有左边没有的结果
    select * from ta right join tb on ta.tb_id = tb.id;
  5. 全外连接:在内连接的基础上增加左边有右边没有的和右边有左边没有的结果
    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;

视图

​ 视图是一个虚拟表,其内容由查询定义。同真实的表一样,视图包含一系列带有名称的列和行数据。但是,视图并不在数据库中以存储的数据值集形式存在。行和列数据来自由定义视图的查询所引用的表,并且在引用视图时动态生成。
​ * 有灵活性的功能需求后,需要改动表的结构而导致工作量比较大。可以使用虚拟表的形式达到少修改的效果
​ * 视图可以隐藏一些数据,如:社会保险基金表,可以用视图只显示姓名,地址,而不显示社会保险号和工资数等
​ * 可使复杂的查询易于理解和使用

视图中虽然可以更新数据,但是有很多的限制。一般情况下,最好将视图作为查询数据的虚拟表,而不要通过视图更新数据。因为,使用视图更新数据时,如果没有全面考虑在视图中更新数据的限制,就可能会造成数据更新失败。
发布了45 篇原创文章 · 获赞 1 · 访问量 467
展开阅读全文

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 1024 设计师: 上身试试

分享到微信朋友圈

×

扫一扫,手机浏览