mysql学习笔记

作者: Stone_Zhuo | 来源:发表于2017-03-28 14:57 被阅读498次

数据库和表

进入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

mysql学习笔记

数据库和表

字段类型

数字类型

事务

补充

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读

程序猿阵线联盟-汇总各类技术干货

PHP经验分享

程序猿的进阶屋