1.mysql建立数据库以及相关操做
建立数据库：
create database 数据库名;
eg. CREATE DATABASE mydb;
删除数据库：
drop database 数据库名;
eg. drop database mydb;
启用数据库：
use 数据库名;
eg. USE mydb;
查询当前用户下的全部数据库：
SHOW DATABASES;
查询当前链接数据库的信息：
SHOW CREATE DATABASE 数据库名;
eg. SHOW CREATE DATABASE mydb;
显示当前所链接的数据库下全部表：
SHOW TABLES;
2.mysql经常使用数据类型
int 整型
double 浮点型
double(6,2) 表示 总位数6位 小数点后2位
varchar 可变长度字符
varchar(10) 'aa' 占两个字节
数据库中 utf-8编码(出中文外一个字符一个字节,中文占3个字节)
char 不可变长度字符串类型
char(10) 10表示的是字节数'aa '占十个字节
日期类
date 日期形yyyy-MM-dd格式
time 事件形hh:mm:ss
timestamp 时间戳yyyy-MM-dd hh:mm:ss
注意时间戳在MySQL中会自动的赋值 在insert 语句中给null就能够
datetime 日期时间yyyy-MM-dd hh:mm:ss
3.mysql数据表操做
3.1建立表与删除表
建立表
CREATE TABLE users(
uid INT PRIMARY KEY AUTO_INCREMENT,
uname VARCHAR(20) NOT NULL,
upass VARCHAR(20) NOT NULL,
age INT);
删除表
drop tableusers;
3.2添加主键
(1) 开头声明主键
create table users (
username varchar(50) primary key
);
(2)结尾声明主键
create table users(
username varchar(50) ,
userpwd varchar(50),
primary key (username));
(3)声明联合主键create table users(
username varchar(50),
userpwd varchar(50),
birth datetime,
primary key (username, userpwd)
);
(4)建立表后在添加主键
create table users (
username varchar(50),
userpwd varchar(50)
);
alter table users add primary key (username, userpwd);
3.3添加外键
(1) 添加一个外键
create table users(
username varchar(50),
role_id int ,
constraint fk_users_roles foreign key (role_id) references roles(roleid));
添加外键时：constraint fk_users_roles 能够省略
简写为：foreign key (role_id) references roles(roleid)
(2)添加两个两个外键
CREATE TABLE roles_menus (
roles_id INT ,
menus_id INT,
PRIMARY KEY (roles_id, menus_id),
CONSTRAINT fk_users_roles FOREIGN KEY (roles_id) REFERENCES roles(roleid),CONSTRAINT fk_users_menus FOREIGN KEY (menus_id) REFERENCES menus(menuid)
);
(3)建立表后再添加外键
create table users(
username varchar (50) primary key,
role_id int
);
alter table users add constraint fk_users_roles foreign key (role_id) references roles(roleid);
备注：添加外键前，必须先建立被参照表
3.4其余表操做
(1)建立表后添加或删除列
alter tablestudent add scoreint;
alter table student drop score;
(2)修改表某字段类型
alter tablestudent modifyage text;
(3)修改字段名称
alter table stu changeage ag int;
(4)修改表名
renametable student to stu;
(5)查看表字段信息
desc student;
4.mysql基本操做CRUD
CREATE TABLE student(
sid INT PRIMARY KEY AUTO_INCREMENT,
sname VARCHAR(30),
sage INT,
gender VARCHAR(10));
4.1插入记录
(1)插入单条记录
INSERT INTO student(sid,sname,sage,gender) VALUES(8,"金凤",20,"女");
INSERT INTO student VALUES(10,"无名",21,"男");
(2)插入多条记录
INSERT INTO student(sid,sname,sage,gender) VALUES
(1,"张三",20,"男"),
(2,"李四",18,"男"),
(3,"王五",22,"男"),
(4,"赵六",21,"男");
或
INSERT INTO student VALUES
(1,"张三",20,"男"),
(2,"李四",18,"男");
(3)插入指定字段
INSERT INTO student(sid,sname) VALUES(5,"沈佳1");
INSERT INTO student(sid,sname) VALUES(6,"沈佳2"),(7,"沈佳2");
4.2删除记录
DELETE FROM student WHERE sid=1 OR sid=2;
DELETE FROM student;//删除全部数据，表结构还在
TRUNCATE TABLE student2; //删除整个表(包括表结构)，再建立表结构
4.3修改记录
update student set age=25;
update student set age=15，name=”哈哈” where id=1 ;
4.4查询记录
select 列名1，列名2，…….，列名nfrom 表名where条件group by列名having条件order by列名limit 偏移量,记录条数；
说明：
1.where condition //条件,分组前的过滤条件，其实就是对整个表进行第一次筛选；有一张虚拟表1
2.group by group_columns //对结果进行分组，对第一次结果虚拟表1进行分组；获得一个虚拟表2
3.having condition //分组后的行条件，对虚拟表2进行条件筛选，获得一个虚拟表3
4.order by sorting_colunms //对结果排序 对虚拟表3再次进行排序；
5.limit offset_start,row_count //结果限定 又一次筛选查询 ，经常使用来分页
5.mysql查询语句
5.1基础查询
(1)查询全部字段
SELECT * FROM student;
SELECT sid,sname,sage,gender FROM student;
(2)查询指定字段
SELECT sid,sname FROM student;
使用别名：as能够省略
SELECT sid 学号,sname学生姓名FROM student;
SELECT sid AS 学号,sname AS学生姓名FROM student;
5.2条件查询
(1)运算符
=、!=、<>(不等于)、、>=；
(2)关键字
BETWEEN…AND；NOT BETWEEN…AND
SELECT * FROM student WHERE sage BETWEEN 20 AND 22; [20,22]中全部记录
SELECT * FROM student WHERE sage NOT BETWEEN 20 AND 22; [20,22]之外全部记录
IN(set)；NOT IN(set)
SELECT * FROM student WHERE sage IN(20,23,222); 取交集
等价于SELECT * FROM student WHERE sage=20 OR sage=23 OR sage=222;
IS NULL；IS NOT NULL
SELECT * FROM student WHERE gender IS NULL;
取出gender为null(空)的字段--这里null为：未初始化，未赋值
区别：SELECT * FROM student WHERE gender=NULL;
取出gender值为null的记录，已初始化只是赋值为null
SELECT * FROM student WHERE gender IS NOT NULL;
AND；OR；NOT
SELECT * FROM student WHERE NOT sname='张三';
5.3模糊查询
关键字
like 模式匹配
not like 去模式匹配相反的结果
通配符
_ 任意一个字符 至关于占位符，一个_占一位，该位的内容能够任意
% 任意0~n个字符 至关于Java中*，表示任意字符且长短也任意
SELECT * FROM student WHERE sage LIKE '_3'; 取出年龄为2位数，且结尾是3的记录
SELECT * FROM student WHERE sage LIKE '%3'; 取出年龄的位数任意，结尾是3的记录
SELECT * FROM student WHERE sname LIKE '%张%';取出名字中含有”张”的记录
SELECT * FROM student WHERE sname NOT LIKE '%张%';
备注：不管字段是何类型，模式匹配时，匹配内容必须放在引号中。
错误语句：SELECT * FROM student WHERE sage LIKE _3;
缘由：觉得sage是整数，没将匹配内容放在引号中
5.4排序
升序
order by 列名asc(默认)按主键升序排列
降序
order by 列名desc
SELECT * FROM student ORDER BY sage DESC; 按sage降序排列
SELECT * FROM student ORDER BY sage ASC; 按sage升序排列
SELECT * FROM student WHERE sage>20 ORDER BY sage DESC; 先筛选再排序
(3)字段控制
distinct：去除重复
SELECT DISTINCT * FROM student; 去除每一个字段都同样的记录[取出全部不重复的记录]
SELECT DISTINCT sname FROM student; 去除名字相同的记录[取出全部不重复的名字]
SELECT DISTINCT sname,sage FROM student; 去除名字和年龄均相同的记录
备注：distinct只能放在select和from之间，修饰字段
distinct前不能有其余字段eg. SELECT sid,DISTINCT sname,sage FROM student;
5.5聚合函数
特色：聚合函数是用来作纵向运算的函数：
COUNT()：统计指定列不为NULL的记录行数；
MAX()：计算指定列的最大值；
MIN()：计算指定列的最小值；
SUM()：计算指定列的数值和(非数值计算结果为0)；
AVG()：计算指定列的平均值(非数值计算结果为0)；
SELECT COUNT(sid) 学生人数,MAX(sage)学生最大年龄,MIN(sage)学生最小年龄,AVG(sage)学平生均年龄FROM student;
备注：
a.数值类型和非数值类型没法作加法运算
b.数值类型和空数值作加法运算：把NULL转换成数值0的函数IFNULL，例如IFNULL(列名,0)
c.直接拿某个字段值与null进行运算，结果均为null
d.聚合函数sum,max,min,avg等等，内部对null值作了处理，结果不会直接是null
数值和非数值运算
进行加减乘除运算时，非数值默认为0(非数值必须是初始化的，即已赋值的)
SELECT sname,sage,sname+sage FROM student;
SELECT sname,sage,sname*sage FROM student;
NULL处理，IFNULL(列名,0)
条件：sid=10学生年龄为初始化，为null
SELECT sid,sage,sid+sage FROM student WHERE sid=10; 结果为null
SELECT sid,sage,sid+IFNULL(sage,0) FROM student WHERE sid=10; 结果为sid的值10
5.6分组查询
group by 字段[having条件]
根据性别进行分组，并计算每组中的人数
SELECT gender,COUNT(*) FROM student GROUP BY gender;
根据性别进行分组，再取出性别为女的组，并计算该组人数
SELECT gender,COUNT(*) FROM student GROUP BY gender HAVING gender='女';
同上(备注：某些记录的gender字段没有初始化，因此为null，即按性别分有3组)
SELECT gender,COUNT(*) FROM student GROUP BY gender HAVING gender IN ('女','男');
having与where的区别:
1.having是在分组后对数据进行过滤，后面能够直接使用聚合函数
2.having紧跟group by，它们是一个总体
3.where是在分组前对数据进行过滤，不能够直接使用聚合函数
having和where +聚合函数分析
a.having+聚合函数
SELECT gender,COUNT(*),SUM(sage) FROM student GROUP BY gender HAVING SUM(sage)>43;
SELECT gender,COUNT(*),AVG(sage) FROM student GROUP BY gender HAVING AVG(sage)>22;
默认根据分组后的结果，计算每组的SUM，再与43比较
默认根据分组后的结果，计算每组的AVG，再与22比较
b.where直接加聚合函数
SELECT * FROM student WHERE sage>AVG(sage);
报错：由于mysql引擎不知道计算什么条件下的AVG，改成以下语句则正常执行
SELECT *,AVG(sage) FROM student WHERE sage>(SELECT AVG(sage) FROM student);
5.7分页查询
select * from 表名limit参数1,参数2
参数1：offset，偏移量 可省略，默认offset=0
参数2：查询返回的最大记录数 不可省略
偏移量理解：offset=n 表示前面n条数据不用管，从n+1条开始查询
若总记录数小于5，则有几条就返回几条；不然返回5条
SELECT * FROM student LIMIT 5; 默认偏移量为0，则从第一条数据开始，得到5条记录
SELECT * FROM student LIMIT 0,5; 效果同上
若去除前7条记录后，剩下总记录数小于5，则有几条就返回几条；不然返回5条
SELECT * FROM student LIMIT 7,5; 偏移量为7，则从第8条数据开始，获取5条记录
6.mysql多表查询
为何要拆表：去除冗余数据(注意区别于分表)
表与表之间的关系：一对一，一对多(多对一)，多对多
6.1 union合并结果集
UNION:去除重复数据。例如select * from t1 UNION select * from t2
UNION ALL:不去除重复纪录，所有保留。例如select * from t1 UNION ALL select * from t2
重复：指记录的每一个字段彻底同样，若主键同样，其余字段不同，是不会去除的
注意：union合并的两个表或报表的列数，列类型必须相同，列名能够不一样
CREATE TABLE t1(
tid INT PRIMARY KEY,
tname VARCHAR(20));
CREATE TABLE t2(
tid INT PRIMARY KEY,
tn VARCHAR(20));
报表与基本表合并
SELECT * FROM t1 UNION SELECT sid,sname FROM student;
两个彻底相同表合并
select * from t1 UNION select * from t2
union合并：至关因而将分表合并在一块儿，这两个表的结构彻底同样，只是列名可能有差别。
union是将表t2一条条记录insert进t1表，不会再左侧或右侧产生新的字段。
6.2普通多表查询
普通多表查询至关于下面的内链接，
区别：内链接限定条件的关键词是on，普通多表查询使用where
创员工表emp和部门表dept
CREATE TABLE emp(
empno INT PRIMARY KEY,
ename VARCHAR(20),
job VARCHAR(20),
sal DOUBLE(6,2),
deptno INT);
CREATE TABLE dept(
deptno INT PRIMARY KEY,
dname VARCHAR(20),
loc VARCHAR(20));
(1)直接合并两个表
由于没有给限定条件，因此会产生笛卡尔积
公式：合并后的总记录数=emp表记录数*dept表记录数
SELECT * FROM emp,dept; 等价于内链接：SELECT * FROM emp JOIN dept;
SELECT e.empno,e.ename,e.job,e.sal,d.dname,d.loc FROM emp e,dept d;
(2)给限定条件合并两个表
SELECT * FROM emp e,dept d WHERE e.deptno=d.deptno;
等价于内链接：SELECT * FROM emp e JOIN dept d ON e.deptno=d.deptno;
6.3链接查询
(1)内链接inner
语法：select 列名from表1 inner join表2 on条件
inner能够省略，只有join关键字，mysql默认是内链接
能够没有on条件
SELECT * FROM emp INNER JOIN dept;
SELECT * FROM emp JOIN dept;
SELECT * FROM emp e JOIN dept d ON e.deptno=d.deptno;
(2)外链接outer
语法：select 列名from表1 left/right outer join表2 on条件
outer可省略
必须有on条件
分为左链接和右链接，二者刚好相反
a.左链接left
左链接是先查询出左表，而后查询右表，右表中知足条件的显示出来，不知足的显示NULL
(先查询t1表，再根据后面条件查询t2表中知足条件的数据)
SELECT * FROM emp e LEFT JOIN dept d ON e.deptno=d.deptno;(outer能够省略)
b.右链接right
右链接就是先把右表中全部记录都查询出来，而后左表知足条件的显示，不知足显示NULL
SELECT * FROM emp e RIGHT JOIN dept d ON e.deptno=d.deptno;
(3)天然链接natural
自动去除无用重复信息
语法：select 列from表1 natural join表2;
内链接和天然链接比较
a.内链接 SELECT * FROM emp INNER JOIN dept;
b.天然链接SELECT * FROM emp NATURAL JOIN dept;
天然链接：1.默认根据两个表中的相同字段去除重复，emp和dept中都有deptno，
则根据e.deptno=d.deptno来去除重复
2.若两个表没有相同字段，则会直接合并产生笛卡尔积
natural能够修饰外链接，此时能够不须要on条件，由于如有相同字段，natural默认会根据该字段进行限定。如果没有相同字段，不会像使用外链接时，不加on时(SELECT * FROM emp LEFT JOIN dept;)，直接报错，而是产生笛卡尔积。【或者说left和不存才是同样的，由于只有natural时，也是一样的效果，而且有left时，也不能加on来限制】
SELECT * FROM emp NATURAL LEFT JOIN dept;
7.mysql子查询
定义：子查询就是嵌套查询，即select中包含select,若是一条语句中存在两个或者两个以上select,那么就是子查询了
(1)子查询出现的位置
where后，做为条件查询的中条件的一部分
from后，做为表使用
(2)当子查询出如今where后做为条件时，可使用any,all两个关键字
SELECT * FROM emp WHERE deptno IN(SELECT deptno FROM dept WHERE dname='销售部');
等价SELECT * FROM emp WHERE deptno=(SELECT deptno FROM dept WHERE dname='销售部');
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
