一、DBMS SQLyogEnt.exe的使用
1、创建数据库实例
①、利用管理菜单创建
②、利用SQL创建
CREATE DATABASE yhsq1;
2、创建表
CREATE TABLE student(
stu_code VARCHAR(20),
stu_name VARCHAR(20),
stu_age INT,
stu_sex CHAR(2)
);
二、数据库实例中的对象
1、tables:二维表
2、Views:虚拟的二维表
3、Stored Procs:存储过程
4、Function:函数
5、Triggers:触发器
6、Events:时间
三、MySQL数据类型
数据类型 | 描述 | 字节 | 推荐使用 |
SMALLINT | 整数,-32000到32000 | 2 | 存储相对比较小的整数,比如年龄 |
INT | 从-2000000000到2000000000 | 4 | 存储中等整数,比如距离 |
BIGINT | INT不能描述的较大整数 | 8 | 存储超大整数,比如科学数据 |
FLOAT | 单精度浮点 | 4 | 存储小数数据,例如测量、温度 |
DOUBLE | 双精度浮点 | 8 | 需要双精度存储的数据,例如科学计数 |
DECIMAL | 用户自定义精度的浮点类型 | 变量,取决于精度与长度 | 以非常高的精度保存小数数据,比如货币数额、科学计数 |
CHAR | 固定长度的字符串 | 特定字符串长度(高达255个字符) | 存储通常包含预定义字符串的变量,例如定期航线、邮编等 |
VARCHAR | 具有最大限度的可变长字符串 | 变量,1+实际字符串长度 | 存储不同长度的字符串,比如名字、密码、短文等 |
TEXT | 没有最大长度限制的可变长度字符串、大文本数据类型 | 2+实际字符串长度 | 例如:新闻故事、产品描述等 |
BLOB | 二进制字符串 | 2+实际字符串长度 | 存储二进制数据,例如图片、文件 |
DATE | 以yyyy-mm-dd格式的日期 | 3 | 存储日期 |
TIME | 以hh:mm:ss格式的时间 | 3 | 存储时间或时间间隔 |
DATETIME | 以yyyy-mm-dd hh:mm:ss格式存储时间 | 8 | 存储包含日期和时间的数据 |
四、SQL(重点)Structured Query Language
DDL、DML、DQL、DCL
①、DDL(Data Definition Language):数据定义语言
操作对象:数据库、表
创建数据库:CREATE DATABASE yhsq1;
创建表:
CREATE TABLE student(
stu_code VARCHAR(20),
stu_name VARCHAR(20),
stu_age INT,
stu_sex CHAR(2)
);
修改表:
删除表中的列:ALTER TABLE student DROP stu_sex;
增加列:ALTER TABLE student DROP stu_sex;
1:删除列
ALTER TABLE 【表名字】 DROP 【列名称】
2:增加列
ALTER TABLE 【表名字】 ADD 【列名称】 INT NOT NULL COMMENT '注释说明'
3:修改列的类型信息
ALTER TABLE 【表名字】 CHANGE 【列名称】【新列名称(这里可以用和原来列同名即可)】 BIGINT NOT NULL COMMENT '注释说明'
4:重命名列
ALTER TABLE 【表名字】 CHANGE 【列名称】【新列名称】 BIGINT NOT NULL COMMENT '注释说明'
5:重命名表
ALTER TABLE 【表名字】 RENAME 【表新名字】
②、DML:数据操作语言
操作对象:表中的记录
增加数据
insert into book(id,b_code,b_name,b_author,b_publish,b_prce,b_pubdate)values(14,'Z_001','《斗破苍穹》','天蚕土豆','甘肃人民出版社',23,'2010-01-03');
修改数据
update book set b_prce = 34,b_code = 'Z-002',b_author='邵高声' where id =14;
删除数据
delete from book where id = 14;
③、DQL:数据查询语言
-- 1、查询所有的书籍信息
SELECT * FROM book;
-- 2条件子句 where
SELECT * FROM book WHERE id = 12;
-- 3.or、and
SELECT * FROM book WHERE b_prce = 45 OR b_prce = 34
SELECT * FROM book WHERE b_author = '邵高昇' AND b_prce= 345;
-- 4.排序查询order by , 默认是ASC(升序)、 DESC(降序)
SELECT * FROM book ORDER BY b_prce DESC
-- 5.distinct去重查询
SELECT DISTINCT(b_publish) FROM book;
-- 6.区间查询 between .... and
SELECT * FROM book WHERE b_pubdate BETWEEN '2010-01-01' AND '2012-12-31'
-- 7.集合查询 in 和 not in
SELECT * FROM book WHERE b_publish IN('河北人民出版社','甘肃人民出版社')
SELECT * FROM book WHERE b_publish NOT IN('河北人民出版社','甘肃人民出版社')
-- 8.模糊查询 like
SELECT * FROM book WHERE b_author LIKE '邵%'
SELECT * FROM book WHERE b_author LIKE '邵__'
SELECT * FROM book WHERE b_author LIKE '%高%'
-- 9.空查询和非空查询(IS NULL IS NOT NULL)
SELECT * FROM borrow WHERE re_date IS NULL
SELECT * FROM borrow WHERE re_date IS NOT NULL
-- 10.聚合函数(COUNT(*) SUM(),MAX(),MIN(),AVG())
SELECT COUNT(*) AS num FROM book;
SELECT SUM(b_prce) FROM book;
SELECT SUM(b_prce) AS price FROM book WHERE b_name='《红楼梦》';
SELECT MAX(b_prce) FROM book;
SELECT MIN(b_prce) FROM book;
SELECT AVG(b_prce) FROM book;
-- 11.分页查询(LIMIT)
SELECT * FROM book LIMIT 3,3
-- 12.分组 统计group by having
SELECT b_publish,COUNT(*) AS num FROM book GROUP BY b_publish HAVING num >= 2
五、数据库完整性约束
为了防止不符合规范的数据进入数据库,在用户对数据进行插入、修改、删除等操作时,
DBMS自动按照一定的约束条件对数据进行监测,使不符合规范的数据不能进入数据库,以确保数据库中存储的数据正确、有效、相容。
1、实体完整性:主键约束(唯一性+非空)
a、什么是主键:为了实现表中每个元祖(行)的唯一性 要指定某个不能有重复值的字段为主b、注意点:
1、一般情况下给每个表专门设置一个字段id 作为主键
(该字段没有实际的业务含义 在表中只起到主键的作用)
2、某些不能重复的字段也可以定义为表的主键 如:身份证号码 书籍编号 学号等。 键(primary key)
c、主键的创建方式:
在DBMS中添加主键
利用sql语句添加主键 auto_increment
2、参照完整性:外键约束(两种实现方式)
建立外键关系:
a、什么是外键:
A表的主键是B表的字段,该字段叫做外键,A表叫做主表,B表叫做从表
b、注意点:
1、当前表的外键必须引的是参照表的主键
2、当前表的外键字段和参照表的主键字段类型 长度必须一致
3、外键表中的数据要在参照表中存在
4、当前表中如果引了多个外键,外键名不能重复
c、外键的创建方式:
在DBMS中添加外键:
利用SQL语句增加外键:
ALTER TABLE grade ADD FOREIGN KEY FK_NAME(sid) REFERENCES student(id)
ALTER TABLE borrow ADD FOREIGN KEY FK_borrow_b_id(b_id) REFERENCES book(id)
ALTER TABLE borrow ADD FOREIGN KEY FK_borrow_r_id(r_id) REFERENCES reader(id)
CREATE TABLE bo(
id INT PRIMARY KEY AUTO_INCREMENT,
b_id INT,
r_id INT,
FOREIGN KEY FK_bo_b_id(b_id) REFERENCES book(id),
FOREIGN KEY FK_bo_r_id(r_id) REFERENCES reader(id)
);
3、用户自定义完整性
六、多表联合查询
1、链接查询:
内链接和外链接查询
①、内连接(...[inner] join..on):得到交集(公共部分)
②、外链接:(得到的并集)
左外链接(...left [outer] join....on)
右外连接(...right [outer] join....on)
2、嵌套查询:
汽车信息存储:品牌、系列、类型、基本信息(配置、图像....)
表:mark、xl、type、car_info、car_img
1、mark:id name commont status
2、Xl:id name comment status
3、type:id name comment status
4、car_info:id mark_id xl_id type_id data comment status
5、Car_img:id img_name car_id comment status