初级mysql数据库
0 目标:完成分类表的CRUD操作
1 常用操作
DDL :数据定义语言:create drop alert定义数据表数据库的它们的结构
DCL :数据控制语言:数据库访问权限
DQL :数据查询语言:select from where
DML :数据操作语言 :主要用来操作数据,insert update delete
2 表的常规操作(基于DDL)
-
添加列:add
-
修改列:modify
-
修改列名:change
-
修改表名:rename
-
删除列:drop
添加(add):alter table A add 列名 列的类型 列的约束
修改列(modify) alter table A modify sex varchar(2);
修改列名(change) alter table A change sex gender varchar(2);
删除列(drop)alter table A drop chengji;
修改表的字符集alter table A character set u-8;
删除表:drop table heima;
总结:
1.创建数据库和表:都是create table/database 名字
2.查看数据库和表:show tables/databases show create table/database 名字
select database()查看正在使用的数据库。 desc 表名 查看表的结构
3.删除数据库和表:drop database/table 名字
4.修改数据库:alter database 名字 character set 字符集
修改表(包括修改表中的结构和修改表的名字与字符集):
-
添加列:alter table heima add 列名 列的类型 列的约束
-
删除列: alter table heima drop 列名
-
修改列名: alter table heima change 列名 新列名 类型 约束
-
修改列: alter table heima modify 列名 列类型 列约束
-
修改表名:rename table heima to 新表名
-
修改表的字符集(和修改库的一致):alter table heima character set 字符集;
所有修改表中的列而不是操作表自身都要加上alter
-
3 表的常规操作(基于DML)完成对表中数据的CRUD操作
3.1 插入数据
1. insert into 表名 (列名1,列名2,列名3) values(1,2,3);
如果values中包含所有列那么可以就将前面的省略。
2. 批量插入 :
insert into 表名 values (1,2,3,4),(5,6,7,8),(9,10,11,12); 批量插入效率更高一点。
3.2 删除数据
1. delete from 表名 where 条件
delete删除和truncate删除数据有什么差别:
delete :DML 一条一条的删除
truncate :DDL 先删除表在重建表
如果数据量比较少那么用delete比较高效,如果数据比较多,truncate比较高效
3.3 更新数据
1. update 表名 set 列名 1= 列的值,列名2 =列的值[where条件]
update heima set name = ‘lisi’ where id = 5; 更新一般要有where
3.4 查询数据
1. select distinct * from 表名 where 条件
distinct 去除重复的数据。 select distinct price from product
2. 列别名
select p.pname from product as p 其中as可省略
3. 运算查询
select price * 1.5 from product;
select price * 0.8 as 折后价 from product;
4. 条件查询
1.关系运算符 :> >= < <= <> = <>:不等于; select * from product where price < 28;
2. 逻辑运算:and or not (and 优先与 or 如果有括号那就先处理括号里的) not in () :不再范围内的
查询商品价格在10 到100 之间的所有商品:select * from product where price between 10 and 100;
查询商品价格小于35或者大于900的所有商品: select * from product where price<35 or price>900;
3. 空值查询 :IS NULL (检查是否具有空值的列)示例: select * from product where price IS NULL;
5.模糊查询 like
- 表示一个字符 %代表多个字符
6. in 某个范围中获得值
查询出商品分类ID在1,4,5里面的所有商品 select * from product where id in (1,4,5);
7. 排序查询
order by asc/desc (升序/降序)
查询出名称中有小的商品,按价格升序排序 select * from product where name like %小% order by price asc;
8. 常见函数
sum() arg() count() max() min() round()四舍五入round(-1.567,2):-1.57 保留小数点后两位 注:负数四舍五入先取绝对值,再四舍五入再填上-号
注意:where后不能加聚合函数: select * from product where price > arg(price); 这是不允许的。
那么怎样查出商品价格大于平均价格的所有商品: select * from product where price > (select avg(price) from product)
9. 分组 :group by
having 可以接聚合函数 出现在分组之后
where 不可以接聚合函数 出现在分组之前
select cno,avg(price) from product group by cno having avg(price) > 60
10.正则表达式
regexp: select * from product where pname regexp ‘.000’ 其中 . 代表任意一个字符。不区分大小写 , 若想区分就用binary
regexp yu like的区别
product_name 表格如左图: 那么 执行一下两条语句
JetPack 100 1. select product_name from product where product_name like ‘100’;
JetPack 200 2. select product_name from product where product_name regexp ‘100’;
结果: 第一个没找到,第二个把JetPack 100返回了,这就是他俩的区别。
11 .mqsql中+的作用:
select 90+100只用于数值的运算 190
select ‘90’ + 100 结果为190 如果一方为字符型,试图将字符转换成数值,如果转换成功,则继续做加法运算 select ‘lisi’ + 100 结果为 100 如果转换失败则将字符型数值转换为0然后进行加法运算
select null + 100 只要一方出现null 结果为null。
12 contat 拼接 将两个字符拼接成一个,类似与java中的+拼接。
select last+first as 姓名
13 select 中的顺序:select from where group by having order by limit
4 多表间维护
如何维护:
外键约束: foreign key 给product中的cno添加一个外键约束
alter table product add foreign key(cno) references category(cid); 注意:当删除分类表时,必须先删除product表。
5 多表查询
5.1 交叉链接查询:笛卡尔积
select * from product 无意义
5.2 过滤除有意义的数据
select * from product as p,catagory as c where p.cno = c.cid;
5.3 内链接
5.3.1 隐式内链接
select * from product p,category c where p.cno = c.cid;
5.3.2 显示内链接
select * from product innner join category c on p.cno = c.cid;
区别: 显示内链接:在查询出结果的基础上去做的where条件过滤
隐式内链接 : 带着条件去查询结果 执行效率高。
5.4 左外连接
会将左表的所有数据都查询出来,如果右表中没有对应的数据用null代替
select * from product p left outer join category c on p.cno=c.cid;
5.5 右外链接
与上面的正好相反。
select * from product p right outer join category c on p.cno=c.cid;
5.6 分页查询
select * from product limit 0,3 0代表起始索引,3代表个数 这个用在显示商品或分类列表处。
5.7 子查询
1.查询出分类为手机数码的所有商品 select * from product where cno = ( select cid from category where cname = ‘手机数码’ )
2. 查询出(商品名称,商品分类名称)信息
——左链接
select p.pname,c.cname from product p left outer join category c on p.cno = c.cid;
——子查询
select pname ,(select cname from category where p.cno = c.cid) from product.
3.查询出订购物品TNT2的所有客户:
select cust_id from orders where order_num in (select order_num from orderitems where prod_id = ‘TNT2’)
**一个数用=,多个数用in **
-
显示customers表中每个客户的订单总数,订单与相应的客户ID存储在orders表中。**作为计算字段使用子查询 **
select cust_name,(select count(*) from orders where orders.cust_id=customers.cust_id)as orders from customers order by cust_name;
5.8 组合查询 union
select uid,pid from product where price>5
union
select uid,pid from product where id in (1001,1002)
结果是两个查询结果都显示出在一张表上
union可以自动取出重复的行 如果用union all 则不会去重
6 函数(单行函数和多行函数)
一、单行函数
1.字符函数:
(1) length (): 返回字节长度,汉字:gbk:2 utf-8:3 例子:select length(‘join张三丰’) ;
(2) 拼接字符串 上面写过了。
(3) upper、lower 实例:将姓大写,名字小写 然后用_拼接:
selcet concat(upper(first_name),’_’,lower(last_name)) from 表
(4) substr、substring 去一部分 ,第一个数字代表起始,第二个代表个数,如果括号里只有一个,那么就是从起始到结束 注:索引从1开始 select substr(‘王鑫亮真的帅666’,1,3) --> 王鑫亮
例子: 名字中首字符大写,其余小写,然后用_拼接 select concat(upper(sub(last_name,1,1)),lower(substr(last_name,2))) ;
(5) instr 返回子串第一次出现的位置 select instr(‘1231’,‘23’); 返回1 3
(6) trim去除两端的空格 select trim (’ haha ') 结果为哈哈
trim(‘aa’ from ‘aaahhhaaa’) 结果为 ahhha
(7) lpad 用指定的字符实现左填充 select lpad(‘殷素素’,10,‘a’) 结果:aaaaaaa殷素素
(8) rpad 是右填充 对照走填充、
(9)replace 替换 select replace (‘张无忌爱上了周芷若’,‘周芷若’,‘赵敏’) 结果:张无忌爱上了赵敏
二、常用数学函数
(1)round() 四舍五入 : 前面介绍过
(2)floor 向下取整 、ceil()向上取整
(3) truncate 截断 select truncate(1.6669,1) 结果:1.6
(4) mod 取余 mod(a,b) = a-a/b*b;
三、日期函数
(1) now() 返回当前的系统日期+时间 select now()
(2) curdate() 返回当前的系统日期不包含时间 select curdate()
(3) cuitime() 返回当前的时间,不包含日期 select curtime()
(4)可以获取指定的部分 年、月、日、小时、分钟、秒: select year(now()) ;
(5) str_to_date:将日期格式的字符串转换成指定格式的日期
(6) date_format:将日期转换成字符串
例: (5) SELECT STR_TO_DATE(‘1999-2-6’,’%Y-%c-%d’)
(6) SELECT DATE_FORMAT(NOW(),’%Y年-%m月%d日’) SELECT DATE_FORMAT(birthday,’%Y年-%m月%d日’)FROM stu
序号 | 格式符 | 功能 |
---|---|---|
1 | %Y | 四位的年份 |
2 | %y | 两位的年份 |
3 | %m | 月份(01 02 03) |
4 | %c | 月份(1 2 3) |
5 | %d | 日(01 02 03) |
6 | %H | 小时(24小时) |
7 | %h | 小时(12小时) |
8 | %i | 分钟 |
9 | %s | 秒 |
四、流程控制函数
1.if函数: if else 效果
一、select if(10<5,‘大’,‘小’)
二、 case函数使用:
case:要判断的字段或表达式
when : 常量1 then 要显示的值1或语句1
when : 常量2 then 要显示的值1或语句2
when : 常量3 then 要显示的值1或语句3
else 要显示的值n或语句n
end
实例:select salary 原始工资 id case id
when 30 then salary*1.1
when 40 then salary*1.2
else salary
end
例题: 1 显示系统时间 (注:日期加时间)
select now();
-
查询员工号,姓名,工资,以及工资提高20%之后的结果
select id,name,salary,salary*1.2 as “new salary” from employee
-
将员工的姓名按首字母排序,并写出姓名的长度。
SELECT sname,LENGTH(sname)长度 ,SUBSTR(sname,1,1) 首字符 FROM stu ORDER BY 首字符
注意:snmae不可用别名,他是原有的字段,排序放在最后,可以按别名排序,即首字符,不一定要写SUBSTR(sname,1,1) ;