2023年6月27日发(作者：)

mysql数据库基础命令⽤户与权限创建⽤户mysql>create user test identified by 'BaC321@#';修改密码##5.5版本及以前的命令mysql>set password for test=passowrd('!1A@2#3');

##5.6及以上命令mysql>update 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> 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###提⽰此视图不存在