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)