6-3（数据库- 增删改查）

创建表student

CREATE TABLE student(
学号 VARCHAR(20) primary key,
姓名 VARCHAR(10),
年龄 INT,
性别 CHAR(5)
);

删除表

drop table sdudent

添加列

ALTER TABLE student ADD 地址 VARCHAR(50);

删除列

ALTER TABLE student DROP 地址 ;

-修改属性

ALTER TABLE student MODIFY 地址 VARCHAR(40)

修改字段名

ALTER TABLE student CHANGE 地址 住址 VARCHAR(40);

查看表结构

desc student

添加主键约束userid

ALTER TABLE student ADD PRIMARY KEY(userid);

删除主键约束userid

ALTER TABLE student DROP PRIMARY KEY;

insert插入

INSERT INTO stu (uid,NAME,age,sex) VALUES(1,'cc',18,1) 

delete 删除

delete from 表名

DELETE FROM stu WHERE uid =1

update更新

UPDATE stu SET age = 80 WHERE uid =1

查询

SELECT uid,NAME,age,sex FROM stu
SELECT * FROM stu

查询不重复的年龄

SELECT DISTINCT age FROM stu

别名查询，即查询结果用别名显示

SELECT age 'nianling' FROM stu

选择查询

SELECT uid,NAME,age,sex FROM stu WHERE age IN(15,18)

like

SELECT * FROM stu WHERE NAME LIKE '%c%'

not null

SELECT * FROM stu WHERE age IS NOT NULL

计算年龄总值

SELECT SUM(age) FROM stu

统计某个列有多少记录

SELECT COUNT(age) FROM stu

查询前n行

SELECT * FROM stu LIMIT 2

group

相同的性别为一组，将组内所有人的年龄相加

SELECT sex,SUM(age) FROM stu GROUP BY sex

having 可以加聚合函数 where 不可以

SELECT sex,SUM(age) FROM stu GROUP BY sex HAVING SUM(age)<50

排序

DESC 降 默认升序

SELECT * FROM stu ORDER BY age DESC

计算字符长度

SELECT LENGTH(NAME) FROM stu