mysql 基本操作
1. 进入数据库: mysql -u 用户名 -p # 如:mysql -uroot -p # 注:-u 与用户名之间有无空格都可以
2. 退出: exit/quit
select now(); 查看当前时间
# 查看用户
desc mysql.user; # 查看用户表结构
select User from mysql.user; # 只查看mysql的用户名字段
# 库操作
1. show databases; 查看所有数据库 select database(); 查看正在使用的数据库
2. create database <db_name> [charset=utf8]; 创建数据库
3. drop database <db_name>; 删除数据库
# 表操作
1. show tables; 查看所有表
2. 创建表: create table <table_name> (id int[字段 字段类型]);
3. insert into <table_name>(字段1, 字段2...) values(值1, 值2...)[, (值...)];
insert into <table_name> values()[, (), ()...]; # 全字段插入
4. select * from <table_name> [where 条件]; # 查询
5. update <table_name> set <字段=new_value 如: sex='M'> where sex='F'; 把sex='F'的全部修改为sex='M'
6. 删除: delete from <table_name> [where 条件];
create database db_student charset=utf8; -- 创建数据库
use db_student; -- 使用数据库
select * from students; -- 查询student表的所有数据
insert into stu values()[,()[,()]]; -- 全字段插入
insert into stu(id, name) values()[, ()]; -- 指定字段插入
desc table; -- 查看表结构
update students set sex = 'M' where sex = 'F'; -- 把sec='F' 的数据改为 sex='M'
delete from students where id=3; # 删除id=3的数据
筛选条件
select * from students where name is not null; # name 不为空的
select * from students where age != 20;
select * from students where age >=17 and age <=22;
select * from students where age > 18 or age = 11;
order by -- 排序
select * from students order by id; # 正序, (默认的排序, 从小到大)
select * from students order by id desc; # 从大到小排序
limit -- 限制
-- select * from student limit 开始行数[, 结束行数];
select * from student limit 5;
select * from student limit 5, 9; -- 从第5行开始, 显示到第9行,共显示了5行
distinct -- 去重
select distinct * from students;
link '%' -- 模糊查询 %: 任意多个字符 _: 任意一个字符
select * from students where name like 'lon%';
select * from students where name like '_g%';
-- 范围查询
between min_num and max_num -- 连续范围
select * from stu where id between 2 and 6; -- id范围为2~6的数据
上述语句等价于: select * from stu where id >= 2 and id <= 6;
in -- 间隔范围, 列举的值
select * from students where age in(1, 16, 20);
聚合分组
聚合
count() 统计
select count(*) from stu;
select count(age) from stu;
max() 最大值
select max(age) from stu;
min() 最小值
select min(age) from stu;
avg() 平均值
select avg(age) from stu;
sum() 求和
select sum(age) from stu;
group_concat() -- 列出当字段的全部值
select group_concat(age) from stu; -- 列出age字段的全部值
-- 整合使用
select sum(age), max(age), count(*) from stu;
分组
group by 分组查询
select age from stu group by age; # 两个字段必须要一样
与聚合一起使用
select age, group_concat(name) from stu group by age;
聚合筛选 having
select age, avg(age) from stu group by age having age <= 9;
# 取别名
as
# 查出一个结果, 对这个结果再查, 要为那个结果取别名(注: 当这个结果是一张表时, 才需要取别名, 否则不需要取别名)
select * from (select * from stu order by age limit 5) as sel_stu;
select * from stu where age > (select avg(age) from stu)
select age as aaa_age from stu; -- 为age取了一个别名 aaa_age
连接查询
inner join 内连接 select * from 表1 join 表2; -- 无条件连接
select * from stu join stu2;
# 有条件内连接
select * from stu join stu2 on stu.name = 'long';
select * from stu join stu2 on stu.name = stu2.name;
select stu.id name, age, sex, class, phone from stu join stu2 on stu.id = stu2.id;
[left|right] join 左外连接 或 右外连接
select * from stu left join stu2;
select * from students left join detail on students.id=1;