诛仙mysql_Mysql与sql语句

三、表记录

//1.增加一条记录insert

insert into tab_name (field1,filed2,.......) values (value1,value2,.......);

//插入的数据应与字段的数据类型相同。

//数据的大小应在列的规定范围内，例如：不能将一个长度为80的字符串加入到长度为40的列中。

//在values中列出的数据位置必须与被加入的列的排列位置相对应。

//字符和日期型数据应包含在单引号中'zhang' '2013-04-20'

//插入空值：不指定某列的值或insert into table value(null)，则该列取空值。

//如果要插入所有字段可以省写列列表，直接按表中字段顺序写值列表insert into tab_name values(//value1,value2,......);

//练习：使用insert语句向表中插入三个员工的信息。

insert into employee (name,birthday,entry_date,job,salary) values

('alex','1990-09-09','2000-01-01','CTO',9999);

insert into employee (name,birthday,entry_date,job,salary) values

('alvin','1989-08-08','2000-01-01','teacher',9999);

insert into employee (name,birthday,entry_date,job,salary) values

('xialv','1991-07-07','2000-01-02','保安',888);

insert into employee values (7,'wusir',1,'1970-05-05','2001-01-01','保安',1000,null);

//2.修改表记录

update tab_name set field1=value1,field2=value2,......[where 语句]

UPDATE语法可以用新值更新原有表行中的各列。

SET子句指示要修改哪些列和要给予哪些值。

WHERE子句指定应更新哪些行。如没有WHERE子句，则更新所有的行。

//实验：

//将所有员工薪水修改为5000元。

update emp set salary=5000;

//将姓名为’zs’的员工薪水修改为3000元。

update emp set salary=3000 where name='zs';

//将姓名为’ls’的员工薪水修改为4000元,job改为ccc。

update emp set salary=4000,job='ccc' where name='zs';

//将wu的薪水在原有基础上增加1000元。

update emp set salar=salary+4000 where name='wu';

//3.删除表操作

delete from tab_name [where ....]

如果不跟where语句则删除整张表中的数据

delete只能用来删除一行记录，不能只删除一行记录中的某一列值(这是update操作)。

delete语句只能删除表中的内容，不能删除表本身，想要删除表，用drop

同insert和update一样，从一个表中删除记录将引起其它表的参照完整性问题，在修改数据库数据时，头脑中

应该始终不要忘记这个潜在的问题。

TRUNCATE TABLE也可以删除表中的所有数据，词语句首先摧毁表，再新建表。此种方式删除的数据不能在

事务中恢复。

//实验：

//删除表中名称为’zs’的记录。

delete from emp where name='黄忠';

//删除表中所有记录。

delete from emp;

//使用truncate删除表中记录。

truncate table emp;

//4.select操作

(1)select [distinct] *|field1，field2，...... from tab_name

其中from指定从哪张表筛选，*表示查找所有列，也可以指定一个列列表明确指定要查找的列，distinct用来剔除重复行。

//练习：

//查询表中所有学生的信息。

select * from exam;

//查询表中所有学生的姓名和对应的英语成绩。

select name,english from exam;

//过滤表中重复数据。

select distinct english from exam;

(2)select 也可以使用表达式，并且可以使用 as 别名

//练习：

//在所有学生分数上加10分特长分显示。

select name,english+10,chinese+10,math+10 from exam;

//统计每个学生的总分。

select name,english+chinese+math from exam;

//使用别名表示学生总分。

select name,english+chinese+math as 总成绩 from exam;

select name,english+chinese+math 总成绩 from exam;

select name english from exam; //what will happen?---->记得加逗号

(3)使用where子句，进行过滤查询。

//练习：

//查询姓名为XXX的学生成绩

select * from exam where name='张飞';

//查询英语成绩大于90分的同学

select name,english from exam where english>90;

//查询总分大于200分的所有同学

select name,math+english+chinese as 总成绩 from exam where math+english+chinese>200 ;

//where字句中可以使用：

//比较运算符：

> < >= <= <>

between 10 and 20 值在10到20之间

in(10,20,3)值是10或20或30

like '张pattern'

pattern可以是%或者_，

如果是%则表示任意多字符，此例中张三丰 张飞 张abcd,如果是_则表示一个字符张_，张飞符合。张Is null

//逻辑运算符

在多个条件直接可以使用逻辑运算符 and or not

//练习

//查询英语分数在 80－100之间的同学。

select name ,english from exam where english between 80 and 100;

//查询数学分数为75,76,77的同学。

select name ,math from exam where math in (75,76,77);

//查询所有姓张的学生成绩。

select * from exam where name like '张%';

//查询数学分>70，语文分>80的同学。

select name from exam where math>70 and chinese >80;

//查找缺考数学的学生的姓名

select name from exam where math is null;

(4)Order by 指定排序的列，排序的列即可是表中的列名，也可以是select 语句后指定的别名。

//select *|field1,field2... from tab_name order by field [Asc|Desc]

// Asc 升序、Desc 降序，其中asc为默认值 ORDER BY 子句应位于SELECT语句的结尾。

//练习：

//对数学成绩排序后输出。

select * from exam order by math;

//对总分排序按从高到低的顺序输出

select name ,(ifnull(math,0)+ifnull(chinese,0)+ifnull(english,0))

总成绩 from exam order by 总成绩 desc;

//对姓张的学生成绩排序输出

select name ,(ifnull(math,0)+ifnull(chinese,0)+ifnull(english,0))

总成绩 from exam where name like '张%'

order by 总成绩 desc;

(5)聚合函数：技巧,先不要管聚合函数要干嘛，先把要求的内容查出来再包上聚合函数即可。

count(列名)：统计行的个数

//统计一个班级共有多少学生？先查出所有的学生，再用count包上

select count(*) from exam;

//统计数学成绩大于70的学生有多少个？

select count(math) from exam where math>70;

//统计总分大于250的人数有多少？

select count(name) from exam where (ifnull(math,0)+ifnull(chinese,0)+ifnull(english,0))>250;

sum(列名)：统计满足条件的行的内容和

//统计一个班级数学总成绩？先查出所有的数学成绩，再用sum包上

select sum(math) from exam;

//统计一个班级语文、英语、数学各科的总成绩

select sum(math),sum(english),sum(chinese) from exam;

//统计一个班级语文、英语、数学的成绩总和

select sum(ifnull(math,0)+ifnull(chinese,0)+ifnull(english,0)) as 总成绩 from exam;

//统计一个班级语文成绩平均分

select sum(chinese)/count(*) from exam ;

//注意：sum仅对数值起作用，否则会报错。

AVG(列名)：

//求一个班级数学平均分？先查出所有的数学分，然后用avg包上。

select avg(ifnull(math,0)) from exam;

//求一个班级总分平均分

select avg((ifnull(math,0)+ifnull(chinese,0)+ifnull(english,0))) from exam ;

Max、Min

//求班级最高分和最低分(数值范围在统计中特别有用)

select Max((ifnull(math,0)+ifnull(chinese,0)+ifnull(english,0))) from exam;

select Min((ifnull(math,0)+ifnull(chinese,0)+ifnull(english,0))) from exam;

注意：null 和所有的数计算都是null，所以需要用ifnull将null转换为0！

(6)group by 分组查询：

group by字句，其后可以接多个列名，也可以跟having子句对group by 的结果进行筛选。

//练习：对订单表中商品归类后，显示每一类商品的总价

select product，sum(price) from orders group by product;

//练习：查询购买了几类商品，并且每类总价大于100的商品

select product，sum(price) from orders group by product

having sum(price)>100;

//!!! having 和 where两者都可以对查询结果进行进一步的过滤，差别有：

<1>where语句只能用在分组之前的筛选，having可以用在分组之后的筛选；

<2>使用where语句的地方都可以用having进行替换

<3>having中可以用聚合函数，where中就不行。

//练习:查询商品列表中除了橘子以外的商品，每一类商品的总价格大于500元的商品的名字

select product,sum(price) from orders where product<>'桔子' group by

product having sum(price)>500;

(7) //重点：Select from where group by having order by

Mysql在执行sql语句时的执行顺序：from where select group by having order by

//分析:

select math+english+chinese as 总成绩 from exam where 总成绩 >250; ---- 不成功

select math+english+chinese as 总成绩 from exam having 总成绩 >250; --- 成功

select math+english+chinese as 总成绩 from exam group by 总成绩 having 总成绩 >250; ----成功

select math+english+chinese as 总成绩 from exam order by 总成绩;----成功

select * from exam as 成绩 where 成绩.math>85; ---- 成功