对数据库的操作
1.创建数据库
create database 数据库名;
2.使用数据库
use database 数据库名;
3.查看当前使用数据库
select database();
4.当前使用的数据库中所有表的信息
show tables;
5.查看建立数据库命令
show create database 数据库名;
6.删除数据库
drop database 数据库名;
对表的操作
1.创建表
create table table_name(
id int unsigned auto_increment,
title varchar(30) not null,
author varchar(20) not null
)
2.表明的修改
rename table oldname to newname ;
3.添加字段
alter table 表明 add column taddress(字段名) varchar(30);
4.删除字段
alter table 表名 drop 字段名;
修改字段名及类型
change 可以更改列名和类型名 (每次都要把新列名和旧名写上,即使两个列名没有更改,只是改了类型)
modify 只能更改列明属性 只需要写一次,比change 省事点
alter table t1 modify tname varchar(50);
alter table t1 change tname teacher teacher_name varchar(30);
5.主键(pk)
1.建表时设置主键
create tabel tableName(
id int primary key
);
或:
create table tableName(
id int,
primary key (id)
);
2.单独设置主键
alter table tableName add primary key(id);
3.删除主键
alter table tableName drop primary key;
6.唯一约束
1.建表时添加唯一键
create table tableName(
columnName int unique
);或
create table tableName(
columnName int,
unique key(columnName)
);
2.单独添加唯一键
alter table tableName add unique key (columnNama(列名));
3.删除唯一键
alter table tableName drop columnName (列名)
7.外键(fk)
1.创建表时增加外键
create table tableName(
tableName2_id int not null
foregin key (tableName2_id) references tableName2(id)
);
2.已存在表增加外键
FK_ID是外键的名称
alter table 表名 add constraint FK_ID(取得外键名) foregin key(你的外键字段名) references teacher(id);
3.删除外键
alter table tableName drop foregin key 外键名
8.删除表
drop table user(表名)
增删改查
1.增
-- 数据插入 insert into 表名 values();
insert into stu (name ,age,address,money,birth)values('李四',18,'郑州','1995-5-5');
insert into stu (name,sex)values('李丽','女');
-- 如果主键相同 就把以前的数据换成现在的
入过不相同 就插入
replace into stu values(null,'王强','男');
2.删
-- 删除表的所有的数据
delete from st where true;
-- 删除表里面scroe<60的数据
delete from st where score <60;
delete :用于删除表中的行(表的结构,属性,索引完整)
语法:删除某一行:delete from 表名 where 列名称=值
删除所用行:delete from 表名 或 delete *from 表名
drop:用于删除表(表的结构,索引,属性也将会被删除)
语法:drop table 表名称
truncate :用于删除表内的数据(仅删除表内的数据,不删除表本身,相当于delete语句不写where子句一样)
使用场合:
当你不需要该表时,用drop;
当你仍要保留该表时,但要删除所有记录时,用truncate
当你要删除部分记录时 用delete;
3.改
-- mysql 中字符 中连接使用concat函数
select concat(‘hello’,'mysql');
select concat_ws('-','hello','mysql')
-- 字符串连接concat () concat_ws()
update stu set address = concat_ws (':','中',address)
update stu set money = money +10 where address like'%郑州%';
-- 字符串截取函数 left() mid() right()
select mid(‘截取字符串’);
4.查
**1.简单查询**
select *from st --不推荐使用
select score,name ,from st;
--列别名
select name as 姓名,score 成绩 from st;
select name ,scroe,'中国'china ,score *.2 success from st;
**2.条件查询**
-- 条件查询
-->< <= >= <> != and or not
select *from st where score >=60 or name = '赵六';
select *from st where not score >= 60;
select *from st where name <> '赵六' or name != ‘张三冯’;
-- in 或 not in
select id,name,sex ,dept from stu where id in(1,5,106);
select id ,name sex dept from stu where id not in(1,5,106);
-- between and 数字 和日期 not bewteen and
select *from st where scroe between 60 and 90;
select *from stu where birth>'1999-12-31';
select *from st where score >=60 and score <=90;
-- is null 或 is not null
select *from st where score is null;
select *from st where score is not null ;
select *from st where score not score is null;
-- like_代表一个任意字符 %代表0个或多个任何字符
select *from stu where name like '张_';
select *from stu where name like'张%';
select *from stu where name like '李%'
-- 查看姓名中有 丽字的学生信息
select *from stu where name like '%丽%';
select *from stu where name like '__';
select *from stu where name like '__%';
select *from stu where name like '李_%' or 1=2;
select *from stu where name not like '李%';
select *from stu where not name like '李%';
**结果查询order by**
-- order by
-- 查询排序 order by asc 升序 desc 降序
select id 编号 ,name 姓名,age ‘年龄(岁)’ from stu order by age desc ,name asc;
**查询结果限制行数limit**
select id 编号,name 姓名,age 年龄 from stu where age>18 order by age desc ,name asc limit 2,2;
-- limit 限制结果行数
select *from st limit 2;
select *from st limit 0,2; -- 第一页
select *from st limit 2,2; -- 第二页
-- 每页3条记录,显示第三页,如何写语句
select *from st 6,3;
-- 随机查询三条记录
select *from st limit 3;
select *from stu order by rand() limit 3;
**正则表达式regexp**
select 'c' regexp '[abd]+';
select *from stu where name regexp '...';
select *from stu where name regexp '^李.*$';
select '李12432325四887' regexp '[0-9]{6}';
select *from stu where name regexp '李.$';
**聚集函数**
-- 集合函数 count max min avg sum distinct
select count(*) from stu;
select max(money),min(money),avg(money),sum(money) from stu;
update stu set money = null where id = 4;
-- 统计一下几个专业 distinct 消除重复
select distinct dept from stu where dept is not null ;
select count (distinct dept) from stu where dept is not null;
**分组查询统计 group by**
-- 分组查询主要是用来统计
select *from stu
select count(*) from stu where dept = '计算机科学';
select ifnul(dept,'未选专业') 专业 ,count(*) 人数,max(money) 钱最多 from stu group by dept;
--判断函数 if() ifnull ()
select rand(),round(1,5)
select if(round(rand()),'yes','no');
select if(1=4 ,'11','22');
select ifnull('a','yes'),if null('','yes'),ifnull(null,'yes');
select *from stu;
select id , name ,ifnull (sex,'保密') from stu;
select id 编号,name 姓名,if(score is null,0,score) 成绩 from st
union
select id 编号,name 姓名,ifnull(score,0) 成绩 from st;
-- 查询显示学号 ,姓名,成绩,等级(>=90 优秀 >=80 良好 >=60 及格 补考 并统计人数)
select *from st1;
select leve 等级,count(*)人数
from
(select id,name,score,if(score>=90,'优秀',if(score>=80,'良好',if(score>=60,'及格','补考'))) leve
from st1) as stt group by leve ;
-- 分组 group by 分组条件 where group by having order by limit
select leve 等级,count(*)人数
from
(select id,name,score,if(score>=90,'优秀',if(score>=80,'良好',if(score>=60,'及格','补考'))) leve
from st1) as stt group by leve having count(*)>=3 order by count(*) desc limit 1;
**连接查询(多表查询 )子查询**
/* 连接查询left join(多表查询) */
select * from st s1,st s2,st s3;
select * from st1,st2,st3;
-- 查询学号 姓名 成绩 老师的姓名 student teacher(tname)
select * from student;
select * from teacher;
select st.id,sname,gender,tname
from student st ,teacher te
where st.`teacher_id` = te.`id`;
-- left join on 连接查询
select st.id,sname,gender,ifnull(tname,'')
from student st right
join teacher te on st.`teacher_id`=te.id;
-- 建立视图
create view stv as
select st.id,sname,gender,ifnull(tname,'')
from student st right
join teacher te on st.`teacher_id`=te.id;
select * from stv;