与MySQL的零距离接触--笔记(第4章)

记录自己学习MySQL笔记.

◆视频教程来自慕课网:

https://www.imooc.com/learn/122

 

插入记录:
mysql> create table users4(
    -> id smallint unsigned primary key auto_increment,
    -> username varchar(20) not null,
    -> password varchar(32) not null,
    -> age tinyint unsigned not null default 10,
    -> sex boolean
    -> );

INSERT users4 VALUES(NULL,'John','456',23,1)
INSERT users4 VALUES(DEFAULT,'John','456',25,1)
INSERT users4 VALUES(NULL,'Tom','123',3*7-5,1)
INSERT users4 VALUES(NULL,'Sherry','678',DEFAULT,2)
INSERT users4 VALUES(NULL,'John','456',23,1),(NULL,'Nero',md5('456'),DEFAULT,0)

INSERT users4 SET username='Ben', password='567';

更新记录(单表更新):
UPDATE users4 SET age=age+5;
UPADTE users4 SET age=age-id,sex=0;
UPDATE users4 SET age=age+10 WHERE id%2=0;

删除记录(单表删除):
DELETE FROM users4 WHERE id=6;

查找记录:
SELECT VERSION();
SELECT NOW();
SELECT 3+5;

SELECT id,username FROM users4;
SELECT username,id FROM users4;

SELECT users4.id,users4.username FROM users4;

SELECT id AS userId,username AS uname FROM users4;
SELECT id username FROM users4相当于SELECT id AS username FROM users4;

条件表达式:
SELECT sex FROM users4 GROUP BY sex;

分组条件:
SELECT sex,age FROM users4 GROUP BY 1 HAVING age>22;
SELECT sex FROM users4 GROUP BY 1 HAVING count(id)>=2;

对查询结果进行排序:
SELECT * FROM users4 ORDER BY id DESC;
SELECT * FROM users4 ORDER BY age,id DESC; 先排序age,再按id排序

限制查询结果返回的数量:
SELECT * FROM users4 LIMIT 2;(从头开始返回两条记录)
SELECT * FROM users4 LIMIT 3,2;(返回第四条开始的两条记录)

把一张表中部分数据插入到另一张表中:
INSERT test(username) SELECT username FROM users4 WHERE age>=29;