第二十三章 MySQL数据库 手册2 SQL语句
实验二：用SQL语句，管理数据库
目的
用SQL语句，管理数据库。
前提
linux系统，已安装好数据库。
安装mariadb
7 ~]# vim /etc/yum.repos.d/mariadb-10.2.repo
# MariaDB 10.2 CentOS repository list - created 2022-05-14 05:44 UTC
# https://mariadb.org/download/
[mariadb]
name = MariaDB
baseurl = https://mirrors.aliyun.com/mariadb/yum/10.2/centos7-amd64
gpgkey=https://mirrors.aliyun.com/mariadb/yum/RPM-GPG-KEY-MariaDB
gpgcheck=1
7 ~]# yum install mariadb-server -y
7 ~]# systemctl start mariadb
#查看3306端口是否开启
7 ~]# ss -tnl
State Recv-Q Send-Q Local Address:Port Peer Address:Port
...
LISTEN 0 80 :::3306 :::*
...
安全加固，root⼝令为空。
设置数据库管理员root口令
禁止root远程登录
删除anonymous用户帐号
删除test数据库
7 ~]# mysql_secure_installation
NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY!
In order to log into MariaDB to secure it, we'll need the current
password for the root user. If you've just installed MariaDB, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.
Enter current password for root (enter for none):
OK, successfully used password, moving on...
Setting the root password ensures that nobody can log into the MariaDB
root user without the proper authorisation.
Set root password? [Y/n] n
... skipping.
By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB without having to have a user account created for
them. This is intended only for testing, and to make the installation
go a bit smoother. You should remove them before moving into a
production environment.
Remove anonymous users? [Y/n] y
... Success!
Normally, root should only be allowed to connect from 'localhost'. This
ensures that someone cannot guess at the root password from the network.
Disallow root login remotely? [Y/n] y
... Success!
By default, MariaDB comes with a database named 'test' that anyone can
access. This is also intended only for testing, and should be removed
before moving into a production environment.
Remove test database and access to it? [Y/n] y
- Dropping test database...
... Success!
- Removing privileges on test database...
... Success!
Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.
Reload privilege tables now? [Y/n] y
... Success!
Cleaning up...
All done! If you've completed all of the above steps, your MariaDB
installation should now be secure.
Thanks for using MariaDB!
空⼝令，登录。
7 ~]# mysql -uroot -p
Enter password: #直接回车
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 15
Server version: 10.2.43-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]>
命令介绍
- SQL语⾔规范
在数据库系统中，SQL语句不区分大小写(建议用大写)
SQL语句可单行或多行书写，以“;”结尾
关键词不能跨多行或简写
用空格和缩进来提高语句的可读性
子句通常位于独立行，便于编辑，提高可读性
【例1】查看当前数据库服务中都有哪些具体的库show。
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)
- 数据库对象
数据库的组件(对象)：
- 数据库、表、索引、视图、用户、存储过程、函数、触发器、事件调度器等
命名规则：
- 必须以字母开头
- 可包括数字和三个特殊字符（# _ $）
- 不要使用MySQL的保留字
- 同一database(Schema)下的对象不能同名
【例2】选择要操作的数据库use。
MariaDB [(none)]> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
【例3】查看当前数据库下的所有表show。
MariaDB [mysql]> show tables;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| column_stats |
| columns_priv |
| db |
| event |
| func |
| general_log |
| gtid_slave_pos |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| host |
| index_stats |
| innodb_index_stats |
| innodb_table_stats |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| roles_mapping |
| servers |
| slow_log |
| table_stats |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
30 rows in set (0.00 sec)
【例4】查看⽤户表desc。
MariaDB [mysql]> desc user;
+------------------------+-----------------------------------+------+-----+----------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+----------+-------+
| Host | char(60) | NO | PRI | | |
| User | char(80) | NO | PRI | | |
| Password | char(41) | NO | | | |
| Select_priv | enum('N','Y') | NO | | N | |
| Insert_priv | enum('N','Y') | NO | | N | |
| Update_priv | enum('N','Y') | NO | | N | |
| Delete_priv | enum('N','Y') | NO | | N | |
| Create_priv | enum('N','Y') | NO | | N | |
| Drop_priv | enum('N','Y') | NO | | N | |
| Reload_priv | enum('N','Y') | NO | | N | |
| Shutdown_priv | enum('N','Y') | NO | | N | |
| Process_priv | enum('N','Y') | NO | | N | |
| File_priv | enum('N','Y') | NO | | N | |
| Grant_priv | enum('N','Y') | NO | | N | |
| References_priv | enum('N','Y') | NO | | N | |
| Index_priv | enum('N','Y') | NO | | N | |
| Alter_priv | enum('N','Y') | NO | | N | |
| Show_db_priv | enum('N','Y') | NO | | N | |
| Super_priv | enum('N','Y') | NO | | N | |
| Create_tmp_table_priv | enum('N','Y') | NO | | N | |
| Lock_tables_priv | enum('N','Y') | NO | | N | |
| Execute_priv | enum('N','Y') | NO | | N | |
| Repl_slave_priv | enum('N','Y') | NO | | N | |
| Repl_client_priv | enum('N','Y') | NO | | N | |
| Create_view_priv | enum('N','Y') | NO | | N | |
| Show_view_priv | enum('N','Y') | NO | | N | |
| Create_routine_priv | enum('N','Y') | NO | | N | |
| Alter_routine_priv | enum('N','Y') | NO | | N | |
| Create_user_priv | enum('N','Y') | NO | | N | |
| Event_priv | enum('N','Y') | NO | | N | |
| Trigger_priv | enum('N','Y') | NO | | N | |
| Create_tablespace_priv | enum('N','Y') | NO | | N | |
| ssl_type | enum('','ANY','X509','SPECIFIED') | NO | | | |
| ssl_cipher | blob | NO | | NULL | |
| x509_issuer | blob | NO | | NULL | |
| x509_subject | blob | NO | | NULL | |
| max_questions | int(11) unsigned | NO | | 0 | |
| max_updates | int(11) unsigned | NO | | 0 | |
| max_connections | int(11) unsigned | NO | | 0 | |
| max_user_connections | int(11) | NO | | 0 | |
| plugin | char(64) | NO | | | |
| authentication_string | text | NO | | NULL | |
| password_expired | enum('N','Y') | NO | | N | |
| is_role | enum('N','Y') | NO | | N | |
| default_role | char(80) | NO | | | |
| max_statement_time | decimal(12,6) | NO | | 0.000000 | |
+------------------------+-----------------------------------+------+-----+----------+-------+
46 rows in set (0.00 sec)
- SQL语句分类和构成：
DDL: Data Defination Language 数据定义语言
CREATE，DROP，ALTER
DML: Data Manipulation Language 数据操纵语言
INSERT，DELETE，UPDATE
DCL：Data Control Language 数据控制语言
GRANT，REVOKE，COMMIT，ROLLBACK
DQL：Data Query Language 数据查询语言
SELECT
Keyword组成clause
多条clause组成语句
- 数据库操作
创建数据库：
CREATE DATABASE|SCHEMA [IF NOT EXISTS] 'DB_NAME';
CHARACTER SET 'character set name’COLLATE 'collate name'
修改数据库：
ALTER DATABASE DB_NAME character set utf8;
删除数据库
DROP DATABASE|SCHEMA [IF EXISTS] 'DB_NAME';
查看支持所有字符集：SHOW CHARACTER SET;
查看支持所有排序规则：SHOW COLLATION;
获取命令使用帮助：
mysql> HELP KEYWORD;
查看数据库列表：
mysql> SHOW DATABASES;
【例5】创建,查帮助。
MariaDB [mysql]> help create;
Many help items for your request exist.
To make a more specific request, please type 'help <item>',
where <item> is one of the following
topics:
CREATE DATABASE
CREATE EVENT
CREATE FUNCTION
CREATE FUNCTION UDF
CREATE INDEX
CREATE PROCEDURE
CREATE SERVER
CREATE TABLE
CREATE TABLESPACE
CREATE TRIGGER
CREATE USER
CREATE VIEW
SHOW
SHOW CREATE DATABASE
SHOW CREATE EVENT
SHOW CREATE FUNCTION
SHOW CREATE PROCEDURE
SHOW CREATE TABLE
SPATIAL
创建数据库,查帮助。
MariaDB [mysql]> help create database;
Name: 'CREATE DATABASE'
Description:
Syntax:
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
[create_specification] ...
create_specification:
[DEFAULT] CHARACTER SET [=] charset_name
| [DEFAULT] COLLATE [=] collation_name
CREATE DATABASE creates a database with the given name. To use this
statement, you need the CREATE privilege for the database. CREATE
SCHEMA is a synonym for CREATE DATABASE.
URL: https://mariadb.com/kb/en/create-database/
【例6】数据库默认的字符集。
show命令的⽤法：
MariaDB [mysql]> help show;
Name: 'SHOW'
Description:
SHOW has many forms that provide information about databases, tables,
columns, or status information about the server. This section describes
those following:
SHOW AUTHORS
SHOW {BINARY | MASTER} LOGS
SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
SHOW CHARACTER SET [like_or_where]
SHOW COLLATION [like_or_where]
SHOW [FULL] COLUMNS FROM tbl_name [FROM db_name] [like_or_where]
SHOW CONTRIBUTORS
SHOW CREATE DATABASE db_name
SHOW CREATE EVENT event_name
SHOW CREATE FUNCTION func_name
SHOW CREATE PROCEDURE proc_name
SHOW CREATE TABLE tbl_name
SHOW CREATE TRIGGER trigger_name
SHOW CREATE VIEW view_name
SHOW DATABASES [like_or_where]
SHOW ENGINE engine_name {STATUS | MUTEX}
SHOW [STORAGE] ENGINES
SHOW ERRORS [LIMIT [offset,] row_count]
SHOW EVENTS
SHOW FUNCTION CODE func_name
SHOW FUNCTION STATUS [like_or_where]
SHOW GRANTS FOR user
SHOW INDEX FROM tbl_name [FROM db_name]
SHOW MASTER STATUS
SHOW OPEN TABLES [FROM db_name] [like_or_where]
SHOW PLUGINS
SHOW PROCEDURE CODE proc_name
SHOW PROCEDURE STATUS [like_or_where]
SHOW PRIVILEGES
SHOW [FULL] PROCESSLIST
SHOW PROFILE [types] [FOR QUERY n] [OFFSET n] [LIMIT n]
SHOW PROFILES
SHOW SLAVE HOSTS
SHOW SLAVE STATUS
SHOW [GLOBAL | SESSION] STATUS [like_or_where]
SHOW TABLE STATUS [FROM db_name] [like_or_where]
SHOW [FULL] TABLES [FROM db_name] [like_or_where]
SHOW TRIGGERS [FROM db_name] [like_or_where]
SHOW [GLOBAL | SESSION] VARIABLES [like_or_where]
SHOW WARNINGS [LIMIT [offset,] row_count]
like_or_where:
LIKE 'pattern'
| WHERE expr
If the syntax for a given SHOW statement includes a LIKE 'pattern'
part, 'pattern' is a string that can contain the SQL "%" and "_"
wildcard characters. The pattern is useful for restricting statement
output to matching values.
Several SHOW statements also accept a WHERE clause that provides more
flexibility in specifying which rows to display. See
https://mariadb.com/kb/en/extended-show/.
URL: https://mariadb.com/kb/en/show/
数据库默认的字符集：
MariaDB [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 |
+----------+-----------------------------+---------------------+--------+
40 rows in set (0.00 sec)
创建数据库的默认字符集是latin1.
#创建数据库
MariaDB [(none)]> create database school;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> show create database school;
+----------+-------------------------------------------------------------------+
| Database | Create Database |
+----------+-------------------------------------------------------------------+
| school | CREATE DATABASE `school` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+-------------------------------------------------------------------+
1 row in set (0.00 sec)
查看数据库的默认字符集和排序规则：
7 ~]# find / -name db.opt
/var/lib/mysql/mysql/db.opt
/var/lib/mysql/performance_schema/db.opt
/var/lib/mysql/school/db.opt
7 ~]# cat /var/lib/mysql/school/db.opt
default-character-set=latin1
default-collation=latin1_swedish_ci
【例7】创建数据库,删除数据库create和drop。
#创建数据库
MariaDB [(none)]> create database school1;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| school |
| school1 |
+--------------------+
5 rows in set (0.00 sec)
#删除数据库
MariaDB [(none)]> drop database school1;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| school |
+--------------------+
4 rows in set (0.00 sec)
【例8】创建数据库，⾃定义字符集。
MariaDB [(none)]> create database m37 character set utf8mb4;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| m37 |
| mysql |
| performance_schema |
| school |
+--------------------+
5 rows in set (0.00 sec)
数据库使⽤⾃定义的字符集：
MariaDB [(none)]> show create database m37;
+----------+-----------------------------------------------------------------+
| Database | Create Database |
+----------+-----------------------------------------------------------------+
| m37 | CREATE DATABASE `m37` /*!40100 DEFAULT CHARACTER SET utf8mb4 */ |
+----------+-----------------------------------------------------------------+
1 row in set (0.00 sec)
数据库使⽤默认的字符集：
MariaDB [(none)]> show create database school;
+----------+-------------------------------------------------------------------+
| Database | Create Database |
+----------+-------------------------------------------------------------------+
| school | CREATE DATABASE `school` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+-------------------------------------------------------------------+
1 row in set (0.00 sec)
- 创建表 CREATE TABLE
(1) 直接创建
(2) 通过查询现存表创建；新表会被直接插入查询而来的数据
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
[(create_definition,...)] [table_options]
[partition_options] select_statement
(3) 通过复制现存的表的表结构创建，但不复制数据
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name { LIKE
old_tbl_name | (LIKE old_tbl_name) }
字段信息
• col type1
• PRIMARY KEY(col1,...)
• INDEX(col1, ...)
• UNIQUE KEY(col1, ...)
表选项：
• ENGINE [=] engine_name
SHOW ENGINES;查看支持的engine类型
• ROW_FORMAT [=]
{DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}
获取帮助：mysql> HELP CREATE TABLE;
【例9】选中要操作的库,新建的数据库内没有表格use。
MariaDB [(none)]> create database school;
ERROR 1007 (HY000): Can't create database 'school'; database exists #数据库存在
MariaDB [(none)]> use school;
Database changed
MariaDB [school]> show tables;
Empty set (0.00 sec)
- 表操作
查看所有的引擎：SHOW ENGINES
查看表：SHOW TABLES [FROM db_name]
查看表结构：DESC [db_name.]tb_name
SHOW COLUMNS FROM [db_name.]tb_name
删除表：DROP TABLE [IF EXISTS] tb_name
查看表创建命令：SHOW CREATE TABLE tbl_name
查看表状态：SHOW TABLE STATUS LIKE 'tbl_name'
查看库中所有表状态：SHOW TABLE STATUS FROM db_name
【例10】查帮助，创建表。
MariaDB [(none)]> help create table;
Name: 'CREATE TABLE'
Description:
Syntax:
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
(create_definition,...)
[table_options]
[partition_options]
内容较多，请自行测试。
【例11】创建、查看和删除表格create、show和drop。
创建：
MariaDB [(none)]> use school;
Database changed
MariaDB [school]> create table student(
-> stuID int(10) unsigned not null auto_increment primary key,
-> name varchar(50) not null,
-> gender enum('F', 'M') not null)
-> engine=InnoDB default charset=utf8;
Query OK, 0 rows affected (0.01 sec)
MariaDB [school]> show tables;
+------------------+
| Tables_in_school |
+------------------+
| student |
+------------------+
1 row in set (0.00 sec)
查看：
MariaDB [school]> desc student;
+--------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+------------------+------+-----+---------+----------------+
| stuID | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(50) | NO | | NULL | |
| gender | enum('F','M') | NO | | NULL | |
+--------+------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
#注意'\G'=';' 、所以'\G'后面不用加';'
MariaDB [school]> show table status like 'student' \G
*************************** 1. row ***************************
Name: student
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: 2022-05-15 15:48:51
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
删除：
MariaDB [school]> drop table student;
Query OK, 0 rows affected (0.01 sec)
- 数据类型（详见课件54页）
1️⃣整型
2️⃣浮点型
3️⃣定点数
4️⃣字符串
5️⃣二进制数据：BLOB
6️⃣日期时间类型
修饰符
所有类型：
• NULL 数据列可包含NULL值
• NOT NULL 数据列不允许包含NULL值
• DEFAULT 默认值
• PRIMARY KEY 主键
• UNIQUE KEY 唯一键 • CHARACTER SET name 指定一个字符集
数值型：
• AUTO_INCREMENT 自动递增，适用于整数类型
• UNSIGNED 无符号
【例12】参考现有表结构创建表。
MariaDB [school]> create table user select user,host,password from mysql.user;
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
MariaDB [school]> desc user;
+----------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------+------+-----+---------+-------+
| user | char(80) | NO | | | |
| host | char(60) | NO | | | |
| password | char(41) | NO | | | |
+----------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)
⽤查询结果创建表，原表中的部分内容丢失。
MariaDB [school]> select * from user;
+------+-----------+----------+
| user | host | password |
+------+-----------+----------+
| root | localhost | |
| root | 127.0.0.1 | |
| root | ::1 | |
+------+-----------+----------+
3 rows in set (0.00 sec)
【例13】参考现有表结构创建表，复制表结构。
MariaDB [school]> create table user2 like mysql.user;
Query OK, 0 rows affected (0.01 sec)
MariaDB [school]> desc user2;
+------------------------+-----------------------------------+------+-----+----------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+----------+-------+
| Host | char(60) | NO | PRI | | |
| User | char(80) | NO | PRI | | |
| Password | char(41) | NO | | | |
| Select_priv | enum('N','Y') | NO | | N | |
| Insert_priv | enum('N','Y') | NO | | N | |
| Update_priv | enum('N','Y') | NO | | N | |
| Delete_priv | enum('N','Y') | NO | | N | |
| Create_priv | enum('N','Y') | NO | | N | |
| Drop_priv | enum('N','Y') | NO | | N | |
| Reload_priv | enum('N','Y') | NO | | N | |
| Shutdown_priv | enum('N','Y') | NO | | N | |
| Process_priv | enum('N','Y') | NO | | N | |
| File_priv | enum('N','Y') | NO | | N | |
| Grant_priv | enum('N','Y') | NO | | N | |
| References_priv | enum('N','Y') | NO | | N | |
| Index_priv | enum('N','Y') | NO | | N | |
| Alter_priv | enum('N','Y') | NO | | N | |
| Show_db_priv | enum('N','Y') | NO | | N | |
| Super_priv | enum('N','Y') | NO | | N | |
| Create_tmp_table_priv | enum('N','Y') | NO | | N | |
| Lock_tables_priv | enum('N','Y') | NO | | N | |
| Execute_priv | enum('N','Y') | NO | | N | |
| Repl_slave_priv | enum('N','Y') | NO | | N | |
| Repl_client_priv | enum('N','Y') | NO | | N | |
| Create_view_priv | enum('N','Y') | NO | | N | |
| Show_view_priv | enum('N','Y') | NO | | N | |
| Create_routine_priv | enum('N','Y') | NO | | N | |
| Alter_routine_priv | enum('N','Y') | NO | | N | |
| Create_user_priv | enum('N','Y') | NO | | N | |
| Event_priv | enum('N','Y') | NO | | N | |
| Trigger_priv | enum('N','Y') | NO | | N | |
| Create_tablespace_priv | enum('N','Y') | NO | | N | |
| ssl_type | enum('','ANY','X509','SPECIFIED') | NO | | | |
| ssl_cipher | blob | NO | | NULL | |
| x509_issuer | blob | NO | | NULL | |
| x509_subject | blob | NO | | NULL | |
| max_questions | int(11) unsigned | NO | | 0 | |
| max_updates | int(11) unsigned | NO | | 0 | |
| max_connections | int(11) unsigned | NO | | 0 | |
| max_user_connections | int(11) | NO | | 0 | |
| plugin | char(64) | NO | | | |
| authentication_string | text | NO | | NULL | |
| password_expired | enum('N','Y') | NO | | N | |
| is_role | enum('N','Y') | NO | | N | |
| default_role | char(80) | NO | | | |
| max_statement_time | decimal(12,6) | NO | | 0.000000 | |
+------------------------+-----------------------------------+------+-----+----------+-------+
46 rows in set (0.00 sec)
- 表操作
ALTER TABLE 'tbl_name'
字段：
添加字段：add
ADD col1 data_type [FIRST|AFTER col_name]
删除字段：drop
修改字段：in
alter（默认值）, change（字段名）, modify（字段属性）
索引:
添加索引：add index
删除索引：drop index
表选项
修改:
查看表上的索引：SHOW INDEXES FROM [db_name.]tbl_name;
查看帮助：Help ALTER TABLE
【例14】对已创建好的表结构进⾏修改add和drop。
MariaDB [school]> create table student(
-> stuID int(10) unsigned not null auto_increment primary key,
-> name varchar(50) not null,
-> gender enum('F', 'M') not null)
-> engine=InnoDB default charset=utf8;
Query OK, 0 rows affected (0.01 sec)
MariaDB [school]> desc student;
+--------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+------------------+------+-----+---------+----------------+
| stuID | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(50) | NO | | NULL | |
| gender | enum('F','M') | NO | | NULL | |
+--------+------------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)
添加字段：
MariaDB [school]> alter table student add phone varchar(50) not null;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [school]> desc student;
+--------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+------------------+------+-----+---------+----------------+
| stuID | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(50) | NO | | NULL | |
| gender | enum('F','M') | NO | | NULL | |
| phone | varchar(50) | NO | | NULL | |
+--------+------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
删除字段：
MariaDB [school]> alter table student drop gender;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [school]> desc student;
+-------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+----------------+
| stuID | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(50) | NO | | NULL | |
| phone | varchar(50) | NO | | NULL | |
+-------+------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
【例15】修改表结构，其中change可以改字段名和属性，modify只能修改字段属性。
change可以改字段名和属性：
MariaDB [school]> desc student;
+-------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+----------------+
| stuID | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(50) | NO | | NULL | |
| phone | varchar(50) | NO | | NULL | |
+-------+------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
MariaDB [school]> alter table student change phone phone varchar(20) not null;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [school]> desc student;
+-------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+----------------+
| stuID | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(50) | NO | | NULL | |
| phone | varchar(20) | NO | | NULL | |
+-------+------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
modify能修改字段属性：
MariaDB [school]> desc student;
+-------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+----------------+
| stuID | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(50) | NO | | NULL | |
| phone | varchar(20) | NO | | NULL | |
+-------+------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
MariaDB [school]> alter table student modify name varchar(20);
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [school]> desc student;
+-------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+----------------+
| stuID | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | | NULL | |
| phone | varchar(20) | NO | | NULL | |
+-------+------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
- DML语句
DML: INSERT, DELETE, UPDATE
【例16】表中添加数据记录insert。
MariaDB [school]> select * from student;
Empty set (0.00 sec)
MariaDB [school]> insert into student (name,phone) values ("mage","13901234567");
Query OK, 1 row affected (0.00 sec)
MariaDB [school]> select * from student;
+-------+------+-------------+
| stuID | name | phone |
+-------+------+-------------+
| 1 | mage | 13901234567 |
+-------+------+-------------+
1 row in set (0.00 sec)
MariaDB [school]> insert into student values(2,"wang","13907654321");
Query OK, 1 row affected (0.01 sec)
MariaDB [school]> select * from student;
+-------+------+-------------+
| stuID | name | phone |
+-------+------+-------------+
| 1 | mage | 13901234567 |
| 2 | wang | 13907654321 |
+-------+------+-------------+
2 rows in set (0.00 sec)
【例17】表中删除数据记录delete。
MariaDB [school]> select * from student;
+-------+------+-------------+
| stuID | name | phone |
+-------+------+-------------+
| 1 | mage | 13901234567 |
| 2 | wang | 13907654321 |
+-------+------+-------------+
2 rows in set (0.00 sec)
MariaDB [school]> delete from student where name="wang";
Query OK, 1 row affected (0.01 sec)
MariaDB [school]> select * from student;
+-------+------+-------------+
| stuID | name | phone |
+-------+------+-------------+
| 1 | mage | 13901234567 |
+-------+------+-------------+
1 row in set (0.00 sec)
MariaDB [school]> delete from student where phone="13901234567";
Query OK, 1 row affected (0.00 sec)
MariaDB [school]> select * from student;
Empty set (0.00 sec)
危险命令，delete 后不加 where 条件代表删除所有数据 delete。
MariaDB [school]> insert into student (name,phone) values ("mage","13901234567");
Query OK, 1 row affected (0.00 sec)
MariaDB [school]> insert into student values (2,"wang","13907654321");
Query OK, 1 row affected (0.00 sec)
MariaDB [school]> select * from student;
+-------+------+-------------+
| stuID | name | phone |
+-------+------+-------------+
| 2 | wang | 13907654321 |
| 3 | mage | 13901234567 |
+-------+------+-------------+
2 rows in set (0.00 sec)
#危险命令，delete 后不加 where 条件代表删除所有数据 delete。
MariaDB [school]> delete from student;
Query OK, 2 rows affected (0.00 sec)
MariaDB [school]> select * from student;
Empty set (0.00 sec)
更加危险的删除命令truncate效率高，但是不记⼊⽇志，不备份不能还原。清除表的内容，格式还在。
#创建db4数据库
MariaDB [(none)]> create database db4;
Query OK, 1 row affected (0.00 sec)
#查看数据库列表
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| db4 |
| information_schema |
| mysql |
| performance_schema |
| school |
| test |
+--------------------+
6 rows in set (0.00 sec)
MariaDB [(none)]> use db4
Database changed
MariaDB [db4]> create table student(
-> stuid smallint(5) unsigned auto_increment not null primary key,
-> name char(10) not null,
-> gender enum('f', 'm') default 'm',
-> age tinyint(3) unsigned,
-> mobile char(11) );
Query OK, 0 rows affected (0.03 sec)
MariaDB [db4]> desc student;
+--------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+----------------------+------+-----+---------+----------------+
| stuid | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| name | char(10) | NO | | NULL | |
| gender | enum('f','m') | YES | | m | |
| age | tinyint(3) unsigned | YES | | NULL | |
| mobile | char(11) | YES | | NULL | |
+--------+----------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
添加记录
MariaDB [db4]> insert student (name,gender,age,mobile)values('zhao','f','18','13800138000');
Query OK, 1 row affected (0.00 sec)
MariaDB [db4]> insert student (name,gender,age,mobile)values('qian','m','22','110'),('sun','m','20','119'),('li','f','23','10086');
Query OK, 3 row affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
MariaDB [db4]> insert student (name,gender,mobile,age)values('zhou','f','10000','33');
Query OK, 1 row affected (0.00 sec)
MariaDB [db4]> insert student (name,gender,mobile)values('wu','f','10010'),('zheng','f','66668888');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
MariaDB [db4]> select * from student;
+-------+-------+--------+------+-------------+
| stuid | name | gender | age | mobile |
+-------+-------+--------+------+-------------+
| 1 | zhao | f | 18 | 13800138000 |
| 2 | qian | m | 22 | 110 |
| 3 | sun | m | 20 | 119 |
| 4 | li | f | 23 | 10086 |
| 5 | zhou | f | 33 | 10000 |
| 6 | wu | f | NULL | 10010 |
| 7 | zheng | f | NULL | 66668888 |
+-------+-------+--------+------+-------------+
7 rows in set (0.00 sec)
更加危险的删除命令truncate效率高，但是不记⼊⽇志，不备份不能还原。清除表的内容，格式还在【工作中慎用】
#更加危险的删除命令truncate效率高，但是不记⼊⽇志，不备份不能还原。清除表的内容，格式还在【工作中慎用】
MariaDB [db4]> truncate table student;
Query OK, 0 rows affected (0.00 sec)
#查看表信息【已经空了】
MariaDB [db4]> select * from student;
Empty set (0.00 sec)
#表格式还在
MariaDB [db4]> desc student;
+--------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+----------------------+------+-----+---------+----------------+
| stuid | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| name | char(10) | NO | | NULL | |
| gender | enum('f','m') | YES | | m | |
| age | tinyint(3) unsigned | YES | | NULL | |
| mobile | char(11) | YES | | NULL | |
+--------+----------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
【例18】在表结构⼀样的情况下，将表的数据导到另⼀张表中。
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| db4 |
| information_schema |
| m37 |
| mysql |
| performance_schema |
| school |
+--------------------+
6 rows in set (0.00 sec)
进⼊数据库db4，把数据库school中student表的内容添加到数据库db4，表student中。
#如果school中没有student2表执行此步、创建表等...
MariaDB [db4]> use school;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MariaDB [school]> create table student2(
-> stuid smallint(5) unsigned auto_increment not null primary key,
-> name char(10) not null,
-> gender enum('f','m') default 'm',
-> age tinyint(3) unsigned,
-> mobile char(11) );
Query OK, 0 rows affected (0.01 sec)
#添加两条记录
MariaDB [school]> insert student2 (name,gender,age,mobile)values('alice','f','20','13800138000');
Query OK, 1 row affected (0.00 sec)
MariaDB [school]> insert student2 (name,gender,age)values('bob','m','30');
Query OK, 1 row affected (0.00 sec)
#查看school库中setdent2表的信息、可以看的刚刚添加的2条记录
MariaDB [school]> select * from student2;
+-------+-------+--------+------+-------------+
| stuid | name | gender | age | mobile |
+-------+-------+--------+------+-------------+
| 1 | alice | f | 20 | 13800138000 |
| 2 | bob | m | 30 | NULL |
+-------+-------+--------+------+-------------+
2 rows in set (0.00 sec)
进⼊数据库db4，创建结构与数据库school中student2表结构相同的表student。
MariaDB [db4]> use db4;
Database changed
MariaDB [db4]> show tables;
+---------------+
| Tables_in_db4 |
+---------------+
| student |
+---------------+
1 row in set (0.00 sec)
#删除'db4'中指定'表'
MariaDB [db4]> drop table student;
Query OK, 0 rows affected (0.01 sec)
MariaDB [db4]> show tables;
Empty set (0.00 sec)
#创建结构与数据库school中student2表结构相同的表student。
MariaDB [db4]> MariaDB [db4]> create table student like school.student2;
Query OK, 0 rows affected (0.01 sec)
MariaDB [db4]> show tables;
+---------------+
| Tables_in_db4 |
+---------------+
| student |
+---------------+
1 row in set (0.00 sec)
进⼊数据库school，把数据库school中student2表的内容添加到数据库db4，表student中。
MariaDB [db4]> use school;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MariaDB [school]> insert db4.student select * from student2;
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
MariaDB [school]> use db4;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MariaDB [db4]> select * from student;
+-------+-------+--------+------+-------------+
| stuid | name | gender | age | mobile |
+-------+-------+--------+------+-------------+
| 1 | alice | f | 20 | 13800138000 |
| 2 | bob | m | 30 | NULL |
+-------+-------+--------+------+-------------+
2 rows in set (0.00 sec)
【例19】修改记录update。where是危险操作！！！
MariaDB [db4]> select * from student;
+-------+----------+--------+------+-------------+
| stuid | name | gender | age | mobile |
+-------+----------+--------+------+-------------+
| 1 | alice | f | 20 | 13800138000 |
| 2 | bob | m | 30 | NULL |
| 3 | xiaoming | m | 18 | 10010 |
| 4 | xiaohong | m | 16 | 10086 |
| 5 | xiaoqing | m | NULL | NULL |
| 6 | ?? | m | 30 | NULL | <-- 此name项是中文、所以显示'??'【默认表是拉丁、需要改成utf8mb4|注意：参考下面后续实验、即可】
| 8 | aaa | m | 20 | NULL |
+-------+----------+--------+------+-------------+
7 rows in set (0.01 sec)
MariaDB [db4]> insert student (name,age,mobile)values('xiaoming','18','10010'),('xiaohong','16','10086');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
MariaDB [db4]> insert student (name)values('xiaoqiang');
Query OK, 1 row affected (0.00 sec)
修改时⽤了where语句。
MariaDB [db4]> update student set name='bbb',mobile=10000 where stuid=6;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [db4]> select * from student;
+-------+----------+--------+------+-------------+
| stuid | name | gender | age | mobile |
+-------+----------+--------+------+-------------+
| 1 | alice | f | 20 | 13800138000 |
| 2 | bob | m | 30 | NULL |
| 3 | xiaoming | m | 18 | 10010 |
| 4 | xiaohong | m | 16 | 10086 |
| 5 | xiaoqing | m | NULL | NULL |
| 6 | bbb | m | 30 | 10000 | <-- 把'stuid=6' 改成了'bbb','10000'
| 8 | aaa | m | 20 | NULL |
+-------+----------+--------+------+-------------+
7 rows in set (0.01 sec)
修改时没有加where语句。危险操作！请提前备份表格。
#修改时没有加where语句。危险操作！请提前备份表格。
MariaDB [db4]> update student set name='bbb',mobile=10000;
Query OK, 6 rows affected (0.01 sec)
Rows matched: 7 Changed: 6 Warnings: 0
MariaDB [db4]> select * from student;
+-------+----------+--------+------+-------------+
| stuid | name | gender | age | mobile |
+-------+----------+--------+------+-------------+
| 1 | bbb | f | 20 | 10000 |
| 2 | bbb | m | 30 | 10000 |
| 3 | bbb | m | 18 | 10000 |
| 4 | bbb | m | 16 | 10000 |
| 5 | bbb | m | NULL | 10000 |
| 6 | bbb | m | 30 | 10000 |
| 8 | bbb | m | 20 | 10000 |
+-------+----------+--------+------+-------------+
7 rows in set (0.01 sec)
安全模式，避免where的惨剧发⽣，-U。
7 ~]# mysql -U
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 17
Server version: 10.2.43-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> use db4;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MariaDB [db4]> update student set name='bbb',mobile=10000;
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without
a WHERE that uses a KEY column #您正在使用安全更新模式，并且您试图更新一个没有使用键列的WHERE的表（如果想修改、需要加where、参照'【例19】修改时⽤了where语句'）
建议mysql改为mysql -U别名或改客户端的配置⽂件。
[root@centos7 ~]# vim /etc/my.cnf.d/mysql-clients.cnf
#
# These groups are read by MariaDB command-line tools
# Use it for options that affect only one utility
#
[mysql]
safe-updates #添加
[mysql_upgrade]
[mysqladmin]
[mysqlbinlog]
[mysqlcheck]
[mysqldump]
[mysqlimport]
[mysqlshow]
[mysqlslap]
测试：
7 ~]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 13
Server version: 10.2.24-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> use db4;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MariaDB [db4]> select * from student;
+-------+----------+--------+------+-------------+
| stuid | name | gender | age | mobile |
+-------+----------+--------+------+-------------+
| 1 | bbb | f | 20 | 10000 |
| 2 | bbb | m | 30 | 10000 |
| 3 | bbb | m | 18 | 10000 |
| 4 | bbb | m | 16 | 10000 |
| 5 | bbb | m | NULL | 10000 |
| 6 | bbb | m | 30 | 10000 |
| 8 | bbb | m | 20 | 10000 |
+-------+----------+--------+------+-------------+
7 rows in set (0.01 sec)
MariaDB [db4]> update student set name='aaa',mobile=110;
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without
a WHERE that uses a KEY column
#此时发现加了'mysql -U'属性后表内容并没有改变
MariaDB [db4]> select * from student;
+-------+------+--------+------+--------+
| stuid | name | gender | age | mobile |
+-------+------+--------+------+--------+
| 1 | bbb | f | 20 | 10000 |
| 2 | bbb | m | 30 | 10000 |
| 3 | bbb | m | 18 | 10000 |
| 4 | bbb | m | 16 | 10000 |
| 5 | bbb | m | NULL | 10000 |
| 6 | bbb | m | 30 | 10000 |
| 8 | bbb | m | 20 | 10000 |
+-------+------+--------+------+--------+
7 rows in set (0.00 sec)
【例20】显⽰中⽂字符，数据库默认为拉丁，改为utf8mb4。
MariaDB [(none)]> create database m37 character set utf8mb4;
ERROR 1007 (HY000): Can't create database 'm37'; database exists #已创建
MariaDB [(none)]> show create database m37;
+----------+-----------------------------------------------------------------+
| Database | Create Database |
+----------+-----------------------------------------------------------------+
| m37 | CREATE DATABASE `m37` /*!40100 DEFAULT CHARACTER SET utf8mb4 */ | <-- 推荐'utf8mb4'、支持全球语言和表情包
+----------+-----------------------------------------------------------------+
1 row in set (0.00 sec)
MariaDB [m37]> create table student(
-> stuid smallint(5) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> name char(10) NOT NULL,
-> gender enum('m','f') DEFAULT 'm',
-> age tinyint(3) unsigned DEFAULT NULL,
-> mobile char(11) DEFAULT NULL)
-> ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.06 sec)
MariaDB [m37]> show create table student;
+---------+-----------------------------------------------------------------+
| Table | Create Table |
+---------+-----------------------------------------------------------------+
| student | CREATE TABLE `student` (
`stuid` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`name` char(10) NOT NULL,
`gender` enum('m','f') DEFAULT 'm',
`age` tinyint(3) unsigned DEFAULT NULL,
`mobile` char(11) DEFAULT NULL,
PRIMARY KEY (`stuid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+---------+-----------------------------------------------------------------+
1 row in set (0.00 sec)
设置字符集：
MariaDB [m37]> alter table student character set utf8mb4;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [m37]> show table status like 'student'\G
*************************** 1. row ***************************
Name: student
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: 2022-05-18 01:31:01
Update_time: NULL
Check_time: NULL
Collation: utf8mb4_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.01 sec)
表的字符集改了，但是各字符段的字符集没有改。
MariaDB [m37]> show create table student;
+---------+------------------------------------------------------------------------------+
| Table | Create Table |
+---------+------------------------------------------------------------------------------+
| student | CREATE TABLE `student` (
`stuid` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`name` char(10) CHARACTER SET latin1 NOT NULL,
`gender` enum('m','f') CHARACTER SET latin1 DEFAULT 'm',
`age` tinyint(3) unsigned DEFAULT NULL,
`mobile` char(11) CHARACTER SET latin1 DEFAULT NULL,
PRIMARY KEY (`stuid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 | <-- 此处可以看到表的字符集改了，但是各字符段的字符集没有改
+---------+------------------------------------------------------------------------------+
1 row in set (0.00 sec)
创建新的表，就没有字符集不统⼀的问题了
MariaDB [m37]> create table student2(
-> stuid smallint(5) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> name char(10) NOT NULL,
-> gender enum('m','f') DEFAULT 'm',
-> age tinyint(3) unsigned DEFAULT NULL,
-> mobile char(11) DEFAULT NULL)
-> ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected (0.00 sec)
MariaDB [m37]> show create table student2;
+----------+------------------------------------------------------------------------------+
| Table | Create Table |
+----------+------------------------------------------------------------------------------+
| student2 | CREATE TABLE `student2` (
`stuid` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`name` char(10) NOT NULL,
`gender` enum('m','f') DEFAULT 'm',
`age` tinyint(3) unsigned DEFAULT NULL,
`mobile` char(11) DEFAULT NULL,
PRIMARY KEY (`stuid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+----------+------------------------------------------------------------------------------+
1 row in set (0.00 sec)
MariaDB [m37]> insert into student2(age,name)values(30,'小白');
Query OK, 1 row affected (0.00 sec)
MariaDB [m37]> select * from student2;
+-------+--------+--------+------+--------+
| stuid | name | gender | age | mobile |
+-------+--------+--------+------+--------+
| 1 | 小白 | m | 30 | NULL |
+-------+--------+--------+------+--------+
1 row in set (0.00 sec)
【例21】数据库的历史。
退出数据库后。
MariaDB [m37]> exit
Bye
7 ~]# ls -a
. .bash_profile .dbus .ICEauthority Pictures Videos
.. .bashrc Desktop initial-setup-ks.cfg .pki .viminfo
anaconda-ks.cfg .cache Documents .local Public .Xauthority
.bash_history .config Downloads Music .tcshrc
.bash_logout .cshrc .esd_auth .mysql_history Templates
⽇志⽂件：
#日志文件、记录在mysql中执行的历史命令
7 ~]# cat .mysql_history
show databases;
use mysql;
show tables;
desc user;
...
【例22】改变整个数据库的默认字符集。
7 ~]# vim /etc/my.cnf.d/server.cnf
# this is only for the mysqld standalone daemon
[mysqld]
character-set-server=utf8mb4 #改变整个数据库的字符集
#
重启服务：
7 ~]# service mariadb restart
Redirecting to /bin/systemctl restart mariadb.service
重新登录：
7 ~]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 10.2.43-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
新建数据库：
MariaDB [(none)]> create database db5;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> show create database db5;
+----------+-----------------------------------------------------------------+
| Database | Create Database |
+----------+-----------------------------------------------------------------+
| db5 | CREATE DATABASE `db5` /*!40100 DEFAULT CHARACTER SET utf8mb4 */ |
+----------+-----------------------------------------------------------------+
1 row in set (0.00 sec)
1608
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
