MySQL基础一
MySQL基础一
安装,使用,概述
数据库操作
数据表操作
数据行操作
练习题
MAC上安装MySQL
参考: https://www.jianshu.com/p/2d902dd4fff4
用dmg的方式安装。Download MySQL Community Server
下载完成后，双击打开一路确定，但是当弹出一个MYSQL Installer提示框的时候打开备忘录复制粘贴记下弹出框的密码2018-02-01T02:52:44.474550Z 1 [Note] A temporary password is generated for root@localhost: /kL_M_zXd3rA
打开MySQL服务
在设置中打开
配置路径
在terminal输入open .bash_profile，加入PATH=$PATH:/usr/local/mysql/bin并保存.
然后, 在terminal输入source ~/.bash_profile路径就配置好了
登陆并修改密码
mysql -u root -p登陆，输入之前保存的密码
修改密码
进入mysql后, 在mysql>后面输入SET PASSWORD FOR 'root'@'localhost' = PASSWORD('newpassword'); 来修改密码
使用
启动服务器端, 启动客户端连接服务端
启动服务器端:
按Start MySQL Server就是启动服务器端
客户端连接1(MySQL提供的客户端):
在terminal输入mysql -u root -p
->输入密码
详解:
mysql -u root -h 192.168.1.1 -p (-u指user, -h指host, -P指port(默认为3306), -p指password)
客户端连接2(navicat):
....
概述
数据(Data)
数据库(DataBase - DB): 数据库即存放数据的仓库，只不过这个仓库是在计算机存储设备上，而且数据是按一定的格式存放的
数据库管理系统(DataBase Management System - DBMS):
一个软件, 可以高效获取和维护数据. 如MySQL、Oracle、SQLite、Access、MS SQL Server...
功能 - 1. 将数据保存到文件或内存 2. 接收特定的命令, 然后对文件进行相应的操作
数据库管理软件分类
分两大类：
关系型：如sqllite，db2，oracle，access，sql server，MySQL，注意：sql语句通用
非关系型：mongodb，redis，memcache
关系型数据库需要有表结构.
非关系型数据库是key-value存储的, 没有表结构.
SQL(Structured Query Language): 结构化语言, 专门用来与数据库通信的语言.
SQL语言主要用于存取数据、查询数据、更新数据和管理关系数据库系统, SQL语言由IBM开发. SQL语言分为3种类型：
1. DDL语句 数据库定义语言: 数据库、表、视图、索引、存储过程, 例如CREATE DROP ALTER
2. DML语句 数据库操纵语言: 插入数据INSERT、删除数据DELETE、更新数据UPDATE、查询数据SELECT
3. DCL语句 数据库控制语言: 例如控制用户的访问权限GRANT、REVOKE
通俗来说:
数据库: 文件夹
数据表: 文件
数据行: 文件中的一行数据
数据库操作
1. 显示数据库
SHOW DATABASES;
默认数据库：
mysql - 存储用户权限相关数据
information_schema - 存储MySQL本身架构相关数据
performance_schema - 用于收集数据库服务器性能参数, 记录处理查询请求时发生的各种事件,锁等现象
2. 创建数据库
CREATE DATABASE 数据库名 DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
3. 使用数据库
USE 数据库名;
4. 用户管理
创建用户 create user '用户名'@'IP地址' identified by '密码'; 删除用户 drop user '用户名'@'IP地址'; 修改用户 rename user '用户名'@'IP地址' to '新用户名'@'IP地址'; 修改密码 set password for '用户名'@'IP地址' = Password('新密码'); 注: 在本地的话IP地址可用localhost或者127.0.0.1
5. 授权管理
show grants for '用户名'@'IP地址' -- 查看权限 grant 权限 on 数据库名.表名 to '用户名'@'IP地址' -- 授权 revoke 权限 on 数据库名.表名 from '用户名'@'IP地址' -- 取消权限
all privileges 除grant外的所有权限 select 仅查权限 select,insert 查和插入权限 ... usage 无访问权限 alter 使用alter table alter routine 使用alter procedure和drop procedure create 使用create table create routine 使用create procedure create temporary tables 使用create temporary tables create user 使用create user, drop user, rename user和revoke all privileges create view 使用create view delete 使用delete drop 使用drop table execute 使用call和存储过程 file 使用select into outfile 和 load data infile grant option 使用grant 和 revoke index 使用index insert 使用insert lock tables 使用lock table process 使用show full processlist select 使用select show databases 使用show databases show view 使用show view update 使用update reload 使用flush shutdown 使用mysqladmin shutdown(关闭MySQL) super 使用change master, kill, logs, purge, master和set global. 还允许mysqladmin调试登陆 replication client 服务器位置的访问 replication slave 由复制从属使用
对于目标数据库以及内部其他： 数据库名.* --数据库中的所有 数据库名.表 --指定数据库中的某张表 数据库名.存储过程 --指定数据库中的存储过程 *.* --所有数据库
用户名@IP地址 --用户只能在改IP下才能访问 用户名@192.168.1.% --用户只能在改IP段下才能访问(通配符%表示任意) 用户名@% --用户可以再任意IP下访问(默认IP地址为%)
***远程连接
数据表操作
1. 显示表
show tables; desc 表名;
2. 创建表
create table 表名( 列名 类型 是否可以为空, 列名 类型 是否可以为空 )ENGINE=InnoDB DEFAULT CHARSET=utf8;
是否可以为空, null表示空: not null -- 不可为空 null -- 可以为空
默认值: 创建列时可以指定默认值, 当插入数据时如果未主动设置, 则自动添加默认值 create table tb1( nid int not null defalut 2, num int not null )
自增: 如果为某列设置自增列, 插入数据时无需设置此列, 默认将自增(表中只能有一个自增列) create table tb1( nid int not null auto_increment primary key, num int null ) 或 create table tb1( nid int not null auto_increment, num int null, index(nid) ) 注意：1、对于自增列, 必须是索引(含主键) 2、对于自增可以设置步长和起始值 show session variables like 'auto_inc%'; set session auto_increment_increment=2; set session auto_increment_offset=10; shwo global variables like 'auto_inc%'; set global auto_increment_increment=2; set global auto_increment_offset=10;
主键: 一种特殊的唯一索引, 不允许有空值(null), 如果主键使用单个列, 则它的值必须唯一, 如果是多列, 则其组合必须唯一. create table tb1( nid int not null auto_increment primary key, num int null ) 或 create table tb1( nid int not null, num int not null, primary key(nid,num) --联合起来做主键 )
设置唯一约束(unique): create table department1( id int, name varchar(20) unique, comment varchar(100) ); 或 create table department2( id int, name varchar(20), comment varchar(100), constraint uk_name unique(name) );
主键和唯一都是索引, 可以加快查找速度(寻找速度极快). 区别在于一张表只能有一个主键，唯一不能重复，不能为null. 而唯一可以为null，一张表可以有多个唯一列.
外键: 强制加上约束, 使两者联系; 新增时, 必须按约束添加
外键(foreign key): 一个特殊的索引, 只能是指定内容. 建立两张表的约束, 一对多. creat table color( nid int not null primary key, name char(16) not null ) create table fruit( nid int not null primary key, smt char(32) null , color_id int not null, constraint fk_cc foreign key (color_id) references color(nid) )
数据库中, 表的类型不同, 会对应mysql不同的存取机制, 表类型又称为存储引擎. 存储引擎说白了就是如何存储数据, 如何为存储的数据建立索引和如何更新, 查询数据等技术的实现方法. 因为在关系数据库中数据的存储是以表的形式存储的, 所以存储引擎也可以称为表类型（即存储和操作此表的类型） 在Oracle 和SQL Server等数据库中只有一种存储引擎, 所有数据存储管理机制都是一样的. 而MySql数据库提供了多种存储引擎. --InnoDB 存储引擎 支持事务,其设计目标主要面向联机事务处理(OLTP)的应用。其 特点是行锁设计、支持外键,并支持类似 Oracle 的非锁定读,即默认读取操作不会产生锁。 从 MySQL 5.5.8 版本开始是默认的存储引擎。 InnoDB 存储引擎将数据放在一个逻辑的表空间中,这个表空间就像黑盒一样由 InnoDB 存储引擎自身来管理。从 MySQL 4.1(包括 4.1)版本开始,可以将每个 InnoDB 存储引擎的 表单独存放到一个独立的 ibd 文件中。此外,InnoDB 存储引擎支持将裸设备(row disk)用 于建立其表空间。 InnoDB 通过使用多版本并发控制(MVCC)来获得高并发性,并且实现了 SQL 标准 的 4 种隔离级别,默认为 REPEATABLE 级别,同时使用一种称为 netx-key locking 的策略来 避免幻读(phantom)现象的产生。除此之外,InnoDB 存储引擎还提供了插入缓冲(insert buffer)、二次写(double write)、自适应哈希索引(adaptive hash index)、预读(read ahead) 等高性能和高可用的功能。 对于表中数据的存储,InnoDB 存储引擎采用了聚集(clustered)的方式,每张表都是按 主键的顺序进行存储的,如果没有显式地在表定义时指定主键,InnoDB 存储引擎会为每一 行生成一个 6 字节的 ROWID,并以此作为主键。 InnoDB 存储引擎是 MySQL 数据库最为常用的一种引擎,Facebook、Google、Yahoo 等 公司的成功应用已经证明了 InnoDB 存储引擎具备高可用性、高性能以及高可扩展性。对其 底层实现的掌握和理解也需要时间和技术的积累。如果想深入了解 InnoDB 存储引擎的工作 原理、实现和应用,可以参考《MySQL 技术内幕:InnoDB 存储引擎》一书。 --MyISAM 存储引擎 不支持事务、表锁设计、支持全文索引,主要面向一些 OLAP 数 据库应用,在 MySQL 5.5.8 版本之前是默认的存储引擎(除 Windows 版本外)。数据库系统 与文件系统一个很大的不同在于对事务的支持,MyISAM 存储引擎是不支持事务的。究其根 本,这也并不难理解。用户在所有的应用中是否都需要事务呢?在数据仓库中,如果没有 ETL 这些操作,只是简单地通过报表查询还需要事务的支持吗?此外,MyISAM 存储引擎的 另一个与众不同的地方是,它的缓冲池只缓存(cache)索引文件,而不缓存数据文件,这与 大多数的数据库都不相同。 --NDB 存储引擎 年,MySQL AB 公司从 Sony Ericsson 公司收购了 NDB 存储引擎。 NDB 存储引擎是一个集群存储引擎,类似于 Oracle 的 RAC 集群,不过与 Oracle RAC 的 share everything 结构不同的是,其结构是 share nothing 的集群架构,因此能提供更高级别的 高可用性。NDB 存储引擎的特点是数据全部放在内存中(从 5.1 版本开始,可以将非索引数 据放在磁盘上),因此主键查找(primary key lookups)的速度极快,并且能够在线添加 NDB 数据存储节点(data node)以便线性地提高数据库性能。由此可见,NDB 存储引擎是高可用、 高性能、高可扩展性的数据库集群系统,其面向的也是 OLTP 的数据库应用类型。 --Memory 存储引擎 正如其名,Memory 存储引擎中的数据都存放在内存中,数据库重 启或发生崩溃,表中的数据都将消失。它非常适合于存储 OLTP 数据库应用中临时数据的临时表,也可以作为 OLAP 数据库应用中数据仓库的维度表。Memory 存储引擎默认使用哈希 索引,而不是通常熟悉的 B+ 树索引。 --Infobright 存储引擎 第三方的存储引擎。其特点是存储是按照列而非行的,因此非常 适合 OLAP 的数据库应用。其官方网站是 http://www.infobright.org/,上面有不少成功的数据 仓库案例可供分析。 --NTSE 存储引擎 网易公司开发的面向其内部使用的存储引擎。目前的版本不支持事务, 但提供压缩、行级缓存等特性,不久的将来会实现面向内存的事务支持。 --BLACKHOLE 黑洞存储引擎，可以应用于主备复制中的分发主库。 MySQL 数据库还有很多其他存储引擎,上述只是列举了最为常用的一些引擎. 甚至可以编写专属于自己的引擎,这就是开源赋予我们的能力,也是开源的魅力所在.
3. 删除表
drop table 表名;
4. 清空表
delete from 表名; --清空表内容 truncate table 表名; --清空表内容, 速度快, 自增回到原点
5. 修改表
添加列：alter table 表名 add 列名 类型; 删除列：alter table 表名 drop column 列名; 修改列： alter table 表名 modify column 列名 类型; -- 类型 alter table 表名 change 原列名 新列名 类型; -- 列名, 类型 添加主键： alter table 表名 add primary key(列名); 删除主键： alter table 表名 drop primary key; alter table 表名 modify 列名 int, drop primary key; 添加外键：alter table 从表名 add constraint 外键名称(形如: FK_从表名_主表名) foreign key 从表名(外键字段) references 主表名(主键字段); 删除外键：alter table 表名 drop foreign key 外键名称; 修改默认值：ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000; 删除默认值：ALTER TABLE testalter_tbl ALTER i DROP DEFAULT;
6. 基本数据类型
MySQL的数据类型大致分为：数值, 字符串和时间
数值: tinyint[(m)] [unsigned] [zerofill] 小整数, 数据类型用于保存一些范围的整数数值范围： 有符号： -128 ～ 127 无符号： 0 ～ 255 特别的: MySQL中无布尔值, 使用tinyint(1)构造. int[(m)][unsigned][zerofill] 整数, 数据类型用于保存一些范围的整数数值范围： 有符号： -2147483648 ～ 2147483647 无符号： 0 ～ 4294967295 特别的: 整数类型中的m仅用于显示, 对存储范围无限制. 例如: int(5),当插入数据2时, select 时数据显示为: 00002 bigint[(m)][unsigned][zerofill] 大整数，数据类型用于保存一些范围的整数数值范围： 有符号： -9223372036854775808 ～ 9223372036854775807 无符号： 0 ～ 18446744073709551615 decimal[(m[,d])] [unsigned] [zerofill] 准确的小数值, m是数字总个数(负号不算), d是小数点后个数. m最大值为65, d最大值为30. 特别的：对于精确数值计算时需要用此类型 decaimal能够存储精确值的原因在于其内部按照字符串存储. FLOAT[(M,D)] [UNSIGNED] [ZEROFILL] 单精度浮点数(非准确小数值), m是数字总个数, d是小数点后个数. 无符号： -3.402823466E+38 to -1.175494351E-38, 1.175494351E-38 to 3.402823466E+38 有符号： 1.175494351E-38 to 3.402823466E+38 **** 数值越大, 越不准确 **** DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL] 双精度浮点数(非准确小数值), m是数字总个数, d是小数点后个数. 无符号： -1.7976931348623157E+308 to -2.2250738585072014E-308 2.2250738585072014E-308 to 1.7976931348623157E+308 有符号： 2.2250738585072014E-308 to 1.7976931348623157E+308 **** 数值越大, 越不准确 **** 字符串: char (m) --定长(固定长度, 在内存开辟固定空间存储), 查找速度快, 但浪费空间**** char数据类型用于表示固定长度的字符串, 可以包含最多达255个字符. 其中m代表字符串的长度. 注: 即使数据小于m长度, 也会占用m长度 varchar(m) --定长(自定义长度), 查找速度慢, 但节省空间**** varchars数据类型用于变长的字符串, 可以包含最多达255个字符. 其中m代表该数据类型所允许保存的字符串的最大长度, 只要长度小于该最大值的字符串都可以被保存在该数据类型中. 注: 虽然varchar使用起来较为灵活, 但是从整个系统的性能角度来说, char数据类型的处理速度更快 ,有时甚至可以超出varchar处理速度的50%. 因此, 用户在设计数据库时应当综合考虑各方面的因素, 以求达到最佳的平衡. text text数据类型用于保存变长的大字符串, 可以组多到65535 (2**16 − 1)个字符 mediumtext A TEXT column with a maximum length of 16,777,215 (2**24 − 1) characters. longtext A TEXT column with a maximum length of 4,294,967,295 or 4GB (2**32 − 1) characters. enum **** 枚举类型 An ENUM column can have a maximum of 65,535 distinct elements. (The practical limit is less than 3000.) 示例： CREATE TABLE shirts ( name VARCHAR(40), size ENUM('x-small', 'small', 'medium', 'large', 'x-large') ); INSERT INTO shirts (name, size) VALUES ('dress shirt','large'), ('t-shirt','medium'),('polo shirt','small'); set **** 集合类型 A SET column can have a maximum of 64 distinct members. 示例： CREATE TABLE myset (col SET('a', 'b', 'c', 'd')); INSERT INTO myset (col) VALUES ('a,d'), ('d,a'), ('a,d,a'), ('a,d,d'), ('d,a,d'); **** 枚举和集合的分别在于前者是单选, 后者可以是多选 **** 时间: DATE YYYY-MM-DD（1000-01-01/9999-12-31） TIME HH:MM:SS（'-838:59:59'/'838:59:59'） YEAR YYYY(1901/2155） DATETIME YYYY-MM-DD HH:MM:SS (1000-01-01 00:00:00/9999-12-31 23:59:59 Y） TIMESTAMP YYYYMMDD HHMMSS（1970-01-01 00:00:00/2037 年某时） 二进制数据: bit[(m)] 二进制位(101001), m表示二进制位的长度(1-64), 默认m＝1 TinyBlob, Blob, MediumBlob, LongBlob --用于上传文件, Blob, 强制二进制方式
数据表内容(数据行)操作
1. 增
insert into 表名 (列名,列名...) values (值,值,值...); insert into 表名 (列名,列名...) values (值,值,值...),(值,值,值...); insert into 表名 (列名,列名...) select 另一列名,另一列名... from 另一表名;
2. 删
delete from 表名; delete from 表名 where id＝1 and name＝'charon';
3. 改
update 表名 set name ＝ 'charon' where id>2;
4. 查
select * from 表名; select * from 表名 where id > 1; select nid,name,gender as gg from 表名 where id > 1; --as gg 意为在显示表时用gg代替gender显示
5. 其他
a、条件 select * from 表名 where id > 1 and name != 'charon' and num = 12; select * from 表名 where id between 5 and 16; select * from 表名 where id in (11,22,33) select * from 表名 where id not in (11,22,33) select * from 表名 where id in (select nid from 表名) -- subquery b、通配符 select * from 表名 where name like 'ala%' -- ala开头的所有(多个字符串） select * from 表名 where name like 'ala_' -- ala开头的所有(一个字符） c、限制 select * from 表名 limit 5; -- 前5行 select * from 表名 limit 4,5; -- 从第4行开始的5行 select * from 表名 limit 5 offset 4; -- 从第4行开始的5行(推荐使用) d、排序 select * from 表名 order by 列 asc; -- 根据'列'从小到大排列 select * from 表名 order by 列 desc; -- 根据'列'从大到小排列 select * from 表名 order by 列1 desc,列2 asc; -- 根据'列1'从大到小排列,如果相同则按列2从小到大排序 e、分组(分页) select num from 表名 group by num; select num,nid from 表名 group by num,nid; select num,nid from 表名 where nid > 10 group by num,nid order nid desc; 注: group by 必须在where之后, order by之前 select num,nid,count(*),sum(score),max(score),min(score) from 表 group by num,nid; select num from 表名 group by num having max(id) > 10; 注: 用group by 要有聚合函数, 对聚合条件进行筛选的时候, 不用where 用having f、连表 **** 无对应关系则不显示 select A.num, A.name, B.name from A,B where A.nid = B.nid; 无对应关系则不显示 优先使用join select A.num, A.name, B.name from A inner join B on A.nid = B.nid; 注: 使用inner join则永远不会出现null A表所有显示, 如果B中无对应关系, 则值为null select A.num, A.name, B.name from A left join B on A.nid = B.nid; 注: left join表示左边的表a为主表, 全称应为left outer join B表所有显示, 如果B中无对应关系, 则值为null select A.num, A.name, B.name from A right join B on A.nid = B.nid; g、组合 组合, 自动处理重合 select nickname from 表名 union select name from 另一表名 组合, 不处理重合 select nickname from 表名 union all select name from 另一表名
练习题
例:
1、导入导出测试数据
2、查询'生物'课程比'物理'课程成绩高的所有学生的学号；
3、查询平均成绩大于60分的同学的学号和平均成绩；
4、查询所有同学的学号、姓名、选课数、总成绩；
5、查询姓'李'的老师的个数；
6、查询没学过'李平老师'课的同学的学号、姓名；
7、查询学过'1'并且也学过编号'2'课程的同学的学号、姓名；
8、查询学过'李平老师'所教的所有课的同学的学号、姓名；
9、查询课程编号'2'的成绩比课程编号'1'课程低的所有同学的学号、姓名；
10、查询有课程成绩小于60分的同学的学号、姓名；
11、查询没有学全所有课的同学的学号、姓名；
12、查询至少有一门课与学号为'1'的同学所学相同的同学的学号和姓名；
13、查询至少学过学号为'1'同学所选课程中任意一门课的其他同学学号和姓名；
14、查询和'2'号的同学学习的课程完全相同的其他同学学号和姓名；
15、删除学习'李平老师'课的score表记录；
16、向score表中插入一些记录, 这些记录要求符合以下条件：①没有上过编号'2'课程的同学学号；②插入'2'号课程的平均成绩；
17、按平均成绩从低到高显示所有学生的所有课程成绩, 按如下形式显示: 学生ID,生物,物理,体育,美术,有效课程数,有效平均分；
18、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分；
19、按各科平均成绩从低到高和及格率的百分数从高到低顺序；
20、课程平均分从高到低显示（现实任课老师）；
21、查询各科成绩前三名的记录:(不考虑成绩并列情况)
22、查询每门课程被选修的学生数；
23、查询出只选修了一门课程的全部学生的学号和姓名；
24、查询男生、女生的人数；
25、查询姓“张”的学生名单；
26、查询同名同姓学生名单，并统计同名人数；
27、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列；
28、查询平均成绩大于85的所有学生的学号、姓名和平均成绩；
29、查询课程名称为“数学”，且分数低于60的学生姓名和分数；
30、查询课程编号为'3'且课程成绩在80分以上的学生的学号和姓名；
31、求选了课程的学生人数
32、查询选修“杨艳”老师所授课程的学生中，成绩最高的学生姓名及其成绩；
33、查询各个课程及相应的选修人数；
34、查询不同课程但成绩相同的学生的学号、课程号、学生成绩；
35、查询每门课程成绩最好的前两名；
36、检索至少选修两门课程的学生学号；
37、查询全部学生都选修的课程的课程号和课程名；
38、查询没学过'李平老师'讲授的任一门课程的学生姓名；
39、查询两门以上不及格课程的同学的学号及其平均成绩；
40、检索'4'课程分数小于60，按分数降序排列的同学学号；
41、删除'2'同学的'1'课程的成绩；
表结构和数据:
/* Navicat Premium Data Transfer Source Server : localhost Source Server Type : MySQL Source Server Version : 50624 Source Host : localhost Source Database : sqlexam Target Server Type : MySQL Target Server Version : 50624 File Encoding : utf-8 Date: 10/21/2016 06:46:46 AM */ SET NAMES utf8; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for `class` -- ---------------------------- DROP TABLE IF EXISTS `class`; CREATE TABLE `class` ( `cid` int(11) NOT NULL AUTO_INCREMENT, `caption` varchar(32) NOT NULL, PRIMARY KEY (`cid`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of `class` -- ---------------------------- BEGIN; INSERT INTO `class` VALUES ('1', '三年二班'), ('2', '三年三班'), ('3', '一年二班'), ('4', '二年九班'); COMMIT; -- ---------------------------- -- Table structure for `course` -- ---------------------------- DROP TABLE IF EXISTS `course`; CREATE TABLE `course` ( `cid` int(11) NOT NULL AUTO_INCREMENT, `cname` varchar(32) NOT NULL, `teacher_id` int(11) NOT NULL, PRIMARY KEY (`cid`), KEY `fk_course_teacher` (`teacher_id`), CONSTRAINT `fk_course_teacher` FOREIGN KEY (`teacher_id`) REFERENCES `teacher` (`tid`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of `course` -- ---------------------------- BEGIN; INSERT INTO `course` VALUES ('1', '生物', '1'), ('2', '物理', '2'), ('3', '体育', '3'), ('4', '美术', '2'); COMMIT; -- ---------------------------- -- Table structure for `score` -- ---------------------------- DROP TABLE IF EXISTS `score`; CREATE TABLE `score` ( `sid` int(11) NOT NULL AUTO_INCREMENT, `student_id` int(11) NOT NULL, `course_id` int(11) NOT NULL, `num` int(11) NOT NULL, PRIMARY KEY (`sid`), KEY `fk_score_student` (`student_id`), KEY `fk_score_course` (`course_id`), CONSTRAINT `fk_score_course` FOREIGN KEY (`course_id`) REFERENCES `course` (`cid`), CONSTRAINT `fk_score_student` FOREIGN KEY (`student_id`) REFERENCES `student` (`sid`) ) ENGINE=InnoDB AUTO_INCREMENT=53 DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of `score` -- ---------------------------- BEGIN; INSERT INTO `score` VALUES ('1', '1', '1', '10'), ('2', '1', '2', '9'), ('5', '1', '4', '66'), ('6', '2', '1', '8'), ('8', '2', '3', '68'), ('9', '2', '4', '99'), ('10', '3', '1', '77'), ('11', '3', '2', '66'), ('12', '3', '3', '87'), ('13', '3', '4', '99'), ('14', '4', '1', '79'), ('15', '4', '2', '11'), ('16', '4', '3', '67'), ('17', '4', '4', '100'), ('18', '5', '1', '79'), ('19', '5', '2', '11'), ('20', '5', '3', '67'), ('21', '5', '4', '100'), ('22', '6', '1', '9'), ('23', '6', '2', '100'), ('24', '6', '3', '67'), ('25', '6', '4', '100'), ('26', '7', '1', '9'), ('27', '7', '2', '100'), ('28', '7', '3', '67'), ('29', '7', '4', '88'), ('30', '8', '1', '9'), ('31', '8', '2', '100'), ('32', '8', '3', '67'), ('33', '8', '4', '88'), ('34', '9', '1', '91'), ('35', '9', '2', '88'), ('36', '9', '3', '67'), ('37', '9', '4', '22'), ('38', '10', '1', '90'), ('39', '10', '2', '77'), ('40', '10', '3', '43'), ('41', '10', '4', '87'), ('42', '11', '1', '90'), ('43', '11', '2', '77'), ('44', '11', '3', '43'), ('45', '11', '4', '87'), ('46', '12', '1', '90'), ('47', '12', '2', '77'), ('48', '12', '3', '43'), ('49', '12', '4', '87'), ('52', '13', '3', '87'); COMMIT; -- ---------------------------- -- Table structure for `student` -- ---------------------------- DROP TABLE IF EXISTS `student`; CREATE TABLE `student` ( `sid` int(11) NOT NULL AUTO_INCREMENT, `gender` char(1) NOT NULL, `class_id` int(11) NOT NULL, `sname` varchar(32) NOT NULL, PRIMARY KEY (`sid`), KEY `fk_class` (`class_id`), CONSTRAINT `fk_class` FOREIGN KEY (`class_id`) REFERENCES `class` (`cid`) ) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of `student` -- ---------------------------- BEGIN; INSERT INTO `student` VALUES ('1', '男', '1', '理解'), ('2', '女', '1', '钢蛋'), ('3', '男', '1', '张三'), ('4', '男', '1', '张一'), ('5', '女', '1', '张二'), ('6', '男', '1', '张四'), ('7', '女', '2', '铁锤'), ('8', '男', '2', '李三'), ('9', '男', '2', '李一'), ('10', '女', '2', '李二'), ('11', '男', '2', '李四'), ('12', '女', '3', '如花'), ('13', '男', '3', '刘三'), ('14', '男', '3', '刘一'), ('15', '女', '3', '刘二'), ('16', '男', '3', '刘四'); COMMIT; -- ---------------------------- -- Table structure for `teacher` -- ---------------------------- DROP TABLE IF EXISTS `teacher`; CREATE TABLE `teacher` ( `tid` int(11) NOT NULL AUTO_INCREMENT, `tname` varchar(32) NOT NULL, PRIMARY KEY (`tid`) ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of `teacher` -- ---------------------------- BEGIN; INSERT INTO `teacher` VALUES ('1', '张磊老师'), ('2', '李平老师'), ('3', '刘海燕老师'), ('4', '朱云海老师'), ('5', '李杰老师'); COMMIT; SET FOREIGN_KEY_CHECKS = 1;
导入导出:
使用terminal:
导出现有数据库数据：
mysqldump -u用户名 -p密码 数据库名称 > 导出文件路径 # 结构+数据
mysqldump -u用户名 -p密码 -d 数据库名称 > 导出文件路径 # 结构
导入现有数据库数据：
mysqldump -u用户名 -p密码 数据库名称 < 文件路径
使用navicat:
导入: 可在Query复制黏贴表结构和数据, 运行并刷新.
导出: 点击 Dump SQL File 即可导出
答案:
2、查询'生物'课程比'物理'课程成绩高的所有学生的学号； 思路： 获取所有有生物课程的人（学号，成绩） - 临时表 获取所有有物理课程的人（学号，成绩） - 临时表 根据【学号】连接两个临时表： 学号 物理成绩 生物成绩 然后再进行筛选 select A.student_id,sw,ty from (select student_id,num as sw from score left join course on score.course_id = course.cid where course.cname = '生物') as A left join (select student_id,num as ty from score left join course on score.course_id = course.cid where course.cname = '体育') as B on A.student_id = B.student_id where sw > if(isnull(ty),0,ty); 3、查询平均成绩大于60分的同学的学号和平均成绩； 思路： 根据学生分组，使用avg获取平均值，通过having对avg进行筛选 select student_id,avg(num) from score group by student_id having avg(num) > 60 4、查询所有同学的学号、姓名、选课数、总成绩； select score.student_id,sum(score.num),count(score.student_id),student.sname from score left join student on score.student_id = student.sid group by score.student_id 5、查询姓“李”的老师的个数； select count(tid) from teacher where tname like '李%' select count(1) from (select tid from teacher where tname like '李%') as B 6、查询没学过“叶平”老师课的同学的学号、姓名； 思路： 先查到“李平老师”老师教的所有课ID 获取选过课的所有学生ID 学生表中筛选 select * from student where sid not in ( select DISTINCT student_id from score where score.course_id in ( select cid from course left join teacher on course.teacher_id = teacher.tid where tname = '李平老师' ) ) 7、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名； 思路： 先查到既选择001又选择002课程的所有同学 根据学生进行分组，如果学生数量等于2表示，两门均已选择 select student_id,sname from (select student_id,course_id from score where course_id = 1 or course_id = 2) as B left join student on B.student_id = student.sid group by student_id HAVING count(student_id) > 1 8、查询学过“叶平”老师所教的所有课的同学的学号、姓名； 同上，只不过将001和002变成 in (叶平老师的所有课) 9、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名； 同第1题 10、查询有课程成绩小于60分的同学的学号、姓名； select sid,sname from student where sid in ( select distinct student_id from score where num < 60 ) 11、查询没有学全所有课的同学的学号、姓名； 思路： 在分数表中根据学生进行分组，获取每一个学生选课数量 如果数量 == 总课程数量，表示已经选择了所有课程 select student_id,sname from score left join student on score.student_id = student.sid group by student_id HAVING count(course_id) != (select count(1) from course) 12、查询至少有一门课与学号为“001”的同学所学相同的同学的学号和姓名； 思路： 获取 001 同学选择的所有课程 获取课程在其中的所有人以及所有课程 根据学生筛选，获取所有学生信息 再与学生表连接，获取姓名 select student_id,sname, count(course_id) from score left join student on score.student_id = student.sid where student_id != 1 and course_id in (select course_id from score where student_id = 1) group by student_id 13、查询至少学过学号为“001”同学所有课的其他同学学号和姓名； 先找到和001的学过的所有人 然后个数 ＝ 001所有学科 ＝＝》 其他人可能选择的更多 select student_id,sname, count(course_id) from score left join student on score.student_id = student.sid where student_id != 1 and course_id in (select course_id from score where student_id = 1) group by student_id having count(course_id) ＝ (select count(course_id) from score where student_id = 1) 14、查询和“002”号的同学学习的课程完全相同的其他同学学号和姓名； 个数相同 002学过的也学过 select student_id,sname from score left join student on score.student_id = student.sid where student_id in ( select student_id from score where student_id != 1 group by student_id HAVING count(course_id) = (select count(1) from score where student_id = 1) ) and course_id in (select course_id from score where student_id = 1) group by student_id HAVING count(course_id) = (select count(1) from score where student_id = 1) 15、删除学习“叶平”老师课的score表记录； delete from score where course_id in ( select cid from course left join teacher on course.teacher_id = teacher.tid where teacher.name = '叶平' ) 16、向SC表中插入一些记录，这些记录要求符合以下条件：①没有上过编号“002”课程的同学学号；②插入“002”号课程的平均成绩； 思路： 由于insert 支持 inset into tb1(xx,xx) select x1,x2 from tb2; 所有，获取所有没上过002课的所有人，获取002的平均成绩 insert into score(student_id, course_id, num) select sid,2,(select avg(num) from score where course_id = 2) from student where sid not in ( select student_id from score where course_id = 2 ) 17、按平均成绩从低到高 显示所有学生的所有课程成绩，按如下形式显示： 学生ID,语文,数学,英语,有效课程数,有效平均分； select sc.student_id, (select num from score left join course on score.course_id = course.cid where course.cname = "生物" and score.student_id=sc.student_id) as sy, (select num from score left join course on score.course_id = course.cid where course.cname = "物理" and score.student_id=sc.student_id) as wl, (select num from score left join course on score.course_id = course.cid where course.cname = "体育" and score.student_id=sc.student_id) as ty, count(sc.course_id), avg(sc.num) from score as sc group by student_id desc 18、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分； select course_id, max(num) as max_num, min(num) as min_num from score group by course_id; 19、按各科平均成绩从低到高和及格率的百分数从高到低顺序； 思路：case when .. then select course_id, avg(num) as avgnum,sum(case when score.num > 60 then 1 else 0 END)/count(1)*100 as percent from score group by course_id order by avgnum asc,percent desc; 20、课程平均分从高到低显示（现实任课老师）； select avg(if(isnull(score.num),0,score.num)),teacher.tname from course left join score on course.cid = score.course_id left join teacher on course.teacher_id = teacher.tid group by score.course_id 21、查询各科成绩前三名的记录:(不考虑成绩并列情况) select score.sid,score.course_id,score.num,T.first_num,T.second_num from score left join ( select sid, (select num from score as s2 where s2.course_id = s1.course_id order by num desc limit 0,1) as first_num, (select num from score as s2 where s2.course_id = s1.course_id order by num desc limit 3,1) as second_num from score as s1 ) as T on score.sid =T.sid where score.num <= T.first_num and score.num >= T.second_num 22、查询每门课程被选修的学生数； select course_id, count(1) from score group by course_id; 23、查询出只选修了一门课程的全部学生的学号和姓名； select student.sid, student.sname, count(1) from score left join student on score.student_id = student.sid group by course_id having count(1) = 1 24、查询男生、女生的人数； select * from (select count(1) as man from student where gender='男') as A , (select count(1) as feman from student where gender='女') as B 25、查询姓'张'的学生名单； select sname from student where sname like '张%'; 26、查询同名同姓学生名单，并统计同名人数； select sname,count(1) as count from student group by sname; 27、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列； select course_id,avg(if(isnull(num), 0 ,num)) as avg from score group by course_id order by avg asc,course_id desc; 28、查询平均成绩大于85的所有学生的学号、姓名和平均成绩； select student_id,sname, avg(if(isnull(num), 0 ,num)) from score left join student on score.student_id = student.sid group by student_id; 29、查询课程名称为“数学”，且分数低于60的学生姓名和分数； select student.sname,score.num from score left join course on score.course_id = course.cid left join student on score.student_id = student.sid where score.num < 60 and course.cname = '生物' 30、查询课程编号为003且课程成绩在80分以上的学生的学号和姓名； select * from score where score.student_id = 3 and score.num > 80 31、求选了课程的学生人数 select count(distinct student_id) from score select count(c) from ( select count(student_id) as c from score group by student_id) as A 32、查询选修“杨艳”老师所授课程的学生中，成绩最高的学生姓名及其成绩； select sname,num from score left join student on score.student_id = student.sid where score.course_id in (select course.cid from course left join teacher on course.teacher_id = teacher.tid where tname='张磊老师') order by num desc limit 1; 33、查询各个课程及相应的选修人数； select course.cname,count(1) from score left join course on score.course_id = course.cid group by course_id; 34、查询不同课程但成绩相同的学生的学号、课程号、学生成绩； select DISTINCT s1.course_id,s2.course_id,s1.num,s2.num from score as s1, score as s2 where s1.num = s2.num and s1.course_id != s2.course_id; 35、查询每门课程成绩最好的前两名； select score.sid,score.course_id,score.num,T.first_num,T.second_num from score left join ( select sid, (select num from score as s2 where s2.course_id = s1.course_id order by num desc limit 0,1) as first_num, (select num from score as s2 where s2.course_id = s1.course_id order by num desc limit 1,1) as second_num from score as s1 ) as T on score.sid =T.sid where score.num <= T.first_num and score.num >= T.second_num 36、检索至少选修两门课程的学生学号； select student_id from score group by student_id having count(student_id) > 1 37、查询全部学生都选修的课程的课程号和课程名； select course_id,count(1) from score group by course_id having count(1) = (select count(1) from student); 38、查询没学过“叶平”老师讲授的任一门课程的学生姓名； select student_id,student.sname from score left join student on score.student_id = student.sid where score.course_id not in ( select cid from course left join teacher on course.teacher_id = teacher.tid where tname = '张磊老师' ) group by student_id 39、查询两门以上不及格课程的同学的学号及其平均成绩； select student_id,count(1) from score where num < 60 group by student_id having count(1) > 2 40、检索“004”课程分数小于60，按分数降序排列的同学学号； select student_id from score where num< 60 and course_id = 4 order by num desc; 41、删除“002”同学的“001”课程的成绩； delete from score where course_id = 1 and student_id = 2
浙公网安备 33010602011771号