2018-02-08 sql

链接本地数据库，下载xampp以及Navicat

查询表中某个字段

SELECT name FROM ylan_student 

查询表中多个字段

SELECT name,age FROM ylan_student

关键词 DISTINCT 用于返回唯一不同的值

SELECT DISTINCT age FROM ylan_student

选取年龄大于23的人的姓名

SELECT name FROM ylan_student WHERE age > 23

年龄为23且为女性的人的信息

SELECT * FROM ylan_student WHERE age=23 AND sex='女'

SELECT * FROM ylan_student WHERE age=23 OR sex='女'

SELECT * FROM ylan_student WHERE(age=23 OR age=21)AND sex='男'

年龄排序

SELECT * FROM ylan_student ORDER BY age

插入一条数据(全部字段)

INSERT INTO ylan_student VALUES('李喜光',24,'男',1,1)

更新数据

UPDATE ylan_student SET name = 'arc' WHERE name = '李喜光'AND age=24

删除数据

DELETE FROM ylan_student WHERE name = 'adc' 

删除所有行

DELETE FROM ylan_student

创建表 

CREATE TABLE Persons

(

//自动增加

Id_P int NOT NULL AUTO_INCREMENT,

LastName varchar(255),

FirstName varchar(255),

Address varchar(255),

City varchar(255),

//主键

PRIMARY KEY(id_P)

)

修改表名

rename table Persons to ylan_student2

取三条数据

SELECT *

FROM ylan_student

LIMIT 3

COUNT(*) 函数返回表中的记录数：

SELECT COUNT(*)FROM table_name;

平均成绩降序排列以及学生的信息

SELECT  (score.mathe + score.chainese + score.english)/3  as avgs,student.* 

FROM student,score 

WHERE student.id=score.student_id ORDER BY avgs DESC;