mysql学习笔记

数据库和表

进入mysql:mysql -uroot -p

mysql.png

查看数据库:show databases;

show_databases.png

删除数据库:drop database dbname;

drop_database.png

创建数据库:create database dbname;

create_database.png

创建指定编码和字符集的数据库:CREATE DATABASE test DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;

create_database_utf8.png

指定要使用数据库:use dbname，查看数据库中的数据表:show tables

use_show.png

删除数据表:drop table table_name;

drop_table.png

创建一张表:

CREATE TABLE IF NOT EXISTS test_users(
id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT '用户编号',
username VARCHAR(255) NOT NULL DEFAULT '' COMMENT '用户名',
password CHAR(40) NOT NULL DEFAULT '10470c3b4b1fed12c3baac014be15fac67c6e815' COMMENT '密码',
realname VARCHAR(255) NOT NULL DEFAULT '' COMMENT '姓名',
sex ENUM('male', 'female', 'secret') NOT NULL DEFAULT 'secret' COMMENT '性别',
age TINYINT(1) NOT NULL DEFAULT 0 COMMENT '年龄',
introduce TEXT COMMENT '介绍',
created DATETIME NOT NULL DEFAULT NOW() COMMENT '记录创建时间',
modified DATETIME COMMENT '记录修改时间',
UNIQUE INDEX index_username(username)
)ENGINE=InnoDb DEFAULT CHARACTER SET = utf8 COMMENT '用户表';

create_table.png

查看建表语句:show create table table_name;

show_create_table.png

查看表结构:desc table_name;

desc_table.png

不指定字段向表中插入一条记录:insert into tablename values(全部字段值列表)

insert into test_users values(null, 'stone', '10470c3b4b1fed12c3baac014be15fac67c6e815', '刘备', 'male', 50, '刘皇叔', now(), null);

insert_one_value.png

不指定字段向表中插入多条记录:insert into tablename values(全部字段值列表), (全部字段值列表)

insert into test_users values(null, 'jack', '10470c3b4b1fed12c3baac014be15fac67c6e815', '关羽', 'male', 49, '关云长', now(), null), (null, 'tony', '10470c3b4b1fed12c3baac014be15fac67c6e815', '张飞', 'male', 48, '张翼德', now(), nu
ll);

insert_multiple_values.png

向一个表中添加一个字段:alter table tablename add column_name type ...
alter table test_users add status tinyint(1) unsigned not null default 2 comment '记录状态:0-删除, 1-禁用, 2-启用' after introduce;

add_column_after.png

alter table test_users add modified_by int(11) not null comment '记录修改者';

add_column.png

alter table test_users add created_by int(11) unsigned not null default 0 comment '记录创建者' first;

add_column_first.png

指定字段向表中插入一条记录:insert into tablename(字段列表) values(字段值列表)

insert into test_users(username, password, realname, sex, age, introduce) values('stone', '10470c3b4b1fed12c3baac014be15fac67c6e815', '赵云', 'male', 40, '赵子龙');

insert_one_no_default.png

insert into test_users(username, password, realname, sex, age, introduce, modified_by) values('stone', '10470c3b4b1fed12c3baac014be15fac67c6e815', '赵云', 'male', 40, '赵子龙', 1);

insert_one_duplicate_entry.png

insert into test_users(username, password, realname, sex, age, introduce, modified_by) values('tom', '10470c3b4b1fed12c3baac014be15fac67c6e815', '赵云', 'male', 40, '赵子龙', 1);

insert_one_row.png

insert into test_users(username, password, realname, sex, age, introduce, modified_by) values('mike', '10470c3b4b1fed12c3baac014be15fac67c6e815', '马超', 'male', 38, '马孟起', 1), ('client', '10470c3b4b1fed12c3baac014be15fac67c6e815', '黄忠', 'male', 55, '黄汉升', 1);

删除字段:alter table tablename drop column column_name

alter table test_users add column test_small smallint not null default 0 comment '小整型字段';
alter table test_users drop column teat_small;

修改字段类型:alter table tablename modify column_name type ...

alter table test_users modify modified_by int(11) unsigned not null default 0 comment '记录修改者';

删除数据表记录:delete from tablename where column_name1 = column_value1 AND column_name2 = column_value2 ...

delete from test_users where id = 1 and username = 'stone';

delete from test_users;

清空表:truncate tablename

truncate test_users;

更改字段:alter table table_name change column_name new_column_name type ...

alter table test_users change created_by created_by_id int(11) unsigned zerofill not null default 0 comment '记录创建者';

字段类型

数字类型

BIT[(M)]:比特数据类型是一种特殊的数字类型，其中M指示了一个值可以占用多少比特，取值范围是1-64，如果没有指定M，则默认值是1。将数字保存到比特类型字段中将会被转化为数字对应的ascii字符。

CREATE TABLE IF NOT EXISTS test_bit(
bit_test BIT NOT NULL DEFAULT 0 COMMENT '比特测试字段'
)ENGINE=InnoDB DEFAULT CHARACTER SET=utf8 COMMENT '比特类型测试表';

bit_1.png

CREATE TABLE IF NOT EXISTS test_bit(
bit_test BIT(2) NOT NULL DEFAULT 0 COMMENT '比特测试字段'
)ENGINE=InnoDB DEFAULT CHARACTER SET=utf8 COMMENT '比特类型测试表';

bit_2.png

CREATE TABLE IF NOT EXISTS test_bit(
bit_test BIT(3) NOT NULL DEFAULT 0 COMMENT '比特测试字段'
)ENGINE=InnoDB DEFAULT CHARACTER SET=utf8 COMMENT '比特类型测试表';

bit_3.png

CREATE TABLE IF NOT EXISTS test_bit(
bit_test BIT(4) NOT NULL DEFAULT 0 COMMENT '比特测试字段'
)ENGINE=InnoDB DEFAULT CHARACTER SET=utf8 COMMENT '比特类型测试表';

bit_4.png

select_bit_4.png

CREATE TABLE IF NOT EXISTS test_bit(
bit_test BIT(64) NOT NULL DEFAULT 0 COMMENT '比特测试字段'
)ENGINE=InnoDB DEFAULT CHARACTER SET=utf8 COMMENT '比特类型测试表';

select_bit_64.png

TINYINT[(M)] [UNSIGNED] [ZEROFILL]:占用1个字节的整型数据，有符号的数据范围是-128到127，无符号的数据范围是0到255。通过M来指定数字位数，通过UNSIGNED来指定是否有符号，通过ZEROFILL来指定是否进行零填充。

CREATE TABLE IF NOT EXISTS test_int(
tiny_test TINYINT(2) UNSIGNED NOT NULL DEFAULT 0 COMMENT '小整型字段'
)ENGINE=InnoDB DEFAULT CHARACTER SET=utf8 COMMENT '整型数据类型测试表';

unsigned_tinyint.png

CREATE TABLE IF NOT EXISTS test_int(
tiny_test TINYINT(2) ZEROFILL NOT NULL DEFAULT 0 COMMENT '小整型字段'
)ENGINE=InnoDB DEFAULT CHARACTER SET=utf8 COMMENT '整型数据类型测试表';

zerofill_tinyint.png

CREATE TABLE IF NOT EXISTS test_int(
tiny_test TINYINT(2) NOT NULL DEFAULT 0 COMMENT '极小整型字段'
)ENGINE=InnoDB DEFAULT CHARACTER SET=utf8 COMMENT '整型数据类型测试表';

tinyint.png

BOOL(BOOLEAN):布尔型和tinyint(1)是一样的

CREATE TABLE IF NOT EXISTS test_bool(
bool_test BOOL NOT NULL DEFAULT 0 COMMENT '布尔型字段'
)ENGINE=InnoDB DEFAULT CHARACTER SET=utf8 COMMENT '布尔型数据类型测试表';

bool_if.png

SMALLINT[(M)] [UNSIGNED] [ZEROFILL]:占用2个字节的整型数据，有符号的数据范围是-32768到32767，无符号的数据范围是0到65535。通过M来指定数字位数，通过UNSIGNED来指定是否有符号，通过ZEROFILL来指定是否进行零填充。

alter table test_int add column test_small smallint not null default 0 comment '小整型字段';

alter_test_int.png

modify_tiny_test.png

MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL]:占用3个字节的整型数据，有符号的数据范围是-8388608到8388607，无符号的数据范围是0到16777215。通过M来指定数字位数，通过UNSIGNED来指定是否有符号，通过ZEROFILL来指定是否进行零填充。
INT(INTEGER)[(M)] [UNSIGNED] [ZEROFILL]:占用4个字节的整型数据，有符号的数据范围是-2147483648到2147483647，无符号的数据范围是0到4294967296。通过M来指定数字位数，通过UNSIGNED来指定是否有符号，通过ZEROFILL来指定是否进行零填充。
BIGINT[(M)] [UNSIGNED] [ZEROFILL]:占用8个字节的整型数据，有符号的数据范围是-9223372036854775808到9223372036854775807，无符号的数据范围是0到18446744073709551615。通过M来指定数字位数，通过UNSIGNED来指定是否有符号，通过ZEROFILL来指定是否进行零填充。如果一个字段被设置成BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE，则该字段被认为是SERIAL的。

serial.png

bigint.png

bigint2.png

drop_column_2.png

create table if not exists test_int(
bigint_test bigint unsigned not null default 0 comment 'bigint test'
)engine=myisam default character set=utf8 comment '整型测试';

create_bigint_table.png

auto_increment_field.png

bigint_insert_test.png

min_max.png

DECIMAL[(M[,D])] [UNSIGNED] [ZEROFILL]:总数为M，小数位数为D的小数，负数的-及小数点不算在M中，M的最大数为65，D的最大数为30，D的默认值为0，M的默认值为10。通过UNSIGNED来指定是否有符号，通过ZEROFILL来指定是否进行零填充。

create table if not exists test_float(
decimal_test decimal(5, 2) not null default 0 comment 'decimal test'
)engine=myisam default character set=utf8 comment '浮点数测试表';

create_decimal.png

insert_decimal.png

select_decimal.png

unsigned_decimal.png

insert_unsigned_decimal.png

zerofill_decimal.png

DEC[(M[,D])] [UNSIGNED] [ZEROFILL], NUMERIC[(M[,D])] [UNSIGNED] [ZEROFILL], FIXED[(M[,D])] [UNSIGNED] [ZEROFILL]都和DECIMAL和一样的，FIXED适合与其它数据库管理系统进行交互。
FLOAT[(M, D)] [UNSIGNED] [ZEROFILL]:单精度浮点数，取值范围是-1.7976931348623157E+308到-2.2250738585072014E-308, 0, 以及2.2250738585072014E-308到1.7976931348623157E+308，这是基于IEEE标准的理论值，实际值可能略小于这个范围，主要取决于硬件配置和操作系统。M是数字的总位数，D是小数位数，如果M和D被省略了，数值将会根据硬件的限制来存储。单精度浮点数大概可以精确到小数点后面6位。指定了unsigned的字段不能保存负数。使用FLOAT可能会带来意想不到的问题，因为在mysql中数据的计算是按照双精度来进行。

create_float.png

insert_float.png

insert_float(5,2).png

insert_float(5,2)_2.png

DOUBLE[(M, D)] [UNSIGNED] [ZEROFILL]:双精度浮点数，取值范围是-1.7976931348623157E+308到-2.2250738585072014E-308, 0, 以及2.2250738585072014E-308到1.7976931348623157E+308，这是基于IEEE标准的理论值，实际值可能略小于这个范围，主要取决于硬件配置和操作系统。M是数字的总位数，D是小数位数，如果M和D被省略了，数值将会根据硬件的限制来存储。双精度浮点数大概可以精确到小数点后面15位。指定了unsigned的字段不能保存负数。
DOUBLE PRECISION[(M,D)] [UNSIGNED] [ZEROFILL], REAL[(M,D)] [UNSIGNED] [ZEROFILL]这两种是DOUBLE的同义词，但是如果REAL_AS_FLOAT模式被打开的话，REAL就是和FLOAT是同义的。
FLOAT(p) [UNSIGNED] [ZEROFILL]:这是一个浮点数，其中p表示在比特位上的精度，但是mysql仅仅在决定该使用FLOAT还是DOUBLE来存放结果值的时候使用这个值，如果p的范围是0到24，数据类型将是没有M和D的FLOAT。如果p的值从25到53，数据的类型将会是没有指定M和D的DOUBLE。字段的范围和之前描述的单精度浮点类型FLOAT和双精度浮点类型DOUBLE一致。FLOAT(p)是为了和ODBC交互而提供的。
DATE
在mysql中日期格式以YYYY-MM-DD形式显示，取值范围为1000-01-01到9999-12-31，这个类型保存的数据只能是字符串或数字。

create table if not exists datetime_test(
test_date date comment 'test date'
)engine=myisam default character set=utf8 comment '日期时间测试表';

datetime_test.png

insert_date.png

DATETIME[(fsp)]
fsp是可选的，用于表示小于秒的时间，取值范围是0到6，默认值是0。DATETIME可以用于表示日期时间，支持的范围从1000-01-01 00:00:00.000000到9999-12-31 23:59:59.999999。在mysql中日期的格式为YYYY-MM-DD HH:MM:SS[.fraction]，允许存放的值只能是字符串和数字。

alter table datetime_test change test_date test_datetime datetime comment 'datetime test';

create_datetime.png

insert_datetime.png

TIMESTAMP[(fsp)]
timestamp表示时间戳，范围从1970-01-01 00:00:01.000000 UTC to 2038-01-19 03:14:07.999999 UTC，UTC是世界标准时间。timestamp存储的是从1970-01-01 00:00:00 UTC以来的秒数。fsp是可选的，用于表示小于秒的时间，取值范围是0到6，默认值是0。

alter table datetime_test change test_datetime test_timestamp timestamp comment 'timestamp test';

test_timestamp.png

TIME[(fsp)]
YEAR[(4)]
[NATIONAL] CHAR(M) [CHARACTER SET charset_name] [COLLATE collation_name]
M的范围从0到255
[NATIONAL] VARCHAR(M) [CHARACTER SET charset_name] [COLLATE collation_name]
M的范围从0到65535
ENUM('value1','value2',...) [CHARACTER SET charset_name] [COLLATE collation_name]
TINYBLOB
最大可存储字节数为255
TINYTEXT [CHARACTER SET charset_name] [COLLATE collation_name]
最大可存储字节数为255
BLOB[(M)]
最大可存储字节数为65535
TEXT[(M)] [CHARACTER SET charset_name] [COLLATE collation_name]
最大可存储字节数为65535
MEDIUMBLOB
最大可存储字节数为16777215(2 ** 24 - 1)
MEDIUMTEXT [CHARACTER SET charset_name] [COLLATE collation_name]
最大可存储字节数为16777215(2 ** 24 - 1)
LONGBLOB
最大可存储字节数为4294967295 (2 ** 32 − 1)
LONGTEXT [CHARACTER SET charset_name] [COLLATE collation_name]
最大可存储字节数为4294967295 (2 ** 32 − 1)

事务

事务表示一组操作要么都成功要么都不成功。

create_database.jpg

commit.jpg

rollback.jpg

multi_transaction.jpg

commit_rollback.jpg

补充

删除多个字段alter table table_name drop col1, drop col2, drop col3;
导出指定数据库指定表的部分数据mysqldump -h host -u user -p database table --where=" " > file.sql
设置表的auto_increment语句alter table table_name auto_increment=\d

本文首发于公众号：programmer_cc，转载请注明出处。

微信公众号.jpg

最后编辑于：2017.11.20 10:27:44

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 159,015评论 4赞 362
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 67,262评论 1赞 292
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 108,727评论 0赞 243
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 43,986评论 0赞 205
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 52,363评论 3赞 287
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 40,610评论 1赞 219
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 31,871评论 2赞 312
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 30,582评论 0赞 198
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 34,297评论 1赞 242
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 30,551评论 2赞 246
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 32,053评论 1赞 260
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 28,385评论 2赞 253
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 33,035评论 3赞 236
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 26,079评论 0赞 8
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 26,841评论 0赞 195
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 35,648评论 2赞 274
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 35,550评论 2赞 270

mysql学习笔记

数据库和表

字段类型

数字类型

事务

补充

推荐阅读更多精彩内容