MySQL---操作数据表基本操作（增删改查）

插入记录

INSERT [INTO] tbl_name [{col_name,…}] {VALUES|VALUE} (values…),(values…)

• 不加列名表示需要插入全部数据
• 对于自增主键，可以用NULL 和 DEFAULT 使之自己增加

mysql> INSERT INTO users VALUES(NULL,'TOM','123',20,1);
Query OK, 1 row affected (0.08 sec)

mysql> INSERT INTO users VALUES(DEFAULT,'TOM','123',20,1);
Query OK, 1 row affected (0.08 sec)

• 可以写数字运算、函数

mysql> INSERT INTO users VALUES(DEFAULT,'TOM','123',10*2-1,1);
Query OK, 1 row affected (0.08 sec)

// 使用自带的md5()计算md5
mysql> INSERT INTO users VALUES(DEFAULT,'TOM',md5('123'),10*2-1,1);

• 有默认值的列，使用DEFAULT表示使用默认值

mysql> INSERT INTO users VALUES(DEFAULT,'TOM','123',DEFAULT,1);
Query OK, 1 row affected (0.08 sec)

• 插入多条记录

mysql> INSERT INTO users VALUES(DEFAULT,'TOM','123',DEFAULT,1),(NULL,'JERRY','456',DEFAULT,0);
Query OK, 2 rows affected (0.08 sec)
Records: 2  Duplicates: 0  Warnings: 0

INSERT [INTO] tbl_name SET col_name=value，…

此方法可以使用子查询

INSERT INTO users SET username='TIM' , password='789' ,sex=1;

INSERT [INTO] tbl_name [(col_name,…)] SELECT …

经查询结果插入到指定数据表中

将查询到的数据的前5条插入到test表中。
mysql> INSERT INTO test (id,subject) SELECT studentNo, subjectNo FROM result LIMIT 5;

更新记录

UPDATE table_name SET col_name1=value1 [,col_name2=value2…] [WHERE condition]

// 	更改username为TOM的行
mysql> UPDATE userinfo SET username='AAA' WHERE username='TOM';

// 在更改信息时使用表达式
mysql> UPDATE userinfo SET id = id+10;

// 使用 % 运算符判断奇偶
mysql> UPDATE userinfo SET email='ABC' WHERE id%2=0;

// 使用查询的结果更新数据
update z_venue_station, venue_info
set z_venue_station.venue_id=venue_info.OBJ_ID
where z_venue_station.venue_name=venue_info.OBJ_NAME

删除记录

DELETE FROM tbl_name [WHERE condition]

// 删除一条记录
mysql> DELETE FROM username WHERE username='AAA';

查找记录

SELECT value1 [,value2…]
[
** from table_name **
** [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}]**
]

• 每个表达式表示一列，至少有一个
• 多个列之间用逗号分隔
• 星号表示所有列，tbl_name.* 可以表示表的所有列
• 查询表达式可以使用[AS] alias_name 为其服务赋予别名。AS 可以省略不写
• 别名用于GROUP BY ORDER BY和HAVING子句

// 多个表达式之间用逗号隔开
mysql> SELECT id,username FROM userinfo;

// table_name.colname 表示特定表中的列
mysql> SELECT userinfo.id ,userinfo.username,userinfo.email FROM userinfo ;

// table_name.* 表示特定表中的所有行，单表查询时，table_name.* 与 * 效果相同
mysql> SELECT userinfo.* FROM userinfo ;

// 用 AS 给变量取别名
mysql> SELECT id AS userid FROM userinfo ;
+--------+
| userid |
+--------+
|     12 |
|     13 |
+--------+

// AS 可以省略
mysql> SELECT id userid FROM userinfo ;

分组

GROUP BY {col_name|position} [ASC|DESC],…
用GROUP BY分组后，结果的每一列从1开始递增编号。筛选分组时可以用数字代替列名

// 按性别分组，student为各组的第一位的信息。
mysql> SELECT studentName, count(studentNo) AS number  FROM student GROUP BY sex;
+-------------+--------+
| studentName | number |
+-------------+--------+
| 张萍        |      7 |
| newName     |      6 |
+-------------+--------+

筛选分组(HAVING)

HAVING condition
在分组完成后，筛选满足条件的组。

// 按照gradeId分组
mysql> SELECT studentName,count(studentNo) AS number FROM student GROUP BY gradeId ;
+-------------+--------+
| studentName | number |
+-------------+--------+
| newName     |      9 |
| 王宝宝      |      2 |
| 陈志强      |      2 |
+-------------+--------+

// 从分组中筛选number大于3的分组
mysql> SELECT studentName,count(studentNo) AS number FROM student GROUP BY gradeId HAVING number>=3;
+-------------+--------+
| studentName | number |
+-------------+--------+
| newName     |      9 |
+-------------+--------+

用数字代替列名,3>2表示结果中的第三个参数count>2

// 3 表示第三个参数，即count
mysql> SELECT studentNo,examDate,count(studentNo) AS count FROM result GROUP BY examDate HAVING 3>2;
+-----------+---------------------+-------+
| studentNo | examDate            | count |
+-----------+---------------------+-------+
|     10000 | 2016-02-15 00:00:00 |     1 |
|     10000 | 2016-02-17 00:00:00 |     8 |
|     20000 | 2016-07-09 00:00:00 |     2 |
+-----------+---------------------+-------+

// 效果与下列相同
mysql> SELECT studentNo,examDate,count(studentNo) AS count FROM result GROUP BY examDate HAVING count>2;
+-----------+---------------------+-------+
| studentNo | examDate            | count |
+-----------+---------------------+-------+
|     10000 | 2016-02-17 00:00:00 |     8 |
+-----------+---------------------+-------+
1 row in set (0.00 sec)

排序(ORDER)

ORDER BY col_name [ASC|DESC]
将查询结构按照升序（ASC）或者降序（DESC）排列

// 降序排列查询到的结果
mysql> SELECT studentNo ,studentName FROM student ORDER BY studentNo DESC;
+-----------+-------------+
| studentNo | studentName |
+-----------+-------------+
|     30012 | 李露露      |
|     30011 | 陈志强      |
|     20010 | 何小华      |
|     20000 | 王宝宝      |
|     10008 | 何睛睛      |
|     10007 | 秦洋        |
|     10006 | 肖梅        |
|     10005 | 张秋丽      |
|     10004 | 韩秋洁      |
|     10003 | 张萍        |
|     10002 | 李斯文      |
|     10001 | 李文才      |
|     10000 | newName     |
+-----------+-------------+

限制查询返回的数量(LIMIT)

LIMIT length (start,length)

• 返回的数据，序号从0开始。
• length 表示返回前length条记录，（start，length）表示返回从序号为start开始的length条数据

// 选择前三条数据
mysql> SELECT studentNo, studentName FROM student LIMIT 3;
+-----------+-------------+
| studentNo | studentName |
+-----------+-------------+
|     10000 | newName     |
|     10001 | 李文才      |
|     10002 | 李斯文      |
+-----------+-------------+

// 选择从序号为2开始的3条数据
mysql> SELECT studentNo, studentName FROM student LIMIT 2,3;
+-----------+-------------+
| studentNo | studentName |
+-----------+-------------+
|     10002 | 李斯文      |
|     10003 | 张萍        |
|     10004 | 韩秋洁      |
+-----------+-------------+