MSQL拾遗

目录

一 MySQl语句的规范

二 IF NOT EXISTS以及show warnings使用

三 查看数据库的编码

四 创建数据库标准语法

五 修改数据库标准语法

六 删除数据库标准语法

七 查看MySQL版本

八 查看表的信息

九 `这个符号在mysql中的作用

十 DDL、DML、DQL、DCL分别代表什么

十一 阿里巴巴表字段设计规范

十二 建表语句注意点

十三 查看创建数据库的语句

十四 查看创建数据表的语句

十五 修改数据库

十六 模式通配

十七 注意点

十八 delete 和 truncate的相同点和不同点

---

一 MySQl语句的规范

虽然 MySQL 的关键字不区分大小写，但我们建议关键字和函数名称全部大写。

数据库名称、表名称、字段名称全部小写。

SQL语句必须以分号结尾。

如果表名或字段名是一个特殊的字符，就需要带 `。

二 IF NOT EXISTS以及show warnings使用

mysql> create database if not exists test;
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> show warnings;
+-------+------+-----------------------------------------------+
| Level | Code | Message                                       |
+-------+------+-----------------------------------------------+
| Note  | 1007 | Can't create database 'test'; database exists |
+-------+------+-----------------------------------------------+
1 row in set (0.00 sec)

三 查看数据库的编码

mysql> show create database test;
+----------+---------------------------------------------------------------+
| Database | Create Database                                               |
+----------+---------------------------------------------------------------+
| test     | CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+---------------------------------------------------------------+
1 row in set (0.00 sec)

四 创建数据库标准语法

CREATE {DATABASE|SCHEMA} [IF NOT EXISTS] db_name [DEFAULT] CHARACTER SET [=] charset_name

mysql> CREATE DATABASE IF NOT EXISTS t2  CHARACTER SET gbk;
Query OK, 1 row affected (0.00 sec)


mysql> show create database t2;
+----------+------------------------------------------------------------+
| Database | Create Database                                            |
+----------+------------------------------------------------------------+
| t2       | CREATE DATABASE `t2` /*!40100 DEFAULT CHARACTER SET gbk */ |
+----------+------------------------------------------------------------+
1 row in set (0.00 sec)

五 修改数据库标准语法

ALTER {DATABASE|SCHEMA} [db_name] [DEFAULT] CHARACTER SET [=] charset_name

mysql> alter database t2 character set utf8;
Query OK, 1 row affected (0.00 sec)

mysql> show create database t2;
+----------+-------------------------------------------------------------+
| Database | Create Database                                             |
+----------+-------------------------------------------------------------+
| t2       | CREATE DATABASE `t2` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+-------------------------------------------------------------+
1 row in set (0.00 sec)

六 删除数据库标准语法

DROP {DATABASE|SCHEMA} [IF EXISTS] db_name;

mysql> drop database t2;
Query OK, 0 rows affected (0.07 sec)

七 查看MySQL版本

SELECT VERSION()
5.7.30-log

八 查看表的信息

mysql> DESCRIBE tb_book;
+-----------------+--------------+------+-----+---------+----------------+
| Field           | Type         | Null | Key | Default | Extra          |
+-----------------+--------------+------+-----+---------+----------------+
| id              | int(11)      | NO   | PRI | NULL    | auto_increment |
| name            | varchar(54)  | YES  |     | NULL    |                |
| author          | varchar(54)  | YES  |     | NULL    |                |
| publicationdate | date         | YES  |     | NULL    |                |
| publication     | varchar(150) | YES  |     | NULL    |                |
| price           | double       | YES  |     | NULL    |                |
| image           | varchar(54)  | YES  |     | NULL    |                |
| remark          | varchar(600) | YES  |     | NULL    |                |
+-----------------+--------------+------+-----+---------+----------------+
8 rows in set (0.00 sec)   

九 `这个符号在mysql中的作用

` 是 MySQL 的转义符，避免和 mysql 的本身的关键字冲突，只要你不在列名、表名中使用 mysql 的保留字或中文，就不需要转义。

所有的数据库都有类似的设置，不过mysql用的是`而已。通常用来说明其中的内容是数据库名、表名、字段名，不是关键字。例如：

select from from table;

第一个from是字段名，最后的table表名，但是同时也是mysql关键字，这样执行的时候就会报错，所以应该使用

select `from` from `table`;

当然，为了便于阅读，不建议使用关键字作为字段名、表名，同时，应该对数据库名、表名、字段名用一对反引号包含。

CREATE TABLE `jy` (
  `借书证号` char(8) NOT NULL COMMENT '借书证号',
  `ISBN` char(16) NOT NULL COMMENT 'ISBN',
  `索书号` char(10) NOT NULL COMMENT '索书号',
  `借书时间` datetime NOT NULL COMMENT '借书时间',
  PRIMARY KEY (`借书证号`,`ISBN`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

十 DDL、DML、DQL、DCL分别代表什么

数据库    ***   语言
DDL      定义
DML      操作
DQL      查询
DCL      控制

十一 阿里巴巴表字段设计规范

每个表都必须存在以下五个字段。

• id:主键
• version:乐观锁
• is_delete:伪删除
• gmt_create:创建时间
• gmt_update:修改时间

十二 建表语句注意点

1 使用英文小括号

2 表的名称和字段尽量使用反引号 ` 括起来

3 AUTO_INCREMENT表示自增

4 字符串使用单引号括起来

5 所有的语句后面加英文的逗号，最后一个不用加

6 PRIMARY KEY 主键，一个表只有一个唯一的主键

-- 目标 : 创建一个school数据库
-- 创建学生表(列,字段)
-- 学号int 登录密码varchar(20) 姓名,性别varchar(2),出生日期(datatime),家庭住址,email
-- 创建表之前 , 一定要先选择数据库
CREATE TABLE IF NOT EXISTS `student` (
`id` int(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
`name` varchar(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
`pwd` varchar(20) NOT NULL DEFAULT '123456' COMMENT '密码',
`sex` varchar(2) NOT NULL DEFAULT '男' COMMENT '性别',
`birthday` datetime DEFAULT NULL COMMENT '生日',
`address` varchar(100) DEFAULT NULL COMMENT '地址',
`email` varchar(50) DEFAULT NULL COMMENT '邮箱',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

十三 查看创建数据库的语句

SHOW CREATE DATABASE `demo`;
demo    CREATE DATABASE `demo` /*!40100 DEFAULT CHARACTER SET utf8 */

十四 查看创建数据表的语句

SHOW CREATE TABLE `tb_area`;
tb_area    CREATE TABLE `tb_area` (
  `area_id` int(2) NOT NULL AUTO_INCREMENT,
  `area_name` varchar(200) NOT NULL,
  `priority` int(2) NOT NULL DEFAULT '0',
  `create_time` datetime DEFAULT NULL,
  `last_edit_time` datetime DEFAULT NULL,
  PRIMARY KEY (`area_id`),
  UNIQUE KEY `UK_AREA` (`area_name`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8

十五 修改数据库

1 修改表名

ALTER TABLE 旧表名 RENAME AS 新表名

2 添加字段

ALTER TABLE 表名 ADD字段名 列属性[属性]

3 修改字段

ALTER TABLE 表名 MODIFY 字段名 列类型[属性]
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 列属性[属性]

4 删除字段

ALTER TABLE 表名 DROP 字段名

十六 模式通配

•  _：任意单个字符
•  %：任意多个字符，甚至包括零字符
•  单引号：需要进行转义 \'

十七 注意点

1 CMD命令行内的语句结束符可以为 ";"

2 "\G", "\g"，仅影响显示结果。其他地方还是用分号结束。

3 delimiter 可修改当前对话的语句结束符。

十八 delete 和 truncate的相同点和不同点

1 TRUNCATE命令作用

用于完全清空表数据,但表结构、索引、约束等不变。

2 TRUNCATE命令语法

TRUNCATE [TABLE] table_name;
-- 清空年级表
TRUNCATE grade

3 TRUNCATE命令和delete命令相同点

都能删除数据,不删除表结构,但TRUNCATE速度更快。

4 TRUNCATE命令和delete命令不同点

• 使用TRUNCATE TABLE重新设置AUTO_INCREMENT计数器。
• 使用TRUNCATE TABLE不会对事务有影响。

5 测试

-- 创建一个测试表
CREATE TABLE `test` (
`id` INT(4) NOT NULL AUTO_INCREMENT,
`coll` VARCHAR(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8
-- 插入几个测试数据
INSERT INTO test(coll) VALUES('row1'),('row2'),('row3');
-- 删除表数据(不带where条件的delete)
DELETE FROM test;
-- 结论:如不指定Where则删除该表的所有列数据,自增当前值依然从原来基础上进行,会记录日志。
-- 删除表数据(truncate)
TRUNCATE TABLE test;
-- 结论:truncate删除数据,自增当前值会恢复到初始值重新开始;不会记录日志。

6 delete注意点

同样使用DELETE清空不同引擎的数据库表数据.重启数据库服务后。

• InnoDB : 自增列从初始值重新开始 (因为是存储在内存中,断电即失)。
• MyISAM : 自增列依然从上一个自增数据基础上开始 (存在文件中,不会丢失)。