首页 > 代码库 > 数据库技能实战进阶之常用结构化sql语句（上）
数据库技能实战进阶之常用结构化sql语句（上）
常用的结构化查询语言主要分为数据定义语言（DDL）、数据操作语言（DML）、数据控制语言（DCL）和数据查询语言（DQL）。特别在关系型的数据库例如（mysql、mariadb、 percona、DB2、Oracle、SQL server）等都是采用共同的SQL语句来实现增删改查等数据的管理。本文会针对以下的四种类型的结构化SQL来进行介绍。
DDL 数据定义语言 create drop alter DML 数据操作语言 insert delete update DCL 数据控制语言 grant commit rollback DQL 数据查询语言 select
一、 create与drop 简介
首先create 与drop 是直接可以对数据库进行操作，例如创建数据库、删除数据库。简单示例如下，
CREATE DATABASE IF NOT EXISTS DBMS default character set utf8; #创建一个名为DBMS默认字符集为UTF8的数据库 SHOW databases; #显示当前数据库管理系统里面所有的数据库，就可以看到我们当前创建的DBMS库 Drop database DBMS; #删除名为DBMS的数据库
其次，create 和drop能对表进行操作，和对库的操作一致 将database 更改为table，即可创建和删除操作。后面我们要联系增删改查，所以创建以下表SQL如下：
create table if not exists user ( id int unsigned not null auto_increment primary key, username varchar(30) not null, password varchar(30) not null ); 或者使用下方SQL。其实原理和表结构是一样，只是呈现方式不一样 CREATE TABLE if not exists `user` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `username` varchar(30) NOT NULL, `password` varchar(30) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8
二、插入数据 insert
insert 是我们将数据写进数据库最用的方法之一，常见的语法如下：
insert into tablename (COLUMN ) VALUEs( ); #tablename 是我们要插入数据的目标表名，column是列名，values里面对应的是要插入的值，需要强 调的当我们对所有列插入即values（）中对应列与创建表结构的列顺序对应时，可以省略(COLUMN ) insert into tablename VALUEs(‘‘,‘‘,‘‘ ); 例如： insert into user VALUEs(1,‘linux‘,‘linux‘ );但是给id设置了主键自增所以就没必须做， 使用上面的列名对应就可以了 优化后： insert into user (username,password ) VALUE(‘python‘,‘java‘ ) #批量插入数据 我们还可以用 insert into tablename (COLUMN ) VALUEs( 值1)，（值2）...的方式批量插 入效率高很多。 isert into user (username,password ) VALUEs(‘python999‘,334989 ),(‘LInuxmysql‘,‘777‘) 或者 insert into user (username,password ) VALUE(‘python‘,‘java‘ ),(‘LInuxmysql‘,‘777‘) 注意：经测试values和value插入值时一样，需要注意的是，定义的密码是varchar()字符类型， 而3334989是整形，也会插入成功，这里是将整形转换成字符串类型。
将数据插入到数据库的方法除了insert外，还有load data infile （加载文件），以及从其他表查询插入的方法，可以参考之前的文章，甚至可以用navicat之类客户端工具导入。
三、删除数据
删除数据可以分为删除部分和删除全部，删除全部里面又可以分为只删除数据和表和数据一起删除，删除表和数据 ，可以使用上面提到的drop table tablename ，删除全部数据可以用delete from tablename 或者tuncate table。
delete from table 和tuncate table 的区别：
delete 删除后不会刷新主键的值 例如你删除主键ID（3-5），那么下次插入数据就是从5开始。
delete from user #删除所有数据 insert into user (username,password ) VALUE(‘pythonlinux‘,‘javadocker‘ ),(‘LInuxmysql‘,‘redis‘) select * from user;#插入数据再查询 查询所有数据，检查我们的id: select * from user 11 python java 12 LInuxmysql 777 13 pythonlinux javadocker 14 LInuxmysql redis 观察表里面的数据，可以发现第一行的id是从11开始的，这就说明delete删除数据的时候不会刷新自增值 truncate user 再插入数据观察 truncate user#清空所有数据 insert into user (username,password ) VALUE(‘pythonlinux‘,‘javadocker‘ ),(‘LInuxmysql‘,‘redis‘) select * from user;#再插入数据，再查询 1 pythonlinux javadocker 2 LInuxmysql redis truncate 清空表后 再插入数据，自增列从0开始。 删除部分数据先插入多条，检查插入后的数据， insert into user (username,password ) VALUE(‘python‘,‘javadocker‘ ),(‘LInux‘,‘redis‘),(‘linux‘,999) ,(‘redis‘,‘name ‘) 1 pythonlinux javadocker 2 LInuxmysql redis 3 python javadocker 4 LInux redis 5 linux 999 6 redis name 7 python javadocker 8 LInux redis 9 linux 999 10 redis name 11 python javadocker 12 LInux redis 13 linux 999 14 redis name 删除 id >10的部分 delete from user where id >10; 然后再插入数据，查询结果如下： 1 pythonlinux javadocker 2 LInuxmysql redis 3 python javadocker 4 LInux redis 5 linux 999 6 redis name 7 python javadocker 8 LInux redis 9 linux 999 10 redis name 15 python javadocker 16 LInux redis 17 linux 999 18 redis name 与上面的结果相比 id不连续。
四、条件判断与限制
条件判断主要根据数学运算符、逻辑运算符以及比较运算符等完成。
1) 数学运算符: +，-，*，% 2)逻辑运算符 &&，||，and,or ,in, between and 3)比较运算符： =,!=,>=,<=,>,<,<>
五、更改数据或者表结构
数据的更改使用update完成，而表结构的更改时依靠alter完成，接下来先介绍update更改数据，update和delete一样，在条件限制不够严密的情况下会导致数据全部改写，是比较危险的操作，所以一定要细心。
基本的语法：update table set column=" " where 判断条件;
1 pythonlinux javadocker 2 LInuxmysql redis 3 python javadocker 4 LInux redis 5 linux 999 6 redis name 7 python javadocker 8 LInux redis 9 linux 999 10 redis name 15 python javadocker 16 LInux redis 17 linux 999 18 redis name UPDATE user set username=‘kailinux‘ where id in (1,7,9) 更改后：（sql语句一般是先执行条件部分先锁定行再锁定列） 1 kailinux javadocker 2 LInuxmysql redis 3 python javadocker 4 LInux redis 5 linux 999 6 redis name 7 kailinux javadocker 8 LInux redis 9 kailinux 999 10 redis name 15 python javadocker 16 LInux redis 17 linux 999 18 redis name
六、查询数据
查询数据的sql是我们使用频率最高的sql,而这些SQL的优化程度代表着一个人对数据库的熟悉程度，所以我们在查询部分会涉及到很多的知识点。
1） 查询所有与查询个别字段
select username,password form user;
2）对部分列起别名
select username as loginname form user; select username loginame ,password from user; # 注意将新的列名和旧列名空格隔开
3）去掉重复值 distinct
select distinct username from user; select distinct username loginame ,password from user;
4）使用where条件查询
select * from user where **** ;
5）对空值的查询
虽然我们在创建表结构的时候一般设置 not null 但是也有些表会出现null的情况，例如我们将user表更改一下，表里面的数据如下：
1 kailinux javadocker 2 LInuxmysql NULL NULL为字符NULL 3 python NULL 4 LInux redis 5 linux 0 6 redis name 7 kailinux javadocker 8 LInux redis 9 kailinux #值为‘ ‘ 10 redis name 15 python javadocker 16 LInux #值为‘ ‘ 17 linux 999 18 redis name 19 ubuntu #值为默认的NULL 20 centos #值为默认的NULL
经分析得，表里面不仅有NULL值还有‘ ’值，我们现在研究一下为NULL和为‘ ‘的查询,对于NULL一般不用，整形默认为0，字符串模式为" "
select * from user where password=‘ ‘; 结果 id username password 9 kailinux 16 LInux #查找password列为NULL的方法 select * from user where password=NULL 结果为空 select * from user where password=‘NULL‘ 结果为： 2 LInuxmysql NULL 3 python NULL select * from user where password is NULL; 结果为： 19 ubuntu 20 centos 所以当我们要查询默认为NULL的时，需要用is NULL查询
6）条件判断 in和 between...and...
where 条件判断
7）like 模糊查询
like中 %p匹配任意多个字符 _ 下划线匹配一个字符
select * from user where username like ‘%linux%‘ #查询用户中包含linux的用户，不区分大小写 1 kailinux javadocker 2 LInuxmysql NULL 4 LInux redis 5 linux 0 7 kailinux javadocker 8 LInux redis 9 kailinux 16 LInux 17 linux 999
8）使用正则表达式
mysql是非常牛的一个数据库，不仅函数多，而且支持正则表达式，接下来我们研究一下正则表达式的匹配。 regexp 效率会比like差一点
regexp 正则里面
select * from user where username regexp ‘^linux‘ #以linux开头的 结果：不区分大小写 2 LInuxmysql NULL 4 LInux redis 5 linux 0 8 LInux redis 16 LInux 17 linux 999 select * from user where username regexp ‘python|redis‘ ; #用户名中包含redis和python的 3 python NULL 6 redis name 10 redis name 15 python javadocker 18 redis name 21 PYTHonmysql 22 rediswinner 23 PYthonmysql 24 winnerredis select * from user where username regexp ‘ python$|redis$|234$‘ ; 查询以python，redis 234结尾的用户名 6 redis name 10 redis name 18 redis name 24 winnerredis 25 PYTHoREDIS 27 PYthon234 28 winner1234
9 对查询结果排序 order by desc asc
select * from user order by id; #默认升序 1 kailinux javadocker 2 LInuxmysql NULL 3 python NULL 4 LInux redis 5 linux 0 ....... #部分结果省略 23 PYthonmysql 24 winnerredis 25 PYTHoREDIS 26 redispyhon 27 PYthon234 28 winner1234 select * from user order by id desc; #降序排列 desc降序 28 winner1234 27 PYthon234 26 redispyhon 25 PYTHoREDIS ....... #部分结果省略 8 LInux 7 kailinux 6 redis 5 linux 4 LInux 3 python 2 LInuxmysql 1 kailinux select * from user order by id asc; 结果： 1 kailinux javadocker 2 LInuxmysql NULL 3 python NULL 4 LInux redis 5 linux 0 ....... #部分结果省略 23 PYthonmysql 24 winnerredis 25 PYTHoREDIS 26 redispyhon 27 PYthon234 28 winner1234 默认排序方式为asc方式
常用的结构化查询语上上篇内容基本结束，在下篇中会从续查询部分内容，完成我们常使用的结构化查询语句的介绍，由于本人水平有限，内容缺乏精彩片段，但是愿意和各位朋友一起分享学习，请各位朋友多指导！
本文出自 “坚持梦想” 博客，请务必保留此出处http://dreamlinux.blog.51cto.com/9079323/1908264
数据库技能实战进阶之常用结构化sql语句（上）