MySQL中除了字符串类型需要设置长度,其他类型都有默认长度.
数值类型 | Java中 | MySQL中 |
---|---|---|
整型 | byte | tinyint |
short | smallint | |
int | int | |
long | bigint | |
浮点型 | float | float |
double | double | |
字符串类型 | String | 定长char() ;可变长varchar() |
时间日期 | date | date/time/datetime |
SQL可以分为:
--数据定义语言DDL(create,alter,drop)
– 一、数据库相关的DDL
– 1.创建数据库 CREATE DATABASE mybase; – 2.创建数据库并指定字符集 CREATE DATABASE mybase1 CHARACTER SET UTF8; – 3.查看所有数据库 SHOW DATABASES; – 4.查看当前使用的数据库 SELECT DATABASE(); – 5.修改数据库 ALTER DATABASE mybase CHARACTER SET UTF8; – 6.删除数据库 DROP DATABASE mybase1; – 切换数据库 USE mybase;
–二、表相关DDL
– 1.创建表 create table exam( id INT(11) PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20), English INT, Chinese INT, Math int ); – 2.查看数据库中所有表 show TABLES; – 3.查看表结构 desc exam;
– 4.表的删除 drop table exam;
– 5.表的修改(基于exam表)
– 5.1添加列 ALTER TABLE exam ADD History INT NOT NULL; – 5.2修改列的类型、长度、约束 ALTER TABLE exam MODIFY History DOUBLE(7,2); – 5.3修改表的列名 ALTER TABLE exam CHANGE History Physics INT NOT NULL; – 5.4修改表名 RENAME TABLE exam TO score; – 5.5修改表的字符集 ALTER TABLE score CHARACTER SET GBK; – 5.6删除列 ALTER TABLE score DROP Physics; – 5.7
ALTER TABLE
– 三、练习:创建emp表
CREATE TABLE emp( empno INT PRIMARY KEY AUTO_INCREMENT, ename VARCHAR(20), job VARCHAR(20), mgr int, hiredate DATE, sal DOUBLE(7,2), commit double(5,2), deptno INT NOT NULL – 非空约束 );
表exam:
表emp:
-- 数据操纵语言DML(update,insert,delete)
– 1.插入部分列 INSERT INTO score(id,NAME,English,Chinese,Math) VALUE(1,‘Hudie’,90,90,90); INSERT INTO score(id,NAME,English,Chinese) VALUE(NULL,‘diedie’,91,91); – 2.插入所有列 INSERT INTO score VALUES(3,‘Shu’,80,80,80); – 3.修改记录 UPDATE score set Chinese=99; –全表修改 UPDATE score SET Math=100 WHERE id=‘1’; – 4.删除记录 DELETE FROM score WHERE id=‘2’; DELETE FROM score;
– delete与truncate的区别 √ –TRUNCATE TABLE 删除表的记录:将整个表删除掉,重新创建一个新的表,属于DDL. –DELETE FROM 删除表的记录:一条一条进行删除,DELETE. INSERT INTO score VALUES(3,‘Shu’,80,80,80); DELETE FROM score; INSERT INTO score VALUES(NULL,‘Libai’,10,10,10); –不会清空AUTO_INCREMENT值 TRUNCATE TABLE score; INSERT INTO score VALUES(NULL,‘Libai’,10,10,10); –清空AUTO_INCREMENT的值
– 事务管理:只能作用在DML语句上,如果在一个事务中使用delete删除所有记录,可以找回. – 使用delete删除后可以用COMMIT和ROLLBACK找回数据,使用truncate后就找不回来了.
– delete、truncate、drop的区别 √ delete、truncate、只是删除表的记录,而drop会直接删除表.
-- 数据控制语言DCL(grant,revoke)
-- 主要为用户授予和撤销权限
– 1.创建用户:CREATE USER 用户名@ip IDENTIFIED BY 密码; create user Fox@localhost identified by ‘123456’;
– 2.给用户授权:grank 权限1,权限2,…,权限n ON 数据库名.* TO 用户名@IP; grant select,drop on mysql.* to Fox@localhost;
– 3.撤销权限:REVOKE 权限1,权限2,…,权限n ON 数据库名.* FROM 用户名@IP; revoke select on mysql.* from Fox@localhost;
– 4.查看用户的权限:SHOW GRANTS FOR 用户名@IPl show grants for Fox@localhost; – 5.删除用户: DROP USER 用户名@IP; drop user Fox@localhost; – 6.登录:mysql -u 用户名-p 密码; mysql -u root -p – 7.退出登录: exit; exit;
exam表:
-- 数据查询语言DQL(select)
create table exam(
id INT(11) PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20),
English INT,
Chinese INT,
Math int
);
INSERT INTO exam VALUES(NULL,'小花',99,99,99);
INSERT INTO exam VALUES(NULL,'小蓝',89,89,89);
– 1.全表查询: SELECT * FROM 表; SELECT FROM exam; – 2.查询部分字段: SELECT 字段,字段,字段… FROM 表; SELECT NAME,English,Math FROM exam; – 3.过滤重复字段行: SELECT [DISTINCT] |列名 FROM 表; SELECT DISTINCT Math FROM exam; SELECT DISTINCT name,Math FROM exam; – 4.查询字段起别名: SELECT 字段 AS 新字段名,字段 新字段名 FROM 表; SELECT NAME,English AS English_score FROM exam; – 5.查询指定字段 SELECT NAME,English,Chinese FROM exam WHERE NAME=‘李白’; – 6.使用表达式+、-、、/ SELECT id,NAME,English-20 AS _English FROM exam; SELECT NAME,English+Math+Chinese FROM exam; – 7.模糊查询 SELECT FROM exam WHERE NAME LIKE ‘小_’; SELECT FROM exam WHERE NAME LIKE ‘%%’; – 8.使用and,or SELECT FROM exam WHERE English > 90 AND Chinese >90; SELECT FROM exam WHERE English < 90 or Math >99; – 9.使用in,not in SELECT FROM exam WHERE id=2 OR id=3 OR id=4; SELECT FROM exam where id IN(2,3,4); SELECT FROM exam where id not IN(2,3,4); – 10.使用between…and [] SELECT FROM exam WHERE English BETWEEN 90 AND 100; – 11. is null,is not null INSERT INTO exam(id,NAME) VALUES(NULL,NULL); SELECT FROM exam WHERE NAME IS NULL; SELECT FROM exam WHERE NAME IS not NULL; – 11.排序查询 SELECT FROM exam ORDER BY Chinese ASC; SELECT FROM exam ORDER BY Chinese DESC; SELECT FROM exam ORDER BY English DESC,Chinese DESC;– 如果英语成绩相同,按照汉语成绩降序排列 SELECT * FROM exam WHERE NAME LIKE ‘小%’ ORDER BY English ASC; – 12.聚合函数 SELECT SUM(English+Math+Chinese) FROM exam; SELECT COUNT(id) FROM exam WHERE NAME IS NOT NULL; SELECT MAX(English) FROM exam; SELECT MIN(English) FROM exam; SELECT AVG(English) FROM exam ;
-- 一、分组查询
CREATE TABLE emp( empno INT PRIMARY KEY AUTO_INCREMENT, ename VARCHAR(20), job VARCHAR(20), mgr int, hiredate DATE, sal DOUBLE(7,2), commit double(5,2), deptno INT NOT NULL );
INSERT INTO emp VALUES (1002,‘白展堂’,‘clerk’,1001,‘1983-05-09’,7000.00,200.00,10), (1003,‘李大嘴’,‘clerk’,1002,‘1980-07-08’,8000.00,100.00,10), (1004,‘吕秀才’,‘clerk’,1002,‘1985-11-12’,4000.00,null,10), (1005,‘郭芙蓉’,‘clerk’,1002,‘1985-03-04’,4000.00,null,10), (2001,‘胡一菲’,‘leader’,null,‘1994-03-04’,15000.00,NULL,20), (2002,‘陈美嘉’,‘manger’,2001,‘1993-05-24’,10000.00,300.00,20), (2003,‘吕子乔’,‘clerk’,2002,‘1995-05-19’,7300.00,100.00,20), (2004,‘张伟’,‘clerk’,2002,‘1994-10-12’,8000.00,500.00,20), (2005,‘曾小贤’,‘clerk’,2002,‘1993-05-10’,9000.00,700.00,20), (3001,‘刘梅’,‘leader’,null,‘1968-08-08’,13000.00,NULL,30), (3002,‘夏冬梅’,‘manger’,3001,‘1968-09-21’,10000.00,600.00,30), (3003,‘夏雪’,‘clerk’,3002,‘1989-09-21’,8000.00,300.00,30), (3004,‘张一山’,‘clerk’,3002,‘1991-06-16’,88000.00,200.00,30);
– 1.查询每个部门的平均工资 SELECT deptno,AVG(sal) FROM emp GROUP BY deptno; – 2.查询每个职位的最高工资和最低工资 SELECT job,MAX(sal),MIN(sal) FROM emp GROUP BY job; – 3.查询每个部门每种职位的最高工资 SELECT deptno,job,MAX(sal) FROM emp GROUP BY deptno,job; – 4.查询每个部门的最高薪水,只有最高薪水大于15000的记录才被输出显示 SELECT deptno,MAX(sal)AS max_sal FROM emp GROUP BY deptno HAVING max_sal>=15000; – 5.查询每个部门的平均工资 SELECT deptno,AVG(sal) FROM emp GROUP BY deptno HAVING AVG(sal)>9000; – 6.Havaing子句与where子句的区别 (1)where是用来过滤记录的,HAVING是用来过滤分组的 (2)过滤的时机不相同,先过滤Where后过滤Having. (3)WHERE是在查询表时逐行过滤以选取满足条件的记录 (4)having是在数据查询后并且分完组后对分组进行过滤的 (5)HAVING必须跟在group BY (6)查询语句执行顺序:5select 1from 2where 3group by 4having 6order by
– 二、分页查询 – 1.从第几页开始多少页(下标从0开始) SELECT FROM emp LIMIT 0,3; – 2.每页几条第几页==需要查看第几页-1)乘以第二个参数 SELECT FROM emp LIMIT 10,5;– 每页五条第三页(3-1)5 SELECT FROM emp LIMIT 2,2; –每页2条第2页(2-1)2 – 3.查看工资最高的前十个职员信息 SELECT FROM emp ORDER BY sal DESC LIMIT 0,10;
主键约束:primary key
(默认就是唯一非空的)
外键约束: 用于在两个表之间建立关系,需要指定引用主表的哪一列。
唯一约束:unique
非空约束:not null
CREATE TABLE emp(
empno INT PRIMARY KEY AUTO_INCREMENT,--主键约束
ename VARCHAR(20),
job VARCHAR(20),
mgr int,
hiredate DATE,
sal DOUBLE(7,2),
commit double(5,2),
deptno INT NOT NULL -- 非空约束
);
-- √ 添加 唯一约束和非空约束
ALTER TABLE exam MODIFY NAME VARCHAR(21) UNIQUE NOT NULL;
– 创建主表 CREATE TABLE dept( deptno INT PRIMARY KEY, dname VARCHAR(20), loc VARCHAR(20) ); INSERT INTO dept VALUES (10,‘餐饮部’,‘上海’), (20,‘销售部’,‘浙江’), (30,‘财务部’,‘北京’), (40,‘技术部’,‘深圳’);
为从表emp加外键
ALTER TABLE emp ADD FOREIGN KEY (deptno) REFERENCES dept(deptno);
多张数据表或视图的查询叫做连接查询
– 1.笛卡尔积: SELECT FROM emp,dept; – 2.等值链接(SELECT * FROM A,B WHERE A.主键=B.外键;) SELECT FROM emp,dept WHERE dept.deptno = emp.deptno; – 3.内连接(SELECT * FROM A INNER JOIN B ON A.主键=B.外键;) SELECT FROM emp INNER JOIN dept ON dept.deptno = emp.deptno; – 4.外连接: – 4.1左外连接:(SELECT * FROM A LEFT OUTER JOIN B ON 条件;) SELECT FROM emp LEFT OUTER JOIN dept ON dept.deptno=emp.deptno; – 4.2右外连接:(SELECT * FROM A right OUTER JOIN B ON 条件;) SELECT FROM emp RIGHT OUTER JOIN dept ON dept.deptno=emp.deptno; – 5.子查询: – 5.1单行单列,工作地点在上海的员工 SELECT FROM emp WHERE deptno=(SELECT deptno FROM dept WHERE loc =‘上海’); – 5.2多行单列,工作地点不在上海的员工 SELECT FROM emp WHERE deptno in(SELECT deptno FROM dept WHERE loc <>‘上海’); – 6.自连接 SELECT e1.,e2.* FROM emp e1 inner join emp e2 ON e1.mgr =e2.empno WHERE e1.ename =‘吕子乔’
多表查询练习↓ – 1.查看每个员工的名字以及其所在部门的名字 SELECT emp.ename,dept.dname,dept.loc FROM emp,dept WHERE emp.deptno = dept.deptno; – 2.查看工作地点在北京的员工有哪些 SELECT FROM emp INNER JOIN dept ON emp.deptno = dept.deptno WHERE dept.loc =‘北京’; – 3.查看每个城市员工的平均工资 SELECT dept.loc,ifnull(avg(sal),0) FROM emp right join dept on emp.deptno =dept.deptno GROUP BY dept.loc; – 4.查看工作地点在上海的员工有哪些 SELECT emp.,dept.dname,dept.loc FROM emp LEFT OUTER JOIN dept ON emp.deptno=dept.deptno WHERE dept.loc=‘上海’; – 5.查找和曾小贤同职位的员工 SELECT FROM emp WHERE job=(SELECT job FROM emp WHERE ename=‘曾小贤’); – 6.查找薪水比整个机构平均水平高的员工 SELECT FROM emp WHERE sal>(SELECT AVG(sal) FROM emp); – 7.查询出部门中有clerk但职位不是clerk的员工的信息 SELECT * FROM emp WHERE deptno IN(SELECT DISTINCT deptno FROM emp WHERE job=‘clerk’) AND job!=‘clerk’; – 8.查看每个城市员工的平均工资’ SELECT dept.loc,AVG(sal) FROM emp INNER JOIN dept ON emp.deptno=dept.deptno GROUP BY dept.loc; – 9.查询出最低薪水高于部门20的最低薪水的部门信息 SELECT deptno,MIN(sal) AS MIN_sal FROM emp GROUP BY deptno HAVING min_sal>(SELECT MIN(sal) FROM emp WHERE deptno=20); – 10.列出所有员工的姓名及其直接上级的姓名 SELECT e1.ename,e2.ename FROM emp e1 LEFT JOIN emp e2 ON e1.mgr = e2.empno;