</pre><pre name="code" class="sql">/*数据库基本操作*/
/*数据库操作*/
--开启/关闭mysql操作--
net start/stop mysql
--登陆--
mysql -u root -p root;
--查看数据库--
show databases;
select database();
--新建--
create database NAME;
create database NAME[CODED_SET]; --附加编码集
--打开数据库--
use NAME;
--查看该数据库中所有表--
show tables;
--删除数据库--
drop database NAME;
/*SQL 结构化查询语句*/
DDL --数据定义语言，对数据库和数据表的定义。
DML --数据操作语言，对数据进行增删改。(*)
DQL --数据查询语言，对数据进行查询。(**)
DCL --数据控制语言，对数据库的用户、权限等操作。(x)
/*表操作*/
--建表应满足的三个范式（常见面试题）：
--第一范式（1NF）： 无重复的列，不存在某列包含其他列的内容。
--第二范式（2NF）： 数据库表中的每一行必须可以被惟一地区分。
--第三范式（3NF）： 数据库表中不包含已在其它表中已包含的非主关键字信息。
--建表--
create table TABLE(
-- 字段1 数据类型 [约束] --
tempId int primary key auto_increment, --最后一条不加逗号
......
-- 字段常用数据类型 --
int --整型
double / double(x,y) --浮点型，参数表示最大位长为x，必须拥有y位小数
decimal --浮点型，用于保存对精确度要求较高的值
char --固定长度字符串类型，默认长度255（2^8-1），未满补空格
varchar(length) --可变长度类型字符串，可以自定义长度，长度最大值65535（2^16-1）
text --可变长度字符串类型，类似还有tinytext、mediumtext、longtext
blob --可变长度二进制类型，类似还有tinyblob、mediumblob、longblob
date --日期类型，格式为 yyyy-MM-dd
time --时间类型，格式为 hh:mm:ss
timestamp --时间戳类型，格式为 yyyy-MM-dd hh:mm:ss
bit --相当于Java中的boolean
-- 约束类型 --
primary key --主键约束。非空唯一。若有多个主键使用联合主键 primary key(字段1, 字段2...)
not null --非空约束。此列之项不可为空。
unique --唯一约束。此列之项不可重复。
default --默认约束。后跟相应数据类型的默认值。
--外键约束 有外键的表后建
constraint 外键名 foreign key 从表(外键列名) references 主表(主键列名)
--（常见面试题）什么是外键？外键的作用？
FOREIGN KEY--外键 (FK) 是用于建立和加强两个表数据之间的链接的一列或多列。
--通过将保存表中主键值的一列或多列添加到另一个表中，
--可创建两个表之间的链接。这个列就成为第二个表的外键。
--作用：保持数据的参照完整性。
-- 自动增长 --
auto_increment --仅适用于数值类型。当标记的字段数据为null时自动取上一数值+1<span style="white-space:pre"> </span><span style="white-space:pre"> </span>-- 联合主键 --<span style="white-space:pre"> </span>primary key(FIELD_1, FILED_2...)
);
--查看表结构--
desc TABLE;
--删除表--
drop table TABLE;
create table t_example(
eid int primary key auto_increment,/*N*/
ename varchar(10) not null,
gender char(2) default 'M',
age int default 20,
addr varchar(200)
);
+--------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+----------------+
| eid | int(11) | NO | PRI | NULL | auto_increment |
| ename | varchar(10) | NO | | NULL | |
| gender | char(2) | YES | | M | |
| age | int(11) | YES | | 20 | |
| addr | varchar(200) | YES | | NULL | |
+--------+--------------+------+-----+---------+----------------+
--增加字段--
alter table TABLE add FIELD FIELD_TYPE[CONSTRAINT];
+--------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+----------------+
......
| notes | varchar(200) | YES | | No | |
+--------+--------------+------+-----+---------+----------------+
--插入数据--
insert into TABLE values(DATA_1, DATA_2...); -- 注意规范写法为values
insert into TABLE(FIELD_1, FIELD_2...) values(DATA_1, DATA_2...);
--修改数据--
update TABLE set FIELD = CHANGED_DATA where CONDITION;
--查找数据--
select * from TABLE [where CONDITION]; --显示[符合条件的]所有字段
select FIELD_1, FIELD_2... from TABLE; --显示自定义字段
select FIELD_1 as TAG_1, FIELD_2 as TAG_2...from TABLE; --为字段添加标签。优先显示标签。as可以省略
--行筛选：where
select * from TABLE where CONDITION;
= < > <= >=
<> --不等于
and
or
not
is null
is not null
between x and y --在x~y之间
--模糊查询：like
select * from TABLE where FIELD like '...';
ABC --精确匹配
% --匹配0~N个字节
_ --匹配 下划线个数 的字节
--范围查询：limit
select * from TABLE Limit x,y; --查询从x之后的y条数据。用作分页
--从集合中查找：in
select * from TABLE where FIELD in (DATA_1, DATA_2...);
select * from TABLE where FIELD not in (DATA_1, DATA_2...);
select * from TABLE where FIELD in ( /*从子查询结果中查找*/ );
--删除数据--
delete from TABLE; --清空表中数据
delete from TABLE where CONDITION; --可以删除指定记录。可以返回被删除的记录数
truncate table TABLE; --'截断'。只能清空所有数据。执行速度快
/*聚合函数*/
sum()
max()
min()
avg() --平均值
count() --数量
distinct --去掉重复项
select avg(FIELD) from TABLE; --获取该字段所有数据平均值
select count(*) from TABLE [where CONDITION]; --获取[符合条件的]该表行数
select count(distinct FIELD) from TABLE; --获取该字段不同值数量
/*分组 group by*/
select * from TABLE group by FIELD; --默认显示当前组第一个
select count(*) from TABLE group by FIELD; --显示每组元素个数
/*二级分组 having 在已经分组出来的结果上再进行行筛选*/
select * from TABLE group by FIELD having CONDITION;
/*
having 与where 的异同点
having与where类似，可以筛选数据，where后的表达式怎么写，having后就怎么写
where针对表中的列发挥作用，查询数据
having对查询结果中的列发挥作用，筛选数据
*/
/*排序：order by */
select * from TABLE order by FIELD; --默认asc
select * from TABLE order by FIELD asc; --从小到大
select * from TABLE order by FIELD desc;--从大到小
/*二级排序*/
select * from TABLE order by FIELD_1, FIELD_2 asc/desc; --先按字段1再按字段2进行排序
/*备份*/
--cmd 环境下
mysqldump -u root -p 数据库名 > 磁盘文件路径 文件名.sql
Enter password：****
/*多表查询*/
select * from TABLE_1 TAG_1, TABLE_2 TAG_2 where TAG_1.FIELD = TAG_2.FIELD;--!不建议使用!
select * from TABLE_1 TAG_1 join TABLE_2 TAG_2 on TAG_1.FIELD = TAG_2.FIELD;--使用(inner)join...on...
--join...on...可连续使用
select *
from TABLE_1 TAG_1
join TABLE_2 TAG_2
on CONDITION_1
join TABLE_3 TAG_3
on CONDITION_2
......
--左外连接(左侧数据全部显示，右侧字段数据可为空)
select * from TABLE TAG left join...
--右外连接(右侧数据全部显示，左侧字段数据可为空)
select * from TABLE TAG right join...
/*子查询*/
select * from TABLE where FIELD
= /> /< />= /<= /<>
(
select * from TABLE where CONDITION
);
--子查询练习--
--查询与某员工年龄相同的员工
select *
from table_1
where age =
(
select age
from table_1
where ename='someone';
);
--查询与某员工参与工程相同的员工（in）
select id
from table_2
where id in
(
select id --查找某员工参与的项目
from table_2
where id=1001;
);
/*视图*/
--视图是一个虚拟的表。并不占存储空间
--命名规则：view_xxx
--查询视图：
select *
from view_xxx[FIELDS]
join...on...
...
/*索引*/
--索引相当于目录，添加在经常被查找的列上；
--主键、唯一键就是索引。如果要引用外键，会自动增加索引。
create index INDEX_NAME on TABLE(FIELD(?)); --创建索引（直接创建）
alter table TABLE drop index INDEX_NAME; --删除索引
alter table TABLE add index INDEX_NAME(FIELD(?)); --修改表添加索引
--*优点：适当地增加索引可以显著地提升<查询>速度。
--*缺点：1.额外占用磁盘空间；2.减缓<增删改>操作的速度。
--*如何在最快时间内提高数据库效率：添加索引。
/*数据库常用函数*/
/--除号：保留小数
--流程控制--
--switch case:
select FIELD_1, FIELD_2, ...,
case FIELD_3
when DATA_1 then OUTPUT_DATA_1
when DATA_2 then OUTPUT_DATA_2
...
else OUTPUT_DATA_0
end
from TABLE;
--if else:
if(CONDITION, OPERATE_1, OPERATE_2) --类似三目运算
if CONDITION
then...
else...
end if
--字符串函数--
select ascii('str'); --返回ASCII码值
select ->
char_length('str'); --返回字符串长
insert('str_1',x,y,'str_2'); --将长度y的str_2插入到str_1的x位置
left('str',x); --取左边x长度字符串
right('str',x); --取右边~
lower('STR'); --大写转小写
upper('str'); --小写转大写
ltrim(' str'); --去左空格
rtrim('str '); --去右空格
replace('str','s','S'); --替换。将str中的s替换为S
reverse('str'); --rts反序
subString(~); --重载太多详见API
concat(~); --字符串连接
cast(12.3 as char); --转换成char字符串类型'12.3'
--其他类型
binary[(N)]
char[(N)]
date
datetime
decimal
signed [INTEGER]
time
unsigned [INTEGER]
--数值函数--
select ->
floor(f); --返回不大于浮点数f的最大整数值
mod(x,y); --取模。返回x被y除后的余数
rand([N]); --随机数（~1）。参数N为重复序列用种子值（可空）
round(x, [y]); --返回四舍五入后的值。可选参数y为舍入位数（eg：0 舍入至个位；1 舍入至小数点后一位；-1 舍入至十位）
--日期与时间函数--
select ->
now() [[as] TAG]; --返回当前时间与日期。格式：yyyy-MM-dd hh:mm:ss 或yyyyMMddhhmmss( now()+0 )
--在一个存储程序或触发器内，now()返回一个常数时间，
--该常数指示了该程序或触发语句<开始执行>的时间。这同sysdate()的运行不同
sysdate(); --返回内容同上;
--在一个存储程序或触发器中, SYSDATE()返回其<执行>的时间,
--而非存储成都或触发语句开始执行的时间。这个NOW()的运作有所不同
curdate(); --获取当前格式化日期
curtime(); --获取当前格式化时间
date_add(DATE, interval x ENUM); --增加时间
--DATE: 日期数据，格式：yyyy-MM-dd
--x: 所加数据
--ENUM: 所加数据类型 YEAR、MONTH、DAY、HOUR、、、
add_date(DATE, DAY);--默认添加日期
date(DATE + TIME); --提取DATE部分
TIME(DATE + TIME); --提取TIME部分
datediff(DATE_1, DATE_2); --计算天数差（DATE_1 - DATE_2）
year(DATE); --提取年份yyyy
month(DATE); --提取月份mm
day(DATE); --提取天数dd
hour(TIME); --提取小时hh
minute(TIME); --提取分钟mm
second(TIME); --提取秒ss
dayname(DATE); --返回星期(一~日)
dayofmonth(DATE); --返回对应日日期(1~31)
dayofweek(DATE); --返回星期索引(1~7)
dayofyear(DATE); --返回当年历经天数(1~366)
from_days(H); --由天数H返回一个DATE值
str_to_date(DATE, '%m /%d /%y'); --与date_format相反，获取字符串格式
/*使用Navicat for MySQL添加自定义函数 function*/
--设置形参
--设置返回值
--方法体：
begin
declare b int;
set b = 1;
return b;
...
end
--调用：
select FUNCTION([formal parameter...]);
/*存储过程 procedure*/
--eg：使用Navicat for MySQL删除表中指定ID数据--
--创建存储过程，注意复选框中为PROCEDURE
--传参
delectID varchar(20)
--存储过程
begin
delete from t_project where proID = delectID;
end
--创建查询调用存储过程
call delectProject('A4');
/*触发器*/
before --执行之前
after --执行之后
×
insert
update
delete
...附：常见面试题-什么是事务？事务的特征？（转自http://blog.csdn.net/xiaoyu714543065/article/details/8211265）
一．什么是事务
事务是应用程序中一系列严密的操作，所有操作必须成功完成，否则在每个操作中所作的所有更改都会被撤消。也就是事务具有原子性，一个事务中的一系列的操作要么全部成功，要么一个都不做。
事务的结束有两种，当事务中的所以步骤全部成功执行时，事务提交。如果其中一个步骤失败，将发生回滚操作，撤消撤消之前到事务开始时的所以操作。
二．事务的 ACID
事务具有四个特征：原子性（ Atomicity ）、一致性（ Consistency ）、隔离性（ Isolation ）和持续性（ Durability ）。这四个特性简称为 ACID 特性。
1 、原子性
事务是数据库的逻辑工作单位，事务中包含的各操作要么都做，要么都不做
2 、一致性
事 务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态。因此当数据库只包含成功事务提交的结果时，就说数据库处于一致性状态。如果数据库系统 运行中发生故障，有些事务尚未完成就被迫中断，这些未完成事务对数据库所做的修改有一部分已写入物理数据库，这时数据库就处于一种不正确的状态，或者说是 不一致的状态。
3 、隔离性
一个事务的执行不能其它事务干扰。即一个事务内部的操作及使用的数据对其它并发事务是隔离的，并发执行的各个事务之间不能互相干扰。
4 、持续性
也称永久性，指一个事务一旦提交，它对数据库中的数据的改变就应该是永久性的。接下来的其它操作或故障不应该对其执行结果有任何影响。
数据库系统必须维护事务的以下特性 ( 简称 ACID) ：
原子性 (Atomicity)
一致性 (Consistency)
隔离性 (Isolation)
持久性 (Durability)
原子性 (Atomicity)
事务中的所有操作要么全部执行，要么都不执行；
如果事务没有原子性的保证，那么在发生系统 故障的情况下，数据库就有可能处于不一致状态。
1887
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
