跟数据库打交道,SQL语句还是经常用到,把常用的语句写写,复习一下顺便查缺补漏。
本文所用的数据库是MYSQL,先来看看版本:
SELECT VERSION()
1.数据库的操作
创建一个数据库sqltest
CREATE DATABASE sqltest;
SHOW DATABASES;
![]()
删除数据库sqltest
连接数据库DROP DATABASE sqltest; SHOW DATABASES;
USE sqltest;
2.表的操作
链接数据库以后我们就可以在该数据库上进行建表等操作,先建几个表CREATE TABLE Student (Sno CHAR(9) PRIMARY KEY, Sname CHAR(20), Ssex CHAR(2), Sage SMALLINT, Sdept CHAR(20) );
上面表有点特别,就是外键跟主键在同一个表,这是允许的。ON UPDATE CASCADE表示被参照表主键更新时,参照表也更新。ON DELETE CASCADE表示被参照表删除时,该表也删除。CREATE TABLE Course (Cno CHAR(4) PRIMARY KEY, Cname CHAR(20), Cpno CHAR(4), Ccredit SMALLINT, FOREIGN KEY(Cpno) REFERENCES Course(Cno) ON UPDATE CASCADE ON DELETE CASCADE );
CREATE TABLE SC (Sno CHAR(20) , Cno CHAR(4), Grade SMALLINT, PRIMARY KEY(Sno,Cno), FOREIGN KEY(Sno) REFERENCES Student(Sno) ON UPDATE CASCADE ON DELETE CASCADE, FOREIGN KEY(Cno) REFERENCES Course(Cno) ON UPDATE CASCADE ON DELETE CASCADE );
SHOW TABLES;
![]()
可以看到在windows下面,mysql对表名并不区分大小写。
常见的修改表的操作:
ALTER TABLE student ADD Sentrance DATE;//增加一个字段
ALTER TABLE studernt MODIFY Sage INT;//修改字段类型
ALTER TABLE student CHANGE Sentrance Sentranceday DATE;//修改字段名称
ALTER TABLE student DROP COLUMN Sentranceday;//删除一个字段名
删除表的操作,删除表有两个权限,一是RESTRICT,该权限也是默认的权限。在该权限下欲删除的基本表不能被其他表的约束所引用(如外键等),不能有视图,不能有触发器,不能有存储过程或函数等,二是CASCADE,在删除该表的同时,相关的依赖对象一起删除。但MYSQL不提供对CASCADE的支持。ALTER TABLE course ADD UNIQUE(Cname);//增加约束条件
DROP TABLE student;
DROP TABLE student CASCADE;
![]()
可以看出两个都删除不了。删除表sc的外键约束,再试试可不可以删除,先通过下面命令找到sc外键约束的名称。
SHOW CREATE TABLE sc;
![]()
ALTER TABLE sc DROP FOREIGN KEY sc_ibfk_1;
DROP TABLE student;
![]()
显示删除成功,把删除的表约束加回去。
ALTER TABLE sc ADD FOREIGN KEY(sno) REFERENCES student(sno) ON UPDATE RESTRICT ON DELETE RESTRICT;
3.数据更新
插入一些数据。INSERT INTO STUDENT(SNO,SNAME,SSEX,SAGE,SDEPT) VALUES('200215121','李勇','男','20','CS');
INSERT INTO STUDENT VALUES('200215122','刘晨','女','19','CS');
INSERT INTO STUDENT VALUES('200215123','WANG','女','19','CS'),<pre name="code" class="sql">('200215125','ZHANG','男','19','CS');
INSERT INTO COURSE VALUES <pre name="code" class="sql">('2','数学',NULL,'4'),<pre name="code" class="sql">('6','数据处理',NULL,'4'),<pre name="code" class="sql">('4','操作系统','6','4'),<pre name="code" class="sql">('7','PASCAL语言','6','4'),<pre name="code" class="sql">('5','数据结构','7','4'),<pre name="code" class="sql">('1','数据库','5','4'),<pre name="code" class="sql">('3','信息系统','1','4');
插入子查询结果INSERT INTO SC VALUES('200215121','1','92'), ('200215121','2','85'), ('200215121','3','88'), ('200215122','2','90'), ('200215123','3','80');
CREATE TABLE DEPTAGE(SDEPT CHAR(20), AVGAGE SMALLINT );
更新数据INSERT INTO DEPTAGE SELECT <span style="font-family: Arial, Helvetica, sans-serif;">SDEPT,AVG(SAGE) FROM STUDENT GROUP BY SDEPT;//对student表按系求分组年龄,然后把系名跟平均年龄存入新表deptage;</span>
将计算机科学的所有学生成绩变为0UPDATE STUDENT SET SDEPT='MA' WHERE SNO='200215123';UPDATE STUDENT SET SDEPT='IS' WHERE SNO='200215125';
UPDATE SC SET GRADE='0' WHERE 'CS'=(SELECT SDEPT FROM STUDENT WHERE STUDENT.SNO=SC.SNO);
![]()
删除数据DELETE FROM STUDENT WHERE SNO='200115121';
4.数据查询
查询时可以经过计算,比如查询全体学生的姓名及其出生年月;用户可以通过指定别名来改变查询结果的列标题,比如:SELECT SNAME,2014-SAGE FROM STUDENT;
![]()
SELECT SNAME NAME,'YEAR OF BIRTH' BIRTH,2014-SAGE BITHDAY,LOWER(SDEPT) DEPARTMENT FROM STUDENT;
![]()
DISTINCT取消重复的行SELECT DISTINCT SNO FROM SC;
![]()
确定范围,between...AND和NOT BETWEEN...AND...SELECT * FROM STUDENT WHERE SAGE BETWEEN 19 AND 20;
![]()
确定集合,IN 和 NOT INSELECT * FROM STUDENT WHERE SDEPT IN ('CS','MA');
![]()
字符匹配,LIKE,其中%表示任意长度的字符串,_表示任意单个字符,注意中文要用两个_表示。同时也有对应的NOT LIKE,用ESCAPE表示转译字符。SELECT * FROM COURSE WHERE CNAME LIKE 'DB/_DESIGN'ESCAPE'/';
![]()
涉及空值的查询,用IS null。排序用ORDER BY,默认为升序,降序为ORDER BY DESC。集聚函数有COUNT(*/列名),SUM(),AVG(),MIN(),MAX()SELECT COUNT(*) FROM STUDENT;
![]()
GROUP BY子句按照一列或多列分组,值相等的为一组。求选修每门课的名称和人数。SELECT CNO,COUNT(*) FROM SC GROUP BY CNO;
如果分组后还有对组的结过进行筛选,那么用HAVING语句。