搜索到
1
篇与
的结果
-
MySql的学习笔记！ 1.创建数据库CHARACTER SET: 指定数据库采用的字符集，如果不指定字符集，默认utf8COLLATE: 指定数据库字符集的校对规则(常用的utf8 bin,utf8 general ci注意默认是utf8 general ci)实例：使用指令创建数据库 CREATE DATABASE 名字;删除数据库指令 DROP DATABASE 名字;创建一个使用utf8字符集的hsp db02数据库REATE DATABASE hsp db02 CHARACTER SET utf8创建一个使用utf8字符集，并带校对规则的hsp db03数据库REATE DATABASE hsp db03 CHARACTER SET utf8 COLLATE utf8 bin校对规则utf8 bin区分大小默认ut_general_ci不区分天小写下面是一条查询的sql , select查询*表示所有字段FROM从哪个表WHERE从哪个字段NAME= 'tom'查询名字是tomSELECT * FROM t1 WHERE NAME=' tom'查看、删除、备份、恢复数据库显示数据库语句: SHOW DATABASES显示数据库创建语句: SHOW CREATE DATABASE db_ name数据库删除语句【一定慎用！】: DROP DATABASE [IF EXISTS] db_ name备份数据库(注意:在DOS执行)命令行 mysqldump-u用户名-p-B数据库1数据库2数据库n>文件名.sql恢复数据库(注意:进入SQLyog再执行) Source文件名.sql备份库的表 mysqldump-u 用户名-p密码数据库表1表2表n >d:\文件名.sql创建表CREATE TABLE table_name ( field1 datatype, field2 datatype, field3 datatype )character set字符集collate校对规则engine引擎 field:指定列名datatype:指定列类型(字段类型) character set :如不指定则为所在数据库字符集 collate:如不指定则为所在数据库校对规则 engine:引擎(这个涉及内容较多，后面单独讲解) 注意:创建表时，要根据需保存的数据创建相应的列，并根据数据的类型定义相应的列类型。例:user表 id 整形name 字符串password 字符串birthday 日期数值型(bit)的使用1.基本使用 mysql> create table t02(num bit(8));mysql> insert into t02 (1,3);mysql> insert into t02 values(2, 65);2.细节说明 . bit字段显示时，按照位的方式显示..查询的时候仍然可以用使用添加的数值·如果一个值只有0,1可以考虑使用bit(1),可以节约空间.位类型。M指定位数，默认值1，范围1-64.使用不多.数值型(小数)的基本使用1.FLOAT/DOUBLE [UNSIGNED]Float单精度精度,Double双精度·2.DECIMAL[M,D] [UNSIGNED]·可以支持更加精确的小数位。M是小数位数(精度)的总数，D是小数点(标度)后面的位数。·如果D是0，则值没有小数点或分数部分。M最大65。D最大是30。如果D被省略，默认是0。如果M被省略，默认是10。·建议:如果希望小数的精度高，推荐使用decimal字符串的基本使用 CHAR(size)固定长度字符串最大255字符VARCHAR(size)可变长度字符串最大65532字节【utf8编码最大21844字符1-3个字节用于记录大小】 字符串使用细节细节 char(4)//这个4表示字符数(最大255)，不是字节数,不管是中文还是字母都是放四个,按字符计算. varchar(4)//这个4表示字符数,不管是字母还是中文都以定义好的表的编码来存放数据。 不管是中文还是英文字母,都是最多存放4个,是按照字符来存放的.细节2 char(4)是定长(固定的大小)，就是说，即使你插入'aa'， 也会占用分配的4个字符的空间. varchar(4)是变长，就是说，如果你插入了'aa',实际占用空间大小并不是4个字符，而是按照实际占用空间来分配 (说明:varchar本身还需要占用1-3个字节来记录存放内容长度) L(实际数据大小) + （1-3）字节细节3 什么时候使用char,什么时候使用varchar 1.如果数据是定长,推荐使用char,比如md5的密码,邮编，手机号,身份证号码等. char(32) 2.如果一个字段的长度是不确定，我们使用varchar,比如：留言，文章查询速度:( char> varchar ）细节4 在存放文本时，也可以使用Text数据类型.可以将TEXT列视为VARCHAR列,注意Text不能有默认值.大小0-2^16字节如果希望存放更多字符，可以选择MEDIUMTEXT O-2^24 或者LONGTEXT 0~2^32日期类型的基本使用CREATE TABLE birthday6 (t1 DATE, t2 DATETIME, t3 TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ; timestamp时间戳 mysql> INSERT INTO birthday (t1,t2) VALUES('2022-11-11','2022-11-11 10:10:10');日期类型的细节说明 TimeStamp在Insert和update时，自动更新演示时间相关类型#创建一张表，data ， datetime ， timestamp CREATE TABLE t14 ( birthday DATE, -- 生日 job_time DATETIME, -- 记录年月日 时分秒 login_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP -- 登陆时间 如果希望login_time自动更新 需配置 ); SELECT * FROM t14; INSERT INTO t14 (birthday, job_time) VALUES ( '2022-11-11', '2022-11-11 10:10:10' ); -- 如果我们更新 t14表的某条记录，login_time 列会自动更新当前的时间修改表-基本介绍使用ALTER TABLE 语句追加,修改,或删除列的语法. 1.添加列ALTER TABLE tablename ADD (column datatype [DEFAULTexpr]fdc[ , column datatype] . . . ) ;2.修改列ALTER TABLE tablename MODIFY (column datatype [DEFAULTexpr][ , column datatype] . . . ) ;3.删除列ALTER TABLE tablename DROP (column) ;4.查看表的结构:desc表名;--可以查看表的列5.修改表名:Rename table表名to新表名6.修改表字符集:alter table表名character set字符集;数据库CRUD语句Insert语句 (添加数据)Update语句 (更新数据)Delete语句 (删除数据)Select语句 (查找数据)说明insert语句的细节 1.插入的数据应与字段的数据类型相同。比如:把'abc'添加到int类型会错误 '33'会进行转型INSERT INTO `goods` (id, goods_name, price) VALUES('33', '我的手机', 3000);2．数据的长度应在列的规定范围内，例如:不能将一个长度为8O的字符串加入到长度为40的列中INSERT INTO `goods` (id, goods_name, price) VALUES(40, '我的手机我的手机我的手机', 3000);3．在values中列出的数据位置必须与被加入的列的排列位置相对应。INSERT INTO `goods` (id, goods_name, price) VALUES('我的手机',40,3000);4．字符和日期型数据应包含在单引号中。INSERT INTO `goods` (id, goods_name, price) VALUES(40,我的手机,3000);5．列可以插入空值[前提是该字段允许为空]，insert into table value(null)INSERT INTO `goods` (id, goods_name, price) VALUES(40,'我的手机',NULL);6.insert into tab name (列名..) values (),(),()形式添加多条记录INSERT INTO `goods` (id, goods_name, price) VALUES(40,'我的手机',2300),(44,'海尔手机',2200);7．如果是给表中的所有字段添加数据，可以不写前面的字段名称INSERT INTO `goods` VALUES(41,'我的手机',5000);8．默认值的使用，当不给某个字段值时，如果有默认值就会添加，否则报错如果某个列 没有指定not null 那么当添加数据时，没有给定值，则会默认nullINSERT INTO `goods` (id, goods_name) VALUES(50,'格力手机');update语句使用细节:UPDATE语法可以用新值更新原有表行中的各列。SET子句指示要修改哪些列和要给予哪些值。WHERE子句指定应更新哪些行。如没有WHERE子句，则更新所有的行。如果需要修改多个字段，可以通过set字段1=值1,字段2=值2…….delete语句使用细节如果不使用where子句，将删除表中所有数据。Delete语句不能删除某一列的值(可使用update 设为null或者'')使用delete语句仅删除记录，不删除表本身。如要删除表，使用drop table语句。 drop table 表名;select语句Select语句的注意事项(创建测试表学生表)Select 指定查询哪些列的数据。column指定列名。*号代表查询所有列。From指定查询哪张表。DISTINCT可选,指显示结果时，是否去掉重复数据select语句在where子句中经常使用的运算符 案例：--查询英语分数在80—90之间的同学。 SELECT *FROM student WHEREenglish >=80AND english <=90; SELECT *FROM student WHEREenglish BETWEEN 80 AND 90; -- between .. and ..是闭区间 --查询数学分数为89,9o,91的同学。 SELECT *FROM student WHEREmath = 89 OR math = 90 OR math = 91; SELECT *FROM student wHEREmath IN(89, 90, 91); --查询所有姓李的学生成绩。 SELECT *FROM student WHEREmathIN(89. 90. 91);使用order by子句排序查询结果。SELECT column1 , column2 . column3. . FROM table; order by column ascl desc,...Order by指定排序的列，排序的列既可以是表中的列名，也可以是select语句后指定的列名。Asc升序[默认]、Desc降序ORDER BY子句应位于SELECT语句的结尾。合计/统计函数- count Count返回行的总数select count (*) | count(列名) from tablename [WHERE where_definition]合计函数- sum Sum函数返回满足where条件的行的和-一般使用在数值列select sum(列名) { ,sum(列名)..} from tablename [WHERE where_definition]合计函数- avg AVG函数返回满足where条件的一列的平均值select avg(列名) {,avg(列名)...] from tablename [WHERE where_definition]合计函数- Max/min Max/min函数返回满足where条件的一列的最大/最小值select max(列名) from tablename [WHERE where_definition]使用group by子句对列进行分组[先创建测试表]SELECT column1, column2 . column3. . FROM table group by column使用having子句对分组后的结果进行过滤SELECT column1, column2. column3. . FROM table group by column having ...字符串相关函数数学相关函数rand0)返回一个随机浮点值v,范围在0到1之间(即，其范围为0svS1.0)。若已指定一个整数参数N,则它被用作种子值，用来产生重复序列。时间日期相关函数加密和系统函数流程控制函数查询加强-- 使用where子句 -- ?如何查找1992.1.1后入职的员工 -- 老师说明:在mysql中，日期类型可以直接比较 SELECT * FROM emp WHERE hiredate <='1992-01-01' -- 如何使用1ike操作符(模糊) -- %:表示0到多个人任意字符 _:表示单个字符 SELECT ename, sal FROM emp WHERE ename LIKE 's%' -- ?如何显示首字符为s的员工姓名和工资 SELECT ename, sal FROM emp WHERE ename LIKE 's%' -- ?如何显示第三个字符为大写o的所有员工的姓名和工资 SELECT ename, sal FROM emp WHERE ename LIKE '__O%' -- 如何显示没有上级的雇员的情况 SELECT *FROM enp WHERE mgr IS NULL -- 查询表结构selectinc.sql DESC emp -- 使用order by子句 -- ?如何按照工资的从低到高的顺序，显示雇员的信息 SELECT *FROM emp ORDER BY sal -- ?按照部门号升序而雇员的工资降序排列，显示雇员信息 SELECT *FROM emp ORDER BY deptno ASC,sal DESC;分页查询1．按雇员的id号升序取出，每页显示3条记录，请分别显示第一页，第二页,第三页 -- 第一页 SELECT * FROM emp ORDER BY empno LIMIT 0, 3 -- 第二页 SELECT * FROM emp ORDER BY empno LIMIT 3, 3 -- 第三页 SELECT * FROM emp ORDER BY empno LIMIT 6, 3 -- 公式 -- 第二页 SELECT * FROM emp ORDER BY empno LIMIT 每页显示记录数 * （第几页-1）,每页的记录数2．基本语法:select ... limit start, rows 表示从start+1行开始取,取出rows行, start从0开始计算在默认情况下:当两个表查询时，规则 1.从第一张表中，取出一行和第二张表的每一行进行组合，返回结果[含有两张表的所有列].2.一共返回的记录数第一张表行数*第二张表的行数3.这样多表查询默认处理返回的结果，称为笛卡尔集4.解决这个多表的关键就是要写出正确的过滤条件where,需要程序员进行分析mysql约束基本介绍 约束用于确保数据库的数据满足特定的商业规则。在mysql中，约束包括: not null、unique,primary key,foreign key,和check五种. primary key(主键)-基本使用 字段名字段类型primary key用于唯一的标示表行的数据,当定义主键约束后，该列不能重复primary key(主键)-细节说明primary key不能重复而且不能为null。 1.一张表最多只能有一个主键,但可以是复合主键主键的指定方式有两种直接在字段名后指定:字段名primakry key在表定义最后写primary key(列名); 2.使用desc表名，可以看到primary key的情况not nulI(非空) 如果在列上定义了not null,那么当插入数据时，必须为列提供数据。字段名字段类型not nul unique(唯一) 当定义了唯一约束后，该列值是不能重复的。字段名字段类型uniqueunique细节(注意): 如果没有指定not null，则unique字段可以有多个null张表可以有多个unique字段foreign key(外键) 用于定义主表和从表之间的关系:外键约束要定义在从表上，主表则必须具有主键约束或是unique约束.,当定义外键约束后，要求外键列数据必须在主表的主键列存在或是为null(学生/班级图示)FOREIGN KEY(本表字段名) REFERENCES 韦名(丰健名uniaue段包)foreign key(外键)—细节说明外键指向的表的字段，要求是primary key或者是 unique表的类型是innodb,这样的表才支持外键外键字段的类型要和主键字段的类型一致(长度可以不同)外键字段的值，必须在主键字段中出现过，或者为null [前提是外键字段允许为null]一旦建立主外键的关系，数据不能随意删除了.check 用于强制行数据必须满足的条件,假定在sal列上定义了check约束,并要求sal列值在1000~2000之间如果不再1000~2000之间就会提示出错。oracle和sql server均支持check ,但是mysql5.7目前还不支持check ,只做语法校验，但不会生效。基本语法:列名类型check (check条件) user表 id, name, sex(man,woman), sal(大于100小于900)自增长基本介绍字段名 整型 primary key auto_increment添加自增长的字段方式insert into xxx(字段1，字段2….….) values(null,'值'...); insert into xxx(字段2..….) values('值1'，'值2'..…); insert into xxx values(null,'"值1',...….)自增长使用细节峰一般来说自增长是和primary key配合使用的自增长也可以单独使用[但是需要配合一个unique]自增长修饰的字段为整数型的(虽然小数也可以但是非常非常少这样使用)自增长默认从1开始，你也可以通过如下命令修改altertable表名auto increment = xXX;如果你添加数据时，给自增长字段(列)指定的有值，则以指定的值为准索引索引的类型主健索引，主键自动的为主索引(类型Primary)唯一索引(UNIQUE)普通索引 (INDEX)全文索引(FULLTEXT)[适用于MylSAM]一般开发，不使用mysql自带的全文索引，而是使用:全文搜索Solr和 ElasticSearch (ES)索引使用 1.添加索引(建小表测试id , name )create [UNIQUE] index index_name on tbl_name (col_name [(lengthJ[ASC | DESC] ,……); alter table table_name ADD INDEX [index name][index col_name,... )2.添加主键(索引)ALTER TABLE表名 ADD PRIMARY KEY(列名…);4.删除索引DROP INDEX index name ON tbl_ name, alter table table name drop index index_name;4.删除主键索引比较特别:alter table t_b drop primary key;小结:哪些列上适合使用索引 1.较频繁的作为查询条件字段应该创建索引select * from emp where empno = 12.唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件select * from emp where sex =‘男‘3.更新非常频繁的字段不适合创建索引select * from emp where logincount =14.不会出现在WHERE子句中字段不该创建索引什么是事务事务用于保证数据的一致性它油一组相关的dml语句组成,该组的dml语句要么全部成功，要么全部失败。如:转账就要用事务来处理，用以保证数据的一致性。事务和锁 当执行事务操作时(dml语句),mysql会在表上加锁,防止其它用户改表的数据.这对用户来讲是非常重要的mysql 数据库控制台事务的几个重要操作start transaction --开始一个事务savepoint保存点名--设置保存点rollback to保存点名--回退事务rollback --回退全部事务commit --提交事务,所有的操作生效,不能回退细节:没有设置保存点多个保存点存储引擎开始事务方式回退事务 在介绍回退事务前，先介绍一下保存点(savepoint).保存点是事务中的点.用于取消部分事务，当结束事务时(commit)，会自动的删除该事务所定义的所有保存点.当执行回退事务时，通过指定保存点可以回退到指定的点提交事务 使用commit语句可以提交事务.当执行了commit语句子后,会确认事务的变化、结束事务、删除保存点、释放锁，数据生效。当使用commit语句结束事务子后，其它会话将可以查着到事务变化后的新数据事务细节如果不开始事务,默认情况下，dml操作是自动提交的，不能回滚如果开始一个事务，你没有创建保存点你可以执行 rollback,默认就是回退到你事务开始的状态.你也可以在这个事务中(还没有提交时)，创建多个保存点.比如: savepointaaa;执行dml , savepoint bbb;你可以在事务没有提交前，选择回退到哪个保存点.mysql的事务机制需要innodb的存储引擎才可以使用,myisam不好使.开始一个事务start transaction,set autocommit=off;mysql事务隔离级别事务隔离级别介绍 1.多个连接开启各自事务操作数据库中数据时，数据库系统要负责隔离操作，以保证各个连接在获取数据时的准确性。(通俗解释)2.如果不考虑隔离性，可能会引发如下问题:脏读不可重复读幻读●查看事务隔离级别查看当前会话隔离级别select @@tx_isolation;脏读(dirty read): 当一个事务读取另一个事务尚未提交的修改时，产生脏读不可重复读(nonrepeatable read): 同一查询在同一事务中多次进行，由于其·他提交事务所做的修改或删除，每次返回不同的结果集，此时发生不可重复读。幻读(phantom read): 同一查询在同一事务中多次进行，由于其他提交事务所做的插入操作，每次返回不同的结果集，此时发生幻读。事务隔离级别概念:Mysql隔离级别定义了事务与事务之间的隔离程度。1.查看当前会话隔离级别 select @@tx_isolation;2.查看系统当前隔离级别select @@global.tx_isolation;3.设置当前会话隔离级别set session transaction isolation level repeatable read;4.设置系统当前隔离级别set global transaction isolation level repeatable read;5.mysql默认的事务隔离级别是repeatable read，一般情况下，没有特殊要求,没有必要修改(因为该级别可以满足绝大部分项目需求)全局修改，修改my.ini配置文件，在最后加上 可选参数有:READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ.SERIALIZABLE.[mysqld]transactien-isolation = REPEATABLE-READ事务的acid特性 1.原子性(Atomicity)原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。2.一致性(Consistency)事务必须使数据库从一个一致性状态变换到另外一个一致性状态3.隔离性(lsolation)事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。4.持久性(Durability)持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响存储引擎基本介绍1.MySQL的表类型由存储引擎(Storage Engines)决定，主要包括MylSAM、innoDB、Memory等。2.MySQL数据表主要支持六种类型，分别是:CSV、Memory、ARCHIVE、MRG MYISAM、MYISAM、InnoBDB。3．这六种又分为两类，一类是”事务安全型”(transaction-safe)比如:InnoDB;其余都属于第二类，称为”非事务安全型”(non-transaction-safe)[mysiam和memory]。。●细节说明 我这里重点给大家介绍三种:MylSAM、InnoDB、MEMORYMylSAM不支持事务、也不支持外键，但其访问速度快，对事务完整性没有要求InnoDB存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。但是比起MylSAM存储引擎，InnoDB写的处理效率差一些并且会占用更多的磁盘空间以保留数据和索引。MEMORY存储引擎使用存在内存中的内容来创建表。每个MEMORY表只实际对应一个磁盘文件。MEMORY类型的表访问非常得快，因为它的数据是放在内存中的，并且默认使用HASH索引。但是一旦服务关闭，表中的数据就会丢失掉,表的结构还在。如何选择表的存储引擎如果你的应用不需要事务，处理的只是基本的CRUD操作，那么MylSAM是不二选择,速度快如果需要支持事务，选择lnnoDB。Memory存储引擎就是将数据存储在内存中，由于没有磁盘I./O的等待，速度极快。但由于是内存存储引擎，所做的任何修改在服务器重启后都将消失。(经典用法用户的在线状态().视图的基本使用create view视图名as select语句alter view视图名as select语句--更新成新的ShoW CREATE VIEW视图名drop view视图名1,视图名2视图细节讨论 1．创建视图后，到数据库去看，对应视图只有一个视图结构文件(形式:视图名.frm)2．视图的数据变化会影响到基表，基表的数据变化也会影响到视图[insert update delete ] ----针对前面的雇员管理系统-----mysql> create view myview as select empno ,ename , job, comm from emp;mysql> select * from myview;mysql> update myview set comm=200 where empno=7369;//修改视图,对基表都有变化mysql> update emp set comm=100 where empno=7369;//修改基表，对视频也有变化3.视图中可以再使用视图视图最佳实践 1．安全。一些数据表有着重要的信息。有些字段是保密的，不能让用户直接看到。这时就可以创建一个视图，在这张视图中只保留一部分字段。这样，用户就可以查询自己需要的字段，不能查看保密的字段。2.性能。关系数据库的数据常常会分表存储，使用外键建立这些表的之间关系。这时，数据库查询通常会用到连接(JOIN)。这样做不但麻烦，效率相对也比较低。如果建立一个视图，将相关的表和字段组合在一起，就可以避免使用JOIN查询数据。3.灵活。如果系统中有一张旧的表，这张表由于设计的问题，即将被废弃。然而，很多应用都是基于这张表，不易修改。这时就可以建立一张视图，视图中的数据直接映射到新建的表。这样，就可以少做很多改动，也达到了升级数据表的目的。Mysql用户mysql中的用户，都存储在系统数据库mysql中user表中其中user表的重要字段说明:1.host:允许登录的“位置”，localhost表示该用户只允许本机登录，也可以指定ip地址，比如:192.168.1.1002.user:用户名;3.authentication_string:密码,是通过mysql的password()函数加密之后的密码。创建用户 create user '用户名' @ '允许登录位置' identified by'密码'说明:创建用户，同时指定密码删除用户 drop user '用户名' @ '允许登录位置';用户修改密码 修改自己的密码;set password = password(密码');修改他人的密码(需要有修改用户密码权限):set password for '用户名'@"登录位置"= password('密码);给用户授权基本语法:grant all on *.* to '用户名' @ '登录位置'【identified by '密码'】说明: 1.权限列表，多个权限用逗号分开grant select on ...... grant select,delete, create on ...... grant all 【privileges】 on .....//表示赋予该用户在该对象上的所有权限2.特别说明**∶代表本系统中的所有数据库的所有对象(表，视图，存储过程)库.*:表示某个数据库中的所有数据对象(表，视图，存储过程等)3, identified by可以省略，也可以写出.(1)如果用户存在，就是修改该用户的密码。(2)如果该用户不存在，就是创建该用户!回收用户授权基本语法:revoke 权限列表 on 库.对象名 from '用户名'@'登录位置';权限生效指令如果权限没有生效，可以执行下面命令.基本语法:FLUSH PRIVILEGES;细节说明 1．在创建用户的时候，如果不指定Host则为%，%表示表示所有IP都有连接权限create user xXX;2.你也可以这样指定create user 'xxx'@'192.168.1.%' 表示xoxx用户在192.168.1.*的ip可以登录mysql3．在删除用户的时候，如果host 不是 % 需要明确指定 '用户'@'host值'
