MySQL学习-入门语句以及增删查改
1. SQL入门语句
SQL,指结构化查询语言,全称是 Structured Query Language,是一种 ANSI(American National Standards Institute 美国国家标准化组织)标准的计算机语言,可以让我们可以处理数据库。
SQL语句主要分为:
- DQL:数据查询语言,用于对数据进行查询,如select
- DML:数据操作语言,对数据进行增加、修改、删除,如insert、udpate、delete
- DDL:数据定义语言,进行数据库、表的管理等,如create、drop
- TPL:事务处理语言,对事务进行处理,包括begin transaction、commit、rollback
- DCL:数据控制语言,进行授权与权限回收,如grant、revoke
- CCL:指针控制语言,通过控制指针完成表的操作,如declare cursor
1.1 常用入门语句
mysql -uroot -p密码 命令行链接 mysql
status; 查看数据状态信息
exit、quit 退出数据库连接
show databases; 显示所有的数据库
create database data charset=utf8; 新创建一个数据库
use 数据库名; 选择要编辑的数据库,例如是data,则语句就是 use data;
select database(); 查看当前在哪个数据库下
show tables; 展示数据库下所有的表
\c 当你输入有误,想重打的时候可以试试加个 \c 取消
source 数据库文件名.sql; 导入数据库到mysql中
可以把别人事先创建好的数据库文件导入到当前电脑的mysql中
desc `表名`; /describe ‘表名’ 查看表结构信息
1.2 SQL语句划分以及命名规则
基本书写规则
- 每一条语句都要以” ; ”英文分号结尾
- SQL语句不区分关键字的大小写,但是建议属于SQL语句的语法规则用大写,自建的表,字段用小写
- 字符串跟日期的类型的值都要以单引号括起来
- 单词之间需要使用半角的空格隔开
- 我们可以通过使用’’反引号来括起来表名跟字段名,避免关键字冲突
2.DQL数据查询语句
(1.)查询select
select 字段1,字段2 from 表名 从指定表中查询所有数据的字段1和字段2信息
(2.)按条件查询数据
select 字段1,字段2 from 表名 WHERE 字段名 = 字段值;
1、查找学生表的姓名跟年龄 SELECT name,age FROM student; 查找课程表中的id,课程名称和教室编号 select id,course,address from course; 2、查找学生表所有的信息 SELET * FROM student; 3、为字段名设置别名,简化字段名 SELECT name AS n,age AS a FROM student; 4、使用条件查询想要的数据,查学号为14的学生姓名 SELECT name FROM student WHERE id = 14; 使用条件查询名字叫'吴杰'的学生姓名和年龄 SELECT name,age FROM student WHERE name='吴杰'; 注意:应该根据你的实际需求查询所要的字段值,而不是使用 * ,使用这个效率很低。
(3.)逻辑运算符
1、查询年龄大于10岁小于18岁的学生 SELECT * FROM student WHERE age<18 ADN age>10; 查询年龄小于18岁 或者性别是女的学生 select name,age,description as des from student where age<18 or sex=2; 查询年龄在18-22之间的女生信息(班级、姓名、年龄和性别) select class,name,age,sex from student where age>=18 and age<=22 and sex=2; 查询309班的所有男生信息(姓名、年龄、个性签名) select name,age,description from student where class=309 and sex=1; 查询306班、305班、304班的学生信息(姓名、年龄、个性签名) select name,age,description from student where class=304 or class=305 or class=306;
(4.)范围运算符(between…and…)
判断字段值是否在指定区间内
1、 查询年龄在18-22之间的学生(班级、姓名、年龄和性别) SELECT class,name,age,sex FROM student WHERE age BETWEEN 18 AND 22; 2、 当然,反过来查的话,查询18-22岁以外的学生 SELECT class,name,age,sex FROM student WHERE NOT age BETWEEN 18 AND 22;
(5.)比较运算符
1、查询小于等于19岁的学生 SELECT * FROM student WHERE age<=18;
(6.)in运算符(多条件值查询)
IN 运算符 允许在 WHERE 子句中规定多个值。
1、查询304,305和306班级的学生信息 select name,class,age from student where class IN (304,305,306) 2、查询学号为 1、3、5的学生信息 SELECT * FROM student WHERE id IN(1,3,5);
(7.)模糊查询
LIKE 运算符 允许我们针对只知道部分字符串的情况下,查找所有的字符串,进行模糊查找
% 匹配任意多个字符 陈% _ 匹配任意一个字符 陈__
1、查询姓陈的学生 SELECT * FROM student WHERE name LIKE '陈%'; 查找名字以风字结尾的学生 select * from student WHERE name LIKE '%风'; 2、查询姓名中带林的学生 select * from student where name LIKE '%林%'; 查询姓名是2个文字组成的学生 select * from student where name LIKE '__';
(8.)聚合计算
- AVG 返回指定列的平均值
- COUNT 返回指定列中非NULL值的个数
- MIN 返回指定列的最小值
- MAX 返回指定列的最大值
- SUM 返回指定列的所有值之和
聚合运算都是写在select 后面 SELECT COUNT("字段") FROM 表名 WHERE 条件;
1、查询305班所有的学生数量 select COUNT(id) from student where class=305; # 上面查询结果中,字段会变成COUNT('id'),可以使用as别名来处理一下。 select COUNT(id) as c from student where class=305; 2、查询所有学生中最小的年龄 SELECT MIN(age) FROM student; 3、查询302班中所有学生的平均年龄。 select AVG(age) from student where class=302;
(9.)分组查询
GROUP BY子句, 可以对表进行分组,常常与聚合函数一起使用
GROUP BY 字段名,当前这个字段名在表中出现多少个不同的字段值,那么查询结果就会有多少个组。
1、查询表中有多少男女学生 SELECT sex,count(sex) FROM student GROUP BY sex; 2、查询学生表中各个年龄段的学生数量 SELECT age,COUNT(name) FROM student GROUP BY age; 3、查询各个班级的人数各是多少 select class,COUNT(id) FROM student GROUP BY class;
(10.)查询结果排序
- ORDER BY 子句,对查询结果排序
- ASC表示升序(从小到大),为默认值,
- DESC为降序(从大到小)
1、对309班级的学生的年龄进行倒叙排序 select name,age,sex from student where class=309 order by age desc;
补充:
结果排序可以多个字段排序
例如:对学生的年龄进行降序排列。
select id,name,sex from student order by age desc,id asc limit 10; # 上面就有2个排序的字段, # 系统会优先针对 age 进行降序排列, # 当age的值一致的时候,系统会按照id进行升序排列。
(11.)结果限制
LIMIT 子句,可以对查询出的结果进行数量限制,往往我们不想一次取出所有的数据
limit有两个使用方式:
- limit后面跟着 一个参数 表示限制结果的数量
- limit后面跟诊 两个参数,第一个参数表示取数据的开始下标[在表中下标从0开始],第二个参数表示限制结果的数量。
SELECT * FROM student LIMIT 3; // 等同于 limit 0,3 # 下标 0,1,2 SELECT * FROM student LIMIT 3,3; // 等同于 limit 3,3 # 下标 3,4,5 SELECT * FROM student LIMIT 6,3; // 等同于 limit 6,3 # 下标 6,7,8
1、查询出年级最大的10个学生 select * from student order by age desc,id asc limit 10; 2、从所有学生中,查询年级最大的下标从10-19的学生出来。 select id,name,age from student order by age desc,id asc limit 10,10;
limit 主要用于在项目开发中的分页功能实现。
3.DML数据操作语句
3.1 添加数据(insert)
添加一名记录
INSERT INTO 表名 (字段1,字段2,字段3,....) VALUES (字段值1,字段值2,字段值3,....); # 也可以省略不写字段名,但是数据的数据项必须和表结构的字段数量保持一致,查询表结构使用 desc 表名; INSERT INTO 表名 VALUES (字段值1,字段值2,字段值3,....);
insert into student (id,name,sex,class,age,description) values (101,'刘德华',1,508,17,'给我一杯忘情水~'); # 上面的字段,如果是全部字段,那么字段这一块内容可以省略不写。 # 例如,我们再次添加一个学生,如果省略了字段名,那么填写数据的数据项必须和表结构的字段数量保持一致。 insert into student values (102,'张学友',1,508,17,'爱就像头饿狼~',0,0,0); 添加多名学生 INSERT INTO student(name,sex,class,age,description) VALUES ('周润发',1,508,17,'5个A~'),('周杰伦',1,508,17,'给我一首歌的时间~'); 注: 自动增长跟有默认值的字段可以不写。
3.2 更新数据(update)
UPDATE 表名 SET 字段1=字段值1,字段2=字段值2 WHERE 条件 # 更新操作会影响数据的不可逆操作,所以更新的时候,一定要谨慎,添加条件。如果没有条件, # 或者条件的判断结果一直是True,则整个表所有的记录都会被更新。
修改学生的姓名,年龄 UPDATE student set age=8 where id=104;
3.3 删除数据(delete)
DELETE FROM 表名 WHERE 条件
删除一个学生 DELETE FROM student WHERE id=104; 注: 修改跟删除数据都要记得加条件。
DELETE FROM table 删除整个表的内容[没有条件则表示删除整个表所有数据],尽量少用
4.DDL数据定义语言
4.1 创建表
CREATE TABLE 表名( 字段名1 数据类型 约束规则, 字段名2 数据类型, 字段名3 数据类型, ..... 字段名N 数据类型, PRIMARY KEY(一个 或 多个 字段名) ); # 上面语句中,最后一个小句子后面不能有英文逗号出现,前面的小句子必须加上英文逗号。
创建表
(1.)学生表
mysql> create table student( -> id int unsigned auto_increment not null, # 字段名 整型 无符号 自动增长 不能是空, -> name char(10), # 字段名 字符串(长度:10) -> sex int default 1, # 字段名 整型 默认值为 1, -> class int, # 字段名 整型 -> age int, # 字段名 整型 -> description text, # 字段名 文本[可以填写65535个字符] -> primary key(id) # 设置主键(id) 每个表必须都有主键 -> ) engine=innodb charset=utf8; # 表引擎=innodb 编码=utf8;[后面学习,先用] Query OK, 0 rows affected (0.02 sec) # 出现这句话,表示创建表成功 mysql> desc student; # 显示表结构 +-------------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | name | char(10) | YES | | NULL | | | sex | int(11) | YES | | 1 | | | class | int(11) | YES | | NULL | | | age | int(11) | YES | | NULL | | | description | text | YES | | NULL | | +-------------+------------------+------+-----+---------+----------------+ 6 rows in set (0.00 sec)
(2.)班级表
create table classes( id int unsigned auto_increment primary key not null, name varchar(10) );
(3.)课程表
create table `course`( id int unsigned not null auto_increment, course char(20) not null, lecturer int unsigned, address int unsigned, primary key(id) ) engine=innodb charset=utf8;
补充:显示建表语句
show create table 表名 \G;
mysql> show create table course \G; *************************** 1. row *************************** Table: course Create Table: CREATE TABLE `course` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `course` char(20) NOT NULL, `lecturer` int(10) unsigned DEFAULT NULL, `address` int(10) unsigned DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec)
其他语句:
修改表-添加字段
alter table 表名 add 列名 类型; 例: alter table students add birthday datetime;
修改表-修改字段:重命名版
alter table 表名 change 原名 新名 类型及约束; 例: alter table students change birthday birth datetime not null;
修改表-修改字段:不重命名版
alter table 表名 modify 列名 类型及约束; 例: alter table students modify birth date not null;
修改表-删除字段
alter table 表名 drop 列名; 例: alter table students drop birthday;
删除表
drop table 表名; 例: drop table students;
4.2 约束条件
4.2.1字段名
定义字段名,表名、数据库名、规范:
在64个字符以内,建议简短,如果不够清晰,可以使用前缀。
不能是关键字或者保留字
采用变量命名方式[ 由字母、数字、下划线组成,不能以数字开头 ]
4.2.2数据类型
了解数据的数据类型可以通过以下语句来查看和使用帮助:
mysql> ? 查询关键词 # 如果,我们希望了解关于int的可以填值范围 mysql> ? int
使用数据类型的原则:够用就行,尽量使用取值范围小的,而不用大的,这样可以更多的节省存储空间
常用数据类型如下:
整数:bit[0-64],tinyint, smallint, int
小数:decimal
字符串:varchar,char
日期时间: date, time, datetime
枚举类型(enum) 多选一,例如性别字段 enum('男','女'),后面添加数据时,值得填写只能'男'和'女'这两项,其他值填写进来会报错。
特别说明的类型如下:
decimal表示浮点数,如decimal(5,2)表示共存5位数,小数占2位
char表示固定长度的字符串,如char(3),如果填充'ab'时会补一个空格为
'ab '
varchar表示可变长度的字符串,如varchar(3),填充'ab'时就会存储'ab'
字符串text表示存储大文本,当字符大于4000时推荐使用
对于图片、音频、视频等文件,不存储在数据库中,而是上传到某个文件管理服务器上,然后在表中存储这个文件的保存路径
更全的数据类型可以参考http://blog.csdn.net/anxpp/article/details/51284106
(1.)数值类型-常用
(2.)小数类型
开发中,一般QQ号或者手机号都是使用字符串来保存的
(3.)字符串
在5.5版本的mysql以后,varchar类型可以存储的数据,可以达到65535个字符。
(4.)日期时间类型
DATETIME 和 TIMESTAMP,很多时候,我们会使用程序中的时间戳来代替,后面在数据库中保存时设置字段的类型是数值型,这样的话,可以节省存储空间,同时还可以提高数据的读取速度。
4.2.3 约束规则
主键primary key:在表中区分每一行数据的唯一性的标志服,数据在物理上存储的顺序
非空not null:此字段不允许填写空值,如果允许填写空值,则直接不填not null
惟一unique:此字段的值不允许重复
默认default:当不填写此值时会使用默认值,如果填写时以填写为准
外键 foreign key:用于连接两个表的关系,对关系字段进行约束,当为关系字段填写值时,会到关联的表中查询时是否此值是否存在,如果存在则填写成功,如果不存在则填写失败并抛出异常
说明:虽然外键约束可以保证数据的有效性,但是在进行数据的crud(create增加、update修改、delete删除、read查询)时,都会降低数据库的性能,所以不推荐使用,那么数据的有效性怎么保证呢?答:可以在python的逻辑层进行判断控制[用代码控制]
ok