Mysql数据库基础知识笔记

Mysql数据库

目前刚开始学习Mysql,对自己所学的内容做一个总结,会慢慢进行补充!

• 创建数据库

-- 创建数据库
CREATE database database_name;   
-- 创建指定字符集的数据库
CREATE database database_name character set=gbk/utf8;  
-- 创建数据库前判断是否存在同名数据库
CREATE database if not exists database_name;  

• 数据库基本操作

-- 查看数据库
SHOW databases; 
-- 显示数据库创建信息
SHOW create database 库名; 
-- 查看当前数据库
SELECT database();  
-- 设置默认字符集
ALTER database database_name default character set gbk/utf8; 
-- 删除数据库
DROP database database_name;  

• 数据表基本操作

-- 创建数据表
CREATE table 表名(字段名1 类型(长度) [约束],字段名2 类型(长度) [约束],......);
-- 查看数据表
SHOW tables; 
-- 查看表结构
desc table_name; 
-- 向表中新添加列,[]内表示在某列的前或后添加,不指定则添加到最后
ALTER table table_name add 列定义 [first/after 列名];  
-- 列名重定义
ALTER table table_name change 列名 原列名;  
-- 修改列数据类型
ALTER table table_name modify 列定义;  
-- 删除列
ALTER table table_name drop 列名;  
-- 重命名该表
ALTER table table_name rename 新表名;  
-- 排序
ALTER table table_name order by 列名;  
-- 指定修改默认值或删除
ALTER table table_name alter 列名 {set default literal/drop default} 
-- 删除表
DROP table table_name; 
-- 创建一个结构相同的空表
CREATE table table_name1 like table_name2; 

• 用户管理基本操作

-- 创建新用户  用户='用户名'@'主机名'
CREATE user 用户 identified by 'password'; 
-- 删除用户
DROP user 用户; 
-- 授权用户权限 table_name可用*代表所有表
GRANT privileges on database_name.table_name to 用户; 
-- 回收用户权限
REVOKE privileges on daatabase_name.table_name from 用户;  
	 /*其中privileges可以包含:CREATE,ALTER,DROP:库/表结构操作
						 SELECT,INSERT,DELETE,UPDATE:数据操作
						 REFERENCES:外键权限
						 INDEX:索引 */
-- 修改用户名
RENAME user 老用户 to 新用户;  
-- 修改用户密码
SET PASSWORD for 用户=password('新密码');  
-- 删除用户
DROP user 用户; 

• 数据插入

-- 插入新记录方式一,可插入多条
INSERT into table_name(列名1,列名2,...)  values  (记录1,记录2,...),(记录1,记录2,...),(...); 
--  插入新记录方式二
INSERT into table_name set 列名1=记录1,列名2=记录2,...; 
-- 从已有的表中插入数据进来
INSERT into table_name1 select * from table_name2; 
-- 从一个库的一张表记录插入到另一个库的另一张表中
INSERT into database_name1.table_name1 select * from database_name2.table_name2; 

• 数据修改

-- 修改表数据
UPDATE table_name1,table_name2,... set 列名1=expr1,列名2=expr2,...  where 条件; 
-- 将表1的列名1设置成跟表2的列名2一样
UPDATE table_name1 a,table_name2 b set a.列名1=b.列名2 where 条件; 

• 数据删除

-- 删除数据表中的所有记录
DELETE from table_name;  
-- 删除满足条件的行
DELETE from table_name where 条件;  
-- 刪除user1中id值等於user的所有行和user2的id值等于user的id值的所有行 
DELETE user1,user2 from user1,user2,user where user1.id=user.id and user2.id=user.id; 

• 数据查询

常规查询
-- 查询tb_course表中所有字段
SELECT * FROM tb_course; 
-- 查询tb_student表中各个学生的sname,sage,edate
SELECT sname,sage,edate FROM tb_student; 
-- 查询tb_student表中的sno,sname,ssex,结果中各字段的标题分别指定为学号,姓名,性别
SELECT sno 学号,sname 姓名,ssex 性别 FROM tb_student; 
-- 替换查询结果中的数据
CASE
WHEN 条件1 THEN 表达式1
WHEN 条件2 THEN 表达式2
……
ELSE 表达式n
END (别名)
FROM tb_name WHERE 条件;
SELECT sno,cno,
-- 查询tb_mark表中cno='c002'的成绩信息,对成绩小于60的替换为'不及格';若在60-89之间替换为'及格',90分以上为'优秀'.若grade为空值,则替换为'缺考'.case表达式的别名改为'成绩等级'.
CASE 
WHEN grade<60 THEN '不及格'
WHEN grade BETWEEN 60 AND 89 THEN '及格'
WHEN grade BETWEEN 90 AND 100 THEN '优秀'
ELSE '缺考'
END 成绩等级
FROM tb_mark WHERE cno='c002';
条件查询
SELECT 字段名 FROM 表名 WHERE 条件;
-- 比较运算符
= <>,!= > < >= >=
-- 查询学生表中sage在18到20之间的snmae  //范围比较
SELECT sname FROM tb_student where sage between 18 and 20;
SELECT sname FROM tb_student where sage>=18 and sage<=20;
-- 查询学生表中19软件1班和19软件6班学生情况 
SELECT * FROM tb_student where classname in('19软件1班','19软件6班');
-- 查询学生表中除了19软件1班和19软件6班其他班的学生情况   
SELECT * FROM tb_student where classname not in('19软件1班','19软件6班');
-- 查询学生表中手机号不为空的学生情况  //空值比较
SELECT * FROM tb_student where tel is not null;
-- 查询未选修Mysql数据库应用的学生的姓名、学号  //in子查询
SELECT sno,sname FROM tb_student 
->where sno not in(SELECT sno FROM tb_mark
->where cno in(SELECT cno FROM tb_course where cname='MySQL数据库应用'));
-- 比较子查询
表达式{比较运算符}{ALL|ANY}
-- 查找年龄比19软件4班学生年龄都大的学生信息
SELECT * FROM tb_student 
->WHERE sage>ALL(
->SELECT sage FROM tb_student WHERE classname='19软件4班');
-- 查找年龄比19软件4班学生年龄任意一位大的学生信息
SELECT * FROM tb_student 
->WHERE sage>ANY(
->SELECT sage FROM tb_student WHERE classname='19软件4班');

• show语句

SHOW tables或 SHOW tables from database_name; //显示当前数据库中所有表名称
SHOW databases; //显示所有数据库的名称
SHOW columns from table_name from database_name 或 SHOW columns from database_name.table_name;  //显示表中列的名称
SHOW grants for user_name;  //显示一个用户的权限,显示结果类似于grant命令
SHOW index from table_name; //显示表的索引
SHOW status; // 显示一些系统特定资源的信息,例如正在运行的线程数量
SHOW variables; //显示系统变量的名称和值
SHOW processlist; //显示系统中正在运行的所有进程
SHOW table status; //显示当前使用或者指定的database中的每个表的信息;
SHOW privileges: //显示服务器所支持的不同权限。
SHOW create database database_ name: //显示创建某一个数据库的CREATEDATABASE语句。
SHOW create table table_name: //显示创建一个表的 CREATE TABLE语句。
SHOW events; //显示所有事件的列表。
SHOW innodb status;  //显示innoDB存储引擎的状态。
SHOW logs;  //显示BDB存储引擎的日志。
SHOW warnings;  //显示最后一个执行的语句所产生的错误、警告和通知
SHOW errors;  //只显示最后-一个执行语句所产生的错误。
SHOW [storage] engines;  //显示安装后的可用存储引擎和默认引擎。
SHOW procedure status;  //显示数据库中所有存储过程基本信息，包括所属数据库、存储过程名称、创建时间等。
SHOW create proceduresp_name;  /显示某一个存储过程的详细信息。