MySQL基础

-- mysql               #本地登录，默认用户root，空密码，用户为root@127.0.0.1
-- mysql -uroot -p1234 #本地登录，指定用户名和密码，用户为root@127.0.0.1
-- mysql -uroot -p1234 -h 192.168.31.95 #远程登录，用户为root@192.168.31.95
-- 数据库操作（DDL：Data Definition Languages）
--创建数据库（在磁盘上创建一个对应的文件夹）
CREATE DATABASE [IF NOT EXISTS ] aggressive2020 [character SET utf8]
--查看数据库
SHOW databases; --查看所有数据库
show CREATE DATABASE aggressive2019 --查看数据库的创建方式
ALTER DATABASE aggressive2019 CHARACTER SET utf8 --修改数据库
use aggressive2019;--使用数据库
SELECT database();--查看当前使用的数据库

--数据表操作
CREATE TABLE employee(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20),gender bit DEFAULT 1,
birthday DATE,
entry_data date,
job VARCHAR(20),
salary DOUBLE(4,2) UNSIGNED,
resume text);
--查看表结构
DESC employee;
--查看表结构
SHOW columns from employee;
--查看当前数据库表建表语句
SHOW CREATE TABLE employee;
--增加列（字段）ALTER
ALTER TABLE employee ADD addr VARCHAR(20) NOT NULL UNIQUE AFTER name;
--添加多个字段
ALTER TABLE employee ADD age int FIRST , ADD workage VARCHAR(20) AFTER addr;
--修改一列类型 modify
ALTER TABLE employee MODIFY age TINYINT DEFAULT 20;
--修改列名 change
ALTER TABLE employee CHANGE age AGE INT DEFAULT 28 FIRST;
--删除一列
ALTER TABLE employee DROP addr;
--修改表名
RENAME TABLE employee to employee1;
--修改表所用的字符集
ALTER TABLE employee CHARACTER SET utf8;
--删除表
drop table employee;
--添加主键，删除主键
ALTER TABLE employee add PRIMARY KEY(id);
ALTER TABLE employee MODIFY id INT AUTO_INCREMENT;
--删除主键
alter table employee modify id int;
ALTER TABLE employee drop PRIMARY KEY ;
--添加唯一索引
ALTER TABLE employee ADD UNIQUE INDEX index_age(age);
--添加联合索引
ALTER TABLE employee ADD UNIQUE INDEX name_age(name,age);
--删除唯一索引
ALTER TABLE employee DROP INDEX name_age;

--创建文章表
CREATE TABLE article(
  id INT PRIMARY KEY AUTO_INCREMENT,
  title VARCHAR(20),
  publish_date INT,
  click_num INT,
  is_top TINYINT(1),
  content TEXT);
--完整性约束条件之主键约束（一张表只能有一个主键，非空且唯一，主键类型不一定是非整型）
--单字段主键
CREATE TABLE users(id INT PRIMARY KEY ,
name VARCHAR(20),
city VARCHAR(20));
--多字段联合主键
CREATE TABLE users2(
  id INT,
  name VARCHAR(20),
  city VARCHAR(20),
  PRIMARY KEY (name,id));

--表记录操作
--表记录之增、删、改
--增加一条记录
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  VALUES (2,'吴西平','1988-08-07',10000);
INSERT INTO employee_new (name,salary)VALUES ('xialv',1000);
--插入多条数据
INSERT into employee_new values(4,'alvin1','1993-04-20',3000),
(5,'alvin2','1995-05-12',5000);
INSERT INTO employee_new SET id=12,name='alvin3'; --set 插入法
--修改表记录
UPDATE employee_new SET birthday='1989-10-24' WHERE id=1;
--将yuan的薪水在原有的基础上增加1000元
UPDATE employee_new SET salary=salary+1000 where name='yuan';
--删除表记录（delete from employee_new WHERE ...)
--删除表中名称为alex的记录
DELETE FROM employee_new WHERE name='alex';
--删除表中所有记录
DELETE from employee_new;
--使用truncate删除表中的记录
TRUNCATE table employee_new;

--表记录之查询（单表查询）
--查询表达式
   SELECT *|field1,filed2 ...   FROM tab_name
                  WHERE 条件
                  GROUP BY field
                  HAVING 筛选
                  ORDER BY field
                  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 * FROM ExamResult;
--过滤表中的重复数据
SELECT DISTINCT JS FROM ExamResult;
--查询表中所有学生的姓名和对应的JS成绩
SELECT name,JS FROM ExamResult;
--SELECT 也可以使用表达式，并且可以使用：字段 as 别名或者：字段 别名
SELECT name,JS+10,Django+10,OpenStack+10 FROM ExamResult;
--统计每个学生的总分
SELECT name,JS+Django+OpenStack FROM ExamResult;
--使用别名表示学生总分
SELECT name as 姓名,JS+Django+OpenStack as 总成绩 FROM ExamResult;
select name ,JS+Django+OpenStack 总成绩 FROM ExamResult;
SELECT name JS FROM ExamResult;--记得加逗号

--使用where子句，进行过滤查询
--查询姓名为yuan的学生成绩
SELECT * from ExamResult WHERE name='yuan';
--查询JS成绩大于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分数在70——100之间的同学
SELECT name,JS FROM ExamResult WHERE JS BETWEEN 70 AND 100;
--查询Django分数为75,98，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 JS is NULL ;

--order by 指定排序的列，排序的列即可是表中的列名，也可以是select语句后制定的别名
--Asc升序、Desc 降序，其中asc为默认值 order by 子句应位于select 语句的结尾
--对JS成绩排序后输出
SELECT * FROM ExamResult ORDER BY JS;
--对总分排序后从高到底的顺序输出
SELECT name,JS+Django+OpenStack as 总成绩 FROM ExamResult ORDER BY 总成绩 DESC ;
--对姓李学生成绩排序输出
SELECT name,JS+Django+OpenStack as 总成绩 FROM ExamResult WHERE name LIKE 'a%' ORDER BY 总成绩 DESC ;

--group by 分组查询
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 字句，其后可以接多个列名，也可以跟having子句，对group by的结果进行筛选
--按位置字段进行筛选
select * from order_menu group by 5;
--对购物表按类名分组后显示每一组商品的价格总和
SELECT class,sum(price) from order_menu GROUP BY class;
--对购物表按类名分组后显示每一组商品价格总和超过150的商品
SELECT class,sum(price) from order_menu GROUP BY class HAVING sum(price)>150;
--注意：having 和 where两者都可以对查询结果进行进一步的过滤，差别有：
                     <1>where语句只能用在分组之前的筛选，having可以用在分组之后的筛选；
                     <2>使用where语句的地方都可以用having进行替换
                     <3>having中可以用聚合函数，where中就不行。
--group_concat()函数
SELECT id,group_concat(name),group_concat(JS) FROM ExamResult group by id;
--聚合函数（先把要求的内容查出来再包上聚合函数即可）
--统计表中所有记录
--COUNT(列名)统计行的个数
--统计一个班有多少学生，先查出所有学生，再用count包上
select count(*) from ExamResult;
--统计JS成绩大于70的学生有多少个
select count(JS) from ExamResult where JS>70;
--统计总分大于280的人数有多少个
select count(name) from ExamResult where JS+Django+OpenStack>280;

--SUM(列名)：统计满足条件的行的内容和
--统计一个班JS的总成绩，先查出所有JS成绩，再用SUM包上
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(OpenStack,0)) AS 总成绩 FROM ExamResult;
SELECT sum(JS+OpenStack+Django) AS 总成绩 FROM ExamResult;
--统计一个班JS成绩平均分
SELECT sum(JS)/count(*) FROM ExamResult;

--AVG(列名)
--求一个班级JS平均分？先查出所有JS分，然后用avg包上
SELECT avg(JS) FROM ExamResult;
--求一个班级总分平均分
SELECT avg(JS+Django+OpenStack) FROM ExamResult;
SELECT avg((ifnull(JS,0)+ifnull(Django,0)+ifnull(OpenStack,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;
SELECT max(price) FROM order_menu;
--注意
Mysql在执行sql语句时的执行顺序：
                -- from  where  select group by  having order by
分析：
SELECT JS as JS成绩 FROM ExamResult WHERE JS成绩>90;不成功
SELECT JS as JS成绩 FROM ExamResult HAVING JS成绩>90;--成功
--limit
SELECT * FROM ExamResult LIMIT 2; --按顺序显示两条记录
SELECT * FROM ExamResult LIMIT 2,3;--跳过前两条显示接下来的三条记录
SELECT * FROM ExamResult LIMIT 2,2;
--使用正则表达式查询
SELECT * FROM ExamResult WHERE name REGEXP 'yu';
SELECT * FROM ExamResult WHERE name REGEXP 'yun$';
SELECT * FROM ExamResult WHERE name REGEXP 'm{2}';

--外键约束
创建外键
--每一个班主任会对应多个学生，而每一个学生只能对应一个班主任
--主表
create table ClassCharger(
  id tinyint primary key auto_increment,
  name VARCHAR(20),
  age INT,
  is_married boolean
);
insert into ClassCharger(name, age, is_married) 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) ENGINE =innodb;
INSERT INTO Student(name,charger_id) VALUES ("alvin1",2),
                                            ("alvin2",4),
                                            ("alvin3",1),
                                            ("alvin4",3),
                                            ("alvin5",1),
                                            ("alvin6",3),
                                            ("alvin7",2);
--增加和删除外键
alter table Student ADD constraint abc foreign key (charger_id) references ClassCharger(id);
alter TABLE Student drop FOREIGN KEY abc;

--多表查询
CREATE DATABASE aggressive2020 CHARACTER SET utf8;
create table employee(emp_id INT primary key not null,
emp_name varchar(50),
age int,
dept_id int);
INSERT into employee(emp_id,emp_name,age,dept_id)VALUES (1,'A',19,200),
  (2,'B',26,201),
  (3,'C',30,201),
  (4,'D',24,202),
  (5,'E',20,200),
  (6,'F',38,204);
create table department(
       dept_id int,
       dept_name varchar(100));
insert into department values
  (200,'人事部'),
  (201,'技术部'),
  (202,'销售部'),
  (203,'财政部');
--内连接：仅选出两张表中互相匹配的记录，而外连接会先出其他不匹配的记录，最常用的是内连接
-- select * from employee,department where employee.dept_id = department.dept_id;
--select * from employee inner join department on employee.dept_id = department.dept_id;
外连接：
左连接：包含所有左边表中的记录甚至是右边表中没有和它匹配的记录
右连接：包含所有右边表中的记录甚至是左边表中没有和它匹配的记录
全外连接：在内连接的基础上增加左边有右边没有和右边有左边没有的结果
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;
多表查询之复合条件查询
--查询员工年龄大于等于25的部门
select distinct dept_name from employee,department where
  employee.dept_id =department.dept_id AND age>25;
--以内连接的方式查询employee和department表，并且以age字段升序方式显示
SELECT * FROM employee,department WHERE employee.dept_id=department.dept_id ORDER BY age ASC ;

--多表查询之子查询
--查询employee表，但dept_id必须在department表中出现过
select * from employee WHERE dept_id in (SELECT dept_id FROM department);
--查询员工年龄大于等于25岁的部门
select dept_id,dept_name FROM department WHERE dept_id IN (
  SELECT DISTINCT dept_id FROM employee WHERE age>25);

select * FROM employee WHERE exists(SELECT dept_name FROM department
WHERE dept_id=205);