sql语句---增删改查
程序连接数据库的文件(A,ODBC--------PHP<.php> B,JDBC-----------JAVA<.jsp>)
取值范围:一般默认即可
MySQL数据类型 | 最小值 | 最大值 |
---|---|---|
tinyint(n) | -128 | 127 |
smallint(n) | -32,768 | 32,767 |
mediumint(n) | -8388608 | 8388607 |
int(n) | -2,147,483,648 | 2,147,483,647 |
bigint(n) | -9,223,372,036,854,775,808 | 9,223,372,036,854,775,807 |
约束条件操作
1.主键:两种方式 [不允许空格和重复] 表存在,alter table c3 add primary key (id字段); 2.创建表并指定约束,create table c3(id int,ip char(150),name varchar(30),primary key (id)); 3.删除主键,alter table c3 drop primary key; auto_increment自增 [自动增长,第一个不能空格] 1,表存在,alter table c3 modify ip int auto_increment;(主键同时存在),ALTER TABLE user CHANGE id id INT AUTO_INCREMENT; 2.创建表并添加,create table c3(ip int primary key auto_increment,ip char(30),name varchar(15)); 3.删除增长,alter table c3 change id id int not null;(带键就不用加not null) unique key [不能重复] 1.表存在,alter atble c3 add unique key (ip); 2.表创建并添加,create table c3(id int,ip char(30) unique,name varchar(30)); 3.删除unqiue,alter table c3 drop index ip; null和not null 1,表存在alter table c3 modify ip int NOT Null; 2,创建添加create table c3(id int(5),name varchar(10),sex enum('male','female') not null default 'male'); 3,删除,alter table c3 change sex sex enum('male','female'); unsigned属性: 1整数类型有可选的UNSIGNED属性,表示不允许负值,这大致上可以使正数的上限提高一倍 可以使用这几种整数类型:TINYINT,SMALLINT,MEDIUMINT,INT,BIGINT。分别使用8,16,24,32,64位存储空间 它们可以存储的值的范围从-2(n-1)到2(n-1)-1,其中n是存储空间的位数。 mysql> create table guo(id int(1) default null,qq int(5) unsigned default null); id为未指定unsigned,而qq为指定unsigned 先对id列插入数据 根据计算得2(n-1)-1为2147483647 insert into guo(id) values(2147483647); 字符集问题: 1,修改字符集 :在创建表的最后面指定一下: default charset=utf8 #可以指定中文 2,查询:show variables like '%char%'; 3,配置文件设置默认字符集,修改表的字符集,alter table 表名 default character set utf8; 来到MySQL的安装目录下找到my.cnf文件 在[mysql]下方添加一句: default-character-set=utf8 在[mysqld]下方添加两句: collation-server=utf8_general_ci character-set-server=utf8 停止mysql,重新启动mysql,再次执行语句 show variables like '%char%'; 这种方式并不能修改已经创建的数据库或表的字符集,修改的是之后创建的数据库或表的默认字符集 单独给表加 alter table test1(表名) convert to character set utf8mb4 collate utf8mb4_unicode_ci; 外键: 创建一个班级表,插入数据:create table class(cno int primary key auto_increment,cname varchar(20) not null,room varchar(4)); instert into class values(null,'java001',101),(null,'java002',102),(null,'java002',103); 创建一个学生表:create table student(id int primary key auto_increment,name varchar(20),sex enum('man','woman'),age int,classno int,constraint fk_stu_classno[可以不加就fk就行] foreign key (classno) references class(cno)) default charset=utf8; insert into student values(null,'小明','man',19,1),(null,'小刚','man',24,2), (null,'小李','man',21,3); 尝试class表操作,删除、更改1班。都会失败 delete from class where cno=2; update class set cno=5 where cno=2; 因为存在外键,class表中cno=1中有学生存在,所以删除不了。 如果想删除1班,请手动先对1班学生进行处理(删除或者清空外键 update student set classno=null where classno=1; delete from class where cno=1;再次删除cno=1的班级 修改外键设置:外键要修改只能先删除再添加 如果希望在更新班级号的时候,可以直接更新学生的班级编号;希望在删除某个班级的时候,清空学生的班级编号 删除外键 alter table student drop foreign key fk_stu_classno; 添加外键 alter table student add constraint fk_stu_classno foreign key(classno) references class(cno) on delete set null on update cascade;
数据类型
数据类型 1,整型,tinyint smallint mediumint int bigint 浮点数类型 float(5,2) #一共5位,整数占3位.做了限制 定点数类型 char表示定长字符串,长度是固定的;如果插入数据的长度小于char的固定长度时,则用空格填充;因为长度固定,所以存取速度要比varchar快很多,甚至能快50%,但正因为其长度固定,所以会占据多余的空间。 - varchar表示可变长字符串,长度是可变的;插入的数据是多长,就按照多长来存储;varchar在存取方面与char相反,它存取慢,因为长度不固定,但正因如此,不占据多余的空间。 - 结合性能角度(char更快),节省磁盘空间角度(varchar更小),具体情况还需具体来设计数据库才是妥当的做法。 总结: 1.经常变化的字段用varchar 2.知道固定长度的用char 3.超过255字符的只能用varchar或者text 4.能用varchar的地方不用text text:文本格式 枚举类型 create table t10(sex enum('m','w')); create table c3(sex enum('man','woman') not null default 'man'); 只能从m,w两个里面2选其1 日期类型 时间和日期类型测试:year、date、time、datetime、timestamp 作用:用于存储用户的注册时间,文章的发布时间,文章的更新时间,员工的入职时间等 插入年份时,尽量使用4位值 ==插入两位年份时,<=69,以20开头,比如65, 结果2065 >=70,以19开头,比如82,结果1982 create table test_time(d date,t time,dt datetime); insert into test_time values(now(),now(),now());
增删改查
创建库:create database mingge; 查看库:show databases; 进入库:use mingge; 查看所在库位置:select database(); 创建表格:create table web(id varchar(999),name varchar(999),url varchar(999),country varchar(999),sex enum('m','w')) default charset=utf8;(name中文显示) 时间和日期类型测试:year、date、time、datetime、timestamp 枚举类型 enum 有限制二选一用 修改字段和类型:alter table 表名 change 旧字段 新字段 类型;change修改字段名称,类型,约束,顺序 alter table c3 change max maxs int(15) after id; modify 不能修改字段名称 修改表名:rename table 旧名字 to 新名字;or alter table 旧名字 rename 新名字 添加新字段:alter table 表名 add 字段 类型; alter table 表名 add 添加的字段(和类型) after name; -------把添加的字段放到name后面 alter table 表名 add 添加的字段(和类型) first; ----------把添加的字段放在第一个 删除字段:alter table web drop country; 查看有哪些表:show tables; 查看表结构:desc web; 添加记录:insert into web(id,name) values('1','百度'); 添加多条记录:insert into web(id,name) values('2','京东'),('3','淘宝'); 修改记录:update web (表名) set id=2(修改的字段) where name='百度';(set后面跟的要改的记录,where后 跟的定位到哪一行) 删除单条记录:delete from web where id=2; 删的是一行 删除多行:delete from nba where id in =(2,3); 删除所有记录:delete from web; 查看表的状态:show table status like '表名'\G; ----向左翻转90度显示 查看表里的记录:语法: select 内容 from 表名; mysql> select * from t1; 删除表:drop table; 删除库:drop database; 乱码顺序解决方法:
表查询
单表查询 简单查询:select * from c3; 多字段查询:select id,name,sex from c3; 设置条件查询:select id,name, from c3 where id<=3; 显示出id几行的内容 where select id,name,chinese(成绩) from c3 where chinese>100; 设置别名:as select id as '学号',name as '姓名',chinese as '语文' from c3 where chinese>4000 统计记录数量:count() select count(*) from c3; 统计字段得到数量:select count(id) from c3; 统计分数大于100的:select count(name) from c3 where chinese>100; 避免重复distinct:表里数据有相同的 空格也算 select distinct post from employee5;(统计公司部门名称) 表复制:key不会被复制:主键,外键和索引 复制表:create table 新表 select * from 旧表; 复制单,多个字段:create table 新表 select id,name from 旧表; 多条件查询:and :和,与 select 字段1,字段2 from 表名 where 条件1 and 条件2; select name,salary from employee where post='hr' and salary>1500; or : 或者 select name,salary from employee where post='hr' and salary>1500 or salary<7000 between and 什么和什么之间 select name,salary from employee between salary=5000 and salary=15000; not 给条件取反 select name,salary from employee where not salary>5000; 关键字 is null 空的: select name,post from employee where post is null; select name,post from employee where post is not null; 取反 select name,post from employee where post=''; in集合查询 普通 select name,salary from employee where salary=4000 or salary=5000 or salary=8000; in select name,salary from employee where salary in(4000,5000,8000); 加not取反 排序查询 order by select name,salary from emplyee order by salary; select name,salary from emplyee order by salary desc;降序,从大到小 limit 限制 select * from employee limit5;只显示5行 select name,salary from employee order by salary desc limit2,3;从第二行开始打印三行,第二行不算 分组查询:group by 查询男生,女生各有多少人? mysql> select count(name),sex from employee5 group by sex; 如何查询同一薪资水准的,都有多少人? mysql> select count(name),salary from employee5 group by salary; 如何使用 GROUP BY 语句 将数据表按名字进行分组,并统计每个人有多少条记录: mysql> SELECT name, COUNT(*) FROM employee_tbl GROUP BY name; WITH ROLLUP(侧重点) WITH ROLLUP 可以实现在分组统计数据基础上再进行相同的统计(SUM,AVG,COUNT…)。 例如我们将以上的数据表按名字进行分组,再统计每个人登录的次数 mysql> select name,sum(singin) as singin_count FROM employee_tbl group by name WITH ROLLUP; select coalesce(name,'总数'),sun(singin) as singin_count employee groupby name with rollup;显示总数 GROUP BY和GROUP_CONCAT()函数一起使用 GROUP_CONCAT()-------组连接 mysql> SELECT dep_id,GROUP_CONCAT(name) FROM employee5 GROUP BY dep_id; #以dep_id分的组,dep_id这个组里面都有谁 mysql> SELECT dep_id,GROUP_CONCAT(name) as emp_members FROM employee5 GROUP BY dep_id; #给组连接设置了一个别名 查看班级2202 语文成绩大于90有多少人,都有谁 (name) select count(name),group_concat(name) from student where >90 and class=2202; 多表查询:内查询 只找出有相同部门的员工 写法一:select employee6.emp_id,employee6.emp_name,employee6.age,department6.dept_name from employee6,department6 where employee6.dept_id = department6.dept_id; 写法二: select emp_id,emp_name,age,dept_name from employee6,department6 where employee6.dept_id=department6.dept_id; from 表1 inner join 表2 on 条件 写法三:select employee6.emp_id,employee6.emp_name,employee6.age,department6.dept_name from employee6 inner join department6 on employee6.dept_id = department6.dept_id 写法四:select a.emp_id,a.emp_name,a.age,b.dept_name from employee6 a inner join department6 b on a.dept_id = b.dept_id; 左外连接:表A left [outer] join 表B on 关联条件,表A是主表,表B是从表 右外连接:表A right [outer] join 表B on 关联条件,表B是主表,表A是从表 全外连接:表A full [outer] join 表B on 关联条件,两张表的数据不管满不满足条件,都做显示。 查找出所有员工及所属部门 1.左外链接: select emp_id,emp_name,dept_name from employee6 left join department6 on employee6.dept_id = department6.dept_id; 2.右外连接 案例:找出所有部门包含的员工 select emp_id,emp_name,dept_name from employee6 right join department6 on employee6.dept_id = department6.dept_id; 3.全外连接 就是左连接和右连接中间加个 union select employee6.emp_id,employee6.emp_name,employee6.age,department6.dept_name from employee6 left join department6 on employee6.dept_id = department6.dept_id union select employee6.emp_id,employee6.emp_name,employee6.age,department6.dept_name from employee6 right join department6 on employee6.dept_id = department6.dept_id;