MySql基础

CREATE DATABASE NBAmag

CREATE TABLE team(NO INT PRIMARY KEY AUTO_INCREMENT,
                  NAME VARCHAR(9) NOT NULL UNIQUE,
                  addresss VARCHAR(16) NOT NULL)
                  
                  
                  
         INSERT INTO team(NAME,addresss)
         VALUES('湖人','洛杉矶'),
                ('火箭','休斯顿'),
                ('凯尔特人','波士顿'),
                ('骑士','克利夫兰'),
                ('马刺','圣安东尼奥'),
                ('魔术','奥兰多')
                
                
                
    CREATE TABLE teamnum(num INT PRIMARY KEY AUTO_INCREMENT,
                        NAME VARCHAR(10) NOT NULL,
                        birthday DATE,
                        age INT NOT NULL,
                        height DECIMAL(3,2) ,
                        location VARCHAR(16) )
                        
                        DROP TABLE teamnum
                        
                        INSERT INTO teamnum(NAME,birthday,age,height,location)
                        VALUES('韦德','1982-1-17',42,1.9,'后卫'),
                               ('詹姆斯','1982-12-23',42,2.1,'前锋'),
                               ('科比','1978-12-23',46,2.01,'后卫')
                               
               
             INSERT INTO teamnum(NAME,birthday,age,height,location)
             VALUES('奥尼尔','1972-12-12',51,2.02,'中锋'),
                   ('乔丹','1963-12-23',60,2.01,'前锋')
                   
                   DROP TABLE teamnum
  -- 条件查询
  
  /*
  使用WHERE 子句，将不满足条件的行过滤掉，WHERE 子句紧随 FROM 子句。
语法:select <结果> from <表名> where <条件>
比较
=, != 或<>, >, <, >=, <=
逻辑运算
and 与
or 或
not 非
*/

SELECT *FROM team WHERE NAME IS NOT NULL

SELECT * FROM teamnum WHERE height>1.9 AND height<1.99 OR height != 2.1

SELECT * FROM teamnum WHERE height <> 1.9

SELECT * FROM team WHERE NAME='魔术'

/*
条件查询
模糊查询
LIKE ：是否匹配于一个模式 一般和通配符搭配使用，可以判断字符型数值
或数值型. 通配符： % 任意多个字符
between and 两者之间,包含临界值；
in 判断某字段的值是否属于in列表中的某一项
IS NULL（为空的）或 IS NOT NULL（不为空的）
*/
SELECT * FROM team WHERE NAME LIKE '火%'

SELECT SUM(height) FROM teamnum WHERE height BETWEEN 1.6 AND 2.2 -- 包含临界值

SELECT AVG(height) FROM teamnum 
WHERE height IN(SELECT height FROM teamnum
                WHERE height IS NOT NULL)     
                
                -- not 非
             SELECT * FROM team WHERE NAME IN('湖人','火箭') 
                
ALTER TABLE


/*
1、UNION 的语法如下：
[SQL 语句 1]
UNION
[SQL 语句 2]
2、UNION ALL 的语法如下：
[SQL 语句 1]
UNION ALL
[SQL 语句 2]
当使用union 时，mysql 会把结果集中重复的记录删掉，而使用union all ，
mysql 会把所有的记录返回，且效率高于union 。
*/


SELECT * FROM teamnum WHERE height IS NULL AND location LIKE '中%'
UNION
SELECT * FROM teamnum WHERE NAME IS NOT NULL


SELECT * FROM teamnum WHERE height IS NULL AND location LIKE '中%'
UNION ALL
SELECT * FROM teamnum WHERE NAME IS NOT NULL

/*
• 排序
查询结果排序,使用 ORDER BY 子句排序 order by 排序列 ASC/DESC
asc代表的是升序，desc代表的是降序，如果不写，默认是升序
order by子句中可以支持单个字段、多个字段
*/

SELECT * FROM teamnum  ORDER BY age DESC


SELECT * FROM teamnum  ORDER BY height DESC

/*
数量限制
limit子句：对查询的显示结果限制数目 (sql语句最末尾位置)
SELECT * FROM table LIMIT offset rows;
SELECT * from table LIMIT 0,5;
*/

SELECT * FROM teamnum LIMIT 0,2

-- sql server  中用top2   查询前两条