mysql必会必知

select distinct CHARACTER_SET_NAME from CHARACTER_SETS limit 12 offset 30;
select distinct CHARACTER_SET_NAME from CHARACTER_SETS limit 30,12;

use information_schema
mysql> select * from CHARACTER_SETS;
+--------------------+----------------------+-----------------------------+--------+
| CHARACTER_SET_NAME | DEFAULT_COLLATE_NAME | DESCRIPTION | MAXLEN |
+--------------------+----------------------+-----------------------------+--------+
| big5 | big5_chinese_ci | Big5 Traditional Chinese | 2 |
| dec8 | dec8_swedish_ci | DEC West European | 1 |

完全限定(同时使用表明和列名)：
注:CHARACTER_SETS表属于information_schema库

mysql> select CHARACTER_SETS.CHARACTER_SET_NAME from CHARACTER_SETS;
select CHARACTER_SETS.CHARACTER_SET_NAME from information_schema.CHARACTER_SETS;

order by排序：
mysql> select * from CHARACTER_SETS order by CHARACTER_SET_NAME;

多列排序
mysql> select * from CHARACTER_SETS order by MAXLEN,CHARACTER_SET_NAME;

降序：
mysql> select * from CHARACTER_SETS order by CHARACTER_SET_NAME desc;

多列降序
mysql> select * from CHARACTER_SETS order by MAXLEN desc,CHARACTER_SET_NAME desc;

mysql> select * from CHARACTER_SETS where MAXLEN between 2 and 4 order by CHARACTER_SET_NAME;
mysql> select * from CHARACTER_SETS where CHARACTER_SET_NAME = 'binary';
mysql> select * from CHARACTER_SETS where MAXLEN = 1 order by CHARACTER_SET_NAME;
单引号用来限定字符串，如果将值和字符串的列进行比较，则需要限定引号，用来与数值列进行比较的值不用引号

空值检查
mysql> select * from CHARACTER_SETS where DESCRIPTION is null;

计算次序：and优先级大于or
mysql> select * from CHARACTER_SETS where MAXLEN = 4 or maxlen = 3 and character_set_name = 'utf32';
+--------------------+----------------------+----------------+--------+
| CHARACTER_SET_NAME | DEFAULT_COLLATE_NAME | DESCRIPTION | MAXLEN |
+--------------------+----------------------+----------------+--------+
| utf8mb4 | utf8mb4_general_ci | UTF-8 Unicode | 4 |
| utf16 | utf16_general_ci | UTF-16 Unicode | 4 |
| utf32 | utf32_general_ci | UTF-32 Unicode | 4 |
+--------------------+----------------------+----------------+--------+
3 rows in set (0.00 sec)

解决；用圆括号明确分组相应的操作符
mysql> select * from CHARACTER_SETS where (MAXLEN = 4 or maxlen = 3) and character_set_name = 'utf32';
+--------------------+----------------------+----------------+--------+
| CHARACTER_SET_NAME | DEFAULT_COLLATE_NAME | DESCRIPTION | MAXLEN |
+--------------------+----------------------+----------------+--------+
| utf32 | utf32_general_ci | UTF-32 Unicode | 4 |
+--------------------+----------------------+----------------+--------+
1 row in set (0.00 sec)

IN操作符用来指定条件范围
mysql> select * from CHARACTER_SETS where maxlen in (2,3) order by CHARACTER_SET_NAME;
+--------------------+----------------------+---------------------------+--------+
| CHARACTER_SET_NAME | DEFAULT_COLLATE_NAME | DESCRIPTION | MAXLEN |
+--------------------+----------------------+---------------------------+--------+
| big5 | big5_chinese_ci | Big5 Traditional Chinese | 2 |
| cp932 | cp932_japanese_ci | SJIS for Windows Japanese | 2 |
| eucjpms | eucjpms_japanese_ci | UJIS for Windows Japanese | 3 |
| euckr | euckr_korean_ci | EUC-KR Korean | 2 |
| gb2312 | gb2312_chinese_ci | GB2312 Simplified Chinese | 2 |
| gbk | gbk_chinese_ci | GBK Simplified Chinese | 2 |
| sjis | sjis_japanese_ci | Shift-JIS Japanese | 2 |
| ucs2 | ucs2_general_ci | UCS-2 Unicode | 2 |
| ujis | ujis_japanese_ci | EUC-JP Japanese | 3 |
| utf8 | utf8_general_ci | UTF-8 Unicode | 3 |
+--------------------+----------------------+---------------------------+--------+
10 rows in set (0.00 sec)

NOT操作符在where字句中有且只要一个功能，就是否定他之后跟的任何条件
mysql> select * from CHARACTER_SETS where maxlen not in (2,3) order by CHARACTER_SET_NAME;

通配符:
mysql> select * from CHARACTER_SETS where CHARACTER_SET_NAME like 'cp%';
mysql> select * from CHARACTER_SETS where CHARACTER_SET_NAME like 'Cp___';
（下划线只匹配一个字符，%匹配0个或多个字符）

正则表达式：regexp
mysql> select * from CHARACTER_SETS where CHARACTER_SET_NAME regexp '125' order by CHARACTER_SET_NAME;
+--------------------+----------------------+--------------------------+--------+
| CHARACTER_SET_NAME | DEFAULT_COLLATE_NAME | DESCRIPTION | MAXLEN |
+--------------------+----------------------+--------------------------+--------+
| cp1250 | cp1250_general_ci | Windows Central European | 1 |
| cp1251 | cp1251_general_ci | Windows Cyrillic | 1 |
| cp1256 | cp1256_general_ci | Windows Arabic | 1 |
| cp1257 | cp1257_general_ci | Windows Baltic | 1 |
+--------------------+----------------------+--------------------------+--------+
4 rows in set (0.00 sec)

mysql> select * from CHARACTER_SETS where CHARACTER_SET_NAME regexp '1250';
+--------------------+----------------------+--------------------------+--------+
| CHARACTER_SET_NAME | DEFAULT_COLLATE_NAME | DESCRIPTION | MAXLEN |
+--------------------+----------------------+--------------------------+--------+
| cp1250 | cp1250_general_ci | Windows Central European | 1 |
+--------------------+----------------------+--------------------------+--------+
1 row in set (0.00 sec)

mysql> select * from CHARACTER_SETS where CHARACTER_SET_NAME like '1250';
Empty set (0.00 sec)

mysql> select * from CHARACTER_SETS where CHARACTER_SET_NAME like '%1250';
+--------------------+----------------------+--------------------------+--------+
| CHARACTER_SET_NAME | DEFAULT_COLLATE_NAME | DESCRIPTION | MAXLEN |
+--------------------+----------------------+--------------------------+--------+
| cp1250 | cp1250_general_ci | Windows Central European | 1 |
+--------------------+----------------------+--------------------------+--------+
1 row in set (0.00 sec)

binary用来区分大小写
mysql> select * from CHARACTER_SETS where CHARACTER_SET_NAME regexp binary 'cp';
+--------------------+----------------------+---------------------------+--------+
| CHARACTER_SET_NAME | DEFAULT_COLLATE_NAME | DESCRIPTION | MAXLEN |
+--------------------+----------------------+---------------------------+--------+
| cp850 | cp850_general_ci | DOS West European | 1 |
| cp1250 | cp1250_general_ci | Windows Central European | 1 |
| cp866 | cp866_general_ci | DOS Russian | 1 |
| cp852 | cp852_general_ci | DOS Central European | 1 |
| cp1251 | cp1251_general_ci | Windows Cyrillic | 1 |
| cp1256 | cp1256_general_ci | Windows Arabic | 1 |
| cp1257 | cp1257_general_ci | Windows Baltic | 1 |
| cp932 | cp932_japanese_ci | SJIS for Windows Japanese | 2 |
+--------------------+----------------------+---------------------------+--------+
8 rows in set (0.00 sec)

mysql> select * from CHARACTER_SETS where CHARACTER_SET_NAME regexp binary 'Cp';
Empty set (0.00 sec

'|'是正则表达式的or操作符
mysql> select * from CHARACTER_SETS where CHARACTER_SET_NAME regexp binary 'cp850|cp852|cp932';
+--------------------+----------------------+---------------------------+--------+
| CHARACTER_SET_NAME | DEFAULT_COLLATE_NAME | DESCRIPTION | MAXLEN |
+--------------------+----------------------+---------------------------+--------+
| cp850 | cp850_general_ci | DOS West European | 1 |
| cp852 | cp852_general_ci | DOS Central European | 1 |
| cp932 | cp932_japanese_ci | SJIS for Windows Japanese | 2 |
+--------------------+----------------------+---------------------------+--------+
3 rows in set (0.00 sec)

mysql> select * from CHARACTER_SETS where MAXLEN regexp '[23]';
mysql> select * from CHARACTER_SETS where MAXLEN regexp '2|3';
mysql> select * from CHARACTER_SETS where MAXLEN regexp '[^23]';（^取否）

mysql> select * from CHARACTER_SETS where MAXLEN regexp '[1234]';
mysql> select * from CHARACTER_SETS where MAXLEN regexp '[1-4]';

匹配特殊字符(\\)
mysql> select * from CHARACTER_SETS where MAXLEN regexp '\\.';

拼接字段：
select Concat(CHARACTER_SET_NAME,'(',DESCRIPTION,')') from CHARACTER_SETS;

可通过rtrim删除右侧多余的空格来整理数据（ltrim去左边的空格）
mysql> select concat(rtrim(CHARACTER_SET_NAME) ,'(', rtrim(DESCRIPTION), ')') from CHARACTER_SETS;

 

执行算术计算
mysql> select COLLATION_NAME,ID,SORTLEN,ID*SORTLEN as expanded_num from COLLATIONS limit 3;
+-----------------+----+---------+--------------+
| COLLATION_NAME | ID | SORTLEN | expanded_num |
+-----------------+----+---------+--------------+
| big5_chinese_ci | 1 | 1 | 1 |
| big5_bin | 84 | 1 | 84 |
| dec8_swedish_ci | 3 | 1 | 3 |
+-----------------+----+---------+--------------+
3 rows in set (0.00 sec)

mysql> select COLLATION_NAME,ID,SORTLEN,ID*SORTLEN as expanded_num from COLLATIONS;
+--------------------------+-----+---------+--------------+
| COLLATION_NAME | ID | SORTLEN | expanded_num |
+--------------------------+-----+---------+--------------+
| big5_chinese_ci | 1 | 1 | 1 |
| big5_bin | 84 | 1 | 84 |
| dec8_swedish_ci | 3 | 1 | 3 |
| dec8_bin | 69 | 1 | 69 |
| cp850_general_ci | 4 | 1 | 4 |
| cp850_bin | 80 | 1 | 80 |
| hp8_english_ci | 6 | 1 | 6 |
| hp8_bin | 72 | 1 | 72 |

 

mysql> select id,IS_COMPILED,DESCRIPTION,maxlen,SORTLEN from CHARACTER_SETS,COLLATIONS where CHARACTER_SETS.CHARACTER_SET_NAME = COLLATIONS.CHARACTER_SET_NAME order by maxlen,sortlen;

内部联结（和上句结果一样）
mysql> select id,IS_COMPILED,DESCRIPTION,maxlen,SORTLEN from CHARACTER_SETS inner join COLLATIONS on CHARACTER_SETS.CHARACTER_SET_NAME = COLLATIONS.CHARACTER_SET_NAME order by maxlen,sortlen;

联结多个表：
mysql> select COLUMN_NAME,ORDINAL_POSITION,ID,IS_DEFAULT,DESCRIPTION from KEY_COLUMN_USAGE,COLLATIONS,CHARACTER_SETS where KEY_COLUMN_USAGE.ORDINAL_POSITION = COLLATIONS.SORTLEN and COLLATIONS.CHARACTER_SET_NAME = CHARACTER_SETS.CHARACTER_SET_NAME and DESCRIPTION = 'UJIS for Windows Japanese' limit 5;
+------------------+------------------+----+------------+---------------------------+
| COLUMN_NAME | ORDINAL_POSITION | ID | IS_DEFAULT | DESCRIPTION |
+------------------+------------------+----+------------+---------------------------+
| Host | 1 | 97 | Yes | UJIS for Windows Japanese |
| Host | 1 | 97 | Yes | UJIS for Windows Japanese |
| db | 1 | 97 | Yes | UJIS for Windows Japanese |
| name | 1 | 97 | Yes | UJIS for Windows Japanese |
| help_category_id | 1 | 97 | Yes | UJIS for Windows Japanese |
+------------------+------------------+----+------------+---------------------------+
5 rows in set (0.01 sec)

COLLATION_NAME,DESCRIPTION,MAXLEN

mysql> SELECT COLLATION_NAME,DESCRIPTION from character_sets as c1, COLLATIONS as c2 where c1.maxlen = c2.ID and c2.ID = '1';

mysql> select character_sets.maxlen,COLLATIONS.id from character_sets inner join COLLATIONS on character_sets.maxlen = COLLATIONS.id;

mysql> select character_sets.maxlen,COLLATIONS.id from character_sets left outer join COLLATIONS on character_sets.maxlen = COLLATIONS.id order by maxlen;

mysql> select character_sets.maxlen,COLLATIONS.id from character_sets right outer join COLLATIONS on character_sets.maxlen = COLLATIONS.id order by maxlen;

select ID,COLLATION_NAME,SORTLEN FROM COLLATIONS WHERE SORTLEN BETWEEN 2 AND 5 UNION all select ID,COLLATION_NAME,SORTLEN FROM COLLATIONS WHERE ERE SORTLEN > 2 ;

启用全文本搜索支持
mysql> create table producnotes (note_id int not null auto_increment,pro_id char(10) not null,note_date datetime not null,note_text text null,Primary key(note_id),fulltext(note_text)) engine=MyISAM;
Query OK, 0 rows affected (0.04 sec)

 

 

mysql> create table customers(cust_id int not null auto_increment, cust_name char(50) not null , cust_address char(50) null ,cust_city char(50) null, cust_state char(5) null, cust_zip char(10) null, cust_country char(50) null, cust_contact char(50) null, cust_email char(255) null,primary key (cust_id)) engine=Innodb;
Query OK, 0 rows affected (1.27 sec)

mysql> insert into customers(cust_name, cust_address ,cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email) values('Pep E. LaPew','100 Main street', 'los angeles', 'CA', '90046', 'USA', NULL,NULL);
Query OK, 1 row affected (0.01 sec)

mysql> select * from customers;
+---------+--------------+-----------------+-------------+------------+----------+--------------+--------------+------------+
| cust_id | cust_name | cust_address | cust_city | cust_state | cust_zip | cust_country | cust_contact | cust_email |
+---------+--------------+-----------------+-------------+------------+----------+--------------+--------------+------------+
| 1 | Pep E. LaPew | 100 Main street | los angeles | CA | 90046 | USA | NULL | NULL |
+---------+--------------+-----------------+-------------+------------+----------+--------------+--------------+------------+
1 row in set (0.00 sec)

mysql> create table orders(
-> order_num int not null auto_increment,
-> order_date datetime not null,
-> order_id int not null,
-> primary key(order_num))
-> engine=Innodb;
Query OK, 0 rows affected (0.14 sec)

mysql> create table vendors(
-> vend_id int not null AUTO_INCREMENT,
-> vend_name char(50) not null,
-> vend_address char(50) not null,
-> vend_city char(50) null,
-> vend_state char(50) null,
-> vend_zip char(50) null,
-> vend_country char(50) null,
-> primary key (vend_id))
-> ENGINE=Innodb;
Query OK, 0 rows affected (0.16 sec)

mysql> create table orderitems(
-> order_num int not null,
-> order_item int not null,
-> pro_id char(50) not null,
-> quantity int not null,
-> item_price decimal(8,2) not null,
-> primary key(order_num,order_item))
-> ENGINE=InnoDB;
Query OK, 0 rows affected (0.22 sec)

mysql> alter table vendors add vend_phone char(20);
Query OK, 0 rows affected (0.18 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE orderitems ADD CONSTRAINT fk_ordertems_orders FOREIGN KEY (order_num) REFERENCES orders (order_num);
Query OK, 0 rows affected (0.16 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> insert into customers values(null,'simon','58 street','beijing','CA','10008','CHINA','185xxxxxx','yongsan');
Query OK, 1 row affected (0.07 sec)

mysql> select * from customers;
+---------+--------------+-----------------+-------------+------------+----------+--------------+--------------+------------+
| cust_id | cust_name | cust_address | cust_city | cust_state | cust_zip | cust_country | cust_contact | cust_email |
+---------+--------------+-----------------+-------------+------------+----------+--------------+--------------+------------+
| 1 | Pep E. LaPew | 100 Main street | los angeles | CA | 90046 | USA | NULL | NULL |
| 2 | simon | 58 street | beijing | CA | 10008 | CHINA | 185xxxxxx | yongsan |
+---------+--------------+-----------------+-------------+------------+----------+--------------+--------------+------------+
2 rows in set (0.00 sec)

mysql> insert into customers(cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country,cust_contact,cust_email) values('alex','32 street','shanghai','CA','10086','CHINA','183xxxxxx','lys');
Query OK, 1 row affected (0.02 sec)

mysql> select * from customers;
+---------+--------------+-----------------+-------------+------------+----------+--------------+--------------+------------+
| cust_id | cust_name | cust_address | cust_city | cust_state | cust_zip | cust_country | cust_contact | cust_email |
+---------+--------------+-----------------+-------------+------------+----------+--------------+--------------+------------+
| 1 | Pep E. LaPew | 100 Main street | los angeles | CA | 90046 | USA | NULL | NULL |
| 2 | simon | 58 street | beijing | CA | 10008 | CHINA | 185xxxxxx | yongsan |
| 3 | alex | 32 street | shanghai | CA | 10086 | CHINA | 183xxxxxx | lys |
+---------+--------------+-----------------+-------------+------------+----------+--------------+--------------+------------+
3 rows in set (0.00 sec)

mysql> insert into customers(cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country,cust_contact,cust_email) values('a1','33 street','shanghai','CA','10386','JAPAN','123xxxxxx','LLLL'); insert into customers(cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country,cust_contact,cust_email) values('YS','33 street','SHanghai','CD','10010','HK','111xxxxxx','ly1s');
Query OK, 1 row affected (0.02 sec)

Query OK, 1 row affected (0.01 sec)

mysql> update customers set cust_email = 'sina@staff' where cust_id = 4;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from customers;
+---------+--------------+-----------------+-------------+------------+----------+--------------+--------------+------------+
| cust_id | cust_name | cust_address | cust_city | cust_state | cust_zip | cust_country | cust_contact | cust_email |
+---------+--------------+-----------------+-------------+------------+----------+--------------+--------------+------------+
| 1 | Pep E. LaPew | 100 Main street | los angeles | CA | 90046 | USA | NULL | NULL |
| 2 | simon | 58 street | beijing | CA | 10008 | CHINA | 185xxxxxx | yongsan |
| 3 | alex | 32 street | shanghai | CA | 10086 | CHINA | 183xxxxxx | lys |
| 4 | a1 | 33 street | shanghai | CA | 10386 | JAPAN | 123xxxxxx | sina@staff |
| 5 | YS | 33 street | SHanghai | CD | 10010 | HK | 111xxxxxx | ly1s |
+---------+--------------+-----------------+-------------+------------+----------+--------------+--------------+------------+
5 rows in set (0.00 sec)

mysql> update customers set cust_city = 'hefei', cust_state = 'HR' where cust_id = 4;
Query OK, 1 row affected (0.07 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from customers;
+---------+--------------+-----------------+-------------+------------+----------+--------------+--------------+------------+
| cust_id | cust_name | cust_address | cust_city | cust_state | cust_zip | cust_country | cust_contact | cust_email |
+---------+--------------+-----------------+-------------+------------+----------+--------------+--------------+------------+
| 1 | Pep E. LaPew | 100 Main street | los angeles | CA | 90046 | USA | NULL | NULL |
| 2 | simon | 58 street | beijing | CA | 10008 | CHINA | 185xxxxxx | yongsan |
| 3 | alex | 32 street | shanghai | CA | 10086 | CHINA | 183xxxxxx | lys |
| 4 | a1 | 33 street | hefei | HR | 10386 | JAPAN | 123xxxxxx | sina@staff |
| 5 | YS | 33 street | SHanghai | CD | 10010 | HK | 111xxxxxx | ly1s |
+---------+--------------+-----------------+-------------+------------+----------+--------------+--------------+------------+
5 rows in set (0.00 sec)

 

视图：
mysql> select note_text,cust_name,cust_city,cust_email from producnotes, customers where note_id = cust_id;
+-----------+--------------+-------------+------------+
| note_text | cust_name | cust_city | cust_email |
+-----------+--------------+-------------+------------+
| test.txt | Pep E. LaPew | los angeles | NULL |
| test1.txt | simon | beijing | yongsan |
| test3.txt | alex | shanghai | lys |
| test4.txt | a1 | hefei | sina@staff |
| test5.txt | YS | SHanghai | ly1s |
+-----------+--------------+-------------+------------+
5 rows in set (0.00 sec)

mysql> CREATE VIEW PRODUTCUSTOMERS AS select note_text,cust_name,cust_city,cust_email from producnotes, customers where note_id = cust_id;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT * FROM PRODUTCUSTOMERS;
+-----------+--------------+-------------+------------+
| note_text | cust_name | cust_city | cust_email |
+-----------+--------------+-------------+------------+
| test.txt | Pep E. LaPew | los angeles | NULL |
| test1.txt | simon | beijing | yongsan |
| test3.txt | alex | shanghai | lys |
| test4.txt | a1 | hefei | sina@staff |
| test5.txt | YS | SHanghai | ly1s |
+-----------+--------------+-------------+------------+
5 rows in set (0.00 sec)

管理事务处理：
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> DELETE FROM producnotes;
Query OK, 1 row affected (0.00 sec)

mysql> select * from producnotes;
Empty set (0.00 sec)

mysql> ROLLBACK;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> select * from producnotes;

mysql> show collation;
mysql> show character set;

mysql> show variables like 'character%';
+--------------------------+------------------------------------+
| Variable_name | Value |
+--------------------------+------------------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/local/mysql55/share/charsets/ |
+--------------------------+------------------------------------+
8 rows in set (0.00 sec

mysql> show variables like 'collation%';
+----------------------+-----------------+
| Variable_name | Value |
+----------------------+-----------------+
| collation_connection | utf8_general_ci |
| collation_database | utf8_general_ci |
| collation_server | utf8_general_ci |
+----------------------+-----------------+
3 rows in set (0.00 sec)