mysql入门学习笔记 | 您所在的位置:网站首页 › rename 语法英语 › mysql入门学习笔记 |
最近在软件测试培训班内学习嘛 刚好在教这方面的东西 就把现有的笔记复制上来 就当云备份了 但毕竟不是专业的 应付普通的增删改查完全够用了 就没学数据库的设计规范 mysql 数据库(DB)是按照 数据结构 来组织 存储 和管理数据的仓库 数据库管理系统(DBMS)是指数据库系统中对数据进行管理的软件系统 数据库类型1关系型数据库(SQL server Oracle MySQL DB2 Access sybase) 2非关系形数据库(nosql mongodb redis HBase) 关系型数据库的特点建立在关系模型上的数据库 借助集合 代数 等数学概念 和 方法来处理数据库中的数据 RDBMS 关系数据库管理系统 的特点(Relational Database Management System ) 数据以表格的形式出现 每行为各种记录名称 每列为记录名称所对应的数据域 许多的行和列组成一张表单 若干的表单组成database 什么是mysql 关系型数据库管理系统 由瑞典mysqlAB公司开发 目前属于oracle 关联数据库将数据保存在不同的表中 而不是将所有数据放在一个大仓库内 结果:增加了速度并提高了灵活性 mysql的优点 mysql是开源免费的 无需支付额外费用 支持大型数据库 可以处理拥有上千万条记录的大型数据库 mysql使用标准的SQL数据语言形式 可以运行于多个系统 支持多种语言。这些语言包括 C C++ Python Java perl php eiffel ruby TCL等 mysql对PHP有很好的支持 php是最流行的Web开发系统 mysql支持大型数据库 可以支持5000万条记录的数据仓库,32为系统表文件最大可支持4GB,64位系统支持最大的表为8TB Mysql是可以定制的 采用GPL协议 可通过修改源码来开发自己的mysql系统 Mysql基础操作 杂项 目录介绍 如何查看mysql端口号进入安装路径 找到my.ini文件 记事本打开 查看端口号 默认为3306 如何启动和关闭mysqlservices.msc 手动启动或者关闭mysql服务 cmd关闭 net stop mysql cmd启动 net start mysql 查看mysql版本号MySQL -V 登录mysql-u 后接用户名 -p 后接密码 -V 输出版本号 -h 主机地址 退出mysql\q quit exit ctrl+c 退出 mysql -uroot -p -h127.-0.0 .1 常用命令 1修改密码mysqladmin -u用户名 -p密码 password 新密码 2显示所有数据库show databases; 3使用指定数据库use 数据库名; 4显示当前连接的数据库名字select database(); 5进入mysql显示mysql版本select version();或者status 6进入mysql显示系统时间select now(); 7进入mysql显示当前身份select user(); 8进入mysql显示登入账号密码select password('用户名'); 密码加密 第二天 创建数据库语法create database if not exists 库名 DEFAULT character set 字符集; 修改数据库字符集alert database 数据库名 character set 字符集; 查看数据库字符集使用库 之后 show variables like ‘character%’ show create database 数据库; 删除数据库drop database [if exists] 库名; if exitsts 是否存在 存在就删除 不存在也不会直接报错 有提示 mysql数据类型(概念 不记) 什么是数据类型数据类型是指 数据列 存储过程参数 表达式 和局部变量的数据特征 它决定了数据的存储格式 代表了不同的信息类型 整形TINYINT 有符号-128--127 无符号 0-255 SMALLINT -32768 32767 无符号0-65535 MEDIUMINT INT BIGINT 浮点型FLOAT(M,D) DOUBLE(M,D) 定点型DECIMAL(M,D) M是精度=整数+小数位 D是标度(小数点后位数) 日期时间形YEAR YYYY TIME HH:MM:SS DATE YYYY-MM-DD DATETIME YYYY-MM-DD HH:MM:SS TIMESTAMP YYYY-MM-DD HH:MM:SS 字符型char(M) varchar tinytext text mediumtext longtext ENUM(' ',' ') SET(' ',' ') 数据表是数据库最重要的组成部分之一 是其他对象的基础 mysql常见的整型数据类型 笔记记录tinyint smallint mediumint int bigint 浮点型float double decimal 时间型year time date datetime 字符型char varchar text enum(' ',' ')枚举型 创建表的命令create table 表名(表头 数据类型,表头2 数据类型,表头3 数据类型); 显示数据库表格show tables from 库名字; 显示表结构show columns from 表名; describe 表名; desc 表名; show create table 表名; 查看数据库表创建语句show create table 表名字; 第三天 修改数据库表的结构(增加字段)alter table 表名 add 表头 数据类型 [约束条件 first|after 表头]; 修改列名 (改表头)alter table 表名 change 旧列名 新列名 数据类型; 可以用这改数据类型 新旧列名一样 修改列的数据类型alter t able 表名 modify 列名 数据类型; 修改列的排列位置alter table 表名 modify 表头 数据类型 first|after 表头2; 删除列alter table 表名 drop 表头; 第四天 修改表名字alter table 旧表名 rename to 新表名; 删表DROP TABLE [IF EXISTS] 表1,表2,表3 删除没有被关联的表 如果多表关联 得解除外键关联关系之后才能删表 表分区表承载数据量很大时 得去考虑优化 进行表分区 将一张表的数据分成多个区块 区块可以放在同一个磁盘 也可以放在多个磁盘 但是数据必须在一个表中 前提是数据库得支持 显示是否支持分区show plugins; 分区有两种方式 水平和垂直 创建表分区partition by 类型(字段) range分区指定某列范围值来进行分区 使用 values less than来定义分区 partition by range(表头)( partition 区块名 value less than(数值),可同时分多个区 ); select * from 表名 partition(区块名); 约束类型 放在建表语句最后 约束的介绍约束是一种限制 对表的行或列的数据做出限制 来确保表的数据的完整性 唯一性 非空约束NOT NULL 添加数据时设置了非空字段的值不能为空 否则会报错 主键约束PRIMARY KEY 要求主键列的数据唯一 并且不能为空 唯一标识表中的一条记录 分为单字段主键 和 多字段联合主键 唯一约束UNIQUE 唯一约束 保证该字段值唯一,但可为空 主键和唯一键的区别: ①都可以保证唯一性②主键不允许为空,唯一允许为空(只能插入一个null)③一个表中至多一个主键,唯一键可以有多个④都可以组合使用(不推荐) 默认约束DEFAULT 默认约束 用于保证该字段有默认值 外键约束FOREIGN KEY ①要求在从表设置外键关系 ②从表的外键列类型和主表的关联列类型要求一致或兼容,名称无要求 ③主表的关联列必须是一个key(一般是主键) ④要求插入数据时,先插入主表,再插入从表,删除数据时,先删除从表,再删除主表。 修改表时添加约束: [CONSTRAINT ] FOREIGN KEY 字段名 [,字段名2,…] REFERENCES 主键列1 [,主键列2,…] --------------------------------------------------------------------------------------------------------- 创建表并给某个字段设置约束1.create table 表名字( 表头 数据类型 约束 ); 2.在列定义后边指定约束constraint 约束名 (primary key) 表头; 删除约束alter table 表名 modify 表头 数据类型; 建表之后 给某个字段设置约束 alter table 表名 modify 表头 数据类型 约束; alter table 表名 add 约束(表头); alter table 表名 add constraint 约束名 约束(表头); 删除主键alter table 表名 DROP primary key; 多字段联合主键parmary key (表头1,表头2,表头3); create table 表名( 表头 数据类型, 表头2 数据类型, 表头3 数据类型, parmary key (表头1,表头2,表头3); ); 添加非空约束:alter table 表名 modify column 列名 数据类型 not null 添加默认约束:alter table 表名 modify column 列名 数据类型 default 默认值 添加主键约束(主键支持列级约束和表级约束):alter table 表名 modify column 列名 数据类型 primary key alter table 表名 add primary key(列名) 添加唯一约束(唯一键支持列级约束和表级约束):alter table 表名 modify column 列名 类型 unique alter table 表名 add unique(列名) 添加外键约束:alter table 子表表名 add foreign key(列名) references 主表表名(列名) 删除非空约束:alter table 表名 modify column 列名 类型 null 删除默认约束:alter table 表名 modify column 列名 类型 删除主键约束:alter table 表名 drop primary key 删除唯一键约束:alter table 表名 drop index 键名 删除外键约束:alter table 表名 drop foreign key 键名 insert 插入数据 基础插入insert into 表名(表头1)values(字段1,字段2) insert into 表名 values(字段1,字段2); 过滤 插入查询数据insert into 表名 select * from 表名 where 条件; 设置自动增长auto_increment;创表时直接当成约束使用 alter table 表名 modify 表头 数据类型 auto_increment 设置自增初始值 alter table 表名 auto_increment=初始值 单表数据记录更新update 表名 set column_name1 = value1, column_name2 = value2 where 条件 删除单表记录delete from 表名 truncate [table] 表名 区别 delete后面可以带条件 truncate 不能带条件 delete速度低 效率慢 truncate 速度快 效率高 查询数据 查询单表数据select * from table 查询单表多字段内容select 表头1,表头2 from table 查询过滤重复数据内容select distinct 表头 from table 查询空数据select * from table where 表头 is null group by分组查询group by 列名字 having 条件表达式 select * from table group by 表头 having 条件 group分组 having 过滤 先分组再过滤 order by排序ORDER BY column ASC/DESC;默认升序 limit 查看指定数量表单数据select * from table limit 数字; asc升序 默认 desc降序 limit 指定条(默认第0条) , 数量 limit 0,3 limit 数量 offset 指定条开始; limit 3 offset 0 运算符+,-,*,/,%(取余) 比较运算符< , > ,>= , 18 or balance=0 日期时间函数 获取日期函数 CURDATE(); current_date(); 获取时间函数 curtime(); current_time(); 默认输出为hh:mm:ss 加数字为hhmmss 获取当前时间 now() | sysdate()输出格式为yyyy-mm-dd-ss date_add('date',interval expr type)执行日期加运算date参数yyyy-mm-dd interval添加 expr(数值) type(属性) month|year|day|hour|week 计算时间差 datediff('time1','time2');time1-time2 日期格式化 DATE_FORMAT(date,format)%b 月份 英文缩写 %c 月份 单数字 %m 月份 双数字 %M 月份 全英文 %d 日期 双数字 %e 日期 单数字 %Y 年份 4位数 %y 年份 2位数 聚合函数聚合函数 分组函数 1 AVG()返回平均值 2 count()计数 3 MAX()返回某列的最大值 4 MIN()返回某列的最小值 5 SUM()返回某列的和 系统信息函数 version() 返回当前mysql服务器版本号 connection_ID() 返回Mysql 服务器当前链接的次数 database() 和 schema();返回当前数据库名字 user() 返回当前登录用户 加密函数 md5(str);信息摘要算法加密后位32位十六进制数字的二进制字符串形式返回 参数为null则返回null password();密码算法password(str)从原明文密码str计算并返回加密后的密码字符 set password = password('新密码') 查询另一个库user表的例子select user,password from mysql.user 子查询子查询是指嵌套在其他sql语句中的查询语句 any all some关键字子查询any some同义词 表示满足内层子查询中的任何一个条件 all表示需要满足所有内层查询的条件 not in existsexists 如果存在 多表链接查询 内链接[inner] join 例 select 表头 from table [inner] join table2 on 表1.表头 = 表2.表头 外连接left right full 例 select 表头 from table [left|right| full] join table2 on 表1.表头 = 表2.表头 两表以上连接例 select 表头 from table [inner] join table2 on 表1.表头 = 表2.表头 [inner] join table3 on 表1.表头 = 表3.表头 多表更新update 表1 inner join 表2 on 表1.id=表2.id set 条件 where 表1.表头=‘’; 复制表到新表create table table2 as select * from table1; 多表删除的语法delete t1,t2 from table inner table2 on table.id=table2.id where 条件 函数 自定义函数 1 直接写create function 函数名(传入数据 数据类型) returns 数据类型 return DATE_FORMAT(传入数据,'%Y-%m'); 2 函数体修饰DELIMITER // create function 函数名(传入数据 数据类型) returns 数据类型 BEGIN return DATE_FORMAT(传入数据,'%Y-%m'); END // DELIMITER; 函数类型 3 无参函数create function 函数名() returns 数据类型 return DATE_FORMAT(传入数据,'%Y-%m'); 4 通过变量的形式创建自定义函数create function show_name_store(bid int) returns varchar(50) return select concat_ws('---',book_name,store) from bookinfo where bid=book_id 删除函数drop funciton [if exist] function_name 流程控制 1声明变量DECLARE VAR_NAME date_type[] 例如 declare num int default 10; 2变量赋值 声明后赋值1 set VAR_NAME=expr [,var_name = expr] 例如 set num = 100 2 select col_name[,....] into var_name[,.....] table_expr 例如 select store INTO num From bookinfo; 3 if选择在bengin end之间 格式 if 条件 then 触发后执行事件; elseif 条件 then 触发后执行事件; else 触发后执行事件; end if; return DELIMITER // CREATE FUNCTION level(CID VARCHAR(18)) RETURNS VARCHAR(20) BEGIN DECLARE VLEVEL VARCHAR(10); DECLARE MONEY DECIMAL(7,3); SELECT BALABCE INTO MONEY FROM bookreader where custom_id=CID; IF MONEY >=500 THEN SET VLEVEL = '金牌'; ELSEIF MONEY>=300 THEN SET VLEVEL = '银牌'; ELSEIF MONEY>=200 THEN SET VLEVEL = '普通'; ELSE set vlevel = '非会员'; end if; return vlevel; end// delimiter ; 4 CASE分支语句1 CASE CASE_EXPR WHEN when_value THEN statement_list ELSE 条件语句 END CASE 5 while 循环 DELIMITER // create function get_avg(n int) returns int begin declare num1 int default 0; declare num2 int default 0; while num1100 then leave [lab1] end if; end loop [lab1] 7 repeat repeat set num1 = num1 + 1; set num2 = num2 + num1 until num>=n end repeat; return sum; mysql常见流程函数case while......do....... loop repeat 存储过程 什么是存储过程存储过程是 sql语句 和 流程控制语句 的预编译集合 并以一个名称存储 并作为一个单元进行处理 语法结构create procedure 存储过程名称([ IN|OUT|INOUT ] 传入参数 类型) routine_body [过程体] in 必须在调用存储过程时被指定 out该参数的值可以被存储过程改变 并且可以返回 inout 表示该参数调用时指定并且可以被改变和返回 call 存储过程名称(); 调用存储过程 过程体过程体是合法sql语句 过程体可以是任意sql语句 过程体如果为复合结构就使用begin......end语句 复合结构可以包含声明,流程控制语句 例子 无参 create procedure proc() select * from readerinfo; call proc()调用 有参 create procedure proc1(in cid char(18),out num int) delete from readerinfo where card_id=cid; select count(card_id) into num from readerinfo; call proc1('cid',@num) 标准格式 delimiter // create procedure proc1(in cid char(18),out num int) begin delete from readerinfo where card_id=cid; select count(card_id) into num from readerinfo; end // delimiter ; call proc1('cid',@num) 两数互换 delimiter // create procedure proc2(in num1 int,out num2,int) begin declare temp int default 0; 函数内声明 set temp = num1; set num1 = num2; set num2 = temp; end // delimiter; set @num1 = 1; 全局定义 set @num2 = 2; call proc2(@num1,@num2) select @num1,@num2; 往表内添加100条数据 delimiter // create procedure proc3(in num int) begin declare id int default 0 ; declare temp int default 0; select max(category_id) into id from bookcategory; set temp = id; while id 指定路径mysqldump -u user -p password -h localhost 数据库名 库内表名>指定路径 mysqldump -u user -p password -h localhost --databases 数据库名 数据库名>指定路径 mysqldump -u user -p password -h localhost --all -databases >指定路径 恢复备份mysql -uroot -proot 表名路径 |
CopyRight 2018-2019 实验室设备网 版权所有 |