学习总结了一些MySQL的使用技巧:
Tips1 : 关于结束符
MySQL中的结束符默认是分号(;),除了分号外终止语句的另外一种方法是使用\g(表示go),当然也可以使用\G,这样可以竖直排列显示结果,每行一个值,对于输出行比较长,使用\G输出更易于阅读(比如下面是查看数据库定义的语句显示);倘若不想执行已经输入的命令可以在命令的结尾加上\c(clear)清除已经输入的命令。
Tips2 :执行sql文件
如果mysql的命令已经在一个文件当中,则可以利用Shell重定向执行,格式如下:
$ mysql -u root -p < fileName
接着会提示你输入mysql数据库管理员root的密码,输入成功,则执行。
当然,如果你想在用到某个数据库,又没有在sql文件中指明(比如创建一个表,但没有指明数据库),这时就可以在上述命令中指明,格式如下:
$mysql databaseName -u root -p < fileName
或者,你已经进入到MySQL命令模式,可是使用MySQL的source命令执行sql文件
mysql> source /root/test/mysql-learning/insert.sql
还有一种使用Load data载入数据的方式
load data local infile '/root/test/mysql-learning/data.txt' into table T;
其中,文件data.txt的格式如下(
以制表符隔开各列数据)
3 tom
4 messi
5 xavi
Tip3 关于操作系统的差异
在windows下,mysql数据库名、表名是不区分大小写的,但是在Linux环境下,由于其文件系统严格区分大小写,所以,大小写不同,也就是不同的数据库或者表明。
Tip4 : 关于字符集和排序方式
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
[sql]
mysql>
show
characterset
like
'%gbk%'
;
+
---------+------------------------+-------------------+--------+
| Charset | Description |
Default
collation | Maxlen |
+
---------+------------------------+-------------------+--------+
| gbk | GBK Simplified Chinese | gbk_chinese_ci | 2 |
+
---------+------------------------+-------------------+--------+
1 row
in
set
(0.00 sec)
mysql>
show collation
like
'%gbk%'
;
+
----------------+---------+----+---------+----------+---------+
| Collation | Charset | Id |
Default
| Compiled | Sortlen |
+
----------------+---------+----+---------+----------+---------+
| gbk_chinese_ci | gbk | 28 | Yes | Yes | 1 |
| gbk_bin | gbk | 87 | | Yes | 1 |
+
----------------+---------+----+---------+----------+---------+
2
rows
in
set
(0.00 sec)
|
。。。。中间省略若干行
其中字段engine是存储引擎名称,support字段为YES表示支持,为DEFAULT表示默认存储引擎,为DISABLED表示支持但是已经关闭;comment是一些说明;transaction表示是否支持事物,XA表示是否支持分布式存储,savepoint表示是否支持事务回滚。上述显示的内容,其实是MySQL自带的数据库表(information_schema数据库中ENGINES表)中的信息。
Tips 6 : 数据库表的文件存储
MySQL命令行下使用命令查看相关存储路径
mysql> show variables like '%dir%';
其中变量 datadir 的value就是数据库表的存储路径
其中MEMORY引擎表中的所有数据均放在内存中,是临时性的,数据库服务器掉电后,所有数据将消失,它不占用任何硬盘空间,所以大量数据不使用该引擎,研究临时性数据的时候可以选择。
Tips 7 : 创建临时表
create temporary table t10(id int ,name varchar(20));
这样做的好处时,当断开服务器连接的时候,这张临时表会被自动的删除,不必每次手动drop掉,节省数据库资源。
temporary表只对创建它的用户可见,不同的用户可以创建名字相同的临时表。
可以创建一个与永久表同名的临时temporary表,只是这样做临时temporary表会隐藏永久表,在临时表存在的情况下无法对原有表操作,当临时表消失的时候,原来的永久表就会重现。
对于有连接池(或者永久性连接)的应用最好不要用临时temporary表,因为无法保证每次使用的是同一个连接,以及用完无法保证连接会被及时释放,从而销毁临时表。
Tips 8 :创建新的数据表
备份表结构操作的时候,可以使用下面的语句,备份表的结构,但不备份原有表的数据:
create table tt2 like t2;
当然也可以选择复制原来表中的一些列,一些行和数据
create table ttt2 select name from t2 where id=2;
使用create table ... select 语句的缺点是不会把原有表的所有属性都复制过来,会丢失像AUTO_INCREMENT、UNSIGNED等属性,解决的办法是select部分使用cast()函数强制使用特定的属性。
Tips 9 : 改变表的结构
使用alter table可以改变表的结构,如修改某一列的类型,属性名.....
Alter table table_name modify columnName .... 或者
Alter table table_name change columnName columnName ...
change需要把列定义重写一片的原因是change能够改变列明,而modify不可以。
修改数据表的引擎
Alter table tab_name Engine = engineName;
重命名数据表
Alter table tb1_name RENAME To tb2_name; 或者
Rename table old_name to new_name;
其中,还可以使用Rename table 一次重命名多个数据表
Rename table t1 to tt1, t2 to tt2 ...
使用重命名在数据库之间前移数据表(把sampdb数据库表t迁移到test数据库中)
Alter table sampdb.t rename to test.t; 或者
rename table sampdb.t To test.t
Tips 10 联结查询
内联结:即笛卡尔积的形式,a表的每一项与b表的每一项结合,联结后的数据集巨大,如a(10条),b(20条),c(30条)内联结后的数据集是6000条
外联结:分为左联结和右联结,左联结是左边表的数据全部保留,右边表不存在的就set null,右联结恰恰相反。可以通过外连接查找数据缺值。
上述都是表的横向连接,竖向连接可以使用union进行,uninon默认会剔除重复的结果,若想保留重复的结果可以使用union all
select i1 from t1 uinon select i2 from t2; // 结果中的列明来自第一个select中的列明