Table of Contents
1 环境
使用 wampserver 默认环境（还可以用 Navicat， phpmyadmin之类的）
2 terminal 连接
使用 PHPstorm 自带的 Terminal，切换到执行文件所在的目录
mysql.exe -hlocalhost -uroot -p
3 数据库操作
3.1 注释形式
MariaDB [(none)]> -- 单行注释 MariaDB [(none)]> /*开始多行注释 /*> 注释 /*> 注释 /*> 。。。 /*> 结束*/
3.2 指定字符集
MariaDB [(none)]> set names utf8;
3.3 查看字符集
MariaDB [(none)]> show charset; ---------- ----------------------------- --------------------- -------- | Charset | Description | Default collation | Maxlen | ---------- ----------------------------- --------------------- -------- | big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 | | dec8 | DEC West European | dec8_swedish_ci | 1 | ...... | gbk | GBK Simplified Chinese | gbk_chinese_ci | 2 | | latin5 | ISO 8859-9 Turkish | latin5_turkish_ci | 1 | | armscii8 | ARMSCII-8 Armenian | armscii8_general_ci | 1 | | utf8 | UTF-8 Unicode | utf8_general_ci | 3 | | ucs2 | UCS-2 Unicode | ucs2_general_ci | 2 | ...... | cp932 | SJIS for Windows Japanese | cp932_japanese_ci | 2 | | eucjpms | UJIS for Windows Japanese | eucjpms_japanese_ci | 3 | ---------- ----------------------------- --------------------- --------
3.4 查看校验规则（排序规则）
MariaDB [(none)]> show collation; ------------------------------ ---------- ------ --------- ---------- --------- | Collation | Charset | Id | Default | Compiled | Sortlen | ------------------------------ ---------- ------ --------- ---------- --------- | big5_chinese_ci | big5 | 1 | Yes | Yes | 1 | | big5_bin | big5 | 84 | | Yes | 1 | ...... | utf8_general_ci | utf8 | 33 | Yes | Yes | 1 | ...... | eucjpms_japanese_nopad_ci | eucjpms | 1121 | | Yes | 1 | | eucjpms_nopad_bin | eucjpms | 1122 | | Yes | 1 | ------------------------------ ---------- ------ --------- ---------- ---------
3.5 查看数据库
MariaDB [(none)]> show databases; -------------------- | Database | -------------------- | db1 | | information_schema | | mysql | | performance_schema | | test | --------------------
3.6 创建数据库
create database ‘db_name‘ [charset ‘name‘] [collate ‘name‘]
MariaDB [(none)]> create database md charset utf8; MariaDB [(none)]> show databases; -------------------- | Database | -------------------- | db1 | | information_schema | | md | | mysql | | performance_schema | | test | --------------------
3.7 显示创建数据库语句
MariaDB [(none)]> show create database md; ---------- ------------------------------------------------------------- | Database | Create Database | ---------- ------------------------------------------------------------- | md | CREATE DATABASE `md` /*!40100 DEFAULT CHARACTER SET utf8 */ | ---------- -------------------------------------------------------------
3.8 修改数据库
MariaDB [(none)]> alter database md charset gbk; MariaDB [(none)]> show create database md; ---------- ------------------------------------------------------------ | Database | Create Database | ---------- ------------------------------------------------------------ | md | CREATE DATABASE `md` /*!40100 DEFAULT CHARACTER SET gbk */ | ---------- ------------------------------------------------------------
3.9 删除数据库
MariaDB [(none)]> drop database md; MariaDB [(none)]> show databases; -------------------- | Database | -------------------- | db1 | | information_schema | | mysql | | performance_schema | | test | --------------------
3.10 切换到某个数据库
MariaDB [(none)]> create database m charset utf8; MariaDB [(none)]> use m; Database changed MariaDB [m]>
4 表操作
4.1 显示表
MariaDB [m]> show tables; Empty set (0.001 sec)
4.2 创建表
- create table ‘table_name‘ (‘field‘, …) [charset=字符集] [engine=表类型]
- field：field_name, field_type, [字段属性…]
MariaDB [m]> create table tb1 (id int, num int) charset utf8 engine InnoDB; MariaDB [m]> show tables; ------------- | Tables_in_m | ------------- | tb1 | -------------
4.3 显示创建表语句
MariaDB [m]> show create table tb1; CREATE TABLE `tb1` ( `id` int(11) DEFAULT NULL, `num` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8
4.4 查看表结构
字段名称、字段类型、是否为空、索引、默认值、附加
MariaDB [m]> desc tb1; ------- --------- ------ ----- --------- ------- | Field | Type | Null | Key | Default | Extra | ------- --------- ------ ----- --------- ------- | id | int(11) | YES | | NULL | | | num | int(11) | YES | | NULL | | ------- --------- ------ ----- --------- -------
4.5 重命名表
MariaDB [m]> alter table tb1 rename t; MariaDB [m]> show tables; ------------- | Tables_in_m | ------------- | t | -------------
4.6 删除表
MariaDB [m]> drop table t; MariaDB [m]> show tables; Empty set (0.001 sec)
4.7 修改表
再次创建表 tb1
4.7.1 增加字段
MariaDB [m]> alter table tb1 add book int; MariaDB [m]> desc tb1; ------- --------- ------ ----- --------- ------- | Field | Type | Null | Key | Default | Extra | ------- --------- ------ ----- --------- ------- | id | int(11) | YES | | NULL | | | num | int(11) | YES | | NULL | | | book | int(11) | YES | | NULL | | ------- --------- ------ ----- --------- ------- MariaDB [m]> alter table tb1 add mouse int default 0; MariaDB [m]> desc tb1; ------- --------- ------ ----- --------- ------- | Field | Type | Null | Key | Default | Extra | ------- --------- ------ ----- --------- ------- | id | int(11) | YES | | NULL | | | num | int(11) | YES | | NULL | | | book | int(11) | YES | | NULL | | | mouse | int(11) | YES | | 0 | | ------- --------- ------ ----- --------- ------- MariaDB [m]> alter table tb1 add card int default 0 after book; ------- --------- ------ ----- --------- ------- | Field | Type | Null | Key | Default | Extra | ------- --------- ------ ----- --------- ------- | id | int(11) | YES | | NULL | | | num | int(11) | YES | | NULL | | | book | int(11) | YES | | NULL | | | card | int(11) | YES | | 0 | | | mouse | int(11) | YES | | 0 | | ------- --------- ------ ----- --------- ------- MariaDB [m]> alter table tb1 add (ta char(8), tb char(8)); MariaDB [m]> desc tb1; ------- --------- ------ ----- --------- ------- | Field | Type | Null | Key | Default | Extra | ------- --------- ------ ----- --------- ------- | id | int(11) | YES | | NULL | | | num | int(11) | YES | | NULL | | | book | int(11) | YES | | NULL | | | card | int(11) | YES | | 0 | | | mouse | int(11) | YES | | 0 | | | ta | char(8) | YES | | NULL | | | tb | char(8) | YES | | NULL | | ------- --------- ------ ----- --------- ------- MariaDB [m]> alter table tb1 add t int first; MariaDB [m]> desc tb1; ------- --------- ------ ----- --------- ------- | Field | Type | Null | Key | Default | Extra | ------- --------- ------ ----- --------- ------- | t | int(11) | YES | | NULL | | | id | int(11) | YES | | NULL | | | num | int(11) | YES | | NULL | | | book | int(11) | YES | | NULL | | | card | int(11) | YES | | 0 | | | mouse | int(11) | YES | | 0 | | | ta | char(8) | YES | | NULL | | | tb | char(8) | YES | | NULL | | ------- --------- ------ ----- --------- -------
4.7.2 删除字段
MariaDB [m]> alter table tb1 drop t; MariaDB [m]> desc tb1; ------- --------- ------ ----- --------- ------- | Field | Type | Null | Key | Default | Extra | ------- --------- ------ ----- --------- ------- | id | int(11) | YES | | NULL | | | num | int(11) | YES | | NULL | | | book | int(11) | YES | | NULL | | | card | int(11) | YES | | 0 | | | mouse | int(11) | YES | | 0 | | | ta | char(8) | YES | | NULL | | | tb | char(8) | YES | | NULL | | ------- --------- ------ ----- --------- ------- MariaDB [m]> alter table tb1 drop ta; MariaDB [m]> alter table tb1 drop tb; MariaDB [m]> desc tb1; ------- --------- ------ ----- --------- ------- | Field | Type | Null | Key | Default | Extra | ------- --------- ------ ----- --------- ------- | id | int(11) | YES | | NULL | | | num | int(11) | YES | | NULL | | | book | int(11) | YES | | NULL | | | card | int(11) | YES | | 0 | | | mouse | int(11) | YES | | 0 | | ------- --------- ------ ----- --------- -------
4.7.3 修改字段属性
MariaDB [m]> alter table tb1 modify id int default 1; MariaDB [m]> desc tb1; ------- --------- ------ ----- --------- ------- | Field | Type | Null | Key | Default | Extra | ------- --------- ------ ----- --------- ------- | id | int(11) | YES | | 1 | | | num | int(11) | YES | | NULL | | | book | int(11) | YES | | NULL | | | card | int(11) | YES | | 0 | | | mouse | int(11) | YES | | 0 | | ------- --------- ------ ----- --------- ------- MariaDB [m]> alter table tb1 modify book char(64) default ""; MariaDB [m]> desc tb1; ------- ---------- ------ ----- --------- ------- | Field | Type | Null | Key | Default | Extra | ------- ---------- ------ ----- --------- ------- | id | int(11) | YES | | 1 | | | num | int(11) | YES | | NULL | | | book | char(64) | YES | | | | | card | int(11) | YES | | 0 | | | mouse | int(11) | YES | | 0 | | ------- ---------- ------ ----- --------- ------- MariaDB [m]> alter table tb1 modify card varchar(128); MariaDB [m]> desc tb1; ------- -------------- ------ ----- --------- ------- | Field | Type | Null | Key | Default | Extra | ------- -------------- ------ ----- --------- ------- | id | int(11) | YES | | 1 | | | num | int(11) | YES | | NULL | | | book | char(64) | YES | | | | | card | varchar(128) | YES | | NULL | | | mouse | int(11) | YES | | 0 | | ------- -------------- ------ ----- --------- -------
4.7.4 替换字段
MariaDB [m]> alter table tb1 change mouse phone char(11) default ‘‘; MariaDB [m]> desc tb1; ------- -------------- ------ ----- --------- ------- | Field | Type | Null | Key | Default | Extra | ------- -------------- ------ ----- --------- ------- | id | int(11) | YES | | 1 | | | num | int(11) | YES | | NULL | | | book | char(64) | YES | | | | | card | varchar(128) | YES | | NULL | | | phone | char(11) | YES | | | | ------- -------------- ------ ----- --------- -------
5 数据操作
MariaDB [m]> desc tb1; ------- -------------- ------ ----- --------- ------- | Field | Type | Null | Key | Default | Extra | ------- -------------- ------ ----- --------- ------- | id | int(11) | YES | | 1 | | | num | int(11) | YES | | NULL | | | book | char(64) | YES | | | | | card | varchar(128) | YES | | NULL | | | phone | char(11) | YES | | | | ------- -------------- ------ ----- --------- -------
5.1 查看表数据
5.1.1 简单查询数据操作
-- 显示全部数据 MariaDB [m]> select * from tb1; MariaDB [m]> select * from tb1; ------ ------ ------ ------ ------- | id | num | book | card | phone | ------ ------ ------ ------ ------- | 1 | 10 | c | page | 110 | ------ ------ ------ ------ ------- -- 显示指定字段的数据 MariaDB [m]> select id from tb1; ------ | id | ------ | 1 | ------ MariaDB [m]> select id, book, phone from tb1; ------ ------ ------- | id | book | phone | ------ ------ ------- | 1 | c | 110 | ------ ------ -------
5.2 条件查询
MariaDB [m]> select * from tb1; ------ ------ ------ ---------- ------- | id | num | book | card | phone | ------ ------ ------ ---------- ------- | 1 | 10 | c | page | 110 | | 2 | 20 | c | electric | 120 | | 3 | 30 | php | meth | 119 | ------ ------ ------ ---------- ------- MariaDB [m]> select id, card, phone from tb1 where phone = 120; ------ ---------- ------- | id | card | phone | ------ ---------- ------- | 2 | electric | 120 | ------ ---------- -------
5.3 向表插入数据
MariaDB [m]> insert into tb1 values(1, 10, ‘c ‘, ‘page‘, ‘110‘); MariaDB [m]> select * from tb1; ------ ------ ------ ------ ------- | id | num | book | card | phone | ------ ------ ------ ------ ------- | 1 | 10 | c | page | 110 | ------ ------ ------ ------ -------
5.4 修改表中的数据
MariaDB [m]> select * from table; MariaDB [m]> select * from tb1; ------ ------ ------ ---------- ------- | id | num | book | card | phone | ------ ------ ------ ---------- ------- | 1 | 10 | c | page | 110 | | 2 | 20 | c | electric | 120 | | 3 | NULL | php | NULL | | ------ ------ ------ ---------- ------- MariaDB [m]> update tb1 set num = 30, phone=‘119‘, card=‘meth‘ where book = ‘php‘ and id = 3; MariaDB [m]> select * from tb1; ------ ------ ------ ---------- ------- | id | num | book | card | phone | ------ ------ ------ ---------- ------- | 1 | 10 | c | page | 110 | | 2 | 20 | c | electric | 120 | | 3 | 30 | php | meth | 119 | ------ ------ ------ ---------- -------
5.5 删除表中的数据
MariaDB [m]> insert into tb1 (id) values (10); MariaDB [m]> insert into tb1 (id) values (10); MariaDB [m]> insert into tb1 (id) values (10); MariaDB [m]> select * from tb1; ------ ------ ------ ---------- ------- | id | num | book | card | phone | ------ ------ ------ ---------- ------- | 1 | 10 | c | page | 110 | | 2 | 20 | c | electric | 120 | | 3 | 30 | php | meth | 119 | | 10 | NULL | | NULL | | | 10 | NULL | | NULL | | | 10 | NULL | | NULL | | ------ ------ ------ ---------- ------- MariaDB [m]> select * from tb1; MariaDB [m]> select * from tb1; ------ ------ ------ ---------- ------- | id | num | book | card | phone | ------ ------ ------ ---------- ------- | 1 | 10 | c | page | 110 | | 2 | 20 | c | electric | 120 | | 3 | 30 | php | meth | 119 | | 10 | 1 | | NULL | | | 10 | 1 | | NULL | | | 10 | 1 | | NULL | | ------ ------ ------ ---------- ------- MariaDB [m]> delete from tb1 where id = 10 and num = 1; MariaDB [m]> select * from tb1; ------ ------ ------ ---------- ------- | id | num | book | card | phone | ------ ------ ------ ---------- ------- | 1 | 10 | c | page | 110 | | 2 | 20 | c | electric | 120 | | 3 | 30 | php | meth | 119 | ------ ------ ------ ---------- -------
Created: 2019-12-28 周六 22:15