001---mysql

Mysql数据库

数据库相关概念

数据库服务器：运行数据管理软件的计算机
数据库：顾名思义数据仓库，是一个文件夹、存储多个文件(数据表)
数据表：对应一个文件，存储在数据库下
数据：对应文件中的每一行，描述事物特征的符号
数据库管理软件
- 关系型：需要有表结构(mysql,sqlserver,db2,oracle)
- 非关系型：key-value结构(redis,mongodb)

用户相关

win

安装为系统服务：mysqld --install
改密码：mysqladmin -uroot -proot password "root"
登录：mysql -uroot -proot
启动：net start mysql56
停止：net stop mysql56
破解密码：mysqld --skip-grant-tables
更新密码：update mysql.user set password=password("密码") where user="root" and host="localhost";
刷新：flush privileges;
杀：taskkill /F /PID ....
查看登陆用户：select user();

linux

破解：mysql_safe --skip-grant-tables

统一字符编码

查看字符编码

统一字符编码

# 修改配置文件
[mysqld]
default-character-set=utf8 
[client]
default-character-set=utf8 
[mysql]
default-character-set=utf8

#mysql5.5以上：修改方式有所改动
[mysqld]
character-set-server=utf8
collation-server=utf8_general_ci
[client]
default-character-set=utf8
[mysql]
default-character-set=utf8
# 这样在控制台输入mysql可以直接登录
user='root'
password='root'

初识sql语句

操作文件夹(库)

切换文件夹：use db1;
增加：create database db1 charset utf8;
查看：
- 建库语句：show create database db1;
- 所有数据库：show databases;
- 查看当前所在数据库：select database();
修改：
- 修改字符编码：alter database db1 charset gbk;
删除：drop database db1;

操作文件(表)

增：create table t1(id int,name char) engine=innodb;
删：
- 删除：drop table t1;
- 清空表结构：truncate table t1;
改：
- 修改表字段类型：alter table t1 modify name char(6);
- 修改表字段名称：alter table t1 change name Name varchar(8);
查：
- 查看建表语句：show create table t1;
- 查看所有表：show tables;
- 查看表结构：desc t1;

操作文件内容(记录)

增：insert into t1(id,name) values(1,'egon'),(2,'egon'),(3,'alex');
删：
- delete from t1; 自增长并没有改变，不是1，所以尽量与where搭配。
- delete from t1 where id=1;
改：
- update t1 set name='sb';
- update t1 set name='alex' where id=2;
查：
- select * from t1;
- select id,name from t1;

sql语言类型

DDL语句：数据库定义语言，create drop alter
DML语句：数据库操作语言，insert update delete select
DCL语句：数据库控制语言，grant revoke

存储引擎

什么是存储引擎

现实生活中我们用来存储数据的文件有不同的类型，每种文件类型对应各自不同的处理机制：比如处理文本用txt类型，处理表格用excel，处理图片用png等

数据库中的表也应该有不同的类型，表的类型不同，会对应mysql不同的存取机制，表类型又称为存储引擎。

存储引擎说白了就是如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方法。因为在关系数据库中数据的存储是以表的形式存储的，所以存储引擎也可以称为表类型（即存储和操作此表的类型）

查看存储引擎

show engines;

表操作

创建

create table 表名(
字段名1 类型[(宽度) 约束条件],
字段名2 类型[(宽度) 约束条件],
字段名3 类型[(宽度) 约束条件]
);

注意：

在同一张表中，字段名是不能相同
宽度和约束条件可选
字段名和类型是必须的

查看表结构

简单：desc table1
详细：show create table table1

修改表结构

修改表名： ALTER TABLE 表名 RENAME 新表名;

增加字段

  ALTER TABLE 表名  
		ADD 字段名  数据类型 [完整性约束条件…],
		ADD 字段名  数据类型 [完整性约束条件…];

ALTER TABLE 表名
		ADD 字段名  数据类型 [完整性约束条件…]  FIRST;

  ALTER TABLE 表名
  		ADD 字段名  数据类型 [完整性约束条件…]  AFTER 字段名;

删除字段：ALTER TABLE 表名 DROP 字段名;

修改字段

ALTER TABLE 表名
        	MODIFY  字段名 数据类型 [完整性约束条件…];
ALTER TABLE 表名
    		CHANGE 旧字段名 新字段名 旧数据类型 [完整性约束条件…];
ALTER TABLE 表名
    		CHANGE 旧字段名 新字段名 新数据类型 [完整性约束条件…];

表的复制

复制表结构 + 记录：create tale t1 select host,user from mysql.user;

复制表结构：

-- 条件不成立，空记录
create table t2 select host,user from mysql.user where 1>5;
create table t3 like mysql.user;

删除表

drop table t3;

数据类型

数字类型

int 4字节
float(m,d):
- m是数字总个数最大255
- d是小数点后的个数最大30
decimal(m,d):
- m是数字总个数最大65
- d是小数点后的个数最大30
- 精度最高

日期类型

create table student(
    id int,
    name char(6),
    born_year year,
    birth_date date,
    class_time time,
    reg_time datetime
);
insert into student values(1,'egon',now(),now(),now(),now());
insert into student values(2,'alex','1997','1996-10-19','12:12:12','2018-09-13 12:12:12');

datetime与timestamp的区别

DATETIME的日期范围是1001——9999年，TIMESTAMP的时间范围是1970——2038年。
8字节 4字节
不受时区影响受时期影响

'''

字符串类型

'''

char定长(0-255)：简单粗暴，浪费空间(以空格来填充)，存取速度快
varchar变长(0-65535)：省内存，但是查询速度慢

'''

枚举和集合类型

create table consumer(
    id int,
    name char(4),
    sex enum('male','female','other'),
    level enum('vip1','vip2','svip'),
    hobbies set('game','music','run')
);

insert into consumer values(1,'egon','male','svip','music,game');
-- 不在范围内就为空
insert into consumer values(1,'egon','xx','svip','music,game');

enum：单选
set：多选

完整性约束

not null与default

create table t1(
    id int not null ,
    name char(4),
    sex enum('male','female') not null default 'male'
);
insert into t1(id,name) values('1','alex');

不为空和默认值

unique

唯一约束

单列唯一

create table dpt(
  id int,
  name char(10) unique
);

insert into dpt values(1,"IT");
insert into dpt values(2,"IT");

-- 方式一
create table dpt(
id int unique,
name char(10) unique
);
-- 方式二
create table dpt(
id int,
name char(10)，
unique(id),
unique(name)
);

联合唯一

--ip和端口联合唯一
create table services(
    id int ,
    ip char(15),
    port int,
    unique(ip,port),
    unique(id)
);

insert into services values
(1,'127.0.0.1',80),
(2,'127.0.0.1',81),
(3,'118.25.126.94',80);

primary_key

主键

不为空、且唯一
必须设定一个主键
对于innodb存储引擎来说：一张表只能有一个主键
加快了查询速度

单列主键

create table t1(
    id int primary key,
    name char(16),
);

复合主键

create table services1(
    ip char(15),
    port int,
    primary key(ip,port)
);

auto_increment

自增

create table t3(
    id int primary key auto_increment,
    name char(4)
);

insert into t3(name) values
('egon'),
('alex'),
('wpq');

insert into t3(id,name) values('5','jw')

查看：show variables like 'auto_inc%';

步长：auto_increment_increment

起始偏移量：auto_increment_offset

设置步长：

会话级别：set session auto_increment_increment=3;
全局级别：set gloable auto_increment_increment=3;

设置偏移量：

set gloable auto_increment_offset=3;

注意

起始偏移量<=步长
delete from student;：清空表，但是自增按照删除前的位置继续自增
truncate table t1;：清空表，且自增也初始化。

foreign_key

用来建立表与表之间的关系

-- 先创建dep表
create table dep(
    id int primary key auto_increment,
    name char(16),
    comment char(50)
);

-- 插入数据
insert into dep(name,comment) values('CEO','一群吹牛的部门'),
('IT','技术一般的部门'),
('财务','花钱特别多的部门'),
('销售','不会卖东西的部门');

-- 再创建关联的emp表
 create table emp(
 id int primary key,
 name char(10),
 sex enum('male','female'),
 dep_id int,
 constraint fk_dep foreign key(dep_id) references dep(id) 
 on delete cascade 
 on update cascade
 )

-- 插入数据
insert into emp(name,sex,dep_id) values
('alex','male',1),
('egon','male',2),
('yuanhao','female',3),
('wupeiqi','male',4);

数据操作

插入

1. 插入完整数据（顺序插入）
    语法一：
    INSERT INTO 表名(字段1,字段2,字段3…字段n) VALUES(值1,值2,值3…值n);

    语法二：
    INSERT INTO 表名 VALUES (值1,值2,值3…值n);

2. 指定字段插入数据
    语法：
    INSERT INTO 表名(字段1,字段2,字段3…) VALUES (值1,值2,值3…);

3. 插入多条记录
    语法：
    INSERT INTO 表名 VALUES
        (值1,值2,值3…值n),
        (值1,值2,值3…值n),
        (值1,值2,值3…值n);
        
4. 插入查询结果
    语法：
    INSERT INTO 表名(字段1,字段2,字段3…字段n) 
                    SELECT (字段1,字段2,字段3…字段n) FROM 表2
                    WHERE …;

更新

语法：
    UPDATE 表名 SET
        字段1=值1,
        字段2=值2,
        WHERE CONDITION;

示例：
    UPDATE mysql.user SET password=password(‘123’) 
        where user=’root’ and host=’localhost’;

删除

语法：
    DELETE FROM 表名 
        WHERE CONITION;

示例：
    DELETE FROM mysql.user 
        WHERE password=’’;

查询

单表查询

--单表查询

-- select distanct 字段1,字段2,字段3 from 库.表 where 条件 group by 分组条件 having 过滤 order by 排序字段 limit n;
-- 优先级  where group distinct order by


-- 创建表
create table emp(
    id int primary key auto_increment,
    name varchar(20) not null,
    sex enum('male','female') not null default 'male',
    age int not null default 21,
    hire_date date not null,
    post varchar(50),
    post_comment varchar(100),
    salary double(15,2),
    office int, 
    depart_id int
);

-- 插入记录
-- 三个部门：教学，销售，运营
insert into 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);

-- 查询

-- 字段查询
select id,name from emp;

-- 去重
select distinct post from emp;

-- 别名
select name,salary*12 as 年薪 from emp;

-- 字符串拼接
select concat('<姓名:',name ,'>',' <性别:',sex,'>') as info from emp;

-- where条件查询
select id,name,age from emp where id>7;

select name from emp where post='teacher' and salary > 8000;

select name,salary from emp where salary between 20000 and 30000;

select name,salary from emp where salary in (20000,30000);

select name,salary from emp where salary not in (20000,30000);
-- 不能用等于
select name from emp where post_comment is NULL ; 

-- 模糊匹配
-- 任意字符
select name from emp where name like "jin%";    
-- 一个字符
select name from emp where name like "jin___";  

--group by 分组查询
select sex from emp group by sex; -- 只能查分组的字段,以及聚合
select sex,count(sex) from emp group by sex; -- 统计男女性别数目
select post,count(post) from emp group by post;-- 每个职位有多少员工
select post,avg(salary) from emp group by post;
select post,group_concat(name) from emp group by post;-- 每个职位下所有员工的姓名

--having 分组后过滤  可以使用聚合函数过滤

--查询各岗位包含的员工个数小于2的岗位名，岗位内包含的员工名字、个数
select post,group_concat(name),count(post) from emp group by post having count(id)<2;
--查询各岗位平均薪资大于1万的岗位名、平均工资
select post,avg(salary) from emp group by post having avg(salary)>10000;
--查询各岗位平均薪资大于1万且小于2万的岗位名、平均工资
select post,avg(salary) from emp group by post having avg(salary) between 10000 and 20000;

--order by排序
select id,age from emp order by age asc;   --年龄从小到大升序
select id,age from emp order by age desc;   --年龄从小到大降序
select id,age from emp order by age desc ,id asc;   --年龄从小到大降序,id升序

-- limit n分页
select * from emp limit 3;
-- 查询工资最高的那个人的信息
select * from emp order by salary desc limit 1;--第一条
select * from emp limit 0,5;  --取五条数据 ，从1开始
-- 注意：并不是那么简单，性能问题


--总结：
-- 语法顺序：
-- 执行上顺序：
        --from 表--where条件--group分组--hanving过滤--distinct--order by排序--limit 分页--

-- 正则
select * from emp where name regexp '^jin.*g$';
select * from emp where name regexp '^jin.*(g|n)$';

连表查询

-- 建表
create table dep(
id int,
name varchar(20)
);

create table emp(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') not null default 'male',
age int,
dep_id int
);

-- 插入数据
insert into dep values
(200,'技术'),
(201,'人力资源'),
(202,'销售'),
(203,'运营');

insert into emp(name,sex,age,dep_id) values
('egon','male',18,200),
('alex','female',48,201),
('wupeiqi','male',38,201),
('yuanhao','female',28,202),
('liwenzhou','male',18,200),
('jingliyang','female',18,204)
;

--内连接：两个表的共同部分
select * from dep d inner join  emp e on e.dep_id=d.id;

--左连接：保留左表，右表没有的以null填充
select * from dep d left join  emp e on e.dep_id=d.id;

--右连接：保留右表，左表没有的以null填充
select * from dep d right join  emp e on e.dep_id=d.id;

--全连接
select * from dep d left join  emp e on e.dep_id=d.id
union
select * from dep d right join  emp e on e.dep_id=d.id;

--查询平均年龄大于30岁的员工的部门名
select d.name,avg(age) from emp e inner join dep d on d.id=e.dep_id group by d.name having avg(age)>30;

-- {
--   "server":"0.0.0.0",
--   "port_password":{
--     "8383":"wgx123456",
--     "8384":"jw123456"
--     },
--   "timeout":300,
--   "method":"aes-256-cfb",
--   "fast_open":false
--
-- }

权限管理

视图

虚拟表，是已经编译好的SQL语句，是基于执行SQL语句的结果集的可视化的表
视图的建立和删除只影响视图本身，不影响对应的表。

触发器

触发器（trigger）是提供给程序员和数据分析员来保证数据完整性的一种方法，是一种特殊类型的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发。

类型

事后触发器（表）：增删改查自动执行，调用一个或多个
替代触发器（视图）：不止应用到表上，不会先执行增删改查，只会执行自己本身的代码。

事务

事务用于将某些操作的多个SQL作为原子性操作，一旦有某一个出现错误，即可回滚到原来的状态，从而保证数据库数据完整性。

存储过程

存储在服务器，一组tsql语句。通过调用它的名字可以执行其内部的一堆sql

优点

提高程序的通用性和可移植性
提高数据库的运行速度
减轻网络带宽，减少网络流量
安全性高,可设定只有某些用户才具有对指定存储过程的使用权

索引

加速查询速率

常用索引

普通索引INDEX：加速查找
唯一索引：
- 主键索引PRIMARY KEY：加速查找+约束（不为空、不能重复）
- 唯一索引UNIQUE:加速查找+ 约束（可为空，不能重复）
联合索引：
- PRIMARY KEY(id,name):联合主键索引
- UNIQUE(id,name):联合唯一索引
- INDEX(id,name):联合普通索引

posted @ 2019-02-18 09:12 爬呀爬Xjm 阅读(126) 评论(0) 编辑收藏举报

刷新页面返回顶部

爬呀爬Xjm

001---mysql

Mysql数据库

数据库相关概念

用户相关

win

linux

统一字符编码

查看字符编码

统一字符编码

初识sql语句

操作文件夹(库)

操作文件(表)

操作文件内容(记录)

sql语言类型

存储引擎

什么是存储引擎

查看存储引擎

表操作

创建

查看表结构

修改表结构

表的复制

删除表

数据类型

数字类型

日期类型

字符串类型

枚举和集合类型

完整性约束

not null与default

unique

primary_key

auto_increment

foreign_key

数据操作

插入

更新

删除

查询

权限管理

视图

触发器

类型

事务

存储过程

索引

常用索引

公告