Mysql从入门到精通

最新推荐文章于 2024-04-05 05:24:48 发布

WinHly

最新推荐文章于 2024-04-05 05:24:48 发布

阅读量479

点赞数

文章标签： mysql 数据库 sql

本文链接：https://blog.csdn.net/qq_29737471/article/details/122153124

版权

显示数据库语句
   show databases
显示创建数据库的语句
   show create database 库名
修改数据库:
   alter DATABASE 库名 charset = 字符集(utf8或者utf8mb4)
删除数据库
   drop databse 库名
使用数据库
   use 库名

备份数据库
   cmd命令下执行:
   备份数据库表中的数据
   mysqldump -u 用户名 -p 数据库名 > 文件名.sql
恢复数据库
   mysql –u root -p database_name < 文件名.sql; 还原某个数据库,要求该数据库已经创建成功



创建表
   create table 表名(
   字段名字段类型
   字段名字段类型
   )charset = 字符集(utf8或者utf8mb4)

数据库数据类型
   数值类型
   BIT(M)               位类型。M指定位数，默认值1，范围1-64
   INT[UNSIGNED]       带符号是负的2的31次方到2的31次方-1 ,无符号2的32方-1
   TINYINT[UNSIGNED]   带符号的范围是-128到127。无符号0到255。默认是有符号
   FLOAT[(M,D)][UNSIGNED]   M指定豆示长度,d指定小数位数,占用空间4个字节
   DOUBLE[(M,D)] [UNSIGNED]    表示比float精度更大的小数,占用空间8个字节
   DECIMAL(M,D) [UNSIGNED]   定点数M指定长度,D表示小数点的位数

   文本类型
       CHAR(size) char(20)       固定长度字符串最大255
       VARCHAR(size) varchar(20)   可变长度字符串最大65532【在mysql5.0中最大21844】
       TEXT LONGTEXT   大文本,不支持全文索引,不支持黑默认值,建议使用varchar

   时间日期
       DATE/DATETIME/TimeStamp      日期类型(YYYY-MM-DD) (YYYY-MM-DD HH:MM:SS)，TimeStamp表示时间戳，它可用于自动记录insert、update操作的时间

   字符串类型
       ENUM类型〈枚举) ENUM是一个字符串对象,其值来自表创建时在列规定中显式枚举的一列值。
       SET类型（集合) SET是一个字符串对象，可以有零或多个值,其值来自表创建时规定的允许的一列值。指定包括多个SET成员的SET列值时各成员之间用逗号(,间隔开。这样SET成员值本身不能包含逗号。SET最多可以有64个不同的成员

修改表


表的增删改查
   insert (添加)
       inseet into 表名(字段名,字段名) values(值,值)
       insert into student(name,math_score,english_score,mandarin_score) values('hhh',99,96,89);
   delete (删除)
       delete from 表名 where 条件
       delete from student where name = 'Coder';
   updata   (修改)
       update 表名 set 字段名 = 新值 where 条件
       update student set name = 'Coder' where name = 'hhh';

   select    (查询)
       格式:
       SELECT       [字段列表] | *
       FROM        表名
       WHERE        搜索条件
       GROUP BY    分组字段 [ HAVING 子条件 ]
       ORDER BY    排序 ASC | DESC
       LIMIT        分页参数
       顺序必须是这个顺序
       select 字段列表 [from子句] 表名 [where子句] [group by子句] [having子句] [order by子句] [limit子句]；

多表查询(重点)
   #多表连接查询语法(重点)
   SELECT 字段列表
       FROM 表1 INNER | LEFT | RIGHT JOIN 表2
   ON 表1.字段 = 表2.字段;

   内联查询
       SELECT * FROM 表1 INNER JOIN 表2 ON 表1.ID=表2.ID;
   左联查询 (以左表为主)
       SELECT * FROM 左表 LEFT JOIN 右表 ON 左表.ID=右表ID;
   右联查询 (以右表为主)
       SELECT * FROM 左表 RIGHT JOIN 右表 ON 左表.ID= 右表.ID;


查看权限
   show grants for root@localhost



MySql权限
   All/All Privileges权限代表全局或者全数据库对象级别的所有权限
   Alter权限代表允许修改表结构的权限，但必须要求有create和insert权限配合。如果是rename表名，则要求有alter和drop原表， create和insert新表的权限
   Alter routine权限代表允许修改或者删除存储过程、函数的权限
   Create权限代表允许创建新的数据库和表的权限
   Create routine权限代表允许创建存储过程、函数的权限
   Create tablespace权限代表允许创建、修改、删除表空间和日志组的权限
   Create temporary tables权限代表允许创建临时表的权限
   Create user权限代表允许创建、修改、删除、重命名user的权限
   Create view权限代表允许创建视图的权限
   Delete权限代表允许删除行数据的权限
   Drop权限代表允许删除数据库、表、视图的权限，包括truncate table命令
   Event权限代表允许查询，创建，修改，删除MySQL事件
   Execute权限代表允许执行存储过程和函数的权限
   File权限代表允许在MySQL可以访问的目录进行读写磁盘文件操作，可使用的命令包括load data infile,select … into outfile,load file()函数
   Grant option权限代表是否允许此用户授权或者收回给其他用户你给予的权限,重新付给管理员的时候需要加上这个权限
   Index权限代表是否允许创建和删除索引
   Insert权限代表是否允许在表里插入数据，同时在执行analyze table,optimize table,repair table语句的时候也需要insert权限
   Lock权限代表允许对拥有select权限的表进行锁定，以防止其他链接对此表的读或写

   Process权限代表允许查看MySQL中的进程信息，比如执行show processlist, mysqladmin processlist, show engine等命令
   Reference权限是在5.7.6版本之后引入，代表是否允许创建外键
   Reload权限代表允许执行flush命令，指明重新加载权限表到系统内存中，refresh命令代表关闭和重新开启日志文件并刷新所有的表
   Replication client权限代表允许执行show master status,show slave status,show binary logs命令
   Replication slave权限代表允许slave主机通过此用户连接master以便建立主从复制关系
   Select权限代表允许从表中查看数据，某些不查询表数据的select执行则不需要此权限，如Select 1+1， Select PI()+2；而且select权限在执行update/delete语句中含有where条件的情况下也是需要的
   Show databases权限代表通过执行show databases命令查看所有的数据库名
   Show view权限代表通过执行show create view命令查看视图创建的语句
   Shutdown权限代表允许关闭数据库实例，执行语句包括mysqladmin shutdown
   Super权限代表允许执行一系列数据库管理命令，包括kill强制关闭某个连接命令， change master to创建复制关系命令，以及create/alter/drop server等命令
   Trigger权限代表允许创建，删除，执行，显示触发器的权限
   Update权限代表允许修改表中的数据的权限
   Usage权限是创建一个用户之后的默认权限，其本身代表连接登录权限

MySql函数
   1)数据库的相关函数
       version() 数据库的版本
       database() 数据库的名字
       user() 用户名
       md5() 字符串的md5加密
       password() 计算并返回密码字符串
       current_user() 当前的用户名
       system_user() 系统用户名
       @@datadir 数据库的路径
       @@version_compile_os 操作系统
       @@version 数据库的版本

   2)数据库字符串函数
       length()     字符串长度
       substring()       截取字符串
       substr()   截取字符串
       mid()       截取字符串从1开始取 mid('abcd' , 1, 2) ==> ab
       left()           从左侧开始截取几个字符
       ucase()           转化成大写
       lcase()           转化成小写
       replace()       替换字符串 replace(str,serach,replace)
       concat() 没有符号的链接字符串 concat('a','b','c')
       concat_ws()       含有符号的链接字符串 concat_ws('^','a','b','c')
       group_concat() 将多条记录的单个字段合并成一条记录 group_concat(name)

   3)数据库数学函数
       ord() 返回ASCII码值
       ascii() 返回ASCII码值
       hex() 换算成十六进制形式
       floor() 向下取整
       ceil() 向上取整
       round()   四舍五入
       rand() 0~1之间的随机数

   4)数据库读取文件函数
       load_file() 读取文件
       select load_file('C:/xampp/htdocs/as.txt'); 使用/这个
       select load_file('C:\\xampp/htdocs/xxoo.html');

       into outfile() 写文件操作
       select '<?php phpinfo(); ?>' into outfile 'C:/xampp/htdocs/as.txt';
       注意:
       文件名必须全路径(绝对路径)
       用户必须有写文件的权限 root
       没有对引号(单引号和双引号) 过滤

   5)数据库其他函数
       sleep() 睡眠时间以秒为单位
       if(表达式,true, false)    if 判断语句
       select if(substr(database(),1,1) = 'p',sleep(3),1);

       benchmark() 指令运行的次数
       select benchmark(100, MD5(2222));

创建MySql授权用户
   执行create user/grant命令（推荐方式）

通过insert语句直接操作MySQL系统权限表

   # 创建finley 这只是创建用户并没有权限
   mysql> CREATE USER ‘bihuo'@'localhost' IDENTIFIED BY ‘bihuo.cn';
   # 把finley 变成管理员用户
   mysql> GRANT ALL PRIVILEGES ON *.* TO ‘bihuo'@'localhost' WITH
   GRANT OPTION;
   #创建用户并赋予RELOAD,PROCESS权限，在所有的库和表上
   mysql> GRANT RELOAD,PROCESS ON *.* TO ‘bihuo'@'localhost' identified by '123456';

   # 创建keme用户，在test库，temp表，上的id列只有select 权限
   mysql> grant select(id) on test.temp to bihuo@'localhost' identified by '123456';

回收mysql权限
   通过revoke命令收回用户权限,回收的时候看一下这个用户有哪些权限然后回收
   mysql> show grants for bihuo@'localhost';
   mysql> select user,host from mysql.user;
   mysql> revoke PROCESS ON *.* FROM bihuo@'localhost';

删除MySql用户
   通过执行drop user命令删除MySQL用户
   mysql> drop user bihuo@'localhost';

修改MySql用户密码
   mysql> SET PASSWORD FOR ‘bihuo’@'localhost' = PASSWORD('mypass');
   mysql> GRANT USAGE ON *.* TO ‘bihuo'@'localhost' IDENTIFIED BY 'mypass’;
   修改当前会话本身用户密码的方式包括：
   mysql> SET PASSWORD = PASSWORD('mypass');

授权
   GRANT all PRIVILEGES ON *.* TO 'root'@'%'IDENTIFIED BY '123456';

Mysql.ini配置
   secure-file-priv=
   secure-file-priv == 空此时可以读取文件的内容
       blob 数据类型用来存储二进制数据

   当secure-file-priv == NULL 无法读取文件的内容(读取的都为NULL)