环境:mysql-5.5.15,mysql-front 5.3
新建table websites
SET NAMES utf8;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for `websites`
-- ----------------------------
DROP TABLE IF EXISTS `websites`;
CREATE TABLE `websites` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` char(20) NOT NULL DEFAULT '' COMMENT '站点名称',
`url` varchar(255) NOT NULL DEFAULT '',
`alexa` int(11) NOT NULL DEFAULT '0' COMMENT 'Alexa 排名',
`country` char(10) NOT NULL DEFAULT '' COMMENT '国家',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of `websites`
-- ----------------------------
BEGIN;
INSERT INTO `websites` VALUES ('1', 'Google', 'https://www.google.cm/', '1', 'USA'), ('2', '淘宝', 'https://www.taobao.com/', '13', 'CN'), ('3', '菜鸟教程', 'http://www.runoob.com/', '4689', 'CN'), ('4', '微博', 'http://weibo.com/', '20', 'CN'), ('5', 'Facebook', 'https://www.facebook.com/', '3', 'USA');
COMMIT;
SET FOREIGN_KEY_CHECKS = 1;
数据如下:
SELECT
从表中选择某些列,结果被存储在一个结果表中,称为结果集。
SELECT column_name,column_name FROM table_name;
select name, url from websites;
从表中选择所有的列
SELECT * FROM table_name;
SELECT * FROM websites;
SELECT DISTINCT
用于返回唯一不同的值
SELECT DISTINCT column_name,column_name FROM table_name;
select DISTINCT country from websites
WHERE
WHERE 子句用于提取那些满足指定标准的记录。
SELECT column_name,column_name
FROM table_name
WHERE column_name operator value;
select * from websites where country = 'USA'
select * from websites where alexa < 100
AND & OR 运算符
SELECT * FROM Websites
WHERE alexa > 15
AND (country='CN' OR country='USA');
ORDER BY
对结果集进行排序。
默认是升序
SELECT * FROM Websites
WHERE alexa > 15
AND (country='CN' OR country='USA')
ORDER BY alexa;
使用关键字DESC,按照降序对记录进行排序,这个关键字只对它紧邻的column name起作用
SELECT * FROM Websites
WHERE alexa > 15
AND (country='CN' OR country='USA')
ORDER BY alexa DESC;
还可以对多列进行排序,先按照第一个column name排序,再按照第二个column name排序
SELECT * FROM Websites
ORDER BY country, alexa;
在country相同时,再根据alexa排序。
INSERT INTO
1. 指定列名及被插入的值
INSERT INTO table_name (column1,column2,column3,...)
VALUES (value1,value2,value3,...);
INSERT INTO Websites (name, url, alexa, country)
VALUES ('百度','https://www.baidu.com/','4','CN');
没有向id字段插入值,这个值会自动更新,且每条记录都是唯一的。
2. 无需指定要插入数据的列名,只需提供被插入的值即可
INSERT INTO table_name VALUES (value1,value2,value3,...);
UPDATE
更新表中已存在的记录
UPDATE table_name
SET column1=value1,column2=value2,...
WHERE some_column=some_value;
不使用WHERE子句的话会更新所有记录!
执行命令
SET SQL_SAFE_UPDATES = 1
使MySql运行在safe-updates模式下,该模式会导致非主键条件下无法执行update或者delete命令,如果不是用主键当where语句,会报如下错误。但是在这个模式下,就只能使用主键操作。
UPDATE Websites SET country='USA'
UPDATE Websites
SET alexa='5000', country='USA'
WHERE id = 3;
DELETE
删除表中的行,和UPDATE一样需要注意不使用WHERE子句的话会删除所有记录!
DELETE FROM table_name
WHERE some_column=some_value;
DELETE FROM Websites
WHERE name='百度' AND country='CN';