mysql 基本操作

数据库操作:

---查看warnings内容
show warnings

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

---查看有那些数据库
show databases;

---查看数据库创建信息
show create database s3;

---创建数据库
create database if not exists s3;

---创建数据库 设置编码
create database if not exists s4 character set utf8;

---修改数据库编码
alter database s4  character set gbk;

---删除数据库
drop database s4;

创建表

/////////////表处理(主键:非空且唯一) 非空:not null 唯一:unique float(4,2)表示6位数,小数点数2位 最大99.99
---创建表
create table emp(
    id INT primary KEY auto_increment,
   name VARCHAR(30),
   age TINYINT DEFAULT 100,
   salary FLOAT(9,2)
);

 

---多字段联合主键
create table users2(
    id INT,
    name varchar(20),
    city varchar(20),
    primary key(name,id)
    );

 

查看表信息

---查看表创建信息
desc tab_name 查看表结构
show columns from tab_name  查看表结构
show tables 查看当前数据库中的所有的表
show create table tab_name    查看当前数据库表建表语句

修改表信息

----修改表结构
--增加列(字段) alter table tab_name add [column] 列名 类型［完整性约束条件］［first｜after 字段名］;
ALTER TABLE emp ADD address TEXT;
#添加多个字段
ALTER TABLE emp ADD date DATE not NULL,ADD work VARCHAR(22) not NULL;

--删除列
alter TABLE emp DROP WORK;  删除work 字段
#删除多个字段
alter TABLE emp DROP date,DROP address;

--修改字段属性
ALTER TABLE emp MODIFY age SMALLINT NOT NULL; #age字段类型修改成SMALLINT NOT NULL
#修改到那个字段后面［first｜after 字段名］  放到第一  after 放到那个字段后面
ALTER TABLE emp MODIFY age SMALLINT NOT NULL AFTER id;  #AFTER id放到ID 后面

--修改列名
alter table tab_name change [column] 列名 新列名 类型 [完整性约束条件]［first｜after 字段名］;
ALTER TABLE emp CHANGE age uuid VARCHAR(20) not null; #age 修改成uuid


--修改表明
RENAME TABLE emp to emppp;

--添加主键，删除主键
    alter table tab_name add primary key(字段名称,...) 
    alter table users drop primary key;

    eg:
    mysql> 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

--插入表数据
INSERT into emp(name,salary) VALUES("张三丰",1900.99);

INSERT into emp set name="黑墨水"

#插入多行
INSERT into emp(name,salary) VALUES("张三丰",1900.99),("张无忌",2900.99);

--更新
UPDATE emp set salary=salary+20000 WHERE name="张无忌"; #salary=salary+20000(salary是原来存在的数据在加上20000)
#修改多个字段
UPDATE emp set salary=salary+20000,name="一天"  WHERE name="张无忌"

---删除表数据
DELETE FROM emp; #删除所有数据 (TRUNCATE TABLE emp)
DELETE FROM emp WHERE name="一天"
DELETE FROM emp WHERE id=2 and name="张三丰"
DELETE FROM emp WHERE id=2 OR name="张三丰"

表查询

SELECT id,name as 肚子 FROM emp #查询id  name
SELECT DISTINCT name FROM emp  ; #DISTINCT 给name字段去除重复的内容
#别名
SELECT DISTINCT name as 肚子 FROM emp

--%
SELECT  id,name FROM emp WHERE salary like 'yuan%'
 /*
pattern可以是%或者_，
如果是%则表示任意多字符，此例如唐僧,唐国强
如果是_则表示一个字符唐_，只有唐僧符合。两个_则表示两个字符：__
*/


--大于查询出来
SELECT  id,name FROM emp WHERE salary > 100;

--查找区间
SELECT  id,name FROM emp WHERE salary BETWEEN 80 and 90   #between 80 and 100 值在10到20之间

--值包含这些的
SELECT  id,name FROM emp WHERE salary in (80,90,200)   # in(80,90,100) 值是10或20或30

--查询空的
SELECT  id,name FROM emp WHERE salary=null;  #这个无法查询
SELECT  id,name FROM emp WHERE salary is null;  用这个查询空

--升序排序
SELECT  id,name FROM emp WHERE ORDER BY id; #按id
SELECT  id,name FROM emp WHERE id > 100  ORDER BY id; #按id

--降序
SELECT  id,name FROM emp WHERE id > 100  ORDER BY id desc; #按id

--2个字段的相加
SELECT age+salary FROM emp; #age+salary  2个字段相加
SELECT age+salary FROM emp  ORDER BY age+salary desc;  #age+salary降序排
SELECT age+salary as test FROM emp  ORDER BY test desc;

重点：Select   from    where    group by    having    order by  这些过滤条件执行顺序
-- Mysql在执行sql语句时的执行顺序：
-- 先执行 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; --- 成功


---分组
-- 注意,按分组条件分组后每一组只会显示第一条记录(也就说如果按name分类了,那name中有3个叫张三丰的,只会显示第一条记录,另外两条记录给里分离到第一条里面去了)
-- group by字句，其后可以接多个列名，也可以跟having子句,对group by 的结果进行筛选。

--按第二个字段进行分组
SELECT * FROM emp GROUP BY 2

--按name分组 然后计算salary的和

SELECT `name`,SUM(salary) FROM emp GROUP BY name;#解析 name 分组后只会显示第一条记录,然后计算sum计算统计这时候 有3个张珊分到一个组里面会算出 所有是30000

 结果:

 

--HAVING分组后过滤条件 不能使用where   where是在分组之前过滤
SELECT `name`,SUM(salary) FROM emp GROUP BY name HAVING SUM(salary) > 20000;
 /*
having 和 where两者都可以对查询结果进行进一步的过滤，差别有：
 <1>where语句只能用在分组之前的筛选，having可以用在分组之后的筛选；
 <2>使用where语句的地方都可以用having进行替换
 <3>having中可以用聚合函数，where中就不行。
*/

-- （6）聚合函数： 先不要管聚合函数要干嘛，先把要求的内容查出来再包上聚合函数即可。
                 --   (一般和分组查询配合使用)

--<1> 统计表中所有记录

-- COUNT(列名)：统计行的个数
-- 统计一个班级共有多少学生？先查出所有的学生，再用count包上
 select count(*) from ExamResult;
-- 统计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;
-- 注意:count(*)统计所有行;     count(字段)不统计null值.

-- SUM(列名)：统计满足条件的行的内容和
-- 统计一个班级JS总成绩？先查出所有的JS成绩，再用sum包上
    select JS as JS总成绩 from ExamResult;
    select sum(JS) as JS总成绩 from ExamResult;
-- 统计一个班级各科分别的总成绩
    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 ;
-- 注意：sum仅对数值起作用，否则会报错。

-- AVG(列名)：
        -- 求一个班级JS平均分？先查出所有的JS分，然后用avg包上。
            select avg(ifnull(JS,0)) from ExamResult;
        -- 求一个班级总分平均分
            select avg((ifnull(JS,0)+ifnull(Django,0)+ifnull(Database,0)))
                                     from ExamResult ;
-- Max、Min
        -- 求班级最高分和最低分（数值范围在统计中特别有用）
          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和最高价商品是一个商品吗?

          SELECT MAX(price) FROM order_menu;

        -- 注意：null 和所有的数计算都是null，所以需要用ifnull将null转换为0！
        --      -----ifnull(JS,0)


-- with rollup的使用

--<2> 统计分组后的组记录

---外键

---  每一个班主任会对应多个学生 , 而每个学生只能对应一个班主任

----主表

CREATE TABLE ClassCharger(

       id TINYINT PRIMARY KEY auto_increment,
       name VARCHAR (20),
       age INT ,
       is_marriged boolean  -- show create table ClassCharger: tinyint(1)

);

INSERT INTO ClassCharger (name,age,is_marriged) VALUES ("冰冰",12,0),
                                                       ("丹丹",14,0),
                                                       ("歪歪",22,0),
                                                       ("姗姗",20,0),
                                                       ("小雨",21,0);


----子表

CREATE TABLE Student(

       id INT PRIMARY KEY auto_increment,
       name VARCHAR (20),
       charger_id TINYINT,     --切记:作为外键一定要和关联主键的数据类型保持一致
       FOREIGN KEY (charger_id) REFERENCES ClassCharger(id),   关联外建 charger_id字段当前表 到 classcharger表的id字段
       -- ADD CONSTRAINT charger_fk_stu FOREIGN KEY (charger_id) REFERENCES ClassCharger(id)  ##给外建加名字 ADD CONSTARAINT charger_fk_stu 这段是自己加上外键名字  

) ENGINE=INNODB;

INSERT INTO Student(name,charger_id) VALUES ("alvin1",2),
                                            ("alvin2",4),
                                            ("alvin3",1),
                                            ("alvin4",3),
                                            ("alvin5",1),
                                            ("alvin6",3),
                                            ("alvin7",2);


DELETE FROM ClassCharger WHERE name="冰冰";
INSERT student (name,charger_id) VALUES ("yuan",1);
-- 删除居然成功,可是 alvin3显示还是有班主任id=1的冰冰的;

-----------增加外键和删除外键---------

ALTER TABLE student  ADD CONSTRAINT abc
                     FOREIGN KEY(charger_id)
                     REFERENCES  classcharger(id);


ALTER TABLE student DROP FOREIGN KEY abc;

--外键约束对子表的含义:   如果在父表中找不到候选键,则不允许在子表上进行insert/update

--外键约束对父表的含义:    在父表上进行update/delete以更新或删除在子表中有一条或多条对
                    -- 应匹配行的候选键时,父表的行为取决于：在定义子表的外键时指定的
                    -- on update/on delete子句


-----------------innodb支持的四种方式---------------------------------------

-----cascade方式 在父表上update/delete记录时，同步update/delete掉子表的匹配记录
-----外键的级联删除：如果父表中的记录被删除，则子表中对应的记录自动被删除--------

     FOREIGN KEY (charger_id) REFERENCES ClassCharger(id)
                              ON DELETE CASCADE  #主表删除字表里面的数据也跟着删除


------set null方式 在父表上update/delete记录时，将子表上匹配记录的列设为null
   -- 要注意子表的外键列不能为not null

     FOREIGN KEY (charger_id) REFERENCES ClassCharger(id)
                              ON DELETE SET NULL #主表删掉后,字表里面的记录变成null值 不会报错.


------Restrict方式 :拒绝对父表进行删除更新操作(了解)

------No action方式 在mysql中同Restrict,如果子表中有匹配的记录,则不允许对父表对应候选键
   -- 进行update/delete操作（了解）

多表查询:

 

内连接查询

-- 准备两张表
-- company.employee
-- company.department

      create table employee(
      emp_id int auto_increment primary key not null,
      emp_name varchar(50),
      age int,
      dept_id int
      );

      insert into employee(emp_name,age,dept_id) values
        ('A',19,200),
        ('B',26,201),
        ('C',30,201),
        ('D',24,202),
        ('E',20,200),
        ('F',38,204);


    create table department(
       dept_id int,
       dept_name varchar(100)
      );

    insert into department values
      (200,'人事部'),
      (201,'技术部'),
      (202,'销售部'),
      (203,'财政部');

mysql> select * from employee;
+--------+----------+------+---------+
| emp_id | emp_name | age  | dept_id |
+--------+----------+------+---------+
|      1 | A        |   19 |     200 |
|      2 | B        |   26 |     201 |
|      3 | C        |   30 |     201 |
|      4 | D        |   24 |     202 |
|      5 | E        |   20 |     200 |
|      6 | F        |   38 |     204 |
+--------+----------+------+---------+
rows in set (0.00 sec)

mysql> select * from department;
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
|     200 | 人事部    |
|     201 | 技术部    |
|     202 | 销售部    |
|     203 | 财政部    |
+---------+-----------+
rows in set (0.01 sec)

查询A在那个部门
SELECT * FROM department,employee WHERE employee.emp_name = "A" AND  employee.dept_id = department.dept_id
或者:
SELECT * FROM employee INNER JOIN department  on employee.emp_name = "A" AND  employee.dept_id = department.dept_id
都是一样的

 外连接查询

--（1）左外连接：在内连接的基础上增加左边有右边没有的结果 也就是说主表为左边的上的数据全部显示出来,右边表上面如果没有匹配上就显示空置

 select * from employee left join department on employee.dept_id = department.dept_id;

     +--------+----------+------+---------+---------+-----------+
    | emp_id | emp_name | age  | dept_id | dept_id | dept_name |
    +--------+----------+------+---------+---------+-----------+
    |      1 | A        |   19 |     200 |     200 | 人事部    |
    |      5 | E        |   20 |     200 |     200 | 人事部    |
    |      2 | B        |   26 |     201 |     201 | 技术部    |
    |      3 | C        |   30 |     201 |     201 | 技术部    |
    |      4 | D        |   24 |     202 |     202 | 销售部    |
    |      6 | F        |   38 |     204 |    NULL | NULL      |
    +--------+----------+------+---------+---------+-----------+

 --（2）右外连接：在内连接的基础上增加右边有左边没有的结果

 select * from employee RIGHT JOIN department on employee.dept_id = department.dept_id;

        +--------+----------+------+---------+---------+-----------+
        | emp_id | emp_name | age  | dept_id | dept_id | dept_name |
        +--------+----------+------+---------+---------+-----------+
        |      1 | A        |   19 |     200 |     200 | 人事部    |
        |      2 | B        |   26 |     201 |     201 | 技术部    |
        |      3 | C        |   30 |     201 |     201 | 技术部    |
        |      4 | D        |   24 |     202 |     202 | 销售部    |
        |      5 | E        |   20 |     200 |     200 | 人事部    |
        |   NULL | NULL     | NULL |    NULL |     203 | 财政部    |
        +--------+----------+------+---------+---------+-----------+

 --（3）全外连接：在内连接的基础上增加左边有右边没有的和右边有左边没有的结果

    -- mysql不支持全外连接 full JOIN
    -- mysql可以使用此种方式间接实现全外连接
    
   select * from employee RIGHT JOIN department on employee.dept_id = department.dept_id
   UNION
   select * from employee LEFT JOIN department on employee.dept_id = department.dept_id;

        

        +--------+----------+------+---------+---------+-----------+
        | emp_id | emp_name | age  | dept_id | dept_id | dept_name |
        +--------+----------+------+---------+---------+-----------+
        |      1 | A        |   19 |     200 |     200 | 人事部    |
        |      2 | B        |   26 |     201 |     201 | 技术部    |
        |      3 | C        |   30 |     201 |     201 | 技术部    |
        |      4 | D        |   24 |     202 |     202 | 销售部    |
        |      5 | E        |   20 |     200 |     200 | 人事部    |
        |   NULL | NULL     | NULL |    NULL |     203 | 财政部    |
        |      6 | F        |   38 |     204 |    NULL | NULL      |
        +--------+----------+------+---------+---------+-----------+

      -- 注意 union与union all的区别：union会去掉相同的纪录

 

 

-- 查询员工年龄大于等于25岁的部门

    SELECT DISTINCT department.dept_name
    FROM employee,department
    WHERE employee.dept_id = department.dept_id
    AND age>25;


--以内连接的方式查询employee和department表，并且以age字段的升序方式显示

    select employee.emp_id,employee.emp_name,employee.age,department.dept_name
    from employee,department
    where employee.dept_id = department.dept_id
    order by age asc;

 

 子查询

 

-- 子查询是将一个查询语句嵌套在另一个查询语句中。
-- 内层查询语句的查询结果，可以为外层查询语句提供查询条件。
-- 子查询中可以包含：IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字
-- 还可以包含比较运算符：= 、 !=、> 、<等


-- 1. 带IN关键字的子查询

   ---查询employee表，但dept_id必须在department表中出现过

   select * from employee
            where dept_id IN
            (select dept_id from department);


+--------+----------+------+---------+
| emp_id | emp_name | age  | dept_id |
+--------+----------+------+---------+
|      1 | A        |   19 |     200 |
|      2 | B        |   26 |     201 |
|      3 | C        |   30 |     201 |
|      4 | D        |   24 |     202 |
|      5 | E        |   20 |     200 |
+--------+----------+------+---------+
rows in set (0.01 sec)



-- 2. 带比较运算符的子查询
      --      =、!=、>、>=、<、<=、<>

     -- 查询员工年龄大于等于25岁的部门
     select dept_id,dept_name from department
           where dept_id IN
          (select DISTINCT dept_id from employee where age>=25);

-- 3. 带EXISTS关键字的子查询

-- EXISTS关字键字表示存在。在使用EXISTS关键字时，内层查询语句不返回查询的记录。
-- 而是返回一个真假值。Ture或False
-- 当返回Ture时，外层查询语句将进行查询；当返回值为False时，外层查询语句不进行查询

     select * from employee
              WHERE EXISTS
              (SELECT dept_name from department where dept_id=203);#(SELECT dept_name from department where dept_id=203)这句是返回如果是真 就继续执行select * from employee 不然就不执行了

--department表中存在dept_id=203，Ture select * from employee WHERE EXISTS (SELECT dept_name from department where dept_id=205); -- Empty set (0.00 sec)  ps: create table t1(select * from t2); 拷贝表