MySql命令大全，看着脑壳不疼，快来戳我吧

my sql命令  #注释


//启动mysql服务
net start mysql


//停止mysql服务
net stop mysql


//连接数据库
mysql -u root -p 回车
输入密码:root
或者   -u 用户名(user) -p密码(password)
mysql -uroot -proot回车
或者 
mysql -h服务器主机地址（本机地址是localhost） -u用户名 -p用户密码


//查看版本和当前用户
select version(),user();


//寻求帮助
? 后面接关键字(例：？show  )查询所有show的方法语句


//退出连接
exit;




//展示所有的库
show databases;


#查看某个数据库
SHOW CREATE DATABASE 数据库名;


//创建数据库  带中括号的都是可选的  避免错误
create database [if not exists] 数据库名 (例:my School);


//使用数据库
use 数据库名 (my School);


//在图书馆里面  书本是存放信息的 
在MySQL里面  表是存放信息的载体


//删除数据库  带中括号的都是可选的  避免错误
drop database [if exists]数据库名(my School);




//展示库里面表的信息
show tables;


//设置字符类型
set names gbk;


//创建表c
//  primary key 主键(唯一 非空)  auto_increment 自增长 
// comment 注释  not null 不允许为空  default默认值  
create table if not exists 表名 (列名 类型 属性,列名 类型 属性)
create table studen t(name varchar(10),age varchar(10));


//往表里插入数据   列名和 数据必须对应
insert into 表名(列名，列名，列名)values(数据，数据，数据);
  
//插入多行数据
insert into 表名 values(数据，数据，数据),(数据，数据，数据);


//查询出来的结果放入到新的表中
create table `newstudent`(select `name`,`sex`from student)


//更新数据记录
update 表名 set 列名 =更新值[where更新条件]


//查询所有数据
select * from newstudent;


//修改表中的数据 
update `student` set `sex`='女'; 


//修改指定表中的数据
update `student` set `name`='李四' where `name`='张三'; 


//查询成绩小于等于60的人
select `name`,(scort+5) as `newscort` from `students` where `scort`<60;


//查询学生表中同名的学生姓名
SELECT studentName,COUNT(studentName) AS '人数'FROM student GROUP BY studentName HAVING COUNT(studentName)>=2


//删除成绩小于60的人的信息  只能删除数据  序号删不掉
delete from `students`where `scort`<60;


//查询所有数据
select * from students;


//模糊查询 所有姓李的
SELECT * FROM student WHERE studentName LIKE '李%';


//使用truncate删除 1.删除所有数据  2.删除的数据不能恢复  3.可以把自增的序号删掉  慎选
truncate table students：




//求平均分
select avg(成绩列) from 表名;


//求最大值  最小值


select max(成绩) as '最高分’from 表名;
select min(成绩) as '最低分’from 表名;


//求和
select sum(成绩列) as ''




//求表中的数据的数量  count(*) 效率比较慢  count(1)效率比较快select count(2) '数量' from students;


//拼接concat(str1,str2....)伪表
select concat('我叫','盘子','我有爸爸妈妈') as'个人介绍' from dual;


//替换从下标为为3的开始到下标为11的全部替换为后面的盘子
select insert('我叫盘子，我有爸爸妈妈',3,11,'盘子') as'替换后的字符串';


//将字符串中的所有字符变为小写
select loser('WO shi PAN zi');


//将所有字符串变为大写
select upper('wo SHI pan zi');


//留下包括下标为3的2个长度的字符串
select substring('我叫盘子,我有爸爸妈妈',3,2) as '截取' from dual;


 //查看表内容
 desc 表名;


//修改表
alter table(旧表名)rename[to](新表名);


//唯一性 ID是student表的主键
alter table student add constraint `pk_id` primary key `student`(`id`);


//添加外键
student表和result表之间做了关联
alter table `result`add constraint `fk_student_result` foreign key(`id`) references `student`(`id`);


//查询所有表数据
select * from 表名;


//查询个别数据
select 列名,列名 from 表名;


//删除表
drop table student;


//为了让sql命令更加完善  可以在删除之前判断是否存在该表
drop table if exists student;


//获取当前日期
select curdate();


//获取当前时间
select curtime();


//获取当前的日期和时间
select now();


//返回日期为一年中的第几周
select week(now());


//返回日期的年份
select year(now());


//返回时间的小时值
select hour(now());


//返回时间的分钟值
select minute(now());


//在日期的基础上加减天数
select adddate(now(),-1) as'日期' from dual;


//查看两个指定日期之间相隔多少天
select datediff(now(),2018-06-11) as'天数' from dual;


//ceil天花板  向上取整  下面取值为1
select ceil(1.00001) from dual; 


//floor底板  向下取整 下面取值为1
select floor(1.9999)from dual;


//rand 随机数
select rand()from dual;


//取1-10的随机整数   向上取整 随机*10
select ceil(rand()*10)from dual;


//得到2017-05-01和今天相差的月份
select ceil(datediff(now(),'2017-5-1')/30) as'月份' from dual;


//not exists  不存在 true create table    false 不执行 create table
create table if not exists student(name varchar(10));




//是空的
select * from `表名`where `列名` is null;
 
//不是空的
select * from `表名`where `列名` is not null;


//对成绩做一个排序  order by 列名 asc(升序)/desc(降序)
select * from `表名` order by 列名 desc/asc


//对subjectno做一个排序 在对成绩做排序
select * from  result order by subjectno,studentresult desc


//对查询结果做一个分页  limit（列的下标）(要显示的长度)
select * from result order by studentresult desc limit 4,4




//查询Java课程最近一次的考试  如果有80分以上的学员，显示分数在前一名的学生编号和成绩    (先分开查询)


//1.查询Java课程最近的考试
select max(examDate)from result;


//2.查询Java课程
select subjectNo from `subject` where subjectName='java logic';


//3.查询80分以上的学员
select subjectNo from result where studentResult >80;


// 简约以上分开查询  子查询 true 执行  false不执行
select studentNo AS'学生编号', studentResult AS'学生成绩' from result 
 where exists(select * from result where subjectNo=1 and examDate='2017-06-21'
 and studentResult>80)
 and studentResult>80
 order by studentResult desc
 limit 9;


// 整合以上简约查询  子查询 true 执行  false不执行
select studentNo AS'学生编号', studentResult AS'学生成绩' FROM result 
 where existsselect * from result where subjectNo=( select subjectNo from `subject` where subjectName='java logic')
  and examDate=select max(examDate)from result)
 and studentResult >80)
 and studentResult >80
 order by studentResult desc
 limit 9;


//多表查询  要求显示学生姓名，成绩，科目
 select student.`studentName`AS '学生姓名',result.`studentResult`as'成绩',subject.`subjectName`as '科目'
 from student,result,`subject`where  student.`studentNo`=result.`studentNo` 
 and result.`subjectNo`=`subject`.`subjectNo`






//多表查询  (添加别名) 要求显示学生姓名，成绩，科目
 select s.studentName as'学生姓名',r.studentResult as '成绩',su.`subjectName` as '科目'
from student as s,result as r,`subject` as su
where s.studentNo=r.studentNo and r.subjectNo=su.subjectNo
 
 
//分组 （对分组结果做处理）
select subjectNo,avg(studentResult)
from result group by subjectNo;


//只保留 平均值大于60分的科目成绩
//先where  再分组group by 
//实际需求  先分组group by 再筛选
//where group having 
//作用都是筛选   区别：执行的时机不一样
select subjectNo ,avg(studentResult)
from  `result` group by  subjectNo
having avg(studentResult)>60


//查询各个年级的学员人数  年级人数大于5 的信息
select gradeid as'年级',count(*) as'人数'
from student group by gradeid
having count(*)>5 


//查询各个年级的男女生
select gradeid as '年级',sex as'性别'
from student group by studentNo




//多表联合查询方式  查询姓名 成绩 科目
//内连接   inner join第二个表名 on （条件）inner join 第三个表名 on(条件)
//外连接包括   （左连接 ，右连接）
//左外连接   left outer join  第二个表名 on （条件）      左表（主表） 主表所有信息都会显示（如果从表中没有信息，则使用null填充）
//右外连接  right outer join  第二个表名 on （条件）      以从表为主  从表中有信息就显示  没信息就不显示 


//内连接的用法      添加别名  显示学生姓名，成绩，科目
select s.studentName as'姓名',r.studentResult as '成绩',su.subjectName as '科目'
from  student as s inner join  result as r on(s.studentNo=r.studentNo)
inner join `subject`as su on(r.subjectNo=su.subjectNo)  




//左连接的用法     添加别名  显示学生姓名，成绩，科目
select s.studentName as'姓名',r.studentResult as'成绩',su.subjectName as '科目'
from student as s left outer join result as r on(s.studentNo=r.studentNo)
left outer join `subject`as su on(r.subjectNo=su.subjectNo) 


//右连接     添加别名  显示学生姓名，成绩，科目
select s.studentName as'姓名',r.studentResult as'成绩',su.subjectName as '科目'
from student as s right outer join result as r on(s.studentNo=r.studentNo)
right outer join `subject`as su on(r.subjectNo=su.subjectNo) 






//视图


//查询学生视角
select studentName,sex from student;


//创建学生视角的视图（把查询的学生视角放进去）
create view students as (select  studentName,sex from student);


//显示学生视角视图
select * from  students;


//删除视图
//drop view [if exists] studens;




//使用内连接   实现多表联合查询视图


create view studentss as
select s.studentName as '姓名',r.studentResult as'成绩'
from student as s inner join  result as r on(s.studentNo=r.studentNo)


//查询多表联合查询信息
select * from  studentss;




//创建购物库  shop
CREATE DATABASE shop;
//应用shop表
USE shop;
//创建消费表  acount
CREATE TABLE acount(`name` VARCHAR (10),money DOUBLE (10,2));
//acount表里面存入信息
INSERT INTO acount VALUES ('a',2000),('b',10000);
//删除表数据
DELETE  FROM acount;
//查询表数据
SELECT * FROM acount;




//开启事物  1.begin  2.start  3.transaction
//代表一个事物的开始
//事物四个特性 : 
1.原子性（atomicity）:一个事务是一个不可分割的工作单位.要么所有的语句都执行成功,要么所有的语句都不执行    
2.隔离性（isolation）:一个事务的执行不能被其他事务干扰,在一个事务中添加修改的数据,不能被令一个事务读取或者修改.
3.一致性 (consistency) :事务必须是使数据库从一个一致性状态变到另一个一致性状态   
4.持久性 (durability) :指一个事务一旦提交，它对数据库中数据的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响
//COMMIT 代表事物的结束


//创建购物库  shop
CREATE DATABASE shop;
//应用shop表
USE shop;
//创建消费表  acount
CREATE TABLE acount(`name` VARCHAR (10),money DOUBLE (10,2));
//acount表里面存入信息
INSERT INTO acount VALUES ('a',2000),('b',10000);
//删除表数据
DELETE  FROM acount;
//查询表数据
SELECT * FROM acount;


//关掉自动提交
SET autocommit=0;
//开启事务 用事务包起来形成一个整体
BEGIN;


UPDATE acount SET money=money-500 WHERE `name`='a';
UPDATE acount SET money=money+500 WHERE `name`='b';


//提交事物
COMMIT;
//回滚 （撤销）事物
ROLLBACK;


//还回 开启自动提交
SET autocommit=1;






*****索引准则
1.索引不是越多越好
2.不要对经常变动的数据加索引
3.小数据量的表建议不要加索引
4.索引一般加在查找条件的字段




#添加索引(便于理解，才一个表里多个索引,仅参考)： PRIMARY KEY(主键（自动加索引）)  unique(唯一索引)  index(常规索引)
#   fulltext(全文索引,要查的数据的量不能超过百分十五十，超过索引是没有用)  表默认是innoDB 修改表引擎为ENGINE=MYISAM 


#添加索引方式一：创建表，声明列属性时添加上
CREATE TABLE test1 (
id INT (4) PRIMARY KEY,
number VARCHAR (10) UNIQUE,
p VARCHAR (50),
q VARCHAR (20),
h TEXT,
#把p和q一起作为索引 index后是自定义索引名(不取名默认和列名一样)
INDEX `index_p` (p,q),
#把h作为索引
FULLTEXT (h)   
) ENGINE=MYISAM;




#添加索引方式二：创建表，将所有列都声明完毕后，再添加索引
CREATE TABLE test2 (
id INT (4),
number VARCHAR (10),
p VARCHAR (50),
q VARCHAR (20),
h TEXT,
PRIMARY KEY(id),
UNIQUE KEY(number),
#把p和q一起作为索引 index后是自定义索引名(不取名默认和列名一样)
INDEX `index_p` (p,q),
#把h作为索引
FULLTEXT (h)   
) ENGINE=MYISAM;




#添加索引方式三：先创建表，创建表完毕后，修改表，再添加索引
CREATE TABLE test3(
id INT (4),
number VARCHAR (10),
p VARCHAR (50),
q VARCHAR (20),
h TEXT
)ENGINE = MYISAM ;
#添加索引
ALTER TABLE text3 ADD PRIMARY KEY(id);
ALTER TABLE text3 ADD UNIQUE KEY (number);
ALTER TABLE text3 ADD INDEX(p,q);
ALTER TABLE text3 ADD FULLTEXT(h);




#全文索引
ALTER TABLE student ENGINE =MYISAM;


ALTER TABLE student ADD FULLTEXT (studentName);


 EXPLAIN SELECT * FROM student WHERE MATCH(studentName)AGAINST('李');
 
 
#删除索引
# 方法1
 DROP INDEX number ON test;
# 方法2
 ALTER TABLE test DROP INDEX number;
 #删除主键索引
ALTER TABLE test DROP PRIMARY KEY;
  
 #显示索引信息
 SHOW INDEX FROM student;
 


 一.DOS系统下的数据恢复和备份
    1.mysqldump命令备份 
mysqldump 和mysql同级的命令
mysqldump -- help|more 帮助你去查看mysqldump的可选参数
#导入整个bank数据库   使用mysqldump是Dos系统下的命令 
mysqldump -uroot -proot bank >G:/yy/bank.sql
#导入school数据库的某两个表
mysqldump -uroot -proot school student result >G:/yy/bank.sql
  
mysqldump -uroot -proot --skip-add-drop-table school student >G:/yy/school.sql
    2. source命令恢复 
        步骤： 1. 先登录服务器  2.再使用use选择要恢复的数据库名
       3.使用 source 脚本路径
          #恢复数据 
            source G:/yy/school.sql;  
       # mysql命令恢复  test是要恢复到的数据表名 < 脚本路径
    mysql-uroot -proot test <G:/yy/school.sql
 
 二.使用sqlyog工具来备份和恢复
直接点击数据库进行导入导出就行
注意事项： 
打钩的选项看清楚，别影响恢复
 
 三.使用sql命令方式来备份和恢复
#将school数据库里student表中的学号，姓名两列备份出去
    use  school;
#注意：备份出去的文件名不可以提前存在 (sql语句会帮你创建文件名)
            select studentNo,studentName into outfile 'G:/yy/school8.sql'from student;
#将备份出去的数据恢复到test数据库的stutable表中
步骤：1.使用数据库 2.创建新表和数据列名 
      3.从地址把数据导入进来
    use test;
    create table stutable(
id int(4),
sname varchar(20)
    )
load data infile 'G:/yy/school8.sql' into table stutable(id,sname)
#查询表内容
     select * from stutable;
 




绘制E-R图
1.实体 - 关系模型 - 例：客人张三  张三就是一个实体
2.属性 - 实体的体征 例：实体张三的入住日期
3.联系 - 两个或多个实体之间的关联关系
4.映射基数：一对一   一对多  多对一  多对多
5.实体关系图：1.矩形表示实体集(名词) 2.椭圆表示属性(名词) 3.菱形表示联系集(动词)  4.直线连接


第一范式：
保证数据表中的所有数据单位都不可再分，例如：Student里面包含了StudentID,name,age。这就是不满足第一范式！
（原子性的体现）
(一对一)
第二范式：
除了主键以外的其他列，都满足和主键有依赖关系；
例如：一个用户只有一种角色，而一个角色对应多个用户。则可以按如下方式建立数据表关系，使其满足第二范式。
user用户表，字段id,username,password,role_id
role角色表，字段id,name
用户表通过角色id（role_id）来关联角色表
（多（用户） 对一 （角色））
第三范式：
表中的列都和主键直接相关，而不是间接相关。 
一个用户可以对应多个角色，一个角色也可以对应多个用户
（多对多）




反范式化：


反范式化指的是通过增加冗余或重复的数据来提高数据库的读性能。
    例如：在上例中的user_role用户-角色中间表增加字段role_name。
反范式化可以减少关联查询时，join表的次数。














1.创建用户方式   127.0.0.1(回环地址)  localhost(本机)


--create user`用户名`@localhost
--创建一个普通用户并指向密码
create user `user`@`host`[identified by `password`];
eg:
create user `meixi`@localhost identified by `140622`;


注意：创建普通用户 ，仅有登录权限，没有对数据库操作的权限，需要进一步进行授权操作




2.授权操作


grant 指定权限 on 数据库名.表名 to 用户名@地址[identified by `password`];


eg:
--为用户meixi 授权 创建和删除权限
grant create,drop on bank.user to `meixi`@`localhost`;


--为用户meixi 对bank.user表进行select和update授权操作
grant select,update on bank.user1 to `meixi`@`localhost`;


--为用户 meixi 对bank.user表进行所有授权操作
grant all on bank.user1 to `meixi`@`localhost`;


注意:
1.在进行授权操作时 当前用户必须是管理用户状态
2.视图:是一个虚拟表（视图中的表数据是来源于其他表中的数据）




3.修改用户密码
--语法(登录后修改自己的密码)：（推荐）
set password = password (`新密码`);


注意：
必须是登录以后才能修改密码


4.删除指定用户
drop user `用户`@`localhost`;




//获取地址
ipconfig






（复习  理论）


关于sql语句的curd；
   查询
select [*|distinct|聚合函数|子查询|as字段别名|字段列表]
from tableName where 条件表达式
[group by 字段名 having 条件表达式][limit n|m]
[order by aso|deso];


1.关于运算符：
   1.1模糊查询
like _ 表示任何一个字符 % 表示任何多个或0个字符


   1.2范围运算符 只能对数值型数据运算
between and
   1,3区间运算符
      可以用在 数值 字符
in ()
not in()
   1.4测试是否是null值
is null
is not null
2.聚合函数
count()统计个数 
sum()求和
avg()平均值
min()最小值
max()最大值