分类：mysql

-- 注释

-- 创建数据库
create database python_test_1 charset=utf8;

-- 查看数据库
show databases;

-- 使用数据库
use python_test_1;

-- 显示使用的数据库是哪个
select database();

-- student表
create table students(
  id int unsigned primary key auto_increment not null,
  name varchar(20) default '',
  age tinyint unsigned default 0,
  height decimal(5, 2),
  gender enum('男', '女', '中性', '保密') default '保密',
  cls_id int unsigned default 0,
  is_delete bit default 0
);
-- bit 相当于 bit(1), 还可以bit(2)

-- 查看数据库创建语句
show create table students;

-- classes表
create table classes(
  id int unsigned auto_increment primary key not null,
  name varchar(30) not null
);

-- 查看数据表
show tables;

-- 向students表中插入数据
insert into students values
(0, '小明', 18, 180.00, 2, 1, 0),
(0, '小月月', 18, 180.00, 2, 2, 1),
(0, '彭于晏', 29, 185.00, 1, 1, 0),
(0, '刘德华', 59, 175.00, 1, 2, 1),
(0, '黄蓉', 38, 160.00, 2, 1, 0),
(0, '凤姐', 28, 150.00, 4, 2, 1),
(0, '王祖贤', 18, 172.00, 2, 1, 1),
(0, '周杰伦', 36, null, 1, 1, 0),
(0, '程坤', 27, 181.00, 1, 2, 0),
(0, '刘亦菲', 25, 166.00, 2, 2, 0),
(0, '金星', 33, 162.00, 3, 3, 1),
(0, '静香', 12, 180.00, 2, 4, 0),
(0, '郭靖', 12, 170.00, 1, 4, 0),
(0, '周杰', 34, 176.00, 2, 5, 0);

-- 向classes表中插入数据
insert into classes values(0, 'python_01期'), (0, 'python_02期');


-- 查询
  -- 查询所有字段
  -- select * from 表名;
  select * from students;
  select * from classes;

  -- 查询指定字段
  -- select 列, 列 ... from 表名;
  select age, name from students;

  -- 使用 as 给字段起别名
  select name as 姓名, age as 年龄 from students;

  -- select 表名.字段 ... from 表名;
  select students.name, students.age from students;

  -- 可以通过 as 给表起别名
  select s.name, s.age from students as s;
  -- 不可以 select students.name, s.age from students as s;

  -- 消除重复行
  -- distinct 字段
  select distinct gender from students;


-- 条件查询
  -- 比较运算符
    -- select ... from 表名 where ...
    -- > 查询大于18岁的信息
    select * from students where age > 18;

    -- < 查询小于18岁的信息
    select * from students where age < 18;

    -- >= <=
    select * from students where age >= 18;

    -- =
    select * from students where age = 18;

    -- != 或者 <>
    select * from students where age != 18;

  -- 逻辑运算符
    -- and
    select * from students where age > 18 and age < 28;
    select * from students where age > 18 and gender='女';
    select * from students where age > 18 and gender=2;

    -- or
    select * from students where age > 18 or height >= 180;

    -- not
    -- 不在 18岁以上的女性
    -- 错误 select * from students where not age > 18 and gender=2;
    select * from students where not (age > 18 and gender=2);

    -- 年龄不是小于等18 并且是女性
    select * from students where (not age <= 18) and gender=2;

-- 模糊查询
  -- like 性能低
  -- % 替换零个，一个或多个
  -- _ 替换一个
  -- 查询名字中 以"小"开始的名字
  select name from students where name like "小%";

  -- 查询姓名中有 "小"的名字
  select name from students where name like "%小%";

  -- 查询有2个字的名字
  select name from students where name like "__";

  -- 查询至少2个字的名字
  select name from students where name like "__%";

  -- rlike 正则
  -- 查询 以"周"开始的姓名
  select name from students where name rlike "^周.*";

  -- 查询 以"周"开始，"伦"结尾的姓名
  select name from students where name rlike "^周.*伦$";

-- 范围查询

  -- in (1, 3, 8) 表示在一个非连续的范围内
  select name, age from students where age = 18 or age = 34 or age = 12;
  select name, age from students where age in(18, 34, 12);

  -- not in ()
  select name, age from students where age not in(18, 34, 12);

  -- between .. and ..
  -- [18, 34]
  select name, age from students where age between 18 and 34;

  -- not between .. and ..
  select name, age from students where age not between 18 and 34;
  select name, age from students where not age between 18 and 34;
  -- 语法错误 select name, age from students where age not (begin 18 and 34);

-- 判断空
  -- is null
  select * from students where height is null;

  -- is not null
  select * from students where height is not null;


-- 排序
  -- order by 字段
  -- asc 从小到大 即升序
  -- desc 从大到小 即降序
  select * from students where age between 18 and 34 and gender=1 order by age;
  select * from students where (age between 18 and 34) and gender=1 order by age; -- 默认asc
  select * from students where (age between 18 and 34) and gender=1 order by age asc;

  -- 如果有相同的情况下，默认按主键排，从小到大排
  select * from students where (age between 18 and 34) and gender=2 order by height desc, id desc;
  select * from students where (age between 18 and 34) and gender=2 order by height desc, age asc, id desc;
  select * from students order by age asc, height desc;

-- 聚合函数
  -- count 总数
  select * from students where gender=1;
  select count(*) from students where gender=1;
  select count(*) as 男性人数 from students where gender=1;

  -- max 最大值
  select age from students;
  select max(age) from students;
  select max(height) from students where gender=2;

  -- min 最小值

  -- sum 求和
  select sum(age) from students;

  -- avg 平均值
  select avg(age) from students;

  -- 平均年龄
  select sum(age)/count(*) from students;

  -- 四舍五入 round(123.00, 1) 保留1位小数
  select round(sum(age)/count(*), 2) from students;


-- 分组
  -- group by
  -- 按照性别分组，查询所有性别
  -- 先分组，再从组里取数据
  select gender from students group by gender;
  -- 错误 select name from students group by gender;
  -- 错误 select * from students group by gender;

  select gender, count(*) from students group by gender;
  select gender, max(age) from students group by gender;
  select gender, group_concat(name) from students group by gender; -- 各性别，都有谁
  select gender, group_concat(name, age) from students group by gender; -- 各性别，都有谁
  select gender, group_concat(name, '_', age) from students group by gender; -- 各性别，都有谁

  -- 计算男性的人数
  select gender, count(*) from students where gender=1 group by gender;
  select gender, group_concat(name, '_', age) from students where gender=1 group by gender; -- 各性别，都有谁

  -- having
  -- 查询平均年龄超过30的性别，以及姓名 having avg(age) > 30
  select gender, group_concat(name), avg(age) from students group by gender having avg(age) > 30;

  -- where 对源表过滤， having 对组过滤

-- 分页

  -- limit start, count
  -- 限制查询出来的个数
  select * from students where gender=1 limit 2;
  select * from students where gender=1 limit 0, 2; -- 0=0*2=(1-1)*2
  select * from students where gender=1 limit 2, 2; -- 2=1*2=(2-1)*2 => (n-1)*count

  -- 不能用表达式，需要计算结果出来 select * from students limit 2*(6-1), 2;
  -- limit 放在最后
  select * from students order by gender limit 10, 2;
  -- 错误 select * from students limit 10, 2 order by gender
  select * from students where gender=2 order by height desc limit 0,2;

-- 连接查询
  -- 内连接，取交集
  -- inner join .. on

  -- select .. from 表A inner join 表B
  select * from students inner join classes;
  -- 新表数据总数: m * n

  -- 查询 有能过对应班级的学生以及班级信息
  select * from students inner join classes on students.cls_id = classes.id;
  -- 新表数据总数 <= m * n

  -- 按照要求显示姓名、班级
  select students.*, classes.name from students inner join classes on students.cls_id=classes.id;

  select students.name, classes.name from students inner join classes on students.cls_id=classes.id;
  select s.name, c.name from students as s inner join classes as c on s.cls_id=c.id;

  --
  select c.name, s.* from students as s inner join classes as c on s.cls_id=c.id order by c.name;
  select c.name, s.* from students as s inner join classes as c on s.cls_id=c.id order by c.name, s.id;

  -- 左连接
  -- left join
  select s.*, c.name from students as s left join classes as c on s.cls_id=c.id;

  select s.*, c.name from students as s left join classes as c on s.cls_id=c.id having c.id is null;
  select s.*, c.name from students as s left join classes as c on s.cls_id=c.id where c.id is null;

  -- 右连接
  -- right join可以用left join实现

-- 自关联
  -- id name    id name c_id
  -- 1 北京市    1 朝阳区 1
  -- 2 上海      2 浦东   2

  -- |
  -- v

  -- id name p_id
  -- 1  北京市  null
  -- 2  上海    null
  -- 3  朝阳区  1
  -- 4  浦东    2
  -- 5  三林镇  4

  create table areas(
    aid int primary key,
    atitle varchar(20),
    pid int
  );

  -- mysql -uroot -p
  show databases;
  use python_test_1;
  -- source areas.sql;

  -- 省的地级市
  select * from areas as province inner join areas as city on city.pid=province.aid having province.atitle="上海";
  select province.atitle, city.atitle from areas as province inner join areas as city on city.pid=province.aid having province.atitle="上海";

-- 子查询 (慢点)
  -- 标量子查询

  -- 查询最高的男生信息
  select * from students where height = (select max(height) from students);

  select * from areas where pid = (select aid from areas where atitle="上海");

-- 链接数据库
mysql -uroot -p
mysql -uroot -pxxx

-- 退出数据库
exit、quit、ctrl+d

-- 查看所有数据库
show databases;
大小写不分

-- 显示时间
select now();

-- 显示数据库版本
select version();

-- 创建数据库
-- create database 数据库名 charset=utf8;
create database python4;
create database python4new charset=utf8;

-- 查看创建数据库的语句
-- show create database 数据库名
show create database python4;
show create database python4new;

-- 删除数据库
-- drop database 数据库名
drop database `python-04`;

-- 使用数据库
use 数据库名

-- 查看当前数据库
select database();

-- 查看当前数据库中所有表
show tables;

-- 创建表
-- create table 数据表名(字段 类型 约束[, 字段 类型 约束]);
-- auto_increment
-- not null
-- primary key
-- default
create table xxx(id int, name varchar(30));
create table yyy(
    id int primary key not null auto_increment,
     name varchar(30)
);

create table student(
    id int unsigned not null auto_increment primary key,
    name varchar(30),
    age tinyint unsigned default 0,
    high decimal(5, 2),
    gender enum("男", "女", "中性", "保密") default "保密",
    cls_id int unsigned
);

inset into student values(0, "laowang", 18, 190, "男", 0);
select * from student;

create table classes(
    id int unsigned not null auto_increment primary key,
    name varchar(30)
);

-- 查看表结构
-- desc 表名
desc xxx;

-- 修改表结构 添加字段
-- alter table 表名 add 列名 类型;
alter table students add birthday datetime;

-- 修改表-修改字段，不重命名
-- alter table 表名 modify 列名 类型及约束
alter table students modify birthday date;

-- 修改表-修改字段：重命名
-- alter table 表名 change 原名 新名 类型及约束
alter table students change birthday birth date default "1990-01-01";


-- 修改表-删除字段
-- alter table 表名 drop 列名
alter table students drop high;


-- 删除表
-- drop table 表名;
drop database 数据库;
drop table xxxx;

-- 查看创建表的语句
show create table students;


----------- 存储引擎 ----------
MyISAM
Innodb


---------------CURD---------------------
create update retrieve delete
增删改查



-- ----增加----
-- 全列插入
-- insert [into] 表名 values(...)
-- 主键字段 可以用0 null default 来占位
insert into classes values(0, "菜鸟班");
insert into classes values(null, "菜鸟班");
insert into classes values(default, "菜鸟班");


inset into student values(0, "laowang", 18, 190, "男", 0);
-- 枚举 的 下标从1开始
insert into student values(0, "laowang", 18, 190, 1, 0);


-- 部分插入
-- insert into 表名(列名, ...) values(值, ...)
insert into students (name, gender) values("小乔", 2);


-- 多行插入
insert into students values(default "name1", 20, "女", 1, "1990-01-01"), (default "name2", 20, "女", 1, "1990-01-01")
insert into students (name, gender) values("name1", 1), ()

-- ---修改----
-- update 表名 set 列名=值, ... [where 条件]
update students set gender = 2;
update students set gender = 2 where id=3;
update students set gender = 2, age=22 where id=3;


-- 查询
select * from students;
select * from students where name="xxxx";

-- 查询指定列
-- select 列名,... from 表名
select name, gender from students where id>0;

-- 可以使用as为列或表指定别名
-- select 字段[as 别名], 字段[as 别名] from 表名 where 条件
select name as 姓名, gender as 性别 from students;

-- 调整字段顺序
select gender as 性别, name as 姓名  from students;


-- ---删除---
-- 物理删除
-- delete from 表名 where 条件
delete from students;
delete from students where name="xxx";

-- 逻辑删除
-- 用一个字段来表示，这条信息是否已经不能再使用了
alert table students add is_delete bit default 0;
update students set is_delete=1 where id=6;

（1）show databases：查询所有数据库
（2）show tables：查询某一数据库中所有数据库表
（3）show variables：显示系统变量的名称和值
（4）show warnings：显示最后一条执行的SQL语句所产生的错误、警告等信息
（5）show status：显示系统特定资源的状态
（6）use database_name; 指定数据库
（7）desc table_name; 查看表结构

一、简单描述表结构，字段类型

select * from information_schema.columns
      where table_schema = 'db' #表所在数据库
        and table_name = 'tablename' ; #你要查的表

显示表结构，字段类型，主键，是否为空等属性，但不显示外键。

二、查询表中列的注释信息

select column_name, column_comment from information_schema.columns 
       where table_schema ='db' and table_name = 'tablename' ;

四、查看表的注释

select table_name,table_comment from information_schema.tables 
      where table_schema = 'db' and table_name ='tablename'

五、查看表生成的DDL

show create table table_name;

六、建表命令：

CREATE TABLE `t_sold_order` (
     `id`       int(11) NOT NULL AUTO_INCREMENT,
     `dt`       date DEFAULT NULL COMMENT '日期',
     `hour`     tinyint(2) DEFAULT '0' COMMENT '小时',
     `hour_order`  int(11) DEFAULT '0' COMMENT '小时订单数',
     `total_order` int(11) DEFAULT '0' COMMENT '总的订单数',
     `prediction`  int(11) DEFAULT '0' COMMENT '预测订单数',
   PRIMARY KEY (`id`),
   UNIQUE KEY `dt_hour` (`dt`,`hour`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='实时订单数'

七、表操作命令：

复制表结构：create table table1 like table;
复制数据：insert into table1 select * from table

八、机器授权：

grant select on *.* to 'reader'@'%' identified by '123456' WITH GRANT OPTION
flush privileges

九、查询数据直接插入

insert into t_visual_user_domain(`user_id`,`domain`,`group`) 
select id,'www.baidu.com' as domain,`group` from t_visual_user;

十、修改表结构

alter table competitor_goods add sku_id bigint(20) unsigned DEFAULT NULL COMMENT '商品销售码';