mysql中整理设置__mysql基础操作整理(一)

显示当前数据库

mysql> selectdatabase();+------------+

| database() |

+------------+

| test |

+------------+

1 row in set (0.00 sec)

显示数据库表

mysql>show tables;+----------------+

| Tables_in_test |

+----------------+

| t1 |

+----------------+

1 row in set (0.00 sec)

mysql表复制

//复制表结构

mysql>create table t2 like t1;

Query OK,0 rows affected (0.03sec)

mysql> select *from t1;+------+

| id |

+------+

| 1 |

| 2 |

| 3 |

| 4 |

| 5 |

| 6 |

+------+

6 rows in set (0.01sec)//复制表数据

mysql> insert into t2 select *from t1;

Query OK,6 rows affected (0.00sec)

Records:6 Duplicates: 0 Warnings: 0mysql> select *from t2;+------+

| id |

+------+

| 1 |

| 2 |

| 3 |

| 4 |

| 5 |

| 6 |

+------+

6 rows in set (0.00 sec)

添加索引

//添加主键索引

mysql> alter table t1 add primary key(id);

Query OK,6 rows affected (0.01sec)

Records:6 Duplicates: 0 Warnings: 0

//添加唯一索引

mysql> alter table t1 add column name varchar(30) not null; //给t1表添加一个name列

Query OK, 6 rows affected (0.00sec)

Records:6 Duplicates: 0 Warnings: 0

//查看表信息

mysql>desc t1;+-------+-------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+-------------+------+-----+---------+-------+

| id | int(11) | NO | PRI | 0 | |

| name | varchar(30) | NO | | NULL | |

+-------+-------------+------+-----+---------+-------+

2 rows in set (0.00sec)//清空表数据

mysql>truncate t1;

Query OK,0 rows affected (0.00sec)

mysql> select *from t1;

Empty set (0.00sec)//添加唯一索引

mysql>alter table t1 add unique index t1_name_unique(name);

Query OK,0 rows affected (0.01sec)

Records:0 Duplicates: 0 Warnings: 0

//查看索引

mysql>show index from t1;+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |

+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

| t1 | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | |

| t1 | 0 | t1_name_unique | 1 | name | A | 0 | NULL | NULL | | BTREE | |

+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

2 rows in set (0.00sec)//添加普通索引

mysql> alter table t1 add column age int not null default 0;

Query OK,0 rows affected (0.01sec)

Records:0 Duplicates: 0 Warnings: 0mysql>desc t1->;+-------+-------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+-------------+------+-----+---------+-------+

| id | int(11) | NO | PRI | 0 | |

| name | varchar(30) | NO | UNI | NULL | |

| age | int(11) | NO | | 0 | |

+-------+-------------+------+-----+---------+-------+

3 rows in set (0.00sec)

mysql>alter table t1 add index t1_in_age(age);

Query OK,0 rows affected (0.02sec)

Records:0 Duplicates: 0 Warnings: 0mysql>show index from t1;+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |

+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

| t1 | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | |

| t1 | 0 | t1_name_unique | 1 | name | A | 0 | NULL | NULL | | BTREE | |

| t1 | 1 | t1_in_age | 1 | age | A | NULL | NULL | NULL | | BTREE | |

+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

3 rows in set (0.00 sec)

删除索引

mysql>alter table t1 drop primary key;

mysql>show index from t1;+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |

+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

| t1 | 0 | t1_name_unique | 1 | name | A | 0 | NULL | NULL | | BTREE | |

| t1 | 1 | t1_in_age | 1 | age | A | NULL | NULL | NULL | | BTREE | |

+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+mysql>alter table t1 drop index t1_in_age;

Query OK,0 rows affected (0.00sec)

Records:0 Duplicates: 0 Warnings: 0mysql>alter table t1 drop index t1_name_unique;

Query OK,0 rows affected (0.01sec)

Records:0 Duplicates: 0 Warnings: 0mysql>show index from t1;

Empty set (0.00 sec)

设置字段自增长auto_increment

mysql> alter table t1 modify id int not nullprimary key auto_increment;

Query OK,0 rows affected (0.02sec)

Records:0 Duplicates: 0 Warnings: 0mysql>desc t1;+-------+-------------+------+-----+---------+----------------+

| Field | Type | Null | Key | Default | Extra |

+-------+-------------+------+-----+---------+----------------+

| id | int(11) | NO | PRI | NULL | auto_increment |

| name | varchar(30) | NO | | NULL | |

| age | int(11) | NO | | 0 | |

+-------+-------------+------+-----+---------+----------------+

3 rows in set (0.00 sec)

批量插入数据

mysql> insert into t1(name,age) values("aaa",20),("bbb",30),("cc",18),("abc",23);

Query OK,4 rows affected (0.00sec)

Records:4 Duplicates: 0 Warnings: 0mysql> select *from t1;+----+------+-----+

| id | name | age |

+----+------+-----+

| 1 | aaa | 20 |

| 2 | bbb | 30 |

| 3 | cc | 18 |

| 4 | abc | 23 |

+----+------+-----+

4 rows in set (0.00 sec)

备份数据

mysql> select name,age from t1 into outfile "/tmp/t1.txt";

ERROR1086 (HY000): File '/tmp/t1.txt'already exists

mysql> select name,age from t1 into outfile "/tmp/t1.txt";

Query OK,32 rows affected (0.00 sec)

[root@localhost tmp]# pwd

/tmp

[root@localhost tmp]# ls

ssh-TkEopz2496  ssh-zMKSLp2473  t1.txt  test.sql

清空表数据

mysql>delete from t1;

Query OK,32 rows affected (0.00sec)

mysql> select *from t1;

Empty set (0.00sec)//导入数据

mysql> load data infile '/tmp/t1.txt' into table t1(name,age);

Query OK,32 rows affected, 64 warnings (0.00sec)

Records:32 Deleted: 0 Skipped: 0 Warnings: 32

//清空表

mysql>truncate t1;

Query OK,0 rows affected (0.00sec)//两种清空表的方式在原理上不一样，我们可以看出delete方式的影响行数为32，而truncate则是0，那么也就是说delete是一行一行的删除的，

所以truncate在清楚数据上面比delete方式更高效，并且truncate会是auto_increment的值重置为1

重置auto_increment

mysql> delete from t1 where id > 20;

Query OK,12 rows affected (0.00sec)

mysql> alter table t1 auto_increment=1;

Query OK,20 rows affected (0.01sec)

Records:20 Duplicates: 0 Warnings: 0

load data方式导入数据，这种方式只是导入表数据而不会导入表结构，所以在单纯的数据导入上面更高效,我们可以看看导出文件的内容：

[root@localhost tmp]# catt1.txt

aaa20bbb30

cc 18abc23aaa20bbb30

cc 18abc23aaa20bbb30

cc 18abc23aaa20bbb30

cc 18abc23aaa20bbb30

cc 18abc23aaa20bbb30

cc 18abc23aaa20bbb30

cc 18abc23aaa20bbb30

cc 18abc23

mysql> load data infile '/tmp/t1.txt' into table t1(name,age);

Query OK, 32 rows affected, 64 warnings (0.00sec)

Records: 32 Deleted: 0 Skipped: 0 Warnings: 32

case when语句

mysql> select id,name,age,case when age >= 30 then 'a' when age <=18 then 'b' else 'c'end as ddd from t1;+----+------+-----+-----+

| id | name | age | ddd |

+----+------+-----+-----+

| 1 | aaa | 20 | c |

| 2 | bbb | 30 | a |

| 3 | cc | 18 | b |

| 4 | abc | 23 | c |

| 5 | aaa | 20 | c |

| 6 | bbb | 30 | a |

| 7 | cc | 18 | b |

| 8 | abc | 23 | c |

| 9 | aaa | 20 | c |

| 10 | bbb | 30 | a |

| 11 | cc | 18 | b |

| 12 | abc | 23 | c |

| 13 | aaa | 20 | c |

| 14 | bbb | 30 | a |

| 15 | cc | 18 | b |

| 16 | abc | 23 | c |

| 17 | aaa | 20 | c |

| 18 | bbb | 30 | a |

| 19 | cc | 18 | b |

| 20 | abc | 23 | c |

+----+------+-----+-----+

20 rows in set (0.00 sec)

常用函数：字符串函数

//字符串组合函数

mysql> select concat("hello","mysql") as title;+------------+

| title |

+------------+

| hellomysql |

+------------+

1 row in set (0.00sec)

mysql> select concat("hello","mysql") as title;+------------+

| title |

+------------+

| hellomysql |

+------------+

1 row in set (0.00sec)

mysql> select concat("hello","mysql","aaaa") as title;+----------------+

| title |

+----------------+

| hellomysqlaaaa |

+----------------+

1 row in set (0.00sec)//字符串大小写转换

mysql> select lcase('HELLO MYSQL') as title;+-------------+

| title |

+-------------+

| hello mysql |

+-------------+

1 row in set (0.00sec)

mysql> select ucase('hello mysql') as title;+-------------+

| title |

+-------------+

| HELLO MYSQL |

+-------------+

1 row in set (0.00sec)//返回字符的长度

mysql> select length("hello mysql") as length;+--------+

| length |

+--------+

| 11 |

+--------+

1 row in set (0.00sec)//将字符重复N次

mysql> select repeat('hello mysql,',3);+--------------------------------------+

| repeat('hello mysql,',3) |

+--------------------------------------+

| hello mysql,hello mysql,hello mysql, |

+--------------------------------------+

1 row in set (0.00sec)//替换字符串

mysql> select replace("hello mysql","mysql","php") as rp;+-----------+

| rp |

+-----------+

| hello php |

+-----------+

1 row in set (0.00sec)//截取字符串，注意索引是从1开始

mysql> select substring("hello mysql",1,5) as sub;+-------+

| sub |

+-------+

| hello |

+-------+

1 row in set (0.00sec)//返回字符在列表中的位置

mysql> select find_in_set("a","a,b,c,d");+----------------------------+

| find_in_set("a","a,b,c,d") |

+----------------------------+

| 1 |

+----------------------------+

1 row in set (0.00 sec)

常用函数：数学函数

//10进制转2进制

mysql> select bin(2);+--------+

| bin(2) |

+--------+

| 10 |

+--------+

1 row in set (0.00sec)//向上取整

mysql> select ceiling(1.2);+--------------+

| ceiling(1.2) |

+--------------+

| 2 |

+--------------+

1 row in set (0.00sec)//向下取整

mysql> select floor(1.2);+------------+

| floor(1.2) |

+------------+

| 1 |

+------------+

1 row in set (0.00sec)//获取最大值

mysql> select *,max(age) from t1 ;+----+------+-----+----------+

| id | name | age | max(age) |

+----+------+-----+----------+

| 1 | aaa | 20 | 30 |

+----+------+-----+----------+

1 row in set (0.00sec)//获取最小值

mysql> select *,min(age) from t1;+----+------+-----+----------+

| id | name | age | min(age) |

+----+------+-----+----------+

| 1 | aaa | 20 | 18 |

+----+------+-----+----------+

1 row in set (0.00sec)//获取一个0到1之间的随机数

mysql> selectrand();+-------------------+

| rand() |

+-------------------+

| 0.635864053513728 |

+-------------------+

1 row in set (0.00 sec)

常用函数：日期函数

//获取当前时间的日期部分

mysql> selectcurdate();+------------+

| curdate() |

+------------+

| 2015-02-10 |

+------------+

1 row in set (0.00sec)//获取当前时间的小时部分

mysql> selectcurtime();+-----------+

| curtime() |

+-----------+

| 02:43:08 |

+-----------+

1 row in set (0.00sec)//获取当前时间

mysql> selectnow();+---------------------+

| now() |

+---------------------+

| 2015-02-10 02:43:15 |

+---------------------+

1 row in set (0.00sec)//mysql> select unix_timestamp();

+------------------+

| unix_timestamp() |

+------------------+

| 1423507660 |

+------------------+

1 row in set (0.00sec)//获取当前时间戳

mysql> selectunix_timestamp();+------------------+

| unix_timestamp() |

+------------------+

| 1423507660 |

+------------------+

1 row in set (0.00sec)//时间戳转化为日期

mysql> selectfrom_unixtime(unix_timestamp());+---------------------------------+

| from_unixtime(unix_timestamp()) |

+---------------------------------+

| 2015-02-10 02:49:37 |

+---------------------------------+

1 row in set (0.00sec)//获取时间中的年月日

mysql> selectyear(now());+-------------+

| year(now()) |

+-------------+

| 2015 |

+-------------+

1 row in set (0.00sec)

mysql> selectmonth(now());+--------------+

| month(now()) |

+--------------+

| 2 |

+--------------+

1 row in set (0.00sec)

mysql> selectday(now());+------------+

| day(now()) |

+------------+

| 10 |

+------------+

1 row in set (0.00 sec)