mysql 之 sql管理数据 一

2019独角兽企业重金招聘Python工程师标准>>>

1、sql_mode

mysql数据库的中有一个环境变量sql_mode,定义了mysql应该支持的sql语法，数据校验等！

> 查看当前数据库使用的 sql_mode

mysql> select @@sql_mode;
+----------------------------------------------------------------+
| @@sql_mode                                                     |
+----------------------------------------------------------------+
| STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+----------------------------------------------------------------+

  > 通过 查看系统环境变量方式：

mysql> show variables like 'sql_mode%';
+---------------+---------------------------------------------------------------
-+
| Variable_name | Value
 |
+---------------+---------------------------------------------------------------
-+
| sql_mode      | STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
 |
+---------------+---------------------------------------------------------------
-+

> 修改 当前会话 sql_mode

mysql> set sql_mode = 'traditional';
Query OK, 0 rows affected (0.00 sec)

> 修改为 全局性设置 - 需要有 super管理权限

mysql> set global sql_mode = 'traditional';
Query OK, 0 rows affected (0.00 sec)

> 查看 当前会话和全局性 sql_mode

select @@session.sql_mode;
select @@global.sql_mode;

2、字符集

     > 查看mysql 服务器的当前字符集 和 排列方式

mysql> show variables like 'character\_set\_%';
+--------------------------+--------+
| Variable_name            | Value  |
+--------------------------+--------+
| character_set_client     | latin1 |
| character_set_connection | latin1 |
| character_set_database   | latin1 |
| character_set_filesystem | binary |
| character_set_results    | latin1 |
| character_set_server     | latin1 |
| character_set_system     | utf8   |
+--------------------------+--------+
7 rows in set (0.00 sec)

mysql> show variables like 'collation\_%';
+----------------------+-------------------+
| Variable_name        | Value             |
+----------------------+-------------------+
| collation_connection | latin1_swedish_ci |
| collation_database   | latin1_swedish_ci |
| collation_server     | latin1_swedish_ci |
+----------------------+-------------------+
3 rows in set (0.00 sec)

> 创建 新的 库和表时，设置字符集和排列方式

mysql> create database 20170620_test character set utf8 collate utf8_general_ci;
Query OK, 1 row affected (0.01 sec)

mysql> create table tt(
    -> id int primary key auto_increment,
    -> name varchar(20)
    -> ) character set utf8 collate utf8_general_ci;
Query OK, 0 rows affected (0.09 sec)

> 修改 某个数据库 和 某张表的 字符集

alter database mini default character set = gb2312; 
alter table pub_logs default character set = gb2312; 

3、数据库的 操作

    > 选中数据库  use db_name; 

    > 创建数据库  create database if not exists db_name;

                         create database if not exists db_name character set utf8 collate utf8_general_ci;

    > 删除数据库  drop database db_name;   -> 会删除 此数据库中所有的 东西，且不可恢复

    > 数据库的变更 

       alter database db_name character set utf8 collate utf8_general_ci;

4、查看 哪些存储引擎可供选用

mysql> show engines;
+--------------------+---------+------------------------------------------------
----------------+--------------+------+------------+
| Engine             | Support | Comment
                | Transactions | XA   | Savepoints |
+--------------------+---------+------------------------------------------------
----------------+--------------+------+------------+
| FEDERATED          | NO      | Federated MySQL storage engine
                | NULL         | NULL | NULL       |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables
                | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine
                | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to
 it disappears) | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine
                | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for tempor
ary tables      | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine
                | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and f
oreign keys     | YES          | YES  | YES        |
| PERFORMANCE_SCHEMA | YES     | Performance Schema
                | NO           | NO   | NO         |
+--------------------+---------+------------------------------------------------
----------------+--------------+------+------------+
9 rows in set (0.00 sec)

>  也 可以 通过 查询  information_schema.engines 来查看  -> mysql 5.1 以上 更高的 版本

mysql> select engine from information_schema.engines where transactions = 'yes';
+--------+
| engine |
+--------+
| InnoDB |
+--------+
1 row in set (0.00 sec)

 

转载于:https://my.oschina.net/u/1387400/blog/993722