mysql打开数据库的sql语句_数据库MySQL——SQL语句（命令）

SQL语句分类

DCL (Data Control Language)：数据控制语言；用来管理用户及权限

DDL(Data Definition Language)：数据定义语言；用来定义数据库对象：库，表，列等

DML(Data Manipulation Language)：数据操作语言;用来定义数据库记录(数据)

DQL(Data Query Language)：数据查询语言；用来查询数据；难

命令分类不一定准确，没必要分得太准确！

DCL

/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)

tinyint

1

-128~127

0~255

smallint

2

-32768~32767

0~65535

mediumint

3

-8388608~8388607

0~16777215

int/integer

4

-2147483648~2147483647

0~4294967295

bigint

8

-9223372036854775808~

9223372036854775807

0~18446744073709551615

字符串

类型

字节大小

示例

char

0~255

不能伸缩(定多少我就取多少)

varchar

0~255

可以伸缩(要浪费一个字节指定字符长度)

text

0~65535

大文本

日期时间类型

类型

字节大小

示例

date

4

'2019-01-01'

time

3

'12:30:30'

datetime

8

'2019-01-01 12:30:30'

timestamp

4

'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.aid=human.pid having movie.name='动画片'; #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); #查询比所有葫芦娃成绩都高的奥特曼的信息

#