MySQL必知必会的sql语句

Author：钟小湖
发布时间：April 5, 2020
229views
No comments
7242 words
Categories：代码生涯数据分析

推荐阅读：
<div class="preview">
<div class="post-inser post box-shadow-wrap-normal">
<a href="https://www.xn2001.com/archives/579.html" target="_blank" class="post_inser_a no-external-link no-underline-link">
<div class="inner-image bg" style="background-image: url(https://imgcdn.xn2001.com/usr/themes/handsome/assets/img/sj/4.jpg);background-size: cover;"></div>

<div class="inner-content" >
<p class="inser-title">Mysql使用指南</p>
<div class="inster-summary text-muted">
安装卸载启动关闭net start mysql 启动mysql服务net stop mysql 关闭mysql服务...
</div>
</div>
</a>

</div>

</div>

## 如何查看有什么数据库?

```sql
show databases;
```

##  如何选择数据库?

```sql
use databases_name
eg:
use test;
```

##  如何查看该数据库中有哪些表?

```sql
show tables; 
```

##  如何查询表中的数据?

```sql
select * from tableName;
```

## 如何退出数据库服务器?

```sql
exit;
```

## 如何在数据库服务器中创建自己的数据库?

```sql
create database databaseName;
```

## 如何创建一个数据表? 创建一个pet表

```sql
create table pet(
	id int(20),
    name varchar(20),
    age int(5),
    brith date
);
```

## 如何查看数据表的架构?

```sql
desc tableName;
```

## 如何插入数据?

```sql
 INSERT INTO pet VALUES(1,'cc',18,'1998-8-2');
```

## 插入指定字段的数据？

```sql
INSERT INTO demo(id) VALUES(18),(13);
```

## 删除数据

```sql
DELETE FROM tablesName WHRER 条件;
```

## 修改数据

```sql
UPDATE tableName SET 字段1=值1,字段2=值2 ... WHERE 条件;
```

## 查询数据(重要)

下面的语句已经全部经过两次测试。科普：FROM两表和 JOIN ON实际上执行任务是一样的，只是两种不同的写法。

新建一个查询用的数据库:

```sql
CREATE DATABASE selectTest;
```

选择该数据库:

```sql
USE selectTest;
```

创建学生表 student

```sql
CREATE TABLE student(
    s_no VARCHAR(20) PRIMARY KEY COMMENT'学生学号',
    s_name VARCHAR(20) NOT NULL COMMENT'学生姓名 不能为空',
    s_sex VARCHAR(10) NOT NULL COMMENT'学生性别',
    s_birthday DATETIME COMMENT'学生生日',
    s_class VARCHAR(20) COMMENT'学生所在的班级'
);
```

创建课程表 course

```sql
CREATE TABLE course(
    c_no VARCHAR(20) PRIMARY KEY COMMENT'课程号',
    c_name VARCHAR(20) NOT NULL COMMENT'课程名称',
    t_no VARCHAR(20) NOT NULL COMMENT'教师编号'
);
```

创建教师表 teacher

```sql
CREATE TABLE teacher(
    t_no VARCHAR(20) PRIMARY KEY COMMENT'教师编号',
    t_name VARCHAR(20) NOT NULL COMMENT'教师姓名',
    t_sex VARCHAR(20) NOT NULL COMMENT'教师性别',
    t_birthday DATETIME COMMENT'教师生日',
    t_rof VARCHAR(20) NOT NULL COMMENT'教师职称',
    t_depart VARCHAR(20) NOT NULL COMMENT'教师所在的部门'
);
```

创建成绩表 srore

```sql
CREATE TABLE score (
    s_no VARCHAR(20) NOT NULL COMMENT'成绩表的编号 依赖学生学号',
    c_no VARCHAR(20)  NOT NULL COMMENT'课程号 依赖于课程表中的c_no',
    sc_degree decimal,
    PRIMARY KEY(s_no,c_no)
);
```

查看创建的表以及架构

```sql
SHOW TABLES;
```

向表中添加数据

```sql
--学生表数据

INSERT INTO student VALUES('101','曾华','男','1977-09-01','95033');
INSERT INTO student VALUES('102','匡明','男','1975-10-02','95031');
INSERT INTO student VALUES('103','王丽','女','1976-01-23','95033');
INSERT INTO student VALUES('104','李军','男','1976-02-20','95033');
INSERT INTO student VALUES('105','王芳','女','1975-02-10','95031');
INSERT INTO student VALUES('106','陆军','男','1974-06-03','95031');
INSERT INTO student VALUES('107','王尼玛','男','1976-02-20','95033');
INSERT INTO student VALUES('108','张全蛋','男','1975-02-10','95031');
INSERT INTO student VALUES('109','赵铁柱','男','1974-06-03','95031');

--教师表数据

INSERT INTO teacher VALUES('804','李诚','男','1958-12-02','副教授','计算机系');
INSERT INTO teacher VALUES('856','张旭','男','1969-03-12','讲师','电子工程系');
INSERT INTO teacher VALUES('825','王萍','女','1972-05-05','助教','计算机系');
INSERT INTO teacher VALUES('831','刘冰','女','1977-08-14','助教','电子工程系');

--添加课程表

INSERT INTO course VALUES('3-105','计算机导论','825');
INSERT INTO course VALUES('3-245','操作系统','804');
INSERT INTO course VALUES('6-166','数字电路','856');
INSERT INTO course VALUES('9-888','高等数学','831');

--添加成绩表

INSERT INTO score VALUES('103','3-245','86');
INSERT INTO score VALUES('105','3-245','75');
INSERT INTO score VALUES('109','3-245','68');
INSERT INTO score VALUES('103','3-105','92');
INSERT INTO score VALUES('105','3-105','88');
INSERT INTO score VALUES('109','3-105','76');
INSERT INTO score VALUES('103','6-166','85');
INSERT INTO score VALUES('105','6-166','79');
INSERT INTO score VALUES('109','6-166','81');
```

查看表数据

```sql
student  SELECT * FROM student;
teacher  SELECT * FROM teacher;
score  SELECT * FROM score;
course    SELECT * FROM course;
```

几个重要的查询语句

查询student表中所有的记录

```sql
SELECT * FROM student;
```

查询student表中所有记录的s_name,s_sex和s_class列

```sql
SELECT s_no,s_name,s_class FROM student;
```

查询教师所有的单位但是**不重复**的t_depart列

```sql
SELECT DISTINCT (t_depart) FROM teacher; 
```

查询score表中成绩在60-80之间所有的记录(sc_degree)

```sql
SELECT * FROM score WHERE sc_degree BETWEEN 61 AND 79;
```

查询score表中成绩为85, 86, 或者88的记录(sc_degree)

```sql
SELECT * FROM score WHERE sc_degree IN(85,86,88);
```

查询student表中 '95031' 班或者性别为'女'的同学记录

```sql
SELECT * FROM student WHERE s_class = '95031' OR s_sex = '女';
```

以class**降序**查询student表中所有的记录

```sql
SELECT * FROM student ORDER BY s_class DESC;
```

以 c_no **升序**且 sc_degree **降序**插叙score表中所有的数据

```sql
SELECT * FROM score ORDER BY c_no ASC,sc_degree DESC;
```

查询 '95031' 班的学生人数

```sql
SELECT COUNT(s_no) FROM student WHERE s_class = '95031';
```

查询score表中的最高分数的学生号和课程号.(子查询或者排序)

```sql
SELECT c_no, s_no FROM score WHERE sc_degree = (SELECT MAX(sc_degree) FROM score);

SELECT c_no,s_no,sc_degree FROM score ORDER BY sc_degree DESC;
```

查询每门课的平均成绩

```sql
SELECT c_no,AVG(sc_degree) FROM SCORE GROUP BY c_no;
```

查询score表中至少有2名学生选修的,并且以3开头的课程的平均分

```sql
SELECT c_no,AVG(sc_degree) FROM score GROUP BY c_no HAVING COUNT(c_no) >= 2 AND c_no LIKE '3%';
// 建议在后台实现，而不是让sql去查询
```

查询姓王的学生

```sql
SELECT * FROM student WHERE s_name LIKE '王%';
```

查询所有的学生 s_name , c_no, sc_degree列

```sql
SELECT s_name, c_no, sc_degree FROM student,score WHERE student.s_no = score.s_no;
```

查询所有学生的s_no, c_name, sc_degree列

```sql
SELECT student.s_no, c_name, sc_degree FROM student, course,score WHERE student.s_no = score.s_no AND score.c_no = course.c_no ;
```

查询所有的学生 s_name , c_name, sc_degree列

```sql
SELECT s_name, c_name, sc_degree FROM student, course, score WHERE student.s_no = score.s_no AND score.c_no = course.c_no;
```

查询班级是'95031'班学生每门课的平均分

```sql
SELECT c_no,AVG(sc_degree) FROM score WHERE s_no IN (select s_no from student where s_class = '95031') GROUP BY c_no;

SELECT c_no,AVG(sc_degree) FROM score AS sc LEFT JOIN student AS s ON sc.s_no = s.s_no WHERE s.s_class = '95031' GROUP BY sc.c_no;

SELECT sc.c_no,AVG(sc.sc_degree) FROM student AS s, score AS SC  WHERE s.s_class = '95031' AND s.s_no = sc.s_no GROUP BY sc.c_no ;

// 加入课程名称:

SELECT sc.c_no, c.c_name, AVG(sc.sc_degree) FROM student AS s, score AS SC, course AS c WHERE s.s_class = '95031' AND s.s_no = sc.s_no AND c.c_no = sc.c_no GROUP BY sc.c_no ;

SELECT sc.c_no, c.c_name, AVG(sc.sc_degree) FROM student s INNER JOIN score sc ON (s.s_no = sc.s_no) INNER JOIN course c ON (c.c_no = sc.c_no) WHERE s.s_class = '95031'GROUP BY sc.c_no ;

SELECT sc.c_no, c.c_name, AVG(sc_degree) FROM score sc INNER JOIN student s ON sc.s_no = s.s_no INNER JOIN course c ON sc.c_no = c.c_no  WHERE s.s_class = '95031' GROUP BY sc.c_no;
```

查询所有学号为108.101的同学同年出生的所有学生的s_no,s_name和s_birthday

```sql
SELECT * FROM student WHERE YEAR(s_birthday) IN (SELECT YEAR(s_birthday) FROM student WHERE s_no IN(108,101));
```

查询 张旭 老师任课的学生的成绩

```sql
SELECT s.s_name FROM teacher t,course c,score sc,student s WHERE t.t_name = '张旭' AND t.t_no = c.t_no AND c.c_no = sc.c_no AND sc.s_no = s.s_no;
```

查询95033班和95031班全体学生的记录

```sql
SELECT * FROM student WHERE s_class IN('95031','95033') ORDER BY s_class ;
```<hr class="content-copyright" style="margin-top:50px" /><blockquote class="content-copyright" style="font-style:normal"><p class="content-copyright">版权属于：乐心湖's Blog</p><p class="content-copyright">本文链接：<a class="content-copyright" href="https://www.xn2001.com/archives/424.html">https://www.xn2001.com/archives/424.html</a></p><p class="content-copyright">声明：博客所有文章除特别声明外，均采用 <a href="https://creativecommons.org/licenses/by-sa/4.0/deed.zh" target="_blank" rel="nofollow noopener noopener" one-link-mark="yes">CC BY-SA 4.0 协议</a> ，转载请注明出处！</p></blockquote>

腾讯云社区邀请各位技术博主加入，福利多多噢！

Last modification：September 7th, 2020 at 01:15 pm

如果觉得我的文章对你有用，请随意赞赏

MySQL必知必会的sql语句

钟小湖 • 2020 年 04 月 05 日

## 如何查看有什么数据库?

```sql
show databases;
```

##  如何选择数据库?

```sql
use databases_name
eg:
use test;
```

##  如何查看该数据库中有哪些表?

```sql
show tables; 
```

##  如何查询表中的数据?

```sql
select * from tableName;
```

## 如何退出数据库服务器?

```sql
exit;
```

## 如何在数据库服务器中创建自己的数据库?

```sql
create database databaseName;
```

## 如何创建一个数据表? 创建一个pet表

```sql
create table pet(
	id int(20),
    name varchar(20),
    age int(5),
    brith date
);
```

## 如何查看数据表的架构?

```sql
desc tableName;
```

## 如何插入数据?

```sql
 INSERT INTO pet VALUES(1,'cc',18,'1998-8-2');
```

## 插入指定字段的数据？

```sql
INSERT INTO demo(id) VALUES(18),(13);
```

## 删除数据

```sql
DELETE FROM tablesName WHRER 条件;
```

## 修改数据

```sql
UPDATE tableName SET 字段1=值1,字段2=值2 ... WHERE 条件;
```

## 查询数据(重要)

下面的语句已经全部经过两次测试。科普：FROM两表和 JOIN ON实际上执行任务是一样的，只是两种不同的写法。

新建一个查询用的数据库:

```sql
CREATE DATABASE selectTest;
```

选择该数据库:

```sql
USE selectTest;
```

创建学生表 student

创建课程表 course

```sql
CREATE TABLE course(
    c_no VARCHAR(20) PRIMARY KEY COMMENT'课程号',
    c_name VARCHAR(20) NOT NULL COMMENT'课程名称',
    t_no VARCHAR(20) NOT NULL COMMENT'教师编号'
);
```

创建教师表 teacher

创建成绩表 srore

查看创建的表以及架构

```sql
SHOW TABLES;
```

向表中添加数据

```sql
--学生表数据

--教师表数据

--添加课程表

--添加成绩表

查看表数据

```sql
student  SELECT * FROM student;
teacher  SELECT * FROM teacher;
score  SELECT * FROM score;
course    SELECT * FROM course;
```

几个重要的查询语句

查询student表中所有的记录

```sql
SELECT * FROM student;
```

查询student表中所有记录的s_name,s_sex和s_class列

```sql
SELECT s_no,s_name,s_class FROM student;
```

查询教师所有的单位但是**不重复**的t_depart列

```sql
SELECT DISTINCT (t_depart) FROM teacher; 
```

查询score表中成绩在60-80之间所有的记录(sc_degree)

```sql
SELECT * FROM score WHERE sc_degree BETWEEN 61 AND 79;
```

查询score表中成绩为85, 86, 或者88的记录(sc_degree)

```sql
SELECT * FROM score WHERE sc_degree IN(85,86,88);
```

查询student表中 '95031' 班或者性别为'女'的同学记录

```sql
SELECT * FROM student WHERE s_class = '95031' OR s_sex = '女';
```

以class**降序**查询student表中所有的记录

```sql
SELECT * FROM student ORDER BY s_class DESC;
```

以 c_no **升序**且 sc_degree **降序**插叙score表中所有的数据

```sql
SELECT * FROM score ORDER BY c_no ASC,sc_degree DESC;
```

查询 '95031' 班的学生人数

```sql
SELECT COUNT(s_no) FROM student WHERE s_class = '95031';
```

查询score表中的最高分数的学生号和课程号.(子查询或者排序)

```sql
SELECT c_no, s_no FROM score WHERE sc_degree = (SELECT MAX(sc_degree) FROM score);

SELECT c_no,s_no,sc_degree FROM score ORDER BY sc_degree DESC;
```

查询每门课的平均成绩

```sql
SELECT c_no,AVG(sc_degree) FROM SCORE GROUP BY c_no;
```

查询score表中至少有2名学生选修的,并且以3开头的课程的平均分

```sql
SELECT c_no,AVG(sc_degree) FROM score GROUP BY c_no HAVING COUNT(c_no) >= 2 AND c_no LIKE '3%';
// 建议在后台实现，而不是让sql去查询
```

查询姓王的学生

```sql
SELECT * FROM student WHERE s_name LIKE '王%';
```

查询所有的学生 s_name , c_no, sc_degree列

```sql
SELECT s_name, c_no, sc_degree FROM student,score WHERE student.s_no = score.s_no;
```

查询所有学生的s_no, c_name, sc_degree列

```sql
SELECT student.s_no, c_name, sc_degree FROM student, course,score WHERE student.s_no = score.s_no AND score.c_no = course.c_no ;
```

查询所有的学生 s_name , c_name, sc_degree列

```sql
SELECT s_name, c_name, sc_degree FROM student, course, score WHERE student.s_no = score.s_no AND score.c_no = course.c_no;
```

查询班级是'95031'班学生每门课的平均分

```sql
SELECT c_no,AVG(sc_degree) FROM score WHERE s_no IN (select s_no from student where s_class = '95031') GROUP BY c_no;

SELECT c_no,AVG(sc_degree) FROM score AS sc LEFT JOIN student AS s ON sc.s_no = s.s_no WHERE s.s_class = '95031' GROUP BY sc.c_no;

SELECT sc.c_no,AVG(sc.sc_degree) FROM student AS s, score AS SC  WHERE s.s_class = '95031' AND s.s_no = sc.s_no GROUP BY sc.c_no ;

// 加入课程名称:

SELECT sc.c_no, c.c_name, AVG(sc.sc_degree) FROM student AS s, score AS SC, course AS c WHERE s.s_class = '95031' AND s.s_no = sc.s_no AND c.c_no = sc.c_no GROUP BY sc.c_no ;

SELECT sc.c_no, c.c_name, AVG(sc.sc_degree) FROM student s INNER JOIN score sc ON (s.s_no = sc.s_no) INNER JOIN course c ON (c.c_no = sc.c_no) WHERE s.s_class = '95031'GROUP BY sc.c_no ;

SELECT sc.c_no, c.c_name, AVG(sc_degree) FROM score sc INNER JOIN student s ON sc.s_no = s.s_no INNER JOIN course c ON sc.c_no = c.c_no  WHERE s.s_class = '95031' GROUP BY sc.c_no;
```

查询所有学号为108.101的同学同年出生的所有学生的s_no,s_name和s_birthday

```sql
SELECT * FROM student WHERE YEAR(s_birthday) IN (SELECT YEAR(s_birthday) FROM student WHERE s_no IN(108,101));
```

查询 张旭 老师任课的学生的成绩

```sql
SELECT s.s_name FROM teacher t,course c,score sc,student s WHERE t.t_name = '张旭' AND t.t_no = c.t_no AND c.c_no = sc.c_no AND sc.s_no = s.s_no;
```

查询95033班和95031班全体学生的记录

Leave a Comment Cancel reply

MySQL必知必会的sql语句