CREATE DATABASE IF NOT EXISTS `zz` DEFAULT CHARACTER SET utf8 CREATE TABLE `b4` ( `xm` varchar(50) DEFAULT NULL, `je` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=gbk;
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `db_t` /*!40100 DEFAULT CHARACTER SET utf8 */;USE `db_t`;-- -- Table structure for table `a` --DROP TABLE IF EXISTS `a`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `a` ( `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT, `name` char(60) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */;-- -- Dumping data for table `a` --LOCK TABLES `a` WRITE; /*!40000 ALTER TABLE `a` DISABLE KEYS */; /*!40000 ALTER TABLE `a` ENABLE KEYS */; UNLOCK TABLES;-- -- Table structure for table `cv_basic` --DROP TABLE IF EXISTS `cv_basic`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `cv_basic` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(45) NOT NULL, `sex` char(1) NOT NULL, `birth` varchar(10) NOT NULL, `residence` varchar(45) NOT NULL, `work_year` int(3) unsigned NOT NULL DEFAULT '0', `post_code` varchar(10) DEFAULT NULL, `email` varchar(45) DEFAULT NULL, `annual_salary` int(10) unsigned DEFAULT NULL, `telphone` varchar(15) NOT NULL, `education` varchar(15) NOT NULL, `photo` varchar(45) DEFAULT NULL, `graduate_school` varchar(45) DEFAULT NULL, `major` varchar(45) DEFAULT NULL, `graduate_date` varchar(10) DEFAULT NULL, `self_evaluation` varchar(500) NOT NULL, `job_desired` int(10) unsigned NOT NULL, `salary_desired` varchar(45) NOT NULL, `if_traval` tinyint(1) NOT NULL DEFAULT '0', `workplace_desired` varchar(45) DEFAULT NULL, `other_suggestion` varchar(500) DEFAULT NULL, PRIMARY KEY (`id`), KEY `index_2` (`name`), KEY `index_3` (`job_desired`), CONSTRAINT `FK_cv_basic_1` FOREIGN KEY (`job_desired`) REFERENCES `cv_job_desired` (`job_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC; /*!40101 SET character_set_client = @saved_cs_client */;-- -- Dumping data for table `cv_basic` --LOCK TABLES `cv_basic` WRITE; /*!40000 ALTER TABLE `cv_basic` DISABLE KEYS */; /*!40000 ALTER TABLE `cv_basic` ENABLE KEYS */; UNLOCK TABLES;-- -- Table structure for table `cv_it_skill` --DROP TABLE IF EXISTS `cv_it_skill`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `cv_it_skill` ( `it_id` int(10) unsigned NOT NULL AUTO_INCREMENT, `search_id` int(10) unsigned NOT NULL, `it_name` varchar(45) NOT NULL, `it_level` varchar(45) NOT NULL, `it_use_time` int(10) unsigned NOT NULL, PRIMARY KEY (`it_id`), KEY `index_2` (`search_id`), CONSTRAINT `FK_cv_it_skill_1` FOREIGN KEY (`search_id`) REFERENCES `cv_search` (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 ROW_FORMAT=FIXED; /*!40101 SET character_set_client = @saved_cs_client */;-- -- Dumping data for table `cv_it_skill` --LOCK TABLES `cv_it_skill` WRITE; /*!40000 ALTER TABLE `cv_it_skill` DISABLE KEYS */; INSERT INTO `cv_it_skill` VALUES (1,1,'J2EE','了解',3),(2,2,'C#','了解',3); /*!40000 ALTER TABLE `cv_it_skill` ENABLE KEYS */; UNLOCK TABLES;
建库: create database db_name character set =utf8; /*建立库db_name,默认字符集为utf8*/建表: create table tb_1 (id int auto_increment primary key, re varchar(20)) engine=innodb default character set=utf8 auto_increment =100; /*建立表tb_1,存储引擎为innodb,默认字符集为utf8,自增列开始值为100*/create table tb_2 (id2 int auto_increment primary key, re varchar(20)) engine=innodb default character set=utf8 auto_increment =100;内联接: select * from t1 inner join t2 where t1.id=t2.id2;左联接: select * from t1 left join t2 on t1.id=t2.id2;右联接: select * from t1 right join t2 on t1.id=t2.id2;全联接: select * from t1,t2
create database mydb;建表
create table t_macau2008 (id int primary key,fld char(10));
http://dev.mysql.com/doc/refman/5.1/zh/tutorial.html
3. 教程
3.1. 连接与断开服务器
3.2. 输入查询
3.3. 创建并使用数据库
3.3.1. 创建并选择数据库
3.3.2. 创建表
3.3.3. 将数据装入表中
3.3.4. 从表检索信息
CREATE TABLE `b4` (
`xm` varchar(50) DEFAULT NULL,
`je` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gbk;
-- Table structure for table `a`
--DROP TABLE IF EXISTS `a`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `a` (
`id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`name` char(60) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;--
-- Dumping data for table `a`
--LOCK TABLES `a` WRITE;
/*!40000 ALTER TABLE `a` DISABLE KEYS */;
/*!40000 ALTER TABLE `a` ENABLE KEYS */;
UNLOCK TABLES;--
-- Table structure for table `cv_basic`
--DROP TABLE IF EXISTS `cv_basic`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `cv_basic` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(45) NOT NULL,
`sex` char(1) NOT NULL,
`birth` varchar(10) NOT NULL,
`residence` varchar(45) NOT NULL,
`work_year` int(3) unsigned NOT NULL DEFAULT '0',
`post_code` varchar(10) DEFAULT NULL,
`email` varchar(45) DEFAULT NULL,
`annual_salary` int(10) unsigned DEFAULT NULL,
`telphone` varchar(15) NOT NULL,
`education` varchar(15) NOT NULL,
`photo` varchar(45) DEFAULT NULL,
`graduate_school` varchar(45) DEFAULT NULL,
`major` varchar(45) DEFAULT NULL,
`graduate_date` varchar(10) DEFAULT NULL,
`self_evaluation` varchar(500) NOT NULL,
`job_desired` int(10) unsigned NOT NULL,
`salary_desired` varchar(45) NOT NULL,
`if_traval` tinyint(1) NOT NULL DEFAULT '0',
`workplace_desired` varchar(45) DEFAULT NULL,
`other_suggestion` varchar(500) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `index_2` (`name`),
KEY `index_3` (`job_desired`),
CONSTRAINT `FK_cv_basic_1` FOREIGN KEY (`job_desired`) REFERENCES `cv_job_desired` (`job_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
/*!40101 SET character_set_client = @saved_cs_client */;--
-- Dumping data for table `cv_basic`
--LOCK TABLES `cv_basic` WRITE;
/*!40000 ALTER TABLE `cv_basic` DISABLE KEYS */;
/*!40000 ALTER TABLE `cv_basic` ENABLE KEYS */;
UNLOCK TABLES;--
-- Table structure for table `cv_it_skill`
--DROP TABLE IF EXISTS `cv_it_skill`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `cv_it_skill` (
`it_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`search_id` int(10) unsigned NOT NULL,
`it_name` varchar(45) NOT NULL,
`it_level` varchar(45) NOT NULL,
`it_use_time` int(10) unsigned NOT NULL,
PRIMARY KEY (`it_id`),
KEY `index_2` (`search_id`),
CONSTRAINT `FK_cv_it_skill_1` FOREIGN KEY (`search_id`) REFERENCES `cv_search` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 ROW_FORMAT=FIXED;
/*!40101 SET character_set_client = @saved_cs_client */;--
-- Dumping data for table `cv_it_skill`
--LOCK TABLES `cv_it_skill` WRITE;
/*!40000 ALTER TABLE `cv_it_skill` DISABLE KEYS */;
INSERT INTO `cv_it_skill` VALUES (1,1,'J2EE','了解',3),(2,2,'C#','了解',3);
/*!40000 ALTER TABLE `cv_it_skill` ENABLE KEYS */;
UNLOCK TABLES;
create database db_name character set =utf8;
/*建立库db_name,默认字符集为utf8*/建表:
create table tb_1 (id int auto_increment primary key, re varchar(20)) engine=innodb default character set=utf8 auto_increment =100;
/*建立表tb_1,存储引擎为innodb,默认字符集为utf8,自增列开始值为100*/create table tb_2 (id2 int auto_increment primary key, re varchar(20)) engine=innodb default character set=utf8 auto_increment =100;内联接:
select * from t1 inner join t2 where t1.id=t2.id2;左联接:
select * from t1 left join t2 on t1.id=t2.id2;右联接:
select * from t1 right join t2 on t1.id=t2.id2;全联接:
select * from t1,t2
说个需求吧:
3张表,学生表、科目表和成绩表
学生表(学号-主键、姓名、性别、生日)
科目表(编号-主键、科目名)
成绩表(编号-主键、学号-外键、科目号-外键、分数)
用MySql写出来看看下
给个你参考下吧:create table tb_student (
Student_ID int auto_increment primary key,
Student_Name varchar(20),
Sex char(1),
Birthday varchar(10)
) engine=innodb default character set =utf8;create table tb_subject (
Subject_ID int auto_increment primary key,
Subject_Name varchar(50)
) engine=innodb default character set =utf8;create table tb_score (
ID int auto_increment primary key,
Student_ID int,
Subject_ID int,
Score decimal(10,2),
foreign key (Student_ID) references tb_student(Student_ID),
foreign key (Subject_ID) references tb_subject(Subject_ID)
) engine=innodb default character set =utf8;
engine=innodb default character set =utf8;
这句用来干什么的?
我装MySql的时候设定了字符集了还要在这里设定吗?
这句用来干什么的?
---------------------------
设置表的存储引擎和默认字符集
我装MySql的时候设定了字符集了还要在这里设定吗?
-----------------------------------
可以不设置,不设置的话,该表用的是该库的字符集