mysql接触主外表关系_MySql零距离接触 总结

一，mysql的初涉

1，下载并安装mysql了，这在网上有很多的教程很详细的，大家可以自行百度。

在登录到mysql之后，可以使用如下的命令来显示当前mysql的信息：

SELECT VERSION(); #显示当前服务器的版本

SELECT NOW(); #显示当前日期时间

SELECT USER(); #显示当前用户

SHOW DATABASES; #显示系统当前的数据库

2，对数据库进行操作

创建数据库：CREATE {DATABASE} [IF NOT EXISTS] 数据库名字 CHARACTER SET [=] chaeacter_name;

如：CREATE DATABASE base_t1;

修改数据库：ALTER {DATABASE} 数据库名字 CHARACTER SET [=] chaeacter_name;

如：ALTER DATABASE base_t1 CHARACTER SET = uft8; #将数据的字符格式修改成uft8

删除数据库：DROP {DATABASE | SCHEMA} [IF EXISTS] 数据库名字;

如：DROP DATABASE base_t1 ;

显示当前所有的数据库：SHOW DATABASES;

二：数据表的基本操作

1，先使用USE打开数据

USE 数据库名字; 如：USE base_t1; 打开数据库名为base_t1的数据库

2，创建数据表

CREATE TABLE [IF NOT EXISTS] table_name(

column_name data_type,...)

如：CREATE TABLE tb1(id TINYINT ,username VARCHAR(20),sarary float(8,2));

3，删除数据表

DROP TABLE table_name;

如： DROP TABLE tb1;

4，修改数据表

修改表中的列定义：ALTER TABLE tbl_name MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name].

例如：ALTER TABLE users2 MODIFY id SMALLINT UNSIGNED NOT NULL FIRST;//修改id字段的数据类型和在表users2中所处的位置

修改列名称：ALTER TABLE tbl_name CHANGE [column] old_col_name new_col_name column_defintion [FIRST | AFTER col_name].

例如：ALTER TABLE user CHANGE id p_id TINYINT NUSIGNED NOU NULL; #将user表中的字段id改成p_id

给数据表更名:ALTER TABLE tbl_name RENAME new_tbl_name;

例如：ALTER TABLE users2 RENAME users3;//把表名users2改为新的users3;

5,查看数据表以及数据的表结构：

SHOW TABLES[FROM db_name] [LIKE "pattern" | WHERE expr]

如：SHOW TABLES; #显示当前数据库下的数据表

显示某个数据表的表结构

SHOW COLUMNS FROM 数据表名;

如： SHOW COLUMNS FROM tb1;

6，数据表的约束：

NULL | NOT NULL :空与非空约束

AUTO_INCREMENT: 自动编号，默认情况，起始为1，每次增1

PRIMARY KEY:主键约束，一张表一个主键，具有唯一性，且自动为 NOT NULL

UNIQUE KEY:唯一约束，可以多个，可以为空，

DEFAULT :默认约束，在插入记录时，若没有赋值，会自动赋予默认值

如：CREATE TABLE tb2(id SAMLLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, username VARCHAR(20) NOT NULL UNIQUE KEY, sex ENUM("1","2","3") DEFAULT "1");

三，数据表的修改及其约束

对一个数据列的建立的约束为列级约束；对多个数据列的建立的约束为表级约束。

1，外键约束(实际开发时基本不用)

FOREIGN KEY (子表字段) REFERENCES 父表 (父表字段)

2，修改数据表

添加单列： ALTER TABLE tbl_name [COLUMN] col_name column_definition[FIRST | AFTER col_name];

例如： ALTER TABLE tb2 ADD password VARCHAR(20) NOT NULL AFTER id; #在tb2中添加password 字段，并且放在id字段后面

删除单列： ALTER TABLE tbl_name DROP [COLUMN] col_name;

例如： ALTER TABLE tb2 DROP password ;

3，添加和删除约束

a，添加和删除主键约束

添加：ALTER TABLE tbl_name ADD PRIMARY KEY (index_col_name,...);

例如：ALTER TABLE user2 ADD PRIMARY KEY(id);

删除： ALTER TABLE tbl_name DROP PRIMARY KEY

例如：ALTER TABLE user2 DROP PRIMARY KEY(id);

b， 添加/删除唯一约束：

添加：ALTER TABLE tbl_name ADD UNIQUE (...)

例如：ALTER TABLE user2 ADD UNIQUE(username);

删除：ALTER TABLE tbl_name DROP {INDEX|KEY} index_name;

例如：ALTER TABLE user2 DROP INDEX username;(需要知道username的 索引)

c, 添加/删除外键约束

添加：ALTER TABLE tbl_name ADD FOREIGN KEY (index_col_name) REFERENCES reference_definition.(index_col_name);

例如：ALTER TABLE user2 ADD FOREIGN KEY(id) REFERENCES province (pid);

删除：ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol

例如：ALTER TABLE user2 DROP FOREIGN KEY user2_ibfk_1;

d,添加/删除默认约束

添加/删除：ALTER TABLE tbl_name ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DAFAULT};

例如：ALTER TABLE user2 ALTER age SET DEFAULT 15; #为user2表的age字段添加默认约束15.

例如：ALTER TABLE user2 ALTER age DROP DEFAULT； #删除了age字段的默认约束。

四，操作数据表的记录

1,插入记录：INSERT [INTO] tbl_name [(col_name,...)]{VALUES}();

例如：INSERT users VALUES(NULL,"tons",23); 注意列数目的匹配。

也可以一次插入多个值：

INSERT users VALUES(NULL,"tons",23),(DEFAULT,"Rose",23,md5("2334"));

INSERT [INTO] tbl_name [(col_name,...)] VALUES(val,;;;;);

若是省略插入的字段名，则不能省略要插入所有字段。

INSERT tb1 VALUES("TOM",25,6782.12)

当我们仅仅是为某一些字段赋值时，就必须将字段写上。INSERT tb1(username,salary) VALUES("jOIN",3398.98);

也可以通过查询条件将查询的结果数据插入到数据表中：

例如：INSERT test (username) SELECT username FROM users WHERE age>30; 即将users表中年纪大于30的用户的名字插入到test表中。

2,更新记录(即修改某条记录)

UPDATE table_reference SET col_name = {expr}[,col_name={expr2}][where ..]

例如：UPDATE user SET age+5 where id=3;将id为3的用户的年纪加上5

3，删除记录

DELETE FROM tbl_from [WHERE ...] 若省略where语句，则删除所有记录

例如：DELETE FROM users where id =4;删除id为4的那个字段。

4，查询数据表(重头戏)

SELECT select_expr[,select_expr]

[ FROM table_reference [WHERE where_condition]

[GROUP BY {col_name | position}] [ASC|DESC] [HAVING where_condition]

[ORDER BY {col_name | expr | position} [ASC|DESC] ]

[LIMIT {[offset,] row_count | row_count OFFSET offset}]

]

例如：SELECT id,username,age FROM users WHER age =21 GROUP BY id ASCI; 查询users表中年纪为21的用户的 id,username,age并以id的大小升序排列。

GROUP BY 后面也可以跟位置，来表示要排序的字段。

要使用GROUP BY 后面的HAVING语句，就要保证HAVING 后的字段要么出现在了SELECT后面的语句中，要么HAVING 后面是一个聚合函数，如sum,avg等！

例如：SELECT age,username FROM users GROUP BY age HAVING count(id)>3;

还可以使用LIMIT来限制返回的结果：

例如：SELECT FROM users LIMIT 2; 查询users表中前两个记录。

也可以返回指定下标出的几个数据，如

SELECT FROM users LIMIT 3,2;//这里取得是第4,5条记录，因为记录的下标是从0开始的。

五，子查询与连接

首先改变客户端的数据数据显示格式：SET NAMES gbk;

子查询：指出现在其他SQL语句内的SELECT字句。

如：SELECT FROM t1 WHERE col1=(SELECT col2 FROM t2);

其中SELECT FROM t1 为外查询,SELECT col2 FROM t2 为子查询

子查询可以返回标量、一行、一列或子查询

1，使用比较运算符的子查询

SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price >=(SELECT　ROUND(AVG(goods_price),2) FROM tdb_goods ); #显示商品价格大于商品平均价格的商品的名字，id和价格。

比较运算符的关键字：ANY,SOME,ALL。(ANY,SOME具有相同的意义)

SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price > ANY(SELECT goods_price FROM tdb_goods WHERE goods_cate="超级本"); # 查询商品中大于类型为“超级本”的所有商品的id，商品名和商品价格。

2,使用IN或NOT IN引发的子查询

IN相当于ANY关键字，NOT IN相当于 NOT ALL运算：

3，使用INSERT...SELECT...语句

INSERT tab_goods_cates(cate_name ) SELECT goods_cate FROM tdb_goods GROUP BY goods_cate ; #将tdb_goods 数据表中的goods_cate 类型全部写入到tab_goods_cates表中的cate_name 中。

4，多表更新

UPDATE table_reference SET col_name1={expr1|DEFAULT} [,col_name={expr2|DEFAULT}]...[WHERE where_condition]

表的参照关系：

table_reference {[INNER | CROSS] JOIN | {LEFT|RIGHT}} [OUTER| JOIN]

table_reference ON conditional_expr

UPDATE tdb_goods INNER JOIN tdb_goods_cates ON goods_cate=cate_name SET goods_cate = cate_id; #将表tdb_goods与表 tdb_goods_cates 内连接，并将tdb_goods中的goods_cate更换成tdb_goods_cates 中的cate_id。

若是两张表中想要连接的字段名字相同，连接时就需要给两张表起别名了，如：UPDATE tdb_goods AS t INNER JOIN tdb_goods_brands AS c ON t.brand_name=c.brand_name SET t.brand_name = c.brand_id; #将两张表内连接，并在连接时给表起别名，然后将t的brand_name改成c的brand_id。

这种连接被称作适时的外键。

5，连接

关系：table_reference {[INNER | CROSS] JOIN | {LEFT|RIGHT}} [OUTER| JOIN] table_reference ON conditional_expr;

连接包括内连接和外连接两种，外连接又包括左连接和右连接两种。

内连接表示左表和右表符合连接条件的记录：

SELECT goods_id,goods_name,cate_name FROM tdb_goods AS g INNER JOIN tdb_goods_cate AS c ON g.cate_id =c.cate_id; #连接两张表，将c表中的 cate_name在g表和c表cate_id相同时显示出来。

左外连接：显示左表中全部记录以及右表中符合连接条件的记录。

如下图所示，分别是，左外连接，右外连接，内连接

果然总结比想象中的累多了，先放在这，以后再补上。