MySQL 表与记录操作

一.数据表操作
1.基础操作:

--创建表(类似于excel表)
  create table <tab_name> (
    <field_name1> <TYPE(x)> [<完整性约束条件>],
    <field_name2> <TYPE(x)>,
        ...
    <field_namen> >TYPE>--注意:最后1个字段不加逗号!!!
  ) [character set <xxx>];
  --参数说明:
    xxx:字符编码方式
    type(x):数据类型;x为最大位数(Date不需要);Numeric还需要1个参数d指定小数点后最大位数
  --创建一个员工表employee:
    create table employee (
      id TINYINT primary key auto_increment ,
      name VARCHAR(20),
      gender boolean,--MySQL无boolean,自动转换为TINYINT(1)
      --也可使用gender BIT
      job VARCHAR(20),
      salary DOUBLE(4,2) unsigned
    );
  --完整性约束条件:
    primary key:声明是主键(自动要求非空且唯一)
    foreign key:声明是外键
    unique:唯一(不得出现重复值);默认可以重复
    not null:非空;默认可以为空
    auto_increment:该字段为自动编号(从1开始);如果手动给定为x,下一条为x+1(见下图)
    default x:指定默认值为x;默认为NULL

--查看表信息
  desc <tab_name>:查看表结构
    mysql> desc employee
    +------+-----------+----+---+-------+--------------+
    |Field |Type       |Null|Key|Default|Extra         |
    +------+-----------+----+---+-------+--------------+
    |id    |tinyint(4) |NO  |PRI|NULL   |auto_increment|
    |name  |varchar(25)|YES |   |NULL   |              |
    |gender|tinyint(1) |YES |   |NULL   |              |
    |job   |varchar(20)|YES |   |NULL   |              |
    |salary|double(4,2)|YES |   |NULL   |unsigned      |
    +------+-----------+----+---+-------+--------------+
  show columns from <tab_name>:查看表结构(同desc)
  show tables:查看当前数据库中的所有的表
  show create table <tab_name>:查看当前数据库表建表语句 

--修改表结构
  --增加字段
    alter table <tab_name> add [column] <col_name type> [<完整性约束条件 position>];
      --position:指定字段位置;默认为最后;可为first,after <col_name>
      alter table user add addr varchar(20) not null unique first/after username;
    --1次添加多个字段
      alter table users2 
              add addr varchar(20),
              add age  int first,
              add birth varchar(20) after name;
  --修改列类型
    alter table <tab_name> modify <col_nume type> [<完整性约束条件> default <x position>];
      --default x:指定默认值为x;默认为NULL
      --position:指定修改后字段位置;见 增加字段
      alter table users2 modify age tinyint default 20;
      alter table users2 modify age int after id;
  --修改列名
    alter table <tab_name> change [column] <col_name new_col_name type> [<完整性约束条件> default <x position>］;
      alter table users2 change age Age int default 28 first;
  --删除列
    alter table <tab_name> drop [column] <col_name>;
      --1次删除多个字段
        alter table users2
                drop age,
                drop birth;
  --增加列并删除列
    alter table users2 
            add salary float(6,2) unsigned not null after name,
            drop addr;
  --修改表名
    rename table <tab_name> to <new_tab_name>;
      rename table users2 to users
  --修该表所用的字符集
    alter table <tab_name> character set <xxx>
      alter table student character set utf8;

--删除表
  drop table <tab_name>;

--添加/删除主键
  alter table <tab_name> add primary key (<field_name,...>) 
  alter table <tab_name> drop primary key;
  --auto_increment:
    create table test5(num int auto_increment);
    ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
    create table test(num int primary key auto_increment);
  --如何删除主键?
    alter table test modify id int;--auto_increment没了,但这样写主键依然存在
    --所以还要加上下面这句
    alter table test drop primary key;--仅用这句也无法直接删除主键

--唯一索引
  alter table <tab_name> add unique [index|key] [<索引名称>](<字段名称,...>)
    alter table users add unique(name);--索引值默认为字段名show create table users
    alter table users add unique key user_name(name);--索引值为user_name
  --添加联合索引
    alter table users add unique index name_age(name,age);#show create table users;
  --删除唯一索引
    alter table <tab_name> drop {index|key} <index_name>

• 创建文章表:

create table article (
    id int primary key auto_increment,
    title varchar(20),
    publish_date INT,
    click_num INT,
    is_top TINYINT(1),
    content TEXT
);

2.完整性约束条件之主键约束

1. 一张表只能有一个主键
2. 主键类型不一定是整型

(1)单字段主键:非空且唯一

<field type> primary key,

create table users (
    id INT primary key,
    name varchar(20),
    city varchar(20)
);

(2)多字段联合主键:

primary key(<field1,field2...>)

create table users2 (
    id INT,
    name varchar(20),
    city varchar(20),
    primary key(name,id)
);

九.表记录操作
1.增删改:

--增加记录
  insert [into] <tab_name> [(<field1,filed2...>)] values (<value1,value2...>);
    --可以只插入1条记录的部分字段(其余需有默认值或可以为NULL)
    --不加(field1...)时只能增加完整记录,不能空置某些字段
    create table employee_new(
            id int primary key auto_increment,
            name varchar(20) not null unique,
            birthday varchar(20),
            salary float(7,2)
    );
    insert into employee_new (id,name,birthday,salary)
            values (1,'yuan','1990-09-09',9000);
    insert into employee_new (name,salary)
            values ('xialv',1000);
  --插入多条记录
    insert [into] <tab_name> values (<record1>) (<record2>)...;
      --只能增加完整的记录,不能空置某些字段
      insert into employee_new values
              (4,'alvin1','1993-04-20',3000),
              (5,'alvin2','1995-05-12',5000);
  --set插入
    insert [into] <tab_name> set <col_name>=<value>,...;
      insert into employee_new set id=12,name="alvin3";

--修改表记录
  update <tab_name> set <field1>=<value1>,<field2>=<value2>...[where <语句>]
    --SET:修改哪些列/列的值
    --WHERE 语句:更新哪些记录;默认更新所有
    update employee_new set birthday="1989-10-24" WHERE id=1;
    --将yuan的薪水在原有基础上增加1000元:
    update employee_new set salary=salary+4000 where name='yuan';

--删除表纪录
  delete from <tab_name> [where <语句>];
    --where 语句:删除哪些记录;默认删除所有
    --只能删除表中内容,不能删除表本身,用drop删除表
    --删除表中name为"alex"的记录:
    delete from employee_new where name='alex';
    --删除表中id为2或3的记录:
    delete from employee_new where id=2 or id=3;
    --删除标准id为9且name为"y"的记录:
    delete from employee_new where id=4 and name="y";
    --删除表中所有记录:
    delete from employee_new;--注意:auto_increment没有被重置:alter table employee auto_increment=1;
  TRUNCATE TABLE <tab_name>;
    --删除表中的所有数据;该方式删除的数据不能在事务中恢复
    --与delete区别在于该方式先摧毁表,再新建同名表,故提示0条被影响
    truncate table emp_new;

2.单表查询:

• having能否独立使用？

SELECT [ALL|DISTINCT] <field> [AS <别名>] FROM <tab_name> [WHERE <条件> GROUP BY <field> HAVING <条件> ORDER BY <field> <order> LIMIT <条数>]

CREATE TABLE ExamResult(
        id INT PRIMARY KEY  auto_increment,
        name VARCHAR (20),
	    JS DOUBLE,
  	    Django DOUBLE,
		OpenStack DOUBLE
);
INSERT INTO ExamResult VALUES
        (1,"yuan",98,98,98),
        (2,"xialv",35,98,67),
        (3,"alex",59,59,62),
        (4,"wusir",88,89,82),
        (5,"alvin",88,98,67),
        (6,"yuan",86,100,55);

--select查询
  select [distinct|ALL] <field>... from <tab_name>...;
    --from:从哪张表筛选
    --all:查询选定范围内的所有记录;默认值,可省略
    --field:查看哪些字段;*指所有字段;字段按命令中顺序显示
      --如果非当前表的字段,field_应写为tab_.field_
    --tab_name:指定数据源
      --如果field来自多个表,为tab1_,tab2_...
    --distinct:声明剔除重复记录(选定的所有字段重复就剔除,显示第1个)
    --查询表中所有学生的信息:
    select * from ExamResult;
    --查询所有学生的姓名和JS成绩:
    select name,JS from ExamResult;
    --查询所有学生的姓名和JS成绩,并过滤重复数据:
    select distinct JS,name from ExamResult;

--使用表达式(存储的仍是原数据)
  --在所有学生分数上加10分特长分显示:
  select name,JS+10,Django+10,OpenStack+10 from ExamResult;
  --统计每个学生的总分:
  select name,JS+Django+OpenStack from ExamResult;

--以别名显示
  <field> as <别名>|<field 别名>
    --以别名表示学生姓名和总分:
    select name as 姓名,JS+Django+OpenStack as 总成绩 from ExamResult;
    select name 姓名,JS+Django+OpenStack 总成绩 from ExamResult;
    select name JS from ExamResult;--将JS当作name别名

--使用where进行过滤查询
  --只能使用字段进行过滤,不能使用别名进行过滤
  select <field> from <tab_name> [where <语句>];
  --where字句中可以使用:
    --比较运算符:>,<,>=,<=,<>,!=,between x_ and y_,in(x1_,...),like patt_
      between 80 and 100--[10,20]
      in(80,90,100)--值是10或20或30
      like 'yuan%'
        --文本的pattern可以是%(任意多字符),_(几个就匹配字符)
    --逻辑运算符:and,or,not
  --实例:
    --查询姓名为XXX的学生成绩:
    select * from ExamResult where name='yuan';
    --查询英语成绩大于90分的同学:
    select id,name,JS from ExamResult where JS>90;
    --查询总分大于200分的所有同学:
    select name,JS+Django+OpenStack as 总成绩 from
            ExamResult where JS+Django+OpenStack>200;
    --查询JS分数在[80,100]的同学:
    select name ,JS from ExamResult where JS between 80 and 100;
    --查询Django分数为75,76,77的同学:
    select name ,Django from ExamResult where Django in (75,98,77);
    --查询所有姓王的学生成绩:
    select * from ExamResult where name like '王%';
    --查询JS分>90，Django分>90的同学:
    select id,name from ExamResult where JS>90 and Django >90;
    --查找缺考数学的学生的姓名:
    select name from ExamResult where Database is null;

--使用Order by指定排序的列
  --排序的列可以是表中的列名,也可以是指定的别名
  select <field> from <tab_name> order by <field> [<order>];
    --默认按创建顺序排列
    --field:按那1个字段进行排列
    --order:如何排序;Asc(升序),Desc(降序);默认为Asc;NULL最小
    --ORDER BY子句应位于SELECT/WHERE语句后
  --实例:
    --对JS成绩排序后输出:
    select * from ExamResult order by JS;
    --对总分排序按从高到低的顺序输出:
    select name,(ifnull(JS,0)+ifnull(Django,0)+ifnull(Database,0))
            总成绩 from ExamResult order by 总成绩 desc;
    --对姓李的学生成绩排序输出:
    select name,(ifnull(JS,0)+ifnull(Django,0)+ifnull(OpenStack,0))
            总成绩 from ExamResult where name like 'a%'
            order by 总成绩 desc;

CREATE TABLE order_menu(
        id INT PRIMARY KEY auto_increment,
        product_name VARCHAR (20),
        price FLOAT(6,2),
        born_date DATE,
        class VARCHAR (20)
);
INSERT INTO order_menu (product_name,price,born_date,class) VALUES
        ("苹果",20,20170612,"水果"),
        ("香蕉",80,20170602,"水果"),
        ("水壶",120,20170612,"电器"),
        ("被罩",70,20170612,"床上用品"),
        ("音响",420,20170612,"电器"),
        ("床单",55,20170612,"床上用品"),
        ("草莓",34,20170612,"水果");

--使用group by进行分组查询
  --注意,按分组条件分组后每一组只会显示第一条记录
  SELECT <field> from <tab_name> group by <acc>;
  --acc:按什么分组;字段(该字段值相同的记录为1组;可接多个字段),字段位置(按第n个字段)
  --实例:
    --按位置字段筛选:
    select * from order_menu group by 5;--即按class
    --对购物表按类名分组后显示每一组商品的价格总和:
    select class,SUM(price) from order_menu group by class;
  --使用having进行筛选:
    SELECT <field> from <tab_name> group by <field> HAVING <条件语句>
    --对购物表按类名分组后显示每一组商品价格总和超过150的商品:
    select class,SUM(price) from order_menu
            group by class HAVING SUM(price)>150;
    --having和where都可以对查询结果进行过滤
    --差别有:
      ·where语句只能用在分组前的筛选,having可用在分组后的筛选
      ·使用where语句的地方都可以用having进行替换
      ·但使用having必需使用group by
      ·having中可以用聚合函数,where中不行
      ·where效率高于having
  --GROUP_CONCAT()
    SELECT id,GROUP_CONCAT(name),GROUP_CONCAT(JS) from ExamResult GROUP BY id;

--聚合函数:(一般和分组查询配合使用)
  SELECT <func>(<field>) from <tab_name> where <条件> group by <field>
  --将要求的内容查出来再包上聚合函数即可
  --<1>统计表中所有记录:
    --COUNT(<field>):统计记录数
      --注意:count(*)统计所有行;count(field_)不统计NULL值
      --统计1个班级有多少学生:
      select count(*) from ExamResult;--先查出所有的学生,再用count包上
      --统计JS成绩大于70的学生有多少个:
      select count(JS) from ExamResult where JS>70;
      --统计总分大于280的学生有多少个:
      select count(name) from ExamResult
              where (ifnull(JS,0)+ifnull(Django,0)+ifnull(OpenStack,0))>280;
    --SUM(<field>):统计该组该字段所有值的内容和
      --注意:sum仅可用于数值(NULL相当于0),否则会报错
      --统计一个班级JS总成绩:
      select sum(JS) as JS总成绩 from ExamResult;--先查出所有的JS成绩，再用sum包上
      --统计一个班级各科分别的总成绩:
      select sum(JS) as JS总成绩,
              sum(Django) as Django总成绩,
              sum(OpenStack) as OpenStack from ExamResult;
      --统计一个班级各科的成绩总和:
      select sum(ifnull(JS,0)+ifnull(Django,0)+ifnull(Database,0))
              as 总成绩 from ExamResult;
      --统计一个班级JS成绩平均分:
      select sum(JS)|count(*) from ExamResult;
    --AVG(<field>):统计该组该字段所有值的平均值
      --注意:AVG()不计入NULL,故结果不同于SUM()/COUNT()
      --求一个班级JS平均分:
      select avg(ifnull(JS,0)) from ExamResult;--先查出所有的JS分,然后用avg包上
      --求一个班级总分平均分:
      select avg((ifnull(JS,0)+ifnull(Django,0)+ifnull(Database,0)))
              from ExamResult ;
    --Max(<field>)/Min(<field>):统计该组该字段中的最大/小值
      --注意:仅可用于数值(不包括NULL,但又NULL不报错)
      --求班级最高分和最低分(数值范围在统计中特别有用):
      select Max((ifnull(JS,0)+ifnull(Django,0)+ifnull(OpenStack,0)))
              最高分 from ExamResult;
      select Min((ifnull(JS,0)+ifnull(Django,0)+ifnull(OpenStack,0)))
              最低分 from ExamResult;
      --求购物表中单价最高的商品名称及价格
      SELECT id, MAX(price) FROM order_menu;--id和最高价商品是一个商品吗?
    --ifnull(<field>,<x>):如果值是NULL,转换为x,否则不变
    --注意:NULL和所有数计算结果都是null,所以需要用ifnull将NULL转换为0！
  --<2>统计分组后的组记录:
    GROUP BY field_ WITH ROLLUP:对GROUP BY每个分组分别进行指定统计
      --统计各类商品的总花费:
      SELECT product_name,SUM(price) FROM order_menu GROUP BY class WITH ROLLUP;

--执行顺序:from-->where-->select-->group by-->having-->order by
  select JS as JS成绩 from ExamResult where JS成绩 >70;--不成功
  select JS as JS成绩 from ExamResult having JS成绩 >90;--成功

--使用limit限制最大显示记录数
  SELECT <field> from <tab_name> limit <x>,<y>
    --x:开始x条不显示,默认为0
    --y:最多显示y条,默认为到结尾;只有1个参数时为y
  --实例:
    SELECT * from ExamResult limit 1;
    SELECT * from ExamResult limit 2,5;--跳过前两条显示接下来的五条纪录
    SELECT * from ExamResult limit 2,2;

--使用正则表达式查询
  --效率低于LIKE
  SELECT field_ FROM tab_name_ WHERE field_ REGEXP 表达式
    SELECT * FROM employee WHERE emp_name REGEXP '^yu';
    SELECT * FROM employee WHERE emp_name REGEXP 'yun$';
    SELECT * FROM employee WHERE emp_name REGEXP 'm{2}';