关系型数据库管理系统,接下来的一些内容

作者: www.9159.com  发布:2019-11-09

    此前在校学习期间,只是知道数据库很重要,但是并未主动去学习了解。现在的学习过程中学了一遍mysql,就简单的做一个总结吧。

sql常用语法总结

一、数据库的创建

接下来的一些内容,我们需要提前学一些简单的sql语句,方便大家理解接下来的知识。

    首先记住三个概念:

命令行使用mysql

进入mysql :

    mysql -uroot -p

显示所有数据库:

    show databases;

新建一个数据库命令:

    create database 数据库名称;

删除一个数据库命令:

     drop database 数据库名称;

使用某个数据库命令:

     use 数据库名称;

查看表命令:

    show tables;

建立一个新表:

     create table 表名 (字段参数);

或 

    create table if not exists 表名(字段参数);

删除一个旧表:

    drop table 表名;

或 

    drop table if exists 表名;

退出:

    exit

查看表的结构:

    describe 表名;

导入.sql文件命令(例如D:/mysql.sql)

    source d:/mysql.sql;

  或

      /. d:/mysql.sql;


  create database database_name  DEFAULT CHARACTER SET utf8;  //创建一个数据库  

DDL—数据定义语言(Create,Alter,Drop,DECLARE)
DML—数据操纵语言(Select,Delete,Update,Insert)
DCL—数据控制语言(GRANT,REVOKE,COMMIT,ROLLBACK)
DQL---数据查询语言(select)

    1.数据库(Database)是按照数据结构来组织、存储和管理数据的建立在计算机存储设备上的仓库。   

常用语法

#插入数据

insert into 表名('字段1','字段2','字段3','字段4',...) values('值1','值2','值3',...);

insert into 表名 values('值1','值2','值3',...);

例子:

    insert into c (id,name,sex,age,sno) values (null,'涛哥','男',68,1);

    insert into student values(null,'aa','男','1988-10-2','......');

#查询表中的数据

select * from student;

select id,name from student;

#修改某一条数据

update 表名 set 字段=值 where 条件;

#删除数据

delete from 表名 where条件;

# and 且

select * from student where date>'1988-1-2' and date<'1988-12-1';

# or 或

select * from student where date<'1988-11-2' or date>'1988-12-1';

#between

select * from student where date between '1988-1-2' and '1988-12-1';

#in 查询制定集合内的数据

select * from student where id in (1,3,5);

#排序order by , asc 升序  desc 降序 

select * from student order by id asc;

#分组查询group by   #聚合函数

select max(id),name,sex from student group by sex;

select min(date) from student;

select avg(id) as '求平均' from student;

select count(*) from student;  #统计表中总数

select count(sex) from student;  #统计表中性别总数  若有一条数据中sex为空的话,就不予以统计~

select sum(id) from student;

#查询第i条以后到第j条的数据(不包括第i条)

select * from student limit 2,5;  #显示3-5条数据

#建表

create table c(

    id int primary key auto_increment,  #主键

    name varchar(10) not null,  #非空

    sex varchar(50) , 

    age int unsigned, #不能为负值(如为负值 则默认为0)

    sno int unique    #不可重复

);

#删除表

drop table c;

#修改数据

update 表名 set 字段名=值 where 条件;

例子:

    update c set name='名字',age=21,sex='女' where id=2

#修改表的名字

alter table 表名_name rename to 新表名

#向表中增加一个字段(列)

alter table 表名 add 字段名 类型;

例子:

    alter table test add  columnname varchar(20);

    alter table test add(columnname char(10));

#修改表中某个字段的名字

alter table 表名 change 字段名 新字段名 类型;  #修改一个表的字段名

例子:

    alter table test change name uname varchar(50);

#表position 修改列test 默认值

alter table position alter test set default 'system';

#表position 去掉test 默认值

alter table position alter test drop default;

#表position 去掉列test

alter table position drop column test;

#表depart_pos 删除主键

alter table depart_pos drop primary key;

#表depart_pos 增加主键department_id,position_id

alter table depart_pos add primary key PK_depart_pos(department_id,position_id);

#用文本方式将数据装入数据库表中(例如D:/mysql.txt)

load data local infile "D:/mysql.txt" into table MYTABLE;

  drop database database_name;  //删除数据库

DML(data manipulation language):
它们是SELECT、UPDATE、INSERT、DELETE,就象它的名字一样,这4条命令是用来对数据库里的数据进行操作的语言
DDL(data definition language):
DDL比DML要多,主要的命令有CREATE、ALTER、DROP等,DDL主要是用在定义或改变表(TABLE)的结构,数据类型,表之间的链接和约束等工作上,他们大多在建立表时使用
DCL(Data Control Language):
是数据库控制功能。是用来设置或更改数据库用户或角色权限的语句,包括(grant,deny,revoke等)语句。在默认状态下,只有sysadmin,dbcreator,db_owner或db_securityadmin等人员才有权力执行DCL

    2.SQL :结构化查询语言(Structured Query Language)

  1.1 DOS常用命令


    3.MySQL:关系型数据库管理系统

    1.1.1 数据库命令

接下来我们逐步学习SQL语句,在学习之前我们先注意一下SQL语句的注意事项.
1.每条SQL语句结束时要以;做为结束符.(除了use命令)
2.SQL语句的关键字不区分大小写(除了库名字和表名字)
3.在查询数据库信息或者表信息时,可以以G做为结束符,表示以文本模式输出
4.当你不需要一条语句输出的结果以c结束,不可以使用ctrl+c,否则登出mysql.
5.我们可以在命令行执行sql语句,要通过mysql -e参数
mysql -e "show databases /G" 显示到shell上
6.如果需要获取SQL语句的帮助可以用help命令
如:help create
如果需要进一步获取帮助,可以继续使用help命令
如:help create database

    database中存储着各种数据,sql语句用于从database中找出我们需要的数据,mysql是一种应用软件,通过语句对database进行操作。

      mysqld install      // 安装MySQL数据库服务


    MySQL我使用的是5.6版本,通过管理员身份打开cmd后,启用mysql服务为:net start mysql56,关闭服务为:net stop mysql56。登录:mysql -h localhost -u root -p     回车后输入密码:123456(用户名和密码在安装时进行设置)

                net start mysql 或 net stop mysql     // 启动或停止MySQL服务

1.DDL数据库定义语句
建立数据库以及查询
create database db;
create database db CHARACTER SET = 'utf8'
show databases;
show create database db;
alter database db CHARACTER SET = 'latin1';
修改库名只需要改数据库目录名称
drop database db;


      mysql -u username -p  // 登录MySQL管理系统  

建立表以及查询
use db
类型
create table t1(id int(6),name char(10));
create table t1(id int(6),name varchar(10));
日期时间类型
date类型
create table t4(aa date);
insert into t4 values('2010-04-01'),(20100401);
select * from t4;
+------------+
| aa |
+------------+
| 2010-04-01 |
| 2010-04-01 |
+------------+
time类型
create table t5(showttime time);
insert into t5 values ('11:11:11'),('11:11'),('111111');
select * from t5;
+-----------+
| showttime |
+-----------+
| 11:11:11 |
| 11:11:00 |
| 11:11:11 |
+-----------+
出现的问题
create table t6 (a_data data,a_time time);
insert into t6 values('1978-4-6',123412),(651212,'3:5:6');
select * from t6;
+------------+----------+
| a_date | a_time |
+------------+----------+
| 1978-04-06 | 12:34:12 |
| 2065-12-12 | 03:05:06 |
+------------+----------+
年份的范围00-69为2000-2069&&70-99为1970-1999
year类型
create table t7 (year year);
insert into t7 values(2003),(04),(53),(89),(90);
select * from t7;
+------+
| year |
+------+
| 2003 |
| 2004 |
| 2053 |
| 1989 |
| 1990 |
+------+

    下面将会从四个方面进行总结:

      use database_name;  // 引用数据库

datetime和timestamp类型
timestamp 时间戳类型,输入null,显示当前时间。datetime则会显示null
datetime 如果name改变,时间不改变。例如:QQ的申请时间
timestamp 如果name改变,时间会改变成现在时间。例如:提示上次登录时间
values(now()) now()函数,当前时间函数,申请帐号时可以触发。

    1.数据定义语言(DDL)

      show databases;    // 显示所有数据库

create table t8(f_datetime datetime,f_timestamp timestamp);
insert into t8 values('1999-11-11 11:11:11','2002-11-111:11:11');
insert into t8 values(19991111111111,20021111111111);
insert into t8 values(now(),null);
select * from t8;
+---------------------+---------------------+
| f_datetime | f_timestamp |
+---------------------+---------------------+
| 1999-11-11 11:11:11 | 2002-11-11 11:11:11 |
| 1999-11-11 11:11:11 | 2002-11-11 11:11:11 |
| 2012-03-21 21:05:21 | 2012-03-21 21:05:21 |
+---------------------+---------------------+
ENUM和SET类型
create table t10(sex ENUM('M','F'));
insert into t10 values('M'),('m'),('F'),('aa'),(null);
select * from t10;
+------+
| sex |
+------+
| M |
| M |
| F |
| |
| NULL |
+------+
create table t11 (type SET('a','b','c','d','e'));
insert into t11 values(a);
insert into t11 values('b,c');
insert into t11 values('J');
select * from t11;
+------+
| type |
+------+
| a |
| b,c |
| |
+------+
insert into t11 values('b,c,e,f');既有合法字符又有非法字符
select * from t11;
+-------+
| type |
+-------+
| a |
| b,c |
| |
| b,c,e |
+-------+

    2.数据操作语言(DML)

      source path(.sql文件路径)  //导入数据库文件,生成数据库,即根据sql脚本生成数据库;例如:source F:mysql.sql

练习:
创建表test id name money sex hobby email qq shenfezheng jointime
create table test(id tinyint,name char(10),money float(10,2),sex enum('M','F'),hobby set('a','b','c'),email varchar(50),qq char(15),shenfenzheng char(18),jointime datetime);

    3.数据查询语言(DQL)

    1.1.2 数据表操作命令

mysql> rename table test to newtest;
mysql> alter table test change id uid smallint;
mysql> alter table test modify id smallint;

    4.函数

      show tables;         // 显示某数据库中所有表


    本篇小结大部分是语句格式,如果需要代码实现的截图,以及部分额外知识点标注,可以下载安装xmind软件后,下载云盘里的思维导图进行查看。

      desc table _name;   //显示表的字段信息

修饰符(约束)
无符号 unsigned
用0补齐 zerofill

链接: 密码:qrie

      show create table table_name;  //显示表的创建信息

desc t11;
+-------+--------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------------------+------+-----+---------+-------+
| type | set('a','b','c','d','e') | YES | | NULL | |
+-------+--------------------------+------+-----+---------+-------+

ximd内容如下:

   1.2 查询关系运算符

not null约束
create table t12 (id int,sex enum('M','W') NOT NULL );
desc t12;
+-------+---------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------------------+------+-----+---------+-------+
| id | int(10) unsigned zerofill | YES | | NULL | |
| sex | enum('M','W') | YES | | NULL | |
+-------+---------------------------+------+-----+---------+-------+
insert into t12(id) values(1);
Query OK, 1 row affected (0.00 sec)
select * from t12;
+---+-----+
|id | sex |
+---+-----+
| 1 | NULL|
+---+-----+

图片 1

  • =、 !=、 <>、<、<=、>、>=;
  • between   ·····  and ········;
  • in(set) ; //set是集合
  • is null;is not null
  • and ;
  • or;
  • not;
  • like  '%三';          //%表示任意多个字符,_表示任意单个字符

DEFAULT约束
create table t13 (id int ,sex enum('M','W') NOT NULL default 'M' );
desc t13;
+-------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| sex | enum('M','W') | YES | | M | |
+-------+---------------+------+-----+---------+-------+
insert into t13(id) values(3);
select * from t13;
+------+------+
| id | sex |
+------+------+
| 2 | M |
| 3 | M |
+------+------+

 

  1.3 内连接和外连接

AUTO_INCREMENT修饰符自动增长只适用于int字段 一般用于主键 一个表只能有一个
create table t14(id int auto_increment primary key,name char(10) not ll);
desc t14
+-------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | char(10) | NO | | NULL | |
+-------+----------+------+-----+---------+----------------+
insert into t14(name) values(zhb);
insert into t14(name) values('haha');
select * from t14;
+----+------+
| id | name |
+----+------+
| 1 | zhb |
| 2 | haha |
+----+------+

数据定义语言(DDL):

    >内连接:

索引添加删除
show index from t20G
show create table t20;

1.建表:

create table 表名(                                                    create table test(

          列名称1 数据类型 ,                                                           id int,

          列名称2 数据类型,                                                            name char(10),

          .........                                                                                    ......

          列名称n 数据类型);                                                       birthday date);

常用的数据类型:整数(int,tinyint,smallint),小数(float,decimal),字符串(char,varchar),时间(date,time)

        select  t1.id,t1.name,t1.age,t2.score  from table1_name t1  join table2_name t2 on t1.id=t2.id

drop index index_name on table_name;

2.约束:

primary key(主键):约束唯一标识数据库表中的每条记录

foreign key(外键):foreign key就是表与表之间的某种约定的关系,从一个表指向另一个表

unique:约束用于限制加入表的数据的类型

建表时可在数据类型后添加:

create table test(id int primary key);

create table test(id int,primary key(id);

或者建表时未添加,后面需要时再添加:

alter table test add primary key(id);

删除主键:alter table test drop primary key;

unique的用法与primary key 相同。

外键的用法:

create table t1(id int primary key,name char(10));

create table t2(id int primary key,pri_id int,name char(10),

constraint i foreign key(pri_id) references t1(id));

意思为:把t2中的pri_id 作为外键,指向t1中的主键id

删除外键:constraint i,把外键命名成i,方便了我们删除外键

alter table t2 drop foreign key i; 

        或:

create index index_name on table_name(列名);
alter table table_name add index(列名);

3.字段属性

1.unsigned(无符号型),只能用在数值型字段

2.zerofill(自动补零),只能用在数值型字段,前导零,同时该字段自动式UNSIGNED

3.AUTO_INCREMENT(自动增长),寄生于主键

4.NOT NULL:强制约束列不守NULL值,即不添加数值就无法插入数据

5.缺省值(default):给数据一个默认值(系统默认值是NULL),不能与auto_increment同时用于一个字段上

写法均为:create table test(id int 字段属性);

 


        select  t1.name,t1.age,t2.score  from table1_name t1 , table2_name t2 where t1.id=t2.id

索引建立
create table t15(id int not null ,name char(10),index(id));
desc t15;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int(11) | NO | MUL | NULL | |
| name | char(10) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
向已有表添加索引
create table t16(id int not null ,name char(10));
desc t16;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int(11) | NO | | NULL | |
| name | char(10) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
create index id on t16 (id);
alter table t17 add index(id);

DML(数据操作语言):

    >外连接:

desc t16;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int(11) | NO | MUL | NULL | |
| name | char(10) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
删除索引
drop index id on t16;
desc t16;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int(11) | NO | | NULL | |
| name | char(10) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
查询索引
show index from t16;

1.索引:

创建:create index 索引名 on 表名(字段);            create index i on test(id);

删除:drop index 索引名;                                        drop index i;

        select  t1.id,t1.name,t1.age,t2.score  from table1_name t1  left join table2_name t2 on t1.id=t2.id    //左连接

UNIQUE索引(允许空值,null != null)

2.对数据操作:

        或:

create unique index id on table_name(id);

insert(插入):

插入单独数据:insert into 表名 字段1、字段2... values(值1、值2...);

插入默认数据:insert inro 表名 values(值1、值2...);

insert into test(id,name) values(008,'周星星');

        select  t1.id,t1.name,t1.age,t2.score  from table1_name t1 right join table2_name t2 on t1.id=t2.id    //右连接

create table t17(id int ,name char(10),unique(id));
desc t17;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int(11) | YES | UNI | NULL | |
| name | char(10) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
insert into t17 values(null,'zhb');
select * from t17;
+------+------+
| id | name |
+------+------+
| NULL | zhb |
+------+------+

update(修改、更新):修改(更新)数据:update 表名 set 字段=新值 where 列名称=某值

update test set name='詹姆斯' where id=008;

修改多条数据:

update test set name=case id

when 001 then 'qwe'

when 002 then 'asd'

end

where id in(001,002)

        select  t1.id,t1.name,t1.age,t2.score  from table1_name t1 full join table2_name t2 on t1.id=t2.id    //全连接,MySQL不支持!

PRIMARY KEY(主键约束 值唯一 uniq和not null的结合 可作用多列 不可两列同时相同,单列重复可以)

replace(批量更新数据)(也可用于数据添加):replace into 表名(字段1、字段2...) values(值1、值2...)

replace into test(id,name) values(002,'777');

未添加的数据会默认为null

   1.4 数据库备份和恢复

alter table t22 drop primary key;
alter table t22 add primary key(id);

批量更新(通过update):

insert into test(id,name) values(001,'i am 001'),(002,'i am 002')

on duplicate key update

id=values(id),name=values(name);

    a. 备份  

create table t18(id int,name char(10),primary key(id));
desc t18;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int(11) | NO | PRI | 0 | |
| name | char(10) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
insert into t18 values(1,'zhb');
select * from t18;
+----+------+
| id | name |
+----+------+
| 1 | zhb |
+----+------+
insert into t18 values(1,'zhb');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY' 不允许重复

delete删除数据(一条):delete from 表名 where 条件

delete from test where id=001;

      mysqldump -u root -p123 database_name>d:myDBdemo.sql 

删除主键
mysql> alter table t19 drop primary key;
向已有表添加主键
mysql> alter table t19 add primary key(id);

 

    b. 恢复

在多个列上建立主键,不可多次添加主键
create table t19(id int,name char(10),primary key(id,name));
desc t19;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int(11) | NO | PRI | 0 | |
| name | char(10) | NO | PRI | | |
+-------+----------+------+-----+---------+-------+
insert into t19 values(1,'zhb');
insert into t19 values(1,'zorro');
select * from t19;
+----+-------+
| id | name |
+----+-------+
| 1 | zhb |
| 1 | zorro |
+----+-------+

3.对表操作

       mysql -u root -p123 database_name<d:myDBdemo.sql

外键myisam引擎不支持只能用innodb引擎
create table dpmnt(id int not null,name char(10) not null,primary key(id)) type = INNODB;
desc dpmnt;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | char(10) | NO | | NULL | |
+-------+----------+------+-----+---------+-------+
建立外键
create table emp (id int not null, name char(10) not null,fk_dpmnt int not null ,primary key(id),index (fk_dpmnt),foreign key (fk_dpmnt) references dpmnt(id)) type=innodb;
desc emp;
+----------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | char(10) | NO | | NULL | |
| fk_dpmnt | int(11) | NO | MUL | NULL | |
+----------+----------+------+-----+---------+-------+
insert into dpmnt values(1,hr);
insert into dpmnt values(2,'yw');
insert into emp values(10,'zhb',3);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (db.emp, CONSTRAINT emp_ibfk_1 FOREIGN KEY (fk_dpmnt) REFERENCES dpmnt (id))

修改表名:alter table 旧表名 rename as 新表名;

alter table test rename as father;

      或:

fk_dpmnt字段的数据必须得是dpmnt表里有的不然报错...

修改字段的数据类型:alter table 表名 modify 字段名 数据类型

alter table test modify id char(10);

      进入需要还原到的目的数据库;

即使表存在外键约束,MySQL还允许我们删除表,并且不会产生错误。这是删除外键的方法。
alter table emp drop foreign key emp_ibfk_1;
删除外键

修改字段名:alter table 表名 change 字段名 新字段名 数据类型; 

alter table test change name address char(50);

      source d:myDBdemo.sql

全文本索引
mysql> desc t22;
create table t22(id int,name char(10),fulltext(name));
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | char(10) | YES | MUL | NULL | |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.01 sec)

增加字段:alter table 表名 add 字段名1 数据类型

alter table test add address char(30);

 

练习:
创建表test id name money sex hobby email qq shenfezheng jointime
create table test(id tinyint,name char(10),money float(10,2),sex enum('M','F'),hobby set('a','b','c'),email varchar(50),qq char(15),shenfenzheng char(18),jointime datetime);

删除字段:alter table 表名 drop 字段名;

alter table test drop address;

 


二、基本表的创建

1.添加约束
create table test1 ( id int primary key auto_increment, name char(20) not null, money float(10,2) not null, sex enum('M','F') not null default 'M', hobby set('a','b','c') not null default 'a', qq char(15) unique, email char(50), jointime datetime,index(email));
2.删除掉所有的约束
alter table test1 modify id int; 删除auto_increment
alter table test1 drop primary key; 删除primary key
alter table test1 modify id int; 删除 not null

DQL(数据查询语句insert):

  2.1 创建表

alter table test1 modify name char(10);
alter table test1 modify money float(10,2);
alter table test1 modify sex enum('M','F');
alter table test1 modify hobby set('a','b','c');
drop index qq on test1;
drop index email on test1;

1.交叉查询:select 表1.字段,表2.字段 from 表1,表2;

   数据类型:int,char,varchar,text(字符串类型),blob(字节类型);double(5,2)表示最多5位,其中2位小数。

3.在添加约束
alter table test1 add primary key(id);
alter table test1 modify id int auto_increment;

多表联合查询:select 表1.字段,表2.字段 from 表1,表2 where 表1.id=表2.id;

select * from t1,t2 where t1.id=t2.id;

    create table 表名

show tables;
show create table t1;

2.查询不重复的数据:select distinct 字段 from 表名;

select distinct id from test;

    (

ALTER table t2 RENAME t1;
alter table t2 MODIFY a tinyint not null,CHANGE b c char(20);
mysql> alter table test2 modify id tinyint,change name testname char(30); 重命名时名字不能是关键字,rename等
mysql> alter table tt2 change id did int;

3.取别名alias:select * from 表名 as 别名;

自连接:select b.* from shop as a,shop as b where a.name='包子' and a.price

把shop表取别名为a,b,通过a和b进行自己数据的对比

      字段名1 数据类型 [not null] [默认值],

create table members ( id int(11),name char(10),tel char(15));
alter table members ADD qq int;
alter table members drop qq;
alter table members add qq int after name ;
alter table members add phone first;
alter table test1 modify qq char(15) after id;

4.limit(偏移量):

查询前n条数据:select *from 表名 limit n;

查询前n条数据后的i条数据:select *from 表名 limit n,i;

      字段名2 数据类型,

drop table t1;

5.in(在where后规定多个值):select 字段 from 表名 where 字段 in(值1,值2);

select * from test where id in(1,2,3);

      字段名3 数据类型

2.DML 数据库操纵语句
insert
mysql> INSERT INTO members ( member_id,fname,lname,tel,email) VALUES ( NULL,'john','Doe','1234567','jdoe@163.com');
mysql> INSERT INTO members VALUES ( NULL,'kyo','oyk','7654321','kyo@163.com');
mysql> INSERT INTO members (fname,lname,email,tel,member_id) VALUES ('bob','kk','bob@163.com','22334455',NULL);

6.like(用于where子句中搜索列中的指定模式):select 字段 from 表名 where 字段 like 表达式;

例如搜索王姓青年:

select *from test where name like'王%';

%:表示0~多个字符

_:表示一个字符

    );

update
mysql> UPDATE members SET email = 'kyo@163.com' WHERE member_id = 3;
mysql> UPDATE members SET email = 'hoho@163.com',lname = 'ho' WHERE member_id = 2;

7.order by(排序):select 字段 from 表名 order by 字段(排序方式) [desc](使用倒序排列)

select * from test order by id desc;

表示通过id从大到小进行排序显示

  例如:

delete
mysql> DELETE FROM members;
mysql> DELETE FROM members WHERE member_id = 1;

8.join连接:

inner jor(内连接),left join(左连接),right join(右连接)

full join(全连接)(mysql不支持全连接)

格式都相同:select 字段 from 表1 inner/left/right join 表2 on 表1.字段=表2.字段;

    create table student

sql语句使用

9.union(联合查询):select *from 表1 union select *from 表2;

 


    (

连接数据库
mysql -u root -p123 -h localhost
查看服务器状态
show staus;
显示所有库名
show databases;
使用数据库
use db;
显示当前数据库中的所有表
show tables;
查看表结构
desc tables;

函数:

      stu_num int auto_increment,  //自增量

select查询语句(默认乱序,可以指定多列显示顺序)

1.合集函数:操作面向一系列的值,并返回一个单一的值

写法都相同:

查询某列的平均值:select avg(字段) from 表名;

返回某列的行数:select count(字段) from 表名;

查询某列最大值:select max(字段) from 表名;

查询某列最小值:select min(字段) from 表名;

返回某列总和:select sum(字段) from 表名;

分组显示总和:select sum(字段) from 表名 group by 字段;

      stu_name char(10) not null,

select name from tables; 从表中查询指定列
select id,name,sal from tables; 指定多个列名
select * from tables;查询所有的列

2.标量函数:操作面向某个单一的值,并返回基于输入值的一个单一的值

写法也都相同:

ucase(把字段的值转换为大写):select ucase(字段) from 表名;

lcase(把字段的值转换为小写):select lcase(字段) from 表名;

mid(提取字符):mid(字段,起始,结束):

select mid(name,2,3) from test;

表示从name列的第二个数据开始,每个数据只显示3位

len(返回文本长度):select length(字段) from 表名;

round(把数值四舍五入,并保留相应小数位):select round(字段,数字) from 表名;

now()(查询当前时间):select now() from 表名;(有几个数据就出现几个)

 


    此文章虽然是自己的学习小结,而且还是身为初学者的我写的,但也希望更多的朋友能看到我的文章,如果有不足之处或疑问,欢迎到留言区留言。

      stu_gender char(2),

select distinct id from tables; 去掉重复行
select name from tables limit 5; 显示前5行
select name from tables limit 5,5;显示从第5行开始的后5行即5-10行

      stu_score int,

select name from db.t1;没有使用use进入db库时查询db库的t1表
select t1.name from db.t1; 指定库的表 指定表的列

      primary key(stu_num),  //主键

显示mysql中第一个用户名字?

      foreign key(stu_name) references anotherTable_name(stu_name)  //外键

在shell命令行显示用户的名字和密码
显示mysql中的前3个用户

    );


  2.2 常用约束

修改root帐号密码为456

      主键约束: primary key(字段名);  //或在定义字段时直接在后面追加 primary key

[root@uplooking 桌面]# /usr/local/mysql/bin/mysqld_safe --user=mysql --skip-grant-tables &
mysql> update mysql.user set password=password('123456') where user='root' and host='localhost';

    外键约束: foreign key(字段名) references 表名(字段名);

[root@uplooking 桌面]# killall mysqld
[root@uplooking 桌面]# service mysqld restart
[root@uplooking 桌面]# /usr/local/mysql/bin/mysql -uroot -p123456

    非空约束: 字段名 数据类型 not null

排序检索语句
select id,name from t1 order by id; 按id排序
select id,name from t1 order by id,name;先按id排序id相同在按name排序
select id,name from t1 order by id desc; 按id反向排序
select id,name from t1 order by id desc,name; 先按id反向排序再按名字排序
select id,name,sal from t1 order by sal desc limit 1;查找工资最高的人

    唯一性约束: 字段名 数据类型 unique

where子句
select id,name,sal from t1 where name='tom'; 查找tom的信息
where 子句的操作符
= 等于
<> 不等于
!= 不等于
< 小于
<= 小于等于

    默认约束: 字段名 数据类型 default 默认值

大于
= 大于等于
between 5 and 10 在两个值之间
is null 空值
is not null 非空值

    自增量: 字段名 数据类型 auto_increment  //默认初始值为1,增量为1;如需从100开始,可以设置为auto_increment=100

select id,name from t1 where id>5 and name='tom'; and操作符表示两个条件都要满足 与操作
select id,name from t1 where id=10 or name='tom';or操作符表示满足任意条件 或操作

  2.3 修改表

select id,name,sal from t1 where id=10 or id=20 and sal > 5000; id为10的 或者id为20并且薪水大于5000的;and优先执行
select id,name,sal from t1 where (id=10 or id=20) and sal > 5000;id为10或者20 并且薪水大于5000的

    alter table 旧表名 rename 新表名  //修改表名

select id,name,sal from t1 where id in (10,20,30);id在 10 20 30 中的记录
这条语句用or可以做到相同的结果,那in的好处
1.in的语法更加直观
2.in的计算次序更容易管理(操作符少)
3.in 一般比or执行的更快
4.in的最大优点可以包含其他子句 or不行

    alter table 表名 modify 字段名 数据类型;  //修改字段数据类型

取最高薪水的人
select * from test2 where money=(select money from test2 order by money desc limit 1);
select * from test where id=(select id from test order by id desc limit 1); id号最高的人

    alter table 表名 change 旧字段名 新字段名 新数据类型  //修改字段名

复制表结构
create table test1 as select * from test where 1>2; 只复制表结构,不复制数据
create table test2 as select * from test; 完全复制,包括数据

    alter table 表名 add 新字段名 数据类型  //添加字段

select id,name,sal from t1 where id not in (10,20,30); id不在10 20 30的记录 not找到不匹配的记录更简单

    alter table 表名 drop 字段名  //删除字段

通配符%匹配多个字符_匹配一个字符
select id,name from t1 where name like 'jer%';模糊查询名字为jer开头的记录
select id,name from t1 where name like 'j%y'; 匹配j开头y结尾的
select id,name from t1 where name like '_err%' 匹配e前边有一个字符的记录

    alter table 表名 AUTO_INCREMENT=100  //设置自增量初始值为100

原则:
尽量少使用通配符,如果其他操作符能做到就不要使用通配符
在确实需要通配符时,尽量不要使用%erry 这种用法搜索起来会更慢
至于使用位置,使用错了得不到想要的结果

   2.4 删除表

正则表达式的使用regexp
select id,name from t1 where name regexp 'je*';调用正则匹配je开头
select id,name from t1 where name regexp 'y$';

    drop table [if exist] 表名;

语句的拼接
select concat(id ,'(',name,')') from t1 将id和name 拼接为1列 oracle用||
select concat(id ,'(',name,')') all_name from t1 别名也可以使用as
select sal*12 from t1 计算全年薪水 + - * /

三、CRUD增删改查操作

函数使用
select upper(name) as new_name from t1; 将名字转换为大写 lower 小写
group by 分组 必须在where之后 分组前过滤 having 可以分组后过滤

3.1 数据插入-Insert

sum max min avg count year month day hour minute second

  1. 插入单条记录

select count() from emp group by sex;
select count(
) as num emp group by id having num > 3;
where 条件必须在group by前面,having可以在group by 后面

    insert into 表名(字段名,字段名,字段名)  //当插入所有字段时,字段名可以省略

1.建立员工档案表
要求字段:员工员工编号,员工姓名,性别,工资,email,入职时间,部门。
create table emp(uid int primary key auto_increment,name char(10) not null,sex enum('M','F') not null default 'M',money float(10,2),email varchar(50),jointime datetime,dname char(20),unique(email));

    values('值1','值2','值3');

2.合理选择数据类型及字段修饰符,要求有NOT NULL,auto_increment, primary key等。

  2. 插入多条记录

3.查看表的结构
desc emp;

    insert into 表名(字段名,字段名,字段名)

4.新增qq和tel字段,要求tel字段位于email前,要求入职时间是最后一个字段
alter table emp add tel char(11) after money;
alter table emp add qq char(15);
alter table emp modify jointime datetime after qq;

    values('值1','值2','值3'),

5.把email字段修改成mailbox
alter table emp change email mailbox varchar(50)

      ('值1','值2','值3'),

6.向表里添加10条记录

      ('值1','值2','值3');

7.修改其中两条记录的tel和mailbox
update emp set tel=18611112222 where name='lici';
update emp set mailbox='lici@lici.com' where name='lici';

  3. 插入一个查询结果

8.查看所添加记录

    insert into 表名()

9.查看姓名和入职时间记录

    select xxx  //返回值个数应和需插入字段个数相同

10.查询入职时间在2003年以前的
select name,jointime from emp where year(jointime)<2013;

    from xxx

11.查询工资最高和最低的员工姓名
select name,money from emp where money=(select min(money) from emp);
select name,money from emp where money=(select max(money) from emp);

 

12.查询平均工资

3.2 数据删除-Delete

13.统计男员工人数、女员工人数
select count(*) from emp group by sex;

  delete from student(表名)

14.按照入职时间先后进行排序,并显示前5位员工姓名
select * from emp order by jointime limit 5;

  where name='zs'; 

笔记补充:
create table t3 (id int unsigned); unsigned取值范围无符号(0,255)
char(10) 长度 int(6) zerofill 不足6位前面用0自动补齐,例如:员工号
oracle的 OLAP数据库 写入少,读多 carchar利用率高 。 OLTP在线数据库,比较多的更改用char

3.3 数据修改-Update

ENUM和SET 枚举类型
enum单选,可以为空 例如性别 set 多选 喜爱
UNIQUE 不可以重复值,但是可以为空。null != null
create unique index ID on t1(ID);
primary key 可以用多格列,不允许两个列
外键:部门did约束,员工did范围不超过部门did
幻读:事物提交之前读取的数据
脏读:在内存中,未写到硬盘中而读取的数据

  update student(表名)

start transaction; 开启事物。开启后,insert数据 再create,会自动提交数据。不再同一终端可以create,不会提交。例如:申请账号,返回,上一步等选项

  set score=90

  where name='zs';

 

3.4 数据查询-select

  1. 模式

    select number,name,score

    from student

    where age>20

    group by 字段名[字段名,字段名,字段名]  //一个或多个字段名

      having <条件表达式>    //分组后的条件

    order by 字段名 [ASC|DESC],字段名[ASC|DESC];  //默认升序

  2. 常用聚合函数

    count(*)     //计算记录个数

    count(字段名)  //对一列中的值计算个数

    sum(字段名)   //求一列值得总和(数值型数)

    avg(字段名)    //求一列值得平均值(数值型数)

    max(字段名)    //求一列值得最大值

    min(字段名)    //求一列值得最小值

 

四、视图的创建

    create view view_name(字段名,字段名,字段名)

    as <select查询语句>

  例如:

    create view stu_sub(stu_num,stu_name)  //创建视图

    as select stu_num,stu_name

      from student;

    drop view stu_sub;  //删除视图

五、触发器

    create trigger trigger_name  //创建触发器

    on table(表名)

    for insert|delete|update

    as <sql_statement>(sql语句)

  例如:

    create trigger student_backup  //学生备份表随学生表的改变而改变

    on student        

    for insert,delete,update

    as

    begin

      if((select count(*) from deleted) > 0)

      begin

        delete from student_backup

        where stu_name in(select stu_name from deleted)

      end

      if((select count(*) from inserted) > 0)

      begin

        insert into student_backup

        select *

        from inserted

      end

    end

       

六、事务

  6.1 事务四大特性

    原子性;

    一致性;

    隔离性;

    持久性。

  6.2 事务隔离级别

    赃读:一个事务A读取另一个事务B未提交的数据;

    不可重复读:一个事务A读取另一个事务B提交后的数据(update);

    虚读(幻读):一个事务A读取另一个事务B插入的数据(insert),前后不一致。

    以上情况均为事务A已开启,但未提交时发生!

    四个级别:

    1* read uncommitted :赃读,不可重复读,虚读都有可能发生;

    2* read commited :避免赃读。不可重复读,虚读都有可能发生;

    4* repeatable read :避免赃读,不可重复读。虚读有可能发生;

    8* serializable :避免赃读,不可重复读、虚读;

    级别越高,安全性越高,性能越差!

   6.3 查看和设置事务级别

    查看当前事务隔离级别:select @@tx_isolation;  //dos命令

    设置事务隔离级别:set transaction isolation level 四个级别之一;  //dos命令

    设置隔离级别,必须在开启事务之前!

    

    

 

本文由9159.com发布于www.9159.com,转载请注明出处:关系型数据库管理系统,接下来的一些内容

关键词: