mysql学习记录（附案例）

2019年12月08日

数据库---数据的存储和管理的设备

安装数据软件-----mysql/oracle/sqlserver.....

数据库结构------数据库
                 对象（表，视图，索引，过程，函数）
                 字段（列）
                 数据块
                 数据

数据类型：数值int double..
           字符varchar   char..
           日期date DATETIME  time..
开发模式           
c/s： 需要安装独立的客户端软件
b/s:  不需要安装客户端软件，浏览器

            sqlyog
人-------客户端软件------数据库mysql沟通   c/s
人----- 网页    ---------- 数据库
          (html)               (SQL)
                   (java)
                   
sql语言：
1.DDL（定义语言） 对数据库对象进行操作的语言（create alter. drop.)
              比如:  数据库  表   视图
              
2.DML（操作语言） 对数据库的数据进行操作（insert update. delete. select.)

3.DCL( 控制语言） 对象数据库的权限操作grant revoke.

4.TCL( 事务操作） 对数据库事务进行操作 commit.  rollback. 

#-------------------------对数据库
#创建数据库的语法
CREATE DATABASE 数据库名
#使用数据库
USE DATABASE 数据库名
#删除数据库
DROP DATABASE 数据库名

#-----------------------对表
#创建表
CREATE TABLE 表名(
  字段名  数据类型,
  字段名  数据类型,
  字段名  数据类型,
  ....
  字段名  数据类型
)
#删除表
DROP TABLE 表名

#修改表
#(1)添加字段tel
ALTER TABLE account ADD  tel VARCHAR(20)

#(2)删除字段tel
ALTER TABLE account DROP COLUMN tel

#(3)修改字段类型tel 的类型从varchar改为int
ALTER TABLE account MODIFY tel INT

#(4)修改字段名称tel的名称改为telephone
ALTER TABLE account CHANGE tel telephone INT

#---------------------------DML（对数据库数据进行操作的insert update delete select)
#插入数据
INSERT INTO  表名[(列名1,列名2,......)] VALUES(值1,值2.....)
 注意：后面的值要前面的列保持一致(个数，顺序，类型)


数值可以改变为文本    23----'23'   005----'5' 
文本不一定可以变为数值 '005'---5     '你好'---报错

varchar，char 数据表达  ''
INT, DOUBLE,FLOAT 数据表达  数字
DATE,DATETIME,TIME 表达  'yyyy-MM-dd HH:mm:ss'

#修改数据
UPDATE 表名 SET 列名=新值,列名=新值.......列名=新值 [WHERE 条件表达式]

#删除数据
DELETE FROM 表名 [WHERE  条件表达式]



#-------------------DDL (CREATE ALTER DROP)
#创建数据库
CREATE DATABASE kgc;

#在kgc数据下创建表account
#选中数据库的
USE kgc;
#创建表的
CREATE TABLE account(
  aid INT,
  aname VARCHAR(20),
  sex CHAR(2),
  birthday DATETIME,
  tel VARCHAR(20),
  cardnum VARCHAR(18)
);

#删除表
DROP TABLE account;

#删除数据库
DROP DATABASE kgc;


#--------------修改表alter table 表名 
#添加字段tel
ALTER TABLE account ADD  tel VARCHAR(20)

#删除字段tel
ALTER TABLE account DROP COLUMN tel

#修改字段类型tel 的类型从varchar改为int
ALTER TABLE account MODIFY tel INT

#修改字段名称tel的名称改为telephone
ALTER TABLE account CHANGE tel telephone INT



#---------------------向account表插入数据
INSERT INTO account(aid,aname,cardnum,sex) VALUES (1,2,3,4)
INSERT INTO account(aid,aname,cardnum,sex) VALUES ('你好','admin',3,4)
INSERT INTO account VALUES ('你好','admin',3,4)，('你好','admin1',3,5)
-- 可以忽略列名不写，那么默认就是向表中所有列插入数据，顺序就是表中列的默认顺序，插入多组数据时中间用逗号隔开
INSERT INTO account VALUES(3,'lily','男',NULL,NULL,NULL)

#----------------------对account表中的数据进行修改
UPDATE account SET telephone='222'
UPDATE account SET telephone='333',birthday='1990-11-11'
#把用户表中admin用户的电话该为444，出生日期改为1997-12-11
UPDATE account SET telephone=444,birthday='1997-12-11' WHERE aname='admin'
UPDATE account SET telephone=555,birthday='1998-12-11' WHERE aname='admin' OR sex='男'

#------------------------删除account表中的数据
#删除id为0的用户
DELETE FROM account WHERE aid=0
DELETE FROM account

2019年12月09日

1.约束（有一定规范）（保证数据完整性，一致性，正确性，安全性）
   非空约束，not NULL 不能插入null空值
   唯一约束,UNIQUE  不允许插入重复数据
   主键约束,PRIMARY 不允许插入重复数据也不允许插入null
   默认约束,DEFAULT  如果没有插入值默认值为指定值
   外键约束,FOREIGN  插入的数据必须是来自于其他表的主键
   
   检查约束（mysql没有检查约束）
   
   枚举类型 ENUM()
   
  #sql语句添加约束
  #----创建表时添加约束
   CREATE TABLE account(
    aid INT PRIMARY KEY,
    aname VARCHAR(20) NOT NULL,
    sex ENUM('男','女'),
    birthday DATETIME DEFAULT '1990-12-11',
    cardnum VARCHAR(18),
    did INT ,
    UNIQUE(cardnum),
    FOREIGN KEY(did) REFERENCES depart(depno)
   )
   #references 表示参照的意思  
   # REFERENCES depart(depno) 即参照depart的depno列 depno必须是主键
   
   #---表已创建，添加约束
   CREATE TABLE account(
    aid INT ,
    aname VARCHAR(20) ,
    sex ENUM('男','女'),
    birthday DATETIME  ,
    cardnum VARCHAR(18),
    did INT
   )
   #---添加主键约束
   ALTER TABLE  account  ADD PRIMARY KEY(aid)
   #--添加非空约束
   ALTER TABLE account MODIFY aname VARCHAR(20) NOT NULL
   #--添加默认约束
   ALTER TABLE account MODIFY birthday DATETIME DEFAULT '1990-12-11'
   #--添加唯一约束
   ALTER TABLE account ADD UNIQUE(cardnum)
   #--添加外键约束
   ALTER TABLE account ADD FOREIGN KEY(did) REFERENCES depart(depno)
   

   
2.查询---对数据进行操作的语言insert(插入) update（修改) delete（删除）
查询关键是 SELECT 数据,数据....
SELECT '王五',89,'李四'    #查询语句的原理：select执行一次只会查询一行记录
                            #                select后面的数据个数是列数

 SELECT 数据,数据.... [FROM 数据集别名]
      #查询原理是，数据集有几行，select就执行几次
      #数据集的行数是查询行数， select后面的数据个数是列数

#查询部门表中所有信息
SELECT 1,'市场部',NULL;
SELECT 2,'行政部',NULL;
SELECT 3,'人事部',NULL;
SELECT 4,'财务部',NULL;
#------优化1
SELECT 'kk','mm' FROM depart
SELECT 1,'市场部',NULL FROM depart
#------优化2
SELECT depno,'市场部',NULL FROM depart
#------优化3
SELECT depno,depname,remark FROM depart

SELECT depno,depname,'王五' FROM depart

#-------查询员工表中所有记录
SELECT aid,aname,sex,birthday,cardnum,did,'人事部' FROM account

SELECT depname FROM depart WHERE depno=3

SELECT aid,aname,sex,birthday,cardnum,did,
(SELECT depname FROM depart WHERE depno=did)
 FROM account

#--取别名a.空格  b.as
SELECT aid 用户编号,aname AS 用户名称 FROM account
SELECT aid 用户编号,aname AS 用户名称 FROM account X

SELECT 用户编号,用户名称 FROM account  #报错
SELECT '用户编号','用户名称' FROM account 

SELECT 用户编号,用户名称 FROM 
(SELECT aid 用户编号,aname AS 用户名称 FROM account) X

#----*号代表是一行所有列
SELECT * FROM account

#---取几条记录的查询limit 起始下标(0开始),个数
SELECT * FROM depart LIMIT 2,2
SELECT * FROM depart LIMIT 1,3
-- 获取前3条记录
SELECT * FROM depart LIMIT 0,3
SELECT * FROM depart LIMIT 3

#--条件查询 select 数据,数据... [from 数据集别名 [where 条件表达式]]
/*
 比较: >  <  =  !=  < >  >= <=
 多条件:且(and)  或or
 范文查询: between ..and..  not between..and ..
 集合查询： in(数据,数据...) not in(数据,数据...)
 null查询: is null           is not null
 模糊查询: like 通配符_  %
*/

#查询部门编号为3的部门
SELECT * FROM depart WHERE  depno=3
#查询部门编号大于3的部门
SELECT * FROM depart WHERE depno>3
#查询部门编号大于等于3 且 小于等于7的部门
SELECT * FROM depart WHERE depno>=3 AND depno<=7
SELECT * FROM depart WHERE depno BETWEEN 3 AND 7
#查询部门编号是3,2,8的部门
SELECT * FROM depart WHERE depno=2 OR depno=3 OR depno=8
SELECT * FROM depart WHERE depno IN(2,3,8)
SELECT * FROM depart WHERE depno NOT IN(2,3,8)

2019年12月11日

SELECT aid,sex,'5层4教' FROM account 

SELECT 用户编号,aname FROM account 

SELECT 用户编号,aname FROM (SELECT aid 用户编号,* FROM account)a

SELECT aid,aname FROM (SELECT aid 用户编号,* FROM account)a WHERE aid=1

---SELECT 数据,数据....[FROM 数据集名 [WHERE 条件表达式]]
SELECT '王五',aid FROM account

#条件表达式
#比较> < >= <= != =
#范围between..and..  not between..and..
#集合in(数据，数据...) not in(数据，数据...)
#多条件 and(且)   or(或者)
#null值查询
#模糊查询 like  通配符%(任意长度任意字符) _（一个任意字符）

#查询性别是男性的员工
SELECT * FROM account WHERE sex='男'
#查询编号大于2的员工
SELECT * FROM account WHERE aid>2
#查询性别是男性或者编号>2的员工
SELECT * FROM account WHERE sex='男' OR aid>2
#查询部门编号是1，3的员工信息
SELECT * FROM account WHERE did=1 OR did=3
SELECT * FROM account WHERE did IN(1,3)

#查询性别是女性而且部门是2的员工
SELECT * FROM account WHERE sex='女' AND did=2

#查询有身份证号的员工
SELECT * FROM account WHERE cardnum IS NOT NULL
SELECT * FROM account WHERE cardnum!=NULL #查询不出结果
#查询姓名为空字符的员工
SELECT * FROM account WHERE aname=''
SELECT * FROM account WHERE aname IS NULL

#查询姓名有y的员工
SELECT * FROM account WHERE aname = '%y%'
#查询姓l的员工
SELECT * FROM account WHERE aname LIKE 'l%'

#取别名as或者空格      取记录limit 起始下标,个数  #去重复distinct
#查询所有员工的名称和部门编号
SELECT DISTINCT aname,did FROM account

SELECT DISTINCT did FROM account


#---聚和函数查询sum(数据)求和  max()求最大值 min()求最小值 count()求个数 avg()求平均数
#查询所有人的年龄
SELECT age FROM account

#查询所有人的年龄之和
SELECT SUM(age) FROM account
SELECT COUNT(age) FROM account
#查询平均年龄
SELECT SUM(age)/COUNT(age) FROM account
SELECT AVG(age) FROM account
SELECT MAX(age) FROM account
SELECT MIN(age) FROM account

SELECT aname,age FROM account
SELECT aname, SUM(age) FROM account   #不允许聚合行数和其他列名一起使用，除非用了分组查询

#---分组查询（分组后的条件查询）group by 列名,列名...[having 条件表达式]
#查询员工总人
SELECT COUNT(*) FROM account
#查询每个部门下的人数
SELECT * FROM account GROUP  BY did   #语法是错误的

SELECT did,COUNT(*) FROM account GROUP BY did
#查询每个部门下的人数，最大年龄，最小年龄
SELECT did,COUNT(*),MAX(age),MIN(age) FROM account GROUP BY did

#查询每个部门人数在2人及以上的部门
SELECT did,COUNT(*) FROM account GROUP BY did HAVING COUNT(*)>=2

#--排序order by 【asc(升序)默认  desc降序】
SELECT * FROM account ORDER BY did DESC,age ASC

# select 数据,数据 ... from 数据集名称  [where 条件 group by 列名 having 条件 order by 列名,列名...]


#多表查询---多张表整合成1张表
#查询的员工7个数据+部门的3个数据  （笛卡儿积查询)
SELECT account.*,depart.* FROM account,depart
SELECT account.*,depart.* FROM account,depart WHERE account.did=depart.depno

#联合查询union  ，行合，考虑列对称(以第一条查询语句列数为主，列数一致，类型兼容)
SELECT aid,aname ,sex FROM account         #16行
UNION
SELECT depno,depname,NULL FROM depart      //21行


#连接查询join   ，列合，考虑行对称
#笛卡儿集cross join
SELECT account.*,depart.* FROM account  CROSS JOIN  depart
#左外连接
SELECT account.*,depart.* FROM account  LEFT JOIN  depart ON account.did=depart.depno
#右外连接
SELECT account.*,depart.* FROM account  RIGHT JOIN  depart ON account.did=depart.depno
#内连接  inner join
SELECT account.*,depart.* FROM account  INNER JOIN  depart ON account.did=depart.depno