-- 使用SQL语句创建数据库
-- 如果数据库已经存在 则报错[Err] 1007 可以在语句中加入判断
-- if no exists 如果数据库不存在 再去创建 如果存在则不再创建
create database if not exists qbcm
default character set utf8
default collate utf8_general_ci;
-- 最简单的数据库创建方式采用的是数据库默认的字符集
-- eg:
-- create database student;
-- 修改数据库 alter DATABASE 其中default character set
utf8设置数据库的字符集编码默认为utf8,collate utf8_general_ci:
-- 数据库的校验规则,ci是case insensitive的缩写,意思是大小写不敏感;相对的是cs,即case
sensitive,大小写敏感;还有一种是utf8_bin,是将字符串中的每一个字符用二进制数据存储,区分大小写。
-- 整理如下:
-- utf8_bin:区分大小写;
-- utf8_general_cs:大小写敏感;
-- utf8_general_ci:大小写不敏感。
alter database qbcm
default character set utf8 collate utf8_general_ci;
-- 删除数据库
drop database qbcm;
-- 创建表
use qbcm;
create table dapartmemt(
dapId int ,
dapName varchar(20),
dapNum int,
primary key(dapId)
)engine=innodb;
use qbcm;
create table employee(
empId int primary key,
empName varchar(20),
age int,
address varchar(255),
constraint unique_name unique(empName)
)engine=innodb;
alter table employee add salary float;
insert into dapartmemt values(1001,'nin',11);
select * from dapartmemt;
update dapartmemt set dapId=1002 where dapId=1001;
use qbcm;
create table dapartmemt(
dapId int ,
dapName varchar(20),
dapNum int,
primary key(dapId)
)engine=innodb;
insert into dapartmemt(dapId,dapName)
values(1004,'osidjf'),(1005,'ois');
alter table employee add salary float;
alter table employee add dapId int;
alter table employee add time datetime;
-- 修改表
-- 1.修改表名
alter table employee rename to emp; -- 表名修改为emp
alter table dapartmemt rename to dapartment;
-- 2.修改列 modify column 修改 列的位置,类型
alter table employee modify column salary double after age;
-- 3.修改列名
alter table employee change column time birthDay date;
-- 4.删除列
alter table employee drop column id; -- 删除 id列
-- 5.添加外键约束
alter table employee add constraint unique_dapId unique(dapId);
-- 6.删除外键约束
alter table employee index unique_name;-- 删除唯一索引港式 删除唯一索引
alter table employee drop foreign key unique_name;
-- 添加数据
-- 1.insert 语句
insert into dapartmemt(dapId,dapName)
values(1004,'osidjf'),(1005,'ois');
insert into employee(empId,empName,dapId)
values(1001,'lisi',1004),(1002,'zhangsan',1005);
-- 2. replace语句 先判断是否存在,存在就替换,不存在就添加
replace into dapartment(dapId,dapName)values(1004,'iiiii');
-- ----------修改数据---------
update employee set empName='' dapId='' where empName="";
-- 查询
-- 别名
select empId as 'yuangong',empName as 'yuangongxingm' from
employee;
select e.empId 'id',e.empName 'name' from emp e;-- 表也可以设置别名
-- 使用where条件查询特定行的数据
-- 1.使用比较运算符
,<=,>=,=,!=
-- 2.逻辑运算符 and && or ||
select * from employee age<20
&& salary
<500;
select * from employee age<20 and salary
<500;
-- 3.like 关键字 模糊匹配
-- %通配符 表示任意字符 可以表示多位
select * from employee where empName like '刘%';-- 刘字开头的
select * from employee where empName like '%刘';-- 刘字结尾的
select * from employee where empName like '%刘%';-- 中间带刘的
-- _ 通配符 表示一位字符
select * from employee where empName like '_刘';--
开头带刘字的两位的字符!
-- between A and B 相当于
A<=x<=B
select * from employee where salary between 2000 and 4000;
select * from employee where salary >= 2000 and
salary
<=4000;
-- 5.in 多值条件 集合条件
select * from employee where salary = 1000 or salary = 2000
or
salary = 4000;
select * from employee where salary in(1000,2000,4000);
-- 6.limit 子句 有两个参数,参数1:表示跳过参数1条开始,参数2:表示你所要查询的条数
select * from employee limit 0,5;
-- 正则表达式 一个字符串 可以作为查询条件
-- select * from employee where regexp "正则表达式";
-- 查询姓名以刘字开头的员工信息
select * from employee where regexp "^刘";
-- MySQL 聚合函数
-- MAX()求最大值
-- MIN()求最小值
-- AVG()求平均数
-- SUM()求总和
-- COUNT() 返回满足条件的行数
select max(salary) '最高工资' from employee;
select min(salary) '最低工资' from employee;
select count(*) '员工总数' from employee;
select man(salary),avg(salary) from employee;
-- 分组 group by
-- 先分组depId分组,再计算平均工资
select avg(salary),depId from employee group by depId;
-- 查询部门为2001并且工资大于平均工资的人
select * from where depId=2001 and sqlary > (select
avg(salary)
where depId=2001);
-- having 子句 用来筛选分组
select avg(salary),depId from employee group by depId
having
depId != 2001;
-- 数据排序 order by asc表示升序,默认也是升序,可以不写 desc表示降序
select * from employee order by age asc;
-- MySQL 函数
-- 1.字符串函数
-- 截取字符串
select left(address,3) from employee; -- 截取左边三位
select substring(address,4,3) from employee;--
第四位开始截取包括第四位,3表示
select reverse(address) from employee;-- 倒叙排序
-- 向下取整
select floor(2.8);
-- 向上取整
select ceiling(2.8);
-- 2.数字函数
-- 3.日期函数
-- 子查询
-- 1. 使用in和not in 语句 子查询结果集多余1条 可以用in或not in
-- 查询年龄等于 部门为2001员工年龄的所有员工
select * from employee where age in (select age from emp
where
dapId = 2001);
-- 3.修改过的比较运算符
-- 年龄大于 2001 这个部门所有员工年龄的 员工信息
select * from employee where age > all(select age
from employee
where dapId=2001);
select * from employee where age > (select max(age)
from
employee where dapId =2001);
--只要年龄大于子查询结果集中任意一个即可
use qbcm;
select * from employee where age > any(select age
from employee
where dapId=2001);
-- 联接
-- 例如:查询所有员工信息,以及其部门名称
-- 1.内联接
select e.empId,e.empName,d.dapName from employee e inner join
dapartment d on e.dapId = d.dapId;
-- 2.左外联接
left join
on
-- 3.右外联接
right join
on
-- 5.交叉联接 笛卡尔积
select e.empId,e.empName,d.dapName from employee e cross join
dapartment d on e.dapId = d.dapId;