posts - 78, comments - 34, trackbacks - 0, articles - 1
  BlogJava :: 首页 :: 新随笔 :: 联系 :: 聚合  :: 管理

2009-11-29传智播客 数据库——数据库入门[mysql]

Posted on 2009-11-30 14:39 長城 阅读(858) 评论(0)  编辑  收藏

方老师今日讲解数据库入门，主要内容是对数据库操作的SQL语句，仅涉及单表操作。内容虽然枯燥，但也学习了很多知识。

什么是SQL？structred query language（结构化查询语言），在数据库中它是通用的。我之前并未深入使用数据库。对此还有些模糊，但今日的学习让我有了深入了解。其实我也应该早就想到，对数据的一些操作通过SQL语句就可以完成，程序直接拿结果就可以了。

方老师以MySQL5.0数据库，给我们做了讲解。是为了下一课学习JDBC做准备。前面的对数据库的简介和MySQL5.0的安装及配置，我就不写出来了。下面是用户、服务器、数据库和表的关系图：

clip_image001

在配置数据库时选中：clip_image002，或手动将MySQL安装目录添加到Windows环境变量Path中。使用控制台连接MySQL数据库：mysql –u 用户名 –p 密码。

下面是常用的SQL语句，只针对单表使用，有关语句的具体信息请查询MySQL的手册：

一、数据库相关SQL语句：

1. 创建数据库： 
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name [create_specification [, create_specification] ...]

其中create_specification是：

[DEFAULT] CHARACTER SET charset_name | [DEFAULT] COLLATE collation_name
例，创建一个名为“ccdb”，字符集为UTF-8的数据库：
create database ccdb character set utf8;
clip_image003

2. 查看、删除数据库： 
显示数据库：
SHOW CREATE {DATABASE | SCHEMA} db_name
例，查看服务器中所有的数据库：
 clip_image004
显示数据库创建语句：
SHOW {DATABASES | SCHEMAS} [LIKE 'pattern']
例，查看创建的“ccdb”数据库的创建语句：
clip_image005
删除数据库：
DROP {DATABASE | SCHEMA} [IF EXISTS] db_name
例，删除创建的数据库“ccdb”：
clip_image006

3. 修改数据库： 
ALTER {DATABASE | SCHEMA} [db_name] alter_specification [, alter_specification] ...
其中alter_specification是：
[DEFAULT] CHARACTER SET charset_name | [DEFAULT] COLLATE collation_name

例，更改数据库“ccdb”的字符编码为“gbk”：

clip_image008

二、数据表相关SQL语句：

1. 创建表： 
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(create_definition,...)] [table_options] [select_statement]
或
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(] LIKE old_tbl_name [)];
其中create_definition、table_options等，请查看手册。
例，在数据库“ccdb”中创建一个“user”表：
在创建表之前，必须选择当前库。否则服务器不知道在哪个数据库中创建表，或者当前已经有被选择的其他数据库，就会创建到别的库。

clip_image009

clip_image010

 

 
MySQL常用数据类型（拿方老师的，嘿嘿）：
分类
 
数据类型
 
说明
 
 
 
 
 
数值类型
 
BIT
TINYINT [UNSIGNED] [ZEROFILL] 
BOOL，BOOLEAN 
SMALLINT [UNSIGNED] [ZEROFILL] 
INT [UNSIGNED] [ZEROFILL] 
BIGINT [UNSIGNED] [ZEROFILL] 
FLOAT[(M,D)][UNSIGNED] [ZEROFILL] 
DOUBLE[(M,D)][UNSIGNED] [ZEROFILL] 
范围为从1到64。
带符号的范围是-128到127。无符号0到255。
使用0或1表示真或假
2的16次方
2的32次方
2的64次方
M指定长度，d指定小数位数
表示比float精度更大的小数
 
 
 
文本类型
 
CHAR(size)
VARCHAR(size)
BLOB   LONGBLOB
TEXT   LONGTEXT
 
固定长度字符串
可变长度字符串
二进制数据
大文本
 
时间日期
 
DATE/DATETIME/TimeStamp
 
日期类型(YYYY-MM-DD)  (YYYY-MM-DD HH:MM:SS)，TimeStamp表示时间戳，它可用于自动记录insert、update操作的时间
 
 

2. 修改表： 
ALTER [IGNORE] TABLE tbl_name alter_specification [, alter_specification] ...

其中alter_specification，请查看手册。

修改表“user”名为“userinfo”：
clip_image011
修改表“userinfo”的字符集为“gb2313”，并将字符集校对设置为与“gb2313”相应的校对：
clip_image013
可以在控制台输入：SHOW CHARACTER SET;，列出可用的字符集。也可以在手册中查找“Character sets”，查找手册中的字符集列表。
可以在控制台输入：SHOW COLLATION LIKE 'gb%';，查看以gb开头的所有字符集。
先将表“userinfo”恢复名称为“user”，查看表“user”：
clip_image014
查看表“user”的结构：
clip_image015
添加列“photo”：

clip_image016

修改列“sex”属性-类型为“bit”：

clip_image017

修改列“email”名称为“address”：

clip_image018

删除列“address”：

clip_image019

3. 数据库CURD语句：

Insert语句：

INSERT INTO tbl_name (col1,col2) VALUES(15,col1*2);

例，向“user”表中插入一条新数据：

clip_image021

Update语句：

UPDATE tbl_name  SET col_name1=expr1 [, col_name2=expr2 ...] [WHERE where_definition]

例，将“sex”为1的人的“name”修改为“changcheng”：

clip_image022

clip_image023

Delete语句：

delete from tbl_name [WHERE where_definition]

例，删除刚才添加的新记录：

clip_image024

如果没有后边的where条件，则删除表中所有的数据。

truncate table tbl_name;是删除表，然后立即创建一个新表。

clip_image025

4. Select查询语句（因为它是重点，所以单独拿出来）：

1. 基本select语句：

SELECT [DISTINCT] *|{column1, column2. column3..} FROM table;

例，我们新创建了一个sutdent表，查看表中的所有记录：

clip_image026

例，查询表中所有学生姓名和他的数学成绩：

clip_image027

例，过滤表中的重复数据（distinct）：

clip_image028

2. 在select语句中可使用表达式对查询的列进行运算：

SELECT *|{column1｜expression, column2｜expression，..} FROM table;

例，将所有学习的数学加上10分：

clip_image029

例，统计学生的总分：

clip_image030

3. 在select语句中可使用as语句：

SELECT column as 别名 from 表名;

例，修改统计学生总分的列名：

clip_image031

4. 在select语句中加上where条件，进行过滤查询：

例，查询总分大于260分的学生：

clip_image033

5. where经常使用的运算符：

比较运算符

> < <= >= = <>

大于、小于、大于(小于)等于、不等于

BETWEEN ...AND...

显示在某一区间的值

IN(set)

显示在in列表中的值，例：in(100,200)

LIKE ‘pattern’

模糊查询，Like语句中，% 代表零个或多个字符，_ 代表一个字符，例first_name like ‘_a%’;

IS NULL

判断是否为空

逻辑运算符

and

多个条件同时成立

or

多个条件任一成立

not

不成立，例：where not(salary>100);

例，查询数学成绩在80-90之间的学生：

clip_image034

例，查询数据成绩为90、80、78的学生：

clip_image036

例，查询所有名字以“a”开头的学生的数学成绩：

clip_image037

6. 使用order by 子句排序查询结果：

SELECT column1, column2. column3.. FROM table order by column asc|desc

clip_image039

5. 合计函数：

1. Count返回满足where子句条件的行的总数：

Select count(*)|count(列名) from tablename [WHERE where_definition]

例，查询共有多少学生：

clip_image040

例，查询总分大于250分的人数：

clip_image041

2. Sum函数返回满足where条件的行的和：

Select sum(列名)｛,sum(列名)…｝ from tablename [WHERE where_definition] 

注意：sum仅对数值起作用，否则会报错。

例，统计本班数学成绩的平均分：

clip_image042

例，统计各科总成绩：

clip_image043

3. AVG函数返回满足where条件的一列的平均值：

Select avg(列名)｛,avg(列名)…｝ from tablename [WHERE where_definition] 

例，求数学的平均分：

clip_image044

例，求总平均分：

clip_image045

4. Max/min函数返回满足where条件的一列的最大/最小值：

Select max(列名) from tablename [WHERE where_definition]

例，查询数学成线最高分和最低分：

clip_image046

6. 分组与过滤：

1. 使用group by 子句对列进行分组：

SELECT column1, column2. column3.. FROM table group by column

例，按照ID分组并对数据成绩求和：

clip_image047

2. 使用having 子句过滤：

SELECT column1, column2,column3 ... FROM table group by column having ...

Having和where均可实现过滤，但在having可以使用合计函数,having通常跟在group by后，它作用于组。

例，按id分组，查询数学成绩组总和大于100的组：

clip_image048

例，查询数据成绩>=90的学生：

clip_image049

7. 时间日期相关函数：

ADDTIME (date2 ,time_interval )

将time_interval加到date2

CURRENT_DATE (  )

当前日期

CURRENT_TIME (  )

当前时间

CURRENT_TIMESTAMP ( )

当前时间戳

DATE (datetime )

返回datetime的日期部分

DATE_ADD (date2 , INTERVAL d_value d_type )

在date2中加上日期或时间

DATE_SUB (date2 , INTERVAL d_value d_type )

在date2上减去一个时间

DATEDIFF (date1 ,date2 )

两个日期差

NOW (  )

当前时间

YEAR|Month|DATE (datetime )

年月日

8. 字符串相关函数：

CHARSET(str)

返回字串字符集

CONCAT (string2  [,... ])

连接字串

INSTR (string ,substring )

返回substring在string中出现的位置,没有返回0

UCASE (string2 )

转换成大写

LCASE (string2 )

转换成小写

LEFT (string2 ,length )

从string2中的左边起取length个字符

LENGTH (string )

string长度

REPLACE (str ,search_str ,replace_str )

在str中用replace_str替换search_str

STRCMP (string1 ,string2 )

逐字符比较两字串大小,

SUBSTRING (str , position  [,length ])

从str的position开始,取length个字符

LTRIM (string2 ) RTRIM (string2 )

去除前端空格或后端空格

9. 数学相关函数：

ABS (number2 )

绝对值

BIN (decimal_number )

十进制转二进制

CEILING (number2 )

向上取整

CONV(number2,from_base,to_base)

进制转换

FLOOR (number2 )

向下取整

FORMAT (number,decimal_places )

保留小数位数

HEX (DecimalNumber )

转十六进制

LEAST (number , number2  [,..])

求最小值

MOD (numerator ,denominator )

求余

RAND([seed])

RAND([seed])

10. 定义表的约束：

1. 定义主键：

Primary key，在预定义主键的列后边加上此属性。

例，创建student表时，将id设为主键：

clip_image050

删除、添加主键属性：

clip_image051

2. 定义非空约束：

not null，在预定义主键的列后边加上此属性：

例，将name列设置为非空：

clip_image052

此时，当向表中添加新数据时，name必须指定值。可以使用“is null”判断某一记录的值是否为空。

3. 定义列值自动增长：

Auto_increment，在预定义主键的列后边加上此属性：

例，定义id为主键且自动增长：

clip_image053

4. 定义外键约束：

这个是今天课程中，唯一涉及到两个表的SQL语句操作。

constraint ordersid_FK foreign key(ordersid) references orders(id)，在预定义外键的列后边加上此属性：

例，定义一个classes表，然后再定义一个students表：

创建classes表：

clip_image054

创建students表：

clip_image056

当向students表中添加一条记录时，如果指定的class_id值在classes表中不存在，添加就会失败！如果对应的classes表中的id值被修改了，那么也需要手动修改students表中的class_id值。

如果创建库和表，以及对它们的操作过多的话，逐条手动键入SQL语句是一件痛苦的事。SQL的脚本文件(*..sql)可以为我们解决这一难题。Sql文本文件中保存的是我们要手动键入所有的SQL语句，使用source filename语句，导入sql脚本文件。比如：“source C:\test.sql”。

OK，终于搞完了！也当做练习了，不过是按照老方的PPT一步步来的，有点侵权的感觉。不过我想没问题的，老方对此会很高兴的。

SQL语句本身并不复杂，做为程序员并不需要完全掌握数据库。数据库的优化上全由DBA来做。我们需要掌握对数据库的增、删、改、查这些SQL语句即可！

今日的课程没有多深的理论与实践，主要就是讲解SQL语句。同学们学习效果很好！


只有注册用户登录后才能发表评论。
网站导航: