Mysql

1.     Mysql基础知识

1.1.     Index and table searching of Mysql

1.1.1.     Basic concepts of Mysql and DBMS

• What is sql: a storage for storing data and sql means Structured Query language
• Contents of sql : title and table
• DB: database是指长期存储在计算机内的，有组织，可共享的数据的集合。数据库中的数据按一定的数字模型组织、描述和存储，具有较小的冗余，较高的数据独立性和易扩展性，并可为各种用户共享。
• DMS：database Management System是一种操纵和管理数据库的大型软件
• 除了mysql外，还有甲骨文oracle的，IBM的db2，sql server，Access
• Select now()不区分大小写，可以写成SELECT now()或者select now()或者Select now()
• 支持换行输出（但要以“；”结尾），不能对单词跨行。
• 单行注释：--，多行注释：/*......*/

安装mysql步骤：

1. 这里我安装的时候出现错误5：说明没有用管理员身份运行。
2. 从官网直接下载最新版（压缩版zip，mysql5.以后就不是msi了）
3. 解压到本地目录，将mysql/bin文件夹，将间接路径…/bin添加到系统Path的环境变量下
4. 有时候会出现解压后的mysql包里没有default.ini文件或者data文件夹，同时在C：/ProgramData里也没有
5. 在命令行输入cmd,用管理员身份运行cmd
6. 输入mysqld --initialize-insecure --user=mysql，这是在mysql根目录下会出现data文件夹
7. 接下来输入mysqld –install，系统提示successfully installed表明安装成功
8. 接下来运行net start MySQL，进入MySQL服务，如果服务启动成功，到下一步
9. 输入mysql –u root –p，需要输入密码，第一次可以用enter键跳过
10. 系统提示如下界面，说明mysql安装成功

　　删库：drop databases zrc: 出现Query OK表明删除成功

　　三种退出数据库命令行方法：\q，exit，quit

　　Mysql密码，忘记密码需要删除库

• 数据库命令

　　Create database 创建数据库

　　Create database if not exists s3; #如果不存在s3，就创建s3

　　Create database if not exists s4 character set gbk; #设置s4表的字符为gbk

　　Show database 查看数据库

　　Show create database; 查看创建的某个数据库

　　Show warning #查看警告内容

　　alter database s4 character set utf8 更改数据库编码

　　select database(); 查看进入的数据库序号

1.1.2.     数据库关键字类型

　　主键（字段）ID两个特性：非空且唯一；非空 not null，唯一 unique

　　Mysql三种类型：数值，日期和时间

　　数值类型：区别是字节不一样

　　表的创建：

　　CREATE TABLE employee.Charger(

           　　  id TINYINT PRIMARY KEY auto_increment,

           　　  name VARCHAR(25),

            　　 gender boolean,

            　　 age INT DEFAULT 19,

            　　 department VARCHAR(20),

             　　salart DOUBLE(7,2) 

　　)

　　Employee:库名

　　Charger:数据表名

1.1.3.     数据表操作

　　desc tab_name（表名） 查看表结构

　　show columns from tab_name 查看表结构

　　show tables 查看当前数据库中的所有的表

　　show create table tab_name 查看当前数据库表建表数据

　　alter table employee add is_married 向主键添加一个字段

　　alter table employee add entry_date date not null 添加入职时间

　　alter table employee DROP A 删除A列

　　alter table employee modify age smallint unique 修改字段类型

　　alter table employee modify age smallint not null default 18 after id; 移动字段位置

　　修改前：

　　修改后：

　　alter table employee change department depart varchar(20) after salart改字段名称并移动

　　rename table employee to emp 重命名表头employee 到emp

　　create table A(id int) id必须有

插入表数据语句

• INSERT INTO emp (id,age,name,gender,salart,is_married) VALUES (1,38,”alex”,0,1700,1)
• select * from emp; 从emp中查询数据表内容
• INSERT INTO emp set name=”珊珊” 向table中插入name珊珊

修改表字段名

• alter table emp change salart salary VARCHAR(20) 修改表table字段名
• UPDATE emp SET salary=salary+20000, depart=”保安部” WHERE NAME=”yuan”; 修改数据

删除表字段名

• DELETE From emp WHERE id=2 OR id=3; 删除记录
• Truncate table emp; 删除记录

　　上述两者区别：delect逐条删除数据；truncate属于销毁emp表，重新创建新表

Select * from emp 和 desc emp区别：

• Select * from emp表示查看字段和数据内容；

• desc emp表示查看字段类型

选择并查看数据库：

• select database(); 选择数据库
• show tables; 查看数据库中的表（有哪些表）
• use s3；选择用户

例.向mysql中插入一个

CREATE TABLE ExamResult(

       id INT PRIMARY KEY auto_increment,

       name VARCHAR(20),

       JS DOUBLE,

       Django DOUBLE,

       Flask DOUBLE

);

INSERT INTO ExamResult VALUES (1,"yuan",98,98,98),

                                                　　    (2,"xialv",35,98,67),

                                                　　    (3,"alex",59,59,62),

                                                　　    (4,"wusir",88,89,82),

                                                　　    (5,"alvin",88,98,67),

                                                　　    (6,"yuan",86,100,55);

选择查看所有name: select name from Examresult;

也可以同时查看多个字段名，比如name+JS

选择name列，去重复：select distinct name from examresult; 此时重复yuan，消除。

显示所有信息：select name,JS,Django,Flask from examresult;

所有人分数+10分显示：select name,JS+10,Django+10,Flask+10 from examresult;

也可以写成中文名并在数值中加10: select name 姓名,JS+10 as JS成绩,Django+10 as django成绩,Flask+20 from examresult;

显示成绩大于80的字段和数据：select name,JS from examresult where JS>80; （Where 常用于添加条件用。）

查看88到100的数据：select name,JS from examresult where JS between 88 and 100; (包括88和100)

select name,JS from examresult where JS in (88,99,77) 筛选出examresult中有88,99,77的数值

select name,JS from examresult where name like "y%"; 模糊匹配y开头的name数据

select name,JS from examresult where name like "a____"; 匹配a+后四位的name数据

 添加一个空值到examresult中 insert into examresult name value (“Tom”)

 Select name from examresult where JS is NULL; 查看name中空值的名字

 select name,JS from examresult order by JS; 按照升值对name排序

select name,JS from examresult where JS>70 order by JS; name值>70并按照升序排列

select name,JS from examresult where JS>70 order by JS desc; name值>70并按照降序排列

select name,JS+Django+Flask as 总成绩 from examresult order by 总成绩 desc; 把JS+Django+Flask赋值给总成绩并按照降序排列；

select name,JS+Django+Flask as ‘总成绩’; from examresult where name="yuan" order by ‘总成绩’ asc; 选择上述总成绩并挑选出yuan进行升序排序；

错误语句分析：select JS as JS成绩 from examresult where JS成绩 > 70 不能执行，因为首先处理from examresult, 接着找到JS>70处，但是此时没有JS成绩的字段，所以报错

改变字段名：alter table examresult change JS JS成绩 double;

Group_by听所很难！其实也不简单，这也是后来在看<<MySQL必知必会>>领悟更多的。

select name,sum(JS成绩) from examresult group by name; 分组后求和；

Where和having 区别：where分组前过滤，having分组后过滤；

select name,sum(Django) from examresult group by name having sum(Django)>90;

select * from examresult having id=3; 选出id=3

等于 select * from examresult where id =3;

select count(name) from examresult where JS成绩>70; 取出JS成绩>70人的个数

select sum(JS成绩)/count(name) from examresult; 求JS成绩平均数

select AVG(JS成绩) from examresult; 求JS成绩平均数

两者区别在于sum中考虑空值NULL，AVG过滤掉空值NULL

select max(JS+Django+flask) from examresult; 求JS+Django+flask的最大值

select * from examresult limit 5; 限制显示的数据条数

以下关键字排序顺序：

Where条件

Group by field

Having 筛选

Order by field

Limit 限制条数

正则匹配例子：

Select * from examresult where emp_name regexp “yu”;

Select * from examresult where emp_name regexp “yun$”;

1.2.     Matters of mysql

1.2.1.     多表查询值连接查询

创建老师表+学生表

CREATE TABLE lessonmysql.ClassCharger(
id TINYINT PRIMARY KEY auto_increment,
name VARCHAR(20),
age INT,
is_married boolean
);

CREATE TABLE Student(
id INT PRIMARY KEY auto_increment,
name VARCHAR(20),
charger_id TINYINT
FOREIGN KEY (charger_id) REFERENCES classcharger(id)
ENGINE=INNODB;

相关联的两个数据类型必须一致

插入老师名：

1 Insert into ClassCharger(name,age,is_married) values (
2 "冰冰",52,0),
3 ("丹丹",34,0),
4 ("玩玩",32,0),
5 ("jiji",28,0),
6 ("dingding",35,0);

插入学生名：

INSERT INTO Student(name,charger_id) VALUES ("alvin1",2),
("alvin2",4),
("alvin3",1),
("alvin4",3),
("alvin5",1),
("alvin6",3),
("alvin7",2);

 创建表student2

CREATE TABLE Student2(

id INT PRIMARY KEY auto_increment,

name VARCHAR(20),

charger_id TINYINT,

FOREIGN KEY (charger_id) REFERENCES classcharger(id)

)ENGINE=INNODB;

 删除charger冰冰后再次插值，将1改为5

INSERT INTO Student2(name,charger_id) VALUES ("alvin1",2),
("alvin2",4),
("alvin3",5),
("alvin4",3),
("alvin5",5),
("alvin6",3),
("alvin7",2);
Classcharger主表，student2是子表；

添加外键关联：

alter Table student ADD CONSTRAINT abc Foreign KEY(charger_id) references classcharger(id);   

删除外键关联：

alter table student2 drop foreign key abc

1.2.2.     级联删除与set NULL

• 外键的级联删除：如果父表中的记录被删除，则子表中对应的记录自动删除

CREATE TABLE Student3(
id INT PRIMARY KEY auto_increment,
name VARCHAR(20),
charger_id TINYINT,
FOREIGN KEY (charger_id) REFERENCES classcharger(id) on DELETE CASCADE
)ENGINE=INNODB;

• Set NULL：当父表上update/delete记录时，将子表上匹配记录的列设为null

Alter table s3 add constraint s3_fk_cc foreign key (charger_id) References cc(id) on delete set null;

1.2.3.     多表查询之连接查询

• 内联结，外联结和交叉来联结定义：

1、内联接（典型的联接运算，使用像 =  或 <> 之类的比较运算符）。包括相等联接和自然联接。     
内联接使用比较运算符根据每个表共有的列的值匹配两个表中的行。例如，检索 students和courses表中学生标识号相同的所有行。   
2、外联接。外联接可以是左向外联接、右向外联接或完整外部联接。     
在 FROM子句中指定外联接时，可以由下列几组关键字中的一组指定：     
1）LEFT  JOIN或LEFT OUTER JOIN     
左向外联接的结果集包括  LEFT OUTER子句中指定的左表的所有行，而不仅仅是联接列所匹配的行。如果左表的某行在右表中没有匹配行，则在相关联的结果集行中右表的所有选择列表列均为空值。       
2）RIGHT  JOIN 或 RIGHT  OUTER  JOIN     
右向外联接是左向外联接的反向联接。将返回右表的所有行。如果右表的某行在左表中没有匹配行，则将为左表返回空值。       
3）FULL  JOIN 或 FULL OUTER JOIN
完整外部联接返回左表和右表中的所有行。当某行在另一个表中没有匹配行时，则另一个表的选择列表列包含空值。如果表之间有匹配行，则整个结果集行包含基表的数据值。

3、交叉联接   
交叉联接返回左表中的所有行，左表中的每一行与右表中的所有行组合。交叉联接也称作笛卡尔积。    
FROM 子句中的表或视图可通过内联接或完整外部联接按任意顺序指定；但是，用左或右向外联接指定表或视图时，表或视图的顺序很重要。有关使用左或右向外联接排列表的更多信息，请参见使用外联接。

•  · 两表外联结例子：

-------------------------------------------------
  a表     id   name     b表     id   job   parent_id   
              1   张3                   1     23     1   
              2   李四                 2     34     2   
              3   王武                 3     34     4       
  a.id同b.parent_id   存在关系   
--------------------------------------------------    
 1） 内连接   
  select   a.*,b.*   from   a   inner   join   b     on   a.id=b.parent_id       
  结果是     
  1   张3                   1     23     1   
  2   李四                  2     34     2   

  2）左连接   
  select   a.*,b.*   from   a   left   join   b     on   a.id=b.parent_id       
  结果是     
  1   张3                   1     23     1   
  2   李四                  2     34     2   
  3   王武                  null   

 3） 右连接   
  select   a.*,b.*   from   a   right   join   b     on   a.id=b.parent_id       
  结果是     
  1   张3                   1     23     1   
  2   李四                  2     34     2   
  null                       3     34     4   

 4） 完全连接   
  select   a.*,b.*   from   a   full   join   b     on   a.id=b.parent_id   
  结果是     
  1   张3                  1     23     1   
  2   李四                 2     34     2   
  null               　　  3     34     4   
  3   王武                 null

多表查询（mysql）：

将两张表中存在联结关系的字段符合联结关系的那些记录形成记录集的联结。

Select A.cname,B.sno,B.degree from course as A join score as B on A.cno=B.cno;

将三张表中存在联结关系的字段符合联结关系的那些记录形成记录集的联结。

select A.sname,B.cname,c.degree from student as A join (course B,score C) on A.sno=C.sno and B.cno=C.cno;

1.2.4.     多表查询之复合查询与子查询

子查询与复合查询

1. Select * from employee where dept_id in (…)，Select dept_id from department;
2. Create table AA(select * from employee where dept_id in (select dept_id from department));
3. Xxx where exists (select dept_name xxx)，exists表明查询正确与否，正确True返回执行where前语句，False返回false不执行where前语句.即NULL值
4. 更新某一列所有值 update test1(table) set resume=1000（设置表test1中的列resume值为1000）
5. 修改字段name为unique  alter table test1(表) modify name varchar(20) unique

1.2.5.     Mysql之索引

添加索引：create index index_name on t2(id) $$

删除索引：drop index 索引名 on 表名

如：drop index index_name on t2；

如：drop index index_emp on emp1；

1.3.     Mql事务

Import pymysql

Conn = pymysql.connect()

1.3.1.     Python操作数据库pymysql (后续跟进)

Mysql设置密码：

• 第一次进入mysql时候不用密码，当password出来时，可以直接按enter进入；
• 用flush.privileges刷新权限；（这一步，必须有，否则会报错）
• 执行alter user “root”@localhost IDENTIFIED BY ”newpassport”
• 用flush privileges再次刷新状态
• 退出mysql，用ctrl+z或者”\q”
• 管理员身份重新运行cmd，进入mysql的bin目录，执行net start mysql命令启动mysql
• 执行mysql –u root –p
• 输入新密码/修改后密码，登录mysql成功。

这是最后成功的状态：

如果需要修改密码需要在重设密码前停止mysql进程，所以要启动下面命令：

Net stop mysql; 停止进程

Mysqld --shared-memory –skip-grant-tables 进入免密码模式

退出cmd，重启cmd后，输入net start mysql进入启动流程

输入进入密码操作mysql –u root -p

 两种操作那个数据库的框架：pymysql和orm

1.3.2.     数据库之事务

事务：指逻辑上的一组操作，组成这组操作的各个单元，要不全部成功，要不全部不成功，数据库开启事务命令。

1.3.3.     Mysql事务之savepoint

事务：

Create table account lid int.name varchar(20),balance double),

Insert into account values(2,”zhangwen”,8000)

Start transaction #开始事务

Update account set balance = balance -5000 where id=1;

Select * from account; 查表rollback 回滚事务，即撤销指定sql语句（撤销两个conn.commit()中间的内容）

Update account set balance = balance + 5000 where id =2

Commit; 提交未存储的事务

Savepoint: 保留点，事务处理中设置的临时占位符，可以对它发布回退（区别于整个事务回退rollback）

Savepoint使用，需要配合rollback使用，

如：savepoint delete1---rollback to savepoint

2.     Mysql基础练习:

2.1.     基础练习一

1.        Cd 到mysql安装目录（bin下）
2.        输入mysql –u root –p
3.        选择数据库/自己创建数据库,比如 use s4（会显示change s4）
4.        创建student表，如下开始练习
5.        Create table student...(表示创建好字段)

CREATE TABLE STUDENT
(SNO VARCHAR(3) NOT NULL,
SNAME VARCHAR(4) NOT NULL,
SSEX VARCHAR(2) NOT NULL,
SBIRTHDAY DATETIME,
CLASS VARCHAR(5))

CREATE TABLE COURSE
(CNO VARCHAR(5) NOT NULL,
CNAME VARCHAR(10) NOT NULL,
TNO VARCHAR(10) NOT NULL)

CREATE TABLE SCORE
(SNO VARCHAR(3) NOT NULL,
CNO VARCHAR(5) NOT NULL,
DEGREE NUMERIC(10, 1) NOT NULL)

CREATE TABLE TEACHER
(TNO VARCHAR(3) NOT NULL,
TNAME VARCHAR(4) NOT NULL, TSEX VARCHAR(2) NOT NULL,
TBIRTHDAY DATETIME NOT NULL, PROF VARCHAR(6),
DEPART VARCHAR(10) NOT NULL)

INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (108,‘曾华‘
,‘男‘ ,19770901,95033);
INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (105,‘匡明‘
,‘男‘ ,19751002,95031);
INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (107,‘王丽‘
,‘女‘ ,19760123,95033);
INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (101,‘李军‘
,‘男‘ ,19760220,95033);
INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (109,‘王芳‘
,‘女‘ ,19750210,95031);
INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (103,‘陆君‘
,‘男‘ ,19740603,95031);

INSERT INTO COURSE(CNO,CNAME,TNO)VALUES (‘3-105‘ ,‘计算机导论‘,825);
INSERT INTO COURSE(CNO,CNAME,TNO)VALUES (‘3-245‘ ,‘操作系统‘ ,804);
INSERT INTO COURSE(CNO,CNAME,TNO)VALUES (‘6-166‘ ,‘数据电路‘ ,856);
INSERT INTO COURSE(CNO,CNAME,TNO)VALUES (‘9-888‘ ,‘高等数学‘ ,100);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (103,‘3-245‘,86);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (105,‘3-245‘,75);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (109,‘3-245‘,68);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (103,‘3-105‘,92);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (105,‘3-105‘,88);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (109,‘3-105‘,76);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (101,‘3-105‘,64);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (107,‘3-105‘,91);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (108,‘3-105‘,78);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (101,‘6-166‘,85);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (107,‘6-106‘,79);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (108,‘6-166‘,81);
INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (804,‘李诚‘,‘男‘,‘1958-12-02‘,‘副教授‘,‘计算机系‘);
INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (856,‘张旭‘,‘男‘,‘1969-03-12‘,‘讲师‘,‘电子工程系‘);
INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (825,‘王萍‘,‘女‘,‘1972-05-05‘,‘助教‘,‘计算机系‘);
INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (831,‘刘冰‘,‘女‘,‘1977-08-14‘,‘助教‘,‘电子工程系‘);

创建好数据见下图：

2.2.     基础练习一及参考sql语句

1、 查询Student表中的所有记录的Sname、Ssex和Class列。

select sname,ssex,class from student;

2、 查询教师所有的单位即不重复的Depart列。

Select distinct depart from teacher;

3、 查询Student表的所有记录。

Select * from student;

4、 查询Score表中成绩在60到80之间的所有记录。

Select degree from score where degree between 60 and 80;

5、 查询Score表中成绩为85，86或88的记录。

Select degree from score where degree in (85,86,88); 或者 Select * from score where degree in (85,86,88);

6、 查询Student表中“95031”班或性别为“女”的同学记录。

Select * from student where class =‘95031‘or ssex =‘女‘;

7、 以Class降序查询Student表的所有记录。

Select class from student where class order by class desc;

8、 以Cno升序、Degree降序查询Score表的所有记录。

Select cno from score where cno order by cno;

Select degree from score where degree order by degree desc;

9、 查询“95031”班的学生人数。

select count(class) from student where class=95033;

10、查询Score表中的最高分的学生学号和课程号。

select sno,degree from score where degree = (select max(degree) from score );

11、查询‘3-105’号课程的平均分。

select avg(cno) from score where cno=‘3-105’;

12、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。

select avg(degree) from score where cno=‘3-105‘;

13、查询最低分大于70，最高分小于90的Sno列。

select sno from score where degree between 70 and 90;

14、查询所有学生的Sname、Cno和Degree列。（两张表查询）

Select A.sname,B.cno,B.degree from student as A join score as B on A.sno=B.sno;

Left/right/inner在实际用时可以不加，as也可以省略，如下

Select A.sname,B.cno,B.degree from student (as) A (left/right/inner) join score (as) B on A.sno=B.sno;

(NTOE:要选出不同表中的数据，需要将多个表进行并联，sname在studetn，cno在course和score，degree在score，所以sno为student和degree共有的，则在这两张表上进行并联，首先从student中选出sname，其次将A加入到score表，将B加入到A表，同时A表的sno和B表的sno相等)

15、查询所有学生的Sno、Cname和Degree列。

Select A.cname,B.sno,B.degree from course as A join score as B on A.cno=B.cno;

16、查询所有学生的Sname、Cname和Degree列。(三张表查询)

select A.sname,B.cname,c.degree from student as A join (course B,score C) on A.sno=C.sno and B.cno=C.cno;

17、查询“95033”班所选课程的平均分。

Select avg(A.degree) from score as A join student B on A.sno=B.sno where B.class=‘95033‘;

18、假设使用如下命令建立了一个grade表：

create table grade(number(3,0), number(3),char(1));

insert into grade values(90,100,’A’);

insert into grade values(80,89,’B’);

insert into grade values(70,79,’C’);

insert into grade values(60,69,’D’);

insert into grade values(0,59,’E’);

commit;

insert into grade values(90,100,‘A‘);

insert into grade values(80,89,‘B‘);

insert into grade values(70,79,‘C‘);

insert into grade values(60,69,‘D‘);

insert into grade values(0,59,‘E‘);

commit

现查询所有同学的Sno、Cno和rank列。（两表查询）

select sno,cno,level from score,grade where score.degree between grade.low and grade.upp order by level;

19、查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。

select A.* from score as A join score B where A.cno=‘3-105‘ and A.degree>B.degree and B.sno=‘109‘ and B.cno=‘3-105‘;

20、查询score中选学一门以上课程的同学中分数为非最高分成绩的记录。

Step1 非最高分：degree not in (select max(degree) from score )

select * from score where sno not in (select s.sno from score s join (select cno,max(degree) degree from score group by cno) sc on (sc.cno=s.cno and sc.degree=s.degree));

21、查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录。

select degree from score where degree>(select degree from score where sno=‘109‘ and cno=‘3-105‘);

22、查询和学号为108的同学同年出生的所有学生的Sno、Sname和Sbirthday列。

select sno,sname,sbirthday from student where year(sbirthday)=(select year(sbirthday) from student where sno=‘108‘);

23、查询“张旭“教师任课的学生成绩。

select A.tno,B.cno,C.degree from teacher as A join (course B,score C) on A.tno=B.tno and B.cno=C.cno where A.tname=‘张旭’;

24、查询选修某课程的同学人数多于5人的教师姓名。

这样写（报错）：X.tno,Y.cno只能取其中一个

select tname from teacher where tno in (select X.tno,Y.cno from course X,score Y where X.cno=y.cno group by x.tno having count(x.tno)>5);

ERROR 1241 (21000): Operand should contain 1 column(s)

正确写法：

select tname from teacher where tno in (select X.tno from course X,score Y where X.cno=y.cno group by x.tno having count(x.tno)>5);

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

select * from student where class=‘95033‘or class=‘95031‘;

26、查询存在有85分以上成绩的课程Cno.

select distinct cno from score where degree>85;

27、查询出“计算机系“教师所教课程的成绩表。(三表相联)

select cno,degree from score where cno in (select cno from course where tno in (select tno from teacher where depart=‘计算机系‘));

select a.* from score a join (teacher b,course c) on a.cno=c.cno and b.tno=c.tno where b.depart=‘计算机系‘;

select * from score where cno in (select A.cno from Course A join teacher B on B.tno=A.tno and B.depart=‘计算机系‘);

28、查询“计算机系”与“电子工程系“不同职称的教师的Tname和Prof。

select tname,prof from teacher where depart=‘计算机系‘and prof not in (select prof from teacher where depart=‘电子工程系‘);

select tname,prof from teacher where depart=‘电子工程系‘and prof not in (select prof from teacher where depart=‘计算机系‘)

29、查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”的同学的Cno、Sno和Degree,并按Degree从高到低次序排序。

select cno,sno,degree from score where cno=‘3-105‘and degree>= any(select degree from score where cno=‘3-245‘) order by degree desc;

30、查询选修编号为“3-105”且成绩高于选修编号为“3-245”课程的同学的Cno、Sno和Degree.

select cno,sno,degree from score where cno=‘3-105‘and degree>any(select degree from score where cno=‘3-245‘) order by degree desc;

31、查询所有教师和同学的name、sex和birthday.

select A.sname as name,A.ssex as sex, sbirthday as birthday from student A union select B.tname as name,B.tsex as sex,B.tbirthday as birthday from teacher B;

32、查询所有“女”教师和“女”同学的name、sex和birthday.

select A.sname as name,A.ssex as sex, sbirthday as birthday from student A where ssex=‘女‘ union select B.tname as name,B.tsex as sex,B.tbirthday as birthday from teacher B where tsex=‘女‘;

33、查询成绩比该课程平均成绩低的同学的成绩表。

select A.* from score A where degree <(select avg(degree) from Score B where B.CNO=A.CNO);

34、查询所有任课教师的Tname和Depart.

选项两个参数以上，防止备选项出现重复，比如出现两个“电子工程系”

select tname,depart from teacher; ?

select A.tname,A.depart from teacher A join Course B on B.Tno=A.Tno;?

select tname,depart from teacher where tno in (select tno from course); ?

35  查询所有未讲课的教师的Tname和Depart.

select tname,depart from teacher where tno not in (select tno from course);

select tname,depart from teacher where not exists (select * from course where teacher.tno=course.tno);

36、查询至少有2名男生的班号。

select class from student where ssex=‘男‘ group by class having count(ssex)>=2;

37、查询Student表中不姓“王”的同学记录。

select sname from student where sname not like ‘王%‘;

38、查询Student表中每个学生的姓名和年龄。

select sname as name,(year(now())-year(sbirthday)) as age from student;

39、查询Student表中最大和最小的Sbirthday日期值。

select sname,sbirthday as maxbirthday from student where sbirthday=(select min(sbirthday) from student) union select sname,sbirthday as minbirthday from student where sbirthday=(select max(sbirthday) from student);

40、以班号和年龄从大到小的顺序查询Student表中的全部记录。

select class,(year(now())-year(sbirthday)) as age from student order by class desc,age desc;

41、查询“男”教师及其所上的课程。

select A.tname,B.cname from teacher A join course B using(TNO) where A.tsex=‘男‘;

42、查询最高分同学的Sno、Cno和Degree列。

Select sno,cno,degree from score where degree=(select max(degree) from score);

43、查询和“李军”同性别的所有同学的Sname.

Select A.sname from student A where A.ssex=(select B.ssex from student B where B.sname=‘李军‘);

44、查询和“李军”同性别并同班的同学Sname.

select sname from student a where ssex=(select ssex from student b where b.sname=‘李军‘ ) and class=(select class from student c where c.sname=‘李军‘)

45、查询所有选修“计算机导论”课程的“男”同学的成绩表（三表关联）

select * from score A where A.cno in (select B.cno from course B where B.cname=‘计算机导论‘) and A.sno in (select C.sno from student C where C.ssex=‘男‘);

select A.* from score A join (Course B,Student C) using(cno,sno) where B.cname=‘计算机导论‘ and C.ssex=‘男‘;

2.3.     基础练习二及参考sql语句

数据库中有三张表，分别为student,course,SC（即学生表，课程表，选课表）

//三张表截图如下：

创建表:

CREATE TABLE STUDENT(

SNO VARCHAR(8) NOT NULL,

SNAME VARCHAR(5) NOT NULL,

SSEX VARCHAR(2) NOT NULL,

SAGE VARCHAR(3) NOT NULL,

SDEPT VARCHAR(7) NOT NULL);

INSERT INTO STUDENT (SNO,SNAME,SSEX,SAGE,SDEPT) VALUES (9512101,’李勇’,’男’,19,’计算机系’);

INSERT INTO STUDENT (SNO,SNAME,SSEX,SAGE,SDEPT) VALUES (9512102,‘刘晨‘,‘男‘ ,20,’计算机系’)

INSERT INTO STUDENT (SNO,SNAME,SSEX,SAGE,SDEPT) VALUES (9512103,‘王敏‘ ,‘女‘ ,20,’计算机系’);

INSERT INTO STUDENT (SNO,SNAME,SSEX,SAGE,SDEPT) VALUES (9521101,‘张立‘ ,‘男‘ ,22,’信息系’);

INSERT INTO STUDENT (SNO,SNAME,SSEX,SAGE,SDEPT) VALUES (9521102,‘吴宾‘ ,‘女‘ ,21,’信息系’);

INSERT INTO STUDENT (SNO,SNAME,SSEX,SAGE,SDEPT) VALUES (9521103,‘张海‘ ,‘男‘ ,20,’信息系’);

INSERT INTO STUDENT (SNO,SNAME,SSEX,SAGE,SDEPT) VALUES (9531101,‘钱小力‘ ,‘女‘ ,18,’数学系’);

INSERT INTO STUDENT (SNO,SNAME,SSEX,SAGE,SDEPT) VALUES (9531102,‘王大力‘ ,‘男‘ ,19,’数学系

CREATE TABLE COURSE(

CNO VARCHAR(4) NOT NULL,

CNAME VARCHAR(7) NOT NULL,

HOURS VARCHAR(3) NOT NULL);

insert into course (cno,cname,hours) values (‘c01‘,‘计算机文化 学,70);

insert into course (cno,cname,hours) values (‘c01‘,‘VB‘,90);

insert into course (cno,cname,hours) values (‘c03‘,‘计算机网络‘,80);

insert into course (cno,cname,hours) values (‘c04‘,‘数据库基础‘,108);

insert into course (cno,cname,hours) values (‘c05‘,‘高等数学‘,180);

insert into course (cno,cname,hours) values (‘c06‘,‘数据结构‘,72);

CREATE TABLE SC(

SNO VARCHAR(8) NOT NULL,

CNO VARCHAR(4) NOT NULL,

GRADE VARCHAR(6) NOT NULL)

insert into sc (sno,cno,grade) values (‘9512101‘,‘c01‘,90);

insert into sc (sno,cno,grade) values (‘9512101‘,‘c02‘,86);

insert into sc (sno,cno,grade) values (‘9512101‘,‘c06‘,‘<NULL>‘);

insert into sc (sno,cno,grade) values (‘9512102‘,‘c02‘,78);

insert into sc (sno,cno,grade) values (‘9512102‘,‘c04‘,66);

insert into sc (sno,cno,grade) values (‘9521102‘,‘c01‘,82);

insert into sc (sno,cno,grade) values (‘9521102‘,‘c02‘,75);

insert into sc (sno,cno,grade) values (‘9521102‘,‘c04‘,92);

insert into sc (sno,cno,grade) values (‘9521102‘,‘c05‘,50);

insert into sc (sno,cno,grade) values (‘9521103‘,‘c02‘,68);

insert into sc (sno,cno,grade) values (‘9521103‘,‘c06‘,‘<NULL>‘);

insert into sc (sno,cno,grade) values (‘9531101‘,‘c01‘,80);

insert into sc (sno,cno,grade) values (‘9531101‘,‘c05‘,95);

insert into sc (sno,cno,grade) values (‘9531102‘,‘c05‘,85);

1、分别查询学生表和学生修课表中的全部数据。

Select * from student;

Select * from sc;

2、查询成绩在70到80分之间的学生的学号、课程号和成绩。

select sno,cno,grade from sc where grade between 70 and 80;

3、查询C01号课程成绩最高的分数。

select grade from sc where cno=‘c01‘ order by grade desc limit 1;

4、查询学生都选修了哪些课程,要求列出课程号。

select cno as 课程号,cname as 课程名称 from course where cno in (select cno from sc);

5、查询修了C02号课程的所有学生的平均成绩、最高成绩和最低成绩。

select avg(grade) as 平均成绩,max(grade) as 最高成绩,min(grade) as 最低成绩  from sc where cno=‘c02‘;

6、统计每个系的学生人数。

Select count(学生id) from 表 group by 系字段；

select sdept,count(sno) from student group by sdept;

7、统计每门课程的修课人数和考试最高分。

select cname,count(*) as 修课人数,max(grade) from course,sc where course.cno=sc.cno group by cname;

8、统计每个学生的选课门数,并按选课门数的递增顺序显示结果。

select sname,count(sc.sno) as 选课人数 from sc,student where student.sno=sc.sno group by sname order by count(sc.sno) asc;

9、统计选修课的学生总数和考试的平均成绩。

select count(distinct sno) as 学生总数,avg(grade) as 平均成绩 from sc;

10、查询选课门数超过2门的学生的平均成绩和选课门数。

Group by 一般最后用作求数或者排名用

select sname,avg(sc.grade),count(sc.cno) from student,sc where sc.sno=student.sno group by sname having count(sc.sno)>2;

11、列出总成绩超过200分的学生,要求列出学号、总成绩。

Select sno as 学号,sum(grade) as 总成绩 from sc group by sno having sum(grade)>200;

12、查询选修了c02号课程的学生的姓名和所在系。

select sname as 姓名,sdept as 所在系 from student where sno in (select sno from sc where sc.cno=‘c02‘);

select sname,sdept from student join sc using (sno) where sc.cno=‘c02‘;

13、查询成绩80分以上的学生的姓名、课程号和成绩,并按成绩的降序排列结果。

select A.sname,B.sno,B.grade from student A,sc B where A.sno=B.sno and B.grade>80 order by grade desc;

使用using匹配相同字段必须加()

select A.sname,B.sno,B.grade from student A join sc B using (sno) where B.grade>80 order by grade desc;

14、查询计算机系男生修了"数据库基础"的学生的姓名、性别、成绩。

select A.sname,A.ssex,B.grade,C.cname from student A,sc B,course C where A.sno=B.sno and B.cno=C.cno and A.sdept=‘计算机系‘ and C.cname=‘数据库基础‘;

15、查询哪些学生的年龄相同,要求列出年龄相同的学生的姓名和年龄。

select A.sname as 年龄相同的学生姓名, A.sage as 年龄 from student A join student B on A.sage=B.sage group by A.sname,A.sage order by A.sage;

这种做法看似正确，其实多过滤出来三组数据；

select A.sname as 年龄相同的学生姓名, A.sage as 年龄 from student A join student B on A.sage in (select sage from student where A.sage=B.sage and A.sname!=B.sname) group by A.sname,A.sage order by A.sage;

16、查询哪些课程没有人选,要求列出课程号和课程名。

Select cno as 课程号,cname as 课程名 from course where cno not in (select cno from sc);

17、查询有考试成绩的所有学生的姓名、修课名称及考试成绩要求将查询结果放在一张新的永久表(假设新表名为new-sc)中。

Select student.sname,course.cname,sc.grade from student,course,sc where student.sno =sc.sno=course.cno=sc.cno and sc.grade is not null order by sname;

18、分别查询信息系和计算机系的学生的姓名、性别、修课名称、修课成绩,并要求将这两个查询结果合并成一个结果集,并以系名、姓名、性别、修课名称、修课成绩的顺序显示各列。(Union)

Select student.ssame,student.ssex,course.cname,sc.grade from student,course,sc where student.sdept=’计算机系’ and student.sno=sc.sno and course.cno=sc.cno;

19、用子查询实现如下查询:

• 查询选修了C01号课程的学生的姓名和所在系。

Select distinct student.sname,student.sdept from student,sc where student.sno in (select sc.sno from sc where sc.cno=’c01’);

• 查询数学系成绩80分以上的学生的学号、姓名。

Select sno,sname from student where sno in (select sno from sc where sc.grade>80) and sno in (select sno from student where sdept =’数学系’);

• 查询计算机系学生所选的课程名.

Select course.cname from course where cno in (select cno from sc sc.sno in (select sno from student where sdept=’计算机系’));

2.4.     Mysql基础知识补充（fromMySQL必知必会 有知识点会和上面重合！）

2.4.1.     如何使用 UPDATE修改单个表中字段值：（e.g.）

update course set cno=‘c02‘ where cname=‘VB‘;

通用格式如下:

UPDATE <表名> SET 字段 1=值 1 [,字段 2=值 2… ] [WHERE 子句 ] [ORDER BY 子句] [LIMIT 子句]

语法说明如下：

<表名>：用于指定要更新的表名称。

• SET 子句：用于指定表中要修改的列名及其列值。其中，每个指定的列值可以是表达式，也可以是该列对应的默认值。如果指定的是默认值，可用关键字 DEFAULT 表示列值。
• WHERE 子句：可选项。用于限定表中要修改的行。若不指定，则修改表中所有的行。
• ORDER BY 子句：可选项。用于限定表中的行被修改的次序。
• LIMIT 子句：可选项。用于限定被修改的行数。
• 注意：修改一行数据的多个列值时，SET 子句的每个值用逗号分开即可。

2.4.2.     mysql如何修改字段属性：由varchar(4)修改为varchar(8)

alter table course modify column cname varchar(8);

2.4.3.     待补充

1. 必知必会；

2. pycharm如何驱动mysql

MySQL基础和习题强化

原文：https://www.cnblogs.com/yuyukun/p/11973904.html