mysql建库、建表命令、sql语句大全

小咪咪 2022-03-18 16:30 571阅读 0赞

# 1.常见的关系型数据库 #

SQL Server 微软提供  
Oracle 甲骨文公司  
DB2 IBM  
MySQL 瑞典MySQL AB  
Sybase Sybase公司

SQLite 迷你的数据库，用在一些嵌入式设备中  
…

# 2.mysql数据库简介 #

特点:目前是免费开源的  
创始人是芬兰人,2009年以10亿美元卖给了SUN公司.1年后SUN公司被oracle(甲骨文)公司收购.

## 2.1基础操作 ##

### mysql服务器常用命令 ###

**登录mysql客户端:**  
**命令:**`mysql -u+用户名 -p`  
\[输入密码:自己设置的\]  
设置登录时转换GBK类库,防止中文乱码,命令:

mysql  –default  -character  -set=gbk
    
    **注意:MySQL数据库中的mysql库为系统库,一般不用这个库.就和电脑的C盘一个性质**

### 查看库.表 ###

查看所有数据库: `show databases;`  
进入数据库: `use +指定的库名;`  
查看库中所有的表: `show tables;`  
查看表结构: `desc+表名;`  
退出/断开连接:`exit;或quit;或 \q;或ctrl+c;`

### 建库.建表 ###

**建库:**  
– 删除db1库(如果存在)加了个判断如果存在删除，不存在不用删  
(–杠杠+空格是命令窗口的注释的意思)

drop database  if exists db1;

（推荐用软件进行操作：Notepad++软件(可以避免出错,有出错提示)、SQLyogEnt、navicat等(数据库建库表各种操作)）  
– 重新创建db1库,重新创建了db1库，类型是utf8；

create database db1 charset utf8;

– 查看、进入db1库

show databases; //查看
    use db1;  //进入

**建表:**  
– 删除stu学生表(如果存在)

drop table if exists stu;

– 创建stu学生表（编号、姓名、性别、出生年月、考试成绩）  
– (unique—唯一性)有唯一的需求时要加unique这个词;

create table stu(
    	id int primary key auto_increment,     -- 学生编号,设置id为主键，并且自增
    	name varchar(50) unique,             -- 学生姓名(保证学生名字不可以重复)
    	gender char(1),	                       -- 学生性别
    	birthday date,		               -- 出生年月
    	score double		               -- 考试成绩
            );

– 查看stu学生表结构 `desc stu;`

## 2.2基本语法 ##

### 数值类型 ###

MySQL中支持5种整数类型，其实很大程度上相同的，只是存储值的大小范围不同而已。  
其次是浮点类型float和double类型

tinyint：占用1个sss字节，相对于java中的byte
    smallint：占用2个字节，相对于java中的short
    int：占用4个字节，相对于java中的int
    bigint：占用8个字节，相对于java中的long
    float：4字节单精度浮点类型，相对于java中的float
    double：8字节双精度浮点类型，相对于java中的double

### 字符串类型 ###

char()------定长字符串，最长255个字符。定长会浪费空间  
varchar()----变长(不定长)字符串，最长不超过 65535个字节,一般超过255个字节，会使用text类型. 不定长节省空间,剩余空间会留给别的数据使用

### 长文本类型 ###

text--------最长65535个字节

**总结：** char、varchar、text都可以表示字符串类型，其区别在于：  
(1)char在保存数据时, 如果存入的字符串长度小于指定的长度n,后面会用空格补全。  
(2)varchar和text保存数据时, 按数据的真实长度存储, 剩余的空间可以留给别的数据用.  
(3)char会造成空间浪费(不足指定长度的会用空格补全), 但是由于不需要计算数据的长度, 因此速度更快。（即浪费空间、节约时间）  
(4)varchar和text但是节省了空间, 但是存储的速度不如char快(因为要计算数据的实际长度)

### 日期类型 ###

1、date：年月日  
2、time：时分秒  
3、datetime：年月日 时分秒  
4、timestamp：时间戳，与datetime存储相同的数据。  
timestamp最大表示2038年，而datetime范围是1000~9999  
timestamp在插入数、修改数据时，可以自动更新成系统当前时间

### 字段约束 ###

创建表时, 除了要给每个列指定对应的数据类型, 有时也需要给列添加约束。常见的约束有：主键约束、唯一约束、非空约束、外键约束。

#### 主键(primary key) ####

主键是数据表中，一行记录的唯一标识。比如学生的编号，人的身份证号;  
当主键为数值时，为了方便维护，可以设置主键为自增（auto\_increment）

#### 唯一(unique) ####

保证所约束的列必须是唯一的，即不能重复出现，例如：用户注册时，保存的用户名不可以重复。

#### 非空(not null) ####

保证所约束的列必须是不为空的，即在插入记录时，该列必须要赋值，例如：用户注册时，保存的密码不能为空。  
创建user表, 指定密码不能为空

create table user(
    	id int primary key auto_increament,
    	username varchar(50) unique,
    	password varchar(50) not null,
    	...
    );

#### 外键 ####

外键是用于表和表之间关系的列

# 3. SQL语句大全 #

## 3.1增 ##

insert into XXX
             Values  XXX;

## 3.2删 ##

delete from  XXX
             Where XXX;

## 3.3改 ##

update XXX set XXX
              Where  XXX;

## 3.4查 ##

select  XXX from;

以上是简化版,第一行表示表中的某某,第二行表示条件是什么.

### 3.4.1基本查询 ###

– 查询emp表中的所有员工信息

select * from emp;

– 查询emp表中的所有员工的姓名、薪资、奖金

select name,sal,bonus from emp;

提示: distinct用于剔除重复值  
– 查询emp表中的所有部门, 剔除重复的记录

### 3.4.2where字句查询 ###

(1) between x and y(在x~y之间的值)。  
(2) like在模糊搜索时，可以配合 “%” 和 "*" 等符号。 “%” 表示通配，表示0或多个字符。"*"表示一个字符串。  
(3) and 表示并的关系，当两边的条件都为true时结果才为true，or 表示或的关系，当两边只要有一边为true，结果就为true，只有两边同时为false，结果才为false。  
(4) not 表示对条件取反。  
– 查询emp表中薪资大于3000的所有员工，显示姓名、薪资

select name,sal from emp where sal>3000;

– 查询emp表中总薪资(薪资+奖金)大于3500的所有员工，显示姓名、总薪资

select name, sal+bonus from emp where (sal+bonus)>3000; 
    -- 或
    select name, sal+bonus as 总薪资 from emp where (sal+bonus)>3000;

提示：as用于定义别名(仅在查询的结果中作为列的表头显示)

– 查询emp表中薪资在3000和4500之间的员工，显示姓名和薪资

select name,sal from emp where sal between 3000 and 4500;

– 查询emp表中薪资为1400、1600、1800的员工，显示姓名和薪资

select name,sal from emp where sal in(1400,1600,1800);

– 查询emp表中姓名中以"刘"开头的员工，显示姓名。

select name,sal from emp where name like '刘%';

– 查询emp表中姓名以"刘"开头并且不超过2个字的员工，显示姓名。

select * from emp where name like '刘_';

– 查询emp表中姓名中包含"涛"员工，显示所有字段。

select * from emp where name like '%涛%';

“%” 表示通配，表示0或多个字符。"\_"表示一个字符串

– 查询emp表中薪资大于4000和薪资小于2000的员工，显示姓名、薪资。

select name,sal from emp where sal<2000 or sal >4000;

– 查询emp表中薪资大于3000并且奖金小于600的员工，显示姓名、薪资、奖金。

select name,sal,bonus from emp where sal>3000 and bonus<600;

### 3.4.3排序查询 ###

对查询的结果进行排序使用 order by关键字。  
order by 排序的列XXX asc 升序  
order by 排序的列XXX desc 降序  
– 对emp表中所有员工的薪资进行升序(从低到高)排序，显示姓名、薪资。

select name,sal from emp order by sal asc;

– 对emp表中所有员工的总薪资进行降序(从高到低)排序，显示姓名、总薪资。

select name, sal+bonus as 总薪资 from emp order by (sal+bonus) desc;

### 3.4.4分组查询 ###

对所查询的记录可以根据某一列进行分组, 分组使用group by。  
– 将员工按照部门进行分组

select * from emp group by dept;

– 对emp表按照部门进行分组, 并统计每个部门的人数, 显示部门和对应人数

select dept 部门名称, count(*) 部门人数 from emp group by dept;

– 对emp表按照部门进行分组, 求每个部门的最高薪资(不包含奖金)

select max(sal) 总薪资 from emp group by dept;

### 3.4.5聚合函数查询 ###

1、max()或min() – 求所查询记录中指定列的最大值或最小值  
2、count() – 求所查询记录中指定列的记录数  
3、sum() – 求所查询记录中指定列的总和  
4、avg() – 求所查询记录中指定列的平均值  
– 查询emp表中最高薪资

select max(sal) as 最高薪资 from emp;

– 查询emp表中最高总薪资

select max(sal+bonus) as 最高薪资 from emp;

– 统计emp表中薪资大于3000的员工个数

select count(*) from emp where sal>3000;

– 统计emp表中所有员工的总薪资(不包含奖金)

select sum(sal) as 员工总薪资 from emp;

– 统计emp表员工的平均薪资(不包含奖金)

select avg(sal) as 员工总薪资 from emp;

**!!重要提示：**  
a) 可以使用count(*)统计记录行数  
b) 多个聚合函数可以一起查询  
– 例如:根据部门进行分组，统计每个部门员工人数和平均薪资  
select dept, count(*) 员工人数, avg(sal) 平均薪资 from emp group by dept;  
c) 聚合函数不能用在where子句中  
d) 在没有分组的情况下，聚合函数不能和其他普通字段一起查询  
– 例如: 查询emp表中薪资最高的员工姓名, 下面的写法是错的:  
select name, max(sal) from emp;–结果是错的  
– 正确的查询：  
select name, sal from emp where sal=(  
select max(sal) from emp  
);-- 子查询

### 3.4.6其他函数 ###

1、数值函数  
(1)ceil(数值) – 向上取整  
(2)floor(数值) – 向下取整  
(3)round(数值) – 向下取整  
(4)rand(数值) – 随机数  
– emp表中所有员工薪资上涨15.47%, 向上取整。  
select name,sal, ceil(sal\*1.1547) from emp;

2、日期函数  
(1)curdate() – 返回当前日期(年月日)  
(2)curtime() – 返回当前时间(时分秒)  
(3)now() – 返回当前日期+时间(年月日 时分秒)  
(4)date\_add()、date\_sub() – 增加/减少日期  
(5)year()、month()、day()、hour()、minute()、second()，分别用来获取日期中的年、月、日、时、分、秒

– 查询系统当前时间。  
select now();

– 查询emp表中所有员工的年龄，显示姓名、年龄。

select name,year(curdate()) - year(birthday) 年龄 from emp;

– 查询emp表中所有在1993和1995年出生的，显示姓名、出生日期。

select name,birthday from emp where year(birthday) between 1993 and 1995;

查询语句比较多,先写这些了,后期会把表关联和多表查询做一总结!!