• 线上老虎机|线上老虎机官网唯一授权入口

  • 发布时间:2016-01-19 07:05 | 作者:yc | 来源:互联网 | 浏览:1200 次
  • 线上老虎机|线上老虎机官网唯一授权入口

    mySQL简明入门

    详情请参阅

    http://downloads.mysql.com/docs/mysql-tutorial-excerpt-5.1-en.pdf

    mysql -u root -p 回车

    暗码 12345

    Ctrl + D 退出

    反省版别

    SELECT VERSION(), CURRENT_DATE;

    反省数据库

    SHOW DATABASES;

    创立数据库

    CREATE DATABASE menagerie;

    运用数据库

    USE menagerie

    创立表格

    CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20), species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);

    显现数据库中的表格的名称

    SHOW TABLES;

    显现表格各列的特征

    DESCRIBE pet;

    从本地读取文件

    LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet;

    把稳, 要是是Windows, 要运用 LINES TERMINATED BY '\r\n';

    在表格中刺进一行

    INSERT INTO pet VALUES ('Puffball','Diane','hamster','f','1999-03-30',NULL);

    INSERT INTO pet VALUES ('Fluffy',' Harold ','cat ','f ','1993-02-04',NULL);

    INSERT INTO pet VALUES ('Claws ','Gwen ','cat ','m ','1994-03-17',NULL);

    INSERT INTO pet VALUES ('Buffy ','Harold ','dog ','f ','1989-05-13',NULL);

    INSERT INTO pet VALUES ('Fang ','Benny',' dog ','m ','1990-08-27',NULL);

    INSERT INTO pet VALUES ('Bowser ','Diane ','dog ','m ','1979-08-31','1995-07-29');

    INSERT INTO pet VALUES ('Chirpy',' Gwen',' bird',' f',' 1998-09-11',NULL)

    INSERT INTO pet VALUES ('Whistler',' Gwen',' bird',' NULL', '1997-12-09',NULL)

    INSERT INTO pet VALUES ('Slim',' Benny',' snake',' m ','1996-04-29',NULL)

    从数据表中获取数据

    SELECT 的根本句子格局:

    SELECT what_to_select

    FROM which_table

    WHERE conditions_to_satisfy;

    ### 查询悉数数据

    SELECT * FROM pet;

    ### 修正有些数据

    UPDATE pet SET birth = '1989-08-31' WHERE name = 'Bowser';

    ### 查询有些数据

    SELECT * FROM pet WHERE name = 'Bowser';

    ### 比较运算

    SELECT * FROM pet WHERE birth >= '1998-1-1';

    ### 逻辑运算 AND

    SELECT * FROM pet WHERE species = 'dog' AND sex = 'f';

    ### 逻辑运算 OR

    SELECT * FROM pet WHERE species = 'snake' OR species = 'bird';

    ### 运算的优先级

    SELECT * FROM pet WHERE (species = 'cat' AND sex = 'm') OR (species = 'dog' AND sex = 'f');

    ### 遴选特定的列

    SELECT name, birth FROM pet;

    SELECT owner FROM pet;

    ### 去重复

    SELECT DISTINCT owner FROM pet;

    ### SELECT 的根本组合

    SELECT name, species, birth FROM pet WHERE species = 'dog' OR species = 'cat';

    ### 排序, 默许是 由小到大年夜排序

    SELECT name, birth FROM pet ORDER BY birth;

    ### 由大年夜到小排序

    SELECT name, birth FROM pet ORDER BY birth DESC;

    ### 多个列排序

    SELECT name, species, birth FROM pet ORDER BY species, birth DESC;

    ### 当前日期

    CURDATE()

    ### 两个日期相隔时候

    TIMESTAMPDIFF()

    ### 构成新的一列

    AS age (见后边的比如)

    ### 日期核算

    SELECT name, birth, CURDATE(), TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age FROM pet;

    ### 参加新一列的表格再排序, 依照name

    SELECT name, birth, CURDATE(), TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age FROM pet ORDER BY name;

    ### 参加新一列的表格再排序, 依照 age

    SELECT name, birth, CURDATE(), TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age FROM pet ORDER BY age;

    ### 判别 is not null

    SELECT name, birth, death, TIMESTAMPDIFF(YEAR,birth,death) AS age FROM pet WHERE death IS NOT NULL ORDER BY age;

    ### 时候核算

    YEAR(), MONTH(), DAYOFMONTH()

    ### 哪个月份出世

    SELECT name, birth, MONTH(birth) FROM pet;

    ### 依照运算出的成果查找

    SELECT name, birth FROM pet WHERE MONTH(birth) = 5;

    ### 十仲春份等的处置惩罚

    SELECT name, birth FROM pet WHERE MONTH(birth) = MOD(MONTH(CURDATE()), 12) + 1;

    ### 对NULL的判别

    SELECT 1 IS NULL, 1 IS NOT NULL;

    #### 匹配与正则表达式, 寻觅name中以b最初的

    SELECT * FROM pet WHERE name LIKE 'b%';

    #### 寻觅name中以fy完毕的

    SELECT * FROM pet WHERE name LIKE '%fy';

    #### 寻觅姓名中含有w的

    SELECT * FROM pet WHERE name LIKE '%w%';

    #### 寻觅姓名只包括五个字母的, 用五个_

    SELECT * FROM pet WHERE name LIKE '_____';

    #### 正则表达式的关键词

    REGEXP and NOT REGEXP operators (or RLIKE and NOT RLIKE)

    #### 正则表达式

    . 任意单个字符

    [a-z] a到z的任意字符

    [0-9] 0到9的任意字符

    “*” 之hg0088.com前, 放任何字符, 注解的是任意重复次数的该字符

    .* 注解任意重复次数的任意字符

    ^ 起头

    $ 完毕

    ### 正则表达式举例

    ### 以b起头的人名(不差别大年夜小写)

    SELECT * FROM pet WHERE name REGEXP '^b';

    ### 匹配大年夜小写 BINARY, 姓名以小写的b最初的

    SELECT * FROM pet WHERE name REGEXP BINARY '^b';

    ### 姓名以fy完毕的

    SELECT * FROM pet WHERE name REGEXP 'fy$';

    ### 姓名中含有w的

    SELECT * FROM pet WHERE name REGEXP 'w';

    ### 姓名仅由五个字母构成

    SELECT * FROM pet WHERE name REGEXP '^.....$';

    或许 SELECT * FROM pet WHERE name REGEXP '^.{5}$';

    ### 核算表格的行数

    SELECT COUNT(*) FROM pet;

    ### GROUP BY, 关于每一个水平进行核算

    SELECT owner, COUNT(*) FROM pet GROUP BY owner;

    SELECT species, COUNT(*) FROM pet GROUP BY species;

    SELECT sex, COUNT(*) FROM pet GROUP BY sex;

    ### 分组能够一路思虑几个要素

    SELECT species, sex, COUNT(*) FROM pet GROUP BY species, sex;

    ### 进一步遴选, 只需 dog 或许 cat的有些纪录

    SELECT species, sex, COUNT(*) FROM pet WHERE species = 'dog' OR species = 'cat' GROUP BY species, sex;

    ### 只保留已知性其余, 依照 species 和 sex 核算

    SELECT species, sex, COUNT(*) FROM pet WHERE sex IS NOT NULL GROUP BY species, sex;

    把稳: select后边的列, 要是运用了count()函数, 则后边的group by后边, 有需要跟上一样的列. 不然mysql会报错.

    ####### 运用多个表格

    #### 创立一个新的表

    CREATE TABLE event (name VARCHAR(20), date DATE, type VARCHAR(15), remark VARCHAR(255));

    #### 载入数据

    LOAD DATA INFILE '/home/jinlong/programming/mysql/event.txt' INTO TABLE event;

    #### 一路查询两个表格, 把稳该当先将两个表格

    用 INNER JOIN 吞并.

    ON 表格吞并的凭仗 关于pet表格来说, 是name, 所所以 pet.name

    关于event表格来说, 也是name, 所所以event.name

    而遴选的前提,是 event表格中的type为 'litter'

    SELECT pet.name,

    (YEAR(date)-YEAR(birth)) - (RIGHT(date,5)RIGHT(birth,5)) AS age,remark

    FROM pet INNER JOIN event

    ON pet.name = event.name

    WHERE event.type = 'litter';

    ###为了核算的便利, 无意偶尔候能够将同一个表格进行 INNER JOIN

    mysql> SELECT p1.name, p1.sex, p2.name, p2.sex, p1.species

    FROM pet AS p1 INNER JOIN pet AS p2

    ON p1.species = p2.species AND p1.sex = 'f' AND p2.sex = 'm';

    #### 查询数据库的名称

    SELECT DATABASE();

    线上老虎机|线上老虎机官网唯一授权入口

    #### 显现表名

    SHOW TABLES;

    #### 获取表格的构造

    DESCRIBE pet;

    #### MYSQL的批处置惩罚 从terminal运转

    UNIX渠道

    mysql batch-file

    WINDOWS 渠道

    mysql -e "source batch-file"

    ####

    #### 从terminal运转 mySQL的批处置惩罚文件

    mysql -h host -u user -p batch-file

    #### 从terminal运转 成果的分屏显现

    mysql batch-file | more

    #### 从terminal运转 输出成果导出到文件

    mysql batch-file > mysql.out

    #### 从terminal运转 在批处置惩罚形式下显现输出

    mysql -t

    #### 从terminal运转 在批处置惩罚形式下, 保留输入的指令

    mysql -vvv

    #### 在mySQL中运转脚本文件

    source filename;

    或 \. filename;

    ####################################################################

    ### 创立一个产品价目表, 并根据该表做各类查询

    show databases;

    create database test;

    use test;

    CREATE TABLE shop (

    article INT(4) UNSIGNED ZEROFILL DEFAULT '0000' NOT NULL,

    dealer CHAR(20)

    DEFAULT ''

    NOT NULL,

    price

    DOUBLE(16,2)

    DEFAULT '0.00' NOT NULL,

    PRIMARY KEY(article, dealer));

    INSERT INTO shop VALUES

    (1,'A',3.45),(1,'B',3.99),(2,'A',10.99),(3,'B',1.45),

    (3,'C',1.69),(3,'D',1.25),(4,'D',19.95);

    SELECT * FROM shop;

    #### 查找最贵产品

    SELECT MAX(article) AS article FROM shop;

    #### 查找最高价对应的货色, 供货商

    SELECT article, dealer, price

    FROM

    shop

    WHERE price=(SELECT MAX(price) FROM shop);

    #### 每一组的最贵产品

    SELECT article, MAX(price) AS price

    FROM

    shop

    GROUP BY article;

    #### 每组最贵产品地点的行

    SELECT article, dealer, price

    FROM

    shop s1

    WHERE price=(SELECT MAX(s2.price)

    FROM shop s2

    WHERE s1.article = s2.article);

    #### 暂时变量

    SELECT @min_price:=MIN(price),@max_price:=MAX(price) FROM shop;

    SELECT * FROM shop WHERE price=@min_price OR price=@max_price;

    ######################################

    #### KEYS

    CREATE TABLE person (

    id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,

    name CHAR(60) NOT NULL,

    PRIMARY KEY (id)

    );

    CREATE TABLE shirt (

    id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,

    style ENUM('t-shirt', 'polo', 'dress') NOmg电子游戏注册送20元T NULL,

    color ENUM('red', 'blue', 'orange', 'white', 'black') NOT NULL,

    owner SMALLINT UNSIGNED NOT NULL REFERENCES person(id),

    PRIMARY KEY (id)

    );

    INSERT INTO person VALUES (NULL, 'Antonio Paz');

    SELECT @last := LAST_INSERT_ID();

    INSERT INTO shirt VALUES

    (NULL, 'polo', 'blue', @last),

    (NULL, 'dress', 'white', @last),

    (NULL, 't-shirt', 'blue', @last);

    INSERT INTO person VALUES (NULL, 'Lilliana Angelovska');

    SELECT @last := LAST_INSERT_ID();

    INSERT INTO shirt VALUES

    (NULL, 'dress', 'orange', @last),

    (NULL, 'polo', 'red', @last),

    线上老虎机|线上老虎机官网唯一授权入口(NULL, 'dress', 'blue', @last),

    (NULL, 't-shirt', 'white', @last);

    SELECT * FROM person;

    ###### 查询keys

    SELECT field1_index, field2_index FROM test_table

    WHERE field1_index = '1' OR field2_index = '1'

    ### 查询每个月的造访量

    CREATE TABLE t1 (year YEAR(4), month INT(2) UNSIGNED ZEROFILL,

    day INT(2) UNSIGNED ZEROFILL);

    INSERT INTO t1 VALUES(2000,1,1),(2000,1,20),(2000,1,30),(2000,2,2),

    (2000,2,23),(2000,2,23);

    SELECT year,month,BIT_COUNT(BIT_OR(1day)) AS days FROM t1

    GROUP BY year,month;

    ##### 为行添加 unique identity for new rows

    AUTO_INCREMENT

    CREATE TABLE animals (

    id MEDIUMINT NOT NULL AUTO_INCREMENT,

    name CHAR(30) NOT NULL,

    PRIMARY KEY (id)

    ) ENGINE=INNODB;

    INSERT INTO animals (name) VALUES

    ('dog'),('cat'),('penguin'),

    ('lax'),('whale'),('ostrich');

    SELECT * FROM animals;

  • 相关内容

友情链接: