Mysql学习笔记<3>数据记录SQL

数据记录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