MySQL—基本操作

1. 基本操作

显示现有的数据库：

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| hiveMeta           |
| mydb               |
| mysql              |
| test               |
+--------------------+
5 rows in set (0.00 sec)

使用mydb数据库：

mysql> use mydb;
Database changed

显示现有的表：

mysql> show tables;
Empty set (0.00 sec)

创建一张新的表：

mysql> create table emp(
    -> id int(11) auto_increment,
    -> name varchar(100),
    -> gender char(1),
    -> hiredate date,
    -> salary float(10,2),
    -> job varchar(100),
    -> comm float(7,2),
    -> primary key(id)
    -> );

显示现有的表：

mysql> show tables;
+----------------+
| Tables_in_mydb |
+----------------+
| emp            |
+----------------+
1 row in set (0.00 sec)

查看表 emp的结构：

mysql> describe emp;
+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| id       | int(11)      | NO   | PRI | NULL    | auto_increment |
| name     | varchar(100) | YES  |     | NULL    |                |
| gender   | char(1)      | YES  |     | NULL    |                |
| hiredate | date         | YES  |     | NULL    |                |
| salary   | float(10,2)  | YES  |     | NULL    |                |
| job      | varchar(100) | YES  |     | NULL    |                |
| comm     | float(7,2)   | YES  |     | NULL    |                |
+----------+--------------+------+-----+---------+----------------+
7 rows in set (0.00 sec)

向表emp添加数据：

mysql> insert into emp
    -> (name,gender,hiredate,salary,job,comm)
    -> values
    -> ("张三","男","2016-2-12",5000,"老师",200);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> insert into emp
    -> (name,gender,hiredate,salary,job,comm)
    -> values
    -> ("李四","男","2016-2-13",8000,"设计师",500);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> insert into emp
    -> (name,gender,hiredate,salary,job,comm)
    -> values
    -> ("王五","男","2016-1-25",1000,"总监",1000);
Query OK, 1 row affected, 1 warning (0.00 sec)

查询表emp数据：

mysql> select * from emp;
+----+--------+--------+------------+---------+-----------+---------+
| id | name   | gender | hiredate   | salary  | job       | comm    |
+----+--------+--------+------------+---------+-----------+---------+
|  1 | 张三   | 男     | 2016-02-12 | 5000.00 | 老师      |  200.00 |
|  2 | 李四   | 男     | 2016-02-13 | 8000.00 | 设计师    |  500.00 |
|  3 | 王五   | 男     | 2016-01-25 | 1000.00 | 总监      | 1000.00 |
+----+--------+--------+------------+---------+-----------+---------+
3 rows in set (0.00 sec)

修改表emp中的数据：

mysql> update emp
    -> set gender="man"
    -> where id =1;
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 1

mysql> update emp set gender="man" where id =2;
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 1

mysql> update emp set gender="man" where id =3;
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 1

再次查询表emp中数据：

mysql> select * from emp;
+----+--------+--------+------------+---------+-----------+---------+
| id | name   | gender | hiredate   | salary  | job       | comm    |
+----+--------+--------+------------+---------+-----------+---------+
|  1 | 张三   | m      | 2016-02-12 | 5000.00 | 老师      |  200.00 |
|  2 | 李四   | m      | 2016-02-13 | 8000.00 | 设计师    |  500.00 |
|  3 | 王五   | m      | 2016-01-25 | 1000.00 | 总监      | 1000.00 |
+----+--------+--------+------------+---------+-----------+---------+
3 rows in set (0.00 sec)

删除王五的相关信息：

mysql> delete from emp where id=3;
Query OK, 1 row affected (0.07 sec)

mysql> select * from emp;
+----+--------+--------+------------+---------+-----------+--------+
| id | name   | gender | hiredate   | salary  | job       | comm   |
+----+--------+--------+------------+---------+-----------+--------+
|  1 | 张三   | m      | 2016-02-12 | 5000.00 | 老师      | 200.00 |
|  2 | 李四   | m      | 2016-02-13 | 8000.00 | 设计师    | 500.00 |
+----+--------+--------+------------+---------+-----------+--------+
2 rows in set (0.00 sec)

2. select操作

查询所有员工信息：

mysql> select id,name,gender,hiredate,salary,job,comm from emp;
+----+--------+--------+------------+---------+-----------+---------+
| id | name   | gender | hiredate   | salary  | job       | comm    |
+----+--------+--------+------------+---------+-----------+---------+
|  1 | 张三   | m      | 2016-02-12 | 5000.00 | 老师      |  200.00 |
|  2 | 李四   | m      | 2016-02-13 | 8000.00 | 设计师    |  500.00 |
|  4 | 王五   | f      | 2016-01-25 | 1000.00 | 总监      | 1000.00 |
+----+--------+--------+------------+---------+-----------+---------+
3 rows in set (0.00 sec)

在没有表被引用的情况下，允许使用dual作为假的表名。

mysql> select 1+1 from dual;
+-----+
| 1+1 |
+-----+
|   2 |
+-----+
1 row in set (0.00 sec)

查询id为1的员工的信息：

mysql> select * from emp where id=1;
+----+--------+--------+------------+---------+--------+--------+
| id | name   | gender | hiredate   | salary  | job    | comm   |
+----+--------+--------+------------+---------+--------+--------+
|  1 | 张三   | m      | 2016-02-12 | 5000.00 | 老师   | 200.00 |
+----+--------+--------+------------+---------+--------+--------+
1 row in set (0.00 sec)

查询职业为空的员工信息：

mysql> select * from emp where job is null;
Empty set (0.00 sec)

查询id为1的员工的姓名和性别：

mysql> select name,gender from emp where id=1;
+--------+--------+
| name   | gender |
+--------+--------+
| 张三   | m      |
+--------+--------+
1 row in set (0.00 sec)

使用别名查询id为1的员工的姓名和性别：

mysql> select e.name as 姓名,e.gender as 性别 from emp as e where id=1;
+--------+--------+
| 姓名   | 性别   |
+--------+--------+
| 张三   | m      |
+--------+--------+
1 row in set (0.00 sec)

查询性别为女和指定日期的员工信息：

mysql> select * from emp where gender="f" and hiredate="2016-1-25";
+----+--------+--------+------------+---------+--------+---------+
| id | name   | gender | hiredate   | salary  | job    | comm    |
+----+--------+--------+------------+---------+--------+---------+
|  4 | 王五   | f      | 2016-01-25 | 1000.00 | 总监   | 1000.00 |
+----+--------+--------+------------+---------+--------+---------+
1 row in set (0.00 sec)

查询id大于2的员工信息：

mysql> select * from emp where id>2;
+----+--------+--------+------------+---------+--------+---------+
| id | name   | gender | hiredate   | salary  | job    | comm    |
+----+--------+--------+------------+---------+--------+---------+
|  4 | 王五   | f      | 2016-01-25 | 1000.00 | 总监   | 1000.00 |
+----+--------+--------+------------+---------+--------+---------+
1 row in set (0.00 sec)

查询性别为男或者id小于3的员工：

mysql> select * from emp where gender="m" or id<3;
+----+--------+--------+------------+---------+-----------+--------+
| id | name   | gender | hiredate   | salary  | job       | comm   |
+----+--------+--------+------------+---------+-----------+--------+
|  1 | 张三   | m      | 2016-02-12 | 5000.00 | 老师      | 200.00 |
|  2 | 李四   | m      | 2016-02-13 | 8000.00 | 设计师    | 500.00 |
+----+--------+--------+------------+---------+-----------+--------+
2 rows in set (0.00 sec)

查询名字最后一个字符为“五”的员工（%）：

mysql> select * from emp where name like "%五";
+----+--------+--------+------------+---------+--------+---------+
| id | name   | gender | hiredate   | salary  | job    | comm    |
+----+--------+--------+------------+---------+--------+---------+
|  4 | 王五   | f      | 2016-01-25 | 1000.00 | 总监   | 1000.00 |
+----+--------+--------+------------+---------+--------+---------+
1 row in set (0.00 sec)

查询姓名与指定字符开头的员工：

mysql> select * from emp where name like "王%";
+----+--------+--------+------------+---------+--------+---------+
| id | name   | gender | hiredate   | salary  | job    | comm    |
+----+--------+--------+------------+---------+--------+---------+
|  4 | 王五   | f      | 2016-01-25 | 1000.00 | 总监   | 1000.00 |
+----+--------+--------+------------+---------+--------+---------+
1 row in set (0.00 sec)

查询姓名中包含“四”的员工：

mysql> select * from emp where name like "%四%";
+----+--------+--------+------------+---------+-----------+--------+
| id | name   | gender | hiredate   | salary  | job       | comm   |
+----+--------+--------+------------+---------+-----------+--------+
|  2 | 李四   | m      | 2016-02-13 | 8000.00 | 设计师    | 500.00 |
+----+--------+--------+------------+---------+-----------+--------+
1 row in set (0.00 sec)

查询所有员工信息，并按日期降序排序（order by：默认为升序：asc，降序使用desc）：

mysql> select * from emp order by hiredate desc;
+----+--------+--------+------------+---------+-----------+---------+
| id | name   | gender | hiredate   | salary  | job       | comm    |
+----+--------+--------+------------+---------+-----------+---------+
|  2 | 李四   | m      | 2016-02-13 | 8000.00 | 设计师    |  500.00 |
|  1 | 张三   | m      | 2016-02-12 | 5000.00 | 老师      |  200.00 |
|  4 | 王五   | f      | 2016-01-25 | 1000.00 | 总监      | 1000.00 |
+----+--------+--------+------------+---------+-----------+---------+
3 rows in set (0.00 sec)

多个排序条件：当第一个条件相同，以第二个条件排序。

mysql> select * from emp order by hiredate desc,salary asc;
+----+--------+--------+------------+---------+-----------+---------+
| id | name   | gender | hiredate   | salary  | job       | comm    |
+----+--------+--------+------------+---------+-----------+---------+
|  2 | 李四   | m      | 2016-02-13 | 8000.00 | 设计师    |  500.00 |
|  1 | 张三   | m      | 2016-02-12 | 5000.00 | 老师      |  200.00 |
|  4 | 王五   | f      | 2016-01-25 | 1000.00 | 总监      | 1000.00 |
+----+--------+--------+------------+---------+-----------+---------+
3 rows in set (0.00 sec

按照性别分组查询男女员工的人数（group by）：

mysql> select gender,count(gender) from emp group by gender;
+--------+---------------+
| gender | count(gender) |
+--------+---------------+
| f      |             1 |
| m      |             2 |
+--------+---------------+
2 rows in set (0.00 sec)

按照性别分组查询女员工的总人数（group by、having）：

mysql> select gender,count(gender) as 人数 from emp group by gender having gender="f";
+--------+--------+
| gender | 人数   |
+--------+--------+
| f      |      1 |
+--------+--------+
1 row in set (0.00 sec)

也可以使用where：

mysql> select gender,count(gender) as 人数 from emp where gender="f" group by gender;
+--------+--------+
| gender | 人数   |
+--------+--------+
| f      |      1 |
+--------+--------+
1 row in set (0.00 sec)

查询表的总记录数：

mysql> select count(*) from emp;
+----------+
| count(*) |
+----------+
|        3 |
+----------+
1 row in set (0.00 sec)

查询员工记录的前2条：

mysql> select * from emp limit 0,2;
+----+--------+--------+------------+---------+-----------+--------+
| id | name   | gender | hiredate   | salary  | job       | comm   |
+----+--------+--------+------------+---------+-----------+--------+
|  1 | 张三   | m      | 2016-02-12 | 5000.00 | 老师      | 200.00 |
|  2 | 李四   | m      | 2016-02-12 | 8000.00 | 设计师    | 500.00 |
+----+--------+--------+------------+---------+-----------+--------+
2 rows in set (0.00 sec)

查询员工记录的1-3条：

mysql> select * from emp limit 1,3;
+----+--------+--------+------------+---------+-----------+---------+
| id | name   | gender | hiredate   | salary  | job       | comm    |
+----+--------+--------+------------+---------+-----------+---------+
|  2 | 李四   | m      | 2016-02-12 | 8000.00 | 设计师    |  500.00 |
|  4 | 王五   | f      | 2016-01-25 | 1000.00 | 总监      | 1000.00 |
+----+--------+--------+------------+---------+-----------+---------+
2 rows in set (0.00 sec)