MSQL拾遗

冷不防 2021-07-24 21:45 397阅读 0赞

**目录**

一 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中的作用 #

\` 是 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 : 自增列依然从上一个自增数据基础上开始 (存在文件中,不会丢失)。