MySQL查询评论成绩两种sql_MySQL学习（二）SQL语句的总结

1、连接查询和关联查询

连接查询：把两个表中相同的元素的连接就可以查询，

使用：where里，select table1.*,table2.* from table1,table2 where table1.id=table2.id;

关联查询：把两个表关联，之后的操作对两个表都有影响(left join，right join，full join，

inner join)

使用:不用where，select table1.*,table2.* from table1 (inner)join table2 on

table1.id=table2.id;

(1)SELECT * FROM score, student WHERE score.id = student.id ...

(2)SELECT * FROM score JOIN student ON (score.id = student.id) WHERE ...

(3)SELECT * FROM score JOIN student USING (id) WHERE ...

前两种写法，只是写法不同而已，其功能完全相同；最后一种，会将同名的列，合并起来。

第一种，是传统写法，SQL89标准里，就是这种；后面两种，是在SQL92标准才有的！

2、多表查询(连接查询)关系数据库中最主要特征

无论哪种连接都不能对text、ntext和image数据类型列进行直接连接，但可以对这三种列进行间接连接。

连接：

(1)自连接；一个表自己与自己建立连接称为自连接或自身连接。可以把一个表的某一行与同一表中的

另一行连接起来。

(2)内连接；

语法：select 字段名列表 from 表名 [inner] join 表名 on 连接条件 [where 条件表达式]

分类：

(1)等值连接：是指表之间通过“等于”关系连接起来，产生一个临时表。

(2)不等值连接：是指表之间的连接关系不是“等于”，而是其它关系

这些运算符包括>、>=、<=、、!。

(3)自然连接：在等值连接中消除重复列就是自然连接。表A natural join B where 连接条件

(3)外连接；外连接是对内连接的扩充,除了将两个数据集合中重叠部分以内的那些数据行连接起来之外

,

(4)交叉连接；交叉连接不使用任何连接条件来限制结果集合,将各表的记录以“笛卡尔”积的方式组合

起来。

(5)联合查询：UNION运算符可以将两个或两个以上上SELECT语句的查询结果集合合并成一个结果集合显

示。

(6)复杂查询：---复合连接，三个表以上的连接。

3、查询平均成绩

select class_name as 班级名,avg(stu_score1)  as 平均成绩 from stu_table,class_table where stu_table.class_id=class_table.class_id;

4、查询成绩排名的前五名

select stu_name,stu_score1 from stu_table order by stu_score1 desc limit 5 ;

5、在college_id字段后边加入tel字段

alter table stu_table add tel varchar(15) after college_id;

6、-- 把tel字段修改成tel1 --

alter table stu_table change tel tel1 varchar(30);

7、新建表(SQL语句建表)(需要刷新才能见到)

create table test1(

test_id int not null auto_increment,

test_title varchar(100) not null,

test_author varchar(40) not null,

primary key ( test_id )

);

字段使用not null属性，不希望这个字段值为null。

字段使用auto_increment属性告诉我们自动带有增加id字段下一个可用编号

字段primary key用于定义此列作为主键

8、新建表(通过命令行提示符来创建表)使用数据库TUTORIALS，建立表tutorials_tbl

root@host# mysql -u root -p

Enter password:

mysql> use TUTORIALS;

Database changed

mysql> CREATE TABLE tutorials_tbl(

-> tutorial_id INT NOT NULL AUTO_INCREMENT,

-> tutorial_title VARCHAR(100) NOT NULL,

-> tutorial_author VARCHAR(40) NOT NULL,

-> submission_date DATE,

-> PRIMARY KEY ( tutorial_id )

-> );

Query OK, 0 rows affected (0.16 sec)

mysql>

9、-- 删除表 --

drop table tutorials_tbl; key用于定义此列作为主键。

10、my sql约束条件

(1)主键(PRIMARY KEY)

标识该属性为该表的主键，可以唯一的标识对应的记录。

(2)外键(FOREIGN KEY)

标识该属性为该表的外键，与某个表的主键关联。

(3)唯一性(UNIQUE)

标识该属性的值是唯一的。

(4)非空(NOT NULL)

标识该属性不能为空。

(5)默认值(DEFAULT)

为该属性设置默认值。

* MySQL不支持CHECK约束，但可以使用CHECK约束而没有任何效果。

#建表(班级表)

create table t_class(

id int primary key auto_increment, #班级id为主键，id设置自增长

className varchar(20) unique not null #班级名称，不能为空且唯一

);

#创建学生表

create table t_student(

id int primary key auto_increment, #学生id为主键，id自增长

name varchar(20) unique not null, #学生姓名，不能为空且唯一

sex varchar(1) not null, #学生性别，不能为空

birthday datetime not null default '1992-10-01 00:00:00', #学生出生日期，不能为空，默认

值为1992-10-01 00:00:00

classId int, #学生所属的班级id为外键，关联班级表't_class'的主键id

constraint test_class foreign key(classId) references t_class(id) #创建外键约束，约束名

为test_class

);

11、mysql视图

理由(原因)：

(1)安全性，用于权限与视图进行绑定，使用特性:grant语句针对视图进行授予权限

(2)查询性能提高

(3)灵活性的功能需求，改动表的结构工作量大，使用虚表达到少修改效果

(4)复杂的查询需求

视图的内容没有存储，而是在视图被引用的时候才派生出数据。这样不会占用空间，由于是即时引

用，视图的内容总是与真实表的内容是一致的。

视图这样设计有什么好处？节省空间，内容是总是一致的话，那么我们不需要维护视图的内容，维

护好真实表的内容，就可以保证视图的完整性了。

针对每个表创建一个视图(注：视图也是一种表，是虚拟表。不能与已有的表(视图)出现重名)

create view teams(字段，字段，字段) as select distinct(字段，字段) from 表名

例如：#创建视图

create view test3(stu_id,stu_name,stu_score1,stu_sex) as

select distinct stu_id,stu_name,stu_score1,stu_sex from stu_table;

12、数据库优化1

应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，

Sql 代码 : select id from t where num=10 or num=20;

可以这样查询：

Sql 代码 : select id from t where num=10 union all select id from t where num=20;

13、数据库优化2

in 和 not in 也要慎用，否则会导致全表扫描，如：

Sql 代码 : select id from t where num in(1,2,3);

对于连续的数值，能用 between 就不要用 in 了：

Sql 代码 : select id from t where num between 1 and 3;

14、数据库优化3

应尽量避免在 where 子句中对字段进行表达式操作， 这将导致引擎放弃使用索引而进行全表扫描。

Sql 代码 : select id from t where num/2=100;

可以这样查询：

Sql 代码 : select id from t where num=100*2;