数据库的创建，数据的增删改查
显示数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.03 sec)
创建数据库（下面的方式创建的只支持英文）
mysql> create database emydb;
Query OK, 1 row affected (0.00 sec)
显示数据库，有新增的数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| emydb |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
这样创建的数据库只能支持英文输入
mysql> show create database emydb;
+----------+------------------------------------------------------------------+
| Database | Create Database |
+----------+------------------------------------------------------------------+
| emydb | CREATE DATABASE `emydb` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+------------------------------------------------------------------+
1 row in set (0.00 sec)
删除数据库（实际使用中，不要删数据库）
mysql> drop database emydb;
Query OK, 0 rows affected (0.04 sec)
重新创建数据库，要支持英文和中文
mysql> create database TestDB charset utf8;
Query OK, 1 row affected (0.00 sec)
mysql> show create database TestDB;
+----------+-----------------------------------------------------------------+
| Database | Create Database |
+----------+-----------------------------------------------------------------+
| TestDB | CREATE DATABASE `TestDB` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+-----------------------------------------------------------------+
1 row in set (0.00 sec)
创建表：
mysql> use TestDB; 切换数据库
mysql> create table student(id int auto_increment,name char(32) not null,age int not null,register_date date not null,primary key (id));创建表
mysql> show tables;查看表
+------------------+
| Tables_in_TestDB |
+------------------+
| student |
+------------------+
1 row in set (0.00 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> insert into student(name,age,register_date) value('emy',42,'2018-08-16'); 插入一条数据
Query OK, 1 row affected (0.07 sec)
（其中id显示为递增，不需要插入数据；可多次插入不同的数据）
mysql> select * from student;查看表数据
mysql> select * from student;
+----+---------+-----+---------------+
| id | name | age | register_date |
+----+---------+-----+---------------+
| 1 | emy | 42 | 2018-08-16 |
| 2 | battier | 6 | 2018-08-16 |
| 3 | barbie | 6 | 2018-08-16 |
| 4 | barbie | 1 | 2018-08-16 |
| 5 | fanwen | 28 | 2018-08-16 |
+----+---------+-----+---------------+
5 rows in set (0.00 sec)
mysql> select * from student limit 2 offset 2;查看从3开始的两条数据
+----+--------+-----+---------------+
| id | name | age | register_date |
+----+--------+-----+---------------+
| 3 | barbie | 6 | 2018-08-16 |
| 4 | barbie | 1 | 2018-08-16 |
+----+--------+-----+---------------+
2 rows in set (0.00 sec)
mysql> select * from student where id>3;条件查询（查询ID大于3的所有数据）
+----+--------+-----+---------------+
| id | name | age | register_date |
+----+--------+-----+---------------+
| 4 | barbie | 1 | 2018-08-16 |
| 5 | fanwen | 28 | 2018-08-16 |
+----+--------+-----+---------------+
2 rows in set (0.02 sec)
mysql> select * from student where id>3 and age<28; 组合条件查询（id>3和age<28）
+----+--------+-----+---------------+
| id | name | age | register_date |
+----+--------+-----+---------------+
| 4 | barbie | 1 | 2018-08-16 |
+----+--------+-----+---------------+
1 row in set (0.00 sec)
mysql> select * from student where id=4;（指定条件查询）
+----+--------+-----+---------------+
| id | name | age | register_date |
+----+--------+-----+---------------+
| 4 | barbie | 1 | 2018-08-16 |
+----+--------+-----+---------------+
1 row in set (0.00 sec)
mysql> select * from student where register_date like "2018-08%";（模糊查询）
+----+---------+-----+---------------+
| id | name | age | register_date |
+----+---------+-----+---------------+
| 1 | emy | 42 | 2018-08-16 |
| 2 | battier | 6 | 2018-08-16 |
| 3 | barbie | 6 | 2018-08-16 |
| 4 | barbie | 1 | 2018-08-16 |
| 5 | fanwen | 28 | 2018-08-16 |
+----+---------+-----+---------------+
5 rows in set, 1 warning (0.00 sec)
mysql> select * from student ;查看原始表，下面对其进行修改
+----+------+-----+---------------+
| id | name | age | register_date |
+----+------+-----+---------------+
| 1 | emy | 28 | 2018-08-16 |
| 2 | emy | 28 | 2018-08-16 |
| 3 | emy | 28 | 2018-08-16 |
| 4 | emy | 28 | 2018-08-16 |
| 5 | emy | 28 | 2018-08-16 |
| 6 | emy | 28 | 2012-08-16 |
+----+------+-----+---------------+
mysql> update student set name='battier',age=6 where id =2;修改id=2的name为battier，age=6
Query OK, 1 row affected (0.04 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from student ;
+----+---------+-----+---------------+
| id | name | age | register_date |
+----+---------+-----+---------------+
| 1 | emy | 28 | 2018-08-16 |
| 2 | battier | 6 | 2018-08-16 |
| 3 | emy | 28 | 2018-08-16 |
| 4 | emy | 28 | 2018-08-16 |
| 5 | emy | 28 | 2018-08-16 |
| 6 | emy | 28 | 2012-08-16 |
+----+---------+-----+---------------+
6 rows in set (0.00 sec)
mysql> update student set name='barbie',age=0.5 where id =3;
Query OK, 1 row affected (0.04 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from student ;
+----+---------+-----+---------------+
| id | name | age | register_date |
+----+---------+-----+---------------+
| 1 | emy | 28 | 2018-08-16 |
| 2 | battier | 6 | 2018-08-16 |
| 3 | barbie | 1 | 2018-08-16 |
| 4 | emy | 28 | 2018-08-16 |
| 5 | emy | 28 | 2018-08-16 |
| 6 | emy | 28 | 2012-08-16 |
+----+---------+-----+---------------+
6 rows in set (0.01 sec)
mysql> mysql> update student set name='jingxi',age=18 where id>4;按条件批量生产
mysql> select * from student ;
| id | name | age | register_date |
+----+---------+-----+---------------+
| 1 | emy | 28 | 2018-08-16 |
| 2 | battier | 6 | 2018-08-16 |
| 3 | barbie | 1 | 2018-08-16 |
| 4 | fanwen | 33 | 2018-08-16 |
| 5 | jingxi | 18 | 2018-08-16 |
| 6 | jingxi | 18 | 2012-08-16 |
+----+---------+-----+---------------+
6 rows in set (0.00 sec)
mysql> delete from student where name = "jingxi"; 条件name=jingxi数据都删除
Query OK, 2 rows affected (0.05 sec)
mysql> select * from student order by id desc ;按照id降序排序显示（默认为升序排序）
+----+---------+-----+---------------+
| id | name | age | register_date |
+----+---------+-----+---------------+
| 4 | fanwen | 33 | 2018-08-16 |
| 3 | barbie | 1 | 2018-08-16 |
| 2 | battier | 6 | 2018-08-16 |
| 1 | emy | 28 | 2018-08-16 |
+----+---------+-----+---------------+
4 rows in set (0.00 sec)
分组统计group by
mysql> select * from student;原始表
mysql> select * from student;
| id | name | age | register_date |
+----+---------+-----+---------------+
| 1 | emy | 28 | 2018-08-16 |
| 2 | battier | 6 | 2018-08-16 |
| 3 | barbie | 1 | 2018-08-16 |
| 4 | fanwen | 33 | 2018-08-16 |
| 7 | emy | 18 | 2017-08-16 |
| 8 | emy | 18 | 2016-08-16 |
| 9 | emy | 18 | 2015-08-16 |
| 10 | battier | 100 | 2112-02-26 |
| 11 | battier | 99 | 2111-02-26 |
+----+---------+-----+---------------+
9 rows in set (0.00 sec)
mysql> select name,count(*) from student group by name;统计name
+---------+----------+
| name | count(*) |
+---------+----------+
| barbie | 1 |
| battier | 3 |
| emy | 4 |
| fanwen | 1 |
+---------+----------+
4 rows in set (0.00 sec)
mysql> select register_date,count(*) from student group by register_date;
+---------------+----------+
| register_date | count(*) |
+---------------+----------+
| 2015-08-16 | 1 |
| 2016-08-16 | 1 |
| 2017-08-16 | 1 |
| 2018-08-16 | 4 |
| 2111-02-26 | 1 |
| 2112-02-26 | 1 |
+---------------+----------+
6 rows in set (0.00 sec)
mysql> select register_date,count(*) as stu_num from student group by register_date;取别名
+---------------+---------+
| register_date | stu_num |
+---------------+---------+
| 2015-08-16 | 1 |
| 2016-08-16 | 1 |
| 2017-08-16 | 1 |
| 2018-08-16 | 4 |
| 2111-02-26 | 1 |
| 2112-02-26 | 1 |
+---------------+---------+
6 rows in set (0.01 sec)
mysql> select name,sum(age) from student group by name;按名字统计年龄
+---------+----------+
| name | sum(age) |
+---------+----------+
| barbie | 1 |
| battier | 205 |
| emy | 82 |
| fanwen | 33 |
+---------+----------+
4 rows in set (0.00 sec)
mysql> select name,sum(age) from student group by name with rollup;统计所有学生的age
+---------+----------+
| name | sum(age) |
+---------+----------+
| barbie | 1 |
| battier | 205 |
| emy | 82 |
| fanwen | 33 |
| NULL | 321 |
+---------+----------+
5 rows in set (0.00 sec)
mysql> select coalesce(name,'Total_age'),sum(age) from student group by name with rollup;定义学生年龄总数名，对比与上一条语句的不同
+----------------------------+----------+
| coalesce(name,'Total_age') | sum(age) |
+----------------------------+----------+
| barbie | 1 |
| battier | 205 |
| emy | 82 |
| fanwen | 33 |
| Total_age | 321 |
+----------------------------+----------+
5 rows in set (0.00 sec)