数据库课程笔记..docxVIP

7.15创建数据库：create database表名（名称不能含空格符等）数据库建表（主键不可少）约束也很重要删除数据库：drop database表名数据库数据类型：Char:定长字符串，最大2000字节varcahr4000字节最多Integer、int、smallit、decimal(设置含2个小数位)1、查询t_user_base_info表中字段为name值为‘abc’的记录select * from t_user_base_info where user_name=abc2、更新t_user_base_info表中字段为user_name的值从‘u’更新为’abc’updatet_user_base_info SET user_name=abc where user_name=u3、向t_user_base_info中插入一条记录insert?into?t_user_base_info(id,user_id,user_name,salary,num,consume)记录的值如下：value(4,999,eee,3.14,20,9.15)4、删除t_user_base_info表中id=‘5’的那条记录delete from t_user_base_info where id=55、创建一个表名为t_user_test_info的表CREATE TABLE t_user_test_info ( id int(11) NOT NULL,设置字段和数据类型user_idvarchar(255) default NULL,user_namevarchar(255) default NULL, PRIMARY KEY (id)主键设为’id’)6、创建索引t_user_test_info_idx_1?一般索引t_user_test_info_un_idx_1?唯一索引7、唯一索引的创建代码CREATE?TABLE?t_user_test_info?(??id?int(11)?NOT?NULL,??user_id?varchar(255)?default?NULL,??user_name?varchar(255)?default?NULL,??PRIMARY?KEY??(id),??UNIQUE?KEY?t_user_test_info_un_idx_1?USING?BTREE?(user_id))8、一般索引的创建代码CREATE?TABLE?t_user_test_info?(??id?int(11)?NOT?NULL,??user_id?varchar(255)?default?NULL,??user_name?varchar(255)?default?NULL,??PRIMARY?KEY??(id),??KEY?t_user_test_info_idx_1?USING?BTREE?(user_id))9、计算分数（score）的总和select?sum(score)?from?t_student_info10、统计student_no并命名为student_numselect?COUNT(student_no)?as?student_num?from?t_student_info11、统计出表中大于等于80分的记录select?*??from?t_student_info?where?score=8012、统计出表中不等于80分的记录select?*??from?t_student_info?where?score8013、统计出表中不等于80分的记录select?*??from?t_student_info?where?score!=8014、统计出表中小于80分的记录select?*??from?t_student_info?where?score8015、根据type统计出各门课程的总分select?type,sum(score)?as?total?from?t_student_info?group?by?type16、根据学号studen_no统计出各个学生总分数select?student_no,sum(score)?as?total?from?t_student_info?group?by?student_no17、从t_student_info表中统计出不同的课程的typeselect count(a.type)as type_num from (SELECT type FROM t_student_info GROUP BY type)a18、从t_student_info表中统计出不同的课程的typeSELECT count(DISTINCT type)as type_num FROM t_s