mysql基础知识汇总

有关MySQL的知识点总结

1.相关概念

DB

数据库(database)：存储数据的"仓库"，它保存了一系列有组织的数据。

DBMS

数据库管理系统(Database Management System).数据库是通过DBMS创建和操作的容器。

SQL

结构化查询语言(Structure Query Language):专门用来与数据库通信的语言。

常见的数据库管理系统：Mysql Oracle  DB2 sqlServer等。

Mysql是关系型数据库(c/s),是一种开放资源代码的关系型数据管理系统，适用于多种平台。

关系型数据库的特点：

    1.实现数据共享，减少数据冗余

    2.采用特定的数据类型

    3.具有较高的数据独立性

    4.具有统一的数据控制功能

sql语句：

    DDL:数据库定义语言，用于库和表的创建(create)、修改、删除 。

    DQL:数据库查询语句 select(查询)

    DML:数据操纵语句，用于添 加、删除、修改、查询数据库记录，并检查数据完整性 ，insert(插入/添加) update(修改),delete(删除)

    DCL:数据控制语句，用于定义用 户的访问权限和安全级别。用于对数据库对象操作的权限。如grant revoke commit rollback等语句

相关知识、操作介绍：

​
​

-- 连接mysql
mysql -uroot -p密码;

-- 退出
exit; 或 /q(Queit)
/**数据库操作 **/
-- 查看当前数据库
  select database();
-- 查看已有的库
  show databases;

  show databases like '匹配模式';(模糊查询)
-- 创建库
  create database [ if not exists] 数据库名 数据库选项

-- 删除库
  drop database [ if exists ] 数据库名
  同时删除该数据库箱管的目录及目录的内容
//表的操作
-- 创建表
  create table [ if not exists] [库名.]表名 (表的结构定义)[表选项]
    
   每个字段必须有数据类型，最后一个字段后不能有逗号
对于字段的定义：
  字段名 数据类型 [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY] [COMMENT 'string']
如：
    create table people(
        id int not null primary key auto_increment,指定为主键且自增
        name varchar(10),
        sex char(1),
        age smallint,
        tid int(10),
        foreign key (tid) reference 父表(主键名tid) //tid的类型,显示宽度要一致
   );

//表选项
-- 字符集
CHARSET = charset_name  如果表没有设定，则使用数据库字符集
-- 存储引擎
ENGINE = engine_name
  表在管理数据的时候采用不同的数据结构，结构不同会导致处理方式，提供的特性操作等不同，
常见的引擎：InnoDB  MyISAM Memory/Heap CSV Mrg_Myisam  Blackhole(黑洞引擎)
-- 显示存储引擎的状态信息
  show engines
-- 显示存储引擎的日志或状态信息
  show engines 引擎名 {logs | status}
-- 表注释
  comment = 'string'
-- 查看所有表
  show tables [ like 'pattern' ]
  show tables from 表名
-- 查看表结构
  show create table 表名
  desc 表名 / show columns from 表名; / describe 表名
-- 修改表
  -- 修改表本身的选项
    alter table 表名 表的选项
    alter table 表名 engine=MyISAM;
  -- 修改表名：rename
    alter table 旧表名 rename 新表名;
 -- 修改字段数据类型：modify
    alter table 表名 modify 字段名 新数据类型 [约束];
 -- 设置非空约束：
    alter table 表名 modify 字段名 数据类型 not null;
    alter table 表名 modify age int(10) not null;
    注意：设置非空约束时，已经存在数据的字段是null值的情况下是无法设置成功的。
 -- 新增外键约束：
    alter table 表名 add foreign key (外键列) reference 父表(父表主键列);
 -- 新增唯一约束：
    alter table 表名 add unique(字段名);
 -- 新增主键：
    alter table 表名 add primary key(字段名);
    主键设有自增无法删除，先把自增设为0，再删。
    alter table 表名 auto_increment=0;在进行删除
 -- 删除主键约束：
    alter table 表名 drop primary key;
 -- 删除外键约束：
     alter table 表名 drop foreign key(约束名);
     外键名在外键设置时constraint关键字之后的参数，如果没有设置外键名，系统会有默认的外键名，
     通过建表语句(show create table 表名;) 查询。
 -- 删除唯一约束：
     alter table 表名 drop index 字段名;
 -- 设置默认约束：
     alter table 表名 alter 字段 set default 默认值;
 -- 删除默认值：
     alter table 表名 alter 字段 drop default;
 -- 修改字段排序位置：	
     alter table 表名 modify 字段一 数据类型 first;
     alter table 表名 modify 字段一 数据类型 after 字段二;
 -- 修改字段名和数据类型：change
     alter table 表名 change 旧字段名 新字段名 数据类型;
     alter table 表名 change phone newPhone int(11);
 -- 添加字段：add
    alter table 表名 add 新字段名 数据类型[first/after];
    alter table 表名 add address varchar(50) first;可以指定添加的位置。
    first:将新的字段设置顺序为第一个
    after:将新的字段添加到指定字段之后
    alter table 表名 add weight float(5,2) after sex;
 -- 删除字段：drop
    alter table 表名 drop 字段名;
    alter table 表名 drop weight;
 -- 更改表的储存引擎：
    使用show engines;查看系统支持的存储引擎
    alter table 表名 engine=更改后的存储引擎名;
 -- 清空表数据
     truncate [table] 表名;
-- 复制表结构
    create table 表名 like 要复制的表名
-- 复制表结构和数据
    create table 表名 [as] select * from 要复制的表名
//数据的操作
-- 增
 insert into 表名 [(字段列表)] values（值列表）
   -- 当插入的值列表包含所有字段并顺序一致，则可以省略字段列表，可以插入多条数据记录。
-- 删
 delete from 表名 [删除条件子句]
 truncate table 表名 //清空表数据
******	truncate delete 的区别 	*******
#1.truncate不能加where条件，而delete可以加where条件	
#2.truncate 是删除表再创建，delete 是逐条删除(truncate的效率高一丢丢	)
#3.truncate 删除带自增长的列的表后，如果再插入数据，数据从1开始
#delete 删除带自增长列的表后，如果再插入数据，数据从上一次的断点处开始(truncate 重置auto_increment的值。而delete不会)	
#4.truncate删除不能回滚，delete删除可以回滚
#5.truncate 不知道删除了几条，而delete知道
#6.当被用于带分区的表时，truncate 会保留分区
-- 改
 update 表名 set 字段 = 值 [, 字段名 = 值] [更新条件]
--查
  select 字段列表 from 表名 [其他子句]
// 字符集编码 
-- MySQL、数据库、表、字段均可设置编码
-- 数据编码与客户端编码不需一致,在mysql中,是utf8
show variables like 'character_set_%'   -- 查看所有字符集编码项
    character_set_client        客户端向服务器发送数据时使用的编码
    character_set_results       服务器端将结果返回给客户端所使用的编码
    character_set_connection    连接层编码
set 变量名 = 变量值
    set character_set_client = gbk;
    set character_set_results = gbk;
    set character_set_connection = gbk;
set names gbk;  -- 相当于完成以上三个设置
-- 校对集
    校对集用以排序
    show character set [like 'pattern']/show charset [like 'pattern']   查看所有字符集
    show collation [like 'pattern']     查看所有校对集
   charset 字符集编码     设置字符集编码
   collate 校对集编码     设置校对集编码

//数据类型
1.数值类型
 --整型
      类型             字节            范围（有符号位）
    tinyint           1字节          -128 ~ 127      无符号位：0 ~ 255
    smallint         2字节         -32768 ~ 32767
    mediumint    3字节       -8388608 ~ 8388607
    int                 4字节
    bigint            8字节

    int(M)  M表示总位数
    - 默认存在符号位，unsigned 属性修改
    - 显示宽度，如果某个数不够定义字段时设置的位数，则前面以0补填，zerofill 属性修改
        例：int(6)   插入一个数'123'，补填后为'000123'
    - 在满足要求的情况下，越小越好。
    - 1表示bool值真，0表示bool值假。MySQL没有布尔类型，通过整型0和1表示。常用tinyint(1)表示布尔型。
    
-- 浮点型 
    类型                     字节     
    float(单精度)        4字节
    double(双精度)    8字节
    浮点型既支持符号位 unsigned 属性，也支持显示宽度 zerofill 属性。
        不同于整型，前后均会补填0.
    定义浮点型时，需指定总位数和小数位数。
        float(M, N)     double(M, N)
        M表示总位数，N表示小数位数。
        M和N的大小会决定浮点数的范围。不同于整型的固定范围。
        M既表示总位数（不包括小数点和正负号），也表示显示宽度（所有显示符号均包括）。
        支持科学计数法表示。
        浮点数表示近似值。
-- 定点数 
    decimal -- 可变长度
    decimal(M, N)   M也表示总位数，N表示小数位数。
    保存一个精确的数值，不会发生数据的改变，不同于浮点数的四舍五入。
   
2. 字符串类型
-- char, varchar
    char    定长字符串，速度快，但浪费空间
    varchar 变长字符串，速度慢，但节省空间
    M表示能存储的最大长度，此长度是字符数，非字节数。
    不同的编码，所占用的空间不同。
    char,最多255个字符，与编码无关。
    varchar,最多65535字符，与编码有关。
    一条有效记录最大不能超过65535个字节。
        utf8 最大为21844个字符，gbk 最大为32766个字符，latin1 最大为65532个字符
    varchar 是变长的，需要利用存储空间保存 varchar 的长度，如果数据小于255个字节，则采用一个字节来保存长度，反之需要两个字节来保存。
    varchar 的最大有效长度由最大行大小和使用的字符集确定。
    最大有效长度是65532字节，因为在varchar存字符串时，第一个字节是空的，不存在任何数据，然后还需两个字节来存放字符串的长度，所以有效长度是64432-1-2=65532字节。
    例：若一个表定义为create table tb(c1 int, c2 char(30), c3 varchar(N)) charset=utf8; 问N的最大值是多少？ 答：(65535-1-2-4-30*3)/3
--  blob, text
    blob 二进制字符串（字节字符串）
        tinyblob, blob, mediumblob, longblob
    text 非二进制字符串（字符字符串）
        tinytext, text, mediumtext, longtext
    text 在定义时，不需要定义长度，也不会计算总长度。
    text 类型在定义时，不可给default值
-- binary, varbinary 
    类似于char和varchar，用于保存二进制字符串，也就是保存字节字符串而非字符字符串。
    char, varchar, text 对应 binary, varbinary, blob.
3. 日期时间类型
    一般用整型保存时间戳，因为PHP可以很方便的将时间戳进行格式化。
    datetime      8字节    日期及时间     1000-01-01 00:00:00 到 9999-12-31 23:59:59
    date             3字节    日期              1000-01-01 到 9999-12-31
    timestamp   4字节    时间戳          19700101000000 到 2038-01-19 03:14:07
    time             3字节    时间             -838:59:59 到 838:59:59
    year             1字节    年份             1901 - 2155

4. 枚举和集合
-- 枚举(enum)
enum(val1, val2, val3...)
    在已知的值中进行单选。最大数量为65535.
    枚举值在保存时，以2个字节的整型(smallint)保存。每个枚举值，按保存的位置顺序，从1开始逐一递增。
    表现为字符串类型，存储却是整型。
    null值的索引是null。
    空字符串错误值的索引值是0。

//列属性
-- primary key 主键
-- 唯一标识(唯一，非空)
   create table tb ( id int, stu varchar(10), primary key (id));
   create table tb ( id int, stu varchar(10), age int, primary key (stu, age));
 -- unique 唯一索引（唯一约束）
    使得某字段的值也不能重复。
 --null 约束
    null不是数据类型，是列的一个属性。
    表示当前列是否可以为null，表示什么都没有。
    null, 默认,允许为空。
    not null, 不允许为空。
    insert into tb values (null, 'val');
        -- 此时表示将第一个字段的值设为null, 取决于该字段是否允许为null
 -- default 默认值属性
    当前字段的默认值。
    insert into tb values (default, 'val');    -- 此时表示强制使用默认值。
    create table tb ( add_time timestamp default current_timestamp );
        -- 表示将当前时间的时间戳设为默认值。
        current_date, current_time
 -- auto_increment 自动增长约束
    自动增长必须为索引（主键或unique）
    只能存在一个字段为自动增长。
    默认为1开始自动增长。可以通过表属性 auto_increment = x进行设置，或 alter table tbl auto_increment = x;
 --  comment 注释
    例：create table tab ( id int ) comment '注释内容';
 --  foreign key 外键约束
    用于限制主表与从表数据完整性。
    alter table t1 add constraint `t1_t2_fk` foreign key (t1_id) references t2(id);
        -- 将表t1的t1_id外键关联到表t2的id字段。
        -- 每个外键都有一个名字，可以通过 constraint 指定
    存在外键的表，称之为从表（子表），外键指向的表，称之为主表（父表）。
    作用：保持数据一致性，完整性，主要目的是控制存储在外键表（从表）中的数据。
    MySQL中，可以对InnoDB引擎使用外键约束：
    语法：
    foreign key (外键字段） references 主表名 (关联字段) [主表记录删除时的动作] [主表记录更新时的动作]
    此时需要检测一个从表的外键需要约束为主表的已存在的值。外键在没有关联的情况下，可以设置为null.前提是该外键列，没有not null。
    可以不指定主表记录更改或更新时的动作，那么此时主表的操作被拒绝。
    如果指定了 on update 或 on delete：在删除或更新时，有如下几个操作可以选择：
    1. cascade，级联操作。主表数据被更新（主键值更新），从表也被更新（外键值更新）。主表记录被删除，从表相关记录也被删除。
    2. set null，设置为null。主表数据被更新（主键值更新），从表的外键被设置为null。主表记录被删除，从表相关记录外键被设置成null。但注意，要求该外键列，没有not null属性约束。
    3. restrict，拒绝父表删除和更新。
    注意，外键只被InnoDB存储引擎所支持。其他引擎是不支持的。
 -- 常见约束
     not null     default    unique    check     primary key       foreign key
 -- 条件查询
 语法：
	select 
		要查询的字段|表达式|常量值|函数
	from 
		表
	where 
		条件 ;
分类：
	1.条件表达式
		示例：salary>10000
		条件运算符：
		> < >= <= = != <>	
	2.逻辑表达式
	示例：salary>10000 && salary<20000	
	逻辑运算符：
		and（&&）:两个条件如果同时成立，结果为true，否则为false
		or(||)：两个条件只要有一个成立，结果为true，否则为false
		not(!)：如果条件成立，则not后为false，否则为true
              3.模糊查询：name like 'a%'
		-:下划线通配符，一个下划线只能匹配单个字符
                    	%：百分号通配符，表示任何字符出现任何次数(包括0次)。
		%值：左模糊查询 (以值结尾的)
		值%:右模糊查询(以值开始的)
		%值%：全模糊查询(包含值的)
 -- 排序查询	
       语法：
	select
		要查询的东西
	from
		表
	where 
		条件
	
	order by 排序的字段|表达式|函数|别名 【asc|desc】
 -- 常见函数
	一、单行函数
	1、字符函数
		concat拼接
		substr截取子串
		upper转换成大写
		lower转换成小写
		trim去前后指定的空格和字符
		ltrim去左边空格
		rtrim去右边空格
		replace替换
		lpad左填充
		rpad右填充
		instr返回子串第一次出现的索引
		length 获取字节个数
		
	2、数学函数
		round 四舍五入
		rand 随机数
		floor向下取整
		ceil向上取整
		mod取余
		truncate截断
	3、日期函数
		now当前系统日期+时间
		curdate当前系统日期
		curtime当前系统时间
		str_to_date 将字符转换成日期
		date_format将日期转换成字符
	4、流程控制函数
		if 处理双分支
		case语句 处理多分支
			情况1：处理等值判断
			情况2：处理条件判断
		
	5、其他函数
		version版本
		database当前库
		user当前连接用户

 -- 分组函数
		sum 求和
		max 最大值
		min 最小值
		avg 平均值
		count 计数
	
	特点：
	  	1、以上五个分组函数都忽略null值，除了count(*)
		2、sum和avg一般用于处理数值型
		     max、min、count可以处理任何数据类型
	                3、都可以搭配distinct使用，用于统计去重后的结果
		4、count的参数可以支持：
			字段、*、常量值，一般放1
		   建议使用 count(*)
 -- 分组查询
	语法：
	select 查询的字段，分组函数
	from 表
	group by 分组的字段
特点：
	1、可以按单个字段分组
	2、和分组函数一同查询的字段最好是分组后的字段
	3、分组筛选
			针对的表	                位置			关键字
	分组前筛选：	原始表		group by的前面		where
	分组后筛选：	分组后的结果集	group by的后面		having
	
	4、可以按多个字段分组，字段之间用逗号隔开
	5、可以支持排序
	6、having后可以支持别名
 -- 多表连接查询
	笛卡尔乘积：如果连接条件省略或无效则会出现
	解决办法：添加上连接条件
-- 1.传统模式下的连接 ：等值连接——非等值连接
	1.等值连接的结果 = 多个表的交集
	2.n表连接，至少需要n-1个连接条件
	3.多个表不分主次，没有顺序要求
	4.一般为表起别名，提高阅读性和性能
	
-- 2.sql99语法：通过join关键字实现连接
	含义：1999年推出的sql语法
	支持：等值连接、非等值连接 （内连接）外连接   交叉连接	
	语法：	
	select 字段，...
	from 表1
	【inner|left outer|right outer|cross】join 表2 on  连接条件
	【inner|left outer|right outer|cross】join 表3 on  连接条件
	【where 筛选条件】
	【group by 分组字段】
	【having 分组后的筛选条件】
	【order by 排序的字段或表达式】	
	好处：语句上，连接条件和筛选条件实现了分离，简洁明了！
 -- 子查询

含义：
	一条查询语句中又嵌套了另一条完整的select语句，其中被嵌套的select语句，称为子查询或内查询
	在外面的查询语句，称为主查询或外查询

特点：
	1、子查询都放在小括号内
	2、子查询可以放在from后面、select后面、where后面、having后面，但一般放在条件的右侧
	3、子查询优先于主查询执行，主查询使用了子查询的执行结果
	4、子查询根据查询结果的行数不同分为以下两类：
	① 单行子查询
		结果集只有一行
		一般搭配单行操作符使用：> < = <> >= <= 
		非法使用子查询的情况：
		a、子查询的结果为一组值
		b、子查询的结果为空
		
	② 多行子查询
		结果集有多行
		一般搭配多行操作符使用：any、all、in、not in
		in： 属于子查询结果中的任意一个就行
		any和all往往可以用其他查询代替
 -- 分页查询
 语法：
	select 字段|表达式,...
	from 表
	【where 条件】
	【group by 分组字段】
	【having 条件】
	【order by 排序的字段】
	limit 【起始的条目索引，】条目数;

特点：
	1.起始条目索引从0开始
	
	2.limit子句放在查询语句的最后
	
	3.公式：select * from  表 limit （page-1）*sizePerPage,sizePerPage
	假如:
	每页显示条目数sizePerPage
	要显示的页数 page
 -- 联合查询
引入：
	union 联合、合并
语法：
	select 字段|常量|表达式|函数 【from 表】 【where 条件】 union 【all】
	select 字段|常量|表达式|函数 【from 表】 【where 条件】 union 【all】
	select 字段|常量|表达式|函数 【from 表】 【where 条件】 union  【all】
	.....
	select 字段|常量|表达式|函数 【from 表】 【where 条件】
特点：
	1、多条查询语句的查询的列数必须是一致的
	2、多条查询语句的查询的列的类型几乎相同
	3、union代表去重，union all代表不去重
 -- 修改单表语法：
	update 表名 set 字段=新值,字段=新值【where 条件】
 -- 修改多表语法：
	update 表1 别名1,表2 别名2
	set 字段=新值，字段=新值
	where 连接条件
	and 筛选条件
-- 多表的删除：
	delete 别名1，别名2
	from 表1 别名1，表2 别名2
	where 连接条件
	and 筛选条件;

-- 悲观锁：先获取锁，再进行操作。(即“悲观”的认为获取锁是非常有可能失败的)
		'一锁二查三更新' 在数据库上的悲观锁需要数据库本身提供支持，  
		通常用select ... for update操作来实现悲观锁。
		begin;开启事务
		select * from tb where ... for update; 设置悲观锁，其它事务中不能对tb进行修改
		悲观的认为有操作就有修改

 -- 乐观锁：先进行业务操作，不到万不得已不去拿锁。即“乐观”的认为拿锁多半是会成功的。
	乐观锁在数据库上的实现完全是逻辑的，不需要数据库提供特殊的支持。一般的做法是在需要锁的数据上增加一个版本号，
	或者时间戳，然后按照如下方式实现：
		添加版本号version ,每更新一次version加1;如果查询的version与设置的不一致则代表数据被别人修改过。

​

​

mysql的基础知识总结至此，如有不足望不吝赐教。