MySQL第三天--乱码、约束、子查询、1对1表关系

编码

※MySQL中的字符编码(注意，utf8中没有'-'，跟Java中不一样)
SHOW VARIABLES; //查看系统变量

//查询字符编码相关的系统变量
SHOW VARIABLES WHERE variable_name LIKE 'character%';

 

解决MySQK的中文乱码:
character_set_client，character_set_connection和character_set_results这三者必须一致(GBK,GB2312,UTF8这三者都可以，但若采用其中一种则3个变量都要设这种)

设置字符编码的系统变量:
set character_set_client=utf8;  //独立设置某个变量
set names utf8; //同时设置3个:client、 connection和results

※其实，MySQL中的数据库、数据表 甚至 数据列 都可以有自己的编码，只是我们一般不专门设置。---从SQLyog中的更改表结构中可以查看出

 

数据库的约束：

主键primary key

•在创建表时指定主键---在声明主键时，应该同时使用notnull关键字。

•Create table stud(id int primary key,…..)

•在创建完成之后指定主键：

•Alter table stud add constraint stud_pk primary key(id);

主键自动增长：

•Auto_increment 适合于int类型的字段

外键foreign key－表示一种引用关系

•与另一个表的关联关系－称为外键，外键的作用时当主表中没有记录时，无法向子表中写入无引用的记录：

在建表时指定外键：

   create table 表名(
     列名1 参数,
     列名2 参数,
     foreign key(列名) references 目标表名(目标列名)
   );

表建立成功以后，修改外键：

•Alter table stud

•     add constraint   stud_fk foreign key(stud_clsid) references cls(id);

•主外键关系是一种1对多的关系。如果主表中没有的记录，子表中将不能增加。

•创建主外键关联，两个表的数据类型和大小必须保持一致否则创建将不会成功。

•可以通过可视化界面操作主外关联。

 

约束：

唯一unique – 与主键有所区别，主键不能为null，而unique可以有一列为null这是SqlServer的限制，但Mysql可以写入多列的null值，所以，在mysql上使用unique时一般再通过not null进行限制。

•altertable cls

•   add constraint cls_uk unique(name); /* 指定name不能重复*/

默认值default

•在创建表时，可以指定默认值如：

•Createtable stud(sex char(1) default‘1’,…..);

 

INSERT INTO stud VALUES(4,'湖南',20);CREATE TABLE person(  id INT,  NAME VARCHAR(30),  age INT);CREATE TABLE car(  id INT PRIMARY KEY,  //不允许重复，也不允许NULL  NAME VARCHAR(30),  NO INT UNIQUE  //不允许重复，允许多个NULL);//除了在创建表时如上的方式直接指定主键，也可以通过修改表的方式单独创建主键：ALTER TABLE person ADD CONSTRAINT person_pk PRIMARY KEY(id);ALTER TABLE person ADD CONSTRAINT UNIQUE (NAME); //为name字段添加一个unique约束//添加和删除unique约束 --通过约束名称ALTER TABLE person ADD CONSTRAINT UNIQUE uniq_nm (NAME);DROP INDEX uniq_nm ON person;//删除主键ALTER TABLE person DROP PRIMARY KEY;

 

 //有关性别字段，比较好的表结构。DATE类型勉强用，建议不要用DATETIME。为考虑兼容性，最好用CHAR(19),VARCHAR(19)  //datetime格式： 2016-11-11 23:24:09CREATE TABLE s(  id INT,  sex CHAR(1) DEFAULT '0',  birth DATE);INSERT INTO s(id,birth) VALUES(3,'2008/8/8'); //因为sex字段设了default，所以该字段不手动赋值时，由默认值0来赋按理,date的规范格式是"yyyy-MM-dd"，但我们给其他格式如"yyyy/M/d"时，MySQL也会帮我们自动转换成规范格式。//有关性别字段的显示学习一个SQL语法： 手册-->函数和操作符-->控制流程函数-->Case-WhenSELECT id, (CASE sex WHEN '0' THEN '女' WHEN '1' THEN '男' ELSE '其它' END) AS 性别, birth FROM s; 

 

默认是：

修改显示后：

子查询

※1无关子查询需求:查询具有同龄人的学生SELECT * FROM stud WHERE age IN(20,22,23); //过渡版："20,22,23" 写死了SELECT age FROM stud GROUP BY age HAVING COUNT(age)>=2; //这句可以动态输出:20,22,23综上:SELECT * FROM stud WHERE age IN( SELECT age FROM stud GROUP BY age HAVING COUNT(age)>=2 ) ORDER BY age ASC;※2相关子查询(子查询中用到了外面的查询结果表)需求:查询具有同龄人且年龄大于22的学生SELECT * FROM stud as s2 WHERE age IN( SELECT age FROM stud where s2.age>22 GROUP BY age HAVING COUNT(age)>=2 ) ORDER BY age ASC;select ... from ... where ... in( 子查询 )  order by ... asc(desc) select ... from ... where ... group by ... having ...

 

 

表与表之间的关系：

1对1

•示例：一夫一妻

•思考：在一个表中保存着所有人的信息。有男有女，要求查询出所有的夫妻，即一男一女。

•注意下面：王五是光棍不应该能查询出来。

 

 ※表与表之间的关系（1对1）1、CREATE TABLE person(   id INT,   NAME VARCHAR(10),   sex CHAR(1),   wife INT,   husband INT);INSERT INTO person VALUES(1,'小花','0',0,3);INSERT INTO person VALUES(2,'玉芬','0',0,4);INSERT INTO person VALUES(3,'张三','1',1,0);INSERT INTO person VALUES(4,'李四','1',2,0);INSERT INTO person VALUES(5,'王五','1',0,0);SELECT * FROM person WHERE sex='0';SELECT * FROM person WHERE sex='1';CREATE VIEW w AS SELECT * FROM person WHERE sex='0';CREATE VIEW m AS SELECT * FROM person WHERE sex='1';或CREATE VIEW w2 AS SELECT id,NAME,sex,husband FROM person WHERE sex='0';CREATE VIEW m2 AS SELECT id,NAME,sex,wife FROM person WHERE sex='1';SELECT w.name AS wn, m.name AS mn FROM w INNER JOIN m ON w.husband=m.id AND m.wife=w.id; SELECT * FROM w INNER JOIN m ON w.husband=m.id AND m.wife=w.id;