SQL语句 sql语句创建表

创建数据库：

CREATE DATABASE IF NOT EXISTS 数据库名字 DEFAULT CHARSET utf8 COLLATE utf8_general_ci;

创建表：

CREATE TABLE IF NOT EXISTS `user`

( `user_id` INT UNSIGNED AUTO_INCREMENT,

`user_name` VARCHAR(100) NOT NULL,

`birthday` DATE,

PRIMARY KEY ( `user_id` ) )

ENGINE=InnoDB DEFAULT CHARSET=utf8;

---------------------------------------------------------------------------------------------------

增：

INSERT INTO 表名(字段名1，字段名2，……字段名N)

VALUES(值1，值2，……值N);

--------------------------------------------------------------------------------------------------------

删：

DELETE FROM school where school_name="北大";

-----------------------------------------------------------------------------------------------------------------------

改（更新）：

UPDATE 表名 SET 列1=新值1， 列2=新值2

UPDATE school SET school_name=“北大”，school_address=“中关村” WHERE id=6

更新学生分数小于30或分数大于130的课程为99

UPDATE score SET score=99 WHERE score<30 OR score>130

-----------------------------------------------------------------------------------------------------------------------------

查：(SELECT)

SELECT * FROM school

SELECT school_name from school

SELECT * FROM school WHERE school_name="北京大学"

school_name like "北%" ：%代表只要是首字是“北”的全部查出

school_name like "北____" :_ 代表有限制的字数

school_name like "%学"

school_name like "%京%"

SELECT * FROM score WHERE score >=145

<=6

!=5 不等于

SELECT * FROM school WHERE school_address !=" "

is null

is not null

------------------------------------------------------------------------------

SELECT * FROM school LIMIT 1,2 跳过一行，显示后两行

2 显示两行

SELECT * FROM score WHERE score IN(10,20,30) 显示score表中分数为10,20,30

NOT IN(10,20,30) 显示除了10,20,30分数的其他数

SELECT * FROM score WHERE score BETWEEN 1 AND 5 显示1到5中的分数

score>=1 AND score<=5 显示1到5中的分数

score=10 OR score=20 OR score=30 显示10 20 30的分数

---------------------------------------------------------------------------------------------------------------------------------

左关联：左表school全显示，右表grade对应的显示

select s.*,g,* from school s s是school的别名

left join grade g g是grade的别名

on s.id=g.school_id school表中的id 与 grade表中 的school_id相对应

---------------------------------------------------------------------------------------------------------------------------------

右关联：左表school对应的显示，右表grade全显示

select s.*,g,* from school s s是school的别名

right join grade g g是grade的别名

on s.id=g.school_id school表中的id 与 grade表中 的school_id相对应

-------------------------------------------------------------------------------------------------------------------------------

全关联：左表school全显示，右表grade全显示

select s.*,g,* from school s s是school的别名

join grade g g是grade的别名

on s.id=g.school_id school表中的id 与 grade表中 的school_id相对应

------------------------------------------------------------------------------------------------------------------------------

select * from score score order by score desc 代表从大到小排序

asc 代表从小到大排序

-------------------------------------------------------------------------------------------------------------------------------

select student_id,max(score) from score group by student_id 每个学生课程的最高分数

min 低

avg 平均

count 课程个数

sum 每个学生课程分数的总和

select student_id，sum(score) from score

group by student_id

having sum(score)>100 总分数大于100

order by sum(score) desc 分数从小到大排序