软件开发技术基础6.ppt

软件开发技术基础 顾 刚 计算机教学实验中心 关系数据库标准语言SQL 建立表 CREATE TABLE employee (eid CHAR(6) NOT NULL PRIMARY KEY, name VARCHAR(8) NOT NULL), age NUMERIC(3,0), country VARCHAR(10) NULL ) 插入记录 ?INSERT INTO employee(eid, name, age, country) VALUES (‘3018’, ‘zhang’, 28, ‘China’) ? INSERT INTO employee VALUES (‘3056’, ‘wang’, 35, ‘USA’) ? INSERT INTO employee(eid, name, country) VALUES (‘4025’, ‘li’, ‘Russia’) 修改记录 UPDATE employee SET name=’Li Ming’ WHERE eid=’4025’ UPDATE employee SET name=’Zhang Li’, age=36,country=’USA’ WHERE eid=’3056’ 可以修改多个字段的值。如果不带WHERE修改所有的记录 删除记录 DELETE employee WHERE eid=’3018’ 查询记录 查询举例 ?SELECT pub_id, pub_name, country FROM publishers ? SELECT * FROM publishers SELECT city, state FROM authers SELECT DISTICT city, state FROM authers ?SELECT title_id, type, price, price-price*0.3 FROM title ? ?SELECT au_lname+’.’+au_fname, city+’.’+state FROM authors 条件：= = = != price45.3 country=’China’ NOT AND OR Country=’China’ AND age35 ?SELECT title_id, type, price, price - price*0.3 FROM titles WHERE price - price*0.318 ? SELECT pub_id, pub_name FROM publishers WHERE country=’USA’ AND state=’CA’ 字符串中%表示匹配任意字符串 字符串中_表示匹配任意一个字符 SELECT au_lname+’.’+au_fname, city+’.’+state FROM authors WHERE au_fname LIKE ‘D%’ SELECT au_lname+’.’+au_fname, city+’.’+state FROM authors WHERE au_id LIKE ‘72_-%’ 通常返回的结果是随机排列的。? SELECT stor_name, city FROM stores ORDER BY store_name 升序和降序排列结果 SELECT stor_name, city FROM stores ORDER BY store_name DESC ? SELECT stor_name, city FROM stores ORDER BY store_name ASC SUM（expression） AVG（ expression ） MIN（ expression ） MAX（ expression ） COUNT（ expression ） COUNT（*） SELECT AVG(price) FROM titles ? SELECT MAX(price) FROM titles WHERE type=’business’ SELECT AVG(qty), SUM(qty), COUNT(DISTICT stor_id) FROM sales ? SELECT COUNT（*） FROM publishers WHERE state=’CA’ SELECT type, AVG(price), SUM(pric