MySQL常用命令

数据库基本知识以及MySql语句

1.主键约束：

保证数据的完整性。
一个表只能有一个主键；主键列不允许为空。

2.外键约束：主表关联从表

MySql命令行连接MySql：
mysql-h服务器主机地址（本机可省略）-u用户名-p密码（可省略，回车继续输入密码）

mysql -u root -p root

SQL结构化查询语言简介：
数据操作语言DML：操作数据。

  INSERT;UPDATE;DELETE。

数据定义语言DDL：创建删除数据库对象。

  CREATE;DROP;ALTER。

数据查询语言DQL：数据库查询。

  SELECT

数据控制语言DCL：控制数据库组件许可权限。

  GRANT;REVOKE；commit；rollback。

SQL运算符：

 +  -  *  /  %  =  >    <   >=   <= 

<>:不等于
AND:两个表达式都为true返回true
OR：两个表达式都为false返回false
NOT：取反操作。

DDL操作数据库:

创建数据库：

  CREATE DATABASE 【IF NOT EXISTS】数据库名；

查看数据库：

  SHOW databases;

选择数据库：

 USE 数据库名；

删除数据库：

  DROP DATABASE 数据库名；

DDL操作数据库表：

数值类型：

 TINYINT：非常小的数据
           INT：标准整数
       DOUBLE:双精度浮点数
       CHAR：定长字符串
       VARCHAR：可变字符串
       DATATIME：年月日时分秒
       TIMESTAMP：时间戳。年月日时分秒连在一起。
                      设置当前时间时用到

创建表：

CREATE TABLE [IF NOT EXISTS] 表名
（ 字段 数据类型 [字段属性 约束] [索引] [注释]
）[表类型][表字符集][注释]； #(注释符号)例如创建学生表：

 CREATE TABLE `student`（
  `studentNo`INT(4) PRIMARY KEY，
   `name` CHAR(10),
    .....
 ）;comment='';

属性 约束：
NOT NULL:非空约束。
default：默认约束。
unique KEY：唯一约束。
primary KEY:主键约束。
foreign KEY:外键约束。
auto_increment/auto_increment=10:自动增长/从10自动增长约束。通常用于主建。
set *** auto_increment_increment=5;所有自动增长以+5方式。
unsigned属性：标识为无符号属性值。不可取值为负数。相当于给 正数位数扩大一倍。
zerofill属性：宽度（位数）不足时以0填充。
若指定了ZEROFILL属性，将自动添加UNSIGNED。
set sql_mode='strict_trans_tables';严格模式。
不能插入多于指定长度的列类型。

注释：

 comment'注释内容'

表字符集设置：

 charset=字符集名称；

数据库字符集设置

character set 字符集名称；

校对规则：

  collate  校对规则；

查看表：

  SHOW tables；

查看表定义：

  describe 表名；

避免DOS窗口乱码：

  SET NAMES GBK; 

删除表：

  DROP TABLE [IF EXISTS]表名;

删除表数据：

   delete from 表名；

截断表数据：

   truncate  表名；

MySql系统帮助：

  HELP 查询内容；

修改表名：

alter table 旧表名  rename as 新表名；

添加字段：

alter table 表名 add 字段名 列类型【属性】

修改字段：

alter table 表名 modify 字段名 列类型 【属性】

修改列名：

alter table  表名 change  旧字段名  新字段名 列类型【属性】

添加主键；

alter table 表名 add constraint 主键名  primary key(字段名);

添加外键；

alter table 表名 add constraint 外键名  foreign key（字段名）references  外表(外表主键)；

删除字段：

alter table 表名 drop 字段名 

暂时关闭外键约束：

SET FOREIGN_KEY_CHECKS=0;

引入sql文件：

source  sql文件路径。路径用/隔开。

MySql存储引擎：
SHOW engines；列出当前版本支持引擎。

MyISAM：不支持事务处理；不支持数据行锁定；
不支持外键约束；支持全文索引。较小。 不需事务，空间小，以查询访问为主。

InnoDB：支持事务处理；支持数据锁定；
支持外键约束；不支持全文索引。较大。
多删除更新操作，安全性高，事务处理及并发控制。

设置表的表类型引擎：

CREATE TABLE 表名（ …）engine=存储引擎；

MyISAM类型表文件：

 以.frm表结构定义文件
 以.MYD数据文件；一个表对应一个文件
 以.MYI索引文件

InnoDB类型文件：

 以.frm表结构定义文件
 以ibdata1数据文件；多个表对应一个文件

DML语句：

插入单条记录：

  insert  into 表名 （字段名） values 值;

插入多条数据：

  insert into  表名 （字段名，字段名，字段名）values（值，值，值），（值，值，值），...；

插入具有单独条件的数据表记录：

insert into 表名 （字段名） values（字段名的值）；

将查询到的结果集插入新表：

  create table 新表 （select 字段1，...from 原表）；

更新数据记录：

  update 表名 set 字段1=值1，...where 条件；

删除数据：

  delete  from 表名 where条件；自增列不会重置。推荐，有事务数据安全机制。
  truncate table 表名；自增列重置。截断表。不推荐。不受事安全机制的影响。

DQL语句：

查询语法:

  select 列名|表达式|函数|常量|关键字  (列)
  from 表名
  where 查询条件|表达式   （行）
  order by 排序的列名 默认ASC升序排 DESC降序排列 ；

查询全部行和列：

  select * from `表名`;

查询部分列：

  select `列名`
  from`表名`
  where 条件；

使用AS命名列：

  select `studentNo`AS 学生编号 
  from `student`
  where`address`<>'河南'；

distinct关键字：

 去掉select查询返回的记录结果中重复的记录。所偶有返回列都相同的情况下值返回一条。

查询null行：

  select `列名`from `表名`where`列名` is null;
  查询被清空的列名is null 换成：where `列名`='';

如何使用常量列：

  select `studentName` AS 姓名，`address`AS 地址，
  `北京信息中心`AS 学校名称
  from`student`；  

模糊查询行：

like关键字：

  select * from student where studentname   like  '张'
  like关键字若不带任何通配转义字符，则是严格查询。

任意字符%：

select *from student  where studentname lie '张%'

占位任意字符_:

  select *from student where studentname like '张__'

可以连用：

select *from student wher studengname  like '_飞%'

指定转义字符的escape关键字、转移字符:

 select *from student where studentname  like '\%' escape '\\'

in关键字：

 select*from  student where studentresult  in  (80,90,100);

匹配到指定数值就返回
between关键字：

  selecrt * from result where studentResult between  80 and 90;
  在指定范围匹配到数值就返回

常用函数：

聚合函数：常与group by 一起用

   avg（字段名）查询某个字段平均值；
   count（字段名）查询行数；
   max（字段名）查询最大值；
   min（字段名）查询最小值
   sum（字段名）查询和；

字符串函数：

   concat（str1，str2，...）字符串连接。
   insert（str，pos，len，newstr）字符串替换。
   lower（str）字符串转化为小写。
   upper（str）字符串转化为大写。
   substring（str，num，len）字符串截取。

日期函数：

   curdate（）获取当前日期。
   curtime（）获取当前时间。
   now（）获取当前日期和时间。
   week（date）获取date为一年中的第几周。
   year（date）获取date的年份。
   hour（date）获取date的小时值。
   minute（date）获取date的分钟数。
   datediff（date1，date2）获取相隔天数。
   adddate（date，n）计算date加上n天后的日期。

数学函数：

   seil（x）大于或等于x的最小整数。
   floor（x）小于或等于x的最大整数。
   rand（）随机数。

通用函数：

  select version();返回mysql版本号 
  select 100*3f;返回计算结果

order by子句：

order by子句实现按一定顺序显示查询结果,默认为升序，desc为降序。
把成绩都降低10%加5分后查询及格成绩从高到低显示

 select`studentNo`as 学生编号，
         （studentResult*0.9+5）as 综合成绩
  from `result`
  where（`studentResult`*0.9+5）>=60
  order by studentResult desc；

limit子句：

limit[位置偏移量][行数]； 
  从位置偏移量开始显示指定行数数据

简单子查询： 不允许查询多个记录
先执行括号里面的子查询语句再执行外围的父查询语句
括号内子查询结果必须为一。
显示比李思文同学小的学生信息，并显示。

    #父查询语句
    select`studentNo`,`studentName`,`sex`
    from`student`
    where`bornDate`>
    #子查询语句
   （select`bornDate`
    from`student`
    where`studentName`='李思文');

查询”java”课程至少一次考试刚好等于60分的学生。

表连接实现： 适合多表数据

 select`studentName`from`student`stu
 inner join `result`r on               stu.studentNo=r.studentNo
 inner join `subject`subon
 r.subjectNo=sub.subjectNo
 where `studentResult`=60 and               `subjectName`='java'
子查询实现：  将查询嵌套到查询中适合单表查询
 select`studentName`from`student`
 where`studentNo`=
（select `studentNo`from `result`
 inner join`subject`on result.subjectNo=
 subject.subjectNo
 where `studentResult`=60 and 
 `subjectName`='java'）;

in子查询 not in子查询： 允许查询多个记录

 查询"java"课程至少一次考试刚好等于60分的学生。
  将where后的等号换为in即可。
   子查询实现：  将查询嵌套到查询中适合单表查询
     select`studentName`from`student`
     where`studentNo` in
    （select `studentNo`from `result`
     inner join`subject`on result.subjectNo =
     subject.subjectNo
     where `studentResult`=60 and 
     `subjectName`='java'）;
   也可以写为：
     select`studentName`from`student`
     where`studentNo` in(
       select`studentNo`from `result` 
       where `subjectNo`=(
         select`subjectNo`from`subject`
         where`subjectName`='java'
       )and`studentResult`=60
     );

查询参加’java’课程最近一次考试的在读学生。

  #查询出参加'java'课程最近一次考试的在读学生名单
  select`studentNo`,`studentName`from`student`
  where`studetnNO` in(
  #获取参加'java'课程最近一次考试的学生学号
  select`studentNo`from`result`
  where`subjectNo`in(
  #获取'java'课程的课程编号
  select`subjectNo`from`subject`
  where`subjectName`='java'
  )and `examDate`=(
  #获取'java'课程最近一次考试日期
  select max(`examDate`)from`result`
  where`subjectNo`=(
  #再次获取'java'课程的课程编号
  select`subjectNo`from`subject`
  where`subjectName`='java')));

exists子查询：

如何用sql语句检测temp表已经创建：

drop table if exists  temp;

exists用法:

 select 列表 from 表名 where exists（子查询）；
 如果子查询为true执行外部循环
 如果子查询为false不执行外部循环

检查’java’课程最近一次考试；
如果有80分以上成绩则显示排在前5名的学员学号分数。

     #java课程的课程编号
     select subjectNo from subject where   subjectName='java'；
     #获取java最近一次考试日期
     select max(examData) from result where  subjectNo=(  
     select  subjectNo  from  subject  where
     subjectName='java'）；
     #找到成绩达到80分的纪录
     select studentNo,studentResult
     from result
     where subjectNo=(
     select subjectNo from subject where  subjectName='java')
     and examData=(
     select max(examData) from result where subjectNo=(  
     select  subjectNo  from  subject  where
     subjectName='java'）)
     and seudentResult>80;
     #显示java最近一次考试前五名学生信息
     select studentNo,studentResult
     from result
     where exists(
     select studentNo,studentResult
     from result
     where subjectNo=(
     select subjectNo from subject where  subjectName='java')
     and examData=(
     select max(examData) from result where  subjectNo=(  
     select  subjectNo  from  subject  where
     subjectName='java'）)
     and seudentResult>80;)
     and subjectNo=(
     select subjectNo from subject where   subjectName='java')
     and examData=(
     select max(examData) from result where subjectNo=(  
     select  subjectNo  from  subject  where
     subjectName='java'））
     order by studentResult desc
     limit 5;

not exists子查询：
检查java最近一次考试
如果全部未通过考试，则本次考试偏难，计算该次平 均分加五分

     #java课程的课程编号
     select subjectNo from subject where    subjectName='java'；
     #获取java最近一次考试日期
     select max(examData) from result where  subjectNo=(  
     select  subjectNo  from  subject  where
     subjectName='java'）；   
     #找到成绩达到60分的纪录
     select studentNo,studentResult
     from result
     where subjectNo=(
     select subjectNo from subject where   subjectName='java')
     and examData=(
     select max(examData) from result where subjectNo=(  
     select  subjectNo  from  subject  where
     subjectName='java'）)
     and seudentResult>=60;
     #计算平均分并增加5分
     select avg(studentResult)+5 as 平均分
     from result
     where not exists(
     select studentNo,studentResult
     from result
     where subjectNo=(
     select subjectNo from subject where   subjectName='java')
     and examData=(
     select max(examData) from result where subjectNo=(  
     select  subjectNo  from  subject  where
     subjectName='java'）)
     and seudentResult>=60)
     and subjectNo=(
     select subjectNo from subject where    subjectName='java')
     and examData=(
     select max(examData) from result where   subjectNo=(  
     select  subjectNo  from  subject  where
     subjectName='java'））;

分组查询;
group by分组子句查询：
统计每门课程的平均分显示

      select subjectNo,avg(studentResult) as 成绩
      from result
      group by subjectNo;

having分组子句:
统计每门课程的平均分只显示平均分及格的信息

      select subjectNo,avg(studentResult) as 成绩
      from result
      group by subjectNo
      having avg(studentResult)>=60;

where子句来筛选from子句中指定的操作筛选。
group by子句来分组where子句。
having子句用来筛选group by的结果 。

多表连接查询：
内连接查询inner join on：
内连接使用比较运算符根据表中的通用列匹配表中的行。

    select  需要显示的字段
    from 需关联的表1
    innerjoin需关联的表2
    on(表一.相同字段=表二.相同字段);
    inner join 表三
    on （表n.相同字段=表n.相同字段）； 

外链接查询：

    左外连接查询left join  on：
    left join左边的表为主表，右边为从表
    左表显示全部信息，右表中没有左表的信息则为null
    右外连接right join  on：
    right join左边的表为从表，右边的表为主表
    右表显示全部信息，左表中没有右表的信息则为null

事务transation：
向工作单元执行一系列操作来防止出现故障数据不一致。
要么都执行要不都不执行。
必须具备以下ACID属性：

    1.原子性Atomicity：事务各部操作不可分割。
    2.一致性Consistency：事务完成后数据必须一致。
    3.隔离性Isolation：各种并发事务会处于隔离状态。
    4.持久性Durability：事务完成后修改被永久保持。

操作事务：
关闭自动提交功能；
开始事务：

            begin；或者start transation；

提交事务：

            commit；

回滚(撤销)事务：

            rollback；

开启自动提交功能。
从张三的账户转出500元，存入李四的账户中。

   begin；
   update `bank` set `currentMoney`=`currentMoney`-500
      where `customerName`='张三'
   update`bank` set `currentMoney`=`currentMoney`+500
      where `customerName`='李四'
   commit；

关闭/开启自动提交事务：

   set autocommit=0|1；

关闭后需要用commit或者rollback结束该事务。
关闭后提交事务后最好再次开启自动提交。

视图：

虚拟表。筛选表中的行。
 防止未经许可的用户访问敏感数据
 降低操作数据库的复杂程度
 将多个物理数据库抽象为一个逻辑数据库
 可以相互嵌套

语法：
创建视图：

    create view 视图名
    as
    查询语句

删除视图：

    drop view if exists 视图名

查看视图：

     select 字段一，字段二...from视图名

查看所有视图 ：

     use information-schema；
     select*from views

索引：

有效组合数据的方式，快速查找到指定记录。

分类：

    B-树索引。
    哈希索引。

常用索引：

    普通索引：允许索引列中插入重复值和空值。
    唯一索引unique：数据不重复，允许有一个空值。
    主键索引：主键列中非空唯一。主键自动创键。
    复合索引：将多个列组合作为索引。
    全文索引fulltext：支持值的全文查找允许重复和空值，用于char  varchar   text
                     只有MyIsAm类型数据表才支持
                     全文索引要用
             where   match（列名）against（要检索的关键字）；
    空间索引spatial：空间数据类型的列建立的索引。

创建索引：

    在创建表示在字段后边添加
    key`普通索引名 `（`字段一`,`字段二`）,
    index`普通索引名`（`字段一`），

或者创建表后追加

    create   索引类型   index 索引名
    on 表名（索引字段）

删除索引：

    drop index 索引名 on 表名

删除全部索引
例子：
创建学校学生名字索引

    use  myschool；
    create index `index_student_studentName`
    on`student`(`studentName`); 

查看索引：

 show index from 表名\G（按列显示）

创建索引的原则：

    频繁搜索，用作查询，经常排序，分组，连接的列
    不要仅包含几个不同值，仅包含几行就创建索引

数据库备份：

MySQLdump命令–备份工具：将create和insert into语句保存到文本文件。 为dos命令。mysql命令行无法执行

  mysqldump  选项参数  -u 用户名  -h  登录名  -p 密码

需备份的数据库名  需备份的数据库表名 > 备份后的文件名称
省略数据库名和表名则备份全部。

选项参数：

   -add-drop-table：在每个create table前添加drop table。
           默认是打开 ，可以使用-skip-add-drop-table取消。
   --add-locks：锁定表。防止导入时其他用户再进行表操作。
           默认是打开的
   -t或-nocreate-info：只导出数据不添加create  table语句。
   -c或--complete-insert：在每个insert之前加上列名。
   -d或--no-data：不写表的数据，只存储表结构。

数据库恢复：

方法一：mysql -u 用户名  -p 恢复后数据库名  <  文件名。
     此方法为DOS命令。数据库必须存在。
方法二：source 文件名；
     此方法为mysql命令行下使用。必须登录mysql。数据库必须存在并且选择。

导入导出数据：

导出数据语法：

    select*from  导出表的表名  
    where 条件
    into outfile  '导出的文件'  参数(option：使导出文件具有更好可读性。）  导出文件路径为/分割。

导入数据语法：

    load  data  infile  文件名  into table   表名   参数(option：使导出文件具有更好可读性。）