数据记录SQL
数据表记录的插入
语法:
insert into 表名(列名,列名, .... ) values(值, 值, ....)
注:
值个数应该列个数相同,值顺序和列顺序相同,值类型和列字段类型匹配
Windows下具体操作过程:
1) 启动cmd窗口
2) mysql -u root -p 回车 输入密码
3) 创建数据库 day11
create database day11 ;
* show databases ; 查看当前所有数据库
* show create database day11 ;
4) 切换数据库 use day11;
* select database();
5) 创建数据表
create table employee (
id int primary key not null auto_increment ,
name varchar(40) unique not null,
gender varchar(10) not null,
birthday date not null,
entry_date date not null,
job varchar(20) not null,
salary double not null,
resume longtext not null
);
* desc employee; 查看表结构
6) 插入数据
insert into employee(id,name,gender,birthday, entry_date,job,salary,resume) values(null,'zs','male','1990-
01-10','2012-10-10','hr',3000,'He is a good man!');
* 插入数据时,字符串添加 单引号 '' ---- 非数字的字符和日期型数据应包含在单引号中
* 在插入数据时,如果有些列存在默认值或者可以为null ,插入省略部分列 。
在可以缺省的情况下的插入
举例:
create table person(
id int primary key not null auto_increment,
name varchar(40) not null,
introduce varchar(255)
);
insert into person(name) values('zs'); --- 这里只要写不能为空列 就可以了
insert into person values(null,'lisi',null); ---- 省略所有列名,必须为所有列提供value值,按照数据表中列顺序
* 插入数据后,通过select * from 表名; ------- 查询插入的数据
数据表记录的修改update 语句
语法:
update 表名 set 列名=值,列名=值 where条件语句
注: 没有where语句,对所有数据行进行更新
举例:
update employee set salary = 5000 ; ---- 修改所有员工工资5000
练习:
<1>将所有员工薪水修改为5000元。 ------ update employee set salary = 5000 ;
<2>将姓名为’zs’的员工薪水修改为3000元。update employee set salary = 3000 where name = 'zs';
* update employee set salary = 3000 where binary name = 'ZS'; --- 条件前添加 binary 使比较更加精确严格
<3>将姓名为’lisi’的员工薪水修改为4000元,job改为ccc。 --------- update employee set salary = 4000, job='ccc' where binary name = 'lisi';
<4>将wangwu的薪水在原有基础上增加1000元。------- update employee set salary = salary+1000 where name = 'wangwu';
数据表记录的删除delete语句
语法:
delete from 表名 where 条件语句;
删除一个表所有记录 truncate 表名; 效果与 delete from 表名; 相同
truncate与delete 使用上区别 ?
truncate 删除记录后不可恢复的,不受事务管理,原理:先删除整个表,重新创建
delete 可以被事务管理 ,在事务中删除数据可以回滚恢复,原理: 一行一行删除数据记录
truncate 删除所有记录性能上 好于 delete
练习:
<1>删除表中名称为’zs’的记录 。 ---- delete from employee where name='zs';
<2>删除表中所有记录。 ---- delete from employee;
事务的回滚 ,通过delete在事务中删除可以恢复的
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> delete from employee;
Query OK, 3 rows affected (0.00 sec)
mysql> select * from employee;
Empty set (0.00 sec)
mysql> rollback;
使用truncate删除表中记录。-----truncate employee; 数据永远删除,不会恢复
数据记录的查询
语法:select * | 指定列名,列名 from 表名;
1) 基本查询
select * from employee; ---- 查看当前数据表所有记录
select gender from employee; ----- 查看employee的 gender列数据
select distinct gender from employee; ---- 查看employee表所有性别 ,排重
举例:
创建scores
create table scores(
id int primary key not null auto_increment,
name varchar(40),
math double,
chinese double,
english double
);
insert into scores values(null,'老黎',85,90,76);
insert into scores values(null,'老刘',70,65,80);
insert into scores values(null,'老冯',60,70,65);
练习:
<1>查询表中所有学生的信息。------ select * from scores;
<2>查询表中所有学生的姓名和对应的英语成绩。------- select name,english from scores;
<3>过滤表中重复数据。----- select distinct * from scores;
2) 查询时进行运算
语法:
select 列名运算表达式 from 表名;
select 列名 as 列别名 from 表名;
练习:
<1>在所有学生英语分数上加10分特长分。select name,english+10 from scores;
<2>统计每个学生的总分。 select name,math+chinese+english from scores;
<3>使用别名表示学生分数。select name,math+chinese+english as 总分 from scores;
注:
* 起别名时 as 可以省略 select name 姓名 from scores;
* select name math from scores; select name,math from scores;
查询数据添加where条件
使用where子句,进行过滤查询。
练习:
<1>查询姓名为老黎的学生总成绩 ---- select math+chinese+english from scores where name='老黎';
<2>查询英语成绩大于80分的同学 ---- select * from scores where english > 80;
<3>查询总分大于200分的所有同学 ---- select * from scores where math+chinese+english > 200;
select 其它比较运算
查询语文成绩不为90 分学生 select * from scores where chinese <> 90;
between ...and... 查询语文在70-80之间同学
select * from scores where chinese<=80 and chinese>=70;
select * from scores where chinese between 70 and 80 ; ---- 先写小的后写大的
in(set) 在几个固定值中取值 select * from scores where chinese in (70,80,90); ---- 查询语文成绩为70分或者80分或者90分同学
like 模糊查询 select * from scores where name like '老%';
查询学生中姓 老所有人 ----- % 代表任意字符串
select * from scores where name like '老_';
查询学生中 姓 老 名字为两个字 学生 ------ _ 代表任意单个字符
is null 判断一列是否为空
insert into scores values(null,'小丽',null,80,90);
查询无数学成绩所有人 select * from scores where math is null;
查询有数学成绩人 select * from scores where math is not null;
* select * from scores where 1; --- 在编程语言中 1 ---- true
select * from scores where 0 ; ---- 0 --- false
* null 代表1/2 ---- 任何表达式和null 进行逻辑运算 结果都是false
逻辑运算中 and 、or ---- and 和 or谁优先级高 ?
select * from scores where 2>1 or 2>3 and 3>4 ; ---- and先执行 SQL攻击
练习:
<1>查询英语分数在 80-90之间的同学。 select * from scores where english between 80 and 90;
<2>查询数学分数为89,90,91的同学。 select * from scores where math in (89,90,91);
<3>查询所有姓李的学生成绩。 select * from scores where name like '李%';
<4>查询数学分>80,语文分>80的同学。 select * from scores where math>80 and chinese >80;
select查询通过 order by 排序
语法 :
select * from 表名 where条件语句 order by 列名 asc|desc , 列名 asc|desc ... ;
练习:
<1>对数学成绩排序后输出。select * from scores order by math; 默认是升序
<2>对总分排序按从高到低的顺序输出 select name,chinese+math+english from scores order by chinese+math+english desc;
<3>对姓老的学生数学成绩排序输出 select * from scores where name like '老%' order by math desc;
聚集函数
count返回查询结果记录条数
练习:
<1>统计一个班级共有多少学生? select count(*) from scores;
<2>统计数学成绩小于80的学生有多少个? select count(*) from scores where math<80;
<3>统计总分大于250的人数有多少? select count(*) from scores where math+chinese+english>250;
sum 对一列的数据求和
举例:
<1>统计一个班级数学总成绩? select sum(math) from scores;
<2>统计一个班级语文、英语、数学各科的总成绩 select sum(math),sum(chinese),sum(english) from scores;
<3>统计一个班级语文、英语、数学的成绩总和 select sum(math+ chinese + english) from scores;
<4>统计一个班级语文成绩平均分 select sum(chinese)/count(chinese) from scores;
* sum求和时 null不运算
avg对一列数据求平均值
举例:
<1>求一个班级数学平均分? select avg(math) from scores; * null数据不参与运算
<2>求一个班级总分平均分 select avg(math+chinese+english) from scores;
max和min 对一列数据 计算最大值和最小值
举例:
<1>求班级总分最高分和总分最低分(数值范围在统计中特别有用) select max(math+chinese+english),min(math+chinese+english) from scores;
<2>思考题:语文最高分是谁? select max(chinese) from scores;
select name from scores where chinese = (select max(chinese) from scores);
分组目的:统计 分组前提:重复数据
举例:
create table orders(
id int,
product varchar(20),
price float
);
insert into orders(id,product,price) values(1,'电视',900);
insert into orders(id,product,price) values(2,'洗衣机',100);
insert into orders(id,product,price) values(3,'洗衣粉',90);
insert into orders(id,product,price) values(4,'桔子',9);
insert into orders(id,product,price) values(5,'洗衣粉',90);
练习:
<1>对订单表中商品归类后,显示每一类商品的总价 select product,sum(price) from orders group by product;
在分组查询中通过 having关键字,添加分组查询条件
练习:
<1>查询购买了几类商品,并且每类总价大于100的商品 select product,sum(price) from orders where sum(price)>100 group by product; ** where中不能使用分组函数
select product,sum(price) from orders group by product having sum(price)> 100;
where 和 having 添加条件使用上区别 ?
where 是在分组之前执行,having是在分组之后执行,where 不能使用分组函数,having使用分组函数
* having可以替换where