![cd8467abe76767bcf03b95cb476e8c88.png](https://i-blog.csdnimg.cn/blog_migrate/a77c0933798958261261e6938419e8a9.jpeg)
今天介绍的是关于Mysql数据库一些操作的基础命令
用户与权限
创建用户
mysql>create user test identified by 'BaC321@#';
修改密码
5.5版本及以前的命令
mysql>set password for test=passowrd('!1A@2#3');
5.6及以上命令
mysql>update mysql.user set authentication_string=password('A1b2c3#!@') where user='test';
创建用户并授权
mysql>grant select,insert,update on student.* to test@localhost identified by 'A1b2c3#!@';
查看授权
mysql> show grants for test@localhost;
![ade30e6958f260a788f3749dd3e0d537.png](https://i-blog.csdnimg.cn/blog_migrate/6385c6959f36362d8596c32514ff2bda.jpeg)
移除权限
mysql> revoke insert,update on student.* from test@localhost;
建库与表
创建库
mysql> create database student; mysql> show databases;
![3f3fa8dc23dc31fd79d8fe78090d7686.png](https://i-blog.csdnimg.cn/blog_migrate/7eb8af0ec81b1112077d4a3d3d5a5a05.jpeg)
创建表
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%';
匹配查询
![7c8744bf9125ec19dab47bb4385b5590.png](https://i-blog.csdnimg.cn/blog_migrate/de9c1f65696f5833d2444ff19201a7a0.jpeg)
mysql> select * from T1 order by name,age;
查询排序
![4913c2468a02532018c4ffd4b58c9bc2.png](https://i-blog.csdnimg.cn/blog_migrate/4bff154a3f6e7ecd8b8ca40f57ab5b23.jpeg)
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;
查询值
![d02691c1d37006c4d91d920702dc6830.png](https://i-blog.csdnimg.cn/blog_migrate/e99bb3d8dfebec7d4455fb4df709c216.jpeg)
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);
条件查询
![8c241e1773097ee9bb8eae819069cced.png](https://i-blog.csdnimg.cn/blog_migrate/3ee622ee6f7fdd22043bd93e1b6d7f7b.jpeg)
mysql> select * from T2; mysql> select * from T1;
![98be078d581279e90e68c87ac8601ca7.png](https://i-blog.csdnimg.cn/blog_migrate/00b186500c19393247460f1ea66ec129.jpeg)
增删更新
增加与删除列
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;
![bbe0c14037616df094ff101c7e1ef90f.png](https://i-blog.csdnimg.cn/blog_migrate/5578f7aeb362e22fca259d06de5ef358.jpeg)
创建与删除视图
mysql> create view t1view as select name from T1;mysql> select * from t1view;
![5900c49f69780a658acd6a0393b41551.png](https://i-blog.csdnimg.cn/blog_migrate/daf56d40cdd1cf7d16d3e4a335fcda24.jpeg)
mysql> drop view t1view; mysql> select * from t1view; ERROR 1146 (42S02): Table 'student.t1view' doesn't exist #提示此视图不存在
![ac6f2c93967e25df4568a8519ecff5fc.png](https://i-blog.csdnimg.cn/blog_migrate/6a7a06836440b20556df4aa947ac72d4.jpeg)
以上有不足的地方可以指出讨论,觉得不错的朋友希望能得到您的转发支持,同时可以持续关注我,每天分享Linux C/C++后台开发干货内容!