MySQL基础语法、以及练习

作者: 王王王王王景 | 来源:发表于2019-07-26 16:09 被阅读0次

一、基本语法

创建数据库

(test为我们创建数据库的名称)

create database test;

查看数据库

show databases;

选择数据库

use test;

创建表

create table if not exists mytable (
    id int unsigned primary key auto_increment,
    title varchar(100) not null,
    author varchar(40) not null,
    submission_date date
) default charset=utf8;

列出所有表

show tables;

查看某一个表的结构

desc/describe mytable;

mysql> desc mytable;
+-----------------+------------------+------+-----+---------+----------------+
| Field           | Type             | Null | Key | Default | Extra          |
+-----------------+------------------+------+-----+---------+----------------+
| id              | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| title           | varchar(100)     | NO   |     | NULL    |                |
| author          | varchar(40)      | NO   |     | NULL    |                |
| submission_date | date             | YES  |     | NULL    |                |
+-----------------+------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

删除表（数据）

drop table mytable;   # 直接删除表数据以及表
truncate table mytable;  #  truncate(截断),只删除表数据，不删除表
delete from table [where ...];  # 按照条件对表中的数据进行删除

插入数据(增)

insert mytable(title, author, submission_date) values("MySQL学习", "jing.wang", NOW())
...

mysql> select * from mytable;
+----+------------+-----------+-----------------+
| id | title      | author    | submission_date |
+----+------------+-----------+-----------------+
|  1 | MySQL学习  | jing.wang | 2019-07-26      |
|  2 | 计算机网络 | pei.yang  | 2019-07-26      |
|  3 | 数据结构   | jing.wang | 2019-07-26      |
+----+------------+-----------+-----------------+

删除数据(删)

delete from mytable where author='author_name';

修改数据(改)

update mytable set title='test_titile' where title='MySQL学习';

LIKE子句

mysql> select * from mytable where author like '%wang';
+----+-----------+-----------+-----------------+
| id | title     | author    | submission_date |
+----+-----------+-----------+-----------------+
|  1 | MySQL学习 | jing.wang | 2019-07-26      |
|  3 | 数据结构  | jing.wang | 2019-07-26      |
+----+-----------+-----------+-----------------+
2 rows in set (0.00 sec)


'%a'     //以a结尾的数据
'a%'     //以a开头的数据
'%a%'    //含有a的数据
'_a_'    //三位且中间字母是a的
'_a'     //两位且结尾字母是a的
'a_'     //两位且开头字母是a的

排序

mysql> select * from mytable order by author;
+----+------------+-----------+-----------------+
| id | title      | author    | submission_date |
+----+------------+-----------+-----------------+
|  1 | MySQL学习  | jing.wang | 2019-07-26      |
|  3 | 数据结构   | jing.wang | 2019-07-26      |
|  2 | 计算机网络 | pei.yang  | 2019-07-26      |
+----+------------+-----------+-----------------+

分组

mysql> select author,count(*) from mytable group by author;
+-----------+----------+
| author    | count(*) |
+-----------+----------+
| jing.wang |        2 |
| pei.yang  |        1 |
+-----------+----------+
2 rows in set (0.00 sec)

某员工表staff如下所示：

 id    name  dept  salary  edlevel   hiredate 
  1    张三  开发部  2000     3      2009-10-11
  2    李四  开发部  2500     3      2009-10-01
  3    王五  设计部  2600     5      2010-10-02
  4    王六  设计部  2300     4      2010-10-03
  5    马七  设计部  2100     4      2010-10-06
  6    赵八  销售部  3000     5      2010-10-05
  7    钱九  销售部  3100     7      2010-10-07
  8    孙十  销售部  3500     7      2010-10-06

列出每个部门最高薪水的结果，sql语句如下：

select dept, max(salary) as MAXIUM from staff group by dept;

查询结果如下：
dept       MAXIMUM
开发部      4500
设计部      2600
销售部      3500

将where子句与group by子句一起使用
分组查询可以在形成组和计算列函数之前具有消除非限定行的标准where子句。必须在group by子句之前指定where子句
查询公司2010年入职的各个部门每个级别里的最高薪水

select dept, edlevel, max(salary) 
from staff 
where hiredate > ' 2010-01-01 '
group by dept, edlevel;


查询结果如下：
dept     edlevel     MAXIMUM
设计部      4         2300
设计部      5         2600
销售部      5         3000
销售部      7         3500

GROUP BY子句之后使用Having子句
可应用限定条件进行分组，以便系统仅对满足条件的组返回结果。因此，在GROUP BY子句后面包含了一个HAVING子句。HAVING类似于WHERE（唯一的差别是WHERE过滤行，HAVING过滤组）AVING支持所有WHERE操作符。
例如，查找雇员数超过2个的部门的最高和最低薪水：

select dept, max(salary), min(salary) 
from staff
group by dept
having count(*) > 2
order by dept;

查询结果如下：
dept     MAXIMUM       MINIMUM
设计部      2600       2100
销售部     3500        3000

查找雇员平均工资大于3000的部门的最高薪水和最低薪水：

select dept, max(salary), min(salary) 
from staff
group by dept
having avg(salary) > 3000
order by dept;

查询结果如下：
dept    MAXIMUM   MINIMUM
销售部          3500       3000

在使用group by的过程中必须注意的一点

在select指定的字段要么就要包含在Group By语句的后面，作为分组的依据；要么就要被包含在聚合函数中。

select dept, name, max(salary) as MAXIUM from staff group by dept; (错误的)
其中name不属于group by分组的条件，也不属于聚合函数max等的参数，因此select不能使用name这个属性

Having与Where的区别

where 子句的作用是在对查询结果进行分组前，将不符合where条件的行去掉，即在分组之前过滤数据，where条件中不能包含聚组函数，使用where条件过滤出特定的行。
having 子句的作用是筛选满足条件的组，即在分组之后过滤数据，条件中经常包含聚组函数，使用having 条件过滤出特定的组，也可以使用多个分组标准进行分组。

join用法

JOIN 按照功能大致分为如下三类：

INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录。[默认的join]
LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。
RIGHT JOIN（右连接）：与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。

INNER JOIN

LEFT JOIN

RIGHT JOIN

当前有两张表：

mysql> use RUNOOB;
Database changed
mysql> SELECT * FROM tcount_tbl;
+---------------+--------------+
| runoob_author | runoob_count |
+---------------+--------------+
| 菜鸟教程       | 10           |
| RUNOOB.COM    | 20           |
| Google        | 22           |
+---------------+--------------+
3 rows in set (0.01 sec)
 
mysql> SELECT * from runoob_tbl;
+-----------+---------------+---------------+-----------------+
| runoob_id | runoob_title  | runoob_author | submission_date |
+-----------+---------------+---------------+-----------------+
| 1         | 学习 PHP    | 菜鸟教程         | 2017-04-12      |
| 2         | 学习 MySQL  | 菜鸟教程         | 2017-04-12      |
| 3         | 学习 Java   | RUNOOB.COM      | 2015-05-01      |
| 4         | 学习 Python | RUNOOB.COM      | 2016-03-06      |
| 5         | 学习 C      | FK              | 2017-04-05      |
+-----------+---------------+---------------+-----------------+
5 rows in set (0.01 sec)

INNER JOIN

mysql> SELECT a.runoob_id, a.runoob_author, b.runoob_count 
FROM runoob_tbl a INNER JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;
+-------------+-----------------+----------------+
| a.runoob_id | a.runoob_author | b.runoob_count |
+-------------+-----------------+----------------+
| 1           | 菜鸟教程    | 10             |
| 2           | 菜鸟教程    | 10             |
| 3           | RUNOOB.COM      | 20             |
| 4           | RUNOOB.COM      | 20             |
+-------------+-----------------+----------------+
4 rows in set (0.00 sec)

LEFT JOIN

mysql> SELECT a.runoob_id, a.runoob_author, b.runoob_count 
FROM runoob_tbl a LEFT JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;
+-------------+-----------------+----------------+
| a.runoob_id | a.runoob_author | b.runoob_count |
+-------------+-----------------+----------------+
| 1           | 菜鸟教程    | 10             |
| 2           | 菜鸟教程    | 10             |
| 3           | RUNOOB.COM      | 20             |
| 4           | RUNOOB.COM      | 20             |
| 5           | FK              | NULL           |
+-------------+-----------------+----------------+
5 rows in set (0.01 sec)

RIGHT JOIN

mysql> SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a RIGHT JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;
+-------------+-----------------+----------------+
| a.runoob_id | a.runoob_author | b.runoob_count |
+-------------+-----------------+----------------+
| 1           | 菜鸟教程    | 10             |
| 2           | 菜鸟教程    | 10             |
| 3           | RUNOOB.COM      | 20             |
| 4           | RUNOOB.COM      | 20             |
| NULL        | NULL            | 22             |
+-------------+-----------------+----------------+
5 rows in set (0.01 sec)

二、SQL语句50道题目练习

https://blog.csdn.net/fashion2014/article/details/78826299/

--建表
--学生表
CREATE TABLE `Student`(
    `s_id` VARCHAR(20),
    `s_name` VARCHAR(20) NOT NULL DEFAULT '',
    `s_birth` VARCHAR(20) NOT NULL DEFAULT '',
    `s_sex` VARCHAR(10) NOT NULL DEFAULT '',
    PRIMARY KEY(`s_id`)
);
--课程表
CREATE TABLE `Course`(
    `c_id`  VARCHAR(20),
    `c_name` VARCHAR(20) NOT NULL DEFAULT '',
    `t_id` VARCHAR(20) NOT NULL,
    PRIMARY KEY(`c_id`)
);
--教师表
CREATE TABLE `Teacher`(
    `t_id` VARCHAR(20),
    `t_name` VARCHAR(20) NOT NULL DEFAULT '',
    PRIMARY KEY(`t_id`)
);
--成绩表
CREATE TABLE `Score`(
    `s_id` VARCHAR(20),
    `c_id`  VARCHAR(20),
    `s_score` INT(3),
    PRIMARY KEY(`s_id`,`c_id`)
);
--插入学生表测试数据
insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
insert into Student values('02' , '钱电' , '1990-12-21' , '男');
insert into Student values('03' , '孙风' , '1990-05-20' , '男');
insert into Student values('04' , '李云' , '1990-08-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吴兰' , '1992-03-01' , '女');
insert into Student values('07' , '郑竹' , '1989-07-01' , '女');
insert into Student values('08' , '王菊' , '1990-01-20' , '女');
--课程表测试数据
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');

--教师表测试数据
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');

--成绩表测试数据
insert into Score values('01' , '01' , 80);
insert into Score values('01' , '02' , 90);
insert into Score values('01' , '03' , 99);
insert into Score values('02' , '01' , 70);
insert into Score values('02' , '02' , 60);
insert into Score values('02' , '03' , 80);
insert into Score values('03' , '01' , 80);
insert into Score values('03' , '02' , 80);
insert into Score values('03' , '03' , 80);
insert into Score values('04' , '01' , 50);
insert into Score values('04' , '02' , 30);
insert into Score values('04' , '03' , 20);
insert into Score values('05' , '01' , 76);
insert into Score values('05' , '02' , 87);
insert into Score values('06' , '01' , 31);
insert into Score values('06' , '03' , 34);
insert into Score values('07' , '02' , 89);
insert into Score values('07' , '03' , 98);

Student

Course

teacher

score

1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数

方式1：

select a.*, b.s_score as 01_score, c.s_score as 02_score from
  student a , score b, score c
  where a.s_id = b.s_id
  and a.s_id = c.s_id
  and b.c_id = '01'
  and c.c_id = '02'
  and b.s_score > c.s_score;

方式2：(join on)

select a.*, b.s_score as 01_score, c.s_score as 02_score from
student a 
join score b on a.s_id = b.s_id and b.c_id = '01'
join score c on a.s_id = c.s_id and c.c_id = '02'
where b.s_score > c.s_score;

2、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩

select a.s_id, a.s_name, avg(s_score) as avg_score 
  from student a, score b
  where a.s_id = b.s_id
  group by a.s_id, a.s_name
  having avg(s_score) >= 60;

3、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩 (包括有成绩的和无成绩的)

select a.s_id, a.s_name, avg(b.s_score)
    from student a
    left join score b
    on a.s_id = b.s_id
    group by s_id
    having avg(b.s_score) < 60
    union
select a.s_id, a.s_name, 0
    from student a
    where a.s_id not in (select distinct s_id from score);

4、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩（*）

// 错误的写法，因为这里强调了是所有的学生，所以要包含那些没有成绩的学生
select a.s_id, a.s_name, count(b.c_id), sum(s_score)
    from student a, score b
    where a.s_id = b.s_id
    group by a.s_id;

// 正确写法
select a.s_id, a.s_name, count(b.c_id), sum(s_score)
    from student a
    left join score b on a.s_id = b.s_id
    group by a.s_id;

5、查询"李"姓老师的数量

select count(*) from teacher where t_name like '李%';

6、查询学过"张三"老师授课的同学的信息

// 需要找到张三老师上过课的id，如果那个学生上课的id有前者id集合中，则表示该学生上过张三老师的课
select a.* from student a
    join score b on a.s_id = b.s_id where b.c_id in
    (select c_id from course c
        join teacher d on c.t_id = d.t_id
        where d.t_name = '张三'
    );

7、查询没学过"张三"老师授课的同学的信息 (和上一题类似)

// 先筛选出上个张三老师课程的学生的s_id统计出来，最后只需要将s_id不在前者s_id集合内的学生列举出来信息即可
select a.* from 
    student a where a.s_id not in (
        select a.s_id from student a
            join score b on a.s_id = b.s_id where b.c_id in
            (select c_id from course c
                join teacher d on c.t_id = d.t_id
                where d.t_name = '张三'
            )
    );

8、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息（*）

select a.* from student a, score s1, score s2
    where a.s_id = s1.s_id and a.s_id = s2.s_id and s1.c_id = '01' and s2.c_id = '02';

9、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息（*）

select a.* from student a
    where a.s_id in (select s_id from score where c_id = '01')
    and a.s_id not in (select s_id from score where c_id = '02');

10、查询没有学全所有课程的同学的信息（*）

select a.* from 
    student a 
    join score b on a.s_id = b.s_id 
    group by b.s_id having count(*) < (select count(c_id) from course);

错误结果

只关心了参加考试的学生，但是存在有学生没有参加考试；

select a.* from 
    student a 
    left join score b on a.s_id = b.s_id 
    group by b.s_id having count(*) < (select count(c_id) from course);

11、查询至少有一门课与学号为"01"的同学所学相同的同学的信息

select distinct a.* from 
    student a join score b on a.s_id = b.s_id 
    where b.c_id in (
    select c.c_id from score c where c.s_id = '01'
    );

12、查询和"01"号的同学学习的课程完全相同的其他同学的信息（*）

select a.* from student a
    where 
    a.s_id in (
    # 找到与01同学所学课程数相同的s_id的学生
        select s_id from score group by s_id 
        having count(c_id) = (select count(c_id) from score where s_id = '01')
    )
    and
    a.s_id not in (
    # 排除01同学学习了但是有一些同学没有学习
        select distinct s_id from score where c_id not in (
            select c_id from score where s_id = '01'
        )
    )
    and
    # 排除自身
    a.s_id != '01';

13、查询没学过"张三"老师讲授的任一门课程的学生姓名

select a.s_name from student a
    where a.s_id not in (
        # 学过"张三"老师的课
        select distinct s_id from score 
        where c_id in (select c_id from course where t_id = (select t_id from teacher where t_name = '张三'))
    );

14、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩（*）

select a.s_id,a.s_name,ROUND(AVG(b.s_score)) from 
    student a 
    join score b on a.s_id = b.s_id
    where a.s_id in(
            select s_id from score where s_score<60 GROUP BY s_id having count(s_id)>=2
            )
    GROUP BY a.s_id

15.检索"01"课程分数小于60，按分数降序排列的学生信息

select a.* , b.s_score from student a , score b
    where a.s_id = b.s_id and b.s_score < 60 and b.c_id = '01'
    order by b.s_score desc;

递增是asc