SQL 语句在Mysql 5.6中的实例(初级):
术语:
关系 relation -- 表
元组 tuple -- 表中的一行记录
属性 attribute -- 表的列
域 domain -- 属性的数据类型,如varchar(50)
Path环境变量中配置好,把mysql\bin目录加入
mysql -u root -p
******
create database test;
use test;
create table department (dept_name varchar(20), building varchar(20), budget numeric(12,2), primary key (dept_name));
create table course (course_id varchar(7), title varchar(50) not null, dept_name varchar(20), credits numeric(2,0), primary key(course_id), foreign key (dept_name) references department(dept_name));
create table instructor(ID varchar(5), name varchar(20) not null, dept_name varchar(20), salary numeric(8,2), primary key (ID), foreign key (dept_name) references department(dept_name));
insert into department values ('Biology', 'Yifu Building', 1000000.00);
insert into course values (100001, 'Biology Science Basic', 'Biology', 2);
delete from course; //删除course中的所有元组
drop table course;
alter table department add person_in_charge varchar(20); //给department表加一列
alter table department drop person_in_charge; //删除department表的一列
insert into course values (30001, 'C Language Basic', 'ComputerScience', 3);
insert into course values (30101, 'Java Language Basic', 'ComputerScience', 4);
select dept_name from course;
select distinct dept_name from course; //去除重复的结果
select dept_name from department where building = 'Yifu Building';
select budget * 1.1 from dept_name where budget >= 500000 and budget < 1000000;
一个SQL查询的含义可以理解如下:
1. 为from子句中列出的关系产生笛卡尔积
2. 在步骤1的结果上应用where子句中指定的谓词
3. 对于步骤2结果中的每个元组,输出select子句中指定的属性
natural join:
自然连接生成两个关系中具有相同属性的笛卡尔积
select * from instructor natural join department;
等同于:
select * from instructor, department where instructor.dept_name = department.dept_name;
排序:order by ATTRIBUTE asc/desc
聚集函数:avg, min, max, sum, count
注意Mysql中,avg (attribute),avg后面可以有空格,但其他几个后面不能有空格,如sum(salary)
select max(salary) from instructor; //最高的工资
select count(*) from instructor; //用于取得元组的个数
分组: group by ATTRIBUTE
按照某属性对元组进行分组,语句执行在这写分组的基础上
select avg(salary) from instructor group by dept_name; //取得各个系的平均工资
having子句
对分组找到相应的结果以后,再在此基础上继续加以查询的时候,用having语句
select dept_name, avg(salary) as avg_salary from instructor group by dept_name having avg(salary) > 50000; //平均工资大于50000的系