MYSQL完全手册学习笔记（第八章）

使用数据库和表


创建数据库

mysql> create database db1;
Query OK, 1 row affected (0.01 sec)

不允许含有\,/,:,*,?,”,<,>这些符号，数据库的名字不能超过64个字符

两个数据库的名字不能相同

mysql> use test
Database changed

选择需要的数据库

删除数据库
mysql> drop database db1;
Query OK, 0 rows affected (0.00 sec)

创建表

mysql> create table user(id int(4),name varchar(50),tel varchar(50));
Query OK, 0 rows affected (0.06 sec)


Null 和not null 修饰符
mysql> create table user(id int(4),name varchar(20) not null,pass varchar(20) n
ot null);
Query OK, 0 rows affected (0.06 sec)


Auto_increment 修饰符
自动增加1

MYSQL中的表只能有一个auto_increment字段，而且这个字段必须被定义为键


索引


P158——P163 空

主键
可以使用primary key 约束为一个表指定主键。在以俄国设计好的数据库中，主键对每条记录都是不变的、唯一的标识符。如果一个键被声明为主键，主键内的值很少被修改




mysql> use test
Database changed
mysql> create table firewall(
-> host varchar(11) not null,
-> port smallint(4) not null,
-> access enum('deny','allow') not null,
-> primary key(host,port)
-> );
Query OK, 0 rows affected (0.08 sec)

主键约束可以用于一个单独的字段，也可以用于多字段的组合。


mysql> insert into firewall(host,port,access) values ('202.69.63.2','21','fe');
ERROR 1265 (01000): Data truncated for column 'access' at row 1
mysql> insert into firewall(host,port,access) values ('202.69.63.2',21,'fe');
ERROR 1265 (01000): Data truncated for column 'access' at row 1
mysql> insert into firewall(host,port,access) values ('202.69.63.2',21,'deny');
Query OK, 1 row affected (0.05 sec)

mysql> insert into firewall(host,pot,access) values ('101.102.0.3',22,'deny');
ERROR 1054 (42S22): Unknown column 'pot' in 'field list'
mysql> insert into firewall(host,port,access) values ('101.102.0.3',22,'deny');
Query OK, 1 row affected (0.01 sec)

外键是管理关系的 关系中所有的边中具有相同含义的字段作为公共部分来连接不同表中的记录。外键关系可以是一对一的，也可以是一对多的

表类型
MyISAM表类型

优化了压缩比例和速度，可以在不同的操作系统和平台之间进行移植
这种格式支持大表文件（大于4GB）并且允许对blog和text列进行索引

ISAM表类型
不大于4G 不能在不同的平台移植 容易分裂

HEAP 表类型等不常用的就暂时忽略一下了


InniDB类型
比较很完善的表的格式

复制表


mysql> use test
Database changed
mysql> select * from chengji ;
+----+------+------+---------+------------+
| id | name | math | physics | literature |
+----+------+------+---------+------------+
| 1 | john | 60 | 37 | 45 |
| 2 | jim | 96 | 89 | 92 |
| 3 | bill | 65 | 12 | 57 |
| 4 | hary | 69 | 85 | 12 |
+----+------+------+---------+------------+
4 rows in set (0.00 sec)

mysql> create table chengji2 select * from chengji;
Query OK, 4 rows affected (0.34 sec)
Records: 4 Duplicates: 0 Warnings: 0

同理 可以根据查询到的字段 和需要的条件 来创建新的表

修改表

ADD drop alter change


mysql> create table members (mid int(2), mima char(8),mpass varchar(25));
Query OK, 0 rows affected (0.86 sec)

mysql> alter table members add email varchar(255) not null;
Query OK, 0 rows affected (0.33 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> descrire members;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'descr
ire members' at line 1
mysql> describe members;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| mid | int(2) | YES | | NULL | |
| mima | char(8) | YES | | NULL | |
| mpass | varchar(25) | YES | | NULL | |
| email | varchar(255) | NO | | NULL | |
+-------+--------------+------+-----+---------+-------+
4 rows in set (0.03 sec)

mysql> alter table members change mid id int(8) auto_increment unique;
Query OK, 0 rows affected (0.31 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> describe members;
+-------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+----------------+
| id | int(8) | NO | PRI | NULL | auto_increment |
| mima | char(8) | YES | | NULL | |
| mpass | varchar(25) | YES | | NULL | |
| email | varchar(255) | NO | | NULL | |
+-------+--------------+------+-----+---------+----------------+
4 rows in set (0.05 sec)

mysql> alter table members drop mpass;
Query OK, 0 rows affected (0.33 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> describe members;
+-------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+----------------+
| id | int(8) | NO | PRI | NULL | auto_increment |
| mima | char(8) | YES | | NULL | |
| email | varchar(255) | NO | | NULL | |
+-------+--------------+------+-----+---------+----------------+
3 rows in set (0.05 sec)


mysql> alter table members rename to mem;
Query OK, 0 rows affected (3.23 sec)

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

mysql> select * from members;
ERROR 1146 (42S02): Table 'test.members' doesn't exist
mysql>


mysql> drop table mem;

mysql> drop table if exists mem;
Query OK, 0 rows affected (0.05 sec)


获得数据库、表、字段和索引的信息


mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| 2 |
| etdao |
| mysql |
| test |
+--------------------+
5 rows in set (0.00 sec)


mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| birthday |
| chengji |
| chengji2 |
| data |
| data2 |
| firewall |
| library |
| toys |
| user |
+----------------+
9 rows in set (0.36 sec)

mysql> describe chengji;
+------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(45) | YES | | NULL | |
| math | float | YES | | NULL | |
| physics | float | YES | | NULL | |
| literature | float | YES | | NULL | |
+------------+------------------+------+-----+---------+----------------+
5 rows in set (0.09 sec)