数据库基本操作--增删改查

一、增：新增数据库、表、索引：

创建数据库

这样做就可以创建一个数据库：

CREATE DATABASE 数据库名称

创建一个表

这样做就可以创建一个数据库中的表：

CREATE TABLE 表名称
(
列名称1 数据类型,
列名称2 数据类型,
.......
)

实例

本例演示如何创建名为 "Person" 的表，有四个列。列名是："LastName"、"FirstName"、"Address" 以及 "Age"：

CREATE TABLE Person 
(
LastName varchar,
FirstName varchar,
Address varchar,
Age int
) 

这个例子演示如何为某些列定义长度的最大值：

CREATE TABLE Person 
(
LastName varchar(30),
FirstName varchar,
Address varchar,
Age int(3)
)

数据类型（data_type）规定了列可容纳何种数据类型。下面的表格包含了SQL中最常用的数据类型：

数据类型	描述
integer(size) int(size) smallint(size) tinyint(size)	仅容纳整数。在括号内规定数字的最大位数。
decimal(size,d) numeric(size,d)	容纳带有小数的数字。 "size" 规定数字的最大位数。"d" 规定小数点右侧的最大位数。
char(size)	容纳固定长度的字符串（可容纳字母、数字以及特殊字符）。 在括号中规定字符串的长度。
varchar(size)	容纳可变长度的字符串（可容纳字母、数字以及特殊的字符）。 在括号中规定字符串的最大长度。
date(yyyymmdd)	容纳日期。

创建索引

索引被创建于已有的表中，它可使对行的定位更快速更有效。可以在表格的一个或者多个列上创建索引，每个索引都会被起个名字。用户无法看到索引，它们只能被用来加速查询。

注释：更新一个包含索引的表需要比更新一个没有索引的表更多的时间，这是由于索引本身也需要更新。因此，理想的做法是仅仅在常常用于搜索的列上面创建索引。

唯一的索引 (Unique Index)

在表格上面创建某个一个唯一的索引。唯一的索引意味着两个行不能拥有相同的索引值。

CREATE UNIQUE INDEX 索引名称
ON 表名称 (列名称) 

"列名称" 规定你需要索引的列。

简单的索引

在表上创建一个简单的索引。当我们省略关键词 UNIQUE 时，就可以使用重复的值。

CREATE INDEX 索引名称
ON 表名称 (列名称)

"列名称" 规定你需要索引的列。

实例

本例会创建一个简单的索引，名为 "PersonIndex"，在 Person 表的 LastName 字段：

CREATE INDEX PersonIndex
ON Person (LastName) 

如果您希望以降序索引某个列中的值，您可以在列名称之后添加保留字 DESC：

CREATE INDEX PersonIndex
ON Person (LastName DESC) 

假如您希望索引不止一个列，您可以在括号中列出这些列的名称，用逗号隔开：

CREATE INDEX PersonIndex
ON Person (LastName, FirstName)

二、删、改：table的修改和删除
 2、修改基本表

             alert  table  <表名>

                                  [add <新列名> <数据类型> [完整性约束]]

                                  [drop <完整性约束名>]

                                  [modify <列名><数据类型>];

          add: 用于向表中增加新列及新列相关的完整性约束条件，新的列增加成功后不带任何数据

          drop:用于删除指定的完整性约束条件

          modify:用于修改原来的列定义，包括修改列名和数据类型，列数据类型修改后可能会丢失原有的数据

 

alert table student add Scome date;
alert table student modify Sage smallint;
alert table student drop unique(Sname);

         3、SQL中没有提供删除属性列的功能，若需要删除列，必须间接通过select语句来实现，将需要的列的内容复制到另一个表中，再删除原表，然后将新表的名字命名为原表名

        4、删除基本表

              drop table <表名>

5、删除索引 drop index <索引名>;

三、改、删：数据更新

1、数据插入

　　　insert into <表名> [<列名>[,<列名>]....) values (value1, value2,...);

             如果表中的每一列均有数据插入，则可不用指定任何表名

如：

 

.插入职工数据：EmpTable（职工表）
insert into EmpTable(Name,Age,Duty,Salary) values('王明',35,'外贸总监',12500)
insert into EmpTable(Name,Age,Duty,Salary) values('李勇',24,'外贸员',6500)
insert into EmpTable(Name,Age,Duty,Salary) values('刘星',25,'外贸员',7500)
insert into EmpTable(Name,Age,Duty,Salary) values('周平',21,'外贸员',3500)
insert into EmpTable(Name,Age,Duty,Salary) values('杨兰',20,'外贸员',3000)

 

2、数据修改

　　　update <表名> set <列名>=新值 where <条件表达式>

3、数据删除

           删除指的是删除数据库中的一个记录，而不是删除某一列

           delete from <表名> where <条件表达式>

四、数据查询

       select  [distinct | distinctRow | all]

                    列名[，列名][，....]

                    [into {outfile | dumpfile} '文件名' export_options]

                    [from 表名]

                    [where 查询条件]

                   [group by 列名[, ....]]

                    [having 条件定义]

                   [order by {无符号整数|列名|公式}[asc | desc],...]

                  [limit [偏移量，] 行数]

                  [procedure 过程名称]

               注：select语句中所有使用的关键词必须精确的以上面的顺序给出 

　　　　　　如果以降序排列，则在order by中加入关键字desc，升序则加入关键字asc,默认是升序

　　　　　　having子名能引用任何列或在查询中命名的别名，它最后运用，就在项目被送到客户之前是，没有变化。需要注意，不要对应该在where子句中的项目使用having.

                       limit子名被用来限制select返回的行数，如果给定1个参数，它指出返回行的最大数目；如果给定2个参数，第一个指定要返回的第一行的偏移量，第二个指定返回行的最大数目，初始行的偏移量是0

//返回前5个记录
select * from table limit 5;

//返回第6到第15个记录
select * from table limit 5,10;

                    select .... into outfile '文件名'　格式的select语句将选择的行写入一个文件，文件在服务器上被创建，并且不能是已经存在的，且在服务器主机上还必须有file权限以使用这种select

                    distinct,使用了这个关键字后，再执行select语句的结果集中，如果有重复的值，则会在结果集中去掉重复的值而只保留一个值。

//选择学生表中所有学生的姓名记录，并且筛选掉重复的记录，将记录保存在名字为name的结果集中，并按学号由大到小排列
select distinct sname as name, sno from student order by sno desc;

//选择学生表中所有学生的姓名和年龄，并将每个同学的姓名后加上“同学”二字，将每个人的年龄增加1岁，同是计算所有同学年龄的平均值　
// sum() ---- 求所有数据的总和
// max() ----求所有数据中的最大值
// min() -----求所有数据中的最小值
// count() ---求所有数据的个数
// avg() ----　求所有数据的平均值
select sname, "同学", age+1, avg(age) from student;
求数学成绩排在前5名的平均分

select average（grade）from （select top 5 * from table_grade order by  grade desc）
//列出学号在1和100之间，并且成绩大于85分的所有姓”赵“同学的所胡记录
// % ---代表任意字符
//使用通配符时，前面必须用关键字”like”
select * from student where sname like '赵%’ and sno between 1 and 100 and sno in (select sno from student where grade>85);

//使用学生表、课程表以及学生选课表这3个基本表，列出所有学生的姓名、学号、所选课程的课程名称及该课程的学分
select sname, sno, cno, credit from student, course, sc where student.sno=sc.sno and sc.cno=course.cno;

 

统计每种状态的订单数和累计订单总额？group by

select count(order_status),count(pay) from order group by order_status

 

内连接 innner join 

inner join(等值连接) 只返回两个表中联结字段相等的行
left join(左联接) 返回包括左表中的所有记录和右表中联结字段相等的记录
right join(右联接) 返回包括右表中的所有记录和左表中联结字段相等的记录

INNER JOIN 语法：

INNER JOIN 连接两个数据表的用法：
SELECT * FROM 表1 INNER JOIN 表2 ON 表1.字段号=表2.字段号

例子：查询人员信息表中 id关联的人员name，和部门id关联的部门name

select stu.stu_name,org.org_name from stu inner join org on sut.id=org.stu_id

INNER JOIN 连接三个数据表的用法：
SELECT * FROM (表1 INNER JOIN 表2 ON 表1.字段号=表2.字段号) INNER JOIN 表3 ON 表1.字段号=表3.字段号

INNER JOIN 连接四个数据表的用法：
SELECT * FROM ((表1 INNER JOIN 表2 ON 表1.字段号=表2.字段号) INNER JOIN 表3 ON 表1.字段号=表3.字段号) INNER JOIN
表4 ON Member.字段号=表4.字段号

 

INNER JOIN 连接五个数据表的用法：
SELECT * FROM (((表1 INNER JOIN 表2 ON 表1.字段号=表2.字段号) INNER JOIN 表3
ON 表1.字段号=表3.字段号) INNER JOIN 表4 ON Member.字段号=表4.字段号) INNER JOIN 表5 ON
Member.字段号=表5.字段号