Mysql总结之基本语句

Mysql

DDL（数据库定义语言）

1、数据库

• 查看所有数据库：SHOW DATABASES;
• 切换（选择要操作的）数据库： USE 数据库名;
• 创建数据库： CREATE DATABASE [IF NOT EXITS] 数据库名[CHARSET = utf8];
• 删除数据库： DROP DATABASE [IF EXITS] 数据库名;
• 修改数据库编码： ALTER DATABASE 数据库名 CHARACTER SET utf8;
  2、常用数据类型（列类型）
  int:整型
  Tinyint:
  Smallint:
  Mediumint:
  Int：
  Bigint:
  double:浮点型，例如double（5,2）表示最多5位，其中非必须有2位小数，即最大值为999.99；
  decimal:浮点型，在表示金钱方面使用该类型，因为不会出现精度缺失问题；
  char:固定长度字符串类型； char(255)最大值，如数据长度不足指定长度，补足到指定长度；
  varchar:可变长度字符串类型； varchar(65535)最大值，zhangsan
  text(clob):字符串类型(可变字符串)；
  tinytext ->2^8 -1B 256B
  text ->2^16-1B 64K
  mediumtext->2^24-1B 16M
  longtext ->2^32-1B 4G

blob:字节类型；
tinyblob ->2^8-1B 256B
blob ->2^16-1B 64K
mediumblob->2^24-1B 16M
longblob ->2^32-1B 4G
date：日期类型，格式为：YYYY-MM-DD；
time：时间类型，格式为：HH:MM:SS；
timestamp：时间戳类型；格式为：YYYY-MM-DD HH:MM:SS；
3、表

• 创建表：
  CREATE TABLE 表名（
  列名 列类型,
  列名 列类型,
  …

列名 列类型
）;

• 查看当前数据库中所有表名称：SHOW TABLES;
• 查看表结构：DESC 表名;
• 删除表：DROP TABLE 表名;
• TRUNCATE TABLE 表名：先删除drop该表，再create该表。而且无法回滚。
• 修改表：
  修改之添加列：
  ALTER TABLE 表名 ADD(
  列名 列类型,
  列名 列类型,
  …
  列名 列类型
  );
  修改之修改列类型：ALTER TABLE 表名 MODIFY 列名 新的列类型;
  修改之修改列名：ALTER TABLE 表名 CHANGE 原列名 新列名 列类型;
  修改之删除列：ALTER TABLE 表名 DROP 列名;
  修改表名称：ALTER TABLE 原表名 RENAME TO 新表名；

DML（数据库操作语言，对表记录的操作（增、删、改））

1、插入数据

• INSERT INTO 表名（列名1，列名2，…） VALUES（列值1，列值2， …）;
  在表名后给出要插入的列名，其他没有指定的列等同与插入null值。所以插入一行，不可能是 半行。
  在VALUES后给出列值，值的顺序和个数必须与前面指定的列对应

• INSERT INTO 表名 VALUES（列值1，列值2）
  没有给出要插入的列，那么表示插入所有列。
  值的个数必须是该表列的个数
  值的顺序，必须与表创建时给出的顺序相同。

2、修改数据

• UPDATE 表名 SET 列名1 = 列值1，列名2 = 列值2，…[WHERE 条件]
• 条件（条件可选的）：
  条件必须是一个Boolean类型的值或表达式：UPDATE t_person SET sex = ‘男’ , age = age+1 WHERE sid = ‘1’;
  运算符：=、!=、<>、>、<、<=、>=、BETWEEN…AND…IN（…）、IS NULL、NOT、OR、AND
  3、删除数据
• DELETE FROM 表名 [WHERE 条件];

DCL（数据控制语言）
1、创建用户

• CREATE USER 用户名@IP地址 IDENTIFIED BY ‘密码’;
  用户只能在指定的IP地址上登录
• CREATE USER 用户名@’%’ IDENTIFIED BY ‘密码’;
  用户可以在任意IP地址上登录
  2、给用户授权
• GRANT 权限1,2…权限n ON 数据库.* TO 用户名@IP地址
  权限、用户、数据库
  给用户分派在指定的数据库上的指定的权限
  例如：GRANT CREATE,ALTER,DROP,INSERT,UPDATE,DELETE,SELECT ON student.* TO user1@localhost;
  给user1用户分派在student数据库上的create、alter、drop、insert、update、delete、select权限
• GRANT ALL ON 数据库.* TO 用户名@IP地址
  给用户分派数据库上的所有权限
  3、撤销权限
• REVOKE 权限1,2…权限n ON 数据库.* FROM 用户名@IP地址
  撤销指定用户在指定数据库上的指定权限
  例如：REVOKE CREATE,ALTER,DROP ON student.* FROM user1@localhost;
  撤销user1用户在student数据库上的create、alter、drop权限
  4、查看权限
  *SHOW GRANTS FOR 用户名@IP地址
  5、删除用户
• DROP USER 用户名@IP地址

DQL（数据查询语言（查询不会修改数据库表记录！））

一、基本查询
1.字段（列）控制
1）查询所有列

SELECT * FROM 表名;
例：SELECT * FROM emp;
--> 其中“*”表示查询所有列

2）查询指定列

SELECT 列1[,列2, ... 列N] FROM 表名;
例：SELECT empno, ename, sal, comm FROM 表名; 

3）完全重复的只记录（显示）一次
当查询结果中的多行记录一模一样是，只显示一行。

SELECT DISTINCT * 列1[,列2, ... 列N] FROM 表名;
例：SELRCT DISTINCT sal FROM emp;

–> 查询员工表的工资，如果存在只显示一次！
4）列运算
数量类型的列可以做加、减、乘、除运算

SELECT sal*1.5 FROM emp;
SELECT sal+comm FROM emp;

字符串类型可以做连续运算

SELECT CONCAT(‘$’, sal) FROM emp;

转换NULL值
有时需要把NULL转换其他值，例如com+100时，如果com列存在NULL值，那么NULL+100还是NULL， 而我们这时希望把NULL当前0来运算。

SELECT IFNULL(com,0) FROM emp;

–> IFNULL(com,0) :如果com中存在NULL值，那么当成0来运算。
给列起别名
当使用列运算后，查询出的结果集中的列名称很不好看，这是我们需要给列名起个别名，这样在结果 集中列名就显示别名了
例：SELECT IFNULL(com,0) + 100 AS 奖金 FROM emp;
–> 其中AS可以省略
2.条件查询
1）条件查询
与UPDATE和DELETE语句一样，SELECT语句也可以使用WHERE来控制记录。
2）模糊查询
当你想查询姓张，并且姓名一共两个字的员工时，这是就可以使用模糊查询

SELECT FROM emp WHERE ename LIKE ‘张_’；

–>模糊查询需要使用运算符：LIKE，注意：只能匹配一个字符而不是多个
如果想要查询姓张，名字不限长度的员工时就要使用“%”了。

SELECT * FROM emp WHERE ename LIKE ‘张%’

–>其中%可以匹配0~N个任意字符

SELECT FROM emp WHERE ename LIKE ‘%阿%’

–>不能理解成名字中间有“阿”，因为%匹配0~N，所以姓名只要出现“阿”字，不管开头结尾都会查 询到。
当你想查询名字为3个字的员工详细信息时可以使用

SELECT * FROM emp WHERE ename LIKE ‘___’;

–> 一个下划线匹配一个字符，多个下划线匹配相应的字符
二、排序
1）升序

SELECT * FROM WHERE emp ORDER BY sal ASC;

–> 将sal排序，升序！
–> 其中ASC是可以省略，默认
2）降序

SELECT * FROM WHERE emp ORDER BY comm DESC;

–> 将comm排序，降序！
–> 其中DESC不能省略
3）使用多列作为排序条件

SELECT * FROM WHERE emp ORDER BY sal ASC, comm DESC;

–> 将sal升序排列，如果sal相同，使用comm的降序排列。可以继续追加条件。
三、聚合函数
聚合函数用来做某列的纵向运算
1）COUNT

SELECT COUNT(*) FROM emp;

–> 计算emp表中所有列都不为null的记录的行数
SELECT COUNT(comm) FROM emp;
–> 计算emp表中comm列不位null的记录的行数
2）MAX

 SELECT MAX(sal) FROM emp;

–> 查询最高工资
3）MIN

 SELECT MIN(sal) FROM emp;

–> 查询最低工资
4）SUM

 SELECT SUM(sal) FROM emp;

–> 查询工资合
5）AVG

 SELECT AVG(sal) FROM emp;

–> 查询平均工资
四、分组查询
分组查询是把记录使用某一列进行分组，然后查询组信息
例如：查看所有部门的记录数。

SELECT deptno,COUNT(*) FROM emp GROUP BY depyno;

–> 使用deptno分组，查询部门编号和每个部门的记录数

SELECT job,MAX(sal) FROM emp GROUP BY job;

–> 使用job分组，查询每种工作的最高工资
组条件:以部门分组，查询每组记录数。条件记录数大于3

SELECT deptno,COUNT(*) FROM emp GROUP BY deptno HAVING COUNT(*) > 3;

五、Limit子句（方言）
LIMIT用来限定查询结果的起始行，以及总行数。
例如：查询起始行为第5行，一共查询3行记录

SELECT * FROM emp LIMIT 4,3

–> 其中4表示从第5行开始，其中3表示一共查询3行。即第5、6、7行记录。
数据库备份与恢复
1、备份语句
mysqldump -u用户名 -p密码 数据库名>生成的脚本文件路径

mysqldump -uroot -p123456 mydb1>C:/a.sql    

注意，不要打分号，不要登录mysql，直接在cmd下运行
注意生成的脚本文件中不包含create database语句
2、恢复语句
1）mysql -u用户名 -p密码 数据库<脚本文件路径
例：
先删除mydb1数据库，再重新创建mydb1库

mysql -uroot -p123456 mydb1<C:/a.sql

2)登录mysql
Source SQL脚本路径
例：
先删除mydb1库，再重新创建mydb1库
切换到mydb1库

source C:/a.sql

约束
1、主键约束

2、主键自增长

3、非空和唯一约束

4、外键约束

5、一对一关系

6、多对多关系

7、合并结果集

7、查询连接之内外连接

8、子查询