自学mysql数据库。
1.察看当前数据库
mysql> show databases\G
*************************** 1. row ***************************
Database: information_schema
*************************** 2. row ***************************
Database: mysql
*************************** 3. row ***************************
Database: performance_schema
3 rows in set (0.01 sec)
2.创建数据库
mysql> create database test\G
Query OK, 1 row affected (0.00 sec)
mysql> show databases\G
*************************** 1. row ***************************
Database: information_schema
*************************** 2. row ***************************
Database: mysql
*************************** 3. row ***************************
Database: performance_schema
*************************** 4. row ***************************
Database: test
4 rows in set (0.00 sec)
3.删除数据库
mysql> drop database test
-> ;
Query OK, 0 rows affected (0.13 sec)
mysql> show databases\G
*************************** 1. row ***************************
Database: information_schema
*************************** 2. row ***************************
Database: mysql
*************************** 3. row ***************************
Database: performance_schema
3 rows in set (0.00 sec)
4.存储引擎
是MySQL的特点,是一种插入式的存储引擎概念,决定了MySQL数据库中的表可以用不同的方式存储。
查看MySQL数据库支持的存储引擎类型。
mysql> show engines\G
*************************** 1. row ***************************
Engine: FEDERATED
Support: NO
Comment: Federated MySQL storage engine
Transactions: NULL
XA: NULL
Savepoints: NULL
*************************** 2. row ***************************
Engine: MRG_MYISAM
Support: YES
Comment: Collection of identical MyISAM tables
Transactions: NO
XA: NO
Savepoints: NO
*************************** 3. row ***************************
Engine: MyISAM
Support: YES
Comment: MyISAM storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 4. row ***************************
Engine: BLACKHOLE
Support: YES
Comment: /dev/null storage engine (anything you write to it disappears)
Transactions: NO
XA: NO
Savepoints: NO
*************************** 5. row ***************************
Engine: MEMORY
Support: YES
Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
XA: NO
Savepoints: NO
*************************** 6. row ***************************
Engine: CSV
Support: YES
Comment: CSV storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 7. row ***************************
Engine: ARCHIVE
Support: YES
Comment: Archive storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 8. row ***************************
Engine: PERFORMANCE_SCHEMA
Support: YES
Comment: Performance Schema
Transactions: NO
XA: NO
Savepoints: NO
*************************** 9. row ***************************
Engine: InnoDB
Support: DEFAULT
Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
XA: YES
Savepoints: YES
9 rows in set (0.00 sec)
Engine参数指存储引擎名称,
Support参数说明MySQL是否支持该类引擎,YES表示支持;
Transactions参数表示是否支持事务处理,YES表示支持;
XA参数表示是否分布式交易处理的XA规范,YES表示支持;
Savepoints参数表示是否支持保存点,以便事务回滚到保存点,YES表示支持;
InnoDB为默认存储引擎;
另一种方式查询MySQL支持的存储引擎;
mysql> show variables like 'have%';
+----------------------+----------+
| Variable_name | Value |
+----------------------+----------+
| have_compress | YES |
| have_crypt | YES |
| have_csv | YES |
| have_dynamic_loading | YES |
| have_geometry | YES |
| have_innodb | YES |
| have_ndbcluster | NO |
| have_openssl | DISABLED |
| have_partitioning | YES |
| have_profiling | YES |
| have_query_cache | YES |
| have_rtree_keys | YES |
| have_ssl | DISABLED |
| have_symlink | YES |
+----------------------+----------+
14 rows in set (0.00 sec)
5.察看MySQL默认的存储引擎
mysql> show variables like 'storage_engine';
+----------------+--------+
| Variable_name | Value |
+----------------+--------+
| storage_engine | InnoDB |
+----------------+--------+
1 row in set (0.00 sec)
若想修改默认的存储引擎,可以在my.ini中修改。将“default-storage-engine=INNODB"改为”default-storage-engine=MyISAM",然后重启服务,修改生效。
6.InnoDB存储引擎的优势在于提供了良好的事务管理,崩溃修复能力和并发控制。缺点读写效率稍差,占用的数据空间相对比较大。
MyISAM存储引擎的优势在于占用空间小,处理速度快,缺点是不支持事务的完整性和并发性。
memory存储引擎优点处理速度非常快,缺点是数据易丢失,生命周期短。
特性 | InnoDB | MyISAM | MEMORY |
事务安全 | 支持 | 无 | 无 |
存储限制 | 64TB | 有 | 有 |
空间使用 | 高 | 低 | 低 |
内存使用 | 高 | 低 | 高 |
插入数据的速度 | 低 | 高 | 高 |
对外健的支持 | 支持 | 无 | 无 |
7.创建表
mysql> use test;
Database changed
mysql> create table student(num int(10) not null unique primary key,
-> name varchar(20) not null,
-> sex varchar(4) not null,
-> birthday datetime,
-> address varchar(50));
Query OK, 0 rows affected (0.35 sec)
mysql> desc student
-> ;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| num | int(10) | NO | PRI | NULL | |
| name | varchar(20) | NO | | NULL | |
| sex | varchar(4) | NO | | NULL | |
| birthday | datetime | YES | | NULL | |
| address | varchar(50) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
mysql> show create table student\G
*************************** 1. row ***************************
Table: student
Create Table: CREATE TABLE `student` (
`num` int(10) NOT NULL,
`name` varchar(20) NOT NULL,
`sex` varchar(4) NOT NULL,
`birthday` datetime DEFAULT NULL,
`address` varchar(50) DEFAULT NULL,
PRIMARY KEY (`num`),
UNIQUE KEY `num` (`num`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
8.修改列属性
修改类型
mysql> alter table student modify name varchar(50);
Query OK, 0 rows affected (0.90 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table student\G
*************************** 1. row ***************************
Table: student
Create Table: CREATE TABLE `student` (
`num` int(10) NOT NULL,
`name` varchar(50) DEFAULT NULL,
`sex` varchar(4) NOT NULL,
`birthday` datetime DEFAULT NULL,
`address` varchar(50) DEFAULT NULL,
PRIMARY KEY (`num`),
UNIQUE KEY `num` (`num`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
修改顺序
mysql> alter table student modify address varchar(50) after num;
Query OK, 0 rows affected (0.82 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table student\G
*************************** 1. row ***************************
Table: student
Create Table: CREATE TABLE `student` (
`num` int(10) NOT NULL,
`address` varchar(50) DEFAULT NULL,
`name` varchar(50) DEFAULT NULL,
`sex` varchar(4) NOT NULL,
`birthday` datetime DEFAULT NULL,
PRIMARY KEY (`num`),
UNIQUE KEY `num` (`num`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
修改列名
mysql> alter table student change birthday bir datetime;
Query OK, 0 rows affected (0.79 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table student\G
*************************** 1. row ***************************
Table: student
Create Table: CREATE TABLE `student` (
`num` int(10) NOT NULL,
`address` varchar(10) DEFAULT NULL,
`name` varchar(50) DEFAULT NULL,
`sex` varchar(4) NOT NULL,
`bir` datetime DEFAULT NULL,
PRIMARY KEY (`num`),
UNIQUE KEY `num` (`num`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
修改存储引擎和字符集
mysql> alter table student engine=MyISAM;
Query OK, 0 rows affected (0.43 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table student\G
*************************** 1. row ***************************
Table: student
Create Table: CREATE TABLE `student` (
`num` int(10) NOT NULL,
`address` varchar(10) DEFAULT NULL,
`name` varchar(50) DEFAULT NULL,
`sex` varchar(4) NOT NULL,
`bir` datetime DEFAULT NULL,
PRIMARY KEY (`num`),
UNIQUE KEY `num` (`num`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> alter table student charset=utf8
-> ;
Query OK, 0 rows affected (0.30 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table student\G
*************************** 1. row ***************************
Table: student
Create Table: CREATE TABLE `student` (
`num` int(10) NOT NULL,
`address` varchar(10) CHARACTER SET latin1 DEFAULT NULL,
`name` varchar(50) CHARACTER SET latin1 DEFAULT NULL,
`sex` varchar(4) CHARACTER SET latin1 NOT NULL,
`bir` datetime DEFAULT NULL,
PRIMARY KEY (`num`),
UNIQUE KEY `num` (`num`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
删除列
mysql> alter table student drop bir;
Query OK, 0 rows affected (0.24 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table student\G
*************************** 1. row ***************************
Table: student
Create Table: CREATE TABLE `student` (
`num` int(10) NOT NULL,
`address` varchar(10) CHARACTER SET latin1 DEFAULT NULL,
`name` varchar(50) CHARACTER SET latin1 DEFAULT NULL,
`sex` varchar(4) CHARACTER SET latin1 NOT NULL,
PRIMARY KEY (`num`),
UNIQUE KEY `num` (`num`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
增加列
mysql> alter table student add birth datetime;
Query OK, 0 rows affected (0.23 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table student\G
*************************** 1. row ***************************
Table: student
Create Table: CREATE TABLE `student` (
`num` int(10) NOT NULL,
`address` varchar(10) CHARACTER SET latin1 DEFAULT NULL,
`name` varchar(50) CHARACTER SET latin1 DEFAULT NULL,
`sex` varchar(4) CHARACTER SET latin1 NOT NULL,
`birth` datetime DEFAULT NULL,
PRIMARY KEY (`num`),
UNIQUE KEY `num` (`num`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.01 sec)
9.删除表
mysql> drop table student;
Query OK, 0 rows affected (0.00 sec)
10.索引
包括:普通索引,唯一性索引,全文索引,单列索引,多列索引和空间索引。
所有的存储引擎对每个表至少支持16个索引,从索引长度至少256字节。索引有两种存储类型,B型树索引和哈希索引。默认是前者。InnoDB和MyISAM存储引擎支持B索引,memory存储引擎支持两者。
全文索引只能创建在char,varchar或text类型的字段上。
a.创建普通索引
mysql> create table student(id int,
-> name varchar(20),
-> sex boolean,
-> index(id));
Query OK, 0 rows affected (0.36 sec)
mysql> show create table student\G
*************************** 1. row ***************************
Table: student
Create Table: CREATE TABLE `student` (
`id` int(11) DEFAULT NULL,
`name` varchar(20) DEFAULT NULL,
`sex` tinyint(1) DEFAULT NULL,
KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
察看索引是否被使用
mysql> explain select * from student where id =1\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: student
type: ref
possible_keys: id
key: id
key_len: 5
ref: const
rows: 1
Extra: Using where
1 row in set (0.00 sec)
已经被使用。
b.创建唯一性索引
mysql> create table student(id int unique,
-> name varchar(20),
-> sex boolean,
-> unique index unique_id(id asc)
-> );
Query OK, 0 rows affected (0.48 sec)
mysql> show create table student\G
*************************** 1. row ***************************
Table: student
Create Table: CREATE TABLE `student` (
`id` int(11) DEFAULT NULL,
`name` varchar(20) DEFAULT NULL,
`sex` tinyint(1) DEFAULT NULL,
UNIQUE KEY `id` (`id`),
UNIQUE KEY `unique_id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
c.创建全文索引
mysql> create table student(id int,name varchar(30), sex boolean, fulltext index fulltext_name(name) )engine=MyISAM;
Query OK, 0 rows affected (0.22 sec)
mysql> show create table student\G
*************************** 1. row ***************************
Table: student
Create Table: CREATE TABLE `student` (
`id` int(11) DEFAULT NULL,
`name` varchar(30) DEFAULT NULL,
`sex` tinyint(1) DEFAULT NULL,
FULLTEXT KEY `fulltext_name` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
d.创建单列索引
mysql> create table student(id int,name varchar(20),data varchar(50),
-> index danlie_data(data(5)));
Query OK, 0 rows affected (0.33 sec)
mysql> show create table student\G
*************************** 1. row ***************************
Table: student
Create Table: CREATE TABLE `student` (
`id` int(11) DEFAULT NULL,
`name` varchar(20) DEFAULT NULL,
`data` varchar(50) DEFAULT NULL,
KEY `danlie_data` (`data`(5))
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
e.创建多列索引
mysql> create table student(id int,st_id int,kc_id int,data varchar(20),index index2(st_id,kc_id));
Query OK, 0 rows affected (0.36 sec)
mysql> show create table student\G
*************************** 1. row ***************************
Table: student
Create Table: CREATE TABLE `student` (
`id` int(11) DEFAULT NULL,
`st_id` int(11) DEFAULT NULL,
`kc_id` int(11) DEFAULT NULL,
`data` varchar(20) DEFAULT NULL,
KEY `index2` (`st_id`,`kc_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
f.创建空间索引
删除索引
drop index 索引名 on 表名