数据库(mysql)
命令
查看所有数据库
show databases;//查看所有数据库
mysql> show databases;//查看所有数据库
+--------------------+
| Database |//数据库内容
+--------------------+
| information_schema |
| mysql |
| test |
+--------------------+
创建数据库
create database+库名;//建立数据库
mysql> create database users;//创建库users
Query OK, 1 row affected (0.00 sec)
mysql> show databases;//查看所有库,如下
+--------------------+
| Database |
+--------------------+
| information_schema |
| j135 |
| mysql |
| test |
| users |
删除数据库
DROP DATABASE db_name//删除数据库dbname,如果存在dbname这个库
使用数据库
use database+库名;//使用数据库
mysql> use users;//使用库users
Database changed
创建表
creat table+表名;//建立表
mysql> create table userInfo(//创建userInfo表
-> id int)
-> ;
Query OK, 0 rows affected (0.21 sec)
查看表中的内容
show tables;//查看数据库中内容
mysql> show tables;//查看users数据库下标名称
+-----------------+
| Tables_in_users |
+-----------------+
| userinfo |
+-----------------+
1 row in set (0.00 sec)
表的增、删、改功能
alter table stu_info change age stu_age int; //更改列age名为stu_age
alter table stu_info drop stu_age; //删除stu_age列
alter table stu_info add stu_age int; //增加列stu_name
创建索引
create index+索引名 on+表名(表中的列);
create index ind on users(u_id);
删除索引
drop index 索引名 on 表名;
drop index ind on users;
定义约束(Constraint)条件
常用的约束条件有6种:
主键(Primary Key)约束--一个表中只能有一个主键
外键(Foreign Key)约束
唯一性(Unique)约束
默认值(Default)约束
alter table stu_info1 change stu_phone stu_phone VARCHAR(11) default'hhh';
非空(Not NULL)约束
检查(Check)约束。
设置自增字段,"auto_increment"此方法只对整型有效
create table stu_info1(
stu_id int primary key auto_increment,//primary key一个表中只能有一个主键,
在int类型中才能使用auto_increment(自增)
stu_name varchar(20),
stu_age int default'hhh',
stu_phone varchar(20) UNIQUE,//unique表示唯一的
stu_type int check(stu_type=0 or stu_type=1)
)
让主键自动增长
alter table 表名 auto_increment=增长起始值
alter table manager auto_increment=10000
查看表的详细信息
show create table+表名;
索引使用:
create table lovoclass(
lov_id int primary key auto_increment,
lov_className varchar(10) unique,
lov_type int check(lov_type=0 or lov_type=1)
)
多
create table student(
stu_id int PRIMARY KEY auto_increment,
stu_name varchar(20) not null,
stu_age int not null,
stu_address varchar(30) default "人民南路",//创建默认地址为"人民南路"
cla_id int,
CONSTRAINT FK_lovoclass_claId foreign key(cla_id) REFERENCES lovoclass(lov_id)//将lovoclass索引到student表中
)
数据的增、删、改
删:delete from+表名 where+限制条件(一般使用主键)
delete from users where u_id=10000//将uid为10000的行从users表中删除
增:
insert into 表名(列1,列2,····列你) values(数据1,数据2,···数据n)
insert into users(uid,uname,upassword,utype) values(10003,'lisi','wang',1)
如果只插入单独的一条数据,那么使用value
改:
update 表名 set 列名='语句' where+限制条件(一般使用主键)
update users set u_name='wangwu' where uid=10003//将主键为10003的name内容改为wangwu
update users set utype=utype+2 where u_id<10001//将uid小于10001的数据对应的utype都加2
查询操作方法
投影
方法
select 列名 from 表名
select u_id form users
select * from users//全部查找,速度慢。尽量不使用“*”
选择
方法
between A and B //查询int类型的数据在A~B之间
select * from 表名 where 列名 between A and B
select * from users where u_id between 10000 and 10001
in(A,B) //只查询包含A,B的信息
select*from 表名 where 列名 in(A,B)
select*from users where uid in(1000,10001)//只查询uid为10000,10001的数据
not in(A,B)//查询不包含A、B的数据信息
select*from 表名 where 列名 not in(A,B)
select*from users where uid in(1000,10001)//不查询uid为10000,10001的数据
like模糊查找
like "字符%" //查找包含字符的数据
select*from 表名 where 列名 like "字符%"
select * from users where u_name like "王%" //查询包含王性的人员信息
is null和is not null
判断某个数据是否为空
排序
order by
order by 默认为升序排列,如果要将序排列,需要在最后加desc
select*from 表名 order by 列名
select *from users order by u_name desc//将姓名按将序排列
检索长度
使用limit语句
select*from+表名 limit [start],length//检索表中的数据,从start行开始检索,检索3行长度数据。
select*from users limit 2,2//检索users表中的从第3行开始,检索2行数据
添加外键
alter table 表名 add CONSTRAINT FKclassificationtId foreign key(tid) REFERENCES teacher(tid)
删除外键
alter table 表名 drop FOREIGN key 外键名
alter table teacher drop FOREIGN key FKteachersId
聚合函数
运行顺序:
from-->where-->group by-->having-->select--->order by
count
计算表的行数,如果使用列名,则计算本列中不含null值的数目
select count(列名) from 表名
select count(id) from emp//计算emp表中id列的数目
AVG
计算列中的平均数,对int类型有效
select avg(列名) from 表名
select avg(sal) from emp//计算emp表中sal的平均数
SUM
求列的和,对int类型有效
select sum(列名) from 表名
select sum(sal) from emp//计算emp表中sal的和
max
求列的最大值,对int类型有效
select max(列名) from 表名
select max(sal) from emp//计算emp表中sal的最大值
*min
求列的最小值,对int类型有效
select min(列名) from 表名
select min(sal) from emp//计算emp表中sal的最小值
分组
group by
select from 表名 group by 列名//以列名进行分组
SELECT min(sal),ename,job from emp group by type//将emp表以type类型进行分组,并显示最小sal,ename、job
select count(i.deptNo),i.deptNo from emp as i GROUP BY i.deptNo having count(i.deptNo)>4//将emp表中部门人数大于4的列出来
select count(*),job,deptNo from emp group by deptNo,job having count(*)>1 order by count(*)//将emp表以部门和job分组,并将分组大于1的以升序排列显示
子查询
一条sql语句不能产生最终的结果,需要两条sql语句才能解决问题,其中一条sql语句作为条件,用括号括起来
select max(sal) from emp -- 查询最高工资
select * from emp
select * from emp where sal=(select max(sal) from emp) -- 查询最高工资员工信息
select max(sal),deptNo from emp group by deptNo -- 查询部门最高工资
select * from emp where (sal,deptNo) in(select max(sal),deptNo from emp group by deptNo) -- 查询部门最高员工工资
查询公司工资最高、最低员工信息
select max(sal) from emp -- 查询最高工资
select min(sal) from emp -- 查询最低工资
select * from emp where sal=(select max(sal) from emp) -- 查询公司最高薪水的人的信息
select * from emp where sal=(select min(sal) from emp) -- 查询公司最低薪水的人的信息
查询每个部门最高薪水信息
select max(sal),deptNo from emp GROUP BY deptNo -- 查询公司部门最高工资
select * from emp where (sal,deptNo) in (select max(sal),deptNo from emp GROUP BY deptNo) -- 查询每个部门最高薪水人员信息
查询薪水比公司平均薪水还要高的人
select avg(sal) from emp -- 查询公司的平均薪水
select * from emp where sal>(select avg(sal) from emp) -- 查询公司员工比平均薪水还要高的人员信息
查询人员king的手下有谁
select empno from emp where ename="king" -- 查询King信息对应的empno信息
select * from emp where mgr=(select empno from emp where ename="king")
联接
因为外键的产生,在查询时需要将所有数据都显示,则需要使用联接
select *from emp as e,dept as d where e.deptNo=d.deptNo -- emp为员工信息表,dept为公司部门表
-- 使用笛卡尔成绩将两张表(左表和右表相连)
-- 再用外键将两张表进行匹配,得到需要的结果
分类: 内联接
select * from emp as e join dept as d on e.deptNo=d.deptNo -- 内连接,inner join 表1 on 表2 此时inner可以省略
外联接
左联接
select * from emp as e left outer join dept as d on e.deptNo=d.deptNo -- 左联接,此时outer可以省略,以表1为准,显示表1数据,如果表1没有对应表2的外键,则表2中的数据显示为null
右联接
select * from emp as e right outer join dept as d on e.deptNo=d.deptNo -- 右联接,此时outer可以省略,以表2为准,显示表1数据。如果表2中有数据,表1没有对应的外键设置的数据,则表1中显示为null
select * from emp where sal>all(select sal from emp where ename='smith')
//使用all包含所有子查询的内容
select * from emp where sal>any(select sal from emp where ename='smith')
//使用any任意一个满足即可输出
组合查询
使用union链接2条或以上查询条件
例如:
select * from emp where sal in(select max(sal) from emp group by deptNo) union select * from emp where sal in(select min(sal) from emp group by deptNo)