[大咖. 来了 第 7 期] 10 月 24 日晚 8 点观看《智能导购对话机器人实践》
今天介绍的是关天 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;
移除权限
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
- # 提示此视图不存在
点击关注 民工哥技术之路 微信公众号对话框回复关键字: 1024 可以获取一份最新整理的技术干货: 包括系统运维, 数据库, Redis,MogoDB, 电子书, Java 基础课程, Java 实战项目, 架构师综合教程, 架构师实战项目, 大数据, Docker 容器, ELK Stack, 机器学习, BAT 面试精讲视频等.
来源: http://database.51cto.com/art/201910/604612.htm