Ⅰ.sql基础

1. 定义

用于访问和处理数据库的标准的计算机语言。

创建数据库
CREATE DATABASE dbname;
使用数据库
use dbname;

创建表
CREATE TABLE table_name
(
column_name1 data_type(size),
column_name2 data_type(size),
column_name3 data_type(size),
....
);

2. 修改密码

mysql> set password for root@localhost = password('123');  

3.常用命令

SELECT - 从数据库中提取数据
UPDATE - 更新数据库中的数据
DELETE - 从数据库中删除数据
INSERT INTO - 向数据库中插入新数据
CREATE DATABASE - 创建新数据库
ALTER DATABASE - 修改数据库
CREATE TABLE - 创建新表
ALTER TABLE - 变更（改变）数据库表
DROP TABLE - 删除表
CREATE INDEX - 创建索引（搜索键）
DROP INDEX - 删除索引

4. select

查表

SELECT column_name,column_name FROM table_name;
SELECT name,country FROM Websites;

SELECT * FROM table_name;
SELECT * FROM Websites;

5.select distinct

查表并去除重复数据

SELECT DISTINCT column_name,column_name FROM table_name;
SELECT DISTINCT country FROM Websites;

5.where

过滤

SELECT column_name,column_name FROM table_name WHERE column_name operator value;
SELECT * FROM Websites WHERE country='CN';
文本使用单引号，数字不需要单引号。

where逻辑运算符

6.insert into

INSERT INTO table_name VALUES (value1,value2,value3,...);
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');

7.update

UPDATE table_name
SET column1=value1,column2=value2,...
WHERE some_column=some_value;

8. delete

DELETE FROM table_name
WHERE some_column=some_value;
DELETE FROM Websites
WHERE name='百度' AND country='CN';

9.order by

SELECT column_name,column_name FROM table_name
ORDER BY column_name,column_name ASC|DESC;

#升序排列
SELECT * FROM Websites
ORDER BY alexa;

#降序排列
SELECT * FROM Websites
ORDER BY alexa DESC;

#多列
SELECT * FROM Websites
ORDER BY country,alexa;

10.drop

#DROP TABLE 语句用于删除表
DROP TABLE table_name

#DROP DATABASE 语句用于删除数据库
DROP DATABASE database_name

#仅删除表内的数据，但并不删除表本身
TRUNCATE TABLE table_name

11.alter table

#在表中添加列
ALTER TABLE table_name
ADD column_name datatype

#删除表中的列
ALTER TABLE table_name
DROP COLUMN column_name

#改变表中列的数据类型
ALTER TABLE table_name
MODIFY COLUMN column_name datatyp