数据库

1.1.      WHERE

语法

       SELECT 列名 FROM 表名 [WHERE 条件语句]

WHERE子句中的运算符

 

比较运算符

 

>, <, >=, <=, =, <>

 

注意不等于和Java中不同, 是<>

 

BETWEEN ... AND ...

 

某一区间内的值, 从 ... 到 ...

 

IN(列表)

 

在列表之中, 例: in(1,2,3) 代表1或2或3

 

LIKE(表达式)

 

模糊查询, %代表多个字符, _代表单个字符

 

IS NULL

 

判断是否为NULL

 

逻辑运算符

 

AND &&

 

与, 两边都为TRUE结果为TRUE

 

OR ||

 

或, 一边为TRUE结果就为TRUE

 

NOT !

 

非, 将表达式结果取反

1.2.      ORDER BY

语法

       SELECT 列名 FROM 表名 ORDER BY 列名ASC|DESC;

注意事项

       ORDERBY 指定排序的列名可以是表中的列名, 也可以是SELECT语句后面起的别名

       ASC为升序, DESC为降序

       ORDERBY应在查询语句的结尾

1.3.      COUNT函数

语法

SELECT COUNT(*)|COUNT(列名) from 表名[WHERE 条件语句]

注意事项

       COUNT(列名)的方式是统计指定列中有多少条记录, 不包括值为NULL的

       COUNT(*)则是统计表中有多少条数据

       COUNT(DISTINCT列名) 统计不重复的记录数

       如果加上WHERE子句, 则是统计满足条件的记录

1.4.      SUM函数

语法

       SELECT SUM(列名) FROM 表名 [WHERE 条件语句];

注意事项

       计算指定列中所有记录的和, 如果有WHERE子句则计算满足条件的记录

      

1.5.           AVG函数

语法

       SELECT AVG(列名) FROM 表名 [WHERE 条件语句];

注意事项

       计算指定列的平均值, 如果有WHERE子句, 则计算满足条件的记录

       AVG()统计平均数不包含NULL值

1.6.      MAX / MIN函数

语法

       SELECT MAX(列名) FROM 表名 [WHERE 条件语句];

       SELECT MIN(列名) FROM 表名 [WHERE 条件语句];

注意事项

       获取指定列最高/最低值, NULL不参与统计

1.7.      GROUP BY(重点)

语法

       SELECT 列名 FROM 表名 GROUP BY 列名[HAVING 条件语句]

注意事项

       按照某列归类

       HAVING和WHERE类似, 但HAVING是作用于组, 其中可以使用组函数

SELECT列表中未包含在组函数中的列名, 只能是GROUP BY中的列名

HAVING中可以使用组函数, WHERE不能.

2.     函数

2.1.      时间函数

注意date,datetime, timestamp之间的区别

 

ADDTIME(原时间, 增加值)       在某个时间上增加一段时间

       selectaddtime('18:23:01', '01:01:01');

select addtime(now(),'3:0:0');

CURRENT_DATE()                    当前日期

       selectcurrent_date();

CURRENT_TIME()                    当前时间

       selectcurrent_time();

CURRENT_TIMESTAMP()         当前时间戳

       selectcurrent_timestamp();

DATE(时间)                               返回制定时间的日期部分

       selectdate('2011-02-14 18:00:00');

DATE_ADD(日期,INTERVAL增加值 类型)           在指定日期上对某个字段增加

       selectdate_add('2011-02-14 23:00:00', interval 10 month);

DATE_SUB(日期,INTERVAL减少值 类型)            在指定日期上对某个字段减少

       select date_sub('2011-02-14 23:00:00',interval 1 year);

DATEDIFF(日期1, 日期2)        计算两个日期之间的差值

       selectdatediff('2000-02-14', '2001-02-14');

NOW()          当前时间

       selectnow();

YEAR|MONTH|DATE|HOUR|MINUTE|SECOND(时间)          获取指定时间的某个字段

       selectyear('2011-02-14 23:00:00');

       selecthour('2011-02-14 23:00:00');

2.2.      字符串函数

CHARSET(字符串)                                                返回字符串字符集

       selectcharset(name) from student;

CONCAT(字符串1[,字符串2]... )                         连接字符串

       selectconcat('aaa', 'bbb', 'ccc');

INSTR(字符串, 子字符串)                                     查找子字符串出现位置, 注意序号从1开始

       selectinstr('abc', 'a');

UCASE(字符串)                                                     将字符串转为大写

       selectucase('aBc');

LCASE(字符串)                                                     将字符串转为小写

       selectlcase('aBc');

LEFT(字符串, 长度)                                              从字符串左边取指定长度个字符

       selectleft('aBc',2);

LENGTH(字符串)                                                  计算字符串长度

       selectlength('aBc');

REPLACE(字符串, 搜索字符串,替换字符串)        将字符串中指定字符串替换为其他字符串

       selectreplace('abbcbbd', 'bb', 'ee');

STRCMP(字符串1, 字符串2)                                逐个字符比较两个字符串, 如果是包含关系, 则返回长度差值

       selectstrcmp('abcc', 'abde');

       selectstrcmp('abc', 'ab');

SUBSTRING(字符串, 开始坐标[,个数])         从字符串中截取

       selectsubstring('abcdef', 3);

       selectsubstring('abcdef', 3, 2);

LTRIM(字符串)                                                      去掉左边空白

       selectltrim('    abc   ');

       selectconcat('--', ltrim('    abc   '), '--');

RTRIM(字符串)                                                      去掉右边空白

       selectconcat('--', rtrim('    abc   '), '--');

TRIM(字符串)                                                        去掉左右两边空白

       selectconcat('--', trim('    abc   '), '--');

2.3.      数学函数

ABS(数字)                                        求绝对值

       selectabs(10);

       selectabs(-10);

BIN(十进制数)                                  将十进制转换为二进制

       selectbin(5);

HEX(十进制数)                                将十进制转换为十六进制

       selecthex(10);

CONV(数字, 原进制, 目标进制)       转换进制

       selectconv(12, 10, 16);

       selectconv(12, 10, 2);

       selectconv(12, 16, 2);

CEILING(小数)                                 向上取整

       selectceiling(3.4);

FLOOR(小数)                                   向下取整

       selectfloor(3.4);

ROUND(小数)                                  四舍五入

       selectround(3.4);

select round(3.5);

FORMAT(小数, 保留位数)                保留小数位

       selectformat(3.1415926, 2);

LEAST(值,值[,值]...)                         取最小值

       selectleast(1,2,3,4);

       selectleast('a', 'b', 'c', 'd');

GREATEST(值,值[,值]...)                  取最大值

       selectgreatest(1,2,3,4);

       selectgreatest('a', 'b', 'c', 'd');

MOD(数字, 数字)                             取余

       selectmod(3,2);

       select3%2;

RAND()                                            生成随机数, 14位小数, 0 <= n <= 1

       selectrand();

3.     表的约束

约束的作用

限定某一列上的数据, 阻止非法数据录入, 提高程序健壮性.

3.1.      唯一约束 unique

unique约束的字段在整张表中唯一, 不可重复, 不包括多个NULL

 

创建表时设置唯一

create table test (

       idint,

       name varchar(20) unique

);

 

删除唯一约束

show create table test; 发现唯一索引名叫name

alter table test dropindex name;

 

添加唯一约束

alter table test changename name varchar(20) unique;

3.2.      非空约束 not null

not null约束的字段不能为空

 

创建表时设置非空

create table test1 (

       idint,

       name varchar(20) not null

);

 

删除非空约束

alter table test1 changename name varchar(20);

 

添加非空约束

alter table test1 changename name varchar(20) not null;

3.3.      主键约束 primary key

通常我们在设计表的时候需要给每一条记录一个独有的标识, 我们就用主键来约束这个标识.

primary key用来标识一个字段, 这个字段是非空且唯一的.

 

创建表时设置主键

create table test2(

id int primary key,

name varchar(20)

);

 

删除主键

alter table test2 dropprimary key;

 

在制定列上添加主键

alter table test2 changeid id int primary key;

alter table test2 addprimary key(id);

 

设置主键自动增长

create table test3(

id int primary key auto_increment,

name varchar(20)

);

 

删除自增长

alter table test3 changeid id int;

 

设置自增长

alter table test3 changeid id int auto_increment;

 

UUID主键

128位的2进制, 32位16进制加上4个-

java.util.UUID.randomUUID().toString()

3c2372a4-da2a-4470-b17a-f2e50ac79636

3.4.      外键约束foreign key

foreignkey约束某一列的值是参照另外一列

 

创建表时添加外键约束

create tablehusband(

       id int primary key,

       name varchar(20) not null

);

create tablewife(

       id int primary key,

       name varchar(20) not null,

       husband_id int,

       constrainthusband_id_fk foreign key(husband_id) references husband(id)

);

wife表的husband_id的值必须是husband表中的id

 

被外键引用的记录不能删除, 如果想要删除某条被引用的记录, 需要找到引用这条记录的记录, 解除关联

 

被外键引用的表不能删除, 如果想要删除被引用的表, 需要删除所有引用此表的外键

 

删除外键约束

alter table wife dropforeign key husband_id_fk;

 

添加外键约束

alter table wife add constraint husband_id_fkforeign key(husband_id) references husband(id)

4.     表的设计

4.1.      单独的实体

4.2.      一对多、多对一

独立外键, 没有依赖关系, 两个表的对象都可以独立存在.

 

主键即外键, 分为主表和从表, 从表依赖于主表, 从表中的对象不能单独存在.

注意从表的主键, 不能自动增长.

4.3多对多

用一张关系表保存多对多的关系,有两列分别引用两张表的主键,并且这两列组合起来成为联合主键

 

insert into student_teacher values(3,2);

5.     多表查询

5.1.      连接查询

当我们要插叙的数据不只是在一张表中, 我们就需要使用多表连接查询.

例如: 查询员工所在的部门名称, 查询部门中员工名称, 都需要查询两张表.

 

注意:

在多表连接查询的时候, 如果没有有效的连接条件, 所有表中的行会互相连接, 形成笛卡尔集.

为了避免笛卡尔集, 可以再where后加入有效的连接条件

5.2.      多表连接

多张表连接查询, 一张表外键引用另外一张表, 另外一张表再引用其他表.

例如: 员工表引用部门, 部门表引用城市表. 这时如果想根据员工查城市, 或者根据城市查员工就需要将三张表连接查询

 

准备工作:

创建城市表, id主键自动生成, 带有名称

插入两条记录, 北京和上海

在部门表添加city_id,外键引用城市表的id

将开发部的地址改为北京, 市场部的地址改为上海

 

5.3.      自连接

自己和自己连接, 当前表的外键引用自己的主键.

例如: 员工的经理也是员工, 应该在员工表中添加一列经理id. 之后添加一个外键, 引用员工表的主键.

 

准备工作:

在员工表中添加manager_id,外键引用员工表id

插入赵六, 孙七. 分别属于开发部和市场部.

将张三和李四的经理设置为赵六,王五经理设置为孙七.

 

5.4.      内连接、左外连接、右外连接(SQL99)

准备工作:

插入记录周八, 部门为空

插入部门财务部

 

1. 内连接

 

之前我们使用逗号和where子句进行的连接就是内连接. 标准语法应使用 inner join 和 on, 例如:

select e.name,d.name from employee e,departmentd where e.department_id=d.id;

select e.name,d.name from employee e innerjoin department d on e.department_id=d.id;

内连接会将两张表完全匹配连接条件的记录查询出来, 不满足的不会显示

 

2. 左外连接

 

使用 left outerjoin 和 on 关键字进行连接查询, 这时左表中不满足条件的记录也会被查询出来

例如: 查询所有员工的部门, 要将没有部门的周八查询出来. 这时需要查询出员工表中所有记录, 即使不满足连接条件, 周八也要显示.

 

3. 右外连接

 

使用 right outerjoin 和 on 关键字进行连接查询, 这时右表中不满足条件的记录也会被查询出来

例如: 查询每个部门下都有哪些员工, 要将没有员工的财务部显示出来.

 

4. 全外连接

MySql不支持全外连接full outer join, 可以使用union distinct来实现.

 

5.5.      使用组函数的多表查询

6.     子查询

一条查询语句需要使用另一条查询语句的结果.

例如: 查询工资比张三高的员工

 

子查询在主查询之前完成

子查询结果被主查询所使用

子查询要包含在括号内

子查询放在条件右侧

 

多行子查询

ANY 集合中只要有一个元素满足条件, 结果就成立.

ALL 集合中所有元素都满足条件, 结果才成立

IN 相当于 =ANY

7.     中文乱码问题

MySQL有六处使用了字符集, 分别为:

 

client:            客户端使用的字符集

connection:     连接数据库的字符集, 如程序没有指明, 就按照服务器端默认字符集.

database:        数据库服务器中库的字符集, 如建库时没有指明, 将使用服务器安装时指定的字符集.

result:            数据库给客户端返回时使用的字符集, 如没有指明, 使用服务器默认的字符集.

server:            服务器安装时指定的默认字符集.

system:           数据库系统使用的字符集.