MySQL基础操作
一、MySQL数据库管理
1.登入
mysql -u root -p
然后输入密码
2.查看当前MySQL会话使用的字符集
show variables like 'character%';
显示如下：
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 | C:Program FilesMySQLMySQL Server 5.5sharecharsets |
+--------------------------+---------------------------------------------------------+
8 rows in set (0.10 sec)
3.创建数据库
create database database_name;
create database choose;
显示如下：
mysql> create database database_name;
Query OK, 1 row affected (0.08 sec)
mysql> create database choose;
Query OK, 1 row affected (0.00 sec)
4.查看数据库
show databases;
显示如下：
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| choose |
| database_name |
| mysql |
| performance_schema |
| test |
+--------------------+
6 rows in set (0.17 sec)
5.显示数据库结构
show create database_name;
例如show create choose;
显示如下：
mysql> show create database choose;
+----------+-----------------------------------------------------------------+
| Database | Create Database |
+----------+-----------------------------------------------------------------+
| choose | CREATE DATABASE `choose` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+-----------------------------------------------------------------+
1 row in set (0.00 sec)
6.选择当前操作的数据库
use database_name;
显示如下：
mysql> use database_name;
Database changed
7.删除数据库
drop database database_name;
显示如下：
mysql> drop database database_name;
Query OK, 0 rows affected (0.08 sec)
二、MySQL表管理
1.查看MySQL服务实例支持的存储引擎
show engines;
显示如下：
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 temporary tables | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.05 sec)
2.创建数据库表my_table
mysql> use choose;
Database changed
mysql> set default_storage_engine=InnoDB;
Query OK, 0 rows affected (0.02 sec)
mysql> create table my_table(
-> today datetime,
-> name char(20)
-> );
Query OK, 0 rows affected (0.18 sec)