SQL学习笔记

标签：www country SQL Websites 学习 笔记 mysql com SELECT

1. 创建数据库、显示数据库、使用数据库

mysql>    create database test;

mysql>    show databases;

mysql>    use test;

2. 创建数据表、插入数据

+------+--------------+---------------------------+-------+---------+

| id   | name         | url                       | alexa | country |

+------+--------------+---------------------------+-------+---------+

|    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     |

+------+--------------+---------------------------+-------+---------+

mysql>  CREATE TABLE Websites ( id int, name varchar(255), url varchar(255), alexa int, country varchar(255) );

  mysql> INSERT INTO Websites VALUES (1, 'Google', 'https://www.google.cm/', 1, 'USA');

  mysql> INSERT INTO Websites VALUES (2, '淘宝', 'https://www.taobao.com/', 13, 'CN');

  mysql> INSERT INTO Websites VALUES (3, '菜鸟教程', 'http://www.runoob.com/', 4689, 'CN');

  mysql> INSERT INTO Websites VALUES (4, '微博', 'http://weibo.com/', 20, 'CN');

  mysql> INSERT INTO Websites VALUES (5, 'Facebook', 'https://www.facebook.com/', 3, 'USA');

3. SELECT、SELECT DISTINCT 、WHERE、AND/OR

# SELECT
mysql> SELECT name,country FROM Websites;
mysql> SELECT * FROM Websites;

# SELECT DISTINCT
mysql> SELECT DISTINCT country FROM Websites;
mysql> SELECT DISTINCT country,url FROM Websites; # country,url的组合是唯一的

# WHERE
mysql> SELECT * FROM Websites WHERE id=1;

#AND/OR
mysql-> SELECT * FROM Websites
     -> WHERE alexa > 15
     -> AND (country='CN' OR country='USA');

#ORDER BY
mysql> SELECT * FROM Websites ORDER BY alexa DESC; #DESC降序
mysql> SELECT * FROM Websites ORDER BY country,alexa DESC; #ORDER BY多列

4. INSERT INTO、UPDATE、DELETE

# INSERT INTO # 插入新记录
mysql> INSERT INTO Websites (name, url, alexa, country) 
       -> VALUES ('百度','https://www.baidu.com/','4','CN');

#UPDATE # 更新某记录
mysql> UPDATE Websites 
    -> SET alexa='5000', country='USA' 
    -> WHERE name='菜鸟教程';

#DELETE # 删除某记录
mysql> DELETE FROM Websites
    -> WHERE name='Facebook' AND country='USA';

#DELETE # 删除表中所有记录
mysql> DELETE FROM WebSites;

 

标签：www,country,SQL,Websites,学习,笔记,mysql,com,SELECT
From： https://www.cnblogs.com/Xylophone/p/16772435.html