Shulou(Shulou.com)06/01 Report--

Basic parameters of the SQL statement

Create database benet / / create a database named benetuse benet / / Open the benet database create table A1 / / create a table A1 (number int identity (1) not null, / / identity (1) indicates the column identity column Both seed and increment value are 1 student number int primary key not null. / / primary key means the name of the primary key column is nvarchar (20) not null. / / not null means that empty ID number nvarchar (18) not null, age tinyint not null, class int not null, and remarks nvarchar (1000) null are not allowed. ) alter table A1add date of birth datetime not null// means to add a "date of birth" column to table A1 alter table A1alter column remarks nvarchar (2000) null// modify parameters in table A1 alter table A1drop column remarks / delete "remarks" column drop table A1 / delete table A1 insert into B1 (student number, name, document number, age, class, remarks) values (2pint 'Liuyan', '1102581983082882) ) / insert update B1 set remarks of Liu Yan into the B1 table = 'math class representative' where name = 'Liuyan' / / change the remarks of Liu Yan in the B1 table to the math class representative delete from B1 where student number = 2ram / delete the record with the student number 2 in the table

About deleted statements

Syntax:

Delect from table name truncate table table name / / is to clear all the contents of the table

Operators, wildcards, logical operators to be used when querying

Select * from B1amp / View all columns select names in B1 table Class from B1swap / check the name and class column select name in the table from B1 where Note = 'English Section Representative' / / View the names of all English Section representatives in the B1 table select * from B1 where basic salary between 8000 and 10 000 shock / check all the information of the employees with a base salary of 8000 to 10000 in the B1 table select * from B1 where basic salary 2000 / check all the information select * of employees below 1000 and above 2000 in the B1 table From B1 where basic salary in (8000 dint 9000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000, Employee Information select * from B1 where name like'% 'and Job =' Operation engineer'/ / View the information of the operation and maintenance engineer surnamed Wang in Table B1 select * from B1 where remarks is not null// View the employee information select top 3 * from B1 in Table B1 not empty / View the data information select name as name in the first three rows of Table B1 Document number as idcard from B1ram / query the data in the columns "name" and "document number" in table B1 The name is changed to name, and the × × number is changed to idcardselect * from B1 order by base salary desc// to view the information of all employees in the B1 table, and to display the query results according to the basic salary from high to low select * from B1 order by base salary asc// to view the information of all employees in the B1 table. Display the query results according to the basic salary from low to high select distinct job from B1 move / check which jobs there are in the B1 table select name as name, document number, job, basic salary from B1where certificate number like'_ 0% 'and job! =' cto'order by basic salary desc// lists the third place from the left to meet the certificate number in the B1 table is 0. Except for cto, the name, certificate number, position and base salary of all employees, where the name is displayed as name, and the query results are sorted by the highest to the lowest (_ _ 0% is two underscores). An underscore represents a vacancy) select name, document number, job into new01 from B1 / generate a new table new01 (new form does not need to be created beforehand) insert into new02 (name, title, date of birth) select name, title, date of birth from B1 where base salary > = 15000 / / name and position of all employees whose basic salary is greater than or equal to 15000 in B1 table And the date of birth is saved to the new02 table (note that the Table_1 table here needs to be established in advance) insert into new03 (name, title, date of birth) insert into new03 (name, title, date of birth) select 'Zhang San', 'operation and maintenance', '1995-01-01' union select'Li Si', 'operation and maintenance', '1996-01-01' union select name, title, date of birth from B1 / / will be the name, title and date of birth of all employees in the B1 table And the newly entered information about the two employees, saved to the new table new03

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.