Mysql基础
来源:互联网 发布:js鼠标移动显示图片 编辑:程序博客网 时间:2024/05/29 16:38
-- 查看数据库
SHOW DATABASES;
-- 创建数据库
USE day19;
-- 查看这个数据库中的表
SHOW TABLES;
-- 创建学生表,数据有(学号,姓名,年龄)
age INT,
DESC stu;
-- 添加字段address varchar(20)
ALTER TABLE student ADD COLUMN address VARCHAR(20);
-- 修改字段类型address varchar(30)
ALTER TABLE student MODIFY COLUMN address VARCHAR(30);
-- 修改字段名称,同时还可以修改字段类型address 改为address2
ALTER TABLE student CHANGE COLUMN address address2 VARCHAR(20);
-- 删除字段address varchar(30)
ALTER TABLE student DROP COLUMN address2;
-- 删除多个字段
ALTER TABLE student DROP COLUMN age, DROP COLUMN NAME;
-- 修改表名
ALTER TABLE student RENAME stu;
-- 删除表
DROP TABLE student;
-- 对数据的操作
-- 添加数据添加所有列
INSERT INTO student VALUES(1,"张杰",34,56,45);
INSERT INTO student VALUES(2,"张",34,67,68);
INSERT INTO student VALUES(3,"杰",34,57,78);
-- 添加部分列
INSERT INTO student(id,NAME) VALUES(2,"张娜拉");
-- 修改一列数据
UPDATE student SET age=40;
-- 修改指定的数据
UPDATE student
SET NAME = "李连杰"
WHERE id = 1;
-- 删除全表数据(删除的时候是一行一行的删除)
DELETE FROM student;
-- 按条件删除数据
DELETE FROM student
WHERE id=2;
-- 第二种删除全表数据的方式 truncate table student
TRUNCATE TABLE student;
-- 注意两种删除全表数据的方式的区别
-- 1、delete from 删除的时候可以删除全部,也可以按条件删除,但是truncate只能全表删除
-- 2、delete from 删除的数据可以回滚,但是truncate不行
-- 3、delete from不可以把自增长约束(auto_increment)重置,truncate table可以把自增长约束(auto_increment)重置
-- 查看表中的数据
SELECT * FROM student;
-- 查询所有列
SELECT * FROM student;
-- 查询指定列
SELECT NAME FROM student;
-- 查询时指定别名(as是可以去掉的)
SELECT id AS '学号',NAME AS '名字' FROM student;
-- 合并列查询
-- 注意:合并列查询要求数据必须是数值类型的,其他的没有意义
SELECT id AS '学号',(selvlet+jsp) '总成绩' FROM student;
-- 查询是添加常量列
SELECT id '学号',(selvlet+jsp) '总成绩','计科二班' AS '班级' FROM student;
-- 查询是去除重复数据
SELECT DISTINCT(age) FROM student;
-- 另一种方式
SELECT DISTINCT age FROM student;
-- 条件查询
-- 逻辑条件: and 和 or
SELECT * FROM student WHERE NAME='张' AND age=34;
-- 比较条件:> < >= <= = between and
SELECT * FROM student WHERE jsp>70;
SELECT * FROM student WHERE jsp<=68;
SELECT * FROM student WHERE jsp=68;
SELECT * FROM student WHERE jsp BETWEEN 60 AND 80;
-- 判空条件:='' is null <>'' is not null
INSERT INTO student VALUES(4,NULL,'',34,56);
SELECT * FROM student WHERE NAME IS NULL;
SELECT * FROM student WHERE age='';
SELECT * FROM student WHERE age<>'';
SELECT * FROM student WHERE NAME IS NOT NULL;
-- 条件查询sum avg max min count
SELECT SUM(jsp) FROM student;
SELECT AVG(jsp) FROM student;
SELECT MAX(jsp) FROM student;
SELECT MIN(jsp) FROM student;
SELECT COUNT(*) FROM student;
SELECT COUNT(NAME) FROM student;
-- 分组查询(group by)
SELECT * FROM student GROUP BY NAME;
-- 分组筛选 (group by + having)
SELECT * FROM student GROUP BY selvlet HAVING jsp>70;
-- 分页查询(limit 起始行数(从0开始) 查询的行数)
SELECT * FROM student LIMIT 0,2;
SELECT * FROM student LIMIT 2,2;
-- 查询后排序 (order by)desc 降序 asc升序 默认是升序的
SELECT * FROM student ORDER BY jsp;
SELECT * FROM student ORDER BY jsp DESC;
SHOW DATABASES;
-- 创建数据库
CREATE DATABASE day19;
-- 删除数据库
drop database day19;
-- 使用数据库USE day19;
-- 查看这个数据库中的表
SHOW TABLES;
-- 创建学生表,数据有(学号,姓名,年龄)
CREATE TABLE student(
id INT,
NAME VARCHAR(20),age INT,
selvlet INT,
birth timestamp default current_timestamp,
)engine=innodb default character set utf8;
1、engine=innodb表示数据库引擎是innodb
2、timestamp有两个属性,分别是CURRENT_TIMESTAMP 和ON UPDATE CURRENT_TIMESTAMP两种,使用情况分别如下:
(1)CURRENT_TIMESTAMP
当要向数据库执行insert操作时,如果有个timestamp字段属性设为
CURRENT_TIMESTAMP,则无论这个字段有木有set值都插入当前系统时间
(2)ON UPDATE CURRENT_TIMESTAMP
当执行update操作是,并且字段有ON UPDATE CURRENT_TIMESTAMP属性。
set names gbk 设置数据库编码;
-- 查看表结构DESC stu;
-- 添加字段address varchar(20)
ALTER TABLE student ADD COLUMN address VARCHAR(20);
-- 修改字段类型address varchar(30)
ALTER TABLE student MODIFY COLUMN address VARCHAR(30);
-- 修改字段名称,同时还可以修改字段类型address 改为address2
ALTER TABLE student CHANGE COLUMN address address2 VARCHAR(20);
-- 删除字段address varchar(30)
ALTER TABLE student DROP COLUMN address2;
-- 删除多个字段
ALTER TABLE student DROP COLUMN age, DROP COLUMN NAME;
-- 修改表名
ALTER TABLE student RENAME stu;
-- 删除表
DROP TABLE student;
-- 对数据的操作
-- 添加数据添加所有列
INSERT INTO student VALUES(1,"张杰",34,56,45);
INSERT INTO student VALUES(2,"张",34,67,68);
INSERT INTO student VALUES(3,"杰",34,57,78);
-- 添加部分列
INSERT INTO student(id,NAME) VALUES(2,"张娜拉");
-- 修改一列数据
UPDATE student SET age=40;
-- 修改指定的数据
UPDATE student
SET NAME = "李连杰"
WHERE id = 1;
-- 删除全表数据(删除的时候是一行一行的删除)
DELETE FROM student;
-- 按条件删除数据
DELETE FROM student
WHERE id=2;
-- 第二种删除全表数据的方式 truncate table student
TRUNCATE TABLE student;
-- 注意两种删除全表数据的方式的区别
-- 1、delete from 删除的时候可以删除全部,也可以按条件删除,但是truncate只能全表删除
-- 2、delete from 删除的数据可以回滚,但是truncate不行
-- 3、delete from不可以把自增长约束(auto_increment)重置,truncate table可以把自增长约束(auto_increment)重置
-- 查看表中的数据
SELECT * FROM student;
-- 查询所有列
SELECT * FROM student;
-- 查询指定列
SELECT NAME FROM student;
-- 查询时指定别名(as是可以去掉的)
SELECT id AS '学号',NAME AS '名字' FROM student;
-- 合并列查询
-- 注意:合并列查询要求数据必须是数值类型的,其他的没有意义
SELECT id AS '学号',(selvlet+jsp) '总成绩' FROM student;
-- 查询是添加常量列
SELECT id '学号',(selvlet+jsp) '总成绩','计科二班' AS '班级' FROM student;
-- 查询是去除重复数据
SELECT DISTINCT(age) FROM student;
-- 另一种方式
SELECT DISTINCT age FROM student;
-- 条件查询
-- 逻辑条件: and 和 or
SELECT * FROM student WHERE NAME='张' AND age=34;
-- 比较条件:> < >= <= = between and
SELECT * FROM student WHERE jsp>70;
SELECT * FROM student WHERE jsp<=68;
SELECT * FROM student WHERE jsp=68;
SELECT * FROM student WHERE jsp BETWEEN 60 AND 80;
-- 判空条件:='' is null <>'' is not null
INSERT INTO student VALUES(4,NULL,'',34,56);
SELECT * FROM student WHERE NAME IS NULL;
SELECT * FROM student WHERE age='';
SELECT * FROM student WHERE age<>'';
SELECT * FROM student WHERE NAME IS NOT NULL;
-- 条件查询sum avg max min count
SELECT SUM(jsp) FROM student;
SELECT AVG(jsp) FROM student;
SELECT MAX(jsp) FROM student;
SELECT MIN(jsp) FROM student;
SELECT COUNT(*) FROM student;
SELECT COUNT(NAME) FROM student;
-- 分组查询(group by)
SELECT * FROM student GROUP BY NAME;
-- 分组筛选 (group by + having)
SELECT * FROM student GROUP BY selvlet HAVING jsp>70;
-- 分页查询(limit 起始行数(从0开始) 查询的行数)
SELECT * FROM student LIMIT 0,2;
SELECT * FROM student LIMIT 2,2;
-- 查询后排序 (order by)desc 降序 asc升序 默认是升序的
SELECT * FROM student ORDER BY jsp;
SELECT * FROM student ORDER BY jsp DESC;
SELECT * FROM student;
select * from table limit m,n
其中m是指记录开始的index,从0开始,
表示第一条记录 n是指从第m+1条开始,取n条。
select * from tablename limit 2,4 即取出第3条至第6条,4条记录
阅读全文
0 0
- MySQL基础
- MySQL基础
- MySql基础
- MySQL基础
- mysql基础
- MYSQL基础
- MySQL基础
- mysql基础
- mysql基础
- MySQL 基础
- mysql基础
- mysql基础
- mysql 基础
- MySQL基础
- MySql 基础
- mysql基础
- mysql基础
- mysql基础
- Mysql-Connector-ODBC 安装报错126,解决办法
- 一致性 hash 算法( consistent hashing )
- Android 手势滑动识别
- Unity3D编辑器之脚本打开SpritePacker窗口
- OPENGL—编码裁剪(Cohen-Sutherland)法裁剪图
- Mysql基础
- HDU2159
- 让ScrollView滑动到最顶端
- Paxos算法细节详解--通过现实世界描述算法
- MySQL学习笔记(4)
- Android 反编译初探 应用是如何被注入广告的
- 【LeetCode】210. Course Schedule II
- 深入理解UE4宏定义—— GENERATED_BODY
- Flutter进阶—实现动画效果(十)