Mysql基础知识点

Mysql数据库知识点

1.管理数据库语句：

使用数据库：

use test;

添加数据库：

-- create database 数据库名;
create database test;

修改数据库：

-- alter database 数据库名;
alter database test;

删除数据库：

-- drop database 数据库名;
drop database test;

查看所有数据库：

show databases;

2.管理表语句：

添加数据表：

/*
create table 表名(
　　列名 数据类型 数据约束,
　　列名 数据类型 数据约束
);
*/
create table student(
　　name varchar(20) not null,
　　age int(4)
);

修改数据表：

-- alter table 表名;
-- 在表中增加新字段
alter table student add colunm name varchar(20);

-- 删除表中的字段
alter table student drop name;

-- 修改表中字段的类型
alter table student modify name varchar(10);

删除数据表：

-- drop table 表名;
drop table student;

查看所有表：

show tables;

3.管理数据语句：

插入数据：

-- insert into 表名 (列1,列2...)  values (值1,值2...);
insert into student (name,age) values ('张三',20);

更新数据：

-- update 表名 set 列=修改值 where 条件;
update student set  name='李四' where name='张三';

删除数据：

-- delete from 表名 where 条件;
delete from student where name='李四';

查询数据：

-- selete *  from 表名 where 条件;
select * from student where name='张三';

4.各种查询语句：

查询时指定别名：

-- selete 字段名 AS '别名' from student;
select id AS '编号',name AS '姓名' from student;

查询时合并列：

需求:查询每个学生的总分

-- selete (字段名1+字段名2) AS '总成绩' from student;
select (servlet+mysql) AS '总成绩' from student;

注意:合并列的字段必须是数值类型的字段

查询时去除重复(distinct)：

需求:查询有哪些地区的学生

select DISTINCT address from student;

另一种语法

select DISTINCT(address) from student;

条件查询，逻辑条件: and or

需求:查询学生的id为1，且姓名为张三的学生

-- 交集
select * from student where id=1 and name='张三';

需求:查询学生的id为2，或姓名为张三的学生

-- 并集
select * from student where id=2 or name='张三';

比较条件: > < >= <= = <> (between and–在…之间 包前包后)

需求:查询servlet分数大于80分的学生

select * from student where servlet>80;

需求:查询mysql分数小于或等于85分的学生

select * from student where mysql<=85;

需求:查询servlet分数大于或等于80分，且小于或等于85分的学生

select * from student where servlet>=80 AND servlet<=85;

代替上面语句的语法

select * from student where servlet BETWEEN 80 AND 85;

需求:查询年龄不等于30的学生

select * from student where age<>30;

判空条件: is null, is not null, =’’, <>’’

Null:表示没有数据

空字符:表示有数据

需求:查询没有性别数据的学生(数据‘男’或‘女’)

select * from student where gender IS NULL OR gender='';

需求:查询有性别数据的学生

select * from student where gender IS NOT NULL AND gender<>'';

模糊条件: like

模糊替代符号:

%:替代任意个字符

_:替代一个字符

需求:查询姓“李”的学生

SELECT * FROM student WHERE NAME LIKE '李%';

需求:查询姓名中包含‘四’字的学生

SELECT * FROM student WHERE NAME LIKE '%四%';

需求:查询姓‘李’，全名只有两个字的学生

SELECT * FROM student WHERE NAME LIKE '李_';

聚合函数查询(用于统计结果)

Max()取最大值 min()取最小值 avg()取平均值 count()统计标的记录数量 sum()求和

需求:查询servlet的最高分

SELECT MAX(servlet) FROM student;

需求:查询mysql的最低分

SELECT MIN(mysql) FROM student;

需求:查询servlet的平均分

SELECT AVG(servlet) FROM student;

需求:查询当前有几个学生

SELECT COUNT(*) FROM student;

需求:查询servlet成绩的总和

SELECT SUM(servlet) from student;

分页查询(limit)

Limit起始行数，查询的行数

起始行数从0开始

需求:查询第1,2条数据

SELECT * FROM student LIMIT 0,2;

分页查询需知道:当前页码，每页显示条数

结论分页查询当前页数据:select * from student limit (当前页码-1)*每页显示条数，每页显示条数;

需求:学生共20条数据，每页显示5条，共4页

查询第3页(第11到15条)的学生数据:select * from student limit 10,5;

查询后排序(order by)

DESC:降序。数值从大到小，字母z-a

ASC:升序。数值从小到大，字母a-z

默认情况下，按照插入的顺序排序

需求:按照id的升序排序

select * from student order by id asc;

需求:按照servlet成绩降序排序

select * from student order by servlet desc;

多个排序条件的情况:先按照前面的条件排序，当出现重复记录，再按照后面的条件排序

需求:按照age升序，再按照servlet成绩升序排序

select * from student order by age asc,servlet asc;

分组查询(group by)

需求:查询每个地区有多少人

SELECT address,COUNT(*) FROM student GROUP BY address;

需求:统计男女的人数

注意:where条件必须放在group by 分组之前

SELECT gender,COUNT(*) FROM student WHERE gender IS NOT NULL AND gender<>'' GROUP BY gender;

分组后筛选(having)

需求:查询哪些地区的人数是大于2个的地区

查询哪些地区多少人 2)筛选人数大于2的地区

注意:having使用在group by分组之后，对分组后的条件进行筛选

SELECT address,COUNT(*) FROM student GROUP BY address HAVING COUNT(*)>2;

5.数据约束（给表添加数据约束，从而约束用户操作表数据的行为）

1）默认值约束(default)

create table test(
　　name varchar(20),
　　gender varchar(20) default ‘男’
);

需求:当不插入gender的时候，分配一个‘男’的默认值

注意:1)当没有插入gender字段的时候，分配一个默认值

2）非空约束（not null）

create table test(
　　name varchar(20) not null,
　　gender varchar(20)
);

需求；name字段一定要有值(不能不插入数据，不能是null)，这是给name添加非空约束

1)非空约束，不能不插入值

Insert into test(gender) values(‘男’);

2)非空约束，不能插入null

Insert into test(name,gender) values(null,’男’);

3)唯一约束(unique)

create table test(
　　Id int unique,
　　name varchar(20)
);

需求:id的值不能出现重复。这时就要给id添加一个唯一约束

1)不能插入重复的值

2)唯一约束，可以插入多个null。所以唯一约束不能约束null

Insert into test(id,name) values(1,’张三’);

4)主键约束(primary key)(唯一+非空)

注意；

1)通常情况下，我们会给每张表都设置一个主键字段，用来标记记录的唯一性

2)但是不建议把业务含义字段作为主键，因为随着业务的变化，业务字段可能出现重复

3)建议给每张表都独立添加一个叫id的字段，把这个id字段设置成主键，用来作为记录的唯一性

create table test(
　　Id int primary key,
　　name varchar(20)
);

1)唯一性

2)非空性

5)自增长约束(auto_increment)

create table test(
　　Id int primary key auto_increment,
　　name varchar(20)
);

自增长约束:初始值为0，每次递增1

使用truncate table 删除数据的时候，可以把自增长的初始值置为0

6.多表查询

1.交叉连接查询(笛卡尔乘积:4*3=12，产生笛卡尔积的原因是没有足够的连接条件)(一般不用)

需求:查询员工及其部门名称

SELECT employee.name,dept.name FROM employee,dept;

2.内连接查询(使用最多)

多表查询的步骤:

1)确定查询哪些表

2)确定查询哪些字段

3)确定连接条件(规则:条件=表数量-1)

SELECT employee.name,dept.name
FROM employee,dept
WHERE employee.deptId=dept.id;

另一种语法

SELECT e.name,d.name
FROM employee e
INNER JOIN dept d
ON e.deptId=d.id;

3.左外连接查询(左表数据全部显示，如果右边不满足，则显示null)

需求:查询部门及其部门的员工

SELECT d.name,e.name
FROM dept d
LEFT OUTER JOIN employee e
ON d.id=e.deptId;

4.右外连接查询(右表数据全部显示，如果左边不满足，则显示null)

SELECT d.name,e.name
FROM employee e
RIGHT OUTER JOIN dept d
ON e.deptId=d.id;