目录
- 一、sql语言的简介和规范
- 四、dml: 数据操作语言
- 1、insert 插入数据
- 2、update 修改数据
- 3、delete 删除数据
- 五、select：数据查询
系列教程
mysql系列之开篇 mysql关系型数据库基础概念
mysql系列之一 mariadb-server安装
mysql系列之二 多实例配置
mysql系列之三 基础篇
mysql系列之五 视图、存储函数、存储过程、触发器
mysql系列之六 用户与授权
mysql系列之七 mysql存储引擎
mysql系列之八 mysql服务器变量
mysql系列之九 mysql查询缓存及索引
mysql系列之十 mysql事务隔离实现并发控制
mysql系列之十一 日志记录
mysql系列之十二 备份与恢复
mysql系列之十三 mysql的复制
mysql系列之十四 mysql的高可用实现
mysql系列之十五 mysql常用配置和性能压力测试
一、sql语言的简介和规范
是一种特定目的程序语言，用于管理关系数据库管理系统（rdbms），或在关系流数据管理系统（rdsms）中进行流处理。
- 20世纪70年代，ibm开发出sql，用于db2
- 1981年，ibm推出sql/ds数据库
- 业内标准微软和sybase的t-sql，oracle的pl/sql
- sql作为关系型数据库所使用的标准语言，最初是基于ibm的实现在1986年被批准的。1987年，“国际标准化组织(iso)”把ansi(美国国家标准化组织) sql作为国际标准。
- sql：ansi sql ——sql-86, sql-89, sql-92, sql-99, sql-03
sql语言的规范
- 在数据库系统中，sql语句不区分大小写(建议用大写)
- 但字符串常量区分大小写
- sql语句可单行或多行书写，以“;”结尾
- 关键词不能跨多行或简写
- 用空格和缩进来提高语句的可读性
- 子句通常位于独立行，便于编辑，提高可读性
- 注释：
- sql标准：
- /* 注释内容 */ 多行注释
- — 注释内容 单行注释，注意有空格
- mysql注释： #
数据库对象的命名规则
– 必须以字母开头
– 可包括数字和三个特殊字符（# _ $）
– 不要使用mysql的保留字
– 同一database(schema)下的对象不能同名
sql语句的分类
ddl: data defination language 数据定义语言
- create, drop, alter
dml: data manipulation language 数据操作语言
- insert, delete, update
dcl：data control language 数据控制语言
- grant, revoke
dql：data query language 数据查询语言
- select
二、数据库操作
1、创建库
create database [if not exists] db_name; 创建数据库
character set ‘character set name’ 设置字符集类型
collate ‘collate name’ 设置排序规则
查看支持所有字符集：show character set;
查看支持所有排序规则：show collation;
mariadb [(none)]> create database if not exists testdb;
2、删除库
我不会
3、查看数据库列表
show databases;
三、表操作
1、创建表
方法一： 直接创建
create table [if not exists] tbl_name (create_definition,…)
mariadb [testdb]> create table if not exists students (id tinyint unsigned auto_increment primary key,name varchar(30) not null,phone char(11),gender enum('m','f'));
方法二： 通过查询现存表创建；新表会被直接插入查询而来的数据
create table [if not exists] tbl_name select_statement
mariadb [testdb]> create table user select user,host,password from mysql.user;
如果只想模仿查询旧表创建一个无记录的表我们可以加入条件 where 0=1;
mariadb [testdb]> create table user2 select user,host,password from mysql.user where 0=1;
方法三： 通过复制现存的表的表结构创建，但不复制数据
create table [if not exists] tbl_name like old_tbl_name
mariadb [testdb]> create table user3 like mysql.user;
2、修改表
alter table tbl_name [alter_specification [, alter_specification] …]
增加属性 add
mariadb [testdb]> alter table students add age tinyint after name;
删除属性 drop
mariadb [testdb]> alter table students drop phone;
修改属性 change, modify
mariadb [testdb]> alter table students change age ages tinyint(2) not null;
mariadb [testdb]> alter table students modify gender enum('m','f');
3、删除表
mariadb [testdb]> drop table user3;
4、查看表
show tables; 列出库中所有的表
desc [db_name.]tb_name; 查看表结构
show create table tbl_name; 查看创建表的命令
show table status like ‘tbl_name’; 查看表状态
show table status from db_name; 查看指定库中所有表状态
show engines; 查看所有存储引擎
四、dml: 数据操作语言
mariadb [testdb]> desc students; #示例表
+--------+---------------------+------+-----+---------+----------------+
| field | type | null | key | default | extra |
+--------+---------------------+------+-----+---------+----------------+
| id | tinyint(3) unsigned | no | pri | null | auto_increment |
| name | varchar(30) | no | | null | |
| ages | tinyint(2) | no | | null | |
| gender | enum('m','f') | yes | | null | |
+--------+---------------------+------+-----+---------+----------------+
1、insert 插入数据
单条记录插入
insert into tb_name (col1,col2,…) values (val1,val2,…);
mariadb [testdb]> insert students(id,name,ages,gender) values (1,'tom',26,'m');
mariadb [testdb]> insert students(name,ages,gender) values ('jerry',19,'m');
mariadb [testdb]> insert students(name,ages,gender) values ('maria',19,'m');
mariadb [testdb]> insert students set name='ouyangfeng',ages=56,gender='m';
多条记录插入
insert into tb_name (col1,col2,…) values (val1,val2,…)[,(val1,val2,…),…];
mariadb [testdb]> insert students(name,ages,gender) values ('xiaolongnv',18,'f'),('dongfangbubai',28,'f');
mariadb [testdb]> select * from students; +----+---------------+------+--------+ | id | name | ages | gender | +----+---------------+------+--------+ | 1 | tom | 26 | m | | 2 | jerry | 19 | m | | 3 | maria | 19 | m | | 4 | xiaolongnv | 18 | f | | 5 | dongfangbubai | 28 | f | | 6 | ouyangfeng | 56 | m | +----+---------------+------+--------+
从其他表查询数据保存到此表中
mariadb [testdb]> alter table students add address text; #加个字段做测试用
mariadb [testdb]> insert students(name,address) select user,host from mysql.user;
mariadb [testdb]> select * from students; +----+---------------+------+--------+-----------+ | id | name | ages | gender | address | +----+---------------+------+--------+-----------+ | 1 | tom | 26 | m | null | | 2 | jerry | 19 | m | null | | 3 | maria | 19 | m | null | | 4 | xiaolongnv | 18 | f | null | | 5 | dongfangbubai | 28 | f | null | | 6 | ouyangfeng | 56 | m | null | | 7 | root | 0 | null | 127.0.0.1 | | 8 | root | 0 | null | ::1 | | 9 | | 0 | null | centos7 | | 10 | root | 0 | null | centos7 | | 11 | | 0 | null | localhost | | 12 | root | 0 | null | localhost | +----+---------------+------+--------+-----------+
2、update 修改数据
update tbl_name set col1=value1,col2=value2,… where col=value;
mariadb [testdb]> update students set gender='f' where id=3;
3、delete 删除数据
mariadb [testdb]> delete from students where name=''; #删除名字为空的记录 mariadb [testdb]> truncate table user; #情况表记录
注意：一定要有限制条件（where | limit），否则将修改所有行的指定字段
五、select：数据查询
- as：别名
- where：指明过滤条件以实现“选择”的功能
- +, -, *, /, %：算术操作符
- =, !=, <>, >, <, >=, <=：比较操作符
- between min_num and max_num：在min_num和max_mun之间
- in (element1,element2,…)：在element…中的
- is null：为空
- is not null：不为空
- like：做匹配，像。。。
%：任意长度的任意字符
_：单个任意字符 - rlike：正则表达式，不建议用
- regexp：同上
- not, and, or, xor：逻辑操作符
- group by：根据指定的条件把查询结果进行“分组”以用于做“聚合”运算
- avg() 平均数
- max() 最大数
- min() 最小数
- count() 统计
- sum() 求和
- having ：对分组聚合运算后的结果指定过滤条件。类似where的作用，但只能在分组中使用
- order by：排序
- asc：正序，默认
- desc：倒序
- -keyword：在排序时在关键字前加-可以避免把null排在前边
- limit [[offset,]row_count]：对查询的结果进行输出行数数量限制
1、选择
mariadb [testdb]> select * from students where name='maria'; #查询maria的信息
mariadb [testdb]> select * from students where id between 2 and 5; #查询2到5号学生的信息
mariadb [testdb]> select * from students where name in ('jerry','xiaolongnv'); #查询jerry和xiaolongnv的信息
mariadb [testdb]> select * from students where gender is not null; #查询年龄不为空的信息
mariadb [testdb]> select * from students where name like '%o%'; #查询姓名中包含'o'的信息
2、投影
mariadb [testdb]> select user as 用户,host as 主机,password as 密码 from mysql.user;
3、分组
mariadb [testdb]> select gender,avg(ages) from students group by gender; #查询男生、女生年龄的平均值 mariadb [testdb]> select gender,avg(ages) from students group by gender having gender='m'; #只显示男生的平均年龄信息
4、排序
mariadb [testdb]> select * from students order by ages desc; #按年龄排序，倒序显示 mariadb [testdb]> select * from students where ages > 0 order by ages limit 3; #按年龄排序，过滤年龄大于0的，正序排序，取前三条记录
六、多表查询
为了练习，我们将表在扩展一下
mariadb [testdb]> delete from students where id between 7 and 12; mariadb [testdb]> create table score (id tinyint(2) unsigned auto_increment primary key,score tinyint(3)); mariadb [testdb]> alter table students add sid tinyint(2); mariadb [testdb]> update students set sid=6 where id=6; mariadb [testdb]> insert score set score=87; mariadb [testdb]> select * from students; +----+---------------+------+--------+---------+------+ | id | name | ages | gender | address | sid | +----+---------------+------+--------+---------+------+ | 1 | tom | 26 | m | null | 1 | | 2 | jerry | 19 | m | null | 2 | | 3 | maria | 19 | f | null | 3 | | 4 | xiaolongnv | 18 | f | null | 4 | | 5 | dongfangbubai | 28 | f | null | 5 | | 6 | ouyangfeng | 56 | m | null | 6 | +----+---------------+------+--------+---------+------+ mariadb [testdb]> select * from score; +----+-------+ | id | score | +----+-------+ | 1 | 99 | | 2 | 98 | | 3 | 88 | | 4 | 68 | | 5 | 78 | | 6 | 87 | +----+-------+
join on：交叉连接
inner join on：内连接
left outer join on：左外连接
right outer join on：右外连接
union on：完全外连接
mariadb [testdb]> select * from students as s,score as o where s.sid=o.id; #俩张表取交集
1、交叉连接
mariadb [testdb]> select * from students join score;
2、内连接
mariadb [testdb]> select t.name,s.score from students as t inner join score as s on t.sid=s.id; +---------------+-------+ | name | score | +---------------+-------+ | tom | 99 | | jerry | 98 | | maria | 88 | | xiaolongnv | 68 | | dongfangbubai | 78 | | ouyangfeng | 87 | +---------------+-------+
3、外连接
mariadb [testdb]> select t.name,s.score from students as t left join score as s on t.sid=s.id; #左外连接 +---------------+-------+ | name | score | +---------------+-------+ | tom | 99 | | jerry | 98 | | maria | 88 | | xiaolongnv | 68 | | dongfangbubai | 78 | | ouyangfeng | 87 | +---------------+-------+
mariadb [testdb]> select * from students as t right join score as s on t.sid=s.id; #右外连接
4、完全外连接
mariadb [testdb]> select name,address from students
-> union
-> select user,host from mysql.user;
+---------------+-----------+
| name | address |
+---------------+-----------+
| tom | null |
| jerry | null |
| maria | null |
| xiaolongnv | null |
| dongfangbubai | null |
| ouyangfeng | null |
| root | 127.0.0.1 |
| root | ::1 |
| | centos7 |
| root | centos7 |
| | localhost |
| root | localhost |
+---------------+-----------+
5、自连接
mariadb [testdb]> alter table students add tid tinyint(2); #再加一个tid字段 mariadb [testdb]> select * from students; +----+---------------+------+--------+---------+------+------+ | id | name | ages | gender | address | sid | tid | +----+---------------+------+--------+---------+------+------+ | 1 | tom | 26 | m | null | 1 | 2 | | 2 | jerry | 19 | m | null | 2 | 1 | | 3 | maria | 19 | f | null | 3 | 4 | | 4 | xiaolongnv | 18 | f | null | 4 | 5 | | 5 | dongfangbubai | 28 | f | null | 5 | 4 | | 6 | ouyangfeng | 56 | m | null | 6 | 4 | +----+---------------+------+--------+---------+------+------+
mariadb [testdb]> select s1.name as studentname,s2.name as teachername from students as s1 inner join students as s2 on s1.id=s2.tid; +---------------+---------------+ | studentname | teachername | +---------------+---------------+ | jerry | tom | | tom | jerry | | xiaolongnv | maria | | dongfangbubai | xiaolongnv | | xiaolongnv | dongfangbubai | | xiaolongnv | ouyangfeng | +---------------+---------------+
七、子查询
子查询：在查询语句嵌套着查询语句，性能较差，基于某语句的查询结果再次进行的查询
1、用在where子句中的子查询
用于比较表达式中的子查询；子查询仅能返回单个值
mariadb [testdb]> select name,ages from students where ages > (select avg(ages) from students); #查询大于平均年龄的同学
用于in中的子查询：子查询应该单键查询并返回一个或多个值从构成列表
2、用于from子句中的子查询
select tb_alias.col1,… from (select clause) as tb_alias where clause;
八、数据类型
选择正确的数据类型对于获得高性能至关重要，三大原则：
- 更小的通常更好，尽量使用可正确存储数据的最小数据类型
- 简单就好，简单数据类型的操作通常需要更少的cpu周期
- 尽量避免null，包含为null的列，对mysql更难优化
1、数值型
精确数值
- int
- tinyint 微整型 1
- smallint 小整型 2
- mediumint 中整型 3
- int 整型 4
- bigint 大整型 8
- decimal 精确定点型
近似数值
- float 单精度浮点型 4
- double 双精度浮点型 8
- real
- bit
2、字符型
定长
– char（不区分大小写）255
– binary（区分大小写）
变长
- varchar（不区分大小写）65,535
- varbinnary（区分大小写）
text（不区分大小写）
- tinytext 255
- text 65,535
- mediumtext 16,777,215
- longtext 4,294,967,295
blob（区分大小写）
- tinyblob 微二进制大对象 255
- blob 二进制大对象 64k
- mediumblob 中二进制大对象 16m
- longblob 长二进制大对象 4g
enum 枚举 65535种变化
set 集合 1-64个字符串，可以随意组合
3、日期时间型
- date 3
- time 3
- datetime 8
- timestamp 4
- year{2|4} 1
4、布尔型
- bool，boolean：布尔型，是tinyint(1)的同义词。zero值被视为假。非zero值视为真。
参考官方文档：
总结
到此这篇关于sql语法的文章就介绍到这了,更多相关sql语法内容请搜索www.887551.com以前的文章或继续浏览下面的相关文章希望大家以后多多支持www.887551.com！