mysql数据库基础命令

灰太狼 2022-04-12 07:24 224阅读 0赞

**用户与权限**

**创建用户**

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数据库基础命令（一）][mysql]

**移除权限**

mysql> revoke insert,update on student.\* from test@localhost;

**建库与表**

**创建库**

mysql> create database student;

mysql> show databases;

![mysql数据库基础命令（一）][mysql 1]

**创建表**

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数据库基础命令（一）][mysql 2]

mysql> select \* from T1 order by name,age;

\#\#查询排序

![mysql数据库基础命令（一）][mysql 3]

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数据库基础命令（一）][mysql 4]

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数据库基础命令（一）][mysql 5]

mysql> select \* from T2;

mysql> select \* from T1;

![mysql数据库基础命令（一）][mysql 6]

**增删更新**

**增加与删除列**

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数据库基础命令（一）][mysql 7]

**创建与删除视图**

mysql> create view t1view as select name from T1;

mysql> select \* from t1view;

![mysql数据库基础命令（一）][mysql 8]

mysql> drop view t1view;

mysql> select \* from t1view;

ERROR 1146 (42S02): Table 'student.t1view' doesn't exist

\#\#\#提示此视图不存在

[mysql]: /images/20220412/f17032b0c4ac48bd898536d8006c2ce7.png
[mysql 1]: /images/20220412/03513d49c16c4d438cf77339937e9c4c.png
[mysql 2]: /images/20220412/5f3a3cf79e204f00a54aabea8449df69.png
[mysql 3]: /images/20220412/8b25fc78ddbc4dccbd07c47dc79b74ef.png
[mysql 4]: /images/20220412/dcbbfca9a8d44fe8b7377875e34e47b8.png
[mysql 5]: /images/20220412/711841ddc1ff4896b791ca6e648c4f6a.png
[mysql 6]: /images/20220412/5d141fd63a39419fb6647e5e5d652b6a.png
[mysql 7]: /images/20220412/7f6f63d00a2e49be8c429e39f6fd594e.png
[mysql 8]: /images/20220412/302e64c80f474bd3917c572ed1e3b2b9.png