MySQL基本操作

最新推荐文章于 2024-08-11 14:00:00 发布

冥想10分钟大师

最新推荐文章于 2024-08-11 14:00:00 发布

阅读量211

点赞数

分类专栏： MySQL

本文链接：https://blog.csdn.net/qq_31235811/article/details/90520293

版权

MySQL 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

文章目录

操作数据库

增
	create database db1 charset utf8;
删
	drop database db1;
改
	alter database db1 charset gbk;
查
	show create database db1;
	show databases;

操作表

show tables;
增
	create table t1(id int,name char);
删
	drop table t1;
改
	alter table t1 modify name char(6);
	alter table t1 change name NAME char(7);
查
	show create table t1;
	show tables;
	desc t1;

表字段属性

auto_increment 	表示自增
primary key  	主键、指定该列的值可以唯一地标识该列记录，不指定id，则自动增长
FOREIGN KEY ：外键，指定该行记录从属于主表中的一条记录，主要用于参照完整性
not null	 	是否为空
unique  		唯一约束，指定某列或者几列组合不能重复
default			自己设置默认值
默认值，创建列时可以指定默认值，当插入数据时如果未主动设置，则自动添加默认值null

方法

1. 增加字段
ALTER TABLE 表名  ADD 字段名  数据类型 [完整性约束条件…],				
2. 删除字段
ALTER TABLE 表名 DROP 字段名;
3. 修改字段
ALTER TABLE 表名 RENAME 新表名;
ALTER TABLE 表名 MODIFY  字段名 新数据类型 [完整性约束条件…];
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 旧/新数据类型 [完整性约束条件…];
4.指定位置
ALTER TABLE 表名 ADD 字段名  数据类型 [完整性约束条件…]  FIRST;
ALTER TABLE 表名 ADD 字段名  数据类型 [完整性约束条件…]  AFTER 字段名;
ALTER TABLE 表名  MODIFY 字段名 新数据类型 [完整性约束条件…]  AFTER 字段名;

语句操作

插入数据：
	insert into t1(id,name) values(1,'alex');
	
	insert into tb1(name,age) values('alex',12),('root',18);
	
	insert into tb12(name,age) select name,age from tb11;
删除：
	drop table t1;
	清空表：
	主键不变	delete from t1;
	主键从0开始 truncate table t1;
	
	delete from t1 where id<6;
	delete from tb12 where id >=2 or name='zrq';
修改：
	update t1 set age=18;
	update t1 set age=18 where age=17;
查看数据：
	select * from t1;
	select * from tb12 where id != 1;
	select * from tb12 where id not in (1,5,12);
	select * from tb12 where id in (select id from tb11)
	select * from tb12 where id between 5 and 12;
	
通配符：
	%表示任意多个字符
	select * from tb12 where name like "a%"；
	_表示一个字符
	select * from tb12 where name like "a_"；
分页：
	查看前10行
	select * from tb12 limit 10;
	查看第10行后面20条
	select * from tb12 limit 10,20;
	从第20行开始读取，读取10行；
	select * from tb12 limit 10 offset 20;
排序：
	从大到小排序
	select * from tb12 order by id desc; 
	从小到大排序
	select * from tb12 order by id asc;  				先按照age从大到小排序，有相同age再按照ID小到大排序
	select * from tb12 order by age desc,id asc;
	取后10条数据
	select * from tb12 order by id desc limit 10;
分组：				
	select count(id),max(id),part_id from userinfo group by part_id;
	max min sum avg count
	
	如果对于聚合函数结果进行二次筛选时？必须使用having 
	select count(id),part_id from userinfo group by part_id having count(id) > 1;
	select count(id),part_id from userinfo where id > 0 group by part_id having count(id) > 1;

数据类型：

数字：

TINYINT	1 字节	(-128，127)	(0，255)	小整数值
SMALLINT	2 字节	(-32 768，32 767)	(0，65 535)	大整数值
MEDIUMINT	3 字节	(-8 388 608，8 388 607)	(0，16 777 215)	大整数值
INT或INTEGER	4 字节	(-2 147 483 648，2 147 483 647)	(0，4 294 967 295)	大整数值
BIGINT	8 字节	(-9 233 372 036 854 775 808，9 223 372 036 854 775 807)	(0，18 446 744 073 709 551 615)	极大整数值

FLOAT	4 字节 float(255,30)		单精度浮点数值

DOUBLE	8 字节double(255,30)		双精度浮点数值

DECIMAL	double(65,30) 		小数值

create table t1(
	id int signed not null auto_increment primary key,
	num decimal(10,5),#第一个总位数，第二个为小数点后有几位
	name char(10)
)engine=innodb default charset=utf8;

字符串：

CHAR	0-255字节	定长字符串
VARCHAR	0-65535 字节	变长字符串
TINYBLOB	0-255字节	不超过 255 个字符的二进制字符串
TINYTEXT	0-255字节	短文本字符串
BLOB	0-65 535字节	二进制形式的长文本数据
TEXT	0-65 535字节	长文本数据
MEDIUMBLOB	0-16 777 215字节	二进制形式的中等长度文本数据
MEDIUMTEXT	0-16 777 215字节	中等长度文本数据
LONGBLOB	0-4 294 967 295字节	二进制形式的极大文本数据
LONGTEXT	0-4 294 967 295字节	极大文本数据

时间类型字节格式

DATE	3	YYYY-MM-DD	年月日
TIME	3	HH:MM:SS	时分秒
YEAR	1	YYYY	年份值
DATETIME	8	YYYY-MM-DD HH:MM:SS	年月日时分秒
TIMESTAMP	4	YYYYMMDD HHMMSS	混合日期和时间值，时间戳

enum枚举

#规定范围，只能单独取这几个字符
create table t10 (name char(20),gender enum('female','male'));
#gender只能是'female','male'中一个
insert into t10 values ('nezha','male');

set集合

#规定范围，只能取这几个字符的任意组合
create table t11 (name char(20),hobby set('抽烟','喝酒','烫头','翻车'));
#可以任意选择set('抽烟','喝酒','烫头','翻车')中的项，并自带去重功能
insert into t11 values ('yuan','烫头,喝酒,烫头');

自增起始值修改

显示表的信息
desc t1;
显示表的创建信息
show create table t1;
格式化查看
show create table t1 \G;
修改插入值，让自增值从20启
alter table t10 auto_increment=20;

自增步长

基于会话级别：窗口一关就没了
	查看会话变量
	show session variables like 'auto_inc%';
	设置会话步长2
	set session auto_increment_increment=2; 	
基于全局级别：全局都改了
	查看全局变量
	show global variables like 'auto_inc%';	    
	设置会话步长3
	set global auto_increment_increment=3; 	    
表级别：只修改一个表的
	CREATE TABLE `t5` (
	  `nid` int(11) NOT NULL AUTO_INCREMENT,
	  `pid` int(11) NOT NULL,
	  `num` int(11) DEFAULT NULL,
	  PRIMARY KEY (`nid`,`pid`)
	) ENGINE=InnoDB AUTO_INCREMENT=4, 步长=2 DEFAULT CHARSET=utf8

外键：省内存

	. cascade方式
在父表上update/delete记录时，同步update/delete掉子表的匹配记录 

   . set null方式
在父表上update/delete记录时，将子表上匹配记录的列设为null
要注意子表的外键列不能为not null  

   . No action方式
如果子表中有匹配的记录,则不允许对父表对应候选键进行update/delete操作  

   . Restrict方式
同no action, 都是立即检查外键约束

   . Set default方式
父表有变更时,子表将外键列设置成一个默认的值 但Innodb不能识别

分析步骤：

#1、先站在左表的角度去找
是否左表的多条记录可以对应右表的一条记录，如果是，则证明左表的一个字段foreign key 右表一个字段（通常是id）

#2、再站在右表的角度去找
是否右表的多条记录可以对应左表的一条记录，如果是，则证明右表的一个字段foreign key 左表一个字段（通常是id）

#3、总结：
#多对一：
如果只有步骤1成立，则是左表多对一右表
如果只有步骤2成立，则是右表多对一左表

#多对多
如果步骤1和2同时成立，则证明这两张表时一个双向的多对一，即多对多,需要定义一个这两张表的关系表来专门存放二者的关系

#一对一:
如果1和2都不成立，而是左表的一条记录唯一对应右表的一条记录，反之亦然。这种情况很简单，就是在左表foreign key右表的基础上，将左表的外键字段设置成unique即可
customer_id int unique, #该字段一定要是唯一的

foreign key(customer_id) references customer(id)	
create table department(
	id int auto_increment primary key,
	title char(15)
)engine=innodb default charset=utf8;

create table userinfo(
	uid int auto_increment primary key,
	name varchar(32),
	aa_id int,
	constraint fk_user_depar foreign key (aa_id) references department (id)
)engine=innodb default charset=utf8;
此时可以有一个id对应多个aa_id值，是一对多
如果加上unique aa (aa_id)就只能一一对应了

双向多对多，新建一个表，2个外键，即可实现多对多，不重复
create table info(
	id int auto_increment primary key,
	name char(10),
	gender char(10),
	email varchar(64)
)engine=innodb default charset=utf8;

create table host(
	id int auto_increment primary key,
	hostname char(64)
)engine=innodb default charset=utf8;


create table info_host(
	id int auto_increment primary key,
	userid int not null,
	hostid int not null,
	CONSTRAINT fk_u2h_user FOREIGN key (userid) REFERENCES info(id),
	CONSTRAINT fk_u2h_host FOREIGN key (hostid) REFERENCES host(id)
)engine=innodb default charset=utf8;

连表操作

select * from table1,table2 where table1.part_id = table2.id；

# 左边表table1全部显示
select * from table1 left join table2 on table1.part_id = table2.id

# 右边表table2全部显示
select * from table1 right join table2 on table1.part_id = table2.id

#内连接
select * from table1 inner join table2 on table1.part_id = table2.id

#全部连接
select * from table1 union join table2 on table1.part_id = table2.id

索引

索引加速查找

普通索引		INDEX 
唯一索引		UNIQUE INDEX
全文索引		FULLTEXT INDEX 
空间索引		SPATIAL INDEX 
组合索引		INDEX 索引名 (列名1,列名2);
			CREATE INDEX 索引名 ON 表名(列名1,列名2);

#方法一：创建表时
CREATE TABLE 表名 (
       字段名1  数据类型 [完整性约束条件…],
       字段名2  数据类型 [完整性约束条件…],
       [UNIQUE | FULLTEXT | SPATIAL ] INDEX [索引名] (字段名[(长度)] [ASC|DESC]) 
      );
      index(id),
      index ix_id(id),

#方法二：CREATE在已存在的表上创建索引
CREATE  [UNIQUE | FULLTEXT | SPATIAL ]  INDEX  索引名 ON 
表名 (字段名[(长度)]  [ASC |DESC]) ;
create index ix_age on t1(age);

#方法三：ALTER TABLE在已存在的表上创建索引
ALTER TABLE 表名 ADD [UNIQUE | FULLTEXT | SPATIAL ] INDEX 
索引名 (字段名[(长度)]  [ASC |DESC]) ;
alter table t1 add index ix_sex(sex);
alter table t1 add index(sex);

#删除索引：DROP INDEX 索引名 ON 表名字;

关键字

重点中的重点：关键字的执行优先级
from
where
group by
having
select
distinct
order by
limit

1.找到表:from

2.拿着where指定的约束条件，去文件/表中取出一条条记录

3.将取出的一条条记录进行分组group by，如果没有group by，则整体作为一组

4.如果有聚合函数，则将组进行聚合

5.将4的结果过滤：having

6.查出结果：select

7.去重

8.将6的结果按条件排序：order by

9.将7的结果限制显示条数