MySQL使用

一、SQL分类

    1.DDL(Data Definition Languages)语句：数据定义语言，这些语句定义了不同的数据段、数据库、表、列、索引等数据库对象。常用的语句关键字主要包括：create、drop、alter等。

    2.DML(Data Control Language)语句：数据操纵语句，用于添加、删除、更新和查询数据库记录，并检查数据完整性。常用的语句关键主要包括insert、delete、update和select等

    3.DCL(Data Control Language)语句：数据控制语句，用于控制不同数据段直接的许可和访问级别的语句。这些语句定义了数据库、表、字段、用户的访问权限和安全级别。主要的语句关键字包括grant、revoke等。

二、DDL语句

    1.创建数据库

        create database dbname

        mysql> create database test1;

        Query OK, 1 row affected (0.01 sec)

      查系统中有哪些数据库

        show databases;

        mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mxonline           |
| mysql              |
| performance_schema |
| sakila             |
| sys                |
| test1              |
| world              |
+--------------------+

8 rows in set (0.00 sec)

      选择系统的数据库进行使用

    use dbname;

mysql> use test1;

Database changed

      选中test1数据库，进而查看test1数据库中创建的所有数据表

mysql> show tables;

Empty set (0.00 sec)

    2.删除数据库

    drop databases dbname;

mysql> Database changed

    -> mysql> show tables;

    3.创建表

    create table tablename(

        column_name column_type_1 constraints,

        .......)

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

Query OK, 0 rows affected (0.29 sec)

        查看表的定义

        desc tablename;

mysql> desc emp;
+----------+---------------+------+-----+---------+-------+
| 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.01 sec)

查看创建表的SQL语句

show creata table emp \G;

mysql> 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=utf8
1 row in set (0.00 sec)


ERROR:

No query specified

    4.删除表   

      drop table tablename;

drop table emp;

    5.修改表

        5.1修改表类型

            alter table tablename modify[column] column_defination[first|after col_name]

            修改表emp的ename字段定义，将varchar(10)改为varchar(20);

            

            5.2、增加表字段，语句如下：

                alter table tablename add[column] column_defination[first|after col_name]

                在表emp中增加字段age，类型为int(3)

                

            5.3、删除表字段，语法如下：

                alter table tablename drop[column] clo_name

                

            5.4、字段改名

                alter table tablename change[column] old_col_name column_defination[first|after col_name

                将age改名为 age1，同时修改字段类型为int(4)

                

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

                5.5修改字段排列顺序

                将新增的字段birth date加在ename之后

                

                修改字段age1，将他放在最前面：

                

                5.6、更改表名

                alter table tablename rename[to] new_tablename

                

    三、DML语句

    DML操作是指对数据库中表记录的操作，主要包括记录的插入（insert）、更新（update）、删除（delete）和查询（select）,是开发人员日常使用最频繁的操作。

    1.插入记录

    insert into tablename(field1,field2...)values(values1,values2)

    

     

     

    查看实际插入值

     

        

        可一次性插入多条记录

       

    2.更新记录

    update tablename set field1= value1,field2 = value2.....[where condition]

    将emp表中ename为‘lisa’的薪水（sal）从3000更改为4000：

    

    在mysql中，update命令可以同时更新多个表中的数据

    update t1,t2,...tn set t1.field=expr1 [ where condition]

    

    

    *多表更新的语法更多滴用在了根据一个表的字段来动态地更新另外一个表的字段。

    3.删除记录

    delete from tablename [where condition]

    删除ename为tom的记录

    

    在mysql中可以一次删除多个表的数据

    delete t1,t2,.....from t1,t2,....[where condition]

    同时删除表emp和dept中deptno为2的记录

    

    

    *不管是单表还是多表，不加where条件将会把表的所有记录删除，所以操作时一定要小心。

    4.查询记录

    select * from tablename [where condition]

    其中，“*”表示要将所有的记录都选出来。

        4.1查询不重复记录

            

        4.2多条件查询

            

            

        4.3、排序和限制

            语法：select * from tablename[where condition] [order by field1 [desc|asc],field2 [desc|asc].....]

            desc降序排列，asc升序排列。不此关键字，则默认为 升序排列。

            order by 后面可跟多个不同的字段，并且每个排序字段可以有不同的排序。如果排序字段的值一样，则值相同的字段按照第二个排序字段进行排序，依次类推。

            eg：按工资高低进行显示

            

            部门相同时，则按工资降序排列

            

            对于排序后的记录，如果希望只显示一部分，而不是全部，可以使用limit关键字实现。

            语法：select ....[limit offset_start,row_count]         

                        offset_start表示记录的起始偏移量     row_count表示显示的行数。默认情况下，起始偏移量为0，只需写记录

                                                                            行数就可以。

            eg：emp表中按照sal排序后的前2条记录

            

            eg:显示emp表中按照sal排序后从第二条记录开始的2条记录。

            

        4.4、聚合

            语法：

            select [field1,field2,....] fun_name

            from tablename 

            [where where condition]

            [group by field1,field2.........]

            [with rollup]

            [having where_condition]

            fun_name表示要做的聚合操作，也就是聚合函数，常用的有sum、count(*)(记录数)、max、min.

            group by 关键字表示要进行分类聚合的字段，比如要按部门分类统计员工数量，部门就应该写在group by 后面。

            with rollup是可选语法，表明是否对分类聚合后的结果进行再汇总。

            having 关键字表示对分类后的结果再进行条件的过滤。

            ***having和where区别在于，having是对聚合后的结果进行条件的过滤，而where是在聚合前就对记录进行过滤，如果逻辑允许，尽可能用where先过滤记录，这样因为结果集减小，将对聚合的效率大大提高，最后再根据逻辑看是否用having进行再过滤。

            eg：在emp中统计公司总人数

            

            eg：在此基础上，统计各部门的人数

            

            eg：既要统计各部门人数，又要统计总人数

            

            eg：统计人数大于1的部门

            

            eg：最后统计公司所有员工的薪水总额、最高和最低薪水

            

        4.5、表连接

            当需要同时显示多个表中的字段时，就可以用表连接来实现这样的功能。

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

            eg：查询所有雇员的名字和所在的部门名称。

            

            外连接又分为左连接和右 连接。

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

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

            eg：查询emp中所有用户名和所在部门名称

            左连

            

            右连

            

        4.6、子查询

            用于子查询的关键字主要包括in、not in、=、!=、exists、not exists

            eg:从emp表中查询出所有部门在dept表中的所有记录

            

            如果子查询记录数唯一，可以用=代替in

            

            某些情况下，子查询可转化为表连接。

            

            **子查询和表连接之间的转换主要应用在两个方面

                1.MySql4.1以前的版本不支持子查询，需要用表连接来实现子查询的功能。

                2.表连接在很多情况下用于优化子查询。

        4.7、记录联合

            将两个表的数据按照一定的查询条件查询出来后，将结果合并到一起显示出来，这个时候，就需要用union和union all关键字来实现这样的功能。

            语法：

            select * from t1

            union|union all

            select * from t2....;

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

            eg：将emp和dept表中的部门编号的集合显示出来

            

            将结果去掉重复记录后显示

            

        5.DCL语句：

                 创建一个数据库用户za，具有对test数据库中所有表的select/insert权限。

                

        6.帮助的使用 

            ？contents

            ?show

        7.查询元数据信息

            日常工作中，会遇到类似下面的场景

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

            2.将数据库test1下所有存储殷勤为myisam的表改为innodb

        mysql5.0后提供了一个新的数据库information_schema，用来记录mysql中的元数据信息

        对于上面的两个需求，语句如下：

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

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