1、SHOW DATABASES; 查看已有的数据库
2、USE [db_name]; 使用某个数据库
3、三个关键词:CREATE(创建)、DROP(删除)、ALTER(修改)
四种基本对象:DATABASE, TABLE, VIEW(视图), INDEX(索引)
**ALTER只能作用于TABLE
4、CREATE TABLE [table_name] (
[col_name] [type] [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT] [UNIQUE] [PRIMARY KEY],
...
);
5、数据操纵语言:
插入新数据:INSERT [INTO] table_name [(col_name,...)] VALUES(pression,...)
如INSERT INTO Student VALUES('07210100', '张三', '男', 19, '信息系');
INSERT [INTO] table_name SET col_name=expression,...
6、数据查询语言SELECT
SELECT column_list (选择哪些列显示)
FROM table_list (从哪个表中选行)
WHERE primary_constraint (行必须满足的条件)
GROUP BY grouping_columns (怎样对结果分组)
HAVING secondary_constraint (行必须满足的第二个条件)
ORDER BY sorting_columns[ASC|DESC] [,...] (根据哪一行排序)
多表查询:从多个表中查询数据,通过条件WHERE建立联系
如SELECT Student.*,sc.* FROM Student,sc WHERE Student.sno=sc.sno;
7、替换语句:REPLACE、REPLACE...SELECT
8、LOAD批量导入数据
基本语法:LOAD DATA [LOCAL] INFILE 'file_name.txt' [REPLACE|IGNORE] INTO TABLE tbl_name
FIELDS和LINES子句,明确数据源格式
"0001",'大学英语',"1001","12"
LOAD DATA INFILE 'c:\data.txt' INTO TABLE course
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n';
但是文本格式需要时UTF-8编码
9、修改数据
UPDATE tbl_name SET [要更改的列]
WHERE [要更新的记录]
如UPDATE Student SET Sname="方岳红" WHERE Sno="072101004";
10、删除数据
DELETE FROM Student
WHERE Sno="072101005";