Mysql题目篇

1.增

1.1为表中指定字段添加数据

INSERT INTO 表名(字段名1,字段名2,…)VALUES(值1,值2,…)

INSERT INTO `user`(id,role) VALUES(3,"admin");

1.2为表中所有字段添加数据(可以不指名字段)

INSERT INTO 表名 VALUES(值11,值2,…);

INSERT INTO `user` VALUES(5,"lsy","lsy1234","admin");

1.3同时添加多条数据

INSERT INTO `user` VALUES(6,"lsy","lsy1234","admin"),
                         (7,"lsy7","lsy1234","admin"),
                         (8,"lsy8","lsy1234","admin");

2.删

2.1删除部分数据

DELETE from user where username="lsy"

2.2删除所有数据

DELETE from user 

3.改

3.1修改部分数据

UPDATE user set role="admin1" where id=9

3.2修改全部数据

UPDATE user set role="admin2" 

4.查

4.1简单查询

select id,role from user
select * from user
select id,role from user where id<10
select id,role from user where id in (9,10)
select id,role from user where id BETWEEN 2 and 10
select id,role from user where role is NULL
select id,role from user where role like "adm%"(%匹配任意长度的字符串,包括空字符串。)
select id,role from user where role like "adm_"(下划线通配符只匹配单个字符)
select id,role from user where role like "admi_" and username="shiro"(AND 的优先级高于 OR)
select id,role from user where role like "admi_" or username="shiro"

4.2高级查询

SELECT COUNT(*) FROM user
SELECT sum(id) FROM user
SELECT avg(id) FROM user
SELECT max(id) FROM user
SELECT min(id) FROM user
SELECT *from user order by id DESC
SELECT COUNT(*) ,gender FROM user GROUP BY gender;
SELECT sum(grade),gender FROM user GROUP BY gender HAVING SUM(grade) < 300;
SELECT * from user   LIMIT 2 

4.3子查询

SELECT * FROM user where id=(SELECT MAX(id) from user)
SELECT * FROM (SELECT MAX(id) from user) as user1

4.4表连接

SELECT * FROM user u INNER JOIN role r on u.id=r.id 
SELECT * FROM user u LEFT JOIN role r on u.id=r.id 
SELECT * FROM user u RIGHT JOIN role r on u.id=r.id 
SELECT * FROM user u LEFT JOIN role r on u.id=r.id UNION SELECT * FROM user u RIGHT JOIN role r on u.id=r.id
  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值