简明SQL初级教程

简明MySQL教程

创建新user并添加password及权限

■创建用户scott，密码为tiger

create user 'scott'@'localhost' identified by 'tiger'

■添加用户权限：

grant select, insert, update, delete, create, create view, drop,    execute, references on *.* to 'scott'@'localhost';

■ 若要启用远程数据库连接，并赋予所有操作权限则：

    grant all privileges on *.* to 'scott'@'%' identified by 'tiger';

■ 若仅仅开放某个IP地址的数据库操作权限，则：

    grant all privileges on *.* to 'scott'@'ipAddress'identified by 'tiger';

create 和 drop

■创建table:

create table Course (    courseID char(5),    subjectID char(4) not null,    courseNumber integer,    title varchar(50) not null,    numOfCredits integer,    primary key(courseID));create table Student (    ssn char(9),    firstName varchar(25),    mi char(1),    lastName varchar(25),    birthDate date,    street varchar(25),    phone char(11),    zipCode char(5),    deptId char(4),    primary key(ssn));create table Errollment (    ssn char(9),    courseID char(5),    dateREgistered date,    grade char(1),    primary key (ssn, courseID),    foreign key (ssn) references         Student(ssn),    foreign key (courseID) references        Course(courseID));

■删除table

drop table Course;

insert, update, delete

■插入数据项

insert into Course (courseId, subjectId, courseNumber, title, numOfCredits)values ('11113', 'CSCI', '3720', 'Database Systems', 3);

■更新数据项信息

update Courseset numOfCredits = 4where title = 'Database Systems';

■删除table

delete from Coursewhere title = 'Database Systems';

like, between-and, is null关键字

■like:

lastName like '_mi%'表示第2字母为m，第3个字字母为i的字符串lastName，其中_表示单个字符，%表示多个字符

■between, and:

v between v1 and v2 等价于 v >= v1 and v <= v2v not between v1 and v2 等价于 v < v1 or v > v2.

■null

v is null v is not null

■应用：

select ssnfrom Enrollmentwhere grade between 'C' and 'A';

列的别号

由于create一个新的table的时候，colome中不能含有空格，因此，显示表格的时候可读性会降低，因此，使用别号显示可以增强描述性，如下应用：

select lastName as "Last Name", zipCode as "Zip Code"from Studentwhere deptId = 'CS';

使用算术运算符

+, -, *, /都可以在sql中使用
假设每节课有50min，则打印总课时的命令如下：

select title, 50 * numOfCredits as "Lecture Minutes Per Week"from Coursewhere subjectId = 'CSCI';

distinct消除重复元素

当有重复元素时，采用distinct关键字：

select distinct subjectId as "Subject ID"from Course;

当选择多个列的时候，当且仅当两个列的元素完全相同才消除：

select distinct subjectId, titlefrom Course;

order有序显示数据

desc为降序，asc为升序：

select lastName, firstName, deptIdfrom Studentwhere deptId = 'CS'order by lastName desc, firstName asc;

联系多个数据表格

select distinct lastName, firstName, courseIdfrom Student, Enrollmentwhere Student.ssn = Enrollment.ssn andlastName = 'Smith' and firstName = 'Jacob'