一、数据库表curd
==================================添加记录===============================
(1)*在制定表中添加记录
-语句:insert into 表名称 values(对照表的结构);
例如:插入person记录
insert into person values(001,'ayit','man','1996-6-22','work','500');
insert into person values(002,'ay','unknow','1999-9-9','door','500');
insert into person values(002,'ah','woman','2008-8-8','movie','900');
查看添加的记录
select * from person;
mysql> select * from person;
+----+------+-----+-----------+------+-------+
| id | name | ser | birthdaty | job | salay |
+----+------+-----+-----------+------+-------+
| 1 | ayit | man | 1996-6-22 | work | 500 |
+----+------+-----+-----------+------+-------+
(2)查看所有的编码:show variables like 'character%';
=================================修改表中记录=============================
(3)*修改表中的记录
-语句:update 表名 set 修改的字段名称=修改的值 where 条件
-练习:
--将所有员工薪水修改为5000元。
update employee set sal=5000;
--将姓名为’lucy’的员工薪水修改为3000元。
update employee set sal=3000 where name='lucy';
--将姓名为’lucy’的员工薪水修改为4000元,job改为ccc。
update employee set sal=4000,job='ccc' where name='lucy';
--将姓名为’lucy’的员工薪水在原有基础上增加1000元。
update employee set sal=sal+1000 where name='lucy';
=================================删除表中记录=============================
(4)*删除表中记录
-语句:delete from 表名称 where 条件
-练习:
-- 如果添加where,根据添加进行删除;如果没有添加where,把表里面的所有数据都删除
-- 删除表中名称为’lucy’的记录。
delete from employee where name='lucy';
-- 删除表中所有记录。
delete from employee;
=================================查询表中记录=============================
十分重要
*** 查询数据库中的记录
(1)*sql分类:DQL (数据查询语言)
-语句 select 要查询的字段(写*表示所有的字段) from 要查询的表名称 where ..........
--关键字 DISTINCT:表示去除表中重复的记录
* 创建一个学生表
create table student (
id int,
sname varchar(40),
chinese int,
english int,
math int
)
*+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| sname | varchar(40) | YES | | NULL | |
| chinese | int(11) | YES | | NULL | |
| english | int(11) | YES | | NULL | |
| math | int(11) | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
insert into student values(004,'ayit',100,150,105);
insert into student values(003,'jie',110,100,105);
insert into student values(002,'yu',120,110,130);
insert into student values(001,'xia',100,100,100);
* 练习:
-查询表中所有学生的信息。
select * from stu;
-查询表中所有学生的姓名和对应的英语成绩。
select sname,english from stu;
-查询表中sname=xia学生对应的英语数学语文成绩。
select chinese,english,math from stu where sname='xia';
-去除重复的记录
select distinct * from stu;
(2)*别名
-语句 select english as eng,math as ma from student;
+------+------+
| eng | ma |
+------+------+
| 100 | 100 |
| 110 | 130 |
| 100 | 105 |
| 150 | 105 |
| 150 | 105 |
| 100 | 105 |
| 110 | 130 |
| 100 | 100 |
+------+------+
-练习
--在所有学生语文英语分数上加10分特长分。
mysql> select Chinese+10,English+10,Math from student;
+------------+------------+------+
| Chinese+10 | English+10 | Math |
+------------+------------+------+
| 110 | 110 | 100 |
| 130 | 120 | 130 |
| 120 | 110 | 105 |
| 120 | 110 | 105 |
| 130 | 120 | 130 |
| 110 | 110 | 100 |
+------------+------------+------+
--统计每个学生的总分。
mysql> select Chinese+English+Math as Sum,sname from student;
+------+-------+
| Sum | sname |
+------+-------+
| 300 | xia |
| 360 | yu |
| 315 | jie |
| 315 | jie |
| 360 | yu |
| 300 | xia |
| 300 | xia |
+------+-------+
7 rows in set (0.00 sec)
--查询姓名为xia的学生成绩
mysql> select * from student where sname='xia';
+------+-------+---------+---------+------+
| id | sname | Chinese | English | Math |
+------+-------+---------+---------+------+
| 1 | xia | 100 | 100 | 100 |
| 1 | xia | 100 | 100 | 100 |
| 1 | xia | 100 | 100 | 100 |
+------+-------+---------+---------+------+
3 rows in set (0.00 sec)
--查询英语成绩大于90分的同学
mysql> select sname,English from student where English>105;
+-------+---------+
| sname | English |
+-------+---------+
| yu | 110 |
| yu | 110 |
+-------+---------+
2 rows in set (0.00 sec)
(3)*显示当前的数据库 : select database();
(4)*select语句里面where条件部分有关键字
==========in关键字===========
-in:记录在范围里面值
-练习:查询语文成绩在 110,150 之间的学生信息
mysql> select * from student where Chinese in(110,150);
+------+-------+---------+---------+------+
| id | sname | Chinese | English | Math |
+------+-------+---------+---------+------+
| 3 | jie | 110 | 100 | 105 |
| 3 | jie | 110 | 100 | 105 |
+------+-------+---------+---------+------+
==========like关键字==========
-like:用在模糊查询
-练习:查询表中 sname 姓名中存在 x 的学生
mysql> select * from student where sname like '%x%';
+------+-------+---------+---------+------+
| id | sname | Chinese | English | Math |
+------+-------+---------+---------+------+
| 1 | xia | 100 | 100 | 100 |
| 1 | xia | 100 | 100 | 100 |
| 1 | xia | 100 | 100 | 100 |
+------+-------+---------+---------+------+
3 rows in set (0.00 sec)
==========and关键字===========
· -and:表示条件同时满足
-练习:查询表中 语文,英语成绩>100 学生
mysql> select * from student where Chinese>100 and English>100;
+------+-------+---------+---------+------+
| id | sname | Chinese | English | Math |
+------+-------+---------+---------+------+
| 2 | yu | 120 | 110 | 130 |
| 2 | yu | 120 | 110 | 130 |
+------+-------+---------+---------+------+
2 rows in set (0.00 sec)
==========or关键字============
-or:表示或者关系,两者满足其一就可
-练习:查询表中 语文成绩>110 或者 数学成绩>105 的学生
mysql> select * from student where Chinese>110 or Math>105;
+------+-------+---------+---------+------+
| id | sname | Chinese | English | Math |
+------+-------+---------+---------+------+
| 2 | yu | 120 | 110 | 130 |
| 2 | yu | 120 | 110 | 130 |
| 5 | ayit | 105 | 123 | 129 |
| 8 | ah | 115 | 121 | 109 |
+------+-------+---------+---------+------+
4 rows in set (0.00 sec)
(5)*查询操作排序
-语句:order by 要排序字段
mysql> select * from student order by id;
+------+-------+---------+---------+------+
| id | sname | Chinese | English | Math |
+------+-------+---------+---------+------+
| 1 | xia | 100 | 100 | 100 |
| 1 | xia | 100 | 100 | 100 |
| 1 | xia | 100 | 100 | 100 |
| 2 | yu | 120 | 110 | 130 |
| 2 | yu | 120 | 110 | 130 |
| 3 | jie | 110 | 100 | 105 |
| 3 | jie | 110 | 100 | 105 |
| 5 | ayit | 105 | 123 | 129 |
| 8 | ah | 115 | 121 | 109 |
+------+-------+---------+---------+------+
9 rows in set (0.00 sec)
-默认排序是正序 asc,若要降序 需要在 字段后加 desc
mysql> select * from student order by id desc;
+------+-------+---------+---------+------+
| id | sname | Chinese | English | Math |
+------+-------+---------+---------+------+
| 8 | ah | 115 | 121 | 109 |
| 5 | ayit | 105 | 123 | 129 |
| 3 | jie | 110 | 100 | 105 |
| 3 | jie | 110 | 100 | 105 |
| 2 | yu | 120 | 110 | 130 |
| 2 | yu | 120 | 110 | 130 |
| 1 | xia | 100 | 100 | 100 |
| 1 | xia | 100 | 100 | 100 |
| 1 | xia | 100 | 100 | 100 |
+------+-------+---------+---------+------+
9 rows in set (0.00 sec)
mysql> select * from student order by id asc;
+------+-------+---------+---------+------+
| id | sname | Chinese | English | Math |
+------+-------+---------+---------+------+
| 1 | xia | 100 | 100 | 100 |
| 1 | xia | 100 | 100 | 100 |
| 1 | xia | 100 | 100 | 100 |
| 2 | yu | 120 | 110 | 130 |
| 2 | yu | 120 | 110 | 130 |
| 3 | jie | 110 | 100 | 105 |
| 3 | jie | 110 | 100 | 105 |
| 5 | ayit | 105 | 123 | 129 |
| 8 | ah | 115 | 121 | 109 |
+------+-------+---------+---------+------+
9 rows in set (0.00 sec)