mysql的考核方案_mysql考核

image.png

表1：person

mysql>create database person;

mysql>CREATE TABLE IF NOT EXISTS `person_tbl`(

`person_id` INT UNSIGNED AUTO_INCREMENT,

`person_firstname` VARCHAR(100) NOT NULL,

`person_lastname` VARCHAR(40) NOT NULL,

PRIMARY KEY ( `person_id` )

)ENGINE=InnoDB DEFAULT CHARSET=utf8;

表2：address

mysql>create database address;

mysql>CREATE TABLE IF NOT EXISTS `address_tbl`(

`address_addressid` INT UNSIGNED AUTO_INCREMENT,

`address_personid` INT UNSIGNED AUTO_INCREMENT,

`address_city` VARCHAR(100) NOT NULL,

`address_state` VARCHAR(40) NOT NULL,

PRIMARY KEY ( `address_addressid` )

)ENGINE=InnoDB DEFAULT CHARSET=utf8;

SQL查询1：

mysql>SELECT person_firstname , person_lastname , address_city , address_state FROM address_tbl LEFT JOIN person_tbl;

SQL查询2：

mysql>create database person;

mysql>CREATE TABLE IF NOT EXISTS `person_tbl`(

`person_id` INT UNSIGNED AUTO_INCREMENT,

`person_email` VARCHAR(100) NOT NULL,

PRIMARY KEY ( `person_id` )

)ENGINE=InnoDB DEFAULT CHARSET=utf8;

mysql>select person_email from person group by person_email having count(person_email)>1;

总结：表的操作类似于集合的交并补，明确操作的对象，比如做inner join，就要明确哪些原来的表有，哪些没有，有没有是在合并操作时补充的。感觉用mysql的命令行还是有点不习惯，有语法错误出现时无法第一时间发现，在运行时才被报错，重打一遍又有点麻烦，或许可以试试PHP？