MySQL在cmd中的操作

cmd中不区分大小写，不管输入大写还是小写，统一显示小写
SQL语句结束时一定要加;

一、基本命令

1、启动说明

	说明：以管理员的身份运行cmd
	格式：net start 服务名称
	示例：net start mysql57

2、停止服务

	说明：以管理员的身份运行cmd
	格式：net stop 服务名称
	示例：net stop mysql57

3、连接数据

	格式：mysql -uroot -p
			mysql -hlocalhost -uroot -p
			mysql -hIP地址 -uroot -p
	说明：-h HOST            主机名
			-u   USER             用户
			-p    PASSWORD 设置的密码
			root                     用户，超级管理员，不可进行远程访问

4、退出登录（断开连接）

	quit或exit

5、查看版本（连接后可以执行）

	示例：select version();

6、显示当前的时间（连接后可以执行）

	示例：select now();

7、远程连接

	格式：mysql -hIP地址 -uroot -p
	输入对方的mysql密码

---

二、数据库的操作

1、创建数据库

	格式1：create database 数据库名 charset=utf8;
	示例1：create database stu charset=utf8;
	格式2：create database if not exists 数据库名 character set utf8;
		说明：如果该数据库不存在则创建该数据库
	示例2：create database if not exists stu character set utf8;

2、删除数据库

	格式1：drop database 数据库名;
	示例1：drop database sunck;
	格式2：drop database if exists 数据库名;  
		说明：防止执行删除不存在的库报错
	示例2：drop database if exists sunck;

3、切换数据库

	格式:use 数据库名;
	示例:use sunck;

4、查看当前选择的数据库

	select database();

5、查看所有的数据库

	show databases;

6、查看所创建的库

7、查找此库所有的字段

SELECT COUNT(column_name) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = 'industrial_chain_enterprise_project_1007';

8、查找此库所有的数据量

#查看数据量总和
select sum(table_rows) from information_schema.TABLES where table_schema = 'industrial_chain_enterprise_project_1007';
# 按照表查看数据量
select table_name,table_rows from information_schema.TABLES where table_schema = 'industrial_chain_enterprise_project_1007' ORDER BY table_rows desc;

---

三、事务处理

1.修改当前表的引擎是否为innoDB

	alter table 表名 engine = innoDB

2、查询是否为自动提交

	select @@autocommit = 0

3、设置手动提交

	set autocommit = 0

4、执行事务的代码

	代码块

5、执行提交或者回滚

	commit work 提交
	rollback work 回滚

---

四、表的操作

1、查看当前数据库中的所有表

	show tables;

2、清空表

	truncate 表名

3、创建表

	格式：create table 表名(列及类型);
	说明：
		auto_increment                  表示自增长
		primary key                     表示主键
		not null                        表示不为空
		unique                          表示唯一
	示例：create table student (id int auto_increment primary key,name varchar(20) not null,age int not null,gender bit default 1,address varchar(20),isDelect bit default 0);

4、删除表

	格式：drop table 表名;
	示例：drop table student;

5、查看表结构

	格式：desc 表名;
	示例：desc student;
	查看表的详细语句：desc 表名 \G  竖看

6、查看建表语句

	格式：show create table 表名;
	示例：show ceate table student;

7、重命名表名

	格式：rename table 原表名 to 新表名;
	示例：rename table car to newCar;
	格式2: alter table 表名 rename 新表名
	示例：alter table car rename newCar;

8、修改表

	格式：alter table 表名 add| change| drop 列名 类型;
	示例：alter table newCar add isDelete bit default 0;修改默认值等

1、更改索引：

		格式：alter table 表名 add 索引名(列名);
		示例：alter table newCar add unique(id);

2、为已经创建好的表添加外键

		格式：alter table 表名 add constraint FK_ID foreign key(你的外键字段名) REFERENCES 外表表名(对应的表的主键字段名);
        示例： alter table tb_active add constraint FK_ID foreign key(id) REFERENCES tb_user(id)

3、给表添加新的字段（默认排在最后）

		 alter table 表名 add 字段名 字段类型 约束条件

4、添加的字段排在某个位置

		alter table 表名 add 字段名  字段类型 约束条件
			first  排在第一位
			after 字段名排在某个字段后面

9、创建一个和a一样表结构的表b

---

五、数据操作

1、增

a、全列插入

		格式：insert into 表名 values(...);
		说明：主键列是自动增长的，但是全列插入时需要占位，通常使用0，插入成功以后以实际数据为准
		示例：insert into student values(0,"Tom",19,1,"北京",0);

b、缺省插入

		格式：insert into 表名(列1,列2,……)  values(值1,值2,……);
		示例：insert into student(name,age,address) values("Lilei",19,"深圳");

c、同时插入多条数据

		格式：insert into 表名 values(...),(...),……
		示例：insert into student values(0,"hanmeimei",18,0,"北京",0),(0,"poi",19,1,"北京",0),(0,"Bob",20,1,"上海",0);
		格式：insert into 表名[(指定的字段)] value(值),(值)……
		说明：给指定字段插入多个值
		示例：insert into student(username) values("hanmeimei"),("poi");

d、快速插入值

		格式：insert into 表名[(字段名)] select 字段[,*] from 表名；
				insert into a select * from a;
		说明：将表中现有的数据进行复制并加入到本表中
		示例：insert into a(username) select username from a
				 

注意：字段的值和字段的名是一一对应的

2、删

a、删除数据

		格式：delete from 表名 where 条件;
		示例：delete from student where id=4
		注意：没有条件就是全部删除

b、清空表的方式

1.格式：truncate 表名 自增回原位

2.格式：delete from 表名 删除表中的所有数据

	示例：delete from a;
		alter table 表名 auto_increment=1        把自增设置为1
	注意：删除的时候，如果没有条件语句，会删除所有的数据
			删除后的数据，自增依然记录当前的数据的位置

3、改

1.格式：updata 表名 set 列1=值1,列2=值2,……where 条件;

	示例：updata student set age=16 where id=7;
	注意：没有条件默认全部列都修改

2.所有的年龄的基础上加2

	updata a set age=age+2;

3.插入数据,如果有了数据就更新对应的列

insert into bs_douyin_user(`uid`,`nickname`,`secUid`,`signature`,`enterprise_verify_reason`) " \
                  "values ('{uid}','{nickname}','{secUid}','{signature}','enterprise_verify_reason')ON DUPLICATE KEY UPDATE `secUid`='{secUid}'"

4、查

	说明：查询表中的所有数据
	格式：select * from 表名;
	示例：select * from student;

---

六、查

1、基本语法

	格式：select * from 表名;
	说明：
			a、from关键字后面写表中的表名，表示数据来源于这张表
			b、select后面写表的列名，如果是*表示在结果集中显示表中的所有列
			c、在select后面的列名部分，可以使用as 为列起别名，这个别名显示在结果集中
			d、如果要查询多个列，之间使用逗号分隔。
	示例：
			select * from student;
			select name,age from student;
			select name as a,age from student;

2、消除重复行

	在select后面列前面使用distinct可以消除重复的行
	示例：
			select gender from student;
			select distinct gender from student;

3、条件查询

	a、语法
		select * from 表名 where 条件;
	b、比较运算符  
		等于            =
		大于            >
		小于            <
		大于等于         >=
		小于等于         <=
		不等于          !=或<>
		需求：查询id大于8的所有值
		示例：select * from student where id>8;
	c、逻辑运算符
		and           并且
		or            或者
		not           非
		需求：查询id大于7的女同学
		示例：select * from student where id>7 and gender=0;
	d、模糊查询
		insert into student values(0,"Bob",30,1,"北京",0);
		insert into student values(0,"Ba",20,1,"北京",0);
		like：
			%表示任意多个任意字符
			—表示一个任意字符
		需求：查询以B为首的同学
		示例：
			select * from student where name like "B%";
			select * from student where name like "B_";
	e、范围查询
		in：表示在一个非连续的范围内
		示例：select * from a where age in(18,20);
			a:表名
			age：列名
			select * from a where age 18 or 20;
		between。。。and。。。：表示在一个连续的范围内
			示例：select * from a where age between 18 and 20;
				a:表名
				age：列名
				select * from a where age>=18 and age<=20;
		not between and 不在。。。之间
			示例：select * from a where age not between 18 and 20;
				a:表名
				age：列名
				select * from a where age<18 or age>20;
	f、空判断
		insert into student(name,age) values("川普",70);
		注意：null 与 ""不同
		判断空：is null
		判断非空：is not null
		需求：查询没有地址的同学
		示例：select * from student where address is null;
		需求：查询有地址的同学
		示例：select * from student where address is not null;
	g、优先级
		小括号，not，比较运算符，逻辑运算符
		and 比or优先级高，如果同时出现并希望先选or，需要结合()来使用

4、聚合

	为了快速等到统计数据，提供了5个聚合函数
	a、count(*)   表示计算总行数，括号中可以写*和列名
	b、max(列)    表示求此列的最小值
	c、min(列)    表示求此列的最小值
	d、sum(列)    表示求此列的和
	e、avg(列)    表示求此列的平均值
	 
	需求:查询学生的总数
	示例：select count(*) from student; 
	需求：查询女生的编号最大值
	示例：select max(id) from student where gender=0;
	需求：查询女生编号的最小值
	示例：select min(id) from student where gender=0;
	需求：求所有学生的年龄和
	示例：select sun(age) from student;
	需求：查询所有学生年龄的平均值
	示例：select avg(age) from student;

5、分组

	按照字段分组，表示此字段相同的数据会被放到一个集合中。
	分组后， 只能查询出相同的数据列，对于有差异的数据列无法显示在结果集中
	可以对分组后的数据进行统计，做聚合运算
	语法：select 列1，列2，聚合……from 表名 group by 列1，列2，列3，……;
	需求：查询男女生总数
	示例：
		select gender，count(*) from student group by gender;
		select name,gender count(*) from student group by gender,age;

	分组后的数据筛选：select 列1,列2,聚合…… from 表名 group by 列1,列2,列3,……hanving 列1,……聚合……;
	示例：select gender，count(*) from student group by gender having gender;

	where和having的区别：
		where是对from后面的指定的表进行筛选，属于对原始数据的筛选
		having是对group by 的结果进行筛选

6、排序

	语法：select * from 表名 order by 列1 asc|desc,列2 asc|desc,……;
	说明：
		a、将数据按照列1进行排序，如果某些列1的值相同，则按照列2进行排序
		b、默认按照从小到大的顺序排序
		c、asc升序
		d、desc降序
	需求：将没有被删除的数据按年龄排序
	示例：
		select * from student where isDelete=0 order by age desc;
		select * from student where isDelete=0 order by age desc,id desc;

7、分页

	 语法：select * from 表名 limit start，count;
	 说明：start索引从0开始
	 示例：
		 select * from student limit 0,3;
		 select * from student limit 3,3;

---

七、关联

多表联查

	1) 隐式内连接查询
		select * from goods,user where `user`.id=goods.uid and uid=1
		select `user`.id,`user`.username,goods,goodsname fom goods,user where `user`.id = goods,uid and uid=1
	2）显式内连接查询 inner join
		select * from user INNER JOIN goods on `user`.id=goods.uid and`user`.id=2
		说明：A表 inner join b表 on 条件
	3) 左关联 left join
		select * from user LEFT JOIN goods on `user`.id=goods.uid and `user`.id=2
		注意：左关联以左表为主表，右表为辅表，会将主表所有的数据查询出来，辅表没有关联匹配上的数据，用null表示
	4)右关联 right join
		select * from user RIGHT JOIN goods on `user`.id=goods.uid and `user`.id=2
		注意：左关联以右表为主表，左表为辅表，会将主表所有的数据查询出来，辅表没有关联匹配上的数据，用null表示

	5)分类
		  1、表A inner join 表B：表A与表B匹配的行会出现在结果集中
		  2、表A left join 表B：表A与表B匹配的行会出现在结果集中，外加表A中独有的数据，未对应的数据使用null填充
		  3、表A right join 表B： 表A与表B匹配的行会出现在结果集中，外加表B中独有的数据，未对应的数据使用null填充

 	6)查询两个表相同数据
 		以两个表的id为准
 		select * from bs_douyin_user INNER JOIN bs_jrtoutiao_user on bs_douyin_user.uid=bs_jrtoutiao_user.id
 	7)三个表相同的数据
 		SELECT * FROM (SELECT bs_douyin_user.*,bs_jrtoutiao_user.`id`,bs_jrtoutiao_user.`name` FROM bs_douyin_user INNER JOIN bs_jrtoutiao_user ON bs_douyin_user.uid=bs_jrtoutiao_user.id) AS n INNER JOIN bs_huoshan_user ON n.uid=bs_huoshan_user.uid
 	8) 查询a.time<=2020-10-22 and a.time>=2020-10-21 中得数据和B表a.id=b.id得数据，并且显示B表得内容
 	    SELECT a.* FROM (SELECT id,articleid FROM BWHERE storage_time >= '2020-10-20 10:54:00'  AND storage_time <= '2020-10-20 10:55:00')m INNER JOIN bs_subject_article_72e2265ac1994953a01f3cc67951549f a ON a.id = m.articleid 

---

八、某些操作

当你输入失误的时候，可以用\c退出，重新输入
mysql>show tables'
    '>\c
    '>\c    默认为引号内的内容，所以不会退出
    '>'     再加一个引号结束之后再用\c退出就可以
     >\c
mysql>

显示信息

show关键词

1. show tables或show tables from database_name; -- 显示当前数据库中所有表的名称。 
2. show databases; -- 显示mysql中所有数据库的名称。 
3. show columns from table_name from database_name; 或show columns from database_name.table_name; -- 显示表中列名称。 
4. show grants for user_name; -- 显示一个用户的权限，显示结果类似于grant 命令。 
5. show index from table_name; -- 显示表的索引。 
6. show status; -- 显示一些系统特定资源的信息，例如，正在运行的线程数量。 
7. show variables; -- 显示系统变量的名称和值。 
8. show processlist; -- 显示系统中正在运行的所有进程，也就是当前正在执行的查询。大多数用户可以查看他们自己的进程，但是如果他们拥有process权限，就可以查看所有人的进程，包括密码。 
9. show table status; -- 显示当前使用或者指定的database中的每个表的信息。信息包括表类型和表的最新更新时间。 
10. show privileges; -- 显示服务器所支持的不同权限。 
11. show create database database_name; -- 显示create database 语句是否能够创建指定的数据库。 
12. show create table table_name; -- 显示create database 语句是否能够创建指定的数据库。 
13. show engines; -- 显示安装以后可用的存储引擎和默认引擎。 
14. show innodb status; -- 显示innoDB存储引擎的状态。 
15. show logs; -- 显示BDB存储引擎的日志。 
16. show warnings; -- 显示最后一个执行的语句所产生的错误、警告和通知。 
17. show errors; -- 只显示最后一个执行语句所产生的错误。 
18. show [storage] engines; --显示安装后的可用存储引擎和默认引擎。

kill卡住的进程

show processlist 显示系统中正在运行的所有进程
status 显示 Waiting for table metadata lock 代表锁表中
info 显示锁的表是哪个
杀掉不想要的进程
kill show出来的id

cast用法

CAST()函数将任何类型的值转换为具有指定类型的值。目标类型可以是以下类型之一：BINARY，CHAR，DATE，DATETIME，TIME，DECIMAL，SIGNED，UNSIGNED。
CAST()函数通常用于返回具有指定类型的值，以便在WHERE，JOIN和HAVING子句中进行比较。
我们来看一下使用CAST()函数的一些例子。

//当一个表想转字段类型 是以上cast支持类型的时候并且因为已有数据并且数据量大而无法立即转类型可以用以下命令
create  table   转化后的表名 as 
select 
id,   //原来的表的字段名称
info_id,
cast(websiteList as text),  //想要转化的字段 字段名 as 目标类型
from  转化签的表名

varchar 转text 快速

复制表结构 更改字段类型
数据复制
INSERT INTO table_name SELECT * FROM other_name