mysql 2007_07_mysql的基本操作

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 [charset=utf8]; 创建数据库

3. drop database ; 删除数据库

# 表操作

1. show tables; 查看所有表

2. 创建表： create table (id int[字段 字段类型]);

3. insert into (字段1, 字段2...) values(值1, 值2...)[, (值...)];

insert into values()[, (), ()...]; # 全字段插入

4. select * from [where 条件]; # 查询

5. update set where sex='F'; 把sex='F'的全部修改为sex='M'

6. 删除： delete from [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;