结构化查询语言(SQL)是专门用来与数据库通信的语言,它可以帮助用户操作 关系数据库。
--- --- --- --- --- --- --- --- ---- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---- --- --- ---
SQL的特点:
1、SQL不是某个特定的数据库供应商专有的语言
2、SQL简答易学
3、SQL强大、灵活,可以进行非常复杂和高级的数据库操作
--- --- --- --- --- --- --- --- ---- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---- --- --- ---
数据定义语言DDL:create 、 alter、 drop (对象是数据库或数据库对象)
数据操纵语言DML : select 、insert 、 update 、 delete (对象是数据)
数据控制语言DCL:grant 、 revoke
--- --- --- --- --- --- --- --- ---- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---- --- --- ---
常量:字符串常量,数值常量,十六进制常量,时间日期常量,位字段值,布尔值,NULL值
用户变量:用户变量前常添加一个符号@,用于将其与列名分开。
系统变量:大多数系统变量应用与其他SQL语句中时,必须在系统变量前添加来给两个@
运算符
表达式
内置函数
--- --- --- --- --- --- --- --- ---- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---- --- --- ---
数据库模式定义
创建数据库
create database mysql_test ;
选择数据库
use mysql_test ;
修改数据库
alter database mysql_test
default character set gb2312
default collate gb2312_chinese_ci;
删除数据库
drop database mysql_test ;
或者 drop database if exists mysql_test ;
查看数据库
show mysql_test ;
--- --- --- --- --- --- --- --- ---- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---- --- --- ---
数据定义
- 1、创建表
字段名 数据类型 是否为空 默认值
- 2、更新表
增加一个字段
alter table mysql_customers
add column cust_city char(10) not null default 'Wuhan' after cust_sex ;
修改表中列的名称或数据类型
alter table mysql_customers
change column cust_sex char(1) null default 'M' ;
修改或删除表中指定列的默认值
alter table mysql_customers
alter column cust_city set default 'Beijing' ;
修改指定列的数据类型,不会干涉他的列名
alter table mysql_customers
modify column cust_name char(20) first ;
删除某一列数据
alter table mysql_customers
drop column cust_contact ;
更新表名 第一种方法
alter table mysql_customers
rename to mysql_test backup_customers ;
更新表名 第二种方法
rename table 旧表名 to 新表名 ;
- 3、删除表
drop table 表名
- 4、查看表
show columns from mysql_test ;
--- --- --- --- --- --- --- --- ---- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---- --- --- ---
索引是提高数据文件访问效率的有效方法
索引存在的弊端:
1、缩影是以文件的形式存储的,如果有大量的索引,索引文件可萌比数据文件更快达到最大的文件尺寸。
2、索引在提高查询速度的同事,会降低更新表的速度。
普通索引 index或key
唯一性索引 unique (候选码 多个)
主键 primary key 1个
创建索引 第一种方法
create index index_customers
on mysql_test customers(cust_name(3) asc) ;
创建索引 第二种方法 : 使用create table 语句创建
create table seller(
seller_id int not null ,
seller_name char(50) not null ,
seller_address char(50) null ,
seller_contact char(50) null ,
product_type int(5) null ,
sales int null ,
primary key(seller_id,product_type), --创建表的同时创建表的主键
index (unique)index_seller(sales) --创建表的同时创建表的(唯一)索引
foreing key(外键名) --创建外键
);
添加索引
alter table mysql_test.seller
add index index_seller_name(seller_name) ;
查看索引
show index from mysql_test.seller ;
删除索引 第一种方法
drop index 索引名 on mysql_test.seller ;
删除索引 第二种方法
1)选用drop primary key 子句英语删除各种类型的索引,由于一个表只有有个主键,其也是一个索引;
2)选用drop index 子句用于删除各种类型的索引;
3)选用drop foreign 子句用于删除外键。
alter table mysql_test.customers
drop primary key,
drop index index_customers; ---主键不能随便删
--- --- --- --- --- --- --- --- ---- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---- --- --- ---
数据更新
插入数据
insert...values... 插入单行或多行
insert into mysql_test.customer
values(901,'张三',‘F’,'北京市',‘朝阳区’),
(.......),
(.......).......; --- 多行继续加
insert...set... 插入部分列值数据
insert into mysql_test.customers set cust_name='lisi',cust_city='wuhan' ;
insert...select 插入子查询数据
insert into table_name ('','','',) select ......
删除数据
delete from mysql_test.customers where cust_name = 'lisi' ;
修改数据
update mysql_test.customers set cust_address = 'wuan' where cust_name = 'zhangsan' ;
--- --- --- --- --- --- --- --- ---- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---- --- --- ---
替换语句结构集中的数据
--- --- --- --- --- --- --- --- ---- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---- --- --- ---
聚合函数
--- --- --- --- --- --- --- --- ---- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---- --- --- ---
交叉查询
内连接
select * from tb_student inner join tb_sorce on tb_studentNo = tb_sorceNo
外连接
select * from tb_student left join tb_sorce on tb_studentNo = tb_sorceNo
比较运算符
between...and...限定范围
select * from mysql_test.customers where cust_id between 903 and 912 ;
使用关键字“IN”可以指定一个值得枚举表,该表中会列出所有可能的值
select * from mysql_test.customers where cust_id in (903,906,908);
判定空值
select * from mysql_test.customers where cust_contract is null ;
子查询(一定是两个表格才会涉及到子查询)----会考
查询任意所选课程成绩高于80分的学生的学号和姓名信息
select studentNo,studentName from tb_student where studentNo in (select studentNo from tb_score where score>80) ;
exists
子查询的结果集不为空,则返回true,否则返回false。
group by
having 子句
order by 子句
排序 asc 正序/ desc 倒序
limit 子句
--- --- --- --- --- --- --- --- ---- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---- --- --- ---
视图
视图不是数据库中真实的表,而是一张虚拟表,其自身并不存储数据。
视图优点
集中分散数据,简化查询语句,重用sql语句,保护数据安全,共享所需数据,更改数据格式
创建视图
删除视图
drop view 视图名;
修改视图
查看视图
show create view 视图名;
使用insert 语句通过视图向基本表插入数据
insert into mysql_test_customers_view values(909,'周明','M','武汉市','洪山区');
通过视图修改基本表的数据