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> 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> alter table student add sex enum("M","F");
Query OK, 0 rows affected (0.18 sec)
Records: 0 Duplicates: 0 Warnings: 0
插入字段
mysql> insert into student (name,age,register_date,sex) values("hanzb",22,"2018-04-21","MF");
ERROR 1265 (01000): Data truncated for column 'sex' at row 1
mysql> select * from student;
+----+----------+------+---------------+------+
| id | name | age | register_date | sex |
+----+----------+------+---------------+------+
| 1 | ZhanYang | 3 | 2018-06-20 | NULL |
| 4 | LiuJia | 33 | 2018-05-30 | NULL |
| 8 | JiaLiu | 5 | 2018-06-20 | NULL |
| 9 | JiaLiu | 8898 | 2018-04-20 | NULL |
| 10 | gaoyf | 8898 | 2018-04-20 | NULL |
| 11 | zhujh | 89 | 2018-04-20 | NULL |
| 12 | zhouha | 22 | 2018-04-20 | NULL |
+----+----------+------+---------------+------+
7 rows in set (0.00 sec)
mysql> insert into student (name,age,register_date,sex) values("hanzb",22,"2018-04-21","M");
Query OK, 1 row affected (0.02 sec)
mysql>
删除age字段：
mysql> select * from student;
+----+----------+------+---------------+------+
| id | name | age | register_date | sex |
+----+----------+------+---------------+------+
| 1 | ZhanYang | 3 | 2018-06-20 | NULL |
| 4 | LiuJia | 33 | 2018-05-30 | NULL |
| 8 | JiaLiu | 5 | 2018-06-20 | NULL |
| 9 | JiaLiu | 8898 | 2018-04-20 | NULL |
| 10 | gaoyf | 8898 | 2018-04-20 | NULL |
| 11 | zhujh | 89 | 2018-04-20 | NULL |
| 12 | zhouha | 22 | 2018-04-20 | NULL |
| 13 | hanzb | 22 | 2018-04-21 | M |
+----+----------+------+---------------+------+
8 rows in set (0.00 sec)
mysql> alter table student drop age;
Query OK, 0 rows affected (0.17 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select * from student;
+----+----------+---------------+------+
| id | name | register_date | sex |
+----+----------+---------------+------+
| 1 | ZhanYang | 2018-06-20 | NULL |
| 4 | LiuJia | 2018-05-30 | NULL |
| 8 | JiaLiu | 2018-06-20 | NULL |
| 9 | JiaLiu | 2018-04-20 | NULL |
| 10 | gaoyf | 2018-04-20 | NULL |
| 11 | zhujh | 2018-04-20 | NULL |
| 12 | zhouha | 2018-04-20 | NULL |
| 13 | hanzb | 2018-04-21 | M |
+----+----------+---------------+------+
8 rows in set (0.01 sec)
修改字段名：命令：MODIFY 和 CHANGE
如果需要修改字段类型及名称, 你可以在ALTER命令中使用 MODIFY 或 CHANGE 子句 。
语法：alter table testater_tbl modify c char(10);
使用 CHANGE 子句, 语法有很大的不同。 在 CHANGE 关键字之后，紧跟着的是你要修改的字段名，然后指定新字段名及类型。尝试如下实例：
mysql> ALTER TABLE testalter_tbl CHANGE i j BIGINT;
mysql> ALTER TABLE testalter_tbl CHANGE j j INT;
mysql> alter table student modify sex enum("M","F") no null;
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 'no null' at line 1
mysql> alter table student modify sex enum("M","F") not null;
ERROR 1138 (22004): Invalid use of NULL value
mysql> alter table student change sex enum("M","F") not null;
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 '("M","F") not null' at line 1
