Mysql資料庫基礎操作命令

民工哥發表於2019-10-18
MySql資料庫

接上一篇：MySQL基礎入門之常用命令介紹

今天介紹的是關天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面試精講視訊等。

相關文章