【MySQL】数据库——常用命令总结

1, 启动mysql服务
   net start mysql;
   
2，停止mysql服务
   net stop mysql;
   
3，查询端口是否冲突
   netstat –na | findstr 8080 查看被监听的端口 , findstr用于查找后面的端口是否存在。
   
4，退出mysql
   输入 exit
   
==============================================================================================
注意：-- 以双减号开头的是注释

数据库的基本操作
1，连接mysql
	使用管理员身份打开cmd
	输入 e：  然后回车
	输入 cd E:\mysql\mysql-5.7.30-winx64\bin  然后回车
	输入 mysql -u root -p
	输入 自己设置的数据库密码

2，创建数据库
   create database 库名;   (create database 库名 character set utf8mb4;)
   设置编码格式为utf-8
   create database 表名称 character set utf8mb4;

3，显示当前所有的数据库
   show databases;  //英文分号一定要加上
   
4, 查询当前使用的是哪个数据库
	select database();
   
5，使用数据库
   use  库名;
   列出库中所有的表
   show tables;

4, 查询当前使用的是哪个数据库
	select database();

6,创建表
	CREATE TABLE 表名 (字段名 VARCHAR(20), 字段名 CHAR(1))；
	举例
	drop table if exists booklist; 
	CREATE TABLE booklist  (
		  id mediumint(8) unsigned not null AUTO_INCREMENT COMMENT '书本id',
		  user_id mediumint(8) unsigned not null COMMENT '用户id',
		  title varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '书名',
		  PRIMARY KEY (`id`) USING BTREE 
    );

7，查看表结构
	desc 表名;
	
	
8：给表中添加新数据
	INSERT INTO booklist VALUES (1, 19, '《莎士比亚》');
	INSERT INTO booklist VALUES (2, 20, '《悲惨世界》');
	INSERT INTO booklist VALUES (3, 21, '《西游记》');
	INSERT INTO booklist VALUES (4, 24, '《红楼梦》');
	INSERT INTO booklist VALUES (5, 25, '《圣经》');
	
9，查看表中数据
	select*from booklist;
	

10，删除
    删除数据库
	drop database 库名;
	删除数据表
    drop table 表名;
	将表中数据清空
	delete from 表名;
	delete from students where math is null;//删除数据为空的行
	
11，修改自己的表格式为utf-8
	create database 表名 character set utf8mb4;
	
==============================================================================================
常用的数据类型

id类       用int  和  unsigned 
自动编号   用auto_increment
名字       用varchar(20)
注释       用comment
日期       用datetime  和 timestamp default NOW()
判空唯一   用not null unique
主键       用primary key
外键       用foreign key
成绩       用decimal(4, 1)  4指的是长度，1指的是小数点位数

==============================================================================================
增删改查CRUD

学生示例表格
create table students (
		id int, 
		name varchar(20), 
		class_name varchar(20),
		math decimal(3.1),
		chinese decimal(3.1),
		english decimal(3.1)
		);


一：增加
1，单行数据+全列插入     
insert into students values (1, '宋江', '水浒',67,89,65);
2,多行数据+指定列插入
insert into students(id,name,class_name,math,chinese,english)values
	  (1, '宋江', '水浒',67,89,65),
	  (2, '卢俊义', '水浒',89,56,26),
	  (3, '公孙胜', '水浒',56,36,45),
	  (4, '吴用', '水浒',98,76,89),
	  (5, '呼延灼', '水浒',69,89,76),
	  (6, '林冲', '水浒',56,79,83),
	  (7, '武松', '水浒',99,89,76),
	  (8, '鲁智深', '水浒',69,62,54),
	  (9, '唐三藏', '西游',56,79,86),
	  (10, '孙悟空', '西游',46,89,79);
3，新增一列数据
alter table 表名 add column 列名 varchar(20) not null;
4，删除一列数据
alter table 表名 drop column 列名 ;

二：删除
delete from 表名 [where 条件]
drop table if exists 表名
举例： delete from  students where name='宋江';

三：修改
update 表名 set 列名=.. where条件 
举例： update students set math=80 ,english=80 where name='林冲';  // 修改林冲的数学和英语成绩都为80分
       update students set chinese=chinese+10;  //给每个同学的语文成绩都加上10分


四：查询

1，全列查询
select * from 表名;
举例：select * from students;

2，指定列查询
select 列名  from 表名;
举例：select id,class_name from students;

3，查询字段为表达式
select 列名，表达式 from 表名;
举例：select id+10,name from students;
      select id, name, chinese+math+english from students;
	  
4，别名
为查询结果中的列指定别名
select 列名 [as] 新列名 from 表名;
举例： select id  as 编号 from students;
       select id, name, chinese+math+english 总分数 from students;//as可以省略
	   
5，去重
select distinct 列名 from 表名;
举例： select distinct class_name from students;

6，排序
ASC 升序（上小下大）    desc 降序（上大下小）  一般默认为升序
select  列名 from 表名  where条件  order by  列名 desc;
举例： select id ,math from students order by math desc;
       select id, name, chinese+math+english as 总分数 from students  order by 总分数 asc;
	   select name, chinese，math from students order by chinese desc,math;

7，条件查询
where查询可以使用表达式，但是不能用别名
基本查询： select id,name from students where id>10; 
		   select id, name, chinese+math+english 总分数 from students where chinese+math+english>210 ;
and与or：  select * from students where chinese>60 and math>80;
		   select * from  students where math>80 or english>70;
范围查询： select * from students where math between 60 and 80;
in：       select name,math from students where  math in (57,89,69);  //查询数学成绩是57或者89或者59的同学 
模糊查询： select name from students where name like '孙%';  //匹配到姓孙的人  %表示匹配任意字符    比如 孙悟空，孙权
           select name from students where name like '宋_';  //匹配到姓宋的人，且名字只有两个字    比如 宋江，宋晓
null查询： select name qq_mail from students where qq_mail is not null; //查询qq_mail已知的同学姓名

8，分页查询
起始下标为0
从0开始，筛选n条结果：select 列名 from 表名 limit n;
从m开始，筛选k条结果：select 列名 from 表名 limit k offset m;
举例： select id,name from students limit 3;  //筛选前三条信息
       select id,name from students limit 5 offset 2; //从第2条开始，筛选5条结果
	   
==============================================================================================
数据库约束-建表语句里面写

1，null约束
建表时，可以指定某列不为空
id int not null;

2，唯一约束 unique
指定列为唯一的，不重复的
email varchar(20) unique;

3，默认值约束 default
指定插入数据时，years列为空，默认值为2021
years int default '2021';


4，主键约束 primary key
指定id为主键
id int primary key auto_increment; //auto_increment指插入数据对应字段不给值时，使用最大值+1;

5，外键约束 foreign key
外键用于关联其他表的主键或唯一健
foreign key (字段名) references 主表(列)
关联学生表和班级表，在建表示
foreign key (class_id) references classes(id);

6，约束 check
check(sex='男'or sex='女');
	  
==============================================================================================
表的设计-聚合查询-联合查询

表的设计：1:1  1:多  多:多
查询
将学生表中的所有数据复制到用户表
insert into test_user(name,email)select name,qq_mail from student;

聚合查询
1，count 统计数量
select count(*) from students;//统计班级有多少同学，查询表中有多少行数据
select  class_name, count(*) from  students group by class_name;// 聚合查询，分组查询显示结果，然后按班级进行分班
2，sum 求数据总和
select sum(math) from students;//统计数学成绩总分
3，avg 求平均
select avg(english) from students;//统计全班英语成绩平均分
4，max 求最大值
select mix(english) from students;//返回英语成绩最高分
5，min 求最小值
select mix(chinese) from students;//返回语文成绩最底分

group by 分组查询--对指定列进行分组
select  name ,max(math),min(math),avg(math) from students  group by class_name;//查询每个班级的最高数学成绩、最低数学成绩、平均数学成绩

having
group句子分组之后，需要对分组结果再进行条件过滤时，用having
where  是分组前进行筛选：having 是分组后进行筛选；
select role,max(salary),min(salary),avg(salary) from emp group by role having avg(salary)<1500;//显示平均工资低于1500的角色和它的平均工资

联合查询
注意：关联查询可以对关联表使用别名
1，内连接
select 字段 from 表1，表2 where 条件;//inner可以省略
举例  select
		  stu.id,
		  stu.name,
		  cls.name as class_name,
		  gender
	  from
		  stu,
		  cls
	  where
      cls.id=class_id;
2，外连接（左连接+右连接）
select 字段 from 表1 left/right join 表2 on 条件 where 条件
select stu2.id,stu2.name,cls2.name as class_name  from  stu2 left join cls2 on stu2.class_id=cls2.id;//左联
select stu2.id,stu2.name,cls2.name as class_name  from  stu2 right join cls2 on  stu2.class_id=cls2.id;//右联
3，自连接
select 字段 from 表1，表1 where 条件;
4，子查询
select 字段 from 表1 where 字段1=（select...from ...）;
select 字段 from 表1 where 字段1 in（select...from ...）;
5，合并查询
--union ：去除重复的数据
select...from ...where 条件
  union
select...from ...where 条件
--union all :不去重
select...from ...where 条件
  union  all
select...from ...where 条件



==============================================================================================
索引和事务

1，索引
数据库中的表、数据、索引之间的关系，类似于书架上的图书、书籍内容和书籍目录的关系。
索引所起的作用类似书籍目录，可用于快速定位、检索数据。
索引对于提高数据库的性能有很大的帮助。

创建主键约束，唯一约束，外键约束会自动创建索引
 查看索引
 show index from  booklist;
 创建索引
 create index 索引名 on 表名（字段名）;
 create index id_classname on students(name);
 删除索引
 drop index 索引名 on 表名;
 drop index id_classname on students;
 
 2，事务
 事务指逻辑上的一组操作，组成这组操作的各个单元，要么全部成功，要么全部失败。
(1)开启事务
 start transaction;
(2)执行多条SQL语句
(3)回滚或提交：
rollback/commit;
说明：rollback即是全部失败，commit即是全部成功。