MySql数据库相关sql语句

一、数据库相关sql语句：
1、直接创建数据库

CREATE DATABASE 数据库名;

2、判断是否存在并创建数据库

CREATE DATABASE IF NOT EXISTS 数据库名;

3、创建数据库并指定字符集(编码表)

CREATE DATABASE 数据库名 CHARACTER SET 字符集;

4、查看所有的数据库

SHOW databases;

5、查看某个数据库的定义信息

SHOW CREATE DATABASE 数据库名;

6、修改数据库字符集格式

ALTER DATABASE 数据库名 CHARACTER SET 字符集;

7、删除数据库

DROP DATABASE 数据库名;

8、查看正在使用的数据库

SELECT DATABASE();

9、使用/切换数据库

USE 数据库名;

二、DDL操作表
1、创建表

CREATE TABLE 表名 (字段名1 字段类型1, 字段名2 字段类型2…);

2、删除表

DROP TABLE 表名;

三、修改表结构
1、添加表列

ALTER TABLE 表名 ADD 列名 类型;

2、修改列类型

ALTER TABLE 表名 MODIFY列名 新的类型;

3、修改列名

ALTER TABLE 表名 CHANGE 旧列名 新列名 类型;

4、删除列

ALTER TABLE 表名 DROP 列名;

5、修改表名

RENAME TABLE 表名 TO 新表名;

6、修改字符集

ALTER TABLE 表名 character set 字符集;

7、插入数据

INSERT INTO 表名 (字段名1, 字段名2, ...) VALUES (值1, 值2, ...);
没有添加数据的字段会使用NULL

注意：

• 值与字段必须对应，个数相同，类型相同
• 值的数据大小必须在字段的长度范围内
• 除了数值类型外，其它的字段类型的值必须使用引号引起。（建议单引号）
• 如果要插入空值，可以不写字段，或者插入null

四、更新表记录
1、带条件修改数据

UPDATE 表名 SET 字段名=值 WHERE 字段名=值;

2、带条件删除数据

DELETE FROM 表名 WHERE 字段名=值;

3、truncate删除表记录

TRUNCATE TABLE 表名;

truncate和delete的区别：

• delete是将表中的数据一条一条删除
• truncate是将整个表摧毁，重新创建一个新的表,新的表结构和原来表结构一模一样

五、简单查询
1、使用*表示所有列

SELECT * FROM 表名;

2、写出查询每列的名称

SELECT 字段名1, 字段名2, 字段名3, ... FROM 表名;

3、查询时给列、表指定别名需要使用AS关键字
使用别名的好处是方便观看和处理查询到的数据

   SELECT 字段名1 AS 别名, 字段名2 AS 别名... FROM 表名;
   SELECT 字段名1 AS 别名, 字段名2 AS 别名... FROM 表名 AS 表别名;

4、查询指定列并且结果不出现重复数据

SELECT DISTINCT 字段名 FROM 表名;

六、条件查询

• and(&&) 多个条件同时满足
• or(||) 多个条件其中一个满足
• not(!) 不满足

LIKE表示模糊查询

SELECT * FROM 表名 WHERE 字段名 LIKE '通配符字符串';

满足通配符字符串规则的数据就会显示出来
所谓的通配符字符串就是含有通配符的字符串

• MySQL通配符有两个：
  %: 表示0个或多个字符(任意个字符)
  _: 表示一个字符

七、排序
1、通过ORDER BY子句，可以将查询出的结果进行排序(排序只是显示方式，不会影响数据库中数据的顺序)

SELECT 字段名 FROM 表名 WHERE 字段=值 ORDER BY 字段名 [ASC|DESC];

ASC: 升序, 默认是升序
DESC: 降序

2、组合排序就是先按第一个字段进行排序，如果第一个字段相同，才按第二个字段进行排序，依次类推。

SELECT 字段名 FROM 表名 WHERE 字段=值 ORDER BY 字段名1 [ASC|DESC], 字段名2 [ASC|DESC];

3、五个聚合函数：

• count： 统计指定列记录数，记录为NULL的不统计
• sum： 计算指定列的数值和，如果不是数值类型，那么计算结果为0
• max： 计算指定列的最大值
• min： 计算指定列的最小值
• avg： 计算指定列的平均值，如果不是数值类型，那么计算结果为0

4、分组
分组查询是指使用 GROUP BY语句对查询信息进行分组，相同数据作为一组

SELECT 字段1,字段2... FROM 表名 GROUP BY 分组字段 [HAVING 条件];

1、查询男女各多少人

SELECT sex, COUNT(*) FROM student3 GROUP BY sex;

2、查询年龄大于25岁的人,按性别分组,统计每组的人数

SELECT sex, COUNT(*) FROM student3 WHERE age > 25 GROUP BY sex;

3、查询年龄大于25岁的人,按性别分组,统计每组的人数,并只显示性别人数大于2的数据

SELECT sex, COUNT(*) FROM student3 WHERE age > 25 GROUP BY sex HAVING COUNT(*) >2;

having与where的区别

• having是在分组后对数据进行过滤，写在group by的后面
• where是在分组前对数据进行过滤,写在group by的前面
• having后面可以使用聚合函数
• where后面不可以使用聚合函数

LIMIT是限制的意思，所以LIMIT的作用就是限制查询记录的条数。

SELECT *|字段列表 [as 别名] FROM 表名 [WHERE子句] [GROUP BY子句][HAVING子句][ORDER BY子句][LIMIT子句];

• LIMIT offset,length; 或者limit length;
• offset是指偏移量，可以认为是跳过的记录数量，默认为0
• length是指需要显示的总记录数

例：查询学生表中数据，从第三条开始显示，显示6条

SELECT * FROM student3 LIMIT 2,6;

例：limit查询获取年龄最大的学生的信息

SELECT * FROM student3 ORDER BY age DESC LIMIT 0,1；

八、主外键
1、在已有表中添加主键(了解)

ALTER TABLE 表名 ADD PRIMARY KEY(字段名);

2、新建表时增加外键：

[CONSTRAINT] [外键约束名称] FOREIGN KEY(外键字段名) REFERENCES 主表名(主键字段名)；

3、已有表增加外键：

ALTER TABLE 从表 ADD [CONSTRAINT] [外键约束名称] FOREIGN KEY (外键字段名) REFERENCES 主表(主键字段名);

4、根据外键名进行删除，记住:不是外键字段名

ALTER TABLE 从表 drop foreign key 外键名称;

5.级联操作
在修改和删除主表的主键时，同时更新或删除副表的外键值，称为级联操作

• ON UPDATE CASCADE – 级联更新，主键发生更新时，外键也会更新

• ON DELETE CASCADE – 级联删除，主键发生删除时，外键也会删除

  CREATE TABLE employee (
  id INT PRIMARY KEY AUTO_INCREMENT,
  NAME VARCHAR(30),
  age INT,
  dep_id INT,
  – 添加外键约束,并且添加级联更新和级联删除
  CONSTRAINT employee_dep_fk FOREIGN KEY (dep_id) REFERENCES department(id) ON UPDATE CASCADE ON DELETE CASCADE
  );

九、内连接
隐式内连接：看不到JOIN关键字，条件使用WHERE指定

SELECT 字段名 FROM 左表, 右表 WHERE 条件；

显示内连接：使用INNER JOIN … ON语句, 可以省略INNER

SELECT 字段名 FROM 左表 INNER JOIN 右表 ON 条件；
例：SELECT * FROM dept INNER JOIN emp ON emp.`dept_id`=dept.`id`;
例：SELECT * FROM dept INNER JOIN emp ON emp.`dept_id`=dept.`id` AND emp.`NAME`='唐僧';
例：SELECT emp.`id`, emp.`NAME`, emp.`gender`, emp.`salary`, dept.`NAME` FROM dept INNER JOIN emp ON emp.`dept_id`=dept.`id` AND emp.`NAME`='唐僧';

十、外连接
左外连接：使用LEFT OUTER JOIN … ON，OUTER可以省略

SELECT 字段名 FROM 左表 LEFT OUTER JOIN 右表 ON 条件；

用左边表的记录去匹配右边表的记录，如果符合条件的则显示；否则，显示NULL

可以理解为：在内连接的基础上保证左表的数据全部显示

右外连接：使用RIGHT OUTER JOIN … ON，OUTER可以省略

SELECT 字段名 FROM 左表 RIGHT OUTER JOIN 右表 ON 条件；

用右边表的记录去匹配左边表的记录，如果符合条件的则显示；否则，显示NULL

可以理解为：在内连接的基础上保证右表的数据全部显示

十一、子查询
一条SELECT语句结果作为另一条SELECT语法一部分（查询条件，查询结果，表）

SELECT 查询字段 FROM 表 WHERE 查询条件;

例：SELECT * FROM employee WHERE salary=(SELECT MAX(salary) FROM employee);

子查询结果的三种情况：
1.子查询的结果是一个值的时候
2.子查询结果是单例多行的时候
3.子查询的结果是多行多列
说明：

子查询结果只要是单列，肯定在WHERE后面作为条件
子查询结果只要是多列，肯定在FROM后面作为表

例：根据最高工资到员工表查询到对应的员工信息
    SELECT * FROM emp WHERE salary=(SELECT MAX(salary) FROM emp);
    
例：查询工资大于5000的员工，来自于哪些部门的名字
SELECT dept.name FROM dept WHERE dept.id IN (SELECT dept_id FROM emp WHERE salary > 5000);

例：查询出2011年以后入职的员工信息，包括部门名称
SELECT * FROM dept d INNER JOIN emp e ON d.id = e.dept_id WHERE e.join_date > '2011-1-1';

多表查询规律总结：

1. 不管我们查询几张表，表连接查询会产出笛卡尔积，我们需要消除笛卡尔积，拿到正确的数据。我们需要找到表与表之间通过哪个字段关联起来的（通常是外键=主键）
2. 消除笛卡尔积规律：2张表需要1个条件，3张表需要2个条件，4张表需要3个条件。（条件数量=表的数量-1），每张表都要参与进来
3. 多表连接查询步骤：
   3.1. 确定要查询哪些表
   3.2. 确定表连接条件
   3.3. 确定查询字段

十二、MySql数据库创建用户及授权：
1、创建用户

CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';

1.用户名：将创建的用户名
2. 主机名：指定该用户在哪个主机上可以登陆，如果是本地用户可用localhost，如果想让该用户可以从任意远程主机登陆，可以使用通配符%
3. 密码：该用户的登陆密码，密码可以为空，如果为空则该用户可以不需要密码登陆服务器

例：-- user1用户只能在localhost这个IP登录mysql服务器
   CREATE USER 'user1'@'localhost' IDENTIFIED BY '123';
   -- user2用户可以在任何电脑上登录mysql服务器
   CREATE USER 'user2'@'%' IDENTIFIED BY '123';

2、授权用户
用户创建之后，基本没什么权限！需要给用户授权

GRANT 权限1, 权限2... ON 数据库名.表名 TO '用户名'@'主机名';

1.GRANT 授权关键字
2. 授予用户的权限，如SELECT，INSERT，UPDATE等。如果要授予所的权限则使用ALL
3. 数据库名.表名：该用户可以操作哪个数据库的哪些表。如果要授予该用户对所有数据库和表的相应操作权限则可用*表示，如*.*
4. '用户名'@'主机名': 给哪个用户授权

例：1.给user1用户分配对test这个数据库操作的权限
   GRANT CREATE,ALTER,DROP,INSERT,UPDATE,DELETE,SELECT ON test.* TO 'user1'@'localhost';
   2.给user2用户分配对所有数据库操作的权限
   GRANT ALL ON *.* TO 'user2'@'%';

3.撤销授权

REVOKE  权限1, 权限2... ON 数据库.表名 FROM '用户名'@'主机名';

例：撤销user1用户对test操作的权限
REVOKE ALL ON test.* FROM 'user1'@'localhost';

4.查看权限

SHOW GRANTS FOR '用户名'@'主机名';

5.删除用户

DROP USER '用户名'@'主机名';

6.修改用户密码

例：1.修改管理员密码
    mysqladmin -uroot -p password 新密码  -- 新密码不需要加上引号
   2.修改普通用户密码
   set password for '用户名'@'主机名' = password('新密码');