首页 > 代码库 > mysql基础(一)

mysql基础(一)

mysql:  是用于管理文件的一个软件
-- 服务端软件
- socket 服务端
- 本地文件操作
- 解析指令(sql语句)
-- 客户端软件
- socket 客户端
- 发送指令
- 解析指令(sql语句)

ps DBMS 数据库管理系统
要学习的技能:
-- 安装 服务端和客户端
-- 连接
-- 学习sql语句规则,指示服务端做任意操作
其他类似软件:
关系型数据库:sqllite,db2,oracle,access,sql server
非关系型数据库:MongDB,redis

安装:
服务端:
初始化:E:\wupeiqi\mysql-5.7.16-winx64\mysql-5.7.16-winx64\bin\mysqld --initialize-insecure


启动:E:\wupeiqi\mysql-5.7.16-winx64\mysql-5.7.16-winx64\bin\mysqld\mysqld

客户端:
连接:E:\wupeiqi\mysql-5.7.16-winx64\mysql-5.7.16-winx64\bin\mysqld\mysql -u root -p

配置环境变量:

PATH:E:\wupeiqi\mysql-5.7.16-winx64\mysql-5.7.16-winx64\bin
启动:mysqld
window服务

添加服务:E:\wupeiqi\mysql-5.7.16-winx64\mysql-5.7.16-winx64\bin\mysqld --install

net start MySQL 启动mysql服务
net stop MySQL 停止mysql服务

删除服务:E:\wupeiqi\mysql-5.7.16-winx64\mysql-5.7.16-winx64\bin\mysqld --remove

添加window服务的作用: 添加到window服务之后,服务端会一直运行,不会随着命令行界面的关闭而关闭服务端
并且添加到window服务之后,不能再在命令行重新开启一个进程


数据库操作
数据库结构: 文件夹(数据库)
文件(表)
数据行(行)


显示数据库: show databases
默认数据库:mysql 用户权限相关数据
mysql数据库中user表: select user,host from user 存储所有用户信息
test 用于用户测试数据
information_schema mysql本身架构相关数据

创建数据库: create database db1 default charset utf8
使用数据库: use db1
显示当前数据库中所有的表 show tables
查看表中的所有数据 select * from 表名
用户管理

创建用户:create user ‘alex‘@‘192.168.1.%‘ identified by ‘123123‘;
删除用户:drop user ‘alex‘@‘%‘
修改用户:rename user ‘alex‘@‘%‘ to ‘egon‘@‘%‘
修改密码:set password for ‘alex‘@‘%‘=password(‘123‘)

授权:grant select,insert,update on db1.t1 to ‘alex‘@‘%‘;
解除授权:revoke all privileges on db1.t1 from ‘alex‘@‘%‘;
查看权限:show grants for ‘alex‘@‘%‘
刷新:flush privileges
mysql基于会话级别
查看全局变量:show session variables like ‘auto_cre%‘
设置会话自增步长 set session auto_increment_increment=2
设置自增初始值:set session auto_increment_offset=10;
mysql基于全局级别
查看全局变量:show global variables like ‘auto_cre%‘
设置会话自增步长 set global auto_increment_increment=2
设置自增初始值:set global auto_increment_offset=10;
对比SqlServer 自增步长的设置 基础表级别:
CREATE TABLE `t5` (
`nid` int(11) NOT NULL AUTO_INCREMENT,
`pid` int(11) NOT NULL,
`num` int(11) DEFAULT NULL,
PRIMARY KEY (`nid`,`pid`)
) ENGINE=InnoDB AUTO_INCREMENT=4, 步长=2 DEFAULT CHARSET=utf8

数据表操作
创建表:create table t1(
id int not null default 2 auto_increment primary key,
num decimal(10,5),
name char(10)
)engine=innodb default charset=utf8;

not null: 是否为空
innodb 支持事务,原子性操作:在服务突然中断的情况下,支持回滚,将失败的事务变为未进行前的状态
如果把一个事务可看作是一个程序,它要么完整的被执行,要么完全不执行。这种特性就叫原子性
myisam myisam
default 2 设置默认值
auto_increment 表示:自增
设置自增步长:alter table t1 auto_increment=20
primary key: 主键 表示 约束(不能重复且不能为空); 加速查找

主键:一个表只能有一个主键,主键可以由多列组成
create table t5(
nid int(11) not null auto_increment,
pid int(11) not null,
num int(11),
primary key(nid,pid)
)engine=innodb default charset=utf8;
外键:表的外键不一定是另一个表的主键,但一定是唯一索引列
create table department(
id bigint auto_increment primary key,
title char(15)
)engine=innodb default charset=utf8;

create table userinfo(
uid bigint auto_increment primary key,
name varchar(32),
department_id int,
xx_id bigint
constraint fk_user_depar foreign key (department_id) references department(id),
constraint fk_xx_ff foreign key ("xx_id",) references XX(‘id‘)
)engine=innodb default charset=utf8;

可以同时为表的两列设置外键,但是对应的外键也必须是设置为两列的主键(唯一索引项)
create table t6(
id int auto_increment primary key,
name char(10),
id1 int,
id2 int,
CONSTRAINT fk_t5_t6 foreign key (id1,id2) REFERENCES t1(nid,pid)
)engine=innodb default charset=utf8;
删除表:drop table t1
清空表: delect from t1 删除后插入行继续编号
truncate table t1 删除后插入行重新编号


修改表:添加列: alter table t1 add id int
删除列 alter table t1 drop id
修改列 alter table t1 modify column id int
alter table t1 change id sid int
添加主键:alter table t1 add primary key(id)
删除主键:alter table t1 drop primary key
添加外键:alter table 从表 add constraint 外键名称(形如:FK_从表_主表) foreign key 从表(外键字段) references 主表(主键字段);
删除外键:alter table 表名 drop foreign key 外键名称

修改默认值:ALTER TABLE tbl ALTER i SET DEFAULT 1000;
删除默认值:ALTER TABLE tbl ALTER i DROP DEFAULT;

基本数据类型: 数值 字符串 时间
整数: tinyint(m)
int(m)
bigint(m) m 用于数据显示
小数: float(m,d)
double(m,d) m是数字总个数,d是小数点后个数。
decimal(m,d)
准确的小数值,m是数字总个数(负号不算),d是小数点后个数。 m最大值为65,d最大值为30。

特别的:对于精确数值计算时需要用此类型
decaimal能够存储精确值的原因在于其内部按照字符串存储。

字符串:char(m)
char数据类型用于表示固定长度的字符串,可以包含最多达255个字符。其中m代表字符串的长度。
PS: 即使数据小于m长度,也会占用m长度
varchar(m)
varchars数据类型用于变长的字符串,可以包含最多达255个字符。其中m代表该数据类型所允许保存的字符串
最大长度,只要长度小于该最大值的字符串都可以被保存在该数据类型中。

注:虽然varchar使用起来较为灵活,但是从整个系统的性能角度来说,char数据类型的处理速度更快,有时甚
至可以超出varchar处理速度的50%。因此,用户在设计数据库时应当综合考虑各方面的因素,以求达到最佳的平衡
text
上传文件:文件存硬盘 db存路径

枚举:enum
CREATE TABLE shirts (
name VARCHAR(40),
size ENUM(‘x-small‘, ‘small‘, ‘medium‘, ‘large‘, ‘x-large‘)
);
INSERT INTO shirts (name, size) VALUES (‘dress shirt‘,‘large‘), (‘t-shirt‘,‘medium‘),(‘polo shirt‘,‘small‘);
集合:set
CREATE TABLE myset (col SET(‘a‘, ‘b‘, ‘c‘, ‘d‘));
INSERT INTO myset (col) VALUES (‘a,d‘), (‘d,a‘), (‘a,d,a‘), (‘a,d,d‘), (‘d,a,d‘);

时间:DATE
TIME
YEAR
DATETIME
TIMESTAMP
外键的变种:
1 一对多或多对一 外键的原生属性
用户表和部门表:
部门:
1 服务
2 保安
3 公关
用户:
1 alex 1
2 root 1
3 egon 2
4 laoyao 3
2 一对一
用户表和博客表:
用户表:
1 alex
2 root
3 egon
4 laoyao
博客表:(一个人只能有一个博客)
FK() + 唯一
1 /yuanchenqi/ 4
2 /alex3714/ 1
3 /asdfasdf/ 3
4 /ffffffff/ 2
create table userinfo1(
id int auto_increment primary key,
name char(10),
gender char(10),
email varchar(64)
)engine=innodb default charset=utf8;

create table admin(
id int not null auto_increment primary key,
username varchar(64) not null,
password VARCHAR(64) not null,
user_id int not null,
unique uq_u1 (user_id),
CONSTRAINT fk_admin_u1 FOREIGN key (user_id) REFERENCES userinfo1(id)
)engine=innodb default charset=utf8;
3 多对多
a 用户表 ----相亲记录表 (可以重复) 设置两个外键
b 用户表 主机表 用户主机关系表(不能重复)设置两个外键并且联合唯一
create table userinfo2(
id int auto_increment primary key,
name char(10),
gender char(10),
email varchar(64)
)engine=innodb default charset=utf8;

create table host(
id int auto_increment primary key,
hostname char(64)
)engine=innodb default charset=utf8;


create table user2host(
id int auto_increment primary key,
userid int not null,
hostid int not null,
unique uq_user_host (userid,hostid),
CONSTRAINT fk_u2h_user FOREIGN key (userid) REFERENCES userinfo2(id),
CONSTRAINT fk_u2h_host FOREIGN key (hostid) REFERENCES host(id)
)engine=innodb default charset=utf8;

文件内容操作(表内容操作)
:insert into t1(id,name) values(1,‘egon‘)
insert into t1(id,name) values(‘alex‘,12),(‘root‘,18);
insert into tb12(name,age) select name,age from tb11;
:delete from t1 where id = 1
delete from tb12 where id !=2
delete from tb12 where id =2
delete from tb12 where id > 2
delete from tb12 where id >=2
delete from tb12 where id >=2 or name=‘alex‘
:update t1 set age=18 where id =3
update tb12 set name=‘alex‘,age=19 where id>12 and name=‘xx‘
:select id from t1
select id,name as cname from tb12 where id > 10 or name =‘xxx‘;
其他
select * from t1 where id != 1
select * from t1 where id in (1,5,8)
select * from t1 where id not in (1,5,8)
select * from t1 where id not in (select id from t2)
select * from tb12 where id between 5 and 12;

通配符
select * from tb12 where name like "a%"
select * from tb12 where name like "a_"
分页
select * from tb12 limit 10;
select * from tb12 limit 0,10;
select * from tb12 limit 10,10;
select * from tb12 limit 20,10;
select * from tb12 limit 10 offset 20;
# page = input(‘请输入要查看的页码‘)
# page = int(page)
# (page-1) * 10
# select * from tb12 limit 0,10; 1
# select * from tb12 limit 10,10;2
排序
select * from tb12 order by id desc; 大到小
select * from tb12 order by id asc; 小到大
select * from tb12 order by age desc,id desc;

取后10条数据
select * from tb12 order by id desc limit 10;
分组
distinct 去重 效率不高
select count(id),max(id) from userinfo group by part_id;
聚合函数: count max min sum avg
对于聚合函数的结果进行二次筛选时,必须使用having
select count(id),part_id from userinfo5 group by part_id having count(id) > 1;

select count(id),part_id from userinfo5 where id > 0 group by part_id having count(id) > 1;
连表操作
上下连表:union (自动去重) 表的列数必须相同
select id,name from tb1
union
select num,sname from tb2
union all(不去重):select sid,sname from student
UNION ALL
select sid,sname from student

左右连表:

两个表进行连表操作是基于主外键关系,没有主外键关系不能进行连表
两个相同或不同的临时表具有相同的外键也可以进行连表

交叉连接:生成笛卡尔积,不适用任何匹配或者选取条件,而是直接将一个数据源中的每一行与另一个数据源的每一行都皮诶
select * from userinfo5,department5

select * from userinfo5,department5 where userinfo5.part_id = department5.id

左外连接:包含左边表的全部行(不管右边的表中是否存在与他们匹配的行),以及右边表中全部匹配的行
select * from department5 left join userinfo5 on userinfo5.part_id = department5.id

右外连接:包含右边表的全部行(不管左边的表中是否存在与他们匹配的行),以及左边表中全部匹配的行
# select * from userinfo5 right join department5 on userinfo5.part_id = department5.id

内连接:只连接匹配的行 将出现null时一行隐藏
select * from userinfo5 innder join department5 on userinfo5.part_id = department5.id

select score.sid,student.sid from score
left join student on score.student_id = student.sid
left join course on score.course_id = course.cid
left join class on student.class_id = class.cid
left join teacher on course.teacher_id=teacher.tid;


临时表应用补充
1 select * from (select * from student where id > 10) as B
2 select tid,tname,1,(select count(1) from student) from teacher
SELECT
student_id,
(select num from score as s2 where s2.student_id=s1.student_id and course_id = 1) as 语文,
(select num from score as s2 where s2.student_id=s1.student_id and course_id = 2) as 数学,
(select num from score as s2 where s2.student_id=s1.student_id and course_id = 3) as 英语
from score as s1;
3 select
course_id,
max(num),
min(num),
min(num)+1,
case when min(num) < 60 then 0 else min(num) end as c
from corse group by course_id

select
course_id,avg(num),
sum(case when num <60 THEN 0 ELSE 1 END),
sum(1),
sum(case when num <60 THEN 0 ELSE 1 END)/sum(1) as jgl
from score GROUP BY course_id order by AVG(num) asc,jgl desc;


4 条件: case when id>8 then 10 else 0 end
5 三元运算:if(isnull(xx),0,1)
 

mysql基础(一)