mysql建表后设置字符集_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、创建库必须要设置字符集。

#创建数据库

createdatabasememlcharsetutf8mb4;

#查看数据库

showdatabases;

#删除数据库

dropdatabasememl;

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

showccreatedatabasememl;#查看字符集

alterdatabasememlcharsetutf8mb4;

表定义

#创建表(图形化)

Datatype：字符集

Len：长度

Default：默认值

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

Unsigned：不允许负数

Autoincrease：自动增长

Zerofill：补零

Comment：注释

#创建表(命令)

表属性

语法规范：

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

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

3、 每一个列设置notnull+default

4、 每个列要加上注释(comment)

5、 设置好存储引擎以及字符集(engineinnodbcharsetutf8mb4)

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

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

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

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

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

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

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

comment：注释

auto_increment：自增长

#创建表

createtablestu(

idintprimary keynotnullauto_incrementcomment '学号',

namevarchar(255) not null  comment '姓名',

genderenum('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 modifyqqvarchar(64) not null comment '姓名';

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

alter table stu  changeqqweixin char(4) not null comment '微信';

DCL语句

就是授权(下面有----grantrevoke)

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*fromstuwhereid=1;

可以是=> <

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

select*fromstuwhereid>3andsex='男';

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

select*fromstuwhereid>3orage=13;

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

select district, sum(population) from  city

where countrycode='chn'

select配合like模糊查询

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

select*fromstuwhere name like 'z%'

select配合groupby+聚合函数实现统计(重点)

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

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 countrycodehavingsun(population)>10000;

select配合orderby进行排序(默认从小到大 从大到小后面加desc)

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

select * from student  order by age;

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

select * from student  order by agedesc;

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表1join表2on表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.%';