用户与权限
创建用户
代码语言：javascript复制mysql>create user test identified by 'BaC321@#';修改密码
##5.5版本及以前的命令
代码语言：javascript复制mysql>set password for test=passowrd('!1A@2#3'); ##5.6及以上命令
代码语言：javascript复制mysql>update mysql.user set authentication_string=password('A1b2c3#!@') where user='test';创建用户并授权
代码语言：javascript复制mysql>grant select,insert,update on student.* to test@localhost identified by 'A1b2c3#!@';查看授权
代码语言：javascript复制mysql> show grants for test@localhost;
移除权限
代码语言：javascript复制mysql> revoke insert,update on student.* from test@localhost;建库与表
创建库
代码语言：javascript复制mysql> create database student;
mysql> show databases;
创建表
代码语言：javascript复制mysql> use student;
mysql> create table T1 (name varchar(10) not null,sex varchar(10) not null);##通过现有的表创建新表
代码语言：javascript复制mysql> create table T2 as select * from T1;插入数据
插入数据
代码语言：javascript复制mysql> insert into T1 values('zhang','man');
Query OK, 1 row affected (0.03 sec)
mysql> insert into T1 values('li','man');
Query OK, 1 row affected (0.03 sec)
mysql> insert into T1 values('wang','man');
Query OK, 1 row affected (0.02 sec)
mysql> insert into T1 values('zhao','women');
Query OK, 1 row affected (0.05 sec)##需要注意的是如果列超过两列，就需要指定列字段名如下
代码语言：javascript复制mysql> insert into T1(name,sex) values('gege','man');查询数据
查询数据
代码语言：javascript复制mysql> select user,host from mysql.user;
### 查看用户
mysql> select * from T1 where name like '%an%';
mysql> select * from T1 where age like '2%';##匹配查询
mysql> select * from T1 order by name,age;##查询排序
mysql> select count(*) as toaolcount from T1;
mysql> select sum(age) as sumvalue from T1;
mysql> select avg(age) as avgvalue from T1;
mysql> select max(age) from T1;##查询值
mysql> select score from T1 where score <91;
mysql> select score from T1 where score >=91;
mysql> select * from T1 where score in (96,100);##条件查询
mysql> select * from T2;
mysql> select * from T1;
增删更新
增加与删除列
代码语言：javascript复制mysql> alter table T1 add age int(4) not null;
mysql> alter table T1 drop age更新表里的数据
代码语言：javascript复制mysql> update T1 set age=25 where name='zhang';
mysql> update T1 set age=23 where name='li';删除数据
代码语言：javascript复制mysql> delete from T1 where age='22';建索引与删除
代码语言：javascript复制mysql> create index indexT1 on T1(name(10));
mysql> drop index indexT1 on T1;主键与视图
创建主键
代码语言：javascript复制mysql> alter table T1 add primary key(name);
mysql> desc T1;
创建与删除视图
代码语言：javascript复制mysql> create view t1view as select name from T1;
mysql> select * from t1view;
mysql> drop view t1view;
mysql> select * from t1view;
ERROR 1146 (42S02): Table 'student.t1view' doesn't exist
