MENU

MYSQL笔记

• July 26, 2018 • Read: 111 • Code

[title]MySQL相关操作[/title]

登录:

mysql -uroot -p

mysql -uroot -proot(这里的root是明文密码,不推荐)

mysql -hlocalhost -uroot -p

$ mysql -hlocalhost -uroot -p -P3306(这里的P代表端口号)

退出:

exit

quit

\q

ctrl+c

登录的同时打开数据库:mysql -uroot -p -D db-name(数据库名)

登录信息中需要知道的:

  • 每一行语句以;或者\g结尾
  • 可以通过help或者\h或者?加上相关关键字来查看手册
  • \c可以取消当前命令的执行

常用SQL语句:

  • SELECT USER()
    得到登陆的用户
  • SELECT VERSION()
    得到MySQL的版本信息
  • SELECT NOW()
    得到当前的日期时间
  • SELECT DATABASE()
    得到当前打开的数据库


[title]数据库相关操作[/title]

创建数据库:

create database db-name; or create schema db-name;

create database if not exists db-name;(如果不存在则创建数据库)

create database [if not exists] db_name [default] character set [=] charset;(在创建数据库的同时设置编码方式)

查看当前服务器下所有的数据库:

show databases; or show schemas;

查看数据库的详细信息:

show create database db-name;

修改指定数据库的编码方式:

alter database db_name [default] character set [=] charset;

打开指定数据库;

use db-name;

得到当前打开的数据库:

select database()|schema();

删除指定的数据库:

drop database db-name;

drop database if exists db-name;(如果数据库存在则删除)


[title]数据表相关操作[/title]

是数据库的最重要的组成部分之一,数据是保存在数据表中

数据表由行(row)和列(column)组成,列>=1,行>=0。没有记录的是空表,表名要唯一且有意义。

创建表:

create table [if not exists] tbl-name(

    字段名称 字段类型 [完整性约束条件],
    字段名称 字段类型 [完整性约束条件],
    ...

);ENGINE=存储引擎 CHARACTER=编码方式

约束条件:

UNSIGNED:无符号,没有负数,从0开始

ZEROFILL:零填充,当数据的显示长度不够的时候可以使用前补0的效果填充至指定长度,字段会自动添加UNSIGNED

NOT NULL:非空约束,也就是插入值的时候这个字段必须要给值,值不能为空

DEFAULT:默认值,如果插入记录的时候没有给字段赋值,则使用默认值

PRIMARY KEY:主键,标识记录的唯一性,值不能重复,一个表只能有一个主键,自动禁止为空

AUTO_INCREMENT:自动增长,只能用于数值列,而且配合索引使用,默认起始值从1开始,每次增长1

UNIQUE KEY:唯一性,一个表中可以有多个字段是唯一索引,同样的值不能重复,但是NULL值除外

FOREIGN KEY:外键约束

查看当前数据库下已有数据表:

show tables;

SHOW [FULL] TABLES [{FROM | IN} db_name]
[LIKE 'pattern' | WHERE expr]

查看指定数据表的详细信息:

SHOW CREATE TABLE tbl_name;

查看表结构:

DESC tbl_name;

DESCRIBE tbl_name;

SHOW COLUMNS FROM tbl_name;

删除指定的数据表:

DROP TABLE [IF EXISTS] tbl_name;

表结构相关操作:

添加字段:

ALTER TABLE dbl-name

ADD 字段名称 字段属性 [完成性约束条件] [FIRST|AFTER字段名称]

删除字段:

ALTER TABLE dbl-name

DROP 字段名称

添加默认值:

ALTER TABLE dbl-name

ALTER 字段名称 SET DEFAULT 默认值;

删除默认值:

ALTER TABLE dbl-name

ALTER 字段名称 DROP DEFAULT;

修改字段类型、字段属性:

ALTER TABLE tbl_name

MODIFY 字段名称 字段类型 [字段属性] [FIRST | AFTER 字段名称]

修改字段名称、字段类型、字段属性:

ALTER TABLE tbl_name

CHANGE 原字段名称 新字段名称 字段类型 字段属性 [FIRST | AFTER 字段名称]

添加主键:

ALTER TABLE tbl_name

ADD PRIMARY KEY(字段名称)

删除主键:

ALTER TABLE tbl_name

DROP PRIMARY KEY;

添加唯一:

ALTER TABLE tbl_name

ADD UNIQUE KEY|INDEX [index_name] (字段名称)

删除唯一:

ALTER TABLE tbl_name

DROP index_name;

修改数据表名称:

ALTER TABLE tbl_name

RENAME [TO|AS] new_tbl_name

or

RENAME TABLE tbl_name TO new_tbl_name;

修改AUTO_INCREMENT的值:

ALTER TABLE tbl_name AUTO_INCREMENT=值


[title]MySQL中的数据类型[/title]

主要包括以下五大类:

整数类型:BIT、BOOL、TINY INT、SMALL INT、MEDIUM INT、 INT、 BIG INT

浮点数类型:FLOAT、DOUBLE、DECIMAL

字符串类型:CHAR、VARCHAR、TINY TEXT、TEXT、MEDIUM TEXT、LONGTEXT、TINY BLOB、BLOB、MEDIUM BLOB、LONG BLOB

日期类型:Date、DateTime、TimeStamp、Time、Year

其他数据类型:BINARY、VARBINARY、ENUM、SET、Geometry、Point、MultiPoint、LineString、MultiLineString、Polygon、GeometryCollection等

 

1、整型

MySQL数据类型 含义(有符号)
tinyint(m) 1个字节  范围(-128~127)
smallint(m) 2个字节  范围(-32768~32767)
mediumint(m) 3个字节  范围(-8388608~8388607)
int(m) 4个字节  范围(-2147483648~2147483647)
bigint(m) 8个字节  范围(+-9.22*10的18次方)

取值范围如果加了unsigned,则最大值翻倍,如tinyint unsigned的取值范围为(0~256)。

int(m)里的m是表示SELECT查询结果集中的显示宽度,并不影响实际的取值范围,没有影响到显示的宽度,不知道这个m有什么用。

 

2、浮点型(float和double)

MySQL数据类型 含义
float(m,d) 单精度浮点型    8位精度(4字节)     m总个数,d小数位
double(m,d) 双精度浮点型    16位精度(8字节)    m总个数,d小数位

设一个字段定义为float(6,3),如果插入一个数123.45678,实际数据库里存的是123.457,但总个数还以实际为准,即6位。整数部分最大是3位,如果插入数12.123456,存储的是12.1234,如果插入12.12,存储的是12.1200.

 

3、定点数

浮点型在数据库中存放的是近似值,而定点类型在数据库中存放的是精确值。

decimal(m,d) 参数m<65 是总个数,d<30且 d<m 是小数位。

 

4、字符串(char,varchar,_text)

MySQL数据类型 含义
char(n) 固定长度,最多255个字符
varchar(n) 固定长度,最多65535个字符
tinytext 可变长度,最多255个字符
text 可变长度,最多65535个字符
mediumtext 可变长度,最多2的24次方-1个字符
longtext 可变长度,最多2的32次方-1个字符

char和varchar:

1.char(n) 若存入字符数小于n,则以空格补于其后,查询之时再将空格去掉。所以char类型存储的字符串末尾不能有空格,varchar不限于此。

2.char(n) 固定长度,char(4)不管是存入几个字符,都将占用4个字节,varchar是存入的实际字符数+1个字节(n<=255)或2个字节(n>255),

所以varchar(4),存入3个字符将占用4个字节。

3.char类型的字符串检索速度要比varchar类型的快。
varchar和text:

1.varchar可指定n,text不能指定,内部存储varchar是存入的实际字符数+1个字节(n<=255)或2个字节(n>255),text是实际字符数+2个字

节。

2.text类型不能有默认值。

3.varchar可直接创建索引,text创建索引要指定前多少个字符。varchar查询速度快于text,在都创建索引的情况下,text的索引似乎不起作用。

 

5.二进制数据(_Blob)

1._BLOB和_text存储方式不同,_TEXT以文本方式存储,英文存储区分大小写,而_Blob是以二进制方式存储,不分大小写。

2._BLOB存储的数据只能整体读出。

3._TEXT可以指定字符集,_BLO不用指定字符集。

 

6.日期时间类型

MySQL数据类型 含义
date 日期 '2008-12-2'
time 时间 '12:25:36'
datetime 日期时间 '2008-12-2 22:06:44'
timestamp 自动存储记录修改时间

若定义一个字段为timestamp,这个字段里的时间数据会随其他字段修改的时候自动刷新,所以这个数据类型的字段可以存放这条记录最后被修改的时间。

 

数据类型的属性

 

MySQL关键字 含义
NULL 数据列可包含NULL值
NOT NULL 数据列不允许包含NULL值
DEFAULT 默认值
PRIMARY KEY 主键
AUTO_INCREMENT 自动递增,适用于整数类型
UNSIGNED 无符号
CHARACTER SET name  

指定一个字符集

 


[title]MYSQL存储引擎[/title]

存储引擎就是指表的类型,存取和处理数据的方式。

查看当前支持的存储引擎:

show engines;

存储引擎的分类:

  • MEMORY存储引擎
  • CSV存储引擎
  • ARCHIVE存储引擎
  • MyISAM存储引擎
  • InnoDB存储引擎(默认)


[title]MySQL数据操作[/title]

增删改查

向表中插入数据 (增)
insert 语句可以用来将一行或多行数据插到数据库表中, 使用的一般形式如下:

insert into 表名 (列名1, 列名2, 列名3, ...) values (值1, 值2, 值3, ...); 字段和值一定要用括号括起来;

其中 [] 内的内容是可选的, 例如, 要给 samp_db 数据库中的 students 表插入一条记录, 执行语句:

insert into students values(NULL, "王刚", "男", 20, "13811371377");

按回车键确认后若提示 Query Ok, 1 row affected (0.05 sec) 表示数据插入成功。 若插入失败请检查是否已选择需要操作的数据库。

有时我们只需要插入部分数据, 或者不按照列的顺序进行插入, 可以使用这样的形式进行插入:

insert into students (name, sex, age) values("孙丽华", "女", 21);

查询表中的数据 (查)
select 语句常用来根据一定的查询规则到数据库中获取数据, 其基本的用法为:

select 列名称 from 表名称 [查询条件];

例如要查询 students 表中所有学生的名字和年龄, 输入语句 select name, age from students; 执行结果如下:

mysql> select name, age from students;
+--------+-----+
| name | age |
+--------+-----+
| 王刚 | 20 |
| 孙丽华 | 21 |
| 王永恒 | 23 |
| 郑俊杰 | 19 |
| 陈芳 | 22 |
| 张伟朋 | 21 |
+--------+-----+
6 rows in set (0.00 sec)

mysql>
也可以使用通配符 * 查询表中所有的内容, 语句: select * from students;

按特定条件查询:
where 关键词用于指定查询条件, 用法形式为: select 列名称 from 表名称 where 条件;

以查询所有性别为女的信息为例, 输入查询语句: select * from students where sex="女";

where 子句不仅仅支持 "where 列名 = 值" 这种名等于值的查询形式, 对一般的比较运算的运算符都是支持的, 例如 =、>、<、>=、<、!= 以及一些扩展运算符 is [not] null、in、like 等等。 还可以对查询条件使用 or 和 and 进行组合查询, 以后还会学到更加高级的条件查询方式, 这里不再多做介绍。

示例:

查询年龄在21岁以上的所有人信息: select * from students where age > 21;

查询名字中带有 "王" 字的所有人信息: select * from students where name like "%王%";

查询id小于5且年龄大于20的所有人信息: select * from students where id<5 and age>20;

更新表中的数据 (改)
update 语句可用来修改表中的数据, 基本的使用形式为:

update 表名称 set 列名称=新值 where 更新条件;

使用示例:

将id为5的手机号改为默认的"-": update students set tel=default where id=5;

将所有人的年龄增加1: update students set age=age+1;

将手机号为 13288097888 的姓名改为 "张伟鹏", 年龄改为 19: update students set name="张伟鹏", age=19 where tel="13288097888";

删除表中的数据 (删)
delete 语句用于删除表中的数据, 基本用法为:

delete from 表名称 where 删除条件;

使用示例:

删除id为2的行: delete from students where id=2;

删除所有年龄小于21岁的数据: delete from students where age<20;

删除表中的所有数据: delete from students;

创建后表的修改
alter table 语句用于创建后对表的修改, 基础用法如下:

添加列
基本形式: alter table 表名 add 列名 列数据类型 [after 插入位置];

示例:

在表的最后追加列 address: alter table students add address char(60);

在名为 age 的列后插入列 birthday: alter table students add birthday date after age;

修改列
基本形式: alter table 表名 change 列名称 列新名称 新数据类型;

示例:

将表 tel 列改名为 telphone: alter table students change tel telphone char(13) default "-";

将 name 列的数据类型改为 char(16): alter table students change name name char(16) not null;

删除列
基本形式: alter table 表名 drop 列名称;

示例:

删除 birthday 列: alter table students drop birthday;

重命名表
基本形式: alter table 表名 rename 新表名;

示例:

重命名 students 表为 workmates: alter table students rename workmates;

删除整张表
基本形式: drop table 表名;

示例: 删除 workmates 表: drop table workmates;

删除整个数据库
基本形式: drop database 数据库名;

示例: 删除 samp_db 数据库: drop database samp_db;


[title]MYSQL常用函数[/title]

一、数学函数

 

ABS(x) 返回 x 的绝对值
BIN(x) 返回 x 的二进制(OCT 返回八进制,HEX 返回十

六进制)
CEILING(x) 返回大于 x 的最小整数值
EXP(x) 返回值 e(自然对数的底)的 x 次方
FLOOR(x) 返回小于 x 的最大整数值
GREATEST(x1,x2,...,xn) 返回集合中最大的值
LEAST(x1,x2,...,xn) 返回集合中最小的值
LN(x) 返回 x 的自然对数
LOG(x,y) 返回 x 的以 y 为底的对数
MOD(x,y) 返回 x/y 的模(余数)
PI() 返回 pi 的值(圆周率)
RAND() 返回0到1内的随机值,可以通过提供一个参数(种 子)使 RAND()随机数生成器生成一个指定的值。
ROUND(x,y) 返回参数 x 的四舍五入的有 y 位小数的值
SIGN(x) 返回代表数字 x 的符号的值
SQRT(x) 返回一个数的平方根
TRUNCATE(x,y) 返回数字 x 截短为 y 位小数的结果

二、聚合函数(常用于 GROUP BY 从句的 SELECT 查询中)

 

AVG(col_name) 返回指定列的平均值
COUNT(col_name) 返回指定列中非 NULL 值的个数
MIN(col_name) 返回指定列的最小值
MAX(col_name) 返回指定列的最大值
SUM(col_name) 返回指定列的所有值之和

 

三、字符串函数

 

ASCII(char) 返回字符的 ASCII 码值
BIT_LENGTH(str) 返回字符串的比特长度
CONCAT(s1,s2...,sn) 将 s1,s2...,sn 连接成字符串
CONCAT_WS(sep,s1,s2...,sn) 将 s1,s2...,sn 连接成字符串,并用 sep 字符间隔
INSERT(str,x,y,instr) 将字符串 str 从第 x 位置开始,y 个字符长的子串替

换为字符串 instr,返回结果
FIND_IN_SET(str,list) 分析逗号分隔的 list 列表,如果发现 str,返回 str 在

list 中的位置
LCASE(str)或 LOWER(str) 返回将字符串 str 中所有字符改变为小写后的结果
LEFT(str,x) 返回字符串 str 中最左边的 x 个字符
LENGTH(s) 返回字符串 str 中的字符数
LTRIM(str) 从字符串 str 中切掉开头的空格
POSITION(substr,str) 返回子串 substr 在字符串 str 中第一次出现的位置

 

QUOTE(str) 用反斜杠转义 str 中的单引号
REPEAT(str,srchstr,rplcstr) 返回字符串 str 重复 x 次的结果
REVERSE(str) 返回颠倒字符串 str 的结果
RIGHT(str,x) 返回字符串 str 中最右边的 x 个字符
RTRIM(str) 返回字符串 str 尾部的空格
STRCMP(s1,s2) 比较字符串 s1 和 s2
TRIM(str) 去除字符串首部和尾部的所有空格
UCASE(str)或 UPPER(str) 返回将字符串 str 中所有字符转变为大写后的结果

四、日期和时间函数

 

NOW()              返回当前的日期和时间
CURDATE()或 CURRENT_DATE()                返回当前的日期
CURTIME()或 CURRENT_TIME()                    返回当前的时间
DATE_ADD(date,INTERVAL  int  keyword)                返回日期 date 加上间隔时间 int 的结果

(int 必须按照关键字进行格式化)

 

例如:
DATE_FORMAT(date,fmt)               依照指定的 fmt 格式格式化日期 date 值
FROM_UNIXTIME(ts,fmt)                 根据指定的 fmt 格式,格式化 UNIX 时间戳 ts
DAYOFWEEK(date)            返回 date 所代表的一星期中的第几天(1~7)
DAYOFMONTH(date)            返回 date 是一个月的第几天(1~31)
DAYOFYEAR(date)              返回 date 是一年的第几天(1~366)

 

QUARTER(date)            返回 date 在一年中的季度(1~4) , 如

 
DAYNAME(date)              返回 date 的星期名,如:
MONTHNAME(date) 返回 date 的月份名,如:
YEAR(date)              返回日期 date 的年份(1000~9999)
MONTH(date)             返回 date 的月份值(1~12)
HOUR(time)               返回 time 的小时值(0~23)
WEEK(date)            返回日期 date 为一年中第几周(0~53)
MINUTE(time)             返回 time 的分钟值(0~59)

 

根据 format 字符串格式化 date 值。下列修饰符可以被用在 format 字符串中:

 

%M 月名字(January……December)
%W 星期名字(Sunday……Saturday)
%D 有英语前缀的月份的日期(1st, 2nd, 3rd …)
%Y 年, 数字, 4 位
%y 年, 数字, 2 位
%a 缩写的星期名字(Sun……Sat)
%d 月份中的天数, 数字(00……31)
%e 月份中的天数, 数字(0……31)
%m 月, 数字(01……12)

 

%c 月, 数字(1……12)
%b 缩写的月份名字(Jan……Dec)
%j 一年中的天数(001……366)
%H 小时(00……23)
%k 小时(0……23)
%h 小时(01……12)
%I 小时(01……12)
%l 小时(1……12)
%i 分钟, 数字(00……59)
%r 时间,12 小时(hh:mm:ss [AP]M)
%T 时间,24 小时(hh:mm:ss)
%S 秒(00……59)
%s 秒(00……59)
%p AM 或 PM
%w 一个星期中的天数(0=Sunday ……6=Saturday )
%U 星期(0……52), 这里星期天是星期的第一天
%u 星期(0……52), 这里星期一是星期的第一天
%% 一个文字“%”。

五、加密函数

 

AES_ENCRYPT(str,key) 返回用密钥 key 对字符串 str 利用高级加密标准算法加密后的结果,调用 AES_ENCRYPT 的结果是一个二进制字

符串,以 BLOB 类型存储
AES_DECRYPT(str,key) 返回用密钥 key 对字符串 str 利用高级加密标准算法解密

后的结果
DECODE(str,key) 使用 key 作为密钥解密加密字符串 str
ENCRYPT(str,salt) 使用 UNIXcrypt()函数,用关键词 salt(一个可以唯一确

定口令的字符串,就像钥匙一样)加密字符串 str
ENCODE(str,key) 使用 key 作为密钥加密字符串 str,调用 ENCODE()的结

果是一个二进制字符串,它以 BLOB 类型存储
MD5() 计算字符串 str 的 MD5 校验和
PASSWORD(str) 返回字符串 str 的加密版本,这个加密过程是不可逆转

的,和 UNIX 密码加密过程使用不同的算法。
SHA() 计算字符串 str 的安全散列算法(SHA)校验和


[title]图形化工具管理数据库[/title]

  • phpmyadmin
  • Navicat


[title]常用SQL语句[/title]

查看上一步的警告信息:show warnings;

mysql中的注释:#或--

检测表:CHECK TABLE tbl_name

修复表:REPAIR TABLE tbl_name