MYSQL数据库常用命令

MySQL是一个关系型数据库管理系统，由瑞典MySQL AB 公司开发，目前属于 Oracle 旗下产品。MySQL 最流行的关系型数据库管理系统，在 WEB 应用方面MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件之一。MySQL是一种关联数据库管理系统，关联数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。MySQL所使用的 SQL 语言是用于访问数据库的最常用标准化语言。MySQL 软件采用了双授权政策，它分为社区版和商业版，其体积小、速度快、总体拥有成本低，并且开源。
**数据库:** 数据库是一些关联表的集合。.
**数据表:** 表是数据的矩阵。在一个数据库中的表看起来像一个简单的电子表格。
**列:** 一列(数据元素) 包含了相同的数据, 例如邮政编码的数据。
**行：**一行（=元组，或记录）是一组相关的数据，例如一条用户订阅的数据。
**冗余：**存储两倍数据，冗余可以使系统速度更快。
**主键**：主键是唯一的。一个数据表中只能包含一个主键。你可以使用主键来查询数据。
**外键：**外键用于关联两个表。
**复合键：**复合键（组合键）将多个列作为一个索引键，一般用于复合索引。
**索引：**使用索引可快速访问数据库表中的特定信息。索引是对数据库表中一列或多列的值进行排序的一种结构。类似于书籍的目录。
**参照完整性:** 参照的完整性要求关系中不允许引用不存在的实体。与实体完整性是关系模型必须满足的完整性约束条件，目的是保证数据的一致性。
**数据库字母不区分大小写**
**创建数据库：** ----- create database 数据库名称;
CREATE DATABASE 数据库名 CHARACTER SET utf8;（尽量做到指定字符集）
create database 数据库名 character set  字符集 collate  utf_bin;
**查看数据库编码格式：** ----- \s;
 **指定字符集：**----- create database character set utf8/gbk;(数据库的指定)
 ****source**：** ---- 他人sql文件导入到自己的数据库中的命令，下面讲下流程
 1.把他人的数据表放到指定磁盘中
 2.打开终端命令控制台
 3.use 自己的数据库，调用自己数据库后，调用source命令 然后直接拖拽他人的数据表，即可把他人的数据表添加到自己的数据库中。
**删除数据库：** ----- drop database 数据库名;
**查询数据库详情：** ----- show create database 数据库名;;
**查询所有数据库：** ----- show databases;
**删除数据库：** ----- drop database 数据库名;;
**使用指定数据库：** ----- use 数据库名;
**--别名**---- as 举例：
select p.name from 表名 as p;
**:**----show engines \G
**创建表：** ----- create table 表名(字段1名 字段1类型, 字段2名 字段2类型....)default charset=utf8;(表的指定)
**主健约束：** ----- create table 表名(字段 类型 primary key);
			 ------create table emp(id int primary key)
			 id int primary key 和 primary key (id)有什么区别：在创建的过程中，如果只是一张表，那这两个就没什么区别，如果多表，且有表的主键是多个，那就会用到你后面的这种，比如 primary key (s_id,c_id)
**插入表信息：** ----- insert into tb（参数）values（值）如下：
				----- create table tb（username varchar(10)）values（"张三"）,（"李斯"）;				
**查看表的信息：** ----- show create table 表名;
**查看表的基本信息：**show create database 表名\G
**查看表字段：**   ----- desc 表名;
**删除表：** ----- drop table 表名;
**修改表名：**  ----- rename table 表名1 to 表名2;
 **修改引擎字符集：**-----  alter table 表名 engine=myisam/innodb charset=gbk/utf8; Innodb支持事务和行级锁（如需同步提交修改  采用Innodb  反之使用myisam，myisam为默认）
**查看数据库自动提交的状态：** show variables like '%autocommit%';
------关闭和打开自动提交  0:关闭   1：开启
------set autocommit=0;
**最后添加：**  -----  alter table 表名 add 字段名 类型;
**最前添加：**  -----  alter table 表名 add 字段名 类型 first;
**某个字段后面添加：**  -----  alter table 表名 add （需要添加的）字段名 类型 after 字段名（在谁之后）;
**删除表字段：** ----- alter table 表名 drop 字段名;
**修改表字段名称和类型：** ----- alter table 表名 change 原字段名 新字段名 新类型;
**修改字段类型和位置：**  ----- alter table 表名 modify 字段名 类型 first/(after xxx);
						 alter table tb1  modify english int after chinese;
**查询数据：**  ----- select * from 表名;
**修改**　-----　update 【表名】set 列名=值,列名=值..... where 条件；
**注释 comment：** ----- create table 表名(id int primary key auto_increment comment '这是id表示唯一性', name varchar(10) comment '这是名字', sal int comment '这是工资');
** ` 和 ' ：** ----- -`-是用来修饰表名和字段名的  可以省略- ' -是用来修饰字符串的
**SQL分类：**  ----- 
数据定义语言DDL（Data Definition Language），用于改变数据库结构，包括创建、修改和删除数据库对象。包括create（创建）、alter（修改）、drop（删除）和等命令操作。
数据操纵语言DML（Data Manipulation Language），用户通过它可以实现对数据库的基本操作，包括insert（插入）、delete（删除）、update（更改）和select（查询）等命令操作。
数据查询语言DQL（Data Query Language），包括基本查询语句、Order By子句、Group By子句等。
事务控制语言TCL（Transaction Control Language），包括commit（提交）命令、save point（保存点）命令、rollback（回滚）等命令操作。
数据控制语言DCL（Data Control Language），包括grant（授权）、revoke（撤销）等命令操作。
1. DDL：Data Definition Language 数据定义语言，包括：create,alter,drop,truncate。不支持事务
2. DML：Data Manipulation Language 数据操作语言，包括：insert delete update 和 select(DQL)。支持事务
3. DQL：Data Query Language 数据查询语言，只有select 
4. TCL：Transaction Control Language 事务控制语言，包括：commit，rollback，savepoint，rollback to xxx;
5. DCL：Data Control Language 数据控制语言，分配用户权限相关的SQL
**删除表并创建新表：**  -----  truncate table 表名;
**truncate、drop、delete区别：**
drop只是删除表，truncate是删除表并创建一个空表，delete只是删除数据自增数值不会清零
**日期类型：** ----- 
- date: 保存年月日
- time: 保存时分秒
- datetime： 保存年月日时分秒 默认值为null，最大值9999-12-31
- timestamp(时间戳)：保存年月日时分秒 默认值为当前时间，最大值2038-1-19 
- create table t_date(d1 date,d2 time,d3 datetime,d4 timestamp);
- insert into t_date values('2018-03-18',null,null,null);
- insert into t_date values(null,'17:23:18','2018-05-15 12:18:33',null);
**is null：** ----- 表示为空 select empno,ename,sal from emp where mgr is null;
**is not null：** 表示不能为空 ----- select * from emp where comm is not null;
“is null”用来判断操作数是否为空值（null）。操作数为null时，结果返回1；否则，返回0。is not null刚好与is null相反。
**运算符：**  ----- +加，-减，*乘，/除，%求余，==	等，<=>	安全的等于，<>(!=)	不等于，<=	小于等于，>=	大于等于，>大于.... 
**去重：** ----- distinct  用在查询语句  create xxx from 表名的xxx条件前
**and 和 or：** ----- and和java中的 &&效果一样,or 和java中的|| 效果一样 多个的话可以用in代替
举例：select * from 表名 where 部门=10 and 薪水>3000;
**between x and y：**  ----- select * from 表名 where 字节 between 2000 and 4000;
**模糊查询 like：**  ----- _: 代表单个未知字符     %：代表0或多个未知字符
举例：
6. 名字以a开头  ename like 'a%'
7. 以a结尾    %a  
8. 包含a     %a%
9. 第二个字母是a   _a%
10. 倒数第三个字符是a   %a__
11. 第二个字母是a最后字母是b    _a%b
12. escape转义字符：用法
	select * from  emp where name like '%\%%' escape'\';
**order by：** ----- 如果有条件写在条件的后面 没条件写在 表名的后面,默认是升序   desc降序  asc升序。
举例：select 字节 from 表名 order by 字节 desc;
order by 和 group by区别：oeder by按照一定的条件  升序或者降序  而group by是分组
 显示第二页 每页7条数据
**分页查询：** ----- 第一个参数代表跳过的条数 - 第二个参数代表每页的数量
select * from 表名  字节 limit  7,7;
**聚合函数：**  ----- 求和（sum），平均值（avg）最大值（max）最小值（min）统计数量（count）该字段用在select（）from之间
**字符串的拼接：**  ----- select concat(字段,'元') from 表名;
**字符串的长度：** ----- select 字段,char_length(字段) from 表名;
**获取字符串在另一个字符串出现的位置 从1开始：** 
----- 1-格式： instr(str,substr)：     select instr('abcdefg','d');  4
----- 2-格式： locate(substr,str);     select locate('d','abcdefg');  4
**转大写转小写：**  ----- select upper('abc'),lower('NBA');    ABC   nba
**去空白：**  ----- select trim('  a b   ');     a  b只能去除两头的空白
**字符串截取：** ----- left()、right()、substring()、substring_index()
从左开始截取字符串
用法：left(str, length)，即：left(被截取字符串， 截取长度)
SELECT LEFT('www.baidu.com',9)
结果为：www.baidu
从右开始截取字符串
用法：right(str, length)，即：right(被截取字符串， 截取长度)
SELECT RIGHT('www.baidu.com',9)
结果为：baidu.com
**截取特定长度的字符串：** ----- substring(str, pos)，即：substring(被截取字符串， 从第几位开始截取)。substring(str, pos, length)，即：substring(被截取字符串，从第几位开始截取，截取长度)
1.从字符串的第9个字符开始读取直至结束
SELECT substring('www.xingzhexi.com', 9)
结果为：zhexi.com
2.从字符串的第9个字符开始，只取3个字符
SELECT substring('www.xingzhexi.com', 9, 3)
结果为：zhe
3.从字符串的倒数第6个字符开始读取直至结束
SELECT substring('www.xingzhexi.com', -6)
结果为：xi.com
4.从字符串的倒数第6个字符开始读取，只取2个字符
SELECT substring('www.xingzhexi.com', -6, 2)
结果为：xi
**反转：**  -----select reverse('abc'); ### cba
**替换：**  ----- select replace('abcdefg','de','mm');  abcdefg中的de替换成了mm 效果是abcmmfg
**循环：** ----- select repeat('',2); 
**向下取整：** ----- select floor(3.84);  3
**四舍五入：**  ----- select round(23.8);  24。另外一种四舍五入 round(num,m)  m代表小数位数
**非四舍五入：**  ----- select truncate(23.879,2);  23.87。 truncate(num,m) m代表小数位数
**rand() 随机数：** -----  0-1随机数 ，select floor(rand()*9);------select floor(rand()*3)+3;  3-5 随机数
**having：** ----- 普通字段的条件写在where后面，聚合函数的条件写在having后面
**关联查询：**  ----- 
select e.字段,d.字段
from 表名 e,表名 d
where e.字段=d.字段; 
**笛卡尔积：**  ----- 如果关联查询不写关联关系则查询到的数据是两张表的乘积，这个乘积称为笛卡尔积，笛卡尔积是一种错误查询方式的结果，工作中切记不要出现
**等值连接和内连接：** 
----- 1. 等值连接：select * from A,B where A.x=B.x and A.age=18;
----- 2. 内连接： select * from A join B on A.x=B.x where A.age=18;
**mysql左连接、右连接、内连接（等值连接）、全连接：** ----- 
1.准备表、数据
create table a(id int, nameA varchar(16));
insert into a values(1,'a1');
insert into a values(2,'a2');
create table b(id int, nameB varchar(16));
insert into b values(1,'b1');
insert into b values(3,'b3');
2.内连接、外连接
2.1内连接
----------------------------------------------------------
SELECT * from a inner JOIN b on a.id = b.id;    --内连接
1    a1    1    b1 
SELECT * from a, b where a.id = b.id;     --等值连接 = 内连接
1    a1    1    b1
2.2左连接
----------------------------------------------------------
SELECT * from a left JOIN b on a.id = b.id;    --左连接
1    a1    1    b1
2    a2    null null
2.3右连接
----------------------------------------------------------
SELECT * from a right JOIN b on a.id = b.id;    --右连接
1    a1    1    b1
null null  3    b3
2.4全连接
----------------------------------------------------------
SELECT * from a full JOIN b;  --全连接
1    a1    1    b1
2    a2    1    b1
1    a1    3    b3
2    a2    3    b3
这几种连接的区别，大体可以理解为：
左连接以左边为基准，右边可能有空值；
右连接以右边为基准，左边可能有空值；
内连接则保证没有空值,不对应的就不显示了。
**嵌套查询：**  ----- select 字段1 from 表名 where sal=(select 字段2 from 表名);
**关联关系之表设计：** ------ 外键： 用来建立关系的字段称为外键
				 	 ------ 主键： 用来表示数据唯一性的字段称为主键
**关联关系之表设计之一对一：** ----- 案例
- 有AB两张表，A表中的一条数据对应B表中的一条数据同时B表一条对应A表一条，这种关系称为一对一
- 应用场景：商品表和商品详情表，   
- 如何建立关系： 在从表中添加外键，外键的值指向主表的主键
- 练习：请设计表保存以下数据
- 1. 用户名：wukong 密码：123456 昵称：齐天大圣 电话：13733666633 地址：花果山
- 2. 用户名：bajie 密码：abcd 昵称：二师兄 电话：13833446622 地址：高老庄celect
- 3. 用户名：libai 密码：aabbcc 昵称：李白 电话：13355668877 地址：语文书里
create table user(id int primary key auto_increment,username varchar(10),password varchar(10));
create table userinfo(userid int,nick varchar(10),tel varchar(15),address varchar(20));
insert into user values(null,'wukong','123456'),(null,'bajie','bacd'),(null,'libai','aabbcc');
insert into userinfo values(1,'齐天大圣','13833446622','花果山'),(2,'二师兄','13833446622','高老庄'),(3,'李白','13833446622','语文书里');
 完成以下查询：
 1. 查询李白的用户名和密码是什么
select u.username,u.password
from user u join userinfo ui
on u.id=ui.userid
where ui.nick='李白';
14. 查询每一个用户的所有信息
select *
from user u join userinfo ui
on u.id=ui.userid;
15. 查询用户名bajie 的昵称是什么
select ui.nick
from user u join userinfo ui
on u.id=ui.userid
where u.username='bajie';
**关联关系之表设计之一对多：** ----- 案例
- AB两张表，A表中一条数据对应B表中多条数据同时B表中一条数据对应A表中多条，称为多对多 
- 应用场景： 老师-学生   用户-角色 
- 如何建立关系：需要创建新的关系表，表中添加两个外键，指向两个主表的主键
- 练习：创建表保存以下数据
- 1. 唐僧的学生有：悟空，传奇哥
- 2. 苍老师的学生有： 传奇哥，克晶姐2. 苍老师的学生有： 传奇哥，克晶姐
create table teacher(id int primary key auto_increment,name varchar(10));
create table student(id int primary key auto_increment,name varchar(10));
create table t_s(tid int,sid int);
insert into teacher values(null,'唐僧'),(null,'苍老师');
insert into student values(null,'悟空'),(null,'传奇哥'),(null,'克晶姐');
insert into t_s values(1,1),(1,2),(2,2),(2,3);
- 查询苍老师的学生姓名
select s.name
from teacher t join t_s ts
on t.id=ts.tid
join student s
on ts.sid=s.id
where t.name='苍老师';
- 查询传奇哥的老师姓名
select t.name
from teacher t join t_s ts
on t.id=ts.tid
join student s
on ts.sid=s.id
where s.name='传奇哥'
**表设计之权限管理案例：**  ----- 案例
- 创建三张主表user(id,name) role(id,name) module(id,name) 和两张关系表 u_r(uid,rid)（用户和角色） r_m(rid,mid)（角色和权限）
create table user(id int primary key auto_increment,name varchar(10));	 
create table role(id int primary key auto_increment,name varchar(10));	
create table module(id int primary key auto_increment,name varchar(10));
create table u_r(uid int,rid int);
create table r_m(rid int,mid int);
- 保存以下数据：  
- 用户表：刘德华，貂蝉
insert into user values(null,'刘德华'),(null,'貂蝉');
角色表：男游客，男管理员，女游客，女会员
insert into role values(null,'男游客'),(null,'男管理员'),(null,'女游客'),(null,'女会员');
权限表：男浏览，男发帖，男删帖，女浏览，女发帖
insert into module values(null,'男浏览'),(null,'男发帖'),(null,'男删帖'),(null,'女浏览'),(null,'女发帖');
关系：男游客->男浏览；男管理员->男浏览，男发帖，男删帖；女游客-》女浏览；女会员-》女浏览，女发帖
刘德华-》男管理员和女游客
貂蝉-》女会员和男游客
insert into r_m values(1,1),(2,1),(2,2),(2,3),(3,4),(4,4),(4,5);
insert into u_r values(1,2),(1,3),(2,4),(2,1);
- 练习：
- 3. 查询每个用户对应的所有权限	select u.name,m.name
from user u join u_r ur
on u.id=ur.uid
join r_m rm 
on ur.rid=rm.rid
join module m
on rm.mid=m.id;
16. 查询刘德华的所有权限
select m.name
from user u join u_r ur
on u.id=ur.uid
join r_m rm 
on ur.rid=rm.rid
join module m
on rm.mid=m.id 
where u.name='刘德华';
17. 查询拥有男浏览权限的用户都是谁
select u.name
from user u join u_r ur
on u.id=ur.uid
join r_m rm 
on ur.rid=rm.rid
join module m
on rm.mid=m.id 
where m.name='男浏览';
**约束：** ----- 约束就是给表字段添加限制条件
**非空约束 not null：** ----- 字段的值不能为null-----案例
create table t1(id int,age int not null);
-测试：
insert into t1 values(1,20);//成功
insert into t1 values(2,null);//失败
**#唯一约束 unique：**  ----- 字段的值不能重复------ 案例
create table t2(id int,age int unique);
-测试：
insert into t2 values(1,20);//成功
insert into t2 values(1,20);//失败
**主键约束 primary key：**  ----- 字段的值唯一且非空  一般用于在创建表字段的时候   要让id唯一  那么我们会采用 primary key主健来约束id，上面有写到  不清楚格式的翻倒上面查看下copy此字段查询（primary key）
**默认约束default：** ----- 给字段设置默认值 ------ 案例
create table t3(id int,age int default 20);
-测试：
insert into t3 (id) values (1);//默认值生效
insert into t3 values (2,30);
**外键约束：** ----- 
	外键：用来建立关系的字段称为外键
 外键约束：添加外键约束的字段，值可以为null，可以重复，但是不能是关联表中不存在的数据，外键指向的数据不能先删除，外键指向的表不能先删除
 案例：
 1. 创建部门表
create table dept(id int primary key auto_increment,name varchar(10));
18. 创建员工表
create table emp(id int primary key auto_increment,name varchar(10),deptid int,constraint 　 foreign key(deptid) references dept(id));
-格式介绍：constraint 约束名称 foreign key(外键字段名) references 表名(字段名)
-插入数据：
insert into dept values(null,'神仙'),(null,'妖怪');
-测试：
insert into emp values(null,'悟空',1);//成功
insert into emp values(null,'赛亚人',3);//失败
delete from dept where id=1;//失败
drop table dept;//失败
drop table emp;//成功
工作中除非特定场景一般不是用外键约束，因为添加约束后会影响测试效率，一般通过代码建立逻辑外键。
**索引：**  ----- 索引是数据库中用来提高查询效率的技术，类似于目录
**为什么使用索引：**  ----- 如果不使用索引，数据会零散的保存在磁盘块中，查询数据需要挨个遍历每一个磁盘块，直到找到数据为止，使用索引后会将磁盘块以树状结构保存，查询数据时会大大降低访问的磁盘块数量，从而提高查询效率。
**创建索引：** -----  create index 索引名 on 表名(字段(字符长度));*name varchar(10)*
create index index_item_title on item2(title);
select * from item2 where title='100'; //耗时0.02秒
**查看索引：** ----- 只要是给表添加主键约束，则数据库会为此表自动创建主键字段的索引。表达式： show index from item2;
**删除索引：**  ----- drop index 索引名 on 表名;  表达式：create index index_item_title_price on item2(title,price);
索引总结：
19. 索引是用来提高查询效率的技术，类似于目录
20. 因为索引会占用磁盘空间所以不是越多越好 2. 因为索引会占用磁盘空间所以不是越多越好
21. 因为数据量小的表创建索引会降低查询效率所以不是有索引就一定好3. 因为数据量小的表创建索引会降低查询效率所以不是有索引就一定好
**事务：**  ----- 数据库中执行SQL语句的工作单元，保证全部成功或全部失败 
事务的ACID特性
- Atomicity： 原子性， 最小不可拆分 全部成功全部失败
- Consistency: 一致性， 从一个一致状态到另外一个一致状态 
-  Isolation： 隔离性， 多个事务之间互不影响 
-  Durability：持久性，事务完成后数据提交到数据库持久保存 
**事务操作命令：** ----- 在默认情况下MySQL开启的是autocommit模式，也就是隐含的将每条语句当做一个事务处理，每条SQL都会被自动提交。当我们使用BEGIN或者START TRANSCATION时会把自动提交挂起，直到显示的调用COMMIT。使用事务可以有如下两种方法：
BEGIN; //开始事务，挂起自动提交
insert into t_cart_shopcart (user_id, sku_id, amount, shop_id,  status) values(10001, 10001, 1, 10001, 0);
insert into t_cart_shopcart (user_id, sku_id, amount, shop_id,  status) values(10001, 10002, 1, 10001, 0);
COMMIT; //提交事务，恢复自动提交
set autocommit = 0; //挂起自动提交
insert into t_cart_shopcart (user_id, sku_id, amount, shop_id,  status) values(10001, 10001, 1, 10001, 0);
insert into t_cart_shopcart (user_id, sku_id, amount, shop_id,  status) values(10001, 10002, 1, 10001, 0);
COMMIT; //提交事务
set autocommit = 1; //恢复自动提交
22. 查看自动提交状态show variables like '%autocommit%';
23. 修改状态
set autocommit=0/1;
24. 提交  
commit;
25. 回滚
rollback;
26. 保存回滚点
savepoint s1;
27. 回滚到某个回滚点 
rollback to s1;
**group_concat()：** ----- select deptno,group_concat(ename,'-',sal) from emp group by deptno;
**后续更新....**  -----