create user test identified by 'BaC321@#';修改密碼5.5版本及以前的命令mysql>set password for test=passowrd..." />
今天介紹的是關天Mysql數據庫一些操作的基礎命令用戶與權限創建用戶mysql>create user test identified by 'BaC321@#';修改密碼5.5版本及以前的命令mysql>set password for test=passowrd...
今天介紹的是關天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
#提示此視圖不存在
來源:本文內容搜集或轉自各大網絡平臺,并已注明來源、出處,如果轉載侵犯您的版權或非授權發布,請聯系小編,我們會及時審核處理。
聲明:江蘇教育黃頁對文中觀點保持中立,對所包含內容的準確性、可靠性或者完整性不提供任何明示或暗示的保證,不對文章觀點負責,僅作分享之用,文章版權及插圖屬于原作者。
Copyright?2013-2024 JSedu114 All Rights Reserved. 江蘇教育信息綜合發布查詢平臺保留所有權利
蘇公網安備32010402000125
蘇ICP備14051488號-3技術支持:南京博盛藍睿網絡科技有限公司
南京思必達教育科技有限公司版權所有 百度統計