数据库基本管理
数据库基本管理
一.连接MySQL服务
1.连接方式
(1)客户端连接MySQL服务的方法
命令行
web页面
安装图形软件
编写脚本（ php、Java、python .... )·
(2)使用mysq1命令
- mysql -h服务器IP -u用户名 -p密码「数据库名]
- quit或exit退出
连接MySQL服务器时，最基本的用法是通过 -u 选项指定用户名、-p指定密码。密码可以写在命令行（如果不写，则出现交互，要求用户输入），当然基于安全考虑一般不推荐这么做：
[root@dbsvr1 ~]# mysql -uroot -p123456 //紧挨着选项，不要空格 mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 16 Server version: 5.7.17 MySQL Community Server (GPL) Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> exit //退出已登录的mysql> 环境 Bye
默认情况下，msyql命令会连接本机的MySQL服务。但在需要的时候，可以通过 -h 选项指定远程主机；
[root@dbsvr1 ~]# mysql -h 127.0.0.1 –uroot –p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 17
Server version: 5.7.17 MySQL Community Server (GPL)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> exit //退出已登录的mysql环境 Bye
2.数据存储流程
客户端把数据存储到数据库服务器上的步骤
（1）连接数据库服务器
（2）建库 //类似于文件夹
（3）建表 //类似于文件
（4）插入记录 //类似于文件内容
（5）断开连接
3.MySQL管理环境
. SQL命令使用规则
- SQL命令不区分字母大小写（密码、变量值除外)-每条SQL命令以;结束
一默认命令不支持Tab键自动补齐
- \c 终止sql命令
常用的SQL命令分类
-管理数据库使用SQL（结构化查询语言)
1 DDL数据定义语言如:create、alter、drop
2 DML 数据操作语言如:insert、update、delete
3 DCL 数据控制语言如:grant、revoke
4 DTL数据事物语言如:commit、rollback、savepoint
二.MySQL基本操作
1.库管理命令
(1)库名命名规则
-仅可以使用数字、字母、下划线、不能纯数字
-区分字母大小写，具有唯一性
-不可使用指令关键字、特殊字符
(2)库类似于文件夹，用来存储表
-可以创建多个库，通过库名区分- show databases;
//显示已有的库
- select user();
//显示连接用户
- use库名;
//切换库
- select database();
//显示当前所在的库
- create database库名;
//创建新库
- show tables;
//显示已有的表
- drop database库名;
//删除库
1）查看现有的库
mysql> show databases; //查看现有的库 +--------------------+ | Database | +--------------------+ | information_schema | //信息概要库 | mysql | //授权库 | performance_schema | //性能结构库 | sys | //系统元数据库 +--------------------+ 4 rows in set (0.15 sec)
2）切换/使用指定的库
mysql> use sys; //切换到sys库 Database changed mysql> select database(); //确认当前所在的库 +------------+ | DATABASE() | +------------+ | sys | +------------+ 1 row in set (0.00 sec)
切换到mysql库：
mysql> use mysql; //切换到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 mysql> select database(); //确认当前所在的库 +------------+ | DATABASE() | +------------+ | mysql | +------------+ 1 row in set (0.00 sec) 5 rows in set (0.00 sec)
3）新建名为newdb的库，确认结果：
mysql> create database newdb; //新建名为newdb的库 Query OK, 1 row affected (0.00 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mydb | //新建的mydb库 | mysql | | newdb | //新建的newdb库 | performance_schema | | sys | +--------------------+ 6 rows in set (0.00 sec)
4）删除指定的库
mysql> drop database newdb; //删除名为newdb的库 Query OK, 0 rows affected (0.01 sec) mysql> show databases; //确认删除结果，已无newdb库 +--------------------+ | Database | +--------------------+ | information_schema | | mydb | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.00 sec)
2.表管理命令
(1)建表
-表存储数据的文件。
Mysql>create table库名.表名(
字段名1类型(宽度),
字段名2类型(宽度)
......
) DEFAULT CHARSET=utf8 ; //指定中文字符集 ，可以给字段赋值中文
(2）表类似于文件
desc 库名.表名; //查看表结构
drop table 库名.表名; //删除表
3.记录管理命令
记录类似于文件里的行
select * from 库名.表名; //查看表记录
insert into 库名.表名 values(值列表）; //插入表记录
update 库名.表名 set 字段=值; //修改表记录
delete from 表名; //删除表记录
1）查看指定的库里有哪些表
查看mysql库里有哪些表：
mysql> 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 mysql> show tables; +---------------------------+ | Tables_in_mysql | +---------------------------+ | columns_priv | | db | | engine_cost | | event | | func | | general_log | | gtid_executed | | help_category | | help_keyword | | help_relation | | help_topic | | innodb_index_stats | | innodb_table_stats | | ndb_binlog_index | | plugin | | proc | | procs_priv | | proxies_priv | | server_cost | | servers | | slave_master_info | | slave_relay_log_info | | slave_worker_info | | slow_log | | tables_priv | | time_zone | | time_zone_leap_second | | time_zone_name | | time_zone_transition | | time_zone_transition_type | | user | //存放数据库用户的表 +---------------------------+ 31 rows in set (0.00 sec)
2）查看指定表的字段结构
当前库为mysql，查看columns_priv表的结构，以列表形式展现：
mysql> desc columns_priv\G //查看表结构，以列表形式展现，末尾不用分号
*************************** 1. row ***************************
Field: Host
Type: char(60)
Null: NO
Key: PRI
Default:
Extra:
*************************** 2. row ***************************
Field: Db
Type: char(64)
Null: NO
Key: PRI
Default:
Extra:
*************************** 3. row ***************************
Field: User
Type: char(32)
Null: NO
Key: PRI
Default:
Extra:
*************************** 4. row ***************************
Field: Table_name
Type: char(64)
Null: NO
Key: PRI
Default:
Extra:
*************************** 5. row ***************************
Field: Column_name
Type: char(64)
Null: NO
Key: PRI
Default:
Extra:
*************************** 6. row ***************************
Field: Timestamp
Type: timestamp
Null: NO
Key:
Default: CURRENT_TIMESTAMP
Extra: on update CURRENT_TIMESTAMP
*************************** 7. row ***************************
Field: Column_priv
Type: set('Select','Insert','Update','References')
Null: NO
Key:
Default:
Extra:
7 rows in set (0.01 sec)
查看columns_priv表的结构，以表格形式展现：
mysql> desc columns_priv; //查看表结构，以表格形式展现末尾需要有分号
+-------------+----------------------------------------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+----------------------------------------------+------+-----+-------------------+-----------------------------+
| Host | char(60) | NO | PRI | | |
| Db | char(64) | NO | PRI | | |
| User | char(32) | NO | PRI | | |
| Table_name | char(64) | NO | PRI | | |
| Column_name | char(64) | NO | PRI | | |
| Timestamp | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| Column_priv | set('Select','Insert','Update','References') | NO | | | |
+-------------+----------------------------------------------+------+-----+-------------------+-----------------------------+
7 rows in set (0.00 sec)
上述操作中，当引用非当前库中的表时，可以用“库名.表名”的形式。比如，切换为mysql库再执行“desc columns_priv;”，与以下操作的效果是相同的：
mysql> desc mysql.columns_priv;
+-------------+----------------------------------------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+----------------------------------------------+------+-----+-------------------+-----------------------------+
| Host | char(60) | NO | PRI | | |
| Db | char(64) | NO | PRI | | |
| User | char(16) | NO | PRI | | |
| Table_name | char(64) | NO | PRI | | |
| Column_name | char(64) | NO | PRI | | |
| Timestamp | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| Column_priv | set('Select','Insert','Update','References') | NO | | | |
+-------------+----------------------------------------------+------+-----+-------------------+-----------------------------+
7 rows in set (0.00 sec)
3）在test库中创建一个名为pwlist的表
包括name、password两列，其中name列作为主键。两个字段值均不允许为空，其中密码列赋予默认空值，相关操作如下所述。
切换到mydb库：
mysql> use mydb; Database changed
新建pwlist表：
mysql> create table pwlist(
-> name char(16) not null,
-> password char(48)default '',
-> primary key(name)
-> );
Query OK, 0 rows affected (0.38 sec)
确认新创建的表：
mysql> show tables; +----------------+ | Tables_in_mydb | +----------------+ | pwlist | //新建的pwlist表 +----------------+ 1 rows in set (0.01 sec)
查看pwlist表的字段结构：
mysql> desc pwlist; +----------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+----------+------+-----+---------+-------+ | name | char(16) | NO | PRI | NULL | | | password | char(48) | YES | | | | +----------+----------+------+-----+---------+-------+ 2 rows in set (0.01 sec)
4）删除指定的表
删除当前库中的pwlist表：
mysql> drop table pwlist; Query OK, 0 rows affected (0.01 sec)
确认删除结果：
mysql> show tables; Empty set (0.00 sec)
5）在mydb库中创建一个学员表
表格结构及数据内容如表-1所示。
在MySQL表内存储中文数据时，需要更改字符集（默认为latin1不支持中文），以便MySQL支持存储中文数据记录；比如，可以在创建库或表的时候，手动添加“DEFAULT CHARSET=utf8”来更改字符集。
根据上述表格结构，创建支持中文的student表：
mysql> CREATE TABLE mydb.student(
-> 学号 char(9) NOT NULL,
-> 姓名 varchar(4) NOT NULL,
-> 性别 enum('男','女') NOT NULL,
-> 手机号 char(11) DEFAULT '',
-> 通信地址 varchar(64),
-> PRIMARY KEY(学号)
-> ) DEFAULT CHARSET=utf8; //手工指定字符集，采用utf8
Query OK, 0 rows affected (0.31sec)
查看student表的字段结构：
mysql> DESC mydb.student;
+--------------+-------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------------+------+-----+---------+-------+
| 学号 | char(9) | NO | PRI | NULL | |
| 姓名 | varchar(4) | NO | | NULL | |
| 性别 | enum('男','女') | NO | | NULL | |
| 手机号 | char(11) | YES | | | |
| 通信地址 | varchar(64) | YES | | NULL | |
+--------------+-------------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
查看student表的实际创建指令：
mysql> SHOW CREATE TABLE mydb.student;
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|Table |Create Table |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| student | CREATE TABLE `student` (
`学号` char(9) NOT NULL,
`姓名` varchar(4) NOT NULL,
`性别` enum('男','女') NOT NULL,
`手机号` char(11) DEFAULT '',
`通信地址` varchar(64) DEFAULT NULL,
PRIMARY KEY (`学号`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
注意：若要修改MySQL服务的默认字符集，可以更改服务器的my.cnf配置文件，添加character_set_server=utf8 配置，然后重启数据库服务。
[root@dbsvr1 ~]# vim /etc/my.cnf //修改运行服务配置 [mysqld] .. .. character_set_server=utf8 [root@dbsvr1 ~]# systemctl restart mysqld //重启服务 .. .. [root@dbsvr1 ~]# mysql –u root -p Enter password: .. .. mysql> SHOW VARIABLES LIKE 'character%'; //确认更改结果 +--------------------------+----------------------------+ | Variable_name | Value | +--------------------------+----------------------------+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | utf8 | | character_set_filesystem | binary | | character_set_results | utf8 | | character_set_server | utf8 | | character_set_system | utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | +--------------------------+----------------------------+ 8 rows in set (0.03 sec)
浙公网安备 33010602011771号