SQL练习

最新推荐文章于 2024-10-09 22:03:16 发布

lilimu996

最新推荐文章于 2024-10-09 22:03:16 发布

阅读量80

点赞数

分类专栏： sql 文章标签： sql 数据库 mysql

本文链接：https://blog.csdn.net/lilililimu/article/details/128133650

版权

sql 专栏收录该内容

1 篇文章 1 订阅

订阅专栏

show databases;
#使用数据库
use test;
#创建表
CREATE TABLE website (
    id      INT              NOT NULL   AUTO_INCREMENT,
    name    VARCHAR(20)      NOT NULL,
    url     VARCHAR(30)                 DEFAULT '',
    age     TINYINT UNSIGNED NOT NULL,
    alexa   INT UNSIGNED     NOT NULL,
    uv      FLOAT                       DEFAULT '0',
    country CHAR(3)          NOT NULL   DEFAULT '',
    PRIMARY KEY (`id`)
);
#查看表
desc website ;
#重命名表
ALTER TABLE tb_website RENAME TO website;
RENAME tb_website TO website;
#修改表的字符集，不然会插入中文失败
alter table website  character set utf8;
#修改出错的数据格式
alter table website change name name varchar(20) character set utf8;
#插入数据
INSERT INTO website(name, url, age, alexa, uv, country)
VALUES('百度', 'https://www.baidu.com/', 21, 4, 5010.5, 'CN');
INSERT INTO website(name, url, age, alexa, uv, country)
VALUES('淘宝','https://www.taobao.com/', 17, 8, 3996.75, 'CN');
INSERT INTO website(name, url, age, alexa, uv, country)
VALUES('C语言中文网', 'http://c.biancheng.net/', 12, 7923, 11.62, 'CN');
INSERT INTO website(name, url, age, alexa, uv, country)
VALUES('Google', 'https://www.google.com/', 23, 1, 36474, 'US');
INSERT INTO website(name, url, age, alexa, uv, country)
VALUES('GitHub', 'https://github.com/', 13, 95, 216.3, 'US');
INSERT INTO website(name, url, age, alexa, uv, country)
VALUES('Stack Overflow', 'https://stackoverflow.com/', 16, 48, 592.2, 'US');
INSERT INTO website(name, url, age, alexa, uv, country)
VALUES('Yandex', 'http://www.yandex.ru/', 11, 53, 591.82,'RU');
INSERT INTO website VALUES(14,'VK', 'https://vk.com/', 23,23,1206, 'RU');
#SELECT语句
SELECT id, name, url FROM website;
#WHERE语句
SELECT id, name, url, uv
FROM website
WHERE uv > 800;

SELECT id, name, url, uv
FROM website
WHERE uv > 500 AND name LIKE '%o%';
#AND和OR
SELECT id, name, url, uv
FROM website
WHERE uv>500 AND age<20;

SELECT id, name, url, uv
FROM website
WHERE uv<500 OR age>20;