MySql入门(1)
来源:互联网 发布:吾知的小说 编辑:程序博客网 时间:2024/06/06 07:23
- MySql技术内幕之MySQL入门1
- 安装
- 关于注释
- 执行SQL语句
- 关于命令大小写
- 创建数据库
- 查看表的信息
- 查看更加详细的信息
- 查看与给定模式相匹配的列
- 插入数据
- 利用insert添加行
- 利用文件添加新行
- 总结
MySql技术内幕之MySQL入门(1)
安装
检查系统中是否已经安装了MySQL
sudo netstat -tap | grep mysql
若没有显示已安装结果,则没有安装。否则表示已经安装。
sudo apt-get install mysql-server mysql-client
安装过程中会让输入密码,记得把密码记住。
关于注释
三种写法:
- 用#单行注释
- 用– 单行注释,注意后面有一空格
- /* */ 多行注释
mysql> SELECT 1+1; # 这个注释直到该行结束mysql> SELECT 1+1; -- 这个注释直到该行结束mysql> SELECT 1 /* 这是一个在行中间的注释 */ + 1;mysql> SELECT 1+/*这是一个多行注释的形式*/1;
执行SQL语句
ltq@lab:~$ mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 7Server version: 5.7.19-0ubuntu0.16.04.1 (Ubuntu)Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> SELECT NOW(); # 查询当前日期和时间,使用分号终止语句+---------------------+| NOW() |+---------------------+| 2017-10-20 22:07:14 |+---------------------+1 row in set (0.00 sec)mysql> SELECT NOW()\g # 查询当前日期和时间,使用\g终止语句+---------------------+| NOW() |+---------------------+| 2017-10-20 22:07:56 |+---------------------+1 row in set (0.00 sec)mysql> SELECT NOW(), USER(), VERSION()\g # 查询时间,用户,系统版本,并在一列展示+---------------------+----------------+-------------------------+| NOW() | USER() | VERSION() |+---------------------+----------------+-------------------------+| 2017-10-20 22:08:28 | root@localhost | 5.7.19-0ubuntu0.16.04.1 |+---------------------+----------------+-------------------------+1 row in set (0.00 sec)mysql> SELECT NOW(), USER(), VERSION()\G #查询时间,用户,系统版本,并在一行展示*************************** 1. row *************************** NOW(): 2017-10-20 22:08:43 USER(): root@localhostVERSION(): 5.7.19-0ubuntu0.16.04.11 row in set (0.00 sec)mysql> SELECT NOW(), -> USER(), -> VERSION()\G # 在多行输入*************************** 1. row *************************** NOW(): 2017-10-20 22:18:44 USER(): root@localhostVERSION(): 5.7.19-0ubuntu0.16.04.11 row in set (0.00 sec)mysql> SELECT NOW(), -> USER(), -> \c # 使用\c表示不执行上述语句mysql> SELECT NOW(); SELECT USER(); SELECT VERSION(); # 在一行输入多条语句+---------------------+| NOW() |+---------------------+| 2017-10-20 22:21:38 |+---------------------+1 row in set (0.00 sec)+----------------+| USER() |+----------------+| root@localhost |+----------------+1 row in set (0.00 sec)+-------------------------+| VERSION() |+-------------------------+| 5.7.19-0ubuntu0.16.04.1 |+-------------------------+1 row in set (0.00 sec)
关于命令大小写
一般用大写字母表示SQL关键字和函数名;
用小写字母表示数据库,表和列的名字。
创建数据库
mysql> CREATE DATABASE sampdb; # 创建数据库Query OK, 1 row affected (0.00 sec)mysql> SELECT DATABASE(); # 查看当前数据库,结果为NULL+------------+| DATABASE() |+------------+| NULL |+------------+1 row in set (0.00 sec)mysql> USE sampdb; # 把sampdb设置为当前默认选择的数据库Database changedmysql> SELECT DATABASE(); # 查看当前数据库,结果为NULL+------------+| DATABASE() |+------------+| sampdb |+------------+1 row in set (0.00 sec)mysql> source create_president.sql # 如果已经在终端cd到create_president.sql路径下,那么则可运行该语句Query OK, 0 rows affected (0.15 sec)Query OK, 0 rows affected (0.23 sec)
补充,文件create_president.sql
的内容如下:
# Create president table for U.S. Historical LeagueDROP TABLE IF EXISTS president;#@ _CREATE_TABLE_CREATE TABLE president( last_name VARCHAR(15) NOT NULL, first_name VARCHAR(15) NOT NULL, suffix VARCHAR(5) NULL, city VARCHAR(20) NOT NULL, state VARCHAR(2) NOT NULL, birth DATE NOT NULL, death DATE NULL);#@ _CREATE_TABLE_
查看表的信息
mysql> DESCRIBE president; # 查看president表的结构+------------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+------------+-------------+------+-----+---------+-------+| last_name | varchar(15) | NO | | NULL | || first_name | varchar(15) | NO | | NULL | || suffix | varchar(5) | YES | | NULL | || city | varchar(20) | NO | | NULL | || state | varchar(2) | NO | | NULL | || birth | date | NO | | NULL | || death | date | YES | | NULL | |+------------+-------------+------+-----+---------+-------+7 rows in set (0.00 sec)mysql> DESC president; # DESCRIBE的简写为DESC+------------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+------------+-------------+------+-----+---------+-------+| last_name | varchar(15) | NO | | NULL | || first_name | varchar(15) | NO | | NULL | || suffix | varchar(5) | YES | | NULL | || city | varchar(20) | NO | | NULL | || state | varchar(2) | NO | | NULL | || birth | date | NO | | NULL | || death | date | YES | | NULL | |+------------+-------------+------+-----+---------+-------+7 rows in set (0.00 sec)mysql> EXPLAIN president;+------------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+------------+-------------+------+-----+---------+-------+| last_name | varchar(15) | NO | | NULL | || first_name | varchar(15) | NO | | NULL | || suffix | varchar(5) | YES | | NULL | || city | varchar(20) | NO | | NULL | || state | varchar(2) | NO | | NULL | || birth | date | NO | | NULL | || death | date | YES | | NULL | |+------------+-------------+------+-----+---------+-------+7 rows in set (0.00 sec)mysql> SHOW COLUMNS FROM president;+------------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+------------+-------------+------+-----+---------+-------+| last_name | varchar(15) | NO | | NULL | || first_name | varchar(15) | NO | | NULL | || suffix | varchar(5) | YES | | NULL | || city | varchar(20) | NO | | NULL | || state | varchar(2) | NO | | NULL | || birth | date | NO | | NULL | || death | date | YES | | NULL | |+------------+-------------+------+-----+---------+-------+7 rows in set (0.00 sec)mysql> SHOW FIELDS FROM president;+------------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+------------+-------------+------+-----+---------+-------+| last_name | varchar(15) | NO | | NULL | || first_name | varchar(15) | NO | | NULL | || suffix | varchar(5) | YES | | NULL | || city | varchar(20) | NO | | NULL | || state | varchar(2) | NO | | NULL | || birth | date | NO | | NULL | || death | date | YES | | NULL | |+------------+-------------+------+-----+---------+-------+7 rows in set (0.00 sec)
查看更加详细的信息
mysql> SHOW FULL COLUMNS FROM president; # SHOW FULL COLUMNS 比 SHOW COLUMNS显示更多的信息+------------+-------------+-------------------+------+-----+---------+-------+---------------------------------+---------+| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |+------------+-------------+-------------------+------+-----+---------+-------+---------------------------------+---------+| last_name | varchar(15) | latin1_swedish_ci | NO | | NULL | | select,insert,update,references | || first_name | varchar(15) | latin1_swedish_ci | NO | | NULL | | select,insert,update,references | || suffix | varchar(5) | latin1_swedish_ci | YES | | NULL | | select,insert,update,references | || city | varchar(20) | latin1_swedish_ci | NO | | NULL | | select,insert,update,references | || state | varchar(2) | latin1_swedish_ci | NO | | NULL | | select,insert,update,references | || birth | date | NULL | NO | | NULL | | select,insert,update,references | || death | date | NULL | YES | | NULL | | select,insert,update,references | |+------------+-------------+-------------------+------+-----+---------+-------+---------------------------------+---------+7 rows in set (0.00 sec)
查看与给定模式相匹配的列
如果需要对所查找的信息加以限定,
mysql> DESCRIBE president '%name'; # 查找名称中包含name的项+------------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+------------+-------------+------+-----+---------+-------+| last_name | varchar(15) | NO | | NULL | || first_name | varchar(15) | NO | | NULL | |+------------+-------------+------+-----+---------+-------+2 rows in set (0.00 sec)mysql> SHOW FIELDS FROM president like '%name'; # 查找名称中包含name的项,另外一种写法+------------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+------------+-------------+------+-----+---------+-------+| last_name | varchar(15) | NO | | NULL | || first_name | varchar(15) | NO | | NULL | |+------------+-------------+------+-----+---------+-------+2 rows in set (0.00 sec)
插入数据
利用insert添加行
一次添加一行数据:
INSERT INTO president VALUES ('Washington','George',NULL,'Wakefield','VA','1732-02-22','1799-12-14');INSERT INTO president VALUES ('Adams','John',NULL,'Braintree','MA','1735-10-30','1826-07-04');
一次添加多行数据:
INSERT INTO president VALUES ('Jefferson','Thomas',NULL,'Albemarle County','VA','1743-04-13','1826-07-04'), ('Madison','James',NULL,'Port Conway','VA','1751-03-16','1836-06-28');
利用文件添加新行
mysql> source insert_president.sqlQuery OK, 0 rows affected (0.00 sec)Query OK, 1 row affected (0.06 sec)Query OK, 1 row affected (0.03 sec)Query OK, 1 row affected (0.07 sec)Query OK, 1 row affected (0.03 sec)Query OK, 1 row affected (0.03 sec)Query OK, 1 row affected (0.02 sec)Query OK, 1 row affected (0.02 sec)Query OK, 1 row affected (0.04 sec)Query OK, 1 row affected (0.03 sec)Query OK, 1 row affected (0.03 sec)Query OK, 1 row affected (0.04 sec)Query OK, 1 row affected (0.03 sec)Query OK, 1 row affected (0.03 sec)Query OK, 1 row affected (0.04 sec)Query OK, 1 row affected (0.03 sec)Query OK, 1 row affected (0.04 sec)Query OK, 1 row affected (0.03 sec)Query OK, 1 row affected (0.03 sec)Query OK, 1 row affected (0.04 sec)Query OK, 1 row affected (0.03 sec)Query OK, 1 row affected (0.03 sec)Query OK, 1 row affected (0.04 sec)Query OK, 1 row affected (0.03 sec)Query OK, 1 row affected (0.03 sec)Query OK, 1 row affected (0.04 sec)Query OK, 1 row affected (0.03 sec)Query OK, 1 row affected (0.03 sec)Query OK, 1 row affected (0.03 sec)Query OK, 1 row affected (0.03 sec)Query OK, 1 row affected (0.04 sec)Query OK, 1 row affected (0.03 sec)Query OK, 1 row affected (0.03 sec)Query OK, 1 row affected (0.04 sec)Query OK, 1 row affected (0.03 sec)Query OK, 1 row affected (0.03 sec)Query OK, 1 row affected (0.04 sec)Query OK, 1 row affected (0.03 sec)Query OK, 1 row affected (0.03 sec)Query OK, 1 row affected (0.04 sec)Query OK, 1 row affected (0.03 sec)Query OK, 1 row affected (0.03 sec)Query OK, 1 row affected (0.04 sec)Query OK, 1 row affected (0.03 sec)
补充,文件source insert_president.sql
内容如下:
DELETE FROM president;INSERT INTO president VALUES ('Washington','George',NULL,'Wakefield','VA','1732-02-22','1799-12-14');INSERT INTO president VALUES ('Adams','John',NULL,'Braintree','MA','1735-10-30','1826-07-04');INSERT INTO president VALUES ('Jefferson','Thomas',NULL,'Albemarle County','VA','1743-04-13','1826-07-04');INSERT INTO president VALUES ('Madison','James',NULL,'Port Conway','VA','1751-03-16','1836-06-28');INSERT INTO president VALUES ('Monroe','James',NULL,'Westmoreland County','VA','1758-04-28','1831-07-04');INSERT INTO president VALUES ('Adams','John Quincy',NULL,'Braintree','MA','1767-07-11','1848-02-23');INSERT INTO president VALUES ('Jackson','Andrew',NULL,'Waxhaw settlement','SC','1767-03-15','1845-06-08');INSERT INTO president VALUES ('Van Buren','Martin',NULL,'Kinderhook','NY','1782-12-05','1862-07-24');INSERT INTO president VALUES ('Harrison','William H.',NULL,'Berkeley','VA','1773-02-09','1841-04-04');INSERT INTO president VALUES ('Tyler','John',NULL,'Greenway','VA','1790-03-29','1862-01-18');INSERT INTO president VALUES ('Polk','James K.',NULL,'Pineville','NC','1795-11-02','1849-06-15');INSERT INTO president VALUES ('Taylor','Zachary',NULL,'Orange County','VA','1784-11-24','1850-07-09');INSERT INTO president VALUES ('Fillmore','Millard',NULL,'Locke','NY','1800-01-07','1874-03-08');INSERT INTO president VALUES ('Pierce','Franklin',NULL,'Hillsboro','NH','1804-11-23','1869-10-08');INSERT INTO president VALUES ('Buchanan','James',NULL,'Mercersburg','PA','1791-04-23','1868-06-01');INSERT INTO president VALUES ('Lincoln','Abraham',NULL,'Hodgenville','KY','1809-02-12','1865-04-15');INSERT INTO president VALUES ('Johnson','Andrew',NULL,'Raleigh','NC','1808-12-29','1875-07-31');INSERT INTO president VALUES ('Grant','Ulysses S.',NULL,'Point Pleasant','OH','1822-04-27','1885-07-23');INSERT INTO president VALUES ('Hayes','Rutherford B.',NULL,'Delaware','OH','1822-10-04','1893-01-17');INSERT INTO president VALUES ('Garfield','James A.',NULL,'Orange','OH','1831-11-19','1881-09-19');INSERT INTO president VALUES ('Arthur','Chester A.',NULL,'Fairfield','VT','1829-10-05','1886-11-18');INSERT INTO president VALUES ('Cleveland','Grover',NULL,'Caldwell','NJ','1837-03-18','1908-06-24');INSERT INTO president VALUES ('Harrison','Benjamin',NULL,'North Bend','OH','1833-08-20','1901-03-13');INSERT INTO president VALUES ('McKinley','William',NULL,'Niles','OH','1843-01-29','1901-09-14');INSERT INTO president VALUES ('Roosevelt','Theodore',NULL,'New York','NY','1858-10-27','1919-01-06');INSERT INTO president VALUES ('Taft','William H.',NULL,'Cincinnati','OH','1857-09-15','1930-03-08');INSERT INTO president VALUES ('Wilson','Woodrow',NULL,'Staunton','VA','1856-12-19','1924-02-03');INSERT INTO president VALUES ('Harding','Warren G.',NULL,'Blooming Grove','OH','1865-11-02','1923-08-02');INSERT INTO president VALUES ('Coolidge','Calvin',NULL,'Plymouth Notch','VT','1872-07-04','1933-01-05');INSERT INTO president VALUES ('Hoover','Herbert C.',NULL,'West Branch','IA','1874-08-10','1964-10-20');INSERT INTO president VALUES ('Roosevelt','Franklin D.',NULL,'Hyde Park','NY','1882-01-30','1945-04-12');INSERT INTO president VALUES ('Truman','Harry S',NULL,'Lamar','MO','1884-05-08','1972-12-26');INSERT INTO president VALUES ('Eisenhower','Dwight D.',NULL,'Denison','TX','1890-10-14','1969-03-28');INSERT INTO president VALUES ('Kennedy','John F.',NULL,'Brookline','MA','1917-05-29','1963-11-22');INSERT INTO president VALUES ('Johnson','Lyndon B.',NULL,'Stonewall','TX','1908-08-27','1973-01-22');INSERT INTO president VALUES ('Nixon','Richard M.',NULL,'Yorba Linda','CA','1913-01-09','1994-04-22');INSERT INTO president VALUES ('Ford','Gerald R.',NULL,'Omaha','NE','1913-07-14','2006-12-26');INSERT INTO president VALUES ('Carter','James E.','Jr.','Plains','GA','1924-10-01',NULL);INSERT INTO president VALUES ('Reagan','Ronald W.',NULL,'Tampico','IL','1911-02-06','2004-06-05');INSERT INTO president VALUES ('Bush','George H.W.',NULL,'Milton','MA','1924-06-12',NULL);INSERT INTO president VALUES ('Clinton','William J.',NULL,'Hope','AR','1946-08-19',NULL);INSERT INTO president VALUES ('Bush','George W.',NULL,'New Haven','CT','1946-07-06',NULL);INSERT INTO president VALUES ('Obama','Barack H.',NULL,'Honolulu','HI','1961-08-04',NULL);
总结
登录
mysql -u root -p
本节SQL语句
mysql> SELECT NOW(); # 查询当前日期和时间,使用分号终止语句mysql> SELECT NOW()\g # 查询当前日期和时间,使用\g终止语句mysql> SELECT NOW(), USER(), VERSION()\g # 查询时间,用户,系统版本,并在一列展示mysql> SELECT NOW(), USER(), VERSION()\G #查询时间,用户,系统版本,并在一行展示mysql> SELECT NOW(); SELECT USER(); SELECT VERSION(); # 在一行输入多条语句mysql> CREATE DATABASE sampdb; # 创建数据库mysql> SELECT DATABASE(); # 查看当前数据库,结果为NULLmysql> USE sampdb; # 把sampdb设置为当前默认选择的数据库mysql> SELECT DATABASE(); # 查看当前数据库,结果为sampdbmysql> source create_president.sql # 如果已经在终端cd到create_president.sql路径下,那么则可运行该语句mysql> DESCRIBE president; # 查看president表的结构mysql> DESC president; # DESCRIBE的简写为DESCmysql> EXPLAIN president;mysql> SHOW COLUMNS FROM president;mysql> SHOW FIELDS FROM president;mysql> SHOW FULL COLUMNS FROM president; # SHOW FULL COLUMNS 比 SHOW COLUMNS显示更多的信息mysql> DESCRIBE president '%name'; # 查找名称中包含name的项mysql> SHOW FIELDS FROM president like '%name'; # 查找名称中包含name的项,另外一种写法mysql> INSERT INTO president VALUES ('Washington','George',NULL,'Wakefield','VA','1732-02-22','1799-12-14'); # 插入数据mysql> INSERT INTO president VALUES ('Jefferson','Thomas',NULL,'Albemarle County','VA','1743-04-13','1826-07-04'), ('Madison','James',NULL,'Port Conway','VA','1751-03-16','1836-06-28'); # 一次添加多行数据mysql> source insert_president.sql # 利用文件添加新行
待补充……
阅读全文
0 0
- php+mysql入门1
- MySQL入门1
- mysql#1入门
- mysql入门笔记1
- MySql入门(1)
- MySQL入门训练1
- MySql 入门(1)
- MySql入门(1)
- 【Mysql】mysql分区入门1—简介
- mysql手册总结1---入门
- 1、MySQL和SQL入门
- MySQL入门1--介绍篇
- MySQL入门 基本语法1
- MySql入门
- MYSQL入门
- MySQL入门
- MySQL 入门
- MYSQL入门
- 距离算法(更新中)
- java初入多线程9
- Numpy库学习——向量表示
- Stm32cubeMX生成配置工程的例子和过程记录
- 随记接口跟抽象类
- MySql入门(1)
- Python3pandas库transform用法
- Sicily 1155. Can I Post the letter
- C语言的字节对齐
- Centos 添加静态路由及 SIOCADDRT错误处理
- Cobbler自动批量部署不同需求的系统
- 轻松掌握正则表达式(1)
- TCP/IP协议三次握手与四次握手流程解析
- CSDN如何转载别人的文章