MySQL之旅_Day04

今天学习mysql中的DML语句，主要是增删改查

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

MySQL入门

编码环境：OS X 10.10.1 | MySQL5.5.38 | mac终端

先创建一个user表：

CREATE TABLE IF NOT EXISTS user (

   id TINYINT UNSIGNED AUTO_INCREMENT KEY,

   name VARCHAR(10) UNIQUE NOT NULL,

   email VARCHAR(30) NOT NULL

);

1、 插入记录INSERT

语法：INSERT INTO tb_name(字段名称1，字段名称2...) VALUSE (值1，值2...);

INSERT INTO user(name, email) VALUES ('zhangsan','123456');

一次插入多条数据：

INSERT INTO user(name, email) VALUES ('李四','23456'),

('王五','34567'),

('赵六','45678');

2、 删除记录DELETE

语法：DELETE FROM tb_name [WHERE 条件];//如果不加条件，就是删除全部记录

删除id=1的记录：

DELETE FROM user WHERE id=1

3、 修改记录UPDATE

语法：UPDATE tb_name SET 字段1=值1,字段2=值2，....[WHERE 条件];//如果不加条件，就是修改所有记录

把name为zhangsan的记录改为张三：

UPDATE user SET name='张三' WHERE name='zhangsan';

4、 查询记录SELECT

常用语法：SELECT *|字段名1，字段名2... FROM tb_name [WHERE 条件]

[GROUP BY 字段名]

[HAVING 条件]

[ORDER BY 字段名[ASC|DESC]

[LIMIT [偏移量,]条数];

给user表新增两个字段，age和course

ALTER TABLE user ADD age TINYINT NOT NULL DEFAULT 20,

ADD course VARCHAR(10);

增加100条测试记录：

<?php
for($i=1; $i <= 100; $i++) {
	$name="user".$i;
	$email=$name.'qq.com';
	$age=mt_rand(1, 100);
	if($age < 30) {
		$course='java';
	} else if($age < 70){
		$course='php';
	} else {
		$course='ios';
	}
	echo "INSERT INTO user(name, email, age, course) 
	VALUES ('{$name}','{$email}','{$age}','{$course}');";
	echo "<br/>";
}
?>

-查询年龄大于60，按course分组，根据id降序排序

SELECT * FROM user WHERE age>60 GROUP BY course ORDER BY id DESC;

5、 聚合函数（一般跟分组GROUP BY配合使用）

SUM()：求和

AVG()：求平均值

COUNT()：表达式中记录条数

MAX()：最大值

MIN()：最小值

FIRST()：第一个值

LAST()：最后一个值

6、 子查询：

将一个查询语句嵌套在另一个查询语句中，内层查询语句查询出结果，作为外层查询语句的条件。（实际开发中，子查询不会超过两层）

新增一个课程表course：

CREATE TABLE IF NOT EXISTS course (

id TINYINT UNSIGNED AUTO_INCREMENT KEY,

name CHAR(10) NOT NULL

);

插入课程记录：

INSERT INTO course(name) VALUES ('java'),('php'),('ios');

删除user表的记录，再新增几条记录，用course表中的id代替课程：

insert into user(name, email, age, course) values ('张三风','zhangsan@qq.com',24,1), 
('李四','lisi@qq.com',36,2),
('王五','wangwu@qq.com',25,2),
('赵六','zhaoliu@qq.com',36,3),
('小明','xiaoming@qq.com',16,1),
('韩梅梅','hmm@qq.com',30,2);

+----+-----------+-----------------+-----+--------+
| id | name      | email           | age | course |
+----+-----------+-----------------+-----+--------+
|  1 | 张三      | zhangsan@qq.com |  24 | 1      |
|  2 | 张三风    | zhangsan@qq.com |  24 | 1      |
|  3 | 李四      | lisi@qq.com     |  36 | 2      |
|  4 | 王五      | wangwu@qq.com   |  25 | 2      |
|  5 | 赵六      | zhaoliu@qq.com  |  36 | 3      |
|  6 | 小明      | xiaoming@qq.com |  16 | 1      |
|  7 | 韩梅梅    | hmm@qq.com      |  30 | 2      |
+----+-----------+-----------------+-----+--------+
-查询用户名为李四所选的课程：

mysql> SELECT c.name FROM course AS c WHERE id=(SELECT u.course FROM user AS u WHERE name='李四');
+------+
| name |
+------+
| php  |
+------+

7、 多表连接

两个表查询，如果没有条件的话，会产生笛卡尔积效果

-查询user表中韩梅梅的邮箱和课程：

mysql> SELECT u.email, c.name FROM user AS U, course AS c WHERE u.course=c.id AND u.name='韩梅梅';
+------------+------+
| email      | name |
+------------+------+
| hmm@qq.com | php  |
+------------+------+

连接

内连接：将符合多个表中的记录查询出来（一般不超过3个表）

[INNER] JOIN

连接条件 ON 条件

语法：

SELECT 字段名称... FROM tb_1 JOIN tb_2 ON 条件 JOIN tb_3 ON 条件...;

-查询id小于5的用户信息：

mysql> SELECT u.id, u.name, u.email, u.age, c.name FROM user AS u JOIN course AS c ON u.course=c.id WHERE u.id<5;
+----+-----------+-----------------+-----+------+
| id | name      | email           | age | name |
+----+-----------+-----------------+-----+------+
|  1 | 张三      | zhangsan@qq.com |  24 | java |
|  2 | 张三风    | zhangsan@qq.com |  24 | java |
|  3 | 李四      | lisi@qq.com     |  36 | php  |
|  4 | 王五      | wangwu@qq.com   |  25 | php  |
+----+-----------+-----------------+-----+------+
4 rows in set (0.00 sec)

外连接（很少使用）

左外连接：主表 LEFT JOIN 副表

右外连接：副表 RIGHT JOIN 主表

先把主表的所有数据查询出来，再查询副表中符合条件的记录

user表增加一条记录，course=6，在course中不存在id=6的课程

INSERT INTO user (name, email, age, course) VALUES ('mark', 'mark@qq.com', 25, 6);

左外连接查询mark的信息：

mysql> SELECT u.id, u.name, u.email, u.age, c.name AS '课程' FROM user AS u LEFT JOIN course AS c ON u.course=c.id WHERE u.name='mark';
+----+------+-------------+-----+--------+
| id | name | email       | age | 课程   |
+----+------+-------------+-----+--------+
|  8 | mark | mark@qq.com |  25 | NULL   |
+----+------+-------------+-----+--------+
1 row in set (0.00 sec)

可以看出课程是null，因为course表中没有id=6的课程

如果换成右外连接：

SELECT u.id, u.name, u.email, u.age, c.name AS '课程' FROM user AS u RIGHT JOIN course AS c ON u.course=c.id WHERE u.name='mark';

没有记录返回，因为是先查询course表，根据主表不同，查询到的结果也不同（主要是显示NULL的字段不同）

8、 外键

外键的作用：保证记录的一致性和完整性

注意：使用外键存储引擎一定是INNODB，外键列如果没有指定索引的话，会自动创建索引

           尽量不使用物理外键，要用外键的思想来保证数据的一致性和完整性

①创建表时指定外键

foreign key(cid)：代表的是外键列

references cate(id)：被参照的表（字段），一定是cate表的主键

//分类表cate

CREATE TABLE IF NOT EXISTS cate (

id TINYINT UNSIGNED AUTO_INCREMENT KEY,

cName VARCHAR(20) NOT NULL UNIQUE

);

//新闻表news

CREATE TABLE IF NOT EXISTS news(

id INT UNSIGNED AUTO_INCREMENT KEY,

title VARCHAR(50) NOT NULL,

content TEXT,

cId TINYINT UNSIGNED,

FOREIGN KEY(cId) REFERENCES cate(id)

);

给news表添加了外键，cate表的主键id

//给cate表添加几条记录

INSERT INTO cate(cName) VALUES ('体育新闻'),('国际新闻');

//给news插入一条记录

INSERT INTO news (title, cId) VALUES ('标题1', 2);

如果要删除cate表或者里面的记录，一定要先删除和cate表相关的表中的记录

//删除外键

ALTER TABLE news DROP FOREIGN KEY news_ibfk_1;

②ALTER语句添加外键

ALTER TABLE news ADD FOREIGN KEY(cId) REFERENCES cate(id);

END