mysql笔记

数据库产品

RDBMS

 

特点：

1、二维表.有严格的行列结构及属性约束

2、支持更加复杂的查询

产品：

Mysql:

mariadb、perconaDB

oracle

PG

NOSQL

 

特点：

1、没有严格的行列结构

2、可伸缩性可扩展性，性能产品

产品：

Kv：

Redis，Memcache

document:

MongoDB

搜索

ES（多用做全文搜索）

wide

Cassandra

NewSQL(R+N+分布式)

 

产品：

spaner

TiDB

polarDB

 

 

Mysql理论

 

数据库表

#这是我安装时候定义的数据库的数据存储位置。

cd /data/mysql/data

Innodb表格式

 

xxx.frm : 存储表的结构（列、列属性）

xxx.idb : 存储表的数据记录和索引

ibdata1：数据字典信息

 

 

Innodb段区页

 

一般情况下（非分区表）一个表就是一个段

一个段由多个区构成

一个区在（16k），64个连续的页，1M大小

 

 

 

 

 

Mysql的配置文件

 

服务器端配置

 

[mysqld]

#用户

user=mysql   

#软件安装目录                

basedir=/application/mysql  

#数据路径

datadir=/data/mysql/data

#socket文件位置、类似mysql登录协议，本地登录自动找sock

socket=/tmp/mysql.sock

#服务器id号

server_id=3

#端口号

port=3306

客户端配置

 

[mysql]

#socket文件位置、类似mysql登录协议，本地登录自动找sock

socket=/tmp/mysql.sock

mysql配置文件读取顺序（一般都会在/ect/my.conf）

 

#若每个文件都有。则会以最后那个为准

[root@db01 ~]# mysqld --help --verbose |grep my.cnf

/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf

强制读取某配置文件

--defautls-file

[root@db01 tmp]# mysqld_safe --defaults-file=/tmp/aa.txt &

 

 

 

 

Mysql常用内置命令 

 

help 打印mysql帮助

\c  ctrl+c 结束上个命令运行

\q quit; exit; ctrl+d 退出mysql

 

\G 将数据竖起来显示（一般是横着）

 

 

source 恢复备份文件

source /root/hehe,sql

 

常用的mysql的数据类型

 

数字

 

整数：int（范围：正负2的31次方）、tinyint（范围：0-255）

 

浮点数基本不用。

字符串

 

char 固定100个字符长度，未占满的采用空格填充

 

varchar 可变长度，按需分配空间，超过255长度就会多两个字符串占用（用的比较多）

 

enum 枚举类型（就是可以自己定义某某字母或者数字什么的，然后插入表时只能从其中选择想要的。）

 

时间

 

datetime 年月日分十秒

 

DDL语句

 

数据库的定义

 

语法规范：

1、数据库名字要小写。

2、创建库必须要设置字符集。

 

#创建数据库

create database meml charset utf8mb4;

#查看数据库

show databases;

#删除数据库

drop database meml;

#修改数据库字符集（只能从小往大改，如本身是utf8改为utf8mb4）

show ccreate database meml; #查看字符集

alter database meml charset utf8mb4;

 

表定义

 

#创建表（图形化）

 

 

Datatype：字符集

Len：长度

Default：默认值

PK：列的值，不能重复，不能不填

Unsigned：不允许负数

Auto increase：自动增长

Zerofill：补零

Comment：注释

 

 

 

#创建表（命令）

 

表属性

 

语法规范：

1、 表名小写字母，不能数字开头、保留字符

2、 选择合适的数据类型及长度

3、 每一个列设置not null + default

4、 每个列要加上注释（comment）

5、 设置好存储引擎以及字符集（engine innodb charset utf8mb4）

6、 主键尽量无关数字列，最好自增长

7、 Enum类型不要保存数字，只用字符串

 

primary key：主键约束，表中只能有一个，非空且唯一

not null：非空约束，不允许空值

unioue key：唯一键约束，不允许重复值

default：一般配合 NOT NULL一起使用

unsigned：无符号，一般是配合数字列，非负数

comment：注释

auto_increment ：自增长

#创建表

create table stu (

id int primary key not null auto_increment comment '学号',

name varchar(255) not null  comment '姓名',

gender enum('M ', 'F ') comment '性别'

);

 

#修改表

在stu表中添加qq列

desc stu;

alter table stu add qq varchar(20) not null comment 'qq号';

 

删除stu表的qq列

alter table stu drop qq;

 

修改qq属性

alter table stu modify qq varchar(64) not null comment '姓名';

 

修改stu表的qq列改为weixin修改属性类型

alter table stu  change qq weixin char(4) not null comment '微信';

 

DCL语句

就是授权（下面有----grant revoke）

DML语句

 

update

delete

 

插入一张表内容：

insert into stu stu values(1,'zs',18,'m');

规范写法：

insert into stu(id,sname,age,sex)

values (2,'ls',19,'f');

 

update(一定要加where条件)

update stu set sname='bb' where id=6;

 

delete (一定要有where条件)

delete from stu where id=9;

 

select语句

 

select 配合from

 

语法：select 列或* from 表

 

select id,name from stu;

select * from stu;

 

select配合where（过滤条件）

 

语法：select 列或* from 表 where 条件

 

select * from stu where id=1;

 可以是=  > <

查询stu表中id大于3且sex性别为男的数据

select * from stu where id>3 and sex='男' ;

 

查询stu表中id大于3或age年龄等于13的数据

select * from stu where id>3 or age=13 ;

 

统计中国 每个省的总人口数

select district, sum(population) from  city

where countrycode='chn'

 

select配合like模糊查询

 

查询stu表中name字段中z开头的所有数据，前面不要加百分号，影响效率

select * from stu where name like 'z%'

select配合group by + 聚合函数实现统计（重点）

 

 

统计每个国家,城市的个数

select  countrycode ,count(id) from city 

group by countrycode;

 

统计每个国家的总人口数.

select countrycode,sum(population) from city

group by countrycode;

 

 

select配合having统计并过滤

 

统计每个国家的总人口数，且把大于1万的人数过滤出来.

select countrycode,sum(population) from city

group by countrycode having sun(population)>10000;

 

select配合order by进行排序（默认从小到大 从大到小后面加desc）

 

查询student表并将年龄按从小到大排序

select * from student  order by age;

 

查询student表并将年龄按从大到小排序

select * from student  order by age desc;

 

select配合limit限制显示内容

 

查询student表只显示前3行

select * from student  limit 3;

 

查询student表跳过2行，一共显示3行

select * from student  limit 2,3;

 

union 和 union all

作用: 多个结果集合并查询的功能

 

需求: 查询中或者美国的城市信息

select * from city where countrycode='chn' or countrycode='usa';

 

面试题: union 和 union all 的区别 ?

union all   不做去重复

union 会做去重操作

 

多表查询

 

多表查询用法

 

语法：

select 表1.字段1 , 表2.字段1 from 表1  join  表2 on 表1.字段2 = 表2.字段2（on这里比较重要，要求两表之间有相关联的词。）

 

举例：

 

吃饭是两表的关联词。

下面语句中找出两表之中都含有吃饭的的名字：

select xs.mingzi,users.userName from users join xs on users.interests = xs.xingqv;

 

后面还可以加where等等

 

密码

 

无密码初始化               安装包目录        登录、日志目录（自行创建）

mysqld --initialize-insecure --user=mysql --basedir= /mysql --datadir=/data/mysql/data

 

 

密码修改

 

Mysql第一次启动时候修改密码（会报警告说密码显示在屏幕不安全）

mysqladmin -uroot -p password 123

 

如果再次想修改的话

mysqladmin -uroot -p password 新密码

Enter password: 旧密码

 

Mysql忘记密码（停止掉mysql服务）：

--skip-grant-tables  #跳过授权表

--skip-networking    #跳过远程登录

mysqld_safe --skip-grant-tables --skip-networking &

mysql  #进入数据库

mysql> flush privileges; #刷新授权表

mysql> grant all on *.* to root@'localhost' identified by '123'; #修改密码

去重启服务。然后就可以用新密码了

用户操作

 

创建有、用户

 

create user 'meml'@'192.168.1.%' identified by '123'; #创建一个meml的用户

 

8.0以前，可以自动创建用户并授权

grant all on *.* to meml@'192.168.1.%' identified by '123';

删除用户

 

drop user meml@'192.168.1.%'; #删除一个meml的用户

查询用户

 

select user,host from mysql.user;

修改用户密码

 

alter user 'meml'@'192.168.1.%' identified by '123';  

权限管理

 

授予权限

 

创建一个用户meml,可以通过192.168.1.0这个网段对数据库进行管理。

grant all on *.* to 'meml'@'192.168.1.%' identified by '123' with grant option;

 

创建一个用户wordpress,可以对wordpress这个库中进行查询、插入、删除、更新

grant select,insert,delete,update on wordpress.* to wordpress@'192.168.1.%' identified by '123';

关于授权

 

权限：

with grant option #写最后代表是允许这个用户有授权权限

all #所有

select,insert,delete,update ：查、增、删、改

允许地址访问规则：

wordpress0'%'

wordpress@' localhost'

wordpress0'127.0.0.1'

wordpress0'10.0.0.%'

wordpress0'10.0.0.5%'  #50-9

wordpress0'10.0.0.0/255.255.254.0 #23位掩码

wordpress0'10.0.%'

作用目标：

Wordpress.* #作用于wordpress这个库下的所有表

Wordpress.hehe #用于wordpress这个库下的hehe表

*.* #作用所有库所有表

 

查询权限

 

查询wordpress这个用户所拥有的权限

show grants for 'wordpress'@'192.168.1.%';

回收权限

 

把wordpress用户对wordpress这个库的所有权限回收

revoke all  on wordpress.* from 'wordpress'@'192.168.1.%';  

 

把wordpress用户对wordpress这个库的delete权限回收

revoke delete  on wordpress.* from 'wordpress'@'192.168.1.%';