mysql 常用指令整理-学习笔记
2,create database tttt;建立数据库tttt;
drop database tttt; 删除数据库tttt;
3,use tttt;使用tttt数据库;
4,show tables; 显示表项;
5,create table student(id int(10) primary keyauto_increment,name varchar(30),age tinyint(2));创建一个表格;student
6,desc student;显示一个表格;
7,select * from student;显示表中所有信息;
8,select name from student where id=1; 显示ID等于1的数据;
9,insert into student (name,age) values(“zhangsan”,22);向表中插入信息;
10, mysqldump–uroot –p tttt>d:/tttt.sql 备份数据;
12, mysql –uroot–p tttt < d:/tttt.sql 倒入备份信息;
13, tinyint(1),smallint(2), mediumint(3), int(4), bigint(8);各种整形类型;
14, 浮点类型float(4),double(8);
15, Decimal(M,N)M表示数据整体长度,N表示小数点后面的位数。
16, Altertable tttt modifyprice decimal(10,2)修改数据类型。
17, Createtable stu(id int(10) primary key auto_increment, sname char(30) not null, sextinyint(1) default 0,qq varchar(225)) default charater set utf8 engine myisam;
18, Select* from stu where sname=”李四”;根据约束条件输出;
19, Select* from stu where sname like “李%”;输出以“李”字开头的数据;
20, Selectid, sname, sex from stu where sname like “%玉%”;
21, Selectif(sex, “男生”, “女生”), sname, sex from stu;转换输出;
22, Selectif(sex, “男生”,”女生”) as stusex, sname from stu;给一个字段起别名;
23, Selectconcat(“姓名:”, sname,”性别:”,if(sex, “男”, “女”),”QQ:”, qq) as stuinfo from stu wheresex=0 and sname like “%玉%”;链接字符串与数据。
24, Grantall on tttt.* to”hp”@”localhost” identified by “hp”;创建一个用户。
25, Selectversion( );显示版本号;
26, Selectdatabase( );显示当前使用的库;
27, 表的字段;
28, Delete from stu where id=2;删除符合条件的行数据;
29, Alter table stu add birday date; 插入日期字段;
30, Update stu set birday=”1922/2/23” where id=3;向表stu中birday字段id为3加入数据。
31, Select* from stu limit2; 取出两条数据。
32, Select* from stu order byid desc; 以降序排列。
33, Select* from stu order by id asc; 以升序排列输出。
34, Select* from stu order by id desc limit 2; 有选择的输出数据。
35, Selectaname, birday from stu order by birday limit 1,1;第一个数字表示从那个位置开始取数据,第二个表示取出多少个数据。
36, Select* from stu where birday <=( select birday from order by birday asc limit1,1);
37, Selectyear(birday) from stu;只显示数据中有哪些年份(可重复);
38, Selectdistinct year(birday)as “学生出身年份” from stu;
39, show character set; 显示字符集。
40, showcreate table demo; 显示字符集类型。
41, selectlength(name),length(name2)form students; 显示数据的大小;
42, selectchar_length(name),char_length(name) from students; 读取对应字符集中字符的个数。
43, show collation; 显示字符集校对规则;
44, createtable demo2(name varchar(30) character set utf8 collate utf8_bin,name2varchar(30) character set utf8 collate utf8_general_ci);不同的校对规则创建表结构。前者区分大小写,后者则不。
45, createtable demo(name binary(3),name2 barchar(3)) default character set utf8;
posted on 2014-04-15 10:03 haveGoodDay 阅读(296) 评论(0) 编辑 收藏 举报