|
|
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: 3 Duplicates: 0 Warnings: 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: 5 Duplicates: 0 Warnings: 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 字段名=修改值[,字段名=修改值[,....]] [where 条件] [order by 排序] [limit 部分数据]
--实例
--将学号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: 1 Changed: 1 Warnings: 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 表名 [where 条件] [order by 排序] [limit 部分数据]
--删除实例：
--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 分组列名[having 分组后的子条件]]
-- [ order by 排序列名 [desc降序|asc升序(默认)]]
-- [ 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+5 age2 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 * from stu where classid='lnmp80' and sex="w"
--4. 获取性别为m的lamp81和lamp82的学生信息
mysql> select * from stu 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 字段名 [asc升（默认）|desc降]
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
|
|
QQ群⑧：
窥视卡
雷达卡
发表于 2018-10-11 07:53:26
提升卡
置顶卡
沉默卡
喧嚣卡
变色卡
千斤顶
显身卡