面试题-数据库试题

一.单选、下列SQLA、 修改表结 更新表数D、 录入表数Update表名setUpdate表名set字段名称=字段值,zd1=v1.... 条A、updateintousersetidB、updateusersetid'u001andusername='jack';C、updateusersetusername='jack'whereid=1;D、updateintousersetidu001',、用户表user中存在一个字段username,查询名字包含"凤"（不考虑位置A、select*fromuserwhereusername凤B、select*fromuserwhereusernamelike凤%';C、select*fromuserwhereusernamelike'_凤_';D、select*fromuserwhereusernamelike'凤';第一页第一页：limit --- 格式：limit开始索引,每页显示的个A、select*fromtestlimit10,10;B、select*fromtestlimit11,10;C、select*fromtestlimit10,20;D、select*fromtestlimit、用户表usersage和countint | | |count |u001 18 60 执行“selectagecountfromuserswhereid'u001';”结果为？UpdateaccountUpdateaccountsetmoney=money+ 二.多选BnullC、主键必须在创建表是指定(3种)D、sql语句主键预留的关键为：primaryA、外键是为了保证从表与主表之间的数据一致性和完整性（关系BnullC、性Djava A、starttransaction;开启事务Bjava C、rollback;D、delete;三.简答*本题中的数据库database的名称获得:4个用户名 例：1234 Statement对象查询所有数据，打印到控制台获得Connectionconn=Statementst=sqlStringsql=“selectid,title,pricefrombooks”;ResultSetrs=st.executeQuery(sql);while(rs.next()getString(String字段 索引getString(String字段 索引号从1开始Doubleprice=rs.getDouble(“price”);syso…}使用PreparedStatement对象录入(b004,ssh,125) Connectionconn …省略1sqlStringsql=“insertintobooks(id,title,price)2PreparedStatementpsmt=//3设置内容4intrpstm.executeUpdatesqlif(r>}else}//5资roles into`privileges`(`id`,`privilegename`,`description`,`url`)values('p001','添加','具有录入的功能','/functions/addBook.jsp'),('p002','查询','查询查询查询查询('p003','更新','更新更新更新更新','/functions/updateBook.jsp'),('p004','删除','删除删除删除','/functions/deleteBook.jsp'); into`roles`(`id`,`rolename`,`description`valuessqlsql查询权限表rolename1.sqlsql并提供至少两个字段，一个是rolesprivileges#createtableprivileges(idvarchar(32),privilegenamevarchar(50),descriptionvarchar(200),urlvarchar(200)#createtableroles(idrolenamevarchar(50),descriptionvarchar(200) into`privileges`(`id`,`privilegename`,`description`,`url`values('p001','添加','具有录入的功能','/functions/addBook.jsp'),('p002','查询','查询查询查询查询','/functions/findBook.jsp'),('p003','更新','更新更新更新更新','/functions/updateBook.jsp'),('p004','删除','删除删除删除','/functions/deleteBook.jsp'); into`roles`(`id`,`rolename`,`description`values##createtableroles_id #privileges_idvarchar(32)####1.rolesaltertablerolesaddconstraintprimarykeyid);#2.roles对应外键altertableroles_privilegesaddconstraintforeignkey(roles_id)references3privilegesaltertableprivilegesaddconstraintprimarykey(id);#4privileges对应外键altertableroles_privilegesaddconstraintforeignkey(privileges_id)referencesselect权限表rolenameinsertintoroles_privileges(roles_id,privileges_id)values('r001','p001');insertintoroles_privileges(roles_id,privileges_id)values('r001','p002');insertintoroles_privileges(roles_id,privileges_id)values('r001','p003');insertintoroles_privileges(roles_id,privileges_id)values('r001','p004');insertintoroles_privileges(roles_id,privileges_id)values('r002','p001');insertintoroles_privileges(roles_id,privileges_id)values('r002','p003');selectprivileges.*fromprivileges,roles_privileges,whereprivileges.idroles_privileges.privileges_id