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 <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;