数据库应用-mysql语句拾遗
mysql> desc stu;+---------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+---------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(16) | NO | UNI | NULL | |
| sex | enum('m','w') | NO | | m | |
| age | tinyint(3) unsigned | NO | | NULL | |
| classid | char(6) | NO | | NULL | |
+---------+---------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
-- 标准格式添加，指定所有的字段，给定所有的值
mysql> insert into stu(id,name,sex,age,classid) values(null,'zhangsan','m',20,'lnmp80');
Query OK, 1 row affected (0.09 sec)
-- 给定部分字段添加值（值和字段要对应上）
mysql> insert into stu(name,age,classid) values('lisi',22,'lnmp80');
Query OK, 1 row affected (0.03 sec)
-- 不给字段信息，来添加值。（值和表结构对应上）
mysql> insert into stu values(null,'wangwu','w',25,'lnmp80');
Query OK, 1 row affected (0.08 sec)
-- 批量添加数据
mysql> insert into stu(name,sex,age,classid)
-> values('qq','w',21,'lamp81'),
-> ('aa','m',26,'lnmp81'),
-> ('bb','w',20,'lnmp80');
Query OK, 3 rows affected (0.03 sec)
Records: 3Duplicates: 0Warnings: 0
mysql> select * from stu;
+----+----------+-----+-----+---------+
| id | name | sex | age | classid |
+----+----------+-----+-----+---------+
|1 | zhangsan | m |20 | lnmp80|
|2 | lisi | m |22 | lnmp80|
|3 | wangwu | w |25 | lnmp80|
|4 | qq | w |21 | lnmp81|
|5 | aa | m |26 | lnmp81|
|6 | bb | w |20 | lnmp80|
+----+----------+-----+-----+---------+
6 rows in set (0.00 sec)
mysql>
mysql> insert into stu values
-> (null,'zhaoliu','w',19,'lnmp81'),
-> (null,'tianqi','m',27,'lnmp82'),
-> (null,'uu','w',26,'lnmp80'),
-> (null,'yy','m',24,'lnmp82'),
-> (null,'pp','w',29,'lnmp81');
Query OK, 5 rows affected (0.03 sec)
Records: 5Duplicates: 0Warnings: 0
mysql> select * from stu;
+----+----------+-----+-----+---------+
| id | name | sex | age | classid |
+----+----------+-----+-----+---------+
|1 | zhangsan | m |20 | lnmp80|
|2 | lisi | m |22 | lnmp80|
|3 | wangwu | w |25 | lnmp80|
|4 | qq | w |21 | lnmp81|
|5 | aa | m |26 | lnmp81|
|6 | bb | w |20 | lnmp80|
|7 | zhaoliu| w |19 | lnmp81|
|8 | tianqi | m |27 | lnmp82|
|9 | uu | w |26 | lnmp80|
| 10 | yy | m |24 | lnmp82|
| 11 | pp | w |29 | lnmp81|
+----+----------+-----+-----+---------+
11 rows in set (0.00 sec)
mysql>
--2. 数据的修改：
--===================================================
--格式： update 表名 set 字段名=修改值[,字段名=修改值[,....]]
--实例
--将学号id值为5的信息，年龄改为28，班级设为lamp82
mysql> update stu set age=28,classid='lamp82' where id=5
Query OK, 1 row affected (0.11 sec)
Rows matched: 1Changed: 1Warnings: 0
mysql> select * from stu;
+----+----------+-----+-----+---------+
| id | name | sex | age | classid |
+----+----------+-----+-----+---------+
|1 | zhangsan | m |20 | lnmp80|
|2 | lisi | m |22 | lnmp80|
|3 | wangwu | w |25 | lnmp80|
|4 | qq | w |21 | lnmp81|
|5 | aa | m |28 | lnmp82|
|6 | bb | w |20 | lnmp80|
|7 | zhaoliu| w |19 | lnmp81|
|8 | tianqi | m |27 | lnmp82|
|9 | uu | w |26 | lnmp80|
| 10 | yy | m |24 | lnmp82|
| 11 | pp | w |29 | lnmp81|
+----+----------+-----+-----+---------+
11 rows in set (0.00 sec)
mysql>
--3. 数据的删除
---===========================================================================
--格式： delete from 表名
--删除实例：
--1.删除id号为5的信息
mysql> delete from stu where id=5;
--2.删除班级为lnmp80的男生信息
mysql> delete from stu where classid='lnmp80' and sex='m';
--3.删除年龄在21到25之间的信息
mysql> delete from stu where age>=21 and age delete from stu where age between 21 and 25;
--4.删除年龄在21到25之外的信息
mysql> delete from stu where age25;
mysql> delete from stu where age not between 21 and 25;
-- 4. 数据的查询
--=======================================================
--格式： select 字段名|* from 表名
-- [ where 搜索条件]
-- [ group by 分组列名]
-- [ order by 排序列名 ]
-- [ limit m[,n] 获取部分数据（分页） ]
实例：
mysql>
mysql> select * from stu;--查看所有字段的所有信息
+----+----------+-----+-----+---------+
| id | name | sex | age | classid |
+----+----------+-----+-----+---------+
|1 | zhangsan | m |20 | lnmp80|
|2 | lisi | m |22 | lnmp80|
|3 | wangwu | w |25 | lnmp80|
|4 | qq | w |21 | lnmp81|
|5 | aa | m |28 | lnmp82|
|6 | bb | w |20 | lnmp80|
|7 | zhaoliu| w |19 | lnmp81|
|8 | tianqi | m |27 | lnmp82|
|9 | uu | w |26 | lnmp80|
| 10 | yy | m |24 | lnmp82|
| 11 | pp | w |29 | lnmp81|
+----+----------+-----+-----+---------+
11 rows in set (0.00 sec)
-- 查看name/sex/age这几个字段的所有信息
mysql> select name,sex,age from stu;
+----------+-----+-----+
| name | sex | age |
+----------+-----+-----+
| zhangsan | m |20 |
| lisi | m |22 |
| wangwu | w |25 |
| qq | w |21 |
| aa | m |28 |
| bb | w |20 |
| zhaoliu| w |19 |
| tianqi | m |27 |
| uu | w |26 |
| yy | m |24 |
| pp | w |29 |
+----------+-----+-----+
11 rows in set (0.00 sec)
-- 查看数据，将name字段名换成username（其中as关键字可以省略不写）
mysql> select name as username,sex,age from stu;
-- 查看所有学生信息，并追加一列（年龄都加5的值），起个别名age2
mysql> select *,age+5age2 from stu;
+----+----------+-----+-----+---------+------+
| id | name | sex | age | classid | age2 |
+----+----------+-----+-----+---------+------+
|1 | zhangsan | m |20 | lnmp80| 25 |
|2 | lisi | m |22 | lnmp80| 27 |
.....
| 11 | pp | w |29 | lnmp81| 34 |
+----+----------+-----+-----+---------+------+
11 rows in set (0.00 sec)
-- 在查看学生信息时，追加一列。值为beijing，字段名为city
mysql> select *,"beijing" city from stu;
+----+----------+-----+-----+---------+---------+
| id | name | sex | age | classid | city |
+----+----------+-----+-----+---------+---------+
|1 | zhangsan | m |20 | lnmp80| beijing |
|2 | lisi | m |22 | lnmp80| beijing |
|3 | wangwu | w |25 | lnmp80| beijing |
....
| 10 | yy | m |24 | lnmp82| beijing |
| 11 | pp | w |29 | lnmp81| beijing |
+----+----------+-----+-----+---------+---------+
11 rows in set (0.00 sec)
-- 将字串aa和bb合并到一列中输出
mysql> select concat("aa","bb");
+-------------------+
| concat("aa","bb") |
+-------------------+
| aabb |
+-------------------+
1 row in set (0.02 sec)
-- 将stu表中班级和姓名字段合并到一列输出，起字段名为uname
mysql> select concat(classid,":",name) as uname from stu;
+-----------------+
| uname |
+-----------------+
| lnmp80:zhangsan |
| lnmp80:lisi |
。。。
| lnmp82:tianqi |
| lnmp80:uu |
| lnmp82:yy |
| lnmp81:pp |
+-----------------+
11 rows in set (0.00 sec)
mysql>
-- where条件查询语句
--1. 年龄在20至25岁的学生信息（包含20和25）
mysql> select * from stu where age>=20 and age select * from stu where age between 20 and 25;
--2. id号为3,5,8,9的学生信息。
mysql> select * from stu where id=3 or id=5 or id=8 or id=9
mysql> select * from stu where id in(3,5,8,9);
--3. 获取lamp80期的女生信息
mysql> select * fromstu where classid='lnmp80' and sex="w"
--4. 获取性别为m的lamp81和lamp82的学生信息
mysql> select * fromstu where classid in('lnmp81','lamp82') and sex="w"
--5. 查询数据时去除重复的数据
mysql> select distinct classid from stu;
+---------+
| classid |
+---------+
| lnmp80|
| lnmp81|
| lnmp82|
+---------+
3 rows in set (0.00 sec)
--6. 查询学生信息id不是1,3,5,8,10的信息
mysql> select * from stu where id not in(1,3,5,8,10);
--7. like模糊查询只支持两个通配符: '%'表示任意长度的任意值, '_'表示1位的任意值。
-- 获取name是由z字符开头的所有信息
mysql> select * from stu where name like 'z%';
+----+----------+-----+-----+---------+
| id | name | sex | age | classid |
+----+----------+-----+-----+---------+
|1 | zhangsan | m |20 | lnmp80|
|7 | zhaoliu| w |19 | lnmp81|
+----+----------+-----+-----+---------+
2 rows in set (0.00 sec)
-- 查看name值中包含a字符的所有信息
mysql> select * from stu where name like '%a%';
+----+----------+-----+-----+---------+
| id | name | sex | age | classid |
+----+----------+-----+-----+---------+
|1 | zhangsan | m |20 | lnmp80|
|3 | wangwu | w |25 | lnmp80|
|5 | aa | m |28 | lnmp82|
|7 | zhaoliu| w |19 | lnmp81|
|8 | tianqi | m |27 | lnmp82|
+----+----------+-----+-----+---------+
5 rows in set (0.00 sec)
-- 查看name是由两个字符构成的信息。
mysql> select * from stu where name like '__';
+----+------+-----+-----+---------+
| id | name | sex | age | classid |
+----+------+-----+-----+---------+
|4 | qq | w |21 | lnmp81|
|5 | aa | m |28 | lnmp82|
|6 | bb | w |20 | lnmp80|
|9 | uu | w |26 | lnmp80|
| 10 | yy | m |24 | lnmp82|
| 11 | pp | w |29 | lnmp81|
+----+------+-----+-----+---------+
6 rows in set (0.00 sec)
-- 统计查询（mysql的聚合函数：count() /sum() /max() /min() /avg()
-- 统计stu表的数据条数11，年龄最大29，最小19，平均年龄23.7273，年龄总和261
mysql> select count(*),max(age),min(age),avg(age),sum(age) from stu;
+----------+----------+----------+----------+----------+
| count(*) | max(age) | min(age) | avg(age) | sum(age) |
+----------+----------+----------+----------+----------+
| 11 | 29 | 19 |23.7273 | 261 |
+----------+----------+----------+----------+----------+
1 row in set (0.00 sec)
-- 分组查询： group by 字段名
-- 按班级号分组查询
mysql> select classid from stu group by classid;
+---------+
| classid |
+---------+
| lnmp80|
| lnmp81|
| lnmp82|
+---------+
3 rows in set (0.00 sec)
-- 按班级分组并统计，统计每个班的人数，最大年龄，最小年龄
mysql> select classid,count(*),max(age),min(age) from stu group by classid;
+---------+----------+----------+----------+
| classid | count(*) | max(age) | min(age) |
+---------+----------+----------+----------+
| lnmp80| 5 | 26 | 20 |
| lnmp81| 3 | 29 | 19 |
| lnmp82| 3 | 28 | 24 |
+---------+----------+----------+----------+
3 rows in set (0.00 sec)
-- 统计每个班男生的平均年龄
mysql> select classid,avg(age) from stu where sex='m' group by classid;
+---------+----------+
| classid | avg(age) |
+---------+----------+
| lnmp80|21.0000 |
| lnmp82|26.3333 |
+---------+----------+
2 rows in set (0.00 sec)
-- 按班级分组，统计每个班的平均年龄，并获取平均年龄在23及以上信息。
mysql> select classid,avg(age) from stu group by classid having avg(age)>=23;
+---------+----------+
| classid | avg(age) |
+---------+----------+
| lnmp81|23.0000 |
| lnmp82|26.3333 |
+---------+----------+
2 rows in set (0.02 sec)
--排序：order by 字段名
mysql> select* from stu; --没有排序
+----+----------+-----+-----+---------+
| id | name | sex | age | classid |
+----+----------+-----+-----+---------+
|1 | zhangsan | m |20 | lnmp80|
|2 | lisi | m |22 | lnmp80|
|3 | wangwu | w |25 | lnmp80|
。。。
|9 | uu | w |26 | lnmp80|
| 10 | yy | m |24 | lnmp82|
| 11 | pp | w |29 | lnmp81|
+----+----------+-----+-----+---------+
11 rows in set (0.00 sec)
--按照年龄升序排序
mysql> select* from stu order by age;
mysql> select* from stu order by age asc; --等价于上面语句
+----+----------+-----+-----+---------+
| id | name | sex | age | classid |
+----+----------+-----+-----+---------+
|7 | zhaoliu| w |19 | lnmp81|
|1 | zhangsan | m |20 | lnmp80|
|6 | bb | w |20 | lnmp80|
|4 | qq | w |21 | lnmp81|
|2 | lisi | m |22 | lnmp80|
| 10 | yy | m |24 | lnmp82|
|3 | wangwu | w |25 | lnmp80|
|9 | uu | w |26 | lnmp80|
|8 | tianqi | m |27 | lnmp82|
|5 | aa | m |28 | lnmp82|
| 11 | pp | w |29 | lnmp81|
+----+----------+-----+-----+---------+
11 rows in set (0.00 sec)
--多列排序：首先按班级升序，相同班级再按年龄降序排序
mysql> select* from stu order by classid,age desc;
+----+----------+-----+-----+---------+
| id | name | sex | age | classid |
+----+----------+-----+-----+---------+
|9 | uu | w |26 | lnmp80|
|3 | wangwu | w |25 | lnmp80|
|2 | lisi | m |22 | lnmp80|
|1 | zhangsan | m |20 | lnmp80|
|6 | bb | w |20 | lnmp80|
| 11 | pp | w |29 | lnmp81|
|4 | qq | w |21 | lnmp81|
|7 | zhaoliu| w |19 | lnmp81|
|5 | aa | m |28 | lnmp82|
|8 | tianqi | m |27 | lnmp82|
| 10 | yy | m |24 | lnmp82|
+----+----------+-----+-----+---------+
11 rows in set (0.00 sec)
--limit 分页：
--分页公式： limit （当前页-1）*页大小，页大小;
mysql> select * from stu limit 5; --获取前5条信息
+----+----------+-----+-----+---------+
| id | name | sex | age | classid |
+----+----------+-----+-----+---------+
|1 | zhangsan | m |20 | lnmp80|
|2 | lisi | m |22 | lnmp80|
|3 | wangwu | w |25 | lnmp80|
|4 | qq | w |21 | lnmp81|
|5 | aa | m |28 | lnmp82|
+----+----------+-----+-----+---------+
5 rows in set (0.00 sec)
--获取年龄最大的3条数据
mysql> select * from stu order by age desc limit 3;
+----+--------+-----+-----+---------+
| id | name | sex | age | classid |
+----+--------+-----+-----+---------+
| 11 | pp | w |29 | lnmp81|
|5 | aa | m |28 | lnmp82|
|8 | tianqi | m |27 | lnmp82|
+----+--------+-----+-----+---------+
3 rows in set (0.00 sec)
--以4条数据为一页，取第一页
mysql> select * from stu limit 0,4;
+----+----------+-----+-----+---------+
| id | name | sex | age | classid |
+----+----------+-----+-----+---------+
|1 | zhangsan | m |20 | lnmp80|
|2 | lisi | m |22 | lnmp80|
|3 | wangwu | w |25 | lnmp80|
|4 | qq | w |21 | lnmp81|
+----+----------+-----+-----+---------+
4 rows in set (0.00 sec)
--以4条数据为1页，取第二页
mysql> select * from stu limit 4,4;
+----+---------+-----+-----+---------+
| id | name | sex | age | classid |
+----+---------+-----+-----+---------+
|5 | aa | m |28 | lnmp82|
|6 | bb | w |20 | lnmp80|
|7 | zhaoliu | w |19 | lnmp81|
|8 | tianqi| m |27 | lnmp82|
+----+---------+-----+-----+---------+
4 rows in set (0.00 sec)
mysql> select * from stu limit 8,4;
+----+------+-----+-----+---------+
| id | name | sex | age | classid |
+----+------+-----+-----+---------+
|9 | uu | w |26 | lnmp80|
| 10 | yy | m |24 | lnmp82|
| 11 | pp | w |29 | lnmp81|
+----+------+-----+-----+---------+
3 rows in set (0.00 sec)
-- 数据的导入和导出
--================================================
-- 导出lnmp80数据的所有信息
D:\xampp\htdocs\lamp80>mysqldump -u root -p lnmp80 >lnmp80_20170106.sql
Enter password:
-- 只导出lamp80库下的stu表信息
D:\xampp\htdocs\lnmp80>mysqldump -u root -p lnmp80 stu>stu.sql
Enter password:
D:\xampp\htdocs\lnmp80\python_mysql03>
--数据库lnmp80的导入（要求数据库必须存在）
D:\xampp\htdocs\lnmp80>mysql -u root -p lnmp80mysql -u root -p lnmp80 < stu.sql
页:
[1]