mysql基本语法总结

可以放在文件中，然后cmd中输入：source F:\代码。。。。\平时练习代码\数据库\selectDatas.sql

mysql -uroot -puse mysql;--????

创建数据库：

create database javabook;use javabook;

创建表：

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 Enrollment(    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));

删除表：

drop table Course;

插入、更新、删除：

insert into Course (courseId,subjectId,courseNumber,title,numOfCredits)values('1114','CSCJ','3721','Datadase Systemt',4);update Courseset numOfCredits=4where title='Datadase Systems';delete from Coursewhere title='Datadase Systemt';

查询：
select…from…where。
not and or
like（%，_），between…and…，is null
as：别名
distinct：不重复
order by：asc升序，desc降序，默认升序

select numOfCredits,subjectIdfrom Coursewhere courseNumber='3720' and numOfCredits=4;select numOfCredits as "NC",courseNumberfrom Coursewhere numOfCredits between 1 and 10;select title,50*numOfCredits as "minutes per week"from Coursewhere subjectId="CSCI";select distinct subjectId as "Subject ID"from course;select distinct subjectId,titlefrom Course;select lastName,firstName,deptIdfrom Studentwhere deptId='CS'order by lastName desc,firstName asc;

联结表：

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