今天我们继续来讲Mysql方面的知识。

※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-表示一种引用关系
•与另一个表的关联关系-称为外键,外键的作用时当主表中没有记录时,无法向子表中写入无引用的记录:
•Alter table stud
• add constraintstud_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:09
CREATE 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-When

SELECT 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;

因为水平有限,难免有疏忽或者不准确的地方,希望大家能够直接指出来,我会及时改正。一切为了知识的分享。

后续会有更多的精彩的内容分享给大家。