MySQL数据库课堂实例笔记.docx
《MySQL数据库课堂实例笔记.docx》由会员分享，可在线阅读，更多相关《MySQL数据库课堂实例笔记.docx（19页珍藏版）》请在冰豆网上搜索。
MySQL数据库课堂实例笔记
--MySQL数据库实例笔记
-=====================================================
二、连接数据库：
mysql-h主机名-u用户名-p密码库名
1）C:
\>mysql--采用匿名账号和密码登陆本机服务
2）C:
\>mysql-hlocalhost-uroot-proot--采用root账号和root密码登陆本机服务
3）C:
\>mysql-uroot-p--推荐方式默认登陆本机
Enterpassword:
****
4）C:
\>mysql-uroot-plpp61--直接进入lpp61数据库的方式登陆
三、授权：
格式：
grant允许操作on库名.表名to账号@来源identifiedby'密码';
--实例：
创建zhangsan账号，密码123，授权lpp61库下所有表的增/删/改/查数据,来源地不限
mysql>grantselect,insert,update,deleteonlpp61.*tozhangsan@'%'identifiedby'123';
4、SQL的基本操作
--查看当前用户下都有哪些数据库
showdatabases;
创建库
createdatabaselpp80;
createdatabaseifnotexistslpp80;
删除库aa
dropdatabaseaa;
dropdatabaseifexistsabc;
选择并进入lpp80数据库
uselpp80
查看当前所在的数据库
selectdatabase（）;
查看当前数据库下所有的表
showtables;
查看aa表的表结构
descaa;
查看建表的语句
showcreatetablestu\G
删除表mytab
droptableifexistsmytab;--
修改表名
把表stu更名新名字stu02
altertablesturenameasstu02;
查看bb中所有数据。
select*frombb;
添加一条数据
insertintodemo（name,age,sex）values（'zhangsan',20,'w'）;
insertintodemovalues（'lisi',22,'m'）;
不指定字段名来添加数据
insertintodemo（name,age）values（'wangwu',23）;
指定部分字段名来添加数据
批量添加数据
mysql>insertintodemo（name,age,sex）values（'aaa',21,'w'）,（"bbb",22,'m'）;
删除数据
deletefromdemowherename='bbb';
删除id值大于10的所有数据
mysql>deletefrombbwhereid>10;
修改数据
updatedemosetage=24wherename='aaa';
将bb表中id值为3的信息，name改为ddd，age改为23
mysql>updatebbsetname='ddd',age=23whereid=3;
更改AUTO_INCREMENT初始值:
ALTERTABLE表名称AUTO_INCREMENT=1
修改表结构
格式：
altertable表名action（修改选项）
change可以改变字段名称，而modify不可以
在t1表的最前面插入一个字段a0
mysql>altertablet1adda0intunsignedfirst;
在t1表的a2字段后插入一个字段aa
mysql>altertablet1addaaintunsignedaftera2;
删除t1表中的a0字段
mysql>altertablet1dropa0;
修改t1表中aa字段，将值改为cc，类型设为varchar
altertablet1changeaaccvarchar（8）;
修改字段名
alterTABLE`uses`change`username``uname`VARCHAR（32）NOTNULL;
设置表stu02字段id为非空，自增，主键
ALTERTABLEstu02MODIFYCOLUMN`id`int（11）NOTNULLAUTO_INCREMENTFIRST,ADDPRIMARYKEY（`id`）
-表中索引添加和删除\查看
为stu表中age字段添加一个普通索引，索引名为index_age
mysql>altertablestuaddindexindex_age（age）;
设置表stu02的id字段为主键
ALTERTABLE`stu02`ADDPRIMARYKEY（`id`）
删除表stu02主键
ALTERTABLE`stu02`DROPPRIMARYKEY
-删除stu中age的index_age索引
mysql>altertablestudropindexindex_age;
-修改表名t2为t3
mysql>altertablet2renamet3;
查看索引
showindexesfrom表名
创建表：
createtable表名（字段名类型[约束],字段名类型[约束],...）;
createtableaa（idint,namevarchar（6）,ageint）;
五、MySQL数据库的数据类型：
MySQL的数据类型分为四大类：
数值类型、字串类型、日期类型、NULL。
5.1数值类型：
*tinyint（1字节）
smallint（2字节）
mediumint（3字节）
*int（4字节）
bigint（8字节）
*float（4字节）float（6,2）
*double（8字节）
decimal（自定义）字串形数值
5.2字串类型
普通字串
*char定长字串char（8）
*varchar可变字串varchar（8）
二进制类型
tinyblob
blob
mediumblob
longblob
文本类型
tinytext
*text常用于
mediumtext
longtext
*enum枚举
set集合
5.3时间和日期类型：
date年月日
time时分秒
datatime年月日时分秒
timestamp时间戳
year年
5.4NULL值
NULL意味着“没有值”或“未知值”
可以测试某个值是否为NULL
不能对NULL值进行算术计算
对NULL值进行算术运算，其结果还是NULL
0或NULL都意味着假，其余值都意味着真
MySQL的运算符：
算术运算符：
+-*/%
比较运算符：
=><>=<=<>!
=
数据库特有的比较：
in，notin,isnull,isnotnull,like,betweenand
逻辑运算符：
andornot
六、表的字段约束：
unsigned无符号（正数）
zerofill前导零填充
auto_increment自增
default默认值
notnull非空
PRIMARYKEY主键（非null并不重复）
unique唯一性（可以为null但不重复）
index常规索引
七:
建表语句格式：
createtable表名（
字段名类型[字段约束],
字段名类型[字段约束],
字段名类型[字段约束],
...
）;
--==========================
--MySQL数据库的查询
--==========================
--格式：
--select[*]|[字段名,字段名,...]from表名
--[where查询条件]
--[groupby分组[having分组子条件]]
--[orderby排序字段名]
--[limitm[,n]]获取部分数据
获取学生所有字段的信息
select*fromstu;
查看学生信息，只看id、name和classid字段
mysql>selectid,name,classidfromstu;
其别名
selectid,nameasstuname,classidfromstu;
其中as关键字可以省略
mysql>selectid,namestuname,classidfromstu;
为表其别名，并输出字段时使用表名指定。
selects.id,s.name,s.agefromstuass;
在显示原数据的基础上，实现追加一个年龄加4的字段信息并为age+4这个字段起个别名
mysql>select*,age+4asage2fromstu;
直接在输出时添加一个字段city值为beijing
selectid,name,classid,'beijing'ascityfromstu;
使用distinct关键字可以去除字段中重复的值。
mysql>selectdistinctclassidfromstu;
--带where条件的查询
--1.请查出性别sex为m的所有信息
mysql>select*fromstuwheresex='m';
--2.请查出班级为lpp86的所有男生（m）
mysql>select*fromstuwhereclassid='lpp86'andsex='m'
--3.请查出年龄在20至25岁的所有信息
mysql>select*fromstuwhereage>=20andage<=25;
mysql>select*fromstuwhereagebetween20and25;
请查出年龄在20到25岁之外所有信息
mysql>select*fromstuwhereage<20orage>25;
mysql>select*fromstuwhereagenotbetween20and25;
--6.请查出班级是lpp86和lpp88期所有女生信息（w）
mysql>select*fromstuwhere（classid='lpp86'orclassid='lpp88'）andsex='w';
mysql>select*fromstuwhereclassidin（'lpp86','lpp88'）andsex='w';
请查出没有分配班级的学生信息（isnull）
mysql>select*fromstuwhereclassidisnull;
请查出姓名只有两个字母的学生信息
mysql>select*fromstuwherenamelike"__";
mysql>select*fromstuwherenameregexp'^[a-z]{2}$';--使用正则
请查处姓名中含有ang字串信息。
mysql>select*fromstuwherenamelike"%ang%";
mysql>select*fromstuwherenameregexp'ang';--使用正则
-分组统计查询
---------------------------
--统计函数：
count（）sum（）max（）min（）avg（）
--分组：
groupby
统计学生总数，年纪和，平均年纪，最大年龄和最小年龄
selectcount（*）,sum（age）,avg（age）,max（age）,min（age）fromstu;
统计lpp86期的人数
mysql>selectcount（*）fromstuwhereclassid='lpp86';
统计每个班级都有多少学生
mysql>selectclassid,count（*）fromstugroupbyclassid;
统计lpp86期的男生和女生各多少人呢
selectsex,count（*）fromstuwhereclassid='lpp86'groupbysex;
统计每个班的平均年龄
mysql>selectclassid,avg（age）fromstugroupbyclassid;
统计每个班的平均年龄,要求只显示23岁以上的信息。
selectclassid,avg（age）aafromstugroupbyclassidhavingaa>23;
--orderby排序
----------------------------------
--格式：
orderby字段名[asc|desc]
--其中：
asc是默认，表示升序desc表示降序
mysql>select*fromstuorderbyage;
--先对性别做升序排序，相同性别按年龄降序排序。
mysql>select*fromstuorderbysex,agedesc;
--limit分页提取部分数据
--==========================================
--格式：
limitm[,n];
--分页公式：
limit（当前页-1）*页大小，页大小
--获取年龄最大的3条数据
mysql>select*fromstuorderbyagedesclimit3;
从第12行开始查，查4行
mysql>select*fromstulimit12,4;
MySQL服务器的用户权限管理
=========================================
--授权一个用户（zhangsan）密码123，可以对所有的库，所有的表做所有操作。
mysql>grantallon*.*tozhangsan@'%'identifiedby'123';
QueryOK,0rowsaffected（0.17sec）
--刷新生效，否则就要重启MySQL服务才可以。
mysql>flushprivileges;
QueryOK,0rowsaffected（0.00sec）
--浏览当前MySQL用户信息
--移除一些权限
--revoke:
只删除了用户权限，但没有删除这个用户
mysql>revokeinsert,deleteon*.*fromadmin@192.168.112.132identifiedby'123';
--查看指定用户的权限信息
mysql>showgrantsforxbb@localhost;
+------------------------------------------------------------------------------------------------------------+
|Grantsforxbb@localhost|
+------------------------------------------------------------------------------------------------------------+
|GRANTUSAGEON*.*TO'xbb'@'localhost'IDENTIFIEDBYPASSWORD'*23AE809DDACAF96AF0FD78ED04B6A265E05AA257'|
+------------------------------------------------------------------------------------------------------------+
--dropuser:
删除了整个用户及其权限（包括数据字典中的数据）
mysql>dropuser'xbb'@'localhost';
QueryOK,0rowsaffected（0.00sec）
mysql>selectuser,hostfrommysql.user;
+------------------+-----------+
|user|host|
+------------------+-----------+
|root|127.0.0.1|
|debian-sys-maint|localhost|
|root|localhost|
|root|wangxg|
+------------------+-----------+
4rowsinset（0.00sec）
-
补充创建表
（1）NULL|NOTNULL用于定义列的空值约束。
（定义列） （下面的蓝色部份是单选其中之一）
语法：
CONSTRAINT约束名NULL|NOTNULL
（2）UNIQUE 约束唯一标识数据库表中的每条记录。
（即可以定义列也可能定义表）
语法：
CONSTRAINT约束名UNIQUE（列名,列名,......）;
注意：
a.UNIQUE约束唯一标识数据库表中的每条记录。
b.UNIQUE和PRIMARYKEY约束均为列或列集合提供了唯一性的保证。
c.PRIMARYKEY拥有自动定义的UNIQUE约束。
d.请注意，每个表可以有多个UNIQUE约束，但是每个表只能有一个PRIMARYKEY约束。
（3）PRIMARYKEY约束唯一标识数据库表中的每条记录。
（即可以定义列也可能定义表）
语法：
CONSTRAINT约束名PRIMARYKEY（列名,列名,......）;
说明：
用于定义基本表的主键。
与UNIQUE约束类似，PRIMARYKEY约束也是通过建立唯一索引来保证基本表在主键列（某一个列或多个列的组合）上取值的唯一性。
然而它们之间也存在着很大差别：
在一个基本表中只能定义一个PRIMARYKEY约束，却能定义多个UNIQUE约束。
如果为基本表的某一个列或多个列的组合指定了PRIMARYKEY约束，那么其中在任何一个列都不能出现空值；而UNIQUE约束允许出现空值。
注意：
a.主键必须包含唯一的值。
b.主键列不能包含NULL值。
c.每个表应该都一个主键，并且每个表只能有一个主键。
（4）FOREIGNKEY外键（即可以定义列也可能定义表）
语法：
CONSTRAINT约束名FOREIGNKEY（列名,列名,......）REFERENCES（列名,列名,......）;
说明：
指定某一个列或多个列的组合作为外部键，并在外部键和它所引用的主键或唯一键之间建立联系。
在这种联系中，包含外部键的基本表称为从表，包含外部键引用的主键或唯一键的表称为主表。
一旦为一列或列的组合定义了FOREIGNKEY约束，系统将保证从表在外部键上的取值要么是主表中某一个主键值或唯一键值，要么取空值。
注意：
a.在REFERENCES中引用的列必须和FOREIGNKEY的外部键列一一对应，即列数目相等并且相应列的数据类型相同。
（5）CHECK 约束用于限制列中的值的范围。
（即可以定义列也可能定义表）
语法：
CONSTRAINT约束名CHECK （约束条件）;
说明：
用于指定基本表中的每一条记录必须满足的条件，可以对基本表在各个列上的值做进一步的约束，如成绩列的取值既不能大于100，也不能小于0。
注意：
a.如果对单个列定义CHECK约束，那么该列只允许特定的值。
b.如果对一个表定义CHECK约束，那么此约束会在特定的列中对值进行限制。
例：
USElijintaoCREATETABLES2
（SNOCHAR
（2）CONSTRAINTS_PRIMPRIMARYKEY,
SNCHAR（8）CONSTRAINTSN_CONSNOTNULL,
AGENUMERIC
（2）CONSTRAINTAGE_CONSNOTNULLCONSTRAINTAGE_CHKCHECK（AGEBETWEEN15AND45）,
SEXCHAR
（2）DEFAULT'男'CONSTRAINTSEX_CHKCHECK（SEX='男'ORSEX='女'）,
DEPTCHAR
（2）CONSTRAINTDEPT_CONSNOTNULL）;
注意事项：
1.表的字段之间要使用逗号隔开。
2.建表的最后一句一定不能有逗号。
3.表名称和字段名称尽量不要使用MySQL系统的关键字
4.如果一定要使用关键字，我们可以使用反引号将表名称和字段名称包含起来来进行过滤屏蔽。
5.使用反引号会使建表效率增高。
6.数据表名称和字段名称不能重名
7.AUTO_INCREMENT属性必须依附于主键索引或唯一索引
---数据库恢复（导入）
将php02.sql数据恢复到数据库php02中
C:
\>mysql-uroot-p
升级会员 