mysql> show databases
-> ;
+--------------------+
| Database |
+--------------------+
| information_schema |
| alarms |
| dashboard |
| falcon_portal |
| graph |
| mysql |
| oldboydb |
| performance_schema |
| uic |
| zabbix |
+--------------------+
10 rows in set (0.00 sec)
mysql> use oldboydb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql>
mysql>
mysql>
mysql> show tables;
+--------------------+
| Tables_in_oldboydb |
+--------------------+
| student |
+--------------------+
1 row in set (0.00 sec)
增加数据
mysql> insert into student(name,age,register_date) values("JiaLiu",5,"2018-06-20");
Query OK, 1 row affected (0.01 sec)
mysql> insert into student(name,age,register_date) values("JiaLiu",8,"2018-06-20");
Query OK, 1 row affected (0.00 sec)
mysql> select * from student;
+----+----------+-----+---------------+
| id | name | age | register_date |
+----+----------+-----+---------------+
| 1 | ZhanYang | 3 | 2018-06-20 |
| 2 | JiaLiu | 30 | 2018-06-20 |
| 3 | JiaLiu | 5 | 2018-06-20 |
| 4 | JiaLiu | 8 | 2018-06-20 |
+----+----------+-----+---------------+
4 rows in set (0.00 sec)
查找数据
mysql> select * from student offset 2;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '2' at line 1
mysql> select * from student limit 2;
+----+----------+-----+---------------+
| id | name | age | register_date |
+----+----------+-----+---------------+
| 1 | ZhanYang | 3 | 2018-06-20 |
| 2 | JiaLiu | 30 | 2018-06-20 |
+----+----------+-----+---------------+
2 rows in set (0.00 sec)
mysql> select * from student limit 2 offset 1;
+----+--------+-----+---------------+
| id | name | age | register_date |
+----+--------+-----+---------------+
| 2 | JiaLiu | 30 | 2018-06-20 |
| 3 | JiaLiu | 5 | 2018-06-20 |
+----+--------+-----+---------------+
2 rows in set (0.00 sec)
mysql> select * from student limit 2 offset 2;
+----+--------+-----+---------------+
| id | name | age | register_date |
+----+--------+-----+---------------+
| 3 | JiaLiu | 5 | 2018-06-20 |
| 4 | JiaLiu | 8 | 2018-06-20 |
+----+--------+-----+---------------+
2 rows in set (0.00 sec)
mysql> select * from student where id>3
-> ;
+----+--------+-----+---------------+
| id | name | age | register_date |
+----+--------+-----+---------------+
| 4 | JiaLiu | 8 | 2018-06-20 |
+----+--------+-----+---------------+
1 row in set (0.01 sec)
mysql> select * from student where id>3 and age<20;
+----+--------+-----+---------------+
| id | name | age | register_date |
+----+--------+-----+---------------+
| 4 | JiaLiu | 8 | 2018-06-20 |
+----+--------+-----+---------------+
1 row in set (0.00 sec)
mysql> insert into student(name,age,register_date) values("JiaLiu",8,"2018-04-20");
Query OK, 1 row affected (0.01 sec)
mysql> insert into student(name,age,register_date) values("JiaLiu",50,"2018-05-20");
Query OK, 1 row affected (0.00 sec)
mysql> insert into student(name,age,register_date) values("JiaLiu",40,"2018-09-20");
Query OK, 1 row affected (0.01 sec)
mysql> desc student;
+---------------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+----------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | char(32) | NO | | NULL | |
| age | int(11) | NO | | NULL | |
| register_date | date | NO | | NULL | |
+---------------+----------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql> selent * from student;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'selent * from student' at line 1
mysql> selent * from student;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'selent * from student' at line 1
mysql> select * from student;
+----+----------+-----+---------------+
| id | name | age | register_date |
+----+----------+-----+---------------+
| 1 | ZhanYang | 3 | 2018-06-20 |
| 2 | JiaLiu | 30 | 2018-06-20 |
| 3 | JiaLiu | 5 | 2018-06-20 |
| 4 | JiaLiu | 8 | 2018-06-20 |
| 5 | JiaLiu | 8 | 2018-04-20 |
| 6 | JiaLiu | 50 | 2018-05-20 |
| 7 | JiaLiu | 40 | 2018-09-20 |
+----+----------+-----+---------------+
7 rows in set (0.00 sec)
mysql> select * from student where register_date like "2018-06%";
+----+----------+-----+---------------+
| id | name | age | register_date |
+----+----------+-----+---------------+
| 1 | ZhanYang | 3 | 2018-06-20 |
| 2 | JiaLiu | 30 | 2018-06-20 |
| 3 | JiaLiu | 5 | 2018-06-20 |
| 4 | JiaLiu | 8 | 2018-06-20 |
+----+----------+-----+---------------+
4 rows in set, 1 warning (0.00 sec)
改表数据
mysql> update student set name="LiuJia",age=33 where id=4;
Query OK, 1 row affected (0.10 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from student;
+----+----------+-----+---------------+
| id | name | age | register_date |
+----+----------+-----+---------------+
| 1 | ZhanYang | 3 | 2018-06-20 |
| 2 | JiaLiu | 30 | 2018-06-20 |
| 3 | JiaLiu | 5 | 2018-06-20 |
| 4 | LiuJia | 33 | 2018-06-20 |
| 5 | JiaLiu | 8 | 2018-04-20 |
| 6 | JiaLiu | 50 | 2018-05-20 |
| 7 | JiaLiu | 40 | 2018-09-20 |
+----+----------+-----+---------------+
7 rows in set (0.00 sec)
mysql>
删除数据：
mysql> select * from student;
+----+----------+-----+---------------+
| id | name | age | register_date |
+----+----------+-----+---------------+
| 1 | ZhanYang | 3 | 2018-06-20 |
| 2 | JiaLiu | 30 | 2018-06-20 |
| 3 | JiaLiu | 5 | 2018-06-20 |
| 4 | LiuJia | 33 | 2018-06-20 |
| 5 | JiaLiu | 8 | 2018-04-20 |
| 6 | JiaLiu | 50 | 2018-05-20 |
| 7 | JiaLiu | 40 | 2018-09-20 |
+----+----------+-----+---------------+
7 rows in set (0.00 sec)
mysql> delete from student where name="JiaLiu";
Query OK, 5 rows affected (0.01 sec)
mysql> select * from student;
+----+----------+-----+---------------+
| id | name | age | register_date |
+----+----------+-----+---------------+
| 1 | ZhanYang | 3 | 2018-06-20 |
| 4 | LiuJia | 33 | 2018-06-20 |
+----+----------+-----+---------------+
2 rows in set (0.00 sec)
mysql>
排序：
升序 指定id
mysql> select * from student order by id;
+----+----------+-----+---------------+
| id | name | age | register_date |
+----+----------+-----+---------------+
| 1 | ZhanYang | 3 | 2018-06-20 |
| 4 | LiuJia | 33 | 2018-06-20 |
+----+----------+-----+---------------+
2 rows in set (0.00 sec)
降序
mysql> select * from student order by id desc;
+----+----------+-----+---------------+
| id | name | age | register_date |
+----+----------+-----+---------------+
| 4 | LiuJia | 33 | 2018-06-20 |
| 1 | ZhanYang | 3 | 2018-06-20 |
+----+----------+-----+---------------+
2 rows in set (0.00 sec)
mysql>
统计分组：
通过NAME分组:
mysql> select name,count(*) from student group by name;
+----------+----------+
| name | count(*) |
+----------+----------+
| gaoyf | 1 |
| JiaLiu | 2 |
| LiuJia | 1 |
| ZhanYang | 1 |
| zhouha | 1 |
| zhujh | 1 |
+----------+----------+
6 rows in set (0.00 sec)
使用注册日期分组：
mysql> select register_date ,count(*) from student group by register_date ;
+---------------+----------+
| register_date | count(*) |
+---------------+----------+
| 2018-04-20 | 4 |
| 2018-05-30 | 1 |
| 2018-06-20 | 2 |
+---------------+----------+
3 rows in set (0.00 sec)
将count设置别名为stu_num：
mysql> select register_date ,count(*) as stu_num from student group by register_date ;
+---------------+---------+
| register_date | stu_num |
+---------------+---------+
| 2018-04-20 | 4 |
| 2018-05-30 | 1 |
| 2018-06-20 | 2 |
+---------------+---------+
3 rows in set (0.00 sec)
mysql>
SUM 统计总和，这里是统计年龄总和：
mysql> select name ,sum(age) from student;
+----------+----------+
| name | sum(age) |
+----------+----------+
| ZhanYang | 17948 |
+----------+----------+
1 row in set (0.00 sec)
mysql>
mysql> select name ,sum(age) from student group by name ;
+----------+----------+
| name | sum(age) |
+----------+----------+
| gaoyf | 8898 |
| JiaLiu | 8903 |
| LiuJia | 33 |
| ZhanYang | 3 |
| zhouha | 22 |
| zhujh | 89 |
+----------+----------+
6 rows in set (0.00 sec)
mysql> select name ,sum(age) from student group by name with rollup;
+----------+----------+
| name | sum(age) |
+----------+----------+
| gaoyf | 8898 |
| JiaLiu | 8903 |
| LiuJia | 33 |
| ZhanYang | 3 |
| zhouha | 22 |
| zhujh | 89 |
| NULL | 17948 |
+----------+----------+
7 rows in set (0.01 sec)
mysql>
mysql> select coalesce(name ,sum(age)) from student group by name with rollup;
+--------------------------+
| coalesce(name ,sum(age)) |
+--------------------------+
| gaoyf |
| JiaLiu |
| LiuJia |
| ZhanYang |
| zhouha |
| zhujh |
| 17948 |
+--------------------------+
7 rows in set (0.00 sec)
mysql>
mysql增删改查
原文：http://blog.51cto.com/zhanx/2153495
