MySQL常见操作
一、创建数据表
create Table table_name(column_name、column_type)
create table user_li(id int AUTO_INCREMENT,name varchar(30), primary key(id));
二、插入数据
insert into user_li values("","lihua")
或者:insert into user_li(name) values("lihua")
三、查询数据
select * from user_li;
select name from user_li where id=1
select * from user_li limit 1,1
select * from user_li limit 1
select * from user_li limit 1 offset 2
1、limit(1,1)标识从一页有1行,从第一个记录之后开始返回(也就是返回第二个),一般用于数据量大,分批次返回
2、limit 1;从头开始,返回一行,一般和orderby结合使用,取前几名
3、offset :偏移量,标识从第几个之后开始返回,通常和limit联合使用,做分页。offset表示第几页,limit标识一页里面有多少行
where语句的字符串比较是不区分大小写的,可以用binary关键字区分大小写:
select * from user_li where name ="lihua"
select * from user_li where binary name="LIHUA"
四、更新数据
update user_li set name="llll" where id=2
五、删除数据
delete from user_li where name="llll"
注意:
1、delete 和 truncate 仅仅删除表数据,drop 连表数据和表结构一起删除
2、delete 是 DML 语句,操作完以后如果没有不想提交事务还可以回滚,truncate 和 drop 是 DDL 语句,操作完马上生效,不能回滚
3、执行的速度上,drop>truncate>delete
六、模糊查询
select * from user_li where name like "li%"
七、UNION
union不能用于列出重复的值
例子:SELECT country FROM Websites UNION SELECT country FROM apps ORDER BY country;
unionall 可以选取重复的值
SELECT country FROM Websites UNION ALL SELECT country FROM apps ORDER BY country;
带有 WHERE 的 SQL UNION ALL
SELECT country, name FROM Websites WHERE country='CN' UNION ALL SELECT country, app_name FROM apps WHERE country='CN' ORDER BY country;
八、orderBy排序
SELECT * from runoob_tbl ORDER BY submission_date ASC;
九、分组 GROUP BY
SELECT name, COUNT(*) FROM employee_tbl GROUP BY name;
with rollup 可以实现在分组统计数据基础上再进行相同的统计(SUM,AVG,COUNT)
SELECT name, SUM(signin) as signin_count FROM employee_tbl GROUP BY name WITH ROLLUP;
其中记录 NULL 表示所有人的登录次数。我们可以使用 coalesce 来设置一个可以取代 NUll 的名称。
SELECT coalesce(name, '总数'), SUM(signin) as signin_count FROM employee_tbl GROUP BY name WITH ROLLUP;