近期開始学习MySQL,主要是通过书籍,和看燕十八老师的视频,然后通过博客记录自己的学习过程。
登入数据库
zhiniaobu@telunsu-K55VD:~$ mysql -uroot -p Enter password:
查看当前全部数据库
mysql> show databases;#查看当前全部数据库 +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | +--------------------+ 3 rows in set (0.00 sec)
创建数据库
mysql> create database ceshi;#创建数据库ceshi Query OK, 1 row affected (0.00 sec) mysql> show databases;#查看当前全部数据库,会看到如今多了一个ceshi +--------------------+ | Database | +--------------------+ | information_schema | | ceshi | | mysql | | performance_schema | +--------------------+ 4 rows in set (0.00 sec)
查看数据库的定义
mysql> show create database ceshi;#查看数据库ceshi的定义 +----------+------------------------------------------------------------------+ | Database | Create Database | +----------+------------------------------------------------------------------+ | ceshi | CREATE DATABASE `ceshi` /*!40100 DEFAULT CHARACTER SET latin1 */ | +----------+------------------------------------------------------------------+ 1 row in set (0.00 sec)
删除数据库
mysql> drop database ceshi;#删除数据库ceshi Query OK, 0 rows affected (0.07 sec) mysql> show databases;#查看当前全部数据库,如今会发现ceshi已经不存在了,由于已经删除了 +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | +--------------------+ 3 rows in set (0.00 sec
使用数据库
mysql> create database ceshi_ku;#先建立一个库,等下用来建表 Query OK, 1 row affected (0.00 sec) mysql> show databases;#ceshi_ku已经成功的建立了 +--------------------+ | Database | +--------------------+ | information_schema | | ceshi_ku | | mysql | | performance_schema | +--------------------+ 4 rows in set (0.00 sec) mysql> use ceshi_ku;#建立表之前,首先要选择一个库 Database changed
查看当前库以下的全部表
mysql> show tables;#查看当前库以下的全部表,眼下是什么表也没有 Empty set (0.00 sec)
创建表
mysql> create table ceshi_biao -> ( -> id int(11), -> salary float -> );#建立ceshi_biao表 Query OK, 0 rows affected (0.10 sec) mysql> show tables;#查看当前库以下的全部表,ceshi_biao表已经成功建立 +--------------------+ | Tables_in_ceshi_ku | +--------------------+ | ceshi_biao | +--------------------+ 1 row in set (0.00 sec)
查看表基本结构
mysql> desc ceshi_biao;#查看ceshi_biao表基本结构 +--------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+---------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | salary | float | YES | | NULL | | +--------+---------+------+-----+---------+-------+ 2 rows in set (0.04 sec)
查看表具体结构
mysql> show create table ceshi_biao;#查看ceshi_biao表具体结构 +------------+-------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +------------+-------------------------------------------------------------------------------------------------------------------------------+ | ceshi_biao | CREATE TABLE `ceshi_biao` ( `id` int(11) DEFAULT NULL, `salary` float DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +------------+-------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
改动表名
mysql> alter table ceshi_biao rename to ceshi_new;#把ceshi_biao表的名字换成ceshi_new Query OK, 0 rows affected (0.05 sec) mysql> show tables;#查看当前库以下的全部表,ceshi_biao表的名字已经成功改为ceshi_new +--------------------+ | Tables_in_ceshi_ku | +--------------------+ | ceshi_new | +--------------------+ 1 row in set (0.00 sec) ###########2种方法################ mysql> rename table ceshi_biao to ceshi_new;#把ceshi_biao表的名字换成ceshi_new Query OK, 0 rows affected (0.03 sec) mysql> show tables;#查看当前库以下的全部表,ceshi_biao表的名字已经成功改为ceshi_new +--------------------+ | Tables_in_ceshi_ku | +--------------------+ | ceshi_new | +--------------------+ 1 row in set (0.00 sec)
往表里面插入数据
mysql> insert into ceshi_new values -> (11,400.56), -> (12,600.07), -> (13,800.45); Query OK, 3 rows affected (0.03 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from ceshi_new;#这个表就是员工号码以及员工的工资,刚才就仅仅插入了3条 +------+--------+ | id | salary | +------+--------+ | 11 | 400.56 | | 12 | 600.07 | | 13 | 800.45 | +------+--------+ 3 rows in set (0.00 sec
清空表
mysql> truncate ceshi_new;#清空表,不是删除表 Query OK, 0 rows affected (0.04 sec) mysql> select * from ceshi_new;#查看内容 Empty set (0.00 sec)
删除表
mysql> drop table ceshi_new;#删除ceshi_new表 Query OK, 0 rows affected (0.05 sec) mysql> show tables;#查看当前库以下的全部表,ceshi_new表已经成功删除 Empty set (0.01 sec)
改动编码
mysql> set names utf8;#改动编码为utf8 Query OK, 0 rows affected (0.00 sec)