SQL基础命令

2019-06-09-SQL基础命令

Basic SQL Commands:

1	`SHOW SELECT INSERT CREATE UPDATE DELETE GRANT REVOKE CREATE`

CREATE

1	`CREATE DATABASE [IF NOT EXISTS] db_name [DEFAULT] CHARACTER SET [=] charset_name;`

SHOW

SHOW databases;
/* The SQL SHOW statement displays information contained in the database and its tables.*/
SHOW DATABASES;

SHOW TABLES;
/* The SHOW TABLES command is used to display all of the tables in the currently selected MySQL database.*/

SHOW CREATE TABLE tbl_name;	#查看创建表的语句

SELECT

/* The SELECT statement is used to select data from a database.*/
SELECT column_list FROM tbl_name;
  SELECT * FROM <表名>;
  eg1. SELECT * FROM customers;
  | ID | FirstName | LastName |     City    | ZipCode | Age |
  |----|-----------|----------|-------------|---------|-----|
  |  1 | John      | Smith    | NewYork     |   10199 |  21 |
  |  2 | David     | Williams | Los Angeles |   90052 |  22 |
  |  3 | Chole     | Anderson | Chicago     |   60607 |  24 |
  |  4 | Emily     | Adams    | Houston     |   77201 |  23 |

  eg2. SELECT FirstName,LastName,City FROM customers;
  | ID | FirstName | LastName |     City    |
  |----|-----------|----------|-------------|
  |  1 | John      | Smith    | NewYork     |
  |  2 | David     | Williams | Los Angeles |
  |  3 | Chole     | Anderson | Chicago     | 
  |  4 | Emily     | Adams    | Houston     |

/* The SQL DISTINCT keyword is used in conjunction with SELECT to eliminate all duplicate records and return only unique ones.(不选择重复的数据).*/
  eg1. SELECT DISTINCT column_name1, column_name2 FROM tbl_name;  
  | ID | FirstName | LastName |     City    |
  |----|-----------|----------|-------------|
  |  1 | John      | Smith    | NewYork     |
  |  2 | David     | Williams | Los Angeles |
  |  3 | Chole     | Anderson | Chicago     |
  |  4 | Emily     | Adams    | Houston     |
  |  5 | Yue       | Xing     | NewYork     |

/* Sometimes we need to retrieve just a subset of records(限制选择数据条数).*/
  eg. SELECT column list FROM table_name LIMIT [number of records];  
  eg. SELECT ID, FirstName, LastName, City FROM customers LIMIT 3; 

/* 从第2个位置开始拾取3条记录：(计数由0开始).*/
  eg. SELECT ID, FirstName, LastName, City FROM customers LIMIT 1,3;

/* In SQL, you can provide the table name prior to the column name, by separating them with a dot.The following statements are equivalent:*/  
  eg. SELECT City FROM customers;
  eg. SELECT customers.City FROM customers;

/* ORDER BY is used with SELECT to sort the returned data.*/ 
  eg. SELECT * FROM customers ORDER BY FirstName,Age;
  
SELECT databases();
SELECT version();
SELECT now();

WHERE

/* The WHERE clause is used to extract only those records that fulfill a specified criterion.*/
  eg. SELECT column_list FROM table_name WHERE condition;
  eg. SELECT * FROM customers WHERE ID = 7;

  /* SQL Operators:
     | Operator | Describtion               |
     |----------|---------------------------|
     | =        | equal                     |
     | !=       | Not equal                 |
     | >        | Greater than              |
     | <        | Less than                 |
     | >=       | Greater than or equal     |
     | <=       | Less than or equal        |
     | BETWEEN  | between an inclusiverange |
     | AND      | ----                      |
     | OR       | ----                      |
     | IN       | ----                      |
     | NOT      | ---                       |
   */

  eg. SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2;
  eg. SELECT * FROM customers WHERE ID BETWEEN 3 AND 7;
  eg. SELECT ID, FirstName FROM customers WHERE City = 'New York';
  eg. SELECT ID, Age FROM customers WHERE Age >= 30 AND Age <= 40;
  eg. SELECT * FROM customers WHERE City = 'New York' AND (Age=30 OR Age=35);
  eg. SELECT * FROM customers WHERE City IN ('New York', 'Los Angeles');
  eg. SELECT * FROM customers WHERE City NOT IN ('New York', 'Los Angeles');

CONCAT()

/* The CONCAT function is used to concatenate two or more text values and returns the concatenating string.*/
  eg. SELECT CONCAT(FirstName, ', ' , City) FROM customers; 
  | CONCAT(FirstName,',',City) |
  |----------------------------|
  | John,NewYork               |
  | David,Los Angeles          |
  | Chole,Chicado              |

AS

/* A concatenation results in a new column. The default column name will be the CONCAT function.You can assign a custom name to the resulting.*/
  column using the AS keyword:
  eg. SELECT CONCAT(FirstName,', ', City) AS new_column FROM customers;
  eg. SELECT ID, FirstName, LastName, Salary+500 AS Salary FROM employees;

Function:

/* The UPPER function converts all letters in the specified string to uppercase.*/ 

/* The LOWER function converts the string to lowercase.*/
  eg. SELECT FirstName, UPPER(LastName) AS LastName FROM employees;

/* The SQRT function returns the square root of given value in the argument.*/
  eg. SELECT Salary, SQRT(Salary) FROM employees;

/* Similarly, the AVG function returns the average value of a numeric column:*/
  eg. SELECT AVG(Salary) FROM employees;

/* The SUM function is used to calculate the sum for a column 的 values.*/
  eg. SELECT SUM(Salary) FROM employees;

/* DESC:递减排列(子查询) / ASC:升序排列*/
  eg. SELECT AVG(Salary) FROM employees;
  eg. SELECT FirstName，Salary FROM employees WHERE Salary>3100 ORDER BY Salary DESC; 
  eg. SELECT FirstName，Salary FROM employees WHERE Salary>(SELECT AVG（Salary) FROM employees）ORDER BY Salary DESC; 

/* The LIKE keyword is useful when specifying a search condition within your WHERE clause.*/
  eg. SELECT * FROM employees WHERE LastName LIKE '%s';	 /* 以s结尾的*/
  eg. SELECT * FROM employees WHERE FirstName LIKE 'A%'; /* 以A开头的*/
  eg. SELECT * FROM employees WHERE LastName LIKE '_A';  
/* 2个字符*/

/* The MIN function is used to return the minimum value of an expression in a SELECT statement.*/
  eg. SELECT MIN(Salary) AS Salary FROM employees;

JOIN:

/* To join the two tables, specify them as a comma-separated list in the FROM clause:*/
  eg. SELECT customers.ID, customers.Name, orders.Name, orders.Amount FROM customers, orders WHERE customers.ID=orders.Customer_ID ORDER BY customers.ID;   

            customers table
  | ID  | Name  |     City     | Age |
  | --- | ----- | ------------ | --- |
  | 1   | John  | New York     | 35  |
  | 2   | David | Los Angeles  | 23  |
  | 3   | Chole | Chicago      | 27  |
  | 4   | Emily | Houston      | 34  |
  | 5   | James | Philadelphia | 31  |

		    orders table
  | ID |  Name   | Customer_ID | Amount |
  |----|---------|-------------|----------|
  |  1 | Book    |           3 |     5000 |
  |  2 | Box     |           5 |     3000 |
  |  3 | Toy     |           2 |     4500 |
  |  4 | Flowers |           4 |     1800 |
  |  5 | Cake    |           1 |     6700 |
	
		    Joining Tables
  | ID |  Name |   Name  | Amount |
  |----|-------|---------|--------|
  |  1 | John  | Cake    |   6700 |
  |  2 | David | Toy     |   4500 |
  |  3 | Chole | Book    |   5000 |
  |  4 | Emoly | Flowers |   1800 |
  |  5 | James | Box     |   3000 |

/* AS*/
  eg. SELECT customers.ID, customers.Name, orders.Name, orders.Amount FROM customers AS ct, orders AS ord WHERE ct.ID=ord.Customer_ID ORDER BY ct.ID;

  # INNER JOIN 相当于 JOIN
    eg. SELECT column_name(s) FROM table1 INNER JOIN table2 ON table1.column_name=table2.column_name;
  # LEFT JOIN  LEFT JOIN返回左表中的所有行，即使右表中没有匹配项也是如此
    eg. SELECT table1.column1, table2.column2...FROM table1 LEFT OUTER JOIN table2 ON table1.column_name = table2.column_name;
  # RIGHT JOIN 即使左表中没有匹配项，RIGHT JOIN也会返回右表中的所有行
    eg. SELECT table1.column1, table2.column2...FROM table1 RIGHT OUTER JOIN table2 ON table1.column_name = table2.column_name;
# 3)UNION:UNION运算符用于组合两个或多个SELECT语句的结果集
  eg. SELECT column_name(s) FROM table1 UNION SELECT column_name(s) FROM table2;
# 4)UNION ALL:UNION ALL选择每个表中的所有行并将它们组合成一个表
  eg. SELECT ID, FirstName, LastName, City FROM First UNION ALL SELECT ID, FirstName, LastName, City FROM Second;

INSERT

INSERT INTO 表名 (列名1,列名2,...) VALUES (列值1,列值2,...);
eg. INSERT INTO table_name VALUES(value1, value2, value3,...);
eg. INSERT INTO Employees VALUES(8, 'Anthony', 'Young', 35);

eg. INSERT INTO table_name (column1, column2, column3, ...,columnN)  VALUES (value1, value2, value3,...valueN);	
eg. INSERT INTO Employees (ID, FirstName, LastName, Age) VALUES (8, 'Anthony', 'Young', 35);

UPDATE

1
2
3

UPDATE 表名 SET 列名=值 where 条件;
eg. UPDATE tbl_name SET column1=value1, column2=value2, ... WHERE condition;       
eg. UPDATE Employees SET Salary=5000 WHERE ID=1;

DELETE

1 2	`eg. DELETE FROM tbl_name WHERE condition; eg. DELETE FROM Employees WHERE ID=1;`

CREATE TABLES

CREATE TABLE <表名> 
(
	列名1 数据类型[列级别约束条件][default],
    列名2 数据类型[列级别约束条件][default],
    ...
    [表级别约束条件]
);
eg. CREATE TABLE table_name(
	  column_name1 data_type(size),
	  column_name2 data_type(size),
	  column_name3 data_type(size),
	  ....
	  column_Nu data_type(size)
	);

eg. CREATE TABLE Users(
   	  UserID int,
   	  FirstName varchar(100), 
   	  LastName varchar(100),
  	  City varchar(100),
  	  PRIMARY KEY(UserID)
    );

eg. CREATE TABLE testDB(
	  id int(11) key,         # primary key(id)
	  name varchar(45) not null comment 'student name'
	);

ALTER: 用于添加，删除或修改现有表中的列

ALTER TABLE <表名> ADD (<列名> <类型>,<列名> <类型>,...) [约束条件] [FIRST | AFTER 已存在列名];
eg. ALTER TABLE People ADD DateOfBirth varchar(50);
  上述SQL代码People表中添加一个名为DateOfBirth的新列

ALTER TABLE <表名> DROP <列名>;
eg. ALTER TABLE People DROP DateOfBirth;
  上述SQL代码删除People表中名为DateOfBirth的列。

eg. DROP TABLE IF EXISTS People,People1;
	DROP DATABASES [IF EXITSTS] db_name;

ALTER TABLE <表名> CHANGE <旧列名> <新列名> <新数据类型>;
ALTER TABLE <表名> MODIFY <列名> <列类型> [FIRST|AFTER] <列2>;
eg. ALTER TABLE People CHANGE FirstName name varchar(100);

ALTER TABLE <旧表名> RENAME [TO] <新表名>;
eg. ALTER TABLE People RENAME TO Users;
  上述SQL代码重命名列
  
ALTER DATABASES db_name [DEFAULT] CHARACTER SET [=] charset_name
eg. ALTER databases test2 character set latin1;
	SHOW variables like 'character%';

约束

约束介绍

约束类型	非空约束	主键约束	唯一约束	默认约束	外键约束
关键字	NOT NULL	PRIMARY KEY	UNIQUE	DEFAULT	FOREIGN KEY

NOT NULL

/*创建表时加非空约束*/
CREATE TABLE bookinfo(
	book_id int,
    book_name varchar(20) NOT NULL
);
/*修改表*/
ALTER TABLE bookinfo MODIFY book_name varchar(20) NOT NULL;

PRIMARY KEY

/*单字段*/
CREATE TABLE bookinfo(
	book_id int PRIMARY KEY,
    book_name varchar(20) NOT NULL
    /*CONSTRAINT pk_id PRIMARY KEY (book_id)*/
);
/*复合主键*/
CREATE TABLE bookinfo(
	book_id int,
    book_name varchar(20) NOT NULL,
    PRIMARY KEY(book_id,card_id)
);
/*修改主键*/
ALTER TABLE bookinfo ADD PRIMARY KEY(book_id);
ALTER TABLE bookinfo MODIFY book_id INT PRIMARY KEY;
ALTER TABLE bookinfo ADD CONSTRAINT PK_ID PRIMARY KEY(book_id);
/*删除主键*/
ALTER TABLE bookinfo DROP PRIMARY KEY;

UNIQUE

CREATE TABLE bookinfo(
	book_id int PRIMARY KEY,
    book_name varchar(20) UNIQUE NOT NULL,
    /*CONSTRAINT uk_bname UNIQUE (book_id)*/
);
/*删除主键*/
ALTER TABLE bookinfo DROP KEY uk_bname;

FOREIGN KEY

/*创建外键*/
CREATE TABLE bookcategory(
	category_id int PRIMARY KEY,
    category varchar(20),
    parent_id int
);
CREATE TABLE bookinfo(
	book_id int PRIMARY KEY,
    book_category_id int,
    CONSTRAINT fk_cid FOREIGN KEY (book_category_id) REFERENCES bookcategory (category_id) ON DELETE CASCADE;
);

ALTER TABLE bookinfo DROP FOREIGN KEY fk_cid;
ALTER TABLE bookinfo ADD FOREIGN  KEY (book_category_id) REFERENCE bookcategory (category_id);

/*CASCADE 从父表删除或更新且自动删除或更新子表中匹配的行*/

创建用户

1
2
3

CREATE USER 用户名@IP地址 IDENTIFIED BY '密码'; # 指定的IP上登陆
CREATE USER 用户名@'%' IDENTIFIED BY '密码';    # 登陆不限定IP
DROP USER 用户名@IP地址;

授权/查看权限/解除权限

GRANT 权限1,...权限n ON 数据库.* TO 用户名@IP地址;
GRANT ALL PRIVILEGES ON 数据库.* TO 用户名@IP地址;

SHOW GRANTS FOR 用户名@IP地址;

REVOKE 权限1,...权限n ON 数据库.* FROM 用户名@IP地址;
REVOKE ALL PRIVILEGES ON 数据库.* FROM 用户名@IP地址;

忘记密码问题解决

service mysql stop 
mysqld_safe --user=mysql --skip-grant-tables --skip-networking &   # 跳过授权表模式  
mysql -uroot -p    # 不输密码直接回车
use mysql;
update user set password=PASSWORD("123") where user ='root';  # 设置新密码为123  
flush privileges;  
quit;

service mysql stop   
service mysql start     # 以正常模式启动
mysql -u root -p        # 回车输入新密码即可 

MariaDB [(none)]> delete from user where user='';
MariaDB [(none)]> delete from user where password='';  
MariaDB [(none)]> create database anyue default character set latin1;  
[root@xingyue masR5]# mysql -uroot -p123456 anyue < masR5.sql --default-character-set=lantin1

回到顶部

SQL

#Mysql #数据库

SQL基础命令

https://anyu967.github.io/posts/5b397e67.html

作者

anyu967

发布于

2019年6月9日

许可协议

Adobe 上一篇

BatShell 下一篇