MySQL学习笔记2

一. 创建数据库

方法一：使用create database语句

mysql> create database if not exists scott character set utf8 collate utf8_bin;
Query OK, 1 row affected (0.00 sec)


mysql> system ls -l data
ls: cannot access data: No such file or directory
mysql> system pwd
/usr/local/mysql/data
mysql> system ls -l
total 177500
-rw-rw---- 1 mysql mysql       56 Feb  1 06:24 auto.cnf
-rw-rw---- 1 mysql mysql 79691776 Feb  7 17:13 ibdata1
drwx------ 2 mysql mysql     4096 Feb  1 06:21 mysql
-rw-rw---- 1 mysql mysql       57 Feb  1 06:24 mysql-bin.index
drwx------ 2 mysql mysql     4096 Feb  1 06:21 performance_schema
drwx------ 2 mysql mysql     4096 Feb  7 17:16 scott
drwxr-xr-x 2 mysql mysql     4096 Feb  1 22:25 test
-rw-r----- 1 mysql root     32130 Feb  7 06:15 vmhostd.net.err
-rw-rw---- 1 mysql mysql        5 Feb  2 22:22 vmhostd.net.pid
mysql> system cat scott/db.opt
default-character-set=utf8
default-collation=utf8_bin
mysql> 

方法二：手动创建

[root@vmhostd data]# pwd
/usr/local/mysql/data
[root@vmhostd data]# mkdir shopx
[root@vmhostd data]# chmod 700 shopx

[root@vmhostd data]# echo "default-character-set=utf8">shopx/db.opt

[root@vmhostd data]# echo "default-collation=utf8_bin" >>shopx/db.opt
[root@vmhostd data]# ll
total 177504
-rw-rw---- 1 mysql mysql       56 Feb  1 06:24 auto.cnf
-rw-rw---- 1 mysql mysql 79691776 Feb  7 17:13 ibdata1
drwx------ 2 mysql mysql     4096 Feb  1 06:21 mysql
-rw-rw---- 1 mysql mysql    67110 Feb  1 06:21 mysql-bin.000001
-rw-rw---- 1 mysql mysql       57 Feb  1 06:24 mysql-bin.index
drwx------ 2 mysql mysql     4096 Feb  1 06:21 performance_schema
drwx------ 2 mysql mysql     4096 Feb  7 17:16 scott
drwx------ 2 root  root      4096 Feb  7 17:23 shopx
[root@vmhostd data]# chmod 660 shopx/db.opt 
[root@vmhostd data]# ll shopx
total 4
-rw-rw---- 1 root root 54 Feb  7 17:23 db.opt
[root@vmhostd data]# cat shopx/db.opt 
default-character-set=utf8
default-collation=utf8_bin
[root@vmhostd data]# chown -R mysql:mysql shopx

[root@vmhostd data]# mysql -uroot -proot

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| scott              |
| shopx              |
| test               |
+--------------------+

二,修改数据库

mysql> show character set like 'latin1%'; --查看数据库支持的字符集
+---------+----------------------+-------------------+--------+
| Charset | Description          | Default collation | Maxlen |
+---------+----------------------+-------------------+--------+
| latin1  | cp1252 West European | latin1_swedish_ci |      1 |
+---------+----------------------+-------------------+--------+
1 row in set (0.00 sec)


mysql> show collation like 'latin1%'; --查看数据库支持的collation
+-------------------+---------+----+---------+----------+---------+
| Collation         | Charset | Id | Default | Compiled | Sortlen |
+-------------------+---------+----+---------+----------+---------+
| latin1_german1_ci | latin1  |  5 |         | Yes      |       1 |
| latin1_swedish_ci | latin1  |  8 | Yes     | Yes      |       1 |
| latin1_danish_ci  | latin1  | 15 |         | Yes      |       1 |
| latin1_german2_ci | latin1  | 31 |         | Yes      |       2 |
| latin1_bin        | latin1  | 47 |         | Yes      |       1 |
| latin1_general_ci | latin1  | 48 |         | Yes      |       1 |
| latin1_general_cs | latin1  | 49 |         | Yes      |       1 |
| latin1_spanish_ci | latin1  | 94 |         | Yes      |       1 |
+-------------------+---------+----+---------+----------+---------+
8 rows in set (0.00 sec)


mysql> alter database shopx character set latin1 collate latin1_swedish_ci;
Query OK, 1 row affected (0.00 sec)

mysql> status;
--------------
mysql  Ver 14.14 Distrib 5.6.22, for linux-glibc2.5 (i686) using  EditLine wrapper


Connection id: 16
Current database: shopx
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.6.22-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset:latin1
Db     characterset: latin1
Client characterset: utf8
Conn.  characterset: utf8
UNIX socket: /tmp/mysql.sock
Uptime: 4 days 19 hours 25 min 57 sec


Threads: 1  Questions: 1424  Slow queries: 0  Opens: 363  Flush tables: 1  Open tables: 63  Queries per second avg: 0.003
--------------

三. 创建表

mysql> show engines; --查看数据库支持的engine
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

×××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××
mysql> create table if not exists category(iid int,cname varchar(30))engine=MyISAM CHARSET=UTF8; --指定字符集引擎
Query OK, 0 rows affected (0.06 sec)


mysql> show create table category;
+----------+---------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                    |
+----------+---------------------------------------------------------------------------------------------------------------------------------+
| category | CREATE TABLE `category` (
  `iid` int(11) DEFAULT NULL,
  `cname` varchar(30) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+----------+---------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

×××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××

mysql> create table if not exists product(iid int, pname varchar(30)); --使用默认的字符集，引擎
Query OK, 0 rows affected (0.07 sec)


mysql> show create table product;
+---------+----------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                     |
+---------+----------------------------------------------------------------------------------------------------------------------------------+
| product | CREATE TABLE `product` (
  `iid` int(11) DEFAULT NULL,
  `pname` varchar(30) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+---------+----------------------------------------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

×××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××

mysql> create table if not exists product(pid int auto_increment primary key, pname varchar(30)charset utf8 not null, isin varchar(10)unique, descrip text, fulltext(descrip)) default charset=latin1; --指定主键并自增长，指定列字符集，非空，唯一.对text建立全文索引
Query OK, 0 rows affected (0.17 sec)


mysql> show columns from product;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| pid   | int(11)     | NO   | PRI | NULL    | auto_increment |
| pname | varchar(30) | NO   |     | NULL    |                |
| isin  | varchar(10) | YES  | UNI | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)


mysql> show index from product;/show keys from product;--查看索引，会自动创建两个索引，一个主键索引，一个unique列索引
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| product |          0 | PRIMARY  |            1 | pid         | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| product |          0 | isin     |            1 | isin        | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

mysql> system ls -l data/shopx
total 1200
-rw-rw---- 1 mysql mysql     65 Feb  7 17:40 db.opt
-rw-rw---- 1 mysql mysql  98304 Feb  7 20:10 FTS_00000000000003c5_00000000000003d5_INDEX_1.ibd
-rw-rw---- 1 mysql mysql  98304 Feb  7 20:10 FTS_00000000000003c5_00000000000003d5_INDEX_2.ibd
-rw-rw---- 1 mysql mysql  98304 Feb  7 20:10 FTS_00000000000003c5_00000000000003d5_INDEX_3.ibd
-rw-rw---- 1 mysql mysql  98304 Feb  7 20:10 FTS_00000000000003c5_00000000000003d5_INDEX_4.ibd
-rw-rw---- 1 mysql mysql  98304 Feb  7 20:10 FTS_00000000000003c5_00000000000003d5_INDEX_5.ibd
-rw-rw---- 1 mysql mysql  98304 Feb  7 20:10 FTS_00000000000003c5_00000000000003d5_INDEX_6.ibd
-rw-rw---- 1 mysql mysql  98304 Feb  7 20:10 FTS_00000000000003c5_BEING_DELETED_CACHE.ibd
-rw-rw---- 1 mysql mysql  98304 Feb  7 20:10 FTS_00000000000003c5_BEING_DELETED.ibd
-rw-rw---- 1 mysql mysql  98304 Feb  7 20:10 FTS_00000000000003c5_CONFIG.ibd
-rw-rw---- 1 mysql mysql  98304 Feb  7 20:10 FTS_00000000000003c5_DELETED_CACHE.ibd
-rw-rw---- 1 mysql mysql  98304 Feb  7 20:10 FTS_00000000000003c5_DELETED.ibd
-rw-rw---- 1 mysql mysql   8656 Feb  7 20:10 product.frm
-rw-rw---- 1 mysql mysql 131072 Feb  7 20:10 product.ibd

**************************************************************************************************************************************************

根据另一张表来创建一张表，但是这样索引之类的不会被创建过来

mysql> create table product2 as select * from product;

根据另一张表来创建一张空表，索引，分区等会复制过来

mysql> create table emp1 like emp;

四. 创建临时表

×临时表和normal table同名是，temporary table会隐藏normal table,临时表的优先级高于normal table. temporary table值在同一个session中可见，关闭连接temporary table会自动drop掉。临时表在/tmp/目录下建立文件，在master/slave中，临时表会有问题，当slave重启后临时表会丢失。

mysql> create temporary table if not exists t_product_tmp(iid int, pname varchar(30)) engine=memory;
Query OK, 0 rows affected (0.00 sec)


mysql> show tables; --临时表不能看到，如果临时表和normal table同名，会隐藏normal table
+-----------------+
| Tables_in_shopx |
+-----------------+
| category        |
| product         |
+-----------------+
2 rows in set (0.00 sec)


mysql> system ls -l /tmp

-rw-rw----  1 mysql      mysql      8590 Feb  7 19:39 #sql1bea_15_0.frm
-rw-rw----  1 mysql      mysql      8590 Feb  7 19:41 #sql1bea_15_1.frm

mysql> select t1.iid, t2.cname from t_product_tmp t1, t_product_tmp t2 where t1.iid = t2.iid; --临时表不能重复打开
ERROR 1137 (HY000): Can't reopen table: 't1'
mysql> 

五,创建分区的表

1.range分区

mysql> create table emp(eid int primary key,ename varchar(30) not null,hiredate date not null,birth date not null,departcd int not null)
    -> partition by range(eid)(
    -> partition part0 values less than (100),
    -> partition part1 values less than (200),
    -> partition part2 values less than (300),
    -> partition part3 values less than (400)
    -> );
Query OK, 0 rows affected (0.22 sec)

2. hash分区

mysql> create table emp2(eid int primary key,ename varchar(30) not null,hiredate date not null,birth date not null,departcd int not null) partition by hash(eid) partitions 10;
Query OK, 0 rows affected (0.31 sec)

从指定分区查询

mysql> select * from emp partition(part3);

查看分区信息

mysql> select table_name, partition_name, table_rows,tablespace_name from information_schema.partitions where table_name='emp';
+------------+----------------+------------+-----------------+
| table_name | partition_name | table_rows | tablespace_name |
+------------+----------------+------------+-----------------+
| emp        | part0          |         99 | NULL            |
| emp        | part1          |        100 | NULL            |
| emp        | part2          |        100 | NULL            |
| emp        | part3          |        100 | NULL            |
+------------+----------------+------------+-----------------+
4 rows in set (0.00 sec)

1. 外键

create table product(pid int, cid int references xxx(cid), pname varchar(30), primary key(pid));

* MySQL doesn't perform any sort of check to make sure that xxx(cid) actually exists, even table xxx itself.

* MySQL doesn't perform any sort of action on xxx such as deleting rows in response to actions taken on rows in the table which you are defining, in other words, tis syntax indeces noon delete or on update behavior(although you can write an on delete or on update clause as part of thereferecens clause, it is also ignored)

* This syntax creats a column, it doesn't create any sort of index or key