1.创建,插入,查看,备份,恢复
Microsoft Windows XP [版本 5.1.2600]
(C) 版权所有 1985-2001 Microsoft Corp.
C:\Documents and Settings\Administrator>mysql -uroot -p
Enter password: ****
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.22-community-nt
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| test |
+--------------------+
3 rows in set (0.08 sec)
mysql> create database mydb2 charset utf8;
Query OK, 1 row affected (0.06 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mydb2 |
| mysql |
| test |
+--------------------+
4 rows in set (0.00 sec)
mysql> create database mydb3 charset utf8 collate utf8_general_ci;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mydb2 |
| mydb3 |
| mysql |
| test |
+--------------------+
5 rows in set (0.00 sec)
mysql> show create database mydb3;
+----------+----------------------------------------------------------------+
| Database | Create Database |
+----------+----------------------------------------------------------------+
| mydb3 | CREATE DATABASE `mydb3` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+----------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> drop database mydb2;
Query OK, 0 rows affected (0.28 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mydb3 |
| mysql |
| test |
+--------------------+
4 rows in set (0.00 sec)
mysql> use mydb3;
Database changed
mysql> create table test(
-> id int unsigned auto_increment primary key,
-> username char(20) not null,
-> password char(20) not null,
-> description varchar(50)) engine=InnoDB charset=utf8;
Query OK, 0 rows affected (0.34 sec)
mysql> insert into test values(null,'Xiao Bai','6395749','Fight For Beijing!');
Query OK, 1 row affected (0.09 sec)
mysql> show create table test;
+-------+-----------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
------+
| Table | Create Table
|
+-------+-----------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
------+
| test | CREATE TABLE `test` (
`id` int(10) unsigned NOT NULL auto_increment,
`username` char(20) NOT NULL,
`password` char(20) NOT NULL,
`description` varchar(50) default NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-----------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
------+
1 row in set (0.05 sec)
mysql> select * from test;
+----+----------+----------+--------------------+
| id | username | password | description |
+----+----------+----------+--------------------+
| 1 | Xiao Bai | 6395749 | Fight For Beijing! |
+----+----------+----------+--------------------+
1 row in set (0.01 sec)
重开一个命令行窗口备份:mysqldump是命令行指令!
Microsoft Windows XP [版本 5.1.2600]
(C) 版权所有 1985-2001 Microsoft Corp.
C:\Documents and Settings\Administrator>mysqldump -uroot -p mydb3>c:\XiaoBai.sql
Enter password: ****
mysqldump: mysqldump: Couldn't execute 'SHOW TRIGGERS LIKE 'test'': Can't create
/write to file 'C:\WINDOWS\TEMP\#sql_9c_0.MYI' (Errcode: 13) (1)
显示出现了一个问题,但已成功备份出sql文件,上网查可能是mysql版本问题或操作系统设置问题,待解决:
备份出的文件:
-- MySQL dump 10.10
--
-- Host: localhost Database: mydb3
-- ------------------------------------------------------
-- Server version 5.0.22-community-nt
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Table structure for table `test`
--
DROP TABLE IF EXISTS `test`;
CREATE TABLE `test` (
`id` int(10) unsigned NOT NULL auto_increment,
`username` char(20) NOT NULL,
`password` char(20) NOT NULL,
`description` varchar(50) default NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
-- Dumping data for table `test`
--
/*!40000 ALTER TABLE `test` DISABLE KEYS */;
LOCK TABLES `test` WRITE;
INSERT INTO `test` VALUES (1,'Xiao Bai','6395749','Fight For Beijing!');
UNLOCK TABLES;
/*!40000 ALTER TABLE `test` ENABLE KEYS */;
恢复:只能恢复数据,所以要先创建库:
mysql> drop database mydb3;
Query OK, 1 row affected (0.11 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| test |
+--------------------+
3 rows in set (0.02 sec)
mysql> create database mydb3;
Query OK, 1 row affected (0.00 sec)
mysql> use mydb3;
Database changed
mysql> source c:\XiaoBai.sql
Query OK, 0 rows affected (0.03 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.11 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 1 row affected (0.01 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mydb3 |
| mysql |
| test |
+--------------------+
4 rows in set (0.00 sec)
mysql> show create table test;
+-------+-----------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
------+
| Table | Create Table
|
+-------+-----------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
------+
| test | CREATE TABLE `test` (
`id` int(10) unsigned NOT NULL auto_increment,
`username` char(20) NOT NULL,
`password` char(20) NOT NULL,
`description` varchar(50) default NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-----------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
------+
1 row in set (0.00 sec)
mysql> select * from test;
+----+----------+----------+--------------------+
| id | username | password | description |
+----+----------+----------+--------------------+
| 1 | Xiao Bai | 6395749 | Fight For Beijing! |
+----+----------+----------+--------------------+
1 row in set (0.00 sec)
mysql>
恢复库的另一种办法:同样是windows命令:
mysql -uroot -proot mydb3<c:\XiaoBai.sql
2.表相关
Microsoft Windows XP [版本 5.1.2600]
(C) 版权所有 1985-2001 Microsoft Corp.
C:\Documents and Settings\Administrator>mysql -uroot -p
Enter password: ****
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.22-community-nt
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mydb3 |
| mysql |
| test |
+--------------------+
4 rows in set (0.11 sec)
mysql> use mydb3;
Database changed
mysql> show tables;
+-----------------+
| Tables_in_mydb3 |
+-----------------+
| test |
+-----------------+
1 row in set (0.01 sec)
mysql> desc test;
+-------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| username | char(20) | NO | | NULL | |
| password | char(20) | NO | | NULL | |
| description | varchar(50) | YES | | NULL | |
+-------------+------------------+------+-----+---------+----------------+
4 rows in set (0.23 sec)
mysql> describe test;
+-------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| username | char(20) | NO | | NULL | |
| password | char(20) | NO | | NULL | |
| description | varchar(50) | YES | | NULL | |
+-------------+------------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)
mysql> show columns from test;
+-------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| username | char(20) | NO | | NULL | |
| password | char(20) | NO | | NULL | |
| description | varchar(50) | YES | | NULL | |
+-------------+------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql> alter table test add gender char(1) after password;
Query OK, 1 row affected (0.28 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> alter table test add image blob;
Query OK, 1 row affected (0.14 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> desc test;
+-------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| username | char(20) | NO | | NULL | |
| password | char(20) | NO | | NULL | |
| gender | char(1) | YES | | NULL | |
| description | varchar(50) | YES | | NULL | |
| image | blob | YES | | NULL | |
+-------------+------------------+------+-----+---------+----------------+
6 rows in set (0.02 sec)
mysql> alter table test modify password varchar(30);
Query OK, 1 row affected (0.13 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> alter table test modify username varchar(30);
Query OK, 1 row affected (0.14 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> alter table test drop gender;
Query OK, 1 row affected (0.14 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> desc test;
+-------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(30) | YES | | NULL | |
| password | varchar(30) | YES | | NULL | |
| description | varchar(50) | YES | | NULL | |
| image | blob | YES | | NULL | |
+-------------+------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
mysql> rename table test to user;
Query OK, 0 rows affected (0.03 sec)
mysql> show tables;
+-----------------+
| Tables_in_mydb3 |
+-----------------+
| user |
+-----------------+
1 row in set (0.00 sec)
mysql>
mysql> alter table user character set utf8;
Query OK, 1 row affected (0.16 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> alter table user charset utf8;
Query OK, 1 row affected (0.14 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> show create table user;
+-------+-----------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
------------------------------------+
| Table | Create Table
|
+-------+-----------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
------------------------------------+
| user | CREATE TABLE `user` (
`id` int(10) unsigned NOT NULL auto_increment,
`username` varchar(30) default NULL,
`password` varchar(30) default NULL,
`description` varchar(50) default NULL,
`image` blob,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-----------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
------------------------------------+
1 row in set (0.00 sec)
mysql> alter table user change column username username varchar(60);
Query OK, 1 row affected (0.14 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> \s
--------------
mysql Ver 14.12 Distrib 5.0.22, for Win32 (ia32)
Connection id: 1
Current database: mydb3
Current user: root@localhost
SSL: Not in use
Using delimiter: ;
Server version: 5.0.22-community-nt
Protocol version: 10
Connection: localhost via TCP/IP
Server characterset: utf8
Db characterset: utf8
Client characterset: utf8
Conn. characterset: utf8
TCP port: 3306
Uptime: 1 hour 16 min 10 sec
Threads: 1 Questions: 24 Slow queries: 0 Opens: 25 Flush tables: 1 Open tab
les: 0 Queries per second avg: 0.005
--------------
3.乱码问题:显式通知mysql码表-------->通知以gb2312编码存入,但库、表其实还是创建时的码表,要正确显示又要设置按gb2312显示,并且重开窗口还是原来的样子,想一劳永逸,一开始创建时就要指定码表!
mysql> show variables like 'chara%';
+--------------------------+----------------------------------------------------
-----+
| 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 | F:\Program Files\MySQL\MySQL Server 5.0\share\chars
ets\ |
+--------------------------+----------------------------------------------------
-----+
8 rows in set (0.03 sec)
mysql> set character_set_client=gb2312;
Query OK, 0 rows affected (0.08 sec)
mysql> show variables like 'chara%';
+--------------------------+----------------------------------------------------
-----+
| Variable_name | Value
|
+--------------------------+----------------------------------------------------
-----+
| character_set_client | gb2312
|
| 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 | F:\Program Files\MySQL\MySQL Server 5.0\share\chars
ets\ |
+--------------------------+----------------------------------------------------
-----+
8 rows in set (0.00 sec)
mysql> insert into user(id,username) values('2','小白');
Query OK, 1 row affected (0.06 sec)
mysql> select * from user;
+----+----------+----------+--------------------+-------+
| id | username | password | description | image |
+----+----------+----------+--------------------+-------+
| 1 | Xiao Bai | 6395749 | Fight For Beijing! | NULL |
| 2 | 灏忕櫧 | NULL | NULL | NULL |
+----+----------+----------+--------------------+-------+
2 rows in set (0.00 sec)
mysql> set charset gb2312;
Query OK, 0 rows affected (0.02 sec)
mysql> select * from user;
+----+----------+----------+--------------------+-------+
| id | username | password | description | image |
+----+----------+----------+--------------------+-------+
| 1 | Xiao Bai | 6395749 | Fight For Beijing! | NULL |
| 2 | 小白 | NULL | NULL | NULL |
+----+----------+----------+--------------------+-------+
2 rows in set (0.02 sec)
mysql>
mysql> show variables like 'chara%';
+--------------------------+----------------------------------------------------
-----+
| Variable_name | Value
|
+--------------------------+----------------------------------------------------
-----+
| character_set_client | gb2312
|
| character_set_connection | utf8
|
| character_set_database | utf8
|
| character_set_filesystem | binary
|
| character_set_results | gb2312
|
| character_set_server | utf8
|
| character_set_system | utf8
|
| character_sets_dir | F:\Program Files\MySQL\MySQL Server 5.0\share\chars
ets\ |
+--------------------------+----------------------------------------------------
-----+
8 rows in set (0.00 sec)
mysql> set character_set_results=gb2312;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from user;
+----+----------+----------+--------------------+-------+
| id | username | password | description | image |
+----+----------+----------+--------------------+-------+
| 1 | Xiao Bai | 6395749 | Fight For Beijing! | NULL |
| 2 | 小白 | NULL | NULL | NULL |
+----+----------+----------+--------------------+-------+
2 rows in set (0.00 sec)
可以更改my.ini配置文件中mysql客-户-端码表(注意不要改mysql服务端码表!--->默认utf8),但一般不这么做!!!
注意的安全问题:更新或删除时,一定注意先把后边的where条件写好,再在前面添加数据,不然忘了写where条件,结果是致命的!!!
实际中一般要经常备份!!!
更新或删除时还要注意表关联的问题!
4.(待复习,更新)表查询(复杂的多表查询,子查询)
5.外键约束
mysql> create table husband
-> (id int primary key,
-> name varchar(40)
-> );
Query OK, 0 rows affected (0.09 sec)
mysql> create table wife
-> (id int primary key,
-> name varchar(40),
-> husband_id int,
-> constraint husband_id_FK foreign key(husband_id) references husband(id)
-> );
Query OK, 0 rows affected (0.08 sec)
mysql> insert into husband(id,name) values(1,'张三');
ERROR 1406 (22001): Data too long for column 'name' at row 1
mysql> set charset gb2312;
Query OK, 0 rows affected (0.06 sec)
mysql> insert into husband(id,name) values(1,'张三');
ERROR 1062 (23000): Duplicate entry '1' for key 1
mysql> insert into husband(id,name) values(2,'李四');
Query OK, 1 row affected (0.02 sec)
mysql> insert into wife(id,name,husband_id) values(1,'陈琪',5);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint f
ails (`mydb3/wife`, CONSTRAINT `husband_id_FK` FOREIGN KEY (`husband_id`) REFERE
NCES `husband` (`id`))
mysql> insert into wife(id,name,husband_id) values(1,'陈琪',2);
Query OK, 1 row affected (0.03 sec)
mysql> select * from husband;
+----+------+
| id | name |
+----+------+
| 1 | 张三 |
| 2 | 李四 |
+----+------+
2 rows in set (0.05 sec)
mysql> select * from wife;
+----+------+------------+
| id | name | husband_id |
+----+------+------------+
| 1 | 陈琪 | 2 |
+----+------+------------+
1 row in set (0.02 sec)
6.表设计:(数据库三范式)
一对多的复杂对象数据存入表:先各自独立建表,再在多的一方设置另一方为外键约束,描述数据关系。
多对多的对象关系:设计一个中间表描述关系,中间表的相应字段分别加两方表为外键约束。
一对一:主从关系(如人和身份证),外键约束加唯一约束、非空约束(主可以没有从,从不能没有主),因为从的主键有唯一、非空约束,所以把主键设置外键约束即可
自关联:(家族族谱)增加字段parent_id,并设置外键约束为自身的id字段----------->应用:设计一棵无限级分类的树(以后要用二叉树方案)
例子:
多对多
设计:
mysql> create table teacher
-> (
-> id int primary key,
-> name varchar(40),
-> salary decimal(8,2)
-> );
Query OK, 0 rows affected (0.16 sec)
mysql> create table student
-> (id int primary key,
-> name varchar(40));
Query OK, 0 rows affected (0.11 sec)
mysql> create table teacher_student
-> (
-> teacher_id int,
-> student_id int,
-> primary key(teacher_id,student_id),
-> constraint teacher_id_FK foreign key(teacher_id) references teacher(id),
-> constraint student_id_FK foreign key(student_id) references student(id)
-> );
Query OK, 0 rows affected (0.14 sec)
一对一
设计:
mysql> create table person
-> (
-> id int primary key,
-> name varchar(40)
-> );
Query OK, 0 rows affected (0.20 sec)
mysql> create table idcard
-> (
-> id int primary key,
-> city varchar(40),
-> constraint id_FK foreign key(id) references person(id));
Query OK, 0 rows affected (0.11 sec)
自关联:一个列的值来自另一列
设计:自连接的表
mysql> drop table person;
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constrai
nt fails
mysql> drop table idcard;
Query OK, 0 rows affected (0.03 sec)
mysql> drop table person;
Query OK, 0 rows affected (0.05 sec)
mysql> create table person
-> (
-> id int primary key,
-> name varchar(40),
-> parent_id int,
-> constraint parent_id_FK foreign key(parent_id) references person(id));
Query OK, 0 rows affected (0.08 sec)
注:此前那个person表被idcard表设置成外键约束,所以无法删除,只能先删除idcard表,怎样删除外键待查。
无限级分类示意:
不按三范式的设计:放在一张表里,会出现冗余,但查询性能较好,因为不用查多表。要求查询性能高的方案可能会这样设计。