Mysql数据库命令行操作指南

命令行操作数据库

1）创建数据库

create database [if not exists] 数据库名

2）显示所有的数据库

show databases 

3）删除数据库

drop  database [if  exists] 数据库名

4）使用数据库

 use 数据库名 

5)显示表的结构

 desc 表名

6)显示表的整体结构创建语句

show create table 表名;

7)创建表的结构语法：

 CREATE   TABLE [ IF NOT EXISTS ]    `表名`   (

    `字段名1`    列类型 [ 属性 ]  [ 索引 ] [注释] ,
    `字段名2`   列类型 [ 属性 ]  [ 索引 ] [注释] , 
     … …    
     `字段名n`   列类型 [ 属性 ]  [ 索引 ] [注释] )  [  表类型 ] [ 表字符集 ] [注释] ;

对比：
 CREATE TABLE `student` (
  `id` int(11) NOT NULL  COMMENT '编号',
  `name` varchar(50) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=utf8

列类型分为
	1）数值类型  （int,float,double,decimal(m, d))
	2）字符串类型  (varchar)
	3）日期和时间型数值类型(date,time, datetime,TIMESTAMP,)
	4）NULL值

数据字段属性
   1)AUTO_INCREMENT  自动增长
   2)ZEROFILL 填充0
   3)UNSIGNED 无符号
   4)NULL 和 NOT NULL
   5)DEFAULT  
    
表列类型注释
  COMMENT   ‘编码号’

数据表的类型:
 ENGINE =  MyISAM
 ENGINE =  InnoDB

数据表的存储位置:

C:\ProgramData\MySQL\MySQL Server 5.7\Data\hlx
目录名对应数据库名，该目录下文件名对应数据表

InnoDB类型数据表只有一个*.frm文件，数据文件为上一级目录的 ibdata1 文件

MyISAM类型数据表对应三个文件：
*.frm – 表结构定义文件
*.MYD – 数据文件
*.MYI – 索引文件

修改数据表:

1) 修改表名  ALTER TABLE 旧表名  RENAME AS  新表名
2) 添加字段  ALTER TABLE 表名   ADD 字段名   列类型 [ 属性 ]  
3) 修改字段  ALTER TABLE 表名   modify 字段名   列类型 [ 属性 ]  
       ALTER TABLE 表名   CHANGE 旧字段名  新字段名   列类型 [ 属性 ]
			 
4)删除字段    ALTER TABLE 表名   DROP  字段名

删除数据库表： DROP TABLE [ IF EXISTS ] 表名

创表时添加主键约束：

create table if not exists grade(
  gid int PRIMARY KEY
);

创表时添加外键约束：

create table if not exists accpstudent(
  gid int,
  FOREIGN key(gid) references grade(gid)
);

删除表

drop table accpstudent;

– 添加约束

ALTER TABLE <数据表名> ADD CONSTRAINT <约束名>
FOREIGN KEY(<列名>) REFERENCES <主表名> (<列名>);

– 添加PK约束（主键）

alter table accpstudent add CONSTRAINT pk_sid PRIMARY key(sid)

– 添加UQ约束（唯一）

alter table accpstudent add CONSTRAINT qk_name UNIQUE(name) 

– 添加DF约束（默认）

alter table accpstudent alter age set DEFAULT 20

添加多条数据语法

--  INSERT INTO  表名  [ ( 字段1, 字段2, 字段3, … ) ] 
--  VALUES  (  '值1', '值2', '值3', …),
--          (  '值1', '值2', '值3', …),
--          (  '值1', '值2', '值3', …)

更新语法：

UPDATE 表名 set 字段名=新数据 WHERE 字段名=旧数据

例：
更新学生表中年龄为22
    update accpstudent set age=22;

根据学号是4的学生更新姓名是张三,年龄是24信息
     update accpstudent set name='张三',age=24 where sid=4;

根据班级是2的并且年龄为22的学生更新姓名是无名氏信息
   update accpstudent set name='无名氏' where gid=2 and age>22

删除语法：

DELETE FROM 表名 { WHERE 字段名=数据 }

int(3) zerofill  保留0    例：001

– 降序升序

ord by (以什么排序) desc(降序)     不加desc默认为升序

– 查询表所有的数据

select *（*代表是所有的字段名） from 表

– 过滤重复的数据性别

select DISTINCT sex from student

– NULL空值条件查询

例：SELECT * FROM student where StudentName is null;
SELECT * FROM student where StudentName is NOT null;

– 空符串查询

例：SELECT * FROM student where address='';

---------- 内连接简写语法：

    SELECT * from  表1 , 表2  WHERE 条件
	  
例：-- 查询学员所在的班级名称，学员姓名;
     select GradeName,StudentName from student st,Grade gr
     where st.gradeid =gr.gradeid;

      -- 查询学员所在的班级名称，学员姓名,考试成绩;
     select GradeName,StudentName,StudentResult from student st,Grade gr,result r
     where st.gradeid =gr.gradeid and st.StudentNo=r.StudentNo;

 

  -- 查询学员所在的班级名称，学员姓名,考试科目，考试成绩;
 select GradeName,StudentName,SubjectName,StudentResult from student st,Grade gr,result r,`subject` su
 where st.gradeid =gr.gradeid and st.StudentNo=r.StudentNo  and su.SubjectNo =r.SubjectNo;

---------- 外连接简写语法

– 左外连接（LEFT JOIN） （以左表为基准，如果没有匹配的，那么就用Null填充）

	select * from  表1 LEFT JOIN 表2 on 条件
	
例：select * from result LEFT JOIN student on student.StudentNo = result.StudentNo;

     -- 没有参加考试的学员信息
  select student.StudentNo, StudentName from student LEFT JOIN result 
  on student.StudentNo = result.StudentNo where result.StudentResult is null;	

– 右外连接（RIGHT JOIN）（以右表为基准，如果没有匹配的，那么就用Null填充）

select * from  表1 right JOIN 表2 on 条件
	
例：-- 查询学生所在的年级
  -- select * from  grade RIGHT JOIN student on  grade.GradeID =student.GradeId;

---------- 自连接查询

   select c1.cName,c2.cName from category c1,category c2 where c1.cId=c2.pid;
   
例：查询不同课程成绩相同的学生的学号、课程号、学生成绩(自连接)   （源自home3)
select DISTINCT s1.sid,s1.cid,s2.score from sc s1, sc s2
where s1.cid<>s2.cid and s1.score=s2.score ORDER BY s2.score

– ORDER BY排序 （desc降序/ASC升序(默认可写可不写)）

 select * from student;
 select * from result;
  -- 查询学员姓名及成绩，成绩按降序排序
  select studentname,studentResult from student st,result r
  where st.StudentNo =r.StudentNo
  order by StudentResult desc;

– 查询学员学号,姓名及成绩，成绩按降序排序,学号升序

  select st.StudentNo, studentname,studentResult from student st,result r
  where st.StudentNo =r.StudentNo
  order by StudentResult desc,st.StudentNo asc;

– MySQL的LIMIT
– LIMIT [m,]n 或 LIMIT n OFFSET m
– 限制SELECT返回结果的行数
– m 制定第一个返回记录行的偏移量
– n 制定返回记录行的最大数目
– 查询成绩表前8条 （第一页）
select * from result LIMIT 0,8;
– （第二页）
select * from result LIMIT 8,8;
– （第三页）
select * from result LIMIT 16,8;
– （第四页）
select * from result LIMIT 24,8;
– 0,8,16,24… (页数-1)*每页显示个数

– 查询学生姓名，课程名，成绩，显示前5名的信息

   select studentname 姓名, subjectname 课程名, studentResult 成绩
   from student st,`subject` s,result r
   where st.studentno=r.StudentNo and s.SubjectNo =r.SubjectNo
   order by StudentResult DESC
   LIMIT 5;
-- 查询《JAVA第一学年》课程成绩前10名且分数大于80的学生信息（学号，姓名，课程名，分数）
  -- 子查询  
     -- 1) 课程ID
    select SubjectNo from subject where SubjectName='JAVA第一学年';
     -- 2) 学生ID

– 多张内连接

  select  st.studentno 学号, studentname 姓名, subjectname 课程名, studentResult 成绩
  from student st,`subject` s,result r
  where st.StudentNo =r.StudentNo  and s.SubjectNo =r.SubjectNo and StudentResult>80
  and SubjectName='JAVA第一学年'
  order by StudentResult DESC
  limit 10;

– 内连接 子查询

  select st.studentno 学号, studentname 姓名, subjectname 课程名, studentResult 成绩
  from student st,`subject` s,result r
  where st.StudentNo =r.StudentNo  and s.SubjectNo =(select SubjectNo from subject where SubjectName='JAVA第一学年') and StudentResult>80
  order by StudentResult DESC
  limit 10;

– 子查询
– 在查询语句中的WHERE条件子句中，又嵌套了另外一个查询语句

-- 查询课程为《高等数学-2》且分数不小于80分的学生的学号和姓名
 select * from student;
 select * from result;
 select * from subject;
 
 -- 子查询返回多个值时，必须用in 
select DISTINCT st.studentno 学号, studentname 姓名 from student st,result r,`subject` s
where st.StudentNo=r.StudentNo 
and s.SubjectNo in(SELECT SubjectNo from subject where SubjectName='高等数学-2')
and StudentResult>=80

– MySQL函数
– 数学函数
– 字符串函数
– 日期和时间函数
– 系统信息函数
– 聚合函数
– count(),sum(),avg(),max() min() 返回是单个值还是集合？
select * from result where studentno=1000;

 -- 查询学号是1000的最高分，最低分，平均分，总分，总个数；
 select count(1),sum(studentResult),avg(studentResult),min(studentResult),max(studentResult)
 from result where studentno=1000;


-- mysql  SubjectNo多条数据没有错误!但结果不对！！！
-- 根据学号进行分组查询最高分，最低分，平均分，总分，总个数；
 select studentno,count(1),sum(studentResult),avg(studentResult),min(studentResult),max(studentResult)
 from result 
 group by studentno;

– 分组 group by
– 按照不同的课程分组，分别算出其平均分、最高分和最低分，对于低于60分平均分的不予显示
select SubjectNo,avg(studentResult) avgs,min(studentResult),max(studentResult)
from result
group by SubjectNo
HAVING avgs>=60
order by avgs

– where 分组之前的查询
– HAVING 分组之后的查询
– group by 与聚合函数一起使用