mysql显示缺半边_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，这样可以提高多用户并发操作的性能。