1,连接
mysql -h 127.0.0.1 -u root -p
2,显示数据库
show database ;
mysql> show databases ;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)
use db_aaa;
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
3,显示表
mysql> show tables ;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| event |
| func |
+---------------------------+
24 rows in set (0.00 sec)
show create table test;
4, 创建数据库
create database db_name;
show create database test;
create database lhb default character set gb2312 collate gb2312_chinese_ci;
show collation;
mysql COLLATE是校对集的意思,可以理解为,排序规则等。一个字符集可能有多种校对集合;
例子如下:
CREATE DATABASE db_name
[[DEFAULT] CHARACTER SET charset_name]
[[DEFAULT] COLLATE collation_name]
例如:
CREATE DATABASE db_name
DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;
MySQL这样选择数据库字符集和数据库校对规则:
· 如果指定了CHARACTER SET X和COLLATE Y,那么采用字符集X和校对规则Y。
· 如果指定了CHARACTER SET X而没有指定COLLATE Y,那么采用CHARACTER SET X和CHARACTER SET X的默认校对规则。
· 否则,采用服务器字符集和服务器校对规则。
5, 创建表
use db_name;
create table students
(
id int unsigned ,
name char(8) ,
sex char(4),
age tinyint unsigned
) ENGINE=InnoDB/MyISAM DEFAULT CHARSET=utf8;
6,查询
select * from tb_name;
7,插入一行
insert into tb_name values( "aaa", 27);
8,跟新一行
UPDATE 表名 SET 字段名1='a',字段名2='b' WHERE 字段名3='c';
9,删除
delete from friends where user_name = 'simaopig';
10,执行sql脚本
source /path/mysql.sql
ref:
http://www.cnblogs.com/baochuan/archive/2012/03/15/2398276.html