Contents

1 1.MySQL数据库之管理命令

1.MySQL数据库之管理命令

1.1表的操作

以默认的格式创建一个库
mysql> create database john;
Query OK, 1 row affected (0.00 sec)

mysql> show databases like 'jo%';        ##查询创建的库
+----------------+
| Database (jo%) |
+----------------+
| john           |
+----------------+
1 row in set (0.00 sec)

mysql> show create database john\G       ##查看建库的语句
*************************** 1. row ***************************
 Database: john
Create Database: CREATE DATABASE `john` /*!40100 DEFAULT CHARACTER SET utf8 */
1 row in set (0.00 sec)

1.1.1创建表的语句

mysql> use john
Database changed
mysql> create table student(
 Id int(4) not null,
 Name char(20) not null,
 age tinyint(2) not null default '0',
 dept varchar(16) default NULL
 );

mysql> show tables;         ##查看表
+----------------+
| Tables_in_john |
+----------------+
| student        |
+----------------+
1 row in set (0.00 sec)

mysql> select database();  ##当前数据库
+------------+
| database() |
+------------+
| john       |
+------------+
1 row in set (0.00 sec)

mysql> show create table student\G           ##查看建表语句
*************************** 1. row ***************************
 Table: student
Create Table: CREATE TABLE `student` (
 `Id` int(4) NOT NULL,
 `Name` char(20) NOT NULL,
 `age` tinyint(2) NOT NULL DEFAULT '0',
 `dept` varchar(16) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.01 sec)
 
mysql> desc student;        ##查看表结构
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| Id    | int(4)      | NO   |     | NULL    |       |
| Name  | char(20)    | NO   |     | NULL    |       |
| age   | tinyint(2)  | NO   |     | 0       |       |
| dept  | varchar(16) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

1.1.2MySQL字段类型介绍

1）常用数字类型

列类型                    需要的存储量
tinyint                   需要2的8次方256
smallint                  2个字节
mediumint                 3个字节
int                       4个字节，2的23次方
integer                   4个字节
bigint                    8个字节
float(x)                  4或8如果x<=24时为4，当25<=x<=53时为8
float                     4个字节
double                    8个字节
double precision          8个字节
real                      8个字节
decimal(M,D)              M字节（D+2，如果M<D）

2）时间类型

列类型                    需要的存储量
date                      3个字节
datetime                  8个字节
timestamp                 4个字节
time                      3个字节
year                      1个字节

3）字符串类型

列类型                    需要的存储量
char(M)定长                M字节，1<=M<=255
varchar(M)  变长           L+1字节，在此L<=M和1<=M<=255
tinyblob,tinytext         L+1字节，在此L<2^8
blob,text                 L+2字节，在此L<2^16
MEDIUMBLOB,MEDIUMTEXT     L+3字节，在此L<2^32
ENUM('value1','value2',.) 1或2个字节，取决于枚举值的数目（最大值65535）
set('value1','value2'...) 1,2,3,4或8个字节，取决于集合成员的数量（最多64个成员）

解释说明：

1.INT(M)型：正常大小整数类型
2.CHAR(M)型：定长字符串类型，当存储时，总是用空格填满右边到指定的长度，4个字节，优点：查询速度快
3.VARCHAR型：变长字符串类型

1.1.3查看系统建表语句

mysql> show create table user\G
*************************** 1. row ***************************
 Table: user
Create Table: CREATE TABLE `user` (
 `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
 `User` char(16) COLLATE utf8_bin NOT NULL DEFAULT '',
 `Password` char(41) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '',
 `Select_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 `Insert_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 `Update_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 `Delete_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 `Create_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 `Drop_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 `Reload_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 `Shutdown_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 `Process_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 `File_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 `Grant_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 `References_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 `Index_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 `Alter_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 `Show_db_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 `Super_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 `Create_tmp_table_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 `Lock_tables_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 `Execute_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 `Repl_slave_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 `Repl_client_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 `Create_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 `Show_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 `Create_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 `Alter_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 `Create_user_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 `Event_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 `Trigger_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 `Create_tablespace_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 `ssl_type` enum('','ANY','X509','SPECIFIED') CHARACTER SET utf8 NOT NULL DEFAULT '',
 `ssl_cipher` blob NOT NULL,
 `x509_issuer` blob NOT NULL,
 `x509_subject` blob NOT NULL,
 `max_questions` int(11) unsigned NOT NULL DEFAULT '0',
 `max_updates` int(11) unsigned NOT NULL DEFAULT '0',
 `max_connections` int(11) unsigned NOT NULL DEFAULT '0',
 `max_user_connections` int(11) unsigned NOT NULL DEFAULT '0',
 `plugin` char(64) COLLATE utf8_bin DEFAULT '',
 `authentication_string` text COLLATE utf8_bin,
 PRIMARY KEY (`Host`,`User`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Users and global privileges'
1 row in set (0.00 sec)

贴一份我在企业的建表语句：

CREATE TABLE `tbl_group_visitor_info` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `action` tinyint(2) NOT NULL,
 `title` varchar(200) DEFAULT NULL,
 `person_type` varchar(512) DEFAULT NULL,
 `issue` varchar(50) DEFAULT NULL,
 `send_type` varchar(50) DEFAULT NULL,
 `earthly_branch` varchar(50) DEFAULT NULL,
 `address` varchar(200) DEFAULT NULL,
 `send_time` timestamp NULL DEFAULT '0000-00-00 00:00:00',
 `police_name` varchar(50) DEFAULT NULL,
 `police_num` varchar(50) DEFAULT NULL,
 `police_phone` varchar(50) DEFAULT NULL,
 `interview_deputy` varchar(1024) DEFAULT '',
 `remarks` varchar(512) DEFAULT NULL,
 `start_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
 `end_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
 PRIMARY KEY (`id`),
 KEY `start_time` (`start_time`),
 KEY `end_time` (`end_time`)
) ENGINE=InnoDB AUTO_INCREMENT=46 DEFAULT CHARSET=utf8;

1.2为表的字段创建索引

数据库的索引就像书的目录一样，如果在字段上建立了索引，那么以索引列为查询条件时可以加快查询数据的速度，这是MySQL优化的重要内容之一

1.2.1创建主键索引

查询数据库时按主键查询是最快的，每个表只能有一个主键列，但是可以有多个普通索引列。主键列要求列的所有内容必须唯一，而普通索引列不要求内容必须唯一

主键就类似与我们的学号一样，班级内是唯一的，整个表的每一条记录的主键值在表内都是唯一的，用来唯一标识一条记录

首先，无论建立主键索引还是普通索引，都要在表的对应列上创建，可以对单列创建索引，也可以对多列创建索引

1.在建表时，建立主键索引的方法：

mysql> use john
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> drop table student;
Query OK, 0 rows affected (0.07 sec)

mysql> create table student(
 Id int(4) not null auto_increment,#自增
 Name char(20) not null,
 age tinyint(2) not null default '0',
 dept varchar(16) default NULL,
 primary key(id),
 key index_name(name)
 );
说明：
1.primary key(id)  主键
2key index_name(name) name字段为普通索引

mysql>alter table student drop primary key; ##自增的不能使用alter删除，工作中不这么用
使用命令增加主键
mysql> alter table student change id id int primary key auto_increment;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc student;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| Name  | char(20)    | NO   | MUL | NULL    |                |
| age   | tinyint(2)  | NO   |     | 0       |                |
| dept  | varchar(16) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

2.创建普通索引

1.在建表的时候创建普通索引如下
create table student(
KEY index_name (name)
);
说明：
一般习惯在最下面创建
KEY 关键字
index_name 一般都使用index加下划线跟字段的名字
(name)索引的列

查看索引
mysql> show index from student\G
*************************** 1. row ***************************
 Table: student
 Non_unique: 0
 Key_name: PRIMARY
 Seq_in_index: 1
 Column_name: id
 Collation: A
 Cardinality: 0
 Sub_part: NULL
 Packed: NULL
 Null: 
 Index_type: BTREE
 Comment: 
Index_comment: 
*************************** 2. row ***************************
 Table: student
 Non_unique: 1
 Key_name: index_name
 Seq_in_index: 1
 Column_name: Name
 Collation: A
 Cardinality: 0
 Sub_part: NULL
 Packed: NULL
 Null: 
 Index_type: BTREE
 Comment: 
Index_comment: 
2 rows in set (0.00 sec)

2.建表后利用alter增加普通索引
删除普通索引
mysql> alter table student drop index index_name;
创建普通索引
mysql> alter table student add index index_name(name); ##索引建在列上（name）

3.对字段的前n个字符创建普通索引
  当遇到表中比较大的列时，列内容的前n个字符在所有内容中已经接近唯一时，这时可以对列的前n个字符建立索引，而无需对整个列建立
索引这样可以节省创建索引占用的系统时间，以及降低读取和更新维护索引消耗的系统资源
  对字段的前n个字符创建普通索引的语法：
mysql> desc student;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| Name  | char(20)    | NO   | MUL | NULL    |                |
| age   | tinyint(2)  | NO   |     | 0       |                |
| dept  | varchar(16) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)

mysql> create index index_dept on student(dept(8));##为dept列的前8个字符创建索引
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
等同于命令：
mysql> alter table student add index index_dept(dept(8));

mysql> show index from student\G      ##查看索引详细内容
*************************** 1. row ***************************
 Table: student
 Non_unique: 0
 Key_name: PRIMARY
 Seq_in_index: 1
 Column_name: id
 Collation: A
 Cardinality: 0
 Sub_part: NULL
 Packed: NULL
 Null: 
 Index_type: BTREE
 Comment: 
Index_comment: 
*************************** 2. row ***************************
 Table: student
 Non_unique: 1
 Key_name: index_name
 Seq_in_index: 1
 Column_name: Name
 Collation: A
 Cardinality: 0
 Sub_part: NULL
 Packed: NULL
 Null: 
 Index_type: BTREE
 Comment: 
Index_comment: 
*************************** 3. row ***************************
 Table: student
 Non_unique: 1
 Key_name: index_dept
 Seq_in_index: 1
 Column_name: dept
 Collation: A
 Cardinality: 0
 Sub_part: 8
 Packed: NULL
 Null: YES
 Index_type: BTREE
 Comment: 
Index_comment: 
3 rows in set (0.00 sec)

1.2.2为表的多个字段创建联合索引

如果查询数据的条件是多列时，我们可以为多个查询的列创建联合索引，甚至，可以为多列的前n个字符列创建联合索引，实践如下：

mysql> create index index_name_dept on student(name,dept); ##创建联合索引
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0

可以给前n个字符，创建联合索引
mysql> create index index_name_dept on student(name(8),dept(10));

##工作中，我们需要在唯一值多的大表上创建索引，那如何查看表的唯一值，语句如下：
mysql> select distinct count(user) from mysql.user;
+-------------+
| count(user) |
+-------------+
| 8           |              ##MySQL的user表用户列8个
+-------------+
1 row in set (0.00 sec)

mysql> select count(distinct user) from mysql.user; ##统计表记录的唯一值的命令
+----------------------+
| count(distinct user) |
+----------------------+
| 4                    |
+----------------------+
1 row in set (0.00 sec)
注意：尽量在唯一值多的大表上建立索引
提示：
index(a,b,c)仅a,ab,abc三个查询条件列可以走索引，b,bc,ac,c等无法使用索引
尽量把最常用作为查询条件的列，放在第一的位置
mysql> show create table mysql.user\G
主键也可以创建联合索引
PRIMARY KEY (`Host`,`User`)

1.2.3创建唯一索引（非主键）

mysql> create unique index uni_ind_name on student(name);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> desc student;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| Name  | char(20)    | NO   | UNI | NULL    |                |
| age   | tinyint(2)  | NO   |     | 0       |                |
| dept  | varchar(16) | YES  | MUL | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)

1.3索引小结

更新频繁或重复值很多的列及读取较少的表少建立索引，索引是一把双刃剑，并不是越多越好
索引一定要创建在where后的条件列上，而不是select后的选择数据的列上（select user,host from mysql.user where password=...），尽量在唯一值多的大表上的列建立索引
索引类似书的目录，会加快查询数据的速度
要在表的列（字段）上创建索引
索引会加快查询速度，但是也会影响更新的速度，因为更新需要维护索引数据
多个列联合索引有前缀生效特性
当字段内容前N个字符已经接近唯一时，可以对字段的前N个字符创建索引
索引从工作方式区分，有主键，唯一，普通索引
索引类型会有BTREE（默认）和hash（适合做缓存（内存数据库））等
https://blog.csdn.net/dyllove98/article/details/9631303 ##索引列型介绍

1.4往表中插入数据

（1）语法介绍

insert into <表名>[(<字段名1>[,..<字段名n>])] values (值1)[,(值n)]

（2）创建一个测试表test

CREATE TABLE `test` (
 `id` int(4) NOT NULL AUTO_INCREMENT,
 `Name` char(20) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

（3）往表中插入数据的不同语法命令

1.按规矩指定所有列名，并且每列都插入值
insert into test(id,name) values(1,'zhangsan');
mysql> select * from test;
+----+----------+
| id | Name     |
+----+----------+
| 1  | zhangsan |
+----+----------+
1 row in set (0.00 sec)


2.由于id列为自增的，所以，可以只在name列插入值
insert into test(name) values ('wangsan');
insert into test(id,name) values(null,'zhangsan');

3.如果不指定列，就要按规矩为没列都插入恰当的值
insert into test values(3,'haha');

4.批量插入数据方法，提升效率
insert into test values(4,'kaka'),(5,'fafa');
delete from test;

1.5修改表中数据

1.5.1修改表中指定条件固定列的数据

1.命令语法：update 表名 set 字段=新值，….where 条件（一定要注意条件）

2.修改指定字段的内容

查看要修改的表

mysql> use john
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from test where id=4;##把id号为4，name=fafa的更改为name=pengchun
+------+------+------+
| id   | age  | name |
+------+------+------+
| 4    | 24   | fafa |
+------+------+------+
1 row in set (0.00 sec)

mysql> update john.test set name='pengchun' where id=4;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from test where id=4;
+------+------+----------+
| id   | age  | name     |
+------+------+----------+
| 4    | 24   | pengchun |
+------+------+----------+
1 row in set (0.00 sec)

1.5.2删除表中数据

mysql> delete from test where name='pengchun';
Query OK, 1 row affected (0.00 sec)

mysql> truncate table tesst;

1.5.3通过update伪删除数据

开发人员，建表的时候加一列状态列state状态为1就显示为0就不显示
mysql> select * from test;
+----+------+-----------+--------+
| id | age  | name      | state  |
+----+------+-----------+--------+
| 2  | NULL | john      |   1    |
| 3  | NULL | john01    |   1    |
+----+------+-----------+--------+
页面正常显示的数据：select * from test where state=1;
删除上述john的记录：update test set state=0 where name='john';

1.5.3命令小结

删除主键索引
alter table student drop primary key; ##自增的不能使用alter删除

创建主键索引
alter table student change id id int primary key auto_increment;

创建普通索引
create index index_name on student(name);
alter table student add index index_name(name);

删除普通索引与唯一索引
alter table student drop index index_dept;
drop index index_dept on student;

根据列的前n个字符创建索引
create index index_dept on student(dept(8));
alter table student add index index_dept(dept(8));

根据多个列创建联合索引
create index index_name_dept on student(name,dept);

可以给前n个字符，创建联合索引
create index index_name_dept on student(name(8),dept(10));

创建唯一索引（非主键）
create unique index uni_ind_name on student(name);

查看唯一值数量
select count(distinct user) from mysql.user; ##统计表记录的唯一值的命令

往表中插入数据
insert into test(id,name) values(1,'zhangsan');

批量插入数据方法，提升效率
insert into test values(4,'kaka'),(5,'fafa');

删除表
delete from test;
truncate table test;

删除表中数据
delete from test where name='pengchun';

修改字段类型
alter table test modify age char(4);

更改表名
rename语法:
RENAME TABLE old_table TO backup_table, new_table TO old_table;
rename table test to john01;
alter table test rename to john01;
删除表
drop table john01;

无法对数据库进行更改
mysql -U -uroot -p${mysql_pwd} -S ${mysql_sock_file}

Get busy living or get busy dying. 努力活出精彩的人生，否则便如行尸走肉

如无特殊说明，文章均为本站原创，转载请注明出处

转载请注明来源：MySQL数据库之管理之表的操作
本文永久链接地址：https://www.xionghaier.cn/archives/444.html

该文章由 John 发布

这货来去如风，什么鬼都没留下！！！

MySQL数据库之管理之表的操作

1.MySQL数据库之管理命令

1.1表的操作

1.1.1创建表的语句

1.1.2MySQL字段类型介绍

1.1.3查看系统建表语句

1.2为表的字段创建索引

1.2.1创建主键索引

1.2.2为表的多个字段创建联合索引

1.2.3创建唯一索引（非主键）

1.3索引小结

1.4往表中插入数据

1.5修改表中数据

1.5.1修改表中指定条件固定列的数据

1.5.2删除表中数据

1.5.3通过update伪删除数据

1.5.3命令小结

该文章由 John 发布

Hi，请填写昵称和邮箱！

近期文章

文章归档

近期评论

分类

MySQL数据库之管理之表的操作

1.MySQL数据库之管理命令

1.1表的操作

1.1.1创建表的语句

1.1.2MySQL字段类型介绍

1.1.3查看系统建表语句

1.2为表的字段创建索引

1.2.1创建主键索引

1.2.2为表的多个字段创建联合索引

1.2.3创建唯一索引（非主键）

1.3索引小结

1.4往表中插入数据

1.5修改表中数据

1.5.1修改表中指定条件固定列的数据

1.5.2删除表中数据

1.5.3通过update伪删除数据

1.5.3命令小结

该文章由 John 发布

相关推荐

Saltstack一键部署MySQL主从

数据库主从复制之MySQL半同步

数据备份工具xtrabackup使用介绍

解决atlas与mha结合使用方案

Hi，请填写昵称和邮箱！

近期文章

文章归档

近期评论

分类