sql基础语法

1. 数据库操作

创建数据库

CREATE DATABASE database_name;

CREATE DATABASE database_name;

示例：

CREATE DATABASE test_database;

删除数据库

DROP DATABASE database_name;

示例：

DROP DATABASE test_database;

使用数据库

USE database_name;

示例：

USE test_database;

2. 数据表操作

创建表

CREATE TABLE table_name(
column1 datatype constraint,
column2 datatype constraint,
.....
);

示例：

CREATE TABLE students(
in int primary key AUTO_INCREMENT,
name varchar(50) not null,
age int
);

删除表

DROP TABLE table_name;

示例：

DROP TABLE students;

修改表结构

添加列：

ALTER TABLE table_name ADD column_name datatype;

示例：

ALTER TABLE students ADD email varchar(100);

删除列：

ALTER TABLE table_name DROP COLUMN column_name;

示例：

ALTER TABLE students DROP COLUMN email;

3. 数据操作

插入数据

insert into table_name(column1,column2,...)values(value1,value2,...);

示例：

insert into students(name,age)values('Alice'，20);

查询数据

select column1,column2,..from table_name where condition;

示例：

select * from students where age>18;

更新数据

update table_name set column1=value1,column2=value2,...where condition;

示例：

update students set age = 21 where name='Alice';

删除数据

DELETE FROM table_name WHERE condition;

示例：

DELETE FROM students WHERE name = 'Alice';

4. 排序和分组

排序

SELECT * FROM table_name ORDER BY column_name ASC|DESC;

示例：

SELECT * FROM students ORDER BY age DESC;

分组

SELECT column1, COUNT(*) FROM table_name GROUP BY column1;

示例：

SELECT age, COUNT(*) FROM students GROUP BY age;

5. 聚合函数

COUNT()

SELECT COUNT(*) FROM students;

SUM()

SELECT SUM(age) FROM students;

AVG()

SELECT AVG(age) FROM students;

MAX()

SELECT MAX(age) FROM students;

MIN()

SELECT MIN(age) FROM students;