Mysql随笔
## 数据库操作 ##
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;
7.表的复制
复制表结构
CREATE TABLE 表名 LIKE 要复制的表名
插入数据
insert into 表名 select * from 要复制的表名
复制表结构和数据
CREATE TABLE 表名 [AS] SELECT * FROM 要复制的表名
8.查看表结构
desc table 表名
## 数据类型 ##
**整数** (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 主表名 (关联字段) [主表记录删除时的动作] [主表记录更新时的动作]
## 视图 ##
什么是视图:
视图是一个虚拟表,其内容由查询定义。同真实的表一样,视图包含一系列带有名称的列和行数据。但是,视图并不在数据库中以存储的数据值集形式存在。行和列数据来自由定义视图的查询所引用的表,并且在引用视图时动态生成。
视图具有表结构文件,但不存在数据文件。
对其中所引用的基础表来说,视图的作用类似于筛选。定义视图的筛选可以来自当前或其它数据库的一个或多个表,或者其它视图。通过视图进行查询没有任何限制,通过它们进行数据修改时的限制也很少。
视图是存储在数据库中的查询的sql语句,它主要出于两种原因:安全原因,视图可以隐藏一些数据,如:社会保险基金表,可以用视图只显示姓名,地址,而不显示社会保险号和工资数等,另一原因是可使复杂的查询易于理解和使用。
**-- 创建视图**
CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] VIEW view_name [(column_list)] AS select_statement
- 视图名必须唯一,同时不能与表重名。
- 视图可以使用select语句查询到的列名,也可以自己指定相应的列名。
- 可以指定视图执行的算法,通过ALGORITHM指定。
- column_list如果存在,则数目必须等于SELECT语句检索的列数
-
**-- 查看结构**
SHOW CREATE VIEW view_name
**-- 删除视图**
- 删除视图后,数据依然存在。
- 可同时删除多个视图。
DROP VIEW [IF EXISTS] view_name ...
**-- 修改视图结构**
- 一般不修改视图,因为不是所有的更新视图都会映射到表上。
ALTER VIEW view_name [(column_list)] AS select_statement
## 索引 ##
**概念**
索引用于快速找出在某个列中有一特定值的行,不使用索引,MySQL必须从第一条记录开始读完整个表,直到找出相关的行,表越大,查询数据所花费的时间就越多,如果表中查询的列有一个索引,MySQL能够快速到达一个位置去搜索数据文件,而不必查看所有数据,那么将会节省很大一部分时间。
**优点:**
1、所有的MySql列类型(字段类型)都可以被索引,也就是可以给任意字段设置索引
2、大大加快数据的查询速度
**缺点:**
1、创建索引和维护索引要耗费时间,并且随着数据量的增加所耗费的时间也会增加
2、索引也需要占空间,我们知道数据表中的数据也会有最大上线设置的,如果我们有大量的索引,索引文件可能会比数据文件更快达到上线值
3、当对表中的数据进行增加、删除、修改时,索引也需要动态的维护,降低了数据的维护速度。
**索引的分类**
Mysql常见索引有:主键索引、唯一索引、普通索引、全文索引、组合索引
PRIMARY KEY(主键索引)
ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )
UNIQUE(唯一索引)
ALTER TABLE `table_name` ADD UNIQUE (`column`)
INDEX(普通索引)
ALTER TABLE `table_name` ADD INDEX index_name ( `column` )
FULLTEXT(全文索引)
ALTER TABLE `table_name` ADD FULLTEXT ( `column` )
组合索引
ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )
**Mysql各种索引区别:**
普通索引(INDEX):最基本的索引,没有任何限制
唯一索引(UNIQUE):与"普通索引"类似,不同的就是:索引列的值必须唯一,但允许有空值。
主键索引(PRIMARY):它 是一种特殊的唯一索引,不允许有空值。
全文索引(FULLTEXT ):仅可用于 MyISAM 表, 用于在一篇文章中,检索文本信息的, 针对较大的数据,生成全文索引很耗时好空间。
组合索引:为了更多的提高mysql效率可建立组合索引,遵循”最左前缀“原则。
**建立索引的原则:**
1) 定义主键的数据列一定要建立索引。
2) 定义有外键的数据列一定要建立索引。
3) 对于经常查询的数据列最好建立索引。
4) 对于需要在指定范围内的快速或频繁查询的数据列;
5) 经常用在WHERE子句中的数据列。
6) 经常出现在关键字order by、group by、distinct后面的字段,建立索引。如果建立的是复合索引,索引的字段顺序要和这些关键字后面的字段顺序一致,否则索引不会被使用。
## 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,这样可以提高多用户并发操作的性能。