【一步一步学习mysql】数据表的基本操作
USE db_name;，打开已创建的数据库。
创建数据表
CREATE TABLE [IF NOT EXISTS] table_name(
column_name data_type,
...
)
mysql> use imax;
Database changed
mysql> CREATE TABLE user( name VARCHAR(20), age TINYINT UNSIGNED, salary FLOAT(8, 2) UNSIGNED );
Query OK, 0 rows affected (0.31 sec)
查看数据表
SHOW TABLES;
SHOW COLUMNS FROM tbl_name;
mysql> SHOW COLUMNS FROM user;
+--------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+-------+
| name | varchar(20) | YES | | NULL | |
| age | tinyint(3) unsigned | YES | | NULL | |
| salary | float(8,2) unsigned | YES | | NULL | |
+--------+---------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
插入
方式1
INSERT [INTO] tbl_name[(col_name, ...)] {VALUES|VALUE}(val, ...);
mysql> INSERT user values('tom', 25, 7888.98);
Query OK, 1 row affected (0.07 sec)
mysql> INSERT user(name, salary) VALUES('jack', 8997.98);
Query OK, 1 row affected (0.11 sec)
注意：INSERT中，如果表明没有指定字段名则默认所有的字段都要赋值。
这里我就有个疑问了：我们现在的表中场景很简单没有自增的字段，如果有的话，怎么赋值？
- 将user表增加一个id字段，并进行数据的插入。
mysql> ALTER TABLE user ADD id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT FIRST;
Query OK, 0 rows affected (0.45 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SHOW CREATE TABLE user;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| user | CREATE TABLE `user` (
`id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
`age` tinyint(3) unsigned DEFAULT NULL,
`salary` float(8,2) unsigned DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT * FROM user;
+----+------+------+---------+
| id | name | age | salary |
+----+------+------+---------+
| 1 | tom | 25 | 7888.98 |
| 2 | jack | NULL | 8997.98 |
+----+------+------+---------+
2 rows in set (0.00 sec)
- 插入数据(使用DEFAULT|NULL即可)
mysql> INSERT user VALUES(DEFAULT, 'xxx', 29, 19999);
Query OK, 1 row affected (0.06 sec)
mysql> SELECT * FROM user;
+----+------+------+----------+
| id | name | age | salary |
+----+------+------+----------+
| 1 | tom | 25 | 7888.98 |
| 2 | jack | NULL | 8997.98 |
| 3 | xxx | 29 | 19999.00 |
+----+------+------+----------+
3 rows in set (0.00 sec)
补充：如果字段具有默认值的场景下，也可以使用DEFAULT来赋值。
方式2
INSERT [INTO] tbl_name SET col_name={DEFAULT|expr}, ...
mysql> INSERT user SET name='yyy', salary=9999;
方式3
INSERT [INTO] tbl_name [(col_name, ...)] SELECT ...
说明：此方法可以将查询结果插入到指定的数据表。
# 创建一个新表top，存放工资前3的信息
mysql> CREATE TABLE top (
-> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
-> salary FLOAT UNSIGNED);
Query OK, 0 rows affected (0.29 sec)
# 使用 INSERT SELECT 插入数据
mysql> INSERT top(salary) SELECT salary FROM user ORDER BY salary DESC LIMIT 3;
Query OK, 3 rows affected (0.06 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from top;
+----+---------+
| id | salary |
+----+---------+
| 11 | 10999 |
| 12 | 9997.98 |
| 13 | 8888.98 |
+----+---------+
3 rows in set (0.00 sec)
更新
UPDATE table_reference SET col_name1={expr1|DEFAULT}[, col_name2={expr2|DEFAULT}] ... [WHERE where_condition];
- 后边没有where条件，就是所有的数据都更新。全员涨工资1000
mysql> SELECT * FROM user;
+----+------+------+----------+
| id | name | age | salary |
+----+------+------+----------+
| 1 | tom | 30 | 7888.98 |
| 2 | jack | NULL | 8997.98 |
| 3 | xxx | 34 | 19999.00 |
| 4 | yyy | NULL | 9999.00 |
+----+------+------+----------+
4 rows in set (0.00 sec)
mysql> UPDATE user SET salary=salary+1000;
Query OK, 4 rows affected (0.07 sec)
Rows matched: 4 Changed: 4 Warnings: 0
mysql> SELECT * FROM user;
+----+------+------+----------+
| id | name | age | salary |
+----+------+------+----------+
| 1 | tom | 30 | 8888.98 |
| 2 | jack | NULL | 9997.98 |
| 3 | xxx | 34 | 20999.00 |
| 4 | yyy | NULL | 10999.00 |
+----+------+------+----------+
4 rows in set (0.00 sec)
- id是奇数的年龄增加10岁
mysql> UPDATE user SET age=age+10 WHERE id%2=1;
Query OK, 2 rows affected (0.05 sec)
Rows matched: 2 Changed: 2 Warnings: 0
查询
SELECT expr, ... FROM tbl_name;
mysql> select * from user;
+------+------+---------+
| name | age | salary |
+------+------+---------+
| tom | 25 | 7888.98 |
| jack | NULL | 8997.98 |
+------+------+---------+
- 复杂形式(真的好复杂)
SELECT select_expr [, ...]
[
FROM table_references
[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}]
]
- 查询时间
mysql> SELECT NOW();
+---------------------+
| NOW() |
+---------------------+
| 2018-08-27 15:17:19 |
+---------------------+
1 row in set (0.00 sec)
- 设置别名
mysql> SELECT name AS username, id AS userid FROM user;
+----------+--------+
| username | userid |
+----------+--------+
| tom | 1 |
| jack | 2 |
| yyy | 4 |
+----------+--------+
3 rows in set (0.00 sec)
- 查询工资大于10000的名字和其id
mysql> SELECT name AS username, id AS userid FROM user WHERE salary>10000;
+----------+--------+
| username | userid |
+----------+--------+
| yyy | 4 |
+----------+--------+
1 row in set (0.00 sec)
- 根据年龄分类
mysql> SELECT age FROM user GROUP BY age;
- 根据年龄分类，并且输出分类年龄中最高工资和最低工资
mysql> SELECT age, max(salary) as max_salary, min(salary) as min_salary FROM user GROUP BY age;
+------+------------+------------+
| age | max_salary | min_salary |
+------+------------+------------+
| NULL | 10999.00 | 9997.98 |
| 40 | 8888.98 | 6000.00 |
+------+------------+------------+
2 rows in set (0.00 sec)
- 根据年龄分类，并且输出分类年龄中最高工资和最低工资，将最高工资大于10000的类输出出来
mysql> SELECT age, max(salary) as max_salary, min(salary) as min_salary FROM user GROUP BY age HAVING max_salary>10000;
+------+------------+------------+
| age | max_salary | min_salary |
+------+------------+------------+
| NULL | 10999.00 | 9997.98 |
+------+------------+------------+
1 row in set (0.00 sec)
- 查询所有信息，按照工资的降序排序
mysql> SELECT * FROM user ORDER BY salary DESC;
+----+------+------+----------+
| id | name | age | salary |
+----+------+------+----------+
| 4 | yyy | NULL | 10999.00 |
| 2 | jack | NULL | 9997.98 |
| 1 | tom | 40 | 8888.98 |
| 5 | xxx | 40 | 6000.00 |
+----+------+------+----------+
4 rows in set (0.00 sec)
- 查询所有信息，按照工资的降序排序，只显示TOP3
mysql> SELECT * FROM user ORDER BY salary DESC LIMIT 3;
+----+------+------+----------+
| id | name | age | salary |
+----+------+------+----------+
| 4 | yyy | NULL | 10999.00 |
| 2 | jack | NULL | 9997.98 |
| 1 | tom | 40 | 8888.98 |
+----+------+------+----------+
3 rows in set (0.00 sec)
删除
DELETE FROM tbl_name [WHERE condition]
mysql> DELETE FROM user WHERE id=3;
Query OK, 1 row affected (0.07 sec)
自动编号
AUTO_INCREMENT; 默认起始值为1，递增量为1；
- 必须同主键一起使用
