顯示當前數據庫
mysql> select database(); +------------+ | 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.03 sec) mysql> select * from t1; +------+ | id | +------+ | 1 | | 2 | | 3 | | 4 | | 5 | | 6 | +------+ 6 rows in set (0.01 sec) //復制表數據 mysql> insert into t2 select * from t1; Query OK, 6 rows affected (0.00 sec) Records: 6 Duplicates: 0 Warnings: 0 mysql> 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.01 sec)
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.00 sec)
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.00 sec)
//清空表數據
mysql> truncate t1;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t1;
Empty set (0.00 sec)
//添加唯一索引
mysql> alter table t1 add unique index t1_name_unique(name);
Query OK, 0 rows affected (0.01 sec)
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.00 sec)
//添加普通索引
mysql> alter table t1 add column age int not null default 0;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> 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.00 sec)
mysql> alter table t1 add index t1_in_age(age);
Query OK, 0 rows affected (0.02 sec)
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 | |
| 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.00 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> alter table t1 drop index t1_name_unique; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show index from t1; Empty set (0.00 sec)
設置字段自增長auto_increment
mysql> alter table t1 modify id int not null primary key auto_increment; Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> 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.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> 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"; ERROR 1086 (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.00 sec) mysql> select * from t1; Empty set (0.00 sec) //導入數據 mysql> load data infile '/tmp/t1.txt' into table t1(name,age); Query OK, 32 rows affected, 64 warnings (0.00 sec) Records: 32 Deleted: 0 Skipped: 0 Warnings: 32 //清空表 mysql> truncate t1; Query OK, 0 rows affected (0.00 sec) //兩種清空表的方式在原理上不一樣，我們可以看出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.00 sec) mysql> alter table t1 auto_increment=1; Query OK, 20 rows affected (0.01 sec) Records: 20 Duplicates: 0 Warnings: 0
load data方式導入數據，這種方式只是導入表數據而不會導入表結構，所以在單純的數據導入上面更高效,我們可以看看導出文件的內容：
[root@localhost tmp]# cat t1.txt aaa 20 bbb 30 cc 18 abc 23 aaa 20 bbb 30 cc 18 abc 23 aaa 20 bbb 30 cc 18 abc 23 aaa 20 bbb 30 cc 18 abc 23 aaa 20 bbb 30 cc 18 abc 23 aaa 20 bbb 30 cc 18 abc 23 aaa 20 bbb 30 cc 18 abc 23 aaa 20 bbb 30 cc 18 abc 23
mysql> load data infile '/tmp/t1.txt' into table t1(name,age); Query OK, 32 rows affected, 64 warnings (0.00 sec) 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.00 sec)
mysql> select concat("hello","mysql") as title;
+------------+
| title |
+------------+
| hellomysql |
+------------+
1 row in set (0.00 sec)
mysql> select concat("hello","mysql","aaaa") as title;
+----------------+
| title |
+----------------+
| hellomysqlaaaa |
+----------------+
1 row in set (0.00 sec)
//字符串大小寫轉換
mysql> select lcase('HELLO MYSQL') as title;
+-------------+
| title |
+-------------+
| hello mysql |
+-------------+
1 row in set (0.00 sec)
mysql> select ucase('hello mysql') as title;
+-------------+
| title |
+-------------+
| HELLO MYSQL |
+-------------+
1 row in set (0.00 sec)
//返回字符的長度
mysql> select length("hello mysql") as length;
+--------+
| length |
+--------+
| 11 |
+--------+
1 row in set (0.00 sec)
//將字符重復N次
mysql> select repeat('hello mysql,',3);
+--------------------------------------+
| repeat('hello mysql,',3) |
+--------------------------------------+
| hello mysql,hello mysql,hello mysql, |
+--------------------------------------+
1 row in set (0.00 sec)
//替換字符串
mysql> select replace("hello mysql","mysql","php") as rp;
+-----------+
| rp |
+-----------+
| hello php |
+-----------+
1 row in set (0.00 sec)
//截取字符串，注意索引是從1開始
mysql> select substring("hello mysql",1,5) as sub;
+-------+
| sub |
+-------+
| hello |
+-------+
1 row in set (0.00 sec)
//返回字符在列表中的位置
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.00 sec) //向上取整 mysql> select ceiling(1.2); +--------------+ | ceiling(1.2) | +--------------+ | 2 | +--------------+ 1 row in set (0.00 sec) //向下取整 mysql> select floor(1.2); +------------+ | floor(1.2) | +------------+ | 1 | +------------+ 1 row in set (0.00 sec) //獲取最大值 mysql> select *,max(age) from t1 ; +----+------+-----+----------+ | id | name | age | max(age) | +----+------+-----+----------+ | 1 | aaa | 20 | 30 | +----+------+-----+----------+ 1 row in set (0.00 sec) //獲取最小值 mysql> select *,min(age) from t1; +----+------+-----+----------+ | id | name | age | min(age) | +----+------+-----+----------+ | 1 | aaa | 20 | 18 | +----+------+-----+----------+ 1 row in set (0.00 sec) //獲取一個0到1之間的隨機數 mysql> select rand(); +-------------------+ | rand() | +-------------------+ | 0.635864053513728 | +-------------------+ 1 row in set (0.00 sec)
常用函數：日期函數
//獲取當前時間的日期部分 mysql> select curdate(); +------------+ | curdate() | +------------+ | 2015-02-10 | +------------+ 1 row in set (0.00 sec) //獲取當前時間的小時部分 mysql> select curtime(); +-----------+ | curtime() | +-----------+ | 02:43:08 | +-----------+ 1 row in set (0.00 sec) //獲取當前時間 mysql> select now(); +---------------------+ | now() | +---------------------+ | 2015-02-10 02:43:15 | +---------------------+ 1 row in set (0.00 sec) //mysql> select unix_timestamp(); +------------------+ | unix_timestamp() | +------------------+ | 1423507660 | +------------------+ 1 row in set (0.00 sec) //獲取當前時間戳 mysql> select unix_timestamp(); +------------------+ | unix_timestamp() | +------------------+ | 1423507660 | +------------------+ 1 row in set (0.00 sec) //時間戳轉化為日期 mysql> select from_unixtime(unix_timestamp()); +---------------------------------+ | from_unixtime(unix_timestamp()) | +---------------------------------+ | 2015-02-10 02:49:37 | +---------------------------------+ 1 row in set (0.00 sec) //獲取時間中的年月日 mysql> select year(now()); +-------------+ | year(now()) | +-------------+ | 2015 | +-------------+ 1 row in set (0.00 sec) mysql> select month(now()); +--------------+ | month(now()) | +--------------+ | 2 | +--------------+ 1 row in set (0.00 sec) mysql> select day(now()); +------------+ | day(now()) | +------------+ | 10 | +------------+ 1 row in set (0.00 sec)
