Mysql 语法
-
三大范式
-
第一范式
目标是确保每列的原子性
-
第二范式
在第一范式的基础上,目标是确保表中的每列都和主键相关
-
第三范式
在第二范式的基础上,目标是 确保每列都和主键列直接相关,而不是间接相关
2.E-R图(实体关系图)
-
矩形表示实体集 //数据信息(表、表内的一行信息)
-
椭圆形表示属性 //实体的特征(属性对应表中的列)
-
菱形表示联系集 //实体间的关联关系
-
直线用来连接实体集与属性、实体集与联系集
3.映射基数
-
一对一
-
一对多
-
多对一
-
多对多
数据库操作
1.创建数据库
CREATE DATABASE 名称;
提示 : Query OK,1 row affected <0.00 sec>
-
Query OK 表示执行成功
-
1 row affected 表示执行行数
-
<0.00 sec> 表示操作执行时间
2.查看数据库列表
show databases;
3.选择数据库
use 数据库名; eg: use myschool;
4.创建数据表
CREATE TABLE IF NOT EXISTS `runoob_tbl`( `runoob_id` INT UNSIGNED AUTO_INCREMENT, `runoob_title` VARCHAR(100) NOT NULL, `runoob_author` VARCHAR(40) NOT NULL, `submission_date` DATE, PRIMARY KEY ( `runoob_id` ) )ENGINE=InnoDB DEFAULT CHARSET=utf8;
5.插入数据‘
INSERT INTO runoob_tbl -> (列名, runoob_author, submission_date) -> VALUES -> ("学习 PHP", "菜鸟教程", NOW());
6.查询数据
select * from 表名;
7.删除表
DROP TABLE 表名
8.删除数据库
drop databfase 数据库名
表的操作
1.修改表名
ALTER TABLE 旧表名 RENAME TO 新表名 eg:ALTER TABLE student RENAME TO student1
2.添加字段
ALTER TABLE 表名 ADD 字段名 数据类型 【属性】 ALTER TABLE student ADD pass
3.修改字段
ALTER TABLE 表名 CHANGE 原字段名 新字段名 数据类型 【属性】;
4.修改表列名
Alter table 表名 CHANGE COLUMN '旧列名' '新列名' 类型
5.删除字段
ALTER TABLE 表名 DROP 字段名 ; ALTER TABLE student DROP studentId;
6.要删除一个表中的列
使用语法
alter table 表名 drop column 列名
7.添加主键
ALTER TABLE 表名 ADD CONSTRAINT 主键名 PRIMARY KEY 表名 (主键字段); // 将student 表中的studentId设置为主键 ALTER TABLE student ADD CONSTRAINT pk_student PRIMARY KEY student(studentId);
8.设置外键
ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (外键字段) REFERENCES 关联表名(关联字段); // 设置student 表的studentId字段与 stu表的stuId字段县里主外键关联 ALTER TABLE student ADD CONSTRAINT fk_student_stu FOREIGN KEY (stuId) REFERENCES stu(stuId);
存储引擎
名称 | InnoDB | MyISAM |
---|---|---|
事务处理 | 支持 | 不支持 |
数据行锁定 | 支持 | 不支持 |
外键约束 | 支持 | 不支持 |
全文索引 | 不支持 | 支持 |
表空间大小 | 较大,约两倍 | 较小 |
- character_set_server:默认的内部操作字符集 - character_set_client:客户端来源数据使用的字符集 - character_set_connection:连接层字符集 - character_set_results:查询结果字符集 - character_set_database:当前选中数据库的默认字符集 -character_set_system:系统元数据(字段名等)字符集
常用约束
常用的属性约束 # not null 非空约束 # default 默认约束 # unique key 唯一约束 # primary key 主键约束 # foreign key 外键约束 # auto_increment 自动增长
DML语句(增删改)
1.插入单行数据
insert into 表名(字段名列表) values(值列表); insert into student (studentNo,studentNmae...) values (1,“张三”);
2.插入多行数据
insert into 新表(字段列表) values (值列表1),(值列表2); insert into subject (subjectNO,subjectName) values (1,"张三"),(2,"李四"),(3,"王五");
3.将查询结果插入到新表
insert into 新表(字段1,字段2.。。) select 字段1,字段2.。 from 原表; create table 新表(select studentName,phone from 原表);
4.修改数据
update 表名 set 字段=值,字段=值...where 条件; update student set studentNo=1,studentName="张三" where studentsex="男";
5.删除数据
delete from 表名 where 条件; delete from student where studentNmae="张三"; truncate table 表名; truncate table student; // 删除student 中所有数据
DQL语句
SELECT<字段名列表> FROM<表名或试图> WHWEW<查询条件> GROUP BY <分组的字段名> HAVING <条件> ORDER BY <排序的字段名> ASE 或者DESC;
-
字段名列表是要查询的字段名 多个字段名间用逗号(,)隔开,若查询全表的字段 ,则使用*代表
-
From 后的表名为要查询的来源,可以是单个或多个
-
Where 子句可选 ,指定查询条件
-
GROUP BY 子句表名查询出来的数据指定字段进行分组
-
HAVING 子句用于筛选组
-
ORDER BY 子句指定按什么顺序显示查询出来数据,升序(Ase)降序(DESC);
LIMIT(显示行数)
SELECT<字段名列表> FROM<表名或试图> WHWEW<查询条件> GROUP BY <分组的字段名> ORDER BY <排序的字段名> ASE 或者DESC; LIMIT 位置偏移量,行数; // 查询五行数据 limit 5; //从第三行开始输出五行数据 limit 2,5;
常用函数
AVG() | 返回平均值 |
---|---|
COUNT() | 返回某字段的行数 |
MAX() | 返回最大值 |
MIN() | 返回最小值 |
SUM() | 字段和 |
1常用日期函数
函数名 | 作用 | 举例 |
---|---|---|
CURDATE() | 获取当前日期 | SELECT CURDATE 返回当前日期 |
CURTIME() | 获取当前时间 | SELECT CURTIME(); 返回当前时间 |
NOW() | 获取当前日期和时间 | SELECT NOW() |
2.数学函数
函数名 | 作用 | 举例 |
---|---|---|
CEIL(x) | 返回大于或等于数值x的最小值 | SELECT CEIL(2.3)返回 3 |
FLOOR(x) | 返回小于或等于数值x的最大整数 | SELECT FLOOR(2.3) 返回2 |
RAND() | 返回0~1间的随机数 | SELECT RAND() 返回 0.123123213 |
子查询
子查询用于为主查询返回其所需数据,或者对检索数据进行进一步的限制。
子查询可以在 SELECT、INSERT、UPDATE 和 DELETE 语句中,同 =、<、>、>=、<=、IN、BETWEEN 等运算符一起使用。
使用子查询必须遵循以下几个规则:
-
子查询必须括在圆括号中。
-
子查询的 SELECT 子句中只能有一个列,除非主查询中有多个列,用于与子查询选中的列相比较。
-
子查询不能使用 ORDER BY,不过主查询可以。在子查询中,GROUP BY 可以起到同 ORDER BY 相同的作用。
-
返回多行数据的子查询只能同多值操作符一起使用,比如 IN 操作符。
-
SELECT 列表中不能包含任何对 BLOB、ARRAY、CLOB 或者 NCLOB 类型值的引用。
-
子查询不能直接用在聚合函数中。
-
BETWEEN 操作符不能同子查询一起使用,但是 BETWEEN 操作符可以用在子查询中。
示例:
student 表
ID | NAME | AGE | ADDRESS | SALARY |
---|---|---|---|---|
1 | Ramesh | 35 | Ahmedabad | 2000.00 |
2 | 张三 | 25 | Delhi | 1500.00 |
3 | Khilan | 23 | kaushik | 6500.00 |
4 | kaushik | 34 | Chaitali | 4500.00 |
5 | Chaitali | 24 | Mumbai | 8500.00 |
select * from student where iD iN ( select ID from student where SALARY>4500 )
结果是:
ID | NAME | AGE | ADDRESS | SALARY |
---|---|---|---|---|
3 | Khilan | 23 | kaushik | 6500.00 |
5 | Chaitali | 24 | Mumbai | 8500.00 |
EXISTS 子查询
SELECT ....FROM 表名 Where EXISTS (子查询);
NOT EXISTS 子查询
exists 和 in 一样 同样允许添加NOT关键字实现取反操作 NOT EXISTS 表示不存在
表链接
合并多个数据表中的字段
select 查询列表 from 表名1 inner join 表名2 on 表1.列表1=表2.列表2 #列表1与列表2必须相对应
事务
事务是一种机制,一个操作序列,包含了数据库操作命令,并且把所有的命令作为一个整体一起向系统提交或撤销操作请求,即这一组数据库命令要么都执行,要么都不执行
开启事务有两种方式,一种是START TRANSACTION,另一种是BEIGN
事务的4个特性:(ACID)
-
原子性
-
一致性
-
隔离性
-
隔离级别:
未提交读(READ UNCOMMITTED)脏读 已提交读(READ COMMITTED)不可重复读 可重复读(REPEATABLE READ)mysql默认的隔离级别
-
持久性
执行事务的语句
#开始事务 begin;或者 start transaction #提交事务 commit; #回滚(撤销)事务 rollback;
设置自动提交关闭或开始
set autocommit=0 / 1; #值为0:关闭自动提交 ,从下一条SQL语句开始则开启新的事务,需手动提交(commit)或回滚(rollback)事务 #值为1:开启自动提交
视图
-
为什么需要视图?
不同身份的用户所关注的数据库可能有所不同,可以把复杂的查询结果保存为视图
-
什么是视图?
视图是另一种查看数据库中一个或多个表中数据的方法
-
视图给用户和开发人员的好处
对最终用户的好处:
1.结果更容易理解
2.获得数据更容易
对开发人员的好处:
1.限制数据检索更容易
2.维护应用系统更方便
-
如何创建和使用视图
#创建视图 create view 视图名 //视图名一般为view_xxx或v_xxx as <select语句> #删除视图 drop view 【if exists】视图名 #查看视图数据 select * from 视图名 select 字段1,字段2,...... from 视图名
-
使用视图的注意事项
1.每个视图可以使用多个表
2.与查询相似,一个视图可以嵌套另一个视图,但最好不要超过三层
3.对视图数据进行添加,更新和删除操作直接引用表中的数据
4.当视图数据来自多个表时,不允许添加和删除数据
-
查看所有视图的相关信息
use unformation_schema; select * from view\G;
索引
-
索引的作用
通过使用索引,大大提高数据库的检索速度,改善数据库性能
-
索引分类
1.普通索引
2.唯一索引 unique 不允许重复值允许空值
3.主键索引
4.复合索引
5.全文索引 fulltext
6.空间索引 spatial
-
使用索引的语句
#创建索引 create 【 unique(唯一)、 fulltext(全文索引)、 spatial(空间索引) 】 index 索引名 on 创建索引的表名 (指定需要创建索引的列【索引长度】...); #只有字符串类型才能指定索引长度 #删除索引 drop index 索引名 on 创建索引的表名 #查看索引 show index from 创建索引的表名
备份和恢复
-
使用mysqldump命令备份数据库
mysqldump -u username -h host -ppassword dbname【tbname1【,tbname2......】】>filename.sql #username---用户名 #host---登录用户的主机名称,如本机为主机可省略 #password---登录密码 #dbname---需要备份的数据库 #tbname---需要备份的数据表,可指定多个表,如备份整个表则省略 #filename.sql---备份文件的名称 #获得当前版本的完整选项列表 mysqldump --help
-
使用mysql命令恢复数据库
mysql -u username -p 【dbname】<filename.sql #username---用户名 #dbname---数据库名 #filename.sql---数据库备份后的文件名称 source filename; 如果已经登录了MySQL服务器,也可以使用source命令恢复数据库 #filename---数据库备份文件名
-
表数据导出到文本文件
select 列表 from 表名 【where】 into outfile '导出到的文件路径' 【 option 】
-
文本文件导入到数据表
load data infile 导入的文件路径 into table 表名 【 option 】
用来操作数据库中所包含的数据:INSERT,UPDATE,DELETE 用于创建和删除数据库对象等操作:CREATE,DROP,ALTER 用来对数据库中的数据进行查询:SELECT 用来控制数据库组件的存取许可和存取限权等:GRANT,COMMOT,ROLLBACK