一:數(shù)據(jù)查詢語言(DQL),Data Query Language,用以從表中獲取數(shù)據(jù),確定數(shù)據(jù)怎樣在程序中給出。SELECT是DQL中用的最多的!
select user,host,password from mysql.user order by user asc;select user,host,password from mysql.user order by user desc;
二:數(shù)據(jù)操作語言(DML),Data Manipulation Language,包括INSERT, UPDATE和DELETE。
delete from mysql.user where user='myname';
三:事物處理語言(TPL),它的語句能確保被DML語句影響的表的所有行及時得更新。TPL語句包括BEGIN, TRANSACTION, COMMIT和ROLLBACK。
四:數(shù)據(jù)控制語言(DCL),DCL全稱是Data Control Language,包括GRANT或REVOKE。
五:數(shù)據(jù)定義語言(DDL),Data Definition Language,其語句包括create和DROP。
六:指針控制語言(CCL),DECLARE CURSOR,FETECH INTO等
最后可以總結(jié)為如下:
SQL常見操作
1:創(chuàng)建數(shù)據(jù)庫create database myyang DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;show create database myyang; 2:查看當前使用的數(shù)據(jù)庫select database();select version();select user();select now(); 3:刪除數(shù)據(jù)庫drop database myyang; 4:進入數(shù)據(jù)庫use myyang; 5:使用-e參數(shù)不進入數(shù)據(jù)庫執(zhí)行命令 mysql -uroot -p123456 -e "show grants for myyang@localhost;" 6:回收權(quán)限REVOKE INSERT ON test.* FROM 'myyang'@'localhost'; 7:MySQL的權(quán)限 1 SELECT, 2 INSERT, 3 UPDATE, 4 DELETE, 5 CREATE, 6 DROP, 7 REFERENCES, 8 INDEX, 9 ALTER, 10 CREATE TEMPORARY TABLES, 11 LOCK TABLES, 12 EXECUTE, 13 CREATE VIEW, 14 SHOW VIEW, 15 CREATE ROUTINE, 16 ALTER ROUTINE, 17 EVENT, 18 TRIGGER8:建表示例create table student(id int(4) not null, name char(20) not null, age tinyint(2) not null default '0', dept varchar(16) default null) ENGINE=InnoDB DEFAULT CHARSET=utf8;
索引
主鍵索引
create table student2(id int(4) not null AUTO_INCREMENT,name char(20) not null, age tinyint(2) NOT NULL dept varchar(16) default NULL, primary key(id),KEY index_name(name) ); PS: primary key(id) 是主鍵 KEY index_name(name) name字段普通索引alter table student2 drop primary key; 刪除主鍵alter table student2 change id id int primary key auto_increment; 利用alter命令修改id列為自增主鍵
普通索引
KEY index_name(name) index_name 是索引的名字 name是創(chuàng)建普通索引的字段alter table student2 drop index index_name;刪除索引alter table student2 add index index_name(name);添加索引 對前n個字符簡歷索引create index index_dept on student2(dept(8)); 對dept列的前8個字符創(chuàng)建索引show index form student2; 查看索引 對表的多個字段創(chuàng)建聯(lián)合索引create index ind_name_dept on student2(name,dept); ps:聯(lián)合索引允許列上有自己的索引 也可以使用drop來刪除索引drop index ind_name_dept on student2; 對多個列的前n個字符創(chuàng)建索引create index ind_name_dept on student(name(8),dept(10)); 聯(lián)合索引是有前綴生效條件的 index(a,b,c)僅a,ab,abc三個查詢條件可以走索引,b,bc,ac,c等無法使用索引了
唯一索引
創(chuàng)建唯一索引,插入的時候必須唯一,多用于郵箱注冊create unique index uni_index_name on student(name);
索引列的創(chuàng)建及生效條件
索引占用系統(tǒng)空間,更新數(shù)據(jù)庫時需要維護索引數(shù)據(jù),索引并不是越多越好,更新頻繁讀取較少的表要少建立索引
索引一定要創(chuàng)建在where后的條件列上,而不是select后選擇的數(shù)據(jù)列,盡量選擇在唯一值多的大表上建立索引
插入數(shù)據(jù)
create table test(id int(4) not null AUTO_INCREMENT,name varchar(20) not null, PRIMARY KEY(id)); 1:指定所有列明然后給數(shù)據(jù)insert into test(id,name) values(1,'ob'); 2:由于主鍵是自增的,因此只需要給name列insert into test(name) values('oc'); 3:不指定列,必須按照列的對應(yīng)一個一個給值,也不能省掉自增列insert into test values(3,'od');insert into test values(4,'oe'),(5,'of');
備份數(shù)據(jù)庫
mysqldump -uroot -p123456 -B test > /opt/test.sql 查看備份的sql語句grep -E -v "#|\/|^$|--" /opt/test.sqldump是邏輯備份,把sql的數(shù)據(jù)以sql的命令形式導出
查詢數(shù)據(jù)
select * from test;select id,name from test; ps:不建議用* 查詢兩個select id,name from test limit 2;select id,name from test limit 0,2;從0開始查查詢兩個select id,name from test where id=1;select id,name from test where name='od';字符串類型的查詢條件需要引號select id,name from test where name='od' and id=3; and代表兩遍都要成立select id,name from test where name='od' or id=5;or代表或select id,name from test where id>2 and id<4;select id,name from test order by id asc; asc是升序select id,name from test order by id desc; desc是降序
連表查詢
```
1:創(chuàng)建學生表
mysql> create table student(
-> Sno int(10) NOT NULL COMMENT '學號',
-> Sname varchar(16) NOT NULL COMMENT '姓名',
-> Ssex char(2) NOT NULL COMMENT '性別',
-> Sage tinyint(2) NOT NULL default '0' COMMENT '學生年齡',
-> Sdept varchar(16) default NULL COMMENT '學生所在系別',
-> PRIMARY KEY (Sno),
-> key index_Sname (Sname));
2:創(chuàng)建課程表
mysql> create table course( -> Cno int(10) NOT NULL COMMENT '課程號',
-> Cname varchar(64) NOT NULL COMMENT '課程名',
-> Ccredit tinyint(2) NOT NULL COMMENT '學分',
-> PRIMARY KEY (Cno));
3:選課表
mysql> create table SC(
-> SCid int(12) NOT NULL AUTO_INCREMENT COMMENT '主鍵',
-> Cno int(10) NOT NULL COMMENT '課程號',
-> Sno int(10) NOT NULL COMMENT '學號',
-> Grade tinyint(2) NOT NULL COMMENT '學生成績',
-> PRIMARY KEY (Scid));
4:插入數(shù)據(jù)
insert into student values(0001,'趙信','男',30,'計算機網(wǎng)絡(luò)');
insert into student values(0002,'蓋倫','男',30,'Python');
insert into student values(0003,'孫悟空','男',30,'Java');
insert into student values(0004,'亞索','男',30,'Go');
insert into student values(0005,'提莫','女',6,'.net');
insert into student values(0006,'金克斯','女',6,'shell');
insert into course values(1001,'Linux運維',3);
insert into course values(1002,'Python開發(fā)',5);
insert into course values(1003,'Java開發(fā)',4);
insert into course values(1004,'.NET開發(fā)',4);
insert into course values(1005,'Go開發(fā)',4);
insert into course values(1006,'shell開發(fā)',3);
insert into SC(Sno,Cno,Grade) values(0005,1004,9);
insert into SC(Sno,Cno,Grade) values(0004,1003,6);
insert into SC(Sno,Cno,Grade) values(0003,1002,1);
insert into SC(Sno,Cno,Grade) values(0002,1001,3);
insert into SC(Sno,Cno,Grade) values(0001,1005,6);
insert into SC(Sno,Cno,Grade) values(0006,1006,6);
5:連表查詢
select student.Sno,student.Sname,course.Cname,SC.Grade from student,course,SC where student.Sno=SC.Sno and course.Cno=SC.Cno order by Sno;
http://www.cnblogs.com/skymyyang/p/7154419.html