建表
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;查看该用户的权限