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
最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
平台声明：文章内容（如有图片或视频亦包括在内）由作者上传并发布，文章内容仅代表作者本人观点，简书系信息发布平台，仅提供信息存储服务。

推荐阅读更多精彩内容

  • 面向对象的用电信息数据交换协议
    国家电网公司企业标准（Q/GDW）- 面向对象的用电信息数据交换协议 - 报批稿：20170802 前言： 排版 ...
    庭说阅读 11,262评论 6赞 13
  • mysql 学习笔记1 基础
    参考MySQL 5.5 Reference ManualMySQL 教程 | 菜鸟教程 知识点大纲 1 基本知识1...
    Dargonfly429阅读 467评论 0赞 0
  • MySQL学习笔记
    目录 MySQL简介 基本输入查询 创建并使用数据库 SELECT语句 选择特殊行 条件 通配符 选择特殊列 排序...
    鬼宇书生阅读 1,046评论 2赞 1
  • MySQL学习笔记
    参考资料 慕课网——与MySQL的零距离接触 前言 本人菜鸟，入IT只为当鼓励师。本编文章主要对 MySQL的一些...
    BeYanJin阅读 331评论 0赞 2
  • mySQL笔记
    1、MySQL启动和关闭（安装及配置请参照百度经验，这里不再记录。MySQL默认端口号：3306；默认数据类型格式...
    强壮de西兰花阅读 686评论 0赞 1