## 数据库操作 ##
1.创建数据库和数据库中的表
create database [if not exists]数据库名 [default character set 字符编码(集)]加上中括号是代表不是必选
create database if not exists shcool defadult charset utf8;
use 数据库名； //选中所需的数据库
use school;
建表
create table student2(
stuNo varchar(6),
stuName varchar(50),
address varchar(50),
emailvarchar(40)
);
2.切换不同的数据库
use 数据库名;
use yunsi2;
3.获取自己目前所在的数据库
select database( );
show databases like 'yu%';
4 . 删除表
drop table student2;
5.查看数据库中有多少张表
show tables;
6.查看表的创建语句
show create table student2;
## 数据类型 ##
**整数**(int用的最多）
tinyInt
smallInt
mediumInt
int
bigInt
范围从小到大
这些整数分为带符号的 signed 负数-----正数
无符号 unsigned 0-----值
signed tinyInt -128----- 127
unsigned tinyInt 0-----255
create table t_ceshi1(
t_num1 tinyInt signed,
t_num2 tinyInt unsigned,
t_num3 tinyInt,
t_createTime dateTime
)engine=innoDB default charset utf8;
注意点： t_num3没有标明是无符号还是带符号，默认成带符号的 signed
**小数类型**（double用的多）
decimal(M,D)
numeric(M,D)
float(M,D);
double(M,D)
取值范围不同
M:数字的总位数 ，D:小数位数 ，小数点不算
create table t_ceshi3(
t_num1 decimal(5,2),
t_num2 numeric(5,2),
t_num3 float(5,2),
t_num4 double(5,2),
t_createTime dateTime
)engine=innoDB default charset utf8;
数字：总共5位，小数占2位，整数部分最多3位
**位字段类型 **bit
create table t_ceshi5(
t_num1 bit(8),
t_createTime dateTime
)engine=innoDB default charset utf8;
**字符串类型**
char(M) M:0-255,默认值是255
varchar(M) M:0-65535,如果mysql编码方式指定为utf-8时，实测 M:0---21844。
----- 创建表的时候，char可以不指定长度，varchar必须指定长度
------实测char不指定长度时，默认长度1， 测试一下非严格模式时，char的默认长度、
create table t_ceshi6(
t_num1 char,
t_num2 varchar(10),
t_createTime dateTime
)engine=innoDB default charset utf8;
**复合类型**
存储一整篇文章,整本书,字符量比较大，（char,varchar不够用了）
text 文本类型
tinyText ,text, mediumText, longText
tinyText----0-255个字符
text--- 0-65535个字符
mediumText- ---- 0 - 16777215个字符
longText--- 0 -- 4294967295个字符
存储图片，音频，视频 blob ，二进制大对象
tinyBlob blob, mediumBlob , longBlob
//没有默认长度
create table t_ceshi8(
t_num1 text,
t_num2 blob,
t_createTime dateTime
)engine=innoDB default charset utf8;
---- 选用文本类型，二进制大对象类型时，无需指定长度 ，只需要写类型
create table t_ceshi9(
t_num1 text(5),
t_num2 blob(5),
t_createTime dateTime
)engine=innoDB default charset utf8;
text(5) ----->tinytext
blob(5)----->tinyblob
**enum枚举类型**
固定的几个值 ,一旦列名是枚举类型，插入值的时候，只能在固定的几个值里选取。
create table t_ceshi10(
t_num1 varchar(10),
t_num2 enum('spring','summer','autumn','winter'),
t_num3 enum('male','female'),
t_num4 enum('101','102','103'),
t_createTime dateTime
)engine=innoDB default charset utf8;
insert into t_ceshi10(t_num1,t_Num2,t_num3,t_Num4,t_createTime)
values('jack','spring','male','101',now());
**日期类型**(date,dateTime)
1. Date yyyy-MM-dd
1000-01-01----9999-12-31
2. Time HH:mm:ss
-838:59:59-----838:59:59
3. DateTime yyyy-MM-dd HH:mm:ss
1000-01-01 00:00:00 -----9999-12-31 23:59:59
4. timestamp yyyy-MM-dd HH:mm:ss
1970-01-01 00:00:01-----2038-01-19 03:14:07
经过测试得出范围为1970-01-01 08:00:01 到2038-01-19 11:14:07
5. year yyyy
1901---2155
create table t_ceshi11(
t_num1 date,
t_num2 time,
t_num3 dateTime,
t_num4 timestamp,
t_num5 year
)engine=innoDB default charset utf8;
## 数据操作 ##
-- 增
INSERT [INTO] 表名 [(字段列表)] VALUES (值列表)[, (值列表), ...]
-- 如果要插入的值列表包含所有字段并且顺序一致，则可以省略字段列表。
-- 可同时插入多条数据记录！
REPLACE 与 INSERT 完全一样，可互换。
INSERT [INTO] 表名 SET 字段名=值[, 字段名=值, ...]
-- 查
SELECT 字段列表 FROM 表名[ 其他子句]
-- 可来自多个表的多个字段
-- 其他子句可以不使用
-- 字段列表可以用*代替，表示所有字段
-- 删
DELETE FROM 表名[ 删除条件子句]
没有条件子句，则会删除全部
-- 改
UPDATE 表名 SET 字段名=新值[, 字段名=新值] [更新条件]
## INSERT ##
select语句获得的数据可以用insert插入。
可以省略对列的指定，要求 values () 括号内，提供给了按照列顺序出现的所有字段的值。
或者使用set语法。
INSERT INTO tbl_name SET field=value,...；
可以一次性使用多个值，采用(), (), ();的形式。
INSERT INTO tbl_name VALUES (), (), ();
可以在列值指定时，使用表达式。
INSERT INTO tbl_name VALUES (field_value, 10+10, now());
可以使用一个特殊值 DEFAULT，表示该列使用默认值。
INSERT INTO tbl_name VALUES (field_value, DEFAULT);
可以通过一个查询的结果，作为需要插入的值。
INSERT INTO tbl_name SELECT ...;
可以指定在插入的值出现主键（或唯一索引）冲突时，更新其他非主键列的信息。
INSERT INTO tbl_name VALUES/SET/SELECT ON DUPLICATE KEY UPDATE 字段=值, …;
##DELETE##
DELETE FROM tbl_name [WHERE where_definition] [ORDER BY ...] [LIMIT row_count]
按照条件删除。where
指定删除的最多记录数。limit
可以通过排序条件删除。order by + limit
支持多表删除，使用类似连接语法。
delete from 需要删除数据多表1，表2 using 表连接操作 条件。
## TRUNCATE##
TRUNCATE [TABLE] tbl_name
清空数据
删除重建表
区别：
1，truncate 是删除表再创建，delete 是逐条删除
2，truncate 删除表中记录，不支持事务，不能恢复 （本质上是删除表结构表数据，然后再重建表结构）
3，delete 删除表记录，支持事务，可以在事务中使用回滚恢复之前删除的数据。
## 备份与还原 ##
备份，将数据的结构与表内数据保存起来。
利用 mysqldump 指令完成。
-- 导出
mysqldump [options] db_name [tables]
mysqldump [options] ---database DB1 [DB2 DB3...]
mysqldump [options] --all--database
1. 导出一张表
2.
mysqldump -u用户名 -p密码 库名 表名 > 文件名(D:/a.sql)
2. 导出多张表
mysqldump -u用户名 -p密码 库名 表1 表2 表3 > 文件名(D:/a.sql)
3. 导出所有表
mysqldump -u用户名 -p密码 库名 > 文件名(D:/a.sql)
4. 导出一个库
mysqldump -u用户名 -p密码 --lock-all-tables --database 库名 > 文件名(D:/a.sql)
可以-w携带WHERE条件
-- 导入
1. 在登录mysql的情况下：
source 备份文件
2. 在不登录的情况下
mysql -u用户名 -p密码 库名 < 备份文件
## 用户操作 ##
**创建一个用户，指定密码。**
格式：
create user 用户名@IP地址 identified by '密码';
如：创建一个用户，用户名为ming，密码为123456
create user ming@'%' identified by '123456';
Use mysql;
User 表，存储了所有的登录用户
**为用户赋予操作权限**
格式：
grant create,alter, drop, insert, update, delete, select on 数据库名字.表名 to 用户名@IP地址;
如：
赋予ming用户 create, alter, drop, insert, update, delete权限在njwangbo中的t_studnet表上
grant create,alter, drop, insert, update, delete on njwangbo.t_studnet to ming@'%';
给root授予在任意主机(%)访问任意数据库的所有权限
grant all privileges on*.*to root@’%’ identified by ‘123456’ with grant option;
给root授予在指定数据库上的权限
**回收权限**
格式：
revoke 权限 on 数据库.*|表 from 用户名@IP地址;
如
回收ming用户在t_studnet表上的select权限
revoke select on njwangbo.t_studnet from ming@'%';
回收ming在njwangbo数据库所有数据对象上的所有权限
revoke all on*.*from ming@'%';
**查看用户的权限**
show grants for 用户名@IP地址
**删除某个用户**
drop user 用户名@IP地址;
delete from user where user=’’;
**修改密码**
使用create user创建用户时，相当于在mysql数据库的user表中插入一行数据。如果修改某个用户的信息（权限，密码），则可以直接修改user表中的列即可。
修改某用户的密码
use mysql;
update user set password=password('root') where user = 'root';
flush privileges;
或者 set password for 用户名=password（'root');
**重命名用户**
RENAME USER old_user TO new_user
**数学函数**
向上取整ceil(8.1);
向下取整floor(8.9);
取余数mod(10,3);
四舍五入round(10.572,1);
截断 truncate(7.123456,2);
**查询**
SELECT [ALL|DISTINCT] select_expr FROM -> WHERE -> GROUP BY [合计函数] -> HAVING -> ORDER BY -> LIMIT
a. select_expr
-- 可以用 * 表示所有字段。
select * from tb;
-- 可以使用表达式（计算公式、函数调用、字段也是个表达式）
select stu, 29+25, now() from tb;
-- 可以为每个列使用别名。适用于简化列标识，避免多个列标识符重复。
- 使用 as 关键字，也可省略 as.
select stu+10 as add10 from tb;
b. FROM 子句
用于标识查询来源。
-- 可以为表起别名。使用as关键字。
SELECT * FROM tb1 AS tt, tb2 AS bb;
-- from子句后，可以同时出现多个表。
-- 多个表会横向叠加到一起，而数据会形成一个笛卡尔积。
SELECT * FROM tb1, tb2;
-- 向优化符提示如何选择索引
USE INDEX、IGNORE INDEX、FORCE INDEX
SELECT * FROM table1 USE INDEX (key1,key2) WHERE key1=1 AND key2=2 AND key3=3;
SELECT * FROM table1 IGNORE INDEX (key3) WHERE key1=1 AND key2=2 AND key3=3;
c. WHERE 子句
-- 从from获得的数据源中进行筛选。
-- 整型1表示真，0表示假。
-- 表达式由运算符和运算数组成。
-- 运算数：变量（字段）、值、函数返回值
-- 运算符：
=, <=>, <>, !=, <=, <, >=, >, !, &&, ||,
in (not) null, (not) like, (not) in, (not) between and, is (not), and, or, not, xor
is/is not 加上ture/false/unknown，检验某个值的真假
<=>与<>功能相同，<=>可用于null比较
d. GROUP BY 子句, 分组子句
GROUP BY 字段/别名 [排序方式]
分组后会进行排序。升序：ASC，降序：DESC
以下[合计函数]需配合 GROUP BY 使用：
count 返回不同的非NULL值数目 count(*)、count(字段)
sum 求和
max 求最大值
min 求最小值
avg 求平均值
group_concat 返回带有来自一个组的连接的非NULL值的字符串结果。组内字符串连接。
e. HAVING 子句，条件子句
与 where 功能、用法相同，执行时机不同。
where 在开始时执行检测数据，对原数据进行过滤。
having 对筛选出的结果再次进行过滤。
having 字段必须是查询出来的，where 字段必须是数据表存在的。
where 不可以使用字段的别名，having 可以。因为执行WHERE代码时，可能尚未确定列值。
where 不可以使用合计函数。一般需用合计函数才会用 having
SQL标准要求HAVING必须引用GROUP BY子句中的列或用于合计函数中的列。
f. ORDER BY 子句，排序子句
order by 排序字段/别名 排序方式 [,排序字段/别名 排序方式]...
升序：ASC，降序：DESC
支持多个字段的排序。
g. LIMIT 子句，限制结果数量子句
仅对处理好的结果进行数量限制。将处理好的结果的看作是一个集合，按照记录出现的顺序，索引从0开始。
limit 起始位置, 获取条数
省略第一个参数，表示从索引0开始。limit 获取条数
h. DISTINCT, ALL 选项
distinct 去除重复记录
默认为 all, 全部记录
## 列约束 ##
1. PRIMARY 主键
- 能唯一标识记录的字段，可以作为主键。
- 一个表只能有一个主键。
- 主键具有唯一性。
- 声明字段时，用 primary key 标识。
也可以在字段列表之后声明
例：create table tab ( id int, stu varchar(10), primary key (id));
- 主键字段的值不能为null。
- 主键可以由多个字段共同组成。此时需要在字段列表后声明的方法。
例：create table tab ( id int, stu varchar(10), age int, primary key (stu, age));
2. UNIQUE 唯一索引（唯一约束）
使得某字段的值也不能重复。
3. NULL 约束
null不是数据类型，是列的一个属性。
表示当前列是否可以为null，表示什么都没有。
null, 允许为空。默认。
not null, 不允许为空。
insert into tab values (null, 'val');
-- 此时表示将第一个字段的值设为null, 取决于该字段是否允许为null
4. DEFAULT 默认值属性
当前字段的默认值。
insert into tab values (default, 'val'); -- 此时表示强制使用默认值。
create table tab ( add_time timestamp default current_timestamp );
-- 表示将当前时间的时间戳设为默认值。
current_date, current_time
5. AUTO_INCREMENT 自动增长约束
自动增长必须为索引（主键或unique）
只能存在一个字段为自动增长。
默认为1开始自动增长。可以通过表属性 auto_increment = x进行设置，或 alter table tbl auto_increment = x;
6. COMMENT 注释
例：create table tab ( id int ) comment '注释内容';
7. FOREIGN KEY 外键约束
用于限制主表与从表数据完整性。
alter table t1 add constraint `t1_t2_fk` foreign key (t1_id) references t2(id);
-- 将表t1的t1_id外键关联到表t2的id字段。
-- 每个外键都有一个名字，可以通过 constraint 指定
存在外键的表，称之为从表（子表），外键指向的表，称之为主表（父表）。
作用：保持数据一致性，完整性，主要目的是控制存储在外键表（从表）中的数据。
MySQL中，可以对InnoDB引擎使用外键约束：
语法：
foreign key (外键字段） references 主表名 (关联字段) [主表记录删除时的动作] [主表记录更新时的动作]
## MyISAM和InnoDB两者区别##
**事务支持**
MyISAM：强调的是性能，每次查询具有原子性,其执行数度比InnoDB类型更快，但是不提供事务支持。
InnoDB：提供事务支持事务，外部键等高级数据库功能。 具有事务(commit)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)的事务安全(transaction-safe (ACID compliant))型表。
**全文索引**
MyISAM：支持(FULLTEXT类型的)全文索引
InnoDB：不支持(FULLTEXT类型的)全文索引，但是innodb可以使用sphinx插件支持全文索引，并且效果更好。
**外键**
MyISAM：不支持
InnoDB：支持
**CURD操作**
MyISAM：如果执行大量的SELECT，MyISAM是更好的选择。
InnoDB：如果你的数据执行大量的INSERT或UPDATE，出于性能方面的考虑，应该使用InnoDB表。DELETE 从性能上InnoDB更优，但DELETE FROM table时，InnoDB不会重新建立表，而是一行一行的删除，在innodb上如果要清空保存有大量数据的表，最好使用truncate table这个命令。
**MyISAM和InnoDB两者的应用场景：**
1) MyISAM管理非事务表。它提供高速存储和检索，以及全文搜索能力。如果应用中需要执行大量的SELECT查询，那么MyISAM是更好的选择。
2) InnoDB用于事务处理应用程序，具有众多特性，包括ACID事务支持。如果应用中需要执行大量的INSERT或UPDATE操作，则应该使用InnoDB，这样可以提高多用户并发操作的性能。
网友评论