MySQL学习笔记

MySQL

DDL-数据库操作

查询

show databases

查看当前数据库

SELECT DATABASE();

创建

CREATE DATABASE [IF NOT EXISTS] 数据库名 [DEFAULT CHARSET 字符集］[COLLATE 排序规则];

删除

DROP DATABASE[IF EXISTS] 数据库名;
使用
USE 数据库名;

DDL-表操作

DML-增删改

添加数据

1.指定字段添加数据

INSERT INTO 表名(字段1, 字段2) VALUES(值1，值2);

2.全部字段添加数据

3.批量添加数据

修改数据

删除数据

DQL-查询

1.查询指定字段

SELECT stuId,stuName,sex FROM t_student;

2.所有字段

SELECT * FROM t_student;

3.查字段起别名

SELECT stuId AS '学号' ,stuName AS '姓名' ,sex AS '性别' FROM t_student;

4.查字段不重复

SELECT DISTINCT address AS '工作地址' FROM t_student;

eg

–1

elect * from emp where age = 18;

–2

select * from emp where age < 20;

–3

select * from emp where age < =20;

–4

select * from emp where idcard is null;

–5

select * from emp where idcard is not null;

–6

select * from emp where age != 20;

select * from emp where age <> 20;

–7

select * from emp where age >= 15 && age <= 20;

select * from emp where age >= 15 and age <= 20;

select * from emp where age between 15 and 20;

–8

select * from emp where gender = ‘女’ and age <15;

–9

select * from emp where age = 18 or age = 20 or age -40;

select * from emp where age in(18,20,40)

–10 查询名字为两个字的员工信息

select * from emp where like '--'；

–11

select * from emp where idcard like ‘%x’

select * from emp where idcard like ‘-----------x’

聚合函数

count max min avg sum

1.统计员工数量

select count * from emp;

select count(id) from emp;

null 值不参与计算

2.平均值

select avg(age) from emp;

3.最大值

select max(age) from emp;

4.最小值

select max(age) from emp;

5.统计河北省当前员工的年龄之和

select * sum(age) from emp where address = ‘河北省’

分组查询

select 字段名 from 表名 [ where 条件] group by 分组字段名 [having 分组过滤后条件]

1.

select gender, count(*) from emp group by gender;

2.

select gender, avg(age) from emp group by gender;

3.

select address ,count(*) address_count from emp where age <45 group by address;

排序查询

select 字段列表 from 表名 order by 字段1 排序方式1,字段2 排序方式2;

1.升序排序

select * from emp order by age;

select * from emp order by age asc;

2.降序排序

select * from emp order by age desc;

3.根据年龄对公司员工进行升序排序,年龄相同,载按照入职时间降序排序

select * from emp order by age asc , etrydate desc;

3.分页查询

select * from limit 起始索引 查询记录数

起始索引 = (页码-1)*每页显示记录数

查询第一页 limit 10;

1.查询第一页员工信息 每页10条记录

select * from emp limt 0 10;

2.查询第二页 每页10条记录

select * from emp limit 10,10;

案例

1.查询年龄为20,21,22,23的女性员工信息

select * from emp where gender = ‘女’ and age in(20,21,22,23);

2.查询男 年龄在20-40(含) 姓名3个字地员工

select * from emp where gender = ‘男’ and age betwen 20 and 40 and name like’___’;

3.select geder, count(*) from emp where age<60 group by gender;

4.select name,age from emp where age <=35 order by age asc, entrydate desc;

5.select name ,age from emp where gender = ‘男’ and age between(20,40) order by age asc,etrydate desc limit 5

存储过程

1.创建

create procedure p1 ()

begin

select count(*)from student

end;

调用

call p1()

查看

select * from

2.show create procedure p1；

删除

drop procedure if exists p1();

if

create procedure p3

begin

declare score int default 58；

declare result varchar(10);

if score>= 85 then

set result := '优秀;'

elseif score >=60 then

set result :='及格';

else

set result := '不及格';

end if;

select result;

end;

call p3();

自定义函数

创建自定义函数语句

多条语句,写在BEGIN…AND中,函数体必须包含一个return值语句,值为函数的返回值3.

修改默认结束符

3.调用自定义函数

select 自定义函数名(参数)

4.函数体

(1)常量

(2)局部变量 declare 变量名 数据类型 [default 默认值]

变量赋值

set 变量名 = 表达式

输出变量的值

select 局部变量名

示例9.1.2

case

case 条件表达式

when 条件表达式结果1 then 语句1

when 条件表达式结果2 then 语句2

when 条件表达式结果n then 语句n

else 语句n+1

end case