mysql 基本命令

一，DDL语句（数据定义语言）

     DDL 语句指 对数据库内部对象进行创建/删除/修改等操作的语句。

1，创建数据库

     

     create database test1;

2,查看系统中有哪些数据库

  

     show databases;

mysql 自带4个数据库:

             information_schema：主要存储一些数据库对象信息，如用户表信息，列信息，权限信息，字符信息，分区信息等。

            cluster：存储系统集群信息。

            mysql ： 存储系统用户权限信息。

            test ： 系统自动创建的测试数据库，任何用户都可以使用。

3，选择数据库

   

      use test1;

4,查看该数据库下，所有表

   

     show tables;

5， 删除数据库

   

     drop database test1;

6，创建表

   

   create table emp(ename varchar(10),hiredate date,sal decimal(10,2),deptno int(2));

7， 查看表emp

   

    desc emp;

    结果：

    

<pre name="code" class="java">+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename    | varchar(10)   | YES  |     | NULL    |       |
| hiredate | date          | YES  |     | NULL    |       |
| sal      | decimal(10,2) | YES  |     | NULL    |       |
| deptno   | int(2)        | YES  |     | NULL    |       |
+----------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

 8，查看表（得到详细的信息）

   show create table emp \G;

结果：

*************************** 1. row ***************************
       Table: emp
Create Table: CREATE TABLE `emp` (
  `ename` varchar(10) DEFAULT NULL,
  `hiredate` date DEFAULT NULL,
  `sal` decimal(10,2) DEFAULT NULL,
  `deptno` int(2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

ERROR: 
No query specified

其中，\G 的含义是使得记录能够按照字段竖向排列，以便更好的显示内容较长的记录。

9，删除表

  

    drop table emp;

10,修改表中字段

 

   alert table emp  modify ename varchar(20);

  将表emp 中的ename字段修改类型为varchar（20）

11，增加表字段

 alter table emp add column age int(3);

 在表emp中增加列age 类型为int(3)

12，删除表的某列

   alter table emp drop column age;

 将表emp中 的age列删除

13，字段改名

   alter table emp change age age1 int(4);

将age 改名为age1,同时修改类型为int(4)

注：change 和modify 都可以修改表的定义，不同的是change后面需要写两次列明，不方便，但是change的优点是可以修改列的名称，而modify则不可以。

 14，修改字段排列顺序

     add ,change ,modify 语法都可以有一个选项first ,after ，这个选项用来修改字段在表中的位置

使用事例：

     

   alert table emp add birth date after ename;

 

   alter table emp modify birth date first;

15 ，更改表名

   alert table emp rename emp1;

二， DML语句（数据操纵语言）

    DML语句是指对数据库中表记录的操作，主要包括表记录的插入，更新，删除，查询。他与DDL语句最大的区别是：DML只是对表内部进行操作，不涉及表的定义，结构的改变，更不会涉及其他对象。

1,插入语句

 

    insert into emp(ename,hiredate,sal,deptno)values('fzy','2016-09-21','700',1);

     

         一次插入两条数据：

    insert into emp(ename,hiredate,sal,deptno)values('fzy1','2016-09-21','800',2),('fzy2','2016-09-21','900',3);

2，查询

        （1） 查询，去掉重复数据：

    select distinct  hiredate from emp;

        （2） 查询，排序，限制：

                      先按deptno升序排列，如果deptno相同，则按sal降序排列，并限制前两条数据

     select ename,hiredate,sal,deptno from emp order by deptno,sal desc limit 2;

       

        （3）查询，聚合

            常用的聚合函数有：sum,count,min,max

            聚合时常用到的一些关键字：  group by 关键字表示要进行分类聚合的字段，with  rollup 表明是否对分类聚合后的结果再进行汇总，having表示对分类后的结果再进行过滤。

         实例：

            *  按deptno分类统计人数:

         select deptno,count(1) from emp group by deptno;

               结果：

 

+--------+----------+
| deptno | count(1) |
+--------+----------+
|      1 |        2 |
|      2 |        1 |
|      3 |        1 |
+--------+----------+
3 rows in set (0.00 sec)

          * 按deptno分类统计人数，并统计总人数

    

           select deptno,count(1) from emp group by deptno with rollup;

                 结果：

+--------+----------+
| deptno | count(1) |
+--------+----------+
|      1 |        2 |
|      2 |        1 |
|      3 |        1 |
|   NULL |        4 |
+--------+----------+
4 rows in set (0.00 sec)

       * 按deptno分类，找出人数大于一的

  

      select deptno,count(1) from emp group by deptno having count(1)>1;

      * 查询sal字段最大值，最小值，和sal总和

 

      select sum(sal),max(sal),min(sal) from emp;

           （4）查询，连接

                 连接分为内连接和外连接，他们主要的区别是，内连接仅选出两张表中相互匹配的记录，而外连接会选出其他不匹配的记录。

             *内连接

                

         select a.ename,b.deptname,a.deptno from emp a,dept b where a.deptno=b.deptno;

               或

        

         select a.ename,b.deptname,a.deptno from emp a join dept b on a.deptno=b.deptno;

               结果：

             

+-------+----------+--------+
| ename | deptname | deptno |
+-------+----------+--------+
| fzy1  | fzy1     |      2 |
| fzy2  | fzy2     |      3 |
+-------+----------+--------+
2 rows in set (0.00 sec)

            *  外连接

                 左连接：包含所有左边表中的记录甚至是右边表中没有的和他匹配的记录

          

           select a.ename,b.deptname,a.deptno from emp a left join dept b on a.deptno=b.deptno;

                结果：

+-------+----------+--------+
| ename | deptname | deptno |
+-------+----------+--------+
| fzy1  | fzy1     |      2 |
| fzy2  | fzy2     |      3 |
| fzy   | NULL     |      1 |
| fzy3  | NULL     |      1 |
+-------+----------+--------+
4 rows in set (0.00 sec)

               *  右连接：包含所有右边表中的记录甚至是右边表中没有的和他匹配的记录

                     

             select a.ename,b.deptname,a.deptno from emp a right join dept b on a.deptno=b.deptno;

                      结果：

+-------+----------+--------+
| ename | deptname | deptno |
+-------+----------+--------+
| fzy1  | fzy1     |      2 |
| fzy2  | fzy2     |      3 |
+-------+----------+--------+
2 rows in set (0.00 sec)

                       

            （5）查询，子查询

                    在某些情况下，当进行查询的时候，需要的条件是另外一个select语句的结果，这个时候就要用到子查询。

                   在子查询中，常用的关键子有：in, not in, =, !=, exists, not exists

                 * in, not in , =  , !=   

         select ename,hiredate,sal,deptno from emp where deptno in(select deptno from dept);

       注：在子查询中如果记录数唯一，可以用=代替in ，但一般情况下，即使明确知道了只用一个记录，我们也通常直接使用in而不用=

            

                 *  exists, not exists

         select a.ename,a.hiredate,a.sal,a.deptno from emp a where exists(select b.deptno from dept b where b.deptno=a.deptno);

                 

                exists 用于检查子查询是否至少返回一行数据，该子查询实际上并不返回任何数据，而是返回值true或false

 

             (6) 查询，记录联合

                  将两个表数据按照一定的查询条件查询出来后，将结果合并到一起显示出来，这是用到union 和union all 。

                  union 和union all的主要区别是union all 把结果集直接合并在一起，而union 是将union all后的结果进行一次distinct去重操作。

              

          select ename from emp union all select deptno from dept;

                  结果：

 

+-------+
| ename |
+-------+
| fzy   |
| fzy1  |
| fzy2  |
| fzy3  |
| 2     |
| 3     |
+-------+
6 rows in set (0.00 sec)

3，更新

 

  同时更新两张表中数据

    update emp a,dept b set a.sal=a.sal*b.deptno,b.deptname=a.ename where a.deptno=b.deptno;

4，删除

 

        删除两张表中的数据：

    delete a,b from dept a,emp b where a.deptno=b.deptno and a.deptno=3;

         注：当我们删除一张表数据的时候，我们通常将delete 后的表名给省略，但当我们删除两张表的数据的时候，我们不能将上述的delete a,b 中的a,b 省略。

 

三，DCL语句

        DCL语句主要是DBA用来管理系统中的对象权限时使用，一般开发人员很少使用。

       （暂时不学习）

 

四，帮助命令的简单介绍

  1，按层次查看

   首先，如果不知道帮助能够提供些什么，我们可以用命令来"? contents"来显示所有可供查询的分类

   

      ? contents;

     结果：

      

You asked for help about help category: "Contents"
For more information, type 'help <item>', where <item> is one of the following
categories:
   Account Management
   Administration
   Compound Statements
   Data Definition
   Data Manipulation
   Data Types
   Functions
   Functions and Modifiers for Use with GROUP BY
   Geographic Features
   Help Metadata
   Language Structure
   Plugins
   Procedures
   Storage Engines
   Table Maintenance
   Transactions
   User-Defined Functions
   Utility

    然后，我们看到了上述各种分类，我们依然可以用 "? 类别"的方式接着查看

            如：我们查看Data Types项

              

        ? Data Types;

         这样我们就能看到各种数据类型了：

     

You asked for help about help category: "Data Types"
For more information, type 'help <item>', where <item> is one of the following
topics:
   AUTO_INCREMENT
   BIGINT
   BINARY
   BIT
   BLOB
   BLOB DATA TYPE
   BOOLEAN
   CHAR
   CHAR BYTE
   DATE
   DATETIME
   DEC
   DECIMAL
   DOUBLE
   DOUBLE PRECISION
   ENUM
   FLOAT
   INT
   INTEGER
   LONGBLOB
   LONGTEXT
   MEDIUMBLOB
   MEDIUMINT
   MEDIUMTEXT
   SET DATA TYPE
   SMALLINT
   TEXT
   TIME
   TIMESTAMP
   TINYBLOB
   TINYINT
   TINYTEXT
   VARBINARY
   VARCHAR
   YEAR DATA TYPE

             接着如果想知道某个具体数据类型的介绍，可以再按上述方式，如我们查看INT:

        ? INT;

             结果：

         

Name: 'INT'
Description:
INT[(M)] [UNSIGNED] [ZEROFILL]

A normal-size integer. The signed range is -2147483648 to 2147483647.
The unsigned range is 0 to 4294967295.

URL: http://dev.mysql.com/doc/refman/5.7/en/numeric-type-overview.html

          2，快速查看

           直接输入关键字即可，如：

             

         ? select;

  

         ? create table;

五，查询元数据信息

     在mysql数据库的自带数据库中，有一个数据库名为information_schema,这个数据库用来记录mysql中的元数据信息，像 表名，列名，列类型，索引名等各种属性名称都属于元数据。这个数据库比较特殊，他是一个虚拟的数据库，物理上并不存相关的目录文件，里面的表也不是实际存在的物理表，而全是视图。下面我们举两个例子来体会一下其作用吧。

      1，删除数据库test1下所有前缀为tmp的表

     

      select concat('drop table test1.',table_name,';') from tables where table_schema='test1' and table_name like 'tmp%';

     2,将数据库test1下所有的存储引擎为myisam的表改为innodb

         

     select concat('alter table test1.',table_name,'engine=innodb;') from tables where table_schema='test1' and engine='MyISAM';

 

    一些常用的视图：

           schemata ：该表提供了当前mysql实例中所有数据库信息，我们可通过命令show databases看到，即schemata为show databases的结果。

           tables： 该表提供了关于数据库中表的信息（包括视图），详细表述累某个表属于那个shemata，表类型，表引擎，创建时间。由show tables from schemaname得到。

          columns：该表提供了表中列信息，详细表述了某张表的所有列及每列信息。由show columns from schemaname.tablename得到。

          statistics：该表提供了关于表索引的信息，由show index from schemaname.tablename取到。