常用的Mysql语句
hithyc 2011-07-23
一.安装与配置MYSQL
二.常用mysql命令行命令
1.mysql的启动与停止
启动MYSQL服务netstartmysql
停止MYSQL服务netstopmysql
2.netstat–na|findstr3306查看被监听的端口,findstr用于查找后面的端口是否存在
3.在命令行中登陆MYSQL控制台,即使用MYSQLCOMMENDLINETOOL
语法格式mysql–user=root–password=123456db_name
或mysql–uroot–p123456db_name
4.进入MYSQL命令行工具后,使用status;或\s查看运行环境信息
5.切换连接数据库的语法:usenew_dbname;
6.显示所有数据库:showdatabases;
7.显示数据库中的所有表:showtables;
8.显示某个表创建时的全部信息:showcreatetabletable_name;
9.查看表的具体属性信息及表中各字段的描述
Describetable_name;缩写形式:desctable_name;
三。MySql中的SQL语句
1.数据库创建:Createdatabasedb_name;
数据库删除:Dropdatabasedb_name;删除时可先判断是否存在，写成:dropdatabaseifexitsdb_name
2.建表:创建数据表的语法:createtabletable_name(字段1数据类型,字段2数据类型);
例:createtablemytable(idint,usernamechar(20));
删表:droptabletable_name;例:droptablemytable;
8.添加数据:Insertinto表名[(字段1,字段2,….)]values(值1,值2,…..);
如果向表中的每个字段都插入一个值,那么前面[]括号内字段名可写也可不写
例:insertintomytable(id,username)values(1,’zhangsan’);
9.查询:查询所有数据:select*fromtable_name;
查询指定字段的数据:select字段1,字段2fromtable_name;
例:selectid,usernamefrommytablewhereid=1orderbydesc;多表查询语句------------参照第17条实例
10.更新指定数据,更新某一个字段的数据（注意，不是更新字段的名字）
Updatetable_nameset字段名=’新值’[,字段2=’新值’,…..][whereid=id_num][orderby字段顺序]
例:updatemytablesetusername=’lisi’whereid=1;
Order语句是查询的顺序,如:orderbyiddesc(或asc),顺序有两种:desc倒序(100—1,即从最新数据往后查询),asc(从1-100)，Where和order语句也可用于查询select与删除delete
11.删除表中的信息:
删除整个表中的信息:deletefromtable_name;
删除表中指定条件的语句:deletefromtable_namewhere条件语句;条件语句如:id=3;
12.创建数据库用户
一次可以创建多个数据库用户如：
CREATEUSERusername1identifiedBY‘password’,username2IDENTIFIEDBY‘password’….
13.用户的权限控制：grant
库，表级的权限控制:将某个库中的某个表的控制权赋予某个用户
GrantallONdb_name.table_nameTOuser_name[indentifiedby‘password’];
14.表结构的修改
（1）增加一个字段格式：
altertabletable_nameaddcolumn(字段名字段类型);----此方法带括号
（2）指定字段插入的位置：
altertabletable_nameaddcolumn字段名字段类型after某字段；
删除一个字段：
altertabletable_namedrop字段名;
（3）修改字段名称/类型
altertabletable_namechange旧字段名新字段名新字段的类型;
（4）改表的名字
altertabletable_namerenametonew_table_name;
（5）一次性清空表中的所有数据
truncatetabletable_name;此方法也会使表中的取号器(ID)从1开始
15.增加主键，外键，约束，索引。。。。(使用方法见17实例)
①约束（主键Primarykey、唯一性Unique、非空NotNull）
②自动增张auto_increment
③外键Foreignkey-----与referencetable_name(col_name列名)配合使用，建表时单独使用
④删除多个表中有关联的数据----设置foreignkey为setnull---具体设置参考帮助文档
16.查看数据库当前引擎
SHOWCREATETABLEtable_name;
修改数据库引擎
ALTERTABLEtable_nameENGINE=MyISAM|InnoDB;
17.SQL语句运用实例:
--1建users表
createtableusers(idintprimarykeyauto_increment,nikenamevarchar(20)notnullunique,passwordvarchar(100)notnull,addressvarchar(200),reg_datetimestampnotnulldefaultCURRENT_TIMESTAMP);
--2建articles表,在建表时设置外键
createtablearticles(idintprimarykeyauto_increment,contentlongtextnotnull,useridint,constraintforeignkey(userid)referencesusers(id)ondeletesetnull);
-----------------------------------------------------------------------
--2.1建articles表,建表时不设置外键
createtablearticles(idintprimarykeyauto_increment,contentlongtextnotnull,useridint);
--2.2给articles表设置外键
altertablearticlesaddconstraintforeignkey(userid)referencesusers(id)ondeletesetnull;
------------------------------------------------------------------------
--3.向users表中插入数据,同时插入多条
insertintousers(id,nikename,password,address)values(1,'lyh1','1234',null),(10,'lyh22','4321','湖北武汉'),(null,'lyh333','5678','北京海淀');
--4.向article中插入三条数据
insertintoarticles(id,content,userid)values(2,'hahahahahaha',11),(null,'xixixixixix',10),(13,'aiaiaiaiaiaiaiaiaiaiaiaia',1),(14,'hohoahaoaoooooooooo',10);
--5.进行多表查询，选择users表中ID=10的用户发布的所有留言及该用户的所有信息
selectarticles.id,articles.content,users.*fromusers,articleswhereusers.id=10andarticles.userid=users.idorderbyarticles.iddesc;
--6.查看数据库引擎类型
showcreatetableusers;
--7.修改数据库引擎类型
altertableusersengine=MyISAM;---因为users表中ID被设置成外键，执行此句会出错
--8.同表查询,已知一个条件的情况下.查询ID号大于用户lyh1的ID号的所有用户
selecta.id,a.nikename,a.addressfromusersa,usersbwhereb.nikename='lyh1'anda.id>b.id;
------也可写成
selectid,nikename,addressfromuserswhereid>(selectidfromuserswherenikename='lyh1');
9.显示年龄比领导还大的员工：
selecta.namefromusersa,usersbwherea.managerid=b.idanda.age>b.age;
查询编号为2的发帖人:先查articles表,得到发帖人的编号,再根据编号查users得到的用户名。
接着用关联查询.
select*fromarticles,users得到笛卡儿积,再加orderbyarticles.id以便观察
使用select*fromarticles,userswherearticles.id=2筛选出2号帖子与每个用户的组合记录
再使用select*fromarticles,userswherearticles.id=2andarticles.userid=users.id选出users.id等于2号帖的发帖人id的记录.
只取用户名:selectuserwhereuser.id=(selectuseridfromarticleswherearticle.id=2)
找出年龄比小王还大的人:假设小王是28岁,先想找出年龄大于28的人
select*fromuserswhereage>(selectagefromuserswherename='xiaowang');
*****要查询的记录需要参照表里面的其他记录:
selecta.namefromusersa,usersbwhereb.name='xiaowang'anda.age>b.age
表里的每个用户都想pk一下.selecta.nickname,b.nicknamefromusersa,usersbwherea.id>b.id;
更保险的语句:selecta.nickname,b.nicknamefrom(select*fromusersorderbyid)a,(se
lect*fromusersorderbyid)bwherea.id>b.id;
再查询某个人发的所有帖子.
selectb.*fromarticlesa,articlesbwherea.id=2anda.userid=b.userid
说明:表之间存在着关系，ER概念的解释，用access中的示例数据库演示表之间的关系.只有innodb引擎才支持foreignkey，mysql的任何引擎目前都不支持check约束。
四、字符集出现错误解决办法
出现的问题：
mysql>updateusers
->setusername='关羽'
->whereuserid=2;
ERROR1366(HY000):Incorrectstringvalue:'\xB9\xD8\xD3\xF0'forcolumn'usern
ame'atrow1
向表中插入中文字符时，出现错误。
mysql>select*fromusers;
+--------+----------+
|userid|username|
+--------+----------+
|2|????|
|3|????|
|4|?í?ù|
+--------+----------+
3rowsinset(0.00sec)
表中的中文字符位乱码。
解决办法：
使用命令：
mysql>status;
--------------
mysqlVer14.12Distrib5.0.45,forWin32(ia32)
Connectionid:8
Currentdatabase:test
Currentuser:root@localhost
SSL:Notinuse
Usingdelimiter:;
Serverversion:5.0.45-community-ntMySQLCommunityEdition(GPL)
Protocolversion:10
Connection:localhostviaTCP/IP
Servercharacterset:latin1
Dbcharacterset:latin1
Clientcharacterset:gbk
Conn.characterset:gbk
TCPport:3306
Uptime:7hours39min19sec
Threads:2Questions:174Slowqueries:0Opens:57Flushtables:1Openta
bles:1Queriespersecondavg:0.006
--------------
查看mysql发现Servercharacterset，Dbcharacterset的字符集设成了latin1，所以出现中文乱码。
mysql>showtables;
+----------------+
|Tables_in_test|
+----------------+
|users|
+----------------+
1rowinset(0.00sec)
更改表的字符集。
mysql>altertableuserscharactersetGBK;
QueryOK,3rowsaffected(0.08sec)
Records:3Duplicates:0Warnings:0
查看表的结构：
mysql>showcreateusers;
ERROR1064(42000):YouhaveanerrorinyourSQLsyntax;checkthemanualthat
correspondstoyourMySQLserverversionfortherightsyntaxtousenear'users
'atline1
mysql>showcreatetableusers;
+-------+-----------------------------------------------------------------------
------------------------------------------------------------------------------+
|Table|CreateTable
|
+-------+-----------------------------------------------------------------------
------------------------------------------------------------------------------+
|users|CREATETABLE`users`(
`userid`int(11)defaultNULL,
`username`char(20)charactersetlatin1defaultNULL
)ENGINE=InnoDBDEFAULTCHARSET=gbk|
+-------+-----------------------------------------------------------------------
------------------------------------------------------------------------------+
1rowinset(0.00sec)
mysql>descusers;
+----------+----------+------+-----+---------+-------+
|Field|Type|Null|Key|Default|Extra|
+----------+----------+------+-----+---------+-------+
|userid|int(11)|YES||NULL||
|username|char(20)|YES||NULL||
+----------+----------+------+-----+---------+-------+
2rowsinset(0.02sec)
这时向表中插入中文然后有错误。
mysql>insertintousersvalues(88,'中文');
ERROR1366(HY000):Incorrectstringvalue:'\xD6\xD0\xCE\xC4'forcolumn'usern
ame'atrow1
mysql>insertintousersvalues(88,'中文');
ERROR1366(HY000):Incorrectstringvalue:'\xD6\xD0\xCE\xC4'forcolumn'usern
ame'atrow1
还要更改users表的username的字符集。
mysql>altertableusersmodifyusernamechar(20)charactersetgbk;
ERROR1366(HY000):Incorrectstringvalue:'\xC0\xEE\xCB\xC4'forcolumn'usern
ame'atrow1
mysql>altertableusersmodifyusernamechar(20)charactersetgbk;
ERROR1366(HY000):Incorrectstringvalue:'\xC0\xEE\xCB\xC4'forcolumn'usern
ame'atrow1
因为表中已经有数据，所以更改username字符集的操作没有成***
清空users表中的数据
mysql>truncatetableusers;
QueryOK,3rowsaffected(0.01sec)
从新更改user表中username的字符集
mysql>altertableusersmodifyusernamechar(20)charactersetgbk;
QueryOK,0rowsaffected(0.06sec)
Records:0Duplicates:0Warnings:0
这时再插入中文字符，插入成***。
mysql>insertintousersvalues(88,'中文');
QueryOK,1rowaffected(0.01sec)
mysql>select*fromusers;
+--------+----------+
|userid|username|
+--------+----------+
|88|中文|
+--------+----------+
1rowinset(0.00sec)
mysql>
