【MySQL常用命令总结】

本文主要是介绍【MySQL常用命令总结】，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

在mysql客户端执行的mysql命令：

1.使用值“where”过滤显示选定的某些行。

SELECT * FROM [table name] WHERE [field name] = "whatever";

显示所有包含name为”Bob”和phone number为“3444444”的记录。

SELECT * FROM [table name] WHERE name = "Bob" AND phone_number = '3444444';

2.使用like匹配。
显示所有的name以字母“bob”开头和phone number为“3444444”的记录。

SELECT * FROM [table name] WHERE name like "Bob%" AND phone_number = '3444444';

3.查询指定的几条记录。
显示name以字母“bob”开头和phone number为“3444444”的第1至第5条记录。

SELECT * FROM [table name] WHERE name like "Bob%" AND phone_number = '3444444' limit 1,5;

4.使用正则表达式查找记录。

使用“正则表达式二进制”强制区分大小写。此命令查找以a开头的任何记录。

SELECT * FROM [table name] WHERE rec RLIKE "^a";

5.以升序或降序显示选定的记录。默认按升序，降序用desc

SELECT [col1],[col2] FROM [table name] ORDER BY [col2] DESC;

6.返回唯一不同的记录。

SELECT DISTINCT [column name] FROM [table name];

7.统计指定列的数量。

SELECT COUNT(*) FROM [table name];

SELECT COUNT(NAME) FROM dt_areaconfig;

8.统计指定列值的总和。

SELECT SUM(*) FROM [table name];

9.查询表里数据是否有重复。

例：查询tb_dt_area_config表里name,code两列的值是否有重复

SELECT * FROM tb_dt_area_config WHERE (CODE,NAME) IN(SELECT CODE,NAME FROM tb_dt_area_config GROUP BY CODE,NAME HAVING COUNT(*)>1);

10.查询在A表但不在B表的数据。

例：查询新增产品类型数据在digital_inspection_device.tb_dt_product_for_sealer表但不在digital_inspection_device.tb_product_type表

SELECT * FROM tb_dt_product_for_sealer WHERE NAME NOT IN (SELECT tb_product_type.NAME FROM tb_product_type WHERE tb_dt_product_for_sealer.NAME=tb_product_type.NAME);

11.联表查询

11.1概念：

在SQL标准中规划的（Join）联结大致分为下面四种：
1.内联结：将两个表中存在联结关系的字段符合联结关系的那些记录形成记录集的联结。

内连接查询使用关键字join或cross join 或 inner join, 然后通过on连接表与表之间的条件

语法:

SELECT 字段,字段1,..
FROM table_name1
INNER JOIN table_name2 
ON table_name1.column_name=table_name2.column_name

2.外联结：分为外左联结和外右联结。
左联结A、B表的意思就是将表A中的全部记录和  表B中联结的字段与表A的联结字段符合联结条件的那些记录   形成的记录集的联结，这里注意的是最后出来的记录集会包括表A的全部记录。
右联结A、B表的结果和左联结B、A的结果是一样的，也就是说：Select A.name B.name From A Left Join B On A.id=B.id 和 Select A.name B.name From B Right Join A on B.id=A.id的结果是一样的

内外联结的区别是内联结将去除所有不符合条件的记录，而外联结则保留其中部分。外左联结与外右联结的区别在于如果用A左联 结B则A中所有记录都会保留在结果中，此时B中只有符合联结条件的记录，而右联结相反。

左外连接使用关键字left join,然后通过on连接表与表之间的条件

注意：left join 会查询出left join左边的表所有的数据，即使右表没有匹配
语法：

  SELECT 字段,字段1,...
    FROM table_name1
    LEFT JOIN table_name2 
    ON table_name1.column_name=table_name2.column_name

右外连接使用关键字right join,然后通过on连接表与表之间的条件
注意：  即使左表中没有匹配，也从右表返回所有的行
语法:

SELECT 字段,字段1,....
    FROM table_name1
    RIGHT JOIN table_name2 
    ON table_name1.column_name=table_name2.column_name

3.全联结：将两个表中存在联结关系的字段的所有记录取出形成记录集的联结。

显示左表(TESTA表)的全部记录和右表(TESTB表)的全部记录，两个表中没有的数据显示为NULL。

语法：

SELECT 字段,字段1,....
    FROM table_name1
    FULL  JOIN table_name2 
    ON table_name1.column_name=table_name2.column_name;

4.无联结：没有使用联结功能，也有自联结的说法。

11.2 MySQL子查询：
子查询就是把一个查询嵌套在另一个查询中。

子查询可以包含普通select可以包括任何子句，比如：distinct,group by, order by,limit,join等

子查询先执行里面的SQL语句，再执行外面SQL语句；
子查询的效率比较低，一般建议使用join替换子查询；
子查询时，MySQL需要为内层查询语句的查询结果建立一个临时表，然后再外层查询语句，可参考例3；

11.3联表查询中用到的一些参数

1.USING (column_list)：
其作用是为了方便书写联结的多对应关系，大部分情况下USING语句可以用ON语句来代替，如下面例子：
a LEFT JOIN b USING (c1,c2,c3)，其作用相当于
a LEFT JOIN b ON a.c1=b.c1 AND a.c2=b.c2 AND a.c3=b.c3

2.STRAIGHT_JOIN：
由于默认情况下MySQL在进行表的联结的时候会先读入左表，当使用了这个参数后MySQL将会先读入右表，这是个MySQL的内置优化参数，大家应该在特定情况下使用，譬如已经确认右表中的记录数量少，在筛选后能大大提高查询速度。

11.4 联表查询举例

例1：

SELECT COUNT(1) FROM (SELECT IFNULL(tr.id, '') AS regionId,
               IFNULL(tpt.id, '') AS productTypeId,
               IFNULL(tp.sale_area, '') AS regionName,
               IFNULL(tpt.name, '') AS productTypeName,
               COUNT(1) total
        FROM tb_product tp
        INNER JOIN `tb_product_type` tpt ON tpt.id = tp.product_type_id
        INNER JOIN `tb_region` tr ON tr.name = tp.sale_area
        WHERE tp.create_time  >=  startTime
          AND tp.create_time < endTime
        GROUP BY regionId, productTypeId, regionName, productTypeName) AS temp;

例2-例6引用自  mysql数据库之联表查询 - 希希大队长 - 博客园 (cnblogs.com)

例2：查询所有的课程的名称以及对应的任课老师姓名

分析需求：我们需要用到course和teacher表：既需要得到课程名称又要拿到老师姓名，然后看表结构模型，我们可以知道course有外键字段teacher_id指向teacher表id，那么我们就可以用内连接inner join将两张表拼接起来然后取其字段course.cname和teacher.tname即可得到我们想要的数据，SQL语句如下：

SELECT cname,tname FROM teacher INNER JOIN course ON course.teacher_id = teacher.tid;

例3:查询平均成绩大于八十分的同学的姓名和平均成绩

需求分析：我们需要用到score表和shtudent表，既要拿到学生姓名又要拿到成绩，我们理所当然需要将这两个表联表或者做子连接，然后需求中需要用到平均数，那么我们应想到用聚合函数avg（），但使用聚合函数的前提是分组(不人为分组时默认整个表就是一个组) group by，下面我们来写sql语句：

首先在联表或子连接前可以通过score表分组得到student_id和平均成绩：

select student_id,avg(num) as avg_score from score group by student_id having avg(num) >80;

然后在以上虚拟表的基础上通过student_id拼接student表，取student.sname和avg_score即可

SELECT student.sname,k.avg_score FROM student
    INNER JOIN ( SELECT student_id, avg( num ) AS avg_score FROM score GROUP BY student_id HAVING avg( num ) > 80 ) AS k ON k.student_id = student.sid;

例4：查询没有报李平老师课的学生姓名

需求分析：我们根据表结构得知我们需要用到student，score，course，teacher这4张表，直接得到没有报李平老师课程的学生比较困难，那么我们就反过来想，哪些是报了李平老师课程的，然后在学生表里剔除掉即可：

首先我们可以先得到李平老师教了哪几门课，用course和teacher联表：

select course.cid,course.cname from course inner join teacher on course.teacher_id = teacher.tid where teacher.tname = "李平老师";

根据表结构我们可以知道，course表和score表通过外键连接，那么我们就可以把上面得到的虚拟表和score表子查询，取字段score.student_id即可得到所有选了李平老师课程的学生id，然后根据student_id分组或去重就可以得到不重名的学生id选了李平老师课程的虚拟表：

select score.student_id from score where course_id in (

select course.cid from course inner join teacher on course.teacher_id = teacher.tid where teacher.tname = "李平老师"
) ;

然后将上面得到的虚拟表与student表做子连接的条件得到选了李平老师课程的学生姓名，然后我们not in即可得到需求

select sname from student where sid not  in (

select score.student_id from score where course_id in (

select course.cid from course inner join teacher on course.teacher_id = teacher.tid where teacher.tname = "李平老师"
) 
);

例5：查询挂科超过两门(包括两门)的学生姓名和班级

需求分析：这个需求和第四个需求类似，我们可以先拿到所有挂科的学生id，然后进行分组，筛选出挂科数大于等于2的一部分学生id，然后把取到的id作为student表的查询条件去取学生姓名即可，sql语句如下：

SELECT
    caption,
    k.sname 
FROM
    class
    INNER JOIN (
    SELECT
        sname,
        class_id 
    FROM
        student 
    WHERE
        sid IN ( SELECT student_id FROM score WHERE num < 60 GROUP BY student_id HAVING count( student_id ) >= 2 ) 
    ) k ON k.class_id = class.cid;

例6、找出同时选了李平老师所有课的学生班级和姓名

需求分析：需要用到班级和老师，那么5张表都要用到，我们还是套用前面的方法，先通过teacher表找到李平老师的id，通过这个id在course表里找到李平老师的课程表的id，然后拿这个id去score表里找选了李平老师课程的student_id，这时候我们要对student_id进行分组了，筛选出含2个以上的student_id，再通过得到的student_id去student_表里找到对应的姓名和class_id，然后将生成的虚拟表去和class表联表，取出class.cname和student.sname即是我们想要的数据，sql语句如下：

SELECT
    class.caption,
    n.sname 
FROM
    class
    INNER JOIN (
    SELECT
        class_id,
        sname 
    FROM
        student 
    WHERE
        sid IN (
        SELECT
            student_id 
        FROM
            (
            SELECT
                student_id,
                course_id 
            FROM
                score 
            WHERE
                course_id IN ( SELECT course.cid FROM teacher INNER JOIN course ON course.teacher_id = teacher.tid WHERE teacher.tid = 2 ) 
            ) AS k 
        GROUP BY
            student_id 
        HAVING
            count( student_id ) = 2 
        ) 
    ) AS n ON class.cid = n.class_id ORDER BY n.sname;

12.更新已存在表的数据。

UPDATE [table name] SET Select_priv = 'Y',Insert_priv = 'Y',Update_priv = 'Y' where [field name] = 'user';

13.删除指定行

删除表中[field name] = ‘whatever’的行。

DELETE from [table name] where [field name] = 'whatever';

14.新增列到db

alter table [table name] add column [new column name] varchar (20);

15.更改列名

alter table [table name] change [old column name] [new column name] varchar (50);

这篇关于【MySQL常用命令总结】的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！