MySQL的增删改查

目录

只有增删改查

DDL//数据定义语言

create

drop

alter

DML//数据操作语言

insert-增

mysql>insert into 表(表字段) values(“值”);

eg.在namelist表格中插入一个id为1,name为wang的数据:

mysql>Insert into namelist (id,name) values (1,”wang”)

update-改

mysql>update 表 set表字段where 某个表字段=值;

l更改时必须加where.

Eg:将namelist数据表中id为1的名字更新为zhao:

mysql>update namelist set name =”zhao” where id = 1;

将namelist数据表中id为1的id和名字都做更改:

mysql>update namelist set id=2,name=’h’ where id=1;

 

delete-删

l更改时必须加where.

Eg:删除表namelist中id为1的数据:

mysql>delete from namelist where id = 1;

mysql>delete from namelist where id in(1,3,5);

mysql>delete from namelist where id =1 or id=3 or id=5;

mysql>delete from namelist where id between 3 and 5;

mysql>delete from namelist where id>=3 and id<=5;

 

DQL//数据查询语言

select

查询表中特有字段:

eg.查看表中所有数据:

mysql>select * from t1;

eg.查看表中id,name数据:

mysql>select id,name from t1;

给字段起别名

name as n---省略as写成name n(中间空格不能省)

eg: mysql>select id,name n from t1;

取出唯一值

distinct

查看t1表中不重复的值: (去掉重复值)

eg: mysql>select distinct name from t1;

使用where进行查询

mysql>select * from t1 where id>=3 and id<=5;

查询空值null

mysql>select * from t1 where id isnull;

mysql>select * from t1 where id isnot null;

搜索like关键字

eg.搜索表里面含有4的数据:

mysql>select * from t1 where name like ‘%4%’;

mysql>select * from t1 where name regexp ‘.*4.*’;

eg.搜索表里面含有hg或者4的数据:

mysql>select * from t1 where name like‘%hg%’ or name like‘%4%’;

mysql>select * from t1 where name regexp ‘(.*4.*)| (.*5.*)’;

regexp:正则搜索

使用order by对查询结果进行排序

eg:搜索的结果以name排序:

mysql>select * from t1 order by id;(默认升序)

mysql>select * from t1 order by id asc;(升序排序)

mysql>select * from t1 order by id desc;(降序排序)

使用limit限定输出个数

mysql>select *from t1 order by id limit 1,2;(从1开始取2个值)

mysql>select *from t1 order by id limit 5;(前5个)

concat函数-字符串连接符

select concat (“a”,”-”,”b”);

mysql>select id,name ,concat(id,“-”,”name”) idnamefrom t1;

rand函数-随机排序

mysql>select * from t1 order by rand();

mysql>select * from t1 order by rand() limit 3;

count统计

mysql>select count(id) tot(别名) from t1;

mysql>select count(*) tot(别名) from t1;

mysql>select count(id) tot(别名) from t1 where name=”user4”;

sum求和

mysql>select sum(id) tot(别名) from t1;

mysql>select sum(id) tot(别名) from t1 where name=”user4”;

avg平均值

mysql>select avg(id) tot(别名) from t1;

mysql>select avg(id) tot(别名) from t1 where name=”user4”;

 

max最大值

mysql>select max(id) tot(别名) from t1;

mysql>select max(id) tot(别名) from t1 where name=”user4”;

min最小值

mysql>select min(id) tot(别名) from t1;

mysql>select min(id) tot(别名) from t1 where name=”user4”;

分组聚合

比如每个人今天发了多少帖子(聚合分组count group by)

eg:根据名字,查看其发了多少个帖子

mysql>select name,count(id) tot from t1 group by name;

mysql>select name,count(id) tot from t1 group by name order by tot desc;(分组优先于排序,即两者同时出现,group by写在order by之前)

mysql>select name,count(id) from t1 group by name having tot>=5;

(having在group by之上进行判断,不能用where)

多表查询

l普通查询(优先选择)

设计时各表之间有一定练习:

mysql>select t1.id,t2.name,t2.tele where t1.id=t2.uid group by post.uid;

mysql>select t1.id,count(t2.tele) where t1.id=t2.uid group by post.uid;

l嵌套查询

只能查到单表的信息,只不过是和另一个表有关系

mysql>select distinct t2.tele from t1, t2 on t1.id=t2.uid; (普通查询)

mysql>select tele from t2 where id in(select id from t1); (嵌套查询)

l左链接查询

mysql>select t1.id, t2.tele from t1 left join t2 on t1.id=t2.uid;

mysql>select t1.id, t2.tele from t1 left join t2 on t1.id=t2.uid group by t1.uid;

往title字段前面加入uid字段

mysql>alter table t1 add uid int after title;

DCL//数据控制语言

grant

commit

rollback