mysql example_（二）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