本人花费半年的时间总结的《Java面试指南》已拿腾讯等大厂offer，已开源在github ，欢迎star！
创建用户
mysqlcreate user test identified by ‘BaC321@#’;
修改密码
##5.5版本及以前的命令
mysqlset password for test=passowrd(‘!1A@2#3’);
##5.6及以上命令
mysqlupdate mysql.user set authentication_string=password(‘A1b2c3#!@’) where user=’test’;
创建用户并授权
mysqlgrant select,insert,update on student.* to test@localhost identified by ‘A1b2c3#!@’;
查看授权
mysql show grants for test@localhost;
移除权限
mysql revoke insert,update on student.* from test@localhost;
创建库
mysql create database student;
mysql show databases;
创建表
mysql use student;
mysql create table T1 (name varchar(10) not null,sex varchar(10) not null);
##通过现有的表创建新表
mysql create table T2 as select * from T1;
插入数据****
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)
##需要注意的是如果列超过两列，就需要指定列字段名如下
mysql insert into T1(name,sex) values(‘gege’,’man’);
查询数据
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;
增加与删除列
mysql alter table T1 add age int(4) not null;
mysql alter table T1 drop age
更新表里的数据
mysql update T1 set age=25 where name=’zhang’;
mysql update T1 set age=23 where name=’li’;
删除数据
mysql delete from T1 where age=’22’;
建索引与删除
mysql create index indexT1 on T1(name(10));
mysql drop index indexT1 on T1;
创建主键
mysql alter table T1 add primary key(name);
mysql desc T1;
创建与删除视图
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
###提示此视图不存在
写文不易，扫码打赏民工哥吧！
