基本语法
-- 显示所有数据库
show databases;
-- 创建数据库
CREATE DATABASE test;
-- 切换数据库
use test;
-- 显示数据库中的所有表
show tables;
-- 创建数据表
CREATE TABLE pet (
name VARCHAR(20),
owner VARCHAR(20),
species VARCHAR(20),
sex CHAR(1),
birth DATE,
death DATE
);
-- 查看数据表结构
-- describe pet;
desc pet;
-- 查询表
SELECT * from pet;
-- 插入数据 往表里面添加记录
INSERT INTO pet VALUES ('puffball', 'Diane', 'hamster', 'f', '1990-03-30', NULL);
-- 修改数据
UPDATE pet SET name = 'squirrel' where owner = 'Diane';
-- 删除数据
DELETE FROM pet where name = 'squirrel';
-- 删除表
DROP TABLE myorder;
MySQL常见数据类型
数值
类型 大小 范围(有符号) 范围(无符号) 用途
TINYINT 1 字节 (-128,127) (0,255) 小整数值
SMALLINT 2 字节 (-32 768,32 767) (0,65 535) 大整数值
MEDIUMINT 3 字节 (-8 388 608,8 388 607) (0,16 777 215) 大整数值
INT或INTEGER 4 字节 (-2 147 483 648,2 147 483 647) (0,4 294 967 295) 大整数值
BIGINT 8 字节 (-9,223,372,036,854,775,808,9 223 372 036 854 775 807) (0,18 446 744 073 709 551 615) 极大整数值
FLOAT 4 字节 (-3.402 823 466 E+38,-1.175 494 351 E-38),0,(1.175 494 351 E-38,3.402 823 466 351 E+38) 0,(1.175 494 351 E-38,3.402 823 466 E+38) 单精度
浮点数值
DOUBLE 8 字节 (-1.797 693 134 862 315 7 E+308,-2.225 073 858 507 201 4 E-308),0,(2.225 073 858 507 201 4 E-308,1.797 693 134 862 315 7 E+308) 0,(2.225 073 858 507 201 4 E-308,1.797 693 134 862 315 7 E+308) 双精度
浮点数值
DECIMAL 对DECIMAL(M,D) ,如果M>D,为M+2否则为D+2 依赖于M和D的值 依赖于M和D的值 小数值
1 日期/时间
类型 大小
(字节) 范围 格式 用途
DATE 3 1000-01-01/9999-12-31 YYYY-MM-DD 日期值
TIME 3 ‘-838:59:59’/‘838:59:59’ HH:MM:SS 时间值或持续时间
YEAR 1 1901/2155 YYYY 年份值
DATETIME 8 1000-01-01 00:00:00/9999-12-31 23:59:59 YYYY-MM-DD HH:MM:SS 混合日期和时间值
TIMESTAMP 4
1970-01-01 00:00:00/2038
结束时间是第 2147483647 秒,北京时间 2038-1-19 11:14:07,格林尼治时间 2038年1月19日 凌晨 03:14:07
YYYYMMDD HHMMSS 混合日期和时间值,时间戳
3 .字符串(字符)类型。
类型 大小 用途
CHAR 0-255字节 定长字符串
VARCHAR 0-65535 字节 变长字符串
TINYBLOB 0-255字节 不超过 255 个字符的二进制字符串
TINYTEXT 0-255字节 短文本字符串
BLOB 0-65 535字节 二进制形式的长文本数据
TEXT 0-65 535字节 长文本数据
MEDIUMBLOB 0-16 777 215字节 二进制形式的中等长度文本数据
MEDIUMTEXT 0-16 777 215字节 中等长度文本数据
LONGBLOB 0-4 294 967 295字节 二进制形式的极大文本数据
LONGTEXT 0-4 294 967 295字节 极大文本数据
数据类型如何选择 日期按照格式
数值按照格式
删除数据
mysql> select *from pet;
+----------+-----------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
+----------+-----------+---------+------+------------+------------+
| puffball | diane | hamster | f | 1996-01-12 | NULL |
| 旺财 | 周星驰 | 狗 | 公 | 1988-01-01 | NULL |
| fluffy | harild | cat | f | 1993-02-04 | NULL |
| claws | gwen | cat | m | 1994-03-17 | NULL |
| buffy | harold | dog | f | 1989-05-13 | NULL |
| fang | benny | dog | m | 1979-08-27 | NULL |
| bowser | biane | dog | m | 1979-08-31 | 1995-07-29 |
| chripy | gwen | bird | f | 1998-09-11 | NULL |
| whistler | gwen | bird | f | 1997-12-09 | NULL |
| slim | benny | sanke | m | 1996-04-29 | NULL |
| puffball | diane | hamster | f | 1999-03-30 | NULL |
| whistler | gwen | bird | f | 1997-12-09 | NULL |
| whistler | gwen | bird | f | 1999-12-09 | NULL |
+----------+-----------+---------+------+------------+------------+
delete from pet where name='whistler';
mysql> select *from pet;
+----------+-----------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
+----------+-----------+---------+------+------------+------------+
| puffball | diane | hamster | f | 1996-01-12 | NULL |
| 旺财 | 周星驰 | 狗 | 公 | 1988-01-01 | NULL |
| fluffy | harild | cat | f | 1993-02-04 | NULL |
| claws | gwen | cat | m | 1994-03-17 | NULL |
| buffy | harold | dog | f | 1989-05-13 | NULL |
| fang | benny | dog | m | 1979-08-27 | NULL |
| bowser | biane | dog | m | 1979-08-31 | 1995-07-29 |
| chripy | gwen | bird | f | 1998-09-11 | NULL |
| slim | benny | sanke | m | 1996-04-29 | NULL |
| puffball | diane | hamster | f | 1999-03-30 | NULL |
+----------+-----------+---------+------+------------+------------+
10 rows in set (0.00 sec)
修改数据
mysql> update pet set name ='旺旺财' where owner='周星驰';
Query OK, 1 row affected (0.05 sec)
+-----------+-----------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
+-----------+-----------+---------+------+------------+------------+
| puffball | diane | hamster | f | 1996-01-12 | NULL |
| 旺旺财 | 周星驰 | 狗 | 公 | 1988-01-01 | NULL |
| fluffy | harild | cat | f | 1993-02-04 | NULL |
| claws | gwen | cat | m | 1994-03-17 | NULL |
| buffy | harold | dog | f | 1989-05-13 | NULL |
| fang | benny | dog | m | 1979-08-27 | NULL |
| bowser | biane | dog | m | 1979-08-31 | 1995-07-29 |
| chripy | gwen | bird | f | 1998-09-11 | NULL |
| slim | benny | sanke | m | 1996-04-29 | NULL |
| puffball | diane | hamster | f | 1999-03-30 | NULL |
| whistler | gwen | bird | f | 1997-12-09 | NULL |
+-----------+-----------+---------+------+------------+------------+
–总结一下数据记录常见记录
–增加
insert
–删除
delete
–修改
update
–查询
select
建表约束
主键约束
它能够为确定一张表中的一条记录, 也就是我们通过给某个字段添加约束,就可以是的该字段`不重复且不为空
–外键约束
–唯一约束
–非空约束
–默认约束
-- 主键约束
-- 使某个字段不重复且不得为空,确保表内所有数据的唯一性。
CREATE TABLE user (
id INT PRIMARY KEY,
name VARCHAR(20)
);
-- 联合主键
--只要联合主键的值加起来不重复就可以,且不为空
-- 联合主键中的每个字段都不能为空,并且加起来不能和已设置的联合主键重复。
CREATE TABLE user (
id INT,
name VARCHAR(20),
password VARCHAR(20),
PRIMARY KEY(id, name)
);
```bash
insert into zhujian1 values(1,'lisi','123');
insert into zhujian1 values(1,'zhangsan','123');
insert into zhujian1 values(1,'zhangsan','123');
mysql> select *from zhujian1;
+----+----------+---------+
| id | name | pasword |
+----+----------+---------+
| 1 | lisi | 123 |
| 1 | zhangsan | 123 |
| 2 | zhangsan | 123 |
+----+----------+---------+
3 rows in set (0.00 sec)
– 自增约束
– 自增约束的主键由系统自动递增分配。
CREATE TABLE user (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20)
);
mysql> insert into zizen (name) values('zhangsan');
Query OK, 1 row affected (0.05 sec)
mysql> select *from zizen;
+----+----------+
| id | name |
+----+----------+
| 1 | zhangsan |
+----+----------+
1 row in set (0.01 sec)
| id | name |
+----+----------+
| 1 | zhangsan |
| 2 | zhangsan |
+----+----------+
如果创建表的时候忘记创建主键 怎么办? 后面添加主键
修改表结构
mysql> desc zhujian2;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
alter table zhujian2 add primary key(id);
mysql> desc zhujian2;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
删除表的主键 删除表结
alter table zhujian2 drop primary key;
mysql> desc zhujian2;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
使用modify 修改字段添加约束
alter table zhujian2 modify id int primary key;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> alter table zhujian2 modify id int primary key;
Query OK, 0 rows affected (0.42 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc zhujian2;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
– 添加主键约束
– 如果忘记设置主键,还可以通过SQL语句设置(两种方式):
ALTER TABLE user ADD PRIMARY KEY(id);
ALTER TABLE user MODIFY id INT PRIMARY KEY;
– 删除主键
ALTER TABLE user drop PRIMARY KEY;
唯一主键
–约束修改的字段的值不可以重复
– 建表时创建唯一主键
create table zhujian3 (id int ,name varchar (20));
alter table zhujian3 add unique(name);
mysql> create table zhujian3 (id int ,name varchar (20));
Query OK, 0 rows affected (0.26 sec)
mysql> alter table zhujian3 add unique(name);
Query OK, 0 rows affected (0.30 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc zhujian3;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | UNI | NULL | |
+-------+-------------+------+-----+---------+-------+
mysql> insert into zhujian3 values(1,'weiyi');
Query OK, 1 row affected (0.05 sec)
mysql> insert into zhujian3 values(1,'weiyi');
ERROR 1062 (23000): Duplicate entry 'weiyi' for key 'name'
unique 添加唯一约束不重复就可以
还可以在添加表的时候添加
create table zhujian4 (id int ,name varchar (20 ),unique (name) );
mysql> desc zhujian4;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | UNI | NULL | |
+-------+-------------+------+-----+---------+-------+
删除唯一约束
alter table zhujian4 drop index name;
mysql> desc zhujian4;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
modify 添加
alter table zhujian4 modify name varcahr(20) unique;
mysql> alter table zhujian4 modify name varchar(20) unique;
Query OK, 0 rows affected (0.19 sec)
mysql> desc zhujian4;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | UNI | NULL | |
+-------+-------------+------+-----+---------+-------+
总结 :
1建表的时候添加约束
2可以使用alter … add …
3 alter … modify …
4删除 alter …drop …
CREATE TABLE user (
id INT,
name VARCHAR(20),
UNIQUE(name)
);
– 添加唯一主键
– 如果建表时没有设置唯一建,还可以通过SQL语句设置(两种方式):
ALTER TABLE user ADD UNIQUE(name);
ALTER TABLE user MODIFY name VARCHAR(20) UNIQUE;
– 删除唯一主键
ALTER TABLE user DROP INDEX name;
## 非空约束
修饰的字段不能为空
```bash
-- 建表时添加非空约束
-- 约束某个字段不能为空
CREATE TABLE user11 (
id INT,
name VARCHAR(20) NOT NULL
);
```bash
mysql> desc user11;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | NO | | NULL | |
+-------+-------------+------+-----+---------+-------+
mysql> select *from user11;
+------+----------+
| id | name |
+------+----------+
| 1 | zhangsan |
| NULL | zhangsan |
+------+----------+
– 移除非空约束
ALTER TABLE user MODIFY name VARCHAR(20);
## 默认约束
当我们插入默认值得时候,如果没有传值,就会使用默认值
```bash
create table user12 values(id int ,name varchar(20), age int default10);
-- 建表时添加默认约束
-- 约束某个字段的默认值
CREATE TABLE user2 (
id INT,
name VARCHAR(20),
age INT DEFAULT 10
);
mysql> create table user14 (id int,name varchar(20),age int default 10);
Query OK, 0 rows affected (0.25 sec)
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| age | int(11) | YES | | 10 | |
+-------+-------------+------+-----+---------+-------+
-- 移除非空约束
ALTER TABLE user MODIFY age INT;
外键约束
–外键约束涉及到两个表 :子表,父表
班级
create table classes (id int primary key, name varchar(20));
学生表
mysql> create table students(id int, name varchar(20),class_id int, foreign key (class_id)references classes(id));.
mysql> desc classes;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> desc studentss;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| class_id | int(11) | YES | MUL | NULL | |
+----------+-------------+------+-----+---------+-------+
insert into classes values(1,'一班');
mysql> select *from classes;
+----+--------+
| id | name |
+----+--------+
| 1 | yiban |
| 2 | erban |
| 3 | sanban |
| 4 | siban |
+----+--------+
insert into studentss values(1001,'zhangsan',1);
insert into studentss values(1002,'zhangsan',2);
insert into studentss values(1003,'zhangsan',3);
insert into studentss values(1004,'zhangsan',4);
insert into studentss values(1005,'lisi',5);
mysql> select *from studentss;
+------+----------+----------+
| id | name | class_id |
+------+----------+----------+
| 1001 | zhangsan | 1 |
| 1002 | zhangsan | 2 |
| 1003 | zhangsan | 3 |
| 1004 | zhangsan | 4 |
+------+----------+----------+
RROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`studentss`, CONSTRAINT `studentss_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `classes` (`id`))
总结
1主表 classes 没用的数据在附表中,是不能使用的
2主表的记录在副表中被引用是不可以被删除的.
-- 班级
CREATE TABLE classes (
id INT PRIMARY KEY,
name VARCHAR(20)
);
-- 学生表
CREATE TABLE students (
id INT PRIMARY KEY,
name VARCHAR(20),
-- 这里的 class_id 要和 classes 中的 id 字段相关联
class_id INT,
-- 表示 class_id 的值必须来自于 classes 中的 id 字段值
FOREIGN KEY(class_id) REFERENCES classes(id)
);
-- 1. 主表(父表)classes 中没有的数据值,在副表(子表)students 中,是不可以使用的;
-- 2. 主表中的记录被副表引用时,主表不可以被删除。
数据库设计三大设计范式
1NF
数据表中所有字段都是不可分割的原子值?
create table student2(
id int primary key,
name varchar(20),
address varchar(30)
);
mysql> select *from student2 ;
+----+----------+----------------------+
| id | name | address |
+----+----------+----------------------+
| 1 | zhangsan | zhongguohubeiwuhan11 |
| 2 | zhangsan | zhongguohubeiwuhan11 |
| 3 | zhangsan | zhongguohubeiwuhan12 |
| 4 | zhangsan | zhongguohubeiwuhan12 |
+----+----------+--------------- -------+
只要字段值还可以继续拆分,就不满足第一范式。
范式设计得越详细,对某些实际操作可能会更好,但并非都有好处,需要对项目的实际情况进行设定。
2NF
在满足第一范式的前提下,其他列都必须完全依赖于主键列。如果出现不完全依赖,只可能发生在联合主键的情况下:
– 订单表
CREATE TABLE myorder (
product_id INT,
customer_id INT,
product_name VARCHAR(20),
customer_name VARCHAR(20),
PRIMARY KEY (product_id, customer_id)
);
实际上,在这张订单表中,product_name 只依赖于 product_id ,customer_name 只依赖于 customer_id 。也就是说,product_name 和 customer_id 是没用关系的,customer_name 和 product_id 也是没有关系的。
这就不满足第二范式:其他列都必须完全依赖于主键列!
CREATE TABLE myorder (
order_id INT PRIMARY KEY,
product_id INT,
customer_id INT
);
CREATE TABLE product (
id INT PRIMARY KEY,
name VARCHAR(20)
);
CREATE TABLE customer (
id INT PRIMARY KEY,
name VARCHAR(20)
);
拆分之后,myorder 表中的 product_id 和 customer_id 完全依赖于 order_id 主键,而 product 和 customer 表中的其他字段又完全依赖于主键。满足了第二范式的设计!
3NF
在满足第二范式的前提下,除了主键列之外,其他列之间不能有传递依赖关系。
CREATE TABLE myorder (
order_id INT PRIMARY KEY,
product_id INT,
customer_id INT,
customer_phone VARCHAR(15)
);
表中的 customer_phone 有可能依赖于 order_id 、 customer_id 两列,也就不满足了第三范式的设计:其他列之间不能有传递依赖关系。
CREATE TABLE myorder (
order_id INT PRIMARY KEY,
product_id INT,
customer_id INT
);
CREATE TABLE customer (
id INT PRIMARY KEY,
name VARCHAR(20),
phone VARCHAR(15)
);
修改后就不存在其他列之间的传递依赖关系,其他列都只依赖于主键列,满足了第三范式的设计!
## 查询练习
准备数据
```bash
-- 创建数据库
CREATE DATABASE select_test;
-- 切换数据库
USE select_test;
-- 创建学生表
CREATE TABLE student (
no VARCHAR(20) PRIMARY KEY,
name VARCHAR(20) NOT NULL,
sex VARCHAR(10) NOT NULL,
birthday DATE, -- 生日
class VARCHAR(20) -- 所在班级
);
-- 创建教师表
CREATE TABLE teacher (
no VARCHAR(20) PRIMARY KEY,
name VARCHAR(20) NOT NULL,
sex VARCHAR(10) NOT NULL,
birthday DATE,
profession VARCHAR(20) NOT NULL, -- 职称
department VARCHAR(20) NOT NULL -- 部门
);
-- 创建课程表
CREATE TABLE course (
no VARCHAR(20) PRIMARY KEY,
name VARCHAR(20) NOT NULL,
t_no VARCHAR(20) NOT NULL, -- 教师编号
-- 表示该 tno 来自于 teacher 表中的 no 字段值
FOREIGN KEY(t_no) REFERENCES teacher(no)
);
-- 成绩表
CREATE TABLE score (
s_no VARCHAR(20) NOT NULL, -- 学生编号
c_no VARCHAR(20) NOT NULL, -- 课程号
degree DECIMAL, -- 成绩
-- 表示该 s_no, c_no 分别来自于 student, course 表中的 no 字段值
FOREIGN KEY(s_no) REFERENCES student(no),
FOREIGN KEY(c_no) REFERENCES course(no),
-- 设置 s_no, c_no 为联合主键
PRIMARY KEY(s_no, c_no)
);
-- 查看所有表
SHOW TABLES;
-- 添加学生表数据
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-105', '92');
INSERT INTO score VALUES('103', '3-245', '86');
INSERT INTO score VALUES('103', '6-166', '85');
INSERT INTO score VALUES('105', '3-105', '88');
INSERT INTO score VALUES('105', '3-245', '75');
INSERT INTO score VALUES('105', '6-166', '79');
INSERT INTO score VALUES('109', '3-105', '76');
INSERT INTO score VALUES('109', '3-245', '68');
INSERT INTO score VALUES('109', '6-166', '81');
-- 查看表结构
1到10
-- 查询 student 表的所有行
SELECT * FROM student;
-- 查询 student 表中的 name、sex 和 class 字段的所有行
SELECT name, sex, class FROM student;
-- 查询 teacher 表中不重复的 department 列
-- department: 去重查询 distinct
SELECT DISTINCT department FROM teacher;
select distinct department from teacher;
-- 查询 score 表中成绩在60-80之间的所有行(区间查询和运算符查询)
-- BETWEEN xx AND xx: 查询区间, AND 表示 "并且"
SELECT * FROM score WHERE degree BETWEEN 60 AND 80;
SELECT * FROM score WHERE degree > 60 AND degree < 80;
-- 查询 score 表中成绩为 85, 86 或 88 的行
-- IN: 查询规定中的多个值
SELECT * FROM score WHERE degree IN (85, 86, 88);
-- 查询 student 表中 '95031' 班或性别为 '女' 的所有行
-- or: 表示或者关系
SELECT * FROM student WHERE class = '95031' or sex = '女';
-- 以 class 降序的方式查询 student 表的所有行
-- DESC: 降序,从高到低
-- ASC(默认): 升序,从低到高
SELECT * FROM student ORDER BY class DESC;
SELECT * FROM student ORDER BY class ASC;
-- 以 c_no 升序、degree 降序查询 score 表的所有行
SELECT * FROM score ORDER BY c_no ASC, degree DESC;
-- 查询 "95031" 班的学生人数
-- COUNT: 统计
SELECT COUNT(*) FROM student WHERE class = '95031';
-- 查询 score 表中的最高分的学生学号和课程编号(子查询或排序查询)。
-- (SELECT MAX(degree) FROM score): 子查询,算出最高分
SELECT s_no, c_no FROM score WHERE degree = (SELECT MAX(degree) FROM score);
-- 排序查询
-- LIMIT r, n: 表示从第r行开始,查询n条数据
SELECT s_no, c_no, degree FROM score ORDER BY degree DESC LIMIT 0, 1;
分组计算平均成绩
-- AVG: 平均值
SELECT AVG(degree) FROM score WHERE c_no = '3-105';
SELECT AVG(degree) FROM score WHERE c_no = '3-245';
SELECT AVG(degree) FROM score WHERE c_no = '6-166';
-- GROUP BY: 分组查询
SELECT c_no, AVG(degree) FROM score GROUP BY c_no;