DDL常用操作及常见问题

Leave a reply

DDL（data defination language）数据定义语言

1、字符编码问题

  • 查看当前编码方式，show variables like ‘%char%’;
  • 设置当前操作及显示编码方式 set names utf8;
  • 修改xxx表的默认字符集：alter table xxx default character set utf8;
  • 修改数据库xxx默认字符集：alter database xxx default character set utf8;
  • 修改表创建字段默认字符集：alter table xxx default character set utf8 collate utf8_general_ci;
  • 修改表所有字段默认字符集及当前字符集：alter table xxx convert to character set utf8 collate utf8_general_ci;
  • 修改某字段的字符集：alter table xxx change xxx chracter set utf8 not null …..;

2、什么时候需要加’table’这个关键字？

  • DDL相关的表操作都需要talbe关键字，简单一点理解，增、删、改、查这些对表的操作语句不需要talbe关键字；

3、drop、delete、truncate的区别

  • 整个表删除使用drop table xxx；
  • 删除表中所有数据使用truncate xxx，效果相当于delete from xxx，不同的是truncate会复位自增字段的值;
  • 删除表中部分数据使用delete from xxx where；

4、修改数据库用户密码的三种方式

  • 在mysql数据库的user表中修改对应数据 
    update user set authentication_string = password(‘******’) where user = ‘root'
  • 使用mysqladmin修改 
    mysqladmin -uroot -pxxxx password ‘xxxxxx'
  • 使用sudo mysqld —skip-grant-tables命令
    • 绕过密码的校验，然后再通过第一种方式修改密码

5、新增一个用户

create user xxx@localhost identified by ‘xxxxxx'

6、删除一个用户

drop user xxxx@localhost

7、创建一个表，指定字符编码、引擎

create table xxx(xx varchar(4)) engine=InnoDB charset=utf8 collate=utf8_general_ci

8、查看当前数据库字符集；

  • show chracter set 
    mysql> show character set;
+----------+---------------------------------+---------------------+--------+
| Charset  | Description                     | Default collation   | Maxlen |
+----------+---------------------------------+---------------------+--------+
| big5     | Big5 Traditional Chinese        | big5_chinese_ci     |      2 |
| dec8     | DEC West European               | dec8_swedish_ci     |      1 |
| cp850    | DOS West European               | cp850_general_ci    |      1 |
| hp8      | HP West European                | hp8_english_ci      |      1 |
| koi8r    | KOI8-R Relcom Russian           | koi8r_general_ci    |      1 |
| latin1   | cp1252 West European            | latin1_swedish_ci   |      1 |
| latin2   | ISO 8859-2 Central European     | latin2_general_ci   |      1 |
| swe7     | 7bit Swedish                    | swe7_swedish_ci     |      1 |
| ascii    | US ASCII                        | ascii_general_ci    |      1 |
| ujis     | EUC-JP Japanese                 | ujis_japanese_ci    |      3 |
| sjis     | Shift-JIS Japanese              | sjis_japanese_ci    |      2 |
| hebrew   | ISO 8859-8 Hebrew               | hebrew_general_ci   |      1 |
| tis620   | TIS620 Thai                     | tis620_thai_ci      |      1 |
| euckr    | EUC-KR Korean                   | euckr_korean_ci     |      2 |
| koi8u    | KOI8-U Ukrainian                | koi8u_general_ci    |      1 |
| gb2312   | GB2312 Simplified Chinese       | gb2312_chinese_ci   |      2 |
| greek    | ISO 8859-7 Greek                | greek_general_ci    |      1 |
| cp1250   | Windows Central European        | cp1250_general_ci   |      1 |
| gbk      | GBK Simplified Chinese          | gbk_chinese_ci      |      2 |
| latin5   | ISO 8859-9 Turkish              | latin5_turkish_ci   |      1 |
| armscii8 | ARMSCII-8 Armenian              | armscii8_general_ci |      1 |
| utf8     | UTF-8 Unicode                   | utf8_general_ci     |      3 |
| ucs2     | UCS-2 Unicode                   | ucs2_general_ci     |      2 |
| cp866    | DOS Russian                     | cp866_general_ci    |      1 |
| keybcs2  | DOS Kamenicky Czech-Slovak      | keybcs2_general_ci  |      1 |
| macce    | Mac Central European            | macce_general_ci    |      1 |
| macroman | Mac West European               | macroman_general_ci |      1 |
| cp852    | DOS Central European            | cp852_general_ci    |      1 |
| latin7   | ISO 8859-13 Baltic              | latin7_general_ci   |      1 |
| utf8mb4  | UTF-8 Unicode                   | utf8mb4_general_ci  |      4 |
| cp1251   | Windows Cyrillic                | cp1251_general_ci   |      1 |
| utf16    | UTF-16 Unicode                  | utf16_general_ci    |      4 |
| utf16le  | UTF-16LE Unicode                | utf16le_general_ci  |      4 |
| cp1256   | Windows Arabic                  | cp1256_general_ci   |      1 |
| cp1257   | Windows Baltic                  | cp1257_general_ci   |      1 |
| utf32    | UTF-32 Unicode                  | utf32_general_ci    |      4 |
| binary   | Binary pseudo charset           | binary              |      1 |
| geostd8  | GEOSTD8 Georgian                | geostd8_general_ci  |      1 |
| cp932    | SJIS for Windows Japanese       | cp932_japanese_ci   |      2 |
| eucjpms  | UJIS for Windows Japanese       | eucjpms_japanese_ci |      3 |
| gb18030  | China National Standard GB18030 | gb18030_chinese_ci  |      4 |
+----------+---------------------------------+---------------------+--------+
41 rows in set (0.00 sec)

9、查看当前数据库支持的引擎

  • show engines; 
    mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| 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       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

10、修改数据库字符集

  • 查看数据库字符集 show create database xxxx 
    mysql> show create database sian;
+----------+---------------------------------------------------------------+
| Database | Create Database                                               |
+----------+---------------------------------------------------------------+
| sian     | CREATE DATABASE `sian` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+---------------------------------------------------------------+
1 row in set (0.00 sec)
  • 修改数据库字符集 
    mysql> alter database sian charset = utf8;
Query OK, 1 row affected (0.00 sec)

11、修改数据库表引擎

  • 查看数据库表引擎 
    mysql> show create table user;
CREATE TABLE `user` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`user_name` varchar(20) NOT NULL COMMENT '用户名',
`sex` enum('男','女') NOT NULL DEFAULT '男',
`password` char(32) NOT NULL COMMENT '密码',
`email` varchar(50) NOT NULL COMMENT '用户邮箱',
`address` varchar(50) DEFAULT NULL,
`fee` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT '用户余额',
`age` tinyint(3) unsigned NOT NULL COMMENT '年龄',
`create_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '创建时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8
  • 修改数据库表引擎 
    mysql> alter table user engine = innodb;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

12、修改表字符集

  • alter table user charset = utf8 
    mysql> alter table user charset = utf8;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

13、查看数据库表状态；

  • show tables status; 
    mysql> show table status \G
*************************** 1. row ***************************
         Name: mark
       Engine: InnoDB
      Version: 10
   Row_format: Dynamic
         Rows: 6
Avg_row_length: 2730
  Data_length: 16384
Max_data_length: 0
 Index_length: 0
    Data_free: 0
Auto_increment: 8
  Create_time: 2019-04-22 20:22:38
  Update_time: 2019-04-22 20:22:38
   Check_time: NULL
    Collation: utf8_general_ci
     Checksum: NULL
Create_options:
      Comment:
*************************** 2. row ***************************
         Name: user
       Engine: InnoDB
      Version: 10
   Row_format: Dynamic
         Rows: 6
Avg_row_length: 2730
  Data_length: 16384
Max_data_length: 0
 Index_length: 0
    Data_free: 0
Auto_increment: 7
  Create_time: 2019-04-22 21:12:39
  Update_time: NULL
   Check_time: NULL
    Collation: utf8_general_ci
     Checksum: NULL
Create_options:
      Comment:
*************************** 3. row ***************************
         Name: users
       Engine: InnoDB
      Version: 10
   Row_format: Dynamic
         Rows: 0
Avg_row_length: 0
  Data_length: 16384
Max_data_length: 0
 Index_length: 0
    Data_free: 0
Auto_increment: 1
  Create_time: 2019-04-20 11:29:16
  Update_time: NULL
   Check_time: NULL
    Collation: latin1_swedish_ci
     Checksum: NULL
Create_options:
      Comment:
3 rows in set (0.00 sec)

14、数据类型int(n)中的n代表含义

  • int类型可存储最大数值由类型本身决定(4个字节)，n只在zerofill时有效，小于n长度数据前面被0 
    mysql> desc t_int;
+-------+---------------------------+------+-----+---------+-------+
| Field | Type                      | Null | Key | Default | Extra |
+-------+---------------------------+------+-----+---------+-------+
| num   | int(10) unsigned zerofill | YES  |     | NULL    |       |
| num_u | int(10) unsigned          | YES  |     | NULL    |       |
+-------+---------------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> select * from t_int;
+------------+-------+
| num        | num_u |
+------------+-------+
| 0000000123 |   456 |
+------------+-------+
1 row in set (0.00 sec)

15、数据类型char(n)中的n含义

  • char类型中n即表示存储的最大字符数，字符不等于字节
  • select length(字段名)可查询字节长度，在utf8中一个中文字符占3个字节(gbk中为2个字节)
  • select char_length(字段名)可查询字符长度，与n一致；
  • 如果是varchar，则length(varc)则是实际数据的字符数；
mysql> insert into t_char values('123');
ERROR 1136 (21S01): Column count doesn't match value count at row 1
mysql> insert into t_char(c) values('123');
Query OK, 1 row affected (0.01 sec)


mysql> select * from t_char;
+----+-----+
| id | c   |
+----+-----+
|  1 | 123 |
+----+-----+
1 row in set (0.00 sec)


mysql> insert into t_char(c) vlaues('12345');
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'vlaues('12345')' at line 1

mysql> insert into t_char(c) values('余西安');
Query OK, 1 row affected (0.01 sec)


mysql> select * from t_char;
+----+-----------+
| id | c         |
+----+-----------+
|  1 | 123       |
|  2 | 余西安    |
+----+-----------+
2 rows in set (0.00 sec)


mysql> select length(c) from t_char;
+-----------+
| length(c) |
+-----------+
|         3 |
|         9 |
+-----------+
2 rows in set (0.01 sec)


mysql> select char_length(c) from t_char;
+----------------+
| char_length(c) |
+----------------+
|              3 |
|              3 |
+----------------+
2 rows in set (0.00 sec)

Leave a Reply