数据库入门
△用数据库的原因
1文件操作的复杂度
2同步
3并发处理
4安全
△数据库系统(DBS)
数据库(DB) + 数据库管理系统 (DBS)+ 数据库应用程序 + 数据库管理员 (BDA)+ 最终用户
△数据库管理系统-DBM
网络应用服务端
我们要使用服务端的数据 - 需要有一个客户端
客户端可以自己写 : 未来写代码的时候
也可以用别人写好的 : 第三方的工具 数据库管理软件的公司出版的官方客户端
数据库管理系统本质上也是管理一堆文件
只不过人家的管理方式比我们更高效 更安全
△数据库管理员-DBA
搭建数据库服务环境
用户的创建 权限的管理
性能语句的优化
数据库的二次开发 : 让数据库具有公司的特质
△软件
mysql : 小公司
甲骨文 oracle : 事业单位 金融企业
微软 sql server
sqllite
△数据库的分类
关系型数据库 mysql oracle sqlserver sqllite
非关系型数据库 redis mongodb memcache hbase
关系型数据库
优点:
易于维护:都是使用表结构,格式一致
2、使用方便:SQL语言通用,可用于复杂查询
3、复杂操作:支持SQL,可用于一个表以及多个表之间非常复杂的查询
缺点:
读写性能比较差,尤其是海量数据的高效率读写
2、固定的表结构,灵活度稍欠
3、高并发读写需求,传统关系型数据库来说,硬盘I/O是一个很大的瓶颈
非关系型数据库
优点:
1、格式灵活:存储数据的格式可以是key,value形式、文档形式、图片形式等等
2、速度快,成本低:nosql数据库部署简单,基本都是开源软件
缺点:
1、不提供sql支持,学习和使用成本较高
2、无事务处理
3、数据结构相对复杂,复杂查询方面稍欠
△ SQL是关系数据库管理系统的标准语言。
△名词
DB 数据库 - 文件夹
table 表 - 文件
data 一条数据-每一行数据
△三种方法操作数据库
终端,python,第三方工具
△二.mysql编码问题
编码问题
1.临时解决问题在客户端执行set XXXX = utf8;
2.永久解决问题fimy. ini添加set xXxx = utf8;
3.实时解决问题create table表名() charset=utf8;
△服务端-安装启动关闭
mysqld install #安装
net start mysql #启动 以管理员身份在cmd中输入
net stop mysql #关闭
creat user 'eva'@'%'identified by'123';
#创建用户eva 在所有网段允许 设置密码为123
△
1,先跟数据库进行连接(用账号和密码,默认3306端口)
2,创建数据库:
create database 数据库名:
删除数据库(慎用!):
DROP DATABASE 数据库名称;
显示所有数据库:
SHOW DATABASES;
切换数据库:
USE 数据库名称 ;
创建表:
查看表结构
数字类型
整数 :
tinyint(m) 1个字节 -128~127
smalint(m) 1个字节 -32768~32767
==mediumint== 3个字节 -8388608~8388608
==int(m)== 4个字节 -2147483648~2147483647
bigint(m) 8个字节 -9223372036854775808~9223372036854775807
m: 显示宽度,和数据类型的取值范围是无关的
(int unsigned) : unsigned 标上表示无符号
小数 :
MySQL中使用浮点数和定点数来表示小数。它们都可以用(m,n)来表示,其中m称为精度,表示总共的位数;n称为标度,是表示小数的位数(会四舍五入)
==float==(m,d) 单精度浮点型,4字节 -3.402823466E+38~-1.175494351E-38
==double==(m,d) 双精度浮点型,8字节 -1.7976931348623157E+308~-2.225738585072014E-308
decimal(m,d) 压缩严格的定点数,m+2个字节 最大取值范围与double一致
时间类型
==year== 年 酒的产期
==time== 时:分:秒 计时软件
==date== 年-月-日 入职日期 出账日期 还款日期
==datetime== 年-月-日 时:分:秒 1000-01-01 00:00:00~9999-12-31 23:59:59 日志,消费记录,上下班打卡
timestamp 年-月-日 时:分:秒 不能为空,自动写当前时间,级联更新,范围小
==now==() 函数表示当前时间
默认当前时间并级联更新
让datetime 自动写当前时间,级联更新
create table 表(
列 datetime NOT NULL #非空
DEFAULT CURRENT_TIMESTAMP #自动写当前时间
ON UPDATE CURRENT_TIMESTAMP #级联更新当前时间
)
字符串类型
==char==(255) :定长的 节省时间 浪费空间 手机号码、身份证号
==varchar==(65535) : 变长的 节省空间 浪费时间 评论
枚举和集合
==enum== 单选
==set== 多选,自动去重
例如建表时定义字段
爱好 set(“游泳”,“篮球”,“下棋”,“音乐”,“旅游”)
insert into t_1 values("游泳,篮球,音乐")
添加时会自动去重和去掉不存在的
举例-数字 字符串
create table t1(i1 tinyint,i2 int); #默认创建的数据类型都是有符号的 i1-表头
create table t2(i1 tinyint unsigned,i2 int unsigned); #给字段添加一个unsigned表示无符号
create table t3(f1 float,f2 double);
mysql> create table t1(
id int,name varchar(20),
sex enum('man','wuman'),
age int,
hire_date date,
post varchar(20),
post_comment varchar(20), #一般在用char varchar时要限制字符
salary double(7,2),
office int,
depart_id int); 32
举例-小数
create table t3(f1 float,f2 double);
create table t4(f1 float(7,2)); 小数点后2位,小数点前5位
举例-时间
create table t7(dt datetime NOT NULL DEFAULT CURRENT_ TIMESTAMP ON UPDATE CURRENT_ TIMESTAMP,y year);
NOT NULL DEFAULT CURRENT_ TIMESTAMP ON UPDATE CURRENT_ TIMESTAMP #让datetime的时间在无数据填充的情况下会自动更新到此次修该的时间
举例-枚举和集
create table t8(username cha(12),gender enum('male','female'));
create table t8(username cha(12),hobby set('喝酒','抽烟','烫头','洗脚'));
填充的时候也必须时字符串格式,添加时会自动去重和去掉不存在的
在维护和操作时,一定要先备份一份,之后再去操作数据库
会遭遇各的数据丢失的情况:
硬件故障
软件故障
自然灾害
黑客攻击
误操作 (占比最大)
备份
1,在MySQL的bin目录中打开命令窗口(bin目录中才有复制的命令)
2,输入:mysqldump –uroot –p test101 > C : 1.sql (如果有警告可以忽略)
恢复
连接MySQL,创建数据库
在MySQL的bin目录中打开命令窗口
输入:mysql –uroot –p 数据库名 < d: 1.sql
# 默认用户登陆之后并没有实际操作的权限
# 需要使用管理员root用户登陆
# mysql -uroot -p -h mysql5.6默认是没有密码的 遇到password直接按回车键
# mysql> set password = password('root'); 给当前数据库设置密码
# mysql> select user(); #查看当前用户
# 输入“ipconfig”即可查看到本机的ip信息。
# ;表示sql语句的结束
# c放弃当前要执行的sql语句
# mysql> exit # 也可以用q quit退出
创建用户账号并授权
mysql> grant all on *.* to 'eva'@'%' identified by '123'
5.6及以下版本
mysql> grant all on *.* to 'eva'@'localhost' identified by '123'
△分两步:
创建用户
mysql> create user 'eva'@'192.168.10.%' IDENTIFIED BY '123';# 指示网段
mysql> create user 'eva'@'192.168.10.5' # 指示某机器可以连接
mysql> create user 'eva'@'%' #指示所有机器都可以连接
mysql> show grants for 'eva'@'192.168.10.5';查看某个用户的权限
给账号授权
mysql> grant all on *.* to 'eva'@'%';
#grant 权限类型 on 数据库名称 . * (或表名) to 'shang'@'%';
mysql> flush privileges; # 刷新使授权立即生效
操作库(文件夹)
数据备份: mysqldump –uroot –p test101 > C : 1.sql (如果有警告可以忽略)
数据恢复: mysql –uroot –p 数据库名 < d: 1.sql
增:create database db1 charset utf8; charset utf8可省略,创建数据库
查:show databases; 显示所有数据库
改:alter database db1 charset latin1;(改成欧洲的)
删除: drop database db1; 删除数据库(慎用!)
切换::use db1; 切换数据库
操作表(文件)
先切换到文件夹下:use db1
增:create table t1(id int,name char)CHARSET=utf8; 创建表
查: select database(); 查看当前所在库
show tables; 查看所有的表
desc 表; 查看表结构
show creat table 表名; 查看建表语句和搜索引擎,显示的信息更全面
show engines; 查看MySQL重要引擎
改: ALTER TABLE t_2 ADD age INT; 增加列名 类型
RENAME TABLE t_1 TO t_2; 更改表名称
alter table t_1 modify name char(3); 修改列的数据类型
alter table t_1 change name name1 char(2); 修改列名和数据类型
ALTER TABLE t_1 DROP age; 删除列
删:drop table t1
操作表数据
增:insert into t1 values(1,'egon1'),(2,'egon2'),(3,'egon3');
insert into t1(id,name) values (5,'wusir'), (6,'wusir');
insert into t2 select * from t1;
先执行执行select,把查到的内容导入到t2
insert into t1(id,name) select id,name from t1;
查: select * from 表; 查询表的信息
select emp_name,salary from employee; 指定列查询
select emp_name,salary*12 from employee; 在列中使用四则运算
select emp_name,salary*12 as annul_salary from employee; 重命名
select emp_name,salary*12 annul_salary from employee; 重命名
select distinct post from employee; 去重
select distinct sex,post from employee; 双向去重
改: update 表 set 字段1=值1,字段2=值2 where 条件;
删:delete from t_1 where id=1;
如果不加where:DELETE FROM t_person; 会删除所有表中的内容
通用建表语句:
CREATE TABLE sanguo(
id INT PRIMARY KEY AUTO_INCREMENT, # 主键自增
NAME VARCHAR(32),
age INT,
arms VARCHAR(32),
country VARCHAR(32),
skill VARCHAR(32)
)engine = innodb CHARSET=utf8;
where
having要跟在group by之后,对分组查询的结果进行过滤(过滤分组)
where要出现在group by之前,执行表中所有数据来进行过滤(过滤行)
另外,having可以用聚合函数,并支持所有where子句操作符数
函数 concat() 拼接
# select concat('姓名 :',emp_name),concat('年薪:',salary*12) from employee;
# select concat_ws('|','a','b','c')
case when语句
== if条件判断句
3SELECT
(
CASE
WHEN emp_name = 'jingliyang' THEN
emp_name
WHEN emp_name = 'alex' THEN
CONCAT(emp_name,'_BIGSB')
ELSE
concat(emp_name, 'SB')
END
) as new_name
FROM
employee;
-- 代表注释
查询用的函数
count 统计值
select concat ('<名字:',name, '>' , '<薪资:',salary,'>') from 表;
max 最大值
min 最小值
avg 平均值
sum 求和
ascii(str)
查看字符的ASCII码值,str是空时返回0
SELECT ASCII('a')
char(数字)
查看ASCII码值对应的字符
SELECT CHAR(97)
concat 拼接字符串
SELECT CONCAT(12,34,'ab')
SELECT uname,CONCAT(age,'岁') FROM t_user;
length(str)
字符串中包含的字符个数
SELECT LENGTH('abc')
left(str,len)
截取字符串
截取字符串左端的len个字符
SELECT LEFT('qwertyui',3)
right(str,len)截取字符串右端的len个字符
SELECT RIGHT('qwertyui',3)
substring(str,pos,len) 指定位置截取,截取字符串str的位置pos起的len个字符(从1开始)
SELECT SUBSTRING('qwertyuio',2,3)
截取所有人物的姓
SELECT SUBSTRING(uname,1,1) FROM t_user;
同时去除重复项
SELECT DISTINCT SUBSTRING(uname,1,1) FROM t_user;
ltrim(str)
返回删除了左空格的字符串
SELECT LTRIM(' abc ')
rtrim(str)
返回删除了右空格的字符串
SELECT RTRIM(' abc ')
trim(方向 remstr from str):返回从某侧删除remstr后的字符串str
方向词包括both(两侧)、leading(左)、trailing(右)
SELECT TRIM(‘ abc ’) --删除两侧空格
SELECT TRIM(BOTH ‘x’ FROM ‘xxxabcxxx’) --删除两侧特定字符
SELECT TRIM(LEADING 'x' FROM 'xxxabcxxx') 删除左侧特定字
SELECT TRIM(TRAILING 'x' FROM 'xxxabcxxx') 删除右侧特定字
space(n)
返回由n个空格组成的字符串
replace(str,from_str,to_str)
替换字符串
SELECT REPLACE('123abc123','123','def')
lower(str) upper(str)
大小写转换
SELECT LOWER('aBcD')
SELECT UPPER('aBcD')
mod(m,n)
求m%n的余数
SELECT MOD(3,2)
abs(n)
求绝对值
SELECT ABS(-30)
floor(n)
表示向下取整
SELECT FLOOR(5.6)
ceiling(n)
表示向上取整
SELECT CEILING(5.6)
round(n)
表示将值 n 四舍五入为整数,无小数位
SELECT ROUND(5.6)
round(n,d)
表示将值 n 四舍五入为小数点后 D 位的数值,D为小数点后小数位数
若要保留 n 值小数点左边的 D 位,可将 D 设为负值
SELECT ROUND(345.6789,2)
pow(x,y)
求x的y次幂
SELECT POW(2,3)
PI()
获取圆周率
SELECT PI( )
rand()
获取一个0.0-1.0之间的随机数
SELECT RAND( )
获取当前日期
SELECT CURRENT_DATE()
获取当前时间
SELECT CURRENT_TIME()
获取当前日期和时间
SELECT NOW()
时间和日期格式化
date_format(data,format)
SELECT DATE_FORMAT('2018-8-8','%Y年%m月%d日')
SELECT DATE_FORMAT(CURRENT_DATE(),'%y年%m月%d日')
UPDATE t_user SET brithday=DATE_FORMAT(CURRENT_DATE(),'%y年%m月%d日')
WHERE uname = '吕布'
format参数可用的值如下:
%Y 年份,返回4 位整数
%y 年份,返回2 位整数
%m 月,返回0-12的整数
%d 日期,返回0-31之间的整数
%H 小时 (00..23)
%h 小时 (01..12)
%i 分钟(00..59)
%s 秒 (00..59)
单表查询
公式
使用算数表达式查询某几列:年龄增加50
SELECT uname,country,age+50 FROM t1;
as
SELECT age+50 AS '年龄' FROM t_user; #修改列名的显示(起别名)注意不要用关键字,as可以省略
distinct 查询时去重
SELECT DISTINCT country FROM t_user;
SELECT DISTINCT country,uname FROM t_user; #联合去重
查询每个国家都有谁,注意:distinct后面的字段用逗号分隔,逗号两边不能有空格
between
a and b [a,b]
# select * from employee where salary between 10000 and 20000;
in
# select * from employee where salary in (17000,19000);
like 模糊查询
_ 通配符 表示一个字符长度的任意内容
select * from employee where emp_name like 'jin___'
% 通配符 表示任意字符长度的任意内容
select * from employee where emp_name like 'jin%'
select * from employee where emp_name like '%g'
select * from employee where emp_name like '%n%'
regexp 正则匹配
# select * from employee where emp_name regexp '^jin'
查看所有员工中名字是jin开头,n或者g结果的员工信息
select * from employee where emp_name regexp '^jin.*[gn]$';
IS NULL关键字
(判断某个字段是否为NULL不能用等号,需要用IS)
SELECT emp_name,post_comment FROM employee WHERE post_comment IS NULL;
SELECT emp_name,post_comment FROM employee WHERE post_comment IS NOT NULL;
SELECT emp_name,post_comment FROM employee WHERE post_comment='';
注意''是空字符串,不是null
ps:
执行
update employee set post_comment='' where id=2;
再用上条查看,就会有结果了
比较运算
= > < >= <= != / <>
select * from employee where age>18;
select * from employee where salary<10000;
select * from employee where salary=20000;
逻辑运算
and or not
and
select * from employee where age>18 and post='teacher';
or
select * from employee where salary<10000 or salary>30000;
not
select * from employee where salary not in (10000,17000,18000);
分组聚合 group by
# 查询岗位名以及岗位包含的所有员工名字
# select post,group_concat(emp_name) from employee group by post;
# 查询各部门年龄在20岁以上的人的平均薪资
# select post,avg(salary) from employee where age>20 group by post;
# select * from 表 where 条件 group by 分组
having 对分组之后过滤 (group by 配合having)
having放在分组之后,因为作用的对象不同。WHERE 子句作用于表和视图,HAVING 子句作用于组
查询平均薪资大于1w的部门
select avg(salary) from employee group by post having avg(salary) > 10000
1. 查询各岗位内包含的员工个数小于2的岗位名、岗位内包含员工名字、个数
select post,emp_name,count(id) from employee group by post having count(id)<2
2. 查询各岗位平均薪资大于10000的岗位名、平均工资
select post,avg(salary) from employee group by post having avg(salary) > 10000
3. 查询各岗位平均薪资大于10000且小于20000的岗位名、平均工资
select post,avg(salary) from employee group by post having avg(salary) between 10000 and 20000;
order by 排序
# asc 升序
# select * from employee order by salary;
# select * from employee order by salary asc;
# desc 降序
# select * from employee order by salary desc;
# select * from employee order by age,salary;
# select * from employee order by age,salary desc;
# select * from employee order by age desc,salary;
排列顺序从高到低 DESC
limit 分页
在查询时可以只检索前几条或者中间某几行数据(数据量很大时,几百万条)
SELECT * FROM t_user LIMIT 0,3;
limit 后面的第一个数字设置从哪里开始检索(偏移量,从0开始)
limit 后面的第二个数字是设置显示多少条
# select * from 表 order by 列 limit n; 取前n条
# select * from 表 order by 列 limit m,n; 从m+1开始,取n条
# select * from 表 order by 列 limit n offset m; 从m+1开始,取n条
多表查询
连表查询
内连接
所有不在条件匹配内的数据,都会被剔出连表
方式一 :
select * from employee,department where dep_id = department.id;
方式二 :
select * from employee inner join department on dep_id = department.id;
外连接
left join
左外连接
select * from employee left join department on dep_id = department.id;
right join
右外连接
select * from employee right join department on dep_id = department.id;
全外连接
别的数据库里是full join
select * from employee left join department on dep_id = department.id
union
select * from employee right join department on dep_id = department.id
子查询
select * from 表 where 字段 = (select ....);
子查询效率相对于连表查低, 因为 子查询要查两张表,连表查询查一张表
primary key 主键 =非空+唯一
创建表时设置主键
create table t6(id int primary key, name char(12) not null unique);
create table t5(family char(12) ,name char(12),primary key(family,name));
# 约束各自不能为空 且联合唯一 还占用了整张表的主键
创建表之后设置主键
CREATE TABLE t1(
id INT NOT NULL,
NAME CHAR(20)
);
ALTER TABLE t1 ADD PRIMARY KEY (id);
自动更新的主键
CREATE TABLE t_user(
id INT PRIMARY KEY AUTO_INCREMENT,
-- id 你自己起的字段名字。
-- int 数据类型,整型。
-- primary key 定义这个字段为主键。
-- auto_increment 定义这个字段为自动增长,即如果INSERT时不赋值,则自动加1
auto_increment 自增
自增的必须是主键
create table t6(id int auto_increment, name char(12)); # 报错
create table t8(id int primary key auto_increment, name char(12)) ;
create table t9(id int unique auto_increment, name char(12)) auto_increment=100000; 自增=>非空 非空+唯一约束会被定义成主键
delete from t7; 清空表数据但不能重置auto_increment
truncate table t7; # 清空表并且重置auto_increment
所有的操作都无法改变auto_increment的自动计数。但是我们也没有必要去改变它。
1.至少要看到自增的效果
2.至少写3条数据 4,5,6
3.删掉第5条,再看结果
4.再insert一条数据
5.删掉第5条,再看结果
6.再insert一条数据
7.清空整张表
8.再insert一条数据,再看结果
修改auto_increment
alter table 表名 auto_increment = n; 修改表的auto_increment
alter table t7 auto_increment = 1000; 修改表的auto_increment
not null 非空
# create table t1(id int not null,name char(12));
# 默认插入0
# create table t2(id int,name char(12) not null);
# 默认插入空字符串
```
设置严格模式:
不支持对not null字段插入null值
不支持对自增长字段插入" ”值-空字符串
不支持text字段有默认值
直接在mysql中生效(重启失效):
mysql>set sql_mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION";
配置文件添加(永久失效):
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
```
unique唯一 (不能重复)
# create table t4(id int unique,name char(12));
# 联合唯一约束
# create table t5(family char(12),name char(12),unique(family,name));
# 约束各自不能为空 且联合唯一
# create table t5(family char(12) not null,name char(12) not null,unique(family,name));
# 唯一+非空 id name
# create table t6(id int not null unique, name char(12) not null unique);
# pri 是怎么产生的? 第一个被设置了非空+唯一约束会被定义成主键 primary key
# 主键在整张表中只能有一个
default 默认值
create table t3(id int,name char(12),sex enum('male','female') default 'male');
非空约束 和 默认值
create table t3(id int not null,name char(12) not null,sex enum('male','female') not null default 'male');
外键
△外键:用来和其他表建立联系
外键具有保持数据完整性和一致性的机制,目前MySQL只在InnoDB引擎下支持
(ENGINE=INNODB)
外键是表中的一个列,其值必须在另一个表的主键或者唯一键中列出
作为主键的表称为 主表,作为外键的表称为 依赖表
外键会参照主表的主键或唯一键
△外键的作用有两点:
1.对子表(外键所在的表)的作用:子表在进行写操作的时候,如果外键字段在父表中找不到对应的匹配,操作就会失败
2.对父表的作用:对父表的主键字段进行删和改时,如果对应的主键在子表中被引用,操作就会失败
△以下情况创建外键会失败:
外键的引用类型不一样,如主键是int外键是char
找不到主表中引用的列
主键和外键的字符编码不一样
foreign key(class_id) references class3(cid)
没有建立外键:
create table stu(id int,name char(12),class_id int);
create table class(cid int,cname char(12));
insert into stu values (1,'日魔',1),(2,'炮手',1)
insert into class values(1,'py27');
insert into class values(2,'py28');
select * from stu,class where class_id = cid;
delete from stu where id = 1;
delete from class where cid = 1;
stu2 class2
create table class2(cid int unique,cname char(12));
create table stu2(id int,name char(12),class_id int,foreign key(class_id)
references class2(cid));
insert into class2 values(1,'py27');
insert into stu2 values (1,'日魔',1),(2,'炮手',1)
delete from class2 where cid = 1;
insert into class2 values(2,'py28');
update class2 set cid = 1 where cid = 2; 不能修改
关联关系
一对一
# create table guest(id int primary key,name char(12));
# create table student(id int primary key,sname char(12),gid int unique,
# foreign key(gid) referances guest(id));
一对多 foreign key
# create table class(id int primary key,cname char(12));
# create table student(id int primary key,sname char(16),cid int,
# foreign key(cid) references class(id));
多对多
# create table class(id int primary key,cname char(12));
# create table teacher(id int primary key,tname char(12));
# create table teach_cls(id int,cid int,tid int,
# foreign key(cid) references class(id)),
# foreign key(tid) references teacher(id))
# );
级联更新
on update cascade
stu3 class3 级联更新
create table class3(cid int primary key,cname char(12));
create table stu3(id int,name char(12),class_id int,foreign key(class_id)
references class3(cid) on update cascade);
insert into class3 values(1,'py27');
insert into stu3 values (1,'日魔',1),(2,'炮手',1)
update class3 set cid = 2; 修改了class3中的cid,stu3中相关的数据也会跟着变化,
是on update cascade设置导致的
意义
数据库存储引擎是数据库底层软件组件,数据库管理系统使用数据引擎进行创建、查询、更新和删除数据操作
使用不同的存储引擎还可以获得特定的功能
存储方式、存储机制
表结构 存在一个文件中 : 硬盘上
表数据 存在另一个文件中、内存中
索引(目录) 为了方便查找设计的一个机制 :
存储引擎的种类
innodb :
存储方式为索引+数据 表结构 ,数据的持久化存储
提供了具有提交、回滚和崩溃恢复能力的事务安全,支持外键。但是比起Myisam存储引擎,InnoDB写的处理效率差一些并且会占用更多的磁盘空间。MySQL 5.5.5 之后,InnoDB 作为默认存储引擎.
myisam :
存储方式为索引 数据 表结构 数据的持久化存储 ,有表级锁
不支持事务、也不支持外键,但访问速度快,对事务没有要求
memory :
存储方式为表结构,数据断电消失
查看表引擎
show create table books;
查看MySQL重要引擎
show engines;
设置引擎
建表的时候可以指定引擎
create table innot(id int) engine = innodb;
create table myist(id int) engine = myisam;
create table memot(id int) engine = memory;
一致性 n条语句的执行状态是一致的
begin; # 开启事务
select id from innot where id =1 for update;
update innot set id = 2 where id = 1;
commit; # 提交事务 解锁被锁住的数据,让他们能够被修改
mysql锁
行级锁 :只对涉及到修改的行加锁,利于并发的修改,但是对于一次性大量修改效率低下
表级锁 :一次性加一把锁就锁住了整张表,不利于并发的修改,但是加锁速度比行锁的效率要高
外键约束 :被约束表中的数据不能随意的修改/删除 约束字段据要根据被约束表来使用数据
索引原理
△磁盘预读性原理
1个block块 4096个字节/9ms
△树
树 根节点 分支节点 叶子节点
平衡树 balance tree - B树
△聚集索引/聚簇索引 : 叶子节点会存储整行数据 —— innodb的主键
△辅助索引/非聚集索引 :除了主键之外的普通索引都是辅助索引,一个索引没办法查到整行数据,需要回聚集索引再查一次(回表)
△b+树 是为了更好的处理范围问题在b树的基础上有所优化
△mysql中innodb存储引擎的所有的索引树都是b+树
优点:
当数据库中存在很多条记录,例如几十万条,查询速度就成了一个问题
先在目录中查询,然后根据目录所示的页码找到查询内容,大大缩短查询时间.
不使用索引,MySQL必须从第一条记录开始读完整个表,直到找出相关的行
表中查询的列有一个索引,MySQL能够快速到达一个位置去搜索数据文件
缺点:
索引也需要占空间,如果我们有大量的索引,索引文件可能会占很多空间
3、当对表中的数据进行增加、删除、修改时,索引也需要动态的维护,降低了数据的维护速度。
使用原则:
建议
对经常更新的表就避免对其进行索引,对经常用于查询的字段应该创建索引
2.数据量小的表最好不要使用索引
3.在不同值少的列上(字段上)不要建立索引,比如在"性别"字段上只有男,女两个不同值
建立一个索引
1,创建表时,主键会默认带有索引
- 创建表时直接指定
CREATE TABLE mytable( -- 创建表时直接指定
ID INT NOT NULL,
username VARCHAR(16) NOT NULL,
INDEX index_1 (username)
);
补索引
CREATE INDEX index_1 ON t_user(uname)
创建一个索引 索引名 从 表名 在哪个字段
删除索引
drop index index_1 on t_user(uname);
在创建一个视图时,只存放视图的定义,也就是动态检索数据的查询语句,并不存放视图对应的数据,在用户使用视图时才去求相对应的数据,所以视图称为‘虚表’
-- 查看每个国家的总人数,年龄总和,平均年龄,最高年龄,最低年龄
SELECT country,COUNT(uname),SUM(age),AVG(age),MAX(age),MIN(age) FROM t_user
GROUP BY country;
为上面的语句创建视图:
CREATE VIEW v_user AS
SELECT country,COUNT(uname),SUM(age),AVG(age),MAX(age),MIN(age) FROM t_user
GROUP BY country;
SELECT * FROM v_user; --以后在使用上面的查询语句时只需要使用视图名
import pymsql
conn=pymyssq.connet
(host='localhost,user='root',pqssword='123',db='数据库名',charset='utf8')
#先修路-conn
cur = conn.cursor() #备车-cur
sql = '语句' #装货
cur.execute(sql) #发车
emps = cur.fetchall() #带回交易货物
for i in data:
print(i) #查货
create table employee(
id int not null unique auto_increment,
emp_name varchar(20) not null,
sex enum('male','female') not null default 'male', #大部分是男的
age int(3) unsigned not null default 28,
hire_date date not null,
post varchar(50),
post_comment varchar(100),
salary double(15,2),
office int, #一个部门一个屋子
depart_id int
);
#插入记录
#三个部门:教学,销售,运营
insert into employee(emp_name,sex,age,hire_date,post,salary,office,depart_id) values
('egon','male',18,'20170301','老男孩驻沙河办事处外交大使',7300.33,401,1), #以下是教学部
('alex','male',78,'20150302','teacher',1000000.31,401,1),
('wupeiqi','male',81,'20130305','teacher',8300,401,1),
('yuanhao','male',73,'20140701','teacher',3500,401,1),
('liwenzhou','male',28,'20121101','teacher',2100,401,1),
('jingliyang','female',18,'20110211','teacher',9000,401,1),
('jinxin','male',18,'19000301','teacher',30000,401,1),
('成龙','male',48,'20101111','teacher',10000,401,1),
('歪歪','female',48,'20150311','sale',3000.13,402,2),#以下是销售部门
('丫丫','female',38,'20101101','sale',2000.35,402,2),
('丁丁','female',18,'20110312','sale',1000.37,402,2),
('星星','female',18,'20160513','sale',3000.29,402,2),
('格格','female',28,'20170127','sale',4000.33,402,2),
('张野','male',28,'20160311','operation',10000.13,403,3), #以下是运营部门
('程咬金','male',18,'19970312','operation',20000,403,3),
('程咬银','female',18,'20130311','operation',19000,403,3),
('程咬铜','male',18,'20150411','operation',18000,403,3),
('程咬铁','female',18,'20140512','operation',17000,403,3)
;
作业:
数据库小考
书名 | 作者 | 出版社 | 价格 | 出版日期 |
---|---|---|---|---|
倚天屠龙记 | egon | 北京工业地雷出版社 | 70 | 2019-7-1 |
九阳神功 | alex | 人民音乐不好听出版社 | 5 | 2018-7-4 |
九阴真经 | yuan | 北京工业地雷出版社 | 62 | 2017-7-12 |
九阴白骨爪 | jinxin | 人民音乐不好听出版社 | 40 | 2019–8-7 |
独孤九剑 | alex | 北京工业地雷出版社 | 12 | 2017-9-1 |
降龙十巴掌 | egon | 知识产权没有用出版社 | 20 | 2019-7-5 |
葵花宝典 | yuan | 知识产权没有用出版社 | 33 | 2019–8-2 |
0.建表book,并向表中插入数据
1.查询egon写的所有书和价格
2.找出最贵的图书的价格
3.求所有图书的均价
4.将所有图书按照出版日期排序
5.查询alex写的所有书的平均价格
6.查询人民音乐不好听出版社出版的所有图书
7.查询人民音乐出版社出版的alex写的所有图书和价格
8.找出出版图书均价最高的作者
9.找出最新出版的图书的作者和出版社
10.显示各出版社出版的所有图书
11.查找价格最高的图书,并将它的价格修改为50元
12.删除价格最低的那本书对应的数据
13.将所有alex写的书作业修改成alexsb
14.select year(publish_date) from book
自己研究上面sql语句中的year函数的功能,完成需求:
将所有2017年出版的图书从数据库中删除
15.有文件如下,请根据链接自学pymysql模块,使用python写代码将文件中的数据写入数据库
学python从开始到放弃|alex|人民大学出版社|50|2018-7-1
学mysql从开始到放弃|egon|机械工业出版社|60|2018-6-3
学html从开始到放弃|alex|机械工业出版社|20|2018-4-1
学css从开始到放弃|wusir|机械工业出版社|120|2018-5-2
学js从开始到放弃|wusir|机械工业出版社|100|2018-7-30
# select distinct * from 表 where 条件 group by 字段
# having 过滤 order by 字段 limit n offset m;
# 书名 作者 出版社 价格 出版日期
# create table books(bname char(20),
# author char(12),
# press char(20),
# price float(6,2),
# pub_date date);
# insert into books values
# ('倚天屠龙记','egon','北京工业地雷出版社',70,'2019-7-1'),
# ('九阳神功','alex','人民音乐不好听出版社',5,'2018-7-4'),
# ('九阴真经','yuan','北京工业地雷出版社',62,'2017-7-12'),
# ('九阴白骨爪','jinxin','人民音乐不好听出版社',40,'2019–8-7'),
# ('独孤九剑','alex','北京工业地雷出版社',12,'2017-9-1'),
# ('降龙十巴掌','egon','知识产权没有用出版社',20,'2019-7-5'),
# ('葵花宝典','yuan','知识产权没有用出版社',33,'2019–8-2');
# 查询egon写的所有书和价格
# select bname,price from books where author='egon';
# 找出最贵的图书的价格
# select max(price) from books;
# 求所有图书的均价
# select avg(price) from books;
# 将所有图书按照出版日期排序
# select * from books order by pub_date;
# 查询alex写的所有书的平均价格
# select avg(price) from books where author = 'alex'
# 查询人民音乐不好听出版社出版的所有图书
# select * from books where press = '人民音乐不好听出版社';
# 查询人民音乐出版社出版的alex写的所有图书和价格
# select bname,price from books where press = '人民音乐不好听出版社' and author = 'alex'
# 找出出版图书均价最高的作者
# select author from books group by author order by avg(price) desc limit 1;
# select author,avg(price) as avg_price from books group by author order by avg_price desc limit 1;
# 找出最新出版的图书的作者和出版社
# select author,press from books order by pub_date desc limit 1
# 显示各出版社出版的所有图书
# select press,group_concat(bname) from books group by press;
# 查找价格最高的图书,并将它的价格修改为50元
# select max(price) from books; # 70
# update books set price=50 where price = 70;
# update books set price=50 order by price desc limit 1;
# update books set price=50 where price = (select * from (select max(price) from books)as t);
# 删除价格最低的那本书对应的数据
# select min(price) from books; # 5
# delete from books where price = 5;
# delete from books order by price limit 1;
# delete from books where price = (select * from (select min(price) from books)as t);
# 将所有alex写的书作者修改成alexsb
# update books set author = 'alexsb' where author = 'alex';
# select year(publish_date) from book
# month(publish_date)
# day(publish_date)
# delete from books where year(publish_date) = 2017;
免责声明:
① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。
② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341