赞
踩
其语句包括动词CREATE,ALTER和DROP。在数据库中创建新表或修改、删除表(CREATE TABLE 或 DROP TABLE);为表加入索引等。
mysql是一个关系型数据库,库里面包含若干个表,而每一张表都是由行和列组成。
- -- 创建数据库 create database 数据库名称
- create database mydb;
-
- -- 查看当前具有的数据
- show databases;
-
- -- 切换到指定的数据库 use 数据库名称
- use mydb;
-
- -- 删除数据库 drop database 数据库名称
- drop database mydb;
注意 : 删除的数据库必须是你自己创建的 千万不要删除系统自带的数据库 而且数据库中不区分大小写.
DROP TABLE IF EXISTS `course`; //检测是否存在表`course`,存在删掉,不存在不执行
- 1.创建表结构的语法:
- create table 表名(
- 列名 数据类型,
- 列名 数据类型,
- 列名 数据结构,
- ......
- 列名 数据结构 -- 千万别加逗号
- );
-
- 注意: 最后一列不使用逗号 逗号必须是英文状态下的
-
- -- 创建表
- create table student(
- id int primary key auto_increment comment '序号',
- name varchar(20),
- age int,
- address varchar(20)
- );
-
- -- int 数据类型整形 一般不建议定义长度 默认11位 如果长度大可以用long 长度短用tinyint
- -- varchar(长度) 字符串类型(字符串的长度)
- -- primary key: 表示该列为主键列. 特点: 该列的值不能为null 也不能重复 一张表只能有一个primary key;
- -- auto_increment: 表示该列的值可以递增.该列的类型必须为int类型.
- -- comment '序号': 注释
-
- -- 显示当前数据库中具有的表
- show tables;
-
- -- 显示表结构 desc 表名;
- desc student;
-
- -- 删除表 drop table 表名;
- drop table student;
MySQL支持所有标准SQL数值数据类型。
这些类型包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL和NUMERIC),以及近似数值数据类型(FLOAT、REAL和DOUBLE PRECISION)。
关键字INT是INTEGER的同义词,关键字DEC是DECIMAL的同义词。
BIT数据类型保存位字段值,并且支持MyISAM、MEMORY、InnoDB和BDB表。
作为SQL标准的扩展,MySQL也支持整数类型TINYINT、MEDIUMINT和BIGINT。下面的表显示了需要的每个整数类型的存储和范围。
类型 | 大小 | 范围(有符号) | 范围(无符号) | 用途 |
---|---|---|---|---|
TINYINT | 1 byte | (-128,127) | (0,255) | 小整数值 |
SMALLINT | 2 bytes | (-32 768,32 767) | (0,65 535) | 大整数值 |
MEDIUMINT | 3 bytes | (-8 388 608,8 388 607) | (0,16 777 215) | 大整数值 |
INT或INTEGER | 4 bytes | (-2 147 483 648,2 147 483 647) | (0,4 294 967 295) | 大整数值 |
BIGINT | 8 bytes | (-9,223,372,036,854,775,808,9 223 372 036 854 775 807) | (0,18 446 744 073 709 551 615) | 极大整数值 |
FLOAT | 4 bytes | (-3.402 823 466 E+38,-1.175 494 351 E-38),0,(1.175 494 351 E-38,3.402 823 466 351 E+38) | 0,(1.175 494 351 E-38,3.402 823 466 E+38) | 单精度 浮点数值 |
DOUBLE | 8 bytes | (-1.797 693 134 862 315 7 E+308,-2.225 073 858 507 201 4 E-308),0,(2.225 073 858 507 201 4 E-308,1.797 693 134 862 315 7 E+308) | 0,(2.225 073 858 507 201 4 E-308,1.797 693 134 862 315 7 E+308) | 双精度 浮点数值 |
DECIMAL | 对DECIMAL(M,D) ,如果M>D,为M+2否则为D+2 | 依赖于M和D的值 | 依赖于M和D的值 | 小数值 |
如果我们要在数据库中存储 钱 等数据 此时不能使用 float double 因为这些类型会丢失精度 要使用decimal
使用数字类型最多为: int tinyint double decimal (钱)
表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。
每个时间类型有一个有效值范围和一个"零"值,当指定不合法的MySQL不能表示的值时使用"零"值。
TIMESTAMP类型有专有的自动更新特性,将在后面描述。
类型 | 大小 ( bytes) | 范围 | 格式 | 用途 |
---|---|---|---|---|
DATE | 3 | 1000-01-01/9999-12-31 | YYYY-MM-DD | 日期值 |
TIME | 3 | '-838:59:59'/'838:59:59' | HH:MM:SS | 时间值或持续时间 |
YEAR | 1 | 1901/2155 | YYYY | 年份值 |
DATETIME | 8 | 1000-01-01 00:00:00/9999-12-31 23:59:59 | YYYY-MM-DD HH:MM:SS | 混合日期和时间值 |
TIMESTAMP | 4 | 1970-01-01 00:00:00/2038 结束时间是第 2147483647 秒,北京时间 2038-1-19 11:14:07,格林尼治时间 2038年1月19日 凌晨 03:14:07 | YYYYMMDD HHMMSS | 混合日期和时间值,时间戳 |
data类型和Datetime类型
字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。该节描述了这些类型如何工作以及如何在查询中使用这些类型。
类型 | 大小 | 用途 |
---|---|---|
CHAR | 0-255 bytes | 定长字符串 |
VARCHAR | 0-65535 bytes | 变长字符串 |
TINYBLOB | 0-255 bytes | 不超过 255 个字符的二进制字符串 |
TINYTEXT | 0-255 bytes | 短文本字符串 |
BLOB | 0-65 535 bytes | 二进制形式的长文本数据 |
TEXT | 0-65 535 bytes | 长文本数据 |
MEDIUMBLOB | 0-16 777 215 bytes | 二进制形式的中等长度文本数据 |
MEDIUMTEXT | 0-16 777 215 bytes | 中等长度文本数据 |
LONGBLOB | 0-4 294 967 295 bytes | 二进制形式的极大文本数据 |
LONGTEXT | 0-4 294 967 295 bytes | 极大文本数据 |
注意:char(n) 和 varchar(n) 中括号中 n 代表字符的个数,并不代表字节个数,比如 CHAR(30) 就可以存储 30 个字符。
CHAR 和 VARCHAR 类型类似,但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。
varchar(20) 长度可变----如果你存入的字符个数小于n 则mysql底层按照你存储字符个数分配空间。
char(20) 长度不可变----如果你存入的字符个数小于n mysql底层按照20的空间来分配。他一般用于长度固定的字符串。比如: 手机号,身份证号
BINARY 和 VARBINARY 类似于 CHAR 和 VARCHAR,不同的是它们包含二进制字符串而不要非二进制字符串。也就是说,它们包含字节字符串而不是字符字符串。这说明它们没有字符集,并且排序和比较基于列值字节的数值值。
BLOB 是一个二进制大对象,可以容纳可变数量的数据。有 4 种 BLOB 类型:TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。它们区别在于可容纳存储范围不同。
问题: MySQL能不能存储音频 视频图片文件 ?
可以的 将音视频图片 转换成流 存储到数据库 blob 但是我们数据库 不存文件
文件一般存到磁盘 然后数据库中存储文件路径
有 4 种 TEXT 类型:TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。对应的这 4 种 BLOB 类型,可存储的最大长度不同,可根据实际情况选择。
- -- (1) 增加一列
- alter table student add column sex varchar(2);
-
- -- (2) 修改列的名称
- alter table student change column sex aaa varchar(2);
-
- -- (3) 修改列的类型
- alter table student modify column aaa int;
-
- -- (4) 删除某一列
- alter table student drop column aaa;
数据操作语言(DML:Data Manipulation Language):其语句包括动词INSERT、UPDATE和DELETE。它们分别用于添加、修改和删除。
上面已经把表结构创建好;
但是我们应该表中添加数据。
- -- 添加数据 注意: 字符串必须用单引号 不能用双引号
- -- (1) 添加所有字段的数据
- insert into student(id,name,age,address) values(1,'张三',18,'郑州');
-
- -- (2) 如果添加所有列的值 那么可以省略列的名称
- insert into student values(2,'李四',19,'上海');
-
- -- (3) 因为主键为递增模式 所以在添加数据时可以不为主键指定值
- insert into student values(null,'王五',20,'北京');
-
- -- (4) 添加部分列
- insert into student(name,address) values('赵六','广东');
-
- -- (5) 添加多条记录
- insert into student values(null,'孙七',12,'杭州'),(null,'田八',54,'开封'),(null,'杨九',34,'洛阳');
- -- 删除记录
- -- (1) 根据条件删除 delete from 表名 where 条件; 关于条件的介绍我们会后面详细讲解.
- delete from student where id = 2;
- -- 修改记录 update 表名 set 列名='值',列名='值' where 条件
- update student set name='赵日天',age=18,address='湖南' where id = 5;
- -- 条件
- -- 范围删除
- delete from student where age > 15;
- -- 逻辑符连接多个条件 and or
- delete from student where age < 15 or name = '王五';
MySQL 判断字段是否包含某个字符串的方法
方法一:like
SELECT * FROM 表名 WHERE 字段名 like "%字符%";
方法二:INSTR(字段,字符)
利用mysql 字符串函数 find_in_set();
select * from 表名 where INSTR(字段,字符)
用locate 是最快的,like 最慢。position一般
实战例子:
select * from historydata
where locate(‘0’,opennum) and locate(‘1’,opennum)
order by number desc limit 10;
方法三:locate(字符,字段名)
使用locate(字符,字段名)函数,如果包含,返回>0的数,否则返回0 ,
它的别名是 position in
select * from 表名 where locate(字符,字段)
select * from 表名 where position(字符 in 字段);
例子:判断site表中的url是否包含’http://'子串,如果不包含则拼接在url字符串开头
update site set url =concat(‘http://’,url) where locate(‘http://’,url)=0
注意mysql中字符串的拼接不能使用加号+,用concat函数
方法四:find_in_set()
利用mysql 字符串函数 find_in_set();
SELECT * FROM users WHERE find_in_set(‘字符’, 字段名);
mysql有很多字符串函数 find_in_set(str1,str2)函数是返回str2中str1所在的位置索引,str2必须以","分割开。
注:当str2为NO1:“3,6,13,24,33,36”,NO2:“13,33,36,39”时,判断两个数据中str2字段是否包含‘3’
mysql > SELECT find_in_set()(‘3’,‘3,6,13,24,33,36’) as test;
-> 1
mysql > SELECT find_in_set()(‘3’,‘13,33,36,39’) as test;
-> 0
1. DML:
-- 增加 insert --- 保证你的列名和值匹配 (个数 类型)。
insert into 表名(列名,列名....) values(值,值....)
-- 删除
delete from 表名 where 条件
-- 修改
update 表名 set 列名=值,列名=值,.... where 条件
2. DDL:
creata database 数据库名;--- 创建数据库的命令
use 数据库名;---- 切换数据库
drop database 数据库名;---- 删除数据库.
show databases; ----显示所有的数据库名称
create table 表名(
列名 数据类型,
列名 数据类型.....
);
数据类型: 数字类型(int double tintyint decimal)
字符串类型(varchar char)
时间类型(date, datetime)
修改表结构:
-- 增加一列
alter table 表名 add column 列名 数据类型;
-- 删除一列
alter table 表名 drop column 列名;
-- 修改列的数据类型
alter table 表名 modify column 列名 数据类型
如果大家使用idea方便 那么 我们也可以使用idea作为mysql的客户端来连接数据库。
选择数据库 右键 转存储SQL 选择 数据+结构 保存
为什么需要把sql导出?
相当于有了备份: 防止sql丢失。
创建一个库 将sql文件拖进去
需要创建相应的数据库并切换到该库中
create database student;
use student;
把我们的sql文件拖入到相应的数据库表名处。
use mydb;
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '学生表的主键列',
`name` varchar(255) ,
`age` int(11) ,
`address` varchar(255),
PRIMARY KEY (`id`) USING BTREE
) ;-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES (1, '张三', 18, '北京');
INSERT INTO `student` VALUES (2, '李四', 19, '北京');
INSERT INTO `student` VALUES (3, '王五', 19, '北京');
INSERT INTO `student` VALUES (4, '钱七', 19, '北京');
INSERT INTO `student` VALUES (5, '关羽', 25, '南京');
INSERT INTO `student` VALUES (6, '张飞', 25, '南京');
INSERT INTO `student` VALUES (7, '赵云', 28, '南京');
INSERT INTO `student` VALUES (8, '项羽', 19, '南京');
INSERT INTO `student` VALUES (9, '刘备', 30, '上海');
INSERT INTO `student` VALUES (10, '曹操', 40, '许昌');
INSERT INTO `student` VALUES (11, '夏侯', 36, '许昌');
INSERT INTO `student` VALUES (12, '诸葛亮', 38, '上海');
INSERT INTO `student` VALUES (13, '周瑜', 78, '江东');
INSERT INTO `student` VALUES (14, '小乔', 56, '江东');
INSERT INTO `student` VALUES (15, '孙尚香', 59, '江东');
格式:
SELECT 列名 FROM 表名
1. select * from student;
查询表中所有的记录,注意select后跟了* 表示通配符,所有的列。而在实际开发中不建议大家使用*,因为*会导致索引失效。而是把每一个列名一一列出。
select id,name,age,address from student;
2. 我们也可以查询部分列。
select name,age from student;
注意: 查询的结果,被放入到一个临时表中。
3.可以把查询的列名起别名
-- 为查询的列名起别名
select name as 姓名, age as 年龄 from student;
-- 这里的as可以省略
select name 姓名, age 年龄 from student;
-- 使用distinct 去除重复字段值 要求查询的所有列值 都必须相同时。
select distinct address from student;
上面讲解的查询都是查询所有记录。而在实际开发中我们会根据条件查询符合的记录
1. 判断
select * from student where age>19;
select * from student where age>=19;
select * from student where age!=19;
select * from student where age<19;
select * from student where age<=19;2. 多条件查询 and(与) or(或)
select * from student where age>=19 and address='上海';select * from student where age<=19 or address='上海';
3. 范围语句
select * from student where age>=19 and age<=30;select * from student where age between 19 and 30; -- 等价于上面
4. 使用in
-- 此时我们有这样一个需求 查询年龄是 18 或者 25 或者 30 或者 36 或者 48 的学生
select * from student where age=18 or age=25 or age=30 or age=36 or age=48;-- 上面这种方式比较麻烦 我们可以使用in来替换上面的模式
select * from student where age in(18,25,30,36,48);
5. 模糊搜索 like
通配符: _统配一个字符 %统配n个字符。-- 模糊查询
-- 查询名字中第二个字符为三 _
select * from student where name like '_三';
-- 查询名字姓李 %统配任意字符
select * from student where name like '李%';
select * from student where name like '%三%';-- 查询年龄为空的学生信息 is null 判断为空 is not null 判断不为空
select * from student where stu_age is null
-- 排序查询 order by 列名 desc:降序 不加 desc asc:升序(默认)
select * from student order by age ;
-- 根据多列进行排序年龄按照降序 id按照升序。
select * from student order by age desc, id desc;
-- 按照年龄进行排序 如果年龄相同 再按照id进行排序
注意: 既有where 又有order by 那么谁在前 谁在后。
where在前 order by在后。
select * from student where age>19 order by age desc;
条件查询。
1. 判断条件 >,>=,<,<=,!=
2. 多个条件 and or
3. 范围查询 between and, in
4. 去除重复 distinct
5. 排序: order by 列名 desc; order by 列名 asc;
6. 模糊查询: like 匹配通配符: % 统配n个字符 _:统配一个字符。
请查询所有学生 三年后的年龄
select age+3 from student
有了这个特性 就可以在设计数据库表的时候 如果有些值可以通过多列运算出来 此时就没有必要设计了
例如 设计一个订单表
编号 名字 数量 价格 总价
此时 总价 可以通过 数量*价格 进行计算 所以就没有必要设计到数据库表中
id name num price
1 娃哈哈 50 2
2 雪糕 50 3
查询的时候
select id,name,num,price,num*price total from `order`
注意 order是表名 但是又是sql的语句关键字 所以加 `` 所以设计数据库表的时候 表名一般都 t_开头
SQL 结构化查询语言 也是一种编程语言,所以也有函数 并且有 内置函数(官方) 和自定义函数(程序员)
聚合函数总共有5个:
max(列名):求某列的最大值。
min(列名):求某列的最小值。
sum(列名):求某列的和
avg(列名):求某列的平均值
count(列名):求某列的个数。
-- 求最大的年龄
select max(age) from t_student;
-- 求最小的年龄
select min(age) from t_student;
-- 求年龄的和
select sum(age) from t_student;
-- 求学生的个数
select count(id) from t_student;
-- 求年龄平均值
select avg(age) from t_student;
面试:
1 聚合函数 可不可以和 where 连用
大致解释如下,sql语句的执行过程是:from-->where-->group by -->having --- >order by --> select;
聚合函数是针对结果集进行的,但是where条件并不是在查询出结果集之后运行,所以主函数放在where语句中,会出现错误,
而having不一样,having是针对结果集做筛选的,所以我门一般吧组函数放在having中,用having来代替where,having一般跟在group by后
2 count(列名) count(1) count(*) 有什么区别?
-- count(列名) count(1)表中第1列但是它不能使用distinct去重 count(*)表中所有列 统计记录条数
执行效果上:
- count(*)包括了所有的列,相当于行数,在统计结果的时候,不会忽略列值为NULL
- count(1)包括了所有列,用1代表代码行,在统计结果的时候,不会忽略列值为NULL
- count(列名)只包括列名那一列,在统计结果的时候,会忽略列值为空(这里的空不是只空字符串或者0,而是表示null)的计数,即某个字段值为NULL时,不统计。
执行效率上:
- 列名为主键,count(1)会比count(列名)快
- 列名不为主键,count(1)会比count(列名)快
- 如果表多个列并且没有主键,则 count(1) 的执行效率优于 count(*)
- 如果有主键,则 select count(主键)的执行效率是最优的
- 如果表只有一个字段,则 select count(*) 最优。
count(列名)某个字段值为NULL时,不统计
如果问一个程序员MySQL中SELECT COUNT(1)和SELECT COUNT()有什么区别,会有很多人给出这样的答案“SELECT COUNT()”最终会转化成“SELECT COUNT(1),而SELECT COUNT(1)省略了转换的这一步,所以SELECT COUNT(1)效率更高“,甚至有一些面试官也会给出类似的答案。最近在看一些历史遗留代码,绝大多数统计数量的SQL都在用SELECT COUNT(1),觉得有必要搞清楚这个问题。
在sql中有个 group by 语句 将某一列相同数据 视为一组 然后进行查询 与聚合函数连用。
-- 求各个地区的人数
select count(*),address from t_student group by address;-- 查询 各个地区的平均年龄
select avg(age),address from t_student group by address;可以使用 having 对分组进行条件检索。
查询 平均年龄大于30的地区人数
select address,count(*),avg(age) from t_student group by address having avg(age)>30;查询 最大值小于30 的地区 人数 和 平均年龄
select address,count(*),avg(age),max(age) from t_student group by address having max(age)<30;
查询 人数大于3的地区 最大年龄select max(age) ,address from student GROUP BY address having count(*)>3
查询每个地区 25岁以上人数的数量
select address,count(*) from t_student where age>25 group by address;
注意: 如果使用了group by 那么select后只能跟分组的条件列和聚合函数。
- select id,address,count(*),avg(age),max(age) from t_student group by address having max(age)<30;
-
- 这种是错误的,因为select后跟了id 而id不是分组的条件。
当数据库表数据量比较大 例如: 1000w行数据 如果我们执行 select * from student 此时有可能数据库卡死
如果在java中 有可能内存直接溢出 所以实际开发中 都是分页查询 (部门查询)分页使用: limit
select * from student limit 3,5; //从第3条记录查询 查询5条select * from t_student limit 3,5; -- 从索引为3(第4条记录)查询 查询5条记录。
select * from t_student limit 0,5; -- 从第1条记录查询 查询5条记录。--- 分页:
select * from 表名 limit (n-1)*m,m; -- 查询第n页得m条记录。
--- n表示页码 m:表示每页得条数,
第1页每页显示5条记录: select * from t_student limit 0,5;
第2页每页显示5条记录: select * from t_student limit 5,5;
第3页每页显示5条记录: select * from t_student limit 10,5;
第4页每页显示5条记录: select * from t_student limit 15,5;
sql语法:
select distinct * from 表名
where 条件
group by 分组
having 分组条件
order by 排序
limit 分页。
上面为他的语法结构。顺序不能乱。
mysql执行引擎:执行的顺序
select name as n,age as a from t_student where a>15;
因为再执行where时,还没有执行select 所以人家不知道名字为a的。
找出年龄最大的学生姓名
select stu_name from student where max(stu_age);
1. 分页查询。 limit (m-1)*n,n; m:表示当前页码 n:表示每页显示得条数。
2. 聚合函数:
max(列名): 求出某列得最大值。
min(列名): 求出某列得最小值
sum(列名): 求出某列得和
count(*|列名): 求出该表中得记录数.
avg(列名): 求出某列得平均值
3. 分组查询 group by 列名,列名.. 一般和聚合函数配合使用, having 分组后得条件。4. 再列上可以进行运算。
5. sql执行得优先级:
from > where > group by > 聚合函数 > select > distinct > order by > limit
完结!!!
感谢观看~~~~~
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。