数据库操作:
---查看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); 拷贝表
