MySQL的sql语言-中篇（ddl,dml,dql）

sql语言：

上篇地址： MySQL的sql语言-上篇

DDL语句：

创建表：

#普通创建
create table t1(字段名 类型 修饰符,..);		

#创建表时直接复制原有的表的数据和内容。主键、自增不复制
create table t2 select * from t1;		

#复制表，只复制结构，不带数据
create table t2 like t1;		

查看：

show engines;			#查看存储引擎
show tables;
show table t1;
desc t1;			查看t1的表结构
show create table t1;		查看创建t1表时的命令
show table status like 't1'\G;		查看t1表的状态信息
show table status from 'test'\G;		查看test库的表状态信息
show warnings;		查看命令的报警
show function status;			查看自定义函数
show processlist		#查看mysql系统中当前运行的所有进程
show master logs;		查看二进制日志
show slave status\G;		查看slave服务的内容
show plugins;		查看插件

删除、修改：

alter的子命令：

• add（添加新字段）
• change（改字段名）
• modify（改字段属性）

drop table [if exists] 't1';		如果存在则删除t1表
alter table 表 add 字段名 类型 [first|after 字段];		添加字段
alter table 表 change 字段名 新名 类型 [修饰符];		改字段名称
alter table 表 modify 字段名 新类型;	修改字段类型
alter table 表 rename to 表新名;		修改表名称
alter table 表 drop 字段;		删除字段

DML语句：

插入数据：

insert 表 [字段1,字段2,..] value (数据);
insert 表 values(数据1,数据2,..);

例：复制表中数据

insert t2 select * from t1;		复制插入，复制t1的数据，插入表t2

更新数据：

update 表 set [字段1=内容,..] [where 条件];		更新数据时，必须加条件

注意：

• 所有操作都要记得条件
• 不加条件时执行，该表的字段数据会有毁灭性操作，所有内容都被修改
• 生产场景中追求安全的场合，可做限制

解决方法:

#方法1：
alias mysq='mysql -U'		#shell的别名实现

#方法2：
echo 'alias="mysql -U"' >> /etc/profile

#方法3：
vim /etc/my.cnf.d/client.cnf	#改客户端文件
[client]
safe-updates

删除数据：

注意点与updata一样，关注安全的场合，可做限制，但truncate命令不会阻拦

delete from t1;		删除整个表中的数据，较慢方式。数据删除后，数据文件还是占用原有的磁盘空间
truncate table t1;	快速删除表中数据，数据文件大小释放
optimize table t1;	手动缩放表占用空间
delete from t1 where id=1;	删除t1表的id=1的数据

DQL语句：

数据查询：

select 字段[*] [[as] 别名] from 表 [条件子句];		

例：

select stuid 学员,name as 姓名,gender 性别 from students;	字段按别名显示
select * from mysql.proc\G;			proc表存放自定义函数

where子句：

实现条件过滤、选择

true、false	bool表达式
+、-、*、/、%	数字运算
=、<=>、==	等于、相等或都为空、等于
between 1 and 5	取1-5之间
in(1,3,5)	取其中的值
is null	为空的
is not null	不为空的
distinct	去除重复，例：select distinct age from students;
like '字符'	支持通配符： % 任意长度任意字符； _ 单个任意字符
rlike	正则表达式。索引失效，不建议使用，性能慢
not、and、or、xor、&&、||	逻辑操作符

例：

select * from students where id!=1;
select * from students where age between 18 and 25;
select 10*20 结果;
select stuid,name,age,gender from students where age in (18,20,22);
select * from students where classid is null;	显示classid为空的行
select * from students where classid <=> null;	同上
select distinct gender from students; 		去重gender

group by子句:

根据条件把查询结果进行“分组”，再做“聚合”运算

常见聚合函数：

• avg()
• max()
• min()
• count()
• sum()

注意：

• 分组操作时，查询的内容只能是：查询字段本身、聚合运算字段。多的没有意义，因为多出来的自动做了min()运算
• group by后面不能加where子句，要换成having（功能与where一样）
• 使用where时要放在group by前面才是正确语法

例：

select gender,avg(age) from students group by gender;

#查询班级、性别、平均年龄，结果按班级、性别分组显示
select classid,gender,avg(age) from students group by classid,gender having classid is not null;

order by子句

根据指定的字段对查询结果进行排序

关键字：

• asc 正序
• desc 降序

小技巧： 有的结果值是null，但排序会排在最前面，可以将其排到最后
select * from students order by -classid desc;

例：

#双字段不同排序显示
select * from students where classid is not null order by gender desc,age asc;

例2： 查看hj库的所有表的行数

这是mysql信息中记录的，若该库的数据变动频繁，行数可能不是最新的

use information_schema;
select table_name,table_rows from tables where TABLE_SCHEMA = 'hj' order by table_rows desc;

limit [[offset,]row_count]子句：

对查询的结果进行输出行数数量限制

例：

select * from students order by age limit 5;

#去重，排序后显示2-5行内容
select distinct age from students order by age limit 2,5;

对查询结果中的数据请求施加“锁”

FOR UPDATE:			写锁，独占或排它锁，只有一个读和写操作
LOCK IN SHARE MODE: 		读锁，共享锁，同时多个读操作

SQL注入攻击：

主要是通过数据库的where子条件做出操作
设：user表的字段有： user、username、password字段

例

#查询admin用户，后者的“or”条件判断：1=1实现永久为真，显示结果
select * from user where name='admin' and password='' or '1'='1';

#原理同上
select * from user where name='admin' and password='' or '1=1';

#查询特殊的 admin';--用户，使用注释，将后面的都当做注释，达到只是查询admin，但有将条件成立
select * from user where name='admin'; -- ' and password='magedu123';

select * from user where name='admin'; # ' and password='magedu123';