MySql（基础操作）

SQL分类：

DDL： 数据定义语言，用来定义数据库对象：库、表、列等（操作结构）
DML： 数据操作语言，用来定义数据库记录（操作数据）
DCL： 数据控制语言，用来定义访问权限和安全级别
DQL： 数据查询语言

DDL

关键字： CREATE、ALTER、DROP

操作数据库

创建：

create database dbname  ;
create database dbname character set gbd ;
create database dbname character set gbd collate gbd_chinese_ci ;

查询：

show databases ;
show create fatabase dbname ;

删除：

drop database dbname ;

修改：

# 修改字符集
alter database dbname character set utf-8 ;

其他：

# 查看当前使用的数据库
select database() ;
# 	切换数据库
use dbname ;
查看数据库编码的具体信息
Show variables like ‘character%’;
临时更改客户端和服务器结果集的编码
Set character_set_client=gbk;
Set character_set_results=gbk;

操作数据表

基本语法：

create table 表名(
字段1 字段类型,
字段2 字段类型,
...
字段n 字段类型
) ;

常用数据类型

int：整型
double：浮点型，例如double(5,2)表示最多5位，其中必须	有2位小数，即最大值为999.99；
char：固定长度字符串类型；   char(10)   'aaa    ' 占10位
varchar：可变长度字符串类型； varchar(10)  'aaa' 占3为
text：字符串类型；
blob：字节类型；
date：日期类型，格式为：yyyy-MM-dd；
time：时间类型，格式为：hh:mm:ss
timestamp：时间戳类型 yyyy-MM-dd hh:mm:ss 会自动赋值
datetime:日期时间类型 yyyy-MM-dd hh:mm:ss

基本操作：

#当前数据库中的所有表
SHOW TABLES;
#查看表的字段信息
DESC employee;
#在上面员工表的基本上增加一个image列。
ALTER TABLE employee ADD image blob;
# 修改job列，使其长度为60。
ALTER TABLE employee MODIFY job varchar(60);
#删除image列,一次只能删一列。
ALTER TABLE employee DROP image;
#表名改为user。
RENAME TABLE employee TO user;
#查看表格的创建细节
SHOW CREATE TABLE user;
#修改表的字符集为gbk
ALTER TABLE user CHARACTER SET gbk;
#列名name修改为username
ALTER TABLE user CHANGE name username varchar(100);
#删除表
DROP TABLE user ;

DML 操作

关键字： INSERT、UPDATE、DELETE

插入操作 INSERT

语法：

INSERT INTO 表名（列名1，列名2 ...）VALUES(列值1，列值2...) ;

注意：

列名与列值的类型、个数、顺序要一一对应。
可以把列名当做java中的形参，把列值当做实参。
值不要超出列定义的长度。
如果插入空值，请使用null
插入的日期和字符一样，都使用单引号括起来。

修改操作 UPDATE

语法：

UPDATE 表名 SET 列名1=列值1，列名2=列值2 。。。【 WHERE 列名=值】

删除 DELETE

语法：

DELETE  FROM 表名 【WHERE 列名=值】

注意：

DELETE 删除表中的数据，表结构还在;删除后的数据可以找回
TRUNCATE 删除是把表直接DROP掉，然后再创建一个同样的新表。Truncate删除的数据不能找回。执行速度比DELETE快

DQL

	数据库执行DQL语句不会对数据进行改变，而是让数据库发送结果集给客户端。
查询返回的结果集是一张虚拟表。

关键字： SELECT
基本语法：

SELECT 列名 
FROM	表名
WHERE 			# 行条件
GOUP BY 		# 分组
HAVING			# 分组后的行条件
ORDER BY  asc(升序) | desc(降序)  	# 排序
LIMIT 	n,m			# 分页

基础查询：

SELECT * FROM table ;
SELECT 字段1, 字段2, 字段3 FROM table ; 

条件查询：

条件查询就是在查询时给出WHERE子句，在WHERE子句中可以使用如下运算符及关键字：
=、!=、<>、<、<=、>、>= ；BETWEEN…AND ；IN(set) ；IS NULL； AND；OR；NOT；

模糊查询：

关键字：like
通配符:
	_ 任意一个字母
	%：任意0~n个字母

字段空值查询：

# 去重记录 distinct
SELECT DISTINCT 字段 FROM 表;
# 字段拼接（两列数值类型），字符串拼接用concat()
SELECT *,sal+comm FROM emp ;
SELECT *,sal+IFNULL(comm,0) FROM emp ; # 转换空值
# 添加别名 as在MySQL中可以省略
SELECT *, sal + IFNULL(comm,0) AS total FROM emp;

聚合函数

# count
SELECT COUNT(*) AS cnt FROM emp ;
SELECT COUNT(1) AS cnt FROM emp ;
SELECT COUNT(comm) cnt FROM emp ;

# sum、avg
SELECT SUM(sal), AVG(comm) FROM emp;
# max、min
SELECT MAX(sal), MIN(sal) FROM emp;

查询顺序

查询语句书写顺序：select – from- where- group by- having- order by - limit
查询语句执行顺序：from - where -group by - having - select - order by - limit

数据完整性

作用：保证用户输入的数据保存到数据库中是正确的。
确保数据的完整性 = 在创建表时给表中添加约束

实体完整性

实体：即表中的一行(一条记录)代表一个实体（entity）
实体完整性的作用：标识每一行数据不重复。
约束类型：
主键约束（primary key） 
唯一约束(unique)
自动增长列(auto_increment)

主键约束

每个表中要有一个主键。数据唯一，且不能为null

第一种添加方式

CREATE TABLE student(
id int primary key,
name varchar(50)
);

第二种添加方式

CREATE TABLE student(
id int,
name varchar(50),
primary key(id)
);

#创建联合主键
CREATE TABLE student(
classid int,
stuid int,
name varchar(50),
primary key(classid,stuid)
);

第三种添加方式

CREATE TABLE student(
id int,
name varchar(50)
);
ALTER TABLE student  ADD  PRIMARY  KEY (id);

唯一约束 unique

数据不能重复

CREATE TABLE student(
Id int primary key,
name varchar(50) unique
);

自动增长列 auto_increment

sqlserver数据库 (identity) oracle数据库( sequence)
给主键添加自动增长的数值，列只能是整数类型

CREATE TABLE student(
Id int primary key auto_increment,
name varchar(50)
);

域完整性

作用：限制此单元格的数据正确，不对照此列的其它单元格比较域代表当前单元格
域完整性约束：数据类型 非空约束（not null） 默认值约束(default)
check约束（mysql不支持）check(sex=‘男’ or sex=‘女’)

数据类型： 数值类型、日期类型、字符串类型

参照完整性

外键约束：FOREIGN KEY

第一种添加方式

CREATE TABLE student(
sid int primary key,
name varchar(50) not null,
sex varchar(10) default ‘男’
);
create table score(
id int primary key,
score int,
sid int , -- 外键列的数据类型一定要与主键的类型一致
foreign key (sid) references student(sid)
);

第二种添加方式

ALTER TABLE score1 ADD CONSTRAINT fk_stu_score FOREIGN KEY(sid) REFERENCES

stu(id);

表与表之间的关系

一对一：

	例如t_person表和t_card表，即人和身份证。这种情况需要找出主从关系，即谁是主表，谁是从表。
人可以没有身份证，但身份证必须要有人才行，所以人是主表，而身份证是从表。设计从表可以有两种方
案：
在t_card表中添加外键列（相对t_user表），并且给外键添加唯一约束；
给t_card表的主键添加外键约束（相对t_user表），即t_card表的主键也是外键。

一对多 / 多对一：

t_user和t_section的关系，从t_user来看就是一对多，而从t_section的角度来看就是多对一！这种情况都是在多方创建外键！

多对多：

例如t_stu和t_teacher表，即一个学生可以有多个老师，而一个老师也可以有多个学生。这种情况通
常需要创建中间表来处理多对多关系。例如再创建一张表t_stu_tea表，给出两个外键，一个相对
t_stu表的外键，另一个相对t_teacher表的外键。
权限设计 (用户表 角色表 权限表 ) 5张表 三个主表 两个中间表 RBAC

多表查询

合并结果集；UNION 、  UNION ALL 了解
连接查询
内连接 [INNER] JOIN  ON
外连接  OUTER JOIN ON
左外连接 LEFT [OUTER] JOIN
右外连接 RIGHT [OUTER] JOIN
全外连接（MySQL不支持）FULL JOIN
自然连接  NATURAL JOIN
子查询

合并结果集

作用：合并结果集就是把两个select语句的查询结果合并到一起
要求：被合并的两个结果：列数、列类型必须相同

#UNION：去除重复记录，例如：
SELECT * FROM t1 UNION SELECT * FROM t2；
#UNION ALL：不去除重复记录，例如：
SELECT * FROM t1 UNION ALL SELECT * FROM t2。

常用函数

日期函数

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|Day(datetime ) 年月日

字符串函数

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 ) trim 去除前端空格或后端空格

数学函数

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])