数据库MySQL——SQL语句,命令

SQL语句分类

  1. DCL (Data Control Language):数据控制语言;用来管理用户及权限
  2. DDL(Data Definition Language):数据定义语言;用来定义数据库对象:库,表,列等
  3. DML(Data Manipulation Language):数据操作语言;用来定义数据库记录(数据)
  4. DQL(Data Query Language):数据查询语言;用来查询数据;


命令分类不一定准确,没必要分得太准确!

数据库MySQL——SQL语句,命令

/usr/bin/mysqladmin -uroot password 123456   #给数据库设置密码
chkconfig mysqld on   #设置MySQL自启动
mysql_secure_installation    #初始化数据库
select version();      #查看数据库版本
SET PASSWORD FOR 'username'@'host' = PASSWORD('newpassword');     #更改某一用户密码
select Host,User,Password from mysql.user;       #查看用户信息
grant 权限 on 库名.表 to 用户@主机 identified  by  '密码';       #给用户加权限和密码
grant all privileges on *.* to root@'%' identified by '123456';     #给root用户在所有主机上的所有权限(用于第三方登录数据库)
show grants for user250@localhost;      #查看250用户的权限
revoke all on *.* from user250@localhost;       #回收250用户在localhost主机上的所有权限
show variables ;      #查看服务器配置变量
show variables like '%engine%';       #查看存储引擎
show status;    #查看服务器状态

DDL

show databases;      #查看所有数据库
create database DBNAME;    #创建数据库
drop database DBNAME;    #删除数据库
use DBNAME;     #使用(进入)某一数据库
show create database DBNAME;      #查看数据库状态
alter database DBNAME default character set=utf8;     #修改指定数据库的字符编码
show tables;   #查看所有表
desc TABLENAME;   #查看表结构
alter table huluwa rename to xiaoairen;    #给葫芦娃表改名为小矮人表
alter table huluwa add color varchar(10);    #给表添加字段
alter table huluwa modify name varchar(20);    #修改表字段(不重新命名)
alter table 表名 change 原名 新名 类型及约束;     #修改表的字段
alter table huluwa drop color;     #删除表的字段
drop table huluwa;      #删除表
alter table huluwa add primary key(sid);    #设定sid字段为huluwa表的主键
alter table huluwa drop primary key;     #删除主键
alter table huluwa add constraint fk_huluwa_school foreign key(cla_id) references school(id);    #给huluwa表添加外键cla_id关联到school表的id列
alter table huluwa drop foreign key fk_huluwa_school;   #删除外键fk;外键的名字叫fk_huluwa_school
  • 创建表
create table huluwa (
    ->  id int unsigned auto_increment primary key, 
    ->  name varchar(10),
    ->  age tinyint unsigned,   
    ->  high decimal(5,2),
    ->  gender enum('boy','girl','unknow')default 'unknow',  
    ->  lost bit(1));

数据类型:

常用数据类型

  • 整数型:int,bit
  • 小数型:decimal #decimal(5,2)
  • 字符串:varchar,char
  • 时间:date,time,datetime
  • 枚举类型:enum

数值类型常用

类型字节大小有符号范围(signed)无符号范围(unsigned)
tinyint1-128~1270~255
smallint2-32768~327670~65535
mediumint3-8388608~83886070~16777215
int/integer4-2147483648~21474836470~4294967295
bigint8-9223372036854775808~

9223372036854775807

0~18446744073709551615

字符串

类型字节大小示例
char0~255不能伸缩(定多少我就取多少)
varchar0~255可以伸缩(要浪费一个字节指定字符长度)
text0~65535大文本

日期时间类型

类型字节大小示例
date4'2019-01-01'
time3'12:30:30'
datetime8'2019-01-01 12:30:30'
timestamp4'1970-01-01 00:00:01'UTC~'2038-01-01 00:00:01'UTC

约束

  • primary key:主键约束(唯一标识)
  • auto_increment:自增长
  • not null:不为空
  • unique:字段不重复
  • default:默认
  • foreign key:外键,对关系字段进行约束,当关系字段填写值时,会到关联的表中查询此值是否存在,如果存在则填写成功,如果不存在则填写失败并抛出异常。
  • constraint fk_huluwa_school foreign key(cla_id) references school(id) : 外键约束例子

DML

insert into 表名 values (...);     #插入数据
insert into 表名 valuse (...),(...),...;   #多条插入数据
insert into 表名(字段) valuse(...);   #部分插入(无数据为null)
update 表名 set 列=值... where 过滤条件;   #修改数据
update huluwa set age=age+1 where name in('大娃','二娃');    #数据修改支持四则运算
update huluwa set age=age+1 where name in('大娃','二娃');    #条件选择支持in语法
delete from 表名 where 条件;    #删除(delete / truncate)

DQL

  • 单表查询
select * from TABLENAME;   #查询表中所有数据
select distinct age from huluwa;   #distinct 查询取消重复行
select * from huluwa  where lost='yes';   #条件查询
select * from huluwa where age between 3 and 6;    #条件查询之between
select * from huluwa where not (age > 3 and age < 6);    #条件查询之not和运算符
select * from huluwa where age > 3 and lost='no';   #条件查询之and和or
select * from huluwa where name like '%aaa%';   #条件查询之模糊查询like  %可以代表0到多个字符
select * from huluwa where name like '____';    #条件查询之模糊查询like  _可以代表一个字符
select * from huluwa where high is not null;    #判断是否为空 is null  或 is not null
select * from huluwa order by high desc;    #查询并排序order  desc降序  asc升序
select * from huluwa order by high desc,rade asc;    #先desc降序,high列相同看rade列,asc升序
select *,age*1.5 from huluwa ;    #数字类型的列可以进行四则运算,无法转换为数字的以0计算
select *,'基本工资'+ifnull('奖金',0) as '工资' from huluwa ;    #四则运算也支持ifnull语句以处理null值  
select CONCAT('我叫','name',',我是','job') as 描述 from huluwa ;     #CONCAT连接字符串 (起别名as可以省略)

  • 聚合函数
select count(*)  from huluwa     #查询有效行数
select max(high)  from huluwa    #查询最大
select min(high)  from huluwa    #查询最小
select sum(high)  from huluwa    #计算总和
select round(avg(high),2) from huluwa;   #求平均值  round保留2位小数

  • 分组查询 group by(分组后只能查询组信息,不能查询个人信息)
select 分组列 ,... from 表名 group by 分组列;     #基本语法

select gender from huluwa group by gender;      #按性别分组
select gender,group_concat(name) from huluwa group by gender;   #按性别分组,并列出所有组员某字段的值
select gender,count(*) from huluwa group by gender;      #按性别分组,并查询组人数
select gender,count(*) from huluwa where age > 18 group by gender;      #先过滤出年龄大于18的,再按性别分组,并查询组人数
select gender,count(*) from huluwa where age > 18 group by gender having count(*) >= 2;   #having 后置条件 ;最后过滤出组人数大于等于2的组
  • limit 分页查询 (方言)
select * from huluwa limit 4,2;     #查询第四行后面的两行(即:第五行和第六行)信息
  • union 合并结果集(一模一样的两张表;union all不去重;union 去重)
select * from a1 union select * from a2;
  • 连接查询
select * from table1 ,table2 where table1.id = table2.eid;      #笛卡尔积查询 (方言) 
select * from table1 inner join table2 on table1.id = table2.eid;     #内连接(标准)
select * from heros as movie inner join heros as human on movie.a动画片';    #having后置条件
select * from huluwa left join school on school.cla_id=huluwa.cla_id;     #左关联
select huluwa.name , ifnull(school.cla_id , '代建') from huluwa left join school on school.cla_id=huluwa.cla_id;     #左关联+ifnull
# left join (左联接) 返回包括左表中的所有记录和右表中关联字段相等的记录
# right join (右联接) 返回包括右表中的所有记录和左表中关联字段相等的记录
# inner join (内关联) 只返回两个表中关联字段相等的行

#通过合并结果集union加左右关联实现全外连接,实现显示所有行;
select * from huluwa left join school on school.cla_id=huluwa.cla_id union select * from huluwa right join school on school.cla_id=huluwa.cla_id;  
  • 子查询
select * from huluwa where grade = (select max(grade) from huluwa);   #查询套查询即子查询---子查询作为条件
select sanban.name , sanban.grade from (select * from huluwa where cla_id = 3;) sanban   #子查询作为一张表  起别名as可以省略

#子查询应用总结---作为条件

#单行单列----可以使用[= , > , < , >= , <= , != ]
select * from huluwa where grade = (select max(grade) from huluwa);  #查询最高分同学的信息
#多行单列----可以使用[IN ALL ANY](in代表在其中;any表示任意;all表示所有)
select * from ultraman where grade > all (select grade from huluwa); #查询比所有葫芦娃成绩都高的奥特曼的信息
#