（二）MySQL基本操作

1、mysql登录：

参数	备注
-D, --database=name	打开指定数据库
--delimiter = name	指定分隔符
-h, --host = name	服务器名称
-p, --password[=name]	密码
-P --port=#	端口
--prompt=name	设置提示符
-u, --user=name	用户名
-V, --version	输出版本信息并且退出

example：

mysql -u root -h 192.168.0.109 -P 3306 -p

2、修改提示符：

登录后，默认提示符为 mysql>

修改提示符：

mysql> prompt \u@\h \d>
PROMPT set to '\u@\h \d>'
root@192.168.0.109 (none)>

• \D：完整的日期
• \d：当前数据库
• \h：当前主体host
• \u：当前用户

3、常用命令和语法规范

-- 显示当前服务器版本
SELECT VERSION();
-- 显示当前日期时间
SELECT NOW();
-- 当前用户
SELECT USER();
-- 查看数据库
SHOW DATABASES;
-- 查看警告信息
root@192.168.0.109 (none)>SHOW WARNINGS;
+-------+------+----------------------------------------------+
| Level | Code | Message                                      |
+-------+------+----------------------------------------------+
| Error | 1007 | Can't create database 'sys'; database exists |
+-------+------+----------------------------------------------+

4、数据库操作：

• 数据库创建

-- 语法
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name [DEFAULT] CHARACTER SET [=] charset_name;
-- 示例
CREATE DATABASE IF NOT EXISTS example_db DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_general_ci;

• 数据库删除

-- 语法
DROP {DATABASE | SCHEMA} [IF EXISTS] db_name;
-- 示例
DROP DATABASE IF EXISTS t1;

• 修改数据库编码

ALTER DATABASE t1 CHARACTER SET gbk;

• 查看建库语句

-- 查看建库语句
root@192.168.0.109 (none)>SHOW CREATE DATABASE t1;
+----------+-------------------------------------------------------------+
| Database | Create Database                                             |
+----------+-------------------------------------------------------------+
| t1       | CREATE DATABASE `t1` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+-------------------------------------------------------------+

表：

• 建表：

-- 语法
CREATE TABLE [IF NOT EXISTS] table_name (
	column_name data_type[size] [DEFAULT] COMMENT '备注',
    ...
) COMMENT = '表注释' ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 示例
CREATE TABLE `example`.`Untitled`  (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(20) NOT NULL COMMENT '名称',
  `age` TINYINT(4) UNSIGNED NOT NULL DEFAULT 18 COMMENT '年龄',
  PRIMARY KEY (`id`)
) ENGINE = InnoDB COMMENT = '学生信息';

• 查看数据表列表：

SHOW TABLES [FROM db_name] [LIKE 'pattern' | WHERE expr];

• 查看表结构：

SHOW COLUMNS FROM student;
DESC table_name;

root@192.168.0.109 example_db>SHOW COLUMNS FROM student;
+-------+---------------------+------+-----+---------+----------------+
| Field | Type                | Null | Key | Default | Extra          |
+-------+---------------------+------+-----+---------+----------------+
| id    | INT(11)             | NO   | PRI | NULL    | auto_increment |
| name  | VARCHAR(200)        | YES  |     | NULL    |                |
| age   | tinyINT(3) unsigned | YES  |     | NULL    |                |
+-------+---------------------+------+-----+---------+----------------+
root@192.168.0.109 example_db>DESC dept;
+--------+-------------+------+-----+---------+----------------+
| Field  | Type        | Null | Key | Default | Extra          |
+--------+-------------+------+-----+---------+----------------+
| deptno | int(11)     | NO   | PRI | NULL    | auto_increment |
| dname  | varchar(50) | YES  |     | NULL    |                |
| loc    | varchar(20) | YES  |     | NULL    |                |
+--------+-------------+------+-----+---------+----------------+

• 查看建表语句

root@192.168.0.109 example_db>SHOW CREATE TABLE emp;
| Table | Create Table|
| emp   | CREATE TABLE `emp` (
  `empno` int(11) NOT NULL AUTO_INCREMENT,
  `ename` varchar(20) DEFAULT NULL COMMENT '员工姓名',
  `job` varchar(20) DEFAULT NULL COMMENT '岗位',
  `mgr` int(11) DEFAULT NULL COMMENT '直接领??编号',
  `hiredate` date DEFAULT NULL COMMENT '雇佣日期，入职日?',
  `sal` int(11) DEFAULT NULL COMMENT '????',
  `comm` int(11) DEFAULT NULL COMMENT '提成',
  `deptno` int(11) NOT NULL COMMENT '部门编号',
  PRIMARY KEY (`empno`),
  KEY `deptno` (`deptno`),
  CONSTRAINT `emp_ibfk_1` FOREIGN KEY (`deptno`) REFERENCES `dept` (`deptno`)
) ENGINE=InnoDB AUTO_INCREMENT=7935 DEFAULT CHARSET=utf8mb4 |

• 修改表

-- 修改表名称
ALTER TABLE emp RENAME new_name;
-- 修改字段类型
ALTER TABLE emp MODIFY ename VARCHAR(30);
-- 修改字段名称
ALTER TABLE emp CHANGE empno id BIGINT(20);
-- 增加字段:ALTER TABLE 表名 ADD 属性名1 数据类型 [完整性约束条件] [FIRST | AFTER 属性名2];
ALTER TABLE emp ADD emp_desc VARCHAR(20) NOT NULL AFTER id;
-- 删除字段
ALTER TABLE emp DROP emp_desc;
-- 更改表的存储引擎:ALTER TABLE 表名 ENGINE = 存储引擎名;
ALTER TABLE emp ENGINE = MYISAM;

• 删除表

DROP TABLE emp;

• 格式化表

TRUNCATE TABLE emp;

5、数据增删改

• 插入：

INSERT [INTO] tbl_name [(col_name,...)] VALUES(val,...);
-- 批量插入
INSERT [INTO] tbl_name [(col_name,...)] VALUES(val,...), VALUES(val,...), VALUES(val,...);

• 删除：

DELETE FROM tbl_name [WHERE expr];

• 更新：

UPDATE tbl_name SET column = value,... [WHERE expr];
-- 批量更新
UPDATE tbl_name 
    SET filed = CASE id 
        WHEN 1 THEN 'value'
        WHEN 2 THEN 'value'
        WHEN 3 THEN 'value'
    END
WHERE id IN (1,2,3);
-- 批量更新
UPDATE categories 
    SET display_order = CASE id 
        WHEN 1 THEN 3 
        WHEN 2 THEN 4 
        WHEN 3 THEN 5 
    END, 
    title = CASE id 
        WHEN 1 THEN 'New Title 1'
        WHEN 2 THEN 'New Title 2'
        WHEN 3 THEN 'New Title 3'
    END
WHERE id IN (1,2,3)

6、约束：

约束条件	说明
PRIMARY KEY	标识该属性为该表的主键，可以唯一的标识对应的元组
FOREIGN KEY	标识该属性为该表的外键，是与之联系某表的主键
NOT NULL	标识该属性不能为空
UNIQUE	标识该属性的值是唯一的
AUTO_INCREMENT	标识该属性的值是自动增加，这是MySQL的SQL语句的特色
DEFAULT	为该属性设置默认值

7、存储引擎

SHOW ENGINES;

• Support：MySQL是否支持该引擎
• Comment：评价说明
• Transaction：是否支持事务
• XA：分布式事务二阶段提交（强一致性）
• Savepoints：参数表示是否支持保存点，以方便事务的回滚操作

Engine	Support	Comment	Transactions	XA	Savepoints
InnoDB	DEFAULT	Supports transactions, row-level locking, and foreign keys	YES	YES	YES
MRG_MYISAM	YES	Collection of identical MyISAM tables	NO	NO	NO
MEMORY	YES	Hash based, stored in memory, useful for temporary tables	NO	NO	NO
BLACKHOLE	YES	/dev/null storage engine (anything you write to it disappears)	NO	NO	NO
MyISAM	YES	MyISAM storage engine	NO	NO	NO
CSV	YES	CSV storage engine	NO	NO	NO
ARCHIVE	YES	Archive storage engine	NO	NO	NO
PERFORMANCE_SCHEMA	YES	Performance Schema	NO	NO	NO
FEDERATED	NO	Federated MySQL storage engine	NULL	NULL	NULL