首页  思维导图  详情

mysql

2018-10-15 20:27:50   65  举报





AI智能生成

mysql基础

mysql

作者其他创作

大纲/内容

数据类型

整形

tinyint(1个字节)

最小值：有符号 -128，无符号0

最大值：有符号 127，无符号 255

smallint(2个字节)

最小值：有符号-32768，无符号0

最大值：有符号 32767，无符号 65535

mediumint(3个字节)

最小值：有符号 -8388608，无符号 0　

最大值：有符号 8838607，无符号 1677215

int(4个字节)

最小值：有符号 -2147483648，无符号 0

最大值：有符号 2147483647，无符号 4294967295

bigint(5个字节)

最小值：有符号 -2**63，无符号 0

最大值：有符号 2**63 -1，无符号2**64 -1

整形有符号和无符号

整型分无符号（unsigned）和有符号（signed）两种类型；
　　在默认情况下声明的整型变量都是有符号的类型（char有点特别），如果需声明无符号类型的话就需要在类型前加上unsigned。
　　无符号数只表示大小，有符号数最高位(二进制情况下最高位表示符号位)，在同一操作系统下，有符号数和无符号数的最大数值在大小上的关系是2x+1(x表示有符号数的最大值，2x+1表示无符号数最大值)。
　　二者表示的数据范围大小是相同的，但是范围不同。
　　无符号整型和有符号整型的区别就是无符号类型可以存放的正数范围比有符号整型中的范围大一倍，因为有符号类型将最高位储存符号，而无符号类型全都储存数字。

关于无符号整数的一些坑

总结下来的结论是：尽量不要使用UNSIGNED，因为可能会带来一些意想不到的效果。另外，对于INT类型可能存放不了的数据，INT UNSIGNED同样可能存放不了，与其如此，还不如在数据库设计阶段将INT类型提升为BIGINT类型。

定点数

decimal(M,D) (M+2字节)

最大取值范围与double相同，给定decimal的有效值范围由M和D决定

例子

decimal(M,D) M为精度，D为标度

decimal(6,2)数据长度为6，小数点保留2位

浮点数

float单精度（4字节）

负数取值范围：-3.402823466E+38～-1.175494351E-38

正数取之范围：0和1.175494351E-38～3.402823466E+38

double双精度（8字节）

负数取之范围：-1.7976931348623157E+308～-2.2250738585072014E-308

正数取值范围：0和2.2250738585072014E-308～1.7976931348623157E

浮点型选择规则

float也可以通过M,D设置一个宽度指示器和一个小数点指示器。

double类型要比float精度要高，如果需要精确到小数点10位以上，那么我们就用double类型，普通情况下float就够用了

在mysql中，定点数的精度要比浮点要高，而且，浮点数会出现误差，如果对数据的精度要求较高的话，应该选择定点数。

日期类型

date （年月日）（4字节）

最小值：1000-01-01

最大值：9999-12-31

datatime（年月日时分秒）（8字节）

最小值：1000-01-01 00:00:00

最大值：9999-12-31 23:59:59

timestamp（时间戳时间）（4字节）

timestamp默认是不可为空，当不给设置值时，会默认填充当前的时间戳

最小值：19700101080001　

最大值：20380119111407

time（时分秒）（3字节）

最小值：-838:59:59

最大值： 838:59:59

year （年）（1字节）

最小值：1901

最大值： 2155

字符串类型

char(m)

M的值为0-255之间个字符长度

varchar(m)

M为0-65535之间个字符长度

tinytext

允许长度为0-255字符长度

text

允许长度为0-65536字符长度

mediumtext

允许长度0-167772150字符长度

longtext

允许长度0-4294967295字符长度

char和varchar的区别

char是定长的，每条记录该字段的长度都是创建表时候规定的字段长度，插入速度较快

varchar是变长的，记录中该字段对应值的长度为字符实际长度+1

char(5)比如这行实际只用了3个字节，但是他还是占用5个字节的空间

varchar(5)比如这行实际值只用了3个字节，它就只占用4个字节的存储空间。

二进制类型

BINARY(M)

字节数为M，允许长度为0-M的定长二进制字符串

VARBINARY(M)

允许长度为0-M的变长二进制字符串，字节数为值的长度+1

TINYBLOB

可变长度二进制数据，最多255字节

BLOB

可变长度二进制数据，最多（2**16-1）个字节

MEDIUMBLOB

可变长度二进制数据，最多（2**24-1）个字节

LONGBLOB

可变长的而机智数据，最多（2**32-1）个字节

补充

binary(10)

varbinary(20)

都是存储普通二进制字符串型，两者的区别和char 和 varchar一样，一个占用实际字节，一个占用分配对固定字节。

text类型和blob类型很相似，text只能存储字符数据，纯文本之类的。选择text类型。

blob类型可以存储二进制数据，可以存储图片pdf等二进制数据。

枚举

enum

多个值中只能选取一个

元素个数取值范围：0～65535

集合

set

多个值中可选取多个

元素个数取值范围：0~64

示例

CREATE TABLE new_sxkj(
idx INT,
name varchar(60),
gender enum('man','women'),
hobby set('swim','football','song','dance'),
address varchar(200),
age TINYINT,
introduce text,
photo BLOB
);

字段约束

primary key 主键

定义

主键约束列不允许重复，即任意两行的主键值都不相同；每行都具有一个主键值，也不允许出现空值；

单列主键

创建方式1

CREATE TABLE student(
id INT PRIMARY KEY,
name VARCHAR(20)
)

创建方式2

CREATE TABLE student(
id INT NOT NULL,
name VARCHAR(20),
CONSTRAINT PRIMARY KEY(id)
//PK_STUD_ID为约束的名字
)

联合主键

有时候一个列的字段可能有重复，可以联合多个列设为主键。

CREATE TABLE student(
name VARCHAR(20),
class VARCHAR(20),
CONSTRAINT PRIMARY KEY(name,class)
)

其他操作

//删除主键约束
ALTER TABLE 表名 DROP PRIMARY KEY;

//添加主键
ALTER TABLE 表名 ADD PRIMARY KEY(列名);

//修改列为主键
ALTER TABLE 表名 MODIFY 列名数据类型 PRIMARY KEY;

foreign key 外键约束

定义

外键约束是保证一个或两个表之间的参照完整性,保持数据一致性。表的外键可以是另一表的主键, 外键可以有重复的, 可以是空值。实现一对一或一对多关系。

创建外键约束

创建主表

CREATE TABLE classes(
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(20)
);

创建外键1

//第一种：添加关键字
CREATE TABLE student(
id INT AUTO_INCREMENT,
name VARCHAR(20),
CONSTRAINT PK_ID PRIMARY KEY(id),
class_id INT REFERENCES classes(id)
)

unique 唯一约束

not null 非空约束

default 默认值

auto_increment 自动增长

约束的定义

关系型数据库有三大完整性

实体完整性：实体属性中的标识属性不能为空、不能重复，该约束通过制定的主键实现，其约束有系统强制实施

参照完整性：实体中的外键可以为空，但不能是错的。

用户定义完整性：某一具体的实际数据库的约束条件，由应用环境所决定，反映某一具体应用所涉及的数据必须满足的要求。(例如定义某一行不能为空)

约束主要完成对数据的检验和限制，从而保证数据库的完整性。

mysql语句执行顺序

用户及权限管理

创建用户

create user username@host identified by 'password';

授权

全部权限

grant all privileges on database.table to 'username'@'host';

增删改查权限

grant select,update,insert,delete on database.table to 'username'@'host'

不同host的授权

本地授权:grant all privileges on *.* to 'username'@'localhost';

网络授权:grant select privileges on db1.userinfo to 'username'@'60.191.%.%';

权限列表

ALTER: 修改表和索引。

CREATE: 创建数据库和表。

DELETE: 删除表中已有的记录。

DROP: 抛弃(删除)数据库和表。

INDEX: 创建或抛弃索引。

INSERT: 向表中插入新行。

SELECT: 检索表中的记录。

UPDATE: 修改现存表记录。

FILE: 读或写服务器上的文件。

PROCESS: 查看服务器中执行的线程信息或杀死线程。

RELOAD: 重载授权表或清空日志、主机缓存或表缓存。

SHUTDOWN: 关闭服务器。

ALL: 所有权限，ALL PRIVILEGES同义词。

USAGE: 特殊的 "无权限" 权限。

取消授权

revoke all privileges on db1.user_info from 'zhangsan'@'60.191.%.%';

删除用户

drop user 'zhangsan'@'60.191.%.%';

查看用户权限

show grants for 'zhangsan'@'%';

刷新权限

flush privileges;

查看用户有设置过权限的host

select user,host from mysql.user where user='username';

修改密码

root用户修改其他用户密码

set password for 'zhangsan'@'60.191.%.%'='new_pwd';

修改自己的密码

set password='new_pwd'

shell命令行修改密码

mysqladmin -uroot -p123456 password 123

修改user表

update user set password=password('123') where user='zhangsan' and host='localhost';

破解密码

mysqld --skip-grant-tables 模式启动mysql

mysql权限分配规则

1.一个用户在不同的host上可能有不同的权限

2.慎用 'username'@'%'，最好根据需要开放对应IP的数据库访问权限，而且数据库的端口最好也只针对指定ip开放

3.用户权限是存在mysql.user表中的，一个用户如果对应有不同的host，在表中也是对应的多条记录，所以说用户权限是根据'username'@'hosts'两个条件区分的（可以看作两个用户，因为也可以设置不用的密码），同用户不同的host之间是没有权限关联的。

4.用户账户包括 "username" 和 "host" 两部分，后者表示该用户被允许从何地接入。tom@'%' 表示任何地址，默认可以省略。还可以是 "tom@192.168.1.%"、"tom@%.abc.com" 等。数据库格式为 db.table，可以是 "test.*" 或 "*.*"，前者表示 test 数据库的所有表，后者表示所有数据库的所有表。

常用操作命令

查看当前登录用户

select current_user();

查看当前选择数据库

select database();

数据库操作

创建数据库

create database db2 charset utf8;

删除数据库

drop database db2;

查看所有数据库

show tables;

表操作

创建表

create table user_info3(idx int primary key auto_increment,name varchar(30) not null,age int,sex boolean);

删除表

drop table user_info2;

修改表

修改表名

alter table user_info3 rename new_userinfo;

修字段数据类型

alter table new_userinfo modify name char(20);

修改字段名

alter table new_userinfo change sex gender boolean;

在最后追加新的字段

alter table new_userinfo add address varchar(200) not null;

在指定字段后插入新字段

alter table new_userinfo add phone varchar(20) not null after age;

在表的第一个位置插入新的字段

alter table new_userinfo add uuid timestamp default now() first;

删除一个字段

alter table new_userinfo drop uuid;

更改表的存储引擎

alter table new_userinfo engine=MyISAM;

删除外键

alter table foriegn_tab drop foreign key c_fk

例子

create table student( num int(10) primary key, name varchar(20) not null, sex varchar(4) not null, birthday date, address varchar(50));

create table grade(id int(10) primary key auto_increment, course varchar(10) not null, s_num int(10) not null, grade varchar(4),constraint f_num foreign key(s_num) references student(num) on delete cascade on update cacade;

//给grande表的course字段的类型修改为varchar(20)
alter table grade modify course varchar(20);

//给grande表的s_sum字段前面新增一个字段page，该字段设置不为空
alter table grade add page varchar(10) not null after s_num;

//将grade表的grade字段改名为scroe
alter table grade change grade score varchar(4);

//删除表grade的外键 f_sum
alter table grade drop foreign key f_sum;

//修改grade的存储引擎为MyISAM
alter table grade engine=MyISAM;

//删除student表的address字段
alter table student drop address;

//给student添加一个phone字段，类型为varchar(10)
alter table student add phone int(10);

//将表grade的名字改为gradeinfo
alter table grade rename gradenfo;

//删除student表
drop table student;

查看当前库下所有的表

show tables;

查看表的字段信息

desc tablename;

查看表的创建信息

show create table tablebname;

数据操作

增

insert into test (id,email,ip,state) values(2,'qq@qq.com','127.0.0.1','0');

删

delete from

delete FROM users WHERE name = 'nike' ORDER BY id DESC LIMIT 6;