建表

 

1.  create database china;创建数据库

2.  use china;选择数据库

3.  create table t1( id int not null, name char(40), city char(40), country char(40),email char(50)) engine=Innodb charset=utf8;建表

4.  alter table t1 add address char(40);增加字段

5.  alter table t1 drop address;删除字段

6.  create table t3 like t1;复制表

7.  alter table t1 modify city char(30);更改类型

8.  alter table t1 rename t2;表的重命名

9.  alter table t2 convert to charset gbk;更改字符集

10.show create table t2;查看字符集是否更改

                  写入数据

 

11.insert into t2(id,name,city) values(1,'刘','长沙');写入数据

insert into t2 values (3,'朱','上海','中国','123');每行都写入数据，可不写字段名

12.            select* from t2;查看数据

13.            update t2 set name='李' where id=1;改变第一行的名字信息

14.            delete from t2 where id=3;删除表中id为四的数据

15.            truncate t3;删除表中所有数据

                 建外键


16.            create table dept( deptID int primary key, deptName varchar(32))engine=innodbcharset=utf8;建外建

17.            create table stu( stuID int auto_increment, stuName varchar(32), deptID int, foreignkey(deptID) references dept(deptID), primary key(stuID) )engine=innodbcharset=utf8;

18.            insert into dept  select 1,'信科院';

19.            insert into stu select null, '张三',1;

20.            select stuName, deptName from stu, dept where stu.deptID = dept.deptID;显示跨表信息

                   创建索引

21.            创建表的同时创建索引

create tablecourse(

cno varchar(5) notnull,

cname varchar(30)not null,

teacher varchar(20),

primary key(cno),

index cna(cname))engine=innodb charset=utf8;

22.            create index sna on t2(name);向已存的表添加索引

23.            drop index sna on t2;删除索引

                  用户与权限

24.            create user 'gongda'@'localhost' identified by '123456';创建用户

    flush privileges;刷新系统权限表

25.            select host,user,password from mysql.user;显示用户信息。

26.            delete from mysql.user wherer user =  'gongda';删除用户

   flush privileges;也需刷新

27.            set password for gongda@'localhost'=password('haha');更改密码

28.            mysql -h localhost -u gongda –p;检测是否更改成功

29.            grant select on liu.student to gongda@'10.0.2.253' identified by 'haha';允许10.0.2.253连接到数据库服务器，可读取数据库liu中student表的数据。

30.            grant update(name) on liu.student to gongda@'localhost';允许localhost连接到数据库，并且能修改student中的name值。

31.            revoke update(name) on liu.student from gongda@'localhost';撤销用户的权限

32.   show grants for gongda@localhost;查看该用户的权限