mysql基本操作

標籤：table   _id   pass   語句   cut   lock   pos   out   建立   

建立庫：create database test1 charset utf8;

查看庫：show databases;
查看創庫語句：show create database test1;
選擇庫：use test1
修改庫：alter database test1 charset utf8mb4;
刪除庫：drop database test1;

建立表：create table stu (id int,name varchar(10));
建立相同表結構的表：create table stu1 like stu;
建立一個相同的備用表：create table stu2 as select * from stu;
查看錶：show tables;
查看創表語句：show create table stu;
查看錶結構：desc stu;
修改表名：alter table stu rename to student;
刪除表：drop table stu;
刪除大表：truncate table stu;(對於很大的表用drop刪慢，可以先用truncate再用drop)

alter修改表：
1.在表最後加一列：alter table student add addr varchar(20);
2.在表頭部加一列：alter table student add stu_id int first;
3.在name列後加一列：alter table student add qq int after name;
4.在age後加tel_num,在最後一行加email：alter table student add tel_num int after age,add email varchar(20);
5.刪除某一列：alter table student drop id;
6.修改列名字：alter table student change name stu_name varchar(20);
7.修改列資料類型：alter table student modify gender varchar(20);

insert向表中插入資料：
1.插入單行：insert [into] student values(1,‘beizi‘,111,20,110,‘male‘,‘bj‘,‘[email protected]‘);
2.指定列進行插入：insert into student(stu_id,stu_name,qq) values(2,‘li4‘,456);
3.多行插入：insert into student values(1,‘zhang3‘,123,20,110,‘male‘,‘bj‘,‘[email protected]‘),(5,‘zz‘,12322,202,1102,‘female‘,‘bj‘,‘[email protected]‘);
4.一個表向另一個表插入：insert into stu0 select * from student;（表結構要一樣）

update修改表資料：
修改一行：update student set stu_name=‘zhangsan‘ where stu_id=1;

delete刪除表資料：
delete from student where stu_name=‘zhangsan‘;

註：delete刪除比較危險，可以用標識符配合update進行偽刪除：
alter table stu0 add state int default 1;
update stu0 set state=0 where stu_name=‘zhangsan‘;
select * from stu0 where state=1;

select查詢語句詳解下次繼續

MySQL提供的許可權:
INSERT,SELECT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE
授權許可權：grant SELECT,INSERT on test.* to [email protected]‘192.168.1.%‘ identified by ‘123456‘;
收回許可權：revoke SELECT,INSERT on test.* from [email protected]‘192.168.1.%‘;
查看許可權：show grants for [email protected]‘192.168.1.%‘;
提醒：
　　如果，庫層級和表層級都設定了許可權，那麼對錶操作時，許可權是疊加。
查看mysql資料庫user表裡一些資訊：
　　select user,host,password from mysql.user;
註：5.7密碼欄位不再是password了，被替換為了authentication_string
5.7資料庫:
mysql> select user,host,authentication_string from mysql.user;
+---------------+-----------+-------------------------------------------+
| user | host | authentication_string |
+---------------+-----------+-------------------------------------------+
| root | localhost | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+---------------+-----------+-------------------------------------------+
若是資料庫密碼忘了，想改資料庫密碼，在5.6裡操作是：
a)關閉服務：service mysqld stop
b)停止授權啟動：mysqld_safe --skip-grant-table --skip-networking & //--skip-grant-table跳過授權，--skip-grant-table禁止遠端連線
c)更改密碼：update mysql.user set password=PASSWORD(‘123‘) where user=‘root‘ and host=‘localhost‘;
　　（在5.7裡將password替換為authentication_string）
d)重啟mysql服務

mysql基本操作